1. 基础结构
-
MySQL Server 的体系结构
-
MySQL 的存储引擎 MyISAM 和 InnoDB 区别 https://www.cnblogs.com/y-rong/p/8110596.html
-
进程间通信的方式
-
InnoDB 存储引擎的架构
-
缓冲池的结构,以及存储什么
2. InnoDB 的三大特性
insert buffer
- https://www.cnblogs.com/chenpingzhao/p/4883884.html
doublewrite
- https://www.cnblogs.com/xuliuzai/p/10290196.html
Insert Buffer 给 InnoDB 存储引擎带来了性能上的提升,而 doublewrite 带给 innodb 存储引擎数据页的可靠性.
自适应哈希索引
3. 日志文件
MySQL 层面的日志
-
error log: 记录了所有的错误信息
-
slow query log:可以在MySQL 启动时设置一个阈值，
将运行时间超过阈值(>而不是≥)的所有SQL语句都记录到 慢查询日志中 -
log : 记录了所有对MySQL 的请求信息
-
bin log: 记录了MySQL数据库执行更改的所有操作,(delete,insert,update)
不包括 select 和 show(因为这类操作并没有对数据进行修改)当使用事务(innodb)存储引擎时,所有未提交的二进制日志会被记录到一个缓存中,
等待该事务提交时,直接将该缓冲中的二进制日志写入bin log.何时同步到磁盘 以及带来的问题?
InnoDB 存储引擎的日志
-
redo log 重做日志
记录了innodb 的事务日志
先写入 redo log buffer ,然后在落盘
- 从redo log buffer 写入磁盘是按照 512 字节即一个扇区大小进行写入的.因为扇区是写入的最小单位,因此可以保证写入一定成功,
因此在redo log 的写入过程中不需要 double write
触发落盘的条件:
bin log 和 redo log 的比较: https://blog.csdn.net/u013452337/article/details/101062195
- 从redo log buffer 写入磁盘是按照 512 字节即一个扇区大小进行写入的.因为扇区是写入的最小单位,因此可以保证写入一定成功,
-
undo log 回滚日志
ibdata 和 表名.ibd 存放什么? https://serverfault.com/questions/487159/what-is-the-ibdata1-file-in-my-var-lib-mysql-directory
-
ibdata
表的数据
表的索引页
MVCC 控制信息 : undo log, rollback 信息
insert buffer
double write buffer
-
开启 innodb_file_per_table: 会将数据和索引页存放在 .ibd 文件中
4. 表
InnoDB 中的表称为 索引组织表
表都是根据主键顺序组织存放的, 这样的表称为 索引组织表
还有索引聚簇表,堆表结构 : https://developer.aliyun.com/article/429680
表的逻辑存储结构
表 => 段(segment) => 区(extent) => 页(page) => 行
一页默认 16 kb
InnoDB 行记录格式
行溢出数据
char
varchar:实际存放字节数 65535 字节
16 kb = 16 * 1024 字节 = 16384 字节
问题: 一个数据页(16kb) 如何存放 65532 字节的数据, varchar 超过多长会保存在 blog 中
数据页结构之 Page Directory(页目录)
约束
-
数据完整性
主键, 外键,唯一键, 非空
-
索引 vs 约束
索引:是用来快速查找数据的,代表着数据的物理存储方式
约束:逻辑概念,用来保证数据的完整性
-
外键约束
-
视图: 一个命名的虚表即没有实际的物理存储.
-
分区表 (涉及到分库分表???) 数据库分库分表思路
分区概念: 将一个表或者索引分解为更小的更可管理的部分.
分区的分类:
- 水平分区: 将同一表的不同行分配到不同的物理文件中
- 垂直分区: 将同一表中不同列的记录分配到不同的物理文件中
MySQL : 支持水平分区,不支持全局分区. 支持的分区类型如下:
- range
- list
- hash
- key
5.索引
索引的分类
-
B+树索引
- https://zhuanlan.zhihu.com/p/113917726 为什么选择 B+树作为存放索引的数据结构
-
哈希索引 , 自适应哈希索引 :
- https://blog.csdn.net/shenjian58/article/details/102493421 这里有个 自适应哈希索引的例子
- https://blog.csdn.net/olizxq/article/details/82313489
hash 索引的限制:
-
哈希索引只包含哈希值和行指针
-
哈希索引只支持等值比较查询
-
…
自适应哈希索引:
- 普通的索引查询, 先从普通索引上根据条件查询出主键,然后在由主键去 聚集索引上回表定位到所在页,取到对应的记录
- 自适应 hash 索引:
-
全文索引
全文索引的表中有两个列, word 字段(设有索引) 和 ilist (存放了word 的位置信息)
待补充
为何选择 B+ 树?
-
二叉查找树: 会退化成链表
-
红黑树 : 删除操作优于 平衡二叉树, 树高,出度为 2
-
平衡二叉树: 插入/删除 维持平衡而旋转,比较耗时 ==> 旋转操作效率太低
- 结构对比
AVL 高度平衡,RBT 基本平衡.平衡性 AVL>RBT - 查找对比
AVL 查找时间复杂度最好最坏都是 O(logn)
RBT 查找最好时间复杂度是 O(logn),最坏比AVL 略差. - 插入删除对比
1)AVL的插入和删除很容易造成树的不平衡,而RBT的平衡度要求较低.
因此在大量数据插入情况下,RBT需要旋转变色操作来达到平衡的频率比AVL 低
2)RBT 比 AVL 多了一个变色操作,时间复杂度O(logn),但是变色操作简单,实际执行非常快
3)插入一个节点引起树的不平衡,AVL 和 RBT 都最多需要2次旋转操作.
删除一个节点引起不平衡,AVL最多需要O(logn)次旋转,而 RBT 最多需要3次
4)AVL 和 RBT 的插入删除代价主要消耗在查找待操作的节点.时间复杂度和O(logn)成正比
- 结构对比
-
B 树:为磁盘而生的数据结构. 能够利用局部性原理
磁盘按照块 block 分的,每个块 512 字节,磁盘一次 IO 读取 若干个 block, 称为一页.
linux : root@yuan:~# getconf PAGESIZE
4096 => 4k 一页windows: $ getconf PAGESIZE
65536 => 64k 一页? -
B+ 树:
区别:
- 最大的区别: B 树 非叶子结点也存储数据, 而 B+ 树 只在叶子结点存储数据
- 查找效率 B 树的最好 O(1), B+树一直是 O(logdN) => d 是出度,出度为2,就是常见的log2N
为什么选择 B+ 树?
- B 树在范围查找的时候效率不如 B+ 树
- 更少得 IO 次数 (同样一页,不存数据得B+树能存更多的结点)
- 局部性原理 对于 B+ 树更加友好, 因为 B+ 树 中 一页存放的结点记录数大于 B 数.
聚集索引和辅助索引
-
聚集索引: 按照每张表的主键构造的 B+ 树,叶子结点存放整个行记录的数据,也称叶子结点为数据页.
非叶子结点:存放的是 (正常情况) 主键的值 以及指向数据页的偏移量
叶子结点: 存放的是完整的行记录.
叶子结点通过双向链表维护
叶子结点即每个数据页中的记录也是通过双向链表维护的.
-
辅助索引: 叶子结点不包含整个行记录, 只包含键值
-
查看索引: show index from 表名
字段含义:
Table : 索引所在表名
Non_unique: 非唯一索引->1,唯一索引->0
Key_name: 索引名称
Seq_in_index:索引中该列的位置.如果是联合索引,第一列是1,第二列是2
Column_name:索引列名称
Collation:列以什么方式存储在索引中
A:B+树索引
NULL:Hash索引
索引选择相关
-
联合索引
- 全部用到索引
- 最左前缀法则
- 导致索引失效的操作: 运算符, 函数, 类型转换
- 索引不要放在范围查询右边
- 减少 select * 的使用,使用覆盖索引最优
- like 模糊搜索, %a% => 导致索引失效
- order by
- group by
-
覆盖索引 using index 表示使用覆盖索引
-
优化器不使用索引的情况
没使用索引就是通过扫描聚集索引的方式,即直接进行全表扫描得到数据
-
选择聚集索引,不使用辅助索引,即辅助索引失效. 当访问数据占据整个表的较大一部分(20%左右)时,优化器会选择聚集索引,而不会走辅助索引
因为通过辅助索引进行读取是离散操作,速度远远低于顺序读.
-
强制使用索引 force index (索引名)
-
-
索引提示(index hint): 显示的告诉优化器使用哪个索引,不需要优化器判断
使用到索引提示的场景: use index(索引名)
- MySQL 优化器选错索引
- 表的索引太多,优化器计算选择哪个索引的时间开销大于 SQL 本身
-
MRR(multi-range read) 优化: 根据辅助索引获取的结果集,然后根据主键进行排序，将乱序化为有序
参考: http://blog.itpub.net/22664653/viewspace-1673682
适用场景: 减少磁盘的随机访问,并将随机访问变成较为顺序的访问
- range, ref, eq_ref 类型的查询
- 针对的是辅助索引
MRR 的步骤:
- 将根据辅助索引查询到键值放在缓存中,此时缓存中的数据是根据辅助索引键值排序的
- 然后将缓存中的值根据 RowID 进行排序
- 根据 RowID 的排序顺序来访问实际文件
-
索引下推(index condition pushdown)
什么是索引下推: (下面的图应该是去 聚合索引上找,不是 table records, 就是 InnoDB )
原理:
- 不支持 ICP,首先读取索引信息，然后根据索引将整行数据读取出来。然后通过where (server层)条件判断当前数据是否符合条件，符合返回数据。
- 支持 ICP,检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行。用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据。 多了一个判断过程
- 适用于 range,ref,eq_ref,ref_or_null , 二级索引
实例:
select id, name, sex from index_opt_test where name='cc' and title like '%7' and sex='male';其中 索引 idx_cb(name,title,age)-
未使用 ICP
Server层把name推到引擎层
引擎层根据name去idx_cb的索引树中匹配主键
回表去捞数据返回给Server层
Server层再根据title、sex筛选出最终的数据
最后返回给客户端 -
使用了 ICP
Server把name和title都推到引擎层
引擎层根据name去idx_cb中查询出主键关键字和title、age
再由title筛选出匹配的主键关键字
回表去捞数据返回给Server层
Server层再根据sex筛选出最终的数据
再返回给客户端
-
explain
extra字段:
using where:使用了 where 过滤
using index:使用了覆盖索引
using index condition :索引下推
using MRR:使用了 MRR 优化
Using filesort:进行外部排序
using temporary:使用了临时表
using join buffer:使用了连接缓存
distinct:优化distinct,找到第一行匹配的记录停止等值查找
6. 锁
MySQL 中锁的分类
-
锁存在的意义
- 一方面要保证数据库的并发访问
- 另一方面要确保每个用户能以一致的方式读取和修改数据，为此就有了锁的机制
-
MySQL中的锁类型
-
latch
MYSQL中的LATCH(闩锁)详解——易产生的问题以及原因分析
内存锁, 是一个小型的在内存中保护 list 的内存锁结构
-
lock : 加锁的对象是 事务
用来锁定数据库中的对象,如表/页/行.
-
-
InnoDB 存储引擎中的锁 (lock 类型)
-
- 共享/排它锁(Shared and Exclusive Locks)
- 意向锁(Intention Locks)
- 记录锁(Record Locks)
- 间隙锁(Gap Locks)
- 临键锁(Next-key Locks)
- 插入意向锁(Insert Intention Locks)
- 自增锁(Auto-inc Locks)
-
InnoDB 技术内幕书中对锁进行分类
-
锁的类型 :行锁, 表锁
行锁和表锁的加锁过程"
-
行锁:
-
表锁:
-
-
一致性非锁定读
mvcc 通过一致性非锁定读实现. 不同的隔离级别下,对于快照数据的定义不同.
-
自增长与锁
-
外键和锁
-
每种锁具体的含义
-
行锁的算法(规则) 锁的都是索引 看xmind
-
这些锁本来和隔离级别没什么关系,看事务里得一篇文章. 但是可以用过加锁实现当前隔离级别解决不了的问题.
-
record lock 锁定索引记录 基于索引来完成行锁
单个行记录上的锁, 总是会锁定索引记录(注意:索引)
select * from t where id=1 for update; 会给 id =1 这条记录加锁,以防止其他事务插入更新删除这行记录
**where条件的字段为非索引字段，且执行 update 操作，此时 innodb 存储引擎加的是表锁，而不是行锁。**其他事务不能执行 insert ，update 操作，只能 select。
快照读是不会加锁的。
-
gap lock 锁定间隔
间隙锁,锁定一个范围,但是不包含记录本身
下面的例子都是 id 为 索引，不是主键索引，是非唯一索引。
select * from t where id between 8 and 15 for update; 会封锁区间，以阻止其他事务id=10的记录插入。
当 id 为索引字段时，会锁住 [8,15] 这个区间的记录，都是闭区间。
SELECT * FROM test02 where id<9 FOR UPDATE; 锁住的是 （-∞，9] 包括 9.
SELECT * FROM test02 where id = 9 FOR UPDATE; （记录数：1,2,9,11)锁住的是 [2,11) 左闭右开区间。
-
next-key lock 锁定索引记录的记录和间隔
gap lock + record lock 锁定一个范围,且锁定记录本身。
SELECT * FROM test02 where id = 9 LOCK in SHARE MODE; 加 next-key lock 的方式。 (id = 1,2,9,11) 此时锁住的是 [2,9] 和 [9,11) 区间,如果9 后面是连续的即 id = 10 这条记录,则 锁住 [2,9].
当 查询的索引是唯一索引时, innodb 会将 next-key lock 降级为 record lock
-
共享锁/排他锁
(1)事务拿到某一行记录的共享S锁，才可以读取这一行；
(2)事务拿到某一行记录的排它X锁，才可以修改或者删除这一行；
-
多个事务可以拿到一把S锁，读读可以并行；
-
而只有一个事务可以拿到X锁，写写/读写必须互斥；
-
共享/排他锁的问题就是,不能充分的并行, 解决思路: MVCC
-
-
意向锁: 未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。
实际应用中, innodb 使用的是 意向锁.
意向锁的特点:
- 是一个 表级别 的锁
- 分为 意向排他锁(IX) 和 意向共享锁 (IS)
- select … lock in share mode; 设置 IS 锁, select … for update 设置 IX 锁.
意向锁协议(规则):
- 事务要想获得 S 锁,必须想获得表的 IS 锁
- 事务要想获得某些行的 X 锁, 必须获得表的 IX 锁.
锁之间的兼容关系:
-
插入意向锁: 是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。
对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。
例子:
读写并发问题: (RR级别)
事务A先执行 select * from t where id>10; 但是还未提交,
事务B 执行 insert into t values(11, xxx);
问题:
-
会使用什么锁?
不会使用锁, RR 级别下,事务 A 的 select 操作是 快照读,不会加锁
-
事务B 会不会被阻塞?
事务 B 不会阻塞,本来事务 A 也没有加锁.
-
-
自增锁(Auto-inc Locks)
概念:
- 自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。
- InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为。
实例演示:
- t(id AUTO_INCREMENT, name);
- 事务 A 先执行 insert into t(name) values(xxx); 未提交,
- 事务 B 后执行 insert into t(name) values(ooo);
- 此时事务 B 会被阻塞,这就是自增锁,等待事务 A commit 之后 事务 B 能够继续插入.
-
各种锁的实际使用小结:
InnoDB使用共享锁，可以提高读读并发；
为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；
InnoDB使用插入意向锁，可以提高插入并发；
意向锁: where 的条件字段,如果不是索引,则使用的是表锁,即 意向锁?
record:
gap lock:
next-key lock:
自增锁:
7. 事务
事务特点及实现
-
事务的特点 (想想是依靠什么来实现的)
A:原子性: undo log 出现异常,会回滚,成功的话记录到 redo log 中
C:一致性: 结合 CAP 里的一致性一起理解
I:隔离性: RU, RC,RR => MVCC(undo log), 串行化=> 加锁
D:持久性: redo log + bin log (MySQL45讲之23-MySQL是怎么保证数据不丢失的)
-
事务的实现
事务在执行的过程中会往 redo log buffer 以及 undo log 中写入日志,
设置 innodb_flush_log_at_trx_commit = 1 时,当事务 commit , 必须将 redo log buffer 中的内存通过 fsync 落盘到 redo log file.
undo log 中记录着事务执行过程的信息,并不会在事务提交之后立即删除,而是由 purge thread 线程判断是否删除.
此时 bin log cache 中的内容也要写入 bin log file.
这里使用两阶段提交的方式 commit :
-
redo log 和 bin log 如何关联起来?
-
两阶段提交的原因:
-
-
Undo + Redo事务的简化过程: (可能有错误,但是大致是这样?)
假设有A、B两个数据，值分别为1,2,开始一个事务，事务的操作内容为：把1修改为3，2修改为4，那么实际的记录如下（简化）：
- A.事务开始.
- B.记录A=1到undo log. //相当于老版本
- C.修改A=3. // 相当于复制了一份新的值进行更改
- D.记录A=3到redo log. // 写入 redo log buffer
- E.记录B=2到undo log.
- F.修改B=4.
- G.记录B=4到redo log.
- H.将redo log写入磁盘。// redo log buffer => fs page cache/redo log file
- I.事务提交
恢复:
- 如果事务未 commit ,此时 redo log buffer 被写入 redo log file,事务发生回滚了,redo log 的记录如何处理?
- 一个被回滚了的事务在恢复时的操作就是先redo再undo，因此不会破坏数据的一致性?
事务的隔离级别如何实现
-
不可重复读和幻读
-
undo 在事务中的作用
-
MVCC
事务的隔离级别,RU直接读取最新数据, RC和RR通过 MVCC (一致性非锁定读) 快照读实现,串行化通过互斥锁实现. 注意: 在 RR 下,通过加锁也可以避免幻读问题,概念不能搞混了. MVCC 在 RC 和 RR 下起作用,通过加锁的方式,也可以解决当前隔离级别下解决不了的问题参考:
MVCC 演变: 这个思路更重要
- 普通锁，本质是串行执行
- 读写锁，可以实现读读并发
- 数据多版本，可以实现读写并发
MVCC 实例:
- 旧版本数据存放在 回滚段 即 undo log 中。
- 表中的行记录会有3个字段： 分别记录 最近一次修改它的事务 ID， 记录指向回滚段日志的指针， 单调递增的行 ID
- DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID；
- DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针
- DB_ROW_ID，6字节，单调递增的行ID；
快照读的含义：
- 快照读（Snapshot Read），这种一致性不加锁的读（Consistent Nonlocking Read），就是InnoDB并发如此之高的核心原因之一。这里的一致性是指，事务读取到的数据，要么是事务开始前就已经存在的数据（当然，是其他已提交事务产生的），要么是事务自身插入或者修改的数据
一条记录被事务连续修改：
RC 和 RR 的实现：
-
可重复读RR：在事务开始的时候创建一致性视图（consistent read view），之后事务里的其他查询条件都共用这个一致性视图
查询只承认在事务启动前就已经提交完成的数据
-
已提交读RC：每一个语句执行前都会重新计算出一个新的视图（read view）。
查询只承认在语句启动前就已经提交完成的数据
-
更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。
读未提交: select 不加锁,可能会出现脏读的问题
读已提交: 普通 select 是快照读 ,锁 select/delete/update 会使用 record lock, 可能出现不可重复读的问题
可重复读: 普通 select 是快照读, 锁 select/delete/update 根据查询条件,会选择 record lock/ gap lock / next-key lock,防止幻读
串行化: select 隐式转化为 select xxx in share mode, 会被 update 和 delete 互斥
默认是: 可重复读 RR, 使用最多的是 RC. (读已提交) read committed
-
Read Commit 下 主从复制存在的一个问题
分析: bin log 使用了 statement 格式记录日志.
事务 B commit后, 先将 insert 语句写入日志,事务A commit 将 delete 语句写入日志,导致 slave 接收到的操作是 先插入在删除.
而 master 上是 先删除 然后在 插入,这就导致了主从不一致的情况.
解决方案:
- bin log 使用 row 格式记录日志, 记录了对于整行的更改的信息, 而不是 SQL 语句.
8. 主从复制_读写分离
MySQL 的配置和在框架中使用
-
o2o 项目中主从复制,并使用读写分离 的配置详解
配置步骤:
-
修改 master 的MySQL 的配置文件(具体位置根据系统不同,自己查看), 开启 binlog, 并添加下面参数,然后重启 mysql.
server-id=1
log-bin=master-bin
log-bin-index=master-bin.index
通过 SHOW MASTER STATUS; 可以查看配置情况, 其中 File slave 需要使用.
-
修改 slave 的MySQL 配置文件,修改如下,然后重启 mysql 服务.
server-id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
-
配置 master-slave 之间的关联.
-
在 master 创建一个专门用于主从同步的用户 repl,并赋予权限
-
在 slave 上通过 change master 命令,连接 master
-
在 slave 上 start slave;
-
可通过 show slave status \G; 查看状态,是否成功.
-
-
主从复制的原理
-
主从复制的过程: 腾讯工程师带你深入解析 MySQL binlog
-
在备库B上通过change master命令，设置主库A的IP、端口、用户名、密码，以及要**从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。**
-
在备库B上执行==start slave==命令，这时候备库会启动两个线程，就是图中的io_thread和sql_thread。其中 io_thread负责与主库建立连接。
-
主库A校验完用户名、密码后，开始按照备库B传过来的位置，从本地读取binlog，发给B。 master 通过 log dump thread 线程完成发送 binlg 操作
备库B 通过 调用 I/O thread 拿到binlog后，写到本地文件，称为中转日志（relay log）。
SQL thread 读取中转日志，解析出日志里的命令，并执行。
下面的回答比较完整:
Master将数据改变记录到二进制日志(binary log)中
Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容
Master接收到来自Slave的IO进程的请求后，负责复制的IO进程会根据请求信息读取日志指定位置之后的日志信息，返回给 Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置
Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master从某个bin-log的哪个位置开始往后的日志内容
Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行
bin log 的 row 格式: 如果是删除操作,会记录真实删除行的 主键 id,并
会把被删掉的行的整行信息保存起来, 更适合恢复数据的场景statement 格式: 记录了 SQL 语句, 可能会出现主从不一致的情况.
mixed 格式存在的合理性:
- 因为 row 很占用空间, 如果一个 delete 删除 10 W 条数据, row 记录了 10 W 条记录的修改,而 statement 指记录了 SQL 语句,不仅占用空间,而且耗费同步的 IO 资源
- 因此, mixed 作为折中方案就出现了, MySQL 会判断这个 SQL 是否会引起主备不一致,如果有可能则使用 row 格式,否则使用 statement
-
循环复制问题: 使用双 M 结构可能会出现的问题
-
双 M 结构存在的问题:
业务逻辑在节点A上更新了一条语句，然后再把生成的binlog 发给节点B，节点B执行完这条更新语句后也会生成binlog。那么，如果节点A同时是节点B的备库，相当于又把节点B新生成的binlog拿过来执行了一次，然后节点A和B间，会不断地循环执行这个更新语句，也就是循环复制了。
-
==如何解决: == 这里的备库是指 MySQL B
-
规定两个库的server id必须不同，如果相同，则它们之间不能设定为主备关系；
-
一个备库接到binlog并在==重放(???没看懂,难道是备库接收到 binlog后同步完成,备库也会生成binlog,这个过程叫重放)==的过程中，生成与原binlog的server id相同的新的binlog；
-
每个库在收到从自己的主库发过来的日志后，先判断server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志。
-
-
使用 双 M 结构, 日志的执行流程
- 从节点A更新的事务，binlog里面记的都是A的server id；
- 传到节点B执行一次以后，节点B生成的binlog 的server id也是A的server id；
- 再传回给节点A，A判断到这个server id与自己的相同，就不会再处理这个日志。所以，死循环在这里就
断掉了。
-
高可用之主备延迟
-
参考: MySQL 45 讲之 25,26
-
高可用((High Availability) => HA
-
主备延迟的时间如何计算
- 主库A执行完成一个事务，写入binlog，我们把这个时刻记为T1;
- 之后传给备库B，我们把备库B接收完这个binlog的时刻记为T2;
- 备库B执行完成这个事务，我们把这个时刻记为T3。
主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，也就是T3-T1。
可以通过 show slave status 命令查看, 字段 seconds_behind_master 记录了 当前备库延迟了多久.
-
主备延迟的可能原因
-
备库所在机器的性能要比主库所在的机器性能差。
-
备库的压力大(备库被用来执行一些分析语句…)
-
大事务 (典型的大事务场景: 一次性地用delete语句删除太多数据
|大表 的 DDL(重建表))因为主库上必须等事务执行完成才会写入binlog，再传给备库。
所以，如果一个主库上的语句执行10分钟，那这个事务很可能就会导致从库延迟10分钟。
-
备库的并行复制能力
-
来自于评论
1.主库DML语句并发大,从库qps高
2.从库服务器配置差或者一台服务器上几台从库(资源竞争激烈,特别是io)
3.主库和从库的参数配置不一样
4.大事务(DDL,我觉得DDL也相当于一个大事务)
5.从库上在进行备份操作
6.表上无主键的情况(主库利用索引更改数据,备库回放只能用全表扫描,这种情况可以调整slave_rows_sear
ch_algorithms参数适当优化下)
7.设置的是延迟备库
8.备库空间不足的情况下
-
-
主备切换的策略
可靠性优先策略
可用性优先策略
双 M 结构下, 使用可靠性优先策略 从状态 1 切换到状态 2 的过程:
- 这个切换流程中是有不可用时间的, 耗时主要集中在 步骤 3,此时 A 和 B 都是只读状态, 这也就是为什么要确保 seconds_behind_master 足够小的原因.
双 M 结构下, 使用可用性优先策略 从状态 1 切换到状态 2 的过程:
-
强行把步骤4、5调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库B，并且让备库B可以读写，那么系统几乎就没有不可用时间了。
-
-
此时存在的问题
可用性优先会导致数据的不一致问题,还可能因为插入键值重复而报错停止?
当binlog 使用 mixed 时,数据不一致不容易被发现,而 row 格式很容易就会发现问题. (上面的就是键值重复)
-
备库为什么会延迟好几个小时? (第26讲)
-
如果是上面介绍的原因,造成的备库延迟,影响都是分钟级别的,备库恢复正常很快就能恢复同步. 但是**如果备库执行日志的速度低于主库生成日志的速度,那这个延迟就可能成了小时级别. ** ====> 备库的并行复制(MySQL 5.6 支持并行复制)
-
MySQL 5.6 并行复制的粒度是按库并行的.
-
多线程模型 => relay log 由 coordinator 线程负责读取 relay log 和分发事务,真正更新日志的是 work 线程.
主库出问题,从库怎么办?
-
一主多从,读写分离 结构
-
在上面这个架构下,如果主库故障,备库 A’ 如何切换?
基于位点的主备切换
-
从库连接主库 ( 从库B连接A’ ) 时,需要执行一条 change master 命令.这个命令有 6 个参数, 主库 IP,端口,用户名,密码 以及 从主库的 master_log_name 文件的 master_log_pos 位置的日志继续同步. 这个位置就是所谓的同步位点, 也就是主库对应得文件名和日志偏移量. 难点就在于到底如何设置这两个参数?
-
如何找到同步位点?
- 等待新主库**A’**把中转日志（relay log）全部同步完成；
- 在A’上执行show master status命令，得到当前A’上最新的File 和 Position；
- 取原主库A故障的时刻 T；
- 用 mysqlbinlog 工具解析 A’ 的File，得到 T 时刻的位点。 使用 123 作为 master_log_pos 的值写在 slave B 中.
这个值是不精确的: 假设在T这个时刻，主库A已经执行完成了一个insert 语句插入了一行数据R，
并且已经将binlog传给了A’和B，然后在传完的瞬间主库A的主机就掉电了。- 此时从库 B ,由于同步了 binlog,已经存在了 R 这一行
- 新主库 A’ 上, R 这一行也存在,体制是写在 123 这个位置后的,即 123 是还未同步完 R 的位置
- 因此,在从库 B 上执行 change master 执行 123 位置, 会在插入 R 这一条记录,然后回报错 duplicate entry …,导致同步停止
- 此时的解决方案: 暂时跳过这些错误. 选择跳过这一个事务 或者 直接设置跳过指定的错误( 重复键/删除数据找不到行),待稳定执行一段时间后,需要将修改的东西改回来,不跳过这些错误.
GTID 解决了找同步点的问题 : Global Transaction Identifier 全局事务 ID
-
由两部分组成: GTID=server_uuid:gno (GTID=source_id:transaction_id 实际是这个,前面的减少误解)
server_uuid是一个实例第一次启动时自动生成的，是一个全局唯一的值；
gno是一个整数，初始值是1，每次提交事务的时候分配给这个事务，并加1。 -
GTID 的细节 想看再说吧,跳过 27讲
基于 GTID 的主备切换:
-
change master 的语法中参数 : master_auto_position=1就表示这个主备关系使用的是GTID协议.不需要指定 master_log_file 和 master_ log_pos 这两个参数了.
-
在基于GTID的主备关系里，系统认为只要建立主备关系，就必须保证主库发给备库的日志是完整的。因此，如果实例B需要的日志已经不存在，A’就拒绝把日志发给B。
-
切换逻辑
由于不需要找位点了,剩下的重新指定主库即可.
-
读写分离的坑
-
读写分离的架构
-
客户端主动做的负载均衡, 由客户端来选择后端数据库进行查询
-
MySQL和客户端之间有一个中间代理层proxy，客户端只连接proxy， 由proxy根据请求类型和上下文决定请求的分发路由。
-
两种方案的对比:
- 客户端直连方案
- 因为少了一层proxy转发，所以查询性能稍微好一点儿，并且整体架构简单，排查问题更方便。
- 但是这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。
- 一般采用这样的架构，一定会伴随一个负责管理后端的组件，比如Zookeeper，尽量让业务端只专注于业务逻辑开发。
- 带proxy的架构
- 对客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由proxy完成的。
- 但这样的话，对后端维护团队的要求会更高
- proxy也需要有高可用架构。因此，带proxy架构的整体就相对比较复杂。
-
-
使用哪种方案都会遇到的问题: 在从库上会读到系统的一个过期状态 即 过期读
常见的解决方案: 第28讲
-
强制走主库方案；
对于必须要拿到最新结果的请求，强制将其发到主库上.
-
sleep方案；
让查询等一会 如 1S, 然后在查的思想. 问题: 1) 本来这个查询0.5 S 执行完,还是会等 1S, 2) 可能等了 1S 还是国企读
-
判断主备无延迟方案；
判断 seconds_behind_master 值是否为 0. 单位是 秒S. 如果觉得精度不够,则使用 对比位点 或者 对比 GTID 集合 的方法.
还存在的问题: ** 主库 trx1,trx2, trx3 都指行完成,并将 trx2 和 trx3 传到从库执行完成并返回给客户端, 但是 trx3 还未传给从库.此时在从库执行查询请求, 从库认为没有同步延迟(为什么认为没有延迟了?和 GTID 有关么? 不懂)**, 但是查不到 trx3 的数据. 过期读.
-
配合semi-sync方案；(半同步复制) MySQL主从复制原理（深度挖掘）
- 如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。
- semi-sync配合前面关于位点的判断，就能够确定在从库上执行的查询请求，可以避免过期读。
- semi-sync+位点判断的方案，只对一主一备的场景是成立的
半同步复制存在的问题1:一主多从场景中,主库只要等到一个从库的ack,就开始给客户端返回确认。这时,在从库上执行查询请求，就有两种情况：- 如果查询是落在这个响应了ack的从库上，是能够确保读到最新数据；
- 但如果是查询落到其他从库上，它们可能还没有收到最新的日志，就会产生过期读的问题。
半同步复制存在的问题2判断同步位点的方案还有另外一个潜在的问题，即：如果在业务更新的高峰期，主库的位点或者GTID集合更新很快，那么上面的两个位点等值判断就会一直不成立，很可能出现从库上迟迟无法响应查询请求的情况。 (具体看第28讲的解释)
-
等主库位点方案
select master_pos_wait(file, pos[,timeout]);
- 它是在从库执行的；
- 参数file和pos指的是主库上的文件名和位置；
- timeout可选，设置为正整数N表示这个函数最多等待N秒。
- 这个命令正常返回的结果是一个正整数M，表示从命令开始执行，到应用完file和pos表示的binlog位置，执
行了多少事务。 = 0 ,表示 已经执行过这个位置了, = -1,等待超过 N 秒, 备库同步线程异常 返回 null.
所以就有了下面的方案:
- trx1事务更新完成后，马上执行show master status得到当前主库执行到的File和Position；
- 选定一个从库执行查询语句；
- 在从库上执行select master_pos_wait(File, Position, 1)；
- 如果返回值是>=0的正整数，则在这个从库执行查询语句；
- 否则，到主库执行查询语句。
-
等GTID方案
如果你的数据库开启了GTID模式，对应的也有等待GTID的方案。
select wait_for_executed_gtid_set(gtid_set, 1);
- 等待，直到这个库执行的事务中包含传入的gtid_set，返回0；
- 超时返回1。
在前面等位点的方案中，我们执行完事务后，还要主动去主库执行show master status。而MySQL 5.7.6版本开始，允许在执行完更新类事务后，把这个事务的GTID返回给客户端，这样等GTID的方案就可以减少一次查询。
等GTID的执行流程: 和上面的等待主库位点方案类似
-
trx1事务更新完成后，从返回包直接获取这个事务的GTID，记为gtid1；
将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_first从返
回包解析出GTID的值即可。 -
选定一个从库执行查询语句；
-
在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
-
如果返回值是0，则在这个从库执行查询语句；
-
否则，到主库执行查询语句。
-
-
小结:
- 即使是最后等待位点和等待GTID这两个方案，虽然看上去比较靠谱儿，但仍然存在需要权衡的情况。如果所有的从库都延迟，那么请求就会全部落到主库上，这时候会不会由于压力突然增大，把主库打挂了呢？
- 在实际应用中，这几个方案是可以混合使用的。比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等GTID或等位点的方案。
- 过期读在本质上是由一写多读导致的
9. redo undo bin log 详解
bin log
-
参考文献
MySQL45讲: 23-MySQL是怎么保证数据不丢的？
-
bin log里面的内容用的是主键索引上的
-
bin log 的写入机制
事务执行过程中, 先把日志写入 bin log cache 中,等事务 commit 过后,在把 bin log cache 写到 bin log file 中.
一个事务的 bin log 是不能被拆开的,因此不论事务多大,都要确保一次性写入.
图解:
sync_binlog 设置为 N(N>1),可以提升性能,对于的风险是,可能会丢失最近的 N 个事务的 bin log file
redo log
-
参考文献
MySQL45讲: 23-MySQL是怎么保证数据不丢的？
-
可以实现事务的持久性和原子性
-
持久性： 事务 commit 时，必须将 事务的所有日志写入 redo log file 中。<== innodb_flush_log_at_trx_commit = 1
事务未提交的时候,会写入 redo log buffer => 等待执行 commit 之后将redo log 进行落盘,可能会写入 FileSystemCache,因此要调用 fsync 强制写入到 redo log file 中.(文件名: ib_logfile+数字)
redo log 存在的3个地方 => redo log buffer , fs page cache, hard disk
注意: 在事务未 commit 时候,也可能出现 redo log buffer 写入 redo log file 的情况, 比如 redo log buffer 占用空间超过 innodb_log_buffer_size 的一半, 并行提交事务的时候,顺带将这个事务的 redo log buffer 持久化了.
-
组提交机制
undo log
-
参考文献
-
用于实现事务的回滚和 MVCC
-
回滚日志并不能将数据库物理地恢复到执行语句或者事务之前的样子；它是逻辑日志，当回滚日志被使用时，它只会按照日志逻辑地将数据库中的修改撤销掉看，可以理解为，我们在事务中使用的每一条
INSERT都对应了一条DELETE，每一条UPDATE也都对应一条相反的UPDATE语句。 -
undo 存放在数据库的特殊段中,称为 回滚段(undo segment).
-
事务在 undo log segment 分配页写入 undo log 的这个过程中,同样需要写入 redo log.当事务需要提交时,innodb 会做以下两件事
- 将 undo log 放入列表,以供后面 purge 操作
- 判断 undo log 所在的页是否可以复用,若可以,分配给下一个事务使用
- 注意: 事务提交后,不能马上删除undo log 及 undo log 所在的页.因为还有其他事务需要通过undo log 来得到记录之前的版本.因此事务commit后,将undo lo g 放入一个链表中, 由purge 线程判断是否删除
-
delete 操作并不直接删除记录,而只是将记录标记为已删除, 将记录的 delete flag 设置为 1.最终的删除在purge 中完成
purge
-
真正删除记录的操作: 这行记录已经不被其它任何事务引用
-
purge 的过程**(详细的看 xmind)**
- 首先从 history list 中找到第一个需要被清理的记录trx1,
- 清理之后,innodb在trx1的undo log所在页继续寻找是否存在可以被清理的记录,这里会找到 trx3,trx5(但trx5被其他事务引用)
- 然后再次去 history list 页中查找,找到 trx2,然后清理trx2.trx6,trx4.
- 此时 undo log page 中所有undo log 都被清理了,undo page 可以重用了
- 这样的查找过程避免了大量的随机读取,提高了 purge 效率
- history list:表示按照事务提交的顺序将 undo log 进行组织,先提交的事务总是在尾端.
ze 的一半, 并行提交事务的时候,顺带将这个事务的 redo log buffer 持久化了.
-
组提交机制
undo log
-
参考文献
-
用于实现事务的回滚和 MVCC
-
回滚日志并不能将数据库物理地恢复到执行语句或者事务之前的样子；它是逻辑日志，当回滚日志被使用时，它只会按照日志逻辑地将数据库中的修改撤销掉看，可以理解为，我们在事务中使用的每一条
INSERT都对应了一条DELETE，每一条UPDATE也都对应一条相反的UPDATE语句。 -
undo 存放在数据库的特殊段中,称为 回滚段(undo segment).
-
事务在 undo log segment 分配页写入 undo log 的这个过程中,同样需要写入 redo log.当事务需要提交时,innodb 会做以下两件事
- 将 undo log 放入列表,以供后面 purge 操作
- 判断 undo log 所在的页是否可以复用,若可以,分配给下一个事务使用
- 注意: 事务提交后,不能马上删除undo log 及 undo log 所在的页.因为还有其他事务需要通过undo log 来得到记录之前的版本.因此事务commit后,将undo lo g 放入一个链表中, 由purge 线程判断是否删除
-
delete 操作并不直接删除记录,而只是将记录标记为已删除, 将记录的 delete flag 设置为 1.最终的删除在purge 中完成
purge
-
真正删除记录的操作: 这行记录已经不被其它任何事务引用
-
purge 的过程**(详细的看 xmind)**
- 首先从 history list 中找到第一个需要被清理的记录trx1,
- 清理之后,innodb在trx1的undo log所在页继续寻找是否存在可以被清理的记录,这里会找到 trx3,trx5(但trx5被其他事务引用)
- 然后再次去 history list 页中查找,找到 trx2,然后清理trx2.trx6,trx4.
- 此时 undo log page 中所有undo log 都被清理了,undo page 可以重用了
- 这样的查找过程避免了大量的随机读取,提高了 purge 效率
- history list:表示按照事务提交的顺序将 undo log 进行组织,先提交的事务总是在尾端.
214
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
