mysql优化之慢SQL

浏览数：30 / 时间：2015年06月12日

写过DB服务的同学们都知道，性能优化很重要，对于数据库应用程序来说，查看慢SQL以优化数据库操作是最基本的，对于以MySQL为DB的应用程序来说也不例外，本文就是以MySQL为例来介绍如何查看慢SQL的问题。在MySQL中，慢SQL就是指所有执行时间大于long_query_time的SQL语句，知道这些语句后你就可以进行相关优化了，比如：加索引、合并语句等。

一、启用慢SQL
1、查看慢SQL是否启用
        mysql> show variables like ‘log_slow_queries‘;
        +------------------+-------+
        | Variable_name    | Value |
        +------------------+-------+
        | log_slow_queries | OFF |
        +------------------+-------+
        上面语句能查看慢SQL是否启用了，即Value为ON表示启用了，为OFF表示禁用了。
        mysql> show variables like ‘long_query_time‘;
        +-----------------+-------+
        | Variable_name   | Value |
        +-----------------+-------+
        | long_query_time | 10 |
        +-----------------+-------+
        上面语句能查看执行慢于多少秒的SQL算慢SQL即会记录到日志文件中，Value为10表示大于10秒的会记录。
2、开启慢SQL
        如果你的MySQL是源码编译的，那么慢SQL日志文件信息由编译时指定，比如：一般将慢SQL日志文件放在/data/mysql/目录下，文件名为hostname-slow.log。
        如果你的MySQL是RPM安装的，那么慢SQL日志文件信息由MySQL配置文件/etc/my.cnf指定，打开配置文件，找到[mysqld]区段，增加日志配置，具体如下：
      [mysqld]
      log=“/var/lib/mysql/mysql.log”
      log_slow_queries="/var/lib/mysql/hostname-slow.log"
      long_query_time=2
      log指定MySQL日志文件存放路径。
        log_slow_queries指定慢SQL日志文件存放路径，此目录文件一定要有写权限。
        long_query_time指定大于多长时间算慢SQL，单位秒。
      需要注意的是，上述配置一定要加在[mysqld]后而不是[mysqld_safe]下面，否则不会生效。
      配置完后，重启MySQL即可：service mysqld restart
mysql> show variables like ‘slow%‘;
        +------------------+-------+
        | Variable_name    | Value |
        +------------------+-------+
        | slow_launch_time | 2 |
        +------------------+-------+
| slow_query_log | ON |
        +------------------+-------+
| slow_query_log_file | /var/lib/mysql/hostname-slow.log|
        +------------------+-------+
      上面说明开启了慢SQL，并且时间为2秒。
       CD到目录/var/lib/mysql/目录下能看到自动创建了慢SQL日志文件hostname-slow.log。
      cat hostname-slow.log会发现里面没有记录，简单测试下：
        mysql> select sleep(3);
         +------------+
| sleep(3) |
        +-------------+
        |               0 |
1 row in set (3.00 sec)
      上面执行完了一个3秒的SQL语句，这时候打开慢SQL日志文件就能看到了。
        当然除了上面的设置方式外，还可以在mysqld进程启动时，通过参数指定选项，比如：--log-slow-queries[=file_name]。

二、慢SQL分析

注：Phpstudy为例，使用之前需要安装pear.步骤如下：

1)下载完整的php5.3，覆盖原有的php53目录下没有的的文件。

2)在php.ini中添加：phar.require_hash = 0

3）到php53目录下,cmd下执行语句：php -d phar.require_hash=0 pear/go-pear.phar即可开始使用mysql自带的mysqldumpslow命令分析日志

慢SQL分析可以通过vi直接打开日志文件来做，但是这种方式还是比较原始，实际上慢SQL分析工具有很多种，常用的有如下几款：
1、mysqldumpslow
      这是MySQL官方提供的慢SQL日志分析工具，安装好后，执行如下语句就能分析日志：
      #mysqldumpslow /var/lib/mysql/hostname-slow.log
      主要功能是统计不同慢SQL的如下指标：
      count（出现次数） Time（执行最长时间/累计总耗费时间） Lock（等待锁的时间S） Rows（发送给客户端的总行数）    用户及SQL语句
      慢SQL日志文件有时候记录数会比较多，那么mysqldumpslow提供了一些参数用于解决这些问题：
      mysqldumpslow -s r -t 10 /var/lib/mysql/hostname-slow.log
      上面命令会以查询时间来排序并显示前10条查询，其中，参数s表示排序选项（c:查询次数、r:返回记录行数、t:查询时间），参数t表示只显示top n条查询。
2、mysqlsla
      这是由hackmysql.com提供的一款日志分析工具，该网站还提供了mysqlreport、mysqlidxchk等实用的mysql工具值得学习。
      安装好后，执行如下命令就能分析日志了：
      #mysqlsla -lt slow /var/lib/mysql/hostname-slow.log
      该工具方便用户分析慢查询的原因，包括执行频率、数据量、查询消耗等，具体包含信息如下：
      queries total（总查询次数）     unique（去重后的SQL数量）
        Sorted by（报表按照什么排序）
      Gand Totals（慢SQL统计信息）：Time（平均执行时间），Lock（等待锁时间），Rows Sent（总行数），Rows Examined（总扫描行数）
      Count（SQL执行次数及占总慢SQL数量的百分比）
      Time（执行时间）：包括总时间、最小/最大时间、时间占总慢SQL时间的百分比
      Lock Time（等待锁时间）
      Rows Sent（行数统计）：包括平均、最小/最大数量
      Database（数据库）
      Users（用户、IP、占所有用户执行SQL的百分比）
      Query abstract（抽象后的SQL语句）
        Query sample（SQL语句例子）
3、mysql-log-filter
      google code上的开源分析工具，提供python和php两种脚本，可以点击这里下载。
      执行如下命令就能分析日志：
      #python mysql_filter_slow_log.py /var/lib/mysql/hostname-slow.log --no-duplicates --sort-execution-count --top=10
      功能上类似于mysqldumpslow，但是多出了很多查询时间的统计信息，包括平均、最大、累计等，此外，还对输出内容排版和格式化。
4、myprofi
      sourceforge上提供的纯php写的开源分析工具，可以点击这里下载。
        执行如下命令就能分析日志：
        #php parser.php -slow /var/lib/mysql/hostname-slow.log
      执行上面命令后，会列出总的慢查询次数和类型、去重后的SQL语句、执行次数及占总慢SQL数量的百分比。
        myprofi的输出比较简洁，适合于只关心慢SQL语句及执行次数的同学。

三、慢SQL优化
      SQL优化是一门大工程，不敢狂言，在此只是表述我实际中遇到的问题及优化解决方案来以此达到抛砖引玉的效果。
1、索引问题
      在一次压力测试中，发现有一个70W记录的流水表，由于没有加索引导致在update时MySQL服务器CPU瞬间飙到170%，语句类似于下面：
      update loginlog set LogoutTime=%lu where RoleName=%s and LoginTime=%lu
      loginlog表的主键是自增ID。
      对字段RoleName和LoginTime建索引后，问题解决了。
      因此，对于操作频繁的select、update、带where的delete语句最好对where条件涉及的字段建索引，当然这也会给insert语句带来一定的性能损失。
2、联合主键问题
      在一次压力测试中，在慢SQL日志中发现一条记录：
        Time Id Command Argument
        # Time: 130528 10:45:19
        # User@Host: qa_root[qa_root] @ [172.28.14.224]
        # Query_time: 0.003057 Lock_time: 0.000026 Rows_sent: 2 Rows_examined: 2262
        use friend_relation;
        SET timestamp=1369709119;
        SELECT FromRoleName FROM userrelation WHERE ToRoleName = ‘xxx‘;
      userrelation中FromRoleName与ToRoleName为联合主键，但是上面的where语句中联合主键并没有达到效果。
      这是因为，联合主键在索引时，where条件对字段及顺序有要求，比如：
      （1）where FromRoleName=‘xxx‘
      （2）where FromRoleName=‘xxx‘ and ToRoleName = ‘xxx‘
      上述两种情况都有效果。
      为字段ToRoleName建立索引后，问题解决了。
3、分页问题
      有时候会对表格记录有limit操作的需求，这就涉及到分页问题，这里贴一段我的DBA同事曾给我的一段关于分页优化的建议吧：
      （1）首次查询的时候缓存结果。这样情况就变得简单了，无论是结果条目的数量，总共的页面数量，还是取出其中的部分条目。
      （2）不显示总共有多少条目。Google搜索结果的分页显示就用了这个特性。很多时候你可能看了前几页，就够了。那么我可以这样，每次我都把结果限制在500条（这个数据越大资源消耗越大）然后你每次查询的时候，都查询501条记录，这样，如果结果真有501个，那么我们就显示链接 “显示下500条记录”。
      （3）不显示总页面数。只给出“下一页”的链接，如果有下一页的话。(如果用户想看上一页的话，他会通过浏览器来回到上一页的)。那你可能会问我“不显示总页面数”怎么知道是不是有下一页呢？这里有一个很好的小技巧：你在每次显示你当前页面条目的时候你都多查询一条，例如你要显示第11-20个条目时，你就取出11-21条记录（多取一条，并不显示这多取的内容），那么当你发现第21条存在的时候就显示“下一页的链接”，否则就是末页了。这样你就不用每次计算总页面数量了，特别是在做缓存很困难的时候这样做效率非常好。
      （4）估算总结果数。Google就是这么做的，事实证明效果很好。用EXPLAIN 来解释你的SQL，然后通过EXPLAIN的结果来估算。EXPLAIN结果有一列”row”会给你一个大概的结果。(这个办法不是处处都行，但是某些地方效果是很好的)这些办法可以很大程度上减轻数据库的压力，而且对用户体验不会有什么影响。