做后端开发和数据库相关工作这么多年，经常在运维群、技术群里看到有人贴一条慢SQL求助，点进去一看，type=ALL，全表扫描，几十万行甚至上百万行的表硬扫。问的人一脸无辜：我建了索引啊，怎么还是慢？再一问，要么索引建错了列，要么查询写法让索引失效了。MySQL索引学习与优化这个坑，几乎每个做业务的开发都得踩一遍，区别只是有人踩完总结出经验，有人同一个坑跳三次。
这篇就针对索引这块，把我自己学习和实战中沉淀下来的东西一次性讲透。不绕弯子，直接从底层结构开始，讲清楚索引为什么快、复合索引怎么设计才不会白建、哪些写法会让索引静悄悄失效、怎么用慢查询日志和EXPLAIN定位问题，最后再聊聊覆盖索引和索引选择性这两个进阶优化点。无论你是刚接触数据库的应届生，还是被慢SQL折腾过几轮的资深开发，这篇都能帮你少走弯路。
1. 从全表扫描到B+树：索引到底快在哪里
很多教程上来就抛结论：MySQL用B+树做索引。但如果你没搞懂“没有索引时MySQL在干什么”和“有了索引之后MySQL在干什么”这两者的区别，后面学什么都像是背口诀。
1.1 没有索引时MySQL在做什么
我习惯用查订单举例子。假设orders表里有500万条记录，你要查WHERE order_no = '20250101120001'。没有索引的情况下，InnoDB只能把主键聚簇索引的叶子节点一个个扫过去，逐行比对order_no字段。这个过程叫全表扫描，复杂度是O(n)，500万行就要读500万个数据页里的记录。
磁盘随机读是非常贵的操作。机械硬盘时代，一次随机IO大概10ms，SSD时代降到了0.1ms到0.5ms左右，但依然比内存操作慢几个数量级。全表扫描500万行，就算数据都在内存里，也要消耗大量CPU去逐行比较字符串；如果数据不在内存里，那磁盘IO时间更是灾难。这就是为什么一条没走索引的查询能从200ms慢到5秒的原因。
1.2 B+树如何把查找变成对数级
B+树的核心优势，是把线性的逐行扫描变成了沿着树径逐层定位。我讲一个最基本的计算，你自己算一遍就明白为什么索引快。
InnoDB默认一个数据页（page）是16KB。假设主键是BIGINT，占8字节，再加6字节的行指针，非叶子节点里每个索引项大概14字节。16KB除以14，约等于1170。也就是说，B+树的第二层能挂约1170个子节点。假设一行数据平均占1KB，那一个16KB的叶子节点能存约16行数据。
那么三层B+树：
- 第一层：1个根节点
- 第二层：1170个节点
- 第三层（叶子层）：1170 × 1170 ≈ 137万个节点
- 每个叶子节点16行：137万 × 16 ≈ 2190万行
这个数字很关键：一个三层B+树，就能支撑约2190万行数据的索引查找。这意味着，哪怕表里有2000万行数据，根据主键查找也只要3次磁盘IO就能定位到目标记录所在的数据页。对比全表扫描的几百万次IO，这就是索引存在意义的全部答案。
1.3 聚簇索引与二级索引的底层区别
搞懂B+树之后，紧接着要分清两个概念：聚簇索引和二级索引。
InnoDB里，表本身就是按主键构建的B+树，叶子节点直接存了整行数据，这叫聚簇索引。你建表时没指定主键，InnoDB也会隐式生成一个6字节的ROWID当作聚簇索引。所以聚簇索引的叶子节点 = 完整行数据。
二级索引（也叫非聚簇索引、辅助索引）就不同了，它的叶子节点不存完整数据，只存索引列的值 + 主键值。比如你在order_no上建了索引，查询时先顺着二级索引的B+树找到order_no对应的主键值，再拿主键值回到聚簇索引里查完整行。这个过程叫回表。
回表这个动作很关键，很多优化手段都围绕“减少回表”展开，后面讲到覆盖索引时会再重点展开。这里你只需要记住：二级索引查询天然比主键查询多一次树查找路径，能避免回表就尽量避免。
2. 复合索引与最左前缀原则：索引设计的第一课
单个字段建索引，几乎所有开发都会。但实际业务查询往往带多个过滤条件，这时候就轮到复合索引上场了。复合索引设计得好不好，直接决定查询能不能走索引，以及走索引的效率高不高。
2.1 单列索引不够用：复合索引的诞生
看这个经典查询：
SELECT * FROM orders WHERE customer_id = 1001 AND status = 'PAID' AND create_time > '2024-01-01';如果你在customer_id、status、create_time三个字段上分别建了三个单列索引，MySQL执行时通常只会选择其中一个（优化器一般会选择区分度最高的那个），再用另外两个字段做过滤。换句话说，另外两个索引基本是废的。
这时候正确的做法是建复合索引：
ALTER TABLE orders ADD INDEX idx_cust_status_time (customer_id, status, create_time);复合索引的底层原理是：先按第一个字段排序，第一个字段相同的再按第二个字段排序，依次类推。它本质上还是一个B+树，只是键值从单列变成了多列的组合。因为是按列的有序组合排列的，所以它能同时高效支撑三个字段的等值查询和范围查询。
2.2 最左前缀原则：用错顺序等于白建
复合索引最关键也最容易出错的地方，就是最左前缀原则。规则是：查询条件必须从复合索引的最左列开始连续命中，索引才会被使用。中间跳过任何一列，后面的条件就用不上索引了。
我用实际例子说清楚。假设我们建了(customer_id, status, create_time)这个复合索引，以下情况分别能否用到索引：
| 查询条件 | 能否使用索引 | 说明 |
|---|---|---|
WHERE customer_id = 1001 | 完全命中 | 用索引的第一个字段 |
WHERE customer_id = 1001 AND status = 'PAID' | 完全命中 | 前两列连续命中 |
WHERE customer_id = 1001 AND status = 'PAID' AND create_time > '2024-01-01' | 完全命中 | 三列全部命中 |
WHERE status = 'PAID' AND create_time > '2024-01-01' | 无法使用索引 | 跳过了最左列customer_id |
WHERE customer_id = 1001 AND create_time > '2024-01-01' | 只能用customer_id | 跳过status，后面的create_time就用不上索引了 |
最后一行很容易被忽略，很多人看到条件里有customer_id就觉得索引全用上了，实际EXPLAIN一看，key_len只覆盖了第一个字段。因为B+树是按(customer_id, status, create_time)的顺序排列的，跳过了status，create_time在索引里的顺序就断了，无法有序查找，只能用customer_id定位后再逐行过滤。
所以复合索引的列顺序，基本决定了它能服务哪些查询。我个人的设计习惯是：先放等值查询的列，再放范围查询的列，同时把区分度高的列往前放。比如customer_id是等值条件且区分度高，就放在最前面。当然这不是绝对规则，具体要看业务查询组合，只能说这是起步最快的思路。
2.3 索引下推：一个容易被忽略的加速点
MySQL 5.6引入的索引下推（Index Condition Pushdown，ICP），是一个很多人没注意但收益很实在的优化。
还是用(customer_id, status, create_time)举个例子。如果查询是：
SELECT * FROM orders WHERE customer_id = 1001 AND status LIKE '%PAID%';没有ICP时，InnoDB会先在索引里定位到customer_id = 1001的所有记录，然后逐条回表取出完整行，再在Server层判断status LIKE '%PAID%'。假设表里有5万条customer_id=1001的记录，就要回表5万次。
开启ICP后，虽然LIKE '%PAID%'无法在索引里定位，但可以在引擎层直接拿索引里的status字段做初步过滤，过滤后的少量记录才回表。5万条可能过滤到几百条再回表，IO次数差距是数量级的。MySQL 5.6以后ICP默认开启，在EXPLAIN里看到Using index condition就说明生效了。这个机制告诉我一个道理：别因为某个条件无法定位，就认为复合索引里后面的列完全没用，引擎层还有一层过滤能力。
3. 五个让索引悄然失效的场景：坑我都替你踩过了
建索引容易，让索引真正在查询里生效才是本事。我在实际工作中遇到过太多“明明有索引却不走”的情况，下面这几个是踩坑最集中的场景。
3.1 函数操作与隐式类型转换：索引失效的头号元凶
这是最常见的两个坑，放在一起说是因为底层逻辑相同：索引列被“加工”之后，就打破了B+树有序排列的前提，MySQL无法再做有序查找。
第一个坑是泰勒函数：
SELECT * FROM orders WHERE DATE(create_time) = '2024-01-01';DATE()函数包住了索引列，优化器就不会用create_time上的索引了。正确写法是改成范围查询：
SELECT * FROM orders WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00';第二种坑是隐式类型转换。比如phone字段是VARCHAR类型，查询写成了：
SELECT * FROM users WHERE phone = 13800138000;数字13800138000会先被转成字符串去和phone比较，还是phone被隐式转成数字？不同场景MySQL处理方式不同，但结果往往都是索引列上发生了类型转换，导致索引失效。解决办法是使用时保持类型一致：
SELECT * FROM users WHERE phone = '13800138000';我在线上见过不止一次因为这种细节导致的核心查询全表扫描，排查了半天最后发现就是一个引号的事。所以写SQL之前，建议先对着字段类型自查一遍。
3.2 左模糊匹配与反查询：范围问题的边界
模糊查询能不能用索引，很多人只记了个结论：LIKE 'xxx%'能用，LIKE '%xxx'不能用。但背后的原因值得说清楚。
B+树的索引是有序的，所以LIKE 'abc%'可以转化为“从上界abc到下界abd之间的有序区间查找”，和范围查询一个道理，索引自然能用。但LIKE '%abc'没有固定前缀，它可能出现在字符串的任何位置，你无法定位到树里的某个起始点，只能遍历所有叶子节点逐行匹配。
这里有个实用技巧：如果确实需要左模糊查询，且字段前缀区分度比较低，可以考虑反向冗余字段——比如存一个反转后的字符串列reverse_phone，用LIKE reverse('138%')的写法实现倒序匹配。这个方案不完美，索引占用会翻倍，但在某些高频场景下确实是可行的取舍。
反查询场景也容易踩坑：NOT IN、NOT LIKE、!=这些操作，因为结果集可能很分散，MySQL优化器大概率选择全表扫描。就算有些情况下能用索引，性能也远不如正查询。如果业务确实依赖反查询，建议改成正查询后处理结果集，或者在设计阶段就避免这种需求。
3.3 OR条件连接造成的全表扫描陷阱
这个坑我印象很深，因为它的表现是“前面的条件明明能走索引，加了OR之后整体就不走了”。
SELECT * FROM orders WHERE customer_id = 1001 OR order_no = '20250101120001';customer_id有索引，order_no也有索引，但MySQL面对OR条件时，需要同时取两个条件的并集，处理逻辑变复杂了。优化器评估后觉得不如直接全表扫描，于是索引就失效了。
解决方案主要有两种。一种是改写为UNION ALL：
SELECT * FROM orders WHERE customer_id = 1001 UNION ALL SELECT * FROM orders WHERE order_no = '20250101120001';另一种是确保OR两边的条件列都在同一个复合索引里，让优化器有合并索引的可能。不过最稳妥的还是第一种改写，简单直接。需要提醒的是，OR和IN不一样，IN在绝大多数情况下都能正常走索引，不要混淆。
3.4 排序方向与索引顺序不一致：filesort的隐形消耗
排序方向不一致很容易被忽略。索引里默认都是升序排列的，也就是(a ASC, b ASC)。如果你的SQL是：
SELECT * FROM orders WHERE customer_id = 1001 ORDER BY create_time DESC;而索引是(customer_id ASC, create_time ASC)，那在customer_id = 1001这个分组内部，create_time是按升序排列的。你要求降序返回，MySQL就没办法直接按索引顺序扫描输出，只能再额外做一次filesort排序。
以前遇到这种情况，优化空间不大。但MySQL 8.0引入了降序索引，可以这样建：
ALTER TABLE orders ADD INDEX idx_cust_time_desc (customer_id ASC, create_time DESC);这下索引内部按照目标查询的排序方向组织数据，查询时可以直接顺着索引顺序读取，省掉filesort。如果还在用5.7或者更早版本，遇到排序和索引方向不一致时，要么改写查询，要么接受filesort的额外开销。我在实际评估时一般先问一句：这个排序查询的频率高不高？不高就别折腾降序索引，太高的SQL才值得专门优化。
4. 慢SQL排查：怎么从一堆SQL里找到该建索引的那个
前面讲了这么多索引原理和失效场景，落地到日常运维和开发里，核心动作是：找出慢SQL，解读执行计划，然后对症下药。这条链路每家公司流程不一样，但底层方法都是通用的。
4.1 开启慢查询日志：先锁定目标
MySQL的慢查询日志是排查性能问题最直接的入口。我一般会在开发环境或者压测环境开启它，用分析和优化慢SQL，确认无误后再针对性处理线上场景。
-- 开启慢查询日志并设置阈值，单位是秒 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 2; SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';这条命令的意思很直白：执行时间超过2秒的SQL全部记录下来。注意两点细节：第一，long_query_time设置后需要重新连接会话才会生效；第二，在生产环境要评估日志磁盘占用，别让慢日志把磁盘写满。我习惯的做法是配合pt-query-digest这类工具定期分析慢日志，把出现频率最高的几十条SQL优先处理。
如果你没有开启慢查询日志的权限，也可以在业务里临时抓取：
SELECT * FROM information_schema.processlist WHERE command = 'Query' AND time > 2;这个能实时看到当前正在执行且超过2秒的查询，适合应急排查。
4.2 EXPLAIN解读：重点只看这几列
拿到慢SQL之后，下一步就是看执行计划。MySQL里直接在查询前面加EXPLAIN：
EXPLAIN SELECT * FROM orders WHERE customer_id = 1001 AND status = 'PAID' ORDER BY create_time DESC;结果里字段很多，我建议新人先盯住这五个列：
| 列名 | 含义 | 重点关注 |
|---|---|---|
| type | 访问类型 | 从好到差依次是：system > const > eq_ref > ref > range > index > ALL。看到ALL就要警惕全表扫描 |
| key | 实际使用的索引 | 为NULL表示没走索引 |
| key_len | 使用的索引长度 | 可以判断复合索引到底用上了几列 |
| rows | 预估扫描行数 | 和type联动看，rows越大越危险 |
| Extra | 额外信息 | 看到Using filesort或Using temporary说明有额外排序/临时表开销 |
五列里最核心的是type和key。我提供一个判断口诀：type至少要达到range，最好是ref或者const；key不能为NULL；key_len要覆盖你预料中的索引列数量。你按这三个标准去检查一条慢SQL的执行计划，大部分问题当场就能定位。
4.3 一个真实的优化前后对照
上面讲了这么多，我用一个实际场景把它们串起来。
假设有个订单支付流水表payment_log，600多万行，业务反馈“查用户流水接口越来越慢”。慢日志抓到这条SQL：
SELECT id, order_no, amount, status, create_time FROM payment_log WHERE payer_uid = 123456 AND status = 'SUCCESS' ORDER BY create_time DESC LIMIT 20;EXPLAIN结果：type=ALL，rows=620万，Extra里有Using filesort。payer_uid、status、create_time三个字段都有各自的单列索引，但查询要求三个字段同时过滤，单列索引最多帮上一个，优化器干脆全表扫。
优化方案是建一个复合索引，顺序定为(payer_uid, status, create_time)。理由：payer_uid是等值条件且区分度高，放最左；status是等值条件，放第二；create_time是排序字段，放最后。因为排序字段放在索引里，ORDER BY create_time DESC正好可以走这个索引，filesort也能消除。
优化后的执行计划：type=ref，key=idx_payer_status_time，rows=38（预估），Extra里Using filesort消失。查询从2.8秒降到了30毫秒左右。这个案例看起来简单，但典型的“单列索引各自为政、复合索引一击致命”的优化思路，你完全可以复制到自己的业务里。
5. 覆盖索引与索引选择性：优化到极致时的两条思路
把慢SQL都优化完之后，还有两个进阶手段，它们解决的问题不一样，但都在“更少的IO”这个方向上做文章。
5.1 覆盖索引：让查询一次都别回表
回想一下聚簇索引和二级索引的区别：二级索引叶子节点存的是索引列值+主键值。如果一个查询要的字段，刚好全部都在二级索引里，那MySQL连回表都省了，直接在索引B+树上扫描取值即可。这种场景叫覆盖索引，EXPLAIN里Extra会显示Using index。
比如这个查询：
SELECT id, order_no, status FROM payment_log WHERE payer_uid = 123456 AND status = 'SUCCESS';如果我们建的复合索引是(payer_uid, status, order_no)，那id是主键值也在索引里，order_no、status、payer_uid都在索引里。整个查询需要的数据全部能从二级索引的叶子节点拿到，无需回表。在超大表的场景里，这能省掉海量的随机IO。
依赖覆盖索引有个代价：索引字段越多，占用空间越大，写入时维护索引的成本也越高。所以我的建议是：只针对高频且返回字段固定的查询，有意识地设计覆盖索引，不要所有查询都无脑把SELECT里的字段塞进索引里。一个折中策略是，把几个常见查询的高频返回字段放进同一个复合索引里，一鱼多吃。
5.2 索引选择性：区分度不高时干脆不建
索引选择性的公式很简单：COUNT(DISTINCT col) / COUNT(*)。比值越高，区分度越好，索引效果越明显。反之，如果区分度很差，建了索引也帮助不大。
最典型的反例就是性别字段，一个只有“男/女/未知”三值的字段，即使有500万行数据，选择性也只有三百分之一，等于没有。WHERE gender = 'M'能过滤掉一半数据，优化器觉得走全表扫描和走索引区别不大，甚至全表更快，干脆不走索引。
那区分度低的字段就完全没法用吗？也不一定，但要看业务场景。如果它总是和其他高区分度字段一起出现在查询里，那把它放进复合索引是有价值的，因为它能进一步缩小返回行数。但如果单独查这个字段的频率不高，就别给低区分度字段建单列索引，纯浪费空间还拖慢写入。
另外还有一个常用的方案是前缀索引。比如order_no超长，但前12个字符基本能区分所有订单，就可以用ALTER TABLE orders ADD INDEX idx_order_prefix (order_no(12))来减少索引体积。注意前缀索引有个限制：它没法用于覆盖索引查询，因为叶子节点里存的是前缀而非完整值。
5.3 冗余索引清理：建多了也危险
索引不是越多越好，这个道理很多人都懂，但真到自己维护的表时，冗余索引比比皆是。最典型的情况是：先有单列索引idx_status，后来又建了复合索引idx_status_time(status, create_time)。结果idx_status就成了冗余索引，因为复合索引的前缀列已经能覆盖它。
怎么发现冗余索引？可以用sys.schema_redundant_indexes视图直接查询，MySQL 5.7以上自带：
SELECT * FROM sys.schema_redundant_indexes;清理冗余索引的意义不止省空间。每一个索引都意味着写操作时额外的B+树维护成本。在写入频繁的表上，多一个用不到的索引，就可能拖慢每次INSERT、UPDATE。我之前优化后台一个每小时写入几十万条数据的表时，清掉两个冗余索引写入延迟就下降了将近20%。索引优化从来不只是读查询的事，写路径同样受影响。
其实索引这一块，越往后做越会发现，它不是“建了就完事”的一次性工作，而是需要不停观察业务查询、反复调整的设计过程。我在实际项目里的习惯是分三步走：第一，所有核心查询上线前必须过EXPLAIN，type、key、key_len、rows四个字段不达标的回去改；第二，每个月定期看慢日志，抓新增的慢SQL，判断是数据量增长导致还是新业务代码引入的；第三，对线上索引做定期的冗余清理评估，把确实没有查询在用的索引果断删掉。
最后再分享一个小技巧：建索引之前先刷一遍ANALYZE TABLE，让统计信息准确。MySQL优化器选不选索引、选哪个索引，很大程度上依赖统计信息。统计信息不准，再好的索引也可能被优化器“无视”。我刚工作那会儿就吃过这个亏——明明刚建好一个复合索引，EXPLAIN就是不走，结果ANALYZE TABLE之后立刻走了。这个细节很多人不会告诉你，但排查索引失效问题时，值得第一时间确认。
