企业官网建设流程全解析

很多人看我写MySQL优化文章，总觉得我好像天生就会调索引。其实不是，我最早接手生产库的时候，光一个订单查询就把我搞到凌晨三点——加了索引，慢查询还是慢。后来查EXPLAIN才发现，那条索引被当成“废铁”了，压根没走。更别提后来在另一个项目里，因为一条UPDATE语句的二级索引锁交叉，两个核心表直接死锁，业务告警足足响了四十分钟。今天把这些年踩过的坑整理成5个最典型的，每一个都附上生产级解决方案，你如果能避开这几条，绝对能少走半年弯路。

这话不敢说满，但至少覆盖了索引失效、冗余索引、排序、锁竞争和统计信息这几类最高频的问题。新手最需要看前两个坑，老手建议重点看第五个锁相关的案例，那才是真正埋在生产库里的雷。

1. 索引失效的隐形杀手：隐式类型转换与函数运算

1.1 一条VARCHAR字段上的“数字条件”是怎么毁掉索引的

我印象最深的一个案例，是某用户表里有一个手机号字段，建表时定义成VARCHAR(20)，也加了普通索引。业务方发来一条慢查询：

SELECT * FROM users WHERE user_phone = 13800138000;

单独看这条SQL，很多人觉得没毛病。但问题恰恰出在条件右边的“13800138000”是数字类型，而字段user_phone是字符串类型。MySQL在比较时会把user_phone先隐式转换成数字，再和右边的数字比较。一旦对索引列做了隐式转换，索引基本就废了。

我当时用EXPLAIN看了一下执行计划，type从预期中的ref直接掉到ALL，全表扫描十万行。你想象一下，如果这个表有一千万行，那这条看起来很正常的等值查询，带来的压力就是毁灭性的。

解决办法最干净的一种，是在应用层把参数统一转成字符串再传进去：

SELECT * FROM users WHERE user_phone = '13800138000';

只要类型匹配，索引就能正常走。除此之外，生产环境还有一种常用方案，就是针对这种“无论如何都可能传错类型”的场景，建一个生成列来兜底：

ALTER TABLE users ADD COLUMN user_phone_num BIGINT UNSIGNED GENERATED ALWAYS AS (CAST(user_phone AS UNSIGNED)) STORED, ADD KEY idx_phone_num (user_phone_num);

然后查询写成WHERE user_phone_num = 13800138000，既能匹配数字类型参数，又能用上索引。这个方案的原理其实很朴素：把“隐式转换”变成“显式存储”，提前算好、存好，查询时直接走索引。

1.2 在索引列上做函数运算，神仙也救不了

相比隐式类型转换，函数运算更常见。比如统计某天注册用户数：

SELECT COUNT(*) FROM users WHERE DATE(created_at) = '2024-06-01';

created_at上就算有索引，DATE()函数把每一行的值都“加工”了一遍，MySQL只能老老实实全表扫完再去过滤。这类问题的修复套路也比较固定：把函数从索引列上挪走，改用范围条件。

SELECT COUNT(*) FROM users WHERE created_at >= '2024-06-01 00:00:00' AND created_at < '2024-06-02 00:00:00';

这种写法有几个好处：一是索引列保持原样，能正常用于搜索；二是范围条件本身是索引友好的；三是性能上限比函数写法高出几个数量级。如果你的表是MySQL 8.0.13以上，官方也支持函数索引，但底层实现其实还是隐藏的生成列，本质思路和我上面说的一致，只是不用自己手动加列了。

注意：不只是DATE()，包括YEAR()、MONTH()、SUBSTRING()，甚至是简单的column + 1 = 9这种算术运算，都可能让索引失效。原则只有一个：查询条件里的索引列，别套任何函数或表达式。

2. 索引建得多不如建得准：冗余索引的代价远超想象

2.1 复合索引下藏着的大量“重复建设”

很多团队建索引的思路是“业务提什么，就加什么”，结果索引越建越多，却不知道建的是冗余索引。举个例子，你为了支持user_phone查询建了idx_phone，后来又因为user_phone + status的组合查询建了idx_phone_status。

KEY idx_phone (user_phone), KEY idx_phone_status (user_phone, status)

这时候idx_phone就是冗余的，因为idx_phone_status的左侧前缀已经覆盖了user_phone单独查询的场景。冗余索引最坑的地方不在于它出现在表里，而在于它带来的副作用：每次INSERT、UPDATE、DELETE都要额外维护一份索引数据，占空间是小事，写放大带来的性能跌幅才是大事。

我在生产环境清理过一次类似的重复索引，表数据量大约三千万，删除三个冗余索引后，写入耗时直接下降了约15%。对高频写入的核心表来说，这已经不是“优化”层面的收益了，而是“减负”级别的改进。

2.2 用系统库找出冗余索引，别靠肉眼排查

MySQL的sys库已经提供了现成的诊断视图，不需要自己写复杂脚本：

SELECT * FROM sys.schema_redundant_indexes;

它会直接列出哪些索引是冗余的、冗余在哪张表哪个列上，还会告诉你重复的部分占用多大空间。这个视图的原理是解析所有索引的列前缀结构，凡是能由另一个复合索引完全覆盖的单列索引或前缀索引，都会被识别出来。

清理的时候别急着一次全删，生产库上的任何DDL都建议分批做。我的习惯是先挑业务低峰期，在测试环境跑一遍SELECT统计，确认删除影响面最小，然后分两到三次删除，每次删除后观察慢查询数量和锁等待情况。如果后续发现某条查询因为删除索引而退化成全表扫描，再针对具体SQL重新评估，加一个更轻量的解决方案。

还有一个更隐蔽的情况：两个复合索引前缀相同但顺序不同，比如(a,b)和(b,a)，它们不是冗余的，因为查询条件里b单独出现的场景可以走第二个索引，a,b同时出现的场景走第一个更合适。这类“看起来像重复但不是重复”的设计，要结合真实查询模式来判断，不能机械地套用“前缀覆盖”规则去删。

3. ORDER BY引发的性能灾难：排序设计不只是加个索引那么简单

3.1 为什么带排序的查询加了索引还是filesort

有一段时间我们订单列表页特别慢，核心SQL长这样：

SELECT order_id, user_id, amount, status FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 20;

当时已经在status上建了索引，理论上过滤到2000行再进行排序也不算太傻。但问题是，在status = 1的等值条件下，优化器可以先用status索引快速定位，得到的结果集中包含大量created_at无序的数据，于是不得不做一次内部排序，即典型的filesort。数据量小的时候感觉不明显，一旦status = 1的数据膨胀到几十万行，排序就是实打实的CPU和内存消耗。

这个场景更合理的方案是建复合索引，让过滤和排序共用同一条索引路径：

ALTER TABLE orders ADD KEY idx_status_created (status, created_at);

查询执行时，MySQL先定位到status = 1的第一行，然后沿着created_at的有序性直接向下扫描20行，全程不需要排序。这也是复合索引“一石二鸟”的核心思想。

3.2 深分页LIMIT 100000, 20才是真正的无底洞

排序问题还没完，另一个常见坑是深分页。同样的ORDER BY created_at DESC LIMIT 100000, 20，即使索引完美，MySQL也得先按索引顺序扫描到第100020行，再把前100000行扔掉。这么做的时间复杂度随页码增长而增长，越翻越慢。

生产上我常用的两种方案，一种是“延迟关联”：

SELECT t.* FROM orders t INNER JOIN ( SELECT id FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id = tmp.id;

内层只查主键id，不取整行数据，走的还是索引，扫描成本低很多；外层再根据20个id回表取完整数据，性能提升非常明显。

另一种方案是适合“滚动加载”场景的游标分页，上一页最后一条记录的created_at作为下一页的起点：

SELECT * FROM orders WHERE status = 1 AND created_at < '2024-06-01 12:00:00' ORDER BY created_at DESC LIMIT 20;

这种方式完全规避了偏移量计算，无论翻到多深，扫描行数都固定在一个小范围内。需要注意的是，如果排序字段存在重复值，游标分页会漏数据，所以建议排序字段带上主键作为二级排序方式，比如ORDER BY created_at DESC, id DESC，同时游标条件也要带上id。

4. 二级索引更新时的锁交叉：一个容易被忽视的生产级死锁现场

4.1 更新一条记录，InnoDB到底加了哪些锁

这个坑我最想详细说，因为它不像前面的问题那样看EXPLAIN就能发现。有一次核心账户表死锁，我SHOW ENGINE INNODB STATUS看到两个事务在相互等待，一个是二级索引项上的锁，一个是主键记录上的锁，互相咬死。

背后的机制是这样的：InnoDB更习惯用主键定位记录。当你用二级索引定位并更新某条记录时，InnoDB会先对二级索引项加锁，然后再回表，对主键索引对应的记录加锁。对于一条记录来说，这个“先锁二级索引再锁主键”的过程是固定顺序，但如果两个事务分别通过不同的二级索引访问同一条主键记录，锁的获取顺序就可能出现交叉：

假设表里有索引idx_status和idx_phone，事务A执行UPDATE users SET ... WHERE status = 1，通过idx_status定位记录：先拿idx_status上的锁，再拿主键锁。事务B执行UPDATE users SET ... WHERE user_phone = '13800138000'，通过idx_phone定位同一行记录：先拿idx_phone锁，再去抢同一行主键锁。如果A已经拿着主键锁，B等着拿主键锁，同时A的下一步又想拿idx_phone上的锁，而这个锁恰好被B持有，就形成了交叉等待，死锁产生。

4.2 生产级解决思路：统一访问路径，消除锁顺序冲突

这类死锁最直接的办法，是让所有更新操作尽量通过同一条索引路径去定位记录。如果一个事务用status更新，另一个事务用phone更新，两者握手时风险就高。我在业务侧做的第一件事就是统一写路径：核心更新一律用主键id操作，而不是让用户通过各种二级索引条件直接改行数据。

比如业务流程先SELECT id FROM users WHERE user_phone = ...，拿到主键后，再执行：

UPDATE users SET ... WHERE id = 12345;

这样所有写操作都走主键，锁顺序统一，交叉现场自然消失。主键定位还有一个额外好处：主键索引是聚簇索引，不需要回表，锁的持有时间更短。

另外，MySQL 8.0里可以通过打开死锁日志来捕捉这类问题的详细现场：

SET GLOBAL innodb_print_all_deadlocks = ON;

开启后，每次死锁都会写入错误日志，包含事务执行的SQL、持有锁和等待锁的具体索引项。定位到是哪些索引路径互相交叉后，下一步就可以从索引设计上做文章：减少同一条记录上的多路二级索引访问，或者把业务里真正必须走二级索引的写操作梳理成一批固定模板。

4.3 隔离级别与锁类型也要纳入考虑

还有一个容易踩的细节：在REPEATABLE READ隔离级别下，InnoDB为了处理幻读，会在范围查询时加gap lock或next-key lock。这意味着锁的范围不只是满足条件的行，还可能是索引段区间。两级索引叠加区间锁，死锁概率更高。

如果业务允许，把隔离级别调整成READ COMMITTED能显著减少间隙锁带来的额外锁定范围，从而降低锁竞争和死锁概率。当然这不是拍脑袋就能改的决定，需要和业务团队确认读取场景是否接受“不可重复读”带来的影响。至少在做索引设计时，要意识到：大概率的锁竞争，不只是“锁了哪些行”，更是“锁了哪个索引区间”。

5. 统计信息“过期”导致优化器选错索引：性能跳水的隐形原因

5.1 同一个SQL，测试环境秒回，生产环境卡死

这是很典型的一个坑：SQL完全一样，索引完全一样，测试环境执行计划用的是正解，生产环境却像一个“偏执狂”，死活不走该走的索引。有次我排查了半天，最后发现是表上的统计信息没有及时更新。

InnoDB的优化器在决定走哪个索引时，依赖的是表的统计信息，包括行数、索引基数、采样页分布等。生产环境的数据经过高频增删改，统计信息很容易变得不精确。优化器一看统计信息里某个索引的“区分度”很低，就选了全表扫描或者换了一条次要索引，性能自然就崩了。

解决办法首先是手动触发一次统计信息更新：

ANALYZE TABLE orders;

这是最轻量的操作，不会重建表，也不会长时间锁表。对于一般场景，这条SQL跑完，执行计划往往就能恢复正常。但要注意，它不是根治方案，数据还在持续变化，统计信息还会再次失真。

5.2 从“救火”到“防火”：统计信息维护策略

更稳定的做法是在业务低峰期设置定期维护任务，比如每周对核心大表执行一次ANALYZE TABLE。MySQL本身有innodb_stats_auto_recalc参数控制自动重算，默认开启，但它的触发逻辑是针对单表超过约10%数据变更时才会重算。对于部分高频更新但整体体量又很大的表来说，10%的阈值不容易触发，需要主动兜底。

还可以调大统计信息采样页数，让优化器拿到更准确的分桶数据：

SET GLOBAL innodb_stats_persistent_sample_pages = 32;

这个参数决定了ANALYZE TABLE时扫描多少页来做基数估计，设置偏小会让统计结果出现偏差。一般32到64之间的值在大多数场景下是合理的，太大了会增加统计分析和DDL的耗时，没必要盲目提高。

对于特别复杂的查询，如果临时无法优化统计信息，FORCE INDEX可以作为应急手段：

SELECT * FROM orders FORCE INDEX (idx_status_created) WHERE status = 1 AND created_at < '2024-06-01' ORDER BY created_at DESC LIMIT 20;

但我不建议把FORCE INDEX当成长期方案，因为它会冻结执行计划，一旦后续数据分布变化，强制指定索引可能变成新的瓶颈。它更合理的定位是“救火工具”，真正的长期方案还是把统计信息维护和SQL写法都规范化。

5.3 一张排查速查表，解决80%的索引问题

这些年我把踩过的坑整理成了一张自查清单，分享出来，排查线上慢查询时顺序对照就行：

6. 线上索引变更的完整落地步骤：从设计到验证，一次做对

6.1 上线前先做“索引健康检查”

变更索引之前，先跑一轮当前库的健康检查是不可省略的准备工作。我自己的习惯是执行下面三个SQL：

-- 查看所有索引大小与占用 SELECT TABLE_NAME, INDEX_NAME, stat_value * @@innodb_page_size / 1024 / 1024 AS index_size_mb FROM mysql.innodb_index_stats WHERE database_name = 'your_db' AND stat_name = 'size'; -- 查看各索引的使用情况（8.0用performance_schema） SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA = 'your_db' AND INDEX_NAME IS NOT NULL ORDER BY COUNT_STAR ASC;

先看清哪些索引几乎没被使用过，哪些索引虽然使用但效果不佳，再结合业务查询模式来判断删除或新增。不要只看慢日志，索引使用频率数据会更客观。

6.2 变更流程中的三个关键节点

索引变更虽然不像改表结构那么重，但在生产库上也必须按流程走。第一步，在测试环境用完整数据量的副本做一次EXPLAIN对比，记录新增或删除索引前后的执行计划变化。第二步，在主库低峰期执行DDL，建议使用pt-online-schema-change或者MySQL 8.0原生的ALGORITHM=INPLACE选项，避免长时间阻塞读写：

ALTER TABLE orders ADD KEY idx_status_created (status, created_at), ALGORITHM=INPLACE, LOCK=NONE;

第三步，变更完成后立刻开启一段观察窗口，监控慢查询、锁等待、CPU/IO指标，至少要覆盖一个完整的业务高峰周期。如果发现异常，第一时间回滚到备份版本，不要在现场“调试”太久。

6.3 从执行计划到真实扫描行数的双重验证

很多新人只看EXPLAIN里的key字段，看到索引名就放心了。这是最大的误区。EXPLAIN给出的rows是估算值，不一定代表真实扫描量。更靠谱的方式是在MySQL 8.0里用EXPLAIN ANALYZE直接看实际执行路径和时间：

EXPLAIN ANALYZE SELECT order_id, user_id, amount, status FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 20;

它会给出每个阶段的实际行数、实际耗时和循环次数，能非常直观地发现“走了索引但还是扫了太多行”的情况。我的经验是，任何索引优化在落地前，都要用EXPLAIN ANALYZE确认最终扫描行数和预期一致，再谈上线。

7. 最后想说的几句话

把这几套方案完整理完之后，我自己最大的一个体会是：索引优化的关键不在于“加了多少索引”，而在于“每一步执行都符合预期”。你不需要把MySQL的锁机制背得滚瓜烂熟，但至少要能在遇到问题时，用对排查工具、看懂执行计划、理解锁等待的方向。

另一个实际经验是：索引变更最能暴露压测做不到的意外，每个方案上线前我都建议准备一份“回滚预案”——删掉的索引先备份创建语句，切换的查询先保留旧版本，统计信息改动前记录默认参数。宁可准备用不上，也别在故障发生时干瞪眼。

如果非要我提炼一条最管用的建议：做任何MySQL查询优化，第一件事永远是EXPLAIN，第二件事是确认实际扫描行数，第三件事才是讨论索引怎么改。把顺序反了，很多功夫都会白费。这套方法论经过多次实战验证，希望你也能少踩几个坑。