1. 先别急着怪索引慢SQL到底慢在哪一步我一直觉得MySQL里有个特别有意思的现象——越是刚接触索引的人越容易陷入一个思维定式SQL慢我已经建索引了啊为什么还这么慢这个问题的答案很有层次。是你建的索引压根没生效还是生效了但帮倒忙让查询更慢了又或者慢的根源根本不在查询逻辑上而是表结构、数据量、并发压力这些隐性因素在拖后腿我工作这些年被喊去救场的慢SQL里真正没建索引的情况其实占少数更多的情况恰恰就是上面那句灵魂拷问索引建了SQL还是慢。而这背后最常见的原因可以分成两大类——索引压根没被用上SQL写法绕开了索引优化器觉得走全表扫描可能更快索引在这条SQL里就是个花瓶。索引被用上了但还是慢索引生效了但它太笨重回表次数太大或者它自己产生了额外的维护代价把你省下来的时间又吃回去了。要彻底讲清楚这两类问题得先回到一个基础问题上我们常说走索引走的是什么索引这条索引路径上究竟发生了哪些事1.1 主键索引和辅助索引走的路根本不是一条MySQL默认的InnoDB引擎下数据是存在主键索引的叶子节点上的这种结构叫聚簇索引。你建的其他索引不管叫普通索引、唯一索引还是联合索引统称辅助索引或二级索引它们的叶子节点存的是主键值不是完整的数据行。这意味着什么当你执行一条WHERE name 张三如果name上有普通索引MySQL先通过辅助索引找到一批主键值再拿着这批主键值回到主键索引里找人——这个过程叫回表。查询条件命中的行数越多回表次数越多查询就越慢。比如一个索引选择性很差的场景表里有10万条数据status字段只有0和1两种值你建了idx_status然后查WHERE status 1。如果表里60%的数据status都是1优化器算一下回表成本大概率直接放弃索引走全表扫描——因为全表扫一遍可能比来回蹦跶更快。这也是为什么很多公司会建议把gender、status这类低基数字段放到索引后面甚至干脆别单独建索引。索引不是越多越好也不是建了就一定给你加速它更像一条专用快车道——你的车必须完全匹配它的规则而且路上的目标不能太多否则还不如直接顺着大马路扫一遍。1.2 InnoDB的B树长什么样决定了你的查询会被怎么走我一直觉得理解B树的形态是解开所有索引问题的钥匙。它比二叉树胖每个节点能存多个索引条目树的高度通常只有3到4层。这意味着你从根节点出发最多走三四个节点就能定位到叶子节点上的数据位置。更重要的是同一个节点在磁盘上物理连续。InnoDB以页默认16KB为单位读写一次I/O能加载一整个页的数据。对范围查询BETWEEN 100 AND 200索引可以顺着叶子节点的双向链表连续读取而且InnoDB还会做预读——发现你在读相邻的页时它会在后台把后面几页也读进来。这就是索引的核心价值把随机I/O变成顺序I/O。磁盘最怕的是转来转去找位置最不怕的是连着读一段。但反过来也是坑。如果你建了一个复合索引idx_a_b_c(a, b, c)却查WHERE c 1或者WHERE b 2 AND a 100这种查询条件不满足索引的最左前缀规则索引路径一开始就断了——B树是按从左到右的字段顺序排列的跳过了a直接查b或cB树没法知道应该走哪个分支只能放弃这条路或者做额外的过滤。2. 索引建了却不生效我踩过的那些看似没问题的SQL写法这一节必须重点讲。很多人建索引之前会查一下字段有没有索引建完以后下一句还是慢SQL问题就出在SQL写法和索引规则不匹配。我见过太多连面试都能答上来最左前缀原则的人实际写代码的时候照样掉坑。下面用几个最典型的场景说话每个都是我在生产环境里真实处理过的。2.1 对索引列做了函数计算或隐式转换这是最常见的自杀式写法SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;哪怕created_at上有索引WHERE DATE(created_at)也会让索引失效。因为你把所有索引值都套了一层DATE函数B树里存的是原始的created_at值不做一次全量计算MySQL没法拿2024-06-01去匹配。优化器一算算了直接全表扫吧。正确写法是把它改成范围查询SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这不只是为了索引生效更是让条件变成可搜索的。函数包裹后索引里存的原始值已经变形了除非你建函数索引MySQL 8.0支持否则常规索引在这个查询上帮不了任何忙。同样的坑还有隐式类型转换。比如phone字段是VARCHAR类型你查WHERE phone 13800138000MySQL会把字符串列隐式转成数字比较效果等同于对列做了CAST函数SELECT * FROM users WHERE CAST(phone AS SIGNED) 13800138000;索引又白建了。2.2 LIKE必须以什么开头这个细节很多人知道但屡屡忽略大家都知道LIKE %关键词%走不了索引因为索引是按首字母/首字节排序的你不知道开头是什么B树不知道该往哪个分支走。但有一个容易被忽略的变形LIKE %关键词其实能走索引前提是你把条件改成LIKE 关键词%。反过来如果非要用%关键词结尾那就老老实实接受全表扫描或者另想办法。这里有个进阶思路MySQL 5.7之后支持生成列Generated Column你可以提前把需要模糊搜索的字段做反转比如把abc123存成321cba然后查询时也用反转后的前缀去匹配ALTER TABLE users ADD COLUMN reversed_name VARCHAR(255) GENERATED ALWAYS AS (REVERSE(name)) STORED, ADD INDEX idx_reversed_name (reversed_name); SELECT * FROM users WHERE reversed_name LIKE CONCAT(REVERSE(?), %);虽然冷门但在某些必须做后缀匹配的场景下意外地好用。2.3 OR连接条件让优化器左右为难很多人以为OR只是把两个条件拼起来不会影响索引。实际上这是个典型的优化器选择题SELECT * FROM products WHERE brand_id 1 OR price 99;如果brand_id和price上都有索引MySQL确实可以分别走这两个索引再合并结果。但如果只有一个字段有索引优化器会怎么选它发现OR要满足任一条件成立就得把所有可能性都找出来索引路径只能覆盖一半那不如干脆全表扫——这样至少结果一定正确不折腾。所以处理OR条件有个不成文的经验要么保证OR两边的字段都有索引或属于同一个联合索引要么把它拆成UNIONSELECT * FROM products WHERE brand_id 1 UNION SELECT * FROM products WHERE price 99;拆开以后每条SQL各自走索引再合并结果往往比全表扫描快得多。如果你发现某条SQL在OR条件下虽然索引失效但数据量也不算大那全表扫也就几毫秒这时候强行改SQL反而浪费时间——优化要讲性价比不是所有索引失效都必须修。2.4 不等于、NOT IN和排序分组的隐藏雷区还有一类隐蔽性更强的场景查询条件本身能走索引但你想把结果排序或分组。SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC;status有索引create_time也有索引但它们是两个独立索引。MySQL走了idx_status拿到一批主键值回表取到数据后还要额外做一次filesort磁盘排序把结果按create_time排好。这里的慢不是索引没生效而是索引帮你找到行行却不在正确顺序上。如果业务上这条SQL很频繁更好的做法是建联合索引idx_status_create_time(status, create_time)——先按status筛选天然按create_time排好序排序这一步直接从执行计划里消失了。类似地NOT IN、这些操作优化器普遍认为不匹配的条件走索引没太大优势通常也会放弃索引。你当然可以通过UNION改写成多个正条件但也要看数据分布值不值得不能一刀切硬改。3. 用EXPLAIN给慢SQL做一次深度体检别再靠猜了上面讲了不少索引失效场景但实际工作中最忌讳的就是靠猜。我见过太多人SQL变慢了第一反应是是不是索引没建对然后埋头看半天变量、试半天新索引也不看执行计划。你要是让一个DBA来看这个问题他第一件事一定是跑一条EXPLAIN就像去医院先拍片子而不是凭感觉开药。3.1 一条完整执行计划的阅读姿势EXPLAIN的输出每一列都有意义但真正判断索引是否生效且高效的关键集中在几个字段上。先看一个经典例子EXPLAIN SELECT order_id, amount FROM orders WHERE status 1 ORDER BY create_time DESC;正常结果长这样字段我精简了id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | orders | ref | idx_status | idx_status | 1 | const | 21737 | Using filesort这里最关键的信息是type显示ref代表命中普通索引等值匹配。更理想的还有const主键/唯一索引查一行、eq_ref连接查询中按唯一键取一行。如果看到ALL恭喜你全表扫描索引没被用上。看到index也要注意它表示扫了整棵索引树虽然没扫表但本质上也没省多少事。keyMySQL最终决定使用的索引。如果key为NULL而possible_keys有值说明优化器经过成本评估认为走索引不比全表扫描快主动弃用了。rows预读取扫过的行数越小越好。这个数字是估算值但有很高的参考价值。Extra这里藏着很多惊喜和惊吓。出来的值五花八门比如Using where代表SQL在存储引擎返回结果后还要在Server层过滤Using temporary代表用了临时表通常和GROUP BY有关Using filesort代表需要额外排序。上面这个例子Extra里有Using filesort提示我们排序出了问题——联合索引idx_status_create_time显然比单独idx_status更合适。3.2 相同查询不同写法的执行计划差异我之前处理过一个具体案例两张表结构都一样因为历史分表但同样的业务查询一张表走了索引另一张全表扫描查出来的执行计划让我一眼就明白问题在哪了。A表执行计划type: ref, key: idx_user_created, rows: 356, Extra: NULLB表执行计划type: ALL, key: NULL, rows: 500000, Extra: Using where同一个业务SQLA表走了索引B表直接全表扫了500万行。为什么会这样因为B表的联合索引顺序是(created_at, user_id)而SQL条件是WHERE user_id ? ORDER BY created_at——user_id在第二个位置查询条件没从联合索引的最左列开始索引直接失效。这个案例很典型地说明了同一个索引在不同表上可能完全没区别但同一个SQL在不同索引结构上的表现可以天差地别。你光看SQL文本看不出任何问题只有EXPLAIN能让你看到真相。3.3 不只有EXPLAINEXPLAIN ANALYZE更直白MySQL 8.0.18之后EXPLAIN还多了一个用法EXPLAIN ANALYZE。它不只是给个估算而是真正执行一遍SQL告诉你在哪里花了多少时间EXPLAIN ANALYZE SELECT order_id, amount FROM orders WHERE status 1 ORDER BY create_time DESC;输出结果里会带着实际的耗时信息比如- Sort: orders.create_time DESC (actual time0.123..0.124 rows20) - Index lookup on orders using idx_status (status1) (actual time0.018..0.092 rows30)这段信息直接告诉你排序花了多久、索引查找花了多久。哪个环节吃掉了大头一眼就清楚。4. 索引用上了还是慢那些生效但低效的深层问题先声明一点从这个章节开始讨论的是比索引失效更细的问题。很多人在索引失效一条SQL都不背锅的时候会把责任推给数据库参数或者服务器性能但事实上有些时候你真得仔细抠一下索引本身的设计。4.1 回表成本被你严重低估了还是回到回表的概念。辅助索引叶子节点只存主键值你要查询的列如果不在这个索引里就必须拿着主键回主键索引取整行数据。假设有一条SQLSELECT user_name, email, phone FROM users WHERE nickname 小李;nickname上有辅助索引但user_name、email、phone都不在索引里。这条SQL每命中一条记录就要回一次表一次回表就是一次随机I/O。如果命中1000条记录那就是1000次随机I/O聚簇索引的叶子页分散在不同位置磁盘可能要来回转上千次。解决这个问题最常用的手段是覆盖索引让索引覆盖你需要的所有字段查询就不需要回表了。ALTER TABLE users ADD INDEX idx_nickname_email_phone (nickname, email, phone);这样查询ID、nickname、email、phone都能直接从索引里取Extra还会出现Using index——这个标志是正面信号代表索引已经包含了查询所需的数据不用再回表。不过我提醒一句覆盖索引不是无脑加。它本质上是用额外存储空间换查询效率联合索引的字段越多B树越胖写入时需要更新的索引列越多写入成本相应上升。如果一个表频繁UPDATE/INSERT加一堆宽索引可能会让你的写性能雪上加霜。实际经验是优先把高频查询的SELECT列补进索引但保持在2到3个字段以内。如果需求明确添加字段时把等值查询列放前面范围查询列放中间排序字段放最后其他冗余需求谨慎追加。4.2 联合索引的字段顺序决定生死联合索引的字段顺序问题是索引用上了但还是慢的高发区。比如用户表场景SELECT * FROM users WHERE age 30 AND city 上海;如果你建了idx_city_age(city, age)这条SQL能很快过滤到city上海的数据再进一步筛age30。但如果你建的是idx_age_city(age, city)MySQL只能先按age过滤全部30岁的人再额外筛选上海。逻辑上都能出正确结果但前者过滤得更狠更快rows估算会差好几倍。更麻烦的是排序和分组对顺序的要求。联合索引天然支持从左到右先排序再分类的查询。比如查最近30天每个城市的新增用户数联合索引里把时间、城市分别放在哪将直接决定SQL是否触发Using filesort或者Using temporary。如果排序字段和等值筛选字段是同一张表上的不同字段一个非常有效的设计思路是-- 场景WHERE status 1 AND channel web ORDER BY created_at -- 联合索引可以设计成 (status, channel, created_at) -- 这样status和channel做等值条件created_at天然就是排好序的也就是说把等值条件字段放前面排序字段放最后这是一张万能配方。它让MySQL在索引树里依次精确匹配等值条件最后沿着排序字段的顺序直接输出不额外排序也不产生临时表。4.3 慢不慢还得看数据分布优化器的理性选择有时候索引明明能用优化器就是不用它这不能全怪优化器犯傻它在做成本评估时相当精明。优化器维护着一张关于表的数据统计信息用来估算走索引要扫多少行、回表多少次、每步大概要多长时间。如果它估算走索引的成本比全表扫描还高它就会选择全表扫描。我遇到过最典型的一个场景是小表大索引一张只有两万行左右的配置表优化器判断一个4层的B树来回跳转的成本远大于直接在两万行里顺序扫一遍。这时候哪怕你把索引建得再完美优化器依然会无视它走全表扫描。这类情况人工干涉的空间不大更好的策略是接受现实让配置表保持小而精。还有一种更值得警惕的情况统计信息过期。表里数据经过大量增删改之后information_schema.statistics里的基数信息可能严重失真导致优化器选择了一个错误的执行路径。常见做法是定期执行ANALYZE TABLE让统计信息刷新有时一条SQL突然变慢跑一次这个命令就好了。4.4 深入细节索引条件下推和排序分组的优化机会有两个深度学习过MySQL内部执行原理的人才会主动去用的技术点这里一并放出来可能会对你理解为什么索引建了还是慢有启发。索引条件下推ICP解决的问题是在没有ICP之前联合索引可能只用到第一个字段其余筛选条件要在回表后逐行过滤。MySQL 5.6引入ICP后存储引擎层可以直接根据联合索引中的其他字段做第二次过滤减少回表次数。怎么判断有没有用上ICP看Extra里是否出现Using index condition。如果出现了说明MySQL已经尽量利用索引列做过滤。想更主动一点可以在筛选条件里尽量使用联合索引包含的字段让ICP发挥更大空间。排序分组的极端案例当你的SQL同时有WHERE、GROUP BY、ORDER BY时索引的设计要同时照顾三个动作。前面提到的联合索引顺序策略不只是为了WHERE更是为了让GROUP BY和ORDER BY能在索引顺序上直接完成避免临时表。一个我调过很多次的通用优化套路是-- 问题SQL SELECT user_id, COUNT(*) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ORDER BY COUNT(*) DESC;这种情况就算有索引也容易让GROUP BY产出临时表因为索引默认是按数据物理顺序排列的而不是按聚合结果排序。一个思路是改变索引结构让GROUP BY字段有序或者接受全表排序的现实业务上做分页处理。如果想同时优化两个动作可以把字段做排列先按user_id分组、再保证时间顺序支持范围条件但代价是你必须接受对COUNT结果排序时的额外损耗。5. 这些经验帮我少走了很多弯路分享给你最后这部分跟大家说说我在实际项目里被问到最多的几个经验性问题和我的处理方法。这次不聊原理了全是实操里边角料级的干货。5.1 慢查询日志和pt-query-digest是排查慢SQL的黄金搭档我在排查慢SQL的时候第一步永远是开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time 1意味着超过1秒的SQL都会被记录下来。生产环境建议设到0.5或1秒就行太多会拖累性能。日志文件出来以后手写脚本统计太累我更推荐用Percona Toolkit里的pt-query-digestpt-query-digest /var/log/mysql/mysql-slow.log它会按总耗时、执行次数、平均耗时做排行一眼就能看到哪条SQL是最该被优化的。重要的是它能帮你识别同一形态SQL的不同写法避免你盯着一个文本去搜结果漏掉了一堆类似写法。5.2 千万别为了平时用得上给每列都加索引我见过最生猛的做法是把一张表里几乎每个VARCHAR字段都加上索引理由是以后哪个SQL慢哪个SQL用得上。实际上索引越多写入越慢每次INSERT/UPDATE/DELETE都要同步维护所有索引。在表数据量过千万以后哪怕你所有索引都用上了MySQL也可能因为维护索引的代价出现严重的写锁竞争。这里有个很朴素的判断标准先看慢查询日志确认哪些SQL真的慢再决定给哪个列建索引。没有实际慢SQL表演过的字段一律不加索引这是DBA的基本修养。5.3 不一定非要把慢SQL改到极致也可以让SQL走查询缓存旧版或让业务侧改变查询模式MySQL 8.0之后已经移除了查询缓存很多人还在用老思路指望一个开缓存就能把所有重复查询提速。现实是为了从缓存拿结果每次写入后缓存要失效高并发读写场景下缓存命中率并不乐观。真正值得花时间的往往是搞清楚业务能不能换个查询模式。比如报表统计天天跑一次大GROUP BY与其反复优化这条SQL不如提前算好汇总表让报表直接读汇总结果。这个思路叫预聚合配合索引加速明细查询效果远超死磕单条SQL。5.4 最后一个小技巧每次改完索引都要回EXPLAIN看一遍这是我能给你最诚实的建议不管你根据任何博客、任何经验之谈调整了索引都必须用EXPLAIN验证一下这次改动到底有没有生效。我自己的习惯是改完索引以后立刻跑一遍这个表上最常执行的10条SQL看type、key、rows、Extra四个字段有没有变好。如果没有变好说明要么是SQL写法仍有索引失效的地方要么是优化器认为这步改动不划算。说句实在的MySQL索引优化这件事从来不是建了就完了它是一个循环往复的过程发现慢SQL做EXPLAIN分析执行计划调整索引再次验证。你踩过的坑越多、验证过的组合越多接到为什么建了索引SQL还是慢这类问题时判断得就越快。跟数据库打交道经验就是这么一笔一笔攒下来的。
MySQL慢SQL排查指南：索引建了为何还慢？优化实战与EXPLAIN诊断
1. 先别急着怪索引慢SQL到底慢在哪一步我一直觉得MySQL里有个特别有意思的现象——越是刚接触索引的人越容易陷入一个思维定式SQL慢我已经建索引了啊为什么还这么慢这个问题的答案很有层次。是你建的索引压根没生效还是生效了但帮倒忙让查询更慢了又或者慢的根源根本不在查询逻辑上而是表结构、数据量、并发压力这些隐性因素在拖后腿我工作这些年被喊去救场的慢SQL里真正没建索引的情况其实占少数更多的情况恰恰就是上面那句灵魂拷问索引建了SQL还是慢。而这背后最常见的原因可以分成两大类——索引压根没被用上SQL写法绕开了索引优化器觉得走全表扫描可能更快索引在这条SQL里就是个花瓶。索引被用上了但还是慢索引生效了但它太笨重回表次数太大或者它自己产生了额外的维护代价把你省下来的时间又吃回去了。要彻底讲清楚这两类问题得先回到一个基础问题上我们常说走索引走的是什么索引这条索引路径上究竟发生了哪些事1.1 主键索引和辅助索引走的路根本不是一条MySQL默认的InnoDB引擎下数据是存在主键索引的叶子节点上的这种结构叫聚簇索引。你建的其他索引不管叫普通索引、唯一索引还是联合索引统称辅助索引或二级索引它们的叶子节点存的是主键值不是完整的数据行。这意味着什么当你执行一条WHERE name 张三如果name上有普通索引MySQL先通过辅助索引找到一批主键值再拿着这批主键值回到主键索引里找人——这个过程叫回表。查询条件命中的行数越多回表次数越多查询就越慢。比如一个索引选择性很差的场景表里有10万条数据status字段只有0和1两种值你建了idx_status然后查WHERE status 1。如果表里60%的数据status都是1优化器算一下回表成本大概率直接放弃索引走全表扫描——因为全表扫一遍可能比来回蹦跶更快。这也是为什么很多公司会建议把gender、status这类低基数字段放到索引后面甚至干脆别单独建索引。索引不是越多越好也不是建了就一定给你加速它更像一条专用快车道——你的车必须完全匹配它的规则而且路上的目标不能太多否则还不如直接顺着大马路扫一遍。1.2 InnoDB的B树长什么样决定了你的查询会被怎么走我一直觉得理解B树的形态是解开所有索引问题的钥匙。它比二叉树胖每个节点能存多个索引条目树的高度通常只有3到4层。这意味着你从根节点出发最多走三四个节点就能定位到叶子节点上的数据位置。更重要的是同一个节点在磁盘上物理连续。InnoDB以页默认16KB为单位读写一次I/O能加载一整个页的数据。对范围查询BETWEEN 100 AND 200索引可以顺着叶子节点的双向链表连续读取而且InnoDB还会做预读——发现你在读相邻的页时它会在后台把后面几页也读进来。这就是索引的核心价值把随机I/O变成顺序I/O。磁盘最怕的是转来转去找位置最不怕的是连着读一段。但反过来也是坑。如果你建了一个复合索引idx_a_b_c(a, b, c)却查WHERE c 1或者WHERE b 2 AND a 100这种查询条件不满足索引的最左前缀规则索引路径一开始就断了——B树是按从左到右的字段顺序排列的跳过了a直接查b或cB树没法知道应该走哪个分支只能放弃这条路或者做额外的过滤。2. 索引建了却不生效我踩过的那些看似没问题的SQL写法这一节必须重点讲。很多人建索引之前会查一下字段有没有索引建完以后下一句还是慢SQL问题就出在SQL写法和索引规则不匹配。我见过太多连面试都能答上来最左前缀原则的人实际写代码的时候照样掉坑。下面用几个最典型的场景说话每个都是我在生产环境里真实处理过的。2.1 对索引列做了函数计算或隐式转换这是最常见的自杀式写法SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;哪怕created_at上有索引WHERE DATE(created_at)也会让索引失效。因为你把所有索引值都套了一层DATE函数B树里存的是原始的created_at值不做一次全量计算MySQL没法拿2024-06-01去匹配。优化器一算算了直接全表扫吧。正确写法是把它改成范围查询SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这不只是为了索引生效更是让条件变成可搜索的。函数包裹后索引里存的原始值已经变形了除非你建函数索引MySQL 8.0支持否则常规索引在这个查询上帮不了任何忙。同样的坑还有隐式类型转换。比如phone字段是VARCHAR类型你查WHERE phone 13800138000MySQL会把字符串列隐式转成数字比较效果等同于对列做了CAST函数SELECT * FROM users WHERE CAST(phone AS SIGNED) 13800138000;索引又白建了。2.2 LIKE必须以什么开头这个细节很多人知道但屡屡忽略大家都知道LIKE %关键词%走不了索引因为索引是按首字母/首字节排序的你不知道开头是什么B树不知道该往哪个分支走。但有一个容易被忽略的变形LIKE %关键词其实能走索引前提是你把条件改成LIKE 关键词%。反过来如果非要用%关键词结尾那就老老实实接受全表扫描或者另想办法。这里有个进阶思路MySQL 5.7之后支持生成列Generated Column你可以提前把需要模糊搜索的字段做反转比如把abc123存成321cba然后查询时也用反转后的前缀去匹配ALTER TABLE users ADD COLUMN reversed_name VARCHAR(255) GENERATED ALWAYS AS (REVERSE(name)) STORED, ADD INDEX idx_reversed_name (reversed_name); SELECT * FROM users WHERE reversed_name LIKE CONCAT(REVERSE(?), %);虽然冷门但在某些必须做后缀匹配的场景下意外地好用。2.3 OR连接条件让优化器左右为难很多人以为OR只是把两个条件拼起来不会影响索引。实际上这是个典型的优化器选择题SELECT * FROM products WHERE brand_id 1 OR price 99;如果brand_id和price上都有索引MySQL确实可以分别走这两个索引再合并结果。但如果只有一个字段有索引优化器会怎么选它发现OR要满足任一条件成立就得把所有可能性都找出来索引路径只能覆盖一半那不如干脆全表扫——这样至少结果一定正确不折腾。所以处理OR条件有个不成文的经验要么保证OR两边的字段都有索引或属于同一个联合索引要么把它拆成UNIONSELECT * FROM products WHERE brand_id 1 UNION SELECT * FROM products WHERE price 99;拆开以后每条SQL各自走索引再合并结果往往比全表扫描快得多。如果你发现某条SQL在OR条件下虽然索引失效但数据量也不算大那全表扫也就几毫秒这时候强行改SQL反而浪费时间——优化要讲性价比不是所有索引失效都必须修。2.4 不等于、NOT IN和排序分组的隐藏雷区还有一类隐蔽性更强的场景查询条件本身能走索引但你想把结果排序或分组。SELECT * FROM orders WHERE status 1 ORDER BY create_time DESC;status有索引create_time也有索引但它们是两个独立索引。MySQL走了idx_status拿到一批主键值回表取到数据后还要额外做一次filesort磁盘排序把结果按create_time排好。这里的慢不是索引没生效而是索引帮你找到行行却不在正确顺序上。如果业务上这条SQL很频繁更好的做法是建联合索引idx_status_create_time(status, create_time)——先按status筛选天然按create_time排好序排序这一步直接从执行计划里消失了。类似地NOT IN、这些操作优化器普遍认为不匹配的条件走索引没太大优势通常也会放弃索引。你当然可以通过UNION改写成多个正条件但也要看数据分布值不值得不能一刀切硬改。3. 用EXPLAIN给慢SQL做一次深度体检别再靠猜了上面讲了不少索引失效场景但实际工作中最忌讳的就是靠猜。我见过太多人SQL变慢了第一反应是是不是索引没建对然后埋头看半天变量、试半天新索引也不看执行计划。你要是让一个DBA来看这个问题他第一件事一定是跑一条EXPLAIN就像去医院先拍片子而不是凭感觉开药。3.1 一条完整执行计划的阅读姿势EXPLAIN的输出每一列都有意义但真正判断索引是否生效且高效的关键集中在几个字段上。先看一个经典例子EXPLAIN SELECT order_id, amount FROM orders WHERE status 1 ORDER BY create_time DESC;正常结果长这样字段我精简了id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra 1 | SIMPLE | orders | ref | idx_status | idx_status | 1 | const | 21737 | Using filesort这里最关键的信息是type显示ref代表命中普通索引等值匹配。更理想的还有const主键/唯一索引查一行、eq_ref连接查询中按唯一键取一行。如果看到ALL恭喜你全表扫描索引没被用上。看到index也要注意它表示扫了整棵索引树虽然没扫表但本质上也没省多少事。keyMySQL最终决定使用的索引。如果key为NULL而possible_keys有值说明优化器经过成本评估认为走索引不比全表扫描快主动弃用了。rows预读取扫过的行数越小越好。这个数字是估算值但有很高的参考价值。Extra这里藏着很多惊喜和惊吓。出来的值五花八门比如Using where代表SQL在存储引擎返回结果后还要在Server层过滤Using temporary代表用了临时表通常和GROUP BY有关Using filesort代表需要额外排序。上面这个例子Extra里有Using filesort提示我们排序出了问题——联合索引idx_status_create_time显然比单独idx_status更合适。3.2 相同查询不同写法的执行计划差异我之前处理过一个具体案例两张表结构都一样因为历史分表但同样的业务查询一张表走了索引另一张全表扫描查出来的执行计划让我一眼就明白问题在哪了。A表执行计划type: ref, key: idx_user_created, rows: 356, Extra: NULLB表执行计划type: ALL, key: NULL, rows: 500000, Extra: Using where同一个业务SQLA表走了索引B表直接全表扫了500万行。为什么会这样因为B表的联合索引顺序是(created_at, user_id)而SQL条件是WHERE user_id ? ORDER BY created_at——user_id在第二个位置查询条件没从联合索引的最左列开始索引直接失效。这个案例很典型地说明了同一个索引在不同表上可能完全没区别但同一个SQL在不同索引结构上的表现可以天差地别。你光看SQL文本看不出任何问题只有EXPLAIN能让你看到真相。3.3 不只有EXPLAINEXPLAIN ANALYZE更直白MySQL 8.0.18之后EXPLAIN还多了一个用法EXPLAIN ANALYZE。它不只是给个估算而是真正执行一遍SQL告诉你在哪里花了多少时间EXPLAIN ANALYZE SELECT order_id, amount FROM orders WHERE status 1 ORDER BY create_time DESC;输出结果里会带着实际的耗时信息比如- Sort: orders.create_time DESC (actual time0.123..0.124 rows20) - Index lookup on orders using idx_status (status1) (actual time0.018..0.092 rows30)这段信息直接告诉你排序花了多久、索引查找花了多久。哪个环节吃掉了大头一眼就清楚。4. 索引用上了还是慢那些生效但低效的深层问题先声明一点从这个章节开始讨论的是比索引失效更细的问题。很多人在索引失效一条SQL都不背锅的时候会把责任推给数据库参数或者服务器性能但事实上有些时候你真得仔细抠一下索引本身的设计。4.1 回表成本被你严重低估了还是回到回表的概念。辅助索引叶子节点只存主键值你要查询的列如果不在这个索引里就必须拿着主键回主键索引取整行数据。假设有一条SQLSELECT user_name, email, phone FROM users WHERE nickname 小李;nickname上有辅助索引但user_name、email、phone都不在索引里。这条SQL每命中一条记录就要回一次表一次回表就是一次随机I/O。如果命中1000条记录那就是1000次随机I/O聚簇索引的叶子页分散在不同位置磁盘可能要来回转上千次。解决这个问题最常用的手段是覆盖索引让索引覆盖你需要的所有字段查询就不需要回表了。ALTER TABLE users ADD INDEX idx_nickname_email_phone (nickname, email, phone);这样查询ID、nickname、email、phone都能直接从索引里取Extra还会出现Using index——这个标志是正面信号代表索引已经包含了查询所需的数据不用再回表。不过我提醒一句覆盖索引不是无脑加。它本质上是用额外存储空间换查询效率联合索引的字段越多B树越胖写入时需要更新的索引列越多写入成本相应上升。如果一个表频繁UPDATE/INSERT加一堆宽索引可能会让你的写性能雪上加霜。实际经验是优先把高频查询的SELECT列补进索引但保持在2到3个字段以内。如果需求明确添加字段时把等值查询列放前面范围查询列放中间排序字段放最后其他冗余需求谨慎追加。4.2 联合索引的字段顺序决定生死联合索引的字段顺序问题是索引用上了但还是慢的高发区。比如用户表场景SELECT * FROM users WHERE age 30 AND city 上海;如果你建了idx_city_age(city, age)这条SQL能很快过滤到city上海的数据再进一步筛age30。但如果你建的是idx_age_city(age, city)MySQL只能先按age过滤全部30岁的人再额外筛选上海。逻辑上都能出正确结果但前者过滤得更狠更快rows估算会差好几倍。更麻烦的是排序和分组对顺序的要求。联合索引天然支持从左到右先排序再分类的查询。比如查最近30天每个城市的新增用户数联合索引里把时间、城市分别放在哪将直接决定SQL是否触发Using filesort或者Using temporary。如果排序字段和等值筛选字段是同一张表上的不同字段一个非常有效的设计思路是-- 场景WHERE status 1 AND channel web ORDER BY created_at -- 联合索引可以设计成 (status, channel, created_at) -- 这样status和channel做等值条件created_at天然就是排好序的也就是说把等值条件字段放前面排序字段放最后这是一张万能配方。它让MySQL在索引树里依次精确匹配等值条件最后沿着排序字段的顺序直接输出不额外排序也不产生临时表。4.3 慢不慢还得看数据分布优化器的理性选择有时候索引明明能用优化器就是不用它这不能全怪优化器犯傻它在做成本评估时相当精明。优化器维护着一张关于表的数据统计信息用来估算走索引要扫多少行、回表多少次、每步大概要多长时间。如果它估算走索引的成本比全表扫描还高它就会选择全表扫描。我遇到过最典型的一个场景是小表大索引一张只有两万行左右的配置表优化器判断一个4层的B树来回跳转的成本远大于直接在两万行里顺序扫一遍。这时候哪怕你把索引建得再完美优化器依然会无视它走全表扫描。这类情况人工干涉的空间不大更好的策略是接受现实让配置表保持小而精。还有一种更值得警惕的情况统计信息过期。表里数据经过大量增删改之后information_schema.statistics里的基数信息可能严重失真导致优化器选择了一个错误的执行路径。常见做法是定期执行ANALYZE TABLE让统计信息刷新有时一条SQL突然变慢跑一次这个命令就好了。4.4 深入细节索引条件下推和排序分组的优化机会有两个深度学习过MySQL内部执行原理的人才会主动去用的技术点这里一并放出来可能会对你理解为什么索引建了还是慢有启发。索引条件下推ICP解决的问题是在没有ICP之前联合索引可能只用到第一个字段其余筛选条件要在回表后逐行过滤。MySQL 5.6引入ICP后存储引擎层可以直接根据联合索引中的其他字段做第二次过滤减少回表次数。怎么判断有没有用上ICP看Extra里是否出现Using index condition。如果出现了说明MySQL已经尽量利用索引列做过滤。想更主动一点可以在筛选条件里尽量使用联合索引包含的字段让ICP发挥更大空间。排序分组的极端案例当你的SQL同时有WHERE、GROUP BY、ORDER BY时索引的设计要同时照顾三个动作。前面提到的联合索引顺序策略不只是为了WHERE更是为了让GROUP BY和ORDER BY能在索引顺序上直接完成避免临时表。一个我调过很多次的通用优化套路是-- 问题SQL SELECT user_id, COUNT(*) FROM orders WHERE created_at 2024-01-01 GROUP BY user_id ORDER BY COUNT(*) DESC;这种情况就算有索引也容易让GROUP BY产出临时表因为索引默认是按数据物理顺序排列的而不是按聚合结果排序。一个思路是改变索引结构让GROUP BY字段有序或者接受全表排序的现实业务上做分页处理。如果想同时优化两个动作可以把字段做排列先按user_id分组、再保证时间顺序支持范围条件但代价是你必须接受对COUNT结果排序时的额外损耗。5. 这些经验帮我少走了很多弯路分享给你最后这部分跟大家说说我在实际项目里被问到最多的几个经验性问题和我的处理方法。这次不聊原理了全是实操里边角料级的干货。5.1 慢查询日志和pt-query-digest是排查慢SQL的黄金搭档我在排查慢SQL的时候第一步永远是开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;long_query_time 1意味着超过1秒的SQL都会被记录下来。生产环境建议设到0.5或1秒就行太多会拖累性能。日志文件出来以后手写脚本统计太累我更推荐用Percona Toolkit里的pt-query-digestpt-query-digest /var/log/mysql/mysql-slow.log它会按总耗时、执行次数、平均耗时做排行一眼就能看到哪条SQL是最该被优化的。重要的是它能帮你识别同一形态SQL的不同写法避免你盯着一个文本去搜结果漏掉了一堆类似写法。5.2 千万别为了平时用得上给每列都加索引我见过最生猛的做法是把一张表里几乎每个VARCHAR字段都加上索引理由是以后哪个SQL慢哪个SQL用得上。实际上索引越多写入越慢每次INSERT/UPDATE/DELETE都要同步维护所有索引。在表数据量过千万以后哪怕你所有索引都用上了MySQL也可能因为维护索引的代价出现严重的写锁竞争。这里有个很朴素的判断标准先看慢查询日志确认哪些SQL真的慢再决定给哪个列建索引。没有实际慢SQL表演过的字段一律不加索引这是DBA的基本修养。5.3 不一定非要把慢SQL改到极致也可以让SQL走查询缓存旧版或让业务侧改变查询模式MySQL 8.0之后已经移除了查询缓存很多人还在用老思路指望一个开缓存就能把所有重复查询提速。现实是为了从缓存拿结果每次写入后缓存要失效高并发读写场景下缓存命中率并不乐观。真正值得花时间的往往是搞清楚业务能不能换个查询模式。比如报表统计天天跑一次大GROUP BY与其反复优化这条SQL不如提前算好汇总表让报表直接读汇总结果。这个思路叫预聚合配合索引加速明细查询效果远超死磕单条SQL。5.4 最后一个小技巧每次改完索引都要回EXPLAIN看一遍这是我能给你最诚实的建议不管你根据任何博客、任何经验之谈调整了索引都必须用EXPLAIN验证一下这次改动到底有没有生效。我自己的习惯是改完索引以后立刻跑一遍这个表上最常执行的10条SQL看type、key、rows、Extra四个字段有没有变好。如果没有变好说明要么是SQL写法仍有索引失效的地方要么是优化器认为这步改动不划算。说句实在的MySQL索引优化这件事从来不是建了就完了它是一个循环往复的过程发现慢SQL做EXPLAIN分析执行计划调整索引再次验证。你踩过的坑越多、验证过的组合越多接到为什么建了索引SQL还是慢这类问题时判断得就越快。跟数据库打交道经验就是这么一笔一笔攒下来的。