索引失效应该避免
案例（索引失效）
# demo 数据DROP TABLE IF EXISTS `staffs`;CREATE TABLE `staffs` (`id` int(11) NOT NULL AUTO_INCREMENT,`NAME` varchar(24) NOT NULL COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`pos` varchar(20) NOT NULL COMMENT '职位',`add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入职时间',PRIMARY KEY (`id`),KEY `idx_staffs_nameAgePost` (`NAME`,`age`,`pos`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';-- ------------------------------ Records of staffs-- ----------------------------INSERT INTO `staffs` VALUES ('1', 'z3', '22', 'manager', '-03-14 15:28:53');INSERT INTO `staffs` VALUES ('2', 'July', '23', 'dev', '-03-14 15:29:31');INSERT INTO `staffs` VALUES ('3', '2000', '23', 'dev', '-03-14 15:29:46');
1.全值匹配我最爱
2.最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
# 案例：不走索引explain select * from staffs where left(name,4) ="July";
4.存储引擎不能使用索引中范围条件右边的列
# 案例:不走索引explain select * from staffs where name = "July" and age > 25 and pos = "manage"
5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，
减少select *
6.mysql在使用不等于(!=或者<> )的时候无法使用索引会导致全表扫描
# 案例 ：不会走索引explain select * from staffs where name != 'July'explain select * from staffs where name <> 'July'
7.is null ,is not null也无法使用索引I
#案例：不会走索引explain select * from staffs where name is null
8.like以通配符开头(’%abc…)mysql索引失效会变成全表扫描的操作
# 案例：不会走索引explain select * from staffs where name like '%July%';# 案例：会走索引explain select * from staffs where name like 'July%';
问题:
解决like '%字符串%'时索引不被使用的方法? ?
使用覆盖索引 >是指建立的复合索引的字段
测试数据
CREATE TABLE `tb1_user` (id INT (11) NOT NULL AUTO_INCREMENT,`NAME` VARCHAR (20) DEFAULT NULL,`age` INT (11) DEFAULT NULL,email VARCHAR (20) DEFAULT NULL,PRIMARY KEY (id)) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;INSERT INTO tb1_user(NAME ,age ,email) VALUES('1aa1',21,'b@');INSERT INTO tb1_user(NAME ,age,email) VALUES('2aa2' ,222,'a@');INSERT INTO tb1_user(NAME ,age,email) VALUES('3aa3' ,265, 'c@');INSERT INTO tb1_user(NAME ,age,email) VALUES('4aa4' ,21,'d@');create index idx_user_nameAge on tb1_user(name,age);# 使用覆盖索引# 案例 ：会走索引explain select name,age from tb1_user where name like "%aa%";# 案例： 不会走索引；因为email不是复合索引中的列；没有覆盖到explain select name,age,email from tb1_user where name like "%aa%";
9.字符串不加单引号索引失效
# 案例 ：不会走索引explain select * from staffs where name = 2000;# 案例 ：会走索引explain select * from staffs where name = '2000';
10.少用or,用它来连接时会索引失效
# 案例：不走索引explain select * from staffs where name='July' OR name = 'z3';
