第二章 mysql性能分析之临时表(共享).pdfVIP

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 表，因为这种类 Author: beiyu95 msn:beiyu95@  型的列不能存储在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