索引失效应该避免
案例(索引失效)
# 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', '2020-03-14 15:28:53');
INSERT INTO `staffs` VALUES ('2', 'July', '23', 'dev', '2020-03-14 15:29:31');
INSERT INTO `staffs` VALUES ('3', '2000', '23', 'dev', '2020-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@163.com');
INSERT INTO tb1_user(NAME ,age,email) VALUES('2aa2' ,222,'a@163.com');
INSERT INTO tb1_user(NAME ,age,email) VALUES('3aa3' ,265, 'c@163.com');
INSERT INTO tb1_user(NAME ,age,email) VALUES('4aa4' ,21,'d@163.com');
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';