2021-10-20

MySQL索引失效以及原因（一)

我们都知道Innodb的底层数据结构是B+tree 支持范围查找。所以当索引失效的时候就会进行全表扫码，那么那些情况会索引失效呢？

建表命令

use `test`;
create table single_table(
	id  int not null auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    common_field varchar(100),
    primary key(id),
    key idx_key1(key1),
    unique key uk_key2(key2),
    key idx_key3(key3),
    key idx_key_part(key_part1,key_part2,key_part3)
)engine=InnoDB charset=utf8;

1.没有按照最左匹配原则

EXPLAIN SELECT * FROM single_table WHERE key_part2='1996' and key_part3='1996';

原因：mysql的索引是最左匹配原则，也就是说在联合索引的b+树是先按key_part1排序的，当key_part1没有使用的时候，那么便不会走索引。

mysql底层会自动帮我们优化索引，所以在查询的时候只要最左边的索引出现了就行

EXPLAIN SELECT * FROM single_table WHERE key_part2='1996' and key_part3='1996' and key_part1='1993';

2.%like进行模糊查询索引会失效

EXPLAIN SELECT * FROM single_table WHERE key1 like '%123';

原因：因为索引是按照首字母排序的，当第一个相同的时候比较第二位，我们进行前模糊查询则无法走排序后的树，所以会进行全表扫描

3.or语句如果左右两边少了索引的话那么索引会失效

EXPLAIN SELECT * FROM single_table WHERE  key2>100 or common_field='abc';

EXPLAIN SELECT * FROM single_table WHERE  key2>100 or key3='123';

EXPLAIN SELECT * FROM single_table WHERE  key1>100 or key2>100;