DROP TABLE IF EXISTS `tbl_syn_blog`; CREATE TABLE `tbl_syn_blog` ( `id` int NOT NULL, `title` varchar(100) DEFAULT NULL, `user_id` int DEFAULT NULL COMMENT '用户ID', `blog_type` int DEFAULT NULL COMMENT '类型', `content` blob COMMENT '内容', `is_delete` varchar(1) DEFAULT '0', `create_date` varchar(20) DEFAULT NULL, `update_date` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_blog_main` (`create_date`,`blog_type`,`is_delete`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;这张表有一个普通联合索引idx_blog_main。 分别是:create_date, blog_type, is_delete。另外ID是主键。
explain SELECT * from tbl_syn_blog where create_date >= '2023-06-05' and create_date < '2023-06-06'
EXPLAIN SELECT * FROM tbl_syn_blog WHERE create_date >= '2023-06-05' AND create_date < '2023-06-06' AND is_delete = '0'
EXPLAIN SELECT * FROM tbl_syn_blog WHERE create_date >= '2023-06-05' AND create_date < '2023-06-06' AND blog_type = 2
EXPLAIN SELECT * FROM tbl_syn_blog WHERE create_date >= '2023-06-05' AND create_date < '2023-06-06' AND blog_type = 7 AND id = 3
如图,加上id作为where条件后,key_len直接变成了4,也就是说只有id的索引生效了。这也说明了,执行器会根据具体的情况,选择最佳的索引去走,一次sql查询中,一张表最多只会走一个索引。
EXPLAIN SELECT * FROM tbl_syn_blog WHERE create_date >= '2023-06-05' AND create_date < '2023-06-06' AND blog_type in (SELECT id from sys_blog_type)sys_blog_type表中的id很多,有几十万,那么就导致IN子句查出来的数量很多,会导致blog_type不走索引。
EXPLAIN SELECT * FROM tbl_syn_blog WHERE 1=1 AND blog_type = 5 AND create_date >= '2023-01-20' AND create_date < '2023-06-28'因为上面sql中的日期范围很大,对应数量非常多,超过了总表的30%,那么执行器会认为还不如全表扫描,于是索引就失效了。还有很多索引失效的场景,这边就不一一赘述了。