企业官网建设流程全解析

昨晚刚处理完一个线上慢查询报警，一个原本 3 毫秒的订单列表查询，业务高峰时突然飙到 7 秒，直接拖垮了接口。排查后发现是索引失效——看似简单的 SQL，就因为一个隐式类型转换，把复合索引完全废掉了。

这类问题并不罕见。很多团队在开发阶段只关注“有没有索引”，却忽略了 MySQL 优化器是否真的用了它。本文用一个真实业务场景（订单列表查询）串联起完整的排查流程：从慢查询日志定位、EXPLAIN 判断方法，到索引失效原因、复合索引设计，再到上线后的验证与监控。每个环节都会给出可复现的 SQL 示例和关键参数说明，希望能让你下次遇到慢查询时，能更快地找到根因。

从慢查询日志定位问题 SQL 与数据分布

首先开启慢查询日志（生产环境建议长期开启，阈值设 1 秒）：

-- 查看当前设置 SHOW VARIABLES LIKE 'slow_query_log%'; SHOW VARIABLES LIKE 'long_query_time'; -- 临时开启（需 SUPER 权限） SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 单位秒，建议 0.1 秒用于测试 SET GLOBAL log_queries_not_using_indexes = ON;

线上出现报警后，登录数据库执行mysqldumpslow分析：

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

输出中可以看到类似下面的 SQL（实际场景中的订单查询）：

SELECT order_id, order_no, user_id, total_amount, status, create_time FROM orders WHERE pay_time > '2024-01-01' AND status = 1 AND user_id = 12345 ORDER BY create_time DESC LIMIT 10;

定位到 SQL 后，不要急着加索引，先看数据分布。执行：

SELECT COUNT(*) AS total, COUNT(DISTINCT user_id) AS users, COUNT(DISTINCT status) AS status_cnt, MAX(pay_time), MIN(pay_time) FROM orders;

假设输出结果：总记录 500 万，用户数 100 万，status 只有 2 个值（0 未支付，1 已支付），pay_time 跨度两年。
这几个数字已经透露出关键信息：status 字段的区分度极低（500 万条记录只分 2 个值），单独对 status 建索引基本没用。而 user_id 区分度约 5 条/用户，是很好的过滤条件。

EXPLAIN 关键字段：type、key、rows、filtered 的判断方法

现在对这个慢 SQL 执行EXPLAIN，看它是怎么执行的：

EXPLAIN SELECT order_id, order_no, user_id, total_amount, status, create_time FROM orders WHERE pay_time > '2024-01-01' AND status = 1 AND user_id = 12345 ORDER BY create_time DESC LIMIT 10;

输出（简化关键列）：

逐一分析

• type = range：使用了索引的范围扫描，比全表扫描好，但比 ref 差。这里说明驱动索引是idx_pay_time（索引范围条件pay_time > '2024-01-01'）。
• key = idx_pay_time：实际用到的索引。
• rows = 150000：MySQL 估算需要扫描 15 万行。pay_time 条件过滤后，依然有大量记录。
• filtered = 10%：表示经过status和user_id条件过滤后，只剩 10% 的行（实际返回 1.5 万行？逻辑不简单，filtered 是存储引擎层处理后的估算比例）。
• Extra = Using filesort：排序使用了文件排序，没有用到索引排序。

问题判断：
虽然idx_pay_time被用上了，但它不是最有效的访问路径。user_id和status两个条件无法在idx_pay_time中直接完成过滤，导致需要回表 + 文件排序。15 万行的回表都还能接受，但加上排序后，性能急剧下降（排序在内存或临时表中进行，LIMIT 10 也无法提前停止，因为排序需要全量数据）。

常见索引失效场景：函数、隐式转换、范围查询与低选择性字段

3.1 函数导致索引失效

我们经常在 WHERE 条件中对索引字段使用函数，比如：

-- 错误写法（索引失效） SELECT * FROM orders WHERE DATE(pay_time) = '2024-01-15'; -- 正确写法（用范围查询或虚拟列） SELECT * FROM orders WHERE pay_time >= '2024-01-15 00:00:00' AND pay_time < '2024-01-16 00:00:00';

如果一定要对日期做函数运算，MySQL 8.0 支持函数索引（Generated Column + 索引），但更推荐改造 SQL。

3.2 隐式类型转换

这是最常见也是最隐蔽的。我们订单表user_id是 VARCHAR(20)，但代码中传了数字：

// 错误的传参 String sql = "SELECT * FROM orders WHERE user_id = ?"; preparedStatement.setLong(1, 12345L); // 触发隐式转换

MySQL 在比较时会CAST(user_id AS SIGNED)，导致索引失效。查看慢查询日志会发现 type 变为 ALL。

解决方法很简单：类型匹配。如果字段是字符串，传入参数必须也是字符串；或者在 ORM 层面上确保参数类型一致。MyBatis 可以通过@Param配合jdbcType强制类型。

3.3 范围查询 + 低选择性字段

上面的例子已经展示了pay_time范围条件导致扫描大量行。低选择性字段（如 status）建索引通常弊大于利：索引的大小并不小，但扫描的比值很高（50% 的行），优化器经常选择全表扫描而不是使用索引。

一个更极端的情况：WHERE status IN (0,1)且 status 只有两个值，索引大概率被跳过。如果业务必须筛选 status，可以结合其他高选择性字段一起做复合索引，让 status 放在最后。

3.4 OR 条件

-- 两个字段各自有索引，但 OR 联合可能导致索引合并（index merge）效率低下 SELECT * FROM orders WHERE user_id = 12345 OR status = 1;

MySQL 5.7 及之后版本可以走index_merge，但通常不如改写为 UNION ALL：

SELECT * FROM orders WHERE user_id = 12345 UNION ALL SELECT * FROM orders WHERE status = 1 AND user_id != 12345;

复合索引设计：最左前缀、排序分页与覆盖索引

回到原始问题，我们需要的 SQL：

WHERE pay_time > '2024-01-01' AND status = 1 AND user_id = 12345 ORDER BY create_time DESC LIMIT 10;

4.1 最左前缀原则

复合索引(a, b, c)可以匹配a、(a,b)、(a,b,c)，但跳过中间列会导致后列无法使用。
对于我们的查询，如果建(user_id, status, pay_time)，那么user_id = 12345可以走索引，status = 1也能走（等值匹配，连续的条件），pay_time > '2024-01-01'只能做 range 过滤，且之后的所有列都无法再用于等值过滤。

但当前 SQL 中pay_time是范围条件，放在最后才能让前面的等值条件充分利用索引。

4.2 排序与分页优化

如果ORDER BY create_time DESC希望使用索引排序，那么索引必须包含排序字段，且排序方向要与索引一致（或者 MySQL 能反向扫描）。
最佳索引设计：

ALTER TABLE orders ADD INDEX idx_query (user_id, status, pay_time, create_time DESC);

注意create_time DESC是从 MySQL 8.0 开始支持的降序索引（之前版本只能 ASC，排序逆向时可能 filesort）。
这个索引的效果：

• 先通过user_id = 12345定位到某几个 Bucket（精确匹配）。
• status = 1继续在桶内精确匹配。
• pay_time > '2024-01-01'在桶内进行范围扫描（连续位置）。
• 由于create_time在索引中已经排序，ORDER BY create_time DESC可以直接从索引后面往前读，无需 filesort。
• 再加上LIMIT 10，只需要读取 10 行然后停止，极大地减少了扫描量。

4.3 覆盖索引——减少回表

查询列是order_id, order_no, user_id, total_amount, status, create_time，如果复合索引包含了这些列，那么整个查询完全在索引中完成，不需要回表。对于 InnoDB，覆盖索引可以显著降低磁盘 I/O。

-- 建立覆盖索引（如果表字段不多） ALTER TABLE orders ADD INDEX idx_cover (user_id, status, pay_time, create_time DESC, order_id, order_no, total_amount);

order_id是主键，InnoDB 二级索引叶子节点默认包含主键值，所以可以省略；order_no和total_amount需要显式包含。
生产环境中应权衡索引长度，避免索引过大（例如 VARCHAR 字段太长时，可以只包含前几个字符或改用前缀索引）。对于大字段，可以只将频繁查询的列放入覆盖索引，其他列回表。

4.4 改进后的 EXPLAIN

添加idx_cover后重新 EXPLAIN：

type = ref（等值匹配），rows = 1（估算扫描 1 行），Extra中出现Using index表示覆盖索引。实际性能提升：从 7 秒降到 0.5ms。

上线验证：回归压测、索引维护成本与监控指标

5.1 回归压测

索引变更后，必须对所有查询进行回归测试，防止其他 SQL 因索引变化而走错执行计划。推荐使用pt-query-digest对比前后慢查询分布。

压测脚本可以用sysbench或自行编写（模拟 100 并发，循环执行线上核心 SQL）。重点关注：

• P99 延时是否下降（目标 < 10ms）
• rows_examined平均值是否明显减少（performance_schema 可查看）

5.2 索引维护成本

• 写放大：每个索引都会增加 INSERT/UPDATE/DELETE 的代价。一个宽索引（比如 7 个字段）比 2 个单列索引的写入开销大得多。对于写密集型表，要平衡读与写。
• 索引大小：使用SHOW TABLE STATUS LIKE 'orders'查看Data_length和Index_length。如果索引大小超过数据本身（例如宽表 + 冗余索引），建议清理无用索引。
• 碎片：在高并发写入下，索引页可能会产生碎片。定期（业务低峰）执行OPTIMIZE TABLE orders或ALTER TABLE orders ENGINE=InnoDB整理。

5.3 监控指标

• MySQL 慢查询日志：设置long_query_time = 0.1（100ms），持续收集。
• performance_schema：开启events_statements_summary_by_digest，可以查看每个 SQL 模板的平均延时、执行次数、扫描行数。
• Prometheus + mysqld_exporter：监控mysql_global_status_innodb_rows_read和mysql_global_variables_handler_commit，如果发现 rows_read 突增而 QPS 未变，可能是索引失效。
• 业务监控：在 APM 中标记该接口的数据库调用耗时，设置告警阈值（如 > 200ms）。

总结：索引失效排查与设计要点

把整篇文章的精华提炼成一张检查清单，方便你下次遇到慢查询时快速对照：

1. 定位问题 SQL：开启慢查询日志（long_query_time=1），用mysqldumpslow或pt-query-digest找到耗时最长的查询。
2. 分析数据分布：COUNT(DISTINCT)检查字段区分度，低选择性字段（如 status）不要单独建索引。
3. EXPLAIN 必看四要素：type（理想为ref或const）、key（是否用了预期索引）、rows（扫描行数是否合理）、Extra（警惕Using filesort和Using temporary）。
4. 检查常见索引失效：
   - 函数操作（DATE()、LEFT()等）→ 改用范围查询或函数索引。
   - 隐式类型转换（字符串 vs 数字）→ 保证参数类型与字段类型一致。
   - 范围查询放错位置 → 等值条件放左边，范围条件放右边。
   - OR 条件 → 改写成 UNION ALL。
5. 设计复合索引：
   - 最左前缀：等值条件优先，范围条件靠后。
   - 排序字段纳入索引（MySQL 8.0 支持降序索引）。
   - 考虑覆盖索引减少回表，但注意索引长度。
6. 上线前验证：回归压测所有 SQL，关注 P99 延时和rows_examined。
7. 持续监控：慢查询日志 + performance_schema + 业务 APM 告警，形成闭环。

索引失效不是玄学，根源通常是：WHERE 条件中对索引列使用了函数/隐式转换、范围查询放错了位置、排序字段没有纳入索引。排查时，先看慢查询日志锁定 SQL，然后用 EXPLAIN 盯着type、rows和Extra中的Using filesort或Using index condition；复合索引设计遵循“最左前缀 + 等值在前范围在后 + 覆盖排序列”的原则，同时注意低选择性字段的取舍。

最后，记得把索引变更当作一次完整的发布，压测通过后再上线，并持续监控一周。如果我的经验能帮你少踩一个坑，这篇文章就没白写。