MySQL慢查询及实践

当数据量相对较大了的时候或者其他原因会导致MySQL的查询变得很慢，影响用户体验。

本文介绍怎么优化MySQL、慢查询及实际操作

一、找出执行时间长的sql

1、使用 show processlist 查看是否有长时间未执行完的堵塞的sql

mysql> show processlist;
+-------+------+-----------------+---------+---------+------+----------+------------------+
| Id    | User | Host            | db      | Command | Time | State    | Info             |
+-------+------+-----------------+---------+---------+------+----------+------------------+
| 25141 | root | localhost:57770 | testabc | Sleep   |    3 |          | NULL             |
| 25142 | root | localhost:57772 | testabc | Sleep   |    3 |          | NULL             |
| 25143 | root | localhost:57774 | testabc | Sleep   |    3 |          | NULL             |
| 25144 | root | localhost:57802 | testabc | Sleep   |    3 |          | NULL             |
| 25145 | root | localhost:57808 | testabc | Sleep   |    2 |          | NULL             |
| 25146 | root | localhost:57828 | testabc | Sleep   |    1 |          | NULL             |
| 25147 | root | localhost:57898 | testabc | Sleep   |    1 |          | NULL             |
| 25148 | root | localhost:57912 | testabc | Sleep   |   11 |          | NULL             |
| 25149 | root | localhost:57914 | testabc | Sleep   |   11 |          | NULL             |
| 25150 | root | localhost:57916 | testabc | Sleep   |    9 |          | NULL             |
| 25152 | root | localhost:57922 | testabc | Sleep   |    9 |          | NULL             |
| 25153 | root | localhost:57928 | testabc | Sleep   |    9 |          | NULL             |
| 25154 | root | localhost:57936 | testabc | Sleep   |   21 |          | NULL             |
| 25155 | root | localhost:57938 | testabc | Sleep   |    9 |          | NULL             |
| 25156 | root | localhost:57940 | testabc | Sleep   |    9 |          | NULL             |
| 25157 | root | localhost:57942 | testabc | Sleep   |    7 |          | NULL             |
| 25158 | root | localhost:57954 | testabc | Sleep   |    7 |          | NULL             |
| 25159 | root | localhost:57956 | testabc | Sleep   |    5 |          | NULL             |
| 25160 | root | localhost:57984 | testabc | Sleep   |    5 |          | NULL             |
| 25161 | root | localhost:57990 | testabc | Sleep   |    4 |          | NULL             |
| 25162 | root | localhost       | testabc | Query   |    0 | starting | show processlist |
+-------+------+-----------------+---------+---------+------+----------+------------------+
21 rows in set (0.03 sec)

如果存在Time列的数值比较大，Info列就是对应的执行的sql。如果这样的数据较多，则需要使用kill命令杀死这些sql线程，让MySQL及时恢复正常，kill命令为： kill Id  如 kill 25141

show process显示的Info列只显示了部分的sql信息，如果要看完整的Info列，可以使用show full processlist命令，这时Info列就能显示完整的正在执行的sql，将完整的sql复制出来后就可以进行sql分析和优化。

2、慢查询

如果当前进程没有堵塞的sql可以跟踪，那么可以开启慢查询，当系统执行时间超过阈值时就会将执行慢的sql保存到慢查询日志文件中，然后就可以针对性地分析这些sql

mysql> show variables like '%query%';
+------------------------------+---------------------------------------------------------+
| Variable_name                | Value                                                   |
+------------------------------+---------------------------------------------------------+
| binlog_rows_query_log_events | OFF                                                     |
| ft_query_expansion_limit     | 20                                                      |
| have_query_cache             | YES                                                     |
| long_query_time              | 10.000000                                               |
| query_alloc_block_size       | 8192                                                    |
| query_cache_limit            | 1048576                                                 |
| query_cache_min_res_unit     | 4096                                                    |
| query_cache_size             | 16777216                                                |
| query_cache_type             | OFF                                                     |
| query_cache_wlock_invalidate | OFF                                                     |
| query_prealloc_size          | 8192                                                    |
| slow_query_log               | OFF                                                     |
| slow_query_log_file          | /data/mysql_data/mysql/iZuv26i9c1i539uu6ke1kyZ-slow.log |
+------------------------------+---------------------------------------------------------+
13 rows in set (0.00 sec)

显示slow_query_log的值为OFF，说明慢查询为关闭状态，下面开启慢查询日志

mysql> set global slow_query_log = on;
Query OK, 0 rows affected (0.26 sec)

mysql> set long_query_time = 2;
Query OK, 0 rows affected (0.01 sec)

第一条命令为开启慢查询，第二条为设置慢查询的时间，执行时间超过这个值的sql都会被保存到slow_query_log_file配置的文件中。

通过上面的方法就能找到需要优化的sql语句

二、优化sql

通过explain命令分析sql的性能，可能会使用什么索引。

show index from user; 查看表user已经创建的索引

创建索引：

CREATE INDEX idx_own ON user(deleted, company_id, nick_name); 

创建合适的索引能够极大地优化sql语句的执行速度。创建索引后继续使用explain命令对sql进行分析，或者直接执行sql看是否执行得很快了

三、优化java程序

当慢查询日志和show processlist命令中都没有发现执行特别慢的sql，那么就需要对java程序就行追踪定位执行时间长的业务逻辑在哪里，比如通过输出程序时间，帮助定位问题所在，再落实到需要优化的sql

比如发现java程序中的某一点循环执行完发现需要执行十几秒或者几十秒，但是sql线程并没有发现执行慢的sql，可能是因为每条sql执行的时间大概是0.5秒，如果java代码循环了20次，那么整个的业务逻辑执行完就要十几秒。

此时可以优化单条sql执行的时间，创建索引让执行时间从0.5秒优化到0.005秒，java业务执行20次后总共的执行时间变为0.1秒，就算优化成功了

也可以将java的20次调用优化为执行1次sql，将总的sql执行时间控制在1秒以内，也能达到优化的效果

本文内容到此结束，更多内容可关注公众号