MySQL最佳化工具之 profiling

標籤：mysql   最佳化   profiling   

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)

本文出自 “12213582” 部落格，請務必保留此出處http://12223582.blog.51cto.com/12213582/1876071

MySQL最佳化工具之 profiling