MySQL联合索引遇到范围查询1、环境准备-- 查看MySQL版本 SELECT VERSION(); ​ -- MySQL 5.7 联合索引范围查询测试脚本 -- 联合索引字段顺序tenant_id, created_at, status -- created_at 位于联合索引中间用于 BETWEEN、、、、 等范围查询。 CREATE TABLE IF NOT EXISTS order_index_test ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID, order_no VARCHAR(32) NOT NULL COMMENT 订单号, tenant_id BIGINT UNSIGNED NOT NULL COMMENT 租户ID, created_at DATETIME NOT NULL COMMENT 创建时间, status TINYINT UNSIGNED NOT NULL COMMENT 状态1待支付2已支付3已关闭, amount DECIMAL(10, 2) NOT NULL COMMENT 订单金额, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_tenant_created_status (tenant_id, created_at, status) ) ENGINE InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci COMMENT 联合索引范围查询测试表; ​ -- 插入50条测试数据。 INSERT INTO order_index_test (order_no, tenant_id, created_at, status, amount) VALUES (ORD202607010001, 1001, 2026-07-01 09:10:00, 1, 99.00), (ORD202607010002, 1001, 2026-07-01 10:20:00, 2, 128.50), (ORD202607020003, 1001, 2026-07-02 08:30:00, 1, 59.90), (ORD202607020004, 1001, 2026-07-02 11:40:00, 3, 399.00), (ORD202607030005, 1001, 2026-07-03 09:15:00, 2, 219.80), (ORD202607030006, 1001, 2026-07-03 14:25:00, 1, 35.00), (ORD202607040007, 1001, 2026-07-04 07:50:00, 2, 499.99), (ORD202607040008, 1001, 2026-07-04 16:05:00, 3, 88.80), (ORD202607050009, 1001, 2026-07-05 10:10:00, 1, 129.00), (ORD202607050010, 1001, 2026-07-05 18:30:00, 2, 268.00), (ORD202607060011, 1001, 2026-07-06 08:45:00, 1, 45.60), (ORD202607060012, 1001, 2026-07-06 13:15:00, 3, 688.00), (ORD202607070013, 1001, 2026-07-07 09:35:00, 2, 178.90), (ORD202607070014, 1001, 2026-07-07 15:55:00, 1, 76.20), (ORD202607080015, 1001, 2026-07-08 10:05:00, 2, 340.00), (ORD202607080016, 1001, 2026-07-08 19:20:00, 3, 29.90), (ORD202607090017, 1001, 2026-07-09 08:10:00, 1, 108.00), (ORD202607090018, 1001, 2026-07-09 12:40:00, 2, 508.50), (ORD202607100019, 1001, 2026-07-10 09:00:00, 1, 66.60), (ORD202607100020, 1001, 2026-07-10 17:10:00, 3, 199.99), (ORD202607110021, 1001, 2026-07-11 08:20:00, 2, 320.00), (ORD202607110022, 1001, 2026-07-11 14:45:00, 1, 55.50), (ORD202607120023, 1001, 2026-07-12 10:30:00, 2, 780.00), (ORD202607130024, 1001, 2026-07-13 16:20:00, 3, 89.00), (ORD202607140025, 1001, 2026-07-14 11:15:00, 1, 145.80), ​ (ORD202607010026, 1002, 2026-07-01 09:25:00, 2, 210.00), (ORD202607020027, 1002, 2026-07-02 13:35:00, 1, 75.00), (ORD202607030028, 1002, 2026-07-03 08:55:00, 3, 430.00), (ORD202607040029, 1002, 2026-07-04 12:10:00, 2, 98.90), (ORD202607050030, 1002, 2026-07-05 17:45:00, 1, 156.00), (ORD202607060031, 1002, 2026-07-06 10:20:00, 2, 620.50), (ORD202607070032, 1002, 2026-07-07 14:30:00, 3, 40.00), (ORD202607080033, 1002, 2026-07-08 09:40:00, 1, 188.80), (ORD202607090034, 1002, 2026-07-09 18:05:00, 2, 275.00), (ORD202607100035, 1002, 2026-07-10 07:35:00, 1, 68.00), (ORD202607110036, 1002, 2026-07-11 11:50:00, 3, 399.90), (ORD202607120037, 1002, 2026-07-12 15:10:00, 2, 125.00), (ORD202607130038, 1002, 2026-07-13 09:45:00, 1, 49.90), (ORD202607140039, 1002, 2026-07-14 13:25:00, 2, 580.00), (ORD202607150040, 1002, 2026-07-15 16:40:00, 3, 230.00), ​ (ORD202607010041, 1003, 2026-07-01 08:15:00, 1, 39.90), (ORD202607020042, 1003, 2026-07-02 12:25:00, 2, 149.00), (ORD202607030043, 1003, 2026-07-03 16:35:00, 3, 299.00), (ORD202607040044, 1003, 2026-07-04 10:45:00, 1, 79.80), (ORD202607050045, 1003, 2026-07-05 14:55:00, 2, 450.00), (ORD202607060046, 1003, 2026-07-06 19:05:00, 1, 90.00), (ORD202607070047, 1003, 2026-07-07 11:15:00, 3, 520.00), (ORD202607080048, 1003, 2026-07-08 15:25:00, 2, 170.50), (ORD202607090049, 1003, 2026-07-09 09:35:00, 1, 65.00), (ORD202607100050, 1003, 2026-07-10 13:45:00, 2, 310.00); -- 验证数据总数应返回50。 SELECT COUNT(*) AS total_rows FROM order_index_test; ​ -- 查看索引定义。 SHOW INDEX FROM order_index_test;2、执行计划2.1 等值查询EXPLAIN SELECT id, order_no, tenant_id, created_at, status, amount FROM order_index_test WHERE tenant_id 1001 AND created_at 2026-07-03 00:00:00 AND status 1;执行计划关键字段key: idx_tenant_created_statuskey_len: 14Extra: Using index condition2.2 左开右开查询EXPLAIN SELECT id, order_no, tenant_id, created_at, status, amount FROM order_index_test WHERE tenant_id 1001 AND created_at 2026-07-03 00:00:00 AND created_at 2026-07-11 00:00:00 AND status 1;执行计划关键字段key: idx_tenant_created_statuskey_len: 13Extra: Using index condition2.3 , 左开右闭查询EXPLAIN SELECT id, order_no, tenant_id, created_at, status, amount FROM order_index_test WHERE tenant_id 1001 AND created_at 2026-07-03 00:00:00 AND created_at 2026-07-11 00:00:00 AND status 1;执行计划关键字段key: idx_tenant_created_statuskey_len: 14Extra: Using index condition2.4 , 左闭右开查询EXPLAIN SELECT id, order_no, tenant_id, created_at, status, amount FROM order_index_test WHERE tenant_id 1001 AND created_at 2026-07-03 00:00:00 AND created_at 2026-07-11 00:00:00 AND status 1;执行计划关键字段key: idx_tenant_created_statuskey_len: 14Extra: Using index condition2.5 , 左闭右闭查询EXPLAIN SELECT id, order_no, tenant_id, created_at, status, amount FROM order_index_test WHERE tenant_id 1001 AND created_at 2026-07-03 00:00:00 AND created_at 2026-07-11 00:00:00 AND status 1;执行计划关键字段key: idx_tenant_created_statuskey_len: 14Extra: Using index condition2.6 between and 查询EXPLAIN SELECT id, order_no, tenant_id, created_at, status, amount FROM order_index_test WHERE tenant_id 1001 AND created_at BETWEEN 2026-07-03 00:00:00 AND 2026-07-11 00:00:00 AND status 1;执行计划关键字段key: idx_tenant_created_statuskey_len: 14Extra: Using index condition3、总结BETWEEN、、、、都是范围条件都可以使用索引。联合索引遇到中间列的范围查询后范围列本身仍然使用索引。范围列后面的字段通常不能像前置等值字段一样缩小整个连续扫描区间。后续字段仍可能参与范围边界计算并通过 ICP 在索引层过滤所以不能简单地说“范围查询后面的索引全部失效”。key_len14不代表status对范围内所有记录都完成了精准定位还需要结合Using index condition和扫描行数判断。范围查询不会让联合索引后面的列完全失效但会限制后续列继续缩小连续扫描范围后续列可能参与边界计算和 ICP 过滤是否真正高效需要结合数据量和执行计划判断。