1. 先搞懂索引的本质：为什么加了索引查询就快
1.1 索引是数据目录，不是玄学
对 MySQL 稍有了解的读者应该都有这个经验：一张表数据量上去之后，查询慢得让人抓狂，加个索引之后速度立刻起飞。但很多人对索引的理解停留在"查询快"这三个字上，真要问为什么快、什么时候该加、什么时候加了反而坏事，就答不上来了。
索引在最底层就是一个数据结构，MySQL 用它来加速数据的检索。你可以把它想象成一本厚书的目录——没有目录时你要一页一页翻（全表扫描），有了目录你直接翻到对应章节就行。MySQL 默认存储引擎 InnoDB 里，索引采用的是 B+ 树结构，这是数据库领域经过几十年验证的最优解之一。
为什么不用二叉树、红黑树或者哈希表？二叉树在数据量增大时会退化成链表，红黑树虽然能自平衡，但树高依然随数据量增长，千万级数据下磁盘 I/O 次数会高到不可接受。哈希表查单条数据确实快，但你没法做范围查询，也没法做排序。B+ 树的优势在于：所有数据都存在叶子节点，并且叶子节点之间用指针串联，既支持等值查询，也天然支持范围查询和排序，树高通常只有 3 到 4 层，意味着最多几次磁盘 I/O 就能定位到目标数据。
1.2 InnoDB 的聚簇索引结构
这里有一个必须搞清楚的概念：InnoDB 的表本身就是一棵 B+ 树。表数据文件按照主键构建了一棵聚簇索引树，叶子节点直接存放整行数据。也就是说，InnoDB 表必须有主键，如果你没有显式指定，MySQL 会隐式生成一个 6 字节的 rowid 作为主键。
这个设计带来的结果很关键：通过主键查询是最快的路径，因为直接走聚簇索引就能拿到整行数据，不需要额外的回表操作。而二级索引（非聚簇索引）的叶子节点存放的是主键值，不是数据行本身，所以通过二级索引查询时，通常会先找到主键，再回到聚簇索引树里取完整数据，这个过程叫回表。
理解这个结构之后，很多问题就迎刃而解了。比如为什么主键不建议用 UUID 这种随机字符串？因为聚簇索引树的叶子节点是按主键顺序排列的，随机主键会导致频繁的页分裂和碎片，写入性能会明显下降。而自增主键是顺序写入，新数据总是追加到末尾，性能最稳定。我处理过不少线上表，主键从自增改成 UUID 之后，写入耗时直接翻倍，这就是聚簇索引结构带来的物理代价。
2. 索引的类型与适用场景
2.1 按功能划分的四种基本索引
MySQL 里常见的索引类型按功能可以分成四种：主键索引、唯一索引、普通索引、全文索引。它们的核心区别在于约束能力和适用场景。
主键索引就是聚簇索引，一张表只能有一个，不允许为空，也不能重复。唯一索引保证列值的唯一性，但允许有一个 NULL 值，这个细节在面试里经常被问到。普通索引没有唯一性约束，纯粹是为了加速查询。全文索引主要用于大文本字段的模糊搜索，比如文章内容的关键词匹配，在 InnoDB 里 5.6 版本之后才开始支持。
实际业务中，唯一索引和普通索引的选择需要仔细权衡。写入端如果业务本身就保证数据唯一（比如订单号），那直接建唯一索引就行，省去应用层的重复检查。但如果唯一性只是业务上偶尔需要，不必为这个场景牺牲写入性能，因为唯一索引每次插入都要额外做一次唯一性校验，写入路径比普通索引更重。
2.2 联合索引与最左前缀原则
联合索引（也叫复合索引）是指在一张表的多个列上共同创建的索引。它的底层结构依然是 B+ 树，但排序规则是先按第一个列排序，再按第二个列排序，以此类推。这就引出了最左前缀原则：查询条件里必须包含联合索引的最左列，索引才能被使用。
举个例子，假设表上有联合索引(a, b, c)，那么以下查询条件走索引：a = 1、a = 1 AND b = 2、a = 1 AND b = 2 AND c = 3。但b = 2或c = 3单独作为条件时，索引无法生效。这条规则是 MySQL 索引面试题里出镜率最高的知识点，也是实际开发中最容易踩的坑。
最左前缀原则看似简单，实际设计联合索引时却非常考验功力。我见过太多团队在(user_id, create_time)和(create_time, user_id)之间反复纠结，其实判断标准很简单：先想清楚业务里最频繁的查询条件是什么，把区分度最高的列放在最左边，同时考虑范围查询的列尽量往后放，因为范围查询之后的列无法继续利用索引进行精确定位。
2.3 覆盖索引与回表的取舍
覆盖索引是一个很容易被忽视但收益巨大的优化手段。所谓覆盖，是指查询所需的全部列都能从索引本身获取，不需要回表。这要求索引列覆盖了 SELECT 的字段列表和 WHERE 条件中的所有列。
我举个例子你就明白了。表user有字段id、name、age、email，二级索引(name)的叶子节点存的是主键 id。执行SELECT id FROM user WHERE name = '张三'时，从二级索引里直接就能拿到 id，不用回表，这个查询就是覆盖索引。但如果执行SELECT age FROM user WHERE name = '张三'，二级索引里没有 age，就必须回表查聚簇索引，性能就多了一次 I/O。
所以设计索引时，不能光看 WHERE 条件，还要看 SELECT 的字段。如果高频查询只用到几个固定字段，可以考虑把这些字段加到索引里，这就是索引列冗余的设计思路。但要注意，索引不是越多越好，每个索引都会增加写入开销和存储空间，覆盖索引的设计要针对真正的热查询，不要为了优化而优化。
3. 索引创建与实战设计
3.1 创建索引的完整 SQL 语法
MySQL 创建索引主要有三种方式，语法上略有差异，但最终效果是一样的。
-- 方式一：建表时直接定义索引 CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, age INT NOT NULL, email VARCHAR(128) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_email (email), KEY idx_name_age (name, age) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 方式二：直接在已存在的表上创建索引 ALTER TABLE user ADD INDEX idx_name_age (name, age); -- 方式三：使用 CREATE INDEX 语法 CREATE INDEX idx_name_age ON user (name, age);这三种方式我用得最多的是第二种，因为线上的表结构变更通常不会重建整张表，ALTER TABLE 语法直观，而且可以和其它表结构变更语句放在一个变更脚本里执行。第三种CREATE INDEX在语义上和 ALTER 等价，但有些自动化 DDL 平台对它的兼容性不如 ALTER，所以团队协作时我会统一约定用 ALTER 方式。
删除索引的语法是DROP INDEX idx_name ON user或者ALTER TABLE user DROP INDEX idx_name。这里要提醒一句：线上删除索引前一定要先确认这个索引没有别的事务正在使用，尤其在高并发环境下，删索引是元数据操作，可能引起短时间的锁等待。稳妥做法是在低峰期操作，先观察一段时间业务日志，确认没有依赖再删。
3.2 联合索引字段顺序怎么排
很多开发者在设计联合索引时，会直接把 SQL 里 WHERE 条件出现的字段顺序抄一遍，这是最常见的错误。字段顺序的排列要综合考虑三个因素：等值条件优先、区分度大的列优先、范围条件放最后。
假设业务里有这么一条高频 SQL：
SELECT * FROM order_info WHERE status = 1 AND user_id = 12345 ORDER BY create_time DESC;我见过很多同学一上来就建了一个(status, user_id, create_time)的三列索引，理由是 SQL 里条件就是按这个顺序写的。但 status 字段的取值可能只有 0 和 1 两种，区分度极低，把它放在最左边会导致整个索引的选择性变差。
更合理的方案是(user_id, status, create_time)。这样定位到某个用户后，status 再过滤，create_time 还能用来排序，避免额外 filesort。这个顺序的调整对查询性能的影响有时候是数量级的差距。
还有一个经验：能用一个联合索引覆盖多个查询场景时，就不要为每个场景单独建索引。比如(user_id, create_time)这个索引，既能支持user_id = 1 AND create_time > '2024-01-01'的范围查询，也能直接支持user_id = 1 ORDER BY create_time的排序场景，一个索引解决两个问题。
3.3 索引下推：MySQL 5.6 之后的隐形优化
索引下推（Index Condition Pushdown，ICP）是 MySQL 5.6 引入的特性，它允许 MySQL 在索引遍历过程中，直接对索引包含的字段进行 WHERE 条件的过滤，减少回表次数。
举个例子，联合索引(name, age)，执行SELECT * FROM user WHERE name = '张三' AND age > 20。在没有 ICP 的旧版本里，MySQL 先通过 name 定位到所有张三的主键，回表拿到完整行，再判断 age 是否大于 20。开启 ICP 之后，age 判断在索引遍历阶段就完成了，只有满足条件的记录才回表。
这个优化对开发者是透明的，不需要改任何代码。如果你想确认自己数据库是否开启了 ICP，可以在 EXPLAIN 的 Extra 列里看到Using index condition字样。了解这个机制的意义在于：联合索引设计时，把范围条件的列放在最后，配合 ICP 可以获得更好的过滤效果，这进一步印证了前面说的字段顺序原则。
4. 索引失效的坑：这些场景明明有索引却不走
4.1 函数操作与隐式类型转换
索引失效的场景我列过一张清单，每次做 SQL 评审都会拿出来对照一遍。其中出现频率最高的，就是查询条件对索引列做了函数操作。
-- 索引失效：对 create_time 使用函数 SELECT * FROM order_info WHERE DATE(create_time) = '2024-06-01'; -- 索引生效：使用范围查询替代函数 SELECT * FROM order_info WHERE create_time >= '2024-06-01' AND create_time < '2024-06-02';第一个查询里，MySQL 无法直接使用 create_time 上的索引，因为索引树里存的是原始时间戳，而查询条件是函数处理后的结果，索引的有序性被破坏了。正确做法是改成范围查询，也就是第二个写法。这个改写方案是我在大量 SQL 优化里最常用的招数，效果立竿见影。
隐式类型转换同样危险。比如手机号字段在表里是varchar类型，查询时写成WHERE phone = 13800138000，MySQL 会把字符串列隐式转成数字比较，你的索引可能直接失效。这个问题在代码 Review 阶段很难发现，因为数据量小的时候全表扫描也能跑得很快，数据量一大就爆发了。
4.2 最左前缀违反与范围查询的截断效应
最左前缀违反导致的索引失效属于"设计层面"的问题，会话中说b = 2单独查不走索引，本质上是因为 B+ 树的排序规则决定了它无法跳过最左列直接定位。
但很多人不知道的是，范围查询对联合索引的影响也很大。假设联合索引是(a, b, c)，查询条件a > 10 AND b = 5，因为 a 是范围条件，b 只能做到索引内过滤，但索引原本可以精确命中 a 和 b 的路径已经断了，这就是范围查询的截断效应。
我处理过一个真实案例：一张订单表有(status, pay_time)联合索引，线上有个查询条件是status = 1 AND pay_time > '2024-01-01' AND type = 2，type 字段没有索引，导致每次查询都要从大量满足前面条件的记录里再过滤一遍。后来我把索引改成(status, type, pay_time)，把等值条件的 type 放中间，范围条件的 pay_time 放最后，查询耗时从 800ms 降到了 30ms。
4.3 优化器为什么不选索引
还有一个让人头疼的场景：索引明明存在，查询条件也完全匹配，但 EXPLAIN 显示走了全表扫描。这不是索引失效，而是优化器认为走索引不如全表扫描快。
最常见的触发条件是查询返回的行数占表总行数比例过高。比如表里 1000 万条数据，一个查询要返回其中 30% 的行，这时候全表扫描可能只需要顺序读，而走索引还要大量回表，随机 I/O 的开销更大。MySQL 优化器有成本模型，会根据表的统计信息估算代价，当它认为全表扫描代价更小时，就会放弃索引。
这种情况下你很难通过改 SQL 来强制走索引，除非用FORCE INDEX提示，但我不建议这么做，因为统计信息变化后，FORCE INDEX 可能让计划更差。更合理的做法是重新审视业务需求，看看能不能缩小查询范围，或者优化表本身的统计信息。另外，LIKE '%keyword%'这种前置通配符的模糊查询也是永远走不了索引的，需要配合全文索引或者外部搜索引擎解决。
5. 用 EXPLAIN 定位索引问题
5.1 EXPLAIN 关键字段解读
排查索引问题，第一工具永远是 EXPLAIN。它不会真正执行 SQL，而是基于优化器的执行计划告诉你这条 SQL 会怎么跑。我平时看 EXPLAIN，重点看四个字段：type、key、rows、Extra。
type 字段表示访问类型，从好到差大致是system>const>eq_ref>ref>range>index>ALL。看到 ALL 就说明是全表扫描，需要警惕。看到 index 也不是好现象，它代表全索引扫描，虽然没用全表，但同样遍历了整棵索引树。key 字段显示实际使用的索引名，可以为 NULL，为 NULL 表示没用索引。rows 是优化器估算的扫描行数，这个值越小越好。Extra 字段里如果出现Using filesort或Using temporary，都要重点排查，前者说明排序没用上索引，后者说明查询涉及临时表。
我一般会用EXPLAIN ANALYZE查看实际执行耗时和行数，5.7 之后的版本还支持EXPLAIN FORMAT=JSON看更详细的成本分析。不过对大多数日常问题，标准 EXPLAIN 的四个字段已经够用了。
5.2 一个真实排查案例
有一次线上用户反馈一个列表接口突然变慢，原来 100ms 以内，后来涨到了 2 秒以上。我先抓了慢查询日志，看到这样一条 SQL：
SELECT order_no, amount, create_time FROM order_info WHERE seller_id = 10086 ORDER BY create_time DESC LIMIT 20;表上有seller_id单列索引，查询条件也命中了，但 EXPLAIN 结果显示 Extra 列有Using filesort，rows 估算有 40 多万行。问题出在 ORDER BY create_time：MySQL 先用 seller_id 索引找到 40 多万行主键，回表拿到 create_time，再在内存里排序取前 20 条。排序消耗成了主要瓶颈。
修改方案是把索引改成(seller_id, create_time)，这样索引本身就是按 seller_id 分组、组内 create_time 有序，排序操作直接省掉。改完之后接口耗时降到 30ms 左右。这个案例给我的启示是：联合索引的设计不能只看 WHERE，ORDER BY 和 GROUP BY 字段同样要纳入考虑范围。
5.3 索引维护与碎片处理
索引不是建好就一劳永逸的。随着业务数据的频繁增删改，索引树会产生碎片，页的利用率下降，查询性能会逐渐变差。InnoDB 里OPTIMIZE TABLE可以用来重建表和索引，但这个操作会锁表，线上大表直接执行风险很高，我一般不建议在业务高峰期跑。
更稳妥的做法是使用一种在线 DDL 工具，或者把索引维护放到低峰期进行。MySQL 5.6 之后支持ALGORITHM=INPLACE, LOCK=NONE的在线 DDL，7.0 之后的版本甚至可以快速加索引。日常维护节奏上，我习惯每季度检查一次大表：
-- 查看表大小和索引碎片情况 SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb, ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' ORDER BY data_mb DESC;另外要注意，ANALYZE TABLE也很重要。它会重新统计索引的基数分布，帮助优化器做出更好的计划。有时候查询计划莫名其妙变差，先别急着改 SQL，执行一次 ANALYZE 可能就解决了。
6. 面试高频问题与避坑清单
6.1 面试官常问的索引问题
MySQL 索引是面试中的必考题，我整理了几道出现频率极高的问题，答案都用上面讲到的原理来组织。
第一道：为什么 MySQL 用 B+ 树而不是 B 树？核心差异是 B+ 树的数据全部存在叶子节点，且叶子节点之间有序连接。这带来两个优势：范围查询可以直接沿链表遍历，不需要中序遍历树结构；非叶子节点可以存放更多键值，树更矮，磁盘 I/O 更少。
第二道：什么是回表？什么时候需要回表？二级索引找到主键后，再通过主键去聚簇索引取整行数据的过程就是回表。当查询的列不在二级索引中时就需要回表，解决办法是设计覆盖索引。
第三道：唯一索引和普通索引如何选择？如果业务需要唯一性约束，直接建唯一索引；如果只是查询加速，就建普通索引。唯一索引在插入时需要额外检查唯一性，写入稍微慢一点，但这是特性的代价，不算问题。
第四道：索引失效的场景有哪些？我前面让你记的那些坑都可以拿来作答，函数操作、隐式类型转换、最左前缀违反、范围查询截断、LIKE 前置通配符，每一条最好都能说出具体的 SQL 例子。
6.2 我总结的索引设计检查清单
最后把我这些年积累的索引设计检查项整理成一张清单，每条都是踩过坑换来的。
- 每张表必须有显式主键，优先自增整型，业务上有自然唯一键也可以作为主键，但不要用 UUID。
- 联合索引字段顺序：等值条件放前，范围条件放后，区分度高的列优先。
- 高频查询尽量设计覆盖索引，避免不必要的回表。
- 控制单表索引数量，一般不超过 5 个，索引不是越多越好，写入频繁的小表尤其要克制。
- 字符串字段要关注字符集，
utf8mb4下每个字符占 4 字节，索引长度容易超限，必要时用前缀索引。 - 大文本字段不要直接建索引，考虑前缀索引或者独立表存储。
- 定期检查慢查询日志，用 EXPLAIN 逐个分析，不要等问题暴露了才去处理。
- 大表 DDL 操作要评估锁和 I/O 影响，尽量使用在线 DDL，在低峰期执行。
6.3 最后分享一个小技巧
在使用SHOW INDEX FROM table_name查看索引信息时，会有一个Cardinality字段，它表示索引列的基数估算值，也就是这一列有多少个不同的值。Cardinality 相对表行数越接近 1，说明列区分度越高，适合做索引前缀列；如果 Cardinality 特别小，比如性别这种只有两三个取值的列，建索引的意义就很有限，即使建了优化器也可能不走。
很多人在设计索引时只看业务字段是否常用，忽略了区分度这个维度，结果建了索引却长期闲置。我的习惯是：设计索引前先用一条 SQL 验证一下候选字段的区分度：
SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;这个比值大于 20% 的字段才值得考虑放在联合索引左侧。行数特别大的表，一次 COUNT 可能比较耗时，可以抽样统计，结果也足够指导设计了。
索引这块知识看起来基础，但越深入越发现每个细节都和底层数据结构挂钩。理解 B+ 树的组织方式，再看最左前缀、回表、索引失效这些问题就不会觉得是死记硬背的规则，而是顺理成章的结论。我个人的体会是：把原理吃透，再结合实际 EXPLAIN 分析，索引设计和 SQL 优化就不会再靠猜了。
