从MySQL临时表谈到filesort

内部临时表的类型和产生时机相关，翻译自：http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

In some cases, the server creates internal temporary tables while processing queries. Such a table can be held in memory and processed by the MEMORY storage engine, or stored on disk and processed by the MyISAM storage engine. The server may create a temporary table initially as an in-memory table, then convert it to an on-disk table if it becomes too large. Users have no direct control over when the server creates an internal temporary table or which storage engine the server uses to manage it.

有时候数据库服务器在执行某些查询的时候会生成内部临时表，这些临时表有可能是生成在内存里的由MEMORY引擎处理的，也有可能是生成在磁盘上由MyISAM引擎处理的。如果说在内存中的临时表大小超过限制，服务器则会将临时表保存成磁盘临时表。用户无法直接控制这些内部临时表和管理这些临时表的数据库引擎。

Temporary tables can be created under conditions such as these:

内部临时表产生的时机有以下几种：

• If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.

• 使用 ORDER BY 子句和一个不一样的 GROUP BY 子句（经过笔者实验，应该是GROUP BY一个无索引列,就会产生临时表），或者 ORDER BY 或 GROUP BY 的列不是来自JOIN语句序列的第一个表，就会产生临时表(经笔者实验，应该是使用JOIN时， GROUP BY 任何列都会产生临时表)

• DISTINCT combined with ORDER BY may require a temporary table.

• DISTINCT 和 ORDER BY 一起使用时可能需要临时表（笔者实验是只要用了DISTINCT（非索引列），都会产生临时表）

• If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.

• 用了 SQL_SMALL_RESULT, mysql就会用内存临时表。定义：SQL_BIG_RESULT or SQL_SMALL_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set has many rows or is small, respectively. For SQL_BIG_RESULT, MySQL directly uses disk-based temporary tables if needed, and prefers sorting to using a temporary table with a key on the GROUP BY elements. For SQL_SMALL_RESULT, MySQL uses fast temporary tables to store the resulting table instead of using sorting. This should not normally be needed. 

 

To determine whether a query requires a temporary table, use EXPLAIN and check the Extra column to see whether it says Using temporary. See Section 8.8.1, “Optimizing Queries with EXPLAIN”.

可以用EXPLAIN来查看Extra字段判断是否使用了临时表

Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

有些情况服务器会直接使用磁盘临时表

• Presence of a BLOB or TEXT column in the table

• 表里存在BLOB或者TEXT的时候(这是因为MEMORY引擎不支持这两种数据类型，这里笔者补充一下，并非只要查询里含有BLOB和TEXT类型的列就会产生磁盘临时表，按照高性能MYSQL里的话，应该这么说：“Because the Memory storage engine doesn't support the BLOB and TEXT types, queries that use BLOB or TEXT columns and need an implicit temporary table will have to use on-disk MyISAM temporry tables, even for only a few rows.”也就是说如果我们的查询中包含了BLOB和TEXT的列，而且又需要临时表，这时候临时表就被强制转成使用磁盘临时表，所以此书一直在提醒我们，如果要对BLOB和TEXT排序，应该使用SUBSTRING（column， length）将这些列截断变成字符串，这样就可以使用in-memory临时表了)

• Presence of any column in a GROUP BY or DISTINCT clause larger than 512 bytes

• GROUP BY 或者 DISTINCT 子句大小超过 512 Bytes

• Presence of any column larger than 512 bytes in the SELECT list, if UNION or UNION ALL is used

• 使用了UNION 或 UNION ALL 并且 SELECT 的列里有超过512 Bytes的列

If an internal temporary table is created initially as an in-memory table but becomes too large, MySQL automatically converts it to an on-disk table. The maximum size for in-memory temporary tables is the minimum of the tmp_table_size and max_heap_table_size values. This differs from MEMORY tables explicitly created with CREATE TABLE: For such tables, the max_heap_table_size system variable determines how large the table is permitted to grow and there is no conversion to on-disk format.

如果内置内存临时表创建后变得太大，MySQL会自动将它转换成磁盘临时表。内存临时表的大小取决与 tmp_table_size参数和max_heap_table_size参数的值。用 CREATE TABLE 产生的内存临时表的大小取决与 max_heap_table_size来决定是否要将其转换成磁盘临时表

When the server creates an internal temporary table (either in memory or on disk), it increments theCreated_tmp_tables status variable. If the server creates the table on disk (either initially or by converting an in-memory table) it increments the Created_tmp_disk_tables status variable.

当服务器生成一个内存临时表，Created_tmp_tables状态变量值会增加，当服务器创建了一个磁盘临时表时，Created_tmp_disk_tables状态变量值会增加。(这几个变量可以通过 show status命令查看得到)

Tips: internal temporaray table 的大小受限制的是tmp_table_size和max_heap_table_size的最小值；而 user-created temporary table的大小只受限与max_heap_table_size,而与tmp_table_size无关。以下是文档原文，注意粗体部分

 tmp_table_size

Command-Line Format	--tmp_table_size=#
Option-File Format	tmp_table_size
Option Sets Variable	Yes, tmp_table_size
Variable Name	tmp_table_size
Variable Scope	Global, Session
Dynamic Variable	Yes
	Permitted Values
	Type	numeric
	Default	system dependent
	Range	1024 .. 4294967295

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum oftmp_table_size and max_heap_table_size.) If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table. Increase the value of tmp_table_size (andmax_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory. This variable does not apply to user-created MEMORY tables.

You can compare the number of internal on-disk temporary tables created to the total number of internal temporary tables created by comparing the values of the Created_tmp_disk_tables andCreated_tmp_tables variables.

See also Section 8.4.3.3, “How MySQL Uses Internal Temporary Tables”.

 max_heap_table_size

Command-Line Format	--max_heap_table_size=#
Option-File Format	max_heap_table_size
Option Sets Variable	Yes, max_heap_table_size
Variable Name	max_heap_table_size
Variable Scope	Global, Session
Dynamic Variable	Yes
	Permitted Values
	Platform Bit Size	32
	Type	numeric
	Default	16777216
	Range	16384 .. 4294967295
	Permitted Values
	Platform Bit Size	64
	Type	numeric
	Default	16777216
	Range	16384 .. 1844674407370954752

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow. The value of the variable is used to calculate MEMORY table MAX_ROWS values. Setting this variable has no effect on any existingMEMORY table, unless the table is re-created with a statement such as CREATE TABLE or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the globalmax_heap_table_size value.

This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. SeeSection 8.4.3.3, “How MySQL Uses Internal Temporary Tables”.

max_heap_table_size is not replicated. See Section 16.4.1.21, “Replication and MEMORY Tables”, andSection 16.4.1.34, “Replication and Variables”, for more information.

————————————————————————————————————————————————————————————————————

 

下面来说说filesort。什么是filesort？翻译一篇来自 Baron Schwartz的blog，他是 High Performance MySQL的第一作者

If you were interviewing to work at Percona, and I asked you “what does Using filesort mean in EXPLAIN,” what would you say?

I have asked this question in a bunch of interviews so far, with smart people, and not one person has gotten it right. So I consider it to be a bad interview question, and I’m going to put the answer here. If anyone gets it wrong from now on, I know they don’t read this blog!

 这段在吹牛比。

The usual answer is something like “rows are being placed into a temporary table which is too big to fit in memory, so it gets sorted on disk.” Unfortunately, this is not the same thing. First of all, this is Using temporary. Secondly, temporary tables may go to disk if they are too big, but EXPLAIN doesn’t show that. (If I interview you, I might ask you what “too big” means, or I might ask you the other reason temporary tables go to disk!)

一般人的回答是： “当行数据太大，导致内存无法容下这些数据产生的临时表时，他们就会被放入磁盘中排序。”  很不幸，这个答案是错的。首先，这个叫做 Using temporary (参见Expain 或者 DESC里的Extra字段)；第二，临时表在太大的时候确实会到磁盘离去，但是EXPLAIN不会显示这些。 (Bala bala bala...)

The truth is, filesort is badly named. Anytime a sort can’t be performed from an index, it’s a filesort. It has nothing to do with files. Filesort should be called “sort.” It is quicksort at heart.

那么事实是， filesort 这个名字取得太搓逼了。 filesort的意思是只要一个排序无法使用索引来排序，就叫filesort。他和file没半毛钱关系。filesort应该叫做sort。（笔者补充一下：意思是说如果无法用已有index来排序，那么就需要数据库服务器额外的进行数据排序，这样其实是会增加性能开销的。）

If the sort is bigger than the sort buffer, it is performed a bit at a time, and then the chunks are merge-sorted to produce the final sorted output. There is a lot more to it than this. I refer you to Sergey Petrunia’s article on How MySQL executes ORDER BY.You can also read about it in our book, but if you read Sergey’s article you won’t need to.

如果说sort数据比sort buffer还大，那么排序会分解成多部分，每次排序一小部分，最后将各部分合并后输出(你就是想说合并排序吧)。这里面还有很多东西可说哦。推荐你去看xxxxx，或者是看我们的书(High Performance MySQL，不得不说确实是好书)。bala bala。。。

OK。现在了解神码是filesort了，实际上确实这玩意和临时表和文件没半毛钱关系。大家可以试试用 order by 一个无索引的列，Extra里就会出现 Using filesort了

。。。额。。。我是为毛把临时表和filesort放在一起来了？