My Little World

慢查询日志分析

发表于

MySQL 调优 金字塔


越向上难度越大，回报减少。
1.硬件和OS的调优，十分复杂，需要有对硬件和OS了解比较深的人员去进行化。
2.MySQL调优，调整表结构、优化SQL、恰当的使用索引，清除多余的索引等等。
3.架构调优，需要考虑实际的业务场景

  1. 可以将非数据库的任务，放到数据仓库，搜索引擎或者缓存。
    2.评估并发量，决定是不是要做分布式。
    3.根据读压力，考虑是否进行读写分离。
    业务DBA：从业务需求讨论到表结构审核、SQL语句审核、上线、索引的更新。

什么是慢查询日志

记录查询花费大量时间的SQL的日志，就是慢查询日志。
long_query_time采数：该参数会设定一个阈值，超过该值的SQL，就是慢查询SQL。

SQL查询性能下降的原因

查询性能变低的最基础的原因，就是访问的数据太多了。
对于低效的查询，可以通过下面两个步骤分析：
1.确认是否在检索大量超过需要的数据。可能是访问了很多的行，也有可能是访问了很多的列。
2.确认MySQL服务层是否在分析大量超过需要的数据行

请求了不需要的数据

  1. 查询不需要的记录
    MySQL会查询出全部的数据集(type : all)，客户端应用程序会接收全部的数据集，然后抛弃其中大部分数据

  2. 总是取出全部的列
    select * …..,注意 是否需要真的返回全部的列。取出全部的列，会导致优化器无法完成索引覆盖扫描。
    一些DBA是严格禁止使用 select *，尤其使用二级索引时，会导致回表，性能下降明显。
    什么时候可以用 select *？
    • 如果应用程序使用了某种缓存机制，可能就需要获取全部的数据进行缓存，这时候可以选择去用，但是要考虑付出的代价。

  3. 重复查询相同的数据
    不断重复同样的查询，返回相同的记录，也是十分消耗资源，影响MYSQL性能的，这个时候可以考虑进行缓存

扫描了过多的额外记录

如果确定了查询只返回需要的数据以后，接下来就应该看看查询时为了返回需要的数据，是否扫描了过多的数据。对于MySQL，最简单的衡量查询开销的三个指标：

1) 响应时间：服务时间+排队时间

* 服务时间：数据库处理这个查询时，真正花了多少时间。
* 排队时间：指的是服务器因为等待某些资源而没有真正执行查询的时间。有可能是等待行锁。

2) 扫描的行数和返回的行数

*   理想情况下，扫描的行数和返回的行数应该是相同的。

3) 扫描的行数和访问的类型

*   在Explain分析结果中，字段type反映了访问的类型。
*   访问类型，从慢到快：全表扫描 --> 索引扫描 --> 范围扫描 --> 唯一索引扫描 --> 主键扫描

MySQL中使用下面三种方式应用where条件，从好到坏：
1) 在索引中使用where条件，过滤不需要的数据，这个操作存储引擎中完成。
2) 使用覆盖索引扫描来返回记录，直接从索引中过滤不需要的记录并且返回命中的结果。是在MySQL的服务层完成，不需要再回表查询。
3) 从数据表中返回数据，存在回表，过滤不需要的数据，在MySQL的服务层完成的，MySQL需要从数据表读取数据然后进行过滤。
返回的行数少，扫描的行数多，优化方式：
1) 使用索引覆盖扫描，把所有需要用的列都放到索引中。
2) 改变库表的结构。
3) 重写复杂SQL。

慢查询日志分析

MySQL慢查询，全名叫做慢查询日志，作用是用来记录MySQL中响应时间超过设定的阈值的SQL语句。
默认情况不启动慢查询日志。
慢查询相关的参数
慢查询参数解释：

  • slow_query_log：是否开启慢查询日志，ON (1)表示开启，OFF(0)表示关闭。
  • slow_query_log_file：MySQL数据库慢查询日志的存储的路径。
  • long_query_time：慢查询的阈值，当查询时间大于设定的阈值的时候，会记录到日志。

可以通过设置slow_query_log的值，来开启慢查询日志

1
2
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.01 sec)

上面这种设置方式，只对当前的窗口有效的，MySQL重启之后就会失效。如果想要永久生效，需要修改my.cnf

1
2
slow_query_log =1
slow_query_log_file=/var/lib/mysql/test-slow.log

重启MySQL让配置生效

1
[root@localhost ~]# service mysqld restart

开启慢查询日志之后，什么样的SQL才会记录到慢查询日志中呢？是由参数long_query_time来控制的，默认情况下值是10秒。
重新设置值：

1
mysql> set global long_query_time=1;

注意：设置完成后，需要重新连接MySQL才能看到修改后的值。

  • log_output：
    • 值为FILE，表示将日志存储到文件，默认值。
    • 也可以设置存储到数据库：log_ouput=TABLE，但是耗费更多系统资源。
  • log_queries_not_using_indexes：开启后，未使用索引的查询，也会被记录到慢查询日志中。

日志内容解析

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
----查看日志 tail -f /var/1ib/mysql/test-slow.1og:
# Time: 2023-08-30T07:52:49.392140Z
# User@Host: root[root] @  [192.168.52.1]  Id:     4
# Query_time: 7.653827  Lock_time: 0.000095 Rows_sent: 3  Rows_examined: 4000003
use test_slowlog;
SET timestamp=1693381969;
SELECT * FROM test_index LIMIT 4000000,3;
# Time: 2023-08-30T07:53:12.301259Z
# User@Host: root[root] @  [192.168.52.1]  Id:     4
# Query_time: 3.328770  Lock_time: 0.000216 Rows_sent: 4  Rows_examined: 5000000
SET timestamp=1693381992;
-- 写一条执行时间超过1秒的SQL
SELECT * FROM test_index WHERE
hobby = '2000001' OR hobby = '2000011' OR hobby = '2000021'
OR dname = 'name40000000' OR 'name400000100' LIMIT 0, 1000;
  • Time: 执行时间
  • User: 用户信息
  • Query_time: 查询语句执行的时间
  • Lock_time: 等待锁的时间
  • Rows_sent: 查询结果行数
  • Rows_examined: 查询扫描的行数
  • SET timestamp: 事件戳
  • SQL的具体信息

慢查询SQL的优化思路

SQL执行时间长的原因：

  • 等待的时间长：大概率是由于锁表或锁冲突导致的，让查询一直处于等待状态。
  • 执行的时间长
  • 查询的SQL写的烂
  • 索引失效
  • 关联的JOIN太多
  • 服务器调优及各个参数设置

慢查询优化的思路：

  • 优先选择优化 高并发执行的SQL，因为高并发执行SQL发生问题带来的后果，更加严重。
    • 比如下面两种情况：
    • SQL1：每小时执行10000次，每次20个IO，优化后每次18个IO，每小时节省2万次IO。
    • SQL2：每小时执行10次，每次20000个IO，优化后每次减少2000个IO，每小时节省2万次IO
    • SQL2的并发度更高，SQL更难去优化，SQL1更好优化一些，但是SQL2属于高并发SQL，更加急需优化。
  • 定位优化对象的性能瓶颈
    • IO：数据访问的时候消耗了太多的时间，查看是否正确的使用了索引。
    • CPU：数据运算花费了太多的时间，数据运算的分组、排序是不是有问题
    • 网络带宽：如果网络带宽受限，数据传输的速度也将受到限制，从而导致查询的响应时间增加了。
  • 明确优化的目标（最终的优化结果，就是给用户一个好的体验）
    • 需要根据数据库当前的状态。
    • 数据库中与当前该条SQL的关系。
    • 当前SQL的具体功能。尽量选择优化那些对业务影响比较大的SQL。
  • 从explain执行计划入手
    • 只有explain能告诉你当前SQL的状态。
  • 永远用小的结果集去驱动大的结果集
    • 小的结果集驱动大的结果集，目的就是减少内层表读取的次数。
1
2
3
4
5
for(int i = 0; i < 5; i++){
    for(int i = 0; i < 1000; i++){

    }
}

如果小的循环在外层，对于数据库的连接来说，就是只连接5次，进行5000次操作，否则反过来就是连接1000次，每次做5次操作，就会增加资源的浪费

  • 尽可能的在索引中完成排序
    • 因为索引本身就是排好序的，排序字段在索引中的话，速度是比较快的。
  • 只获取自己需要的列
    • 不要用select *
  • 只去使用最有效的过滤条件
    • 误区：where后面的条件越多越好，实际上应该是用最短的路径访问到数据才是最好的。
  • 尽可能避免复杂JOIN和子查询
    • 每条SQL的JOIN 操作，建议不超过3张表
    • 将复杂的SQL拆分成多个小SQL 单个表执行，获取到结果 去程序中封装就可以。
  • 合理设计并且利用索引

如何判断是否需要创建索引？

  1. 较为频繁的作为查询条件的字段，应该创建索引
  2. 唯一性太差的字段不适合单独的去创建索引。比如：性别 状态 类型字段，当一条query所返回的数据超过了全表的15%的时候，就不需要再使用索引扫描。
  3. 更新频繁的字段不适合创建索引。因为需要维护索引文件。
  4. 不会出现在where条件中的字段 不需要创建索引。

如何选择合适的索引？

  1. 对于单键索引，尽量选择针对于当前查询效果更好的索引。
  2. 选择联合索引时当前查询中过滤性最好的字段应该在 联合索引的最左侧。