做数据库的人，基本都绕不开索引。不管你是刚入行的后端开发，还是已经写了几年SQL的业务工程师，早晚会遇到一条慢SQL，慢到接口超时、数据库CPU飙升，最后DBA甩给你一句：“这里没走到索引。”MySQL的索引，就像一个加速器，加对了，千万级数据也像翻书一样快；加错了，或者压根没设计，数据一多就直接把数据库拖垮。这篇内容，我把索引从底层数据结构到日常设计、失效排查一次讲透，适合那些想真正搞懂“为什么慢、怎么建索引、索引什么时候失效”的人。内容会比较长，但保证每一条都能直接拿去看业务、建索引、调SQL。
1. 索引到底在解决什么问题
1.1 从一次全表扫描说起
先看一条再普通不过的SQL：
SELECT * FROM user WHERE user_name = '张三';假设user表里有1000万条数据，且user_name上没有任何索引。MySQL会怎么执行？它只能从第一行到最后一行，挨个把user_name拿出来比对。这就是我常说的全表扫描，在InnoDB存储引擎里，实际表现为扫描聚簇索引的叶子节点链表，一行都不放过。
这里要说一个核心概念：InnoDB的最小读写单位不是行，而是数据页。一个页默认16KB，里面可能装着几十行甚至更多数据。全表扫描意味着要把存储这张表数据的页，一连串读进来，页数越多，磁盘IO次数就越多。机械硬盘的IO延迟本来就高，哪怕换成了SSD，大量随机读也会让整体查询时间随数据量线性上涨。数据量一千万时，这种线性扫描基本就是噩梦，一条查询跑几百毫秒甚至几秒都算正常。
没有索引时，MySQL面对一个等值查询只能靠“蛮力”。你体会一下：从一万行里找一个名字，和从一千万行里找一个名字，耗时完全不可控。索引解决的问题，本质上就是把这个“挨个看”的过程，变成“按目录翻页”的过程。
1.2 索引为什么能这么快：有序结构加二分查找
索引的思想其实特别朴素，就像一本几百页的书，没有目录你只能一页一页翻，有了目录你就可以直接定位到章节。数据库索引就是为某一列或某几列的值，建一份“有序目录”。
为什么有序这么重要？因为有序的数据可以做二分查找。想象一个场景：让你在一堆按大小排好的数字里找77，你会从中间开始，比中间值大了就看右半部分，小了就看左半部分，每次都能砍掉一半的数据。对于1000万个值，二分查找最多比较约log2(1000万)次，大约是23次，跟全表扫描1000万次完全不是一个量级。
在MySQL里，这个“有序目录”是用B+树实现的。B+树的每一层都可以看作一个筛子，从根节点逐层往下，一次就能排除大量无关数据。于是，一次索引查找耗时基本可以由树的高度决定，而InnoDB的B+树高度通常只有2到4层。这意味着，哪怕表里有几千万行，走索引也就几次磁盘IO的事，查询时间不会因为数据量增加而急剧劣化。
1.3 索引不是免费的：写变慢，空间变多
索引能提速查询，但不是白送的。每建立一棵索引树，就等于额外维护一份有序结构。你插入一行数据，不仅要写主键索引，还要同步写入每一个二级索引；删除一行，也要从所有索引里把它摘掉；更新一个索引列，本质上相当于删除旧值再插入新值。
所以你会发现，索引建得越多，写入性能越差，磁盘空间占用也越大。这就是空间换时间的典型Trade-off。很多新人容易犯的错，就是把所有可能出现在WHERE里的列都单独建个索引，结果查询没快多少，写入反而越来越慢。
我见过一张20个字段的表，被建了30多个索引，最后每次批量导入数据慢得像蜗牛。优化的第一步不是加索引，而是想清楚“到底哪些查询值得建索引”。一个优秀的索引设计，永远是结合业务查询来的，不是按字段数量来的。
2. MySQL索引的底层数据结构：B+树为什么是主角
2.1 二叉树、B树、B+树到底差在哪
很多人背过“InnoDB用B+树”，但不知道为什么不用二叉树、不用B树。我从磁盘IO的角度拆开说。
先说二叉查找树。它的问题在于极端情况下会退化成链表，比如插入有序数据时，树的形状会变得很高，查询又变成了线性扫描，而且每次都只能读两个分支中的一个，磁盘IO效率很低。平衡二叉树能解决退化问题，但它每个节点只能存一个键、两个孩子，树高依然按数据量对数增长。数据多到千万级时，树高可能达到二三十层，每层一次随机IO，谁也受不了。
再看B树。B树的一个节点可以存多个键，也能有多个孩子，树高被压得很低。但B树有个特点：每个节点既存索引键，也存数据。这意味着，如果一行数据很大，一个节点能容纳的键数量就很有限。非叶子节点还要存储数据，导致每一层能够携带的“搜索路径”变少，树高就不容易压下来。
B+树是怎么改的？B+树的所有数据都存放在叶子节点，非叶子节点只存索引键和指针。这样，一个16KB的页能存上千个键，树高被压得非常低。同时B+树的叶子节点之间用双向链表串联，非常适合范围查询和排序扫描。范围查询时，只要找到起点，顺着叶子节点链表往后走就行，不需要反复从根节点重新搜索。
我自己在给新人讲的时候会打一个比方：B树像是每个楼层都放了一些商品，你找东西每层都要看一眼；B+树则像商场每层只挂楼层索引，所有商品都集中在底层并按顺序摆好。搜索引擎也好，数据库也好，都天然喜欢后者。
2.2 一棵B+树到底能存多少数据
很多人对B+树的“矮”没有直观感受，我算一组经典数字。
假设主键类型是BIGINT，占用8字节，页内的指针占用6字节，那么非叶子节点每条索引项大约14字节。一个16KB的数据页可以容纳大约16 * 1024 / 14 ≈ 1170个索引项。也就是说，第二层能分出1170个分支。假设一行数据大小是1KB，一个叶子页可以存16行。那么三层B+树大约可以存储1170 * 1170 * 16 ≈ 2190万行数据。也就是2200万行左右，只需要三次IO定位到叶子页。
如果行比较小，比如一行只有几百字节，三层树能存的行数会更大。这也是为什么千万级数据表在InnoDB里走主键查询，通常也就两三毫秒的原因。如果索引键更小，比如用INT做主键，一层非叶子页能存的索引项更多，树可以更矮。
这里也顺便解释了为什么InnoDB强烈建议你用自增主键。自增主键顺序插入，新数据总是加到B+树最右侧，页分裂较少。如果主键是随机字符串，插入时索引页会被频繁分裂、移动，产生大量碎片，写性能会明显下降。
2.3 聚簇索引和二级索引：回表到底怎么发生
InnoDB的数据存储和索引是绑在一起的，这个设计叫聚簇索引。表里的主键索引树的叶子节点，直接保存了整行数据。也就是说，只要你按主键查询，拿到叶子节点就等于拿到了这一行的所有列。
那二级索引呢？比如你在user_name上建了一个普通索引，这就是二级索引。二级索引的叶子节点，保存的是索引列的值加上主键值，而不是整行数据。执行：
SELECT * FROM user WHERE user_name = '张三';如果只靠user_name索引，MySQL能在索引树上快速找到“张三”对应的主键id，但发现SELECT *还需要user_name、age、mobile等其他字段。于是它拿着这个主键id，再回到聚簇索引树里查一遍完整数据，这个过程就叫回表。
回表多了一次随机IO，所以性能上比直接在索引里拿数据要差。那怎么避免回表？有两种办法。第一种是只查询索引中包含的列，比如只查user_name和id，那么索引覆盖了需求，不用回表，这就是覆盖索引。第二种是建一个包含所有所需字段的联合索引，让查询所需列都落在索引里。
很多人只听说过覆盖索引这个词，不知道它的意义。在核心高频查询里，设计一个覆盖索引，往往比盲目增加缓存更直接有效。
3. 索引设计实战：从EXPLAIN读懂执行计划开始
3.1 EXPLAIN核心字段到底怎么看
索引设计不能靠猜。拿到一条慢SQL，第一步永远是执行EXPLAIN，让MySQL告诉你它打算怎么查。
EXPLAIN SELECT id, order_no FROM order_info WHERE user_id = 10086 AND status = 1;执行结果里有几个字段要重点看。
type，告诉你访问类型。常见的从好到差排序：system、const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描，通常意味着索引没建好或者没走成。看到index代表扫了整棵索引树，一般也比ALL好不到哪去。ref和range是比较常见且健康的访问方式，一个用于非唯一索引等值匹配，一个用于范围匹配。
key，表示优化器最终选中的索引。possible_keys是可能用到的索引列表，但真正用的是key这一列。有时候possible_keys里明明有索引，优化器却没选中，说明优化器认为用了反而更慢，这种时候要认真分析原因。
rows，是优化器估算的需要扫描的行数。这个数字越小通常越好。有时候索引选择的区分度不够，rows依然很大，比如在性別这种只有两个值的列上建索引，基本没什么意义。
Extra，信息量很大。出现Using filesort，说明排序没有利用索引，需要额外排序操作；出现Using temporary，说明用了临时表，常用于GROUP BY或者去重；出现Using index，则说明正在使用覆盖索引，这是很好的信号。
我看EXPLAIN的习惯是：先看type和rows，再看key和Extra。一次EXPLAIN看不出问题，就加一个FORCE INDEX对比，或者拿真实数据量验证。
3.2 联合索引与最左前缀原则
业务里单列索引远远不够，更多时候需要联合索引。比如你的查询条件是user_id和status同时存在，那么建立一个(user_id, status)联合索引，通常比分别建两个单列索引更高效。
联合索引的底层逻辑是：先按照第一个字段排序，第一个字段相同，再按第二个字段排序。所以它天然支持“从最左边开始连续匹配”。这就是最左前缀原则。
CREATE INDEX idx_user_status ON order_info(user_id, status);下面这些查询都能用上这个索引：
WHERE user_id = 10086 WHERE user_id = 10086 AND status = 1 WHERE user_id = 10086 ORDER BY status但下面的查询用不上，或者只能用一部分：
WHERE status = 1 WHERE status = 1 AND user_id = 10086 -- 优化器可能自动调整，但依赖顺序风险大如果查询条件里有范围条件，比如：
WHERE user_id > 10000 AND status = 1那么联合索引(user_id, status)左列user_id走了范围，status条件不一定能继续走索引。因为索引排序时，在user_id范围后面的status并不是全局有序的。这就是很多人说的“范围查询会打断索引后续列”。
实际设计联合索引时，我建议把等值条件放在最左边，把范围条件放在后面，并把排序字段也尽量纳入索引，避免Using filesort。这听起来简单，但真正动手设计时，还是要拿具体SQL一条条盘。
3.3 一个订单查询的索引设计案例
拿一个我处理过的典型场景说。某项目有张订单表，核心查询是：
SELECT order_no, amount, create_time FROM order_info WHERE user_id = 12345 AND status = 1 ORDER BY create_time DESC LIMIT 20;我先看表结构和数据量：order_id主键，user_id、status、create_time都可能作为查询条件，表里大约800万行。最粗暴的方案是给user_id、status、create_time各建一个单列索引，但这样效果很差，因为单靠user_id的等值扫描可能扫出几千行，再逐个判断status、再排序，性能不稳定。
最终我建的索引是：
ALTER TABLE order_info ADD INDEX idx_user_status_time (user_id, status, create_time);为什么这样设计？user_id等值过滤，最左侧命中；status等值过滤，继续缩小范围；create_time在索引里已经有序，正好满足ORDER BY create_time DESC，避免额外的文件排序。这个索引让查询从全表扫的几百毫秒降到了个位数毫秒。
这里有一个细节：如果还希望避免回表，可以把查询需要的amount也放进索引，比如：
ALTER TABLE order_info ADD INDEX idx_user_status_time_amount (user_id, status, create_time, amount);但索引不是越长越好。多加一个字段，占用空间变大，写入变慢。只有当这条SQL是高频热点时，才值得用覆盖索引换一次回表。我一般在线上先确认查询频率，再决定是否扩大索引列。
4. 索引失效场景与排查技巧
4.1 看起来走了索引，实际却没走上
索引建好了，不代表每条SQL都会命中。最容易踩的坑，就是对索引列做计算或函数操作。
SELECT * FROM order_info WHERE DATE(create_time) = '2024-05-01';如果在create_time上有索引，这样写，优化器很难直接使用索引，因为索引中存储的是完整的日期时间值，而DATE()函数改变了比较条件。更推荐写成范围条件：
SELECT * FROM order_info WHERE create_time >= '2024-05-01 00:00:00' AND create_time < '2024-05-02 00:00:00';另一个常见问题是隐式类型转换。比如mobile字段是varchar类型，却写成：
SELECT * FROM user WHERE mobile = 13800138000;MySQL可能会把字符串转成数字再比较，导致索引失效。解决方法是等号右侧也写成字符串。
还有前导模糊查询：
SELECT * FROM user WHERE user_name LIKE '%张%';因为索引按前缀排序，第一个字符是%时，优化器无法确定从哪里开始匹配。如果业务确实需要，可以改用前缀索引或者全文检索方案。
OR条件里只要有一个列没索引，整条查询也可能放弃索引。比如：
SELECT * FROM order_info WHERE order_no = 'A1001' OR status = 2;即使order_no上有索引，MySQL也可能需要把OR两侧结果合并，若status无索引，容易出现全表扫描。这种情况尽量用UNION拆开，或者确保两边都有索引。
4.2 用慢查询日志定位真正的凶手
线上系统慢SQL往往不会主动跑到你面前。要主动发现问题，第一步是开启慢查询日志。
SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON;long_query_time设置为1秒，意味着超过1秒的SQL会被记录。log_queries_not_using_indexes可以帮你找出那些没走索引的SQL。注意，这些参数只是临时生效，持久化配置需要写进配置文件。
拿到慢SQL之后，我会先EXPLAIN确认执行计划，再根据实际数据重新设计索引。有时候一条SQL慢，不是因为缺索引，而是因为写法让索引用不上，比如前面讲的函数、隐式转换、范围条件位置不对。所以排查慢SQL的最后一步，一定要回到SQL写法上。
4.3 常见失效原因速查表
我把自己平时排查问题时最常遇到的场景整理成了一张表，方便你直接对照。
| 场景 | 原因 | 正确做法 |
|---|---|---|
| WHERE DATE(create_time)=... | 对索引列使用函数 | 改成范围条件 |
| WHERE id+1=100 | 对索引列做运算 | 左边保持原始列 |
| WHERE mobile=13800000000 | 隐式类型转换 | 右侧写字符串 |
| LIKE '%关键词' | 前导模糊 | 用前缀匹配或全文检索 |
| OR多条件 | 部分列无索引 | 确保所有列都有索引或UNION |
| 索引列区分度太低 | 优化器认为全表扫更快 | 换高区分度列建索引 |
| 字符集或排序规则不同 | 关联列无法使用索引 | 统一字符集和排序规则 |
| WHERE a>100 AND b=1 | 联合索引范围截断 | 把范围列放在联合索引末尾 |
这张表不需要死记，你只要记住一个核心：索引的本质是有序结构，任何破坏有序性或者无法直接匹配有序结构的写法，都会让索引失效。
5. 索引维护与大表加索引的坑
5.1 索引不是越多越好，冗余更要命
我在第1章说了索引有写入成本，这里再聊一个常见现象：同一个字段被多个索引重复覆盖。
比如已经有了联合索引(user_id, status)，又单独建了一个user_id索引。因为联合索引左边的user_id已经可以支持“where user_id=?”的查询，单独再建user_id索引就是冗余的。数据库写入时还要多维护一棵索引树，纯粹浪费。
查询系统表可以看到一张表的所有索引，我一般会逐个检查：
SHOW INDEX FROM order_info;重点看每一棵索引是不是被其他联合索引完全包含。如果联合索引的左前缀已经覆盖了单列索引，同时字段长度相近，单列索引基本就是冗余，可以删掉。
当然，有一种例外：如果那个单列索引的查询频率极高，而且联合索引体积很大、前缀列很多，删除单列索引后查询可能需要走更宽的索引树。所以删除前最好结合真实EXPLAIN和统计数据判断，不要机械地“眼见冗余就删”。
5.2 大表加索引：在线DDL与锁表风险
给几百万甚至上千万行的表加索引，最怕锁表。MySQL 5.6之前，很多DDL会锁整张表，业务直接不可用。从5.6开始支持Online DDL，可以在很多场景下做到不阻塞读写。
加普通二级索引时，可以使用：
ALTER TABLE order_info ADD INDEX idx_user_status (user_id, status), ALGORITHM=INPLACE, LOCK=NONE;ALGORITHM=INPLACE表示不复制整张表，在原表上进行索引构建；LOCK=NONE表示允许并发DML。需要注意，并不是所有DDL都支持INPLACE，比如修改主键、改变列类型，有时还是会走COPY算法，复制整张表，耗时和磁盘占用都会剧增。
实际执行前，我会先检查磁盘剩余空间，因为Online DDL过程中可能额外占用临时空间。还会避免在业务高峰期操作，并在从库上观察延迟。大表加索引再优化，也可能要跑几十分钟甚至更久，必须有心理预期。
如果表已经很大，直接ALTER依然有风险，可以考虑用在线变更工具分批处理。这类工具的原理是创建一个新表、同步增量数据、在后台批量拷贝旧数据，最后切换。整个过程中业务无感知。要注意的是，无论用哪种方式，都要在测试环境先跑一遍，拿到执行时间和空间占用，再上生产。
5.3 索引用得少，怎么发现和清理
索引不光是加，还要清理。一张表可能积累了很多历史索引，但实际上从来没有SQL用过。MySQL有个比较方便的办法，从performance_schema里查索引的统计信息。
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = 'your_db' AND OBJECT_NAME = 'order_info';通过这个视图能看到每棵索引被读了多少次，如果某个索引的COUNT_STAR长期为0或者极小，基本可以判定是冗余索引。结合慢日志和业务上线记录，确认安全后就可以删掉。
清理索引的收益往往被忽视。索引少了，写入会变快，缓冲池也能腾出更多空间给真正热的数据。我一直建议每半年做一次索引治理，算是数据库日常保养的一部分。
6. 常见问题实录与独家经验
6.1 字符集和排序规则不同，关联查询索引失效
有一个非常容易忽视的坑：两张表关联字段的字符集不一样。
比如order_info库是utf8mb4_general_ci，user_info库是utf8mb4_unicode_ci，两张表通过user_id关联时，MySQL为了实现比较，可能要对其中一列做隐式转换，导致无法直接使用索引。
解决办法是统一字符集和排序规则。如果暂时不能改表结构，可以在关联时显式指定转换：
SELECT * FROM order_info o JOIN user_info u ON o.user_id = CONVERT(u.user_id USING utf8mb4) COLLATE utf8mb4_general_ci;但这也不是长久之计，因为函数和转换又会影响索引使用。统一规范才是正解。所有业务库的字符集、排序规则，在建库时就该定死。
6.2 ORDER BY、GROUP BY、分页到底能不能走索引
排序不走索引时，EXPLAIN的Extra里会出现Using filesort。解决排序问题最直接的方式，是让排序列在联合索引中正好处于从最左开始连续的位置。
GROUP BY本质上也要排序，如果分组字段能匹配联合索引，也会避免临时表。遇到深分页问题就更有意思了：
SELECT * FROM order_info WHERE user_id = 10086 ORDER BY create_time LIMIT 100000, 20;MySQL不是只查20行，而是先扫描前100020行，再把前面十万行丢弃。数据多了性能急剧下降。常用优化方案是延迟关联，先利用覆盖索引拿到主键ID，再回表取完整数据：
SELECT o.* FROM order_info o JOIN ( SELECT order_id FROM order_info WHERE user_id = 10086 ORDER BY create_time LIMIT 100000, 20 ) t ON o.order_id = t.order_id;这样前半段查询可以在索引上完成，大幅减少回表行数。另一种方案是记住上一页最后一条的排序值，用范围条件翻页，也就是游标分页，适合实时性要求不高的场景。
6.3 我对索引的几条实战建议
做索引设计这么多年，我总结了几条最实用的原则。
第一，先定位热点SQL，再设计索引，而不是看表上有什么列就建什么索引。一张表的索引，是给高频且慢的查询服务的。
第二，联合索引把等值条件放最左，范围条件放右边，尽量把ORDER BY字段放进去。这个顺序我每次设计都会默念一遍。
第三，区分度低的列不要单独建索引，比如状态、性别。联合索引里要根据实际情况决定是否放入。
第四，能用覆盖索引就覆盖索引，但不要无限加列，否则写入成本会反噬业务。
第五，任何索引变更上线前，先在测试环境用接近真实数据量的表做EXPLAIN和压测。索引不是玄学，慢就是慢，快就是快，跑一把就知道。
第六，线上永远不要高峰期大表DDL。就算Online DDL支持并发，也要留足冗余空间，观察主从延迟。
我记得有一次，线上某张表几百万数据，单条查询从几秒压到了几十毫秒，原因只是把一条WHERE user_id=? AND status=? ORDER BY create_time的SQL，从三个单列索引改成了一个联合索引。工具就在那里，原理也就那么点，真正拉开差距的，是能不能理解和设计好索引背后的数据组织方式。你现在写出SQL前，如果能习惯性想一下“这条查询怎么在索引树里走”，那说明你真的入门了。
