copy to tmp table与tmp_table_size

mysql默认情况下:文章源自靠谱运维-https://www.ixdba.net/archives/1242

 文章源自靠谱运维-https://www.ixdba.net/archives/1242

tmp_table_size 33554432文章源自靠谱运维-https://www.ixdba.net/archives/1242

max_heap_table_size 16777216文章源自靠谱运维-https://www.ixdba.net/archives/1242

copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时,参数中设置的临时表的大小小于结果集的大小时,就会将该表放在磁盘上,这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时,则系统会把 max_heap_table_size的值作为最大的内存临时表的上限,大于这个时,改写硬盘。文章源自靠谱运维-https://www.ixdba.net/archives/1242

copy to tmp table的SQL语句,这条语句的时间比较长,且这个表会被加读锁,相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句,会造成更多的语句被阻塞。
tmp_table_size规定了内部内存临时表的最大值,每个线程都要分配。(实际起限制作用的是tmp_table_size和max_heap_table_size两者的最小值。)如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,存储在指定的tmpdir目录下,默认:文章源自靠谱运维-https://www.ixdba.net/archives/1242

mysql> show variables like "tmpdir";文章源自靠谱运维-https://www.ixdba.net/archives/1242

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir | /tmp/ |
+---------------+-------+文章源自靠谱运维-https://www.ixdba.net/archives/1242

优化查询语句的时候,要避免使用临时表,如果实在避免不了的话,要保证这些临时表是存在内存中的。如果需要的话并且你有很多group by语句,并且你有很多内存,增大tmp_table_size(和max_heap_table_size)的值。文章源自靠谱运维-https://www.ixdba.net/archives/1242

你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数(Created_tmp_disk_tables和Created_tmp_tables),一般的比例关系是:文章源自靠谱运维-https://www.ixdba.net/archives/1242

Created_tmp_disk_tables/Created_tmp_tables<5%文章源自靠谱运维-https://www.ixdba.net/archives/1242

max_heap_table_size
这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变,即set @max_heap_table_size=#文章源自靠谱运维-https://www.ixdba.net/archives/1242

,但是对于已经存在的内存表就没有什么用了,除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。文章源自靠谱运维-https://www.ixdba.net/archives/1242

这个变量和tmp_table_size一起限制了内部内存表的大小。
修改方法:文章源自靠谱运维-https://www.ixdba.net/archives/1242

mysql> show full PROCESSLIST;文章源自靠谱运维-https://www.ixdba.net/archives/1242

mysql> show VARIABLES like 'tmp_table_size'
mysql> set GLOBAL tmp_table_size=629145600;文章源自靠谱运维-https://www.ixdba.net/archives/1242

mysql> SHOW VARIABLES LIKE 'max_heap_table_size%';
mysql> SET GLOBAL max_heap_table_size=1073741824;文章源自靠谱运维-https://www.ixdba.net/archives/1242

文章源自靠谱运维-https://www.ixdba.net/archives/1242
  • 本文由 发表于 2018年8月14日18:11:12
  • 转载请务必保留本文链接:https://www.ixdba.net/archives/1242
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: