MySQL 临时表与dstat mysql临时表监控插件开发

MySQL在执行SQL查询时可能会用到临时表，一般而言，用到临时表就意味着性能较低。MySQL临时表可分：内存临时表和磁盘临时表，磁盘

临时表简介

MySQL在执行SQL查询时可能会用到临时表，一般而言，用到临时表就意味着性能较低。MySQL临时表可分：内存临时表和磁盘临时表，，磁盘临时表使用MyISAM存储，而内存临时表则使用Memory引擎。MySQL使用内存临时表来存放查询的中间结果集，如果中间结果集大于临时表的容量设定，又或者中间结果集含TEXT或BLOB列类型字段，则MySQL会把其转化为磁盘临时表。Linux平台缺省是/tmp,/tmp目录小的系统要注意啦。

临时表特性

下面列举几个内存临时表的特性

1.表结构(.frm)在磁盘，数据在内存

2.缺省使用哈希索引

3.定长存储(BTW：即使是varchar也是定长)

4.只支持表锁

5.不支持TEXT和BLOB列类型

相关参数

1 大小参数

MIN{tmp_table_size,max_heap_table_size}

mysql> show global variables like '%table_size';
--------------------- ----------
| Variable_name      | Value    |
--------------------- ----------
| max_heap_table_size | 16777216 |
| tmp_table_size      | 16777216 |
--------------------- ----------
2 rows in set (0.00 sec)

2 数量参数

mysql> show global status like 'created_tmp%';
------------------------- -------
| Variable_name          | Value |
------------------------- -------
| Created_tmp_disk_tables | 7    |
| Created_tmp_files      | 6    |
| Created_tmp_tables      | 90    |
------------------------- -------
3 rows in set (0.00 sec)

通过ps_helper我们可以细化这些临时表到底是谁的临时表

mysql> SELECT 查询、exec_count、memory_tmp_tables、disk_tmp_tables、avg_tmp_tables_per_query、tmp_tables_to_disk_pct FROM statements_with_temp_tables LIMIT 5;
------------------------------ ------------------------------------------------ ------------------------ - -------------------------------------------------- ----------- ------------------------------------
|查询                                                            |执行计数 |内存_tmp_表|磁盘临时表 | avg_tmp_tables_per_query | avg_tmp_tables_per_query | avg_tmp_tables_per_query | tmp_tables_to_disk_pct |
--------------------------------------------------------- ---------------------- ------------------------ ---------------- --- ----------------- -------------------------------------- ---- --------------------
| SELECT IF ( ( `locate` ( ? , ` ...  . `COMPRESSED_SIZE` ) ) DESC  |          2 |                4 |              2 |                        2 |                   50 |
| 选择 IF ( ( `定位` ( ? , ` ... MPRESSED_SIZE` = , , ... |        2 |            2 |              2 |                    50 | 🎜>| SELECT IF ( `isnull` ( `inform ... ` = `performance_schema` . ... |          2 | 4 |            2 |                      2 |                  50 |
| SELECT IF ( `isnull` (`通知 ... by_thread_by_event_name` . ... |          2 |               4 |            2 |                    2 |                  50 |
|状态 ... ` , `性能模式` . 2 |              2 |                                    50 |
-------------------- -------------------------------------------------------- ------ ------ -------------------------------- ----------------- -------- ------------------ ------------------------
集合中 5 行 ( 0.00 秒)