`

mysql 的慢查询调优

阅读更多

原文:http://blog.csdn.net/zm2714/article/details/8510893

 

慢查询定义及作用

慢查询日志,顾名思义,就是查询慢的日志,是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下,慢查询日志是关闭的,要使用慢查询日志功能,首先要开启慢查询日志功能。

 

开启慢查询日志

在mysql的配置文件(linux是my.cnf、windows是my.ini)中的mysqld下方添加以下参数:

#这个参数设置为ON,或1,可以捕获执行时间超过一定数值的SQL语句

slow_query_log = 1

#当SQL语句执行时间超过此数值时,就会被记录到日志中,建议设置为1或者更短,单位秒,缺省是10秒。

long_query_time = 1

#这个参数设置为ON或1,可以捕获到所有未使用索引的SQL语句,尽管这个SQL语句有可能执行得挺快。

log_queries_not_using_indexes = 1

#记录慢查询日志的文件名

slow_query_log_file=/data/mysql/log/slow-query.log

 

注意

log-slow-queries <slow_query_log_file>指定日志文件存放位置,该目录文件一定要有写的权限。可以不用设置,系统会给一个缺省的文件host_name-slow.log ;这2个参数类似,一般都知道明确的文件日志位置;

 

给mysql用户组的mysql用户对某个目录或文件,赋读写权限;

chown -R mysql:mysql /var/log/slow-query.log 
chmod 660 /var/log/ slow-query.log 

给mysql用户组的mysql用户的某个目录或文件赋写权限; 

chown -R mysql:mysql /var/log/slow-query.log

chmod +w /var/log/slow-query.log

 

log-long-format

简单的说log-long-format选项是用来设置日志的格式,它是以扩展方式记录有关事件。扩展方式可记录谁发出查询和什么时候发出查询的信息。可使我们更好地掌握客户端的操作情况。

准确的说,它是记录激活的更新日志、二进制更新日志、和慢查询日志的大量信息。例如,所有查询的用户名和时间戳将记录下来。不赞成选用该选项,因为它现在代表 默认记录行为。

log-short-format

记录激活的更新日志、二进制更新日志、和慢查询日志的少量信息。例如,用户名和时间戳不记录下来。

 

配置文件修改后必须保存、重启mysql服务器;

查看日志方法:

方法1:tail -f /data/mysql/log/slow-query.log  通过这种tail -f    日志文件    命令模式可以查看;

 

# Time: 151028 10:46:06

# User@Host: root[root] @  [172.168.48.196]  Id:   298

# Query_time: 0.104143  Lock_time: 0.000479 Rows_sent: 5  Rows_examined: 9502

SET timestamp=1446000366;

SELECT CASE WHEN c.street IS NULL THEN CONCAT(c.province,c.city,c.town,c.detailAddress) ELSE CONCAT(c.province,c.city,c.town,c.street,c.detailAddress)  END  AS customerAddress,u.userId as workerId,u.realName repairman_name,u.telephone repairman_phone,s.arrAdd repairman_address FROM dispatch_info a JOIN  user_info u ON (a.serverId=u.sermanagerId OR a.serverId=u.userId)   AND a.object_id='312497904'  AND u.isDelete=0 JOIN customer_info c ON a.customerId=c.customerId LEFT OUTER JOIN (SELECT us.id,us.arrAdd,us.workId FROM user_sign us,(SELECT MAX(id) as id from user_sign GROUP BY workId)uus where us.id=uus.id ) s ON u.userId=s.workId;

 

 

第一行,SQL查询执行的时间 
第二行,执行SQL查询的连接信息 
第三行记录了一些我们比较有用的信息 
Query_time SQL执行的时间,越长则越慢 
Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间 
Rows_sent 查询返回的行数 
Rows_examined 查询检查的行数

 

方法2:不用重启数据库,通过设置mysql的全局变量模式来查看;

登陆到mysql上执行一下sql脚本即可

mysql> set global slow_query_log=ON;

mysql> set global long_query_time=1;

mysql> set global log_queries_not_using_indexes = 1;     #打开没有使用索引的sql

然后通过一下命令查看是否成功

查看long_query_time 的值

mysql> show variables like '%long%';

+--------------------------------------------------------+----------+

| Variable_name                                          | Value    |

+--------------------------------------------------------+----------+

| long_query_time                                        | 1.000000 |

| performance_schema_events_stages_history_long_size     | 10000    |

| performance_schema_events_statements_history_long_size | 10000    |

| performance_schema_events_waits_history_long_size      | 10000    |

+--------------------------------------------------------+----------+

 

mysql> show variables like 'slow%';

+---------------------+--------------------------------+

| Variable_name       | Value                          |

+---------------------+--------------------------------+

| slow_launch_time    | 2                              |

| slow_query_log      | ON                             |

| slow_query_log_file | /data/mysql/log/slow-query.log |

+---------------------+--------------------------------+

 

方法3:

随着mysql数据库服务器运行时间的增加,可能会有越来越多的SQL查询被记录到了慢查询日志文件中,这时要分析该文章就显得不是很容易了。mysql提供的mysqldumpslow命令,可以很好地解决这个问题。

 

使用方法如下:

命令行下,进入 mysql/bin 目录,输入 mysqldumpslow ?help 或 --help 可以看到这个工具的参数

mysqldumpslow -s c -t 20 host-slow.log

mysqldumpslow -s r -t 20 host-slow.log 

上述命令可以看出访问次数最多的 20 个 sql 语句和返回记录集最多的 20 个 sql 

mysqldumpslow -t 10 -s t -g "left join" host-slow.log

这个是按照时间返回前 10 条里面含有左连接的 sql 语句。

mysqldumpslow -s c -t 10 /database/mysql/slow-log

这会输出记录次数最多的10条SQL语句,其中:

-s order,是表示按照何种方式排序,order值有:c、t、l、r 分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒序;
-t num,即为返回前面多少条的数据;
-g pattern,pattern可以写一个正则匹配模式,大小写不敏感的;

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句,对MySQL查询语句的监控、分析、优化是MySQL优化的第一步,也是非常重要的一步。

相关命令

查看慢查询的记录数

mysql> show global status like '%slow%';

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| Slow_launch_threads | 0     |

| Slow_queries        | 3444  |

+---------------------+-------+

2 rows in set (0.00 sec)

 

查看log_queries_not_using_indexes状态

mysql> show variables like 'log_queries_not_using_indexes';

+-------------------------------+-------+

| Variable_name                 | Value |

+-------------------------------+-------+

| log_queries_not_using_indexes | ON    |

+-------------------------------+-------+

1 row in set (0.00 sec)

 

 

 

 

 

分享到:
评论

相关推荐

    实战MySQL慢SQL调优

    "实战MySQL慢SQL调优" 在数据库性能优化中,慢SQL查询是最常见的问题之一。慢SQL查询不仅会影响数据库的性能,还会对整个系统的性能产生影响。本文将从实战的角度,讲解如何对MySQL进行慢SQL调优。 首先,需要了解...

    Mysql 优化以及调优,面试必备宝典!

    总之,MySQL优化和调优涉及的知识点非常广泛,包括但不限于索引优化、查询分析、表结构设计、拆分策略、范式与反范式以及慢查询处理等。掌握这些知识点,不仅能提升数据库性能,还能够在面试中表现出对数据库性能...

    如何对MySQL服务器进行调优

    慢查询日志是MySQL中用于记录执行时间超过预设阈值的查询的日志文件。通过配置慢查询日志,可以更有效地定位那些性能不佳的查询,并针对性地进行优化。 #### 配置慢查询日志 在`my.cnf`文件中添加以下配置来启用慢...

    mysql数据性能调优

    在给定文件中,提到了通过配置和使用MySQL的慢查询日志(slow query log)来进行性能调优,下面将详细解读这部分内容。 首先,我们需要了解什么是慢查询日志。慢查询日志是MySQL数据库提供的一种日志记录功能,它...

    Mysql一些调优资料收集

    MySQL提供了一个慢查询日志功能,通过在my.cnf中配置`log-slow-queries`和`long_query_time`,可以记录执行时间超过设定阈值的查询。例如,设置`long_query_time = 5`将记录执行时间超过5秒的查询。此外,`log-...

    mysql性能调优的方法

    MySQL性能调优是一个复杂而重要的任务,涉及到多个层面,包括SQL查询优化、索引管理、存储引擎选择、架构设计等。下面将详细讲解这些关键点。 首先,SQL查询优化是性能提升的基础。优化SQL语句可以减少数据库的计算...

    mysql 慢查询页面工具

    总结来说,MySQL 慢查询页面工具 Query-Digest-UI 是一个基于 Percona Toolkit 的图形化监控解决方案,它帮助管理员可视化 MySQL 的慢查询日志,从而更有效地进行性能调优。通过安装和配置该工具,你可以实时监控...

    Linux下的MySQL调优

    ### Linux下的MySQL调优 #### 一、调优概述 MySQL作为一款广泛使用的数据库管理系统,在Linux环境下进行调优能够显著提高其性能与稳定性。本文将详细介绍Linux环境下MySQL调优的具体步骤与方法,帮助读者理解并...

    MySQL DBA性能调优 全攻略

    本文将详细介绍MySQL数据库性能调优的方法和技术,包括操作系统层面的优化、文件系统的选择、硬件配置建议、SQL语句优化技巧以及数据库内部配置的调整等。 #### 二、操作系统优化 1. **内核参数调整**: - 调整...

    MySQL慢查询日志

    ### MySQL慢查询日志详解 #### 一、慢查询日志概述 MySQL慢查询日志是一种重要的工具,用于记录执行时间超过预设阈值的SQL语句。通过对这些记录进行分析,可以找出数据库中的性能瓶颈,并据此采取相应的优化措施。...

    MYSQL性能调优工具介绍与应用

    ### MySQL性能调优工具介绍与应用 MySQL作为全球最广泛使用的开源关系型数据库之一,在企业级应用中的地位不可动摇。为了确保MySQL能够高效稳定地运行,掌握一系列性能调优工具至关重要。本文将详细介绍多种MySQL...

    linux+tomcat+mysql+solr调优

    ### Linux + Tomcat + MySQL + Solr 调优指南 #### 一、Linux系统调优 ##### TCP连接优化 为了提升Linux系统在网络层面的性能,可以对TCP连接进行优化,尤其是在处理大量并发请求时。以下是一些常用的TCP配置项:...

    MySQL内存使用调优.doc

    你需要在 MySQL 工作负载处于顶峰的时候为它分配足够的内存,否则查询就会因为内存不足而变得很慢,甚至失败。 知道 MySQL 在负载处于顶峰的时候需要多少内存是有用的,但是某些使用模式会出人意料地消耗大量的内存...

    数据库+Mysql+性能调用+用于在实践过程中进行Mysql的性能调优

    15. **监控慢查询日志**:定期分析慢查询日志,找出性能问题并优化。 16. **使用合适的数据存储引擎**:根据应用需求选择合适的存储引擎,如InnoDB适合事务处理,MyISAM适合读取密集型应用。 17. **定期维护数据库...

    mysql调优与性能设计

    在MySQL性能调优方面,首先我们需要关注的是查询优化。这包括了编写高效的SQL语句,避免全表扫描,合理使用索引,以及理解执行计划。索引是提升查询速度的关键,正确的创建主键、唯一索引、普通索引和覆盖索引,可以...

    MySql调优.pdf

    调优MySQL时,通常会关注索引优化、查询优化、存储引擎选择、配置参数调整、硬件资源优化等方面。例如,针对查询性能,可以通过创建合适的索引、避免全表扫描、优化JOIN操作等方式提升;对于存储引擎,根据业务需求...

    mysql高性能---mysql数据库的性能调优

    7. **日志优化**:调整binlog(二进制日志)和slow query log(慢查询日志)设置,可以监控并优化性能问题,同时确保数据安全。 8. **硬件优化**:升级硬件,如使用SSD替换HDD,增加内存容量,使用更快的CPU,都能...

    MySQL性能调优与架构设计--全册

    8. **性能监控与分析**:使用MySQL自带的Performance Schema和监控工具如pt-query-digest,定期检查慢查询日志,找出性能瓶颈。 9. **复制与高可用性**:掌握主从复制原理,实现数据备份和故障恢复,以及更高级的...

    MySQL性能调优与架构设计 - 简朝阳.rar_MySQL性能调优与架构设计简朝阳

    3. 日志分析:通过错误日志、慢查询日志等分析系统运行状况。 以上只是《MySQL性能调优与架构设计》中部分核心知识点的概述,实际内容更加丰富,包括具体的参数调优、性能瓶颈分析、故障排查等多个方面,对于数据库...

Global site tag (gtag.js) - Google Analytics