b u
c e p h a l u s
Mysql 性能分析之临时表
1
临时表与磁盘临时表 ............................................................................................................... 1 2
磁盘临时表的产生 ................................................................................................................... 1 3 临时表状态监控 .. (2)
3.1 主要指标 (2)
3.2 监控方法 ....................................................................................................................... 3 4
案例分析 (4)
1 临时表与磁盘临时表
临时文件大家都不陌生，就是为了各种不同的目的，产生的中间文件。使用完毕后会被及时的回收和清理。临时表也是如此，它是mysql 在进行一些内部操作的时候生成的数据库表。这些操作主要包括，group  by, distinct ，一些order  by  查询语句，UNION ，一些from 语句中的子查询（derived  tables ）等。例如：
● 使用了order  by 子句和一个不同的group  by 子句，或者order  by （或group  by ）包含了JOIN  queue 上非第一个表中的列，临时表将被创建。
● 使用了SQL_SMALL_RESULT 选项，mysql 会使用in ‐memory 临时表
● DISTINCT 和order  by 一起使用可能会用到临时表
可以使用EXPLAIN 来分析查询语句，看看是否会用到临时表。EXPLAIN 输出中的EXTRA 列会指明是否“Using  temporary ”。
事实上，大多数用户都不会去关注临时表的产生、使用与消亡的过程，因为它对用户是透明的。但是对于数据库管理员或者其他关心性能的人员而言，临时表就不得不引起注意，因为如果设置不当或者是程序使用不当，可能会产生大量的磁盘临时表，对系统性能产生很大的影响。
什么是磁盘临时表呢？
除了会直接产生磁盘临时表外，大量磁盘临时表是由内存临时表转化来的。临时表是存在于内存中，由MEMORY 引擎进行处理，速度较快。而磁盘临时表则是在磁盘上创建、使用、销毁的。由于磁盘是慢速访问设备，因此，磁盘临时表的操作效率要比临时表的操作差了几个数量级，具有较差的性能。因此，我们需要尽量避免磁盘临时表的产生。 2 磁盘临时表的产生
在使用中，有些时候是不能使用MEMORY 临时表的，而不得不使用MyISAM 引擎来处理临时表：
● 列中含有BLOB 或TEXT 字段
如果查询中包含BLOB 或者TEXT 列，临时表将直接被生成为MyISAM 表，因为这种类
b u
c e p h a l
u s
型的列不能存储在Memory 表中。
值得注意的是，一些设计上的问题可能会导致内存临时表的大小比想象中的大得多，Memory 表总是使
用固定长度的列，因此，假如varchar(255)类型的列中只存储了y ，n 这样的值，而在内存中占有的大小依然是255，如果是utf8编码，就是255*3 。这也是为什么要避免使用不必要的长度来存储内容的重要原因。
● ORDER  BY  或 DISTINCT 中出现了大于512bytes 的列
● 在使用UNION(ALL)的情况下，SELECT 子句中出现了大于512byte 的列
另外，当创建的MEMORY 临时表过大时，将被转换为MyISAM 表，并且存储在磁盘上。系统参数max_heap_table_size 决定了MEMORY 表大小的上限，适用于所有的内存表，包括使用CREATE  TABLE 创建的表。但是，对于内部临时MEMORY 表而言，其大小还有受限于tmp_table_size 。也就是说，内存临时表的上限实际上是max_heap_table_size 和tmp_table_size 二者中的最小值。当MEMORY 表大小超过最大值时，将被自动转换为存储在磁盘上的磁盘临时表，使用MyISAM 引擎进行处理。
从mysql 的临时文件夹中可以查看是否会产生磁盘临时表，磁盘临时表是以#开头的文件。下面是每秒一次产生的一段查看快照：
3 临时表状态监控
3.1 主要指标
在性能测试的过程中，临时磁盘表的大量产生会造成较多的IO ，使系统性能大幅下降。和磁盘临时表相关的主要参数有：
● MAX_HEAP_TABLE_SIZE
MEMORY 表大小的最大值，通过这个值来计算MEMORY 表的最大行数
● MAX_TMP_TABLES
b u
c e p h a l u s
一个客户端同时打开的临时表个数限制
● TMP_TABLE_SIZE
临时表的最大值。超过了这个值，内存中的MEMORY 表将被自动转换为磁盘上的MyISAM 表。
● CREATED_TMP_DISK_TABLES
创建的磁盘临时表的个数。
● CREATED_TMP_TABLES
创建的内存临时表的个数
下面是一次性能测试过程中，对临时表性能监控的快照。
在性能测试的过程中是通过100*（磁盘临时表个数/(磁盘临时表个数+内存临时表个数)），也就是磁盘临时表占临时表的比例来衡量这个指标的健康程度的。
3.2 监控方法
在测试过程中，可以通过show  status 命令的输出来查看临时表的状态变量，可以通过show  variables 来查看系统参数的设置。
如果想观察一段时间内指标的变化，可以借助脚本或者第三方工具来进行查看。Mysql  manager 就是一个不错的开源工具，它提供了丰富的管理、监控功能。通过它我们可以方便的查看状态变量、系统参数，还可以进行自定义图表，通过定义自己的公式来添加关心的指标。
官方下载地址：sql/downloads/gui ‐tools/5.0.html
b u c
e p h a l u s
4 案例分析
在维护模块客户报表批量下载功能的测试中，发现max_heap_table_size 的值为32M ，而tmp_table_size 的值为512M 。有大量的磁盘临时表产生。mysql group by order by
根据前面的分析，可能会是由于max_heap_table_size 参数过小导致。于是将max_heap_table_size 的值调整为512M 。挑选同一个任务，进行测试，同一个任务调整前后磁盘临时表的比率分别在90%和10%。说明参数的调整大大减少了磁盘临时表的产生，提高了操作效率。
优化前后对比如下：
通过提高该参数的值，可以解决大量磁盘临时表的问题，但是应当注意的有两点：
1. 参数设置只是一部分，相当一部分磁盘表的产生是由与SQL 和数据库的不合理设计造成的，比如含有TEXT 字段，这也是我们进行优化和问题分析的重点。
2. 不能盲目调大临时表的参数，因为内存是有限的，需要从全局来考虑。对参数的调整要进行系统的测试，以获得整体性能的提升。
b u
c e p h a l u s