La variable default_tmp_storage_engine se introdujo en 5.6.3, permitiendo la configuración del motor por defecto para las tablas temporales. Esto parece ir en la dirección, como he comentado con anterioridad, de convertir MyISAM en un motor opcional. En 5.7, se crea un espacio de tablas separado para guardar estas tablas con el objetivo de reducir su impacto en el rendimiento (esas tablas no tienen se rehacerse si el servidor falla de manera inesperada, por lo que se evitan escrituras extra).
Sin embargo, he visto mucha gente que asumía que porque el valor por defecto de default_tmp_storage_engine es “InnoDB”, todas las tablas temporales se crean en formato InnoDB. Esto no es cierto: primero, porque las tablas temporales implícitas se continúan creando en memoria usando el motor MEMORY (a veces denominado HEAP), mientras que MyISAM se usa para tablas en disco. Si no te fías para esto del manual de renferencia, aquí tienes una prueba rápida para comprobarlo:
mysql> SELECT version();
+------------+
| version() |
+------------+
| 5.6.23-log |
+------------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES like 'default%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| default_week_format | 0 |
+----------------------------+--------+
3 rows in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp |
+---------------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE test (id serial, a text);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into test (a) values ('a');
Query OK, 1 row affected (0.06 sec)
mysql> insert into test (a) values ('aa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (a) values ('aaa');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT *, sleep(10) FROM test ORDER BY rand();
...
[ec2-user@jynus_com tmp]$ ls -la
total 24
drwxrwxrwt 5 root root 4096 Feb 24 11:55 .
dr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
-rw-rw---- 1 mysql mysql 0 Feb 24 11:55 #sql_7bbd_0.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 24 11:55 #sql_7bbd_0.MYI
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
...
+----+------+-----------+
| id | a | sleep(10) |
+----+------+-----------+
| 1 | a | 0 |
| 2 | aa | 0 |
| 3 | aaa | 0 |
+----+------+-----------+
3 rows in set (30.00 sec)
La única cosa que he hecho aquí arriba es forzar la creación de la tabla temporal en disco añadiendo un campo de tipo TEXT (incompatible con el motor MEMORY, por lo que se fuerza su creación en disco) y usar sleep para tener el suficiente tiempo como para comprobar el sistema de archivos. En la salida de ls podéis observar los .MYD y .MYI particulares del motor MyISAM. El último paso sería innecesario si simplemente utilizaramos PERFORMANCE_SCHEMA para comprobar los waits/io.
Una segunda, y más obvia razón por la que pensar que todas las tablas temporales se crean en formato InnoDB es incorrecto, es porque las tablas temporales explícitas pueden seguir creándose en un motor distinto con la palabra clave ENGINE:
mysql> CREATE TEMPORARY TABLE test (i serial) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
[ec2-user@jynus_com tmp]$ ls -la
total 36
drwxrwxrwt 5 root root 4096 Feb 24 12:16 .
dr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
-rw-rw---- 1 mysql mysql 8554 Feb 24 12:12 #sql7bbd_36a3_0.frm
-rw-rw---- 1 mysql mysql 0 Feb 24 12:12 #sql7bbd_36a3_0.MYD
-rw-rw---- 1 mysql mysql 1024 Feb 24 12:12 #sql7bbd_36a3_0.MYI
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
mysql> DROP TEMPORARY TABLE test;
Query OK, 0 rows affected (0.00 sec)
[ec2-user@jynus_com tmp]$ ls -la
total 20
drwxrwxrwt 5 root root 4096 Feb 24 12:17 .
dr-xr-xr-x 23 root root 4096 Jan 28 14:09 ..
drwxrwxrwt 2 root root 4096 Jan 28 14:09 .ICE-unix
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:41 ssh-5ZGoXWFwtQ
drwx------ 2 ec2-user ec2-user 4096 Feb 24 11:43 ssh-w9IkW0SvYo
¿Cambiará esto en el futuro? 5.7.5 continua teniendo el mismo comportamiento que 5.6. Sin embargo, tal y como indicó Stewart hace un tiempo, las optimizaciones de rendimiento de 5.7 hacen que ciertos usos de MEMORY y MyISAM sean ahora obsoletos, por lo que no me sorprendería que esta dependencia -junto con la de las tablas de permisos MyISAM- desapareciera en un futuro.
Actualización: Morgan me ha contado por email que en la versión 5.7.6 (que todavía no se ha publicado en el momento de escribir estas líneas) cambiará finalmente el comportamiento por defecto para pasar a utilizar sólo InnoDB para las tables temporales implícitas tal y como se puede leer en las notas de actualización:
InnoDB: The default setting for the internal_tmp_disk_storage_engine option, which defines the storage engine the server uses for on-disk internal temporary tables (see How MySQL Uses Internal Temporary Tables), is now INNODB. With this change, the Optimizer uses the InnoDB storage engine instead of MyISAM for internal temporary tables.
que se puede traducir más o menos como:
InnoDB: el valor por defecto de la opción internal_tmp_disk_storage_engine option, que define el motor por defecto que el servidor usa para las tablas temporales internas en disco (véase Cómo utiliza MySQL tablas temporales), tiene ahora el valor INNODB. Con este cambio, el optimizador usa ahora el motor de almacenamiento InnoDB en vez de MyISAM para las tablas temporales internas.
internal_tmp_disk_storage_engine se introdujo en 5.7.5, pero su valor por defecto era MYISAM.
Esto permitirá una ventaja con el rendimiento de InnoDB en memoria para campos de tamaño variable, por lo que estoy 100% a favor. ¡Gracias Morgan por la información extra!