HOW MYSQL USES INTERNAL TEMPORARY TABLES

时间：2023-12-21 13:33:50

HOW MYSQL USES INTERNAL TEMPORARY TABLES

通过阅读MySQL的官方手册，我们可以知道出现下面情况的时候，MySQL可能会使用临时表，下面的例子都在MySQL5.6版本下面进行测试,创建测试数据表结构：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> show create table Test_Update_Table \G
*************************** 1. row ***************************
       Table: Test_Update_Table
Create Table: CREATE TABLE `Test_Update_Table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registerdate` date NOT NULL DEFAULT '0000-00-00',
  `key1` varchar(10) NOT NULL DEFAULT '',
  `key2` varchar(10) NOT NULL DEFAULT '',
  `key3` varchar(10) NOT NULL DEFAULT '',
  `key_4` varchar(255) DEFAULT NULL,
  `ke5` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_key2` (`key2`),
  KEY `idx_key1_key1` (`key1`,`key2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i1` int(11) NOT NULL DEFAULT '0',
  `i2` int(11) NOT NULL DEFAULT '0',
  `d` date DEFAULT NULL,
  `remark_1` varchar(128) DEFAULT NULL,
  `remark_2` varchar(513) DEFAULT NULL,
  PRIMARY KEY (`i1`,`i2`),
  KEY `k_d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>

1）UNION queries

UNION queries use temporary tables.使用UNION语句的时候会使用临时表，在MySQL5.5版本以前explain的extra是不显示Using temporary信息的，但在5.6版本以后会有该信息显示，无论哪个版都都会增加.eg:

1
2
3
4
5
6
7
8
9
mysql> desc select 1 union select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
|  2 | UNION        | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | No tables used  |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.00 sec)

2）Some views

Some views require temporary tables, such those evaluated using the TEMPTABLE algorithm, or that use UNION or aggregation.一些使用临时表算法或者Union,聚合函数，临时表算法的视图，eg:

1、Union 算法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> create view v_Test_Update_Table as select * from Test_Update_Table where key1 =1 union  select * from Test_Update_Table where key1 =22;
Query OK, 0 rows affected (0.06 sec)
mysql> flush status
    -> ;
Query OK, 0 rows affected (0.00 sec)
mysql> desc select * from v_Test_Update_Table;
+----+--------------+-------------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table             | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+-------------------+------+---------------+------+---------+------+------+-----------------+
|  1 | PRIMARY      | <derived2>        | ALL  | NULL          | NULL | NULL    | NULL |    6 | NULL            |
|  2 | DERIVED      | Test_Update_Table | ALL  | idx_key1_key1 | NULL | NULL    | NULL |    3 | Using where     |
|  3 | UNION        | Test_Update_Table | ALL  | idx_key1_key1 | NULL | NULL    | NULL |    3 | Using where     |
| NULL | UNION RESULT | <union2,3>        | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+-------------------+------+---------------+------+---------+------+------+-----------------+
4 rows in set (0.00 sec)

2、聚合函数，可能在explain中看不到Using temporary状态，其实derived2就是一个临时表，一定要注意

1
2
3
4
5
6
7
8
9
10
11
mysql> create view v_ag_Test_Update_Table as select key1, count(*) as cnt from Test_Update_Table group by key1;
Query OK, 0 rows affected (0.05 sec)
mysql> desc select * from v_ag_Test_Update_Table;
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | PRIMARY     | <derived2>        | ALL   | NULL          | NULL          | NULL    | NULL |    3 | NULL        |
|  2 | DERIVED     | Test_Update_Table | index | idx_key1_key1 | idx_key1_key1 | 64      | NULL |    3 | Using index |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
2 rows in set (0.00 sec)

3、临时表算法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> create view  v_all as select * from Test_Update_Table;
Query OK, 0 rows affected (0.04 sec)
mysql> create ALGORITHM=TEMPTABLE view v_all_temp as select * from Test_Update_Table;
Query OK, 0 rows affected (0.03 sec)
mysql> desc select * from v_all;
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> desc select * from v_all_temp;
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2>        | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

3）SQL_SMALL_RESULT

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选项的query，SELECT语句中指定了SQL_SMALL_RESULT关键字 SQL_SMALL_RESULT的意思就是告诉MySQL，结果会很小，请直接使用内存临时表，不需要使用索引排序 SQL_SMALL_RESULT必须和GROUP BY、DISTINCT或DISTINCTROW一起使用 一般情况下，我们没有必要使用这个选项，让MySQL服务器选择即可，这个例子我没有做出来

4) Multiple-table UPDATE

Multiple-table UPDATE statements,多表的update 查询，该查询在extra也不显示Using temporary的状态信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
mysql> show status like 'create%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 0     |
+-------------------------+-------+
3 rows in set (0.00 sec)
mysql> desc  update Test_Update_Table a,t1 b set  b.d =current_date() where a.id = b.i1 and a.id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY | 4       | const |    1 | Using index |
|  1 | SIMPLE      | b     | ref   | PRIMARY       | PRIMARY | 4       | const |    5 | NULL        |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
mysql>  update Test_Update_Table a,t1 b set  b.d =current_date() where a.id = b.i1 and a.id=1;
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5  Changed: 5  Warnings: 0
mysql> show status like 'create%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

5）Derived tables

Derived tables (subqueries in the FROM clause).使用Derived tables，一般情况子查询会出现该情况，不过5.6版本有改进，可以查阅相关文档

1
2
3
4
5
6
7
8
mysql> desc select * from (select * from Test_Update_Table )a;
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2>        | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
|  2 | DERIVED     | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | NULL  |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)

6）subquery or semi-join

Tables created for subquery or semi-join , 这个主要是为子查询和物化semi-join创建的表，这个例子也没有做出来

7）order by,group by,distinct

最终要的就是group by ，distinct， order by这几组关键字的配合，会出现临时表，文件排序，我来总结一下：

1、单表情况
1.单表无索引（order by，group by，distinct列无可用索引），order by 单列或者多列都不使用临时表，只用到Using filesort， show status like ‘Created_tmp_tables’的计数器也不变，group by， distinct 不管是单列还是多列，均使用临时表，下面是例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> desc select * from `Test_Update_Table` order by key_4 ;
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql> desc select * from `Test_Update_Table` order by key_4,ke5 ;
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql> desc select key_4 from `Test_Update_Table` group by key_4 ;
+----+-------------+-------------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
mysql> desc select key_4 from `Test_Update_Table` group by key_4,ke5 ;
+----+-------------+-------------------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------------------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+---------------------------------+
1 row in set (0.00 sec)
mysql> desc select distinct key_4 from `Test_Update_Table`;
+----+-------------+-------------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)
mysql> desc select distinct key_4 ,ke5 from `Test_Update_Table`;
+----+-------------+-------------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-----------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary |
+----+-------------+-------------------+------+---------------+------+---------+------+------+-----------------+
1 row in set (0.00 sec)

2.单表有索引情况（order by，group by，distinct列有可用索引）在能够使用索引的情况下，那么都不会出现使用临时表的状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
mysql> desc select * from Test_Update_Table group by key1;
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------+
| id | select_type | table             | type  | possible_keys | key           | key_len | ref  | rows | Extra |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------+
|  1 | SIMPLE      | Test_Update_Table | index | idx_key1_key1 | idx_key1_key1 | 64      | NULL |    3 | NULL  |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> desc select * from Test_Update_Table group by key1,key2;
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------+
| id | select_type | table             | type  | possible_keys | key           | key_len | ref  | rows | Extra |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------+
|  1 | SIMPLE      | Test_Update_Table | index | idx_key1_key1 | idx_key1_key1 | 64      | NULL |    3 | NULL  |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> desc select distinct key1 ,key2 from Test_Update_Table;
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | Test_Update_Table | index | idx_key1_key1 | idx_key1_key1 | 64      | NULL |    3 | Using index |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> desc select distinct key1  from Test_Update_Table;
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table             | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | Test_Update_Table | index | idx_key1_key1 | idx_key1_key1 | 64      | NULL |    3 | Using index |
+----+-------------+-------------------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> desc select *  from Test_Update_Table order by key1;
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
mysql> desc select *  from Test_Update_Table order by key1,key2;
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | Test_Update_Table | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

通过上面的例子，我们可以看出来，无论有无索引可以使用，在单表情况下，order by是不影响临时表的使用的，对于 group by， distinct后面只要有非使用上的索引列的字段，必然会使用临时表

2)多表情况
1.多表无可用索引（order by，group by，distinct列无可用索引），只有对驱动表字段排序事才会不使用临时表，其他情况均要使用临时表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> desc select * from t1 a,Test_Update_Table b where a.i1 =b.id order by a.i2;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | NULL                            |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
2 rows in set (0.00 sec)
mysql> desc select * from t1 a,Test_Update_Table b where a.i1 =b.id order by b.key3;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra          |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | NULL           |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------------+
2 rows in set (0.00 sec)
mysql> desc select b.key3 from t1 a,Test_Update_Table b where a.i1 =b.id group by b.key3;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | Using index                     |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
2 rows in set (0.00 sec)
mysql> desc select a.i2 from t1 a,Test_Update_Table b where a.i1 =b.id group by a.i2;
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref         | rows | Extra                                        |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY       | idx_key2 | 32      | NULL        |    3 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref   | PRIMARY,k_d   | PRIMARY  | 4       | testdb.b.id |    2 | Using index                                  |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql> desc select distinct key3 from t1 a,Test_Update_Table b where a.i1 =b.id ;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra                 |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using temporary       |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | Using index; Distinct |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+-----------------------+
2 rows in set (0.00 sec)
mysql> desc select distinct i2 from t1 a,Test_Update_Table b where a.i1 =b.id ;
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref         | rows | Extra                        |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY       | idx_key2 | 32      | NULL        |    3 | Using index; Using temporary |
|  1 | SIMPLE      | a     | ref   | PRIMARY,k_d   | PRIMARY  | 4       | testdb.b.id |    2 | Using index                  |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+------------------------------+
2 rows in set (0.00 sec)

2.多表有可用索引（order by，group by，distinct列有可用索引）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
mysql> desc select * from t1 a,Test_Update_Table b where a.i1 =b.id order by b.key2,key3;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra          |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | NULL           |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+----------------+
2 rows in set (0.00 sec)
mysql> desc select * from t1 a,Test_Update_Table b where a.i1 =b.id order by b.key2,a.i2;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | NULL                            |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
2 rows in set (0.00 sec)
mysql> desc select * from t1 a,Test_Update_Table b where a.i1 =b.id order by a.i2;
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref         | rows | Extra                           |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY       | NULL    | NULL    | NULL        |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY       | PRIMARY | 4       | testdb.b.id |    2 | NULL                            |
+----+-------------+-------+------+---------------+---------+---------+-------------+------+---------------------------------+
2 rows in set (0.00 sec)
mysql> desc  select key1,key2,key3 from t1 a,Test_Update_Table b where a.i1 =b.id group  by key1 ;
+----+-------------+-------+------+-----------------------+---------+---------+-------------+------+---------------------------------+
| id | select_type | table | type | possible_keys         | key     | key_len | ref         | rows | Extra                           |
+----+-------------+-------+------+-----------------------+---------+---------+-------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL  | PRIMARY,idx_key1_key1 | NULL    | NULL    | NULL        |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | PRIMARY               | PRIMARY | 4       | testdb.b.id |    2 | Using index                     |
+----+-------------+-------+------+-----------------------+---------+---------+-------------+------+---------------------------------+
2 rows in set (0.00 sec)
mysql> desc  select key1,key2 from t1 a,Test_Update_Table b where a.i1 =b.id group  by key1,key2 ;
+----+-------------+-------+-------+-----------------------+---------------+---------+-------------+------+-------------+
| id | select_type | table | type  | possible_keys         | key           | key_len | ref         | rows | Extra       |
+----+-------------+-------+-------+-----------------------+---------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | b     | index | PRIMARY,idx_key1_key1 | idx_key1_key1 | 64      | NULL        |    3 | Using index |
|  1 | SIMPLE      | a     | ref   | PRIMARY               | PRIMARY       | 4       | testdb.b.id |    2 | Using index |
+----+-------------+-------+-------+-----------------------+---------------+---------+-------------+------+-------------+
2 rows in set (0.00 sec)
mysql> desc  select i1 from t1 a,Test_Update_Table b where a.i1 =b.id group  by i1 ;
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref         | rows | Extra                                        |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY       | idx_key2 | 32      | NULL        |    3 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref   | PRIMARY,k_d   | PRIMARY  | 4       | testdb.b.id |    2 | Using index                                  |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+----------------------------------------------+
2 rows in set (0.00 sec)
mysql> desc select distinct i1 from t1 a,Test_Update_Table b where a.i1 =b.id;
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref         | rows | Extra                        |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY       | idx_key2 | 32      | NULL        |    3 | Using index; Using temporary |
|  1 | SIMPLE      | a     | ref   | PRIMARY,k_d   | PRIMARY  | 4       | testdb.b.id |    2 | Using index                  |
+----+-------------+-------+-------+---------------+----------+---------+-------------+------+------------------------------+
2 rows in set (0.00 sec)
mysql> desc select distinct key1 from t1 a,Test_Update_Table b where a.i1 =b.id;
+----+-------------+-------+-------+-----------------------+---------------+---------+-------------+------+------------------------------+
| id | select_type | table | type  | possible_keys         | key           | key_len | ref         | rows | Extra                        |
+----+-------------+-------+-------+-----------------------+---------------+---------+-------------+------+------------------------------+
|  1 | SIMPLE      | b     | index | PRIMARY,idx_key1_key1 | idx_key1_key1 | 64      | NULL        |    3 | Using index; Using temporary |
|  1 | SIMPLE      | a     | ref   | PRIMARY               | PRIMARY       | 4       | testdb.b.id |    2 | Using index; Distinct        |
+----+-------------+-------+-------+-----------------------+---------------+---------+-------------+------+------------------------------+
2 rows in set (0.00 sec)

从上面来看，多表操作的时候， 当order by ，group by 后面出现了非驱动表的可使用索引字段，那么必然会使用临时表，而select后面的字段出现了非可用索引字段的时候，也要使用到临时表，select后面的字段不影响order by的索引使用情况
对于distinct，除非通过group有效的消除，否则多表连接必然会出现使用临时表.

参考文章:

1) http://dev.mysql.com/doc/refman/5.6/en/internal-temporary-tables.html

相关文章