SQL 不知道咋优化？吹一手 join 语句的优化准没错

面试最怕遇到的问题是什么，如何做优化一定当仁不让，SQL 优化更是首当其冲，这里先跟大家分享一个比较容易理解的 join 语句的优化~

前文提到过，当能够用上被驱动表的索引的时候，使用的是 Index Nested-Loop Join 算法，这时性能还是很好的；但是，用不上被驱动表的索引的时候，使用的 Block Nested-Loop Join 算法性能就差多了，非常消耗资源。

针对 join 语句的这两种情况，其实都还是存在继续优化的空间的

老规矩，背诵版在文末。

Multi-Range Read 优化

我们先来回顾一下 “回表” 这个概念。回表是指，InnoDB 在普通索引上查到主键 id 的值后，再根据主键 id 的值到主键索引树上去查询整行记录的过程。

那么，思考一个问题，回表的过程是一行行地查数据，还是批量地查数据？

显然是一行行地。

因为回表查询的本质就是查询 B+ 树，在这棵树上，每次只能根据一个主键 id 查到一行数据。

看下面这条语句，从 user 表中获取 80 岁以上用户的信息：

select * from user where age >= 80;

假设，age 对应的 id 是连续自增的，这样，我们对于主键索引树的查询，就是连续的：

 

当然，这是理想情况，如果 age 对应的 id 值不是顺序的话，那当我们顺序取 age 的时候，id 的获取就是乱序随机的了，性能就会比较差。解释下为什么这里乱序查询的性能就比较差：

首先，我们都知道，索引文件其实就是一个磁盘文件，尽管有内存中 Buffer Pool 的存在可以减少访问磁盘的次数，但是并不能完全避开对磁盘的访问。而对于磁盘来说，一个磁盘从内到外有许多磁道，一个磁道又被划分成多个相同的扇区，随机读取性能较差的原因就是每次都需要花费时间去寻找磁道，找到磁道之后又要去寻找合适的扇区，从而耗费大量时间。所以顺序读取比随机读取快很多。