MySQL 查询优化 之 【小表驱动大表，小数据集驱动大数据集】

Mysql 在进行关联表查询时，通常先对一张表A进行查询，然后将循环使用查询出来的每一条记录的关联信息，再来查询另一张表B中的记录，这样查询次数为1 + COUNT(从表A查询的记录)。所以，为了提高查询效率，需要尽量将小表作为首先查询的表，我们可以关联查询时首次使用的表A为主表，将其它表如表B成为嵌套表。(EXPLAIN时，第一个出现的一定是小表即驱动表，后边出现的是大表即被驱动表)

一、IN和EXISTS查询

根据MySQL高级知识（十）——批量插入数据脚本中的相应步骤在 tb_dept_bigdata 表中插入100条数据，在 tb_emp_bigdata 表中插入5000条数据。

注：100个部门，5000个员工。tb_dept_bigdata（小表），tb_emp_bigdata（大表）。

①当 B 表的数据集小于 A 表数据集时，即A大B小时，用 in 优于 exists。

select * from tb_emp_bigdata A where A.deptno in (select B.deptno from tb_dept_bigdata B)

B表为tb_dept_bigdata：100条数据，A表tb_emp_bigdata：5000条数据。

用in的查询时间为：

将上面sql转换成exists：

select *from tb_emp_bigdata A where exists(select 1 from tb_dept_bigdata B where B.deptno=A.deptno);

用exists的查询时间：

经对比可看到，在B表数据集小于A表的时候，即A大B小时，用 in 要优于 exists，当前的数据集并不大，所以查询时间相差并不多。

②当A表的数据集小于B表的数据集时，即A小B大时，用 exists 优于 in。

select *from tb_dept_bigdata A where A.deptno in(select B.deptno from tb_emp_bigdata B);

用in的查询时间为：

将上面sql转换成exists：

select *from tb_dept_bigdata A where exists(select 1 from tb_emp_bigdata B where B.deptno=A.deptno);

用exists的查询时间：

由于数据量并不是很大，因此对比并不是难么的强烈。

附上视频的结论截图：

 

结论：

in 后面跟的是小表，exists 后面跟的是大表。

简记：in+小表，exists+大表。

 

二、JOIN查询

A JOIN B，表A是左表，表B示右表；左表永远是主表，右表永远是嵌套表。

Mysql在版本5.5之前，使用了Nest Loop Join算法，在版本5.5之后，采用了优化的Block Nested-Loop Join算法。优化的Block Nested-Loop Join方法，首先将外层循环数据（即驱动表查询出来的数据）的一部分缓存在 join buffer（join_buffer_size 决定这个缓冲区的大小）中，然后读取被驱动表数据，逐条和buffer中的数据进行对比，从而减少了查表次数（一次Block嵌套查询，仅仅对被驱动表读取一次），这样便可以提高效率（被驱动表越大，驱动表越小，效果越明显）。而驱动表的选取，是系统根据既定规则进行选择的，理论上是选择小表作为驱动表。而 straight_join 是留给用户进行驱动表选择的利器，即使用 straight_join 时永远选择主表为驱动表，当 where 条件和 order by 不在小表时，可以尝试使用 straight_join 选择大表驱动小表。
Block Nested-Loop Join算法： https://dev.mysql.com/doc/refman/5.7/en/nested-loop-joins.html#block-nested-loop-join-algorithm

建立 t1, t2 两个完全相同的表，t1 表中写入 100 条数据，t2 表中 写入 1000 条数据。

Index Nested-Loop Join（NLJ） (被驱动表有索引的情况选择)

　　- 语句

　　　　- 为了避免Mysql 选择驱动表对于分析的影响，改用 straight_join 让 MySQL 使用固定的连接方式执行查询。

　　　　- t1 是驱动表，t2 是被驱动表。

　　　　- select * from t1 straight_join t2 on (t1.a=t2.a);

 

　　- 执行流程

　　　　- 在这条语句里，被驱动表 t2 的字段 a 上有索引，join 过程用上了这个索引

　　　　- 从表 t1 中读入一行数据 R；

　　　　- 从数据行 R 中，取出 a 字段到表 t2 里去查找；

　　　　- 取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；

　　　　- 重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。 

　　　　- 

 

　　- 小结

　　　　- 这个过程是先遍历表 t1，然后根据从表 t1 中取出的每行数据中的 a 值，去表 t2 中查找满足条件的记录。

　　　　- 在形式上，这个过程很像写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ。 

　　　　- 整个过程， 总扫描行数是 200（t1 100 + t2 索引树100）

 

Block Nested-Loop Join（NLJ）（被驱动表无索引时）

　　- 语句

　　　　-  select * from t1 straight_join t2 on (t1.a=t2.b);

　　　　- 由于表 t2 的字段 b 上没有索引，因此在执行流程时，每次到 t2 去匹配的时候，就要做一次全表扫描。

 

　　- 流程

　　　　- 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；

　　　　- 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

　　　　- 

 

　　- 小结

　　　　- 可以看到，在这个过程中，对表 t1 和 t2 都做了一次全表扫描，因此总的扫描行数是 100100。

　　　　- 由于 join_buffer 是以无序数组的方式组织的，因此对表 t2 中的每一行，都要做 100 次判断，总共需要在内存中做的判断次数是：100*1000=10 万次。

　　　　- join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。

 

五：总结

　　- 能不能使用 join ?

　　　　- 如果可以使用 Index Nested-Loop Join 算法，也就是说在连接被驱动表时可以用上被驱动表上的索引，其实是没问题的；

　　　　- 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。

　　　　　　- 尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

 

　　- 如果要使用 join，应该选择大表做驱动表还是选择小表做驱动表？

　　　　- 本人感觉没有区别，在考虑Index Nested-Loop Join 算法的之外，唯一有区别的就是LEFT JOIN、RIGHT JOIN和INNER JOIN的语义。建议在考虑 Index Nested-Loop Join 算法的之外，尽量少用JOIN语法。

               - 如果非要选择join，可以考虑：

                      1）如果有排序时，考虑将排序字段所在表作为驱动表；

                      2）尽量将小表作为驱动表；对于驱动表和被驱动表都可以考虑使用 where 缩小查询范围，其中：缩小被驱动表的查询范围更有效。