一.前言
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
代码语言：javascript复制(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表被扫描的次数为
代码语言：javascript复制(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。
示例
代码语言：javascript复制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)
