前言
不管是开发同学还是 DBA,想必大家都遇到慢查询(select,update,insert,delete 语句慢)，影响业务稳定性。这里说的 慢 ，有两个含义一是 比正常的慢 ，有可能正常执行时间是 10ms，异常的是 100ms 。二是 sql 执行时间超过设置的慢查询标准 比如 500ms。
本文从 IT 架构以及数据库纬度来分析导致 sql 执行慢的原因/场景，抛砖引玉，有不足之处还请大家多多提建议。
二、基础知识
分析慢查询之前，我们先看看 sql 执行的路径,理清楚可能会影响 sql 执行速度的相关因素。
执行路径
app —[proxy]—db
app — db
目前大部分的数据库架构基本都是上面的路径，sql 从 app 的应用服务器发起经过 proxy 然后到 db，db 执行 sql 进过 proxy 或者直接返回给 app 应用服务器。分析这个过程我们可以得到几个会影响 sql 执行速度的因素
三、基础系统层面
3.1 网络层面
网络丢包，重传
其实这个比较容易理解。当 sql 从 app 端发送到数据库，执行完毕，数据库将结果返回给 app 端，这个将数据返回给 app 端的过程本质是网络包传输。因为链路的不稳定性，如果在传输过程中发送丢包会导致数据包重传，进而增加数据传输时间。从 app 端来看，就会觉得 sql 执行慢。
（图来自）
网卡满 比如大字段
这个场景可能不容易遇到，如果公司业务体量很大，比如平时每天 300w 订单的电商平台，平台大促(双十一，618)的时候极有可能出现网卡被打满。网卡带宽被占满类似各种节假日高速公路收费站(网卡)拥堵导致车流(数据包传输的速度)行动缓慢。
网络链路变长
该场景会影响应用纬度的一个事务比如交易下单整体耗时。
我们知道每个节点之间的数据传输是需要时间的，比如同城跨机房(15KM)之间的访问一般网络耗时 1.5ms 左右。
链路 1 [app1]–调用–[app2]—[proxy]—[db] 相比 链路 2[app1] – [proxy] --[db]
执行一条 sql 请求会增加 [app1]–[app2]之间的网络传输耗时大约 3ms。如果一个业务事件包含 30 个 sql ，那么链路 1 要比链路 2 多花至少 90ms 的时间成本。导致业务整体变慢。
3.2 受到影响 IO 的场景
磁盘 io 被其他任务占用
有些备份策略为了减少备份空间的使用，基于 xtrabckup 备份的时候 使用了 compress 选项将备份集压缩。当我们需要在数据库服务器上恢复一个比较大的实例，而解压缩的过程需要耗费 cpu 和占用大量 io 导致数据库实例所在的磁盘 io 使用率 100%，会影响 MySQL 从磁盘获取数据的速度，导致大量慢查询。
raid 卡 充放电，raid 卡重置
RAID 卡都有写 cache(Battery Backed Write Cache),写 cache 对 IO 性能的提升非常明显,因为掉电会丢失数据,所以必须由电池提供支持。电池会定期充放电,一般为 90 天左右,当发现电量低于某个阀值时,会将写 cache 策略从 writeback 置为 writethrough,相当于写 cache 会失效,这时如果系统有大量的 IO 操作,可能会明显感觉到 IO 响应速度变慢,cpu 队列堆积系统 load 飙高。下面是一个 raid 充放电导致 sql 慢查的案例。
raid 卡充电 将磁盘的写策略有 write back 修改为 write through ，io 性能急剧下降导致 sql 慢查，进而影响应用层的逻辑处理。
raid 卡重置 当 raid 卡遇到异常时，会进行重置，相当于程序重启，导致系统 io hang。此时也会导致 sql 慢。下图是生产中遇到的 RAID 卡重置案例。
io 调度算法
noop（电梯式调度策略）：
NOOP 实现了一个 FIFO 队列，它像电梯的工作方式一样对 I/O 请求进行组织，当有一个新的请求到来时，它将请求合并到最近的请求之后，以此来保证请求同一个介质。NOOP 倾向于饿死读而利于写，因此 NOOP 对于闪存设备，RAM 以及嵌入式是最好的选择。
deadline（介质时间调度策略）：
Deadline 确保了在一个截至时间内服务请求，这个截至时间是可调整的，而默认读期限短于写期限。这样就防止了写操作因为不能被读取而饿死的现象。Deadline 对数据库类应用是最好的选择。
anticipatory（预料 I/O 调度策略）：
本质上与 Deadline 一样，但在最后一次读操作后，要等待 6ms，才能继续进行对其他 I/O 请求进行调度。它会在每个 6ms 中插入新的 I/O 操作，而会将一些小写入流合并成一个大写入流，用写入延时换取最大的写入吞吐量。AS 适合于写入较多的环境，比如文件服务器，AS 对数据库环境表现很差。
3.3 cpu 类型
cpu 电源策略是控制 cpu 运行在哪种模式下的耗电策略的，对于数据库服务器推荐 最大性能模式 以下内容摘自 《Red Hat Enterprise Linux7 电源管理指南》：
指令集 最近遇到的一个性能案例是 hw 的机器,因为指令集合默认关闭导致性能下降 15%。
自己对 CPU 并不精通，所以这里的 2 个点并非 CPU 优化配置的全部，自建机房的运维朋友依赖官方技术支持的建议或者技术资料的指导来设置 cpu 相关参数。
四、数据库层面
4.1 没有索引，或者索引不正确
这个场景其实比较容易理解。相信每个 DBA 工作过程中都会或多或少遇到性能案例都和索引设计有关:创建表，没有索引，sql 随着数据量增大全表扫描而变慢。这个就不额外举例子了。
4.2 隐式转换
发生隐式转换时，MySQL 选择执行计划并不能利用到合适的索引而是选择全表扫描导致慢查询。常见的引发隐式转换的场景如下:
in 参数包含多个类型, 简单说,就是在 IN 的入口有一个判断, 如果 in 中的字段类型不兼容, 则认为不可使用索引. 例如 --图
判断符号左边是字符串，右边是数字 ，比如 where a=1;其中 a 是字符串
多表 join 时，where 左右两边的字段的字符集类型不一致。
推荐阅读《聊聊隐式转换》
4.3 执行计划错误
由于 MySQL 优化器本身的不足，选择执行计划时会导致错误的执行计划使 sql 走了错误的索引或者没有做索引。比如
在检查某业务数据库的 slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where 条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢？
推荐阅读《order by 主键id导致全表扫描的问题 》
4.4 数据巨大
比如 select count(*) from t1 where a=‘xxxx’; 尽管字段 a 有索引，但是如果 符合条件的记录数超高 10w ，查询速度还是会比较慢。
4.5 MetaData Lock 锁等待 ****
MDL 锁这个场景其实蛮多案例的，比如 ddl 开始时，针对同一个表的长查询还没结束，后续的写操作都会被堵住导致 thread running 飙高。实例整体的 sql 执行慢。
案例一 长查询/mysqldump 阻塞 DDL
未提交事务阻塞 ddl 阻塞查询
推荐阅读《MetaData Lock 之三》
4.6 并发更新同一行
常见的秒杀场景:数据库并发执行 update，更新同一行的动作会被其他已经持有锁的会话堵住，并且需要要进行判断会不会由于自己的加入导致死锁，这个时间复杂度 O(n)，如果有 1000 个请求，每个线程都要检测自己和其他 999 个线程是否死锁。如果其他线程都没有持有其他锁，约比较 50w 次(计算方式 999+998+…+1)。这个种锁等待和检查死锁冲突带来巨大的时间成本。对于 OLTP 业务高并发大流量访问的情况下，锁等待会直接导致 thread running 飙高，所有的请求会被阻塞并等待 innodb 引擎层处理，于是 sql 会变慢。
推荐阅读《热点更新优化方案》
4.7 数据分布不均
其实和数据分布相关，常见的比如 字段 a 是标记状态 0,1，总行数 1000w，a=0 的值大概几千条，a=1 的有 999w 多。显然执行
select count(*) from tab where a=1 ;
的查询效率肯定比查询 a=0 的要慢很多。
select count(*) from tab where a=0 ;
4.8 sql 姿势不合理
常见的分页查询 ，使用大分页深度查询。
SELECT * FROM table where kid=1342 and type=1 order id desc limit 149420 ,20;
该 SQL 是一个非常典型的排序+分页查询：order by col desc limit N,M MySQL 执行此类 SQL 时需要先扫描到 N 行，然后再去取 M 行。对于此类操作，取前面少数几行数据会很快，但是扫描的记录数越多，SQL 的性能就会越差，因为 N 越大，MySQL 需要扫描越多的数据来定位到具体的 N 行，这样耗费大量的 IO 成本和时间成本。
针对 limit 优化有很多种方式:
1 前端加缓存、搜索，减少落到库的查询操作。比如海量商品可以放到搜索里面，使用瀑布流的方式展现数据，很多电商网站采用了这种方式。
2 优化 SQL 访问数据的方式，直接快速定位到要访问的数据行。
3 使用书签方式 ，记录上次查询最新/大的 id 值，向后追溯 M 行记录。对于第二种方式 我们推荐使用"延迟关联"的方法来优化排序操作，何谓"延迟关联" ：通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。
推荐阅读《性能优化之分页查询》
4.9 表结构设计
表结构设计是否合理也是影响 sql 性能的重要因素之一。以下表格展示了字段类型不同带来的 rt 性能差异。其中字段 c1 为 int 类型的字段，字段 c2 则是表名对应的字符串长度类型 varchar(200)到 varchar(5000) ,还有 text 字段。
对于读请求，单独查询 c1 int 类型的性能并无差异。查询字段 c2 时，随着字段占用的实际字节大小增大，耗费的时间增加，也即 rt 增大。带宽逐步增大，text 的带宽 147MB 对于千兆网卡已经满了。
对于写请求，因为 binlog 为 row 模式，字段长度越大，binlog 也越大，网络传输带宽增加。整体 rt 也增加。
4.10 innodb 刷脏页
对数据库运行机制有一定了解的朋友都会知道 InnoDB 引擎采用 Write Ahead Log(WAL)策略，即事务提交时，先写日志(redo log)，再写磁盘。 为了提高 IO 效率,在写日志的时候会先写 buffer，然后集中 flush buffer pool 到磁盘。 这个过程 我们称之为 刷脏页 。官方文档中描述：
With heavy DML activity, flushing can fall behind if it is not aggressive enough, resulting in excessive memory use in the buffer pool; or, disk writes due to flushing can saturate your I/O capacity if that mechanism is too aggressive. 这个过程中就有可能导致平时执行很快的 SQL 突然变慢。
推荐阅读:
4.11 undo 没有被 purge/回收
UNDO 日志是 MVCC 的重要组成部分，当一条数据被修改时，UNDO 日志里面保存了记录的历史版本。当事务需要查询记录的历史版本时，可以通过 UNDO 日志构建特定版本的数据。
每条行记录上面都有一个指针 DATA_ROLL_PTR，指向最近的 UNDO 记录。同时每条 UNDO 记录包含一个指向前一个 UNDO 记录的指针，这样就构成了一条记录的所有 UNDO 历史的链表。当 UNDO 的记录还存在，那么对应的记录的历史版本就能被构建出来。
当记录对应的版本通过 DATA_TRX_ID 比对发现不可见时，通过系统列 DATAROLLPTR，找到对应的回滚段记录，继续通过上述判断记录可见的规则，进行判断，如果记录依旧不可见，继续通过回滚段查找之前的版本，直到找到对应可见的版本。
所以当有长事务/异常未提交的情况就会因为其他查询需要构建快照导致 undo 不能被及时回收。查询遍历的 undo 越多 sql 执行的越慢。
推荐阅读《一次大量删除导致 MySQL 慢查的分析》
五、小结
这里总结了我工作经历中遇到的一部分可能会影响 SQL 执行效率的场景或者案例，经历有限，难免有遗漏的案例/场景，抛砖引玉，欢迎各位有兴趣的读者朋友留言说说你们遇到的场景。
本文转载自公众号有赞 coder（ID：youzan_coder）。
原文链接：
中国开发者画像洞察研究报告 2024
分析开发者的行为模式、工作价值、职业发展等内容，帮助整个行业生态更深入地理解开发者，为他们提供更精准...
评论