方法一：设置慢查询日志
Mysql5.0以上的版本可以支持将执行比较慢的SQL语句记录下来。
mysql> show variables like 'long%'; 注：这个long_query_time是用来定义慢于多少秒的才算“慢查询”
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> set long_query_time=1; 注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'slow%';
+---------------------+---------------+
| Variable_name | Value |
+---------------------+---------------+
| slow_launch_time | 2 |
| slow_query_log | ON | 注：是否打开日志记录
| slow_query_log_file | /tmp/slow.log | 注： 设置到什么位置
+---------------------+---------------+
3 rows in set (0.00 sec)
mysql> set global slow_query_log='ON' 注：打开日志记录
一旦slow_query_log变量被设置为ON，mysql会立即开始记录。
/etc/my.cnf 里面可以设置上面MYSQL全局变量的初始值。
long_query_time=1
slow_query_log_file=/tmp/slow.log
接着测试一下慢查询是否生效，可以访问一下phpmyadmin或者跑一条select sleep(1)，然后再cat一下/var/log/mysql-slow.log，如果看到有记录就表示设置成功了。不过，生成慢查询日志只是忠实的 记录了每一条慢查询，对于我们做分析并不方便。
====================================================
方法二:mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /tmp/slow-log
这会输出记录次数最多的10条SQL语句，其中：
-s, 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙； -t, 是top n的意思，即为返回前面多少条的数据； -g, 后边可以写一个正则匹配模式，大小写不敏感的；
比如
/path/mysqldumpslow -s r -t 10 /tmp/slow-log
得到返回记录集最多的10个查询。
/path/mysqldumpslow -s t -t 10 -g “left join” /tmp/slow-log
得到按照时间排序的前10条里面含有左连接的查询语句。
方法三：使用pt-query-digest分析mysql slow query log
方法四：修改慢查询配置
在我们做系统性能调优的时候，数据库的慢查询语句的优化是必不可少的，特别是电子商务类型的重度MYSQL应用类型。
下面我们一起来看看怎么做好MYSQL的慢查询分析吧。
1，开启MYSQL的慢查询日志
首先在my.cnf配置里面加入慢查询配置，然后建立慢查询的日志文件，并把用户和组修改为mysql，最后重启mysqld。
|
1
2
3
4
5
6
7
8
9
|
vim /etc/my # 在配置文件的[mysqld]下面加入以下几行 log-slow-queries= /var/log/mysql-slow.loglong_query_time=0.01 #表示查询时间超过10ms的都认为是慢查询log-queries-not-using-indexes #表示没有使用索引的查询也记录日志 touch /var/log/mysql-slow.logchown mysql.mysql /var/log/mysql-slow .log /etc/init .d restart |
接着测试一下慢查询是否生效，可以访问一下phpmyadmin或者跑一条select sleep(1)，然后再cat一下/var/log/mysql-slow.log，如果看到有记录就表示设置成功了。不过，生成慢查询日志只是忠实的 记录了每一条慢查询，对于我们做分析并不方便。
2，安装mysqlsla慢查询分析工具
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
wget http: //hackmysql.com/scripts/mysqlsla-2.03.tar.gztar xzf mysqlsla-2.03.tar.gzcd mysqlsla-2.03 perl Makefile.PL make make install #安装信息 #Installing /usr/local/share/perl5/mysqlsla.pm#Installing /usr/local/share/man/man3/mysqlsla.3pm#Installing /usr/local/bin/mysqlsla#Appending installation info to /usr/lib/perl5/perllocal.podfile /usr/local/bin/mysqlsla#其实是一个perl脚本 #/usr/local/bin/mysqlsla: a /usr/bin/perl -w script text executable |
3，慢查询统计
|
1
2
3
4
5
6
|
#统计出现次数最多的前10条慢查询 mysqlsla -lt slow /var/log/mysql-slow.log -top10 -sortc_sum > top10_count_sum.log#统计执行时间的总和前10条慢查询 mysqlsla -lt slow /var/log/mysql-slow.log -top10 -sortt_sum > top10_time_sum.log#统计平均执行时间最长的前10条慢查询(常用) mysqlsla -lt slow /var/log/mysql-slow.log -top10 -sortt_avg > top10_time_avg.log |
打开其中一个log统计文件，你会看到：
40 queries total, 12 unique 40条查询；除了重复的，有12条查询
Sorted by ‘t_avg’ 按平均查询时间排序
Grand Totals: Time 4 s, Lock 0 s, Rows sent 236, Rows Examined 8.63k
______________________________________________________________________ 001 ___
Count : 1 (2.50%)这条SQL出现了1次，占SQL总数的2.5%
Time : 588.994 ms total执行时间总和, 588.994 ms avg平均每次查询的时间, 588.994 ms最短时间 to 588.994 ms max最长时间 (13.78%)
Lock Time (s) : 91 µs total, 91 µs avg, 91 µs to 91 µs max (2.34%)
Rows sent : 30 avg, 30 to 30 max (12.71%)
Rows examined : 899 avg, 899 to 899 max (10.41%)
Database :
Users :
coreseektest@localhost : 100.00% (1) of query, 100.00% (40) of all users
Query abstract:
SET timestamp=N; SELECT * FROM ecm_goods WHERE goods_name LIKE ‘S’ ORDER BY ecm_goods.brand_id ASC LIMIT N, N;
Query sample:
SET timestamp=1341467496;
SELECT * FROM `ecm_goods` WHERE goods_name like ‘冰箱’ ORDER BY `ecm_goods`.`brand_id` ASC
LIMIT 0, 30;
______________________________________________________________________ 002 ___
Count : 2 (5.00%) 这条SQL出现了2次，占SQL总数的5%
Time : 57.38 ms total 执行时间总和, 28.69 ms avg 平均每次查询的时间, 27.503 ms最短时间 to 29.877 ms max最长时间 (1.34%)
Lock Time (s) : 134 µs total, 67 µs avg, 64 µs to 70 µs max (3.44%)
Rows sent : 3 avg, 3 to 3 max (2.54%)
Rows examined : 3 avg, 3 to 3 max (0.07%)
Database :
Users :
coreseektest@localhost : 100.00% (2) of query, 100.00% (40) of all usersQuery abstract:
SET timestamp=N; SELECT * FROM documents LIMIT N, N;
Query sample:
SET timestamp=1341399487;
SELECT * FROM `documents` LIMIT 0, 30;
…其他省略…
如果需要做更复杂的统计，可以参考官方文档：http://hackmysql.com/mysqlsla_guide
如果希望每隔一段时间，比如一天，出一次慢查询统计的话，可以写一个shell脚本，然后放到/etc/crontab里面。这样的话，就可以定期做查询优化。
pt-query-digest分析Mysql慢查询，Query-Digest-UI生成报表 :http://blog.chinaunix.net/uid-25266990-id-3405952.html
http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html