MySql优化——观察连接状态

MySQL在优化时候，可以使用show processlist来产看mysql的连接工作状态。为了能够在shell中直接使用这个命令，我们使用mysql -uroot -p123456 -e “show processlist”来代替命令行中的show processlist。
该命令返回的结果大致如下：

+----+------+-----------+------------+---------+------+-------+------------------+
| Id | User | Host      | db         | Command | Time | State | Info             |
+----+------+-----------+------------+---------+------+-------+------------------+
|  9 | root | localhost | mydatabase | Sleep   | 4756 |       | NULL             |
| 45 | root | localhost | NULL       | Query   |    0 | init  | show processlist |
+----+------+-----------+------------+---------+------+-------+------------------+

为了能够统计出某一段时间内服务器的连接状态，我们可以创建一个名为processlist.sh的shell脚本，脚本内容如下：

#！/bin/bash
while true
do
mysql -uroot -p123456 -e "show processlist \G" |grep State|sort |uniq -c >>processlist.txt
echo "---------------------"
sleep 1
Done

执行该脚本一段时间，我们得到processlist.txt文件，这个文件记录下了服务器的连接状态信息。
使用命令

cat processlist.txt |sort |uniq -c |sort -rn >>analyze.txt

可以过滤掉相同的状态信息。
拿到这些状态信息，我们就可以对服务器状态进行分析了。
状态信息有几个值尤其值得注意。

converting HEAP to MyISAM

查询结果太大，内存放不下，把结果存放在磁盘中

create tmp table

创建临时表

Copying to tmp table on disk

将临时表复制进磁盘中

locked

被其他查询锁住

如果服务器频繁的出现converting HEAP to MyISAM，那么说明生气了是由问题的，每次取出的结果或者中间结果过大。
当然也可以能是内部内存临时表设置的过小，可以将tmp_table_size和max_heap_table_size 设置大一点。
几个常见的产生临时表的原因：
1.group by的列和order by的列不同时。
2.distinct和order by一起使用
3.使用了SQL_SMALL_RESULT。
4.group by的列没有索引。
5.多表联查时order by ,group by 包含的列不是第一张表的列.
几个常见的临时表写到内存中的情况：
1.取出的列中含有text/blob类型的字段时。
2.在group by 或者distinct的列中存在大于512个字节的string列。
3.使用了union或者union all，并且含有大于512个字节的string列。