MySQL 实战 45 讲
林晓斌
网名丁奇,前腾讯云数据库负责人
224873 人已学习
新⼈⾸单¥68
登录后,你可以任选4讲全文学习
课程目录
已完结/共 49 讲
实践篇 (37讲)
特别放送 (1讲)
结课测试 (1讲)
MySQL 实战 45 讲
15
15
1.0x
00:00/00:00
登录|注册

19 | 为什么我只查一行的语句,也执行这么慢?

解决方法
复现步骤
输出结果
慢查询日志
慢查询日志
解决方法
复现步骤
解决方法
复现步骤
解决方法
复现步骤
加锁读
扫描一行却执行得很慢
全表扫描
等行锁
等flush
等MDL锁
第二类:查询慢
第一类:查询长时间不返回
上期问题时间
小结
为什么我只查一行的语句,也执行这么慢?

该思维导图由 AI 生成,仅供参考

一般情况下,如果我跟你说查询性能优化,你首先会想到一些复杂的语句,想到查询需要返回大量的数据。但有些情况下,“查一行”,也会执行得特别慢。今天,我就跟你聊聊这个有趣的话题,看看什么情况下,会出现这个现象。
需要说明的是,如果 MySQL 数据库本身就有很大的压力,导致数据库服务器 CPU 占用率很高或 ioutil(IO 利用率)很高,这种情况下所有语句的执行都有可能变慢,不属于我们今天的讨论范围。
为了便于描述,我还是构造一个表,基于这个表来说明今天的问题。这个表有两个字段 id 和 c,并且我在里面插入了 10 万行记录。
mysql> 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 所示,在表 t 执行下面的 SQL 语句:
mysql> select * from t where id=1;
查询结果长时间不返回。
图 1 查询长时间不返回
一般碰到这种情况的话,大概率是表 t 被锁住了。接下来分析原因的时候,一般都是首先执行一下 show processlist 命令,看看当前语句处于什么状态。
然后我们再针对每种状态,去分析它们产生的原因、如何复现,以及如何处理。

等 MDL 锁

确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文深入探讨了查询性能优化中可能出现的问题及解决方法。作者通过构造一个包含10万行记录的表,展示了即使是查询单行数据,也可能出现执行缓慢的情况。文章详细分析了表被锁住的情况,包括等MDL锁、等flush或等行锁导致的情况,并提供了相应的处理方法。通过具体案例和复现步骤,读者可以了解查询性能优化中可能遇到的问题及解决方法。文章还提出了一个问题,引发读者思考并留下观点和验证方法。整体而言,本文通过具体案例和深入分析,为读者提供了宝贵的查询性能优化经验。

仅可试看部分内容,如需阅读全部内容,请付费购买文章所属专栏
《MySQL 实战 45 讲》
新⼈⾸单¥68
立即购买
登录 后留言

全部留言(130)

  • 最新
  • 精选
  • 某、人
    最近几张干货越来越多了,很实用,收获不少.先回答今天的问题 版本5.7.13 rc模式下: session 1: begin; select * from t where c=5 for update; session 2: delete from t where c=10 --等待 session 3: insert into t values(100001,8) --成功 session 1: commit session 2:事务执行成功 rr模式下: begin; select * from t where c=5 for update; session 2: delete from t where c=10 --等待 session 3: insert into t values(100001,8) --等待 session 1: commit session 2:事务执行成功 session 3:事务执行成功 从上面这两个简单的例子,可以大概看出上锁的流程. 不管是rr模式还是rc模式,这条语句都会先在server层对表加上MDL S锁,然后进入到引擎层。 rc模式下,由于数据量不大只有10W。通过实验可以证明session 1上来就把该表的所有行都锁住了。 导致其他事务要对该表的所有现有记录做更新,是阻塞状态。为什么insert又能成功? 说明rc模式下for update语句没有上gap锁,所以不阻塞insert对范围加插入意向锁,所以更新成功。 session 1commit后,session 2执行成功。表明所有行的x锁是在事务提交完成以后才释放。 rr模式下,session 1和session 2与rc模式下都一样,说明rr模式下也对所有行上了X锁。 唯一的区别是insert也等待了,是因为rr模式下对没有索引的更新,聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。由于gap锁阻塞了insert要加的插入意向锁,导致insert也处于等待状态。只有当session 1 commit完成以后。session 1上的所有锁才会释放,S2,S3执行成功 由于例子中的数据量还比较小,如果数据量达到千万级别,就比较直观的能看出,上锁是逐行上锁的一个过程.扫描一条上一条,直到所有行扫描完,rc模式下对所有行上x锁。rr模式下不仅对所有行上X锁,还对所有区间上gap锁.直到事务提交或者回滚完成后,上的锁才会被释放。

    作者回复: 分析得非常好。 两个模式下,各增加一个session 4 : update t set c=100 where id=10看看哦 基本就全了👍🏿

    2018-12-26
    21
    203
  • 张永志
    RR隔离级别下,为保证binlog记录顺序,非索引更新会锁住全表记录,且事务结束前不会对不符合条件记录有逐步释放的过程。

    作者回复: 准确

    2018-12-27
    8
    123
  • 老杨同志
    愉快的做一下思考题 begin; select * from t where c=5 for update; commit; 历史知识的结论是,innodb先锁全表的所有行,返回server层,判断c是否等于5,然后释放c!=5的行锁。 验证方法: 事务A执行 锁住一行c!=5的记录 比如id =3 c=3 select * from t where id = 3 for update 或者 update t set c=4 where id =3 然后启动新事务B执行上面的语句select * from t where c=5 for update; 看看有没有被阻塞。 用于判断事务B的语句会不会试图锁不满足条件的记录。 然后把事务A和事务B的执行顺序对调一下,也就是先执行B在执行A。看看有没有阻塞, 判断在事务B加锁成功的情况下会不会释放不满足查询条件记录的行锁。

    作者回复: 👍🏿 思路清晰 隔离级别再愉快地改成RR试试😄

    2018-12-26
    6
    53
  • 小确幸
    问一下:索引扫描与全表扫描,有什么异同点?

    作者回复: 一般说全表扫描默认是值“扫瞄主键索引”

    2018-12-26
    2
    34
  • xm
    @Elvis Elvis  0 老师,最近项目mysql遇到一个难题, 表数据每天230万,一条语句的查询条件有1—40个,最坏情况下40,请问老师有没有好的建议,非常感谢 2018-12-27  作者回复 这个是索引最头大的问题之一了… 如果有明显的某类组合查询是最多的还好,否则确实很麻烦。 我觉得应对这种大数据量的多条件的查询的话换种思路,将mysql复杂的组合查询条件导入到es中作为key,主键id作为value,复杂的查询经过es后得到主键id,之后走mysql会好很多,目前公司是这样做的,老师觉得怎么样?

    作者回复: 嗯,这个是一种思路,见过这么干的😄

    2018-12-28
    2
    32
  • 复兴
    lock table t1 write这个写锁跟mdl写锁,本质上是不是一个东西?

    作者回复: 在5.5及以后的版本中,lock table t1 write 就是通过加mdl写锁,来阻止其他线程访问的

    2019-05-28
    22
  • 孙志强
    亲爱的老师,我这里有一个问题?困惑好久 一致性读为啥还要处理别的事务回滚日志?一致性读不是直接返回视图里的值吗?undo log在没有事务使用的时候会清除掉?

    作者回复: 你再看下08篇哈 简单说,一致性视图只是一个数组+一个高水位,是要靠undo log来获取老版本的数据的

    2019-03-22
    2
    13
  • 滔滔
    老师,我有个问题问,在讲隔离级别的时候讲过可重复读级别下,默认读操作(select查询,不是当前读)读到的是某条数据的某个历史版本,而所有这些历史版本数据都是以undo log的形式存在的对吗?需要找某个历史版本数据就直接执行对应undo log,而不是事先把历史版本的数据保存下来,是这样的吗?

    作者回复: 是的

    2018-12-28
    9
  • 浩涛
    老师,我们公司今年打算迁数据库,也就是oracle迁到mysql,目前要决定选用哪个版本和分支,不知道要怎么分析,您有什么好的建议吗?

    作者回复: 目前5.7是比较主流的,就用最新GA版本吧,记得做全量回归测试

    2019-03-11
    6
  • 所以,出现 Waiting for table flush 状态的可能情况是:有一个 flush tables 命令被别的语句堵住了,然后它又堵住了我们的 select 语句。 记得之前文章讲 flush table会导致该表 不能增改删 但是为什么session c的查询也会被block呢

    作者回复: Flush 过程中,查也不行的

    2018-12-26
    5
收起评论
显示
设置
留言
99+
收藏
沉浸
阅读
分享
手机端
快捷键
回顶部