mysql 参数调优（10）之 tmp_table_size

https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html

实践

tmp_table_size默认16M。tmp_table_size如果过小，存不下了就会存到磁盘上。对于group by会有性能影响。

下面的sql EXPLAIN 如下，出现了Using temporary。表示查询会利用临时表。

(root@localhost) [dbt3]>EXPLAIN select date_format(o_orderDATE,'%Y-%m'),o_clerk,count(1),sum(o_totalprice),avg(o_totalprice) from orders group by date_format(o_orderDATE,'%Y-%m'),o_clerk
k;
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                           |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1372000 |   100.00 | Using temporary; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

在默认tmp_table_size大小16M下执行：

SELECT
    date_format( o_orderDATE, '%Y-%m' ),
    o_clerk,
    count( 1 ),
    sum( o_totalprice ),
    avg( o_totalprice ) 
FROM
    orders 
GROUP BY
    date_format( o_orderDATE, '%Y-%m' ),
    o_clerk;
79710 rows in set (7.34 sec)

查看临时表统计信息，Created_tmp_disk_tables 为0，Created_tmp_tables 为1表示上诉sql执行后生产了一张内存里的临时表。

(root@localhost) [dbt3]>show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+

Created_tmp_disk_tables 产生基于磁盘临时表数量
Created_tmp_tables 产生临时表数量

将tmp_table_size 调从16M调整为16K

set tmp_table_size = 16*1024

再次执行,查询时间从4变成了18秒

SELECT
    date_format( o_orderDATE, '%Y-%m' ),
    o_clerk,
    count( 1 ),
    sum( o_totalprice ),
    avg( o_totalprice ) 
FROM
    orders 
GROUP BY
    date_format( o_orderDATE, '%Y-%m' ),
    o_clerk;
79710 rows in set (18.58 sec)

重新统计

flush status;

再次查看status，这次有在磁盘上创建1个临时表。

(root@localhost) [dbt3]>show status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 3     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

查看全局

(root@localhost) [dbt3]>show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 75    |
| Created_tmp_files       | 3     |
| Created_tmp_tables      | 2840  |
+-------------------------+-------+

对配置的建议

设置为32M

[mysqld]
tmp_table_size=32M

其他的

Percona Server中的临时表信息会记录到慢查询日志
由于MySQL慢查询日志里没有使用临时表的信息，这就给我们诊断性能问题带来了一些不便，第三方的版本如Percona Server，在慢查询里可以有更详细的信息，将会记录临时表使用的情况，从而有助于我们诊断和调优。

mysql8中对临时表有较大的优化
临时表引擎使用innodb（default 磁盘）和temptable(default 内存)