MySQL慢查询及实践

谁践踏了优雅 2022-03-07 16:06 283阅读 0赞

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

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

一、找出执行时间长的sql

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

如果存在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程序