约 8 分钟读完 · 更新于 2026-10-21 · 文中每条都能在下面的配套工具里动手验证
索引在做什么：从全表扫描到 B+ 树查找
没有索引时，数据库只能一行一行读表并逐行判断条件，10 万行就是 10 万次判断。索引是一棵按列值排序的 B+ 树：叶子节点按键值有序并用链表串起来，内部节点只存键。查找时先在树里定位到目标值所在的区间，再沿叶子链表读取相邻行——1000 万行的表和 1000 行的表，定位成本几乎一样。范围查询也能吃到红利，因为区间内的行在叶子链上是物理连续的。代价写在别处：每个索引都是一棵额外的树，写入时必须同步维护，索引越多写越慢、占的空间越大。
EXPLAIN 先看哪三列
EXPLAIN 输出一堆字段，但只有 type、rows 和 Extra 决定性能走向。先看 type 判断访问方式，再看 rows 估算要读多少行，最后看 Extra 有没有 USING INDEX、USING TEMPORARY、USING FILESORT 这三个关键词。只要出现 USING TEMPORARY 且后面跟着 USING FILESORT，排序就已经落到磁盘上了，再大的索引也救不回来。
| type 值 | 含义 | 性能判断 |
|---|---|---|
| const | 主键或唯一索引上的等值查询，最多一行 | 最好，不用优化 |
| eq_ref | 连接时对每行用主键/唯一索引精确匹配一次 | 很好 |
| ref | 等值条件命中普通（非唯一）索引，可能多行 | 可以接受 |
| range | 索引上走了范围：>、<、BETWEEN、IN 的一部分 | 可接受，注意范围后的列用不上索引 |
| index | 扫描整棵索引树（比全表快一点，但仍是全扫） | 偏慢，通常是缺合适的索引 |
| ALL | 全表扫描 | 该建索引，或先缩小过滤范围 |
联合索引的列序规则：最左前缀
建了索引 (a, b, c) 之后，只有查询条件从最左列开始连续使用，才能走到这个索引。这就是最左前缀规则。查 a 能用，查 a 和 b 能用，查 b 单独就完全用不上。原因是索引内部先按 a 排序，a 相同再按 b 排序；只给 b 条件时，全表里 b 的值是交错散落的，结构上无法定位。列序安排的经验法则是：等值条件在前、范围条件在后、排序与分组列尽量靠后。
WHERE user_id = 7 → 用得上（索引第 1 列） WHERE user_id = 7 AND created_at > NOW() - INTERVAL 1 DAY → 用得上（第 1、2 列） WHERE user_id = 7 AND status = 1 → 用不上第 3 列，created_at 断了 WHERE created_at > NOW() - INTERVAL 1 DAY → 完全用不上（缺最左列） WHERE status = 1 → 完全用不上
索引 (user_id, created_at) 低效写法：WHERE created_at > ? AND user_id = ? 范围在最左，user_id 用不上索引 高效写法：WHERE user_id = ? AND created_at > ? 两个条件都在索引上
SELECT user_id, status FROM orders WHERE user_id = 7; 索引 (user_id, status) → Extra 出现 USING INDEX，全在索引里读完，不用回表 索引 (user_id) → Extra 出现 Using where，需要回表再取 status 注意：SELECT * 会让覆盖索引失效，因为 * 需要的列不在索引里
索引失效的六种典型写法
- 在列上做函数或运算：WHERE DATE(created_at) = '2026-03-01' 会让 created_at 上的索引失效，改成范围条件 created_at >= '2026-03-01' AND created_at < '2026-03-02'。
- 隐式类型转换：字段是 varchar 却传了数字（WHERE id = 123，id 是字符串列），类型不匹配时数据库只能逐行转换，索引用不上。
- 前导模糊匹配：LIKE '%abc' 无法使用索引，LIKE 'abc%' 可以。
- OR 连接的条件里有一侧没有索引：优化器往往退化成全表扫描，可以拆成 UNION ALL 各自走索引。
- 选择性太低：gender 只有两三个值，性别列上建索引等于没有，优化器会判断全表扫描更划算。
- 优化器统计信息过期：数据量变化大但没重新收集统计信息时，它可能选错索引，用 ANALYZE 更新后再看。
建几个索引才够：写多的表宁少勿多
一个经验判断：读多写少的表（配置表、字典表、维表）可以按查询模式多建几个索引；写多的表（订单、日志、流水）要克制，因为每插一行，所有索引的 B+ 树都要分裂或合并，还可能触发页分裂和随机 IO。真正需要判断的是「这条索引值不值」——看它的选择性：区分度高的列（几乎不重复，比如订单号、用户 ID）适合建索引，低区分度的列（状态、类型、性别）单独建不如放进联合索引的后半段。
索引也解决不了的三种慢
- 深分页：LIMIT 100000, 20 会让数据库先扫描并丢弃前 10 万行。改成 WHERE id > 上一页最后的 id ORDER BY id LIMIT 20，用主键当游标，代价与页码无关。
- 不等于条件的占比过高：WHERE status != 1 命中 90% 的行，此时走索引反而比全表扫描多一层查找成本，优化器选全表扫描是对的。
- 排序与分组落在非索引列：ORDER BY created_at 在没有索引的列上，就是 USING FILESORT；数据量大时必然落磁盘，能加索引就加，不能加就要减少返回列和行数。
常见问题
EXPLAIN 里type 和 rows 分别看什么？
type 看访问方式，从好到差是 const、eq_ref、ref、range、index、ALL，出现 ALL 或 index 基本就是没走上合适的索引；rows 是优化器估算要扫的行数，估算值和真实值差一个数量级说明统计信息过期，先跑 ANALYZE 再看。
联合索引的列顺序怎么定？
按最左前缀规则：等值条件放最前、范围条件放其后、排序或分组列尽量靠后。比如高频查询是 WHERE user_id = ? AND created_at > ?，就建 (user_id, created_at)；反过来写会让 created_at 的范围条件把索引断掉。
为什么我建了索引查询还是全表扫描？
常见原因有五个：列上套了函数或运算、隐式类型转换（varchar 列比数字）、前导百分号 LIKE '%x'、OR 里有没索引的一侧、以及索引选择性太低（比如性别列）。先用 EXPLAIN 确认 type 是不是 ALL，再逐个排除。
慢查询加了索引还是很慢，LIMIT 100000,20 怎么优化？
这是深分页，瓶颈不在索引而在丢弃行数：数据库要扫过并扔掉前 10 万行。改成 WHERE id > 上一页最后一个 id ORDER BY id LIMIT 20，用主键当游标，代价与页码无关，总量再大也稳定。