搞过线上故障排查的兄弟都知道，一条慢 SQL 能把整个服务拖垮。去年我接手过一个订单系统，业务高峰期接口平均耗时飙到 3 秒多，数据库 CPU 直接打满。当时第一反应就是看慢查询日志，结果发现一条统计订单金额的 SQL 跑了 2.8 秒，全表扫描扫了四百多万行。后来只是加了一条复合索引，执行时间直接掉到 30 毫秒。这事让我意识到，MySQL 索引这东西，平时不显山不露水，真到出问题的时候，就是救命的家伙。这也是我写这篇 MySQL 索引实战笔记的原因——把底层原理、建索引的套路、索引失效的坑一次说透，帮你少走弯路。
这篇文章适合谁看？刚入门想搞懂 B+ Tree 到底是个啥的初学者，写 SQL 经常莫名其妙的慢、又不知道怎么优化的后端开发，还有正在准备面试、需要系统梳理索引知识点的候选人。文章不会堆砌概念，而是用实际场景把索引的底层逻辑讲明白，再给你可以直接抄作业的优化方案。
1. 内容整体设计与思路拆解
1.1 先搞清楚索引到底解决了什么问题
索引的本质，用大白话说就是给数据表建目录。你看《新华字典》前面的拼音检字表，先查拼音定位到页码，再翻到那一页找具体汉字，比一页一页翻快得多。MySQL 里没有索引的时候，查询得把整张表的每一行数据都读一遍，也就是全表扫描，数据量一大，磁盘 IO 次数就上去了，慢是必然的。加了索引之后，通过索引结构定位到数据所在位置，磁盘 IO 次数从几万次降到几次，查询速度自然就上来了。
但这里要强调一个很关键的点：索引不是越多越好。每建一个索引，写入数据的时候就要额外维护一颗 B+ 树， INSERT、UPDATE、DELETE 的性能都会受影响。我见过有人为了优化查询，一张表建了十几个索引，结果写入性能崩了。索引就是拿空间换时间、拿写入性能换查询性能的取舍，所以建索引之前一定要想清楚：这个查询是不是高频查询？这个索引能不能覆盖多个查询场景？
1.2 为什么 MySQL 偏偏选了 B+ Tree
市面上常见的索引数据结构有哈希表、二叉搜索树、平衡二叉树、B Tree、B+ Tree 这几种，MySQL 的 InnoDB 存储引擎最终选了 B+ Tree，这不是拍脑袋决定的，而是因为 B+ Tree 的几个特性完美贴合了数据库的应用场景：
第一个特性：矮胖。B+ Tree 的非叶子节点可以存储多个键值，每个节点能分出很多叉。InnoDB 默认的页大小是 16KB，一个三层高的 B+ Tree 就能轻松存储上千万条数据。这意味着什么？意味着大部分查询只需要 2 到 3 次磁盘 IO 就能定位到数据，而磁盘 IO 恰恰是数据库性能最大的瓶颈。
第二个特性：叶子节点有序且串联。B+ Tree 的所有数据都存储在叶子节点，而且叶子节点之间通过双向指针串联成了一个有序链表。这个设计对范围查询（比如WHERE age BETWEEN 20 AND 30）和排序操作特别友好，找到起点之后顺着链表往后遍历就行，不用来回跳转。
第三个特性：非叶子节点只存索引不存数据。每个节点能容纳更多的键值，树的高度更低，IO 次数更少。相比之下，B Tree 每个节点都存数据，同样的空间能存的键值就少，树就更高，查询的 IO 次数就更多。
为了让你更直观地理解，我画一个简易的对比表：
| 数据结构 | 查询效率 | 范围查询 | 磁盘IO次数 | 为什么不适合MySQL |
|---|---|---|---|---|
| 哈希表 | O(1) | 不支持 | 少 | 无法排序、无法范围查询，Hash冲突处理麻烦 |
| 平衡二叉树 | O(log N) | 支持但不高效 | 多，树太高 | 每个节点只存一个键值，高度太高 |
| B Tree | O(log N) | 支持 | 较少 | 非叶子节点也存数据，扇出度不够 |
| B+ Tree | O(log N) | 高效 | 最少 | InnoDB 默认选择 |
之前有面试者问我：既然哈希表查询最快，为什么不用它做索引？我反问他：你写WHERE age > 18的时候，哈希表怎么查？哈希表只能做等值比较，一旦牵扯到范围查询就歇菜了。还有 InnoDB 的自适应哈希索引，那是建立在 B+ Tree 之上的二次优化，底层结构还是 B+ Tree。
1.3 聚簇索引和非聚簇索引的本质区别
InnoDB 的索引在物理存储上分两大类：聚簇索引和非聚簇索引（也叫二级索引、辅助索引）。这个区分很多人学的时候容易绕晕，我用一个例子讲透。
聚簇索引就是主键索引，它的叶子节点直接存储整行数据。MySQL 规定每张表必须有聚簇索引，如果你建表的时候没指定主键，InnoDB 会偷偷找一个非空的唯一索引当主键，实在没有的话，就生成一个隐藏的 ROW_ID 当主键。这带来的直接后果是：你查询主键的时候，找到叶子节点就等于拿到了完整的数据行，不需要额外操作，这叫"一次查询"。
非聚簇索引就麻烦了，它的叶子节点不存完整数据，只存主键值和索引列的值。假设我在username字段上建了一个普通索引，那么执行SELECT * FROM users WHERE username = 'zhangsan'的时候，查询过程是先去 username 的 B+ Tree 里找到 'zhangsan' 对应的主键值，然后再拿这个主键去聚簇索引的 B+ Tree 里找完整数据行。查了两棵树，这就是著名的回表。
回表很伤性能，那怎么避免？两种办法：
一种是覆盖索引，让查询的列完全包含在索引列里。比如索引建在(username, email)上，查询SELECT email FROM users WHERE username = 'zhangsan'，直接在二级索引的叶子节点就能拿到 email，不需要回表。光这一招，我优化过好几个高频查询，响应时间直接砍半。
另一种是索引下推（ICP），MySQL 5.6 引入的特性。拿(age, city)复合索引举例，查询WHERE age > 20 AND city = '北京'，老版本会把所有 age 大于 20 的记录的主键都捞出来回表，再去过滤 city；5.6 之后，在二级索引的内部就把 city 条件过滤掉，回表的次数大大减少。这个特性默认开启，不需要你手动配置，但理解它有助于你明白为什么复合索引要注意列的顺序。
2. 核心细节解析与实操要点
2.1 索引的分类及各自的应用场景
主键索引，前面说过了，聚簇索引的核心载体。建表时用PRIMARY KEY声明，一般建议用自增整数。为什么？因为 B+ Tree 插入数据要维护有序性，自增主键天然有序，每次插入都在最后追加，不需要大量的节点分裂操作。如果用 UUID 这种随机字符串做主键，数据随机插入，B+ Tree 要频繁的页分裂和页合并，写性能会差一个量级。
唯一索引，用UNIQUE KEY声明，主要作用是保证字段值的唯一性，同时辅助查询。注意区别：业务上有唯一性要求（比如用户手机号、身份证号）就用唯一索引，因为数据库层面的约束比应用层判断靠谱得多，不会出现并发下两个订单同时用了同一个订单号这种事故。
普通索引，没有任何约束，纯粹为了加速查询。适用于查询条件频繁、但又不需要唯一性的字段，比如订单表里的user_id、status。
复合索引，也叫联合索引，最考验功底的一种。阿里规范里建议单表索引数不超过 5 个、单个索引字段数不超过 5 个，就是怕复合索引设计过度，反而适得其反。复合索引的核心规则是最左前缀原则，我在下一章详细展开。
全文索引，适用于大文本的模糊搜索。但说实话，业务量大了之后，全文索引的性能不够看，一般直接上 Elasticsearch，MySQL 的全文索引基本只在特殊场景下用。
还有两种特殊的：覆盖索引和虚拟列索引。覆盖索引不是一种独立的索引类型，而是一种优化手段——通过合理设计索引字段，让查询无需回表。虚拟列索引是 MySQL 5.7 引入的，可以在表达式上建索引，不用像以前一样非得把字段加工成新列才能加速。
2.2 复合索引与最左前缀原则
复合索引是面试和实战的重灾区，先记住一条铁律：MySQL 使用复合索引的时候，会从最左边的列开始匹配，跳过任何一列，后面的列全都用不上了。这就是最左前缀原则。
举个例子，建表语句：
CREATE TABLE `orders` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_id` int NOT NULL, `status` tinyint NOT NULL, `create_time` datetime NOT NULL, `amount` decimal(10,2) NOT NULL, PRIMARY KEY (`id`), KEY `idx_user_status_time` (`user_id`, `status`, `create_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;现在来看几个查询能不能走上idx_user_status_time这个复合索引：
场景一：WHERE user_id = 100 AND status = 1 AND create_time > '2024-01-01'。三个条件从左往右依次匹配，能完整用上这个索引，效率最高。
场景二：WHERE user_id = 100 AND create_time > '2024-01-01'。跳过了中间的status，这时候只用了user_id这一个前缀列，create_time的条件无法参与索引过滤，InnoDB 会把所有 user_id 等于 100 的记录捞出来，再逐行过滤 create_time 的条件，这叫 Index Filter。
场景三：WHERE status = 1 AND create_time > '2024-01-01'。这种查询完全用不上索引，因为第一个列 user_id 没出现，直接从最左边就断了。这种情况，老老实实全表扫描，或者单独给 status 建索引。
我把这个规则总结成一句话：复合索引的列顺序，要按照查询条件的出现频率来排，高频字段放前面，范围查询的字段尽量放后面，因为范围查询后面的列会失效。比如上面的例子，如果你的查询基本都是按 user_id 查，然后把 status 当作过滤条件，create_time 用来做范围筛选，这个顺序就是合理的。反过来，如果你查得最多的是时间段，那应该把 create_time 放在最前面。
还有一个容易忽略的点：复合索引其实可以等效为多个"前缀索引"。(user_id, status, create_time)这个索引，实际上能覆盖user_id单列查询、user_id + status两列查询、三列全查这三个场景。所以建复合索引之前，先想想有没有已经存在的复合索引可以顺带覆盖新需求，别重复建。
2.3 explain 命令真的是看家本领
你说你建的索引到底有没有生效，别看猜的，用EXPLAIN看执行计划。这个命令不执行 SQL，只看 MySQL 怎么规划查询路径，可以说是索引问题的照妖镜。下面是 EXPLAIN 输出里的关键字段解读：
type：访问类型，性能从好到差依次是system > const > eq_ref > ref > range > index > ALL。这里直接记住几个关键档位：ALL就是全表扫描，最差；range意味着索引范围扫描，还不错；ref和eq_ref是等值查询走索引，很好；index虽然也走了索引，但很可能是在扫描整个索引树，有时候比全表扫描强不了太多。
key：实际用到的索引名。如果为 NULL，说明没走索引，找原因去吧。
rows：预估扫描的行数。这个值越小越好，它体现了索引过滤性的好坏。如果你发现 key 明明有值，rows 却大得离谱，说明索引的区分度太低，比如性别字段上的索引，扫出来一半数据，这种索引建了等于没建。
Extra：这里信息量很大。出现Using index说明是覆盖索引，舒服；出现Using where说明有回表后的二次过滤；出现Using filesort意味着 MySQL 在内存或磁盘上做了额外排序，是常见的性能杀手；出现Using temporary说明用了临时表，通常伴随 group by 或 distinct 子句，也是优化重点。
给你一个真实排查案例。我优化过一个报表查询：
SELECT user_id, SUM(amount) FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY user_id;当时的表上只有主键 id 和一个(user_id)索引，EXPLAIN 显示 type 是 ALL，Extra 是Using temporary; Using filesort。4000 万的订单表，这么查得跑十几秒。后来我把索引调整为(create_time, user_id, amount)三列复合索引，EXPLAIN 的结果变成了 type 是 range，Extra 是Using index。因为 create_time 条件走了索引定位，user_id 和 amount 都在索引里，后面的 GROUP BY 甚至不需要回表取数据，整个查询直接从 12 秒降到 0.45 秒。这就是覆盖索引加合理索引顺序的威力。
3. 实操过程与核心环节实现
3.1 索引设计的最佳实践流程
我总结了一套建索引的标准流程，每次开发新功能碰到慢 SQL，我都会照着走一遍：
第一步，先找慢查询。开启慢查询日志，设置阈值。线上环境一般设 1 秒：
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';第二步，抓出慢 SQL 后用 EXPLAIN 分析。先看 type 是不是 ALL，再看 Extra 里有没有 Using filesort、Using temporary，有就说明索引设计有优化空间。
第三步，结合业务查询模式设计索引。这一步不能闭门造车，需要拿真实的查询语句来分析。归纳一下这个表上高频查询的 WHERE 条件、ORDER BY 字段、GROUP BY 字段，然后把这些字段按选择性排列。选择性的计算方式是COUNT(DISTINCT 字段) / COUNT(*)，比例越高说明字段区分度越高。比如性别字段的选择性只有 2/1000000，几乎等于 0，这种字段除非和别的字段组成复合索引，单独建索引纯属浪费空间。而 user_id 如果有一百万个不同的值，选择性接近 1，单独建索引完全够格。
第四步，建索引前先测一下预估的改善效果。可以用CREATE INDEX先建上，然后立刻 EXPLAIN 确认执行计划确实变了，再压测一下查询时间。如果建了索引 EXPLAIN 里没反应，八成是 SQL 写法有问题或索引顺序不对，这时候别急着上线，先排查清楚。
第五步，测试验证后正式上线，并持续观测。索引上线后要监控两个指标：一是慢查询数量是否下降，二是写入性能是否明显受损。特别是高并发写入的表，每加一个索引，写入 TPS 就会掉几分。我记得有个交易流水表，加索引之前每秒写 3000 条，加了两个索引之后掉到 2300 条，后来折中了一下，把不常用的索引删了才恢复。
3.2 索引创建脚本怎么写
先看创建索引的标准语法：
-- 建表时直接创建索引 CREATE TABLE `user` ( `id` bigint NOT NULL AUTO_INCREMENT, `user_name` varchar(64) NOT NULL, `phone` varchar(20) DEFAULT NULL, `age` int DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_phone` (`phone`), KEY `idx_user_name` (`user_name`) ) ENGINE=InnoDB; -- 建表后追加普通索引 CREATE INDEX idx_user_name ON user(user_name); -- 创建复合索引 CREATE INDEX idx_user_age_name ON user(age, user_name); -- 创建前缀索引（适合字符串很长的情况，比如文章标题） CREATE INDEX idx_title_prefix ON article(title(20));前缀索引这里多说一句，TEXT、VARCHAR 超长字段上建索引，尤其是做全文搜索不需要完整匹配的时候，指定前缀长度能显著减小索引占用的空间。比如文章标题一般二十个字就够了，建一个title(20)的前缀索引，比整列索引小好几倍。但注意，前缀索引有两个副作用：一是无法用做 ORDER BY 排序，二是无法用做覆盖索引，因为索引里只存了前缀字符串，没有完整值。
再聊聊删除索引。生产环境删索引要挑业务低峰期，因为 InnoDB 在删除索引的时候会重建表，表越大耗时越长，期间会有元数据锁，阻塞写入。我见过一个同事趁大促前删索引，直接在高峰期执行 DDL，结果一张 5000 万行的表重建了快二十分钟，订单写入全被堵住了。所以一定要记住：大表上的 DDL 操作，先用pt-online-schema-change（pt-osc）这类工具做在线变更，工具的流程是先建一张新表结构、同步旧表数据、最后原子切换表名，对线上业务影响极小。
3.3 排序和分组场景下的索引设计
ORDER BY 和 GROUP BY 是索引失效的重灾区，很多人没意识到排序也能走索引。InnoDB 的 B+ Tree 本来就是有序的，如果 ORDER BY 的字段正好是索引的组成部分，MySQL 直接顺着索引顺序扫描就行，不需要额外排序，Extra 里就不会有 Using filesort。
拿订单表举例：
SELECT id, amount, create_time FROM orders WHERE user_id = 100 ORDER BY create_time DESC LIMIT 20;要支持这个查询，索引设计成(user_id, create_time)就特别合适。先通过 user_id 定位到目标记录范围，然后索引内部对 create_time 已经排好序了，直接逆序扫描拿到最新 20 条，效率极高。如果你把索引设计成(create_time, user_id)，MySQL 就没办法用索引完成排序，得先把 user_id = 100 的记录全部捞出来，再在临时表里 filesort，慢不说，文件排序还可能在磁盘上操作。
GROUP BY 走索引的原理类似。GROUP BY user_id, status的时候，如果复合索引是(user_id, status)，MySQL 发现索引排列顺序和分组顺序一致，就可以顺序扫描、顺带统计，连临时表都省了。反过来，GROUP BY status想用上(user_id, status)索引的门都没有，因为最左前缀缺失。所以分组查询的索引设计思路跟排序一样：把 GROUP BY 的字段放在复合索引的靠前位置，并且要和 WHERE 条件字段尽可能复用同一颗索引树。
还有一个高效排序场景——利用索引避免 filesort 的覆盖式查询。我举个例子：
SELECT description FROM articles WHERE user_id = 100 ORDER BY create_time DESC;假如articles表上有一个(user_id, create_time)索引，查询的 description 不在索引里，MySQL 就得回表取 description。千万级文章表里的 user_id=100 的文章可能几百篇，回表几百次还能忍。但如果是ORDER BY create_time DESC LIMIT 5，你以为 MySQL 会先拿索引定位 5 条再回表？实际上 MySQL 需要先把所有 user_id=100 的记录按 create_time 排序好，再取前 5 条回表，因为你不知道哪五篇才是时间上最新的。所以这种场景下，设计(user_id, create_time, description)三列覆盖索引，连回表都省了，速度还能再快一倍。
4. 常见问题与排查技巧实录
4.1 索引失效的八大经典场景
这部分我要着重讲，因为线上问题多半出在这几个场景。每个都配了反例和正例，你可以对照自己的 SQL 排查。
场景一：LIKE 以通配符开头。WHERE name LIKE '%张'，因为最左侧的字符不确定，B+ Tree 从根节点就不知道往哪走，只能放弃索引。但WHERE name LIKE '张%'能走索引，因为开头字符确定，MySQL 会把它转成一个前闭后开的范围区间。
场景二：对索引列做了函数操作。WHERE DATE(create_time) = '2024-01-01'，MySQL 先把每一行的 create_time 都执行一遍 DATE() 函数再比较，函数处理的结果是无序的，索引就废了。正解是WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'，直接落到范围查询。同理，WHERE id + 1 = 10这种在索引列上做算术运算的，一样失效。
场景三：隐式类型转换。这坑踩的人最多。phone字段是 varchar 类型，你写WHERE phone = 13800138000，数字类型的 13800138000 传进来，MySQL 会先把字段转成数字再比较，索引直接废掉。我复盘过一个线上事故，几千万行的用户表因为这种写法被全表扫描。排查完了把 SQL 改成WHERE phone = '13800138000'，耗时从 2 秒多降到 20 毫秒。注意判断标准：MySQL 隐式转换的规则是字段类型转成参数类型，参数是字符串时字段是 varchar 不受影响，参数是数字时字段会转成数字，索引失效。
场景四：OR 连接的非索引字段。WHERE name = '张三' OR age = 25，如果 age 上没有索引，MySQL 只能把整个结果集做全表扫描。正解是给 age 也建上索引，或者把 OR 查询拆成两个查询用 UNION 合并。如果 name 和 age 都有索引，MySQL 会分别走索引再合并结果，性能尚可。
场景五：复合索引不满足最左前缀。前面已经详细说了，这是最常见的"索引建了但不生效"的原因。一条查询用不上复合索引，别急着骂 MySQL，先看看条件是左边的列开始的吗。
场景六：范围查询右侧的列失效。复合索引(age, name)，你写WHERE age > 20 AND name = '张三'。age 的范围条件会把 age 大于 20 的记录都扫出来，这些记录的 name 在索引里虽然有序，但那是基于 age 分组后的局部有序，没法直接定位， name 条件只能作为 Index Filter 在二级索引内部过滤。所以范围条件右边的列放进去建复合索引，意义不大。
场景七：NOT IN、NOT LIKE、<>操作符。这类否定条件的过滤性往往很差，优化器估算下来觉得走索引还不如全表扫描划算，就放弃索引了。能改写成等值或范围查询就改写，改不了就认命。
场景八：数据量很小。表里就几十条记录，优化器一算发现直接全表扫描比走索引的 IO 次数还少，就不选索引了。这种情况不叫失效，是优化器的正常选择。别纠结，数据量涨上来之后它自己会走。
我把这些场景整理成一个速查表，贴代码里当注释都好用：
| 失效场景 | 反例 | 正解 |
|---|---|---|
| LIKE 前置通配符 | WHERE name LIKE '%张' | 改词库、上 ES，或确保前置字符确定 |
| 索引列函数运算 | WHERE DATE(create_time)='2024-01-01' | 改写为范围查询 |
| 隐式类型转换 | WHERE phone=13800138000 | 参数加引号保持字符串类型 |
| OR 含非索引列 | WHERE name='张三' OR age=25 | 给 age 建索引或拆 UNION |
| 复合索引断列 | WHERE status=1 AND create_time>... | 调整索引列顺序 |
| 范围右侧失效 | WHERE age>20 AND name='张三' | 范围和等值字段谨慎排布 |
| 否定操作 | WHERE status<>1 | 改写为等值 |
| 小表全扫描 | 表记录数很少 | 无需处理 |
4.2 隐式类型转换踩坑实录
这个坑真的值得单独拿出来写。有一次排查线上用户登录超时问题，登录接口偶尔会查到很慢，慢查询日志里出现了用户表的全表扫描。当时 SQL 长这样：
SELECT id, password_hash FROM users WHERE account = 13512345678;细看表结构，account 字段是 varchar(20)，存的是手机号字符串。问题出在传参类型：JDBC 预编译的时候没有指定参数类型，框架默认当 Long 传进去了。MySQL 拿到数字参数后，因为要跟字符串列比较，就把字符串列转成数字，对每一行做 CAST(account AS UNSIGNED)，索引整个废掉。
排查的时候用EXPLAIN一看，type 确实是 ALL，然后我把参数强制改成字符串：
SELECT id, password_hash FROM users WHERE account = '13512345678';EXPLAIN 立刻变成 ref，执行时间从 800 毫秒降到 3 毫秒。后来我在代码里给 PreparedStatement 的参数加上了setString调用，这个坑就再也没出现过。想彻底避开这个坑，记住一句话：WHERE 条件里，参数类型要跟字段类型严格一致。
4.3 实战问题排查速查表
我把自己在排查索引问题时候的排查顺序整理成一个速查表，你可以保存下来。遇到一个慢 SQL，按这个顺序走一遍，基本能定位 90% 的问题：
| 排查步骤 | 具体操作 | 预期结果 |
|---|---|---|
| 第一步 | 开启慢查询日志，找到慢 SQL 语句 | 明确排查对象 |
| 第二步 | 对慢 SQL 执行 EXPLAIN | 查看 type、key、rows、Extra |
| 第三步 | 如果 type 是 ALL，确认 WHERE 条件列上是否有索引 | 没有索引，考虑创建 |
| 第四步 | 如果有索引但没用上，检查是否符合上述失效场景 | 改写 SQL 写法 |
| 第五步 | 如果走了索引但 rows 很大，检查索引选择性 | 选择性低的字段不适合单独建索引 |
| 第六步 | 看 Extra 里的 Using filesort / Using temporary | 调整索引列的顺序和覆盖字段 |
| 第七步 | 使用 SHOW INDEX FROM table_name 查看实际索引定义 | 检查冗余索引，及时清理 |
关于冗余索引，再补充几句。(user_id, status)和(user_id)这两个索引，前者已经覆盖了后者的功能，后者就是冗余的。每次写入的时候，InnoDB 要同时维护两颗 B+ 树，白白浪费性能和空间。我在做索引治理的时候，经常发现一张表上两三个索引存在包含关系，合并之后写入性能回来了不少。检查冗余索引可以用SHOW INDEX FROM table_name，对比索引字段前缀，凡是能被别的索引的前缀包含的，就标记为待删除候选。
4.4 一个真实案例：从 3 秒到 30 毫秒的索引调优全记录
最后分享一个完整的调优案例，把前面所有知识点串一遍。
背景：一个电商后台的商品列表页，运营在筛选数据时经常卡死。核心 SQL 是这样的（我做了简化，但结构不变）：
SELECT id, product_name, price, stock FROM products WHERE category_id = 1024 AND status = 1 AND create_time >= '2024-05-01' ORDER BY sales_volume DESC LIMIT 20;商品表有大约 2200 万行，初始索引只有一个主键 id，没有任何二级索引。我用EXPLAIN看执行计划，type 是 ALL，Extra 里有 Using filesort，这谁扛得住。
第一步，分析查询特征：WHERE 有三个条件，其中 category_id 和 status 是等值匹配，create_time 是范围匹配，ORDER BY sales_volume 需要单独考虑。第二步，设计复合索引：把等值字段放前面，高频筛选的 category_id 放最前面，status 跟在其后，再把 create_time 放在第三位。ORDER BY 的 sales_volume 单独建一个索引排不了，因为查询已经用多列索引过滤了，排序没法跟索引同路，只能另想办法。
初步索引设计：(category_id, status, create_time)。执行计划立刻变成 range，rows 从 2200 万降到了约 8 万，Extra 里已经没有 Using filesort 了，因为 LIMIT 20 走的是索引有序扫描后排序的优化路径，查询时间降到了 350 毫秒左右。
第三步，进一步优化回表。查询里要返回 product_name、price、stock，但这些字段不在索引里，单独靠索引定位后回表。在商品表这种每行数据都很大的表上，回表 8 万行也挺吃力的。干脆把这三个字段加进索引，设计成(category_id, status, create_time, product_name, price, stock)，变成一个完整的覆盖索引。注意，这只在查询字段非常固定的时候才值得这么做，如果业务方时不时要加一个字段，覆盖索引就失去了意义。这次加完之后 EXPLAIN 显示 Extra 是 Using index，查询时间直接降到 30 毫秒左右。
最终效果：从 3.2 秒压到 30 毫秒，快了 100 倍。上线之后观察了一周，这张表的写入速率没有因为新增索引而明显退化，因为商品表本身写入频率不高，属于低频写高频读的表，覆盖索引付出的写入代价完全能接受。
5. 索引维护与进阶优化心得
5.1 索引的日常健康检查
索引建好不代表一劳永逸，数据不断增删改，索引也会出现碎片。这方面的核心指标是索引基数（Cardinality），它表示索引列的去重值的估个数。如果基数明显低于实际值，优化器就走偏，选错索引甚至放弃索引。办法是重新分析表，让优化器刷新统计信息：
ANALYZE TABLE products;另外一张表数据越删越多，索引碎片率会上升。用SHOW TABLE STATUS LIKE 'products'能看到 Data_free 字段，这个值越大说明碎片空间越多。碎片太多的时候，可以执行OPTIMIZE TABLE products重建表。但这条命令和前面提的一样，锁表锁得厉害，建议用在线工具做。日常维护的频率看表的更新强度，更新频繁的表建议每两周 ANALYZE 一次，每个月检测一次碎片率。
5.2 索引与连接的协同优化
JOIN 查询的索引设计和单表不同，核心原则是：驱动表的小结果集走全表扫描没关系，被驱动表上的连接字段必须建索引。拿用户订单关联查询举例：
SELECT u.user_name, o.order_no FROM users u INNER JOIN orders o ON u.id = o.user_id WHERE u.reg_time >= '2024-01-01';users 表是驱动表，经过过滤后可能只剩几千行；orders 表 4000 万行，连接条件是 o.user_id，所以 orders.user_id 上必须建索引。如果没建，MySQL 每拿一条用户的 id，就要全表扫描一次 orders 去匹配，几百次全表扫描，系统直接死给你看。
还有一种情况，被驱动表连接字段已经建了索引，但 EXPLAIN 显示 type 是 ALL。这时候检查一下驱动表是不是选错了。MySQL 默认会选择小结果集当驱动表，但有些复杂的 SQL 优化器会选错。可以用STRAIGHT_JOIN强制指定连接顺序，不过这是最后的杀手锏，一般先尝试改写 SQL 或调整过滤条件。
5.3 讲给开发同学听的索引规范
最后整理几条我们团队内部强制执行的索引开发规范，都是血泪教训换来的：
规范一：新表必须显式指定主键，且优先使用自增 id。别依赖 InnoDB 隐藏的 ROW_ID，你没法用它做数据定位，逻辑删除和分页都很难搞。
规范二：每张表的索引数量控制在 5 个以内。索引数量超过 5 个之后，写入性能下降非常明显，而且优化器选择索引的复杂度大幅上升，经常出现"选了看起来合理但实际很慢的索引"的怪现象。
规范三：WHERE 里等值条件的字段放复合索引前面，范围条件放后面。这个排布能让等值字段发挥最大过滤作用。
规范四：禁止在索引列上使用函数、表达式、隐式类型转换。SQL 审查的时候这几个点逢查必报。
规范五：唯一性约束用唯一索引，在数据库层面兜底，别只依赖应用层判断。我之前遇到过业务方用代码判断唯一性，并发一高就重复插入，后来加了唯一索引，问题秒解。
规范六：大表加索引走在线 DDL 工具或平台，禁止直接 ALTER TABLE。这条已经强调过两次了，但它值得再强调一次。
把这几条规范落到平时的开发评审中，比事后优化省心得多。索引不是"出了问题再补"的东西，而应该从建表开始就当成设计要求来对待。根据我个人经验，最好的索引是和你业务查询模型完全匹配的那个，而不是你以为能覆盖一切的万能索引——后者往往一个都不好使。
