本文摘要：给 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的变化模式一致。
翻车场景：数据分布破坏索引效果
即使idx_customer_province_orderdate设计正确，当customer_id = 1（系统"默认账户"，累计约 120 万笔订单）时，索引在该customer_id下仍有百万级条目。120 万 / 500 万 ≈ 24%，优化器可能回退到全表扫描。
修复方向：缩小order_date时间范围；对极端账号数据做分表；或用覆盖索引减少回表。
隐式类型转换的常见失败
建表时若customer_id定义为VARCHAR(10)，而 JDBC 使用setInt()传参（MyBatis 中参数不带引号），EXPLAIN中key为NULL——索引静默失效。改用setString()或统一列类型即可恢复。
函数调用导致索引失效
WHERE DATE(order_date) = '2024-06-01'使索引无法被使用。MySQL 8.0.13+ 可建函数索引；更早版本应改写为范围查询。函数索引会增加索引维护成本，仅在查询模式固定、无法改写时才值得引入。
思考
当业务确实需要跨大范围查询时，应该优先改写查询缩小扫描范围，还是投入资源做分区或分表？FORCE INDEX 在统计信息偏差时能救急，但长期来看是不是应该修根因而非压症状？
参考资料
- MySQL 8.0 Reference Manual: Optimization
- MySQL 8.0 Reference Manual: Multiple-Column Indexes
- MySQL 8.0 Reference Manual: Type Conversion
- MySQL 8.0 Reference Manual: EXPLAIN
- MySQL 8.0 Reference Manual: CREATE INDEX
