MySQL索引设计避坑指南：从原理到实践的注意事项

1. 索引设计前，先想清楚这几个问题

做 MySQL 开发或者 DBA 的朋友应该都有体会，索引这东西用好了是神器，用不好就是定时炸弹。面试题里问"建索引有哪些注意事项"，看起来是个基础题，但真正能把这个问题讲透的人不多。很多人背了一堆规则，什么"不要建太多索引""不要对大字段建索引"，但真到实际场景里，还是不知道该怎么取舍。

先说个最常见的场景。线上有个订单表，数据量到了千万级别，查询越来越慢。开发同学一看慢查询日志，发现某个 where 条件频繁出现，随手就加了个索引。结果呢？查询是快了，但写入变慢了，磁盘占用也涨了，而且因为这个索引建的太宽，优化器反而不走这个索引，走了全表扫描。这种案例我见过太多次了。

所以建索引之前，先把几个基础问题想清楚：

• 这个索引到底服务哪些查询？是单条件查询还是多条件组合？
• 索引建在哪些列上？列的选择性怎么样？
• 这个表的写入频率高不高？索引数量多了能不能扛住？
• 会不会出现索引失效的情况？比如隐式类型转换、函数运算这些坑。

这篇文章就围绕这几个问题展开，结合我实际踩过的坑，把 MySQL 建索引的注意事项一次性说清楚。不管你是初中级开发还是已经在带团队的技术负责人，这里面的内容都能用得上。

2. 核心思路拆解：索引不是越多越好，也不是越窄越好

2.1 先理解索引的本质：用空间换时间的取舍

索引的本质是数据结构，底层是 B+ Tree。B+ Tree 的特点是数据都存在叶子节点，并且叶子节点之间用指针串联，这样范围查询和排序就特别快。当你执行select * from orders where user_id = 10086 and status = 1时，如果没有索引，MySQL 只能全表扫描，一行一行地比对，数据量大时就是灾难。

有了索引之后，MySQL 先通过 B+ Tree 快速定位到符合条件的记录位置，再去主表回表查询完整数据。这个过程就像查字典一样，先通过偏旁部首定位到页码范围，再一页一页翻，效率就上来了。

但索引不是白给的。每建一个索引，就意味着：

• 写入数据时要额外维护一颗 B+ Tree
• 更新数据时要同步修改索引结构
• 磁盘上要多占用一份空间
• 查询时优化器要多一个选择，选错了反而变慢

所以索引设计本质上就是一场"读写权衡"。读多写少的表，索引可以多建几个；写多读少的表，索引要谨慎再谨慎。

2.2 复合索引最核心的一个原则：最左前缀

面试里还有个高频问题是："where 条件 a and b，应该怎么建索引？"

很多人的第一反应是分别给 a 和 b 各建一个单列索引。这个思路不能说完全错，但绝大多数情况下不是最优解。MySQL 查询优化器面对多个单列索引时，虽然可能走 index merge，但更多时候只能选一个索引用，另一个就被浪费了。更合理的做法是建一个复合索引(a, b)。

这里面的关键就是最左前缀原则。复合索引(a, b, c)实际生效的场景是：

• where a = ?
• where a = ? and b = ?
• where a = ? and b = ? and c = ?
• where a = ? and b > ? and c = ?（注意范围查询后面的列不生效）

但下面的场景就用不上这个复合索引：

• where b = ? and c = ?，没有 a 开头，索引直接废掉
• where a = ? and c = ?，只有 a 能用到，c 用不上，中间断了

这个坑我见得太多太多了。很多人建了复合索引，写 SQL 的时候却不注意字段顺序，导致索引根本没被用到。优化器虽然在某些版本里能做一定程度的调整，但最保险的做法永远是：查询条件的字段顺序尽量和索引字段顺序保持一致。

2.3 区分度和选择性：索引列怎么选

索引列不是随便选的。一个列的"区分度"决定了索引的查询效率。如果一列只有两个值，比如 status 只有 0 和 1，那这一列单独建索引几乎没有意义，选择性太低了。查询的时候，MySQL 通过索引可能还是要过滤掉一半以上的数据，还不如直接全表扫。

区分度好的列是什么样？比如 user_id、order_no、手机号、身份证号这类唯一性强的字段。一个判断经验是：区分度 = COUNT(DISTINCT column) / COUNT(*)，这个值越接近 1，说明这个列选择性越好，越适合建索引。经验上这个比值大于 0.1 就算不错了，低于 0.01 就要慎重考虑。

我们从一个实际案例来看这个问题：

CREATE TABLE `orders` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `order_no` varchar(32) NOT NULL, `user_id` bigint(20) NOT NULL, `status` tinyint(4) NOT NULL DEFAULT '0', `sku_id` bigint(20) DEFAULT NULL, `amount` decimal(10,2) NOT NULL, `create_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设业务上高频查询是"查某个用户最近的订单"和"根据订单号查订单"，那么合理的索引设计是：

ALTER TABLE `orders` ADD INDEX `idx_user_create` (`user_id`, `create_time`), ADD UNIQUE KEY `uk_order_no` (`order_no`);

第一个复合索引支撑按用户查找并按时间排序，第二个唯一索引支撑订单号的精确查询。这个设计不是拍脑袋想的，是基于查询模式推导出来的。

3. 建索引时必须避开的几个细节坑

3.1 隐式类型转换：索引失效的第一大元凶

索引失效的情况有很多，但日常开发中最常见的就是隐式类型转换。比如表里user_id是 varchar 类型，查询时写了where user_id = 10086，MySQL 会把字符串和数字比较时自动把字段转换成数字，导致字段上的索引失效。

为什么？因为索引是基于原始字段值构建的，一旦字段本身被函数或类型转换处理过，B+ Tree 里的有序排列就失效了。优化器只能放弃索引，走全表扫描。

我见过一个真实案例，某用户表手机号字段是 varchar(20)，查询条件写成where phone = 13800138000，明明有索引，但 EXPLAIN 出来 type 是 ALL，数据量几百万时查询要好几秒。后来把 SQL 改成where phone = '13800138000'，加上引号之后立刻走了索引，耗时降到毫秒级。

排查思路也很简单，用 EXPLAIN 看执行计划：

EXPLAIN SELECT * FROM user WHERE phone = 13800138000;

如果看到 type 是 ALL 或者 key 为 NULL，优先怀疑字段类型和值类型不一致。

3.2 函数运算和表达式：索引的隐形杀手

除了类型转换，针对索引列使用函数也是常见的失效场景：

WHERE DATE(create_time) = '2024-01-01' WHERE YEAR(create_time) = 2024 WHERE amount + 100 > 500

这些都让索引列参与了运算，B+ Tree 里存的是原始值，没法直接用到。正确做法是把函数运算移到等号的另一边：

WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'

这个改写既保持了业务逻辑，又让索引列保持"裸"状态，索引就能用上了。

3.3!=、NOT IN、LIKE '%xxx'：这几个操作符要小心

很多优化器版本中对!=和NOT IN的处理并不理想，往往放弃了索引。范围查询、不等值查询本身就意味着 B+ Tree 的最优匹配失效，优化器需要扫描的数据范围会变大。

LIKE查询也是一样。LIKE 'abc%'是可以用到索引的，因为前缀是确定的。但LIKE '%abc'和LIKE '%abc%'就不行了，开头是通配符，B+ Tree 无法定位起点。

如果业务上确实需要模糊搜索，尤其是全文搜索场景，建议引入全文索引或者外部搜索组件，而不是硬扛着用 LIKE。

3.4 索引列上做排序：ORDER BY 也能走索引

ORDER BY 能不能用到索引，很多人忽略了。其实如果排序字段正好满足最左前缀，MySQL 就可以利用索引的有序性直接返回结果，避免 filesort。举个例子：

SELECT * FROM orders WHERE user_id = 10086 ORDER BY create_time DESC;

如果索引是(user_id, create_time)，那这个查询既可以用 user_id 等值匹配定位，又可以利用 create_time 在索引中的有序性直接排序，性能非常好。而如果是where user_id = 10086 order by amount，amount 不在索引里，就需要额外做 filesort，虽然文件排序在小数据量下问题不大，但数据量大时也是性能瓶颈。

所以设计复合索引时，要把查询条件字段和 ORDER BY 字段一起考虑。条件字段放前面，排序字段放后面，这是最优解。

3.5 前缀索引：大字段的折中方案

有些大字段，比如长文本、长字符串，直接建索引空间占用太大，索引效率也不高。这时候可以用前缀索引，只取字段的前 N 个字符建立索引。

ALTER TABLE article ADD INDEX idx_title_prefix (title(20));

注意，前缀索引有代价：无法用于 ORDER BY 和 GROUP BY，也无法用于覆盖索引。选择前缀长度时需要测试区分度，比如：

SELECT COUNT(DISTINCT LEFT(title, 10)) / COUNT(*) AS ratio10, COUNT(DISTINCT LEFT(title, 20)) / COUNT(*) AS ratio20 FROM article;

找到区分度可接受又节省空间的最短前缀长度即可。比如 10 个字符区分度就到了 0.95，20 个字符才 0.97，那就选 10。

4. 实操过程：从慢查询 SQL 到一个完整索引方案

4.1 第一步：拿到慢查询日志，定位高频 SQL

我平时做索引优化，第一步从来不是急着建索引，而是把慢查询日志和分析结果先拉出来。

SHOW VARIABLES LIKE 'slow_query_log%'; SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

当然生产环境一般都已经配置好了，直接去 slow log 里捞。把耗时超过 1 秒的 SQL 都收集起来，按执行次数和总耗时排序。注意一件事：高频但单次很快的查询，可能不在慢日志里，但它占用的总时间不可忽略。所以还要配合 performance_schema 或者 events_statements_summary_by_digest 这类统计表来看。

4.2 第二步：用 EXPLAIN 分析执行计划

拿到 SQL 之后，最核心的一步是用 EXPLAIN 看执行计划。

EXPLAIN SELECT order_no, amount, create_time FROM orders WHERE user_id = 10086 ORDER BY create_time DESC LIMIT 20;

重点关注这几个字段：

还有一个容易被忽略的细节：key_len。它表示索引使用的字节数，可以帮助判断复合索引到底用到了几列。比如idx_user_create(user_id, create_time)，user_id 是 bigint，长度为 8 字节；create_time 是 datetime，长度为 5 字节。如果 key_len 只有 8，说明只用了 user_id 这一列，create_time 没有参与索引检索。这个细节对于排查复合索引是否完整生效特别有用。

4.3 第三步：结合 SQL 模式推出候选索引

来看一个我在实际项目中处理的案例。业务表结构大概是这样的：

CREATE TABLE `payment_record` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `payment_no` varchar(64) NOT NULL, `merchant_id` bigint(20) NOT NULL, `channel` tinyint(4) NOT NULL, `pay_status` tinyint(4) NOT NULL, `pay_amount` decimal(12,2) NOT NULL, `pay_time` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

高频查询有三类：

-- Q1: 商户查询某时间段支付记录 SELECT * FROM payment_record WHERE merchant_id = ? AND pay_time BETWEEN ? AND ?; -- Q2: 根据支付单号查记录 SELECT * FROM payment_record WHERE payment_no = ?; -- Q3: 商户统计某天的交易金额和笔数 SELECT merchant_id, COUNT(*), SUM(pay_amount) FROM payment_record WHERE merchant_id = ? AND pay_time BETWEEN ? AND ?;

针对 Q1 和 Q3，它们都是merchant_id + pay_time的组合查询，所以建一个(merchant_id, pay_time)复合索引就够了。Q2 是等值查询，payment_no 唯一性强，建一个唯一索引即可。

ALTER TABLE payment_record ADD UNIQUE KEY uk_payment_no (payment_no), ADD INDEX idx_merchant_time (merchant_id, pay_time);

这里有一个优化点值得说：Q3 只查merchant_id, COUNT(*), SUM(pay_amount)，这三个字段都能覆盖在idx_merchant_time上吗？不能，因为 pay_amount 不在索引里。如果想让 Q3 完全走覆盖索引，可以改成(merchant_id, pay_time, pay_amount)三列复合索引。但这样索引会更宽，写入成本更高。权衡之后，如果 Q3 的执行频率非常高，宽索引更值得；如果 Q1 Q2 才是大头，那就保持两列索引，让 Q3 回表，代价也不大。

4.4 第四步：验证索引效果，别只看执行计划

索引加完之后，我建议大家做一次真实的压测或抽样测试，不要只看 EXPLAIN 结果。EXPLAIN 只是预估，真实执行时间还受缓存、并发、数据分布的影响。

SELECT * FROM payment_record WHERE merchant_id = 12345 AND pay_time BETWEEN '2024-01-01' AND '2024-01-31' LIMIT 10;

多次执行对比平均耗时。同时注意看 profiling：

SET profiling = 1; SELECT * FROM payment_record WHERE merchant_id = 12345 AND pay_time BETWEEN '2024-01-01' AND '2024-01-31' LIMIT 10; SHOW PROFILES;

如果优化后 SQL 的耗时从几百毫秒下降到几毫秒，说明索引方案是成功的。如果没有明显改善，就要重新检查是不是索引失效了，或者数据分布导致优化器判断错了。

另外，记得用optimizer_trace看优化器的决策过程。有时候优化器就是不走你建的索引，原因可能是统计信息不准确。这时候ANALYZE TABLE payment_record;可以刷新统计信息。这个手段常被忽略，效果却很明显。

4.5 第五步：上线前的索引管理规范

最后说点管理层面的经验。建索引不能只建不拆。我见过一张表，开发迭代了好几个版本，索引建了十几个，其中很多已经没人用了。不仅占空间，还影响写入性能。所以我一般建议团队里运维一个索引治理机制：

• 每次索引变更都要留 SQL 脚本和说明文档
• 定期用慢查询日志和sys.schema_unused_indexes视图找未使用索引
• 确认无业务依赖的索引，在业务低峰期删除

查看未使用索引：

SELECT * FROM sys.schema_unused_indexes;

这个视图能直接列出哪些索引自服务器启动以来从未被使用过，删之前再确认一次，基本不会有问题。

5. 常见问题与排查技巧实录

整理一下我在工作中经常遇到的索引问题，以及对应的排查方法：

下面再展开几个重点问题，给出更具体的操作方案。

5.1 为什么有时候 MySQL 有索引却不用？

这是新手最困惑的地方。明明加了索引，EXPLAIN 出来还是全表扫描。常见原因有三个：

第一，数据量太小。如果表只有几百行，MySQL 优化器认为直接全表扫描比走索引+回表更快。索引访问本身有开销，需要先在 B+ Tree 上查找，再去主表读取数据。对小表来说，这个开销比全表扫描还大。这种情况不用强求走索引。

第二，区分度太低。比如性别字段，只有两个值，走索引返回的数据量可能是全表的一半，优化器还不如全表扫。这种列就不该建索引。

第三，隐式转换或函数运算。前面详细说过，这是最常见的坑。

还有一种情况容易被忽略：统计信息过期。MySQL 的优化器基于表的统计信息来估算扫描行数，如果统计信息陈旧，优化器可能误判。解决办法就是：

ANALYZE TABLE table_name;

5.2 主键索引和唯一索引到底怎么选？

面试也经常问：主键索引和唯一索引的区别是什么？

主键索引是聚簇索引，InnoDB 中数据行本身就按主键组织，主键索引的叶子节点存的是整行数据。所以通过主键查询是最快的，直接用主键就能定位到数据行，不需要回表。

唯一索引是非聚簇索引，叶子节点存的是主键值。查询时先走唯一索引找到主键，再回表查到完整数据。唯一索引和普通索引的区别在于它保证了唯一性约束，允许有一个 NULL 值（如果列允许 NULL）。

实际业务中，主键尽量选择自增或者趋势递增的值，不要用随机字符串、UUID。为什么？因为 InnoDB 聚簇索引是有序的，如果主键随机插入，会导致 B+ Tree 频繁分裂、页分裂，产生大量碎片，写入性能和空间利用率都会受影响。自增主键能顺序写入，减少页分裂。

有一种情况用 UUID 也有合理性：分布式场景需要全局唯一 ID，而且不想暴露自增规律。这种可以接受 UUID 的写放大代价，但一定要知道它的代价是什么。

5.3 联合索引 vs 多个单列索引：真实场景怎么选

前面说where a and b建议建联合索引，但真实场景不是这么绝对的。

举个例子：一张订单表，有的查询是where user_id = ?，有的是where status = ?，还有的是where user_id = ? and status = ?，三个查询频次都很高。

第一种做法：建idx_user和idx_status两个单列索引。那么where user_id and status时，MySQL 有两种选择：选一个索引回表过滤，或者走 index merge（索引合并）。index merge 在某些场景下有效，但不是所有版本和所有查询都稳定。而且 status 区分度低，单独索引价值不大。

第二种做法：建idx_user_status(user_id, status)一个复合索引。它可以覆盖where user_id = ?和where user_id = ? and status = ?，但对于纯粹的where status = ?，这个索引用不上，因为最左前缀断了。

所以正确思路是：优先分析高频查询模式，用查询日志统计出 where 条件里字段组合出现的频率。选择出现最频繁的字段作为复合索引的引导列，再叠加其他高频过滤字段。而不是机械地"每个 where 字段建一个索引"。

5.4 哪些情况下我坚决不建索引？

我自己定的几个原则，分享出来给大家参考：

• 表数据量很小（比如千行以内），不用建索引，全表扫描更快
• 频繁更新的列，不适合建索引，因为索引维护成本高，而且更新慢
• 区分度极低的列，比如性别、布尔值，除非配合其他列组成复合索引，否则不单独建
• 大文本字段，比如 TEXT、超长 VARCHAR，除非用前缀索引，否则不建全文索引不划算
• 索引数量超过 5 个甚至更多时，要先评估现有索引是否冗余，而不是继续叠加

5.5 MySQL 8.0 里可以用不可见索引做安全变更

最后分享一个很实用的技巧。MySQL 8.0 支持不可见索引（invisible index），这是一个我强烈推荐大家在线上环境使用的功能。

流程是这样的：先加一个不可见索引，观察一段时间，确认查询确实会用到它，再把它设为可见。如果发现问题，直接删除即可，中间不会影响任何线上流量。

-- 创建不可见索引 ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time) INVISIBLE; -- 确认执行计划会走这个索引 SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=on') */ * FROM orders WHERE user_id = 10086 AND create_time > '2024-01-01'; -- 没问题，设为可见 ALTER TABLE orders ALTER INDEX idx_user_create VISIBLE;

线上加索引最怕的就是加的时候锁表或者加了之后反而更慢。不可见索引相当于一个"灰度发布"的机制，反复测试确认无副作用再让优化器真正使用它。这个做法在 MySQL 8.0 中算是比较稳的方案了。

我个人在这些年的索引优化里最大的体会是：索引设计不是一次性的，而是一个持续迭代的过程。业务在变，查询模式在变，数据量在变，索引方案也要跟着调整。刚接手一个系统时不要急着大改索引，先花时间把慢查询日志和业务查询模式摸清楚，再做针对性设计。很多时候，一个复合索引的字段顺序调整，带来的性能收益比新建三个索引都明显。

另外还想强调一点，任何索引优化都要以真实数据量为准。开发环境建索引快得飞起，不代表生产环境也一样。有条件的话，尽量在和生产数据量级相当的环境里验证，或者至少在压测环境里经过充分测试再上线。这也是我前面反复提到 EXPLAIN 和 profiling 的原因——没有数据支撑的"优化"，基本都是在碰运气。