MySQL联合索引遇到范围查询问题解决

-- 查看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;

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;

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;

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;

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;

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;

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;