一、创建测试数据
mysql版本5.7.30
创建表结构
create table consumer_info
(
id int auto_increment
primary key,
user_name varchar(32) null,
age int null,
account_name varchar(32) null,
email varchar(64) null,
details_id int null
);
插入数据
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'lily',20,'lily_admin','lily@163.com',1);
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'zhangsan',59,'zhangsan_admin','zhangsan@163.com',2);
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'xiaobai',17,'xiaobai_admin','xiaobai@qq.com',1);
insert into consumer_info(id, user_name, age, account_name, email, details_id) values (null,'huahua',21,'huahua_admin','huahua@163.com',1);
创建复合索引(user_name,age,account_name):索引名叫index_uaa
ALTER TABLE consumer_info ADD INDEX index_uaa(user_name,age,account_name);
查看创建好的索引:
show index from consumer_info;
二、测试索引
1.复合索引跳过首字段导致索引失效
- 测试一:复合索引的三个字段(user_name,age,account_name)都使用
explain select * from consumer_info where user_name='xiaobai' and age=17 and account_name='xiaobai_admin';
可以看到使用了索引,并且ref为三个常量。此时使用到了索引,索引没有失效。
- 测试二:仅使用age和account_name字段做检索
explain select * from consumer_info where age=17 and account_name='xiaobai_admin';
可以从表中看到type为all:全表扫描,说明索引失效了。
- 测试三:跳过复合索引的中间字段,使用(user_name,和account_name)做检索
explain select * from consumer_info where user_name='xiaobai' and account_name='xiaobai_admin';
ref中只有一个const,说明只使用一个常量来索引。说明中间跳过一个索引字段之后,后面的字段索引会失效。
- 测试四: 在复合索引中使用范围查找(age字段使用范围条件)
explain select * from consumer_info where user_name='xiaobai' and age>10 and account_name='xiaobai_admin';
使用了范围条件,索引type变为了range,同时key_len值为104,如果是全匹配应该为203,才对,说明是某个索引列失效了。
- 结论:
对于复合索引,要遵循最左前缀法则,即查询从索引的最左列开始,并且不要跳过索引中的列。
在复合索引中,范围条件后边的列索引会失效。
2.在索引字段上函数、计算或者类型转换导致索引失效
- 测试一:使用user_name正常检索
explain select * from consumer_info where user_name = 'xiaobai';
可以看到索引正常使用。
- 测试二:在user_name列使用了函数
explain select * from consumer_info where left(user_name,7) = 'xiaobai';
发现变成了全表扫描,索引失效。
- 测试三:使用了类型转换
explain select * from consumer_info where user_name = 100;
user_name字段本来是字符串类型,但是查询的时候使用了数值类型,倒是隐式的类型转换,索引失效。
- 结论
在索引字段上函数、计算或者类型转换会导致索引失效。
3.使用 select * 与select 索引列对比
- 测试一:使用select *
explain select * from consumer_info where user_name='xiaobai' and age=17 and account_name='xiaobai_admin';
此时extra为空。
- 测试二:使用select 索引列
explain select user_name,age,account_name from consumer_info where user_name='xiaobai' and age=17 and account_name='xiaobai_admin';
在extra中多出了一个using idnex:表示使用了覆盖索引,避免访问了表的数据行。对性能提升有帮助。
- 结论:尽量使用覆盖索引,查询列和索引列保持一致。
4.使用不等于(!= 或 <>)
- 测试一:使用=
explain select * from consumer_info where user_name='xiaobai';
索引正常使用。
- 测试二:使用!=
explain select * from consumer_info where user_name!='xiaobai';
全表扫描,索引失效。
- 结论
尽量不要使用!=或者<>,因为这样会使索引失效。
5.使用is null 或 is not null
- 测试一:使用is null
explain select * from consumer_info where user_name is null;
- 测试二:使用is not null
explain select * from consumer_info where user_name is not null;
- 结论
使用is not null 的时候会导致索引失效。
6.使用like以通配符开头的检索
- 测试一:使用like:xxx%
explain select * from consumer_info where user_name like 'xiaobai%';
用到索引了,但是不是ref,而是range
- 测试二:使用like:%xxx
explain select * from consumer_info where user_name like 'xiaobai%';
type为all,全表扫描,索引失效。
- 测试三:使用like %xxx,并且覆盖索引查询
explain select user_name,account_name from consumer_info where user_name like '%xiaobai%';
使用复合索引中的一个或者多个字段来做覆盖索引,可以看到此时是使用了索引的。解决了%开头索引失效的问题。
- 结论:
like以通配符%开头的条件会让索引失效。
like以通配符%开头的条件,使用覆盖索引可以解决索引失效的问题。
7.使用or来查询
- 测试:
explain select * from consumer_info where user_name='xiaobai' or age=17 ;
- 结论
在where条件中使用了or,会导致索引失效,从而全表扫描。
三、总结
- 对于复合索引,要遵循最左前缀法则,即查询从索引的最左列开始,并且不要跳过索引中的列。
- 在复合索引中,范围条件后边的列索引会失效。
- 在索引字段上函数、计算或者类型转换会导致索引失效。
- 尽量使用覆盖索引,查询列和索引列保持一致。
- 使用!=或者<>会使索引失效。
- 使用is not null 会导致索引失效。
- like以通配符%开头的条件会让索引失效。
- like以通配符%开头的条件,使用覆盖索引可以解决索引失效的问题。
- 在where条件中使用了or,会导致索引失效。