mysql学习笔记-索引优化【1】-索引失效原因

索引失效应该避免

案例（索引失效）

# 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';