慢SQL治理实践小结

此前整个公司生态开展安全生产的应用治理活动，以故障为镜，可以守安全，这其中的慢sql治理是系统高可用治理的重要一环。慢sql的产生可以从宏观和微观两个角度去看：SQL产生的微观方面的原因包括：DB表数据量大，索引不合理，SQL语句调优等等。此外在高并发、高流量下，数据库所在机器的负载load过高也会导致SQL整体执行时间过长，这时可能需要从机器和实例的分配，分布式部署，分库分表，读写分离等宏观角度进行优化。结合仓储技术部安全生产治理过程实践和相关资料整理，本文主要从微观角度对慢sql的发现、分析、解决三个step，逐渐阐明治理慢sql的系统化思路。

本文阅读时间10~15min左右。

发现慢sql

首先需要知道如何发现慢sql，简要介绍如下几种方法：

• **集团生态统一提供idb数据库管理平台提供mysql监控，进入idb应用系统。选择需要治理的数据库，点击【性能】菜单进入cloudDBA界面查看慢sql。idb中默认执行时间超过1s的SQL为慢SQL。

• 针对**集团，可以访问菜鸟自己的慢sql解决方案泰山。同时风险平台会关联慢sql建立风险单指派人跟进。在治理过程中，笔者即直接查看风险平台追踪的慢sql风险进行

• 当然一般性地还可以MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值（默认值为10）的SQL，则会被记录到慢查询日志中。

本文对慢sql的发现不做详细赘述，非本文重点，主要下面介绍如何分析和解决慢sql。

分析慢sql

在发现找到慢sql后，就是要分析这条慢sql的来龙去脉了，可以分别从sql语句结构、使用场景、执行计划三个方面逐一剖析。

分析sql语句结构

分析的最开始阶段很直观地即是对sql语句本身表象结构的分析，对sql进行结构拆解分析。需要理清如下三点：

• sql的结构特点。如使用的简单单一查询？ join关联查询？还是子查询？等等

• sql语句关键字可能带来的典型问题。如like模糊语句、order by/group by、join使用的驱动表大小、limit高起点的深翻页问题、not in带来的全表扫描等问题等等。（此处提到的典型问题会在下面小节具体展开）

• 相关表建立的索引情况。

一般一个SQL的主要结构包含在如下图所示的结构。可以是其中的某种单一结构，也可以是这些结构的混合形式。

分析sql使用场景

进一步地，定位sql的运行使用场景，即站在sql语句语法本身之外的角度分析sql的使用方式上，包括但不限如下几点：

• 使用的业务场景：

  • 需要支持模糊关键词搜索

  • 需要多条件的复杂的在线实时查询

  • 定时任务（如补数据/删除数据）

  • 页面查询or系统调用

• 运行的环境

  • 产生慢sql的应用机器/DB实例：预发or线上机器产生的，之前遇到过预发环境工具导致的慢sql问题；是否是同一个DB实例产生慢sql，可能实例磁盘问题或数据倾斜等问题。

  • sql运行的周期/频率/时间点：根据周期运行规律可以判断是否为定时任务产生，定时任务有时会捞取大量数据扫全表导致慢sql；其次针对某一时间点的某个特定DB实例造成的慢sql，可以通过DBPaas分析，发现是由于夜间的磁盘抖动造成慢sql，联系DBA确认确实磁盘有问题。

分析sql执行计划

此阶段需要透过现象看本质，需要分析sql的执行细节信息。Mysql提供Explain命令直观反映sql的执行计划，即sql是如何执行的。而SQL 性能优化的目标：type至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。

1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。
3） range 对索引进行范围检索。
反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。

Explain语句执行后的各个输出的字段如下表所示：

Explain输出结果字段

下面对其中我们日常关心的字段进行一些简要介绍，更全面具体的可以参考官方文档，以及文档MySQL explain 执行计划详解

type

表示关联类型（join type）或访问类型（access type），是一个非常重要的字段，是我们判断一个SQL执行效率的主要依据，执行效率依次从最优-->最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

• ref

不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

ref是我们日常开发中较为常见的情况，也是原则上期望要达到的级别，查询命中到索引。

# 根据索引（非主键，非唯一索引），匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;
# 多表关联查询，单个索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;
# 多表关联查询，联合索引，多行匹配
SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

• range

索引范围扫描。常见于当使用<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN等操作符，用常量比较关键字列时

# 常量比较，可能多行
SELECT * FROM tbl_name
  WHERE key_column > 10 and key_column < 20;
# 范围查找
SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;
# 范围查找
SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);
# 多条件加范围查找
SELECT * FROM tbl_name
  WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
 

• index

索引全扫描。index类型和ALL类型类似，区别就是index类型是扫描的索引树，即MYSQL遍历整个索引树，通过读取索引（如果非覆盖索引场景，需要再回表查询）来扫描全表行。以下两种情况会触发：

1. 如果索引是查询的覆盖索引，即索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。在这种情况下，explain 的 Extra 列的结果是 Using index。索引扫描通常比ALL快，因为索引的大小通常小于表数据。

# 即只select索引字段
SELECT key_column FROM tbl_name

2. 按照索引扫描全表的数据是有序的，即全表扫描会按索引的顺序来查找数据行；使用索引不会出现在Extra列中。会避免排序，但也会扫描整表数据

# key_column为索引字段
select * from tbl_name order by key_column 

• ALL

全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。 

possible_keys

这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有值，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句考虑建立合适的索引

key

这一列显示MySQL真正使用的索引是什么。也有可能key的值不存在于 possible_keys中，这种情况可能是possible_keys中没有特别合适的索引，MySQL选择了其他的索引进行查询。

rows

该列表明MySQL估计要读取并检查的行数，注意不是结果集里的行数。

filtered

表明返回结果的行占需要读到的行(rows列的值)的百分比。当我们执行一个查询语句时，MySQL首先会根据索引去扫描出一批数据行，然后再在这些数据行中，根据查询条件进行过滤，实际返回的行数 / 扫描出的结果行的百分比，即为filter的值。

Extra

该列表明了一些额外的信息来说明MySQL如何解析查询的。对于判断一个SQL的执行性能，也是非常重要的判断依据。对其中我们可能常遇到的进行下介绍：

• Using filesort：说明mysql会对数据需要进行排序，而不是按照表内的索引顺序进行读取。表明SQL可能需要进行一定的优化。

• Using index：这个值重点强调了只需要使用索引就可以满足查询表的要求，不需要回表查询了，一般表示使用了覆盖索引。此类sql性能较好。

• Using temporary：这个值表示使用了内部临时表。这种情况通常发生在查询时包含了group by、union等子句时。往往需要优化sql。

• Using where：where条件查询，通常using where表示优化器需要通过索引回表查询数据。

• Using join buffer (Block Nested Loop)：使用join buffer(BNL算法)进行关联执行。往往需要优化sql

• Using MRR(Multi-Range Read ) ：使用辅助索引进行多范围读。

解决慢sql

可以从四个维度或角度对慢sql进行解决，相辅相成，合力击破慢sql，如下图所示：

• SQL优化：从sql本身出发。仅仅对SQL本身进行优化，包括索引优化、SQL语句改写等。

• 业务改造：从业务使用角度触发。在业务场景层面进行改造和“妥协”，避免产生慢SQL。比如：改为分页查询、限制查询条件、实时性的妥协等等。

• 源头替换：从数据自身特性和使用角度出发。如数据生命周期的冷热程度、复杂查询or模糊查询等特性替换为不同的数据源。如缓存、搜索引擎、OLAP数据库等。

• 数据减少：从数据库容量和性能角度出发。如分库分表，定时历史数据清理等。

SQL优化

此处的SQL优化时广泛的SQL概念，即指SQL语句本身优化，以及SQL相关的索引优化问题，具体如下展开。

索引优化

索引缺失

• 最基本但是在业务中占比很高的case，即sql未命中索引。

• 优化建议：增加索引

• 特别地，对应order by 和group by场景：

  • 对于order by a语句，尽量要在列a上建立索引 或是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，利用索引有序性，可以避免排序和临时表建立（具体见下方order by语句问题分析）

  • 对于group by a语句，尽量要在列a上建立索引，利用索引有序性，可以避免排序

索引字段不合理

• 通常是联合索引的字段设置不合理，explain之后看上去有索引命中，但是并非是最合理最优化的索引设计。

• 优化建议：索引中添加需要的字段，建立合理联合索引。

索引字段顺序

• 建立组合索引时，区分度最高的在最左边；对于多列的组合索引，如分别是warehouse_id, user_id, item_id, inventory_status。考虑到最左前缀匹配规则，有了这个组合索引，就相当于有了单列索引(warehouse_id)，组合索引(warehouse_id, user_id)，组合索引(warehouse_id, user_id, item_id)。所以在索引建立的时候，把查询时候常用的（区分度高的）字段要放到索引排序的左边。

• 存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。对于范围查询，MySQL索引会一直向右匹配直到遇到(> < between like)就停止，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立(a,b,c,d)顺序的索引，d是用不到索引的。因此如果字段在多数语句中都以范围查询的形式出现，可以考虑把索引的字段做调整，将其后置，增加索引被命中率。

• =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

索引失效

• 索引可选择性(区分度)差

  • 查询的条件区分度高不高。区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，所以尽量选择区分度高的列作为索引；唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0（实际为0.00003，考虑精度可认为=0）。一般对于区分度大于0.1的查询字段都要建立索引。

  • 如果字段的可选择性非常差，使用索引比全表扫描还慢。因为要先跑一遍索引，然后根据没有消除几个记录的索引再回表跑差不多大半个的全表，结果还不如直接跑全表。

  • 对于查询来讲，最好的索引就是唯一性，一次即可定位，对于重复数据很多的列不适合建立索引，因为过滤后数据量仍然会很大，先走索引在走表，所以很慢。

• 避免类型隐式转换。索引字段的数据类型和查询的数据类型一定要匹配上。

  • 如查看该字段是int类型，但是查询条件值是字符串： sql SELECT * FROM t WHERE c = 'aa'，会导致SQL不走索引，而导致全表扫描。

  • 通过在explain语句后增加extended即explain extended 'sql语句'，再执行show warnings查看是否存在隐式转换以及哪个字段存在隐式转换。

• 使用"非/不等于"( <>，!=，not in )查询时会导致索引失效（但是满足覆盖索引Covering Index使用条件的sql，"!="和"not in"也可以走索引）。尽可能使用等值查询，即全值匹配查询

  • 对于"不等于"准确说是不一定会使用索引：一般情况"不等于"操作会选择表中绝大部分数据，使用二级索引的成本不亚于甚至超过全表扫描的成本，查询优化器按照成本选择"最优执行计划"，导致查询不走二级索引。但是满足覆盖索引Covering Index使用条件的SQL，"!="和"not in"也可以走索引。具体可参见：mysql普通索引不等于为什么会失效？

• LIKE 语句不允许使用 % 开头，否则索引会失效；即未遵循最左前缀原则导致

• IS NOT NULL 或 IS NULL条件查询也可能导致索引失效。

  • 当索引字段不可以为空（null）时，is null 不会使用索引；只有使用is not null 返回的结果集中只包含索引字段时，才使用索引（即覆盖索引）

  • 当索引字段可以为空（null）时，使用 is null 会使用索引（不影响覆盖索引）；但使用 is not null 返回的结果集中只包含索引字段时，才会使用索引（即覆盖索引，同上）

• 索引列不能参与计算、函数，保持列“干净”。比如from_unixtime(create_time) = ’2019-12-01’就不能使用到索引：需要先做一次全表扫描，将字段上的所有值使用表达式作用后再进行匹配，从而会导致Mysql放弃走索引。所以语句应该写成create_time = unix_timestamp(’2019-12-01’);

SQL语句优化

慎用select *

• 可能不会命中索引！！mysql自身优化时除了考虑利用索引提升查询速度，还会考虑数据io消耗等多方面的因素，最终选取一种最合适的方案，即会综合查询效率和io效率的比拼：

  • 使用索引查询时查询效率高，io效率低

  • 使用全表扫描时查询效率低，io效率高

• 字段按需查询，尽可能使用覆盖索引，即查询字段为对应的索引列，则可以直接从索引取出值，而不用回表再次查询。

limit导致的高起点深翻页

• 随着limit offsett, n语句中offset的增大，性能越来越差。主要原因为如limit 10000,10的语法实际上是mysql查找到前10010条数据，之后丢弃前面的10000行后再返回。同时由于此类问题出现在分页场景下，翻页到很深度的页数时会暴露出来，因此也常称为“深翻页”问题。即MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。

• 优化建议：

  • 使用id界限判断优化。where id>offset limit n 来代替使用 limit offset, n；
    

     -- 同样的效果
     select * from notes limit 1000000,3;
     select * from notes where id>1000000 limit 3;

  • 用id的覆盖索引优化。可以先利用覆盖索引查出ID字段，然后根据id再获取数据（即先快速定位需要获取的 id 段，然后再关联）；缺点是需要id必须是单调有序的 （推荐）

    select * from 
    (select ID from job limit 1000000,100) as a 
    join 
    job as b 
    on a.ID = b.id; 

force index强制索引

• 强制走某些索引：Mysql优化器并不总是能做出最好的索引选择。有些情况下使用另外的索引有更好的性能，但是并没有没优化器所采用。则可以使用force index强制要求走某个索引，当然，必须保证这个索引以后不能被删除，不然就是个BUG。

in和exists的使用

• in和exists

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

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

  • 优化建议：in和exists主要是造成了驱动顺序的改变，exists是以外层表为驱动表、IN是先执行内层表的子查询。因此如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in（主要要仔细评估 in 后边的集合元素数量，控制在 1000 个之内，也是为了避免in大结果集后导致JVM内存产生fgc）；反之如果外层的主查询记录较少，子查询中的表大且又有索引时使用exists。

• not in和not exists

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

  • 优化建议：用not exists都比not in要快。

join语句

• sql示例：select * from t1 straight_join t2 on (t1.a=t2.a), 其中驱动表为t1，被驱动表t2。

• 当关联被驱动表上使用到索引时（即t2的字段a有索引），会使用 Index Nested-Loop Join （NLJ）算法，没有问题。

• 当关联被驱动表上没有使用到索引时（即t2的字段a无索引），会使用 Block Nested-Loop Join（BNL）算法。会把表 t1 的数据读入内存 join_buffer 中；扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。整个过程扫描行数就会过多，尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。确认方法为 explain的Extra结果有没有Using join buffer (Block Nested Loop)

• 总是应该使用小表做驱动表：在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。 

order by排序问题

• MySQL 做排序是一个成本比较高的操作：全字段排序在sort_buffer中会建立临时表进行排序、另一种基于rowid排序不仅需要建立临时表还需要涉及回表查询等操作。然而并不是所有的 order by 语句，都需要排序操作，需要排序是因为原来的数据都是无序的。判断是否需要排序通过explain 的Extra结果里有没有Using filesort。

• 优化建议：order by 字段上建立索引，从而天然支持排序。如果order by 最后的字段是组合索引的一部分，需要把放在索引组合顺序的最后

order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。 
正例： where a =?  and b =?  order by c; 索引： a _ b _ c
反例：索引中有范围查找，那么索引自身的有序性无法利用，如： WHERE a >10  ORDER BY b; 索引 a_b 无法排序。

group by临时表问题

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

• 额外排序：group by column默认会根据column排序，因此还会触发排序开销问题。

• 优化建议：

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

  • 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

  • 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；可以通过适当调大tmp_table_size 参数，来避免用到磁盘临时表；

  • 如果数据量实在太大，使用 SQL_BIG_RESULT 这个hint，来告诉优化器直接使用排序算法得到 group by 的结果。

业务改造

有时技术的复杂度或难点可能随着业务的玩法的调整就可以迎刃而解。从业务服务使用的角度出发，能否进行一些trade off或是变通，包括但不限于以下几种方式：

• 是不是真的需要全部查出来，还是取其中的top N就能够满足需求了

• 查询条件过多的情况下，能否前端页面提示限制过多的查询条件的使用。

• 针对实时导出的数据，涉及到实时查DB导出大量数据时，限制导出数据量 or 走T+1的离线导出是不是也是可以的。

• 现在业务上需要做数据搜索，使用了 LIKE "%关键词%" 做全模糊查询，从而导致了慢SQL。是不是可以让业务方妥协下，做右模糊匹配，这样就可以利用上索引了。

源头替换

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

• 有like的全模糊的查询，比如基于文本内容去查订单信息，需要接搜索引擎openSearch的解决。

• 有热点数据的查询，考虑是否要接Tair等缓存解决。

• 针对复杂条件的海量数据查询，可以考虑切换到OLAP(Online Analytical Processing)，可以考虑接Hybrid DB或ADB通道。

• 有些场景Mysql不适用，需要用K-V的数据库，HBASE等列式存储的存储引擎。

数据减少

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

• 使用分库分表。由于单表的数据量过大，例如达到千万级别的数据了，需要使用分库分表技术拆分后减轻单库单表的单点压力。

• 定时清理终态数据。针对已经状态为终态的业务单据或明显信息，可以使用idb历史数据清理的方式配置定时自动清理。如针对我们的仓储库存操作明细为完结状态的数据，我们只保留最近1天的数据在db中，其他直接删除，减少db查询压力。

• 统计类查询可以单独维护汇总数据表。参考数据仓库中的数据分层设计，基于明细数据，抽出一张指标汇总表，或7天/15天等的视图数据进行预计算。此类汇总表数据量级相比明细表下降很多，从而避免直接根据大量明细查询聚合造成慢sql。

治理实践举例

基于上面的思路，可以对一个个sql逐个击破，下面就简单列举几个在治理过程中的实践示例。

• sql预计索引分析。前端页面跳转到库存操作明细页面时触发页面查询，但owner_id没待到后端，导致未走到合理的索引

• 分析sql时间点发现固定db某个示例会导致RT尖峰抖动，发现磁盘也有相应问题。怀疑DB某些库磁盘问题导致，联系DBA确认后进行主备切换解决

• 核销慢sql查询迟迟难以解决。发现库存核销记录每天增量数据达到百万级别，但是核销创建状态记录只有20%~30%左右，因此对完结状态的核销记录idb配置定时清理，由15天缩短到2天，减少db数据量。

• 库存sn查询涉及复杂查询，采用切换到OLAP链路，通过数据同步中间件完成从db到HybridDB一键同步，切换数据源后问题解决。

结语

作为开发人员，需要在平时将sql的常见问题和“坑点”内化到日常开发中；内化于心，敬畏线上，让慢sql“清零”成为常态化，而不要等到每年大促前又要费时费人力的集中治理或等线上问题暴露出来时可能为时已晚。希望本文能多少带给大家一些系统性地思路和思考。水平有限，如有不对之处，欢迎指正讨论~~

参考资料

• MySQL explain 执行计划详解

• Mysql explain 官方文档解释

• mysql普通索引不等于为什么会失效？
• MySQL系列(十八)：优化 之 索引失效分析
• 阿里巴巴Java开发手册泰山版