订单表,tb_order表,索引如下两种:
KEY `idx_created_at` (`created_at`) USING BTREE,
KEY `idx_distribution_id_pay_at` (`distribution_id`,`pay_at`),
EXPLAIN select * from tb_order WHERE created_at>=NOW() ORDER BY created_at;命中索引
EXPLAIN select * from tb_order WHERE created_at<=NOW() ORDER BY created_at desc LIMIT 20;命中索引
EXPLAIN select * from tb_order WHERE created_at<=NOW() ORDER BY created_at desc;没有命中索引
EXPLAIN select * from tb_order ORDER BY created_at LIMIT 20;命中索引
EXPLAIN select * from tb_order ORDER BY created_at;没有命中索引
EXPLAIN select * from tb_order WHERE pay_at='2018-08-01 23:59:59' and distribution_id in('','','') ORDER BY created_at;命中索引,但是有排序
EXPLAIN select * from tb_order WHERE pay_at='2018-08-01 23:59:59' and distribution_id in('','','');命中索引
EXPLAIN select * from tb_order WHERE distribution_id in('','','') and pay_at='2018-08-01 23:59:59' ORDER BY created_at;命中索引,但是有排序
EXPLAIN select * from tb_order WHERE distribution_id in('','','') and pay_at='2018-08-01 23:59:59';命中索引
EXPLAIN select * from tb_order WHERE pay_at='2018-08-01 23:59:59';没有命中索引
总结:
1、组合索引最左原则
2、查询出来的数据量小于总数据的30%,会走索引。
原理支撑:
Mysql索引原理
MySQL的InnoDB索引原理详解