MySQL 临时表的问题

[silenceeeee]

如下表结构:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column1` varchar(10) DEFAULT NULL,
  `column2` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

用 EXPLAIN 查看:

EXPLAIN SELECT  * FROM test ORDER BY column1

显示不会创建临时表

而

EXPLAIN SELECT  * FROM test GROUP BY column1

显示会创建临时表

为什么?

以下是官方关于什么情况下会创建临时表的说明:

In some cases, the server creates internal temporary tables while processing statements. Users have no direct control over when this occurs.

The server creates temporary tables under conditions such as these:

Evaluation of UNION statements, with some exceptions described later.

Evaluation of some views, such those that use the TEMPTABLE algorithm, UNION, or aggregation.

Evaluation of derived tables (subqueries in the FROM clause).

Tables created for subquery or semi-join materialization (see Section 9.2.1.18, “ Subquery Optimization ”).

Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

Evaluation of DISTINCT combined with ORDER BY may require a temporary table.

For queries that 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.

Evaluation of multiple-table UPDATE statements.

Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions.