1\. 一些要知道的概念
- 在表连接过程中。一般选择小表作为驱动表，大表作为被驱动表。
- 驱动表(小表)的连接字段无论建立没建立索引都需要全表扫描的。被驱动表(大表)如果在连接字段建立了索引，则可以走索引。如果没有建立索引则也需要全表扫描。
1.2 两张表连接的情况
- 被驱动表的连接字段有索引：主键索引
- 对于驱动表中的每一条数据，到被驱动表的聚簇索引上寻找其对于的数据。
- 被驱动表的连接字段有索引：二级索引
- 对于驱动表上的每一条数据，到被驱动表的二次索引上寻找其对于的数据id，然后再根据数据id到聚簇索引上寻找对于的数据。
- 被驱动表的连接字段没有索引
- 对于驱动表上的每一条数据，都要到被驱动表上进行一次全表遍历，找到对应的数据。
- 就是针对被驱动表的连接字段没有索引的情况下需要进行全表扫描，所以引入了join buffer内存缓冲区来对这个全表扫描过程进行优化。
2. mysql中表关联的算法
2.0 前提
- 都是在被驱动表的连接字段没有索引的情况下mysql才会使用这些关联算法进行表连接。
2.1 嵌套循环连接 Nested-Loop Join（NLJ）
select * from t1 left join t2 on t1.name=t2.name;
- 会先从表t1里拿出第一条记录row1，完了再用row1遍历表t2里的每一条记录，来寻找是否name字段是否相等，以便输出。然后循环这个过程，直到t1表里的所有的记录都取出。
- 在整个过程中
- t1表遍历了1遍。t2表遍历了100\*1遍。即每层t1表中取出1条记录，都要遍历一遍t2表。
- 因为这些文件都是在磁盘上的。想想在遍历t2表100遍过程中得有多少次IO操作呀。
- 整个过程跟我们平时写程序的双重for循环本质是一样的。但是我们程序写的双重for循环是基于内存得，而mysql中这些却是基于磁盘的，需要将文件从磁盘调用内存，这样双重for循环，内表需要反复调入内存。
- 假设将表t2。全部调入内存需要10次IO。即每次调入1000条记录。则在t2表的100次遍历过程中需要调用IO次数为 10\*100=1000次IO。
2.2 块嵌套循环连接 Block Nested-Loop Join（BNLJ）