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

15 | 答疑文章(一):日志和索引相关问题

解决关注关系并发问题的方法
关注关系的设计
redo log buffer的作用
数据最终落盘的来源
redo log的大小设置
只用binlog是否可行
只用redo log是否可行
binlog无法支持崩溃恢复的原因
为什么需要两阶段提交
binlog的完整性验证
binlog和redo log的关联
异常重启时的数据完整性保证
更新计数表的顺序选择
并发操作的顺序安排
MySQL选择的策略原因
构造实验验证更新操作的结果
更新操作的三种选择
业务设计问题
两阶段提交
问题回答时间
更新操作的结果
日志和索引相关问题

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

在今天这篇答疑文章更新前,MySQL 实战这个专栏已经更新了 14 篇。在这些文章中,大家在评论区留下了很多高质量的留言。现在,每篇文章的评论区都有热心的同学帮忙总结文章知识点,也有不少同学提出了很多高质量的问题,更有一些同学帮忙解答其他同学提出的问题。
在浏览这些留言并回复的过程中,我倍受鼓舞,也尽我所知地帮助你解决问题、和你讨论。可以说,你们的留言活跃了整个专栏的氛围、提升了整个专栏的质量,谢谢你们。
评论区的大多数留言我都直接回复了,对于需要展开说明的问题,我都拿出小本子记了下来。这些被记下来的问题,就是我们今天这篇答疑文章的素材了。
到目前为止,我已经收集了 47 个问题,很难通过今天这一篇文章全部展开。所以,我就先从中找了几个联系非常紧密的问题,串了起来,希望可以帮你解决关于日志和索引的一些疑惑。而其他问题,我们就留着后面慢慢展开吧。

日志相关问题

我在第 2 篇文章《日志系统:一条 SQL 更新语句是如何执行的?》中,和你讲到 binlog(归档日志)和 redo log(重做日志)配合崩溃恢复的时候,用的是反证法,说明了如果没有两阶段提交,会导致 MySQL 出现主备数据不一致等问题。
在这篇文章下面,很多同学在问,在两阶段提交的不同瞬间,MySQL 如果发生异常重启,是怎么保证数据完整性的?
确认放弃笔记?
放弃后所记笔记将不保留。
新功能上线,你的历史笔记已初始化为私密笔记,是否一键批量公开?
批量公开的笔记不会为你同步至部落
公开
同步至部落
取消
完成
0/2000
荧光笔
直线
曲线
笔记
复制
AI
  • 深入了解
  • 翻译
    • 英语
    • 中文简体
    • 中文繁体
    • 法语
    • 德语
    • 日语
    • 韩语
    • 俄语
    • 西班牙语
    • 阿拉伯语
  • 解释
  • 总结

本文是MySQL实战专栏的答疑文章,围绕日志和索引相关问题展开讨论。作者首先感谢读者在评论区留下的高质量留言,以及相互帮助解答问题的热心同学。文章围绕两个主要问题展开讨论:日志相关问题和索引相关问题。在日志相关问题中,作者解释了在MySQL异常重启时如何保证数据完整性,以及MySQL如何判断binlog的完整性。此外,还回答了关于redo log和binlog的关联、两阶段提交的必要性以及只使用binlog支持崩溃恢复的可行性等问题。通过对这些问题的深入解释,读者可以更好地理解MySQL中日志的作用和重要性。整体而言,本文通过解答读者提出的问题,深入探讨了MySQL中日志和索引的相关知识,为读者提供了宝贵的技术参考。文章还涉及了一个有趣且实用的业务设计问题,探讨了在并发场景下处理关注关系导致无法成功加为朋友关系的情况。作者提出了一种解决方案,通过修改逻辑和增加字段来保证数据一致性。这篇文章内容丰富,涵盖了日志和索引相关的技术知识,以及实际业务问题的解决方案,对读者具有很高的参考价值。

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

全部留言(273)

  • 最新
  • 精选
  • 力挽狂澜爆炸输出的臭臭宁
    置顶
    针对不能只用binlog完成数据恢复我的理解: 按照文中这个话题下的示例,因为MySQL写数据是写在内存里的,不保证落盘,所以commit1的数据也可能丢失;但是恢复只恢复binlog失败的也就是commit2的数据,所以数据会丢失。 这样理解对吗?

    作者回复: 是的,binlog一来时机控制不好(就是你说的这个),二来内容的能力不足(没有页面信息) 👍🏿

    2018-12-20
    9
    35
  • null
    置顶
    看到自己的问题上榜,这是对自己的最大鼓励。 学习专栏之前,自己只是一个 CRUD boy,平时同事间讨论 MySQL 的问题,自己完全搭不上话,因为对 MySQL 底层原理完全不懂。对 MySQL 的认知就仅限一点:索引能提高查询效率。但是为什么能提高?不知道!! 现在回想,以前犯过很多错误: 1. 主键使用 UUID,非自增主键。 2. 滥用索引,其实可以通过“最左前缀原则”来精减索引。 3. 不管 SQL 语句是否合理,只要能返回结果集就是好 SQL。 4. 建表时字段类型拿捏不准。 现在都会反复学习专栏的每一篇文章,每次学习都有不一样的收获。 第一次可能是:喔,原来有这么个知识点,但对它的实现原理一知半解。 第二次却是:对它的实现原理有了更深的认识,加强对知识的理解,基本会形成一个比较清晰的逻辑。 第三次是,MySQL 的这种实现原理,是为了解决什么问题等等。 现在感觉有点“走火入魔”了,以前执行查询语句,关注的多久能返回结果集。 现在关注的却是:连接器、分析器、优化器、执行器和 InnoDB 引擎。 连接成功后,获取我的权限,查询缓存,命中缓存直接返回,否则进行后续的操作。(记得老师留言区回复过:连接器取权限,执行器用权限。而编写留言到这产生了一个疑问:查询缓存前,应该会校验权限,所以连接器也会用权限?) 分析器阶段进行词法分析,解析关键字,字段名,表名等。语法分析判断语法是否正确。(记得第一篇《基础架构》留言提到语义分析,今晚要找资料学习下)。 优化器阶段生成执行计划,选择索引(这时会怀疑 MySQL 选择的索引是否最优),能否使用索引下推和覆盖索引减少回表查询,提高性能。 执行器阶段调用引擎接口查询数据,Server 层要啥,引擎给啥,InnoDB 只给必要的值。 查询结束后,返回结果集,并将结果集放入查询缓存。 更新语句的关注点是隔离性,视图,MVCC,回滚日志,redo log,binlog,两阶段提交等。 写业务代码时,会考虑事务内的 SQL 语句,能否调整 SQL 语句的顺序,减少更新表时行锁对性能的影响。 在建表的时,会反复推敲这个索引是否合理。使用普通索引还是唯一索引更为合适。能否通过“最左前缀原则”来减少创建索引的个数。如果索引字段的类型是字符串并长度太长,如何优化使用前缀索引,减少空间占用,提高查询性能。 学习专栏后,基本上涉及到 MySQL 的内容,这些知识点都会浮现在脑海中。昨天还差点应用这些知识,帮同事优化他的 SQL 语句。昨天跟往常一样,当写代码写累了,就跑到同事那溜达溜达。 他正在线上的备库测试查询百万数据要多久,另一位同事建议他使用 force index 强制索引,这次执行 5 秒,再执行零点几秒。 他惊乎,为啥这次这么快。我说,这次查了缓存。我还想帮他看看 SQL 语句,是否 MySQL 选择错了索引,导致使用 force index 显式指定索引。说不定使用 order by field 就解决了呢,哈哈哈哈。后面有事,没有继续跟进他这问题了。 非常感恩,跟着老师学习,让我体会到了学习是一件自然而又充满魅力的事情,也让我从一个基础不牢固的小白,一步步地充实了自己的知识库,另外老师非常尽责,经常半夜回复答疑,希望老师保重身体。谢谢!!

    作者回复: “我说,这次查了缓存” 哈哈,这个场景好棒,这个画面感,有一种扫地僧的感觉👍🏿 一起加油

    2018-12-18
    12
    182
  • Gavin
    置顶
    课后问题: 在命令行先执行以下命令(注意不要提交事务): BEGIN; UPDATE t SET a=2 WHERE id=1; 新建一个命令行终端,执行以下命令: UPDATE t SET a=2 WHERE id=1; 从新建的命令行终端的执行结果看,这条更新语句被阻塞了,如果时间足够的话(InnoDB行锁默认等待时间是50秒),还会报锁等待超时的错误。 综上,MySQL应该是采用第3种方式处理题述场景。 对于MySQL为什么采用这种方式,我们可以利用《08 | 事务到底是隔离的还是不隔离的?》图5的更新逻辑图来解释:假设事务C更新后a的值就是2,而事务B执行再执行UPDATE t SET a=2 WHERE id=1;时不按第3种方式处理,即不加锁不更新,那么在事务B中接下来查询a的值将还是1,因为对事务B来说,trx_id为102版本的数据是不可见的,这就违反了“当前读的规则”。 以上是我的理解与分析,不是很确定准确与否。

    作者回复: 漂亮

    2018-12-17
    16
    238
  • 某、人
    置顶
    孔乙己来到酒馆大喊一声老板来二两酒赊着,酒馆生意太好,老板把孔乙己的欠账记录记到小黑板上并记录了孔乙己点的菜单。孔乙己跟别人吹了会牛,忘了叫的几两酒了。又给老板说,老板把酒改成二两。老板也不确定孔乙己叫没叫酒,就去查菜单,发现孔乙己确实点了酒,但是本来就二两,也就难得麻烦了,又要修改小黑板,又要改菜单。直接就给孔乙己说已经改好了。😄

    作者回复: 老板看完板,正要告知孔乙己今日总账是赊账二两酒, 小二连忙过来拦住,“老板,刚刚孔乙己刚又赊账了一碟茴香豆。” 老板大惊,“差点亏了我一碟豆子!我怎不知?” 小二道,“老板你方才看板的之时没拿记账笔,我看记账笔没人使用,按店规自然可用。老板你自己没看” 老板惊呼,“亏的你小心”。 暗地想店规确有不妥。 于是把店规“变账须用记账笔。” 改为 “改帐均须动笔。纵为不变之帐,仍需覆写之” 😄

    2018-12-17
    10
    516
  • 萤火虫
    置顶
    林老师的每次更新我都会跟着看 跟着学 已经坚持15节课了 受益良多 只是心里有时会反问自己 底层原理有那么重要吗? 会用不就行了吗? 自己不知道该怎么推翻这些想法 加上自己有个不好的习惯 就是容易放弃 希望自己能够坚持到最后。

    作者回复: 加油。 说下我自己的理解。 我在带新人的时候,要求大家在写SQL语句的时候,心里是有数的,知道每个语句执行的结果,以及这些代码会消耗什么资源、如果慢了会慢在哪里、每个语句执行会占用哪些锁等等。 有的新人会问“为什么需要这么麻烦,我执行一下,看看结果对不对,对了就行,不对就改,是不是也可以?” 我说不可以。因为如果这样,我们就会受到很多局限,即使我们定位自己是业务开发人员。 这里我说一个限制: 这会限制基于数据库的业务架构能力。一个语句可以试,一个五个语句的事务分析就要试很多次,一个复杂业务系统的数据库设计,是试不出来的。 原理可以帮我们剪枝,排除掉那些理论上明显错误的方案,这样才有精力真的去试那些有限的、可能正确的方案。 我们不需要100%精通MySQL(我自己离这个目标也相去甚远),但是只要多知道一些原理,就能多剪一些枝,架构设计就能少一些错误选项的干扰,设计出来的项目架构正确的可能性更高。 我自己特别喜欢这个剪枝的过程和感觉,他表示我用以前学习的时间,来节省了现在工作的时间。 当然,“原理”是一个很大的概念,有的原理更接近实战,有的远一些。这个专栏我挑的是跟平时使用相关的原理,以便大家可以有机会边学边用。 一起加油吧🤝

    2018-12-17
    17
    541
  • 于海
    在极客时间也学了不少课程,林老师是这其中最认真负责的,好的课程是用“心”写出来的

    作者回复: 谢谢🙏 希望大家都有收获

    2018-12-17
    67
  • 陈新仁
    【操作符“|”是逻辑或,连同最后一句insert语句里...】 老师,“|” 这应该叫位运算符的按位或操作符,逻辑或是“||”吧? 这里的幂等性原理就是:A < B: relation_ship = 2 | 1; A > B:relation_ship = 1 | 2;重复插入 3 | 1 或者 3 | 2 。位运算: 2 | 1 == 1 | 2 == 3 | 1 == 3 | 2 == 3。感觉这里想法很巧妙。

    作者回复: 你说得对,是按位或,看得很细致👍🏿 我发个堪误

    2018-12-17
    9
    56
  • WL
    关于刷脏页有两个问题请教老师: 1. 当redo log空间不足时, 按照redo log的顺序把脏页更新到磁盘, 那么假如一个脏页在第1条redo log中已经被持久化到磁盘, 后面第1000条redo log又有这个关于这个脏页的信息, 那么innoDB是直接丢弃掉这条redo log的记录吗? 还有这个时候, 是要把redo log上的全部内容更新到磁盘吗, 还是更新一部分? 2. 当内存不足开始刷脏页的时候, 以内存为刷脏页发起点, 这时是把最久不使用的数据页从内存中淘汰掉, 那么这个时候, 对应的额redo log是不是也会清除? 而对于该脏页的操作可能在redo log中并不是连续的记录, redo log有只能顺序读写, 那么redo log是怎么清除掉关于这个脏页的所有记录的?

    作者回复: 1. 刷脏页是只把内存上最新版本的数据页写到磁盘上。 第一个碰到的redolog会把这个脏页刷下去,注意redolog并没有修改内存数据页的数据(这个不是崩溃恢复过程哦) 后面再碰到这个页面的redolog,这个页面是干净页了,不用刷直接跳过 2. 第二个问题的两个问号是一个答案:不用清除。 下次redo log来刷的时候看到是干净页就直接过了。 好问题。

    2018-12-22
    16
    38
  • 刘志兵
    老师您好,读了很久其他问题都明白了,还是没太明白只用binlog为啥不能恢复数据,根本原因还是对binlog和redolog都记录的内容没掌握清楚,我的理解跟评论区里“得不到de颜色”说的一样,就是binlog也同样记录了一行数据,但是不知道该从哪里回放,所以没法恢复,但是redo log可以从check_point开始扫描所以能恢复; 看到你回复的是binlog没有能力做出来一页的数据,反正缺失的数据都记在binlog里了,如果能有办法知道binlog从哪里回放,回放一遍就可以了,为啥一定要做一页数据出来,麻烦老师解答一下,谢谢

    作者回复: 是这样的 一个事务的binlog如果回放,就是重做这个事务,一个事务更新的可能不止一个page。 比如一个事务更新了page ABC 然后崩溃回复了,B坏了,AC没问题,而且AC还落盘了。 这样如果重做事务,B好了,AC又坏了

    2019-04-01
    13
    32
  • 信信
    如果图1的“写入redo log”是写内存,当时刻B发生crash,重启后这部分redo log都丢失了,那么何谈判断redo log是否有完整的prepare还是commit标志呢?

    作者回复: 不是哦, 在事务执行期间是在redo log buffer. 在图中写binlog之前,就已经都写了盘并且fsync了

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