本文摘要给 500 万行订单表建索引时常见误区是给每个 WHERE 列都建单列索引。MySQL 通常只用一个索引需要的是等值列在前、范围列在后的联合索引。一、问题与结论merchant_order表约 500 万行一条业务查询连续跑了 2 秒以上SELECT*FROMmerchant_orderWHEREcustomer_id20034ANDprovinceZhejiangANDorder_typeonlineANDorder_date2024-01-01 00:00:00ORDERBYorder_dateDESCLIMIT50;我先后建了 4 个索引——两次单列、一次列序错误的联合、一次列序正确的联合。逐个用EXPLAIN验证后只有第四个把查询从秒级拉到了毫秒级。本文复现这四次实验解释背后的优化器规则并说明联合索引的适用边界。二、排查与选择依据四次尝试尝试 1idx_province(province)province基数只有 10。单个省份命中约 50 万行优化器估算回表成本接近全表扫描key为NULL。低基数列建单列索引基本没有意义。尝试 2idx_order_date(order_date)order_date基数高优化器会选择type: range但order_date 2024-01-01范围内仍有百万级行回表次数远超LIMIT 50的需求。尝试 3idx_order_date_province(order_date, province)联合索引但列序错误。order_date是范围条件province虽然在索引中却无法参与 B-Tree 导航。key_len只有 5——加了一个列几乎等于没加。尝试 4idx_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 实例上执行验证数据量和统计信息会影响估算结果索引typekeykey_lenrowsExtraidx_provinceALLNULLNULL≈全表行数Using filesortidx_order_daterangeidx_order_date5数十万Using filesortidx_order_date_provincerangeidx_order_date_province5数十万Using filesortidx_customer_province_orderdaterefidx_customer_province_orderdate90约百级无filesortkey_len是判断索引利用程度最直接的指标尝试 1 的key为NULL索引完全未被使用尝试 3 的key_len与尝试 2 相同均为 5证明province被跳过尝试 4 的key_lencustomer_id4 字节 province81 字节 order_date5 字节覆盖三列说明全部参与导航。EXPLAIN FORMATJSON中的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)ENGINEInnoDBDEFAULTCHARSETutf8mb4;DELIMITER$$CREATEPROCEDUREgenerate_test_data()BEGINDECLAREiINTDEFAULT0;WHILEi5000000DOINSERTINTOmerchant_order(customer_id,province,order_type,order_date,amount,status)VALUES(1FLOOR(RAND()*100000),ELT(1FLOOR(RAND()*10),Zhejiang,Jiangsu,Guangdong,Shandong,Sichuan,Hubei,Henan,Fujian,Anhui,Hunan),ELT(1FLOOR(RAND()*3),online,offline,cod),DATE_ADD(2021-01-01 00:00:00,INTERVALFLOOR(RAND()*1095)DAY),ROUND(RAND()*5000,2),FLOOR(RAND()*4));SETii1;IFi%10000THENCOMMIT;ENDIF;ENDWHILE;COMMIT;END$$DELIMITER;CALLgenerate_test_data();ANALYZETABLEmerchant_order;ANALYZE TABLE是必要步骤——不刷新统计信息优化器可能基于过时的rows估算做出错误决策。四次实验-- 尝试 1ALTERTABLEmerchant_orderADDINDEXidx_province(province);EXPLAINSELECT*FROMmerchant_orderWHEREcustomer_id20034ANDprovinceZhejiangANDorder_typeonlineANDorder_date2024-01-01 00:00:00ORDERBYorder_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: OptimizationMySQL 8.0 Reference Manual: Multiple-Column IndexesMySQL 8.0 Reference Manual: Type ConversionMySQL 8.0 Reference Manual: EXPLAINMySQL 8.0 Reference Manual: CREATE INDEX
本文摘要给 500 万行订单表建索引时常见误区是给每个 WHERE 列都建单列索引。MySQL 通常只用一个索引需要的是等值列在前、范围列在后的联合索引。一、问题与结论merchant_order表约 500 万行一条业务查询连续跑了 2 秒以上SELECT*FROMmerchant_orderWHEREcustomer_id20034ANDprovinceZhejiangANDorder_typeonlineANDorder_date2024-01-01 00:00:00ORDERBYorder_dateDESCLIMIT50;我先后建了 4 个索引——两次单列、一次列序错误的联合、一次列序正确的联合。逐个用EXPLAIN验证后只有第四个把查询从秒级拉到了毫秒级。本文复现这四次实验解释背后的优化器规则并说明联合索引的适用边界。二、排查与选择依据四次尝试尝试 1idx_province(province)province基数只有 10。单个省份命中约 50 万行优化器估算回表成本接近全表扫描key为NULL。低基数列建单列索引基本没有意义。尝试 2idx_order_date(order_date)order_date基数高优化器会选择type: range但order_date 2024-01-01范围内仍有百万级行回表次数远超LIMIT 50的需求。尝试 3idx_order_date_province(order_date, province)联合索引但列序错误。order_date是范围条件province虽然在索引中却无法参与 B-Tree 导航。key_len只有 5——加了一个列几乎等于没加。尝试 4idx_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 实例上执行验证数据量和统计信息会影响估算结果索引typekeykey_lenrowsExtraidx_provinceALLNULLNULL≈全表行数Using filesortidx_order_daterangeidx_order_date5数十万Using filesortidx_order_date_provincerangeidx_order_date_province5数十万Using filesortidx_customer_province_orderdaterefidx_customer_province_orderdate90约百级无filesortkey_len是判断索引利用程度最直接的指标尝试 1 的key为NULL索引完全未被使用尝试 3 的key_len与尝试 2 相同均为 5证明province被跳过尝试 4 的key_lencustomer_id4 字节 province81 字节 order_date5 字节覆盖三列说明全部参与导航。EXPLAIN FORMATJSON中的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)ENGINEInnoDBDEFAULTCHARSETutf8mb4;DELIMITER$$CREATEPROCEDUREgenerate_test_data()BEGINDECLAREiINTDEFAULT0;WHILEi5000000DOINSERTINTOmerchant_order(customer_id,province,order_type,order_date,amount,status)VALUES(1FLOOR(RAND()*100000),ELT(1FLOOR(RAND()*10),Zhejiang,Jiangsu,Guangdong,Shandong,Sichuan,Hubei,Henan,Fujian,Anhui,Hunan),ELT(1FLOOR(RAND()*3),online,offline,cod),DATE_ADD(2021-01-01 00:00:00,INTERVALFLOOR(RAND()*1095)DAY),ROUND(RAND()*5000,2),FLOOR(RAND()*4));SETii1;IFi%10000THENCOMMIT;ENDIF;ENDWHILE;COMMIT;END$$DELIMITER;CALLgenerate_test_data();ANALYZETABLEmerchant_order;ANALYZE TABLE是必要步骤——不刷新统计信息优化器可能基于过时的rows估算做出错误决策。四次实验-- 尝试 1ALTERTABLEmerchant_orderADDINDEXidx_province(province);EXPLAINSELECT*FROMmerchant_orderWHEREcustomer_id20034ANDprovinceZhejiangANDorder_typeonlineANDorder_date2024-01-01 00:00:00ORDERBYorder_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: OptimizationMySQL 8.0 Reference Manual: Multiple-Column IndexesMySQL 8.0 Reference Manual: Type ConversionMySQL 8.0 Reference Manual: EXPLAINMySQL 8.0 Reference Manual: CREATE INDEX
