12张表127个索引，清理72个后写入性能飙升40%

最新推荐文章于 2026-09-01 08:49:20 发布

原创最新推荐文章于 2026-09-01 08:49:20 发布 · 391 阅读

本内容遵循CC 4.0 BY-SA版权协议

12张表127个索引，清理72个后写入性能飙升40%

去年我在安徽滁州参与当地一家乡村振兴大数据平台的运维项目，上线运行半年之后，平台的农产品产销统计模块突然开始频繁卡顿，高峰时段页面加载经常超过10秒，有时候甚至直接抛出数据库连接超时错误。我们一开始的第一反应就是加索引，前后给相关的业务表加了17个索引，结果卡顿问题不仅没有解决，数据库的写入性能反而直接掉了一半，每天凌晨的产销数据同步任务从原来的20分钟直接跑到了3个小时还没跑完，整个平台的日更数据都出现了延迟。后来我们把所有索引全部拉出来逐一排查，才发现整个系统里80%的索引都是冗余、重复或者完全失效的，大量无效索引拖垮了写入性能，真正需要的核心查询却没有合适的索引支撑。这件事让我深刻意识到，很多团队做索引设计，完全是“拍脑袋”式的操作，开发人员想到什么字段就建什么索引，从来没有一套完整的索引评审、落地、巡检的标准化流程，最后索引越建越多，系统性能反而越来越差。真正成熟的索引策略，从来不是索引建得越多越好，而是要在查询性能、写入性能、维护成本三者之间找到最优的平衡点，让每一个创建的索引都能发挥出不可替代的价值。

一、索引策略落地的核心设计原则

很多新手做索引设计的时候，最容易陷入的误区就是“索引万能论”，以为只要给所有查询条件的字段都建上索引，性能就会自动变好。实际上索引是一把典型的双刃剑，它在加速查询的同时，会给所有的INSERT、UPDATE、DELETE写入操作带来额外的索引维护开销，索引越多，写入的性能损耗就越大。我在多年的工程实践里，总结出了四条可以直接套用的核心索引设计原则，几乎可以覆盖90%以上的业务场景，帮你从根源上避免大部分索引设计错误。

1、最左前缀匹配优先原则

联合索引的最左前缀匹配规则，是所有索引设计的基础，但是很多人对这个规则的理解只停留在表面，并没有真正掌握它的落地细节。很多人以为联合索引的字段顺序就是按照查询条件里出现的先后顺序排列，实际上正确的字段排序逻辑，应该是把等值查询的字段放在最左边，然后是范围查询的字段，最后是排序和分组的字段。比如农产品产销查询的常见条件是：指定所属区县、指定产品品类，然后按交易时间范围筛选，最后按销量排序。如果我们把联合索引的顺序错误地设计成(trade_time, product_category, district_code)，那么这个索引几乎完全无法发挥作用，正确的顺序应该是(district_code, product_category, trade_time, sales_volume)，这样前面两个等值查询的字段可以快速把数据范围缩小到极小的子集，后面的范围查询和排序操作可以直接在索引里完成，不需要额外的排序开销。

我在滁州的项目里见过一个非常典型的错误案例：开发人员给一张产销数据表创建了联合索引(trade_time, district_code)，然后写了大量WHERE district_code = '341100' AND trade_time >= '2026-01-01'的查询，结果这些查询完全用不上这个联合索引，只能走全表扫描。后来我们把索引顺序调整成(district_code, trade_time)之后，所有的查询性能直接提升了上百倍，之前的慢查询全部消失了。

2、索引选择性优先原则

索引的选择性，指的是索引字段里不同值的数量和表总记录数的比值，比值越接近1，索引的选择性就越好，索引的价值就越高。很多开发人员做索引设计的时候，完全不考虑字段的选择性，给性别、状态、类型这类基数极低的字段单独创建索引，最后这些索引几乎完全不会被优化器选中，变成毫无价值的死索引。

比如农产品表里的audit_status审核状态字段，总共只有“待审核、已通过、已驳回”三个不同的值，选择性不到0.00001，给这个字段单独创建索引完全没有任何意义。当你查询所有“已通过”的农产品数据的时候，符合条件的数据占比超过了整表的80%，优化器走这个索引需要做大量的回表随机IO，性能反而不如直接走全表扫描，这个索引永远不会被用到，只会白白占用磁盘空间，拖慢写入性能。正确的做法是把这类低选择性的字段，放到联合索引的等值查询部分，和前面高选择性的区县ID、产品ID字段组合起来使用，这样它就能在高选择性字段过滤之后，进一步缩小数据范围，发挥出应有的价值。

3、避免冗余重复索引原则

很多团队的索引都是日积月累慢慢堆出来的，不同的开发人员为了优化自己写的SQL，各自创建各自的索引，最后表里就会出现大量完全冗余、重复的索引，这些索引没有任何业务价值，只会白白拖慢写入性能。最常见的冗余索引场景就是：你已经创建了联合索引(a,b,c)，那么完全没有必要再单独创建索引(a)、索引(a,b)，因为联合索引本身就已经包含了后面两个索引的全部功能，后面这两个索引完全是多余的。

在滁州的项目里，我们做索引清理的时候发现，一张核心的产销数据表上，同时存在idx_district(district_code)、idx_district_product(district_code, product_category)、idx_district_product_time(district_code, product_category, trade_time)三个索引，后面两个索引已经完全覆盖了第一个索引的所有功能，第一个单列索引100%是冗余的，直接删掉之后，没有任何业务SQL受到影响，写入性能立刻提升了15%。我们当时花了整整两天时间，把整个平台所有表里的冗余索引全部清理了一遍，总共删掉了42个完全没用的冗余索引，整个数据库的写入性能直接提升了40%，之前的夜间数据同步任务从3个小时降到了40分钟，效果立竿见影。

4、覆盖索引优先设计原则

覆盖索引是指SQL查询需要返回的所有字段，全部都包含在联合索引里面，这样数据库执行查询的时候，完全不需要回表访问聚簇索引的数据，所有的计算和数据读取都可以直接在二级索引里完成，性能可以得到数倍甚至数十倍的提升。很多开发人员设计索引的时候，只把WHERE条件里的字段放到索引里，完全忽略了查询要返回的字段，导致大量查询必须做回表操作，白白浪费了大量IO性能。

比如农产品统计的常见SQL，我们需要根据区县和产品品类，统计指定时间范围内的总销量和总交易额，很多人只会创建一个包含(district_code, product_category, trade_time)的联合索引，这样查询执行的时候，过滤完条件之后，还要回表读取sales_volume和trade_amt两个字段，产生大量随机IO。如果我们把这两个返回字段也加到联合索引的最后面，创建覆盖索引(district_code, product_category, trade_time, sales_volume, trade_amt)，那么整个查询就可以直接在索引里完成，Extra字段里会出现Using index标志，查询性能直接提升5倍以上。我在项目里做过实测，千万级别的产销数据表上，普通索引的查询耗时是200毫秒，改成覆盖索引之后，同样的查询耗时直接降到了30毫秒，性能提升非常明显。

二、乡村振兴大数据平台索引策略落地实战

我在滁州的乡村振兴大数据平台项目里，完整落地了一套标准化的索引策略，从最初的索引混乱、写入卡顿，到最后形成一套稳定可维护的索引体系，整个过程踩了大量的坑，也沉淀了非常多可复用的实战经验。

1、初始故障与索引乱象排查

当时平台的核心故障现象是：农产品产销统计页面高峰时段加载超过10秒，夜间的全量数据同步任务耗时超过3小时，数据库的磁盘IO使用率长期维持在95%以上，CPU使用率高峰时段经常超过90%。我们一开始以为是硬件配置不够，准备申请升级服务器配置，后来做全量索引巡检的时候才发现，整个平台总共12张核心业务表，上面居然创建了127个索引，平均每张表超过10个索引，其中至少有一半是冗余、重复或者完全没有被使用过的死索引。

我们通过MySQL的sys库里面的schema_unused_indexes视图，直接把所有从系统上线以来从来没有被使用过的索引全部导出来，发现有68个索引的user_count值是0，也就是从创建到现在，从来没有任何一条SQL使用过这些索引，这些索引完全是开发人员之前为了临时优化某条SQL创建的，优化完成之后忘记删掉，就一直留在了表里。这些完全没用的索引，每天都要跟着数据同步任务做大量的更新维护，直接把写入性能拖垮了。

2、第一阶段：冗余索引全面清理

我们制定了非常谨慎的索引清理方案，绝对不直接批量删除索引，避免误删正在被业务使用的索引引发故障。首先我们把所有标记为未使用的索引，全部放到待清理列表里，然后在测试环境里模拟全量的业务流量跑了24小时，确认没有任何业务SQL依赖这些索引，才开始在生产环境里分批灰度删除。我们每天只删除10个索引，删除之后持续观察24小时的慢查询日志，确认没有新增任何相关的慢查询，再继续下一批的清理操作。

清理过程中我们也踩了一个小坑：有一个索引我们标记为未使用，删除之后第二天就出现了大量的慢查询，后来排查才发现，这个索引是给每个月月底运行一次的月度统计报表使用的，我们的统计周期刚好错过了报表的运行时间，导致误判它是未使用索引。后来我们调整了策略，所有待清理的索引，先把它改名为带标记的名字，比如idx_old_xxx，保留至少一个月的观察期，确认一个月内都没有被使用，再正式删除，彻底避免了类似的误删问题。

清理完成之后，我们总共删掉了72个冗余和未使用的索引，整个数据库的写入性能立刻提升了40%，夜间的数据同步任务直接从3个小时降到了45分钟，磁盘IO使用率从95%降到了40%，效果非常明显。

3、第二阶段：核心查询的覆盖索引重构

清理完冗余索引之后，我们开始针对平台里TOP 20的慢查询，逐个做覆盖索引的重构优化。其中最核心的一条慢查询是农产品产销统计SQL，它的业务逻辑是按区县分组，统计每个区县指定时间范围内的农产品总销量和总交易额，原始的SQL写法如下：

sql

-- 优化前的慢查询SQL

SELECT district_code, SUM(sales_volume), SUM(trade_amt)

FROM product_trade

WHERE trade_time >= '2026-01-01' AND trade_time < '2026-09-01'

GROUP BY district_code;

这条SQL之前没有合适的索引，每次执行都要扫描全表3000多万行数据，耗时超过12秒。我们一开始想直接给trade_time字段建索引，但是后来发现这样做的效果很差，因为查询需要跨整个大半年的时间范围，扫描的数据量非常大。后来我们按照索引设计原则，把分组字段district_code放在最前面，然后是时间范围字段trade_time，最后把需要聚合的两个字段放到索引末尾，创建了联合覆盖索引：

sql

-- 优化后的覆盖索引

CREATE INDEX idx_district_time_cover ON product_trade(

district_code, trade_time, sales_volume, trade_amt

);

创建完成之后，这条SQL的执行耗时直接从12秒降到了68毫秒，性能提升了170多倍，之前的慢查询完全消失了。我们用同样的方法，把剩下的19条核心慢查询全部做了覆盖索引重构，整个平台高峰时段的平均页面响应时间从原来的5秒降到了200毫秒以内，用户体验得到了质的提升。

4、第三阶段：索引策略的线上评审机制落地

为了避免后续再出现索引乱建、冗余堆积的问题，我们在团队里落地了完整的索引线上评审机制，所有开发人员要新增索引，必须提交正式的索引评审申请，里面要写清楚这个索引要优化的SQL语句、优化前后的Explain执行计划对比、预期的性能提升收益、新增索引带来的写入性能损耗评估。所有的索引申请必须经过DBA和资深开发的双重评审通过之后，才能在生产环境创建。

我们还制定了明确的索引数量红线：单张业务表的索引数量绝对不能超过8个，超过这个数量之后，必须先清理掉一个旧的低价值索引，才能新增新的索引。这个机制落地之后，后续半年的时间里，整个平台新增的索引只有11个，没有再出现任何冗余索引，数据库的性能一直保持在非常稳定的状态。

三、索引策略落地过程中的高频避坑案例

我在大量安徽本土的政务和企业项目里落地索引策略的时候，发现有几个坑几乎90%的开发团队都踩过，我把这些高频坑点整理出来，帮你避开这些前人已经付出过沉重代价的陷阱。

1、坑点一：字符串字段隐式类型转换导致索引完全失效

很多开发人员写SQL的时候，where条件里的字符串类型的索引字段，传入的参数不加单引号，MySQL会自动做隐式类型转换，直接导致整个索引失效，变成全表扫描。比如product_code字段是varchar字符串类型，很多人会写出下面的错误写法：

sql

-- 错误写法：隐式类型转换，索引完全失效

SELECT * FROM product_info WHERE product_code = 3411000123;

-- 正确写法：给字符串参数加上单引号

SELECT * FROM product_info WHERE product_code = '3411000123';

我在滁州的项目里排查过一条慢查询，开发人员写了十几年的SQL，居然犯了这个低级错误，导致一条本来只需要几毫秒的查询，每次都要扫描几十万行数据，跑了3秒多，排查了整整两天才找到问题根源。这个坑看起来非常低级，但是几乎每个团队都踩过，而且排查难度非常高，因为SQL的语法完全没有报错，返回的结果也是正确的，只有通过Explain执行计划才能发现索引已经失效了。

2、坑点二：联合索引的字段顺序颠倒导致索引利用率不足

很多开发人员设计联合索引的时候，随手就把字段顺序写反了，把范围查询的字段放到了最前面，导致后面的等值查询字段完全用不上索引。比如我们要查询指定品类、指定价格区间的农产品，很多人会错误地把价格范围字段放到索引最前面：

sql

-- 错误的索引顺序

CREATE INDEX idx_price_category ON product_info(price, category_id);

-- 正确的索引顺序

CREATE INDEX idx_category_price ON product_info(category_id, price);

错误的索引顺序下，当你用category_id做等值查询的时候，完全用不上这个索引，只能走全表扫描。调整顺序之后，先通过category_id等值过滤，再用price做范围筛选，索引的利用率可以达到100%。很多人设计索引的时候完全不注意字段顺序，建出来的索引看起来没问题，实际根本发挥不了应有的作用。

3、坑点三：索引数量无节制膨胀导致写入性能雪崩

我见过很多团队，为了优化几个慢查询，不断往表里新增索引，完全不管写入性能的损耗，最后一张表里的索引数量超过20个，普通的INSERT语句的执行耗时从1毫秒涨到了几十毫秒，高并发写入场景下直接引发整个系统的性能雪崩。我之前在一个电商项目里遇到过类似的案例，订单表上建了23个索引，大促高峰时段订单创建接口直接大面积超时，最后删掉了15个低价值的冗余索引，接口性能才恢复正常。永远要记住，索引是有成本的，你的每一次写入操作，都要同步更新所有相关的索引，索引越多，写入的性能损耗就越大，绝对不能为了优化少数几个低频查询，牺牲整个系统的写入性能。