MySQL 5.7では暗黙のテンポラリーテーブルにもInnoDBが使われる

取り敢えずダミーデータを突っ込んだテーブルを自己結合しつつぐりぐりソートしてテンポラリーテーブルを作らせる。

$ perl -M"Digest::MD5 'md5_hex'" -e 'for ($n = 1; $n <= 1000000; $n++) { printf("%d\t%s\n", $n, md5_hex($n)); }' > /tmp/md5

mysql> create table t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> LOAD DATA INFILE '/tmp/md5' INTO TABLE t1;
Query OK, 1000000 rows affected (8.88 sec)
Records: 1000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql> explain SELECT * FROM t1 LEFT JOIN t1 AS t2 USING(num) LEFT JOIN t1 AS t3 USING(num) ORDER BY t1.val ASC, t2.val DESC, t3.val ASC;
+----+-------------+-------+------------+--------+---------------+------+---------+-----------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key  | key_len | ref       | rows   | filtered | Extra                      |
+----+-------------+-------+------------+--------+---------------+------+---------+-----------+--------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | NULL          | NULL | NULL    | NULL      | 996250 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | num           | num  | 8       | d1.t1.num |      1 |   100.00 | NULL                            |
|  1 | SIMPLE      | t3    | NULL       | eq_ref | num           | num  | 8       | d1.t1.num |      1 |   100.00 | NULL                      |
+----+-------------+-------+------------+--------+---------------+------+---------+-----------+--------+----------+---------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                          |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val`,`d1`.`t2`.`val` AS `val`,`d1`.`t3`.`val` AS `val` from `d1`.`t1` left join `d1`.`t1` `t2` on((`d1`.`t1`.`num` = `d1`.`t2`.`num`)) left join `d1`.`t1` `t3` on((`d1`.`t1`.`num` = `d1`.`t3`.`num`)) where 1 order by `d1`.`t1`.`val`,`d1`.`t2`.`val` desc,`d1`.`t3`.`val` |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.7.6。

mysql> SELECT * FROM t1 LEFT JOIN t1 AS t2 USING(num) LEFT JOIN t1 AS t3 USING(num) ORDER BY t1.val ASC, t2.val DESC, t3.val ASC;
1000000 rows in set (38.72 sec)

# pt-ioprofile
Tue Mar 10 17:21:50 JST 2015
Tracing process ID 15352
     total      pread     pwrite      lseek filename
  0.092978   0.000000   0.092895   0.000083 /usr/local/mysql/data/ibtmp1
  0.000578   0.000578   0.000000   0.000000 /usr/local/mysql/data/d1/t1.ibd

ibtmp1はテンポラリーテーブル専用のテーブルスペースファイルで、REDOログを書かない(テンポラリーテーブルはクラッシュリカバリーされないため)


5.6.23。

mysql [localhost] {msandbox} (d1) > SELECT * FROM t1 LEFT JOIN t1 AS t2 USING(num) LEFT JOIN t1 AS t3 USING(num) ORDER BY t1.val ASC, t2.val DESC, t3.val ASC;
1000000 rows in set (24.80 sec)

# pt-ioprofile
Tue Mar 10 17:26:44 JST 2015
Tracing process ID 15829
     total      pread       read      write       open      close      lseek filename
  9.197881   8.124282   0.000000   1.009717   0.000035   0.063840   0.000007 /home/mysql/sandboxes/msb_5_6_23/tmp/MY7xramM
  6.187275   5.180143   0.000000   0.933478   0.000064   0.073582   0.000008 /home/mysql/sandboxes/msb_5_6_23/tmp/MYK6H2em
  1.000256   0.000000   0.129345   0.863924   0.006977   0.000000   0.000010 /home/mysql/sandboxes/msb_5_6_23/tmp/MYWMZd7b
  0.304381   0.000000   0.216208   0.088095   0.000035   0.000012   0.000031 /home/mysql/sandboxes/msb_5_6_23/tmp/#sql_3dd5_0.MYD
  0.019654   0.000000   0.000014   0.000084   0.019523   0.000020   0.000013 /home/mysql/sandboxes/msb_5_6_23/tmp/#sql_3dd5_0.MYI
  0.000195   0.000000   0.000030   0.000080   0.000039   0.000040   0.000006 /home/mysql/sandboxes/msb_5_6_23/tmp/MY2rane3

いつもどおり、MyISAMなテンポラリーテーブルを作ってる。


【2015/04/30 15:24】
ibtmp1はmysqldが再起動されるまでサイズが小さくなりはしないので、暗黙のテンポラリーテーブルがあふれると死ぬ