mysql -索引失效(学习笔记)

什么时候索引失效?

1. 最左前缀法则 : 如果是联合索引,查询从索引的最左侧开始,不跳过其他索引. 如果跳过,则索引失效

create index index_name on user(name,status,address);//创建组合索引
select * from user where name = ? and status = ? and address = ?  (全部索引有效)
select * from user where status = ?   (索引失效)
select * from user where name = ? and status = ?   (两个索引有效)
select * from user where name = ? and address = ?  (第一个索引有效, 第二个失效)
select * from user where status = ? and address = ? (索引失效)

2. 使用范围查询时,范围查询条件的右侧的列的索引失效.

select * from user where age > ? and address = ?  则 address 失效.

3. 如果使用运算的话,则索引失效;

select * from user where substring(name,startIndex,count);  name索引失效;

4. 字符串不加单引号则索引失效.

select * from user where id = 1162166234825900032;  id索引失效.

5. 覆盖索引: 只访问索引的查询

select name,status,address from user;   最优: 因为name,status,address是联合索引, using where;using index;
select name from user;   				using index;
select name,fix_name from user;  		using codition;

using index: 使用覆盖索引的时候就会出现
using where: 在查找使用索引的情况下,需要回表去查询所需的数据.
using index condition: 查找使用了索引,但是需要回表查询数据.
using index;using where : 查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据.

6. or查询则索引失效.

select * from user where name = ? or nickName = ? 整个索引失效
	

7. like 模糊查询加前置%则索引失效

select * from user where name like "%xx%";   索引失效, 使用覆盖索引(查询的所有的字段都是索引列)可解决这个问题

8. 如果MYSQL评估使用索引比全表扫描还慢,则MYSQL自动放弃索引查询

select * from user where id = ?  ===> 假设全表共200条数据,扫描到了199条匹配的数据, 则放弃索引,执行全表扫描

9. is null, is not null 有时索引失效.(由数据值决定)

select * from user where name is null;   MYSQL会自动评估,如果数据多为null,则索引失效, 如果数据大多数都不null, 则索引有效.
select * from user where name is not null; MYSQL会自动评估,如果数据多不为null,则索引失效, 如果数据大多数都为null, 则索引有效.

10. in , not in in有效, not in 失效

11. 联合索引: 数据库会使用联合索引, 建议使用联合索引
    单列索引: 数据库会使用最优的索引,而不是全部索引.(辨识度最高的优先)

查看索引的使用情况

show global status like