避免索引失效
全值匹配
对索引中所有列都指定具体值。
最佳左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。
索引上做操作
不要在索引列上做任何操作，如计算、函数、(自动/手动)类型转换等，这些操作都会导致索引失效而转向全表扫描。
字符串不加单引号导致 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;
