MySQL查询性能问题排查

Mysql数据库的性能问题排查是十分复杂的，具体方法视场景而定，这里只做大致思路分析。

1. 整体考虑导致查询性能低下的各种因素

导致SQL查询变慢的原因是多元化的，在遇到问题时首先要有一个全方位的思考：

• 网络问题导致

• 应用层导致

  • 代码中是否有不合理的查询
  • 缓存失效导致查询风暴耗尽磁盘资源

• MySQL服务器性能导致

  • 是否是由服务器上其它任务占用资源过多导致资源不足
  • 磁盘IO的读写速率是否太慢

• MySQL写操作频繁

  • MySQL写入大量数据到磁盘

  • MySQL写入大量日志到磁盘

  • MySQL写入大量排序文件到磁盘

  • MySQL写入大量临时表到磁盘

• 并发度超过某个阀值时，InnoDB的扩展性限制导致查询计划的优化需要很长时间

• InnoDB疯狂刷新脏页导致内部严重阻塞

• 库表结构设计不合理

• SQL语句设计有问题或效率低

• 索引实际没有正常使用

2. 剖析MySQL查询

1. 剖析服务器负载

• 捕获查询到查询日志文件中

  • 使用慢查询日志

    • 5.0版本以前慢查询日志是秒级别，5.1版本以后以达到微秒级。

    • 可以通过设置long_query_time=0来捕获所有查询。

    • 如果长期开启慢查询日志，要部署日志轮转（log rotation）工具。

  • 当因权限不足等原因无法在服务器上记录查询时可使用pt-query-digest工具

    • 通过--processlist选项不断查看SHOW FULL PROCESSLIST的输出，记录查询第一次出现的时间和消失的时间。（精度差）
    • 通过抓取TCP网络包，然后根据MySQL客户端/服务端通信协议进行解析。（精度高）
      1. 通过tcpdump将网络包数据保存到磁盘。
      2. 使用pt-query-digest的--type=tcpdump选项解析并分析查询。

• 分析查询日志

  • 使用pt-query-digist工具生成剖析报告（请勿直接打开整个慢查询日志进行分析，避免浪费时间）

    • 报告中V/M列提供了方差均之比的详细数据，数值高的查询对应的执行时间的变化较大，这类查询通常都值得去优化。
    • pt-query-digest指定--explain选项，输出中会增加一列简要描述查询的执行计划。通过观察V/M和执行计划列，可以更容易识别出性能低下需要优化的查询。
    • 可以通过--limit和--outliers选项指定工具显示更多查询的详细信息。
    • 可以通过查询的ID或排名匹配剖析统计和查询的详细报告。

2. 剖析单条查询

​ 在定位到需要优化的单条查询后，可以根据此查询获得更多信息。以下是一些剖析方法：

• 使用SHOW PROFILES;

  • 5.1版本引入，默认禁用，可以会话级别开启：SET profiling = 1;

  • 使用SHOW PROFILE FOR QUERY #Query_ID;通过Query_ID打印一条查询语句的详细查询报告，其中可以看到查询执行的每个步骤及其花费的时间。（按执行顺序排序，无法ORDER BY）。

  • 直接查询INFORMATION_SCHEMA中对应的表，可以按需要的格式化输出：

    SET @query_id = #Query_ID;
    SELECT STATE, SUM(DURATION) AS Total_R, 
      ROUND(
        100 * SUM(DURATION) / 
            (
              SELECT SUM(DURATION) 
              FROM INFORMATION_SCHEMA.PROFILING 
              WHERE QUERY_ID = @query_id
            ), 2) AS Pct_R, 
      COUNT(*) AS Calls, 
      SUM(DURATION) / COUNT(*) AS "R/Call", 
    FROM INFORMATION_SCHEMA.PROFILING 
    WHERE QUERY_ID = @query_id 
    GROUP BY STATE
    ORDER BY Total_R DESC;
            
    

  • 通过分析结果得到查询时间太长可能存在的原因：

    • 花大量时间将数据复制到临时表。

      • 考虑如何改写查询以避免使用临时表。
      • 提升临时表的使用效率。

    • 发送数据（Sending Data）花费时间太多。

• 使用SHOW STATUS;

  • 此命令返回一些服务器级别和会话级别的计数器。
  • SHOW GLOBAL STATUS返回服务器级别的从服务器启动开始计算的查询次数统计。
  • 很多有用的计数器：Create_tmp_disk_tables磁盘临时表、Create_tmp_tables临时表、Handler_read_rnd_next没有用到索引的读操作等。
  • EXPLAIN是通过估计得到的结果，无法确认临时表是否是磁盘表。（磁盘表和内存表性能差异很大）

• 使用慢查询日志

  1. 执行show variables like 'slow_query_log';查看慢查询日志是否开启。

  2. 若没有开启找到my.cnf，添加如下内容sudo vim /usr/local/mysql/my.cnf

     log_output=file
     slow_query_log=on
     slow_query_log_file = /tmp/mysql-slow.log
     log_queries_not_using_indexes=on
     long_query_time = 1
     

  3. 重启MySQL并执行以下操作查看：

     show variables like 'slow_query_log';
     show variables like '%quer%';
     

  4. 先使用pt_query_digest生成的报告进行分析，然后有目标地使用慢查询日志。

     通过字节量偏移值byte 123直接跳转到日志对应部分：

     tail -c +123 /tmp/mysql-slow.log | head -n100
     

• 使用Performance Schema

  • 5.5版本新增的表但还不支持查询级别的剖析信息。

  • 主要为了测量当为提升服务器性能而修改MySQL源代码时使用。

  • 使用USER_STATISTICS表：

    • 通过这些表可以对数据库活动进行测量和审计

    • 可以强制执行使用策略

    • 对于共享主机环境这样的多租户环境也同样有用

    • 查看这些表：

      SHOW TABLES FROM INFORMATION_SCHEMA LIKE '%_STATISTICS';
      

    • 一些有用的查询：

      • 可以查找使用最多或使用最少的表和索引，通过读取次数或更新次数，或两者一起排序。

      • 可以查找从未使用的索引。

      • 可以查看复制用户的CONNECTED_TIME和BUSY_TIME，以确认是否会很难跟上主库的进度。

3. 诊断间接性问题

尽量不要使用试错的方式解决问题，而是应该在有问题发生的地方通过观察资源的使用情况并尽可能测量数据。

1. 确定是单条查询问题还是服务器问题

• 若果服务器上所有程序都突然变慢，又突然变好，每一条查询也都变慢了，那么慢查询可能就不一定是原因。
• 老版本MySQL对高配置服务器（多CPU）支持不好，新版本相对好些。此时可通过升级MySQL版本来解决问题。

下面是解决间接性问题的方法和工具：

• 以较高频率执行SHOW GLOBAL STATUS
• 以较高频率执行SHOW PROCESSLIST
• 使用查询日志
• 用gnuplot或R等绘图工具将结果绘制成图形帮助分析

2. 捕获诊断数据

当出现间接性问题时，需要尽可能多地收集数据，而不只是出现问题时的数据。

• 诊断触发器

  • 是问题出现时能捕获数据的基础。
  • 误报和漏检可能导致无法达到预期的结果。
  • 监控服务器，当达到触发条件时能收集数据的工具：pt-stalk

• 确定需要收集什么样的数据

  • 在需要的时间段内尽可能地收集所有能收集的数据。

  • 执行时间包括工作时间和等待时间。

  • 用于服务器内部诊断的重要工具oprofile。

  • 可以使用strace剖析服务器的系统调用。（生产环境中有一定风险）

    • 有一些不可预期性
    • 开销大
    • 使用的是实际时间
    • 对mysqld这样有大量线程场景会产生一些副作用，导致mysqld运行非常慢

  • 可以使用tcpdump剖析查询。

  • 可以使用GDB的堆栈跟踪进行对等分析。（具有侵入性，会暂时造成服务器停顿）

  • 可以使用SHOW PROCESSLIST和SHOW INNODB STATUS的快照信息观察线程和事务的状态进行等待分析。

  • 可以使用pt-collect工具收集数据，一般通过pt-stalk调用。

3. 解释结果数据

如果已经正确设置好触发条件，并且长时间运行pt-stalk，则只需要等待足够长的时间来捕获几次问题，就能得到大量数据进行筛选。

建议根据两个目的来查看：

1. 检查问题是否真的发生了。
2. 是否有非常明显的跳跃性变化。

查看异常的查询和事务的行为，以及异常服务器内部行为通常都是最有效的。通过抓取TCP流量或SHOW PROCESSLIST输出，可以获得查询和事务出现的地方，从而知道用户对数据库进行了什么操作。服务器内部行为可在oprofile或者gdb的输出中看到。

• 查看异常的查询和事务的行为，可以显示是否由于使用服务器的方式导致的问题：
  • 性能低下的SQL查询
  • 使用不当的索引
  • 设计糟糕的数据库逻辑架构
• 通过服务器的内部行为：
  • 可以清楚服务器是否有BUG
  • 内部的性能和扩展性是否有问题

pt-mysql-summary和pt-summary这两个工具会输出MySQL的状态和配置信息，以及操作系统和硬件信息。

pt-sift是一款快速检查收集到的样本数据的工具。

gdb的堆栈追踪是重要的等待分析的性能瓶颈分析工具：

• 需要自下而上来看。
• 将很多信息聚合在一起来看。

穷人剖析器poor man's profiler：

• 工具网址
• pt-pmp

如何高性能的使用MySQL呢？

1. 设计最优的库表结构

2. 建立最好的索引并实际应用

3. 设计合理的SQL