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的等待事件。