mysql慢查询问题（Copying to tmp table）

xyaxlz

  解决一例最近常常碰到网站慢的情况，登陆到后台，查询一下 /opt/mysql/bin/mysqladmin processlist;
发现一个查询状态为： Copying to tmp table 而且此查询速度非常慢，基本一分钟左右才出来，后面是很多查询，状态为lock。
此分析对我没有太大的作用，因此用google查询了一下，发现网上一篇文章讲得很好: Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory-based temporary table to a disk based one to save memory.
哦，原来是这样的，如果查询超出了tmp_table_size的限制，那么mysql用/tmp保存查询结果，然后返回给客户端。
set global tmp_table_size=209715200 (200M) 再次运行此查询,用/opt/mysql/bin/mysqladmin processlist;
进行观察,发现不会出现上述问题.
至此问题解决. 调节tmp_table_size 的时候发现另外一些参数
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目
Qcache_free_memory 可以缓存一些常用的查询,如果是常用的sql会被装载到内存。那样会增加数据库访问速度

 

 

数据库连接突然增多到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
set global tmp_table_size=209715200


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'

 

 

它规定了内部内存临时表的最大值，每个线程都要分配。
（实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。）
如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，
存储在指定的tmpdir目录下，默认:
mysql> show variables like "tmpdir";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp/ |
+---------------+-------+
优化查询语句的时候，要避免使用临时表，如果实在避免不了的话，要保证这些临时表是存在内存中的。
如果需要的话并且你有很多group by语句，并且你有很多内存，增大tmp_table_size(和max_heap_table_size)的值。
这个变量不适用与用户创建的内存表(memory table).

你可以比较内部基于磁盘的临时表的总数和创建在内存中的临时表的总数（Created_tmp_disk_tables和Created_tmp_tables），
一般的比例关系是:
Created_tmp_disk_tables/Created_tmp_tables<5%

max_heap_table_size
这个变量定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。
这个变量支持动态改变，即set @max_heap_table_size=#
,但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table)或者修改(alter table)或者truncate table。
服务重启也会设置已经存在的内存表为全局max_heap_table_size的值。
这个变量和tmp_table_size一起限制了内部内存表的大小。