慢SQL解决方案

1. 背景

慢SQL是稳定性中重要的一部分，一般指查询时间超过1s或扫描行数百万的SQL操作，可以通过show variables like 'long_query_time'查看。对于交易这类对稳定性要求很高的业务来说治理慢SQL格外重要。这里总结下不同业务存在的慢SQL问题，以及一些通用的解决方案

2. 危害

1. 响应时效：结果反馈时间长，容易发生超时，用户体验差
2. 资源消耗：慢SQL往往是扫描很多行但是真正使用的只有其中的小部分，这造成了大量无用的资源消耗，这里资源可以是：CPU资源、IO资源、内存空间、网络带宽、锁，一般问题集中在前两个

1. 服务不可用：当大量慢SQL并发的出现，可能把当前数据库的连接池打满或者把数据库服务器CPU打满，导致其他依赖这个库/表的服务不可用。

案例：

平时扫描16000行执行时间1s左右的慢sql，作为任务在一个时间点执行，日常并发400，但是由于一次的大流量并发进入，导致表的连接池被打满，其他依赖这个表的服务不可用时间长达半个多小时。表面看如果数据库有400个连接，16000的1s慢sql需要执行1分多钟而已，但是由于连接打满，数据库性能下降严重，平时只有几十个连接，1s的语句现在可能执行翻了几十倍。因此慢sql的并发突增有很大的安全隐患，可能导致一些冷门业务影响了线上核心业务

3. 解决方案

大部分的慢SQL通过加索引、分库分表的方式解决就可以了，这里介绍一些其他的优化方式。以下的测试效果根据没有缓存的第一次查询算平均（SQL_NO_CACHE没法完全排除数据缓存优化，现在数据库都是默认不使用查询缓存）

3.1. 分页优化

很多接口都会提供分页查询的能力，但当对于一些遍历结果的定时任务来说可能会扫描到最后一页（深度分页）。这意味着当筛选出的结果很多的时候，可能出现SELECT ... OFFSET 49990 LIMIT 10类似这样的SQL语句，它会扫描5w行记录然后抛弃前面49990行返回最后10行，5w行就算全部命中索引，有时候耗时都会高达2s

问题原因：之所以会出现耗时长，主要原因是抛弃前面49990行是数据库server层做的，而数据获取是在存储引擎层，两者是分开的无法感知；其次数据获取会先在辅助索引里按顺序拿到记录的主键id，再去主键索引中找到完整记录数据，但是这个操作不是顺序的是随机的，也就是说进行了5w次的随机IO。。

3.1.1. 分页边改边查

这种优化主要是针对任务类的分页查询。一些定时任务往往会遍历表里所有符合条件的记录，然后做一些操作，当过滤条件比较少的时候会筛选出大量的记录，随着任务推进产生深度分页，导致了慢SQL

SELECT ... WHERE ... ORDER BY id DESC LIMIT 50000,100

针对任务类可以：

• 在处理一批记录的时候改变记录的一些状态信息或者给记录打标，筛选条件中剔除已经被处理的记录，这样保证每次都只处理未被处理的前几个记录，不会产生慢SQL问题。

• 或者可以每次任务记录最后一个处理过的记录的id，然后下次查询条件加上做范围查询，这种查询都会走主键索引，如果WHERE条件很多且有范围查询也会存在效率问题，不过相对深度分页效率还是高很多

-- 方法1:2021-01-01执行定时任务时，对每条查询到的记录modify_tag修改为2021-01-02
SELECT ... WHERE modify_tag = '2021-01-01' LIMIT 100
 
-- 方法2:每次记录上次的id，在下次的查询时带入，由于要id排序没有索引全命中可能会负优化
SELECT ... WHERE a="a" and b="b" and id > 1111 ORDER BY id LIMIT 100

实践

以分页查询的定时任务为例，命中索引(type,status)，这类索引的选择性不高，且要比对where因此扫描行数很多，当深度分页后会扫描大量行记录

SELECt *
  FROM xxxx
 WHERE type= 'ABC'
   AND status= 'VALID'
 LIMIT 10000,
       100

优化后如下，每次记录上一次扫描结束的记录主键id作为下一次扫描的参数，这里还会产生索引合并（index merge），主键索引和二级索引的结果做交集，降低扫描的范围，当分页到很后面的时候优化效果明显。测试下有缓存的情况下优化90%左右（100:1000），尤其深度到后面

SELECT *
  FROM xxxx
 WHERE id> 7506888
   AND debt_type= 'ABC'
   AND status= 'VALID'
 ORDER BY id
 LIMIT 100

注：第二个语句从执行计划上可以看到extra只有using where而没有using filesort，因为当辅助索引包含了主键，当索引值相等其主键是有序的，在查询时MySQL做了优化

Can MySQL use primary key values from a secondary index?

3.1.2. 延迟关联子查询优化

一个典型二级索引查询，例如：

SELECT * FROM xxx 
WHERE code='ABC' 
LIMIT 30000,500

大致分三步：

1. 首先在二级索引(idx_svc)筛选出符合条件的记录的主键id
2. 如果没有索引覆盖则带着主键id到一级索引里查完整行记录

1. 完整行记录返回给server层，根据limit对行记录进行过滤

这里可以发现由于要查询select很多列所以会把一级索引里所有的行记录取出来再使用limit筛选，这一步显的很重，因为我们会对很多不需要的行做读取IO，比如LIMIT 1000,50 那无用的行记录IO就是950。子查询的思路就是把limit的操作提前到二级索引筛选的时候，在到一级索引中搜索结果输出，相当于把查询拆成两步把limit筛选提前。这个过程中大致分为：

1. 首先在二级索引(idx_xxx)筛选出符合条件的主键id
2. 在id的结果中根据limit选出符合要求的主键id

1. 通过主键做表连接，返回符合要求的完整行记录

主要形式有：

SELECT * FROM xxx
WHERE id >= (SELECT id FROM xxx LIMIT 3000, 1)
LIMIT 500

以上这种试用于没有where筛选条件的查询，一旦有条件子查询就不是连续的了，如果只是那种单纯罗列的列表就可以用,对于包含where条件的可以通过连接来做：

SELECT *
FROM xxx p1 join(
	SELECT id FROM xxx WHERE code= 'ABC' 
  LIMIT 30000, 500) as p2 
ON p1.`id`= p2.`id`;

相较原始的语句测试下来大致能提升30%左右（90:130），当分页增加时提升更多。但当where条件中的属性没有全走索引的话，这种优化就没有优势，因为子查询最终也会回表查询，但是由于过滤中不需要全部的行记录因此效率也会有小的提升10%左右（1900:2200）。

3.1.3. 分页展示优化（应用层面）

Pagination Optimization - MariaDB Knowledge Base

这里给出了一个应用层面分页展示优化方案，跟边改边查类似。主要思想是不使用offset，而采用索引范围查询取代。主要实现方式如下：

1. 如果每页有10个记录就先检索11个记录（可以判断是否是最后一页），并记下最后一个记录的id作为下一页的传参
2. 点击下一页的时候把记下的id传入SQL中，检索小于这个id的11个记录，即是下一页的内容。这样每次下一页都是依赖前一页的id坐标，且每次只用检索10个记录，大大提升了效率

First page (latest 10 items):
    SELECT ... WHERE ... ORDER BY id DESC LIMIT 10
Next page (second 10):
    SELECT ... WHERE ... AND id < $left_off ORDER BY id DESC LIMIT 10

由于基于索引做范围查询，可以很快定位到数据并扫描少量的记录，这个速度是很快的。同时这种方式有一些缺点：

• 没法获取记录的总数，如11-20 Out of 12345，但是可以维护一个大致的值
• 从后向前查询没法获取页数

• 没法跳到指定的页数

这种分页方式跟传统分页不同，在UI上也有所改变，可以很好的避免慢SQL但是缺少了准确性，且这种涉及前端的改造成本比较高，因此在实际改造中没有应用

3.2. count优化

很多接口调用需要获取符合条件的记录总量，这就需要用到count(*)（count(*)和count(1)效率相同），计数

当遇到索引使用不到位、筛选条件不合理、大买/卖家数据过多会产生大量行扫描。

count的优化方式不多，一般要么取大概值要么离线算好专门维护一个数量值，要么分表，直接优化的方式最好的就是索引覆盖，避免回表查询后在server层进行筛选然后计数。

3.2.1. exist替代全表count(*)

有的业务需要根据条件判断表中是否有符合条件的记录，这种不需要count计算全表记录数，只需要最快命中符合条件的记录即可，exist有短路机制，因此效率更高。对于这种业务需求可以直接将sql语句改为如下的形式：

-- 1是有，0是无
SELECT count(*) FROM(
SELECT 1 FROM table1 WHERE ... LIMIT 1);
 
-- 1是有，0是无
SELECT EXISTS(
SELECT * FROM table1 WHERE ...);

这种短路机制对于计数量高于3w或者where条件没有完全命中索引的情况提升比较多。尤其是未全命中索引的语句count会回表并到server层一个个比对计数，测试下来大概能提升90%（70:700）左右

3.2.2. 减法替代扫描&额外计数存储

sql - optimize mysql count query - Stack Overflow

这里提了两个方法针对对某个时间区间实时统计总数，其实平时也不怎么会用上，基本统计类的最好都走离线数据，这里只是提一下

1. 获取最早时间的最小自增id和最晚时间最大的自增id，做减法即可算出期间的记录总量而不用扫描（如果有其他筛选条件就不成立了）

SELECT min(incr_id) min_id FROM record_updates WHERE date_updated BETWEEN '2009-10-11 15:33:22' AND '2009-10-12 23:59:59';
SELECT max(incr_id) max_id FROM record_updates WHERE date_updated > DATE_SUB(NOW(), INTERVAL 2 DAY);

1. 创建另一个表或者直接用一个tair计数器，汇总每天的记录数，获取更精准的数量可以通过sql查询今天的总数再加上昨天的计数

3.2.3. 如果结果小于分页大小则无需回查数据库

如果分页查询的页大小为10，但是返回的结果数量只有5，说明总数就是5不需要再回数据库count总数

/**
 * 快速计算当前查询条件下的count
 * return 如果未知返回-1
 * resultSize 本次分页查询结果页的长度
 * start 本次分页查询的偏移起始位置
 * limit 本次分页查询的页长
 */
public static int fastCalculateCount(int resultSize, int start, int limit){
    // 如果分页查询返回结果为0且查询的是第一页
    if(resultSize == 0 && start == 0 && limit > 0){
        return 0;
    }
    // 如果分页查询返回的数量小于当前分页大小，那么总count其实就等于 start + resultSize
    if(resultSize > 0 && resultSize < limit){
        return start + resultSize;
    }
    // 需要回源count
    return -1;
}

3.3. 索引优化

通过explain查看数据访问类型type，SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是const最好。一般来说所有类型效率从高到低：（常见几种的是const > ref > range > index > all）

注：以上效率结论是相对的，如果索引的选择性很低那么ref有时候性能也会很差

3.3.1. 低效索引

在慢SQL治理中可以适当修改或删除低效的索引，减少不必要的资源开销。

• 选择性不高的索引。其性能可能没有提升多少跟全表扫描一样，还会浪费/损耗资源，比如性别之类的。可以删除或根据日常查询需要增加其他列组成联合索引以增加其选择性

索引选择性：不重复的索引值（也称为基数，cardinality）和数据表的记录总数的比值，比值越高，代表索引的选择性越好，唯一索引的选择性是最好的，比值是 1。适用前缀索引、联合索引顺序等的选择

• 体量过大的索引。这里体量大指占用空间大或者无用空间大的索引，例如把文本类的列作为索引（非全文索引），一页数据页按16K算，更大的占用空间代表一个页存储的记录量很少，从而造成更多的IO；再例如对于一个区域表，里面存储着用户的身份证，在给身份证建索引的时候会发现所有记录前几位基本都是一样的（省市区），这意味着这前几位基本没有区分度可言，没有节约时间反而浪费空间。对于这些情况可以考虑使用前缀索引CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));（身份证之类的可以反转再取前缀），去除无用信息减少索引体量
• 使用率不高的索引。使用率的问题只能结合业务来看，是不是能命中或者场景覆盖的全。具体哪里有数据能统计到这个目前不知道，PERFORMANCE_SCHEMA.table_io_waits_summary_by_index_usage没法查到，AliSQL和xdb也都没记录，好像是因为集团数据库版本原因没有记录

3.3.2. 唯一索引插入性能

【强制】业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验和控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

开发手册鉴于数据的正确性建议业务唯一的字段建立唯一索引，还能很小程度（可忽略）提升查询效率，但是唯一索引会使change buffer作用无效而降低插入性能（由于唯一性约束，需要从磁盘/内存中获取索引页以判断是否重复，产生随机IO，而非唯一索引可以接受内存无相应数据页直接存入change buffer），对于频繁插入场景的数据库需要在业务一致性和性能间作出权衡。

3.3.3. 三星索引

索引在建立的时候可以参考三星索引的原则：

• 一星索引，根据where后面等值的条件，或者范围的条件来构建索引。一星索引的核心就是利用索引来尽可能的过滤不必要的数据，减少数据处理的规模。选择性高的列放前面
• 二星索引，利用索引的有序性，消除ordby或者group by等需要排序的操作。避免排序消耗CPU资源，以及因为sort buffer不够而内外存导致换进换出的情况

• 三星索引，索引的叶子节点就能够读到查询SQL所需要的所有信息，而不需要回原表去查询了。这样能大概率的避免，至少是减少物理IO

往往现实中复杂的查询情况难以做到三星索引，这就可以根据筛选能力决定选择哪几个星了

3.4. 限流降级

如果语句并发量不大且无法继续优化，限流降级是一种兜底的方案，以免数据量或并发量突增导致数据库资源耗尽。

3.4.1. 数据库内核限流

数据库升级后具备内核级别的慢SQL隔离能力，会对慢请求进行限流处理而不影响正常的请求，通过SHOW VARIABLES like 'slow_query_blocker_enabled';查看数据库是否具有慢SQL隔离的能力。

其具体流程如下：

1. sql语句到达server层，数据库为请求分配worker线程
2. worker线程解析了语句，在执行前去慢查询模式列表匹配模式，如果有命中且达到并发度，则执行限流

1. 语句执行完后计算请求耗时，如果是慢查询则插入到慢查询模式列表；同时有线程周期性检查线程池，把慢查询模式插入到列表中

3.4.2. 应用接口限流

对于有些存在慢SQL风险的接口，可以通过Sentinel进行接口级别慢调用的限流降级，防止突然的大并发查询导致数据库打满。

限流：保护服务提供方不被过于频繁的调用压垮（防止QPS或占用线程过高）。调用方有时候存在类似OLAP拼接参数的慢查询（如内部小二后台查询系统），这种一般用于工单排查调用量较少且可以接受失败，可以针对这些调用应用进行限流处理：拒绝频繁调用

降级：保护服务调用方不被不可用的服务拖死（RT过高或异常过多）。当有耗时很高的慢SQL进入，虽然qps不高但是对资源占用也很严重，这种需要通过识别RT占用较高的服务进行降级处理：不可用的服务快速失败。

3.5. 在线转离线

3.5.1. 任务在线转离线

对于一些定时任务，如果对时间实时性要求不高的/分析类的/订正数据类的，建议都走OLAP数据库，如通过ODPS拉取离线数据进行数据分析/订正，以减少线上OLTP数据库的压力。

3.5.2. 数据在线转离线

一些历史的数据可能查询频率不高，可以定期将线上数据库的历史数据转移到一些离线的数据库产品，比如ODPS、Hbase。

3.6. 分库分表

如果加了索引且达到了最优效果，由于数据量过大还是会存在性能问题，这时候可以考虑分表、分库分表。水平拆分一般以买家id作为分表键数据散列相对均匀，比如订单表、保单库，再通过数据库中间件进行路由。数据库的水平拆分让容量不再成为瓶颈，但同时在部分性能方面作出了牺牲（分布式事务， 跨库关联等），交易一般数据库现状是：

MySQL在实际的业务场景，特别是核心交易场景下，我们都是通过弱化关系模型， 通过事务拆分，来尽量规避分布式的事务， 也基本放弃跨库join，也不建议使用view，存储过程， 子查询等等

3.6.1. 大买家拆分

有时候虽然根据买家拆分，但是由于一些特殊场景，导致大买家热点，比如交易大买家问题，可以达到一个买家日均几十万单。针对这类问题可以对热点的买家做进一步的hash散列，把数据分散到各个库，在查询的时候再进行聚合。难点在于需要对全链路进行改造

3.6.2. hint并发优化

如果数据库有做分库分表，查询没有禁止全表查：<property name="allowFullTableScan" value="false"/>，当条件中没有分表键的情况下会全表查询。这时候数据库中间件有可能会在分析阶段将查询拼接成一个大的SQL包含多个表的union，有可能导致慢查询。

对于这种扫描类的查询，涉及多个表可以通过Hint对语句进行定制，可以在Hint中指定分表键，它会影响数据库中间件路由规则而不影响SQL语句本身。

hint除了可以指定分库分表的表还可以指定查询主库还是备库、强制使用索引等，可以根据需要进行策略优化

3.7. 其他存储

如果存储没有对事务有很大的依赖或者有文本检索需求又或者存在没法解决的热点用户，可以考虑将MySQL替换为ElasticsSearch一类的存储以提升查询性能和多样性。

以ElasticsSearch为例

优势：高扩展不需要分库分表天然支持分布式、数据量大压缩能力好、数据结构灵活

劣势：不支持事务、没有外键之类的数据约束，成本高

为数据关系约束考虑，存储最好还是以MySQL等关系型数据库为主要存储，ElasticsSearch、Redis等作为辅助存储，将MySQL数据同步到辅助存储以满足高效查询、文本检索等能力

---

参考

• 子查询优化在MySQL中如何使用覆盖索引优化limit分页查询 - 掘金
• MySQL调优底层算法：（ICP等）Interview worry free: source code + practice, talking about the implementation of the underlying algorithm of MySQL tuning

• explain解释：MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

• 唯一索引对插入影响：MySQL 关于唯一索引和普通索引的抉择 - 来份锅包肉 - 博客园
• 索引设计：MySQL 索引设计概要 - 面向信仰编程