1. 一条“简单”SQL为什么会慢成这副德行先说说那天发生了什么吧。我正对着监控面板例行巡检突然发现某条SQL的平均执行时间飙到了1200ms峰值甚至冲过两秒。这条SQL其实是业务里再普通不过的一条查询——单表查询、字段也就几个、WHERE条件里全是等值判断怎么看都不像是能跑出千毫秒的“狠角色”。当时第一反应是网络抖动或者连接池出了问题但点开执行记录的完整链路之后发现时间几乎全耗在了数据库端的查询阶段传输和连接占用的时间连零头都不到。这种场景很多开发同学应该都遇到过SQL写完的时候明明很自信甚至开发环境里跑起来飞快但一到生产环境、数据量上来之后同样的语句性能就断崖式下跌。这里头有个最常见的误区——SQL语句的“简单”和数据库引擎执行的“简单”是两回事。你写的逻辑简单不代表优化器选择的执行路径简单。表结构、索引设计、数据分布、统计信息甚至服务器内存参数任何一个环节出了问题都能让一条表面简单的SQL在内核层面绕一个大圈。我见过太多人遇到这种问题第一反应是“加索引”加完之后发现没效果又开始怀疑服务器配置最后折腾了一整天发现根因根本不在这。所以这篇不是为了晒一条慢SQL的排查过程而是想把这几年踩过的坑、积累的排查思路完整梳理一遍。从日志定位到执行计划解读从索引设计到统计信息刷新每个环节我会把背后的原理和判断依据讲清楚你后面再遇到类似的“纳尼”时刻至少有章可循不用再凭着直觉瞎猜。2. 慢查询日志第一步不是猜是把现场翻出来2.1 打开慢查询日志的正确姿势排查上策永远是先看数据库自己记录的“事故报告”。这里有个很实际的问题慢查询日志并不是默认开启的很多生产MySQL实例跑了好几年日志开关一直是关闭状态等到真出了性能问题才手忙脚乱去开。好在你只需要关注以下几个核心参数slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1 min_examined_row_limit 100其中long_query_time设为1秒是常规做法刚好能够命中我遇到的那种超过1000ms的查询。log_queries_not_using_indexes这个开关特别值得打开——即使一条SQL跑得很快只要没用到索引它就一定会被记录下来。很多慢查询隐患其实是“积小成多”的单次执行看起来只有几十毫秒但高频调用下累积的资源消耗非常可观。min_examined_row_limit100是个容易被忽略的细节。如果不开这个参数那些扫描行数为0的、被优化器直接简化的查询也会被记录日志里会混入大量无用信息。设到100能过滤掉绝大多数用不上索引的空操作留下的都是真正值得关注的执行路径。2.2 慢查询日志里藏着哪些关键信息开启日志跑几天之后你就能在日志文件里看到类似这样的记录# Time: 2025-01-13T10:24:33.882394Z # UserHost: app_user[app_user] [192.168.1.100] Id: 381205 # Query_time: 1.234567 Lock_time: 0.001234 Rows_sent: 456 Rows_examined: 3123456 SET timestamp1736759073; SELECT id, user_name, status, created_at FROM user_order WHERE status PAID AND created_at 2024-01-01;这里面最骗不了人的两个数字是Rows_examined和Rows_sent。我这条SQL返回456行却扫描了312万行比值为0.0146%。换句话说为了取456条有效数据数据库翻了300多万行绝大部分工作都是在做无用功。这种扫描量级的查询如果每天被调用几十万次数据库IO和CPU不飙才奇怪。还有Lock_time这个字段很多人喜欢盯着它看觉得锁等待时间长才代表并发冲突。但我个人经验是InnoDB的行锁等待基本都很短如果Lock_time超过几十毫秒反而要怀疑是不是查询导致了大范围锁升级或者表上存在元数据锁被人为阻塞。慢查询日志不会直接告诉你锁在谁手上它更像是一个“现场照片”告诉你事故发生在哪个时刻、哪些表、哪些行被动了多久真正的锁链关系需要配合sys.innodb_lock_waits这类视图去查。提示慢查询日志里的执行时间是一个全局指标不区分CPU耗时和IO耗时。有时候一条SQL慢在磁盘读取日志里Query_time显示1.2s其中1s都在等数据页从磁盘加载到内存。这类问题的解法就不是加索引而是优化缓冲池命中率。所以看到慢日志不要急着改SQL先确认瓶颈在哪一层。2.3 归并同类项别被单条SQL骗了慢查询日志打开之后第一天可能就给你拉出上百条记录。这时候最忌讳的是看到一条改一条解决一个漏一个。正确操作是把日志里的SQL归类聚合——按“表名核心条件”分组。我一般会用pt-query-digest这类工具直接跑一遍自动聚合出响应时间占比TOP的SQL并计算每个模式的平均耗时、总耗时、出现在日志里的次数。聚合之后你会发现一个很有意思的现象真正占用数据库总时间的大户往往不是单次最慢的那几条而是那些单次只有几百毫秒、但每分钟被执行几百次的“高频中耗时”SQL。一条1200ms的SQL就算每天跑1万次总耗时也就3.3小时如果一条300ms的SQL每天跑10万次总耗时是8.3小时。性能优化的目标是把单位时间内的总成本降下来不是专注于削平个别尖峰。我遇到的这条SQL就属于后者。它在慢日志里的出现频率并不算高真正的问题在于它被一个高频API调用而且每次调用都要执行一遍全表级别的扫描。这就把优化目标带回了原点——为什么一条带条件查询的语句会扫描300多万行性能优化的第一步往往不是优化而是精确定位“病根科室”。3. 执行计划里的“隐形地雷”typeALL和回表3.1 EXPLAIN输出逐列拆解数据库引擎不像人一样“理解”SQL语义它靠的是执行计划。使用EXPLAIN SELECT ...就能看到优化器自己决定的做法EXPLAIN SELECT id, user_name, status, created_at FROM user_order WHERE status PAID AND created_at 2024-01-01\G输出大概是这样的id: 1 select_type: SIMPLE table: user_order partitions: NULL type: ALL possible_keys: idx_status, idx_created_at, idx_status_created key: NULL key_len: NULL ref: NULL rows: 3100000 filtered: 5.20 Extra: Using where看这个结果几个核心字段分别透露出什么信息我给你逐一解释typeALL全表扫描的标志。优化器遍历了整张表的聚簇索引主键索引才把结果捞出来。这是执行计划里最应该引起警觉的信号之一。possible_keys列出索引候选idx_status_created明明存在但keyNULL表明优化器最终一个索引都没用上。这种“有索引不用”的现象是大量慢SQL背后的常见真相。rows3100000优化器估算需要访问310万行。这个估算来自表的统计信息不一定完全精确但量级不会错。ExtraUsing where表示在存储引擎层返回记录之后还需要在服务层再做一次条件过滤。如果这里出现Using index就完全不同代表所有数据都从索引里直接取得连回表都省了。3.2 为什么两个等值条件反而触发全表扫描status PAID和created_at 2024-01-01明明是两个条件而且在列上都有索引为什么优化器还是选择全表扫描这是理解这条慢SQL的核心。用生活化的方式来说索引相当于书的目录但目录的排列顺序是有讲究的。假设idx_status_created是(status, created_at)联合索引它的物理排列顺序是“先按status排再按created_at排”。当查询条件同时命中这两个字段时它本可以非常高效地定位到所需数据。问题出在区分度上。status字段如果只有四五种取值WAIT、PAID、SHIPPED、FINISHED、CANCELLED那么即使走idx_status_created优化器估算出的扫描行数也接近全表的三分之一。对优化器来说走索引要先访问二级索引再回表取数据行这是一套复杂得多的操作。如果预计读取的二级索引行已经占了全表的20%以上优化器会认为全表扫描更快——因为顺序IO读整张表比分散的回表随机IO还要高效。这是MySQL优化器一个很重要的成本决策逻辑。索引不是加了就能用优化器会用“预估扫描行数、回表成本、IO成本、排序成本”综合算出代价然后选择它认为最低的方案。status这种低区分度字段做索引在数据量小时没感觉数据量大了反而会成为优化器决策的障碍——它宁可绕开索引走全表也不愿意承担海量回表的随机IO开销。3.3 回表性能损耗的隐形放大器如果你看到Using index condition或Using where数据库其实做了一件“回表”动作。简单理解二级索引非主键索引的叶子节点只保存了索引列和主键值它并不包含user_name、phone这些完整行数据。执行计划先通过二级索引找到匹配的主键再到主键索引里把完整行记录捞出来这个过程就叫回表。回表本身不是问题——如果只捞少量数据代价完全可以忽略。但如果在statusPAID的二级索引上命中了300万行理论上就要回表300万次。每次回表都是一次随机IO磁盘随机IO的延迟在毫秒级别累计起来就是秒级的总消耗。MySQL优化器虽然聪明但它经常低估了随机IO的实际代价特别是在机械硬盘或者IOPS受限的云盘环境下这种误判会被无限放大。这条SQL里还有一个藏在更深处的陷阱——idx_status_created虽然存在但它的结构是(status, created_at)。查询条件里status是等值created_at是范围理论上联合索引的前缀匹配规则是能生效的。但优化器觉得多花几十毫秒扫描完整个索引结构的收益还比不上全表扫描的顺序IO直接读完来得划算。所以在数据分布这种状态下它选择了全表。3.4 统计信息过期优化器“瞎猜”的元凶执行计划里还有一个很多人忽略的关键因素——统计信息。MySQL计算扫描行数的依据不是实时数据而是information_schema.statistics里缓存的统计值。如果表的数据量发生过剧烈变化比如批量导入了几百万条历史数据但统计信息还没更新优化器就会用一套过期的“视力表”做决策。这种情况很容易从EXPLAIN结果里看出来rows字段显示3100000但SHOW TABLE STATUS LIKE user_order里Rows可能是完全不同的数字或者Cardinality与实际的distinct值偏差极大。解决方式也直接ANALYZE TABLE user_order;这个操作会重新采样统计信息让优化器重新评估索引的区分度。有相当一部分“慢SQL”其实根本不用改SQL、不用加索引刷新一次统计信息之后执行计划就自动切到了正确索引上性能立竿见影。我处理过的案例里这种方式解决过一条1500ms掉到80ms的查询全程就一条ANALYZE命令。4. 真正动手索引重建与统计信息刷新4.1 先看清索引现状再动手排查到这一步我的SQL还没改过一行但已经大概摸清了问题脉络表里存在联合索引idx_status_created但优化器不愿意用原因是status区分度太低。下一步要做的不是马上建新索引而是先看清表上所有索引的真实分布SHOW INDEX FROM user_order;输出里每一行代表索引的一个字段重点看Cardinality这一列。Cardinality表示该字段在索引中的唯一值数量把它和表总行数相除就能得到索引区分度。区分度越高优化器越愿意走这条索引。比如status字段的唯一值只有5个区分度约等于0.0000016%这种索引在优化器眼里基本等同于无效。统计完所有候选索引的区分度之后我把idx_status_created的冗余问题也确认了——既然表里已经有了idx_created_at单列索引idx_status_created在status区分度极低的情况下几乎不产生额外收益。这种“看着合理、实际冗余”的索引不仅浪费磁盘空间还会拖慢写入性能因为每次INSERT/UPDATE都得同步维护多棵B树。4.2 重新设计索引列顺序处理方案不是凭空加一个索引而是对现有联合索引做优化把列顺序调整成区分度从高到低排列ALTER TABLE user_order DROP INDEX idx_status_created, ADD INDEX idx_created_status (created_at, status);把created_at放在联合索引的第一位。这个顺序调整的逻辑在于created_at是时间字段数据分布天然连续且区分度极高查询条件created_at 2024-01-01恰好命中范围扫描的头部status放在后面做过滤通过索引下推Index Condition Pushdown在扫描索引的过程中就完成status PAID的条件筛选能显著减少回表次数。这一点很重要很多人理解联合索引时有误区以为“查询里有哪个条件哪个字段就得放第一位”。其实索引列顺序的决定权在索引列的区分度、查询条件的选择性。区分度越高的列越应该靠前这能让优化器在做成本估算时得到更低的扫描行数预估从而愿意选择这条路径。反过来如果把低区分度的status放在前面等于索引的“第一道门”就宽得能开卡车后续列再精也没大打折扣。4.3 索引生效之后对比差距调整完成之后我重新执行EXPLAIN验证type: ref possible_keys: idx_created_status key: idx_created_status ref: const rows: 7600 Extra: Using index condition从typeALL变成typeref从扫描310万行降到扫描7600行Extra字段从“Using where”变成“Using index condition”——说明存储引擎在索引扫描阶段就把status条件过滤掉了回表次数被压缩到了极低水平。实际执行时间也从1200ms掉到了15ms左右快到没有机会再进慢查询日志。这组数据对比很能说明问题SQL写法一行没变纯粹靠索引结构调整和统计信息刷新性能提升了80倍。这也是我一直强调的观点——遇到慢SQL不要第一反应就重写SQL先把执行计划吃透。很多时候SQL本身没有错错的是“数据库找不到你想要的那条路”。4.4 变更落库的节奏与回滚预案索引变更属于DDL操作在表数据量大的时候会非常敏感。MySQL 5.6之后支持了Online DDL但实际锁表时间依然取决于ALGORITHM和LOCK参数。我的习惯是用pt-online-schema-change这类工具在低峰期执行或者至少按照以下步骤操作先在测试环境跑一遍相同表结构和数据量的变更记录DDL执行耗时。在生产环境选择一个查询低谷窗口执行变更预留至少30分钟的缓冲时间。变更完成之后立即做一次ANALYZE TABLE确保统计信息和新的索引结构同步。保留旧索引一段时间比如48小时确认新索引的线上表现稳定后再物理删除旧索引。另外要注意索引删除之后不可逆一旦新索引入线上后出现特殊情况想回退就得重新建一次索引。这种操作在千万级大表上可能耗时数分钟甚至更久所以在线变更之前备份好表结构定义永远是值得做的动作。5. 执行计划之外那些绕过索引的“隐形杀手”5.1 类型隐式转换一次无需写代码的索引失效排查的时候我把这条SQL的字段结构、表结构都对了一遍其实还发现一个容易被忽视的细节——status字段类型是VARCHAR(20)但如果查询条件传进来的参数是数字类型比如某些老接口直接把2这个数字拼进SQLMySQL会自动做隐式类型转换导致索引失效。原理不复杂优化器需要先把字符串类型的字段值转换成数字才能跟整数参数比较这一步操作等于在索引列上做了一个隐式函数操作索引就被“跳过”了。这类问题很隐蔽因为EXPLAIN显示的结果里经常不会直接告诉你“索引失效是因为隐式转换”。你要做的就是在排查时手动检查一下WHERE条件两侧的字段类型和参数类型是否一致。这个知识点越到后面越值钱因为很多业务代码经过几年迭代传入的参数类型可能已经悄悄从String变成了Integer。5.2 函数包裹与运算包装给索引“戴手铐”还有一个非常常见的索引失效场景就是在索引列上使用函数。比如SELECT * FROM user_order WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-01-01;这种写法对开发人员来说很直观但对数据库来说每一行的created_at都要先进DATE_FORMAT函数转换一遍转换完才能跟右边字符串比较。索引基于原始值的B树排列无法直接用于“函数转换后的值”优化器只能放弃索引走全表。正确的写法是把函数放到参数侧或者改成范围查询SELECT * FROM user_order WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;这个改写既保持了SQL语义不变又让索引列脱离了函数包裹。类似的还有LEFT(column, 3) abc、column 1 100这类运算都属于在索引列上动手脚。排查执行计划的时候看到Extra里出现Using where而且keyNULL第一反应可以查一下是不是WHERE子句里对字段动了函数或运算。5.3 LIKE前置通配符与OR条件分支模糊查询里最常见的性能陷阱是LIKE %keyword%。因为通配符在最前面优化器无法从B树的有序性里获得任何帮助前缀匹配退化成后缀匹配只能把所有可能的行全部扫描一遍。如果你是做电商后台或者内容管理系统的搜索框里这种写法太常见了。对于必须支持模糊搜索的场景更推荐的方式是引入全文索引或专门的外部搜索引擎而不是在SQL层面硬扛。另外OR连接的不同条件也很坑。如果OR两侧的条件只有一个能命中索引另一个走不了索引MySQL会把整条查询降级成全表扫描。比如WHERE status PAID OR user_name zhangsan如果user_name上没有索引这个OR会废掉status上的索引优势。改成UNION ALL把两个独立查询合并反而更有利于各自走各自的索引。这个技巧在数据量大的表上效果好到连你自己都惊讶。5.4 排序和分组非预期中的临时文件不是所有慢SQL都在WHERE环节耗死还有一类慢是“后面”慢——排序和分组。MySQL在ORDER BY字段不在索引里的时候会把结果集先存到内存临时表超出tmp_table_size限制后继续写入磁盘临时文件这个过程的IO成本极其惊人。EXPLAIN里的Using filesort就是信号。优化方式通常是让排序字段成为索引的一部分。因为索引天然有序优化器可以直接按索引顺序扫描省掉独立排序这一步。或者退一步如果必须用非索引字段排序可以尽量减少排序列的数据宽度比如只排序ID字段再通过JOIN查询捞回完整数据让磁盘临时文件的负担降到最低。6. 从一条SQL到一个体系的复盘6.1 巡检自动化长期主义才有价值单一问题解决后我更想说的是后面这套预警体系。在生产环境里跑着的SQL成百上千条今天解决了一条1200ms的查询明天还可能冒出来一条更隐蔽的。靠人力一条条看慢日志根本不现实。我现在的做法是在数据库上面挂了监控巡检脚本定时抓取慢查询日志里聚合后的Top N SQL自动邮件推送到研发群。一旦某条SQL的执行时间持续超过阈值开发同学就能第一时间看到上下文、表结构、执行环境问题从出现到被响应的时间压缩到分钟级别。具体实现不复杂核心逻辑几句话能说清楚每隔5分钟拉取增量慢查询日志。按“表名条件模板”做MD5分桶聚合。计算各桶的平均耗时和累计调用次数。超过阈值的桶触发告警并附带最近10条原始SQL文本。这种自动化不会漏掉任何长尾问题也解放了人工轮巡的时间。对于团队里没有专职DBA的小型后端组自己用Python脚本就能实现成本极低。6.2 规范前置在代码评审阶段消灭慢SQL自动巡检是事后兜底真正省力的是把性能检查前置到代码评审阶段。很多团队有SQL评审流程但只是人工看看语句有没有明显问题执行计划压根不看。我建议把EXPLAIN结果当作代码评审的一部分凡是改动涉及数据库查询的PR附上执行计划截图或文本type值不能是ALL、rows预估不能超过某个阈值具体数字根据单表规模定、Extra里不要出现Using filesort和Using temporary。还有一条容易被忽略的“隐形规则”上线前用生产环境的脱敏数据做性能验证。很多SQL在开发环境秒回是因为开发库只有十万行数据生产库是几千万行索引的决策逻辑完全不同。把数据规模拉齐才能暴露真实的执行计划。如果你的团队没有脱敏环境至少要养成用生产数据量级的备份库测试的习惯。6.3 关于慢SQL这件事我的几点私人心得排查慢SQL的过程中我自己总结了一套优先级顺序按影响范围从大到小排列统计信息 索引结构 SQL写法 硬件配置。很多人上来就换硬件、扩内存但大多数情况下数据库根本不缺资源缺的是让优化器“看得更准”的统计信息和让执行路径“走得更直”的索引设计。把前两项检查完至少八成慢SQL的问题能定下来。关于执行计划本身我想再强调一遍不要只看type和rows把key、Extra、filtered组合起来看才是完整的图景。key为空意味着没走索引ExtraUsing where说明存储引擎返回的数据还有一部分被服务层过滤掉了filtered则表示经过这一步条件过滤后最终剩下数据的百分比。这三个字段合在一起能帮你判断优化器到底在哪一环做出了错误的成本估算。还有一条实际经验值得分享MySQL优化器并不是每次都能“想明白”。在极少数情况下哪怕索引设计和统计信息全都没问题优化器还是选择了一个成本不是最低的执行计划。这时候可以尝试FORCE INDEX强制走索引或者使用STRAIGHT_JOIN调整驱动表顺序但这两招都属于“手动导航”上线之前一定要在预发布环境充分验证不建议作为长期方案。我在这条SQL排查完的第二天又把全表主要的查询语句、索引分布、数据增长速度全部拉出来复查了一遍建了一份表级别的索引档案。现在每次有同事问“这条SQL要不要加索引”我基本都能根据档案快速判断字段区分度够不够、列顺序合不合理、有没有冗余索引需要清理。这种持续积累的档案比任何一次性的SQL优化都更值钱。最后说个很实在的想法如果你想提升自己的SQL调优水平最快的方式不是看一堆理论书而是定期翻慢查询日志把你实际工作里每一条跑得慢的语句扒出执行计划动手改到它不再出现在慢日志里为止。这个循环重复十次之后你会发现很多“纳尼”时刻其实都是同一种套路——数据库想走一条“更快”的捷径结果把自己绕进了坑里。你能做的就是提前把路标立清楚让它永远走那条你最想让走的路。 MySQL慢SQL排查全流程：从执行计划到索引优化，性能提升80倍
1. 一条“简单”SQL为什么会慢成这副德行先说说那天发生了什么吧。我正对着监控面板例行巡检突然发现某条SQL的平均执行时间飙到了1200ms峰值甚至冲过两秒。这条SQL其实是业务里再普通不过的一条查询——单表查询、字段也就几个、WHERE条件里全是等值判断怎么看都不像是能跑出千毫秒的“狠角色”。当时第一反应是网络抖动或者连接池出了问题但点开执行记录的完整链路之后发现时间几乎全耗在了数据库端的查询阶段传输和连接占用的时间连零头都不到。这种场景很多开发同学应该都遇到过SQL写完的时候明明很自信甚至开发环境里跑起来飞快但一到生产环境、数据量上来之后同样的语句性能就断崖式下跌。这里头有个最常见的误区——SQL语句的“简单”和数据库引擎执行的“简单”是两回事。你写的逻辑简单不代表优化器选择的执行路径简单。表结构、索引设计、数据分布、统计信息甚至服务器内存参数任何一个环节出了问题都能让一条表面简单的SQL在内核层面绕一个大圈。我见过太多人遇到这种问题第一反应是“加索引”加完之后发现没效果又开始怀疑服务器配置最后折腾了一整天发现根因根本不在这。所以这篇不是为了晒一条慢SQL的排查过程而是想把这几年踩过的坑、积累的排查思路完整梳理一遍。从日志定位到执行计划解读从索引设计到统计信息刷新每个环节我会把背后的原理和判断依据讲清楚你后面再遇到类似的“纳尼”时刻至少有章可循不用再凭着直觉瞎猜。2. 慢查询日志第一步不是猜是把现场翻出来2.1 打开慢查询日志的正确姿势排查上策永远是先看数据库自己记录的“事故报告”。这里有个很实际的问题慢查询日志并不是默认开启的很多生产MySQL实例跑了好几年日志开关一直是关闭状态等到真出了性能问题才手忙脚乱去开。好在你只需要关注以下几个核心参数slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes 1 min_examined_row_limit 100其中long_query_time设为1秒是常规做法刚好能够命中我遇到的那种超过1000ms的查询。log_queries_not_using_indexes这个开关特别值得打开——即使一条SQL跑得很快只要没用到索引它就一定会被记录下来。很多慢查询隐患其实是“积小成多”的单次执行看起来只有几十毫秒但高频调用下累积的资源消耗非常可观。min_examined_row_limit100是个容易被忽略的细节。如果不开这个参数那些扫描行数为0的、被优化器直接简化的查询也会被记录日志里会混入大量无用信息。设到100能过滤掉绝大多数用不上索引的空操作留下的都是真正值得关注的执行路径。2.2 慢查询日志里藏着哪些关键信息开启日志跑几天之后你就能在日志文件里看到类似这样的记录# Time: 2025-01-13T10:24:33.882394Z # UserHost: app_user[app_user] [192.168.1.100] Id: 381205 # Query_time: 1.234567 Lock_time: 0.001234 Rows_sent: 456 Rows_examined: 3123456 SET timestamp1736759073; SELECT id, user_name, status, created_at FROM user_order WHERE status PAID AND created_at 2024-01-01;这里面最骗不了人的两个数字是Rows_examined和Rows_sent。我这条SQL返回456行却扫描了312万行比值为0.0146%。换句话说为了取456条有效数据数据库翻了300多万行绝大部分工作都是在做无用功。这种扫描量级的查询如果每天被调用几十万次数据库IO和CPU不飙才奇怪。还有Lock_time这个字段很多人喜欢盯着它看觉得锁等待时间长才代表并发冲突。但我个人经验是InnoDB的行锁等待基本都很短如果Lock_time超过几十毫秒反而要怀疑是不是查询导致了大范围锁升级或者表上存在元数据锁被人为阻塞。慢查询日志不会直接告诉你锁在谁手上它更像是一个“现场照片”告诉你事故发生在哪个时刻、哪些表、哪些行被动了多久真正的锁链关系需要配合sys.innodb_lock_waits这类视图去查。提示慢查询日志里的执行时间是一个全局指标不区分CPU耗时和IO耗时。有时候一条SQL慢在磁盘读取日志里Query_time显示1.2s其中1s都在等数据页从磁盘加载到内存。这类问题的解法就不是加索引而是优化缓冲池命中率。所以看到慢日志不要急着改SQL先确认瓶颈在哪一层。2.3 归并同类项别被单条SQL骗了慢查询日志打开之后第一天可能就给你拉出上百条记录。这时候最忌讳的是看到一条改一条解决一个漏一个。正确操作是把日志里的SQL归类聚合——按“表名核心条件”分组。我一般会用pt-query-digest这类工具直接跑一遍自动聚合出响应时间占比TOP的SQL并计算每个模式的平均耗时、总耗时、出现在日志里的次数。聚合之后你会发现一个很有意思的现象真正占用数据库总时间的大户往往不是单次最慢的那几条而是那些单次只有几百毫秒、但每分钟被执行几百次的“高频中耗时”SQL。一条1200ms的SQL就算每天跑1万次总耗时也就3.3小时如果一条300ms的SQL每天跑10万次总耗时是8.3小时。性能优化的目标是把单位时间内的总成本降下来不是专注于削平个别尖峰。我遇到的这条SQL就属于后者。它在慢日志里的出现频率并不算高真正的问题在于它被一个高频API调用而且每次调用都要执行一遍全表级别的扫描。这就把优化目标带回了原点——为什么一条带条件查询的语句会扫描300多万行性能优化的第一步往往不是优化而是精确定位“病根科室”。3. 执行计划里的“隐形地雷”typeALL和回表3.1 EXPLAIN输出逐列拆解数据库引擎不像人一样“理解”SQL语义它靠的是执行计划。使用EXPLAIN SELECT ...就能看到优化器自己决定的做法EXPLAIN SELECT id, user_name, status, created_at FROM user_order WHERE status PAID AND created_at 2024-01-01\G输出大概是这样的id: 1 select_type: SIMPLE table: user_order partitions: NULL type: ALL possible_keys: idx_status, idx_created_at, idx_status_created key: NULL key_len: NULL ref: NULL rows: 3100000 filtered: 5.20 Extra: Using where看这个结果几个核心字段分别透露出什么信息我给你逐一解释typeALL全表扫描的标志。优化器遍历了整张表的聚簇索引主键索引才把结果捞出来。这是执行计划里最应该引起警觉的信号之一。possible_keys列出索引候选idx_status_created明明存在但keyNULL表明优化器最终一个索引都没用上。这种“有索引不用”的现象是大量慢SQL背后的常见真相。rows3100000优化器估算需要访问310万行。这个估算来自表的统计信息不一定完全精确但量级不会错。ExtraUsing where表示在存储引擎层返回记录之后还需要在服务层再做一次条件过滤。如果这里出现Using index就完全不同代表所有数据都从索引里直接取得连回表都省了。3.2 为什么两个等值条件反而触发全表扫描status PAID和created_at 2024-01-01明明是两个条件而且在列上都有索引为什么优化器还是选择全表扫描这是理解这条慢SQL的核心。用生活化的方式来说索引相当于书的目录但目录的排列顺序是有讲究的。假设idx_status_created是(status, created_at)联合索引它的物理排列顺序是“先按status排再按created_at排”。当查询条件同时命中这两个字段时它本可以非常高效地定位到所需数据。问题出在区分度上。status字段如果只有四五种取值WAIT、PAID、SHIPPED、FINISHED、CANCELLED那么即使走idx_status_created优化器估算出的扫描行数也接近全表的三分之一。对优化器来说走索引要先访问二级索引再回表取数据行这是一套复杂得多的操作。如果预计读取的二级索引行已经占了全表的20%以上优化器会认为全表扫描更快——因为顺序IO读整张表比分散的回表随机IO还要高效。这是MySQL优化器一个很重要的成本决策逻辑。索引不是加了就能用优化器会用“预估扫描行数、回表成本、IO成本、排序成本”综合算出代价然后选择它认为最低的方案。status这种低区分度字段做索引在数据量小时没感觉数据量大了反而会成为优化器决策的障碍——它宁可绕开索引走全表也不愿意承担海量回表的随机IO开销。3.3 回表性能损耗的隐形放大器如果你看到Using index condition或Using where数据库其实做了一件“回表”动作。简单理解二级索引非主键索引的叶子节点只保存了索引列和主键值它并不包含user_name、phone这些完整行数据。执行计划先通过二级索引找到匹配的主键再到主键索引里把完整行记录捞出来这个过程就叫回表。回表本身不是问题——如果只捞少量数据代价完全可以忽略。但如果在statusPAID的二级索引上命中了300万行理论上就要回表300万次。每次回表都是一次随机IO磁盘随机IO的延迟在毫秒级别累计起来就是秒级的总消耗。MySQL优化器虽然聪明但它经常低估了随机IO的实际代价特别是在机械硬盘或者IOPS受限的云盘环境下这种误判会被无限放大。这条SQL里还有一个藏在更深处的陷阱——idx_status_created虽然存在但它的结构是(status, created_at)。查询条件里status是等值created_at是范围理论上联合索引的前缀匹配规则是能生效的。但优化器觉得多花几十毫秒扫描完整个索引结构的收益还比不上全表扫描的顺序IO直接读完来得划算。所以在数据分布这种状态下它选择了全表。3.4 统计信息过期优化器“瞎猜”的元凶执行计划里还有一个很多人忽略的关键因素——统计信息。MySQL计算扫描行数的依据不是实时数据而是information_schema.statistics里缓存的统计值。如果表的数据量发生过剧烈变化比如批量导入了几百万条历史数据但统计信息还没更新优化器就会用一套过期的“视力表”做决策。这种情况很容易从EXPLAIN结果里看出来rows字段显示3100000但SHOW TABLE STATUS LIKE user_order里Rows可能是完全不同的数字或者Cardinality与实际的distinct值偏差极大。解决方式也直接ANALYZE TABLE user_order;这个操作会重新采样统计信息让优化器重新评估索引的区分度。有相当一部分“慢SQL”其实根本不用改SQL、不用加索引刷新一次统计信息之后执行计划就自动切到了正确索引上性能立竿见影。我处理过的案例里这种方式解决过一条1500ms掉到80ms的查询全程就一条ANALYZE命令。4. 真正动手索引重建与统计信息刷新4.1 先看清索引现状再动手排查到这一步我的SQL还没改过一行但已经大概摸清了问题脉络表里存在联合索引idx_status_created但优化器不愿意用原因是status区分度太低。下一步要做的不是马上建新索引而是先看清表上所有索引的真实分布SHOW INDEX FROM user_order;输出里每一行代表索引的一个字段重点看Cardinality这一列。Cardinality表示该字段在索引中的唯一值数量把它和表总行数相除就能得到索引区分度。区分度越高优化器越愿意走这条索引。比如status字段的唯一值只有5个区分度约等于0.0000016%这种索引在优化器眼里基本等同于无效。统计完所有候选索引的区分度之后我把idx_status_created的冗余问题也确认了——既然表里已经有了idx_created_at单列索引idx_status_created在status区分度极低的情况下几乎不产生额外收益。这种“看着合理、实际冗余”的索引不仅浪费磁盘空间还会拖慢写入性能因为每次INSERT/UPDATE都得同步维护多棵B树。4.2 重新设计索引列顺序处理方案不是凭空加一个索引而是对现有联合索引做优化把列顺序调整成区分度从高到低排列ALTER TABLE user_order DROP INDEX idx_status_created, ADD INDEX idx_created_status (created_at, status);把created_at放在联合索引的第一位。这个顺序调整的逻辑在于created_at是时间字段数据分布天然连续且区分度极高查询条件created_at 2024-01-01恰好命中范围扫描的头部status放在后面做过滤通过索引下推Index Condition Pushdown在扫描索引的过程中就完成status PAID的条件筛选能显著减少回表次数。这一点很重要很多人理解联合索引时有误区以为“查询里有哪个条件哪个字段就得放第一位”。其实索引列顺序的决定权在索引列的区分度、查询条件的选择性。区分度越高的列越应该靠前这能让优化器在做成本估算时得到更低的扫描行数预估从而愿意选择这条路径。反过来如果把低区分度的status放在前面等于索引的“第一道门”就宽得能开卡车后续列再精也没大打折扣。4.3 索引生效之后对比差距调整完成之后我重新执行EXPLAIN验证type: ref possible_keys: idx_created_status key: idx_created_status ref: const rows: 7600 Extra: Using index condition从typeALL变成typeref从扫描310万行降到扫描7600行Extra字段从“Using where”变成“Using index condition”——说明存储引擎在索引扫描阶段就把status条件过滤掉了回表次数被压缩到了极低水平。实际执行时间也从1200ms掉到了15ms左右快到没有机会再进慢查询日志。这组数据对比很能说明问题SQL写法一行没变纯粹靠索引结构调整和统计信息刷新性能提升了80倍。这也是我一直强调的观点——遇到慢SQL不要第一反应就重写SQL先把执行计划吃透。很多时候SQL本身没有错错的是“数据库找不到你想要的那条路”。4.4 变更落库的节奏与回滚预案索引变更属于DDL操作在表数据量大的时候会非常敏感。MySQL 5.6之后支持了Online DDL但实际锁表时间依然取决于ALGORITHM和LOCK参数。我的习惯是用pt-online-schema-change这类工具在低峰期执行或者至少按照以下步骤操作先在测试环境跑一遍相同表结构和数据量的变更记录DDL执行耗时。在生产环境选择一个查询低谷窗口执行变更预留至少30分钟的缓冲时间。变更完成之后立即做一次ANALYZE TABLE确保统计信息和新的索引结构同步。保留旧索引一段时间比如48小时确认新索引的线上表现稳定后再物理删除旧索引。另外要注意索引删除之后不可逆一旦新索引入线上后出现特殊情况想回退就得重新建一次索引。这种操作在千万级大表上可能耗时数分钟甚至更久所以在线变更之前备份好表结构定义永远是值得做的动作。5. 执行计划之外那些绕过索引的“隐形杀手”5.1 类型隐式转换一次无需写代码的索引失效排查的时候我把这条SQL的字段结构、表结构都对了一遍其实还发现一个容易被忽视的细节——status字段类型是VARCHAR(20)但如果查询条件传进来的参数是数字类型比如某些老接口直接把2这个数字拼进SQLMySQL会自动做隐式类型转换导致索引失效。原理不复杂优化器需要先把字符串类型的字段值转换成数字才能跟整数参数比较这一步操作等于在索引列上做了一个隐式函数操作索引就被“跳过”了。这类问题很隐蔽因为EXPLAIN显示的结果里经常不会直接告诉你“索引失效是因为隐式转换”。你要做的就是在排查时手动检查一下WHERE条件两侧的字段类型和参数类型是否一致。这个知识点越到后面越值钱因为很多业务代码经过几年迭代传入的参数类型可能已经悄悄从String变成了Integer。5.2 函数包裹与运算包装给索引“戴手铐”还有一个非常常见的索引失效场景就是在索引列上使用函数。比如SELECT * FROM user_order WHERE DATE_FORMAT(created_at, %Y-%m-%d) 2024-01-01;这种写法对开发人员来说很直观但对数据库来说每一行的created_at都要先进DATE_FORMAT函数转换一遍转换完才能跟右边字符串比较。索引基于原始值的B树排列无法直接用于“函数转换后的值”优化器只能放弃索引走全表。正确的写法是把函数放到参数侧或者改成范围查询SELECT * FROM user_order WHERE created_at 2024-01-01 00:00:00 AND created_at 2024-01-02 00:00:00;这个改写既保持了SQL语义不变又让索引列脱离了函数包裹。类似的还有LEFT(column, 3) abc、column 1 100这类运算都属于在索引列上动手脚。排查执行计划的时候看到Extra里出现Using where而且keyNULL第一反应可以查一下是不是WHERE子句里对字段动了函数或运算。5.3 LIKE前置通配符与OR条件分支模糊查询里最常见的性能陷阱是LIKE %keyword%。因为通配符在最前面优化器无法从B树的有序性里获得任何帮助前缀匹配退化成后缀匹配只能把所有可能的行全部扫描一遍。如果你是做电商后台或者内容管理系统的搜索框里这种写法太常见了。对于必须支持模糊搜索的场景更推荐的方式是引入全文索引或专门的外部搜索引擎而不是在SQL层面硬扛。另外OR连接的不同条件也很坑。如果OR两侧的条件只有一个能命中索引另一个走不了索引MySQL会把整条查询降级成全表扫描。比如WHERE status PAID OR user_name zhangsan如果user_name上没有索引这个OR会废掉status上的索引优势。改成UNION ALL把两个独立查询合并反而更有利于各自走各自的索引。这个技巧在数据量大的表上效果好到连你自己都惊讶。5.4 排序和分组非预期中的临时文件不是所有慢SQL都在WHERE环节耗死还有一类慢是“后面”慢——排序和分组。MySQL在ORDER BY字段不在索引里的时候会把结果集先存到内存临时表超出tmp_table_size限制后继续写入磁盘临时文件这个过程的IO成本极其惊人。EXPLAIN里的Using filesort就是信号。优化方式通常是让排序字段成为索引的一部分。因为索引天然有序优化器可以直接按索引顺序扫描省掉独立排序这一步。或者退一步如果必须用非索引字段排序可以尽量减少排序列的数据宽度比如只排序ID字段再通过JOIN查询捞回完整数据让磁盘临时文件的负担降到最低。6. 从一条SQL到一个体系的复盘6.1 巡检自动化长期主义才有价值单一问题解决后我更想说的是后面这套预警体系。在生产环境里跑着的SQL成百上千条今天解决了一条1200ms的查询明天还可能冒出来一条更隐蔽的。靠人力一条条看慢日志根本不现实。我现在的做法是在数据库上面挂了监控巡检脚本定时抓取慢查询日志里聚合后的Top N SQL自动邮件推送到研发群。一旦某条SQL的执行时间持续超过阈值开发同学就能第一时间看到上下文、表结构、执行环境问题从出现到被响应的时间压缩到分钟级别。具体实现不复杂核心逻辑几句话能说清楚每隔5分钟拉取增量慢查询日志。按“表名条件模板”做MD5分桶聚合。计算各桶的平均耗时和累计调用次数。超过阈值的桶触发告警并附带最近10条原始SQL文本。这种自动化不会漏掉任何长尾问题也解放了人工轮巡的时间。对于团队里没有专职DBA的小型后端组自己用Python脚本就能实现成本极低。6.2 规范前置在代码评审阶段消灭慢SQL自动巡检是事后兜底真正省力的是把性能检查前置到代码评审阶段。很多团队有SQL评审流程但只是人工看看语句有没有明显问题执行计划压根不看。我建议把EXPLAIN结果当作代码评审的一部分凡是改动涉及数据库查询的PR附上执行计划截图或文本type值不能是ALL、rows预估不能超过某个阈值具体数字根据单表规模定、Extra里不要出现Using filesort和Using temporary。还有一条容易被忽略的“隐形规则”上线前用生产环境的脱敏数据做性能验证。很多SQL在开发环境秒回是因为开发库只有十万行数据生产库是几千万行索引的决策逻辑完全不同。把数据规模拉齐才能暴露真实的执行计划。如果你的团队没有脱敏环境至少要养成用生产数据量级的备份库测试的习惯。6.3 关于慢SQL这件事我的几点私人心得排查慢SQL的过程中我自己总结了一套优先级顺序按影响范围从大到小排列统计信息 索引结构 SQL写法 硬件配置。很多人上来就换硬件、扩内存但大多数情况下数据库根本不缺资源缺的是让优化器“看得更准”的统计信息和让执行路径“走得更直”的索引设计。把前两项检查完至少八成慢SQL的问题能定下来。关于执行计划本身我想再强调一遍不要只看type和rows把key、Extra、filtered组合起来看才是完整的图景。key为空意味着没走索引ExtraUsing where说明存储引擎返回的数据还有一部分被服务层过滤掉了filtered则表示经过这一步条件过滤后最终剩下数据的百分比。这三个字段合在一起能帮你判断优化器到底在哪一环做出了错误的成本估算。还有一条实际经验值得分享MySQL优化器并不是每次都能“想明白”。在极少数情况下哪怕索引设计和统计信息全都没问题优化器还是选择了一个成本不是最低的执行计划。这时候可以尝试FORCE INDEX强制走索引或者使用STRAIGHT_JOIN调整驱动表顺序但这两招都属于“手动导航”上线之前一定要在预发布环境充分验证不建议作为长期方案。我在这条SQL排查完的第二天又把全表主要的查询语句、索引分布、数据增长速度全部拉出来复查了一遍建了一份表级别的索引档案。现在每次有同事问“这条SQL要不要加索引”我基本都能根据档案快速判断字段区分度够不够、列顺序合不合理、有没有冗余索引需要清理。这种持续积累的档案比任何一次性的SQL优化都更值钱。最后说个很实在的想法如果你想提升自己的SQL调优水平最快的方式不是看一堆理论书而是定期翻慢查询日志把你实际工作里每一条跑得慢的语句扒出执行计划动手改到它不再出现在慢日志里为止。这个循环重复十次之后你会发现很多“纳尼”时刻其实都是同一种套路——数据库想走一条“更快”的捷径结果把自己绕进了坑里。你能做的就是提前把路标立清楚让它永远走那条你最想让走的路。