Mysql——避免索引失效

索引相关基础基础知识：Mysql——SQL性能优化基础知识

测试表创建：

CREATE TABLE IF NOT EXISTS staff (
	id INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '自增主键',
	name VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
	age INT(4) NOT NULL DEFAULT 0 COMMENT '年龄',
	position VARCHAR(32) NOT NULL DEFAULT '' COMMENT '职位',
	entry_time DATETIME NOT NULL DEFAULT current_timestamp() COMMENT '入职时间'
) COMMENT '员工表';

INSERT INTO staff (name, age, position, entry_time) 
values
('Leo', 25, 'Java开发', current_timestamp()),
('James', 36, '测试', current_timestamp()),
('Jones', 22, '运维', current_timestamp());

ALTER TABLE staff ADD INDEX idx_staff_nameAgePosition(name, age, position);
-- OR
CREATE INDEX idx_staff_nameAgePosition ON staff(name, age, position);

常见索引失效的原因

违背左前缀法则

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';

EXPLAIN SELECT * FROM staff WHERE age = 25 AND `position` = 'Java开发';

EXPLAIN SELECT * FROM staff WHERE `position` = 'Java开发';

观察上面的三条执行计划可以得出：

如果索引有多个字段参与，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不能跳过索引列。

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND `position` = 'Java开发';

如果按照索引列的顺序中间的部分断开了，索引失效了，同样是会影响到性能的，但是没有过于严重。

总结下来就是：带头大哥不能死，中间兄弟不能断。

不要在索引列上做任何的操作（计算、函数、自动或者手动的类型转换）

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo';

EXPLAIN SELECT * FROM staff WHERE LEFT(`name`, 4) = 'Leo';

由于索引列上存在了函数运算，导致了索引直接失效，从ref级别的const比较，直接变成了ALL全表扫描。索引列上能不要计算就不计算。

搜索引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age > 25 AND `position` = 'Java开发';

虽然第二条语句使用了索引，但是性能也是收到极大的影响：

由于name通过索引进行了ref级别的const检索，但是由于第二个条件由于使用了范围比较，导致Mysql无法再使用索引进行检索，position就无法使用索引了。

between、<、>、in等范围性的过滤条件打断了索引列。

尽量使用覆盖索引，杜绝select *的使用

什么是覆盖索引？

查询column1，column2，正好存在一个复合索引是使用column1和column2创建的，且查询顺序和复合索引的字段顺序一致，则查询就可以直接通过该复合索引检索结果，就不需要去表中进行数据扫描了。

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo' AND age = 25;

EXPLAIN SELECT `name` FROM staff WHERE `name` = 'Leo' AND age = 25 AND `position` = 'Java开发';

上述例子中，使用select *的语句，Extra实际应该是Using where，但是不知道由于什么原因没有显示（暂不做深究），当使用了覆盖索引查询后，Extra为Using index，避免了访问表数据，效率不错。

使用不等于（!=或者<>）时候将索引失效，导致全表扫描

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo';

EXPLAIN SELECT * FROM staff WHERE `name` != 'Leo';

EXPLAIN SELECT * FROM staff WHERE `name` <> 'Leo';

由于使用了不等于，导致了索引失效。

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` != 'Leo';

上述不等于语句，使用了覆盖索引之后，没有出现全表扫描，检索类型变为了index，还能接受。

看了这个例子后，也不要害怕，并不是说导致索引失效了就什么都不用了，该这么写还是这么写，没有那么绝对的。优化SQL并不是完全杜绝没有所谓了不良SQL，而是尽可能的在满足业务的情况下，复合当下情况的最优SQL。

IS NULL、IS NOT NULL导致索引失效

EXPLAIN SELECT * FROM staff WHERE `name` IS NULL;

EXPLAIN SELECT * FROM staff WHERE `name` IS NOT NULL;

以通配符开头的like模糊搜索会导致索引失效

EXPLAIN SELECT * FROM staff WHERE `name` LIKE 'Leo%';

EXPLAIN SELECT * FROM staff WHERE `name` LIKE '%Leo%';

like模糊查询只要不是%开头，其实还是可以接受的。

如下是代替like的模糊查询功能的函数执行计划，看一下，心里有个底就好了：

EXPLAIN SELECT * FROM staff WHERE POSITION('Leo' IN `name`) > 0 ;

EXPLAIN SELECT * FROM staff WHERE LOCATE('Leo', `name`) > 0 ;

POSITION实际上是LOCATE的别名。

EXPLAIN SELECT * FROM staff WHERE INSTR(`name`,'Leo') > 0 ;

实际上这些可以代替like的函数（POSTION、LOCATE、INSTR），都是由于索引字段参与了函数的运算，导致索引失效。

如果必须需要使用前后%的模糊索引呢？应该怎么优化？

• 使用复合索引的方式：

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` LIKE '%Leo%';

显然index要比ALL稍微好一些的。

只要select 列表属于复合索引，不管是否全量匹配复合索引的列，还是单个列属于复合索引，都可以防止like '%%'的索引失效，切记不能在select 列表出现复合索引列之外的其他列（主键除外）。

• 使用主键

EXPLAIN SELECT id FROM staff WHERE `name` LIKE '%Leo%';

字符串不加单引号导致索引失效

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE name = '2000';

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 2000;

很显然，不加引号使Mysql隐性的发生了类型转换，ref直接下降到了index。如果select 列表是*，直接就变为了ALL全表扫描。

尽量少用OR（也可能会导致索引失效）

EXPLAIN SELECT * FROM staff WHERE `name` = 'Leo' OR `name` = 'James';

EXPLAIN SELECT `name`, age, `position` FROM staff WHERE `name` = 'Leo' OR `name` = 'James';