刚接手一个老订单系统的维护，日志里出现一条跑了两三秒的查询，排查了半天发现不是没建索引，而是建了单列索引却被优化器放弃，最后换成联合索引才把耗时压到几十毫秒。这种问题在日常开发里太常见了，今天把MySQL创建索引这件事从头到尾捋一遍，从底层原理到选型、实操、失效排查，都写清楚。

1. 为什么索引不是越多越好：先弄懂B+树与回表

很多人对索引的理解停留在“查得快”三个字，一遇到慢查询就顺手加索引，结果表上堆了十几个索引，写入越来越慢，查询也没快多少。实际上，索引是一把双刃剑，想用好它，得先知道它的代价和原理。

1.1 从“书本目录”说起：索引的底层逻辑

最直白的类比是纸质书的目录。目录能帮你快速翻到目标章节，但每本书出版时额外写目录也要增加成本。数据库索引也是一样：索引的本质是一棵排序好的查找树，MySQL InnoDB引擎里用的是B+树。

B+树有几个特点值得记住。数据只存储在叶子节点，非叶子节点只存键值。每个叶子节点用单向链表相连，方便范围查询时顺序扫描。树的高度一般很矮，常用的假设是三层结构——你只需要从根节点出发，大概做三次磁盘I/O就能定位到数据。

为什么三层就够？InnoDB默认页大小是16KB，假设主键是8字节的bigint，加上6字节的行指针，一个非叶子节点大约能容纳1170个键值对（16384除以14约等于1170）。叶子节点里一条记录连同其他列数据算1KB大小，能放16条数据。三层算下来，1170乘以1170再乘以16，大约是2190万条记录。也就是说，一张两千万行左右的表，用主键查询最多三次I/O就能找到数据，这就是索引快的原因。

1.2 聚簇索引与二级索引，以及回表

InnoDB的每张表都有且只有一个聚簇索引，通常建立在主键上。聚簇索引的叶子节点直接存着这一行的全部数据，所以走主键查询拿到叶子节点就等于拿到了整行。二级索引则是我们平时CREATE INDEX创建的索引，它的叶子节点只存索引列和主键值。

这里就引出了一个关键动作：回表。用二级索引查数据，先在二级索引的B+树里找到匹配的主键，再拿着主键去聚簇索引里把整行数据取出来。一次回表就是一次额外的磁盘I/O，如果查询命中了上千个主键，就要回表上千次，性能自然下降。

所以设计索引时，核心目标之一是尽量减少回表次数。如果能做到“覆盖索引”，也就是SELECT需要的列全部包含在索引里，那查询就根本不需要回表，性能会好一个档次。后面我会专门讲这个。

1.3 索引的真实成本清单

我经常跟团队里的小伙伴说，建索引之前先想想这三笔账：

第一笔是存储成本。每多一个二级索引，就多一棵B+树，磁盘占用直接翻倍都有可能。一张500万行的订单表，加一个大字段的索引可能多占几百MB空间，不能忽视。

第二笔是写放大。每次INSERT、DELETE或者UPDATE涉及索引列，InnoDB都要同步维护所有相关索引树。索引越多，写入路径就越长，高并发写入场景下这是实打实的性能损耗。

第三笔是缓冲池挤出效应。InnoDB有内存缓冲池，索引数据也缓存它在里面。索引越多、占用越大，热数据能留在内存里的就越少，反而可能导致命中率下降。这也是为什么“索引不是越多越好”，而且是“少而精”。

理解了这些，再回头看那些“一张表塞了十几个索引、写入慢到怀疑人生”的案例，问题就很清楚了。

2. 索引选型：普通索引、唯一索引、联合索引到底怎么选

建索引之前，必须先想清楚查询模式。我会按三个顺序来考虑：查询条件是什么，排序分组用什么，哪些列经常一起出现在WHERE里。

2.1 从查询模式反推索引

最忌讳的做法是拍脑袋决定“这个字段用得频繁，加个索引吧”。正确做法是把线上慢查询日志捞出来，看看真实SQL长什么样，再根据高频查询的WHERE条件、JOIN字段、ORDER BY字段来设计索引。

比如某购物系统的订单查询，慢日志里最频繁的是这么几条：

• 按user_id查某用户的所有订单
• 按status查某种状态的订单，并按created_time倒序
• 按订单号精确查单笔订单

那么索引设计就应该是：user_id单列索引，status和created_time联合索引，订单号字段建唯一索引。这样设计，每个查询都有索引可走，覆盖了绝大多数场景。

2.2 什么时候用唯一索引

唯一索引比普通索引更严格，要求列值不能重复。适合的业务场景很明确：业务上本身就要求唯一的字段，比如用户表的手机号、订单表的订单号、支付表的支付流水号。

这里有个细节值得注意：唯一索引除了约束数据唯一性，对查询优化也有帮助。优化器知道唯一索引最多命中一行，所以按唯一索引等值查询时，通常能直接判定最多返回一条记录，执行计划会更高效。如果一个字段业务上允许重复，比如用户昵称，就别硬上唯一索引，否则大量写入会因为重复值校验而变慢。

2.3 联合索引的顺序怎么安排

联合索引是很多新手最容易踩坑的地方。它遵循最左匹配原则：查询条件里必须从联合索引最左边的列开始连续匹配，索引才能生效。比如建了(status, created_time)联合索引，可以用上status=某个值和created_time范围查询的组合条件，但如果没有status条件，只想按created_time查，这个索引就用不上。

那字段顺序怎么定？我的经验是两条原则：区分度高的列放前面，等值条件优先于范围条件。比如status的区分度可能只有几个值，而user_id的区分度很高，如果经常按user_id等值匹配，user_id就应排在左边；如果经常按status加时间范围查，status在前、created_time在后更合理。

注意，区分度不是唯一标准，还要结合真实查询的频率。如果一个字段区分度高但很少出现在WHERE里，光“区分度高”没什么用。

2.4 覆盖索引与字段裁剪

前面说过，二级索引会回表。想彻底避免回表，就用覆盖索引：让索引里的列覆盖SELECT要查询的所有列。举个例子，订单表上经常执行SELECT order_no, created_time FROM t_order WHERE user_id = ?，如果建了(user_id, order_no, created_time)联合索引，这个查询就可以完全在索引树里完成，不需要回表。

所以建索引时不要只盯着WHERE字段，多看两眼SELECT字段。在保证索引不臃肿的前提下，尽量让常用查询直接命中覆盖索引。

下面这张表是我平时选型时的对照参考：

3. 创建索引实操与线上大表避坑

这一章偏操作，但最容易被忽视的不是语法，而是细节和线上安全。

3.1 常用创建语法

MySQL里创建索引的常见写法有三种。建表时直接定义索引，适合新表上线：

CREATE TABLE t_user ( id INT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64) NOT NULL, created_at DATETIME NOT NULL, INDEX idx_username (username) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

已存在的表用CREATE INDEX或者ALTER TABLE都行，效果等价：

CREATE INDEX idx_username ON t_user (username); ALTER TABLE t_user ADD INDEX idx_username (username);

我个人偏好CREATE INDEX，因为它可以单独管理，回滚也方便。ALTER TABLE除了加索引，还能顺便改表结构，语义不够纯粹。

索引命名也有讲究，我习惯用“idx_表名_字段名”这种格式，比如idx_user_username。命名清晰一点，后续排查问题看着就不会混淆，也方便定位哪个索引属于哪张表。

3.2 字段长度、字符集与索引长度限制

字符串字段建索引要留意长度限制。老版本InnoDB的索引键长度限制是767字节，新版本放开到3072字节。如果是utf8mb4字符集，一个字符最多占4字节，那么varchar(255)全字段建索引，光索引键就可能超过1020字节，快逼近新版上限。

所以长字符串字段，我用前缀索引比较多，只取前几十个字符，既能保证大部分区分度，又能减少索引占用空间和执行开销：

CREATE INDEX idx_user_email ON t_user (email(20));

要注意，前缀索引不能用于覆盖索引，也无法做精确的排序优化。所以如果业务对排序要求高，前缀索引并不合适。到底取多长，通常我会跑一条SQL统计不同前缀长度的区分度，取一个区分度接近全字段的值。

3.3 线上大表加索引，最大的坑是锁表

这个坑我踩过，也是想重点提醒的。给一张几百万行甚至上千万行的表直接执行ALTER TABLE ADD INDEX，MySQL早期版本会重建整张表，期间加排他锁，读写全部被卡住，业务直接中断。这在大促期间是不可接受的。

MySQL 5.6之后支持了ONLINE DDL，部分操作可以用INPLACE算法和NO LOCK策略，比如新增二级索引。即便如此，也要谨慎，因为执行过程中仍然有复制延迟、I/O开销、日志量放大等问题。主从架构下，DDL在主库执行完，从库还要重放，延迟可能飙升。

所以我的线上操作流程是：先在测试库或备份环境模拟执行一遍，拿到大致的执行时间；窗口期选在业务低峰；执行时关注主库告警和从库延迟；必要时用在线DDL辅助工具分批处理，把对线上的影响降到最低。总之，线上加索引不是随手跑一条SQL的事，需要走审批和演练。

3.4 区分度太低的字段，干脆别建

有时候一个字段明明经常出现在WHERE里，但区分度极低，比如订单表的status字段，可能只有“待支付、已支付、已退款”几个值。这种情况建单列索引，优化器算一下扫描行数，发现还不如全表扫描，直接放弃索引。白建，还浪费空间。

所以说，索引设计一定要结合数据分布。区分度低于某个阈值（我习惯看大约20%以下）的单列字段，优先级应该往后放，或者考虑放进联合索引做前缀过滤，配合其他高区分度字段一起生效。

4. 索引不生效？一条慢查询的完整排查链路

这一节直接给一条典型的慢查询排查过程，照着这个思路走，能少踩很多坑。

4.1 案发现场：日志里的慢SQL

某项目上线后，慢查询日志里频繁出现下面这条SQL，耗时在1.8秒左右：

SELECT * FROM t_user WHERE phone_no = 13800138000;

第一反应是没索引。一查表结构，phone_no建了索引，字段类型是VARCHAR(64)。那为什么没走索引？EXPLAIN看执行计划：

EXPLAIN SELECT * FROM t_user WHERE phone_no = 13800138000;

结果type为ALL，key为NULL，rows显示全表扫描。

4.2 第一重暗坑：隐式转换

问题出在传参类型上。phone_no是字符串类型，SQL里传入的是整数13800138000。MySQL做比较时，会把字符串值转成数值来比较，相当于在索引列上做了一次隐式类型转换，函数作用在索引列上，索引自然失效。

修复方式很简单，把参数改成字符串，或者写SQL时就用引号包起来：

SELECT * FROM t_user WHERE phone_no = '13800138000';

改完后EXPLAIN显示type为const，key命中了索引，查询降到毫秒级。这类因为隐式转换导致索引失效的案例，在代码里特别隐蔽，因为Java、Python这类语言传参时经常会把字符串和数字混在一起，DB层一转换就完蛋。

4.3 第二重暗坑：函数运算与前导模糊

跟隐式转换同类的还有函数运算。比如查询里写了：

SELECT * FROM t_user WHERE DATE_FORMAT(created_at, '%Y-%m-%d') = '2025-01-01';

created_at建了索引也没用，因为索引列套上了DATE_FORMAT函数，优化器没法直接利用B+树的排序结构。正确的写法是改成范围查询：

SELECT * FROM t_user WHERE created_at >= '2025-01-01 00:00:00' AND created_at < '2025-01-02 00:00:00';

前导模糊查询也一样。LIKE 'abc%'还能借索引的顺序性走范围扫描，LIKE '%abc%'就没办法了，前面有通配符，B+树无从匹配。遇到难以避免的前导模糊，可以额外考虑全文索引，或者干脆依赖外部搜索服务，别死磕MySQL。

4.4 第三重暗坑：OR条件的陷阱

OR也是让人头疼的写法。比如：

SELECT * FROM t_user WHERE username = 'abc' OR status = 1;

即使username建了索引，OR另一个条件是status，而status又不在索引里，优化器通常会把两个条件合并判断，最后选择全表扫描。处理办法一般是改写为UNION拼接：

SELECT * FROM t_user WHERE username = 'abc' UNION SELECT * FROM t_user WHERE status = 1;

两个分支各自走各自的索引，效果立竿见影。当然，如果两个OR条件都是索引列，而且优化器能确认合并后效率更高，也可能走索引合并（index merge），但这需要看实际执行计划，不能想当然。

4.5 最迷惑的一种：优化器觉得“全扫更划算”

还有一类情况，索引本身建得没问题，但优化器算了一笔账，认为全表扫描代价更低，于是弃用索引。典型场景是单列索引区分度太低。上面提到status例子就是如此，假如一张表500万行，95%的行status都是“已支付”，那按status过滤的“收益”太小，优化器宁可全表扫。

碰到这种情况，把单列索引改成联合索引，让过滤条件组合起来提高区分度，是更合理的解法。这也是为什么我一直强调：索引要跟着查询模式走，不能被“哪个字段常用”误导。

下面这张表汇总了常见失效场景和处理思路：

5. 用EXPLAIN验证索引效果，别靠感觉优化

加完索引，不要只是“看起来行了”。我的习惯是每次调整索引之后，都用EXPLAIN确认执行计划，并记录优化前后耗时，心里才踏实。

5.1 EXPLAIN里最值得盯的几个字段

• type列：取值从好到坏大致是const、eq_ref、ref、range、index、ALL。出现ALL说明全表扫，要警惕。
• key列：实际命中的索引名。如果是NULL，说明压根没用到索引。
• rows列：优化器预估要扫描的行数。这个值越小越好，跟查询耗时强相关。
• Extra列：出现Using index说明覆盖索引生效，Using filesort说明文件排序，Using temporary说明临时表，这两个都值得优化。

举个实际例子，同样一条等值查询，优化前后的EXPLAIN对比如下：

优化前：

• type: ALL
• key: NULL
• rows: 500万

优化后：

• type: ref
• key: idx_user_username
• rows: 1

这一眼就知道索引生效了。

5.2 加了索引却不用？先刷统计信息

有一种情况，索引刚创建完，跑EXPLAIN仍然没走索引。别急着删索引，先想一下统计信息。

InnoDB的优化器依赖统计信息来判断执行成本，统计信息慢了或失真，优化器就可能选错执行计划。这时候手动刷新一下统计信息，往往就有改善：

ANALYZE TABLE t_user;

还有更细致的排查方式：用OPTIMIZER_TRACE看优化器的决策过程，能看到它到底在选择什么、为什么放弃索引，比盯着执行计划猜来猜去高效多了。线上遇到“加了索引不走”的诡异问题，我建议第一步就打开trace。

5.3 永远不要在索引列上做“加工”

这一条算是最有价值的心法，也是贯穿全篇的底层原则：保持索引列干干净净。

无论什么数据库，索引列上做了运算、类型转换、函数套用，都会破坏索引的有序性，导致优化器无法匹配。写SQL时，把计算、转换的任务交给“值的这一侧”，而不是“列的这一侧”，这是决定索引能不能生效的关键一环。

我的最后一点实际体会

建索引这件事，写几行SQL简单，真正考验的是对整个系统数据分布和查询模式的理解。我自己的习惯是：每次建索引都在表结构的注释文档里写清楚“这个索引给哪类查询服务”，避免几个月后没人知道某个索引该不该留、能不能删。同时，在新上线前拉一批线上真实SQL样本，逐一跑EXPLAIN并记录执行计划，有对比、有依据，后续出现性能问题追溯起来也有章可循。这样一套习惯下来，SQL慢查询明显少了，线上出问题的概率也低了很多。