本文验证用的是 MYSQL 5.8 版本；
1.1 or 真的不走索引？
建表：
CREATE TABLE `t_common_index` ( `id` int NOT NULL, `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `c_index` (`c`), KEY `a_index` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
复制代码
数据准备：
drop procedure idata;delimiter ;create procedure idata()begin declare i int; set i=1; while(i<=100000)do insert into t_common_index values(i, i, i,i); set i=i+1; end while;end;;delimiter ;call idata();
复制代码
1.1.1 索引列 or 非索引列
explain select * from t_common_index where id = 10000 or b = 12000 ;
复制代码
执行计划：
不走索引
1.1.2 索引列 or 索引列 + 等值查询
explain select * from t_common_index where a = 10000 or c = 12000;
复制代码
执行计划：
走索引
1.1.3 索引列 or 索引列 + 非等值查询
情景 1：
explain select * from t_common_index where a > 10000 or id < 12000;
复制代码
不走索引
情景 2：
explain select * from t_common_index where a > 10000 or id = 12000;
复制代码
不走索引
情景 3：
explain select * from t_common_index where a = 10000 or id < 12000;
复制代码
走索引
1.2 类型不匹配
建表语句：
CREATE TABLE `t_invalid_index_type` ( `id` int NOT NULL, `a` int DEFAULT NULL, `b` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_b` (`b`), KEY `index_a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
复制代码
数据初始化：
drop procedure idata;delimiter ;create procedure idata()begin declare i int; set i=1; while(i<=500000)do insert into t_invalid_index_type values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();
复制代码
情景 1：
explain select * from t_invalid_index_type where b = 1;
复制代码
字符赋值上数值_不走索引
情景 2：
explain select * from t_invalid_index_type where b = '1';
复制代码
类型匹配-走索引
情景 3：
explain select * from t_invalid_index_type where a = 1;
复制代码
类型匹配-走索引
情景 4：
explain select * from t_invalid_index_type where a = '1';
复制代码
数字类型赋值字符串-走索引
1.3 != 不走索引
注：仍用 t_invalid_index_type 表
情景 1：
explain select * from t_invalid_index_type where a != 1;
复制代码
不走索引
情景 2：
explain select * from t_invalid_index_type where a <> 1;
复制代码
不走索引
情景 3：
explain select * from t_invalid_index_type where b != '1';
复制代码
不走索引
注：!= 也走索引的例外情况，见后面 not null 部分
1.4 is null 和 is not null 不走索引？
建表：
CREATE TABLE `t_invalid_index_is_null` ( `id` int NOT NULL, `a` int DEFAULT NULL, `b` varchar(32) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_b` (`b`), KEY `index_a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码
初始化数据：
drop procedure idata;delimiter ;create procedure idata()begin declare i int; set i=1; while(i<=50000)do insert into t_invalid_index_is_null values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();
复制代码
情景 1: is null
explain select * from t_invalid_index_is_null where a is null;explain select * from t_invalid_index_is_null where b is null;
复制代码
a is null - 走索引
b is null - 走索引
情景 2 is not null
explain select * from t_invalid_index_is_null where a is not null;explain select * from t_invalid_index_is_null where b is not null;
复制代码
a is not null - 不走索引
b is not null 不走索引
执行这条 sql 后：
update t_invalid_index_is_null set b = null where 1 = 1;
复制代码
情景 3：
执行情景 1 中的语句依然走索引，我们主要看下 is not null 和 != 这两种情况；
explain select * from t_invalid_index_is_null where a is not null;explain select * from t_invalid_index_is_null where b is not null;explain select * from t_invalid_index_is_null where b != 'test';
复制代码
a 索引 未执行更新数据为 null 的 sql
a is not null - 不走索引
b is not null - 走索引
b != 'test' 走索引
1.5 like
建表
CREATE TABLE `t_invalid_combine_index` ( `id` int NOT NULL, `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` varchar(32) DEFAULT NULL, `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `index_a_b_c` (`a`,`b`,`c`), KEY `index_c` (`c`), KEY `index_create_time` (`create_time`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
复制代码
初始化数据
drop procedure idata;delimiter ;create procedure idata()begin declare i int; set i = 1; while(i <= 100000) do insert into t_invalid_combine_index values (i, i, i, i, current_timestamp); set i = i + 1; end while;end;delimiter ;call idata();
复制代码
情景 1：
explain select * from t_invalid_combine_index where c like '123%';
复制代码
字符% - 走索引
情景 2：
explain select * from t_invalid_combine_index where c like '%123%';
复制代码
%字符% - 不走索引
1.6 联合索引
情景 1：按序使用索引
explain select * from t_invalid_combine_index where a > 123;
复制代码
走索引
情景 2：随机使用索引
explain select * from t_invalid_combine_index where b > 123;
复制代码
不走索引
1.7 使用函数
create_time 的数据都是 2021-02-25 20 点左右的数据
情景 1：
explain select * from t_invalid_combine_index2 where create_time >= '2021-02-25 20:20:27';
复制代码
未走索引
备注：经过优化器，发现数据都是 2021-02-25 20:20:27 左右的数据，近似全表查询，所以并没有走索引；
情景 2：
explain select * from t_invalid_combine_index2 where create_time >= '2022-02-25 20:20:27';
复制代码
走索引
备注：当把时间改为 2022 年的一个时间，这个时间的数据在 create_time 这个字段上是肯定不存在的，mysql 这个时候走了索引；
情景 3：
explain select * from t_invalid_combine_index2 where DATE_FORMAT(create_time,'%Y-%m-%d %h:%m:%s') >= '2021-02-25 20:20:27';explain select * from t_invalid_combine_index2 where DATE_FORMAT(create_time,'%Y-%m-%d %h:%m:%s') >= '2022-02-25 20:20:27';
复制代码
不走索引
备注：索引上使用函数操作，肯定不走索引，无论怎么改时间；
情景 4：
explain select * from t_invalid_combine_index2 where create_time <= DATE_FORMAT('2020-02-26 20:20:27','%Y-%m-%d %h:%m:%s') ;
复制代码
走索引
情景 5：
explain select * from t_invalid_combine_index2 where create_time <= DATE_FORMAT('2021-02-26 20:20:27','%Y-%m-%d %h:%m:%s') ;
复制代码
不走索引
备注：即使不在索引上使用函数，和情景 1 相同，数据量较大的时候，也未走索引；
未完待续 ~
评论