MySQL学习笔记8——慢查询

最新推荐文章于 2024-09-20 18:04:59 发布

耶律妙月

最新推荐文章于 2024-09-20 18:04:59 发布

阅读量714

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_36089832/article/details/103726457

版权

mysql 专栏收录该内容

16 篇文章 3 订阅

订阅专栏

文章目录

一、简单语句的慢查询
二、kill 命令
思考题

一、简单语句的慢查询

假设表有两个字段 id 和 c，并且在里面插入了 10 万行记录。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

1.查询长时间不返回

select * from t where id=1;

一般都是先执行一下 show processlist 命令，看看当前语句处于什么状态。

等 MDL 锁

在这里插入图片描述

复现步骤如上：
session A 通过 lock table 命令持有表 t 的 MDL 写锁，而 session B 的查询需要获取 MDL 读锁。所以，session B 进入等待状态。

这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。

通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。（MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失)
在这里插入图片描述

等 flush

在这里插入图片描述

这个状态表示的是，现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

flush tables t with read lock;

flush tables with read lock;

如果指定表 t 的话，代表的是只关闭表 t；如果没有指定具体的表名，则表示关闭 MySQL 里所有打开的表。

但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。

所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句。
在这里插入图片描述

等行锁

在这里插入图片描述
显然，session A 启动了事务，占有写锁，还不提交，是导致 session B 被堵住的原因。

如果是 MySQL 5.7 版本，可以通过 sys.innodb_lock_waits 表查到是谁占着这个写锁。
在这里插入图片描述
4 号线程是造成堵塞的罪魁祸首。而干掉这个罪魁祸首的方式，就是 KILL QUERY 4 或 KILL 4。

不过，这里不应该显示“KILL QUERY 4”。这个命令表示停止 4 号线程当前正在执行的语句，而这个方法其实是没有用的。因为占有行锁的是 update 语句，这个语句已经是之前执行完成了的，现在执行 KILL QUERY，无法让这个事务去掉 id=1 上的行锁。

实际上，KILL 4 才有效，也就是说直接断开这个连接。这里隐含的一个逻辑就是，连接被断开的时候，会自动回滚这个连接里面正在执行的线程，也就释放了 id=1 上的行锁。

2.查询慢

条件没有索引

select * from t where c=50000 limit 1;

这个语句只能走 id 主键顺序扫描，因此需要扫描 5 万行。

长事务

select * from t where id=1；
在这里插入图片描述
虽然扫描行数是 1，但执行时间却长达 800 毫秒。

select * from t where id=1 lock in share mode
在这里插入图片描述
执行时扫描行数也是 1 行，执行时间是 0.2 毫秒。

在这里插入图片描述

session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。

带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。

注意，undo log 里记录的其实是“把 2 改成 1”，“把 3 改成 2”这样的操作逻辑。

二、kill 命令

在 MySQL 中有两个 kill 命令：一个是kill query + 线程 id，表示终止这个线程中正在执行的语句；一个是 kill connection + 线程 id，这里 connection 可缺省，表示断开这个线程的连接，如果这个线程有语句正在执行，也是要先停止正在执行的语句的。

执行一个查询的过程中，发现执行时间太久，要放弃继续查询，这时我们就可以用kill query + 线程 id 命令，终止这条查询语句。

还有一种情况是，语句处于锁等待的时候，直接使用 kill 命令也是有效的。
在这里插入图片描述

当用户执行 kill query thread_id_B 时，MySQL 里处理 kill 命令的线程做了两件事：

把 session B 的运行状态改成 THD::KILL_QUERY(将变量 killed 赋值为 THD::KILL_QUERY)；
给 session B 的执行线程发一个信号。

为什么要发信号呢？

因为像图 1 的我们例子里面，session B 处于锁等待状态，如果只是把 session B 的线程状态设置 THD::KILL_QUERY，线程 B 并不知道这个状态变化，还是会继续等待。发一个信号的目的，就是让 session B 退出等待，来处理这个 THD::KILL_QUERY 状态。

上面的分析中，隐含了这么三层意思：

一个语句执行过程中有多处“埋点”，在这些“埋点”的地方判断线程状态，如果发现线程状态是 THD::KILL_QUERY，才开始进入语句终止逻辑；
如果处于等待状态，必须是一个可以被唤醒的等待，否则根本不会执行到“埋点”处；
语句从开始进入终止逻辑，到终止逻辑完全完成，是有一个过程的。

所以，如果走不到埋点，无法判断线程状态，就会发生 kill 不掉的情况。
在这里插入图片描述
比如并发查询到上限时，sesssion C 执行的时候被堵住了；session D 执行的 kill query C 命令没有反应，直到 session E 执行了 kill connection 命令，才断开了 session C 的连接。

此时，在 session E 中执行 show processlist
在这里插入图片描述

id=12 这个线程的 Commnad 列显示的是 Killed。也就是说，客户端虽然断开了连接，但实际上服务端上这条语句还在执行过程中。

在实现上，等行锁时，使用的是 pthread_cond_timedwait 函数，这个等待状态可以被唤醒。但是，在这个例子里，12 号线程的等待逻辑是这样的：每 10 毫秒判断一下是否可以进入 InnoDB 执行，如果不行，就调用 nanosleep 函数进入 sleep 状态。

也就是说，虽然 12 号线程的状态已经被设置成了 KILL_QUERY，但是在这个等待进入 InnoDB 的循环过程中，并没有去判断线程的状态，因此根本不会进入终止逻辑阶段。

而当 session E 执行 kill connection 命令时，是这么做的，

把 12 号线程状态设置为 KILL_CONNECTION；
关掉 12 号线程的网络连接。因为有这个操作，所以你会看到，这时候 session C 收到了断开连接的提示。

在执行 show processlist 的时候，有一个特别的逻辑：

如果一个线程的状态是KILL_CONNECTION，就把Command列显示成Killed。

只有等到满足进入 InnoDB 的条件后，session C 的查询语句继续执行，然后才有可能判断到线程状态已经变成了 KILL_QUERY 或者 KILL_CONNECTION，再进入终止逻辑阶段。

还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态。

还有终止逻辑耗时较长。常见于：

超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。
大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。

如果直接在客户端通过 Ctrl+C 命令，是 MySQL 客户端另外启动一个连接，然后发送一个kill query 命令。

所以，如果你发现一个线程处于 Killed 状态，你可以做的事情就是，通过影响系统环境，让这个 Killed 状态尽快结束。

如果是 InnoDB 并发度的问题，你就可以临时调大 innodb_thread_concurrency 的值，或者停掉别的线程，让出位子给这个线程执行。
如果是回滚逻辑由于受到 IO 资源限制执行得比较慢，就通过减少系统压力让它加速。

做完这些操作后，其实你已经没有办法再对它做什么了，只能等待流程自己完成。

思考题

1、select * from t where id=1 lock in share mode;由于 id 上有索引，所以可以直接定位到 id=1 这一行，因此读锁也是只加在了这一行上。但如果是下面的 SQL 语句，

begin;
select * from t where c=5 for update;
commit;

这个语句序列是怎么加锁的呢？加的锁又是什么时候释放呢？

答：由于c字段没有加索引，所以会全表扫描，并且每一行都加上行锁，还会加上间隙锁（RR级别下）。
读提交隔离级别下，在语句执行完成后，是只有行锁的。而且语句执行完成后，InnoDB 就会把不满足条件的行行锁去掉。当然了，c=5 这一行的行锁，还是会等到 commit 的时候才释放的。

可重复读隔离级别下，所有的锁都是在commit时释放。

2、假设现在表里面，有 100 万行数据，其中有 10 万行数据的 b 的值是’1234567890’

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

select * from table_a where b='1234567890abcd';
这时候，MySQL 会怎么执行呢？

答：最理想的情况是，MySQL 看到字段 b 定义的是 varchar(10)，那肯定返回空呀。可惜，MySQL 并没有这么做。

那要不，就是把’1234567890abcd’拿到索引里面去做匹配，肯定也没能够快速判断出索引树 b 上并没有这个值，也很快就能返回空结果。但实际上，MySQL 也不是这么做的。

这条 SQL 语句的执行很慢，流程是这样的：

在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是 10，所以只截了前 10 个字节，就是’1234567890’进去做匹配；这样满足条件的数据有 10 万行；
因为是 select *，所以要做 10 万次回表；
但是每次回表以后查出整行，到 server 层一判断，b 的值都不是’1234567890abcd’;
返回结果是空。

3、如果你碰到一个被 killed 的事务一直处于回滚状态，你认为是应该直接把 MySQL 进程强行重启，还是应该让它自己执行完成呢？为什么呢？

答：因为重启之后该做的回滚动作还是不能少的，所以从恢复速度的角度来说，应该让它自己结束。

当然，如果这个语句可能会占用别的锁，或者由于占用 IO 资源过多，从而影响到了别的语句执行的话，就需要先做主备切换，切到新主库提供服务。

切换之后别的线程都断开了连接，自动停止执行。接下来还是等它自己执行完成。这个操作属于我们在文章中说到的，减少系统压力，加速终止逻辑。

参考资料：林晓斌——MySQL实战45讲