您的位置:68399皇家赌场 > 虚拟主机 > 【澳门皇家赌场55533网址】Profiling MySQL queries fr

【澳门皇家赌场55533网址】Profiling MySQL queries fr

发布时间:2019-05-03 21:45编辑:虚拟主机浏览(167)

    1 mysqlbinlog -vv --base64-output=decode-rows /path/mysql-bin.000010 > mysql-bin000010.sql
    2 pt-query-digest --type=binlog /path/mysql-bin000001.sql > report2.log
    

    Introduce

    mysql> SHOW PROFILES;
     ---------- ------------ ---------------------------------------- 
    | Query_ID | Duration   | Query                                  |
     ---------- ------------ ---------------------------------------- 
    |        1 | 0.00011150 | SELECT * FROM sysbench.sbtest1 LIMIT 1 |
     ---------- ------------ ---------------------------------------- 
    1 row in set, 1 warning (0.00 sec)
    mysql> SHOW PROFILE SOURCE FOR QUERY 1;
     ---------------------- ---------- ----------------------- ------------------ ------------- 
    | Status               | Duration | Source_function       | Source_file      | Source_line |
     ---------------------- ---------- ----------------------- ------------------ ------------- 
    | starting             | 0.000017 | NULL                  | NULL             |        NULL |
    | checking permissions | 0.000003 | check_access          | sql_parse.cc     |        5797 |
    | Opening tables       | 0.000021 | open_tables           | sql_base.cc      |        5156 |
    | init                 | 0.000009 | mysql_prepare_select  | sql_select.cc    |        1050 |
    | System lock          | 0.000005 | mysql_lock_tables     | lock.cc          |         306 |
    | optimizing           | 0.000002 | optimize              | sql_optimizer.cc |         138 |
    | statistics           | 0.000006 | optimize              | sql_optimizer.cc |         381 |
    | preparing            | 0.000005 | optimize              | sql_optimizer.cc |         504 |
    | executing            | 0.000001 | exec                  | sql_executor.cc  |         110 |
    | Sending data         | 0.000025 | exec                  | sql_executor.cc  |         190 |
    | end                  | 0.000002 | mysql_execute_select  | sql_select.cc    |        1105 |
    | query end            | 0.000003 | mysql_execute_command | sql_parse.cc     |        5465 |
    | closing tables       | 0.000004 | mysql_execute_command | sql_parse.cc     |        5544 |
    | freeing items        | 0.000005 | mysql_parse           | sql_parse.cc     |        6969 |
    | cleaning up          | 0.000006 | dispatch_command      | sql_parse.cc     |        1874 |
     ---------------------- ---------- ----------------------- ------------------ ------------- 
    15 rows in set, 1 warning (0.00 sec)
    

    Profiling MySQL queries from Performance Schema,profilingqueries

    转自:

    When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries aka SET profiling = 1; . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative?

    Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This means that you cannot capture profiling information for queries running from other connections. If you are using Percona Server, the profiling option for log_slow_verbosity is a nice alternative, unfortunately, not everyone is using Percona Server.

    Now, for a quick demo: I execute a simple query and profile it below. Note that all of these commands are executed from a single session to my test instance.

    mysql> SHOW PROFILES;
     ---------- ------------ ---------------------------------------- 
    | Query_ID | Duration   | Query                                  |
     ---------- ------------ ---------------------------------------- 
    |        1 | 0.00011150 | SELECT * FROM sysbench.sbtest1 LIMIT 1 |
     ---------- ------------ ---------------------------------------- 
    1 row in set, 1 warning (0.00 sec)
    mysql> SHOW PROFILE SOURCE FOR QUERY 1;
     ---------------------- ---------- ----------------------- ------------------ ------------- 
    | Status               | Duration | Source_function       | Source_file      | Source_line |
     ---------------------- ---------- ----------------------- ------------------ ------------- 
    | starting             | 0.000017 | NULL                  | NULL             |        NULL |
    | checking permissions | 0.000003 | check_access          | sql_parse.cc     |        5797 |
    | Opening tables       | 0.000021 | open_tables           | sql_base.cc      |        5156 |
    | init                 | 0.000009 | mysql_prepare_select  | sql_select.cc    |        1050 |
    | System lock          | 0.000005 | mysql_lock_tables     | lock.cc          |         306 |
    | optimizing           | 0.000002 | optimize              | sql_optimizer.cc |         138 |
    | statistics           | 0.000006 | optimize              | sql_optimizer.cc |         381 |
    | preparing            | 0.000005 | optimize              | sql_optimizer.cc |         504 |
    | executing            | 0.000001 | exec                  | sql_executor.cc  |         110 |
    | Sending data         | 0.000025 | exec                  | sql_executor.cc  |         190 |
    | end                  | 0.000002 | mysql_execute_select  | sql_select.cc    |        1105 |
    | query end            | 0.000003 | mysql_execute_command | sql_parse.cc     |        5465 |
    | closing tables       | 0.000004 | mysql_execute_command | sql_parse.cc     |        5544 |
    | freeing items        | 0.000005 | mysql_parse           | sql_parse.cc     |        6969 |
    | cleaning up          | 0.000006 | dispatch_command      | sql_parse.cc     |        1874 |
     ---------------------- ---------- ----------------------- ------------------ ------------- 
    15 rows in set, 1 warning (0.00 sec)
    

    To demonstrate how we can achieve the same with Performance Schema, we first identify our current connection id. In the real world, you might want to get the connection/processlist id of the thread you want to watch i.e. from SHOW PROCESSLIST .

     

    mysql> SELECT THREAD_ID INTO @my_thread_id
        -> FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID();
    Query OK, 1 row affected (0.00 sec)
    

     

    Next, we identify the bounding EVENT_IDs for the statement stages. We will look for the statement we wanted to profile using the query below from the events_statements_history_long table. Your LIMIT clause may vary depending on how much queries the server might be getting.

     

    mysql> SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, SQL_TEXT, NESTING_EVENT_ID
        -> FROM events_statements_history_long
        -> WHERE THREAD_ID = @my_thread_id
        ->   AND EVENT_NAME = 'statement/sql/select'
        -> ORDER BY EVENT_ID DESC LIMIT 3 G
    *************************** 1. row ***************************
           THREAD_ID: 13848
            EVENT_ID: 419
        END_EVENT_ID: 434
            SQL_TEXT: SELECT THREAD_ID INTO @my_thread_id
    FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID()
    NESTING_EVENT_ID: NULL
    *************************** 2. row ***************************
           THREAD_ID: 13848
            EVENT_ID: 374
        END_EVENT_ID: 392
            SQL_TEXT: SELECT * FROM sysbench.sbtest1 LIMIT 1
    NESTING_EVENT_ID: NULL
    *************************** 3. row ***************************
           THREAD_ID: 13848
            EVENT_ID: 353
        END_EVENT_ID: 364
            SQL_TEXT: select @@version_comment limit 1
    NESTING_EVENT_ID: NULL
    3 rows in set (0.02 sec)
    

     

    From the results above, we are mostly interested with the EVENT_ID and END_EVENT_ID values from the second row, this will give us the stage events of this particular query from the events_stages_history_long table.

     

    mysql> SELECT EVENT_NAME, SOURCE, (TIMER_END-TIMER_START)/1000000000 as 'DURATION (ms)'
        -> FROM events_stages_history_long
        -> WHERE THREAD_ID = @my_thread_id AND EVENT_ID BETWEEN 374 AND 392;
     -------------------------------- ---------------------- --------------- 
    | EVENT_NAME                     | SOURCE               | DURATION (ms) |
     -------------------------------- ---------------------- --------------- 
    | stage/sql/init                 | mysqld.cc:998        |        0.0214 |
    | stage/sql/checking permissions | sql_parse.cc:5797    |        0.0023 |
    | stage/sql/Opening tables       | sql_base.cc:5156     |        0.0205 |
    | stage/sql/init                 | sql_select.cc:1050   |        0.0089 |
    | stage/sql/System lock          | lock.cc:306          |        0.0047 |
    | stage/sql/optimizing           | sql_optimizer.cc:138 |        0.0016 |
    | stage/sql/statistics           | sql_optimizer.cc:381 |        0.0058 |
    | stage/sql/preparing            | sql_optimizer.cc:504 |        0.0044 |
    | stage/sql/executing            | sql_executor.cc:110  |        0.0008 |
    | stage/sql/Sending data         | sql_executor.cc:190  |        0.0251 |
    | stage/sql/end                  | sql_select.cc:1105   |        0.0017 |
    | stage/sql/query end            | sql_parse.cc:5465    |        0.0031 |
    | stage/sql/closing tables       | sql_parse.cc:5544    |        0.0037 |
    | stage/sql/freeing items        | sql_parse.cc:6969    |        0.0056 |
    | stage/sql/cleaning up          | sql_parse.cc:1874    |        0.0006 |
     -------------------------------- ---------------------- --------------- 
    15 rows in set (0.01 sec)
    

     

    As you can see the results are pretty close, not exactly the same but close. SHOW PROFILE shows Duration in seconds, while the results above is in milliseconds.

    Some limitations to this method though:

    • As we’ve seen it takes a few hoops to dish out the information we need. Because we have to identify the statement we have to profile manually, this procedure may not be easy to port into tools like the sys schema or pstop.
    • Only possible if Performance Schema is enabled (by default its enabled since MySQL 5.6.6, yay!)
    • Does not cover all metrics compared to the native profiling i.e. CONTEXT SWITCHES, BLOCK IO, SWAPS
    • Depending on how busy the server you are running the tests, the sizes of the history tables may be too small, as such you either have to increase or loose the history to early i.e. performance_schema_events_stages_history_long_size variable. Using ps_history might help in this case though with a little modification to the queries.
    • The resulting Duration per event may vary, I would think this may be due to the additional as described on performance_timers table. In any case we hope to get this cleared up as result when this bug is fixed.

     

    MySQL queries from Performance Schema,profilingqueries 转自:...

     

     

    From the results above, we are mostly interested with the EVENT_ID and END_EVENT_ID values from the second row, this will give us the stage events of this particular query from the events_stages_history_long table.

     

        There're many ways relevent with performance tuning.For example,using indexes properly is important in doing that.At the very beginning of releasing a project,we're probably supposed to create many different indexes(especially union index) to increase the efficiency of queries on target tables even if some of them are seldom or never used at all.We are sure about that it is not the more the better of indexes on a table.Indexes will occupy more disk space and will cost a lot in maintaining.Alternatively,we should reduce the indexes which are not usually used by freqeuntly cheking them.Therefore,I'll introduce a tool which can help us in the aspect.

    • As we’ve seen it takes a few hoops to dish out the information we need. Because we have to identify the statement we have to profile manually, this procedure may not be easy to port into tools like the sys schema or pstop.
    • Only possible if Performance Schema is enabled (by default its enabled since MySQL 5.6.6, yay!)
    • Does not cover all metrics compared to the native profiling i.e. CONTEXT SWITCHES, BLOCK IO, SWAPS
    • Depending on how busy the server you are running the tests, the sizes of the history tables may be too small, as such you either have to increase or loose the history to early i.e. performance_schema_events_stages_history_long_size variable. Using ps_history might help in this case though with a little modification to the queries.
    • The resulting Duration per event may vary, I would think this may be due to the additional as described on performance_timers table. In any case we hope to get this cleared up as result when this bug is fixed.

    5.Analyze tcpdump.

    1 [root@zlm2 11:46:48 /data/mysql/mysql3306/data]
    2 #pt-index-usage -h192.168.1.101 -P3306 -urepl -prepl4slave -Dzlm /data/mysql/mysql3306/data/slow.log
    3 
    4 ALTER TABLE `zlm`.`test_index_usage` DROP KEY `idx_key1`; -- type:non-unique
    5 
    6 [root@zlm2 11:46:50 /data/mysql/mysql3306/data]
    7 #
    

     

     

    Examples

    Next, we identify the bounding EVENT_IDs for the statement stages. We will look for the statement we wanted to profile using the query below from the events_statements_history_long table. Your LIMIT clause may vary depending on how much queries the server might be getting.

     

    1 [root@zlm2 11:25:20 /data/mysql/mysql3306/data]
    2 #pt-index-usage -h192.168.1.101 -P3306 -urepl -prepl4slave -Dzlm --create-views --no-report --create-save-results-database --save-results-database h=192.168.1.102,P=3306,u=repl,p=repl4slave,D=index_usage /data/mysql/mysql3306/data/slow.log
    3 
    4 [root@zlm2 11:26:37 /data/mysql/mysql3306/data]
    5 #
    

    To demonstrate how we can achieve the same with Performance Schema, we first identify our current connection id. In the real world, you might want to get the connection/processlist id of the thread you want to watch i.e. from SHOW PROCESSLIST .

    7.Analyze slow log since time until time.

     

    When optimizing queries and investigating performance issues, MySQL comes with built in support for profiling queries aka SET profiling = 1; . This is already awesome and simple to use, but why the PERFORMANCE_SCHEMA alternative?

     

     

    Now, for a quick demo: I execute a simple query and profile it below. Note that all of these commands are executed from a single session to my test instance.

    1 [root@zlm2 09:43:12 /data/mysql/mysql3306/data]
    2 #cat slow.log
    3 
    4 # Time: 2018-06-23T07:42:52.891778Z
    5 # User@Host: root[root] @ localhost []  Id:     7
    6 # Query_time: 60.001239  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    7 SET timestamp=1529739772;
    8 select sleep(60);
    
     1 (root@localhost mysql3306.sock)[zlm]10:32:04>create table if not exists test_index_usage(
     2     -> id int unsigned auto_increment not null,
     3     -> order_id int unsigned not null default 0,
     4     -> name varchar(10) not null default '',
     5     -> gender enum('male','female') not null,
     6     -> primary key(id)
     7     -> ) auto_increment=1 engine=innodb charset=utf8mb4;
     8 Query OK, 0 rows affected (0.04 sec)
     9 
    10 (root@localhost mysql3306.sock)[zlm]10:32:14>delimiter $$
    11 (root@localhost mysql3306.sock)[zlm]10:32:19>create procedure pro_index_usage (in n1 int,in s1 varchar(10),in s2 varchar(10))
    12     -> begin
    13     -> declare i int unsigned default 0;
    14     -> start transaction;
    15     -> while i < n1 do
    16     -> insert into test_index_usage(order_id,gender,name) values(i,s1,s2);
    17     -> set i=i 1;
    18     -> end while;
    19     -> commit;
    20     -> end;
    21     -> $$
    22 Query OK, 0 rows affected (0.00 sec)
    23 
    24 (root@localhost mysql3306.sock)[zlm]10:32:19>delimiter ;
    25 (root@localhost mysql3306.sock)[zlm]10:32:20>call pro_index_usage(100000,'male','zlm');
    26 Query OK, 0 rows affected (5.59 sec)
    27 
    28 (root@localhost mysql3306.sock)[zlm]10:32:31>call pro_index_usage(100000,'female','aaron8219');
    29 Query OK, 0 rows affected (5.38 sec)
    30 
    31 (root@localhost mysql3306.sock)[zlm]10:32:38>select count(*) from test_index_usage;
    32  ---------- 
    33 | count(*) |
    34  ---------- 
    35 |   200000 |
    36  ---------- 
    37 1 row in set (0.05 sec)
    38 
    39 (root@localhost mysql3306.sock)[zlm]10:32:40>select * from test_index_usage limit 5;
    40  ---- ---------- ------ -------- 
    41 | id | order_id | name | gender |
    42  ---- ---------- ------ -------- 
    43 |  1 |        0 | zlm  | male   |
    44 |  2 |        1 | zlm  | male   |
    45 |  3 |        2 | zlm  | male   |
    46 |  4 |        3 | zlm  | male   |
    47 |  5 |        4 | zlm  | male   |
    48  ---- ---------- ------ -------- 
    49 5 rows in set (0.00 sec)
    50 
    51 (root@localhost mysql3306.sock)[zlm]10:34:24>alter table test_index_usage add key idx_key1 (order_id,gender);
    52 Query OK, 0 rows affected (0.64 sec)
    53 Records: 0  Duplicates: 0  Warnings: 0
    54 
    55 (root@localhost mysql3306.sock)[zlm]10:35:12>alter table test_index_usage add key idx_key2 (order_id,gender,name);
    56 Query OK, 0 rows affected (0.94 sec)
    57 Records: 0  Duplicates: 0  Warnings: 0
    58 
    59 (root@localhost mysql3306.sock)[zlm]10:35:20>show keys from test_index_usage;
    60  ------------------ ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
    61 | Table            | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    62  ------------------ ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
    63 | test_index_usage |          0 | PRIMARY  |            1 | id          | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
    64 | test_index_usage |          1 | idx_key1 |            1 | order_id    | A         |      100061 |     NULL | NULL   |      | BTREE      |         |               |
    65 | test_index_usage |          1 | idx_key1 |            2 | gender      | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
    66 | test_index_usage |          1 | idx_key2 |            1 | order_id    | A         |       99905 |     NULL | NULL   |      | BTREE      |         |               |
    67 | test_index_usage |          1 | idx_key2 |            2 | gender      | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
    68 | test_index_usage |          1 | idx_key2 |            3 | name        | A         |      199601 |     NULL | NULL   |      | BTREE      |         |               |
    69  ------------------ ------------ ---------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
    70 6 rows in set (0.00 sec)
    
    mysql> SELECT THREAD_ID INTO @my_thread_id
        -> FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID();
    Query OK, 1 row affected (0.00 sec)
    

    Introduce

    summary

    Because profiling will be removed soon (already deprecated on MySQL 5.6 ad 5.7); the built-in profiling capability can only be enabled per session. This means that you cannot capture profiling information for queries running from other connections. If you are using Percona Server, the profiling option for log_slow_verbosity is a nice alternative, unfortunately, not everyone is using Percona Server.

    Make sure the slow log is enabled.

    Check the execute plan.

    mysql> SELECT THREAD_ID, EVENT_ID, END_EVENT_ID, SQL_TEXT, NESTING_EVENT_ID
        -> FROM events_statements_history_long
        -> WHERE THREAD_ID = @my_thread_id
        ->   AND EVENT_NAME = 'statement/sql/select'
        -> ORDER BY EVENT_ID DESC LIMIT 3 G
    *************************** 1. row ***************************
           THREAD_ID: 13848
            EVENT_ID: 419
        END_EVENT_ID: 434
            SQL_TEXT: SELECT THREAD_ID INTO @my_thread_id
    FROM threads WHERE PROCESSLIST_ID = CONNECTION_ID()
    NESTING_EVENT_ID: NULL
    *************************** 2. row ***************************
           THREAD_ID: 13848
            EVENT_ID: 374
        END_EVENT_ID: 392
            SQL_TEXT: SELECT * FROM sysbench.sbtest1 LIMIT 1
    NESTING_EVENT_ID: NULL
    *************************** 3. row ***************************
           THREAD_ID: 13848
            EVENT_ID: 353
        END_EVENT_ID: 364
            SQL_TEXT: select @@version_comment limit 1
    NESTING_EVENT_ID: NULL
    3 rows in set (0.02 sec)
    
    1 pt-query-digest --since '2018-06-23 08:30:00' --until '2018-06-23 10:30:00' slow.log > report7.log
    
    1 --save-results-database -- Save output results into the specific tables of database.
    2 --create-save-results-database -- Create a database with necessary tables if set "--save-results-database" but not exist.
    3 --empty-save-results-tables -- Drop and recreate all the tables which are specified by "--save-results-database".
    4 --create-views -- Create views for tables in database which is specified by "--save-results-database".
    5 --no-report -- Don't generate a report but put results into tables for later analysis."--save-results-database" is indispensable when you set this option.
    6 --report-format -- The only format is "drop_unused_indexes" now.
    7 --drop -- Specify the type of index which you want to drop(Default value is non-unique).
    

    As you can see the results are pretty close, not exactly the same but close. SHOW PROFILE shows Duration in seconds, while the results above is in milliseconds.

     

    Main parameter

    mysql> SELECT EVENT_NAME, SOURCE, (TIMER_END-TIMER_START)/1000000000 as 'DURATION (ms)'
        -> FROM events_stages_history_long
        -> WHERE THREAD_ID = @my_thread_id AND EVENT_ID BETWEEN 374 AND 392;
     -------------------------------- ---------------------- --------------- 
    | EVENT_NAME                     | SOURCE               | DURATION (ms) |
     -------------------------------- ---------------------- --------------- 
    | stage/sql/init                 | mysqld.cc:998        |        0.0214 |
    | stage/sql/checking permissions | sql_parse.cc:5797    |        0.0023 |
    | stage/sql/Opening tables       | sql_base.cc:5156     |        0.0205 |
    | stage/sql/init                 | sql_select.cc:1050   |        0.0089 |
    | stage/sql/System lock          | lock.cc:306          |        0.0047 |
    | stage/sql/optimizing           | sql_optimizer.cc:138 |        0.0016 |
    | stage/sql/statistics           | sql_optimizer.cc:381 |        0.0058 |
    | stage/sql/preparing            | sql_optimizer.cc:504 |        0.0044 |
    | stage/sql/executing            | sql_executor.cc:110  |        0.0008 |
    | stage/sql/Sending data         | sql_executor.cc:190  |        0.0251 |
    | stage/sql/end                  | sql_select.cc:1105   |        0.0017 |
    | stage/sql/query end            | sql_parse.cc:5465    |        0.0031 |
    | stage/sql/closing tables       | sql_parse.cc:5544    |        0.0037 |
    | stage/sql/freeing items        | sql_parse.cc:6969    |        0.0056 |
    | stage/sql/cleaning up          | sql_parse.cc:1874    |        0.0006 |
     -------------------------------- ---------------------- --------------- 
    15 rows in set (0.01 sec)
    

    *    pt-query-digest is a tool focus on log analysis.It is supported to analyze slow log,general log,binlog and so on in different ways.You can also use it to analyze logs and simultaneously put these informations into tables on remote host(server) that you specified.That's really convenient and flexible for us DBA to work efficiently in performance tuning.
    *

    Execute pt-index-usage(create database and tables & views).

    Some limitations to this method though:

    Preface

     

    转自:

     

      1 (root@localhost mysql3306.sock)[(none)]11:26:32>show databases;
      2  -------------------- 
      3 | Database           |
      4  -------------------- 
      5 | information_schema |
      6 | index_usage        |  -- This is the newly create database which contains tables created by pt-index-usage.
      7 | mysql              |
      8 | performance_schema |
      9 | sys                |
     10 | zlm                |
     11  -------------------- 
     12 6 rows in set (0.00 sec)
     13 
     14 (root@localhost mysql3306.sock)[(none)]11:26:56>use index_usage;
     15 Reading table information for completion of table and column names
     16 You can turn off this feature to get a quicker startup with -A
     17 
     18 Database changed
     19 (root@localhost mysql3306.sock)[index_usage]11:27:06>show tables;
     20  --------------------------------- 
     21 | Tables_in_index_usage           |
     22  --------------------------------- 
     23 | index_alternatives              |
     24 | index_usage                     |
     25 | indexes                         |
     26 | queries                         |
     27 | tables                          |
     28 | view_index_alternates           |
     29 | view_index_has_alternates       |
     30 | view_index_usage                |
     31 | view_query_uses_several_indexes |
     32 | view_required_indexes           |
     33 | view_unused_index_alternates    |
     34  --------------------------------- 
     35 11 rows in set (0.00 sec)
     36 
     37 (root@localhost mysql3306.sock)[index_usage]11:29:26>select * from index_alternatives;
     38  --------------------- ----- ------------------ ---------- ---------- ----- 
     39 | query_id            | db  | tbl              | idx      | alt_idx  | cnt |
     40  --------------------- ----- ------------------ ---------- ---------- ----- 
     41 | 4638883468153013157 | zlm | test_index_usage | idx_key2 | idx_key1 |   1 |
     42  --------------------- ----- ------------------ ---------- ---------- ----- 
     43 1 row in set (0.00 sec)
     44 
     45 (root@localhost mysql3306.sock)[index_usage]11:29:31>select * from index_usage;
     46  --------------------- ----- ------------------ ---------- ----- 
     47 | query_id            | db  | tbl              | idx      | cnt |
     48  --------------------- ----- ------------------ ---------- ----- 
     49 | 4638883468153013157 | zlm | test_index_usage | idx_key2 |   1 |
     50  --------------------- ----- ------------------ ---------- ----- 
     51 1 row in set (0.00 sec)
     52 
     53 (root@localhost mysql3306.sock)[index_usage]11:29:41>select * from indexes;
     54  ------- --------------------------- ----------- ----- 
     55 | db    | tbl                       | idx       | cnt |
     56  ------- --------------------------- ----------- ----- 
     57 | mysql | columns_priv              | PRIMARY   |   0 |
     58 | mysql | db                        | PRIMARY   |   0 |
     59 | mysql | db                        | User      |   0 |
     60 | mysql | engine_cost               | PRIMARY   |   0 |
     61 | mysql | event                     | PRIMARY   |   0 |
     62 | mysql | func                      | PRIMARY   |   0 |
     63 | mysql | help_category             | name      |   0 |
     64 | mysql | help_category             | PRIMARY   |   0 |
     65 | mysql | help_keyword              | name      |   0 |
     66 | mysql | help_keyword              | PRIMARY   |   0 |
     67 | mysql | help_relation             | PRIMARY   |   0 |
     68 | mysql | help_topic                | name      |   0 |
     69 | mysql | help_topic                | PRIMARY   |   0 |
     70 | mysql | ndb_binlog_index          | PRIMARY   |   0 |
     71 | mysql | plugin                    | PRIMARY   |   0 |
     72 | mysql | proc                      | PRIMARY   |   0 |
     73 | mysql | procs_priv                | Grantor   |   0 |
     74 | mysql | procs_priv                | PRIMARY   |   0 |
     75 | mysql | proxies_priv              | Grantor   |   0 |
     76 | mysql | proxies_priv              | PRIMARY   |   0 |
     77 | mysql | servers                   | PRIMARY   |   0 |
     78 | mysql | server_cost               | PRIMARY   |   0 |
     79 | mysql | tables_priv               | Grantor   |   0 |
     80 | mysql | tables_priv               | PRIMARY   |   0 |
     81 | mysql | time_zone                 | PRIMARY   |   0 |
     82 | mysql | time_zone_leap_second     | PRIMARY   |   0 |
     83 | mysql | time_zone_name            | PRIMARY   |   0 |
     84 | mysql | time_zone_transition      | PRIMARY   |   0 |
     85 | mysql | time_zone_transition_type | PRIMARY   |   0 |
     86 | mysql | user                      | PRIMARY   |   0 |
     87 | sys   | sys_config                | PRIMARY   |   0 |
     88 | zlm   | checksums                 | PRIMARY   |   0 |
     89 | zlm   | checksums                 | ts_db_tbl |   0 |
     90 | zlm   | indexes                   | PRIMARY   |   0 |
     91 | zlm   | index_alternatives        | db        |   0 |
     92 | zlm   | index_alternatives        | db_2      |   0 |
     93 | zlm   | index_alternatives        | query_id  |   0 |
     94 | zlm   | index_usage               | query_id  |   0 |
     95 | zlm   | queries                   | PRIMARY   |   0 |
     96 | zlm   | tables                    | PRIMARY   |   0 |
     97 | zlm   | test_ddl                  | PRIMARY   |   0 |
     98 | zlm   | test_index_usage          | idx_key1  |   0 |
     99 | zlm   | test_index_usage          | idx_key2  |   1 |
    100 | zlm   | test_index_usage          | PRIMARY   |   0 |
    101 | zlm   | test_innodb               | PRIMARY   |   0 |
    102 | zlm   | test_myisam               | PRIMARY   |   0 |
    103  ------- --------------------------- ----------- ----- 
    104 46 rows in set (0.00 sec)
    105 
    106 (root@localhost mysql3306.sock)[index_usage]11:30:13>select * from queriesG
    107 *************************** 1. row ***************************
    108    query_id: 485931796342352545
    109 fingerprint: create table if not exists tables ( db varchar(?) not ?, tbl varchar(?) not ?, cnt bigint unsigned not ? default ?, primary key (db, tbl) )
    110      sample: CREATE TABLE IF NOT EXISTS tables (
    111     db           VARCHAR(64) NOT NULL,
    112     tbl          VARCHAR(64) NOT NULL,
    113     cnt          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    114     PRIMARY KEY  (db, tbl)
    115   )
    116 *************************** 2. row ***************************
    117    query_id: 4638883468153013157
    118 fingerprint: select * from test_index_usage where order_id>=? and name=?
    119      sample: select * from test_index_usage where order_id>=1 and name='aaron8219'
    120 *************************** 3. row ***************************
    121    query_id: 9976332422031889609
    122 fingerprint: create table if not exists indexes ( db varchar(?) not ?, tbl varchar(?) not ?, idx varchar(?) not ?, cnt bigint unsigned not ? default ?, primary key (db, tbl, idx) )
    123      sample: CREATE TABLE IF NOT EXISTS indexes (
    124     db           VARCHAR(64) NOT NULL,
    125     tbl          VARCHAR(64) NOT NULL,
    126     idx          VARCHAR(64) NOT NULL,
    127     cnt          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    128     PRIMARY KEY  (db, tbl, idx)
    129   )
    130 *************************** 4. row ***************************
    131    query_id: 11154368342625992021
    132 fingerprint: create table if not exists queries ( query_id bigint unsigned not ?, fingerprint text not ?, sample text not ?, primary key (query_id) )
    133      sample: CREATE TABLE IF NOT EXISTS queries (
    134     query_id     BIGINT UNSIGNED NOT NULL,
    135     fingerprint  TEXT NOT NULL,
    136     sample       TEXT NOT NULL,
    137     PRIMARY KEY  (query_id)
    138   )
    139 *************************** 5. row ***************************
    140    query_id: 17850505197851717537
    141 fingerprint: create table if not exists index_alternatives ( query_id bigint unsigned not ?, db varchar(?) not ?, tbl varchar(?) not ?, idx varchar(?) not ?, alt_idx varchar(?) not ?, cnt bigint unsigned not ? default ?, unique index (query_id, db, tbl, idx, alt_idx), index (db, tbl, idx), index (db, tbl, alt_idx) )
    142      sample: CREATE TABLE IF NOT EXISTS index_alternatives (
    143     query_id      BIGINT UNSIGNED NOT NULL, -- This query used
    144     db            VARCHAR(64) NOT NULL,     -- this index, but...
    145     tbl           VARCHAR(64) NOT NULL,     --
    146     idx           VARCHAR(64) NOT NULL,     --
    147     alt_idx       VARCHAR(64) NOT NULL,     -- was an alternative
    148     cnt           BIGINT UNSIGNED NOT NULL DEFAULT 1,
    149     UNIQUE INDEX  (query_id, db, tbl, idx, alt_idx),
    150     INDEX         (db, tbl, idx),
    151     INDEX         (db, tbl, alt_idx)
    152   )
    153 5 rows in set (0.00 sec)
    154 
    155 (root@localhost mysql3306.sock)[index_usage]11:30:24>select * from tables;
    156  ------- --------------------------- ----- 
    157 | db    | tbl                       | cnt |
    158  ------- --------------------------- ----- 
    159 | mysql | columns_priv              |   0 |
    160 | mysql | db                        |   0 |
    161 | mysql | engine_cost               |   0 |
    162 | mysql | event                     |   0 |
    163 | mysql | func                      |   0 |
    164 | mysql | help_category             |   0 |
    165 | mysql | help_keyword              |   0 |
    166 | mysql | help_relation             |   0 |
    167 | mysql | help_topic                |   0 |
    168 | mysql | ndb_binlog_index          |   0 |
    169 | mysql | plugin                    |   0 |
    170 | mysql | proc                      |   0 |
    171 | mysql | procs_priv                |   0 |
    172 | mysql | proxies_priv              |   0 |
    173 | mysql | servers                   |   0 |
    174 | mysql | server_cost               |   0 |
    175 | mysql | tables_priv               |   0 |
    176 | mysql | time_zone                 |   0 |
    177 | mysql | time_zone_leap_second     |   0 |
    178 | mysql | time_zone_name            |   0 |
    179 | mysql | time_zone_transition      |   0 |
    180 | mysql | time_zone_transition_type |   0 |
    181 | mysql | user                      |   0 |
    182 | sys   | sys_config                |   0 |
    183 | zlm   | checksums                 |   0 |
    184 | zlm   | indexes                   |   0 |
    185 | zlm   | index_alternatives        |   0 |
    186 | zlm   | index_usage               |   0 |
    187 | zlm   | queries                   |   0 |
    188 | zlm   | tables                    |   0 |
    189 | zlm   | test_ddl                  |   0 |
    190 | zlm   | test_ddl_no_pk            |   0 |
    191 | zlm   | test_index_usage          |   1 |
    192 | zlm   | test_innodb               |   0 |
    193 | zlm   | test_myisam               |   0 |
    194  ------- --------------------------- ----- 
    195 35 rows in set (0.00 sec)
    

    本文由68399皇家赌场发布于虚拟主机,转载请注明出处:【澳门皇家赌场55533网址】Profiling MySQL queries fr

    关键词: 68399皇家赌场 MySQL 优化