可以通过两种方式定位执行效率较低的SQL语句。
- 慢查询日志：通过慢查询日志定位那些执行效率较低的SQL语句，用—log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
- show processlist :慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist 命令查看当前MySQL在进行的线程，包括线程的状态，是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。
mysql> show processlist;+---------+-----------------+---------------------+------------------------------+-------------+----------+---------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+---------+-----------------+---------------------+------------------------------+-------------+----------+---------------------------------------------------------------+------------------+| 86 | event_scheduler | localhost | NULL | Daemon | 21486430 | Waiting on empty queue | NULL || 3433139 | system user | connecting host | NULL | Connect | 12769931 | Waiting for master to send event | NULL || 3433140 | system user | | NULL | Query | 3 | Slave has read all relay log; waiting for more updates | NULL || 3433141 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433142 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433143 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433144 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433145 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433146 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433147 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433148 | system user | | NULL | Connect | 12769931 | Waiting for an event from Coordinator | NULL || 3433231 | replicator | 11.197.7.22:54531 | NULL | Binlog Dump | 12769697 | Master has sent all binlog to slave; waiting for more updates | NULL || 7461362 | aliyun_root | 127.0.0.1:64669 | NULL | Sleep | 34 | | NULL || 7461363 | aliyun_root | 127.0.0.1:64675 | NULL | Sleep | 19 | | NULL || 7461367 | aliyun_root | 127.0.0.1:64692 | NULL | Sleep | 1 | | NULL || 7461370 | aliyun_root | 127.0.0.1:64712 | NULL | Sleep | 39 | | NULL || 8070330 | aurora | 11.197.5.98:52331 | NULL | Sleep | 3 | | NULL || 8070331 | aurora | 11.197.5.98:54071 | NULL | Sleep | 3 | | NULL || 8070332 | aurora | 11.197.5.98:40652 | NULL | Sleep | 3 | | NULL || 8070333 | aurora | 11.197.5.98:40686 | NULL | Sleep | 22862 | | NULL || 8111473 | aurora | 11.195.184.93:42546 | information_schema | Sleep | 33 | | NULL || 8196851 | tianxia | 8.136.154.180:60082 | lian_hong_university_develop | Sleep | 50 | | NULL || 8402756 | tianxia | 122.235.201.6:53684 | lian_hong_university_develop | Query | 0 | starting | show processlist || 8404290 | tianxia | 8.136.154.180:35804 | lian_hong_university | Sleep | 305 | | NULL || 8406794 | tianxia | 8.136.154.180:37548 | lian_hong_university | Sleep | 305 | | NULL || 8406801 | tianxia | 8.136.154.180:37604 | lian_hong_university | Sleep | 305 | | NULL || 8406802 | tianxia | 8.136.154.180:37620 | lian_hong_university | Sleep | 20 | | NULL || 8406870 | tianxia | 8.136.154.180:38014 | lian_hong_university | Sleep | 305 | | NULL |+---------+-----------------+---------------------+------------------------------+-------------+----------+---------------------------------------------------------------+------------------+28 rows in set (0.03 sec)
若有收获，就点个赞吧
0 人点赞
