1. 索引这件事，先别急着“多多益善”

先聊一个我几乎每天都会遇到的场景：某天业务反馈一个查询变慢了，开发同学甩来一条SQL，后面跟着一句“我已经把所有涉及的字段都加了索引，怎么还是慢？”点开表结构一看，好家伙，单表十几个索引，有的字段上既建了单列索引，又出现在两个复合索引里，还有一些索引从没被任何执行计划用到过。索引确实能加速查询，但建索引是有代价的，而且索引过多带来的问题往往比慢查询更隐蔽、更棘手。

MySQL索引是提升查询性能最常用也最有效的手段之一，但“索引不是越多越好”这句话，很多人在刚接触数据库时都听过，却很少有人真正理解背后的代价是什么。这篇文章我会从索引的底层存储结构出发，讲清楚为什么索引有开销、哪些索引是非必需的、怎么判断一个索引到底该不该建，以及我在实际项目中优化索引的一些方法和踩过的坑。内容主要是针对InnoDB引擎下的MySQL 8.0，但大部分原理同样适用于MySQL 5.7和Percona分支。

适合谁看？如果你刚接触索引，想搞清楚联合索引最左前缀原则和索引失效场景；或者你正在为一个慢查询头疼，不知道该怎么分析现有索引是否合理；又或者你只是想把表结构整理得更干净，减少无用索引对写入性能的影响——这篇文章都值得你花几分钟读完。

先说一个最常见的误区：很多人以为索引就是“给查询加速的”，所以只要能加速的字段就加索引，甚至把WHERE、ORDER BY、GROUP BY后面的字段全部单独建索引。这样做短期内可能确实让某些查询变快了，但长期来看，你的INSERT、UPDATE、DELETE语句会变得越来越慢，磁盘占用越来越大，甚至连优化器选错索引的概率都变高了。

2. 为什么索引不是越多越好：从B+树说起

2.1 索引的存储代价与写入放大

索引不是虚拟的配置项，它是真实存在的物理结构。在InnoDB中，每建立一个二级索引，就等于额外创建一棵B+树。主键索引本身是一棵聚簇索引树，表数据就挂在叶子节点上；二级索引的叶子节点存储的是索引列的值加上主键值。每多一棵B+树，就意味着每一次写入（INSERT、UPDATE、DELETE）都要额外维护这棵树的结构。

这里有一个“写入放大”的概念，很好理解。假设一张表只有主键索引时，插入一条记录只需要写一次聚簇索引；如果这张表上有5个二级索引，那插入一条记录就至少要维护5棵B+树：每个二级索引都要插入一条新的索引记录，并可能触发页分裂、页合并等操作。虽然InnoDB有change buffer可以缓冲一部分二级索引的写入，但缓冲也有上限，而且对于唯一索引根本无法缓冲。结果就是，你建的索引越多，写入路径就越长，锁竞争也更激烈。

我在一个订单表上实测过：原表有8个二级索引，每秒钟插入5000条订单数据时，平均写入延迟在12ms左右；删掉4个完全用不到的索引后，相同压力下写入延迟降到4ms以下。那个表的数据量大约2000万行，删除索引后磁盘占用也少了接近3GB。这就是为什么索引过多会让系统整体变慢——查询变快了，但写入和更新成了瓶颈，尤其在订单、日志、流水这类高写入场景下，问题会被放大到非常明显。

2.2 优化器的选择困境

还有一个容易被忽略的问题：索引太多会让MySQL优化器“挑花眼”。优化器根据统计信息估算不同索引的代价，然后选择它认为最优的那个。如果一张表有十几个索引，优化器需要比较的候选执行计划也会变多，虽然这个比较一般不会消耗太多CPU，但它可能选错。

选错索引的典型案例是：对同一个字段既建了单列索引，又把它放在一个复合索引的第一位。大多数情况下优化器会倾向于选择复合索引，因为它认为复合索引可以覆盖更多查询条件。但如果你有一个查询只用了这一个字段，复合索引可能比单列索引大得多，扫描成本反而更高。由于统计信息可能过时，优化器会基于不准确的基数估算做出错误决定。

我处理过一个线上案例：一张日志表中的create_time字段既有单列索引，又是某个复合索引(user_id, create_time)的首列。业务上有一个统计某时间段内所有日志数量的查询，只带create_time范围条件。在数据量约3000万行时，优化器选择了复合索引扫描，扫描行数比单列索引多了近20倍，查询耗时从60ms飙升到1.8秒。最终确认是复合索引的统计信息过期，且优化器认为多列索引能提供更多信息。把过期的单列索引删掉后，强制走聚簇索引扫描反而更快。

这个案例的教训是：不要在一个字段上同时保留单列索引和作为复合索引首列的冗余关系。两张索引的数据几乎重叠，但维护成本和执行计划选择难度却翻倍。

2.3 磁盘占用和缓冲池压力

InnoDB的数据和索引都存储在磁盘上，查询时会加载到内存的缓冲池（Buffer Pool)中。索引越多，缓冲池需要容纳的索引页就越多，留给真正热数据缓存的空间就越少。当内存不足时，会发生频繁的LRU淘汰和磁盘读，这类随机I/O比顺序I/O慢得多。

有人做过粗略估算：5个二级索引的占用空间往往和表数据本身相当甚至更多。因为二级索引叶子节点存的是索引列加主键，如果索引列是较长字符串，索引体积可能比数据还大。一张1GB的表，如果建了6个冗余索引，总占用可能达到2~3GB。缓冲池默认128MB时，一大半空间都被冷索引占据，热查询自然会命中率下降。

3. 复盘索引设计前：先搞懂查询再说

3.1 索引设计的起点不是字段，而是SQL

很多人的习惯是：看到表结构，觉得哪个字段查询多，就给它建索引。这个思路不能说全错，但它缺少一个关键环节——你设计的索引必须服务于具体的查询模式，而不是服务于字段。

正确的起点是收集这个表上所有的SQL，尤其是高频查询和慢查询。然后逐个分析：这个SQL的WHERE条件是什么？是等值还是范围？ORDER BY和GROUP BY用了哪些字段？是否有多表JOIN？JOIN的关联字段是什么？有没有覆盖索引的优化空间？把这些信息整理成一张“查询模式清单”，才能知道哪些索引是真正必需的。

举一个简单例子。一张用户表有以下查询：

SELECT * FROM user WHERE status = 1 AND create_time > '2024-01-01'; SELECT * FROM user WHERE status = 1 ORDER BY create_time DESC;

这两个SQL条件几乎一样，只是第二个多了一个排序。很多人会分别建(status)、(create_time)两个索引，但实际上一个(status, create_time)复合索引就能同时满足这两个查询：等值status后按create_time排序，不需要额外排序操作。如果把顺序反过来(create_time, status)，第二个查询中status作为范围条件之后的排序字段，索引就帮不上忙了。

更复杂的情况是既有等值又有范围条件。比如：

SELECT * FROM orders WHERE user_id = 1001 AND status = 2 AND create_time >= '2024-03-01';

索引设计建议是：等值条件的字段放在复合索引前面，范围条件字段放在最后。所以(user_id, status, create_time)比(user_id, create_time, status)更优，因为前者可以精确定位user_id=1001 AND status=2后，再用create_time范围扫描，减少不必要的回表。当然如果status区分度极低，比如只有2~3种取值，那把它放在索引中意义不大，也可以考虑去掉，具体要看统计信息。

3.2 冗余索引的辨别方法

冗余索引是“索引负担”的主要来源。判断两个索引是否冗余，不需要复杂的工具，只需要看它们的字段前缀。

假设表上有这些索引：

KEY idx_user_id (user_id), KEY idx_user_status (user_id, status), KEY idx_user_status_time (user_id, status, create_time)

idx_user_id是idx_user_status的前缀，idx_user_status又是idx_user_status_time的前缀。理论上，最左边那个索引可以被最右边那个完全替代，因为最右索引包含了它所有字段作为前缀。所以idx_user_id和idx_user_status都是冗余的，可以删掉，只保留idx_user_status_time即可覆盖所有涉及这三个字段前缀的查询。

不过有一个例外要注意：如果单独的user_id索引和复合索引在优化器眼中执行的代价差异过大，比如复合索引非常大，而单独索引很小，优化器可能会选择更合适的路径。但总体来看，冗余索引删掉利大于弊。

还有另一种形式的冗余：两个索引字段完全一样，只是顺序不同。比如(a, b)和(b, a)完全不是一回事，(a, b)能优化WHERE a=? ORDER BY b，(b, a)能优化WHERE b=? ORDER BY a。不能因为字段相同就认为冗余。

3.3 区分度是一个重要权衡

索引的价值取决于列的区分度。一个只有两个值的列，比如status，如果数据分布均匀，那么索引最多只能筛掉一半数据，全表扫描可能比走索引回表更划算；但如果你总是查询status=1且这类数据只占1%，索引又能显著减少扫描量。

判断区分度可以用一个简单的SQL：

SELECT COUNT(DISTINCT col) / COUNT(*) FROM table;

比值越接近1，区分度越高，索引越有价值；比值接近0，说明大部分值都一样，索引很可能鸡肋。我看到很多“每个字段都加索引”的表里，有一堆布尔字段的索引，这类索引大多没什么用。

即使区分度低，也不能一刀切说不要建索引。像status这样的字段，如果查询条件非常常见，组合到复合索引中作为等值前缀，配合区分度高的第二个字段，整体效果仍然不错。关键是没有必要为低区分度字段单独建索引。

4. 实操：如何分析并精简现有索引

4.1 从慢查询日志和性能视图入手

生产环境调整索引前，先把证据拿全。首选工具是慢查询日志，设置一个合理的阈值，例如long_query_time = 1，至少收集一周的慢SQL。然后按执行次数和平均耗时排序，找到真正的“大头”。

MySQL 8.0提供了performance_schema.table_io_waits_summary_by_table等表，可以看表的IO等待情况，不过最直接的信息还是执行计划。对每条慢SQL执行EXPLAIN，观察key列实际用了哪个索引，rows列估算扫描了多少行，extra里有没有Using filesort或Using temporary。

我用一个实际案例说明。某活动表结构如下：

CREATE TABLE activity ( id INT PRIMARY KEY, user_id INT, activity_type TINYINT, create_time DATETIME, expire_time DATETIME, remark VARCHAR(200), KEY idx_user (user_id), KEY idx_type (activity_type), KEY idx_create (create_time), KEY idx_expire (expire_time), KEY idx_user_type (user_id, activity_type), KEY idx_user_create (user_id, create_time) ) ENGINE=InnoDB;

通过慢查询日志和EXPLAIN分析，发现高频查询是：

SELECT * FROM activity WHERE user_id = ? AND create_time > ? ORDER BY create_time DESC LIMIT 10; SELECT * FROM activity WHERE user_id = ? AND activity_type = ?;

其中idx_type和idx_expire几乎没有出现在任何执行计划中。idx_user_type和idx_user_create出现了，但它们的前缀idx_user完全冗余。最终调整方案是删除idx_type、idx_expire、idx_user，保留(user_id, create_time)和(user_id, activity_type)两个复合索引。删除后，批量更新活动状态的时间缩短了30%，因为那几个冗余索引每次更新都要维护。

4.2 如何用sys.schema_unused_indexes找从未使用的索引

MySQL 5.7以上的sys schema里有一个视图叫schema_unused_indexes，它会根据performance_schema中的索引使用统计信息，列出从未被使用过的索引。这是一个极其方便的工具。

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'your_db';

注意这个视图依赖performance_schema的user_statistics和table_io_waits_summary_by_index_usage，需要开启相关统计能力，否则数据可能是空的。运行结果告诉你哪些索引自服务启动以来从未被使用，这些最值得优先删除。

但刚启动就运行是没意义的，因为统计信息只从启动时刻开始积累。至少要等系统运行一个完整的业务周期，比如一周，确保所有类型的查询都出现过，再来看这个视图。

还有一个坑：如果一条SQL强制使用某个索引FORCE INDEX，即使这个索引本身没什么用，它也会被计入使用状态，schema_unused_indexes就不会显示它。这种索引需要人工排查。

4.3 索引创建与删除的注意事项

在在线业务中删索引也有讲究。MySQL 8.0支持ALGORITHM=INPLACE方式在线删除索引，不会长时间锁表，但也不是完全无感知。它会消耗I/O和CPU资源，可能对高峰期写入造成影响。建议选择业务低峰执行，并先在一个从库上验证，或者使用gh-ost、pt-online-schema-change这类工具。

删除索引前先备份表结构定义，避免误删后无法快速恢复。删除后观察至少一周，确认慢查询没有明显回弹，再继续下一步。

创建索引也一样，不要一次性加好几个索引。逐个添加后使用EXPLAIN验证目标SQL是否真的走了新索引、扫描行数是否下降、执行时间是否缩短。如果加了索引后执行计划没变化，说明优化器不认为它对当前查询有帮助，这时候就要分析是不是索引选择有问题，还是SQL写法需要调整。

5. 核心场景拆解：不得不说的联合索引、排序与覆盖

5.1 联合索引最左前缀原则的完整理解

联合索引是MySQL索引设计里最难的部分。很多人知道“最左前缀”，但理解不深，导致设计出来的索引水土不服。

(a, b, c)联合索引实际上创建了三层有序结构：先按a排序，a相同时按b排序，b相同时按c排序。它能支持以下查询：

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?

以及部分范围查询，比如WHERE a = ? AND b > ?可以利用索引排序，但WHERE b = ?就无法使用这个索引，因为跳过了a。这就是最左前缀原则。

另一个容易误解的点是：范围条件一旦出现，后面的列即使符合前缀，也无法继续用于等值匹配。比如WHERE a = 1 AND b > 10 AND c = 2，索引只用到a和b，c只能通过回表后的过滤。虽然InnoDB在5.7引入了“索引条件下推”，可以把c = 2下推到索引扫描阶段，减少回表次数，但索引的有序性仍然帮不上什么忙。

设计联合索引时最重要的思考题是：这个索引能覆盖哪些高频查询？如果能覆盖80%以上的查询场景，那就值得建；如果每个查询都要走“只用部分列”的路子，倒不如拆成两个独立索引或者调整列的顺序。

5.2 ORDER BY排序与filesort的取舍

排序是索引设计里另一半重要内容。当查询包含ORDER BY时，如果表数据量不大，MySQL会使用filesort把结果在内存或磁盘中排序，这不算严重的性能问题。但当结果集达到几十万行时，filesort的代价就很高。

如果ORDER BY字段正好是联合索引的一部分，并且满足最左前缀条件，那么查询结果本身已经有序，可以避免filesort。举几个例子：

(a, b)索引对ORDER BY a, b有效；对ORDER BY b无效；对WHERE a = ? ORDER BY b有效；对WHERE a > ? ORDER BY b无效，因为范围条件破坏了索引中b的有序性。

在实际项目中，我见过一个报表查询，要求按user_id分组统计各状态数量，并按user_id排序。原来的索引是(status, user_id)，导致GROUP BY user_id需要filesort，处理100万行要1.2秒。调整索引为(user_id, status)后，同样查询降到50ms，效果立竿见影。

这就是为什么设计索引前要收集SQL而不是只看字段名。同样的字段，不同顺序对不同的排序需求影响巨大。

5.3 覆盖索引：查询性能的最优解

覆盖索引指查询的字段全部包含在索引中，查询过程完全不需要回表。InnoDB的二级索引叶子节点包含索引列和主键，所以如果查询列表只是索引列和主键，就可以直接用索引树完成。

举个例子：

SELECT user_id, create_time FROM user_login WHERE user_id = 1001 ORDER BY create_time DESC LIMIT 20;

如果索引是(user_id, create_time)，那么这条SQL可以直接从索引中取出user_id和create_time，完全不需要回表。这就是覆盖索引带来的巨大性能优势。

实际应用中可以刻意把高频查询需要的字段“塞进”索引。比如一个查询频繁需要user_id、status、create_time，可以设计索引(user_id, status, create_time)，如果这个查询还只关心这三列，就变成了一个完美的覆盖索引。不过，过度延伸索引列也会带来副作用：索引体积变大，写入更慢。所以覆盖索引只适合那些高频且固定的查询，不能为了“可能用到”就盲目加列。

6. 从表结构全局看索引优化

6.1 索引与主键设计的关系

InnoDB中每张表的二级索引叶子节点都存储主键值，所以主键的选择直接影响所有二级索引的大小。如果主键是自增整数，每个二级索引叶子节点存储4~8字节的整数，索引体积最小；如果主键是UUID或长字符串，每个二级索引叶子节点都要存对应的主键值，所有二级索引都会膨胀。

我曾经维护过一张用UUID做主键的表，约500万行，三个二级索引总占用超过2GB。后来改成自增bigint主键，同时把原UUID列改成普通varchar(36)列，不再作为主键，三个二级索引占用降到不足700MB。更关键的是插入性能明显提升，因为UUID主键会导致B+树频繁页分裂，而自增主键可以顺序插入。

当然，不是所有表都适合自增主键。分布式场景需要全局唯一主键时，可以选择有序生成算法（如雪花算法的变种），避免完全随机的字符串。核心思路是让主键值尽可能短且有序。

6.2 冗余字段到底要不要

索引设计经常有一种“空间换时间”的思路：在表里加冗余列，把JOIN变成单表查询。比如原来的user_name要通过user_id去用户表关联才能拿到，业务又经常查询，就可以在订单表冗余一个user_name字段，虽然破坏了第三范式，但能大幅度降低查询压力，同时减少复杂JOIN对索引的要求。

冗余字段使用不当会导致更新不一致，需要在业务层或存储过程中保证同事务更新。但好处是可以少建几个跨表关联的复合索引。与其建一个(order_id, user_id, user_name)的冗余宽索引，不如直接在表里冗余一个字段，让查询更简单。

6.3 定期使用SHOW INDEX检查索引基数和状态

运维层面，定期检查索引状态也很重要。SHOW INDEX FROM table可以查看每个索引的基数（Cardinality）、区分度等信息。如果Cardinality远小于表行数，说明该索引的区分度可能不理想。

还可以用ANALYZE TABLE来更新统计信息，帮助优化器生成更准确的执行计划。InnoDB的统计信息默认是持久化的，但样本估算可能因数据分布变化而不准确。遇到优化器选错索引时，先跑一次ANALYZE TABLE，往往就能解决问题。

如果还不行，可以考虑使用FORCE INDEX临时指定索引，但这只是临时方案，根本原因可能是统计信息不准确或索引结构设计不合理。

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

7.1 索引失效的几种典型场景

很多人遇到“索引失效”就开始怀疑SQL写法，实际上80%的情况是理解问题。以下是我整理的几种典型场景：

• 对索引列使用函数：WHERE DATE(create_time) = '2024-01-01'，这个写法让索引列参与了函数运算，MySQL无法直接利用索引。正确做法是改写为范围条件：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
• 隐式类型转换：索引列是字符串，查询用数字，例如WHERE phone = 13800138000，MySQL会先把字符串转成数字再比较，索引失效。应该写成WHERE phone = '13800138000'。
• LIKE前缀通配：WHERE name LIKE '%张%'无法利用索引，WHERE name LIKE '张%'可以（前提是排序规则和索引顺序匹配）。
• OR条件导致失效：WHERE a = 1 OR b = 2如果a和b不是同一个索引，MySQL可能放弃索引走全表扫描。改写为UNION ALL可以使用两个索引。
• 联合索引不满足最左前缀：WHERE b = ?在(a,b)索引下无法使用。

遇到索引失效时，不要急着优化SQL，先用EXPLAIN看type列和key列。type是ALL或index时基本是全表或全索引扫描，需要重点分析条件改写和索引设计。

7.2 为什么索引明明存在却没用上

这是出镜率非常高的问题。排除上述失效场景后，还有几个常见原因：

• 选择性太差：比如索引列90%的数据都是同一个值，优化器认为走全表扫描比走索引后大量回表更便宜。此时即使强制索引，实际性能也未必更好。
• 可以走索引，但需要回表太多：如果查询需要回表的行数占总行数比例很高，优化器会认为全表扫描更优。解决思路是设计覆盖索引，避免回表。
• 索引统计信息过期：执行ANALYZE TABLE更新统计信息后再试。
• ORDER BY与索引顺序不匹配：即使WHERE条件能用上索引，但排序字段使得结果集无法有序输出，优化器也可能放弃索引。

我自己遇到过一个隐藏很深的案例：表数据量只有几万行，但某字段的Cardinality统计为1，导致所有查询都选择全表扫描。原因是数据库刚迁移时，优化器用了样本估算，加上表的统计信息没更新。执行ANALYZE TABLE后恢复正常。这类问题说明，排查索引问题不能只盯着索引本身，统计信息也是关键环节。

7.3 高写入场景下如何保证查询性能

业务属于高写入、高查询并存时，索引设计要有所取舍：

• 优先保证写入吞吐，减少不必要的二级索引。查询性能可以通过增加只读从库、引入缓存（如Redis）等方式弥补。
• 对必须保留的复合索引，尽量保证其中每个字段都有存在价值，避免“顺手多带一列”的冗余设计。
• 大批量写入前，可以临时禁用非唯一索引，例如使用ALTER TABLE ... DISABLE KEYS，不过该语法在InnoDB中不生效（只对MyISAM有效）。InnoDB的替代方案是先删除索引，再导入数据，最后重建索引。
• 分批提交事务，避免长事务持有索引锁。索引维护过程中产生的锁竞争和死锁风险会随事务变长而增加。

7.4 索引碎片和统计信息维护

索引页会因为随机删除和更新产生碎片。不像自增主键那样顺序插入，二级索引的页很容易空一半。碎片会导致索引物理占用高于逻辑大小，扫描效率下降。

可以通过以下SQL查看表空间碎片：

SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024,2) AS data_mb, ROUND(INDEX_LENGTH/1024/1024,2) AS index_mb, ROUND(DATA_FREE/1024/1024,2) AS free_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' ORDER BY free_mb DESC;

DATA_FREE过高的表可以考虑OPTIMIZE TABLE重建表。不过OPTIMIZE TABLE期间会有锁，所以要低峰执行。对于碎片率低但统计信息不准的情况，用ANALYZE TABLE即可，不需要做重建。

8. 一条实用的索引设计“减法”流程

最后，把我在项目中常用的索引精简流程整理成一个可直接操作的清单，方便大家落地。

第一步：收集查询。开启慢查询日志，捞出一个业务周期的慢SQL和高频SQL，整理成列表。并确认是否有定时任务、报表查询，这些低频但昂贵的大查询最容易忽略。

第二步：为每条查询设计“理想索引”。根据WHERE、ORDER BY、GROUP BY、JOIN字段，按等值在前、范围在后，排序字段紧跟等值字段的原则，逐一写出理想索引。

第三步：合并相似索引。把所有理想索引放在一起，删除前缀重复的冗余索引，只保留覆盖能力最全的那一个。但要确保删除后，原先依赖该前缀的查询仍然能走最左前缀原则。

第四步：验证覆盖索引。检查高频查询的SELECT列是否可以通过索引覆盖。如果合适，把必要列追加到索引最后，但只针对那些真正高频的查询，不要贪多。

第五步：执行并观察。删除冗余索引后，通过sys.schema_unused_indexes和慢查询日志对比前后变化。建议从删除“从未使用索引”开始，降低风险。

第六步：定期复盘。即使索引设计得再好，业务一变化索引就会老化。每隔一个季度，重新做一次上面的流程，删除不再使用的索引，补充新的查询模式需要的索引。

这轮流程做下来，一般表能精简掉30%~50%的索引。缩减的不只是磁盘和写入延迟，更重要的是整个数据库的可维护性和执行计划的稳定性。索引多并不代表专业，能根据真实查询模式精确定制索引，才是长期稳定运行的关键。

我个人在实际操作中还有一个心得：如果团队里有多个开发同学维护同一个库，建议在上线新功能时顺便提交一次索引变更说明，说明新增索引的原因、覆盖的查询以及预期效果。这不仅能避免重复建索引，还能让后来的同事理解这个索引为什么要存在。万一以后数据量变化，也更容易判断它是否还需要保留。不要怕删索引，最怕的是不知道索引为什么而存在。