MySQL优化:使用慢查询日志定位效率较低的SQL语句

数据库 MySQL
MySQL通过慢查询日志定位执行效率较低的SQL语句,当慢查询日志的内容过多时,通过mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。

MySQL通过慢查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long_query_time 秒的SQL语句的日志文件,通过查看这个日志文件定位效率较低的SQL 。

慢查询日志在查询结束以后才记录,所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题,可以使用show processlist命令查看当前MySQL在进行的线程,包括线程的状态、是否锁表等,可以实时地查看SQL 的执行情况,同时对一些锁表操作进行优化。

下面我们举例说明一下,如何通过慢查询日志定位执行效率低的SQL 语句:

开启慢查询日志,配置样例:

  1. [mysqld]  
  2.  
  3. log-slow-queries 

在my.cnf 配置文件中增加上述配置项并重启mysql服务,这时mysql慢查询功能生效。慢查询日志将写入参数DATADIR(数据目录)指定的路径下,默认文件名是host_name-slow.log 。

和错误日志、查询日志一样,慢查询日志记录的格式也是纯文本,可以被直接读取。下例中演示了慢查询日志的设置和读取过程。

首先查询一下 long_query_time 的值 。

  1. mysql> show variables like 'long%';  
  2.  
  3. +-----------------+-------+  
  4.  
  5. | Variable_name | Value |  
  6.  
  7. +-----------------+-------+  
  8.  
  9. | long_query_time | 10 |  
  10.  
  11. +-----------------+-------+  
  12.  
  13. 1 row in set (0.00 sec) 

为了方便测试,将修改慢查询时间为5秒。

  1. mysql> set long_query_time=5;  
  2.  
  3. Query OK, 0 rows affected (0.02 sec) 

依次执行下面两个查询语句。

第一个查询因为查询时间低于5 秒而不会出现在慢查询日志中:

  1. mysql> select count(*) from order2008;  
  2.  
  3. +----------+  
  4.  
  5. | count(*) |  
  6.  
  7. +----------+  
  8.  
  9. | 208 |  
  10.  
  11. +----------+  
  12.  
  13. 1 row in set (0.00 sec) 

第二个查询因为查询时间大于5 秒而应该出现在慢查询日志中:

  1. mysql> select count(*) from t_user;  
  2.  
  3. +----------+  
  4.  
  5. | count(*) |  
  6.  
  7. +----------+  
  8.  
  9. | 6552961 | 

查看慢查询日志。

  1. [root@localhost mysql]# more localhost-slow.log  
  2.  
  3. # Time: 081026 19:46:34  
  4.  
  5. # User@Host: root[root] @ localhost []  
  6.  
  7. # Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961  
  8.  
  9. select count(*) from t_user; 

从上面日志中,可以发现查询时间超过5 秒的SQL,而小于5秒的则没有出现在此日志中。

#p#

如果慢查询日志中记录内容很多,可以使用mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分类汇总。下例中对日志文件mysql_master-slow.log进行了分类汇总,只显示汇总后摘要结果:

  1. [root@mysql_master mysql_data]#mysqldumpslow mysql_master-slow.log  
  2.  
  3. Reading mysql slow query log from mysql_master-slow.log  
  4.  
  5. Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@mysql_master  
  6.  
  7. select count(N) from t_user; 

对于 SQL 文本完全一致,只是变量不同的语句,mysqldumpslow 将会自动视为同一个语句进行统计,变量值用N来代替。这个统计结果将大大增加用户阅读慢查询日志的效率,并迅速定位系统的SQL 瓶颈

注意:慢查询日志对于我们发现应用中有性能问题的SQL很有帮助,建议正常情况下,打开此日志并经常查看分析。

【编辑推荐】

  1. 如何安全的远程使用MySQL GUI工具
  2. 浅谈mysql在主从服务器中同步的实现
  3. 记MySQL使用UDF自动同步memcached的效率
  4. 如何解决MySQL第三方客户端工具显示中文乱码
  5. MySQL管理工具phpMyAdmin再发力,新版本发布
责任编辑:赵鹏 来源: 中国IT实验室
相关推荐

2010-11-25 11:07:28

MySQL慢查询

2020-06-05 09:21:20

MySQL慢查询数据库

2010-10-08 16:26:49

mysql查找

2023-11-30 15:37:37

MySQL数据库

2021-04-07 10:38:43

MySQL数据库命令

2011-07-21 13:44:52

MySQLmysqldumpsl

2017-09-18 15:20:02

MySQL慢查询日志配置

2017-04-01 19:00:25

MySQL慢查询

2017-05-23 16:26:26

MySQL优化处理

2020-09-13 13:05:41

MySQL慢查询数据

2011-04-02 16:45:58

SQL Server查询优化

2019-08-14 15:18:55

MySQLSQL数据库

2020-11-23 11:40:35

MySQSQL数据库

2018-10-12 16:45:10

MySQL查询日志数据库

2018-03-29 19:45:47

数据库MySQL查询优化

2019-12-17 10:16:34

MySQLSQL优化数据库

2022-09-27 08:40:44

慢查询MySQL定位优化

2010-11-25 16:29:26

MySQL慢日志查询

2017-08-31 14:09:26

数据库MySQLSQL优化

2018-09-20 11:54:31

数据库MySQL性能优化
点赞
收藏

51CTO技术栈公众号