慢SQL的治理思路

什么是慢SQL

慢 SQL 指的是 MySQL 中执行比较慢的 SQL，排查慢 SQL 最常用的方法是通过慢查询日志来查找慢 SQL。 MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，就会被记录到慢查询日志中，long_query_time 的默认值为 10s，意思是运行超过 10s 以上的语句就会被当做慢 SQL 记录到日志中。

慢SQL产生的原因

• 缺乏索引或索引未生效：当查询未命中索引或索引未生效时，数据库可能需要进行全表扫描，这会消耗大量的I/O资源，从而导致查询速度变慢。
• 单表数据量太大：当表中的数据量非常大时，即使使用了索引，查询速度也可能受到影响，因为索引的维护成本会随着数据量的增加而增加。
• SQL语句书写不当：例如使用了过多的JOIN或子查询、IN元素过多、LIMIT深分页问题、ORDER BY导致文件排序、GROUP BY使用临时表等，这些都会导致查询效率降低。
• 数据库在刷“脏页”：当数据库在刷“脏页”（即将内存中的修改数据页写回磁盘）时，如果redo log写满了，会导致所有系统更新被堵住，无法写入，从而影响查询性能。
• 锁等待：在执行SQL时，如果遇到表锁或行锁，查询需要等待锁被释放，这也会导致查询速度变慢。

查看慢 SQL 是否开启

可以使用 SQL 命令来查看慢 SQL 记录功能是否开启，使用

 mysql> show variables like '%slow_query_log%'; 

来查询慢查询日志是否开启，执行效果如下图所示：

slow_query_log 的值为 OFF 时，表示未开启慢查询日志。

开启慢 SQL 记录开启慢查询日志

可以使用如下 MySQL 命令：

mysql> set global slow_query_log=1

不过这种设置方式，只对当前数据库生效，MySQL 重启也会失效
如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf ：

slow_query_log =1slow_query_log_file=/tmp/mysql_slow.log

分析慢 SQL

得到慢 SQL 之后，可以通过 explain 执行计划分析 MySQL 执行慢的原因并进行优化，比如以下这样：

其中最重要的就是 type 字段，type 值类型如下：

• all — 扫描全表数据
• index — 遍历索引
• range — 索引范围查找
• ndex_subquery — 在子查询中使用
• refunique_subquery — 在子查询中使用
• eq_refref_or_null — 对 null 进行索引的优化的
• reffulltext — 使用全文索引ref — 使用非唯一索引查找数据
• eq_ref — 在 join 查询中使用主键或唯一索引关联
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

如果 type=all 说明没走索引，此时就需要给查询慢的字段加上相应索引就可以提高查询效率。 当然，优化慢 SQL需要综合考虑的因素有很多，比如索引、查询优化（减少联表查询等）、减少锁竞争等因素，所以具体的慢 SQL优化，需要根据实际的业务场景再做优化决策。

解决和优化慢SQL的方法

• 优化数据访问：
  使用LIMIT子句缩减数据行数。
  避免使用SELECT *，只选择需要的列。
  分解大查询为多个小查询，减少每次查询的数据量。
• 索引优化：
  为查询的字段建立合适的索引，避免全表扫描。
  分析查询语句，确定需要加索引的字段，并选择适当的索引类型。
  使用覆盖索引，当索引中的列包含所有查询中需要使用的列时，可以避免回表操作，提高查询性能。
  避免索引失效，例如避免对索引列进行函数操作或未遵循最左匹配原则等。
• 查询语句优化：
  分解联表查询，将复杂的联表查询分解为多个单表查询，然后在业务层聚合数据。
• 优化排序操作，对排序字段建立索引，避免文件排序。
  分析和重写复杂的子查询和JOIN操作，提高查询效率。
• 数据库参数调优：
  根据数据库的配置和硬件环境，调整数据库的参数，如缓冲区大小、连接数等，以优化性能。
• 分表分库：
  对于非常大的数据表，考虑进行分表或分库操作，将数据分散到多个表或数据库中，以提高查询性能。
• 增加缓存：
  对于频繁读取的热点数据，可以将其放入缓存中（如Redis），减少对数据库的访问压力。
• 使用数据库管理工具：
  利用数据库管理工具分析SQL语句的执行计划，找出可能的性能瓶颈，并针对性地进行优化。