MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解

2022-09-15 18:46:21

由一次Left Join查询缓慢引出的Explain和Join算法详解

前些日子在生产环境中，项目经理偶然发现有一条SQL执行的非常缓慢，达到了不杀死这个语句就难以平民愤的程度。于是委派我来解决这个问题。

后来追踪到这是一个600万条数据的表和一个700万条数据的表 left join 的故事，sql语句类似于下面这种：

SELECT 
    a.column_1,
    a.column_2,
    a.column_3,
    c.column_1
FROM
    table_1 a
        LEFT  JOIN
    table_2 c ON  a.column_1 = c.column_1 
WHERE
    a.column_1 = 'value1' 
AND
    a.column_2 BETWEEN STR_TO_DATE('2021-05-27 23:55:59','%Y-%m-%d %H:%i:%s') 
AND STR_TO_DATE('2021-05-27 23:59:59','%Y-%m-%d %H:%i:%s')
ORDER BY a.column_2 DESC
LIMIT 0 , 1

我眉头一皱，发现事情并不简单，就想到了先用 explain 来看看这个语句到底干了什么。

MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解 MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解

Explain详解

执行explain之后的结果如下所示：

MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解

id

  • id越大执行优先级越高
  • id相同则从上往下执行
  • id为NULL最后执行

select_type

  • SIMPLE：简单SELECT，不使用UNION或子查询等。
  • PRIMARY：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY。
  • UNION：UNION中的第二个或后面的SELECT语句。
  • DEPENDENT UNION：在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
  • UNION RESULT：MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT。
  • SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。
  • DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。
  • DERIVED：对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED。
  • UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，必须重新评估外链接的第一行。
  • MATERIALIZED：当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED。
semi-join：
是指当一张表在另一张表找到匹配的记录之后，半连接（semi-jion）返回第一张表中的记录。
与条件连接相反，即使在右节点中找到几条匹配的记录，左节点 的表也只会返回一条记录。
另外，右节点的表一条记录也不会返回。半连接通常使用IN 或 EXISTS 作为连接条件。
物化：
这个将子查询结果集中的记录保存到临时表的过程称之为物化（Materialize）。
那个存储子查询结果集的临时表称之为物化表。
正因为物化表中的记录都建立了索引（基于内存的物化表有哈希索引，基于磁盘的有B+树索引），通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快，从而提升了子查询语句的性能。

table

当前查询的表名。表名有可能是原名，有可能是别名，要看SQL语句的具体情况。

partitions

如果数据表建立分区了的话，这里会显示查询用到的分区。

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

  • ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行。
  • index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
  • range：只检索给定范围的行，使用一个索引来选择行。
  • ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
  • eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用 primary key 或者 unique key 作为关联条件。
  • const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system。
  • NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

possible_keys

可能用到的索引。查询涉及到的字段上若存在索引，则列出，但查询的时候不一定会使用（如果没有任何索引显示 null）。

key

实际用到的索引。

key_len

表示索引中使用的字节数。

该列计算查询中使用的索引的长度，在不损失精度的情况下，长度越短越好。如果键是NULL，则长度为NULL。

该字段显示为索引字段的最大可能长度，并非实际使用长度。

ref

在key列显示的索引中，表在查找时所用到的列或常量，常见的有：const（常量），字段名（例：t1.id）。

rows

预计要读取并检索的行数，注意这个不是结果集里的行数，并且这个数字是一个估值，不是准确值。

filtered

返回结果的行占需要读到的行(rows列的值)的百分比。

Extra

  • Using where：SQL使用了where条件过滤数据。（可以优化）
  • Using index：SQL所需要返回的列都在一棵索引树上，不需要访问其对应的实际行记录。（性能较好）
  • Using index condition：SQL用到了索引，但不是所有需要返回的列都在索引上，还需要访问实际的行记录。（性能次于Using index）
  • Using filesort：要得到想要的结果集，需要将所有的数据进行排序。在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。（性能很差，需要优化）
  • Using temporary：需要建立临时表来得到想要的结果集。group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。（性能较差，需要优化）
  • Using join buffer （Block Nested Loop）：需要进行嵌套循环计算。两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。（性能较差，需要优化）

 Join算法详解

摘自 https://blog.csdn.net/u010841296/article/details/89790399

Mysql Nested-Loop Join 的算法思想去优化join，Nested-Loop Join翻译成中文则是“嵌套循环连接”。

举个例子：
select * from t1 inner join t2 on t1.id=t2.tid
（1）t1称为外层表，也可称为驱动表。
（2）t2称为内层表，也可称为被驱动表。

伪代码表示如下：

List<Row> result = new ArrayList<>();
for(Row r1 in List<Row> t1){
    for(Row r2 in List<Row> t2){
        if(r1.id = r2.tid){
            result.add(r1.join(r2));
        }
    }
}

在Mysql的实现中，Nested-Loop Join有3种实现的算法：

  • Simple Nested-Loop Join：SNLJ，简单嵌套循环连接
  • Index Nested-Loop Join：INLJ，索引嵌套循环连接
  • Block Nested-Loop Join：BNLJ，缓存块嵌套循环连接

在选择Join算法时，会有优先级，理论上会优先判断能否使用INLJ、BNLJ：

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

Simple Nested-Loop

简单嵌套循环连接实际上就是简单粗暴的嵌套循环，如果table1有1万条数据，table2有1万条数据，那么数据比较的次数=1万 * 1万 =1亿次（笛卡尔集），这种查询效率会非常慢。

所以Mysql继续优化，衍生出Index Nested-LoopJoin、Block Nested-Loop Join两种NLJ算法。在执行join查询时mysql会根据情况选择两种之一进行join查询。

MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解

Index Nested-LoopJoin（减少内层表数据的匹配次数）

索引嵌套循环连接是基于索引进行连接的算法。索引是基于内层表的，通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较， 从而利用索引的查询减少了对内层表的匹配次数，优势极大的提升了 join的性能：

原来的匹配次数 = 外层表行数 * 内层表行数
优化后的匹配次数 = 外层表的行数 * 内层表索引的高度

只有内层表join的列有索引时，才能用到Index Nested-LoopJoin进行连接。

由于用到索引，如果索引是辅助索引而且返回的数据还包括内层表的其他数据，则会回内层表查询数据，多了一些IO操作。

MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解

Block Nested-Loop Join（减少内层表数据的循环次数）

缓存块嵌套循环连接通过一次性缓存多条数据，把参与查询的列缓存到Join Buffer 里，然后拿join buffer里的数据批量与内层表的数据进行匹配，从而减少了内层循环的次数（遍历一次内层表就可以批量匹配一次Join Buffer里面的外层表数据）。

当不使用Index Nested-Loop Join的时候，默认使用Block Nested-Loop Join。

MySQL - 由一次Left Join查询缓慢引出的Explain和Join算法详解

什么是Join Buffer？

  • Join Buffer会缓存所有参与查询的列而不是只有Join的列。
  • 可以通过调整join_buffer_size缓存大小
  • join_buffer_size的默认值是256K，join_buffer_size的最大值在MySQL 5.1.22版本前是4G，而之后的版本才能在64位操作系统下申请大于4G的Join Buffer空间。
  • 使用Block Nested-Loop Join算法需要开启优化器管理配置的optimizer_switch的设置block_nested_loop为on，默认为开启。

如何优化Join速度

用小结果集驱动大结果集，减少外层循环的数据量，从而减少内层循环次数：如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。

为匹配的条件增加索引：争取使用INLJ，减少内层表的循环次数.

增大join buffer size的大小：当使用BNLJ时，一次缓存的数据越多，那么内层表循环的次数就越少.

减少不必要的字段查询：

  • 当用到BNLJ时，字段越少，join buffer 所缓存的数据就越多，内层表的循环次数就越少；
  • 当用到INLJ时，如果可以不回表查询，即利用到覆盖索引，则可能可以提示速度。（未经验证，只是一个推论）

最终解决方案

有一说一，由于业务关系，我这次问题的解决方案是不用join。但我在实践中发现，用join代替left join也可以使速度变快，如果我以后研究出了个所以然，会在这里继续更新的。

上一篇：SpringBoot-GraphQL错误


下一篇：SpringMVC配置拦截器后，启动项目500