mysqltmp_table_size优化之设置多⼤合适
通过设置tmp_table_size选项来增加⼀张临时表的⼤⼩，例如做⾼级GROUP BY操作⽣成的临时表。如果调⾼该值，MySQL 同时将增加heap表的⼤⼩，可达到提⾼联接查询速度的效果，建议尽量优化查询，要确保查询过程中⽣成的临时表在内存中，避免临时表过⼤导致⽣成基于硬盘的MyISAM表。
mysql> show global status like ‘created_tmp%‘;
+——————————–+———+
| Variable_name  | Value　|
+———————————-+———+
mysql存储文档
| 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/myf
[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 值。
mysql 的配置⽂件中，tmp_table_size 的默认⼤⼩是 32M。如果⼀张临时表超出该⼤⼩，MySQL产⽣⼀个 The table
tbl_name is full 形式的错误，如果你做很多⾼级 GROUP BY 查询，增加 tmp_table_size 值。这是 mysql 官⽅关于此选项的解释：
tmp_table_size
This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory.
对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等⼦句中的条件判断中⽤到的字段，应该根据其建⽴索引INDEX。
索引被⽤来快速出在⼀个列上⽤⼀特定值的⾏。没有索引，MySQL不得不⾸先以第⼀条记录开始并然后读完整个表直到它出相关的⾏。表越⼤，花费时间越多。如果表对于查询的列有⼀个索引，MySQL能快速到达⼀个位置去搜寻到数据⽂件的中间，没有必要考虑所有数据。如果⼀个表有1000⾏，这⽐顺序读取⾄少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
根据 mysql 的开发⽂档:
索引 index ⽤于：
快速出匹配⼀个WHERE⼦句的⾏
当执⾏联结(JOIN)时，从其他表检索⾏。
对特定的索引列出MAX()或MIN()值
如果排序或分组在⼀个可⽤键的最左⾯前缀上进⾏(例如，ORDER BY key_part_1,key_part_2)，排序或分组⼀个表。如果所有键值部分跟随DESC，键以倒序被读取。
在⼀些情况中，⼀个查询能被优化来检索值，不⽤咨询数据⽂件。如果对某些表的所有使⽤的列是数字型的并且构成某些键的最左⾯前缀，为了更快，值可以从索引树被检索出来。
假定你发出下列SELECT语句：
mysql> select * FROM tbl_name WHERE col1=val1 AND col2=val2；如果⼀个多列索引存在于col1和col2上，适当的⾏可以直接被取出。如果分开的单⾏列索引存在于col1和col2上，优化器试图通过决定哪个索引将到更少的⾏并来出更具限制性的索引并且使⽤该索引取⾏。
⼀般动态设置tmp_table_size的⼤⼩的时候，要使⽤:
set global tmp_table_size=64*1024*1024
set global tmp_table_size=64M
#1232 - Incorrect argument type to variable 'tmp_table_size'

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。