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;