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`);

1.全值匹配

#分析sql
#索引被使用
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july';
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july' AND age = 25;
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july' AND age = 25 AND pos = 'dev';
#总结匹配所有索引，索引不失效

2. 最佳左前缀法则

#继续分析sql 
#索引失效 type ALL 全表扫描 
EXPLAIN SELECT * FROM `staffs` WHERE age = 23 AND pos = 'dev';
EXPLAIN SELECT * FROM `staffs` WHERE pos = 'dev';
#总结无开头索引，也就是这里的name则索引失效————带头大哥不能死

#继续分析sql 
#索引失效 type ALL 全表扫描 
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july' AND pos = 'dev';
#总结 只用到了部分索引就是开头索引，其余索引未实现————中间兄弟不能断
#最左前缀法则，查询从索引最左列开始，并且不跳过索引中间列 ——带头大哥不能死，中间兄弟不能断

3. 不在索引列上做任何操作

#分析sql
#使用left函数，索引失效全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE LEFT(`name`,4) = 'july';
#总结 对索引列进行操作，都会导致索引失效。

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

#分析sql
# age > 11,导致pos索引失效——范围之后全失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july' AND age > 11 AND pos = 'dev';
#总结 （> < like 等操作都是使得右边的所有索引失效）

5. 尽量使用覆盖引擎，减少select *

#分析sql 按需查找最好
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july' AND age = 23 AND pos = 'dev';
EXPLAIN SELECT `name`,`age`,`pos` FROM `staffs` WHERE `name` = 'july' AND age = 23 AND pos = 'dev';
#直接从索引中获取值，而不用访问表
EXPLAIN SELECT `name`,`age`,`pos` FROM `staffs` WHERE `name` = 'july' AND age = 23;
#总结 查询自己需要的字段，尽量不要全部查询

6. mysql在使用不等于(!= <>)的时候无法使用索引会导致全表扫描

#分析sql 使用不等于索引失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'july';

7. is null,is not null也无法使用索引

#分析sql,索引失效，全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` IS NOT NULL;

8. like以通配符开头(’%like%’)mysql索引失效，全表扫描

#分析sql 全表扫描
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%july';
#继续分析sql 索引被使用，没有失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'july%';
#解决 like '%字符串%'时的索引不被使用的方法
#建表
CREATE TABLE tbl_user(
	id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) DEFAULT NULL,
	age INT DEFAULT NULL,
	email VARCHAR(20) DEFAULT NULL
);
#插入数据
INSERT INTO `tbl_user`(`name`,`age`,`email`) VALUES('1aa1',21,'b@163.com');
INSERT INTO `tbl_user`(`name`,`age`,`email`) VALUES('2aa2',222,'a@163.com');
INSERT INTO `tbl_user`(`name`,`age`,`email`) VALUES('3aa3',265,'c@163.com');
INSERT INTO `tbl_user`(`name`,`age`,`email`) VALUES('4aa4',21,'d@163.com');
#分析sql 没建立索引
EXPLAIN SELECT * FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `name`,`age` FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `id` FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `name` FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `age` FROM `tbl_user` WHERE `name` LIKE '%aa%';

#创建索引
CREATE INDEX idx_user_namgAge ON `tbl_user`(`name`,`age`);
#继续分析sql
#type index
EXPLAIN SELECT `name`,`age` FROM `tbl_user` WHERE `name` LIKE '%aa%';
#type index
EXPLAIN SELECT `id` FROM `tbl_user` WHERE `name` LIKE '%aa%';
#type index
EXPLAIN SELECT `name` FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `age` FROM `tbl_user` WHERE `name` LIKE '%aa%';
#type index
EXPLAIN SELECT `id`,`name` FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `id`,`name`,`age` FROM `tbl_user` WHERE `name` LIKE '%aa%';
EXPLAIN SELECT `name`,`age` FROM `tbl_user` WHERE `name` LIKE '%aa%';
#type ALL 索引失效
EXPLAIN SELECT * FROM `tbl_user` WHERE `name` LIKE '%aa%';
#type ALL 索引失效
EXPLAIN SELECT `id`,`name`,`age`,`email` FROM `tbl_user` WHERE `name` LIKE '%aa%';
#总结 如果要查询以%开头的字段，尽量使用覆盖索引，在要查询的字段上建立索引

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

#分析sql 正常sql 使用了索引
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '2000';
#继续分析 type ALL, 发生类型转换，索引失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;

10. 少用or, 用它来连接时索引失效

#分析sql type ALL 索引失效
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'july' OR `name` = 'z3';