慢SQL夜袭数据库，妙手神医瑶池诊疗心法大公开

1. 入夜遇袭 惊魂时刻

“嗖——嗖嗖——”

某日19:43，一阵尖锐的箭鸣声撕破凌霄城平静的夜空，直直击中凌霄城数据舵主数小库，霎时间，用户访问页面响应时间全线飙红，线上服务濒于崩溃。同一时间，数小库体内的生命联动系统DAS触发警报（阿里云瑶池旗下的数据库自治服务DAS是基于机器学习和专家经验实现数据库自感知、自修复、自优化、自运维及自安全的云服务，在“数小库”体内提供7*24h的“生命异常监测”），妙手神医瑶池火速就位，开始诊治。

“底层MySQL数据库CPU使用率100%，活跃会话数量骤升！”

“难道又是慢SQL刺客在破坏？”瑶池一边查看体征监测仪一边暗自思索。

果然，经过进一步查看，发现会话中有很多耗时较长的SQL正在执行中，占比较大的SQL其样本如下：

同时，SHOW ENGINE INNODB STATUS显示此时SEMAPHORES一节有较多线程在等待btr0sea.c创建的RW-latch：

“果然如此，‘老朋友’，别来无恙啊。”

2. 应急处理 妙手回春

和SQL刺客团伙的斗智斗勇要追溯到很多年前，每一次他们都易容成不同的样子，瞄准各路数据宝藏，或刀箭偷袭、或棍棒直击，试图打开一个缺口，伺机破坏全城数字业务。经过多年实战历练，瑶池派已经研制出一套独有的诊疗心法，门下弟子精通各式招术，小到生命体征日常监测、大到移花接木乾坤转移，都能一一轻松应对。

“大夫，怎么样？被慢SQL刺伤而已，怎么严重到不能动了呢？”

只见神医瑶池没有多言，打开数小库内置的DAS系统，首先进行SQL限流，限制了问题SQL模板的并发度，并KILL了会话中正在执行的问题SQL，以快速降低CPU消耗恢复数小库的生命体征，让城中业务顺利开展。

“主要是因为慢SQL和AHI频繁维护同时发难。”谈话间，手起刀落，诊疗结束，神医瑶池对凌霄城一众人缓缓道明此次病因——

之前，问题SQL未曾出现在慢日志中，问题发生阶段，该模板有较多SQL出现在慢日志记录中，通过EXPLAIN可以看到，问题SQL有索引idx_example(instance_id, user_id, is_deleted_by_user, days)可用。通过SQL洞察对比发现，以前问题SQL执行时，翻页较少，即LIMIT语句的OFFSET较小；此次问题时段某些特殊实例的数据量较大，翻页较多，虽然索引是一样的，但语句中LIMIT后的OFFSET值较大执行耗时增加，该类慢SQL大量执行，导致数小库压力突增。

雪上加霜的是，在问题发生时，异常快照里SHOW ENGINE INNODB STATUS结果中SEMAPHORES一节有较多线程在等待btr0sea.c创建的RW-latch，说明数据库频繁维护自适应哈希索引（Adaptive Hash Index），锁竞争激烈，也消耗了大量CPU资源。

对了，顺便提下，我派的DAS推出了新版SQL洞察功能，可快速了解过往SQL执行情况。通过冷热数据分离存储，使用成本相比旧版可下降17%~83%。

“怪不得，原来如此～真是明‘枪’易躲，暗‘箭’难防啊！”

“可以这么理解。大家都看到了慢SQL的‘明枪’，殊不知，AHI频繁维护的‘暗箭’也同时在作祟，好在预警及时，现在都解决了。”

3. 探源溯流 防患未然

应急处理完成后，妙手神医瑶池评估出当前业务场景使用自适应哈希索引收益较小，因此设置innodb_adaptive_hash_index=OFF，关闭了数据库的自适应哈希索引。

那么该如何针对问题SQL进行优化呢？在数小库体内的DAS慢日志页面，点击该模板的「优化」后，给出的改写建议如下：

慢日志中，该问题SQL改写前执行耗时2063毫秒，改写后语句执行耗时133毫秒，耗时降低93%。

“大夫，为什么改写后的SQL语句耗时降低这么多？”

“也罢，时间还够，和大家聊聊我们瑶池派的诊疗心法吧，知其然也要知其所以然嘛～咱们就从索引结构、Server层与存储引擎层交互方式、回表、覆盖索引这四个方面，简单说说吧。”

3.1 InnoDB的索引结构

MySQL的索引是在存储引擎层实现的，InnoDB中，每一个索引都对应一棵B+树，根据叶子节点的内容，索引可以分为主键索引和非主键索引。主键索引的叶子节点存放的是整行数据，也称聚簇索引；非主键索引的叶子节点存放的是主键的值，也称二级索引。

因为叶子节点存放的数据不同，基于主键索引查询时只需要搜索主键索引对应的B+树，然后返回数据，而通过非主键索引查询时，先搜索非主键索引对应的B+树，找到叶子节点后拿到主键ID，再使用主键ID查询一次。查询了二级索引后回到主键索引树获取数据的过程被称为回表。

简单举个例子，假设有张表T如下：

表上有主键索引（PRIMARY KEY id），普通索引idx_v(v)，则主键索引树结构大致如下：

idx_v普通索引的结构如下：

如果我们要查询v=16这行记录的全部字段，则先通过idx_v索引树找到对应的二级索引记录，拿到对应的数据行id值500，然后根据id=500去主键索引树查询取得对应数据行Row4全部字段记录，即回一次表。

3.2 Server层存储引擎层的交互方式

简化来说，MySQL可以分为Server层和存储引擎层两部分。

Server层有连接器、分析器、优化器、执行器等，涵盖了MySQL大多数核心服务功能，以及所有的内置函数（如日期、时间等），所有跨存储引擎的功能也都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，Server层中的执行器通过接口与存储引擎进行通信，这些接口屏蔽了不同存储引擎之间的差异 。MySQL目前有多种存储引擎，各有各的特点，常见的主要是InnoDB和MyISAM，从MySQL 5.5.5开始InnoDB成为了默认存储引擎。

当有语句使用二级索引查询时，Server层和存储引擎层是以记录为单位进行交互的，即存储引擎每获取到一条符合条件的记录就拿着该记录中的主键值去回表，然后取到完整的整行记录后返回给Server层，而不是一次性把所有符合条件的二级索引都取出来再统一进行回表操作。

3.3 回表的性能问题

回表的性能问题要归结到索引B+树的结构。索引B+树一部分存放于内存中，一部分存放于磁盘中，回表查询叶子节点可能会造成大量的磁盘IO。B+树检索过程如下：

3.4 覆盖索引

既然回表次数过多导致查询语句耗时较长，那有没有可能经过索引优化，避免回表过程呢？

如果执行的语句是select id from T where v = 16，这时只需要查id的值，而id的值已经在idx_v索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引idx_v已经“覆盖了”我们的查询需求，即索引包含所有需要查询的字段时可称为覆盖索引。由于通过覆盖索引查询无需回表，可以避免多次的磁盘IO，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

3.5 改写前后SQL对比

3.5.1 改写前SQL执行过程

数小库病发时的问题SQL如下：

改写前问题SQL执行过程：

a. 优化器选择了使用idx_example(instance_id, user_id, is_deleted_by_user, days)索引；

b. 选择条件和排序条件都在索引中，直接排序；

c. 由于MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句是否符合要求，所以当InnoDB从idx_example中获取到第一条满足条件的二级索引记录，然后就进行回表操作得到完整的聚簇索引记录返回给Server层，Server层发现有LIMIT，因此向InnoDB要下一条记录，不断重复，直至满足LIMIT 455000, 1000，此时共回表456000次。

3.5.2 改写后SQL执行过程

瑶池优化后SQL如下：

改写后的SQL执行过程：

a. 先执行INNER JOIN后的临时表tmp_0语句

▷ 由于只需要select出id，优化器选择使用idx_example(instance_id, user_id, is_deleted_by_user, days)索引，选择条件和排序条件都在索引中，所以覆盖索引查询，无需回表；

▷ 排序；

▷ 获取排序后的第OFFSET+1行到第OFFSET+LIMIT行的id列表；

b. 根据id列表匹配INNER JOIN前后的两个SELECT语句， 获取其他需要查询的字段，回表只在这一步进行，只回表1000次。

改写前后，回表次数从456000次变为1000次，执行耗时由2063毫秒变为133毫秒。

可以执行以下两条语句验证下回表次数对耗时的影响：

耗时952.10ms

耗时23.01ms

3.6 Adaptive Hash Index

之前说过，如果数小库只是被深翻页慢SQL袭击，理论上来说，对于其体魄的影响只是资源消耗增多压力较大，但Adaptive Hash Index (AHI) 频繁维护也在同时偷袭破坏，最终导致它无法动弹。

当MySQL单表数据增大时，索引B+树的层数一般会增加至3~4层，查询时可能需要3~4次的磁盘访问，查询耗时较大。为了提升检索效率，InnoDB存储引擎会监控表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，InnoDB存储引擎会自动根据访问的频率和模式为某些热点页建立哈希索引（从某个检索条件到某个数据页的哈希表），称之为自适应哈希索引(Adaptive Hash Index，AHI)。

AHI可以降低对索引树的频繁访问的资源消耗，提升查询效率，且自适应生成，无需人工维护，但AHI需要占用buffer pool，且只适合等值查询的场景，无法提升范围、模糊查询以及ORDER BY语句的效率，只有在部分业务场景和负载情况下，AHI带来的查询效率提升才能大于维护AHI的资源开销。

当数据库系统在单次操作（如drop、truncate、delete、update等）处理大量数据记录时，很容易因为维护AHI导致阻塞，消耗大量资源。如果业务有频繁数据删除需求，建议不使用大事务进行删除，可以改造成更多小事务批次处理，如每个批次处理100~1000条等，或者评估当前业务使用AHI是否合适，不合适则将其关闭。

对于参数设置，如innodb_adaptive_hash_index，可参考DAS参数诊断推荐服务。

一番深入讲解，凌霄城众人对数小库此次症状和瑶池派有了更为深刻的认知。

言毕，妙手神医瑶池作别众人，翻身跃入数据江湖。烟波浩渺，纷争还在继续，瑶池派和各个城池的数据故事还在上演。

江湖路远，未完待续。

文章转载自公众号：阿里云瑶池数据库