Java工程师的简历，在擅长技术点里面，经常会出现 “熟悉MySQL数据库，有丰富的数据库设计和SQL优化经验”，这么一条。
面试过程中，每当我看到这么一条，就会问“说下你日常工作中，都用过哪些手段进行过SQL优化？”
常见的回答是，“一般都是给经常查询的字段，增加索引。”
然后，我继续问，“那还有其他的方式吗？”
到这里，70%的的人回答是，“没有了”。不过，我在最近的面试中，遇到一个候选人，他这样说的，“我会规避多表关联，尽量使用单表查询，这样可以提升程序性能。”
问：“那如果遇到多表关联的业务场景怎么办？”
答：“比如：如果业务场景需要A表关联B表，那我就先把A表的数据，根据查询条件从数据库查出来，再把B表的数据，根据查询条件查出来，然后在程序里面进行merge。”
问：“你觉得这样会比直接在SQL里面用 inner join进行表关联快吗？那你了解数据库的表连接算法是什么吗？”
答：“这我就不知道了。”
表连接算法
MySQL目前用的表连接算法是：Nested-Loops Join（嵌套循环连接），顾名思义，外循环从A张表中每次读取一条记录，然后将记录与B表中的记录进行内循环关联比较，伪代码如下：
for each row in table A matching range {
for each row in table B matching join column{
if row satisfies join conditions，send to client
}
}
这里面把外循环中的表叫做驱动表，内循环的表叫做被驱动表，MySQL查询优化器会倾向于把“满足查询条件后，记录行数少的表”作为驱动表，把“关联列上带索引的表”作为被驱动表，这样可以提升性能。当然，我们也可以自行通过STRAIGHT_JOIN来指定驱动表 ， 用法如下：
SELECT A.C1, B.C2 FROM A STRAIGHT_JOIN B ON A.C3 = B.C4
官方解释为：STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order
在MySQL的实现中，嵌套循环连接算法分为三种，即：
- Simple Nested-Loop Join：SNLJ，简单嵌套循环连接
- Index Nested-Loop Join：INLJ，索引嵌套循环连接
- Block Nested-Loop Join：BNLJ，缓存块嵌套循环连接
Simple Nested-Loops Join
嵌套循环连接最简单的实现，或者说，是最简单粗暴的实现，直接用驱动表A中符合条件的数据，一条一条地带入到被驱动表B中，进行全表扫描匹配。如果驱动表A中符合条件的数据有一万条，那么就需要带入到被驱动表B中进行一万次全表扫描，这种查询效率会非常慢。因此，除非特殊场景，否则查询优化器不太会选择这种连接算法。
Index Nested-Loops Join
当被驱动表B的关联列上有索引的时候，查询优化器极大概率会选择这种这种连接算法，即驱动表A符合条件的数据，一条一条地带入到被驱动表B中，若B表的关联列是主键索引，则可以直接在表中查到记录；若B表的关联列是辅助索引，则通过索引扫描的方式查到记录的主键，然后再回表查到记录。
Block Nested-Loops Join**
这种算法就是针对于，在被驱动表B的没有索引的连接情况设计的。当驱动表A再进行循环匹配的时候，数据并不会直接带入到被驱动表B，而是使用Join Buffer（连接缓存）先缓存起来，等到Join Buffer满了再去一次性关联被驱动表B，这样可以减少被驱动表B被全表扫描的次数，提升查询性能。伪代码如下：
for each row in table A matching range {
store join column in join buffer
if（join buffer is full）{
for each row in table B matching join column{
if row satisfies join conditions，send to client
}
}
}
你以为关于多表关联，这就完了？其实并不是。MySQL在5.6版本，终于推出了它的王炸新特性。
Multi-Range Read
多范围读（Multi-Range Read），在5.6版本后加入的新特性
SELECT * FROM salaries WHERE salary>10000 and salary<40000;
没有MRR之前
- 将查询得到的辅助索引键值存放在一个缓存中，这时缓存中的数据是根据辅助索引键值排序的
- 根据辅助索引键值查出来对应的主键，然后再用这些主键回表，来访问数据文件
MRR：
- 将查询得到的辅助索引键值存放在一个缓存中，这时缓存中的数据是根据辅助索引键值排序的
- 根据辅助索引键值查出来对应的主键，通过主键进行排序，按照主键的顺序回表，来访问实际的数据文件
因此，MRR的作用是，将磁盘的随机I/O转化为顺序I/O，并可减少磁盘的I/O次数（1个page里面可能包含多个命中的record），提高查询效率。执行计划为，explain：Using MRR
Batched Key Access Joins
Batched Key Access Joins，MySql 5.6开始支持，结合了MRR和join buffer，以此来提高联接的执行效率，其发生的条件为被驱动表B有索引，并且该索引为非主键，如果被Join的表上没有索引，则使用老版本的BNL策略(BLOCK Nested-loop)。伪代码如下：
for each row in table A matching range {
store join column in join buffer
if（join buffer is full）{
send to MRR interface，and order by its primary key
for each row in table B matching join column{
if row satisfies join conditions，send to client
}
}
}
问：“你觉得这样会比直接在SQL里面用 inner join进行表关联快吗？”
基于这个问题，我的答案是，基于MySQL查询优化器进行的这么多优化策略，那么，合理地进行表关联，一定是比把各表数据读出来，然后自己在程序里面进行merge快的。
而且，直接拿到应用程序里面来，缺少了DB层面的过滤，如果各表读取出来的数据量过大，是有可能会把网卡和应用程序的内存打满的。
但是，有一点优势，如果在应用程序层面进行join，确实减少了数据库层面的计算压力，会降低数据库的load。
在后续章节，我会着重讲下，在多表关联，且存在where、order by和limit的情况下，如何进行一些合理的SQL优化
