6-Mysql-索引匹配和失效及相关优化

本文知识点来源于网络，仅限学习笔记使用，记录学习过程与心得，如有不适，请联系。

本文涉及explain多个属性值，如果对其不明确，请先参考
Explain简介

一、索引全值匹配（best）

#创建复合索引

#创建复合索引
CREATE INDEX idx_depId_name_age ON employee(dep_id,name,age);
#使用复合索引中全部的字段
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name = '张三' AND age=10;

查看执行计划，复合索引中的字段都用上了，这是最好的情况：

二、最佳左前缀法则

使用复合索引，要遵从最左前缀法则，即从索引的左列开始，并且不跳过（不使用）索引中的列。
接上边的例子，下边展示不同情况索引失效：

1. 不使用第一个，索引失效

#不使用第一个，索引失效
EXPLAIN SELECT * FROM employee WHERE name = '张三' AND age=10;

索引失效，没有使用索引

2. 不使用前两个，索引失效

#不使用前两个，索引失效
EXPLAIN SELECT * FROM employee WHERE age=10;

3. 不使用中间的，只有第一个索引生效

#不使用中间的，只有第一个索引生效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND age=10;

4. 不使用最后一个，前两个索引生效

#不使用最后一个，前两个索引生效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name = '张三'

5. 不使用最后两个，第一个生效

#不使用最后两个，第一个生效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1;

6. 如果创建索引的三个列都使用，就是索引全值匹配，这三个列的顺序可以颠倒，但一定要有
   例如：现在创建索引时顺序为dep_id,name,age

#与顺序无关
EXPLAIN SELECT * FROM employee WHERE age=10  AND dep_id = 1 AND name = '张三';

三、函数计算引起索引失效

不要在索引字段上加上计算，函数，类型转换，会导致索引失效转向全表扫描。
例1：在第二个索引列上加上trim()函数，导致第二个，第三个索引失效，只有第一个索引生效

#函数计算引起索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND trim(name) = '张三' AND age=10;

例2：在第三个索引列age做计算操作，导致只有前两个索引生效

#函数计算引起索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name = '张三' AND age/2 =10;

四、范围条件右边的索引失效（范围条件列索引不会失效）

与查询顺序无关，与创建顺序有关
由上边举例我们可以总结道按索引创建顺序 dep_id,name,age，当我们三个索引都用到的情况下key_len 为73

#与查询顺序无关，与创建顺序有关
EXPLAIN SELECT * FROM employee WHERE dep_id = 5 AND age>10 AND name = '张三';

五 、使用不等于（！=或<>）索引失效

#使用不等于（！=或<>）索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id != 1;

#使用不等于（！=或<>）索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id <> 1;

六、使用is not null 索引失效

#使用is not null 索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id is not NULL;

EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name is not null AND age=10;

注意：is null 看结果

EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name is null AND age=10;

七、用or连接时索引失效

EXPLAIN SELECT * FROM employee WHERE dep_id = 1 or name = '张三';

八、like以通配符（%）开头索引失效

例1：like模糊查询以%开头，造成只有dep_id索引生效，name没有生效

#like以通配符（%）开头索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name LIKE '%三';

例2：like模糊查询不以%开头，两个索引都生效

EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name LIKE '三%';

九、字符串不加单引号造成索引失效

首先加单引号：

EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name = '200';

对比不加单引号

#字符串不加单引号造成索引失效
EXPLAIN SELECT * FROM employee WHERE dep_id = 1 AND name = 200;

十、尽量使用覆盖索引少用select *

即查询的字段和建立索引的字段刚好吻合，称为覆盖索引。
如上述第五条：使用不等于（！=或<>）索引失效，我们不用select * 用select dep_id

EXPLAIN SELECT dep_id FROM employee WHERE dep_id <> 1;

使用了索引

注意这样写只是尽可能的优化，并不是只要使用了覆盖索引就一定能保证使用全部的索引字段
如下，字符串没有加单引号，虽然使用了索引，单只使用了一个索引字段dep_id,和select * 效果是一样的：

EXPLAIN SELECT dep_id,name,age FROM employee WHERE dep_id = 1 AND name = 200 and age = 10;

十一、order by与group by相关

11.1在使用order by时，经常出现Using filesort（使用文件排序，即无法利用索引完成排序操作）。以下有几种情况，我们一一介绍：、

1. order by后边的字段没有按照创建索引的顺序排序时，会出现Using filesort，例我们创建索引的顺序：

#先演示正确情况
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY dep_id, name, age;

#order by后边的字段没有按照创建索引的顺序排序时，会出现Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY name,dep_id, age;

2. 依然是最左匹配，但是单反少左边任何一个都会导致Using filesort
   如：

#不会Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY dep_id, name, age;
#不会Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY dep_id, name;
#会Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY name, age;
#会Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY dep_id,age;

3. 当使用*时会，所以尽量使用覆盖索引

#不会Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee ORDER BY dep_id, name, age;
#会Using filesort
EXPLAIN SELECT * FROM employee ORDER BY dep_id, name, age;

4. 当索引字段是常量时不会出现Using filesort

#为常量
EXPLAIN SELECT dep_id,name,age FROM employee WHERE dep_id = 1 ORDER BY name, age;

作为比较:

#不为常量
EXPLAIN SELECT dep_id,name,age FROM employee WHERE dep_id > 1 ORDER BY name, age;

5. 同时使用一升序一降序，会出现Using filesort

#同时使用一升序一降序，会出现Using filesort
EXPLAIN SELECT dep_id,name,age FROM employee WHERE dep_id > 1 ORDER BY name DESC , age ASC;

11.2 在使用group by时，使用不当，也会出现Using temporary（使用临时表）的情况。

情况同11.1，因为分组前必排序

十二、大批量数据分页操作如何优化

测试前我删除了employee 表复合索引

#格式
SELECT * FROM employee LIMIT [offset,]rows;
#例子
SELECT * FROM employee LIMIT 100000,5;

随着offset增大，查询的速度会越来越慢，原因是mysql做分页的时候会把offset前边的数据都取出来，找到对应位置。

根据执行过程有如下优化思路：

1. 让其最费时的"找到对应位置"这段走索引
   分析原分页语句执行过程，发现其并没有使用索引
   

我们让其使用索引，改造，并分析执行过程：

EXPLAIN SELECT id FROM employee LIMIT 100000,5;

通过内连接拿到我们的目标数据

SELECT * FROM employee e INNER JOIN (SELECT t.id FROM employee t LIMIT 100000,5) temp ON e.id = temp.id;

2.原理同1，但sql不同

SELECT * FROM employee e WHERE e.id >= (SELECT t.id FROM employee t LIMIT 100000,1) LIMIT 5;

3. 直接上sql

#原始
SELECT * FROM employee LIMIT 100000,5;
#改造
SELECT * FROM employee WHERE id > 100000 LIMIT 5;

思路如上，但是你要保证id是自增的，还有先排序，在分页，否则limit没有意义，可能会出现你上下两条sql查询的记录不一样

十三、最大值max优化

例：查询employee表age的最大值（测试前我删除该表复合索引）

#我们传统写法
EXPLAIN SELECT MAX(age) FROM employee;

方法，加索引就是淦

#给age添加索引
CREATE INDEX idx_age ON employee(age);
#查询执行计划
EXPLAIN SELECT MAX(age) FROM employee;

由执行计划看出：Select tables optimized away
SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引，通过优化器统计信息中直接返回数据了）。

十四、 使用count统计信息

准备数据

SELECT * FROM department;

一共6条数据：

1. count(*)统计所有，包含null

SELECT COUNT(*) FROM department;

2. count(字段)统计所有，不包含null

SELECT COUNT(deptName) FROM department;

3. 扩展

SELECT COUNT(*) FROM department;
#等价于
SELECT COUNT(deptName OR deptName IS NULL) FROM department;

十五、in 或 exist的抉择

#小表驱动大表，先执行子查询
SELECT * FROM employee e WHERE e.dep_id IN(SELECT id FROM department);

#大表驱动小表，先执行主查询
SELECT * FROM employee e WHERE EXISTS(SELECT 2 FROM department d WHERE e.dep_id = d.id);

相当于双层for循环，我们抽象来看：

for(外层循环相当于数据连接数，费资源，当然越小越好){
	for(内循环表示每次数据库连接做多少次==判断) {
		if( == 判断)
	}
}

总结A表（employee ），B表（department ）：

当A表数据多余B表数据时，用IN优于EXISTS；
当A表数据少于B表数据时，用EXISTS优于IN；
数据量差不多是，差别不大。