1. 慢查询诊断

EnigmaGcl
372 阅读17分钟

1. 慢查询诊断.png

1. 慢查询相关参数

1.1 slow_query_log + long_query_time

  • slow_query_log：慢查询日志开关，默认开
  • long_query_time：慢查询阈值，默认 10 秒，global+session

建议： set global long_query_time = 1;，分析型业务，session 级设置set long_query_time = N;

1.2 log_slow_extra

日志内容更为详细，建议开启 image.png 需要注意的是 query_time、lock_time、rows_examined、thread_id、created_tmp_disk_tables、created_tmp_tables 等

1.3 log_queries_not_using_indexes

查询未使用索引，就记录慢查询日志，global 级别，建议开启。

但是开启该参数可能存在问题：慢查询日志过多，原因是很多监控系统会频繁调用下面语句：

select * from information_schema.processlist;
select * from infirmation_schema.innodb_trx;
show engine innodb status\G;

运维三连操作，上述操作相关的表是没有索引的，因此都会被记录到慢查询日志中，会污染慢查询日志结果，优化方式：

  1. 跟 log_throttle_queries_not_using_indexes 参数搭配使用，问题是可能存在误伤，慢查询日志可能记录的都是上面语句，用户语句反而被丢弃了，不建议
  2. 跟 min_examined_row_limit 参数搭配使用，问题是存在遗漏，有些扫描函数少，但是执行时间长的语句正是可能存在问题的语句，如果被过滤掉，问题会遗漏，不建议

    • 8.0.17 以后，select count(*) 语句 rows_examined 为 0，原因是引擎层提供了一个函数直接返回表行数，引擎自己一行行扫描累加后返回给 server 层，server 层直接拿到结果，不用像以前一样一行行统计并计算得到结果，因此 rows_eamined 为 0。

      注意：8.0.17～8.0.36 之间版本，该函数在引擎层扫描时，只用主键索引，属于 bug，性能可能很差，8.0.37 及以后版本修复为使用最小索引树

    • undo_log 过多，如果需要查询 undo_log 很多的行，那么花费在计算 undo_log 的时间会比较多，尽管扫描行数可能仅为一行

  3. 修改内核，添加指令白名单或者将 log_queries_not_using_indexes 修改为 global+session，这样监控线程可以将该参数关闭

1.4 log_throttle_queries_not_using_indexes

限制每分钟没有使用索引的查询, 记录到慢查询日志的条数上限

1.5 min_examined_row_limit

扫描行数少于这个值，不记录慢查询

1.6 问答

1.6.1 5.7 和 8.0 关于慢查询日志的差异

5.7 及之前版本，行锁等待时间不计入慢查询时间，8.0 及之后会计算行锁等待时间，不管什么版本，表锁时间不计入

在查看慢查询日志时，如果整体用时较长，且扫描函数不多，未使用临时表，锁时间很短，那可能是表锁导致的整体耗时变长

1.6.2 slowlog 是在语句返回前还是返回后执行

slowlog 是在语句结果返回给客户端后再记录的，因此对当前语句执行性能没有影响，但是我们常说的慢查询开启后会影响性能又是怎么回事呢？虽然慢查询记录对本次查询没有影响，但是下次查询来时，如果慢查询还未记录成功，会等慢查询记录完成后再处理请求，因此慢查询会影响下次请求的性能

这里感觉 MySQL 没有设计好，应该搞一个独立的线程或者线程池来处理慢查询日志，这样请求线程只需将慢查询日志结果扔给慢查询处理线程即可，不用自己处理，这样就不会对请求线程有影响了，只要机器 CPU 扛得住就行

1.6.3 执行错误的语句是否计入慢查询日志

计入，执行时间不短并且是错误的语句，可能有：

  • insert 多行时，发生主键冲突
  • 锁等待直到超时

1.6.4 锁等待

答案是 A，B、C 由于指定 id 不存在，不会有锁，D 是在等待 MDL X 锁而不是行锁，如果该语句最终被计入了慢查询日志，那么慢查询日志中的 lock_time 是否包含等待 MDL 锁的时间？答案是不包含，慢查询不记录表锁和 MDL 锁时间，只有行锁相关的时间，比如行锁、间隙锁以及 Next-key lock。

1.6.5 等待

答案是 B、D

  • A 是 online 的，在拷贝数据到临时表阶段，连 CUD 都不会阻塞，更别说 R 语句了
  • B 是在等待 MDL 写锁，MDL 加锁规则可以看做队列方式，如果同时存在 X 和 S 锁，那需要遵循先来后到排序逻辑，因此 B 选项语句在等待 MDL X 锁时，该语句后面所有的 CRUD 语句都会被锁住
  • C delete 语句是简单的 DML 语句，只会加 MDL S 锁和扫描到的行的写锁，因此不会阻塞 Q1 语句
  • D 是表锁，并且是写锁，会阻塞除了自己外所有线程的读写

2. 怎么减少突发慢查询对系统的影响

  • 业务回归测试提前发现

    • 测试环境：

         set global long_query_time = 0;
   set global log_slow_extra = on;

      注意观察 rows_sent、rows_examined、srot_rows、created_tmp_disk_tables、created_tmp_tables……

  • 审计日志采集
  • 分析和预警

3. 调优思路

  • 了解语句执行流程
  • 对比执行过程的消耗，和输出结果，引擎层返回多少条，server 层返回多少条，如果有差值，可以考虑解决差值，更普遍的是加索引

4. 如何判断 SQL 主要消耗阶段

4.1 分析思路

  • 确认是锁还是执行消耗
  • 对于执行消耗，分析执行过程

4.2 执行器操作

  • B+树上操作

    • index lookup 索引定位，也就是常说的利用索引在 B+ 上进行快速查询定位
    • index range scan 索引范围扫描，利用 B+ 树叶子节点有序且不同页之间通过双向指针连接的特性来进行范围扫描，一般用于 where a > x 等条件查询
    • table scan 主键索引全表扫描，当查询没有携带条件或者条件中没有索引字段时，会触发全表扫描，这里的全表指的是主键索引
    • index skip scan 8.0 引入

  • 聚合操作

    • group by：创建一个两列临时表，第一列为唯一索引，第二列为计数，从引擎层返回的行插入该临时表计算最后返回，8.0 之前版本默认会将 group by 的结果排序，因此还有一个排序消耗，如果 group by 的字段本身是有序的，比如主键索引，order by x group by x 等，可以省略排序过程，并且可以不用临时表，因为扫描到不同数据时，返回当前数，记录下一个数即可，可以作为优化方向

    • distinct：创建一个只有一列的唯一索引表，从引擎层返回的行都插入该表后返回，跟 group 很像，只是没有计数列而已，这两个操作都需要使用临时表，如果内存空间足够，那么使用内存临时表，如果不够，使用磁盘临时表？

    • sum/avg

  • sort 排序操作

    • qsort 快排，时间复杂度 M*log2(N)

    • priority queue 优先队列（堆），limit n 时优先使用优先队列排序，时间复杂度 M*log2(N)，当 N 大到 sort buffer 放不下时，不使用优先队列

    • merge sort 归并排序，如果待排序的数据超过 sort buffer 大小，使用归并排序，首先将数据进行分组，保证每一组能放入 sort buffer，然后将数据分组放入 sort buffer 进行排序，排好后放入磁盘，然后每组分配一个指针用于指定当前待比较的数，依次比较返回

    • 优先使用优先队列排序，不能用再考虑快排，最后考虑归并排序

  • join 操作（小表做驱动表，表 t1 100 行，t2 1000 行）

    • 驱动表和被驱动表

      sequenceDiagram
participant 驱动表
participant 被驱动表

驱动表->>被驱动表: 遍历每一行
loop 逐行匹配
    被驱动表-->>驱动表: 返回匹配结果
end

      • 驱动表（外层表） ：首先被访问的表，相当于 for 循环的外层循环

      • 被驱动表（内层表） ：根据驱动表结果进行匹配的表，相当于 for 循环的内层循环

    • Nest loop join：小表做驱动表，被驱动表有索引，用小表满足条件的数据去大表查询得到结果，语句 select * from t1 join t2 using (a); 执行流程为：

      先查询 t1 得到 100 行数据，然后使用 a 的值去 t2 的索引 a 上查询得到 id，回表得到 t2 的行，组装在一起得到结果集中一行，上面的结果集并不需要占用 join buffer，因为每得到一行就能直接返回给客户端

      可以看到 join 并不能保证根据写法，决定使用哪个表做驱动表，如果执行 select * from t2 join t1 using (a); 也是一样的会使用 t1 表做驱动表，如果害怕编译器选择错误，需要强行指定驱动表，可以使用 straight join 语句：select * from t2 straight_join t1 using (a);，这样就会强行指定 t2 为驱动表，会查询 t2 所有 1000 行数据，然后使用 a 的值去 t1 的索引 a 上查询得到 id，回表得到 t1 的行，组装在一起得到结果集中一行

    • semijoin：当只需要判断左表的记录是否在右表中存在匹配，而不需要返回右表的具体列时，会使用 Semijoin：select * from t1 where a in (select a from t2)，上面的 Nest loop join 在查到 t1 100 行数据后，使用每一行的 a 去 t2 中匹配，会遍历 t2 索引 a 上每一行，找到所有匹配的结果，但是如果使用的是 in，那么找到一行后就不往后匹配查询了，直接算作结果集一行，该语句会被 MySQL 改写成下面语句： image.png

    • anti join：当需要找出左表中不存在于右表中的记录时，会使用 Antijoin：select * from t1 where id not in (select id from t2)，该语句会被改写成 antijoin，扫描 t1 全表，去 t2 主键上匹配 id，存在就过滤掉，不存在就保留为结果集中一行 image.png

    • Block Nest loop join（BNL）：select * from t1 join t2 using (b)，被驱动表 b 是没有索引的，因此需要将 t2 每一行与 t1 每一行进行匹配，扫描函数为 100 * 1000，此时如果 join buffer 无法放下整个驱动表 t1，那么 MySQL 会选择将 t1 分块，依次放入 join buffer，然后遍历 t2 每一行，去 join buffer 中遍历匹配，完成后，将 t1 其余块依次处理，最终得到结果集

    sequenceDiagram
    participant 驱动表
    participant JoinBuffer
    participant 被驱动表

    驱动表->>JoinBuffer: 分块加载驱动表数据
    loop 每个数据块
        JoinBuffer->>被驱动表: 全表扫描匹配当前块
        被驱动表-->>结果集: 返回匹配行
    end

    • hash join：MySQL 8.0.18 后提供 hash join，用于被驱动表无索引，且驱动表能放入 join buffer 的情况，且只用于等值查询

    • 在 MySQL 中，通常不能直接主动选择使用哪种 Join 算法。MySQL 的查询优化器会根据表的统计信息（如数据量、索引情况等）、硬件资源（如内存大小）等因素自动选择最合适的 Join 算法。不过，可以通过一些手段来影响查询优化器的决策，例如：

      • 创建合适的索引：在连接列上创建索引可以增加 Nest Loop Join 被选择的可能性。
      • 调整系统参数：例如调整 join_buffer_size 参数，当该参数设置较大时，可能会更倾向于使用 BNL 算法。

    • Nest Loop Join 中首先选择驱动表和被驱动表，然后应用驱动表的 where 条件，然后 join，Join 完后再判断被驱动表的其他 where 条件

总结：在 MySQL 8.0.18 及后续版本中，Hash Join 是默认的等值 JOIN 算法（无索引时优先使用）。若驱动表有高效索引，优化器可能仍会选择 Nested Loop Join，如果是 8.0.18 之前版本，没有索引就用 BNL。

当驱动表的哈希表超出 Join Buffer 内存限制时，MySQL 会启用 分块处理（Grace Hash Join） ：

  1. 分块（Partitioning） ：

    • 将驱动表和被驱动表按哈希值分成多个小块（Partitions），每个块对应一个磁盘临时文件。
    • 分块时使用相同的哈希函数，确保相同键值的行落在同一块中。

  2. 逐块处理：

    • 对每一块，先将驱动表的小块加载到内存构建哈希表，再用被驱动表的对应小块探测匹配。
    • 重复此过程直到所有块处理完成。

5. group by + limit 语句执行流程

表结构：

create table t1(id int primary key auto_increment, a int , b int, c int , index (a), index bc(b,c))engine=innodb;

insert into t1(a,b,c) values(1,1,1),(2,2,2),(3,3,3),(4,4,4); insert into t1(a,b,c) select a,b,c from t1;//重复插入到16384行 insert into t1(a,b,c) values(5,5,5);

insert into t1(a,b,c) select a,b,c from t1;  
insert into t1(a,b,c) select a,b,c from t1; //总共65540行

请分析语句 select c from t1 where a=3 and b>1 order by b limit 300,10 执行流程

首先 a 和 (b,c) 都是有索引的，但是 a 是单独索引，b,c 是联合索引，a 索引要小些，并且是等值查询，因此 MySQL 更大可能会选择索引 a，尽管 b,c 可以避免后面的 order by 排序

在选择索引 a 的情况下，server 层从引擎层拿到了 1w+ 条 a = 3 的行数据，回表，查询主键索引得到 1w+ 行，引擎层一行行返回给 server 层，server 层在 sort buffer 中构建一个大小为 310 的堆（优先队列排序），然后一行行的将行数据插入该堆，完成后取前 10 个返回给 client，整体的 rows_examined 为引擎层返回给 server 层的数量，后面插入堆并返回的过程并不计入（原因是 rows_examined 是引擎层返回给 server 的行数，不回表就是索引扫描行数，回表就是返回给 server 层的行数）

上述执行流程是我们预估的，MySQL 在选择索引时会计算各种数据后得出，并且并不一致（多次可能选择索引不同，原因是统计数据可能发生变化），可以考虑使用 explain 得到执行流程，但 explain 也是一个预估的值，如果我们想得到确切的执行流程，可以在从库执行下列命令：

explain analyze select ……

该语句是 MySQL 8.0 后提供的，会真实执行，所有的 DML 语句都可以执行（如果是 insert、update、delete，要求不能是单表简单语句，支持带 join 的 update、delete），但是 MySQL 在执行携带 explain analyze 的语句时，如果需要真实修改数据，会跳过修改流程，这样保证语句执行的真实性以及不破坏现有数据，并且得到的执行消耗也是基本准确的，该语句执行结果为：

image.png

结果解读方式：如果是并排，从上到下查看，如果有缩进，从下往上查看，以上述结果为例，其执行流程为：

  1. 在索引 a 上进行 index lookup，得到 16384 行数据
  2. server 层进行 filter，得到 16384 行数据
  3. sort buffer 使用优先队列排序，得到 310 行数据（如果是快排或者是归并排序，rows 应该为 16384，如果还是觉得不能确认，可以打开 optimize trace 进行确认）
  4. 最后从 310 行中取 10 行返回，整个语句 rows_examined=16384

6. 问答

  1. 目前磁盘能干的活有哪些？

    透明加密、透明压缩

  2. 慢查询影响性能会不会造成其他 MySQL 连接断开？

    不会，只会影响当前连接，如果其他线程断开了，很大可能是超时或者被 DBA 主动关闭，比如事务执行时间过长，DBA 发现后主动 kill

  3. 覆盖索引是否回表，对 rows_examined 有何影响？

    如果回表，rows_examined 为回表行数（比如索引扫描 10 行，通过 ICP 等过滤了 2 行， 回表 8 行，那 rows_examined 就为 8），如果不回表，rows_examined 为索引扫描行数

  4. 生产环境大表加索引，有什么建议？

    低峰期做，因为不管是 MySQL 自带的 inplace 算法，还是 PT 或者 gh-ost 工具算法，在应用 log 期间，都会有一段时间需要禁读写，低峰期能保证 log 不会有太多并且执行禁读写时间不会太长

  5. 如果线上突然出了一大堆慢查询日志，如何找出造成慢查询的那条语句？

    • 按照慢查询日志集中爆发的时间或者是 cpu 飙升的时间往前推数条语句，很大可能罪魁祸首就在那几条语句中
    • 看慢查询的 rows_examined
    • 如果慢查询有开启 log_slow_extra，查看 sort_rows、created_tmp_disk_tables、created_tmp_tables
    • 如果是云上环境，有开审计日志，将审计日志拿下来，开一个临时实例，将状态恢复到出问题的时间点前，将所有审计日志拿出来重新打一遍，复现出来

  6. 在 RC 和 RR 之间选哪个？

    业务如果没有强行要求，就选 RC，因为 RC 会少很多锁，最显著的是事务中扫描到的行，如果不满足要求会直接解锁，并且没有间隙锁，并发会大很多，且很多系统是从 oracle 迁移过来的，oracle 默认 RC，因此使用 RC 能保证迁移正确性，但有些场景要求 RR，比如逻辑备份，但这种可以会话级别单独配置

  7. alter table 语句是否需要手动指定算法？

    一般不要手动指定算法

  8. delete 语句会加锁吗？

    delete 语句会加锁，逐行加锁，普通 selete 语句不加锁，因此 delete 语句对普通 select 语句没有任何影响

  9. 引擎层能做什么？

    最左前缀和索引下推 ICP，其余操作都是在 server 层做的，引擎层能做的是跟 B+ 树检索相关的操作（根据 server 层的要求将行返回给 server 层），其余复杂逻辑比如 join、排序等都是在 server 层做的

  10. MySQL 的 HA 方案是怎么做的，以及 MGR

    一般通过代理 proxy 实现

  11. 在 RC 和 RR 级别下，没有主键的表，insert 一行语句会加什么锁？

    没有显示声明主键，MySQL 会默认加一个 6 字节主键，insert 一般不会加锁，直接插入，除非另外有事务加了间隙锁，会导致该 insert 等待，innodb 加锁的逻辑是用到就加

  12. 两阶段锁和两阶段提交分别指什么？

    • 两阶段锁：事务中加的锁（用到才申请）在事务提交时才释放
    • 两阶段提交：事务提交时先 prepare、等 binlog 写完后再 commit

  13. join 里面的条件跟放在外面的 where 中有什么区别？ 首先是执行流程，on 中条件是在 join 时用的，where 是在 join 后用的，其次对结果也会有影响，如果放在 on 里面，那么不匹配也会保留，只是会将被驱动表数据置为 null，如果是放在 where 中，则会将不存在的行过滤掉，语意是不同的

avatar
文章
阅读
粉丝