MySQL--索引失效的各种情况举例分析（一）

索引失效

建表及准备数据：

CREATE TABLE `staffs`(
`id` INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)CHARSET utf8 COMMENT '员工记录表';


INSERT INTO staffs(`name`,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,age,pos,add_time) VALUES('2000',23,'dev',NOW());

SELECT * from staffs;

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(`name`,age,pos);

• 全值匹配
• 最佳左前缀法则
• 不再索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少SELECT *
• mysql在使用不等于（!= 或 <>）的时候无法使用索引会导致全表扫描
• is null, is not null 也无法使用索引
• like 以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作
• 字符串不加单引号索引失效
• 少用or，用它来连接时会索引失效

全值匹配

EXPLAIN SELECT * FROM staffs WHERE name = 'July';

```sql
EXPLAIN SELECT * FROM staffs WHERE name = ‘July’ AND age = 25;

![在这里插入图片描述](https://img-blog.csdnimg.cn/20191124190434151.png)

```sql
EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 25 AND pos = 'dev';

以上三种情况都可以用到索引

EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';

EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

以上两种情况索引失效了。。。违背了左前缀法则

不再索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
#left是一个函数，就是字符串截取的作用

结论：原来的name加上left(name,4)之后，索引就失效了

存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age > 25 and pos = 'manager';

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少SELECT *