学习篇-mysql-优化07-慢查询优化join详解

一、慢查询优化详解

• 永远用小结果集驱动大的结果集（join操作表小于百万级别）

  • 驱动表的定义

    • 当进行多表连接查询时，【驱动表】的定义为：
      • 指定了联接条件时，满足查询条件的记录行数少的表为【驱动表】
      • 未指定联接条件时，行数少的表为【驱动表】

  • left join 则左边的为驱动表

  • right join 则右边的为驱动表

  • explain 结果中，第一行出现的表就是驱动表

• mysql关联查询的概念

  • mysql表关联的算法是Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，最后合并结果

  • left join,right join,inner join的区别

    • left join

       select * from t2 left join t3 on t2.id = t3.id and t3.id in(1,2,3) order by t2.id desc;
      

      

    • right join

       select * from t2 right join t3 on t2.id = t3.id and t3.id in(1,2,3);
      

      

    • inner join

      select * from t2 inner join t3 on t2.id = t3.id and t3.id in(1,2,3);
      或者
      select * from t2 join t3 on t2.id = t3.id and t3.id in(1,2,3);
      或者
      select * from t2,t3 where t2.id = t3.id and t3.id in(1,2,3);
      

      

  • 当left join和right join 以小表驱动大表时，查询结果和inner join的三种写法一致。

    

    

    

  • Mysql8.0之前只支持一种join算法：

    • Nested-Loop Join（嵌套循环连接）

      • 以一张表为驱动表，以驱动表中的每一行去访问另一张表中的每一行记录。类似一个嵌套循环。

    • 但是Nested-Loop Join有三种变种：

      • Simple Nested-Loop Join【基本不会走这种模式】

        

        • 如图以A表为驱动表，A表中的每一条记录去匹配B表中的数据，过于粗糙，假如每个表中有1000条数据，比较的次数就是1000 * 1000，查询效率太低了。所以，基本是弃用算法。

      • Index Nested-Loop Join【索引嵌套循环连接】

        

        • INLJ是在SNLJ的基础上做了优化，通过连接条件确定可用的索引，进行匹配，避免和内层表的每条记录进行匹配，极大的减少了对内层表的匹配次数。
        • 从原来的匹配次数 = 外层表行数 * 内层表行数，变成了外层表的行数 * 内层表索引的高度，极大提高了join的性能。
        • 使用场景：只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接。
        • 缺点：
          • 如果扫描的索引是非聚簇索引，并且需要访问非索引的数据，会产生一个回表读取数据的操作，这就多了一次随机的I/O操作。

      • Block Nested-Loop Join【缓存块嵌套循环连接】

        

        • 在索引不可用的情况下，默认使用该算法

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

        • 使用Block Nested-Loop Join 算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on 默认为开启，如果关闭则使用Simple Nested-Loop Join 算法。

          // 查看是否开启
          show variables like 'optimizer_switc%'; 
          

          

        • 设置join buffer 的大小

          // 查看大小,默认值256k
          show variables like 'join_buffer_size%';
          

          

  • 在mysql8.0支撑了hash join 跟mysql的版本有关系，就算是8.0后面的版本，也有版本不是全面支持的。

    • mysql8.0以前

      

    • mysql8.0以后
      

    • 大致原理：

      • 将驱动表的join字段加载进内存，计算join字段的hash值，并且建立一个hash table，将驱动表字段的hash值存放至hash table中，然后对被驱动表的join字段计算hash值，并与内存中的hash table进行查找匹配。

    • 前提：

      • 打开hash_join=on，默认打开状态

        

      • join的字段上没有索引

  • join的优化思路

    • 尽可能减少join语句中的Nested-Loop的循环总次数
    • 优先优化Nested-Loop的内层循环
    • 保证join语句中被驱动表上join条件字段已经被索引
    • 在无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要吝啬join buffer的设置，一次缓存的数据越多，那么外层表循环的次数就越少
    • 减少不必要的字段查询：
      • 当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，外层表的循环次数就越少；
      • 当用到INLJ时，查询列尽量索引覆盖。

• 总结

  • 并发量太高的时候，系统整体性能可能会急剧下降
  • 复杂的join语句，所需要的锁定的资源也就越多，所阻塞的其他线程也就越多
  • 复杂的query语句分拆成多个较为简单的query语句分步执行