MySQL主从复制与读写分离
主从同步
- master主库，有数据更新，将此次更新的事件类型写入到主库的binlog文件中
- 主库会创建
log dump 线程通知slave有数据更新 - slave从库，向master节点的
log dump线程请求一份指定binlog文件位置的副本，并将请求回来的binlog存到本地的Relay log中继日志中 - slave 再开启一个
SQL 线程读取Relay log事件，并在本地执行redo操作。将发生在主库的事件在本地重新执行一遍，从而保证主从数据同步
主从延迟
指一个写入SQL操作在主库执行完后，将数据完整同步到从库会有一个时间差，称之为主从延迟。
- 主库生成一条写入SQL的binlog，里面会有一个时间字段，记录写入的时间戳 t1
- binlog 同步到从库后，一旦开始执行，取当前时间 t2
t2-t1，就是延迟时间
注意：不同服务器要保持时钟一致。
主从延迟排查方法
通过 show slave status 命令输出的Seconds_Behind_Master参数的值来判断
- 为零：表示主从复制良好
- 正值：表示主从已经出现延时，数字越大，表示从库延迟越严重
主从延迟的解决方案
- 看业务的接受程度。如果不能接受延迟，那么建议强制走主库查询
- 可以考虑引入缓存，更新主库后同步写入缓存，保证缓存的及时性
- 提升从库的机器配置，提高从库binlog的同步效率
- 缩短主、从库的网络距离，减少binlog的网络传输时间
- 一主多从，每个从库都启一个线程从主库同步 binlog，导致主库压力过大，可以采用
canal增量订阅&消费组件，缓解主库压力。 - 因为数据库必须要等到事务完成之后才会写入binlog，所以减少大事务的执行，尽量控制数量，分批执行。
- 5.6版本之前，从库是单线程复制，当遇到执行慢的sql时，就会阻塞后面的同步。5.7 版本后支持多线程复制，可以在从服务上设置
slave_parallel_workers为一个大于0的数，然后把slave_parallel_type参数设置为LOGICAL_CLOCK - 为从库增加浮动IP，并通过脚本检测从库的延迟，延迟大于指定阈值时，将浮动IP切换至Master库，追平后再切换回从库。
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来源 Michael's Blog！
相关推荐
2018-03-20
MySQL的三种日志
redo log日志也叫做WAL技术（Write- Ahead Logging），他是一种先写日志，并更新内存，最后再更新磁盘的技术，为了就是减少sql执行期间的数据库io操作，并且更新磁盘往往是在Mysql比较闲的时候，这样就大大减轻了Mysql的压力。 redo log是固定大小，是物理日志，属于InnoDB引擎的，并且写redo log是环状写日志的形式： 如上图所示：若是四组的redo log文件，一组为1G的大小，那么四组就是4G的大小，其中write pos是记录当前的位置，有数据写入当前位置，那么write pos就会边写入边往后移。 check point记录擦除的位置，因为redo log是固定大小，所以当redo log满的时候，也就是write pos追上check point的时候，需要清除redo log的部分数据，清除的数据会被持久化到磁盘中，然后将check point向前移动。 redo log日志实现了即使在数据库出现异常宕机的时候，重启后之前的记录也不会丢失，这就是crash-safe能力。 binlog称为归档日志，是逻辑上的日志，它属于M...
2019-03-20
MySQL - MVCC
MVCC叫做多版本控制，实现MVCC时用到了一致性视图，用于支持读提交和可重复读的实现。 对于一行数据若是想实现可重复读取或者能够读取数据的另一个事务未提交前的原始值，那么必须对原始数据进行保存或者对更新操作进行保存，这样才能够查询到原始值。 在Mysql的MVCC中规定每一行数据都有多个不同的版本，一个事务更新操作完后就生成一个新的版本，并不是对全部数据的全量备份，因为全量备份的代价太大了： 如图中所示，假如三个事务更新了同一行数据，那么就会有对应的v1、v2、v3三个数据版本，每一个事务在开始的时候都获得一个唯一的事务id（transaction id），并且是顺序递增的，并且这个事务id最后会赋值给row trx_id，这样就形成了一个唯一的一行数据版本。 实际上版本1、版本2并非实际物理存在的，而图中的U1和U2实际就是undo log日志（回滚日志），这v1和v2版本是根据当前v3和undo log计算出来的。 InnoDB引擎就是利用每行数据有多个版本的特性，实现了秒级创建“快照”，并不需要花费大量的是时间。
2019-04-20
MySQL 数据类型选型
一句话 选最小的、能装下你数据的类型。 类型小 = 索引小 = 内存命中率高 = 快。 一、整数类型 类型 字节 范围（无符号） 用途 TINYINT 1 0 ~ 255 状态枚举、布尔 SMALLINT 2 0 ~ 65535 较小计数 MEDIUMINT 3 0 ~ 1677w 较少用 INT 4 0 ~ 42亿 大多数主键、ID BIGINT 8 0 ~ 1844亿亿 雪花 ID、大表主键 1234-- ❌ 用户性别用 INTgender INT-- ✅ 用 TINYINTgender TINYINT UNSIGNED COMMENT '0未知 1男 2女' INT(11) 里的 11 不是长度！只是显示宽度（已废弃），实际仍是 4 字节。 二、字符串类型 类型 长度 适用 CHAR(n) 固定 n 字符 MD5、UUID、固定位手机号 VARCHAR(n) 可变 ≤ n 大多数字符串 TEXT (4 种) 长文本 文章正文，不要建普通索引 ENUM 枚举 不推荐，...
2019-03-20
SQL语句性能分析工具 - explain
通过explain，如以下例子： 1EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26'; id select_type table partitions type possible_keys key key_len ref filtered rows Extra 1 SIMPLE titles null const PRIMARY PRIMARY 59 const,const,const 10 1 id：在⼀个⼤的查询语句中每个SELECT关键字都对应⼀个唯⼀的id ，如 explain select * from s1 where id = (select id from s1 where name = 'egon1'); 第一个select的id是1，第二个select的id是2。 有时候会出现两个sel...
2020-03-20
MySQL三范式
数据库三范式 第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解； 第二范式：2NF是对记录的唯一性约束，要求记录有唯一标识，即实体的唯一性； 第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。 范式化 优点：可以尽量的减少数据冗余，使得更新快，体积小 缺点：对于查询需要多个表进行关联，减少写的效率增加读的效率，更难进行索引优化 反范式化 优点：可以减少表的关联，可以更好的进行索引优化 缺点：数据冗余以及数据异常，数据的修改需要更多的成本 归纳 方式 第一范式 第二范式 第三范式 约束 原子性 唯一性 冗余性 优点 更新快 体积小 减少数据冗余 缺点 对于查询需要多个表进行关联 减少写的效率增加读的效率 更难进行索引优化 (反)优点 可以减少表的关联 可以更好的进行索引优化 - (反)缺点 数据冗余以及数据异常 数据的修改需要更多的成本 -
2022-05-20
MySQL优化经验总结
线上SQL的调优经验 slow_query_log 日志中收集到的慢 SQL ，结合 explain 分析是否命中索引。 减少索引扫描行数，有针对性的优化慢 SQL。 建立联合索引，由于联合索引的每个叶子节点包含检索字段的信息，按最左前缀原则匹配后，再按其它条件过滤，减少回表的数据量。 还可以使用虚拟列和联合索引来提升复杂查询的执行效率。 监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql 打开数据库慢查询日志功能 简化业务逻辑 代码重构、优化 异步处理 sql优化 索引优化 SQL优化 分页优化。比如电梯直达，limit 100000,10 先查找起始的主键id，再通过id>#{value}往后取10条 尽量使用覆盖索引，索引的叶节点中已经包含要查询的字段，减少回表查询 SQL优化（索引优化、小表驱动大表、虚拟列、适当增加冗余字段减少连表查询、联合索引、排序优化、慢日志 Explain 分析执行计划）。 where子句 避免对字段进行null值判断、表达式操作等； where表之间的连接，必须写在其他where条件之前...
评论
