mysql tmp_table_size优化之设置多大合适
通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUPBY操作生成的临时表。如果调高该值，MySQL同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存中，避免临时表过大导致生成基于硬盘的MyISAM表。
mysql>showglobalstatuslike‘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操作语句。
但是明显有好多copytotmptable的SQL语句，这条语读的时间比较长，且这个表会被加读锁，相关表的update语句会被排进队列。如果多执行几次这样的copyttotmptable语句，会造成更多的语句被阻塞。
连接太多造成mysql处理慢。
copytotmptalbe语句产生的原因是查询需要OrderBy或者GroupBy等需要用到结果集时，参数中设置的临时表的大小小于结果集的大小时，就会将该表放在磁盘上，这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另外一个参数max_heap_table_size比tmp_table_size小时，则系统会把max_heap_table_size的值作为最大的内存临时表的上限，大于这个时，改写硬盘。
我们的mysql这两个参数为：
tmp_table_size33554432(33.5M)
max_heap_table_size16777216(16.7M)
比较小。
建议增加到上百M。我们的内存应该够吧。
另外join_buffer_size（影响表之间join性能的缓存）为131072(131K)较小，可以增加一点。
[root@mail~]#vi/etc/my.cnf
[mysqld]
tmp_table_size=200M
mysql>showprocesslist;
mysql>showcolumnsfromwp_posts;
SQL语句的第一个LEFTJOINON子句中：LEFTJOIN_myuserASt3ONt1.userid=t3.userid_mydata的userid被参与了条件比较运算。为_mydata表根据字段userid建立了一个索引：mysql>ALTERTABLE`_mydata`ADDINDEX(`userid`) 增加tmp_table_size值。
mysql的配置文件中，tmp_table_size的默认大小是32M。如果一张临时表超出该大小，MySQL产生一个Thetabletbl_nameisfull形式的错误，如果你做很多高级GROUPBY查询，增加tmp_table_size值。这是mysql官方关于此选项的解释：
tmp_table_size
Thisvariabledeterminesthemaximumsizeforatemporarytableinmemory.Ifthetablebecomestoolarge,aMYISAMtableiscreatedondisk.Trytoavoidtemporarytablesbyoptimizingthequerieswherepossible,butwherethisisnotpossible,trytoensuretemporarytablesarealwaysstoredinmemory.Watchingtheprocesslistforquerieswithtemporarytablesthattaketoolongtoresolvecangiveyouanearlywarningthattmp_table_sizeneedstobeupped.Beawarethatmemoryisalsoallocatedper-thread.AnexamplewhereuppingthisworkedformorewasaserverwhereIuppedthisfrom32MB(thedefault)to64MBwithimmediateeffect.Thequickerresolutionofqueriesresultedinlessthreadsbeingactiveatanyonetime,withall-roundbenefitsfortheserver,andavailablememory.
对WHERE,JOIN,MAX(),MIN(),ORDERBY等子句中的条件判断中用到的字段,应该根据其建立索引INDEX。
索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据mysql的开发文档:
索引index用于：
快速找出匹配一个WHERE子句的行
当执行联结(JOIN)时，从其他表检索行。
对特定的索引列找出MAX()或MIN()值
如果排序或分组在一个可用键的最左面前缀上进行(例如，ORDERBYkey_part_1,key_part_2)，排序或分组一个表。如果所有键值部分跟随DESC，键以倒序被读取。
在一些情况中，一个查询能被优化来检索值，不用咨询数据文件。如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
假定你发出下列SELECT语句：
mysql>select*FROMtbl_nameWHEREcol1=val1ANDcol2=val2;如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。
一般动态设置tmp_table_size的大小的时候，要使用:
setglobaltmp_table_size=64*1024*1024
setglobaltmp_table_size=64M
#1232-Incorrectargumenttypetovariable'tmp_table_size'