mysql的SQL性能监控

bobpxp

　　1、慢查询
　　1.1、慢查询的作用
　　慢查询的主要作用是将执行超过一定时间的sql语句记录到慢查询日志文件，方便找出有性能问题的sql，然后我们可以针对这些sql进行分析调优。
　　和慢查询相关的参数有三个：
　　long_query_time参数设置时间，执行时间大于该参数设置时间的sql都会被记录下来，支持小于1秒的设置，不过一般设置为1秒，主要原因时小于1秒的sql太多了，而且执行计划在大数据量情况下小于1秒的一般是没全表扫描的，而小数据量小于1秒的，即便全表扫描也问题不大，除非是执行频率非常高。
　　slow_query_log参数设置是否打开慢查询日志的开关。
　　slow_query_log_file参数设置慢查询日志文件。
　　1.2、慢查询的设置
　　两种配置方式，一种是通过命令配置，可以即时生效，但重启服务后失效。示例如下：
　　root@laojiang:~> mysql -uroot -proot
　　set global long_query_time=1;
　　set global slow_query_log=on;
　　set global slow_query_log_file='/home/root/data/data/mysql-slow_1.log';
　　Exit
　　root@laojiang:~> mysql -uroot -proot
　　show variables like '%_query_%';
　　第二种方式是修改配置文件，然后重启服务，如下：
　　root@laojiang:~/etc> vi mysql.ini
　　[mysqld]
　　long_query_time=1
　　slow_query_log=ON
　　slow_query_log_file=/home/root/data/data/mysql-slow_2.log
　　root@laojiang:~/etc> service mysql stop
　　root@laojiang:~/etc> service mysql start
　　1.3、慢查询示例
　　进入命令窗口执行查询语句：
　　root@laojiang:~> mysql -uroot -proot
　　mysql> select count(*) from performance.order_line where ol_dist_info like '%abcdefg%' or ol_dist_info like '%hijk%';
　　查看慢查询日志文件：
　　root@laojiang:~/data/data> tail -f mysql-slow_2.log
　　1.4、系统慢查询状态检查
　　mysql> show global status like '%slow%';
　　+---------------------+-------+
　　| Variable_name    | Value |
　　+---------------------+-------+
　　| Slow_launch_threads | 0     |
　　| Slow_queries        | 4148 |
　　+---------------------+-------+
　　打开慢查询日志可能会对系统性能有一点点影响，如果MySQL是主－从结构，可以考虑打开其中一台从服务器的慢查询日志，这样既可以监控慢查询，对系统性能影响又小。
　　1.5、mysqldumpslow命令
　　慢查询日志有可能会有很多重复的sql语句，我们如何过滤呢？Mysql有自带的命令mysqldumpslow可进行查询，例下列命令可以查出访问次数最多的20个sql语句
　　mysqldumpslow -s c -t 20 host-slow.log
　　2、当前系统正在执行的sql
　　show processlist以及show full processlist可以查看mysql当前正在执行的sql语句，以便找到当前消耗资源的sql。第2个命令可以查看完整的正在执行的sql语句。
　　3、sql执行各阶段所花费的时间
　　通过mysql自带profiling(性能分析)工具可以诊断某个sql各个执行阶段消耗的时间，每个执行阶段在cpu  disk io等方面的消耗情况。
　　mysql> show variables like '%profiling%';
　　+------------------------+-------+
　　| Variable_name          | Value |
　　+------------------------+-------+
　　| have_profiling         | YES   |
　　| profiling              | OFF   |
　　| profiling_history_size | 15    |
　　+------------------------+-------+
　　mysql> set global profiling=1;
　　mysql> show variables like '%profiling%';
　　+------------------------+-------+
　　| Variable_name          | Value |
　　+------------------------+-------+
　　| have_profiling         | YES   |
　　| profiling              | ON    |
　　| profiling_history_size | 15    |
　　+------------------------+-------+
　　mysql> select count(*) from test_info;
　　+----------+
　　| count(*) |
　　+----------+
　　| 10000000 |
　　+----------+
　　mysql> show  profiles;
　　+----------+------------+--------------------------------------+
　　| Query_ID | Duration   | Query                                |
　　+----------+------------+--------------------------------------+
　　|        1 | 0.00014900 | 1                                    |
　　|        2 | 0.00007725 | set global profiling=1               |
　　|        3 | 0.00059175 | show variables like '%profiling%'    |
　　|        4 | 2.83227700 | select count(*) from test_info |
　　+----------+------------+--------------------------------------+
　　mysql> show profile for query 4;
　　+----------------------+----------+
　　| Status               | Duration |
　　+----------------------+----------+
　　| starting             | 0.000067 |
　　| checking permissions | 0.000007 |
　　| Opening tables       | 0.000021 |
　　| init                 | 0.000017 |
　　| System lock          | 0.000008 |
　　| optimizing           | 0.000007 |
　　| statistics           | 0.000016 |
　　| preparing            | 0.000015 |
　　| executing            | 0.000003 |
　　| Sending data         | 2.831913 |
　　| end                  | 0.000015 |
　　| query end            | 0.000009 |
　　| closing tables       | 0.000015 |
　　| freeing items        | 0.000141 |
　　| cleaning up          | 0.000024 |
　　+----------------------+----------+
　　15 rows in set, 1 warning (0.00 sec)
　　4、sql执行状态
　　这些状态在show processlist和show profile for query n中会出现，所谓sql执行状态，就是只sql执行的步骤，可以查到当前sql正在做什么，这个在oracle中对应就是sql的等待事件。