一条 MySQL 慢查询排查：我先后建了 4 个索引，只有一个真正生效

发布时间：2026/11/5 7:53:04

本文摘要：给 500 万行订单表建索引时，常见误区是给每个 WHERE 列都建单列索引。MySQL 通常只用一个索引，需要的是等值列在前、范围列在后的联合索引。

一、问题与结论

merchant_order表约 500 万行，一条业务查询连续跑了 2 秒以上：

SELECT*FROMmerchant_orderWHEREcustomer_id=20034ANDprovince='Zhejiang'ANDorder_type='online'ANDorder_date>='2024-01-01 00:00:00'ORDERBYorder_dateDESCLIMIT50;

我先后建了 4 个索引——两次单列、一次列序错误的联合、一次列序正确的联合。逐个用EXPLAIN验证后，只有第四个把查询从秒级拉到了毫秒级。本文复现这四次实验，解释背后的优化器规则，并说明联合索引的适用边界。

二、排查与选择依据

四次尝试

尝试 1：idx_province(province)

province基数只有 10。单个省份命中约 50 万行，优化器估算回表成本接近全表扫描，key为NULL。低基数列建单列索引基本没有意义。

尝试 2：idx_order_date(order_date)

order_date基数高，优化器会选择（type: range），但order_date >= '2024-01-01'范围内仍有百万级行，回表次数远超LIMIT 50的需求。

尝试 3：idx_order_date_province(order_date, province)

联合索引但列序错误。order_date是范围条件，province虽然在索引中，却无法参与 B-Tree 导航。key_len只有 5——加了一个列，几乎等于没加。

尝试 4：idx_customer_province_orderdate(customer_id, province, order_date)

等值条件的列前置。customer_id和province通过等值匹配精确缩小 B-Tree 定位范围，order_date在剩余空间内做范围扫描。这是唯一一个让ORDER BY order_date DESC也由索引提供、消除了Using filesort的方案。

EXPLAIN 关键指标对比

以下为四次尝试的EXPLAIN输出对照（rows为优化器估算值；实际输出请在你自己的 MySQL 8.0 实例上执行验证，数据量和统计信息会影响估算结果）：

索引	`type`	`key`	`key_len`	`rows`	`Extra`
`idx_province`	`ALL`	`NULL`	`NULL`	≈全表行数	`Using filesort`
`idx_order_date`	`range`	`idx_order_date`	5	数十万	`Using filesort`
`idx_order_date_province`	`range`	`idx_order_date_province`	5	数十万	`Using filesort`
`idx_customer_province_orderdate`	`ref`	`idx_customer_province_orderdate`	90	约百级	无`filesort`

key_len是判断索引利用程度最直接的指标：尝试 1 的key为NULL，索引完全未被使用；尝试 3 的key_len与尝试 2 相同（均为 5），证明province被跳过；尝试 4 的key_len（customer_id4 字节 +province81 字节 +order_date5 字节）覆盖三列，说明全部参与导航。EXPLAIN FORMAT=JSON中的cost_info还能看到优化器估算的读取行数和总成本。

替代方案与取舍

当联合索引仍不满足需求时，以下方案各有适用边界：

方案	选择条件	代价	不适用场景
覆盖索引	查询列少、回表开销大	索引变大，写入变慢，磁盘占用增加	`SELECT`列多、写入频繁的表
改写 SQL 缩小范围	业务允许缩小时间窗口或改用游标分页	需修改业务逻辑	需要跨大范围查询的报表场景
分区表	以时间范围查询为主、历史数据极少访问	分区管理复杂度增加	查询条件不含分区键的场景

什么时候不该用本文方案：如果查询本身需要扫描大量行（如SELECT *且无有效过滤条件），联合索引无法解决——应改写查询或分批处理。FORCE INDEX 能强制优化器选某索引，但过度使用会掩盖问题，索引删除后查询会直接报错。

三、关键原理

最左前缀规则

联合索引(a, b, c)的 B-Tree 按a排序，a相同时按b排序。当a是范围条件时，b和c无法参与 B-Tree 导航，只能在回表后做过滤。这就是尝试 3 失效的直接原因——order_date是范围，后面的province白建了。

隐式类型转换

如果customer_id定义为VARCHAR(10)，而查询传入WHERE customer_id = 20034（数字、无引号），MySQL 会将列值逐行转换为数字比较——而不是将常量转为字符串。这导致索引静默失效：EXPLAIN中key为NULL。修复：应用层改用setString()传参，或确保列类型与查询常量类型一致。

函数作用于索引列

WHERE DATE(order_date) = '2024-06-01'中，DATE()函数在 B-Tree 导航前被应用，优化器无法使用索引。MySQL 8.0.13+ 可建函数索引INDEX((DATE(order_date)))解决；更早版本只能改写为WHERE order_date >= '2024-06-01' AND order_date < '2024-06-02'。

四、可运行示例

以下脚本可在 MySQL 8.0 实例上直接执行，复现上述四次实验。数据生成约需 5–15 分钟，若想快速验证可将行数降为 50 万。

建表与造数据

CREATETABLEmerchant_order(idBIGINTNOTNULLAUTO_INCREMENTPRIMARYKEY,customer_idINTNOTNULL,provinceVARCHAR(20)NOTNULL,order_typeVARCHAR(10)NOTNULL,order_dateDATETIMENOTNULL,amountDECIMAL(10,2)NOTNULLDEFAULT0,statusTINYINTNOTNULLDEFAULT0)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;DELIMITER$$CREATEPROCEDUREgenerate_test_data()BEGINDECLAREiINTDEFAULT0;WHILEi<5000000DOINSERTINTOmerchant_order(customer_id,province,order_type,order_date,amount,status)VALUES(1+FLOOR(RAND()*100000),ELT(1+FLOOR(RAND()*10),'Zhejiang','Jiangsu','Guangdong','Shandong','Sichuan','Hubei','Henan','Fujian','Anhui','Hunan'),ELT(1+FLOOR(RAND()*3),'online','offline','cod'),DATE_ADD('2021-01-01 00:00:00',INTERVALFLOOR(RAND()*1095)DAY),ROUND(RAND()*5000,2),FLOOR(RAND()*4));SETi=i+1;IFi%1000=0THENCOMMIT;ENDIF;ENDWHILE;COMMIT;END$$DELIMITER;CALLgenerate_test_data();ANALYZETABLEmerchant_order;

ANALYZE TABLE是必要步骤——不刷新统计信息，优化器可能基于过时的rows估算做出错误决策。

四次实验

-- 尝试 1ALTERTABLEmerchant_orderADDINDEXidx_province(province);EXPLAINSELECT*FROMmerchant_orderWHEREcustomer_id=20034ANDprovince='Zhejiang'ANDorder_type='online'ANDorder_date>='2024-01-01 00:00:00'ORDERBYorder_dateDESCLIMIT50;-- 尝试 2ALTERTABLEmerchant_orderDROPINDEXidx_province;ALTERTABLEmerchant_orderADDINDEXidx_order_date(order_date);-- 执行相同 EXPLAIN 查询-- 尝试 3ALTERTABLEmerchant_orderDROPINDEXidx_order_date;ALTERTABLEmerchant_orderADDINDEXidx_order_date_province(order_date,province);-- 执行相同 EXPLAIN 查询-- 尝试 4（唯一生效）ALTERTABLEmerchant_orderDROPINDEXidx_order_date_province;ALTERTABLEmerchant_orderADDINDEXidx_customer_province_orderdate(customer_id,province,order_date);-- 执行相同 EXPLAIN 查询

五、验证结果与边界

预期输出

尝试 1–3 的EXPLAIN中rows均在数十万至百万级别，耗时为秒级；尝试 4 的rows约百级别，耗时为毫秒级，且Extra中无Using filesort。key_len从NULL/5/5 变为 90，是索引真正生效的标志。实际输出请在你自己的实例上执行上述脚本验证——不同数据分布下rows估算值会有所偏差，但key_len和type的变化模式一致。