MySQL查询优化和索引优化学习笔记

参考视频：链接: 哔哩哔哩视频.

1.关联查询优化

1.1外连接

我们先看一下这个语句：

EXPLAIN SELECT * FROM t1 STRAIGHT_JOIN t2 ON (t1.a=t2.a);

使用STRAIGHT_JOIN是使优化器按照我们设定的方式执行查询，t1表为驱动表，t2表为被驱动表。
整个语句的执行流程是这样的：

1. 从表t1中读入一行数据 R；
2. 从数据行R中，取出a字段到表t2里去查找；
3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4. 重复执行步骤1到3，直到表t1的末尾循环结束。

如果被驱动表上有索引，会用上该索引，因此这种查询也叫“Index Nested-Loop Join”，简称INLJ。
引申的问题是：如果两个表都有索引，驱动表和被驱动表应该怎么选择呢？
如果t1表扩大1000倍，要扫描的行数也要扩大1000倍，但是如果t2扩大1000倍，要扫描的行数由于有索引，因此不会超过10倍，因此结论是：
5. 使用join语句，会比强行拆分成多个单表执行sql语句的性能更好。
6. 如果使用join语句的话，需要让小表做驱动表，即小表驱动大表。

那么问题来了，什么叫小表，什么叫大表？
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

1.2 内连接

内连接由优化器自动选择驱动表和被驱动表，其实外连接在底层也是先转化成内连接，然后再优化。如果连接条件中只有ige字段有索引，那么有索引的字段所在的表会作为倍驱动表。

2. 子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。 子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作 。但是，子查询效率不高，原因是：

1. 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再 撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会
   受到一定的影响。
3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

因此，在MySQL中，尽量使用连接查询来代替子查询，连接查询不需要建立临时表 ，其速度比子查询
要快 ，如果查询中使用索引的话，性能会更好。

3. 排序优化

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中避免全表扫描 ，在 ORDER BY 子句避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
2. 尽量使用索引完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列；如果不同就使用联合索引。
3. 无法使用 索引时，需要对 FileSort 方式进行调优。

3.1 FileSort算法

FileSort算法分为双路排序和单路排序。

双路排序

MySQL4.1之前使用的时双路排序，第一次扫描磁盘，然后对他们排序，按照列表中的值重新读取相应的数据输出。取一批数据需要进行两次IO，比较耗时。

单路排序

从磁盘中读取需要查询的所有列，在buffer中进行排序，然后扫描排序后的列表进行输出，效率更快一些，避免二次IO，并且把随机IO变成顺序IO，但是会占用更多空间。
FileSort优化策略：

1. 尝试提高sort_buffer_size，即缓冲池的大小
2. 尝试提高max_length_for_sort_data，超过此长度会使用双路排序，小于此长度使用单路排序，因此这个长度适当大一些，便于使用单路排序。
3. 只query需要的字段，不要select *。

4. 分组优化

group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接
使用索引。当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

5. 分页查询优化

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

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a WHERE t.id = a.id;

优化思路二：用Limit 查询转换成某个位置的查询 ，适用于主键自增的表。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

6. 覆盖索引

一个索引包含了满足查询结果的数据就叫做覆盖索引。
覆盖索引的优点：

1. 避免Innodb表进行索引的二次查询（回表）
2. 可以把随机IO变成顺序IO加快查询效率

弊端：
索引字段的维护总是有代价的，比如占硬盘空间，维护需要耗费cpu。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

7. 索引下推

Index Condition Pushdown (ICP)是MySQL 5.6中新特性，ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。
在非聚簇索引时，首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的indexfilter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层。这样减少了回表的消耗，减少了硬盘IO时间。
使用条件：

1. 只能用于二级索引(secondary index)。
2. 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。
3. ICP可以用于MyISAM和InnnoDB存储引擎。
4. 当SQL使用覆盖索引时，不支持ICP优化方法。

8. 其他优化策略

8.1 EXISTS 和 IN 的区分

选择的标准是小表驱动大表。

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B .cc=A.cc)

当A小于B时，用exists，因为EXISTS的实现相当于外表循环，当B小于A时用IN，因为实现的逻辑类似于内表循环。

8.2 COUNT(*)，COUNT(1)与COUNT(具体字段)效率

1. count(*)和count(1)效率是一样的，都是对所有结果进行COUNT，本质上没有区别。
2. 对于COUNT(具体字段)要用二级索引，因为二级索引包含的信息少于聚簇索引，而COUNT(*)和COUNT(1)默认由优化器采用占用空间更小的二级索引进行统计。

8.3 关于SELECT(*)

在表查询中，要明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。原
因：

1. MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间。
2. 无法使用 覆盖索引。

8.4 LIMIT 1 对优化的影响

如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫了，这样会加快查询速度。如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不需要加上 LIMIT 1 。

8.5 多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。commit释放资源：

1. 回滚段上用于恢复数据的信息
2. 被程序语句获得的锁
3. redo / undo log buffer 中的空间
4. 管理上述 3 种资源中的内部花费