Mysql索引失效的几种情况

写在前面

explain命令中type中的属性，效率从上往下递减

• system：系统表，少量数据，往往不需要进行磁盘 IO
• const：常量连接
• eq_ref：主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描
• ref：非主键非唯一索引等值扫描
• range：范围扫描
• index：索引树扫描
• ALL：全表扫描 (full table scan)

表结构：

Field	Type	Null	Key	Default
id	int(11)	NO	PRI	NULL
title	varchar(100)	NO		NULL
author	varchar(30)	NO	MUL	NULL
create_time	datetime	NO		NULL
views	int(30)	NO		NULL

建索引：

create index idx_blog_acv on blog(author,create_time,views);

show index from blog;

Table	Non_unique	Key_name	Seq_in_index	Column_name	Index_type
blog	0	PRIMARY	1	id	BTREE
blog	1	idx_blog_acv	1	author	BTREE
blog	1	idx_blog_acv	2	create_time	BTREE
blog	1	idx_blog_acv	3	views	BTREE

一、最左前缀匹配法则

用到了所建立的全部索引

explain select * from blog where author ="张三" and create_time="2020-05-08" and views = 10000000;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ref	idx_blog_acv	idx_blog_acv	101	const,const,const	1	Using index condition

由于条件中缺少第二个索引字段，所以author后的所有字段索引失效，变成全表扫描，数据量一大，效率降低

explain select * from blog where author ="张三" and views = 10000000;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ref	idx_blog_acv	idx_blog_acv	92	const	1	Using index condition

二、在索引列上计算（函数，类型转换）会导致索引失效

未计算：

explain select * from blog where author = "张三";

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ref	idx_blog_acv	idx_blog_acv	92	const	1	Using index condition

已计算：

explain select * from blog where left(author,6) = "张三";

导致全表扫描：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ALL	NULL	NULL	NULL	NULL	3	Using where

三、索引中范围条件右边的列的索引会自动失效（但是范围条件之前的和范围条件还是用到索引的）

未使用范围条件：

explain select * from blog where author="张三" and create_time = "2020-5-8" and views = 10000000;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ref	idx_blog_acv	idx_blog_acv	101	const,const,const	1	Using index condition

使用范围条件：

explain select * from blog where author="张三" and create_time > "2020-5-7" and views = 10000000;

create_time后的索引列失效，变成全表查询

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	range	idx_blog_acv	idx_blog_acv	97	NULL	1	Using index condition

四、索引列使用 ！= ，<>，is null，is not null会导致索引失效

!=：

explain select * from blog where author != "张三";

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ALL	idx_blog_acv	NULL	NULL	NULL	3	Using where

is null：

explain select * from blog where author is null;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE

is not null：

explain select * from blog where author is not null;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	blog	ALL	idx_blog_acv	NULL	NULL	NULL	3	Using where

五、LIKE以%开头会导致索引失效，使用覆盖索引解决，用or连接也会导致索引失效

六、字符串不加单引号会导致索引失效（因为mysql底层会自动将int类型转换为varchar）