MySQL --- 读书笔记 --- 查询优化

关联查询优化

1. 左外连接

在底层做左外连接查询时，与嵌套循环相似，在驱动表中拿出一条数据，然后去被驱动表中扫描一遍，那么一共就会扫描N*M次

那么在优化时，可以在连接条件中，为被驱动表添加对应字段索引，这样驱动表依然是全表扫描，但是被驱动表是索引查询，效率会提升很多

然后如果驱动表也有连接条件的索引，那么两表都会使用索引，效率更加高

2. 内连接

在两表都有索引或者其中一个有索引的时候，优化器可能会调换驱动表和被驱动表的位置，因为在内连接中，两表的地位是一样的。

所以，在内连接中，有索引（连接条件）的表，会作为被驱动表；数据量大的表作为被驱动表，即是“小表驱动大表”

3. JOIN语句的原理

join方式连接多个表，本质就是各个表之间数据的循环匹配，MySQL通过引入BNLJ算法来优化嵌套循环

• 简单嵌套循环连接，内层表会多A*B次扫描比较
  

• 索引嵌套循环，减少内层表的扫描次数，直接索引等值匹配，大大减少比较扫描次数，但是要求被驱动表必须有索引，而且最好是主键，这样还可以减少回表；而且索引查询的成本是固定的，所以优化器倾向于使用记录数少的作为驱动表
  

• 块嵌套循环连接（Block Nested-Loop Join），当没有索引时，被驱动表的扫描次数太多，关键是每次从驱动表中拿出一条数据，然后将被驱动表数据加载到内存，匹配，然后清除内存，然后重新再来，这样的I/O次数太多。所以出现了不再以逐条获取驱动表记录，而是一块一块，引入了join buffer缓冲区，将驱动表join相关的数据列加载到buffer中，然后全表扫描被驱动表，然后一次性与buffer中的所有记录匹配，将多次匹配合并为一次，降低被驱动表的访问频次
  

被加载的不仅仅是关联的列，包括select的列，所以查询时减少不必要的列，可以让buffer中一次加载更多的记录

• hash join（8.0.20开始，废弃BNLJ），这是使用大数据集连接时的常用方式， 优化器使用两表中较小的表利用Join Key在内存中建立散列表，然后扫描较大的表并探测散列表，找出与Hash表匹配的行
  • 这种方式适用于较小的表完全可以放于内存的情况，这样总成本就是两表之和
  • 在表很大的情况下，优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高I/O的性能
  • 它能够很好的工作于没有索引的大表和并行查询的情况中，并提供最好的性能， 它只能用于等值连接

JOIN小结

• 保证被驱动表的关联字段有索引
• 需要JOIN的字段必须保证数据类型一致
• 外连接时选择小表驱动大表，减少外层循环次数
• 内连接时，优化器会自动选择小表驱动
• 能够连接查询的尽量做连接，不用子查询
• 不建议子查询，建议将子查询拆分结合程序多次查询，或者用JOIN代替

子查询优化

优点是可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作

缺点是效率不高

1. 执行子查询，那么需要为内层查询语句的查询结果建立一个临时表，然后外层查询从临时表中查询记录，结束后，再撤销临时表，这样会消耗过多的CPU和I/O
2. 子查询的结果集存储的临时表，不论是内存临时表或者磁盘临时表都不会存在索引，所以查询性能会受到影响

排序优化

MySQL支持两种排序：FileSort和Index

• Index排序，索引可以保证数据的有序性，不需要再进行排序，效率高
• FileSort中，一般在内存中进行，占用CPU高，如果待排序结果集较大，会产生临时I/O到磁盘中，效率较低

在不同场景下

1. order by时，不limit，索引失效，因为当排序字段是二级索引时，不对结果集大小进行限制，那么当查询后的结果，需要回表操作，这时优化器可能选择直接走聚簇索引，内存排序
2. order by时顺序错误，索引失效，主要是联合索引
3. order by时方向相反，索引失效（指多字段排序时，字段的方向不一致）
4. order by时，无过滤，不索引

当范围条件和order by或者group by的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的字段并不多时，优先把索引放在范围字段上

优化分页查询

当分页数据量非常大的时候，比如limit 200000，10，系统需要排序200010条记录，然后仅仅返回200000-200010条记录

优化思路

1. 在索引上完成排序分页，最后根据主键关联回原表查询所需要的字段
2. 自增主键下，直接范围条件过滤前面部分数据，再限制记录数

优先考虑覆盖索引

概念：当一个索引包含了满足查询结果所需要的列，就叫覆盖索引，一般指二级索引

优点

1. 避免回表，减少I/O
2. 不需要回表的前提下，所需要的数据都在一个页或者一个区内，那么出现随机I/O的情况就减少或者没有了

索引条件下推

概念：是一种在存储引擎层使用索引过滤数据的优化方式

如果部分WHERE条件可以仅使用索引中的列进行筛选，则MySQL服务器会把这部分WHERE条件放到存储引擎筛选，然后，存储引擎通过使用索引条目来筛选数据，并且只有满足条件时才从表中读取数据

比如

k1 二级索引
SELECT * FROM s1 WHERE k1 > 'z' AND k1 LIKE '%a';

当只有一个k1索引的时候，会使用k1做范围搜索，然后将这些过滤后的结果，回表继续扫描比较；但是这样经过第一次索引的条件过滤后的数据量比较多时，回表操作就会比较耗时，那么可以将过滤条件下推，在二级索引中，先经过第一个条件过滤一次数据，再经过第二次条件过滤，最后剩下的数据去回表，这时候的回表次数就会大大降低

其他优化

EXIST和IN

1. A IN B，B表是小的
2. A EXIST B，A表是小的

多使用COMMIT

因为在COMMIT的时候，会相应地释放资源

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