MySQL优化工具之profiling



使用慢查询日志分析出慢查询语句后，用profiling分析该语句的优化后执行效果。


查看慢查询设置


mysql> show variables like "%slow%";

+---------------------+---------------------------------+

| Variable_name       | Value                           |

+---------------------+---------------------------------+

| log_slow_queries    | ON                              |

| slow_launch_time    | 2                               |

| slow_query_log      | ON                              |

| slow_query_log_file | /var/lib/mysql/slow-queries.log |

+---------------------+---------------------------------+

4 rows in set (0.00 sec)


更改慢查询日志的文件位置：

mkdir /data/slow

chown -R mysql:mysql /data/slow


mysql> set global slow_query_log_file = "/data/slow/slow.log";



mysql> show variables like "%long_query_time%";

+-----------------+-----------+

| Variable_name   | Value     |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)


设置慢查询记录，单位为秒

mysql> set long_query_time = 1;

Query OK, 0 rows affected (0.00 sec)


mysql> show variables like "%long_query_time%";

+-----------------+----------+

| Variable_name   | Value    |

+-----------------+----------+

| long_query_time | 1.000000 |

+-----------------+----------+

1 row in set (0.00 sec)



mysql> show variables like "%pro%";

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| have_profiling            | YES   |

| profiling                 | OFF   |

| profiling_history_size    | 15    |

| protocol_version          | 10    |

| proxy_user                |       |

| slave_compressed_protocol | OFF   |

| stored_program_cache      | 256   |

+---------------------------+-------+

7 rows in set (0.00 sec)


开启profiling


mysql> set profiling = 1;

Query OK, 0 rows affected (0.00 sec)


mysql> show variables like "%pro%";

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| have_profiling            | YES   |

| profiling                 | ON    |

| profiling_history_size    | 15    |

| protocol_version          | 10    |

| proxy_user                |       |

| slave_compressed_protocol | OFF   |

| stored_program_cache      | 256   |

+---------------------------+-------+

7 rows in set (0.00 sec)


查看sql语句执行情况

mysql> show profiles;

+----------+------------+--------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                              |

+----------+------------+--------------------------------------------------------------------+

|       10 | 0.00034150 | show variables like "%slow%"                                       |

|       11 | 0.00009450 | set global slow_query_log_file = /data/slow.log                    |

|       12 | 0.00008825 | set global slow_query_log_file = "/data/slow.log"                  |

|       13 | 0.00016525 | set global slow_query_log_file = "/data/slow/slow.log"             |

|       14 | 0.00004975 | set global slow_query_log_file = /data/slow/slow.log               |

|       15 | 0.00031400 | show variables like "%slow%"                                       |

|       16 | 0.00005325 | set global slow_query_log_file = /var/lib/mysql/slow-queries.log   |

|       17 | 0.00009750 | set global slow_query_log_file = "/var/lib/mysql/slow-queries.log" |

|       18 | 0.00029575 | show variables like "%slow%"                                       |

|       19 | 0.00028575 | show variables like "%pro%"                                        |

|       20 | 0.00032200 | show variables like "%pro%"                                        |

|       21 | 0.00028725 | show variables like "%pro%"                                        |

|       22 | 0.00003675 | show profile for qurey 21                                          |

|       23 | 0.00004700 | show profile for qurey 1                                           |

|       24 | 0.00003450 | reset query cache                                                  |

+----------+------------+--------------------------------------------------------------------+

15 rows in set (0.00 sec)


查看单个sql语句的详细信息

mysql> show profile for query 15;

+--------------------+----------+

| Status             | Duration |

+--------------------+----------+

| starting           | 0.000049 |

| Opening tables     | 0.000029 |

| System lock        | 0.000006 |

| init               | 0.000006 |

| optimizing         | 0.000003 |

| statistics         | 0.000006 |

| preparing          | 0.000006 |

| executing          | 0.000174 |

| Sending data       | 0.000012 |

| end                | 0.000003 |

| query end          | 0.000002 |

| closing tables     | 0.000002 |

| removing tmp table | 0.000004 |

| closing tables     | 0.000002 |

| freeing items      | 0.000008 |

| logging slow query | 0.000002 |

| cleaning up        | 0.000002 |

+--------------------+----------+

17 rows in set (0.00 sec)



清除sql缓存

mysql> reset query cache;

Query OK, 0 rows affected (0.00 sec)