程序员人生 网站导航

MySQL慢查询

栏目:数据库应用时间:2015-01-14 08:50:36

慢查询(slow log)可以帮助我们定位到特定的SQL语句进行SQL语句层面的优化,例如,慢查询日志会记录那些履行时间超过给定值得SQL语句,从而定位到问题的所在。

开启慢查询

查看MySQL数据库实例关于慢查询的参数

mysql> show variables like '%slow%'; +---------------------------+-----------------------------------+ | Variable_name | Value | +---------------------------+-----------------------------------+ | log_slow_admin_statements | OFF | | log_slow_slave_statements | OFF | | slow_launch_time | 2 | | slow_query_log | OFF | | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +---------------------------+-----------------------------------+ 5 rows in set (0.00 sec) mysql> show variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.02 sec)

其中,

slow_query_log便是开启慢查询的参数

slow_query_log_file是慢查询日志文件的路径

log_query_time是查询时间的最大值,超过这个时间就会被慢查询日志记录。

下面是开始慢查询,然后将查询时间最大值设为2秒,履行sleep3秒,查看慢查询日志的例子

mysql> set global slow_query_log=ON; Query OK, 0 rows affected (0.03 sec) mysql> set long_query_time=2; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%long_query_time%'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 2.000000 | +-----------------+----------+ 1 row in set (0.00 sec) mysql> select sleep(3); +----------+ | sleep(3) | +----------+ | 0 | +----------+ 1 row in set (3.00 sec) mysql> system cat /var/lib/mysql/localhost-slow.log /usr/sbin/mysqld, Version: 5.6.22-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock Time Id Command Argument # Time: 150102 16:56:54 # User@Host: root[root] @ localhost [] Id: 1 # Query_time: 3.001084 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use test2; SET timestamp=1420189014; select sleep(3);

慢查询还有1个参数log_queries_not_using_indexes,用来表示每分钟允许记录到慢查询日志的未使用索引的SQL语句的次数,默许为0,表示没有限制,但是生产环境中,这类语句会大量占据慢查询日志的大小,给DBA的分析带来困扰。

除使用日志文件查看慢查询日志,还可使用表的情势查看。在数据库名为mysql的模式下,有1张叫做slow_log的表,可以记录慢查询的输出,只需改变日志的输出模式log_output,参数log_output是全局的动态的,可以在运行时动态的改变。

mysql> show variables like '%log_output%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set (0.00 sec) mysql> set global log_output='table'; Query OK, 0 rows affected (0.00 sec) mysql> select sleep(3); +----------+ | sleep(3) | +----------+ | 0 | +----------+ 1 row in set (3.00 sec)

指的注意的是slow_log这张表的引擎室CSV,对查询的效力可能不是很高,但是作为日志的追加插入效力很高。

mysql> show create table slow_logG *************************** 1. row *************************** Table: slow_log Create Table: CREATE TABLE `slow_log` ( `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `user_host` mediumtext NOT NULL, `query_time` time NOT NULL, `lock_time` time NOT NULL, `rows_sent` int(11) NOT NULL, `rows_examined` int(11) NOT NULL, `db` varchar(512) NOT NULL, `last_insert_id` int(11) NOT NULL, `insert_id` int(11) NOT NULL, `server_id` int(10) unsigned NOT NULL, `sql_text` mediumtext NOT NULL, `thread_id` bigint(21) unsigned NOT NULL ) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'

关于慢查询的另外1个有用的参数是long_query_io表示将超过指定逻辑IO次数(逻辑IO包括物理IO,表示物理IO和缓冲池读取次数之和)的SQL语句记录到慢查询日志中去。


------分隔线----------------------------
------分隔线----------------------------

最新技术推荐