Day33-MySQL临时表详解

前言

总结：增大内存，合理增加my.cnf中临时表的大小设置，避免临时表创建在磁盘上以及最终调整sql业务语句，避免频繁产生大量的临时表新建和删除操作！

可使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表。

用户可以通过以下方式优化：

1) 对group by和order by的列进行优化，添加索引；

2) 将较大的TEXT或者BLOB列拆分成多个较小的列；

3) 对大量的group by和order by语句做拆分；

4) 优化业务逻辑。

一、怎么样尽量避免创建临时表

某些SQL命令在MySQL数据库中被执行的时候，它可能需要先创建一些内部的临时表来完成比较复杂的排序或分组查询。MySQL的临时表分为 in-memory 和 on-disk 两种。 如有可能，MySQL 总是首先使用 in-memory 的临时表， 而当临时表变得太大的时候，也可能被转存为on-disk 的临时表。

如下几个条件下可能导致SQL命令需要创建临时表：

  * 使用了不同的 ORDER BY 和 GROUP BY 条件，或它们包含了JOIN查询中非首先表的字段；

  * 同时使用了DISTINCT和ORDER BY;

  * 如果SQL命令使用了SQL_SMALL_RESULT选项，那么需要时将创建in-memory临时表，除非

    查询中还包含有需用 on-disk 存储的元素；

一些条件下可能阻碍使用in-memory临时表, 导致MySQL改用on-disk临时表：

  * 数据表中含有BLOB类型或TEXT类型字段列；

  * 在GROUP BY或DISTINCT任何条件中含有超过512字节的列；

  * 如果使用了UNION或UNION ALL，而且SELECT列中含有任何超过512字节的列；

  * 如果in-memory临时表变得太大，超过tmp_table_size或max_heap_table_size数值时；

MySQL数据库用Created_tmp_tables和Created_tmp_disk_tables变量记录所用临时表的数目；

关于MySQL内部临时表（internal temporary table）的优化：

(1) 首先，应该尽可能考虑如何避免您的SQL命令创建临时表；

对于一个查询连接非常繁忙的数据库，频繁地使用需要创建临时表的查询本身就已经是一个性能瓶颈。作为一个 DBA，您需要重新检视您的数据表的结构以及各表之间的关联， 重新考虑主键和索引，重组数据结构以减少应用中需不同的ORDER BY和GROUP BY的情况。

作为一个程序员，您则可能需要重新分析您的应用需求， 设法简化SQL命令的复杂程度，例如，尽可能将单次的多层关联查询，拆分为较少关联层次的多次查询，或使用View表。

(2) 其次，应该尽量设法确保临时表被创建于内存而非磁盘之中；

如果实在是无法避免创建临时表，那么退而求其次，则需要尽量确保这些临时表能够被创建在内存之中。避免在结构设计和查询命令中使用BLOB和TEXT类型字段，或可考虑用 SUBSTRRING(colum,length)函数将其转换为字符串类型；用SQL_SMALL_RESULT选项通知数据库使用in-memory临时表；使用View来简化查询；使用RAM disk内存盘来存储MySQL 数据库的临时表（需确保无使用BLOB和TEXT字段）。

如何避免 On-Disk Temporary Tables

因为 Memory 存储引擎不支持 BLOB和TEXT 类型，所以包含有 BLOB和TEXT 类型字段的查询，当它需要用到隐式临时表（implicit temporary table）的时候，就不得不使用 on-disk的MyISAM临时表，即使它的查询结果可能只有很简单的几行数据。

这会导致一个严重的性能瓶颈，即使您能配置将MySQL的临时表存储到RAM disk上,依然还是会需要用到许多昂贵的操作系统的调用函数。 在实用中还发现，某些SQL语句的临时表甚至根本连RAM disk都不能使用（此时SQL查询命令会因为不能创建临时表而失败）。

The best solution is to avoid using the BLOB and TEXT types unless you really need them.

If you can't avoid them, you may be able to use the ORDER BY SUBSTRRING(colum,length)

trick to convert the values to character strings. wihich will permit in-memory temporary

tables.

Just be sure that you are using a short engough substring that the temporary table

doesn't grow larger than max_heap_table_size or tmp_table_size, or MySQL will convert

the table to an on-disk MyISAM table.

If the Extra column of EXPLAIN contains "Using temporary",

the query uses an implicit temporary table.

二、临时表真的会影响查询性能

问题：

近日，线上MySQL查出一个慢sql，每次都要查询1000ms以上，严重影响用户体验

今得空去诊断一番，记录如下：

sql原句：

[html] view plain copy print?

1. SELECT r.object_id AS cardId, count(1) AS attachs FROM hzresource_object r    

2.     LEFT JOIN   

3.     ( SELECT card_id FROM card_member WHERE user_id = #uid# and card_member.deleted=0  

4.      UNION  

5.     SELECT card_id FROM card_subscribed where user_id = #uid# and card_subscribed.deleted=0  

6.     ) m ON r.object_id = m.card_id  

7.     WHERE r.object_type = #objectType# AND r.deleted = 0  

8.     GROUP BY r.object_id;  

• 解决问题：

由于对数据库优化一知半解，完全无从下手，只能求助度娘和谷哥了，试验了各种方法，都不见效果

几番周折之后，最终把注意力集中到了临时表上，因为explain查看执行计划，可以看到Using temporary

MySQL在执行SQL查询时可能会用到临时表，一般情况下，用到临时表就意味着性能较低。

于是想办法修改sql语句，摒弃临时表，修改如下：

[html] view plain copy print?

1. SELECT r.object_id AS cardId, count(1) AS attachs FROM hzresource_object r    

2.     WHERE r.object_type = #objectType#  AND r.deleted = 0 and r.object_id in (  

3.     SELECT card_id FROM card_member WHERE user_id = #uid# and card_member.deleted=0  

4.         UNION  

5.       SELECT card_id FROM card_subscribed where user_id = #uid# and card_subscribed.deleted=0  

6.     )  

7.     GROUP BY r.object_id;  

即把语句给拆分成两个sql语，用in操作拼接

• 本机测试：

优化前执行时间1040ms，优化后执行时间：85ms，执行速度是原来的12倍多！赞

• PS：

常理我们都会排斥用in操作，用union替换，那为什么这里用in会更快呢？

带着问题，接着去网上找，原来：

sql执行会生成一个巨大的临时表，当内存放不下时，要全部copy 到磁盘，导致IO飙升，时间开销增大。

额外收获知识收藏如下：

• 临时表存储

MySQL临时表分为“内存临时表”和“磁盘临时表”，其中内存临时表使用MySQL的MEMORY存储引擎，磁盘临时表使用MySQL的MyISAM存储引擎；
一般情况下，MySQL会先创建内存临时表，但内存临时表超过配置指定的值后，MySQL会将内存临时表导出到磁盘临时表；

• 使用临时表的场景

1）ORDER BY子句和GROUP BY子句不同， 例如：ORDERY BY price GROUP BY name；
2）在JOIN查询中，ORDER BY或者GROUP BY使用了不是第一个表的列 例如：SELECT * from TableA, TableB ORDER BY TableA.price GROUP by TableB.name
3）ORDER BY中使用了DISTINCT关键字 ORDERY BY DISTINCT(price)
4）SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可。

• 直接使用磁盘临时表的场景

1）表包含TEXT或者BLOB列；
2）GROUP BY 或者 DISTINCT 子句中包含长度大于512字节的列；
3）使用UNION或者UNION ALL时，SELECT子句中包含大于512字节的列；

• 表的设计原则

使用临时表一般都意味着性能比较低，特别是使用磁盘临时表，性能更慢，因此我们在实际应用中应该尽量避免临时表的使用。 常见的避免临时表的方法有：
1）创建索引：在ORDER BY或者GROUP BY的列上创建索引；
2）分拆很长的列：一般情况下，TEXT、BLOB，大于512字节的字符串，基本上都是为了显示信息，而不会用于查询条件， 因此表设计的时候，应该将这些列独立到另外一张表。

• SQL优化

如果表的设计已经确定，修改比较困难，那么也可以通过优化SQL语句来减少临时表的大小，以提升SQL执行效率。常见的优化SQL语句方法如下：1）拆分SQL语句临时表主要是用于排序和分组，很多业务都是要求排序后再取出详细的分页数据，这种情况下可以将排序和取出详细数据拆分成不同的SQL，以降低排序或分组时临时表的大小，提升排序和分组的效率，我们的案例就是采用这种方法。2）优化业务，去掉排序分组等操作有时候业务其实并不需要排序或分组，仅仅是为了好看或者阅读方便而进行了排序，例如数据导出、数据查询等操作，这种情况下去掉排序和分组对业务也没有多大影响。

• 如何判断使用了临时表？

使用explain查看执行计划，Extra列看到Using temporary就意味着使用了临时表。

• 小结：

可见， 完全颠覆了对in操作符的认识，凡事儿都是要分情况讨论的

三、什么情况下会用到临时表

MySQL在以下几种情况会创建临时表：

1、UNION查询；
2、用到TEMPTABLE算法或者是UNION查询中的视图；
3、ORDER BY和GROUP BY的子句不一样时；
4、表连接中，ORDER BY的列不是驱动表中的；
5、DISTINCT查询并且加上ORDER BY时；
6、SQL中用到SQL_SMALL_RESULT选项时；
7、FROM中的子查询；
8、子查询或者semi-join时创建的表；

EXPLAIN 查看执行计划结果的 Extra 列中，如果包含 Using Temporary 就表示会用到临时表。

当然了，如果临时表中需要存储的数据量超过了上限（ tmp-table-size 或 max-heap-table-size 中取其小者），这时候就需要生成基于磁盘的临时表了。

在以下几种情况下，会创建磁盘临时表：

1、数据表中包含BLOB/TEXT列；
2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；
3、在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；
4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。

从5.7.5开始，新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB，而在这以前，只能使用 MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型，在以前默认是MEMORY，不要把这二者混淆了。

见下例：

mysql> set default_tmp_storage_engine = "InnoDB";-rw-rw----   1 mysql mysql  8558 Jul  7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的临时表-rw-rw----   1 mysql mysql 98304 Jul  7 15:22 #sql4b0e_10_0.ibd
-rw-rw----   1 mysql mysql  8558 Jul  7 15:25 #sql4b0e_10_2.frmmysql> set default_tmp_storage_engine = "MyISAM";-rw-rw----   1 mysql mysql     0 Jul  7 15:25 #sql4b0e_10_2.MYD -- MyISAM引擎的临时表-rw-rw----   1 mysql mysql  1024 Jul  7 15:25 #sql4b0e_10_2.MYImysql> set default_tmp_storage_engine = "MEMORY";-rw-rw----   1 mysql mysql  8558 Jul  7 15:26 #sql4b0e_10_3.frm -- MEMORY引擎的临时表

四、 临时表调优案例

作/译者：叶金荣（imysql#imysql.com>），来源：http://imysql.com，

引言：某客户新上线一个项目，利用存储过程处理用户登录相关事务。在存储过程中，需要对用户数据进行处理，于是他们采用临时表(temporary table)来做这个动作，先创建一个临时表，然后插入数据，处理；由于是采用连接池方式，担心临时表被复用，于是在最后删除该临时表。该客户采用16G的2950机器做mysql db server，利用loadrunner进行模拟登录测试，发现并发量达到2,30万之后，就再也上不去了，而且峰值不是很稳定的处于30多万的级别上。
一开始以为是机器性能达到了极限，经过询问各种状况后，认为应该还可以得到改进和优化。经过现场分析后，发现在测试达到峰值时，会有大量的 "waiting for table"，以及大量的 create temporary table 和 drop table 的线程在等待。很明显，瓶颈在于频繁的创建和删除临时表，mysql需要频繁的处理打开和关闭表描述符，才会导致了上面的问题。还好他们采用了连接池，否则情况将会更糟糕。建议他们把最后的 drop table 改成 truncate table，把临时表清空了，也就不会担心下一次调用时临时表不为空了，省去了频繁的处理表文件描述符，并发用户数也稳定的保持在了40多万。

附：上面提到的数字，仅作参考，均为该客户自行提供。

打赏

微信扫一扫，打赏作者吧～