以前一直有个疑惑,对于内连接使用WHER和JOIN有什么区别
答案是没区别
参考《MySQL是怎么运行的》
连接原理
- 循环嵌套连接(Nested-Loop Join)
- 使用索引加快连接速度
- 基于块的嵌套连接(Block Nested-Loop Join)
循环嵌套连接(Nested-Loop Join)
对于两表连接,驱动表
只会访问一遍,但被驱动表
却要被多次访问,具体积变取决于对驱动表
执行单表查询后的结果集中的记录条数。对于内连接
来说,选取哪个表为驱动表
都没关系,而外连接
的驱动表
是固定的,也就是说左(外)连接的驱动表就是左边的那个表,右(外)连接的驱动表就是右边的那个表。
- 步骤1:选取
驱动表
,使用与驱动表
相关的过滤条件,选取代价最低的单表访问方法来执行对驱动表
的单表查询。 - 步骤2:对上一步骤中查询
驱动表
得到的结果集中每一条记录,都分别到被驱动表
中查找匹配的记录。
图片来源《MySQL是怎么运行的》
如果有3个表进行连接的话,那么步骤2中得到的结果集就像是新的驱动表
,然后第三个表就成为了被驱动表
,重复上边过程,也就是步骤2中得到的结果集中的每一条记录都需要到t3表中找一找有没有匹配的记录。驱动表
只访问一次,但被驱动表
却可能被多次访问,访问次数取决于对驱动表
执行单表查询后的结果集中的记录条数 这样的方式称之为嵌套循环连接
。
可以发现这样的方式在没有索引的情况下效率不是那么理想。
使用索引加快连接速度
在嵌套循环连接的步骤2中可能需要访问多次被驱动表
,如果访问被驱动表
的方式都是全表扫描的话,那炸裂,所以我们需要对被驱动表
进行索引的优化。
- 建立了索引不一定使用索引,只有在
二级索引 + 回表
的代价比全表扫描的代价更低时才会使用索引。 - 另外,有时候连接查询的查询列表和过滤条件中可能只涉及
被驱动表
的部分列,而这些列都是某个索引的一部分,这种情况下即使不能使用eq_ref
、ref
、ref_or_null
或者range
这些访问方法执行对被驱动表
的查询的话,也可以使用索引扫描,也就是index
的访问方法来查询被驱动表
。所以我们建议在真实工作中最好不要使用*
作为查询列表,最好把真实用到的列作为查询列表。
基于块的嵌套连接(Block Nested-Loop Join)
扫描一个表的过程其实是先把这个表从磁盘上加载到内存中,然后从内存中比较匹配条件是否满足。但是面对成千上万条记录,内存里可能并不能完全存放的下表中所有的记录,所以在扫描表前边记录的时候后边的记录可能还在磁盘上,等扫描到后边记录的时候可能内存不足,所以需要把前边的记录从内存中释放掉。采用嵌套循环连接
算法的两表连接过程中,被驱动表
可是要被访问好多次的,如果这个被驱动表
中的数据特别多而且不能使用索引进行访问,那就相当于要从磁盘上读好几次这个表,这个I/O代价就非常大了,所以我们得想办法:尽量减少访问被驱动表
的次数。
对于没有索引,只能全表查询的被驱动表
有了这么一套方案:
当被驱动表
中的数据非常多时,每次访问被驱动表
,被驱动表
的记录会被加载到内存中,在内存中的每一条记录只会和驱动表
结果集的一条记录做匹配,之后就会被从内存中清除掉。然后再从驱动表
结果集中拿出另一条记录,再一次把被驱动表
的记录加载到内存中一遍,周而复始,驱动表
结果集中有多少条记录,就得把被驱动表
从磁盘上加载到内存中多少次。
我们能够在把
被驱动表
的记录加载到内存的时候,一次性和多条驱动表
中的记录做匹配吗?
MySQL提出了一个 join buffer
的概念,join buffer
就是执行连接查询前申请的一块固定大小的内存,先把若干条驱动表结果集中的记录装在这个join buffer
中,然后开始扫描被驱动表
,每一条被驱动表
的记录一次性和join buffer
中的多条驱动表
记录做匹配,因为匹配的过程都是在内存中完成的,所以这样可以显著减少被驱动表的I/O
代价。使用join buffer
的过程如下图所示:
当然,最好的情况是join buffer
足够大,能够容纳驱动表
结果集中的所有记录,这样只需要访问一次被驱动表
就可以完成连接操作了。