简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary
Recent Updates

Home  >  Article  >  Database  >  mysql slow query

mysql slow query

WBOY
WBOYOriginal
2016-06-07 15:52:541046browse

优化MySQL最重要的一部分工作是先确定”有问题”的查询语句。只有先找出这些查询较慢的sql查询(执行时间较长)，我们才能进一步分析原因并且优化它。MySQL为我们提供了Slow Query Log记录功能，它能记录执行时间超过了特定时长的查询。分析Slow Query Log有助

 

优化MySQL最重要的一部分工作是先确定”有问题”的查询语句。只有先找出这些查询较慢的sql查询(执行时间较长)，我们才能进一步分析原因并且优化它。MySQL为我们提供了Slow Query Log记录功能，它能记录执行时间超过了特定时长的查询。分析Slow Query Log有助于帮我们找到”问题”查询。

记录slow queries

首先，我们需要查看mysql server版本号，以及是否配置启用了slow query log。查看mysql server版本号，主要是一些功能以及配置依赖于mysql server 版本号
查看mysql server版本号

<span>$echo</span> <span>"status"</span> <span>|</span>mysql <span>|</span><span>grep</span> <span>"Server version"</span>
Server version:         5.1.38-log Source distribution

不同的MySQL版本，设定与功能略有不同。
检查当前服务器有没有在记录slow query

<span>$mysqladmin</span> var <span>|</span><span>grep</span> <span>"log_slow"</span> <span>|</span><span>tr</span> <span>-d</span> <span>"|"</span>
log_slow_queries	OFF

当log_slow_queries是ON时，才表示已经启用了记录slow query功能。默认是不记录slow query的。
启用slow query日志

<span>#//将下列配置放到my.cnf中，查看my.cnf位置可以使用命令ps -ef |grep mysqld_safe</span>
<span>[</span>mysqld<span>]</span>
log-slow-queries	= <span>/</span>var<span>/</span>lib<span>/</span>mysql<span>/</span>slow-queries.log
long_query_time		= <span>1</span>
log-queries-not-using-indexes
log-slow-admin-statements

上面的配置打开了slow query日志,将会捕获了执行时间超过了1秒的查询，包括执行速度较慢的管理命令(比如OPTIMEZE TABLE),并且记录了没有使用索引的查询。这些SQL，都会被记录到log-slow-queries指定的文件/var/lib/mysql/slow-queries.log文件中。

      log-slow-queries
      存放slow query日志的文件。你必须保证mysql server进程mysqld_safe进程用户对该文件有w权限。
      long_query_time
      如果query time超过了该值，则认为是较慢查询，并被记录下来。单位是秒，最小值是1,默认值是10秒。10秒对于大多数应用来讲，太长了。我们推荐从3秒开始，依次减少，每次都找出最”昂贵”的10条SQL语句并且优化他们。日复一日，一步一步优化。一次性找出很多条SQL语句，对于优化来讲，意义并不大。
      log-queries-not-using-indexes
      MySQL会将没有使用索引的查询记录到slow query日志中。无论它执行有多快，查询语句没有使用索引，都会被记录。有的时候，有些没有使用引索的查询非常快(例如扫描很小的表)，但也有可能导致服务器变慢，甚至还会使用大量的磁盘空间。
      log-slow-admin-statements
      一些管理指令，也会被记录。比如OPTIMEZE TABLE, ALTER TABLE等等。

    日志文件

    我们可以通过tail -f查看日志文件。

    <span>$tail</span> <span>-f</span> <span>/</span>var<span>/</span>lib<span>/</span>mysql<span>/</span>slow-queries.log
<span># Time: 110107 16:22:11</span>
<span># User@Host: root[root] @ localhost []</span>
<span># Query_time: 9.869362  Lock_time: 0.000035 Rows_sent: 1  Rows_examined: 6261774</span>
SET <span>timestamp</span>=<span>1294388531</span>;
<span>select</span> count<span>(</span><span>*</span><span>)</span> from ep_friends;

    第一行,SQL查询执行的时间
    第二行,执行SQL查询的连接信息
    第三行记录了一些我们比较有用的信息
        Query_time SQL执行的时间,越长则越慢
        Lock_time 在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
        Rows_sent 查询返回的行数
        Rows_examined 查询检查的行数
    Slow Query日志，虽然帮助你记录了那些执行过了的SQL语句。但它不是万能的，意义可能没有你想象的那么大。它只告诉了你哪些语句慢，但是为什么慢?具体原因，还是需要你自己去分析，不断的调试。也许，你只需要换一条更有效的sql语句，也许你只需简单地增加一个索引，但也有可能你需要调整你应用程序的设计方案。比如，上面那条语句是很明显，它检查了600多万行数据。不幸的是，并不是每条语句都这么明显。也许还有别的原因，比如:
    *锁表了，导致查询处于等态状态。lock_time显示了查询等待锁被翻译的时间
    *数据或索引没有被缓存。常见于第一次启动服务器或者服务器没有调优
    *备份数据库，I/O变慢
    *也许同时运行了其它的查询，减少了当前查询

    所以,不要过于紧张日志文件某条记录，而应该理性地审记，找出真正的原因。如果经常出现的slow query需要特别注意。如果个别出现，则做一些常规检查即可。我们建议，统计并且形成基准报告，进行比较排除，比胡乱瞎撞有用。希望大家不要在这部分过于浪费时间与精力。

    线上记录slow query

    上文的配置需要重启mysql server进程mysqld才会生效。但是很多时候，尤其是产品运营环境，不希望每次修改都需要重新启动mysql服务器，也希望能在某些特定时间记录。MySQL5.1给我们提供了更为灵活的运行时控制，使得你不必重新启动mysql服务器，也能选择性地记录或者不记录某些slow queries。

    MySQL5.1中，提供了全局变量slow_query_log、slow_query_log_file可以灵活地控制enable/disable慢查询。同时可以通过long_query_time设置时间

    <span>#//停用slow query记录</span>
<span>#注意:设置了slow_query_log全局变量, log_slow_queries也会隐性地跟着改变</span>
mysql<span>></span><span>set</span> global <span>slow_query_log</span>=OFF

    不幸运的是,在MySQL5.0并没有提供类似的全局变量来灵活控制，但是我们可以通过将long_query_time设置得足够大来避免记录某些查询语句。比如

    mysql>set global long_query_time = 3600;

    MySQL5.0, 不关服务的情况下，希望不记录日志的办法是将日志文件成为/dev/null的符号链接(symbolic link)。注意:你只需要在改变后运行FLUSH LOGS以确定MYSQL释放当前的日志文件描述符，重新把日志记录到/dev/null

    和MySQL5.0不同,MySQL5.1可以在运行时改变日记行为，将日志记录到数据库表中。只要将mysql全局变量log_output设置为TABLE即可。MySQL会将日志分别记录到表mysql.gengera_log和mysql.slow_log二张表中。但是，我们推荐将日志记录在日记文件中。

    mysql> show variables like ‘log_output’\G
    Variable_name: log_output
    Value: FILE
    mysql>set global log_output=’table’;

    缺陷与审记

    虽然记录了slow query能够帮助你优化产品。但是MySQL目前版本，还有几大蹩足的地方。
    1.MySQL5.0版本, long_query_time时间粒度不够细,最小值为1秒。对于高并发性能的网页脚本而言，1秒出现的意义不大。即出现1秒的查询比较少。直到mysql5.1.21才提供更细粒度的long_query_time设定.
    2.不能将服务器执行的所有查询记录到慢速日志中。虽然MySQL普通日志记录了所有查询，但是它们是解析查询之前就记录下来了。这意味着普通日志没办法包含诸如执行时间，锁表时间，检查行数等信息。
    3.如果开启了log_queries_not_using_indexes选项，slow query日志会充满过多的垃圾日志记录，这些快且高效的全表扫描查询(表小)会冲掉真正有用的slow queries记录。比如select * from category这样的查询也会被记录下来。

    通过microslow-patch补丁可使用更细的时间粒度，和记录所有执行过的sql语句。不过，使用这个补订不得不自己编译MySQL，出于稳定性考滤，我们推荐在开发测试环境，可以打上这个补丁，享受这个补丁带来的便利。在运营环境尽量不要这么做…

    MySQL自带了mysqldumpslow工具用来分析slow query日志，除此之外，还有一些好用的开源工具。比如MyProfi、mysql-log-filter，当然还有mysqlsla

    Statement：
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
    Previous article：Data Provider 中没有.net framework Data provider for MysqlNext article：MySQL Memory(Heap)引擎

    Related articles

    See more