10-索引优化分析(2)

避免索引失效

全值匹配

对索引中所有列都指定具体值。

最佳左前缀法则

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

如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。

索引上做操作

不要在索引列上做任何操作，如计算、函数、(自动/手动)类型转换等，这些操作都会导致索引失效而转向全表扫描。

字符串不加单引号导致 MySQL 查询优化器会自动进行类型转换，从而造成索引失效。

索引中范围条件

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

索引中否定条件

MySQL 在使用不等于 !=、<> 的时候无法使用索引会导致全表扫描；IN 走索引，NOT IN 索引失效。

LIKE、通配符

LIKE 以通配符开头(如 '%abc...') 索引失效，会变成全表扫描的操作。但如果仅仅是尾部模糊匹配，索引不会失效；只有在头部模糊匹配，索引才失效。

OR、UNION

用 OR 分割开的条件，如果 OR 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。所以，对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

示例：name 字段是索引列，而 createtime 不是索引列，中间是 OR 进行连接是不走索引的。

建议使用 UNION 替换 OR

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge
> unique_subquery > index_subquery > range > index > ALL

尽量使用覆盖索引

什么是覆盖索引？

【解释一】就是 SELECT 的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
【解释二】索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。
【解释三】是非聚集组合索引的一种形式，它包括在查询里的 SELECT、JOIN 和 WHERE 子句用到的所有列（即建立索引的字段正好是覆盖查询语句 [SELECT 子句] 与查询条件 [WHERE 子句] 中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以 MySQL 只能使用 B-Tree 索引做覆盖索引当发起一个被索引覆盖的查询 (也叫作“索引覆盖查询”) 时，在 EXPLAIN 的 Extra 列可以看到“Using index”的信息。

Extra 相关：

using index 使用覆盖索引的时候就会出现
using where 在查找使用索引的情况下，需要回表去查询所需的数据
using index condition 查找使用了索引，但是需要回表查询数据
using index; using where 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

全局扫描更快(&NULL)

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

IS NULL，IS NOT NULL 有时索引失效。

表中对应要筛选的非空列数据，如果 NULL 多，则 IS NULL 失效；如果 NULL 少，则 IS NOT NULL 失效。道理同上："认为全局扫描更快"。

一般性建议

尽量使用复合索引，而少使用单列索引。创建复合索引，就相当于创建了多个索引；创建单列索引，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。
对于单键索引，尽量选择针对当前 Query 过滤性更好的索引。
在选择组合索引的时候，当前 Query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择可以能够包含当前 Query 中的 WHERE 字句中更多字段的索引。
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
书写 SQL 语句时，尽量避免造成索引失效的情况。

Test：index(a, b, c)

插入优化

大批量插入数据

当使用 load 命令导入数据的时候，适当的设置可以提高导入的效率。

1. 主键顺序插入

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

2. 关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

3. 手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

优化 INSERT 语句

当进行数据的 insert 操作的时候，可以考虑采用以下几种优化方案。原始方式如下：

insert into tb_test values(1, 'Tom');
insert into tb_test values(2, 'Cat');
insert into tb_test values(3, 'Jerry');

(1) 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的 insert 语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个 insert 语句快。

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

(2) 在事务中进行数据插入

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

(3) 数据有序插入

关联查询优化

[驱动表] 免不了全表扫描，重点是要给 [被驱动表] 的关联字段建立索引。

建议：

保证被驱动表的 JOIN 字段已经被索引
LEFT JOIN 时，选择数据量较少的一张表作为驱动表，数据量较大的那张作为被驱动表。
INNER JOIN 时，MySQL 会自己帮你把小结果集的表选为驱动表。
子查询尽量不要放在被驱动表，有可能使用不到索引。
能够直接多表关联的尽量直接关联，不用子查询。换言之，子查询的优化方式就是将它用连接(JOIN) 替代。

ORDER BY 优化

两种排序方式

第 1 种是通过对返回数据进行排序，也就是通常说的 filesort 排序，所有不是通过索引直接返回排序结果的排序都叫 using filesort。

第 2 种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

【小结】了解了 MySQL 的排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据。Where 条件和 Order by 使用相同的索引，并且 Order By 的顺序和索引顺序相同，以及 Order by 的字段要么都升序，或者都是降序，否则肯定需要额外的操作，这样就会出现 FileSort。

三个原则

Tip：key_len 说的是 WHERE 后面的筛选条件命中索引的长度，不包括 ORDER BY！

无过滤，不索引 // 没有过滤条件，索引用不上（实在不行，加 limit）。

顺序错，必排序 // ORDER BY 后面的顺序（会破坏 SQL 原意），优化器改不了。

方向反，必排序 // 要升都升，要降都降；又升又降，索引用不了。

索引的选择

就默认使用 MySQL 做出的对索引的选择。

filesort

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让 Filesort 消失，那就需要加快 Filesort 的排序操作。对于 Filesort，MySQL 有两种排序算法：

双路排序

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。

先读取行指针和 orderby 排序列，然后在排序区 sort buffer 中排序，如果 sort buffer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机 I/O 操作。

取一批数据，要对磁盘进行了两次扫描，众所周知，I/O是很耗时的，所以在 MySQL 4.1 之后，出现了第二种改进的算法，就是单路排序。

单路排序

从磁盘读取查询需要的所有列，按照 order by 列在排序区 sort buffer 中对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机 IO 变成了顺序 IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了，但是排序效率比两次扫描算法要高。

但是用单路有些问题：在 sort_buffer 中，方法 B 比方法 A 要多占用很多空间，但由于方法 B 是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取 sort_buffer 容量大小，再排 …… 从而多次I/O。本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化策略

Order by 时 SELECT * 是一个大忌，切记只 Query 需要的字段，这点非常重要。在这里的影响是：

当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT/BLOB 类型时，会用改进后的算法 —— 单路排序，否则用老算法 —— 多路排序。
两种算法的数据都有可能超出 sort_buffer 的容量，超出之后会创建 tmp 文件进行合并排序，从而导致多次 I/O，只是用单路排序算法的风险会更大一些，所以要提高 sort_buffer_size。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

尝试提高 sort_buffer_size，不管用哪种算法，提高这个参数都会提高效率。当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。通常在 1M ~ 8M 之间调整。
尝试提高 max_length_for_sort_data，提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出 sort_buffer_size 的概率就增大，明显症状是高的磁盘 I/O 活动和低的处理器使用率。通常在 1024 ~ 8192 之间调整。

【小结】

增大 sort_buffer_size 参数的设置
增大 max_length_for_sort_data 参数的设置
减少 SELECT 后面的查询的字段

GROUP BY 优化

由于 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。

GROUP BY 使用索引的原则几乎跟 ORDER BY 一致，唯一区别是 GROUP BY 即使没有过滤条件用到索引，也可以直接使用索引。

如果查询包含 GROUP BY 但是用户想要避免排序结果的消耗，则可以添加 ORDER BY NULL 禁止排序。如下：

从上面的例子可以看出，第一个 SQL 语句需要进行 "filesort"，而第二个 SQL 由于 order by null 不需要进行 "filesort"，而上文提过 Filesort 往往非常耗费时间。

分页查询优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10，此时需要 MySQL 排序前 2000010 记录，仅仅返回 2000000 ~ 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

【优化思路一】在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

【优化思路二】该方案适用于主键自增(还不能有断层) 的表，可以把 Limit 查询转换成某个位置的查询。

补充知识

查看索引的使用情况

show status like 'Handler_read%';
show global status like 'Handler_read%';

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY ... DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

使用 SQL 提示

SQL 提示，是优化数据库的一个重要手段，简单来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

IGNORE INDEX

如果用户只是单纯的想让 MySQL 忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint。

优化练习

1. 列出自己的掌门比自己年龄小的人员

EXPLAIN SELECT SQL_NO_CACHE e.id, e.name
FROM emp e LEFT JOIN dept d
ON e.deptId = d.id
WHERE e.age > (SELECT age FROM emp WHERE id = d.ceo);

EXPLAIN SELECT SQL_NO_CACHE a.id, a.name
FROM emp a
LEFT JOIN dept b ON a.deptId = b.id
LEFT JOIN emp c ON b.ceo = c.id
WHERE a.age > c.age;

2. 列出所有年龄低于自己门派平均年龄的人员

EXPLAIN SELECT SQL_NO_CACHE id, name FROM emp e
WHERE age < (SELECT AVG(age) FROM emp WHERE deptId = e.deptId);

EXPLAIN SELECT SQL_NO_CACHE id, name FROM emp a INNER JOIN
(SELECT deptId, AVG(age) avg FROM emp WHERE deptId IS NOT NULL GROUP BY deptId) b
ON a.deptId = b.deptId
WHERE a.age < b.avg;

3. 列出至少有 2 个年龄大于 40 岁的成员的门派

EXPLAIN SELECT SQL_NO_CACHE a.id FROM dept a
INNER JOIN emp b ON a.id = b.deptId
WHERE b.age > 40
GROUP BY a.id
HAVING COUNT(*) >= 2;

STRAIGHT_JOIN：直连；左表驱动，右表被驱动。因为是自己指定的驱动关系，所以用的时候要明确两表的数量级。

EXPLAIN SELECT a.id FROM dept a
STRAIGHT_JOIN emp b ON a.id = b.deptId
WHERE b.age > 40
GROUP BY a.id
HAVING COUNT(*) >= 2;

4. 至少有 2 位非掌门人成员的门派

EXPLAIN SELECT SQL_NO_CACHE c.deptname, c.id, COUNT(*)
FROM dept c STRAIGHT_JOIN emp a ON a.deptId = c.id
LEFT JOIN dept b ON a.id = b.ceo
WHERE b.id IS NULL
GROUP BY c.id
HAVING COUNT(*) >= 2;