mysql join

JOIN操作常见算法

• Nested Loop Join (循环嵌套连接)
• Hash Join(散列连接)
• Sort Merge Join(排序归并连接)

MySQL只支持Nested Loop Join，MySQL8.0版本支持Hash Join

Nested-Loop Join

Nested-Loop Join有3种实现的算法

Index Nested-LoopJoin > Block Nested-Loop Join > Simple Nested-Loop Join

Simple Nested-Loop

将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端

Block Nested-Loop Join

将外层循环的行/结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数

Index Nested-Loop Join

通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次

ICP & MRR & BKA

Index Condition Pushdown (ICP)是mysql使用索引从表中检索行数据的一种优化方式；mysql 5.6之后支持ICP后，如果WHERE条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。ICP能减少引擎层访问基表的次数和 Server层访问存储引擎的次数

MRR 的全称是 Multi-Range Read Optimization，是优化器将随机 IO 转化为顺序 IO 以降低查询过程中 IO 开销的一种手段，这对IO-bound类型的SQL语句性能带来极大的提升，适用于range ref eq_ref类型的查询

MySQL5.6开始支持。
Batched Key Access (BKA) 是用来提高表join性能的算法。BKA适合MRR在范围访问的场景，MRR是BKA的基础。
如果被Join的表上没有索引，则使用老版本的BNL策略(Block Nested-Loop)。当被join的表能够使用索引时，就先排好顺序，然后再去检索被join的表。对这些行按照索引字段进行排序，因此减少了随机IO

MySQL join 语句的规范

• 被驱动表上有索引，也就是可以使用 Index Nested-Loop Join 算法时，可以使用 join 操作。
• 无论是 Index Nested-Loop Join 算法或者 Block Nested-Loop Join 都要使用小表做驱动表
• 增大join buffer size的大小：当使用BNLJ时，一次缓存的数据越多，那么内层表循环的次数就越少
• 减少不必要的字段查询