MySQL高级---B站学习总结---索引失效（应该避免）

MySQL高级—B站学习总结—索引失效（应该避免）

首先索引失效是我们应该避免的问题，但是索引失效的前提一定是索引有效，后来才会失效

常见的索引失效的原因

1.全值匹配我最爱

2.最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

案例：

1.建表语句

CREATE TABLE staffs (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR ( 24 ) 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());
INSERT INTO staffs ( NAME, age, pos, add_time )
VALUES
	(
		NULL,
		23,
		'dev',
	NOW());

2.创建一个复合索引idx_staffs_nameAgePos,索引内部顺序是NAME，age，pos

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

通过EXPLAIN查询sql的执行计划

索引第一位置上的name没有了(带头大哥不见了)，所以进行了全表扫描 (type=ALL),没有用上索引

索引第一位置上的name存在(带头大哥出现了)，所以用上了索引

where中的查询条件少了复合索引中的age，但是确实是使用了索引，不过是使用了部分索引，只用到了索引中的name，没用到pos证明了中间兄弟不能断的结论

总结最佳左前缀法则：带头大哥不能死，中间兄弟不能断

 

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

执行sql语句对比

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

总结：

执行下面两个sql语句其实都可以查到结果，但是通过EXPLAIN方式会发现索引上做计算的会令索引失效

SELECT * FROM staffs WHERE NAME = 'July';
SELECT * FROM staffs WHERE left(NAME,4) = 'July';

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

正常使用索引没有问题

当把索引中的age从确定的常量变成范围，那么范围之后的全部失效，所以索引中的pos就失效了，但是索引用到了name和age，因为key_len是78，如上图正常使用索引用到两个索引的key_len也是78

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

覆盖索引： 创建的索引和查询的字段在个数和顺序上保持一致就是覆盖索引

使用select * 和select 覆盖索引(只访问索引的查询(索引列和查询列一致))的效果对比

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

7.is not null 也无法使用索引,但是is null是可以使用索引的

8.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作

问题：解决like '%字符串%'时索引不被使用的方法？？

百分like加右边（sql中的模糊查询要将百分号写到右边），这样可以避免索引失效

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

name是varchar类型，但是加或者不加单引号都可以查询出数据，原因是mysql会在底层进行隐式的类型转换将int类型的2000变成varchar类型的2000，但是这样会引发不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描这个索引失效的条件

执行EXPAIN后发现，被类型转换的sql没有使用到索引，导致索引失效

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

结果是可以正常查询出来的

小总结：

练习

最后一个因为b模糊查询的百分号左边是一个定值，所以可以使用到abc

索引优化口诀总结：