如何排查慢SQL?

回答

如果是在项目中，可以通过SpringAOP去查询这个接口运行的时间；

如果是一个sql，可以通过explain的指令去查这个sql的执行计划。

如果有数据库终端的话，也可以通过开启mysql的慢日志查询，设置好时间阈值，进行捕获。

补充

来源评论区补充：补充链接

在企业内部对于慢查SQL的优化主要经历以下的几个步骤：

202310251903040

在慢SQL的优化过程中，可以从以下五个角度去进行思考优化：SQL优化、资源占用、业务改造、数据减少、源头替换。

在治理慢查的过程中，SQL语句的使用问题是导致慢SQL的主要因素，因此本文主要从SQL优化角度出发，对慢SQL的常见原因和特征进行分析，介绍慢SQL的优化过程以及一些有效的调优技巧和工具，希望能够提供一些有用的方法和策略，帮助大家更好地应对慢SQL问题，并最终实现提升系统性能和优化用户体验的目标。

SQL优化

SQL语句的优化方式主要是通过选择合适的索引、优化查询语句、避免全表扫描等提高查询效率，减少慢SQL的出现

索引

索引主要用于加快数据的查询速度，有了正确的索引，数据库就可以根据索引的数据结构快速定位到匹配的数据行，从而提高查询效率和响应速度。在慢SQL中由于索引导致的主要有两个方面：索引缺失 与 索引失效

索引缺失
1
select * from t1 where text3 = 'text898'

text3列没有索引的情况下：

202310251904540

text3列增加了索引：202310251905902

建议

一般在以下场景中需要为相应的列创建索引：

  • 字段有唯一性限制
  • 经常用于where查询条件的字段
  • 经常用于group by 和 order by的字段，可以避免排序

但是并不是需要为每个字段都添加上索引，有一些场景下添加上索引反而会加重DB的负担：

  • 不用于查询条件的字段
  • 字段中存在大量重复的数据
  • 数据量太少
  • 频繁更新的字段（考虑）

注意：随着数据的增长和变化，索引的有效性可能会下降。定期评估和优化现有索引是十分必要的。可以通过删除不再使用的索引、调整索引的顺序和选择适当的索引类型等方式来进行索引维护和优化

索引失效

索引失效会导致SQL的执行变为全表扫描或选择错误的索引，在explain中一般是type=ALL或type=index

索引失效原因：

  • 索引字段发送隐式转换
    • 数字转换为字符串会发生隐式转换失效
    • 字符串转换为数字是自动转换，不会导致索引失效
    • 通过在explain语句后增加extendedexplain extended sql语句，再执行show warnings查看是否存在隐式转换以及哪个字段存在隐式转换
  • 使用 非/不等于（!=、not in）查询时可能会导致索引失效
    • 在满足索引覆盖的情况下可能会走索引
  • 在查询条件中对索引使用函数或表达式计算
    • 比如from_unixtime(create_time) = ’2019-12-01’就不能使用到索引：需要先做一次全表扫描，将字段上的所有值使用表达式作用后再进行匹配，从而会导致Mysql放弃走索引。所以语句应该写成create_time = unix_timestamp(’2019-12-01’);
  • 没有遵循最左前缀匹配原则，比如联合索引中没有使用到第一列索引、使用左右模糊匹配
  • 在where子句中，or一些条件列是索引列，一些不是，会导致索引失效，直接全表扫描
  • 索引的可选择性差（数据发布严重倾斜或区分度不高）
    • MYSQL查询优化器可能认为返回的数据量本身就很多，通过索引扫描并不能减少多少开销，此时选择全表扫描的权重会提高很多
    • 一般认为区分度 > 0.1的查询字段可以建立索引（经验性指标，一般要进行实际的测试，比如使用前缀索引时，不断尝试直到选择到合适的前缀长度以及合适的区分度）
  • IS NOT NULL 或 IS NULL条件查询也可能导致索引失效
    • 当索引字段不可以为空（null）时
      • is null 不会使用索引，因为条件失效无法查询
      • 只有使用is not null 返回的结果集中只包含索引字段时，才使用索引，因为实现索引覆盖，优化器认为此时成本较小
    • 当索引字段可以为空（null）时
      • 使用 is null 会使用索引，因为NULL值在SQL中被认为是列中最小的值，存储在最左边，所以可以通过索引快速定位
      • 使用 is not null 返回的结果集中只包含索引字段时，才会使用索引，因为实现索引覆盖

总结来说，要让避免索引失效的原则为：遵循最左前缀原则、避免使用函数和表达式、避免隐式转换、尽量实现索引覆盖

建议

  • 修改SQL语句
  • force index强制使用索引
  • ignore index忽略特定索引

SQL语句优化

分页写法（深翻页）

最常见的分页写法就是使用limit，在分页查询时，会在 LIMIT 后面传两个参数，一个是偏移量(offset)，一个是获取的条数(limit)。

实现方式是先查询offset+limit条数据，再将offset条数据丢弃给用户返回剩下的limit条数据。比如limit 10000,10实际上是mysql查找到前10010条数据，之后丢弃前面的10000行后再返回

这样子当偏移量很小时，查询速度很快，但是随着 offset 变大时，查询速度会越来越慢，因为查找的数据越来越多

在limit0,10的情况下执行速度很快，基本可以忽略不计

202310251905273

但是当limit n，n是值越来越大时，就导致查询时间增加了

202310251908548

建议

  • 方式一：
1
select * from t1 where id >= 300000 order by id limit 10

202310251909738

避免了扫描前offset条记录

但是每次查询都需要拿到上一页的最大/小id。比如当前在第3页，需要查询第5页的数据就没办法了

  • 方式二

结合普通limit与方式一，解决方式二的问题，但是offset要尽量小

1
select * from t1 where id > 300000 order by id limit 10, 10

202310251907531

  • 方式三：
1
select * from t1 as a inner join (select id from t1 order by id limit 300000, 10) as b on a.id = b.id order by a.id

由于内部的子查询只扫描了id字段，而不是全表，所以性能会比较强

202310251907545

这种情况下还是扫描聚簇索引树，可能难以理解并且优化效果不是很明显。在order by换成其他字段达到索引覆盖的情况下会比较容易理解

  • 方式四：
1
select * from t1 where id > (select id from t1 order by id limit 300000, 1) limit 10

同样是通过子查询扫描字段id，但是性能会略好于方式三，因为它不需要进行表的关联，而是一个简单的比较，在不知道上一页最大id的情况下，是比较推荐的用法

202310251908991

最大最小写法

MySQL提供了max()和min()用于获取最大最小值，但是优化得不是很好

text1没有索引，因此会全表扫描获取最小的id

202310251910645

建议：

由于id是主键我们可以知道第一次找到的记录对应的id就是我们需要的结果，所以可以根据结果的有序性修改SQL语句

1
select id from t1 where text1 = 'dd' limit 1

202310251911283

最大值同理

order by排序问题

MySQL进行排序是一个成本比较高的操作：

  • 全字段排序会在sort_buffer中建立临时表进行排序
  • 基于rowid排序不仅需要建立临时表，还会涉及回表操作

在需要排序时会在explain的Extra字段中出现Using filesort

1
select * from t1 where v1 < 100 order by v1

202310251912862

建议：

对于索引是本来就是有序的，所以可以给order by字段加上索引

  • 如果order by后面的字段是单个索引，需要order by 条件要与where中条件一致，否则order by不会利用索引进行排序
  • 如果order by 最后的字段是组合索引的一部分，需要把放在索引组合顺序的最后

202310251912164

group by临时表问题

  • 内存占用：group by语句由于可能会建立内部临时表，用于保存和统计中间结果。首先会使用内存临时表，但是内存临时表的大小是有限制的，由参数 tmp_table_size 控制，当超过此限制时会把内存临时表转成磁盘临时表。因此内部临时表的存在会影响内存和磁盘的空间，且需要构造的是一个带唯一索引的表，执行代价都是比较高的。因此需要尽量避免内部临时表的建立
  • 额外排序：group by column默认会根据column排序，因此还会触发排序开销问题

202310251912542

建议：

  • 让 group by 字段用上表的索引，确认方法是 explain 的Extra结果里有没有 Using temporary 和 Using filesort；通过索引建立，只需要顺序扫描到数据结束，就可以拿到 group by 的结果，不需要临时表，也不需要再额外排序

202310251913287

  • 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null
  • 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；可以通过适当调大tmp_table_size 参数，来避免用到磁盘临时表
  • 如果数据量实在太大，使用 SQL_BIG_RESULT 这个hint，来告诉优化器直接使用排序算法得到 group by 的结果

join

当关联被驱动表上使用到索引时，会使用 Index Nested-Loop Join （NLJ）算法

当关联被驱动表上没有使用到索引时（即t2的字段a无索引），会使用 Block Nested-Loop Join（BNL）算法

建议：

NLJ算法优化：小表驱动大表，在join的时候如果明确知道哪张表是小表时可以使用straight_join写法固定连接驱动方式

BNL算法优化：

  • 给被驱动表的join字段加上索引，把BNL算法转成NLJ算法
  • 无法设置索引的情况可以通过设置join_buffer_size参数来控制Join Buffer的大小，以减少分段查询次数

Hash Join算法优化：增加 join_buffer_size值避免生成文件

in & exists

in执行流程：查询子查询的表且内外表有关联时，先执行内层表的子查询，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选，得到结果集。所以相对内表比较小的时候，in的速度较快

exists执行流程：指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的，匹配上就将结果放入结果集中

建议：

遵循小表驱动大表：exists是以外层表为驱动表、IN是先执行内层表的**子查询**。如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in；反之如果外层的主查询记录较少，子查询中的表大且又有索引时使用exists

not in & not exists

not in使用的是全表扫描没有用到索引；而not exists在子查询依然能用到表上的索引

建议：

使用not exists代替not in

查询记录是否存在

在很多时候开发人员判断某一条件对应的记录是否存在时会采用select count(*)，但是这样子会导致扫描所有符合条件的数据

建议：

改用limit 1，这样子数据库查询到一条符合条件的记录就会返回，不需要再继续查找还有多少条记录

资源占用

  • 锁资源等待：在读写很热的表上，通常会发生锁资源争夺，从而导致慢查询的情况
    • 谨慎使用for update
    • 增删改尽量使用到索引
    • 降低并发，避免对同一条数据进行反复修改
  • 网络波动：往客户端发送数据时发生网络波动导致的慢查询
  • 硬件配置：CPU利用率高，磁盘IO经常满载，导致慢查询

在高并发、高流量下，数据库所在机器的负载load过高也会导致SQL整体执行时间过长，这时可能需要从机器和实例的分配，分布式部署，分库分表，读写分离等角度进行优化

业务改造

  • 是不是真的需要全部查出来，还是取其中的top N就能够满足需求了
  • 查询条件过多的情况下，能否前端页面提示限制过多的查询条件的使用
  • 针对实时导出的数据，涉及到实时查DB导出大量数据时，限制导出数据量 or 走T+1的离线导出是不是也是可以的
  • 现在业务上需要做数据搜索，使用了 LIKE “%关键词%” 做全模糊查询，从而导致了慢SQL。是不是可以让业务方妥协下，最右模糊匹配，这样就可以利用上索引了

源头替换

Mysql并不是任何的查询场景都是适合的，如需要支持全模糊搜索时，全模糊的like是无法走到索引的。同时结合数据本身的生命周期，对于热点数据，可以考虑存储到缓存解决。因此针对不适合mysql数据源的情况，我们需要替代新的存储介质

  • 有like的全模糊的查询，比如基于文本内容去查订单信息，需要接搜索引擎解决
  • 有热点数据的查询，考虑是否要接缓存解决
  • 针对复杂条件的海量数据查询，可以考虑切换到OLAP(Online Analytical Processing)，可以考虑接Hybrid DB或ADB通道
  • 有些场景Mysql不适用，需要用K-V的数据库，HBASE等列式存储的存储引擎

数据减少

SQL本身的性能已经到达极限了，但是耗时仍然很长，可能由于数据量或索引数据都比较大了。因此需要从数据量级减少的角度去处理

  • 使用分库分表。由于单表的数据量过大，例如达到千万级别的数据了，需要使用分库分表技术拆分后减轻单库单表的单点压力
  • 定时清理终态数据。针对已经状态为终态的业务单据或明显信息，可以使用idb历史数据清理的方式配置定时自动清理。如针对我们的仓储库存操作明细为完结状态的数据，我们只保留最近1天的数据在db中，其他直接删除，减少db查询压力
  • 统计类查询可以单独维护汇总数据表。参考数据仓库中的数据分层设计，基于明细数据，抽出一张指标汇总表，或7天/15天等的视图数据进行预计算。此类汇总表数据量级相比明细表下降很多，从而避免直接根据大量明细查询聚合造成慢sql

实践举例

  • SQL语句分析
  • 分析sql时间点发现固定db某个示例会导致RT尖峰抖动，发现磁盘也有相应问题。怀疑DB某些库磁盘问题导致，联系DBA确认后进行主备切换解决
  • 核销慢sql查询迟迟难以解决。发现库存核销记录每天增量数据达到百万级别，但是核销创建状态记录只有20%~30%左右，因此对完结状态的核销记录idb配置定时清理，由15天缩短到2天，减少db数据量
  • 库存sn查询涉及复杂查询，采用切换到OLAP链路，通过数据同步中间件完成从db到HybridDB一键同步，切换数据源后问题解决

参考文章

MySQL中IS NULL、IS NOT NULL、!=是否走索引