MySql性能（8）- join的优化（尽可能优化using t

1. 执行计划

using temporary ——使用临时表

mysql要使用临时表来存储中间结果集，一般用于排序和分组查询。mysql使用临时表用来存储中间数据，但是这个临时表的建立过程是比较耗时的。

using where ——筛选

where字句用于限制于下一个表匹配的行记录或发送到客户端的行记录。

mysql在遇到inner join连接语句时，mysql表关联的算法是Nest Loop Join(嵌套联接循环)。而Nest Loop Join就是通过两层循环手段进行依次的匹配操作。最后返回结果集合。在inner join中，mysql会智能选择小表作为基表，而另一张表叫做连接表。首先会处理驱动表中没一行符合条件的数据，之后的每一行数据和连接表进行匹配操作，直至循环结束，最后合并结果返回给用户。对于基表是可以使用索引来分组或者排序操作。而对于非基表的字段排序或者分组就会产生using temporary临时表。

案例分析

在项目中有一个导出数据的sql：

select 
a.project,
a.`name` ,
a.phone_number ,
a.email ,
SUM(CASE WHEN document_type =9 THEN 1 ELSE 0 END) '下载量'
from download t 
INNER JOIN account a ON substring_index(t.user_id,'_CC',1)=a.email 
where a.type=1 and t.document_type =9 
GROUP BY t.school_id,t.user_id;

执行计划：

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5276 |    10.00 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8450 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.11 sec)

分析：

1. select_type为SIMPLE即简单查询；
2. id相同，执行顺序是由上至下；

实际耗时：

15 rows in set (0.48 sec)

sql耗时分析：

mysql> set profiling=1;
Query OK, 0 rows affected (0.01 sec)

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000177 |
| checking permissions | 0.000036 |
| checking permissions | 0.000019 |
| Opening tables       | 0.000049 |
| init                 | 0.000060 |
| System lock          | 0.000022 |
| optimizing           | 0.000025 |
| statistics           | 0.000036 |
| preparing            | 0.000033 |
| Creating tmp table   | 0.000056 |
| Sorting result       | 0.000019 |
| executing            | 0.000013 |
| Sending data         | 0.348893 |
| Creating sort index  | 0.000141 |
| end                  | 0.000024 |
| query end            | 0.000015 |
| removing tmp table   | 0.000045 |
| query end            | 0.000012 |
| closing tables       | 0.000015 |
| freeing items        | 0.000029 |
| cleaning up          | 0.000018 |
+----------------------+----------+
21 rows in set (0.01 sec)

可以看到，这个sql是比较耗时的，且大多数的耗时是发生在Sending data阶段。

分析：执行计划若是id相同，那么执行顺序的优先级是从上到下。mysql会将小表作为主表。即先使用where条件筛选t表数据，然后在使用Nest Loop Join算法和a表进行连接。将结果数据集导入到内存中后，在进行分组操作。而在show profile中可以看出Sending Data占用很长时间(推测可能是构建临时表耗时长)。

优化方案

尝试的优化方向：在内联操作前，缩小连接表的大小。这里可以借助子查询的方式。

优化后的sql：

select 
a.project,
a.name,
a.phone_number,
a.email,
tt.jc '下载量'
from 
(SELECT school_id,user_id,`subject`,SUM(CASE WHEN document_type =9 THEN 1 ELSE 0 END) AS jc
FROM download t WHERE t.document_type =9 GROUP BY t.school_id,t.user_id) tt
INNER JOIN account a ON substring_index(tt.user_id,'_CC',1)=a.email 
where a.type=1;

执行计划

+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  527 |   100.00 | NULL                                               |
|  1 | PRIMARY     | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 8450 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | t          | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 5276 |    10.00 | Using where; Using temporary; Using filesort       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set (0.02 sec)

分析：

1. 如果是子查询，id的序号会递增，id越大优先级越高，越先被执行；
2. id如果相同，可以认为一组，从上往下顺序执行。
3. DERIVED衍生，以上sql的执行顺序为：t、<derived2>、a。

实际耗时（基本减少20倍，这还是数据量少的情况）：

15 rows in set (0.03 sec)

sql耗时分析：

mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000163 |
| checking permissions | 0.000023 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000027 |
| init                 | 0.000068 |
| System lock          | 0.000018 |
| optimizing           | 0.000012 |
| optimizing           | 0.000016 |
| statistics           | 0.000029 |
| preparing            | 0.000018 |
| Creating tmp table   | 0.000022 |
| Sorting result       | 0.000016 |
| statistics           | 0.000019 |
| preparing            | 0.000021 |
| executing            | 0.000014 |
| Sending data         | 0.000016 |
| executing            | 0.000010 |
| Sending data         | 0.002363 |
| Creating sort index  | 0.019497 |
| end                  | 0.000036 |
| query end            | 0.000026 |
| removing tmp table   | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000015 |
| removing tmp table   | 0.000017 |
| closing tables       | 0.000021 |
| freeing items        | 0.000040 |
| cleaning up          | 0.000028 |
+----------------------+----------+

可以看到Sending Data的耗时已经降下来了。

总结：在内联操作前，尽可能的缩小连接表的大小。是可以优化sql。

例如优化后的sql，查询完t表，最终得到的数据量为15条。然后在和a表进行匹配。最终临时表得到的数据也只有15条。

但是优化前的sql，查询完t表，得到的数据量是400多条。在和a表进行匹配时，临时表大小为400多条。而构建临时表。是很消耗时间的。

备注：

mysql语句执行顺序：

1、执行FROM语句
2、执行ON过滤
3、添加外部行
4、执行where条件过滤
5、执行group by分组语句
6、执行having
7、select列表
8、执行distinct去重复数据
9、执行order by字句
10、执行limit字句

临时表和group by的关系

1. 如果GROUP BY 的列没有索引,产生临时表.
2. 如果GROUP BY时,SELECT的列不止GROUP BY列一个,并且GROUP BY的列不是主键 ,产生临时表.
3. 如果GROUP BY的列有索引,ORDER BY的列没索引.产生临时表.
4. 如果GROUP BY的列和ORDER BY的列不一样,即使都有索引也会产生临时表.
5. 如果GROUP BY或ORDER BY的列不是来自JOIN语句第一个表.会产生临时表.
6. 如果DISTINCT 和 ORDER BY的列没有索引,产生临时表.

若不能消除Using temporary临时表，那么需要在内联操作前，连接表尽可能小！

一般来说，group by语句会默认进行排序。所以group by借助索引优化生效情况和order by使用索引优化性能情况相同。

+----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows  | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_student   | idx_student | 768     | NULL | 99282 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+-------+----------+--------------------------+
1 row in set (0.15 sec)

推荐阅读

MySQL系列 | explain之id介绍

using index,using where,using temporary,using filesort

mysql查询优化--临时表和文件排序（Using temporary; Using filesort问题解决）

多表关联group by优化：去除Using temporary; Using filesort

MYSQL一次千万级连表查询优化（一）

关于mysql inner join 连接查询的优化