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用了哪些字段是否有多表JOINJOIN的关联字段是什么有没有覆盖索引的优化空间把这些信息整理成一张“查询模式清单”才能知道哪些索引是真正必需的。举一个简单例子。一张用户表有以下查询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_id1001 AND status2后再用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如果数据分布均匀那么索引最多只能筛掉一半数据全表扫描可能比走索引回表更划算但如果你总是查询status1且这类数据只占1%索引又能显著减少扫描量。判断区分度可以用一个简单的SQLSELECT 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) ) ENGINEInnoDB;通过慢查询日志和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支持ALGORITHMINPLACE方式在线删除索引不会长时间锁表但也不是完全无感知。它会消耗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和bc只能通过回表后的过滤。虽然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 13800138000MySQL会先把字符串转成数字再比较索引失效。应该写成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%的索引。缩减的不只是磁盘和写入延迟更重要的是整个数据库的可维护性和执行计划的稳定性。索引多并不代表专业能根据真实查询模式精确定制索引才是长期稳定运行的关键。我个人在实际操作中还有一个心得如果团队里有多个开发同学维护同一个库建议在上线新功能时顺便提交一次索引变更说明说明新增索引的原因、覆盖的查询以及预期效果。这不仅能避免重复建索引还能让后来的同事理解这个索引为什么要存在。万一以后数据量变化也更容易判断它是否还需要保留。不要怕删索引最怕的是不知道索引为什么而存在。