前言
不管是开发同学还是 DBA,想必大家都遇到慢查询(select,update,insert,delete 语句慢)，影响业务稳定性。这里说的 慢 ，有两个含义一是 比正常的慢 ，有可能正常执行时间是 10ms，异常的是 100ms 。二是 sql 执行时间超过设置的慢查询标准 比如 500ms。
本文从 IT 架构以及数据库纬度来分析导致 sql 执行慢的原因/场景，抛砖引玉，有不足之处还请大家多多提建议。
二、基础知识
分析慢查询之前，我们先看看 sql 执行的路径,理清楚可能会影响 sql 执行速度的相关因素。
执行路径
app —[proxy]—db
目前大部分的数据库架构基本都是上面的路径，sql 从 app 的应用服务器发起经过 proxy 然后到 db，db 执行 sql 进过 proxy 或者直接返回给 app 应用服务器。分析这个过程我们可以得到几个会影响 sql 执行速度的因素
1 网络，各个节点之间的网络2 OS系统 ，即数据库服务器3 MySQL数据库本身三、基础系统层面
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 慢查的案例。
root@rac1#megacli -FwTermLog dsply -aALL11/08/143:36:58: prCallback: PR completed for pd=0a11/08/143:36:58: PR cycle complete11/08/143:36:58: EVT#14842-11/03/12 3:36:58: 35=Patrol Read complete11/08/143:36:58: Next PR scheduled to start at 11/10/123:01:5911/08/140:48:04: EVT#14843-11/04/12 0:48:04: 44=Time established as 11/04/12 0:48:04; (25714971 seconds since power on)11/08/1415:30:13: EVT#14844-11/05/12 15:30:13: 195=BBU disabled; changing WB virtual disks to WT ---问题的原因11/08/1415:30:13: Changein current cache property detected for LD : 0!11/08/1415:30:13: EVT#14845-11/05/12 15:30:13: 54=Policy change on VD 00/0 to [ID=00,dcp=0d,ccp=0c,ap=0,dc=0,dbgi=0,S=0|0] from [ID=00,dcp=0d,ccp=0d,ap=0,dc=0,dbgi=0,S=0|0]raid 卡充电 将磁盘的写策略有 write back 修改为 write through ，io 性能急剧下降导致 sql 慢查，进而影响应用层的逻辑处理。
raid 卡重置 当 raid 卡遇到异常时，会进行重置，相当于程序重启，导致系统 io hang。此时也会导致 sql 慢。下图是生产中遇到的 RAID 卡重置案例。
io 调度算法
noop（电梯式调度策略）：
deadline（介质时间调度策略）：
anticipatory（预料 I/O 调度策略）：
3.3 cpu 类型
cpu 电源策略是控制 cpu 运行在哪种模式下的耗电策略的，对于数据库服务器推荐 最大性能模式 以下内容摘自 《Red Hat Enterprise Linux7 电源管理指南》：
复制代码指令集 最近遇到的一个性能案例是 hw 的机器,因为指令集合默认关闭导致性能下降 15%。
复制代码四、数据库层面
4.1 没有索引，或者索引不正确
这个场景其实比较容易理解。相信每个 DBA 工作过程中都会或多或少遇到性能案例都和索引设计有关:创建表，没有索引，sql 随着数据量增大全表扫描而变慢。这个就不额外举例子了。
4.2 隐式转换
发生隐式转换时，MySQL 选择执行计划并不能利用到合适的索引而是选择全表扫描导致慢查询。常见的引发隐式转换的场景如下:
推荐阅读《聊聊隐式转换》
4.3 执行计划错误
由于 MySQL 优化器本身的不足，选择执行计划时会导致错误的执行计划使 sql 走了错误的索引或者没有做索引。比如
在检查某业务数据库的 slowlog 时发现一个慢查询,查询时间 1.57s ,检查表结构 where 条件字段存在正确的组合索引,正确的情况下优化器应该选择组合索引,而非为啥会导致慢查询呢？
root@rac1 10:48:11>explain select id,gmt_create, gmt_modified,order_id,service_id, seller_id,seller_nick, sale_type from lol where seller_id= 1501204and service_id= 1and sale_type in(3, 4) and use_status in(3, 4, 5, 6) and process_node_id= 6 order by id desc limit 0,20 \G*************************** 1. row ***************************select_type: SIMPLEtype: indexpossible_keys:idx_sellerid,idx_usestatus_saletype,idx_sellerid_saletype,idx_sidustsvidtypekey: PRIMARY--- 应该选择 idx_sidustsvidtypeExtra: Usingwhere1 row inset (0.00 sec)
复制代码推荐阅读《order by 主键id导致全表扫描的问题》
4.4 数据巨大
比如 select count(*) from t1 where a=‘xxxx’; 尽管字段 a 有索引，但是如果符合条件的记录数超高 10w，查询速度还是会比较慢。
selectcount(*) from t1 where app = 'marketing';| count(*) |1 row inset (0.92 sec)
复制代码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 多。显然执行
的查询效率肯定比查询 a=0 的要慢很多。
4.8 sql 姿势不合理
常见的分页查询 ，使用大分页深度查询。
该 SQL 是一个非常典型的排序+分页查询：order by col desc limit N,M MySQL 执行此类 SQL 时需要先扫描到 N 行，然后再去取 M 行。对于此类操作，取前面少数几行数据会很快，但是扫描的记录数越多，SQL 的性能就会越差，因为 N 越大，MySQL 需要扫描越多的数据来定位到具体的 N 行，这样耗费大量的 IO 成本和时间成本。
针对 limit 优化有很多种方式:
推荐阅读《性能优化之分页查询》
4.9 表结构设计
表结构设计是否合理也是影响 sql 性能的重要因素之一。以下表格展示了字段类型不同带来的 rt 性能差异。其中字段 c1 为 int 类型的字段，字段 c2 则是表名对应的字符串长度类型 varchar(200)到 varchar(5000) ,还有 text 字段。
4.10 innodb 刷脏页
对数据库运行机制有一定了解的朋友都会知道 InnoDB 引擎采用 Write Ahead Log(WAL)策略，即事务提交时，先写日志(redo log)，再写磁盘。为了提高 IO 效率,在写日志的时候会先写 buffer，然后集中 flush buffer pool 到磁盘。这个过程 我们称之为刷脏页。官方文档中描述：
推荐阅读:
复制代码4.11 undo 没有被 purge/回收
UNDO 日志是 MVCC 的重要组成部分，当一条数据被修改时，UNDO 日志里面保存了记录的历史版本。当事务需要查询记录的历史版本时，可以通过 UNDO 日志构建特定版本的数据。
所以当有长事务/异常未提交的情况就会因为其他查询需要构建快照导致 undo 不能被及时回收。查询遍历的 undo 越多 sql 执行的越慢。
推荐阅读《一次大量删除导致 MySQL 慢查的分析》
五、小结
这里总结了我工作经历中遇到的一部分可能会影响 SQL 执行效率的场景或者案例，经历有限，难免有遗漏的案例/场景，抛砖引玉，欢迎各位有兴趣的读者朋友留言说说你们遇到的场景。
原文链接：
