一.前言

hello，everyone.爱情只会影响我们打代码的速度，七夕节，我当然打代码了！！！

哈哈哈，不扯了。本周产品突然找我说线上文件中心的页面查询特别慢，我当时寻思着，这系统刚布上去，能有多少数据量。好家伙，最多的才5000条不到的数据，查询时间时间居然要8S。然后领导还在催产品说这垃圾代码是谁写的，居然这么卡...

过程极其坎坷，我问题定位了1个下午，愣是没想到到底什么原因导致的。咨询了几个互联网大厂的朋友与公司的DBA，也没有头绪。

最后还是一个刚入职不久的同事（开发存储过程类代码1年多时间）加了个索引就解决了这个问题，但是背后的原理个人觉得非常值得学习。

本文将给大家展示一下这个问题的解决思路与sql设计规范。

二.问题描述

2.1.业务场景描述

1.文件中心统一维护业务系统上传的所有系统文件。

2.相同MD5的文件仅保持一份，下载下来的文件需要与上传的文件名一致。

3.上传的文件可以被多个案件【业务属性，假设是一种订单类数据】或者多种案件类型关联

4.文件中心管理列表展示5个字段：文件id，文件名称，文件MD5，相关案件【关联案件则展示案件id，关联案件类型则展示案件类型，选择关联所有案件则展示为所有案件，可以同时关联案件与案件类型，展示时以逗号间隔】，备注

5.管理界面可基于文件id，文件名称，md5，案件id，案件类型字段过滤查询

2.2.设计思路

基于上述产品的需求，我将数据库表设计为4张表

表名	描述
t_file	基础文件信息，以文件的md5为唯一索引作区分
t_file_info	扩展文件信息，不同文件名，同一文件在t_file中为1条记录，此表中为2条记录，以t_file主键id做关联
t_file_incident_type	关联案件类型，以t_file_info主键id关联
t_file_incident	关联案件，以t_file_info主键id关联

数据比例关系

t_file 1:n t_file_info 1:n t_file_incident

t_file 1:n t_file_info 1:n t_file_incident_type

表结构由于涉及业务属性，不贴了

2.3.问题描述

子查询数据很快【50ms】，union all结果集之后【8s】，sql执行时间相差160倍！

2.4.问题sql

(SELECT tf.id            AS id,
        tf.md5           AS md5,
        tf.object_name   AS objectName,
        tfi.id           AS fileInfoId,
        tfi.file_name    AS fileName,
        tfi.remark       AS remark,
        tfi.visibility   AS visibility,
        tfi.gmt_create   AS gmtCreate,
        tfin.incident_id AS incidentId,
        NULL             AS incidentType
 FROM t_file AS tf
          JOIN t_file_info AS tfi ON tf.id = tfi.file_id AND tfi.deleted = 0 AND tfi.visibility = 0 AND
                                     tfi.business_type = 'business'
          LEFT JOIN t_file_incident AS tfin
                    ON tfi.id = tfin.file_info_id AND (tfin.deleted = 0 OR tfin.deleted IS NULL)
 WHERE tf.deleted = 0)
UNION all
(SELECT tf.id                 AS id,
        tf.md5                AS md5,
        tf.object_name        AS objectName,
        tfi.id                AS fileInfoId,
        tfi.file_name         AS fileName,
        tfi.remark            AS remark,
        tfi.visibility        AS visibility,
        tfi.gmt_create        AS gmtCreate,
        NULL                  AS incidentId,
        tfit.incident_type AS incidentType
 FROM t_file AS tf
          JOIN t_file_info AS tfi ON tf.id = tfi.file_id AND tfi.deleted = 0 AND tfi.visibility = 0 AND
                                     tfi.business_type = 'business'
          LEFT JOIN t_file_incident_type AS tfit
                    ON tfi.id = tfit.file_info_id AND (tfit.deleted = 0 OR tfit.deleted IS NULL)
 WHERE tf.deleted = 0)
union all
(SELECT tf.id          AS id,
        tf.md5         AS md5,
        tf.object_name AS objectName,
        tfi.id         AS fileInfoId,
        tfi.file_name  AS fileName,
        tfi.remark     AS remark,
        tfi.visibility AS visibility,
        tfi.gmt_create AS gmtCreate,
        '所有案件'         AS incidentId,
        '所有类型'         AS incidentType
 FROM t_file AS tf
          JOIN t_file_info AS tfi ON tf.id = tfi.file_id AND tfi.deleted = 0 AND tfi.visibility = 1 AND
                                     tfi.business_type = 'business'
 WHERE tf.deleted = 0)

因为要分页，数据聚合的动作在内存中实现不太切实际，由于展示与过滤的特殊性需求sql博主只想到上述的做法。上面的sql有的大佬可能会说写的太复杂，我跟阿里的朋友沟通的时候他们也是说表关联关系太多，但是业务限制如此，没有设计冗余字段。

上述的sql执行结果有三个子查询组成，结果使用union all进行关联

1.union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

2.union All：对两个结果集进行并集操作，包括重复行，不进行排序；

3.union 和 union all都可以将多个结果集合并，而不仅仅是两个，你可以将多个结果集串起来。 使用union和union all必须保证各个select 集合的结果有相同个数的列，并且每个列的类型是一样的

表内数据描述

表	数据量
t_file	5000-
t_file_info	5000-
t_file_incident	8000+
t_file_incident_type	0

三个子查询分别执行，执行时间均在50-100ms之间

执行整个sql，执行sql在8s+

三.问题解决思路

3.1.sql执行与子查询执行

产品第一时间反馈问题给我的时候，我从后台日志拿到sql进行整段执行发现sql执行时间超过了8S，况且查询结果得到后还需要在java层面二次的业务处理，所以实际展示到页面上可能差不多不到9S左右。然后我分别对union all的子查询进行执行，执行时间均在50-100ms左右

3.2.explain查看执行计划

说实话，第一步执行完之后我其实还挺蒙的，我一度以为我对union all的用法使用错了，union all就是将子查询结果集直接进行了拼接。但是我使用之后居然执行时间指数级上涨。

还是sql调优的惯用套路，查看一下执行计划

子查询sql执行计划与上述一致

是不是蒙了。。。

3.3.sql执行过程

回顾一下一条sql的执行过程

每个节点的细节不做展开，感兴趣的同学可以自行度娘，这里简单提一下节点的作用。

连接器：连接mysql服务器

查询缓存：mysql中维护了一个Buffer Pool （缓冲池），是 InnoDB 存储引擎中非常重要的内存结构，顾名思义，缓冲池其实就是类似 Redis 一样的作用，起到一个缓存的作用

分析器：分析sql语法

优化器：优化sql执行顺序

执行器：执行sql

由于我们的sql能够正常执行，前三个节点基本被排除了，只能从优化器与执行器中取查看问题了。生产上使用的mysql版本是5.7.26，且服务器配置64G，万兆网卡。top命令查询cpu与内存使用率都很正常。所以排除了执行器的问题。因此就怀疑是优化器的问题了

3.4.问题定位解决

到这里之后因为自己本身对mysql更多都是停留在sql使用，索引使用。存储引擎层面的实现不熟悉，因此询问了公司的DBA大佬

从这里得知两个关键信息点，sql查询慢由两个原因导致：

1.left join走了全表扫描，查询慢【但是子查询直接执行速度很快】

2.mysql内部修改了我自己定义的sql执行逻辑

到这里为止，我已经没有办法了，已经想着等DBA给我调一下mysql的hint，让存储引擎强行执行我的逻辑了。

过了大约半个小时候，同事突然让我再试试sql，瞬间执行时间只需要137ms了【优化后的速度比优化前快了58倍】。差不多是三个子查询时间的总和，符合结果集拼接预期时间。

增加右边索引后的执行计划

我当时就震惊了

他只是在上面t_file_incidnet与t_file_incident_type两张表上关联字段file_info_id上加了个索引，速度瞬间就上去了。

3.5.原理

回到3.2执行计划，其实第一次看执行计划的时候我忽略的一个比较重要的地方，在extra中中已经提示了Using join buffer (Block Nested Loop)。但是因为前面有了using where，而且子查询中也存在这句话我就没在乎，觉得不是问题。

mysql在5.7的版本中做了查询优化：Block Nested-Loop

MySQL BNL算法原本只支持内连接，现在已支持外连接和半连接操作,包括嵌套外连接。

BNL算法原理：将外层循环的行/结果集存入join buffer，内存循环的每一行数据与整个buffer中的记录做比较，可以减少内层循环的扫描次数

举个简单的例子：外层循环结果集有1000行数据，使用NLJ算法需要扫描内层表1000次，但如果使用BNL算法，则先取出外层表结果集的100行存放到join buffer, 然后用内层表的每一行数据去和这100行结果集做比较，可以一次性与100行数据进行比较，这样内层表其实只需要循环1000/100=10次，减少了9/10。

for each row in t1 matching range {
   for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
         for each t1, t2 combination in join buffer {
          if row satisfies join conditions,
          send to client
        }
        }
       empty buffer
     }
   }
 }
​
 if buffer is not empty {
    for each row in t3 {
     for each t1, t2 combination in join buffer {
       if row satisfies join conditions,
       send to client
      }
   }
 }

如果t1, t2参与join的列长度只和为s, c为二者组合数, 那么t3表被扫描的次数为

(S * C)/join_buffer_size + 1

扫描t3的次数随着join_buffer_size的增大而减少, 直到join buffer能够容纳所有的t1, t2组合, 再增大join buffer size, query 的速度就不会再变快了。

optimizer_switch系统变量的block_nested_loop标志控制优化器是否使用块嵌套循环算法。

默认情况下，block_nested_loop已启用。

在EXPLAIN输出中，当Extra值包含Using join buffer（Block Nested Loop）且type值为ALL，index或range时，表示使用BNL。

示例

mysql> explain SELECT  a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 298936 |   100.00 | NULL                                               |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 331143 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

回到我们的问题

5.6的版本是做成了临时表，然后对临时表join。 而5.7直接用了Using join buffer (Block Nested Loop)。

filtered=100% 表示右表没有应用索引下推（ICP），因为where条件没有索引。

这个Using join buffer (Block Nested Loop)是因为右表没有在join列上建索引导致嵌套循环。

看来根源就在这儿了，首先没有ICP导致要全表数据到server层，其次left join 列没有索引又导致了嵌套循环。

可见，mysql的优化器会先执行有索引的结果集，然后再与无索引表join。

四.总结

1.日常研发的过程中还是需要谨慎使用left join，尽量使用join，如果能在代码中做关联，效果可能更好。

2.必须使用left join时，两边最好对于关联字段加上索引，右边必须加索引。

3.索引的建立列建立在区分度高的字段中。区分度公式：count(distinct col)/count(*)

五.参考

MySQL 查询优化之 Block Nested-Loop 与 Batched Key Access Joins

mysql left join 导致的 Using join buffer (Block Nested Loop)

六.联系我

文中如有不正确之处，欢迎指正，写文不易，点个赞吧，么么哒~

钉钉：louyanfeng25

微信：baiyan_lou