慢SQL的治理思路

时间:2024-06-03 12:19:40

慢SQL的治理思路

  • 什么是慢SQL
  • 慢SQL产生的原因
  • 查看慢 SQL 是否开启
  • 开启慢 SQL 记录开启慢查询日志
  • 分析慢 SQL
  • 解决和优化慢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语句的执行计划,找出可能的性能瓶颈,并针对性地进行优化。