MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
43177 人已学习
课程目录
已完结 48 讲
0/4登录后,你可以任选4讲全文学习。
开篇词 (1讲)
开篇词 | 这一次,让我们一起来搞懂MySQL
免费
基础篇 (8讲)
01 | 基础架构:一条SQL查询语句是如何执行的?
02 | 日志系统:一条SQL更新语句是如何执行的?
03 | 事务隔离:为什么你改了我还看不见?
04 | 深入浅出索引(上)
05 | 深入浅出索引(下)
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
07 | 行锁功过:怎么减少行锁对性能的影响?
08 | 事务到底是隔离的还是不隔离的?
实践篇 (37讲)
09 | 普通索引和唯一索引,应该怎么选择?
10 | MySQL为什么有时候会选错索引?
11 | 怎么给字符串字段加索引?
12 | 为什么我的MySQL会“抖”一下?
13 | 为什么表数据删掉一半,表文件大小不变?
14 | count(*)这么慢,我该怎么办?
15 | 答疑文章(一):日志和索引相关问题
16 | “order by”是怎么工作的?
17 | 如何正确地显示随机消息?
18 | 为什么这些SQL语句逻辑相同,性能却差异巨大?
19 | 为什么我只查一行的语句,也执行这么慢?
20 | 幻读是什么,幻读有什么问题?
21 | 为什么我只改一行的语句,锁这么多?
22 | MySQL有哪些“饮鸩止渴”提高性能的方法?
23 | MySQL是怎么保证数据不丢的?
24 | MySQL是怎么保证主备一致的?
25 | MySQL是怎么保证高可用的?
26 | 备库为什么会延迟好几个小时?
27 | 主库出问题了,从库怎么办?
28 | 读写分离有哪些坑?
29 | 如何判断一个数据库是不是出问题了?
30 | 答疑文章(二):用动态的观点看加锁
31 | 误删数据后除了跑路,还能怎么办?
32 | 为什么还有kill不掉的语句?
33 | 我查这么多数据,会不会把数据库内存打爆?
34 | 到底可不可以使用join?
35 | join语句怎么优化?
36 | 为什么临时表可以重名?
37 | 什么时候会使用内部临时表?
38 | 都说InnoDB好,那还要不要使用Memory引擎?
39 | 自增主键为什么不是连续的?
40 | insert语句的锁为什么这么多?
41 | 怎么最快地复制一张表?
42 | grant之后要跟着flush privileges吗?
43 | 要不要使用分区表?
44 | 答疑文章(三):说一说这些好问题
45 | 自增id用完怎么办?
特别放送 (1讲)
直播回顾 | 林晓斌:我的 MySQL 心路历程
结束语 (1讲)
结束语 | 点线网面,一起构建MySQL知识网络
MySQL实战45讲
登录|注册

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

林晓斌 2018-12-26
一般情况下,如果我跟你说查询性能优化,你首先会想到一些复杂的语句,想到查询需要返回大量的数据。但有些情况下,“查一行”,也会执行得特别慢。今天,我就跟你聊聊这个有趣的话题,看看什么情况下,会出现这个现象。
需要说明的是,如果 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/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(93)

  • 某、人
    最近几张干货越来越多了,很实用,收获不少.先回答今天的问题
    版本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
    1
    52
  • 薛畅
    回来老师的问题:
    在 Read Committed 隔离级别下,会锁上聚簇索引中的所有记录;
    在 Repeatable Read 隔离级别下,会锁上聚簇索引中的所有记录,并且会锁上聚簇索引内的所有 GAP;
    在上面两个隔离级别的情况下,如果设置了 innodb_locks_unsafe_for_binlog 开启 semi-consistent read 的话,对于不满足查询条件的记录,MySQL 会提前放锁,不过加锁的过程是不可避免的。
    2018-12-26
    22
  • 似水流年
    请问老师,为什么select blocking_pid from sys.schema_table_lock_waits;查不到mdl锁的进程id,显示为空。
    2018-12-28
    3
    17
  • 沙漠里的骆驼
    @高枕
    这里有些资料提供给你参考:
    1. 何登成的技术博客: 加锁分析 http://hedengcheng.com/?p=771
    2. 锁的常见种类: http://www.aneasystone.com/archives/2017/11/solving-dead-locks-two.html
    2018-12-26
    13
  • 老杨同志
    愉快的做一下思考题
    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
    8
  • 小确幸
    问一下:索引扫描与全表扫描,有什么异同点?

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

    2018-12-26
    7
  • 蠢蠢欲动的腹肌
    老师,您好
        我的mysql版本5.7.24,尝试的时候发现了如下问题
        锁住了表T
            mysql> lock table T write;
            Query OK, 0 rows affected (0.00 sec)
       另一个terminal查询时被阻塞,但是查不到blocking_pid ,这是什么情况呢
       mysql> select blocking_pid from sys.schema_table_lock_waits;
       Empty set (0.00 sec)
      ps:发现查询schema_table_lock_waits表与lock table的语句不能放在一个terminal执行,否则会报
    Table 'schema_table_lock_waits' was not locked with LOCK TABLES
    自行尝试的同学要注意下,老师有空的话也可以帮看看为什么。。。
    2018-12-28
    2
    5
  • Tony Du
    对于课后问题,select * from t where c=5 for update,
    当级别为RR时,因为字段c上没有索引,会扫主键索引,这时会把表中的记录都加上X锁。同时,因为对于innodb来说,当级别为RR时,是可以解决幻读的,此时对于每条记录的间隙还要加上GAP锁。也就是说,表上每一条记录和每一个间隙都锁上了。
    当级别为RC时,因为字段c上没有索引,会扫主键索引,这时会把表中的记录都加上X锁。

    另外,之前看过相关文章,MySQL在实际实现中有些优化措施,比如当RC时,在MySQL server过滤条件,发现不满足后,会把不满足条件的记录释放锁(这里就是把 c!=5的记录释放锁),这里会违背两阶段的约束。当然,之前每条记录的加锁操作还是不能省略的。
    还有,对于semi consistent read开启的情况下,也会提前释放锁。
    2018-12-27
    5
  • 张永志
    RR隔离级别下,为保证binlog记录顺序,非索引更新会锁住全表记录,且事务结束前不会对不符合条件记录有逐步释放的过程。

    作者回复: 准确

    2018-12-27
    5
  • 张永志
    RC隔离级别下,对非索引字段更新,有个锁全表记录的过程,不符合条件的会及时释放行锁,不必等事务结束时释放;而直接用索引列更新,只会锁索引查找值和行。


    2018-12-27
    5
  • 尘封
    课后问题:d这一列不存在,但是还是要加MDL锁,释放时间应该是事务提交时。

    作者回复: 抱歉,是要写成where c=5 , 发起堪误了

    2018-12-26
    5
  • 残天噬魂
    为啥我开启了性能监控,按照案例1执行加锁,select * from sys.schema_table_lock_waits;这个没有结果啊
    2019-07-09
    1
    4
  • xm
    @Elvis
    Elvis

    0
    老师,最近项目mysql遇到一个难题, 表数据每天230万,一条语句的查询条件有1—40个,最坏情况下40,请问老师有没有好的建议,非常感谢
    2018-12-27
     作者回复
    这个是索引最头大的问题之一了… 如果有明显的某类组合查询是最多的还好,否则确实很麻烦。

    我觉得应对这种大数据量的多条件的查询的话换种思路,将mysql复杂的组合查询条件导入到es中作为key,主键id作为value,复杂的查询经过es后得到主键id,之后走mysql会好很多,目前公司是这样做的,老师觉得怎么样?

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

    2018-12-28
    3
  • 尘封
    老师,有没有遇到过select语句一直处于killed状态的情况?

    作者回复: 有😄 这个是在后面的文章中会用到的例子

    2018-12-26
    3
  • godtrue
    课前思考
    1:为啥只查一行的语句,也执行这么慢?
    查的慢,基本上就是索引使用的问题,和查一行还是N行(N不是巨大),没有必然联系。查一行慢,猜测没有走索引查询,且数据量比较大。
    课后思考
    1:阅后发现自己的无知,只查询一行的语句,也比较慢,原因从大到小可分为三种情况?
    第一MySQL数据库本身被堵住了,比如:系统或网络资源不够
    第二SQL语句被堵住了,比如:表锁,行锁等,导致存储引擎不执行对应的SQL语句
    第三确实是索引使用不当,没有走索引
    第四是表中数据的特点导致的,走了索引,但回表次数庞大
    感谢老师的分享,真是醍醐灌顶呀😄
    2019-07-31
    2
  • 浩涛
    老师,我们公司今年打算迁数据库,也就是oracle迁到mysql,目前要决定选用哪个版本和分支,不知道要怎么分析,您有什么好的建议吗?

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

    2019-03-11
    2
  • 小李子
    老师,为什么session B 执行了 select in share mode ,在等行锁的时候,session C 执行
    select * from sys.innodb_lock_waits where locked_table='`test`.`t`' 会报这个错
    [Err] 1356 - View 'sys.innodb_lock_waits' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them,而超时之后,又可以查了?另外,\G 参数会报语法错误?
    2018-12-27
    2
  • 信信
    老师你好,图3上方提到MySQL 5.7 版本修改了 MDL 的加锁策略,不能复现第六章的场景。但我认为只要仍然满足:DML操作加MDL读锁,DDL操作加MDL写锁,并且事务提交才释放锁,那么就可以复现啊。。。所以5.7到底是改了什么导致无法复现的呢?
    2018-12-27
    1
    2
  • 某、人
    老师我请教一个问题:
    flush tables中close table的意思是说的把open_tables里的表全部关闭掉?下次如果有关于某张表的操作
    又把frm file缓存进Open_table_definitions,把表名缓存到open_tables,还是open_table只是一个计数?
    不是特别明白flush table和打开表是个什么流程

    作者回复: Flush tables是会关掉表,然后下次请求重新读表信息的

    第一次打开表其实就是open_table_definitions,包括读表信息一类的

    之后再有查询就是拷贝一个对象,加一个计数这样的

    2018-12-26
    2
  • 陈旭
    老师,最近遇到了一个问题,看您有什么建议。
    业务场景是这样的:
    1.开启事务
    2.在表a插入一条记录
    3.在表b更新一条记录
    4.在表c更新一条记录
    5.提交事务
    看程序日志所有sql都没问题(没看数据库日志),但是结果是2的那条插入了,3和4都没更新,这个问题有哪几种情况?

    作者回复: 这是被别的并发事务又改回去了吗😓
    要么是update的值跟原值相同
    要么是update条件没有匹配到行
    额,最好给一下每个语句执行后的affacted rows , 还有binlog里的日志内容,才好分析

    2018-12-26
    1
    2
收起评论
93
返回
顶部