mysql优化之SHOW PROFILE 定位SQL语句

mysql优化之SHOW PROFILE sql语句定位

1,show profile

show profile 就是mysql提供的可以用来分析当前会话中语句执行的资源损耗情况。
             可以用来sql的调优和测量

值得注意的是默认情况下为关闭状态，如果开启，保存当前会话最近的15次运行结果

//查询状态以及是否支持
show variables like 'profiling';
//开启
set profiling = on
//开启状态下可以在当前会话中执行sql，系统默认会保存最近15条
show profiles
例如：

show profiles;

+----------+------------+--------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                              |
+----------+------------+--------------------------------------------------------------------+
|        5 | 0.00007650 | show dh_tuoling                                                    |
|        6 | 0.00012625 | SELECT DATABASE()                                                  |
|        7 | 0.00055400 | show variables like 'profi%'                                       |
|        8 | 0.00041125 | show tables                                                        |
|        9 | 0.00030425 | select * from ns_blog_link                                         |
|       10 | 0.00040150 | select * from ns_bloginfo                                          |
|       11 | 0.00034575 | select * from ns_bloginfo                                          |
|       12 | 0.00036375 | select * from ns_bloginfo                                          |
|       13 | 0.00040225 | select * from ns_bloginfo                                          |
|       14 | 0.00012475 | SELECT DATABASE()                                                  |
|       15 | 0.12940925 | select * from t_product group by id%10 limit 5000                  |
|       16 | 6.76644050 | select * from t_product group by product_id%10 limit 5000          |
|       17 | 4.72033675 | select * from t_product group by product_id%40 limit 5000          |
|       18 | 0.48150575 | select product_id from t_product group by product_id%40 limit 5000 |
|       19 | 0.12338200 | select * from t_product t_product limit 10000                      |
+----------+------------+--------------------------------------------------------------------+

//定位问题sql可以使用语句，则可以具体查询某条sql的整体运行情况
show profile cpu,block io for query Query_ID;
定位语句常见参数
show profile 'params' for query Query_ID;
    params:
        ALL                 显示所有开销
        BLOCK IO            显示块io开销
        CONTEXT SWITCHES    上下文切换相关开销
        CPU                 显示CPU相关信息开销
        IPC                 显示发送和接收相关开销信息
        MEMORY              显示内存相关开销信息
        PAGE FAULTS         显示页面错误相关开销信息
        SOURCE              显示source_function,source_file,source_line相关开销信息
        SWAPS               显示交换次数相关开销信息


注意的结论：
    converting HEAP to MyISAM 查询结果太大，内存不够用了往磁盘上搬
    creating tmp table        创建了临时表
    copying to tmp table on disk    内存中临时表复制到磁盘，比较危险，开销大
    locked                    存在表锁
例如：
    show profile cpu,block io for query 16;

+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000070 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000069 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000012 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000026 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000015 | 0.000000 |   0.000000 |            0 |             0 |
| Creating tmp table   | 0.000079 | 0.000000 |   0.000000 |            0 |             0 |
| Sorting result       | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000002 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 6.764985 | 4.445324 |   0.000000 |       186208 |            16 |
| Creating sort index  | 0.000176 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| removing tmp table   | 0.000121 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000009 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000010 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000023 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000778 | 0.001000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+