Mysql 索引问题大揭秘（持续更新）

MySQL 索引探索

• 版本：5.7.34

一、索引列参与计算

explain select * from test where age = 10; 		# 触发索引
explain select * from test where age = 10 + 10; # 触发索引
explain select * from test where age + 10 = 20; # possible_keys 未触发，keys 实际触发，不稳定

二、索引列使用函数

explain select * from	test where name = concat("admin",1); 	# 触发索引
explain select * from	test where concat(name,"1") = "admin1"; # 不触发索引

三、索引列使用 like 语句

explain select * from	test where name like "jason%";  # 触发索引
explain select * from	test where name like "%jason";  # 不触发索引
explain select * from	test where name like "%jason%"; # 不触发索引

四、数据类型隐式

# age 是 int 类型
explain select * from	test where age = 1;   # 触发索引
explain select * from	test where age = "1"; # 触发索引

# code 是 varchar 类型
explain select * from	test where code = "320000"; # 触发索引
explain select * from	test where code = 320000; 	# 不触发索引

五、索引列使用 or 或 in 语句

大致规律：in 或者 or 的数据量占比越大越可能不走，具体情况是看优化器自己决定的。

六、索引列使用 != 或 <> 或 is null 或 is not null 语句

这几类都不会触发索引

# name 是 varchar 类型
explain select * from	test where name != "jason"; # 不触发索引
explain select * from	test where name <> "jason"  # 不触发索引

# age 是 int 类型
explain select * from	test where age != 1; # 不触发索引
explain select * from	test where age <> 1; # 不触发索引

# time 是datetime 类型
explain select * from	test where time is null;		# 不触发索引
explain select * from	test where time is not null;	# 不触发索引

七、复合索引

复合索引：最左匹配原则

7.1 建立复合索引

ALTER TABLE `table_name` ADD INDEX (`c1`,`c2`,`c3`);

说明：上面建立的复合索引 c1，c2，c3，本质上是建立了三个索引：c1；c1，c2；c1，c2，c3。

c1

c1	c2

c1	c2	c3

explain select * from	test where c1 = 1  and c2 = 2 and c3 = 3; # 触发索引，走 c1,c2,c3 索引

explain select * from	test where c1 = 1  and c3 = 3 and c2 = 2; # 触发索引，走 c1,c2,c3 索引，where 后面的顺序不收影响，mysql查询优化器会自动优化

explain select * from	test where c2 = 2  and c1 = 1 and c3 = 3;  # 触发索引，走 c1,c2,c3 索引，where 后面的顺序不收影响，mysql查询优化器会自动优化

explain select * from	test where c1 = 1  and c3 = 3; # 触发索引，仅仅走 c1 索引

explain select * from	test where c1 = 1  and c2 = 2; # 触发索引，走 c1,c2 索引

explain select * from	test where c2 = 2  and c1 = 1;  # 触发索引，走 c1,c2 索引，where 后面的顺序不收影响，mysql查询优化器会自动优化

explain select * from	test where c2 = 2  and c3 = 3; # 不触发索引，不满足最左原则

explain select * from	test where c1 = 1; # 触发索引 走 c1 索引

explain select * from	test where c2 = 2; # 不触发索引

explain select * from	test where c3 = 3; # 不触发索引