图片来自极客时间,如有版权问题,请联系我删除。
扫码加入学习!
01 | 基础架构:一条SQL查询语句是如何执行的?
大体来说,MySQL分为Server层和存储引擎层两部分。
连接器:负责跟客户端建立连接、获取权限、维持和管理连接。
查询缓存:查询请求先访问缓存(key 是查询的语句,value 是查询的结果)。命中直接返回。不推荐使用缓存,更新会把缓存清除(关闭缓存:参数 query_cache_type 设置成 DEMAND)。
分析器:对 SQL 语句做解析,判断sql是否正确。
优化器:决定使用哪个索引,多表关联(join)的时候,决定各个表的连接顺序。
执行器:执行语句,先判断用户有无查询权限,使用表定义的存储引擎。
02 | 日志系统:一条SQL更新语句是如何执行的?
redo log
MySQL WAL 技术,先写日志,再写磁盘。保证掉电重启,数据不丢失(crash-safe)。
redo log 是 InnoDB 引擎特有的日志。
当记录更新时,Innodb 先记录 redo log 再更新内存,这时更新就算完成。引擎往往会在系统空闲时刷盘。
redo log 是实现了类似环形缓冲区,一个指针 write pos 是当前记录的位置,另一个指针 checkpoint 是当前要擦除的位置,write pos 和checkpoint 之间是空闲部分。如果 write pos 快追上 checkpoint 时,代表缓冲区快满了,需要暂停刷盘。(CP’见12讲)
innodb_flush_log_at_trx_commit参数:
0:log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。
1:每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。
2:每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
binlog(归档日志)
Server层日志。binlog 日志只能用于归档,没有crash-safe能力。
三个用途:
- 恢复:利用binlog日志恢复数据库数据
- 复制:主从同步
- 审计:通过二进制日志中的信息来进行审计,判断是否有对数据库进行注入攻击
常见格式:
format | 定义 | 优点 | 缺点 |
---|---|---|---|
statement | 记录的是修改SQL语句 | 日志文件小,节约IO,提高性能 | 准确性差,对一些系统函数不能准确复制或不能复制,如now()、uuid()等 |
row(推荐) | 记录的是每行实际数据的变更,记两条,更新前和更新后 | 准确性强,能准确复制数据的变更 | 日志文件大,较大的网络IO和磁盘IO |
mixed | statement和row模式的混合 | 准确性强,文件大小适中 | 有可能发生主从不一致问题 |
sync_binlog参数:
0:当事务提交后,Mysql仅仅是将binlog_cache中的数据写入Binlog文件,但不执行fsync之类的磁盘 同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。
n:在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,同志文件系统将Binlog文件缓存刷新到磁盘。
不同点:
- redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
- redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。binlog 文件到一定大小,会切换到下一个文件。
update执行过程:
mysql> update T set c=c+1 where ID=2;
- 1
两阶段提交
1 prepare阶段 2 写binlog 3 commit
当在2之前崩溃时
重启恢复:后发现没有commit,回滚。备份恢复:没有binlog 。
当在3之前崩溃
重启恢复:虽没有commit,但满足prepare和binlog完整,所以重启后会自动commit。备份:有binlog 。
03 | 事务隔离:为什么你改了我还看不见?
事务ACID、隔离级别
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 |
不可重复读(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
总结:
RR下,事务在第一个Read操作时,会建立read-view
RC下,事务在每次Read操作时,都会建立read-view
不同业务选择不同的隔离级别。
回滚段
rollback segment称为回滚段,每个回滚段中有1024个undo log segment。每个undo操作在记录的时候占用一个undo log segment。
undo log有两个作用:提供回滚和多个行版本控制(MVCC)。
在数据修改的时候,不仅记录了redo,还记录了相对应的undo,如果因为某些原因导致事务失败或回滚了,可以借助该undo进行回滚。
undo log和redo log记录物理日志不一样,它是逻辑日志。可以认为当delete一条记录时,undo log中会记录一条对应的insert记录,反之亦然,当update一条记录时,它记录一条对应相反的update记录。
事务隔离的实现
以可重复读(RR)为例,每条记录在更新的时候都会同时记录一条回滚操作。
不同时刻启动的事务会有不同的 read-view。同一条记录在系统中可以存在多个版本,就是数据库的多版本并发控制(MVCC)。
当系统里没有比这个回滚日志更早的 read-view 的时候,回滚日志会被删除。所以要避免长事务。
04 | 深入浅出索引(上)
索引的常见模型
哈希表,不适合做区间搜索。
有序数组,只适合静态数据,插入麻烦。
二叉搜索树,N叉树。
InnoDB 的索引模型
在 MySQL 中,索引是在存储引擎层实现的。
以主键顺序存在B+树中。
主键索引(聚簇索引) 的叶子节点存的是整行数据。主键查询主需要扫描主键索引。
非主键索引(二级索引)的叶子节点内容是主键的值。通过二级索引需要扫描二级索引树,找到主键后再扫描主键索引。该过程称为回表。
索引维护
当插入到索引树最后,只需直接插入。
但当插入到索引树中间,需要逻辑上挪动后面的数据,空出位置,并且当数据页满时,需要申请一个新的数据页,然后挪动部分数据过去(页分裂)。
当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。
自增索引(追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂)
业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
二级索引的叶子节点为主键,业务字段做主键时会占大量存储空间。
什么时候可以使用业务字段做主键? 只有一个索引;该索引必须是唯一索引。
索引重建
alter table T engine=InnoDB
- 1
不推荐drop,再add。并且不论是删除主键还是创建主键,都会将整个表重建。
05 | 深入浅出索引(下)
覆盖索引
当查询值已经在二级索引上时,不需要回表。
最左前缀原则
联合索引合理安排顺序,可以少维护索引,或者减少存储空间。
CREATE TABLE `geek` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `c` (`c`),
KEY `ca` (`c`,`a`),
KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
索引ca可以去掉,因为c和主键ab,和ca和主键ab相同。
索引下推
MySQL 5.6 引入的索引下推优化,可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?
mysql锁大致可以分成全局锁、表级锁和行锁三类
全局锁
全局锁的典型使用场景是,做全库逻辑备份。
FTWRL命令:
Flush tables with read lock;
- 1
官方自带的逻辑备份工具是 mysqldump,当 mysqldump 使用参数–single-transaction 的时候,导数据之前就会启动一个事务,来确保拿到一致性视图。
但当引擎不支持事务时,只能使用FTWRL 命令了。不推荐不使用 set global readonly=true,readonly会被其他逻辑使用(比如判断主从),readonly发生异常会保持该状态。
表级锁
MySQL 里面表级别的锁有两种:一种是表锁,一种是元数据锁(meta data lock,MDL)。
表锁的语法是 lock tables … read/write。
MDL不需要显式使用,在访问一个表的时候会被自动加上。
当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。
当一个长事务还没提交,进行表结构变更操作,会导致后面的事务block。当客户端有重试机制时,新起session请求,会导致库的线程很快就会爆满。
如何安全地给小表加字段?
- 避免长事务。
- 在 alter table 语句里面设定等待时间。
MariaDB 已经合并了 AliSQL 的这个功能,所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...
- 1
- 2
07 | 行锁功过:怎么减少行锁对性能的影响?
行锁
Mysql行锁由引擎层实现
两阶段锁
行锁需要事务结束时才释放,这就是两阶段锁。
所以需要合理安排事务中sql执行顺序,尽量把容易冲突的更新语句放在后面。
死锁和死锁检测
- 设置超时时间,innodb_lock_wait_timeout。
- 死锁检测,发现死锁主动回滚某个事务,innodb_deadlock_detect 默认on。
假设1000个同时更新一行,则死锁检测操作就是 100 万这个量级的。即使没有死锁,检测也会消耗大量的 CPU 资源。
解决方案:
- 业务不会出现死锁,可以临时关闭。
- 在客户端控制并发。
- 修改MySQL 源码,并发进入引擎之前排队。
- 将一行数据改为多行,如将一个余额账户分为多个,但在数据减少操作时需考虑小于0的情况。
08 | 事务到底是隔离的还是不隔离的?
快照”在 MVCC 里是怎么工作的?
InnoDB 里面每个事务有一个唯一的事务 ID,叫作 transaction id。它是在事务开始的时候向InnoDB 的事务系统申请的,是按申请顺序严格递增的。
而每行数据也都是有多个版本的。每次事务更新数据的时候,都会生成一个新的数据版本,并且把 transaction id赋值给这个数据版本的事务 ID,记为 row trx_id。
也就是说,数据表中的一行记录,其实可能有多个版本 (row),每个版本有自己的 row trx_id。
上图中的三个虚线箭头就是undo log。
某个事务建立快照,只需根据transaction id。只认事务启动时小于数据版本的数据,除自己更新的数据。
快照实现
InnoDB在每个事务启动瞬间,构造了数组保存了当前启动但未提交的事务ID。
数组ID最小值为低水位,当前系统最大事务ID+1为高水位。
数组和高水位,组成了当前事务的一致性事务(read-view)。
黄色部分需分为以下两种情况,因为有可能大于低水位的某个事务已经提交:
- 若 row trx_id 在数组中,表示这个版本是由还没提交的事务生成的,不可见;
- 若 row trx_id 不在数组中,表示这个版本是已经提交了的事务生成的,可见。
select read-view创建在03 | 事务隔离中提过了,就不写了。
更新数据都是先读后写的,而这个读,只能读当前的值,称为“当前读”(current read)。
如果当前的记录的行锁被其他事务占用的话,就需要进入锁等待。
不同隔离级别:
- 对于可重复读,查询只承认在事务启动前就已经提交完成的数据
- 对于读提交,查询只承认在语句启动前就已经提交完成的数据
- 而当前读,总是读取已经提交完成的最新版本。
09 | 普通索引和唯一索引,应该怎么选择?
查询过程
操作成本相差无几。
更新过程
change buffer概念
change buffer是持久化数据,在内存中有拷贝,也会写到磁盘上。
当更新数据页时,如数据页在内存中直接更新。如果不在,在不影响数据一致性的前提下,innodb会将更新操作先缓存到change buffer中,当下次查询该数据页时,执行change buffer中与该页相关的操作。该操作称为merge,除了该情况,系统后台线程也会定期merge,数据库正常关闭也会merge。
change buffer可以减少读磁盘,而且数据读入内存会占用buffer pool。
什么条件下可以使用 change buffer 呢?
对于唯一索引,更新操作都需要判断操作是否违反唯一约束,所以需要将数据都读入到内存,所以会直接更新内存。
所以只有普通索引会使用change buffer。
change buffer使用buffer pool里的内存,参数innodb_change_buffer_max_size设置为50时,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。
当更新记录的目标页不在内存中时,InnoDB 的处理流程如下:
- 对于唯一索引来说,需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
- 对于普通索引来说,则是将更新记录在change buffer,语句执行就结束了。
所以这种情况,唯一索引会导致磁盘大量随机IO的访问(机械硬盘瓶颈)。
但这种情况不是绝对的,写多读少的场景change buffer记录的变更多,收益越大。常见业务模型账单类、日志类的系统。对于写完马上读取的情况,会立即触发merge,反而增加了维护change buffer的成本。
所以尽量选择普通索引。
change buffer 和 redo log
插入语句:
insert into t(id,k) values(id1,k1),(id2,k2);
- 1
假设当前 k 索引树的状态,查找到位置后,k1 所在的数据页在内存(InnoDB buffer pool) 中,k2 所在的数据页不在内存中。下图所示是带 change buffer 的更新状态图。
操作顺序:
- Page 1 在内存中,直接更新内存
- Page 2 没有在内存中,就在内存的change buffer 区域,记录下“我要往 Page 2 插入一行”这个信息
- 将上述两个动作记入 redo log 中(图中 3 和 4)
图中的两个虚线箭头,是后台操作,不影响更新的响应时间。
执行查询操作:
select * from t where k in (k1, k2);
- 1
假设内存中的数据都还在,此时的这两个读操作就与系统表空间(ibdata1)和 redo log(ib_log_fileX)无关。
- 读 Page 1 的时候,直接从内存返回。不需要等内存中的数据更新后返回。
- 要读 Page 2 的时候,需要把 Page 2 从磁盘读入内存中,然后应用 change buffer 里面的操作日志(可能有多个),依次merge一个正确的版本。然后写redo log,redo log中包含数据变更和change buffer 变更。此时内存中数据页为脏页,刷脏是后台线程的流程。如果某个数据页刷脏完成,当redo log中对应的该条刷盘时会识别出来并且跳过。
redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。
最后到底怎么选索引:
- 业务正确性优先,业务可以保证不重复,普通索引提升效率。业务不能保证重复,就需要唯一索引保证。
- 历史数据归档库没有唯一索引冲突,可以选择普通索引。
10 | MySQL为什么有时候会选错索引?
平常不断地删除历史数据和新增数据的场景,mysql有可能会选错索引。
优化器的逻辑
优化器选择索引的目的就是选择一个扫描行数最少的方案。行数越少,磁盘读取越少。
扫描行数不是唯一标准,优化器还会结合是否使用临时表,是否排序等因素。
扫描行数怎么判断?
真正执行语句之前,mysql不知道具体有多少条,只能根据统计信息估算。
这个统计信息就是索引的“区分度”。索引上不同值越多,区分度越好。而一个索引上不同值的个数称为“基数”。
使用show index可以查看。下图中,每行三个字段值都是一样的,但在统计信息中,基数值都不准确。
mysql怎么得到索引的基数?
mysql采用采样统计,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。
参数 innodb_stats_persistent有两种不同的模式
- 设置为 on 的时候,表示统计信息会持久化存储。默认 N 是 20,M 是 10。
- 设置为 off 的时候,表示统计信息只存储在内存中。默认 N 是 8,M 是 16。
如果统计信息不对,可以使用analyze table t 命令重新统计。
索引选择异常和处理
- force index 强行选择一个索引
- 修改语句,引导 MySQL 使用我们期望的索引
- 新建索引,或者删除误用的索引
这章老师举了几个例子,就不写了。sql太慢就用explain看看,有可能就是索引选错了。
11 | 怎么给字符串字段加索引?
mysql支持前缀索引,可以以字符串一部分作为索引。默认包含整个字符串。
alter table t index idx(a(6));
- 1
使用前缀索引虽然可以减少存储空间,但有可能会增加回表次数。
建前缀索引前可以使用下面的sql统计一下重复数:
select count(distinct left(a,字符长度));
- 1
并且前缀索引会影响覆盖索引。
其他方式
- 倒序存储
由于身份证前面的地区码都是相同的,所以存储身份证时,可以将它倒过来存。身份证后6位作为前缀索引有一定的区分度。
select field_list from t where id_card = reverse('input_id_card_string');
- 1
- 使用hash字段
可以在表上再创建一个整数字段,来保存身份证的校验码,同时在这个字段上创建索引。
插入新数据,使用crc32()得到该字段填入。
查询语句如下:
select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card_string';
- 1
另外,如果前缀后缀都重复,可以考虑去掉前缀后缀,只存中间一部分数据。
12 | 为什么我的MySQL会“抖”一下?
脏页的概念就不记了。
MySQL 偶尔慢一下的那个瞬间,可能在刷脏页(flush)。
什么时候会触发刷脏?
-
innodb的redo log写满了,这时候系统会停止所有更新。把checkpoint 往前推进。
-
buffer pool内存不足,此时需要淘汰一些数据页,有可能会淘汰脏页,就要先把脏页刷到磁盘。
刷脏页一定会写盘,就保证了每个数据页有两种状态:
a. 内存里的一定是正确数据。
b. 内存里没有,磁盘上的一定是正确数据。 -
mysql认为系统空闲时,会刷盘。当然系统繁忙时,也会见缝插针刷盘。
-
mysql正常关闭。
PS 可以参考一下:数据库内核月报 - 2017 / 05
InnoDB 刷脏页的控制策略
告诉 InnoDB 所在主机的 IO 能力,正确地设置innodb_io_capacity 参数,使用fio工具统计:
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
- 1
innodb_max_dirty_pages_pct是脏页比例上限,默认值是 75%。
平时要多关注脏页比例,不要让它经常接近 75%。
脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到:
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
- 1
- 2
- 3
另外还有一个策略,当刷脏页时,该页边上也是脏页,也会把边上的脏页一起刷掉。而且该逻辑会一直蔓延。
innodb_flush_neighbors 参数就是来控制该行为的,值为1会有上述机制,0则不会。
机械硬盘可能会有不错的效果,但ssd建议设置为0。
并且mysql 8.0 innodb_flush_neighbors 默认为0。
13 | 为什么表数据删掉一半,表文件大小不变?
mysql8.0 之前,表结构以.frm为后缀的文件里。而8.0版本允许表结构定义放在系统数据表中,因为该部分占用空间很小。
参数 innodb_file_per_table
表数据既可以存在共享表空间里,也可以是单独的文件。
- OFF,表示表的数据放在系统共享表空间,也就是跟数据字典放在一起。drop table及时表删掉了,空间也不会回收。
- ON(5.6.6版本后默认值),表示每个innodb表数据存储在以.ibd为后缀的文件中。drop table系统会直接删除这个文件。
以下内容基于innodb_file_per_table on展开。
假设要删除R4,innodb只会标记R4删除。如果之后插入一个ID在300和600之间的记录时,可能会复用该位置。如果删掉整页,整个数据页可以被复用。所以磁盘文件大小不会缩小。
但记录复用,只能插入符合范围的数据。不能插入300~600范围外的数据。
页的复用,可以插入任何新数据。如pageA数据删除后,可以插入ID=50的数据。
如果相邻数据页利用率都很小,系统会把两个页的数据合到其中一个页上,另一个标记为可复用。
如果使用delete命令,那么所有数据页标记为可复用。
插入数据也会产生空洞,如果按索引递增插入,那么索引是紧凑的。如果数据插入随机,可能造成索引数据页分裂。
当某页满时,再插入数据,就会申请一个新页,将旧页的部分数据保存到新页中。所以旧页中可能有空洞。
更新索引,可能理解为删除旧值,插入新值。也会造成空洞。
重建表
重建表,可以新建一个表,将旧表中的数据一行一行读出来插入到新表中。然后以新表替换旧表。
可以使用 alter table A engine=InnoDB 命令来重建表。在mysql 5.5版本前,该命令流程与上述流程类似。
在此过程中,不能更新旧表数据。
MySQL 5.6 版本开始引入的 Online DDL,对该操作流程做了优化。
- 建立一个临时文件,扫描表 A 主键的所有数据页;
- 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
- 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中,对应的是图中 state2 的状态;
- 临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表 A 相同的数据文件,对应的就是图中state3 的状态;
- 用临时文件替换表 A 的数据文件。
重建方法都会扫描原表数据和构建临时文件。对于很大的表来说,这个操作是很消耗 IO 和 CPU 资源的。
如果是线上服务,要控制操作时间。如果想要比较安全的操作,推荐使用github开源的gh-ost。
optimize table、analyze table和 alter table 这三种方式重建表的区别。
- 从 MySQL 5.6 版本开始,alter table t engine = InnoDB(也就是 recreate)默认是上图的流程;
- analyze table t 其实不是重建表,只是对表的索引信息做重新统计,没有修改数据,这个过程中加了 MDL 读锁;
- optimize table t 等于 recreate+analyze。
14 | count(*)这么慢,我该怎么办?
count(*) 的实现方式
- MyISAM 引擎保存总行数,所以count很快。但如果加了where不能很快返回。
- Innodb需要一行一行读出来累积计数。
innodb由于多版本并发控制(MVCC)的原因,多个事务count的行数不同,所以不能保存总行数。
但count(*)做了优化,引擎会选择最小的普通索引树,来计数。而不是直接统计聚集索引树。
show table status 命令输出TABLE_ROWS 显示这个表当前有多少行,但它也是采样估算来的。官方文档说误差可能达到 40% 到 50%。
用缓存系统保存计数
两个问题:
- 缓存会丢失
- 缓存不准确,因为缓存计数和插入数据不是原子操作,有可能在中间过程,其他事务读取了数据。
在数据库保存计数
使用一张表保存计数,由于事务可以解决使用缓存问题。
不同的 count 用法
下面的讨论还是基于 InnoDB 引擎的
- count(主键 id) ,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。
- count(1),InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。
- count(字段)
a. 如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;
b. 如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。 - count(*),并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是 null,按行累加。
按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以建议尽量使用 count(*)。
15 | 答疑文章(一):日志和索引相关问题
日志相关问题
第2篇文章《日志系统:一条 SQL 更新语句是如何执行的?》,两阶段提交的不同瞬间MySQL 如果发生异常重启,是怎么保证数据完整性的?
原图
如果redo处理perpare阶段,写binlog之前崩溃(crash),恢复时事务回滚。
如果binlog写完了,redo未commit前崩溃(crash):
- 如果redo log事务完整,有了commit标识,直接提交;
- 如果redo log里事务只有完整的perpare,则判断对应事务binlog是否完整:
a. 如果是,则提交事务;
b. 否则回滚。
追问 1:MySQL 怎么知道 binlog 是完整的?
回答:一个事务的binlog是有完整格式的:
- statement 格式的 binlog,最后会有 COMMIT;
- row 格式的 binlog,最后会有一个 XID event。
mysql 5.6.2版本以后,引入binlog-checksum验证binlog内容是否正确。
追问 2:redo log 和 binlog 是怎么关联起来的?
回答:它们有个共同的数据字段:XID。
追问 3:处于 prepare 阶段的 redo log 加上完整 binlog,重启就能恢复,MySQL 为什么要这么设计?
回答:因为写入binlog后,会被从库使用,为了保证主备一致性。
追问 4:如果这样的话,为什么还要两阶段提交呢?干脆先 redo log 写完,再写 binlog。崩溃恢复的时候,必须得两个日志都完整才可以。是不是一样的逻辑?
回答:两阶段提交是经典分布式系统问题,并不是mysql独有的。
innodb,如果redo log提交完成,事务就不能回滚(如果还允许回滚,可能覆盖掉别的事务的更新)。但如果redo log直接提交,binlog写失败时,innodb回滚不了 ,数据和binlog日志会不一致。两阶段提交就是为了每个“人”都ok,在一起提交。
追问 5:不引入两个日志,也就没有两阶段提交的必要了。只用 binlog 来支持崩溃恢复,又能支持归档,不就可以了?
回答:不可以,历史原因,innodb不是mysql原生引擎,binlog不支持崩溃恢复,所以innodb实现了redo log。
追问 6:那能不能反过来,只用 redo log,不要 binlog
回答:如果从崩溃恢复角度来讲是可以的。但redo log是循环写,历史日志没法保留,而binlog有归档功能。binlog还有可以实现复制主从同步。
追问 7:redo log 一般设置多大?
回答:redo log太小会导致很快写满,然后就会强行刷redo log。如果几个TB硬盘,直接将redo log设置为4个文件,每个文件1G。
追问 8:正常运行中的实例,数据写入后的最终落盘,是从 redo log 更新过来的还是从 buffer pool 更新过来的呢?
回答:这个问题就是“redo log 里面到底是什么”的问题。
redo log没有记录数据页完整数据,所以它没有能力自己去更新磁盘数据页。
- 如果再次运行的实例,数据页被修改,跟磁盘数据页不一致,称为脏页。最终数据落盘,就是把内存中的数据页写盘。这过程和redo log毫无关系。
- 在崩溃恢复场景,Innodb如果判断一个数据页可能在崩溃恢复时丢失更新,就会将它读到内存,然后让redo log更新内存内容。更新完成内存也变成脏页,就回到第一种情况。
ps:老师说这个问题很好,我之前学习的时候也想不明白刷盘流程,搜索了好久。
追问 9:redo log buffer 是什么?是先修改内存,还是先写 redo log 文件?
回答:在一个事务的更新过程中,日志是要写多次的。比如下面这个事务:
begin;
insert into t1 ...
insert into t2 ...
commit;
- 1
- 2
- 3
- 4
这个事务往两个表中插记录过程中,生成的日志都要先保存起来,但不能在未commit的时候写到redo log里。
所以redo log buffer就是一块内存,用来先存redo日志。也就是说,在执行第一个 insert 的时候,数据的内存被修改了,redo log buffer 也写入了日志。
但是,真正写redo log文件(文件名是ib_logfile+数字),是在执行commit时做的。
单独执行一个更新语句,innodb会自己启动一个事务,过程和上述内容一致。
业务设计问题
问题描述:
业务上有这样的需求,A、B 两个用户,如果互相关注,则成为好友。设计上是有两张表,一个是 like 表,一个是 friend 表,like 表有 user_id、liker_id 两个字段,我设置为复合唯一索引即uk_user_id_liker_id。语句执行逻辑是这样的:
以 A 关注 B 为例:
第一步,先查询对方有没有关注自己(B 有没有关注 A)
select * from like where user_id = B and liker_id = A;
- 1
如果有,则成为好友
insert into friend;
- 1
没有,则只是单向关注关系
insert into like;
- 1
但是如果 A、B 同时关注对方,会出现不会成为好友的情况。因为上面第 1 步,双方都没关注对方。**第 1 步即使使用了排他锁也不行,因为记录不存在,行锁无法生效。**请问这种情况,在 MySQL 锁层面有没有办法处理?
表结构:
CREATE TABLE `like` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`liker_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;
CREATE TABLE </span>friend<span class="token punctuation">
(
id</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span> <span class="token punctuation">
friend_1_id</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token punctuation">
firned_2_id</span> <span class="token keyword">int</span><span class="token punctuation">(</span><span class="token number">11</span><span class="token punctuation">)</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">UNIQUE</span> <span class="token keyword">KEY</span> <span class="token punctuation">
uk_friend</span> <span class="token punctuation">(</span><span class="token punctuation">
friend_1_id</span><span class="token punctuation">,</span><span class="token punctuation">
firned_2_id</span><span class="token punctuation">)</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token punctuation">
id`)
) ENGINE=InnoDB;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
由于一开始A和B之间没有关注关系,所以两个事务select都为空。因此分别插入一个单向关注关系,这结果对业务来说就是bug了。
另外一个方法,来解决这个问题。
给“like”表增加一个字段,比如叫作 relation_ship,并设为整型,取值 1、2、3。
- 值是 1 的时候,表示 user_id 关注 liker_id;
- 值是 2 的时候,表示 liker_id 关注 user_id;
- 值是 3 的时候,表示互相关注。
当A关注B时逻辑改成如下所示:
应用代码里面,比较 A 和 B 的id大小,如果 A <B,就执行下面的逻辑
mysql> begin; /* 启动事务 */
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;
/* 代码中判断返回的 relation_ship,
如果是 1,事务结束,执行 commit
如果是 3,则执行下面这两个语句:
*/
insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
如果A>B,则执行下面的逻辑:
mysql> begin; /* 启动事务 */
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;
/* 代码中判断返回的 relation_ship,
如果是 2,事务结束,执行 commit
如果是 3,则执行下面这两个语句:
*/
insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
这个设计,让"like"表里的数据保证user_id < liker_id,这样无论A关注B,B关注A,在操作“like”表时,如果反向关系已存在,就会操作同一行出现行锁冲突。
然后,insert … on duplicate 语句,确保事务强行占住行锁,之后select 判断 relation_ship 这个逻辑时就确保了是在行锁保护下的读操作。
操作符 “|” 按位或,和最后一句 insert 语句里的 ignore,保证重复调用时的幂等性。