MySQL实战45讲
林晓斌
网名丁奇,前阿里资深技术专家
立即订阅
42653 人已学习
课程目录
已完结 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讲
登录|注册

22 | MySQL有哪些“饮鸩止渴”提高性能的方法?

林晓斌 2019-01-02
不知道你在实际运维过程中有没有碰到这样的情景:业务高峰期,生产环境的 MySQL 压力太大,没法正常响应,需要短期内、临时性地提升一些性能。
我以前做业务护航的时候,就偶尔会碰上这种场景。用户的开发负责人说,不管你用什么方案,让业务先跑起来再说。
但,如果是无损方案的话,肯定不需要等到这个时候才上场。今天我们就来聊聊这些临时方案,并着重说一说它们可能存在的风险。

短连接风暴

正常的短连接模式就是连接到数据库后,执行很少的 SQL 语句就断开,下次需要的时候再重连。如果使用的是短连接,在业务高峰期的时候,就可能出现连接数突然暴涨的情况。
我在第 1 篇文章《基础架构:一条 SQL 查询语句是如何执行的?》中说过,MySQL 建立连接的过程,成本是很高的。除了正常的网络连接三次握手外,还需要做登录权限判断和获得这个连接的数据读写权限。
在数据库压力比较小的时候,这些额外的成本并不明显。
但是,短连接模型存在一个风险,就是一旦数据库处理得慢一些,连接数就会暴涨。max_connections 参数,用来控制一个 MySQL 实例同时存在的连接数的上限,超过这个值,系统就会拒绝接下来的连接请求,并报错提示“Too many connections”。对于被拒绝连接的请求来说,从业务角度看就是数据库不可用。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(67)

  • 某、人 置顶
    最近才发生了个案列:
    由于一个delete大事务导致磁盘空间满了,数据库hang住,连接不上,所以无法kill掉该大事务
    当时的观察到的现象是:
    binlog有一个文件已经达到50多G
    lsof | grep delete 该tmp文件100多G
    redo log还是只有4个组,每个文件1G
    undo log大概有100来G
    由于数据库连不上,只有把连接切到从库,kill掉主库的进程。过了几分钟,binlog文件才缩小为原来的大小。把主库启起来,但是recovery非常慢。后面kill掉,又以innodb_force_recovery=3恢复,recovery也是半天没反应。由于这个库也不是重要的库,就把新的主库的备份文件重做了之前的主库,以从库启起来

    通过最近的学习+测试分析了下,为什么binlog达到了50多G。tmp文件100多G.
    由于binlog_cache不够用,把binlog写进了tmp文件中,binlog文件50多G,说明事务已经执行完成,是binlog在fsync阶段,把空间占满了。fsync并不是一个move而是相当于copy。要等binlog完全落盘以后,才会删除之前的tmp文件。redo log由于是循环写,而且在事务执行过程中,就会把redo log分为mtx落地到磁盘上。所以没有一次性暴增,还是以1G的大小持续写.
    我也是后续做测试,观察在事务进行中,redo log文件一直都有变化。binlog没有变化
    binlog是在事务执行完以后,才一次性fsync到磁盘
    但是为什么recovery=3的情况下,还比较耗时。我估计是之前脏页较多,而redo log又全部被覆盖掉,
    需要先通过binlog来恢复redo log,然后再通过redo log来恢复数据页。

    请问老师有没有更好的办法来处理这种hang住的情况?
    如果在操作系统层面kill掉执行的线程,就好了。
    昨天提到的问题3,我也没有测试出来Sending to client这个状态.是之前别人问到的,我也挺懵

    作者回复: 先说明下,binlog是没有“恢复redolog”的能力的哈。其它部分分析得很好👍🏿

    Binlog 这么大,说明是大事务,崩溃恢复的时候要处理的redolog 很多,估计耗时间耗在这。

    这种磁盘空间满的情况,以前我的处理方法是把最老的binlog移动到别的盘(如果确定日志已经备份到备份系统了就删掉),目的是腾出空间让这个事务执行完成。
    后面可以考虑这种方案,强制重启还是有点伤的,不过核心还是做好监控,不让出现磁盘100%写满的情况

    2019-01-03
    2
    12
  • Long
    不是专业DBA,遇到过几次数据库问题,有的能解决,有的好像除了重启或者干等着没啥好办法。
    MySQL5.6版本遇到的部分问题:

    1. 几个线程处于killed状态一直kill不掉(1天),然后备份的时候MySQL backup flush all tables with read lock的时候被阻塞,后面的线程只能等待flush table, kill backup以后也没有办法kill那几个killed状态的语句(processlist显示的killed状态的语句的就是show columns, show create table这样的),后面没办法,重启了server。(看到老师后面第25讲有关于kill的解释,非常期待新知识)

    2. 一个非常大(大几百万行)的表truncate,结果后面所有的线程都阻塞了,类似于下面这个MySQL bug的场景,结果就是等这个truncate结束。没有继续干预。
    https://bugs.mysql.com/bug.php?id=80060

    3. 某个新功能上线以后,一个记录操作人员操作页面操作时间KPI的功能,由于sql性能不好,在业务上线跑了3天后数据量增多到临界值,突然影响了整个系统性能。数据库发现是大量的sql执行状态是converting heap to MyISAM,sql写法类似 select (select * from table) where id(有索引)= xxxx order by yyyy
    DBA以及他们团队要求重启。但是分析了几分钟后提供了几个意见给"DBA",并解释重启解决不了问题:首先这个问题重启是解决不了,因为每次这个sql查询全表,查询分配的临时表空间不足了,需要把结果集转到磁盘上,重启了sql动作没变,参数没变所以重启解决不了问题。
    页面查询也没法屏蔽,页面查询也无法过滤条件,
    (1)和研发确认后,表数据删除不影响功能,只影响客户的KPI报表,先备份表,然后删除,后面等功能修复了再补回去。
    (2)调整max_heap_table_size,tmp_table_size,扩大几倍
    (3)给这个sql的唯一的一个order by字段加个索引。
    同时催促研发提供hotfix。最终选择了最简单有效的(1)问题解决,研发迅速后面也发了hotfix解决了。

    4. 某个消费高峰时间段,高频查询被触发,一天几十万次执行,由于存量数据越来越多,查询性能越来越慢,主要是索引没有很好规划,导致CPU资源使用飙升,后面的sql执行越来越慢。 最后尝试了给2个字段添加单独的索引,解决了50%的问题,看到执行计划,extra里面,索引合并使用了intersect,性能还是慢,然后立马drop原先的2个单独索引,创建两个字段的联合索引,问题解决了。

    5. 死锁回滚,导致的MySQL hang住了,当时刚入门,只能简单复现死锁,没有保留所有的日志,现在想查也查不了了。。。
    感觉大部分都是慢sql和高频事务导致的。

    (当然后面的慢sql监控分析,项目上就很重视了。。)


    今天看了这期专栏,发现5.7的这个功能,query_rewrite,受教了。等我们升到5.7以后,可以实际操练下。上面的问题3,也可以用这个功能了(因为是新业务,新表,特殊sql,完全可以起到hotfix的作用)。


    请老师帮忙看下上面几次故障是否有更好,更专业的解决方案。多谢

    作者回复: 1. Kill 掉备份线程在当时是最好的办法了。不过我之前确实也没碰到过show create table 不能kill的情况,我看下代码,如果能复现出来加入那篇文章中
    2. 嗯,80060这个问题是因为要truncate,所以要回收脏页导致慢,不过这个问题在5.5.23就优化掉了哦,看你使用的是5.6,可能是别的原因。truncate如果不是被锁,而是已经在执行了,确实还是别做别的事情,等结束最好;
    3. 这个语句是因为子查询要用临时表,跟order by 无关的(你看到的阶段还没开始order by 操作)。这个语句的临时表都能多到把磁盘打满,增加tmp_table_size是没用的。
    就是说这三个方法里面2和3其实都无效。你们当时的选择很精准呀。
    而且前面提出“重启无效”的这个人值得团队内大力表扬(是不是就是你😄)
    另外这个语句,看着像有机会优化的样子,核心方向是去掉临时表
    4.可以只删掉其中一个独立索引,再加一个联合索引,就是变成(a,b)和(b)这两种索引,也就是把(a)改成(a,b),这样是加法,相对比较安全。删除索引是一个要很小心的操作,少删一个多一份安全,之后再通过观察索引b的使用情况,确定没必要再删。interset确实一般都比较慢。
    5. 正常回滚很快的,是不是大事务回滚?这种还是得从消除大事务入手

    2019-01-02
    23
  • Tony Du
    对于上期问题的解答,有一点不是特别理解,
    因为order by desc,在索引c上向左遍历,对于(15, 25)这段区间没有问题,
    然后,扫描到c=10才停下来,理论上把(10,15]这个区间锁上就应该是完备的了呀。(5,10]这段区间是否锁上对结果应该没有影响呀,为什么会需要(5,10] 这段的next-key lock ?

    作者回复: 就是这么实现的😓

    C=10还是要锁的,如果不锁可能被删除

    2019-01-02
    2
    17
  • null
    原文:

    在扫描过程中,c=20、c=15、c=10 这三行都存在值,由于是 select *,(所以会在主键 id 上加三个行锁。1)

    (主键索引上 id=15、20 两个行锁。2)

    老师,(1) 和 (2) 的描述,是否有出入吖?谢谢
    2019-06-12
    3
    10
  • Long
    老师好,看到有的同学在讨论锁的释放问题。

    之前分析过一个锁表异常,很多用workbench或者类似客户端的同学可能会遇到,
    复现方式:
    Step 1:显示的打开一个事务,或者把autocommit=0,或者mysql workbench中把自动提交的置灰按钮打开以后
    Step 2: 执行一个sql(比如,update或者delete之类的),然后sql还没有返回执行结果的中途点击workbench 自带的那个stop的红色的按钮。
    这个时候很多人可能就不再做其他操作,大多会认为执行已经结束了。但是实际上,锁还在继续锁着的并不会释放。

    系统日志记录:
    (1)processlist的状态是sleep,info为null
    (2)innodb_trx的状态是running,trx_query为null
    (3)performance_schema.events_statements_current表中的,
    sql_text,digest_text:是有正确的sql的。---这个5.6以后就有了,如果ps打开的话,应该是可以看到的。
    message_text :Query execution was interrupted
    (4)inoodb_locks,lock_waits,以及show engine innodb status,只有出现锁等待的时候才会记录,如果只有一个事务的记录行锁,或者表锁,是不会记录的。(不知道是否有参考控制,还是默认的)
    (5)关于行锁记录数的问题,从测试的结果看,inoodb_trx的locked rows,当我点停止的时候,锁定行数保持不变了,当我继续点击执行的时候,锁定记录行数会在之前的记录上向上累加,并不是从0开始。

    然后查了audit log以后发现,客户端(mysqlworkbench)送给server端的是KILL QUERY thread_id,而不是Kill thread_id,
    所以MySQL只是终止了事务中的statement执行,但是并不会释放锁,因为目前的琐的获取和释放都是基于事务结束的(提交或者回滚)。
    这里面关于kill query/ thread_id的区别解释
    https://dev.mysql.com/doc/refman/5.6/en/kill.html

    解决方法:
    自己解决:kill 对应的thread_id,或者关闭执行窗口(这个时候会送个quit给server端)。
    别人解决:有super权限的人kill thread_id。

    关于kill的那个文章,其实对所有DDL,DML的操作释放过程,还没有全部搞清楚,期待老师的第25讲。

    作者回复: 总结的非常好,而且现象很全面。
    核心的一个点是:kill query 只是终止当前执行语句,并不会让事务回滚👍🏿

    2019-01-02
    9
  • Ryoma
    我之前的描述有点问题,其实想问的是:为什么加了 order by c desc,第一个定位c=20 的行,会加上间隙锁 (20,25) 和 next-key lock (15,20]?

    如果没有order by c desc,第一次命中c=15时,只会加上next-key lock(10.15];
    而有了order by c desc,我的理解是第一次命中c=20只需要加上next-key lock (15,20]

    当然最后(20,25)还是加上了锁,老师的结论是对的,我也测试过了,但是我不知道如何解释。
    唯一能想到的解释是order by c desc 并不会改变优化2这个原则:即等值查询时,会向右遍历且最后一个值不满足等值条件;同时order by c desc 带来一个类似于优化2的向左遍历原则。
    进而导致最后的锁范围是(5,25);而没有order by c desc的范围是(10,25]。

    作者回复: 因为执行c=20的时候,由于要order by c desc, 就要先找到“最右边第一个c=20的行”,
    这个怎么找呢,只能向右找到25,才能知道它左边那个20是“最右的20”

    2019-01-03
    8
  • 某、人
    老师,我有几个问题:
    1.如果把order by去掉或者order by c asc,往右扫描,为什么没有加[25,30)next-key lock?
    2.执行session A,为什么slow log里的Rows_examined为2?按照答案来讲不应该是为3嘛
    3.thread states里sending data包括sending data to the client,
    另外还有一种state是Sending to client(5.7.8之前叫Writing to net)是writing a packet to the client.
    请问针对发送数据给客户端,这两种状态有什么区别?

    作者回复: 1. Next-key lock是前开后闭区间呀,有扫描到25,所以(20,25]

    2. Rows_examined 是server层统计的,这个不满足的值没返回给server

    3. 你show processlist 结果发我看下,代码中没搜到😓

    2019-01-02
    6
  • Tony Du
    对于上期问题的解答,有一点不是特别理解,
    因为order by desc,在索引c上向左遍历,对于(15, 25)这段区间没有问题,
    然后,扫描到c=10才停下来,理论上把(10,15]这个区间锁上就应该是完备的了呀。(5,10]这段区间是否锁上对结果应该没有影响呀,为什么会需要(5,10] 这段的next-key lock ?
    2019-01-02
     作者回复
    就是这么实现的😓

    C=10还是要锁的,如果不锁可能被删除

    我的回复:
    所以,如果把sql改成
    select * from t where c>=15 and c<=20 order by c asc lock in share mode;
    那锁的范围就应该是索引c上(10,25)了吧。
    同样查询条件,不同的order顺序,锁的范围不一样,稍微感觉有一点奇怪...

    作者回复: 嗯,因为执行索引遍历的顺序不一样,其实锁范围不一样也算合理啦😄

    2019-01-03
    1
    5
  • devil
    上期问题的答案有点不明白,为什么(5,10]会被加锁?第一个不满足条件的不是10吗?退化到间隙锁的情况也只会加锁(10,15],不退化应该是[10,15]

    另外主键索引到底锁了几行,上面一句写的锁了10,15,20。下面写的锁了15,20
    2019-03-22
    1
    4
  • Moby
    丁奇老师好,不好意思,学渣看得比较慢。关于前两期的问题,我有一点没搞懂。就是你说的:"session A 在 select 语句锁的范围是 1.... ; 2.在主键索引上id=10、15、20三个行锁”,经我测试(MySQL版本:5.7.17-log; 隔离级别:可重复读):“session
    A: begin; select * from t where c>=15 and c<=20 order by c desc lock in share mode;"、"session B: update t set c=1010 where id=10; Query ok"、”session C: update t set c=1515 where id=15;block...“。即:为什么id=10这一行可以更新数据?而id=15、20这两行更新数据就被阻塞?

    作者回复: 这没问题呀

    begin; select * from t where c>=15 and c<=20 order by c desc lock in share mode;
    锁的范围是这样的:
    索引c上,next-key lock: (5,10],(10,15],(15,20];
    索引id上,行锁: id=15和id=20

    2019-01-21
    4
  • 张永志
    分享一个主从切换时遇到的问题,主从切换前主库要改为只读,设置只读后,show master status发现binlog一直在变化,当时应用没断开。
    主库并不是其他库的从库,怎么搞的呢?
    检查业务用户权限发现拥有super权限,查看授权语句原来是grant all on *.* to user,这里要说的是*.* 权限就太大了,而且这个也很容易被误解,需要特别注意。

    作者回复: 对的,readonly对super无效;
    一方面是尽量不要给业务super
    一方面你做完readonly还会去确认binlog有没有变,这个意识很好哦

    2019-01-04
    4
  • mongo
    看完了《算法导论》那本书的前20章,看到了动态规划。再来看老师的专栏,发现我终于可以无障碍get到本专栏的知识了。

    作者回复: 在算法导论面前,这个专栏的内容算很浅的😆

    2019-07-18
    3
  • zws
    老师,如果不是专业的dba看着专栏是不是有点太深了。 老师可不可以把文章分下类,哪部分可以适合业务开发人员看。

    作者回复: 业务开发基本都能用上的哈😆

    2019-04-24
    3
  • 沙漠里的骆驼
    qps(查询语句)突然增大的情况,我们的实践是:
    1. 账号、接口级别的限流。
    2.引导到备库执行

    作者回复: 👍

    2019-02-19
    3
  • 刘昆
    老师你好,上期问题里面我遇到一下问题:
    insert into t values(6,5,6) => block
    insert into t values(4,5,6) => no block
    insert into t values(6,4,6) => no block
    insert into t values(7,5,6) => block
    insert into t values(7,4,6) => no block
    根据你的解答,c 上面的 next-key lock 在 (5, 10],那么上面的情况应该都不会阻塞还对呀?
    Server version: 5.7.24-log MySQL Community Server (GPL)

    作者回复: 是这样的,我们只是简写成(5,10],
    这个是索引c上的next-key lock,
    所以这个范围的左边界是 (c=5,id=5), 右边界是(c=10,id=10)

    你举例里面,
    insert into t values(6,5,6) 是(c=5, id=6);
    insert into t values(7,5,6) 是(c=5, id=7);
    这两个都落在上面的next-key lock的区间,所以是会被锁住的哦

    好问题, 新年快乐

    2019-02-02
    3
  • 曾剑
    老师,关于上期遗留问题的解答,我有一点疑惑:
    解答中的1中,第一个要定位的是索引 c 上“最右边的”c=20 的行,为啥只会加上间隙锁(20,25)和next-key lock(15,20]呢,为啥不是两个next-key lock(15,20]和(20,25]呢?25上的行锁难道是退化的?老师上一篇文章中说到加锁的基本原则中第一点是加锁的基本单位是next-key lock,而退化都是基于索引上的等值查询才会发生呀?盼老师指点迷津。

    作者回复: 就是优化2,找第一个值的时候是等值查询

    2019-01-02
    3
  • 李鑫磊
    用数据库连接池应该不存在短连接的情况的吧?

    作者回复: 看连接池怎么实现的了,不过确实大多数连接池的一个基本功能就是维护长连接

    2019-09-20
    2
  • 黄继立
    老师您好:
         首先要感谢您的分享,您以上的例子在我的线上环境都出现过。 一般情况都是慢sql 语句没有使用索引,我们所有线上的数据库,全部部署了实时kill 脚本,针对查询语句全部进行一个阀值的制定,例如是5秒,超过以后自动kill,这样会保证线上的稳定。 二就是在测试环境严格把控没有使用索引的语句。

    作者回复: 👍

    尤其是第二点,可以在测试环境就发现问题,是最好

    2019-03-02
    2
  • 不二
    老师,曾剑同学的问题
    关于上期遗留问题的解答,我有一点疑惑:
    解答中的1中,第一个要定位的是索引 c 上“最右边的”c=20 的行,为啥只会加上间隙锁(20,25)和next-key lock(15,20]呢,为啥不是两个next-key lock(15,20]和(20,25]呢?25上的行锁难道是退化的?老师上一篇文章中说到加锁的基本原则中第一点是加锁的基本单位是next-key lock,而退化都是基于索引上的等值查询才会发生呀?盼老师指点迷津。
    您给回答是定位到c=20的时候,是等值查询,所以加的是(20,25)的间隙锁,25的行锁退化了,那么在上一期中的案例五:唯一索引范围锁 bug,那id<=15,不也是先定位到id=15,然后向右扫描,那应该也是等值查询,那么应该加的是(15,20)间隙锁,那为啥你说的加的是(15,20],为啥这个id=20的行锁也加上了呢,为啥同样是范围查询,一个行锁退化了,一个没有退化呢,求老师指点迷津

    作者回复: 1. 第一次就是找c=20,这个就是一次等值查找
    2. 案例5那个,等值查的是id=10,然后向右遍历。这两个,一个是有order by desc,索引的扫描方向不一样,“找第一个”的值也是不一样的

    2019-01-04
    2
  • 一大只😴
    老师,我找到我上次说RR隔离级别下,session 1:begin;select * from t where d=5 for update; session 2:update t set d=5 where id=0;可以执行的原因了,我配置文件中禁用了间隙锁,innodb_locks_unsafe_for_binlog=on,改成off默认值就正常了。

    作者回复: 😓居然开了这个,生产不建议开哦

    2019-01-03
    2
收起评论
67
返回
顶部