mysql jooin算法原理及优化建议

本文详细介绍了Mysql的JOIN算法，包括simple nested-Loop、Blocked nested-Loop和Indexed nested-Loop，并给出了优化建议，如使用索引、调整join_buffer_size和减少不必要的字段查询等。

摘要由CSDN通过智能技术生成

1. Mysql join算法原理 simple nested-Loop (简单嵌套循环连接)

2. Blocked nested-Loop（缓存块嵌套循环连接，当关联的字段没有建索引时，默认是用这种关联算法）

3. Indexed nested-Loop （索引嵌套循环连接）

4. 总结

5. 参考资料

1. Mysql join算法原理 simple nested-Loop (简单嵌套循环连接)

   select * from user tb1 left join level tb2 on tb1.id = tb2.user_id;
   上述代码执行的逻辑是嵌套循环。用左表 tb1 中的每一行数据，都要去扫描 tb2 中的每一行， tb1.id=tb2.user_id 则返回该条数据。伪代码如下：
   for(user表的行 ur : user表) {
       for(level表的行 lr : level 表) {
           if(ur.id == lr.user_id) {
               //返回成功的数据
           }
       }
   }

   上述匹配过程简单的嵌套循环连接（Simple nested-Loop Join）。简单嵌套循环简单易理解，缺点是太过粗略，需要扫描很多数据假如每张表都是1万条数据，则扫描的总次数就是 1万*1万=1亿次，（Line number of user表 * Line number of level表）。

   mysql默认不会使用这种简单粗暴的方法的。因此会采用下面2种中的一个

2. Blocked nested-Loop（缓存块嵌套循环连接，当关联的字段没有建索引时，默认是用这种关联算法）

   优化思路是：减少内表的扫描次数。一次性缓存外表（即左表）的多行，与内表进行扫描关联。来减少内表扫描次数（加入一次缓存了10行，则总体来将就是比simple nested-Loop少扫描10倍）。
   Blocked nested-Loop 默认是开启的，如果关闭就是用 simple nested-Loop 。
   每次缓存的记录数可以通过 join_buffer_size 参数配置。因此驱动表（即左表），select 的字段越少，每次就可以缓存更多的左表记录，从而减少内表扫描次数（尽量避免左表select * ）。

3. Indexed nested-Loop （索引嵌套循环连接）

   优化思路是：减少内表的匹配次数
   左表每次与内表的索引树进行比较（避免与内表的每条记录去进行比较），索引树是二叉平衡树，可减少与内表的匹配次数。
   前提条件：关联的字段加了索引，例如下面的语句需要对 user.id 和 level.user_id 创建索引，这样才会使用 indexed nested-Loop 算法进行扫描。
   select * from user tb1 left join level tb2.user_id ;

   扫描次数变为：外表行数 * 内表索引树高度。

4. 总结

   index nested-Loop 通过索引机制减少内层表的循环匹配次数来达到优化效果。
   block nested-Loop 通过一次缓存左表（即驱动表）的多条记录，批量匹配来减少内表的扫描IO次数，来达到优化效果。
   通过对join算法原理的分析，可以得到以下优化思路：

   （1）永远使用小表作为驱动大数据集表（本质是减少外层循环的数据数量）
   （2）为匹配的条件加上索引（减少内表的循环匹配次数）
   （3）增大 join_buffer_size 的值（一次缓存的次数越多，那么内存表的扫描次数就越少）
   （4）减少不必要的字段查询（字段越少， join buffer所缓存的记录数就越多，扫描的次数就会越少）

   mysql在进行join的时候，会根据情况优先选择后2中算法，如果后两种都无法使用，才会使用 simpel nested-Loop （简单循环）