mysql tmp_table_size优化之设置多大合适

通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。

mysql> show global status like ‘created_tmp%‘;

+——————————–+———+

| Variable_name　　 | Value　|

+———————————-+———+

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files　　　| 58　　|

| Created_tmp_tables　　| 1771587 |

+——————————–+———–+

每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：

Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables / Created_tmp_tables * 100% ＝1.20%，应该相当好了

默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞

如果动态页面要调大点，100M以上，如果网站大部分都是静态内容，一般64M足够。

tmp_table_size优化

数据库连接突然增多到1000的问题

查看了一下，未有LOCK操作语句。

但是明显有好多copy to tmp table的SQL语句，这条语读的时间比较长，且这个表会被加读锁，相关表的update语句会被排进队列。如果多执行几次这样的copyt to tmp table 语句，会造成更多的语句被阻塞。
连接太多造成mysql处理慢。

copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时，参数中设置的临时表的大小小于结果集的大小时，就会将该表放在磁盘上，这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时，则系统会把max_heap_table_size的值作为最大的内存临时表的上限，大于这个时，改写硬盘。
我们的mysql这两个参数为：

tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
比较小。
建议增加到上百M。我们的内存应该够吧。

另外join_buffer_size（影响 表之间join性能的缓存）为131072 (131K)较小，可以增加一点。

[root@mail ~]# vi /etc/my.cnf

[mysqld]
tmp_table_size=200M

mysql> show processlist;
mysql> show columns from wp_posts;

SQL 语句的第一个 LEFT JOIN ON 子句中： LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata 的 userid 被参与了条件比较运算。为 _mydata 表根据字段 userid 建立了一个索引： mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )  增加 tmp_table_size 值。