MySQL索引这个话题，我从入行第一天折腾到现在，踩过的坑攒起来估计能写一本小册子。很多朋友一上来就问“怎么建索引”，但真正遇到线上慢查询的时候，却发现索引加了跟没加一样，甚至越加越慢。这篇文章我不讲教科书那套，就从一个实际干活的人视角，把MySQL索引的底层逻辑、建索引的实操套路、以及那些让索引“白建”的坑，一次说清楚。不管你是刚接触数据库的新人，还是被慢SQL折磨过的后端开发，这篇文章应该都能帮你少走几条弯路。
1. 索引到底是什么：先搞懂它的底层逻辑
1.1 从数据页到B+树
MySQL的InnoDB引擎里，数据不是一条条平铺在硬盘上，而是存在一个个“数据页”里，每个页默认16KB。你可以把数据页想象成一本新华字典的某一页，字典要有拼音索引、部首索引，才能快速定位到某一个字；数据库里的索引，就是给这些数据页建立的一套“快速定位目录”。
为什么MySQL默认选B+树而不是二叉树或者哈希表？这里有个关键点：B+树的非叶子节点只存索引键和指针，不存数据，所以一个16KB的页可以容纳特别多的“目录项”。三层B+树往往就能存上千万条记录，而你查询时只要从根节点走三次I/O就能找到叶子节点。相比二叉树那种动不动就几十层深的结构，B+树在机械硬盘和SSD上都是最优解。
哈希索引在InnoDB里也有，但它是自适应哈希索引，只在内存里由引擎自动维护，不归你管。如果要手动建哈希索引，那是在MEMORY引擎里的事。日常我们说的“加索引”，默认都是加B+树索引。
1.2 索引要付出什么代价
别一听索引能提速就每个字段都加索引，索引不是免费的。每建一个二级索引，就等于多维护一棵B+树。写入数据时，不仅要更新数据页，还要同步更新这棵索引树；数据量越大，索引越多，写入和修改的代价就越明显。
我见过最夸张的一个表，业务字段不到30个，索引建了20多个，结果每次批量导入数据都慢得像蜗牛。后来一查，大部分索引是开发同学按“感觉”建的，实际查询里根本没用到。用一句话说：索引是拿空间换时间，拿写入性能换查询性能，这个账必须算清楚。
1.3 InnoDB的聚簇索引与二级索引
InnoDB的表数据本身就是一棵B+树，这棵树的主键就是聚簇索引。叶子节点直接存整行数据，所以通过主键查询，一次索引查找就能拿到全部字段，速度最快。如果你没有显式定义主键，InnoDB会找一个非空唯一列当主键；再找不到，就偷偷生成一个隐藏的rowid做主键。
二级索引（也叫普通索引）的叶子节点存的是索引列的值和主键值。用二级索引查询时，会先在二级索引树里找到主键，再拿着主键去聚簇索引树里“回表”查整行数据。这就是为什么主键查询比其他查询快一大截的原因。能理解这个机制，后面讲“覆盖索引”和“索引失效”就顺了。
2. 搞懂MySQL的索引类型与建索引语法
2.1 主键索引与普通索引的区别
建表时指定PRIMARY KEY就是主键索引，一个表只能有一个主键索引，但主键可以由多个列组成，这就是联合主键。主键索引的值不能为NULL，而且必须唯一。它在InnoDB里就是聚簇索引本身，整张表的物理存储顺序都跟着主键走。
普通索引又叫二级索引，用KEY或INDEX关键字创建。它的价值在于给高频查询的列建立快速检索路径，比如用户表里的手机号、订单表里的用户ID。普通索引允许重复值和NULL，自身不改变表数据的物理存储顺序。
一个常见疑问：既然普通索引最后要回表，那它是不是比主键查询慢很多？不一定。如果查询的字段恰好都在索引树里（覆盖索引），就不用回表；如果查询要回表，也多一次主键树的查找。实际开发中，大部分慢查询问题不是“回不回表”决定的，而是“能不能用上索引”决定的。
2.2 唯一索引与唯一约束的关系
唯一索引UNIQUE KEY有两大作用：一是约束，保证列或列组合的值不重复；二是索引，加速查询。业务上遇到“一个用户只能有一个未删除的手机号”这种需求，直接在数据库层加唯一索引是最靠谱的兜底方案。
但我要多说一句：不要把唯一索引当普通索引乱用。如果业务上根本没有唯一性需求，只是想让查询快一点，那加普通索引就够了。因为唯一索引在写入时要做额外唯一性校验，性能会比普通索引差一点。有些团队习惯把所有字段都设成唯一索引防重，这纯属自找麻烦。
2.3 复合索引的前缀原则
复合索引就是给多个列建一个索引树，比如INDEX idx_user_status(user_id, status)。它的排序规则是：先按第一个列排序，第一个列相同的再按第二个列排序，以此类推。所以复合索引有个核心规律——最左前缀原则：查询条件里必须包含最左侧的列，索引才能被用上。
你建了(user_id, status)的复合索引，下面三类查询的待遇完全不同：
| 查询条件 | 是否使用该索引 | 原因 |
|---|---|---|
| WHERE user_id = ? | 使用 | 命中最左列 |
| WHERE user_id = ? AND status = ? | 使用 | 完全匹配 |
| WHERE status = ? | 不使用 | 没从最左列开始 |
我在实际项目里基本不建单列索引，更喜欢按业务查询组合建复合索引。比如订单表高频查询是“按用户查订单”，那就建(user_id, create_time)的复合索引，一次搞定用户过滤和按时间排序，这种做法后面还会展开讲。
2.4 全文索引的使用边界
全文索引用于大文本字段的模糊搜索，语法是FULLTEXT KEY。很多人误以为LIKE '%关键字%'就是全文搜索，其实那只是普通的模糊匹配，用不上B+树索引；全文索引走的是倒排索引机制，适合对文章、标题做分词检索。
InnoDB的全文索引在5.6版本之后才原生支持，但说实话，在真实业务里，如果只是几个字段的简单搜索，很多团队宁愿用Elasticsearch或者专业的搜索引擎，不会把全文索引用在MySQL这种OLTP数据库上。如果你确实要在MySQL里用全文索引，记得用MATCH()...AGAINST()语法去查，而不是LIKE。
3. 面对实际查询怎么设计索引：where a and b 这类问题的解法
3.1 分析SQL的执行计划
很多热词里都有人在搜“mysql where条件a and b,应该怎么建索引”，这绝对是最典型的索引设计场景。要回答这个问题，第一步不是拍脑袋，而是用EXPLAIN看SQL到底怎么执行。
举个真实例子：
SELECT * FROM order_info WHERE user_id = 10086 AND status = 'paid' ORDER BY create_time DESC;执行计划里最关键的几个字段是type、key、rows、Extra。type从好到差依次是system、const、eq_ref、ref、range、index、ALL。如果看到type=ALL，说明全表扫描，索引没吃上；看到type=ref或range，说明用上了索引；看到Extra里有Using filesort，说明排序没走索引，可能要优化。
我一个习惯是，所有上线SQL都先跑一遍EXPLAIN，把type低于ref的SQL列为重点排查对象。这一步能过滤掉80%的索引设计问题。
3.2 复合索引列顺序怎么排
回到where a and b的问题：复合索引的列顺序，直接决定索引能不能高效使用。原则其实很简单，就看三点。
第一点，区分度高的列放前面。比如用户ID的区分度远高于状态字段，因为用户ID的值基本都不同，而status可能只有几个值。把user_id放复合索引最左边，可以用索引快速把数据范围缩小到一个用户的所有订单；反过来，把status放最左边，第一步只能过滤出一大片相同状态的记录，效率就低了。
第二点，等值条件优先。如果where里user_id是等值条件=，status也是等值条件=，那谁前谁后影响不大，因为B+树可以同时用两个等值条件精确匹配。但如果有范围条件，比如create_time > '2023-01-01'，那就把等值条件的列放前面，范围条件的列放后面。因为B+树一旦遇到范围匹配，后面的列就无法继续用索引定位了。
第三点，查询频率高的列优先。假设业务上80%的查询只按user_id搜，20%按user_id+status搜，那复合索引就建(user_id, status)，而不是status在前。索引是给实际查询服务的，不是给理论完美服务的。
ALTER TABLE order_info ADD INDEX idx_user_status_time (user_id, status, create_time);这个索引可以覆盖“按用户查状态和时间排序”“按用户查所有订单并按时间倒序”“按用户和状态查”这些高频场景，一箭三雕。
3.3 覆盖索引与回表优化
覆盖索引是指查询所需的所有字段都在索引树里，查询可以直接用二级索引结果返回，不需要回表。这是MySQL里性能最接近主键查询的一种方式。
举个例子：
SELECT user_id, status FROM order_info WHERE user_id = 10086;如果你建了idx_user_status(user_id, status)，这条SQL的Extra会显示Using index，意思就是覆盖索引生效，不需要回表。但如果SELECT的列里多了一个order_amount，而order_amount不在索引树里，就必须回表去聚簇索引里取，速度就会慢一些。
想优化回表，思路有两个：一是把常用的查询字段塞进复合索引里，也就是“索引冗余”；二是用主键查询，天然不需要回表。第一种方式要控制好度，不要把整表字段都塞进索引，那跟复制一张表没区别，写入成本直接爆炸。
4. SQL写法里那些让索引“白建”的坑
4.1 函数运算和隐式转换
索引列上做函数运算，这是索引失效的头号杀手。你明明建了索引，写出来的SQL却不去用它，多半是这个原因。
-- 索引失效 SELECT * FROM user WHERE DATE(create_time) = '2023-01-01'; -- 索引生效 SELECT * FROM user WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2023-01-02 00:00:00';DATE()函数把索引列包起来了，MySQL只能先把每条记录的create_time都计算一遍，再跟'2023-01-01'比较，索引自然没法走。解决办法就是改写为范围查询，这也是我编码规范里的一条强制要求：不要在索引列上使用函数，不要对索引列做隐式类型转换。
隐式转换也很隐蔽，比如手机号字段是varchar，你用数字去查：
-- 如果phone是varchar，下面的SQL会让索引失效 SELECT * FROM user WHERE phone = 13800138000;MySQL会把字符串列转成数字比较，相当于对索引列做了隐性函数操作，索引就废了。开发规范里应该约定：查询条件里的类型必须与字段类型一致，字符串就加引号。
4.2 模糊查询与OR条件
LIKE '关键字%'可以用索引，LIKE '%关键字'、LIKE '%关键字%'用不了索引，这个知识点很多人都知道，但实际还是会踩。
原因很简单：B+树是按列值排序的，前缀匹配时，索引可以从“以关键字开头”的位置顺序扫描；后缀匹配时，你不知道目标值排在哪个位置，只能全表扫。业务上非要后端模糊匹配，建议方案是用覆盖索引硬扛，或者干脆交给全文索引、搜索引擎这类专门工具。
OR条件同样容易让索引失效。如果OR前后的条件都走索引倒还好，但只要有一个条件不能走索引，MySQL可能干脆选择全表扫描。比如：
SELECT * FROM order_info WHERE user_id = 10086 OR status = 'paid';如果status字段单独没索引，这个OR就会导致整体全表扫。优化方法是拆成两个SQL用UNION合并，或者给OR后面的列补上索引。
4.3 排序、分组和JOIN场景下的索引应用
ORDER BY排序如果索引帮不上忙，Extra里会写Using filesort。虽然叫filesort，但不一定真用磁盘文件排序，可能是内存排序，但性能总归比走索引排序差。
想让ORDER BY走索引，核心是排序字段要和查询条件里的等值字段一起，组成复合索引的最左前缀。比如前面的例子，where里有user_id等值条件，order by里有create_time，那建(user_id, create_time)复合索引后，排序就能直接沿着索引顺序扫描，不用额外排序。
GROUP BY的优化思路类似。它本质上也是先排序再分组，如果group by字段能通过索引有序，性能会漂亮很多。JOIN的关联字段如果不加索引，每连接一行就要全表扫一次关联表，这是大表JOIN慢的最常见原因。所以JOIN的ON字段基本是必加索引，而且最好是两边类型一致。
5. 常见问题排查与运维实操
5.1 索引失效/效率低的排查套路
遇到一个慢SQL，我通常按这个顺序排查：第一步，EXPLAIN看执行计划，确认type、key、rows和Extra；第二步，看索引列上有没有函数、隐式转换、OR条件这类“杀手”；第三步，看复合索引的列顺序是否与查询条件匹配；第四步，看是不是统计信息不准确，导致优化器选错了执行计划。
最后这一步很值得单独说。MySQL的优化器是依据索引统计信息估行数的，如果统计信息迟迟没更新，估算偏差大，优化器就可能不走最优索引。处理办法是先跑一下：
ANALYZE TABLE order_info;这个操作会重新统计索引分布信息。但注意，对超大表执行ANALYZE会有一定锁和性能影响，我一般挑业务低峰期做。还有一招是使用FORCE INDEX强制指定索引，但这属于治标不治本，线上不建议长期依赖。
如果索引一直在用，但就是慢，我再看看“回表”次数和索引选择度。比如通过二级索引查出10万条主键，再逐条回表，这时候还不如全表扫。这种情况下，要么调整SQL减少返回量，要么改成覆盖索引，要么考虑分区表。记住：索引不是万能药，数据量上了千万级，分页深翻页、大量回表、复杂的统计分析，都得重新设计查询方案。
5.2 索引维护与监控
索引建好不是一劳永逸，日常维护很重要。第一个要监控的是索引碎片。频繁增删改会导致B+树页分裂和空洞，索引空间变虚胖，查询效率下降。可以定期查看表的状态：
SHOW TABLE STATUS LIKE 'order_info';对比Data_length和Max_data_length的值，就能大致判断碎片率。碎片超过一定比例，我一般会做索引整理。但在线DDL虽然MySQL 5.6以后支持了ALGORITHM=INPLACE，大表的ALTER操作还是会给主从同步带来压力，需要挑窗口期执行。
第二个要监控的是无效索引。长期没被使用的索引纯属浪费写性能，可以通过performance_schema或者sys库的schema_unused_indexes视图去查。我的习惯是每季度扫一遍，把60天内都没命中的索引清理掉。
5.3 索引操作的注意事项
线上建索引，尤其要注意锁表和主从延迟问题。MySQL 5.6之前，ALTER TABLE建索引经常全程锁表，业务直接卡死；5.6之后默认使用Online DDL，但仍要小心大表执行期间的磁盘I/O和主从延时。稳妥操作是使用gh-ost、pt-online-schema-change这类工具做在线变更，或者至少选业务低峰期执行。
另外，创建索引时建议给索引起名规范且语义清晰，比如idx_user_id、uniq_phone，一看就知道类型和字段。运维排查时，一堆系统自动生成的冗长索引名，光看名字就知道这团队没有索引管理意识。删除索引要谨慎，先查清楚是否有SQL依赖它，再决定是否回收。
我在实际运维里还踩过一个坑：给一个核心大表加字段，顺手在ALTER里同时加了索引，结果因为一条语句里既有表结构变更又有索引变更，导致回滚困难、同步异常。后来我的规范是：表结构变更和索引变更分开执行，每一步都验证通过再做下一步。
5.4 几个亲测有效的优化小经验
这里分享几个我平时直接用的小经验，不保证玄学，但都是亲测有效的。
第一，EXPLAIN看Extra列出现Using index condition时，是ICP（索引条件下推）在生效。这个特性默认开启，能减少回表次数，不用慌，但要注意它和覆盖索引是两码事，别混淆。
第二，分页深翻页的优化。很多报表接口习惯用LIMIT 100000, 20，这种写法即使走索引也很慢，因为MySQL要扫描并丢弃前10万条记录。我一般改成先通过覆盖索引拿到主键，再回表取数据：
SELECT * FROM order_info JOIN ( SELECT id FROM order_info WHERE user_id = 10086 ORDER BY create_time LIMIT 100000, 20 ) t ON order_info.id = t.id;这个技巧本质上是把深分页的消耗控制在二级索引上，毕竟二级索引的叶子节点小，同样一张页能装更多记录，扫描速度会快很多。
第三，写批量UPDATE和DELETE时，千万别一次更新几十万行。除了锁范围大、回滚日志爆炸，还会拖垮主从同步。改成按主键分批处理，每批几百条，既稳又快，也不容易把数据库搞死。
第四，关于统计信息还有一个日常坑：偶尔某条SQL查询突然从毫秒级变秒级，但表结构和SQL都没改，也排除了锁竞争，那很可能是因为统计信息或执行环境变化导致执行计划变了。这种我一般先ANALYZE TABLE再观察，如果频繁出现，会考虑固定执行计划，或者评估是否需要重建索引。索引设计从来不是写一条CREATE INDEX就完事，它需要结合查询模式、数据分布、写入压力持续调优。
最后再说一句，很多新手在网上搜“mysql创建索引”“mysql索引失效”，看到十几种失效规则就背，背完还是不会用。我的体会是，学索引最有效的方式，是拿一个真实的慢查询日志当教材，逐个SQL去做EXPLAIN分析，把执行计划从ALL优化到ref或const。这个过程走几遍，你对B+树、聚簇索引、回表、覆盖索引的理解就全串起来了。后面的索引设计，基本就是水到渠成的事。
