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

28 | 读写分离有哪些坑?

林晓斌 2019-01-16
在上一篇文章中,我和你介绍了一主多从的结构以及切换流程。今天我们就继续聊聊一主多从架构的应用场景:读写分离,以及怎么处理主备延迟导致的读写分离问题。
我们在上一篇文章中提到的一主多从的结构,其实就是读写分离的基本结构了。这里,我再把这张图贴过来,方便你理解。
图 1 读写分离基本结构
读写分离的主要目标就是分摊主库的压力。图 1 中的结构是客户端(client)主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。也就是说,由客户端来选择后端数据库进行查询。
还有一种架构是,在 MySQL 和客户端之间有一个中间代理层 proxy,客户端只连接 proxy, 由 proxy 根据请求类型和上下文决定请求的分发路由。
图 2 带 proxy 的读写分离架构
接下来,我们就看一下客户端直连和带 proxy 的读写分离架构,各有哪些特点。
客户端直连方案,因为少了一层 proxy 转发,所以查询性能稍微好一点儿,并且整体架构简单,排查问题更方便。但是这种方案,由于要了解后端部署细节,所以在出现主备切换、库迁移等操作的时候,客户端都会感知到,并且需要调整数据库连接信息。
你可能会觉得这样客户端也太麻烦了,信息大量冗余,架构很丑。其实也未必,一般采用这样的架构,一定会伴随一个负责管理后端的组件,比如 Zookeeper,尽量让业务端只专注于业务逻辑开发。
带 proxy 的架构,对客户端比较友好。客户端不需要关注后端细节,连接维护、后端信息维护等工作,都是由 proxy 完成的。但这样的话,对后端维护团队的要求会更高。而且,proxy 也需要有高可用架构。因此,带 proxy 架构的整体就相对比较复杂。
取消
完成
0/1000字
划线
笔记
复制
© 版权归极客邦科技所有,未经许可不得传播售卖。 页面已增加防盗追踪,如有侵权极客邦将依法追究其法律责任。
该试读文章来自付费专栏《MySQL实战45讲》,如需阅读全部文章,
请订阅文章所属专栏。
立即订阅
登录 后留言

精选留言(42)

  • 有铭
    这专栏真的是干货满满,每看一篇我都有“我发现我真的不会使用MySQL”和“我原来把MySQL用错了”的挫败感

    作者回复: 这样我觉得你和我的时间都值了😆

    把你更新了认识的点发到评论区,这样会印象更深哈🤝

    2019-01-16
    34
  • 曾剑
    老师写的每一篇文章都能让我获益良多。每一篇都值得看好几遍。
    今天的问题,大表做DDL的时候可能会出现主从延迟,导致等 GTID 的方案可能会导致这部分流量全打到主库,或者全部超时。
    如果这部分流量太大的话,我会选择上一篇文章介绍的两种方法:
    1.在各个从库先SET sql_log_bin = OFF,然后做DDL,所有从库及备主全做完之后,做主从切换,最后在原来的主库用同样的方式做DDL。
    2.从库上执行DDL;将从库上执行DDL产生的GTID在主库上利用生成一个空事务GTID的方式将这个GTID在主库上生成出来。
    各个从库做完之后再主从切换,然后再在原来的主库上同样做一次。
    需要注意的是如果有MM架构的情况下,承担写职责的主库上的slave需要先停掉。

    作者回复: 👍 表示这两篇文章你都get到了

    2019-01-16
    23
  • 某、人
    老师我先请教两个问题(估计大多数同学都有这个疑惑)😄:
    1.现在的中间件可以说是乱花渐欲迷人眼,请问老师哪一款中间件适合大多数不分库分表,只是做读写分离业务的proxy,能推荐一款嘛?毕竟大多数公司都没有专门做中间件开发的团队
    2.如果是业务上进行了分库分表,老师能推荐一款分库分表的proxy嘛?我目前了解到的针对分库分表的proxy都或多或少有些问题。不过分布式数据库是一个趋势也是一个难点。

    作者回复: 额,这个最难回答了

    说实话因为我原来团队是团队自己做的proxy(没有开源),所以我对其他proxy用得并不多,实在不敢随便指一个。

    如果我说个比较熟悉的话,可能MariaDB MaxScale还不错

    2019-01-16
    2
    14
  • 易翔
    为老师一句你的时间和我的时间都值了。点赞
    2019-01-16
    2
    9
  • 猪哥哥
    老师, 你真棒, 我公司的生产环境解决过期读使用的就是强制走主库方案, 看了这篇文章, 困惑了很久的问题迎刃而解!很感谢!
    2019-01-17
    7
  • 二马
    最近做性能测试时发现当并发用户达到一定量(比如500),部分用户连接不上,能否介绍下MySQL连接相关问题,谢谢!

    作者回复: 修改max_connections参数

    2019-01-16
    4
  • Charles | 姜海龙
    这专栏真的是干货满满,每看一篇我都有“我发现我真的不会使用MySQL”和“我原来把MySQL用错了”的挫败感
    2019-08-12
    1
    3
  • godtrue
    1:单机的性能总是有限的,所以,就出现了读写分离
    2:读写分离带来了更高的性能,也引入了数据不一致的问题
    3:为了数据一致性,又产生了各种解决方案
    人少力量小,人多了事就多,如果管理能力好,还是人多好办事。
    原理是这样,没怎么实操过,感谢老师的分享,让自己的认知边界有移动了一点点。

    作者回复: 👍

    2019-08-04
    3
  • Mr.Strive.Z.H.L
    老师您好:
    关于主库大表的DDL操作,我看了问题答案,有两种方案。第一种是读写请求转到主库,在主库上做DDL。第二种是从库上做DDL,完成后进行主从切换。

    关于第二种,有一个疑惑:
    从库上做DDL,读写请求走主库,等到从库完成后,从库必须要同步DDL期间,主库完成的事务后才能进行主从切换。而如果DDL操作是删除一列,那么在同步过程中会出错呀?(比如抛出这一列不存在的错误)。

    作者回复: 你说得对,这种方案下能支持的DDL只有以下几种:
      创建/删除索引、新增最后一列、删除最后一列

    其中DBA会认为“合理”的DDL需求就是: “创建/删除索引、新增最后一列”

    新春快乐~

    2019-01-21
    1
    3
  • 晓杰
    看了判断主备无延迟这种方案,有个疑惑:
    读写分离的读应该是读从库的吧,为什么是去判断主备无延迟,而不是判断主从的无延迟

    作者回复: 在说主备延迟/主从延迟的时候,从库和备库我当成同一个概念了😆

    2019-06-15
    2
  • IceGeek17
    老师,能不能分析下,如果去实现一个做读写分离的proxy,有哪些重要的点要考虑,比如:连接管理、流量分配管理、proxy自己的高可用,等等。
    因为老师原来的团队自己开发过proxy,肯定有相关的经验,也趟过很多坑,能不能从如何实现一个proxy需要考虑哪些关键点,在架构上做一个分析和梳理

    作者回复: 额,这个问题有点大… 你提一个具体问题我们来讨论吧

    2019-01-29
    2
  • black_mirror
    林老师 您好
    1.mysql_session_track_get_fitst这个函数,从github下载mysql源码后怎么尝试简单编译,如图8?
    2. mysql_session_track_get_fitst这个函数貌似不支持python语言,我想模拟文中等gtid方法,不会java怎么办?
    2019-01-21
    2
  • Max
    我一般是先是在从库上设置 set_log_bin=off,然后执行ddl,语句。
    然后完成以后,主从做一下切换。然后在主库上在执行一下set_log_bin=off,执行ddl语句。
    然后在做一下主从切换。
    个人对pt-online-scheman-change不是很推荐使用,它的原理基本是创建触发器,然后创建和旧表一样结构的数据表,
    把旧表的数据复制过去。最后删除旧表。以前做个一个测试,如果旧表一直在被select,删除过程会一直会等待。
    所以个人不是很建议。万一不小心变成从删库到路步,那就得不偿失了。

    老师,有个问题想请教一下,一主多从可以多到什么地步,以前我们CTO解决的方案就是加机器,一主十三从。
    当时我是反对的,其实个人建议还是从SQL,业务上面去优化。而不是一味的加机器。如果加机器解决的话,还要DBA做什么呢?

    作者回复: 前面的分析很好哈


    然后一主13从有点多了,否则主库生成binlog太快的话,主库的网卡会被打爆。要这么多的话,得做级联。

    DBA解决不能靠加机器解决的事情^_^ 而且如果通过优化,可以把13变成3,那也是DBA的价值

    2019-01-17
    2
  • null
    老师,您好!

    课后思考题,想到了 27 课的“GTID 和在线 DDL”:
    1. 在主库执行:stop slave;
    2. 在备库 DDL,并得到 GTID_OF_DDL;
    3. 在主库执行:
    set gtid_next=GTID_OF_DDL;
    begin; commit;
    set gtid_next=automatic;
    start slave;

    然后切换主备,再在另一个实例执行一遍。

    有些疑问:假如 DDL 删除一个字段,先在 备库A 处理 DDL,执行成功后:
    1. 客户端在 备库A 的查询,和跟在主库的查询,少了该字段,主备数据不一致了,需要剔除 备库A 这段时间不提供服务么?

    2. 备库A DDL 删除字段成功后,在主库 start slave 后,需要主备数据一致后,才能继续切换主备。而在等待 备库A 继续应用主库的 binlog 时,如果有 insert 操作,而备库无该字段,insert 失败了。这又该怎么处理呢?

    谢谢老师!!
    2019-07-10
    1
  • 狼猫
    全网最好的课程,没有之一,不接受反驳
    2019-06-24
    1
  • 肖邦的学徒
    有个疑问 主库执行更新得到的位点信息读请求怎么拿到呢

    作者回复: show master status ?

    2019-04-18
    1
  • 啊啊啊哦哦
    老师。最近公司在阿里云要用 一主多从。 我想问下阿里的。 select *from test for update 会定位到主库吗

    作者回复: 设计不出bug的话,应该要😆

    2019-04-10
    1
  • 涛哥哥
    老师,想问一下,主库用innodb引擎, 从库用 myisam可以吗?如果主库挂了,不准备用从库顶上来,建议这样用不同引擎吗?谢谢老师 😊😊

    作者回复: 不建议,容易主备不一致

    2019-03-17
    1
  • 信信
    老师您好,文中判断主备无延迟方案的第二种和第三种方法,都是对比了主从执行完的日志是否相同。因为不会出现图4下方说的:“从库认为已经没有同步延迟,但还是查不到 trx3 的。”因为如果从库未执行trx3的话,第二,第三种方法都是不通过的。

    作者回复: 不会哦
    如果trx3还没传到备库,备库是会认为已经同步完成了

    2019-01-18
    1
  • ThinkingQuest
    楼上有人提到8小时自动断开连接的问题。
    mysql中有wait_timeout和interactive_timeout两个参数。

    这俩参数挺容易混淆的,往上博客文章说的很多,但是不敢相信他们。
    官方的解释在这里:
    https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_interactive_timeout
    https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout

    只说用这两个参数中的哪个,取决于客户端调用mysql_real_connect()的时候传递的options中是否使用了CLIENT_INTERACTIVE选项。

    但是很多做java开发的同学,想必并不知道JDBC的connector用的是哪一个。
    我倾向于认为是interactive_timeout。 mysql client cli大概是wait_timeout吧。
    其实做一个实验就可以知道结果。 但是不阅读mysql代码,大概不能理解mysql为什么设计这么两个timeout,是出于什么考虑的。

    作者回复: JDBC的connector我也没研究过,不过我认为应该是非interactive模式。

    需要这两个的原因,还是因为有不同的使用模式,给MySQL客户端和一些其他的可视化工具客户端使用。

    2019-01-17
    1
收起评论
42
返回
顶部