凌晨两点被电话吵醒值班同事说线上一个报表查询跑了几十秒没出来数据库 CPU 直接飙到 90% 多。登录进去一看一条带三个条件、两个 JOIN 的 SELECT执行计划里 type 是 ALLrows 估算一百多万行典型的连索引都没走。后来加了联合索引、把 SELECT 需要的字段都塞进索引查询从 40 秒降到 80 毫秒。这种经历干过后端的人多少都遇到过几回MySQL 索引这个东西平时不觉得多重要一出慢 SQL 就恨不得把它原理背下来。这篇文章不堆砌文档就按我实际排查问题、做索引优化时的完整思路来写。从 B 树为什么能扛住千万级数据到联合索引怎么设计、EXPLAIN 怎么读懂再到慢查询定位和索引失效的常见坑最后用一个真实案例把从 40 秒到 80 毫秒的全过程拆开讲一遍。适合被慢 SQL 折磨过的后端开发、DBA 或者刚接触 MySQL 调优的读者照着思路去排查自己的库大概率能少走不少弯路。1. 索引的本质先搞清楚它到底在加速什么1.1 数据库索引为什么默认选择 B 树我刚接触索引的时候也困惑过为什么不直接用二叉树或者哈希表非要用 B 树这种又高又瘦的结构。后来自己画了几张图把数据量和磁盘 IO 摊开算了一笔账才算真正想明白。先说哈希索引。哈希表的查询复杂度是 O(1)单条等值查询确实快得离谱但它有两个硬伤。第一哈希索引不支持范围查询because 哈希函数把键值打散之后相邻的键在存储位置上没有任何关联你查age BETWEEN 18 AND 30它只能全扫。第二哈希索引不支持排序ORDER BY也走不了。生产环境里纯等值查询的场景太少了大多数业务都离不开范围和排序所以哈希索引只能是辅助角色。再说二叉搜索树。它支持范围查询和排序但在数据量大的时候会有问题。假设数据插入顺序是递增的二叉树会退化成一条链表查询复杂度从 O(log n) 直接变成 O(n)比全表扫描还慢。用红黑树或 AVL 树能解决退化成链表的问题但树的高度还是太高了。InnoDB 存储引擎一次 IO 默认读取 16KB 的页如果每个节点只存一个键值两千万条数据大概需要二十多层也就是说每次查询至少要从磁盘读二十多次这个 IO 成本实在扛不住。B 树把这个问题解决得很巧妙。它每个节点能存很多个键值一个 16KB 的页可以放下上千个键三层高度就能支撑千万级数据。更关键的是B 树的数据全部存储在叶子节点叶子节点之间通过指针相连形成了一个有序链表。这意味着不仅等值查询快范围查询也能像翻链表一样顺序扫过去排序操作在很多时候直接就免了。MySQL 选择 B 树作为默认索引结构本质上是为磁盘 IO 优化的结果它把查询时的树高度压到三四层也就是最多三四次磁盘 IO 就能定位到数据这个代价对绝大多数业务来说都可以接受。1.2 主键索引、二级索引与回表明白了 B 树接下来要搞清楚 InnoDB 里索引和数据是怎么组织在一起的。InnoDB 是聚簇索引组织表表里的数据本身就是一个以主键为索引键的 B 树叶子节点存的是整行数据。所以主键索引也叫聚簇索引你建表时定的主键决定了数据在物理磁盘上的组织顺序。二级索引也就是我们平时手动创建的普通索引是另一棵 B 树它的叶子节点不存整行数据只存索引键和主键值。这个设计有两层意思。第一如果通过二级索引查数据先在这棵 B 树里找到对应主键再到主键索引树里回查一次这个动作就是“回表”。第二如果二级索引本身已经覆盖了当前查询需要的所有字段那就不需要再去主键索引树回表了这就叫“覆盖索引”。回表这个操作本身有成本虽然主键查找很快但每回表一次就是一次额外的 B 树检索如果结果集有一万行就要回表一万次这个 IO 开销就很可观了。所以优化 SQL 时有一个很实用的思路尽量把查询需要的字段都放进索引里让索引“覆盖”查询。比如你有一个商品表经常按下单时间区间查订单金额总和那建一个(order_time, amount)的联合索引查的时候从索引里就能拿到 amount完全不需要回表。注意主键索引和数据行是绑定在一起的所以 InnoDB 表设计时要尽量用自增整数或雪花 ID 这类有序值做主键。如果主键是 UUID 这种随机字符串新插入的数据会随机落在树的中间位置触发大量的页分裂和页重排写入性能和新数据占用的物理空间都会变差。2. 索引设计与创建的实战要点2.1 建索引前必须想清楚的三件事建索引之前先别急着写CREATE INDEX想清楚三个问题再动手。一是这个查询走索引到底能不能减少数据扫描量。比如一张统计表只有几千行全表扫描也就几次磁盘读这时候建索引带来的收益微乎其微反而白占空间、拖慢写入。一般来说当表数据量超过十万行并且查询条件能过滤掉绝大多数行时索引的性价比才明显。二是这个索引会不会被高频写入路径拖累。每建一个索引InnoDB 在插入、更新、删除时都要额外维护一棵 B 树的节点变化。索引建得越多写入放大越严重。业务上读多写少的场景可以适当多建索引写多读少的场景则要克制。我见过一个账号流水表因为每个开发都按自己的查询习惯加索引最后积累了十几个索引结果一条简单的INSERT都要花十几毫秒妥妥被索引拖垮。三是区分度够不够。如果一列只有两个不同值比如性别那索引的选择性就很差优化器算一下发现用索引扫描和全表扫描的代价差不多干脆不用索引。区分度可以用SELECT COUNT(DISTINCT column) / COUNT(*)来看值越接近 1 越好。一般低于 0.1 的列除非和其他列组成联合索引否则单独建索引意义不大。2.2 联合索引怎么设计最左前缀规则的另一面联合索引是日常优化里最常用也最容易用错的东西。它的底层结构是一棵 B 树排序规则是先按第一列排序第一列相同再按第二列排序以此类推。这决定了它最核心的一个规则查询条件必须从联合索引的最左列开始连续匹配否则索引就失效。网上管这个叫“最左前缀原则”。我见过最多的菜鸟错误是建了(user_id, status, create_time)这个联合索引然后写一条WHERE status 1 AND create_time 2024-01-01的查询发现压根没走索引于是跑来问为什么。原因很简单查询跳过了第一列 user_id直接咬住索引的第二、三列B 树的排序规则决定了它无法沿着第二列快速定位只能放弃这个索引。联合索引的列顺序要怎么排我总结了一个简单粗暴的优先级先把等值查询的列放前面再把范围查询的列放后面。原因是 B 树在遇到第一个范围条件时后面的列就没办法继续用于等值定位了。举个例子如果你经常查WHERE user_id 123 AND create_time BETWEEN ...那(user_id, create_time)就是合理的顺序而(create_time, user_id)会让 create_time 的范围条件成为第一个范围判断user_id 反而无法参与后续精确定位。当然这个规则不是绝对的。如果某个列区分度特别差即使它是等值条件也不一定要放在最前面因为它过滤不掉多少行。这时候要拿优化器的判断来验证最简单的方法就是建完索引后执行 EXPLAIN看 key_len 是否用到了你期望的列。key_len 越长说明实际使用的索引列越多联合索引利用率越高。2.3 索引下推MySQL 5.6 以后默认开启的性能 buff提到联合索引不能不提索引下推Index Condition Pushdown简称 ICP。这个特性从 MySQL 5.6 就开始默认开启了但很多做了几年开发的人居然不知道属实可惜。索引下推解决的是什么问题呢假设你有一个联合索引(city, age)执行查询WHERE city 杭州 AND age 20。在没有 ICP 的旧版本里InnoDB 会因为 city 命中了索引把 city 为杭州的所有主键都回表查出来然后在服务层再过滤 age 20。这意味着很多 age 不满足条件的行也被白白回表了一次多产生了大量随机 IO。开启 ICP 之后存储引擎在读取二级索引的时候就顺便把 age 20 这个条件一起判断了只有满足条件的索引记录才回表。这相当于把 where 条件提前到索引扫描阶段大大减少了回表次数。你不需要做什么额外操作只要确认索引里包含了 where 条件中用到的列即可MySQL 会自动把能下推的条件都下推下去。提示判断一条 SQL 有没有用上索引下推看 EXPLAIN 输出的 Extra 字段里有没出现Using index condition。如果出现了这个索引的查询条件已经不仅仅用在了“定位”上还用于“过滤”了整体效率会好很多。3. 慢 SQL 排查从 EXPLAIN 到索引失效3.1 慢查询日志怎么开怎么高效捞优化索引的前提是先找到慢 SQL慢查询日志是排查的第一入口。MySQL 里默认是关闭的可以在配置文件[mysqld]段下设置slow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1 log_queries_not_using_indexes ONlong_query_time的单位是秒我一般建议线上环境先设成 0.5 或者 1跑一段时间看看有多少慢查询有需要再收紧。log_queries_not_using_indexes这个参数会把没有走索引的查询也记到慢日志里即使它执行时间不长也值得打开因为全表扫描就像一个潜伏的地雷数据量一旦涨上来就会爆。拿到慢日志之后用mysqldumpslow工具可以先粗筛一遍它能按照查询耗时或扫描行数来聚合相似的 SQL。不过这个工具只能做聚合统计真要分析单条 SQL 的执行计划还得上 EXPLAIN。3.2 EXPLAIN 结果怎么看关键字段逐个拆解EXPLAIN 是 MySQL 优化器给出的执行计划报告说白了就是告诉你“它打算怎么执行这条 SQL”。里面字段很多但真正要重点看的就几个。type是效率的直观体现性能从好到差大致是const、eq_ref、ref、range、index、ALL。ALL就是全表扫描必须警惕index意味着虽然扫的是索引树但要遍历整棵树代价也不小range是范围扫描可以接受ref、eq_ref、const是走等值索引查询属于比较理想的状态。key显示实际用到的索引key_len表示索引使用的字节数。rows是优化器预估要扫描的行数这个数字直接反映索引的过滤效果通常越大越慢。Extra里经常出现的几个值也值得注意Using filesort表示排序没走索引MySQL 要额外在内存或磁盘里排序Using temporary表示使用了临时表常见于 GROUP BY 或 DISTINCTUsing index是覆盖索引扫描属于加分项Using index condition说明用上了索引下推。在实际排查里我一般先看type是不是 ALL再看rows的估算量然后看Extra里有没有 filesort 或 temporary最后判断key用到的索引是不是最优。把这几个字段串起来一条慢 SQL 的病灶基本就能定位了。3.3 索引失效的八种常见场景索引失效的问题在开发面试里高频出现在线上问题里更是家常便饭。我根据这几年踩过的坑列一份最常见的高危清单。在索引列上做函数运算。比如WHERE DATE(create_time) 2024-01-01就算 create_time 有索引也用不上。正确写法是WHERE create_time 2024-01-01 AND create_time 2024-01-02让索引走范围扫描。隐式类型转换。隐式转换很隐蔽例如WHERE phone 13800138000而 phone 是 varchar 类型MySQL 会把字符列转成数字来比较导致索引失效。记住一点字段是什么类型查询参数就传什么类型。模糊查询左匹配。LIKE %关键词%这种前置百分号没法利用索引排序属于必然全扫换成LIKE 关键词%则能走 range 扫描。OR 连接非索引条件。WHERE id 1 OR age 18如果 age 上没有索引优化器为了合并结果集可能放弃主键索引走全表扫描。也写过 UNION 语句拆开通常能救回来。联合索引不满足最左前缀。前面讲过了查询条件里得从联合索引第一列开始连续命中跳列或断列都会失效。NOT IN、、!这些否定操作。这类条件往往让优化器觉得扫描成本比走索引更低尤其是当否定条件覆盖了大量行时。真要优化得看业务能不能改写成IN或范围条件。在索引列上进行隐式字符集排序规则不匹配。这种情况多出现在多表 JOIN 时两个表的连接字段字符集不一致MySQL 必须做转换索引就废了。优化器认为全表扫描更快。当表数据量很小或者索引选择性太差比如性别列优化器会主动弃用索引。注意上面这份列表不是绝对的。比如在索引列上用函数在某些特定版本和特定场景下也可能走索引比如前缀索引。所以最可靠的判断标准始终是跑一次 EXPLAIN看 type 和 key 的实际情况不要让经验主义代替实测。4. 优化实战一个典型订单表查询的调优全过程4.1 问题现场生产环境的报表查询为什么会那么慢今年年初帮一个电商客户排查过一个典型案例订单表t_order存量大概一千两百万行结构简化后是这样的CREATE TABLE t_order ( id BIGINT NOT NULL AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, status TINYINT NOT NULL DEFAULT 0, channel VARCHAR(16) DEFAULT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;报表系统有一条核心查询逻辑大致是统计某个用户在某段时间内、某个渠道下的订单总金额和单数SELECT COUNT(*) AS order_count, IFNULL(SUM(amount), 0) AS total_amount FROM t_order WHERE user_id 123456 AND channel app AND create_time 2024-01-01 AND create_time 2024-03-01;这条 SQL 在凌晨跑批时要花 40 秒左右不仅慢还把从库 CPU 打满导致其他业务受影响。我登录数据库先看了慢日志然后直接对这条 SQL 执行 EXPLAIN结果 current type 是 ALLrows 估算一千一百万Extra 是 Using where。这就意味着它在对整张表做全表扫描把一千多万行数据逐个丢给 where 条件去过滤不慢才怪。4.2 一步步调优从全表扫描到索引覆盖第一反应自然是给 user_id、channel、create_time 建一个联合索引。但列的顺序要想清楚理论上前文说过等值条件放前面范围条件放后面。这里 user_id 和 channel 都是等值create_time 是范围所以索引设计为ALTER TABLE t_order ADD INDEX idx_user_channel_time (user_id, channel, create_time);建完索引后再跑一次 EXPLAINtype 从 ALL 变成了 rangekey 使用到了idx_user_channel_timerows 从一千一百万降到了几百行。效果立竿见影查询从 40 秒降到了 200 毫秒左右。按说到这里已经能交差了但我想再压一压因为报表这个查询还会按天跑很多次能省一点是一点。仔细一看 EXPLAIN 的 Extra还有Using index condition说明虽然索引过滤到了几百行但这几百行还得回表去取 amount 字段做 SUM。回表量不大可如果有几十个用户同时跑报表累积 IO 也不少。我顺手把 amount 也加进了索引尾部做成覆盖索引ALTER TABLE t_order ADD INDEX idx_user_channel_time_amount (user_id, channel, create_time, amount);这里覆盖索引的写法其实就是在联合索引里多塞一个查询需要的列让二级索引树直接提供 amount 的值查询不需要回表。改完之后再看 EXPLAINtype 还是 rangekey 换了新索引Extra 变成了Using index。这就意味着这次查询的全部数据都从索引树里拿一次回表都不会发生。4.3 优化结果对比与效果验证最终结果对比如下阶段typerows 估算查询耗时优化前无索引ALL约 1100 万约 40 秒第一次优化联合索引range约 300 行约 200 毫秒第二次优化覆盖索引range约 300 行约 80 毫秒从 40 秒到 80 毫秒提升了差不多 500 倍最核心的功臣就是联合索引加覆盖索引。另外我还做了一件事把原表里刚才建的idx_user_channel_time直接删掉了。因为新索引idx_user_channel_time_amount的最左前缀和原来的完全一致老索引就是纯冗余留着只会增加写放大和占用存储空间。清理完这两个索引之后再加一层校验拿一个月的历史数据做抽样回归确认结果一致才放心。经验加索引时一定检查一下现有索引里有没有某个索引的最左前缀包含了新索引如果有旧索引就是多余的。每多一个索引写入就要多维护一棵 B 树这种冗余在千万级大表上会成倍放大开销。5. 索引使用中的常见坑与避坑清单5.1 深分页为什么还是慢limit offset 的隐藏代价很多人在后台管理列表页遇到过这样的问题数据量一大翻到第 100 页就开始卡顿。页面查询长这样SELECT * FROM t_order ORDER BY id LIMIT 100000, 20;从执行计划上看它确实走了主键索引type 是 rangerows 也正常但耗时就是高得离谱。问题出在 LIMIT 的实现方式上MySQL 会把从第 0 行到第 100019 行全部扫出来然后丢弃前 100000 行只把最后 20 行返回给客户端。前面白白扫的那十万行虽然走索引很快但依然有额外 IO 成本。优化思路通常是改成“基于游标的分页”也就是记录上一页最后一条记录的 id下一页查询直接用WHERE id 100000来拿数据SELECT * FROM t_order WHERE id 100000 ORDER BY id LIMIT 20;这样 MySQL 可以直奔目标位置扫描行数从十万行直接降到 20 行。不过这种方式对业务有一个要求就是排序的字段必须是唯一的否则可能会漏数据。如果排序字段不是唯一列可以退一步用(create_time, id)联合排序并记录上一页最后一条的(create_time, id)组合值来做游标。5.2 排序与分组走索引的几个前置条件ORDER BY和GROUP BY想走索引也有讲究。首先是排序字段和查询条件的组合必须满足最左前缀规则。比如索引是(user_id, create_time)那WHERE user_id 1 ORDER BY create_time就能用到索引排序不会产生 filesort。但如果你写WHERE create_time 2024-01-01 ORDER BY user_idMySQL 就要额外排序了因为 user_id 在联合索引里是第一列create_time 是范围条件order by 的列顺序和索引键顺序已经不一致了。排序方向也很关键。MySQL 8.0 之前对多个字段排序方向混搭不太友好比如ORDER BY a ASC, b DESC就算 a、b 都在索引里也可能触发 filesort因为索引本身是全部按升序排的。MySQL 8.0 开始支持降序索引可以建INDEX idx_a_b (a ASC, b DESC)但在老版本里几乎无解只能考虑业务上绕一下或者接受文件排序。GROUP BY的原理其实是在分组前先排序所以只要分组字段满足最左前缀并且顺序与索引一致也能避免临时表。比如GROUP BY user_id, channel如果索引是(user_id, channel)那直接从索引顺序扫就能完成分组Extra 里不会出现Using temporary。5.3 索引生命周期管理定期体检比救火更重要优化完成之后还要有一个长期的维护动作因为索引不是建完就一劳永逸的。业务在变查询模式在变有些索引慢慢就没人用了而写放大还在持续伤害数据库。我建议每季度做一次索引体检重点看以下几项。一是检查是否有冗余索引。可以用sys.schema_redundant_indexes这张系统视图来查MySQL 官方运维工具包里的工具也能生成分析报告。比如前面提到(user_id, channel, create_time)和(user_id, channel, create_time, amount)同时存在前者就是后者的子集可以删掉。二是看索引的实际使用频率。MySQL 的performance_schema.table_io_waits_summary_by_index_usage表会记录每个索引的读写次数。如果某个索引的读取次数长期为 0说明它基本没被用上可以考虑删除。但确定删除前一定要先离线保存一份建表语句以防业务某个角落还有依赖。三是关注写入放大。大表加索引和删索引本身都是一种 DDL 操作在 MySQL 8.0 之前做 DDL 会锁表线上业务会瞬间卡住。建议用工具去处理在线 DDL比如使用 Percona Toolkit 里的工具或者在业务低峰期执行并设置合理的锁等待超时时间。最后分享一个我在实际工作中亲测有效的小技巧每次优化完一条 SQL不要只记录“改了什么”要把改之前的执行计划、耗时、改之后的执行计划、耗时都存到团队的 Wiki 里。积累几十个案例之后你再遇到新问题基本上扫一眼 SQL 就能猜到问题出在哪这也是老手和新手之间最大的差距来源。索引优化没有银弹靠的就是案例积累和每次追根究底的习惯。