1、日志模块(redo log)
MySQL采用WAL技术( Write-Ahead Logging),它的关键点就是先写日志,再写磁盘。
具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面,而这个更新往往是在系统比较空闲的时候做。
这里我给出这个 update 语句的执行流程图,图中浅色框表示是在 InnoDB 内部执行的,
深色框表示是在执行器中执行的。下图将 redo log 的写入拆成了两个步骤:prepare 和 commit,这就是"两阶段提交"。redo log 和 binlog和采用 “两阶段提交”。
两阶段提交:
1=prepare阶段 2=写binlog 3 =redo log commit
- 当在2之前崩溃时
重启恢复:后发现没有commit,回滚。备份恢复:没有binlog 。 - 当在3之前崩溃
重启恢复:虽没有commit,但满足prepare和binlog完整,所以重启后会自动commit。备份:有binlog.
问题总结,大家复习的时候可以先尝试回答这些问题检查自己的掌握程度:
- redo log的概念是什么? 为什么会存在.
- 什么是WAL(write-ahead log)机制, 好处是什么.
- redo log 为什么可以保证crash safe机制.
- binlog的概念是什么, 起到什么作用, 可以做crash safe吗?
- binlog和redolog的不同点有哪些?
- 物理一致性和逻辑一直性各应该怎么理解?
- 执行器和innoDB在执行update语句时候的流程是什么样的?
- 如果数据库误操作, 如何执行数据恢复?
- 什么是两阶段提交, 为什么需要两阶段提交, 两阶段提交怎么保证数据库中两份日志间的逻辑一致性(什么叫逻辑一致性)?
- 如果不是两阶段提交, 先写redo log和先写bin log两种情况各会遇到什么问题?
sql执行流程:
1.首先客户端通过tcp/ip发送一条sql语句到server层的SQL interface
2.SQL interface接到该请求后,先对该条语句进行解析,验证权限是否匹配
3.验证通过以后,分析器会对该语句分析,是否语法有错误等
4.接下来是优化器器生成相应的执行计划,选择最优的执行计划
5.之后会是执行器根据执行计划执行这条语句。在这一步会去open table,如果该table上有MDL,则等待。
如果没有,则加在该表上加短暂的MDL(S)
(如果opend_table太大,表明open_table_cache太小。需要不停的去打开frm文件)
6.进入到引擎层,首先会去innodb_buffer_pool里的data dictionary(元数据信息)得到表信息
7.通过元数据信息,去lock info里查出是否会有相关的锁信息,并把这条update语句需要的
锁信息写入到lock info里(锁这里还有待补充)
8.然后涉及到的老数据通过快照的方式存储到innodb_buffer_pool里的undo page里,并且记录undo log修改的redo
(如果data page里有就直接载入到undo page里,如果没有,则需要去磁盘里取出相应page的数据,载入到undo page里)
9.在innodb_buffer_pool的data page做update操作。并把操作的物理数据页修改记录到redo log buffer里
由于update这个事务会涉及到多个页面的修改,所以redo log buffer里会记录多条页面的修改信息。
因为group commit的原因,这次事务所产生的redo log buffer可能会跟随其它事务一同flush并且sync到磁盘上
10.同时修改的信息,会按照event的格式,记录到binlog_cache中。(这里注意binlog_cache_size是transaction级别的,不是session级别的参数,
一旦commit之后,dump线程会从binlog_cache里把event主动发送给slave的I/O线程)
11.之后把这条sql,需要在二级索引上做的修改,写入到change buffer page,等到下次有其他sql需要读取该二级索引时,再去与二级索引做merge
(随机I/O变为顺序I/O,但是由于现在的磁盘都是SSD,所以对于寻址来说,随机I/O和顺序I/O差距不大)
12.此时update语句已经完成,需要commit或者rollback。这里讨论commit的情况,并且双1
13.commit操作,由于存储引擎层与server层之间采用的是内部XA(保证两个事务的一致性,这里主要保证redo log和binlog的原子性),
所以提交分为prepare阶段与commit阶段
14.prepare阶段,将事务的xid写入,将binlog_cache里的进行flush以及sync操作(大事务的话这步非常耗时)
15.commit阶段,由于之前该事务产生的redo log已经sync到磁盘了。所以这步只是在redo log里标记commit
16.当binlog和redo log都已经落盘以后,如果触发了刷新脏页的操作,先把该脏页复制到doublewrite buffer里,把doublewrite buffer里的刷新到共享表空间,然后才是通过page cleaner线程把脏页写入到磁盘中。
2、事务的四种隔离级别
在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别。我们的数据库锁,也是为了构建这些隔离级别存在的。
隔离级别 | 脏读(Dirty Read) | 不可重复读(NonRepeatable Read) | 幻读(Phantom Read) |
---|---|---|---|
未提交读(Read uncommitted) | 可能 | 可能 | 可能 |
提交读(Read Committed) | 不可能 | 可能 | 可能 |
可重复读(Repeated Read) | 不可能 | 不可能 | 可能 |
串行读(Serializable) | 不可能 | 不可能 | 不可能 |
未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据
提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读
串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞
Read Uncommitted这种级别,数据库一般都不会用,而且任何操作都不会加锁,这里就不讨论了。
3、MVCC(多版本控制)
多版本控制(Multiversion Concurrency Control): 指的是一种提高并发的技术。最早的数据库系统,只有读读之间可以并发,读写,写读,写写都要阻塞。引入多版本之后,只有写写之间相互阻塞,其他三种操作都可以并行,这样大幅度提高了InnoDB的并发度。在内部实现中,InnoDB通过undo log保存每条数据的多个版本,并且能够找回数据历史版本提供给用户读,每个事务读到的数据版本可能是不一样的。在同一个事务中,用户只能看到该事务创建快照之前已经提交的修改和该事务本身做的修改。
MVCC在 Read Committed 和 Repeatable Read两个隔离级别下工作。
MySQL的InnoDB存储引擎默认事务隔离级别是RR(可重复读),是通过 "行级锁+MVCC"一起实现的,正常读的时候不加锁,写的时候加锁。而 MVCC 的实现依赖:隐藏字段、Read View、Undo log。
2.1、隐藏字段
InnoDB存储引擎在每行数据的后面添加了三个隐藏字段:
- DB_TRX_ID(6字节):表示最近一次对本记录行作修改(insert |update)的事务ID。至于delete操作,InnoDB认为是一个update操作,不过会更新一个另外的删除位,将行表示为deleted。并非真正删除。
- DB_ROLL_PTR(7字节):回滚指针,指向当前记录行的undo log信息
- DB_ROW_ID(6字节):随着新行插入而单调递增的行ID。理解:当表没有主键或唯一非空索引时,innodb就会使用这个行ID自动产生聚簇索引。如果表有主键或唯一非空索引,聚簇索引就不会包含这个行ID了。这个DB_ROW_ID跟MVCC关系不大。
2.2、Read View 结构(重点)
Read View 叫视图读或则快照读。Read View主要是用来做可见性判断的, 里面保存了“对本事务不可见的其他活跃事务”。Read View中包含几个变量:
- low_limit_id 目前出现过的最大的事务ID+1,即下一个将被分配的事务ID。
- up_limit_id 活跃事务列表trx_ids中最小的事务ID,如果trx_ids为空,则up_limit_id = low_limit_id。
- trx_ids Read View创建时其他未提交的活跃事务ID列表。意思就是创建Read View时,将当前未提交事务ID记录下来,trx_ids是个从大到小排序的链表结构。后续即使它们修改了记录行的值,对于当前事务也是不可见的。
- creator_trx_id 当前创建事务的ID,是一个递增的编号。
2.3、Undo log
undo log主要分为两种:
- insert undo log
代表事务在insert新记录时产生的undo log, 只在事务回滚时需要,并且在事务提交后可以被立即丢弃。 - update undo log
事务在进行update或delete时产生的undo log; 不仅在事务回滚时需要,在快照读时也需要;所以不能随便删除,只有在快速读或事务回滚不涉及该日志时,对应的日志才会被purge线程统一清除。
2.4、记录行修改的具体流程
假设有一条记录行如下,字段有Name和Honor,值分别为"curry"和"mvp",最新修改这条记录的事务ID为1。
(1)现在事务A(事务ID为2)对该记录的Honor做出了修改,将Honor改为"fmvp":
- 事务A先对该行加排它锁
- 然后把该行数据拷贝到undo log中,作为旧版本
- 拷贝完毕后,修改该行的Honor为"fmvp",并且修改DB_TRX_ID为2(事务A的ID), 回滚指针指向拷贝到undo log的旧版本。(然后还会将修改后的最新数据写入redo log)
- 事务提交,释放排他锁。
从上面可以看出,不同事务或者相同事务的对同一记录行的修改,会使该记录行的undo log成为一条链表,undo log的链首就是最新的旧记录,链尾就是最早的旧记录。
2.5、可见性比较算法
在innodb中,创建一个新事务后,执行第一个select语句的时候,innodb会创建一个快照(read view),快照中会保存系统当前不应该被本事务看到的其他活跃事务id列表(即trx_ids)。当用户在这个事务中要读取某个记录行的时候,innodb会将该记录行的DB_TRX_ID与该Read View中的一些变量进行比较,判断是否满足可见性条件。
假设当前事务要读取某一个记录行,拿该记录DB_TRX_ID字段记录的事务ID(trx_id )去跟Read View中比较,比较算法如下:
-
如果 trx_id < up_limit_id(trx_ids中最早的事务ID), 那么表明“最新修改该行的事务”在“当前事务”创建快照之前就提交了,所以该记录行的值对当前事务是可见的。跳到步骤5。
-
如果 trx_id >= low_limit_id(生成这个Read Vew时系统出现过的最大的事务ID+1), 那么表明“最新修改该行的事务”在“当前事务”创建快照之后才修改该行,所以该记录行的值对当前事务不可见。跳到步骤4。
-
如果 up_limit_id <= trx_id < low_limit_id, 表明“最新修改该行的事务”在“当前事务”创建快照的时候可能处于“活动状态”或者“已提交状态”;所以就要对活跃事务列表trx_ids进行查找(源码中是用的二分查找,因为是有序的):
(1) 如果在活跃事务列表trx_ids中能找到 id 为 trx_id 的事务,表明①在“当前事务”创建快照前,“该记录行的值”被“id为trx_id的事务”修改了,但没有提交;或者②在“当前事务”创建快照后,“该记录行的值”被“id为trx_id的事务”修改了(不管有无提交);这些情况下,这个记录行的值对当前事务都是不可见的,跳到步骤4;(2)在活跃事务列表中找不到,则表明“id为trx_id的事务”在修改“该记录行的值”后,在“当前事务”创建快照前就已经提交了,所以记录行对当前事务可见,跳到步骤5。
-
在该记录行的 DB_ROLL_PTR 指针所指向的undo log回滚段中,取出最新的的旧事务号DB_TRX_ID, 将它赋给trx_id,然后跳到步骤1重新开始判断。
-
将该行的值返回(可见)。
2.6、当前读和快照读
- 快照读(snapshot read) 简单的select操作(不包括 select … lock in share mode, select … for update)
- 当前读(current read)
select … lock in share mode
select … for update
insert
update
delete
只靠 MVCC 实现RR隔离级别,可以保证可重复读,还能防止部分幻读,但并不是完全防止。在RR级别下,快照读是通过MVVC(多版本控制)和undo log来实现的,当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。
2.7、RR和RC的Read View产生区别
- Repeatable Read级别, 只有事务在begin之后,执行第一条select(读操作)时, 才会创建一个快照(read view),将当前系统中活跃的其他事务记录起来;并且事务之后都是使用的这个快照,不会重新创建,直到事务结束。
- Read Committed级别, 事务在begin之后,执行每条select(读操作)语句时,快照会被重置,即会重新创建一个快照(read view)。
4、幻读
前提条件:InnoDB引擎,可重复读隔离级别,使用当前读时。
表现:一个事务(同一个read view)在前后两次查询同一范围的时候,后一次查询看到了前一次查询没有看到的行。两点需要说明:
1、在可重复读隔离级别下,普通查询是快照读,是不会看到别的事务插入的数据的,幻读只在当前读下才会出现。
2、幻读专指新插入的行,读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。
3.1、幻读的影响
- 会造成一个事务中先产生的锁,无法锁住后加入的满足条件的行。
- 产生数据一致性问题,在一个事务中,先对符合条件的目标行做变更,而在事务提交前有新的符合目标条件的行加入。这样通过binlog恢复的数据是会将所有符合条件的目标行都进行变更的。
3.2、幻读产生的原因
当一个读事务按条件查询时,行锁仅仅加在了符合条件的记录行上,此时其他写事务,虽然不能操作加有行锁的记录行,但是完全可以操作其他数据行,甚至是新增一条新纪录行。所以如果在读事务的过程中,写事务新增了一条符合条件的记录,那么读事务再次查询时,就会查询到上次查询时没有查到的新纪录;就出现了幻读。
3.3、如何解决幻读
- 将两行记录间的空隙加上锁,阻止新记录的插入;这个锁称为间隙锁。
- 间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的,是往这个间隙中插入一个记录这个操作。
5、间隙锁
间隙锁在可重复读隔离级别下才有效。加锁规则里面,包含了两个“原则”、两个“优化”和一个“bug”
- 原则 1:加锁的基本单位是 next-key lock。希望你还记得,next-key lock 是前开后闭区间。类似(5,10]
- 原则 2:查找过程中访问到的对象才会加锁。
- 优化 1:索引上的等值查询,给唯一索引加锁的时候,next-key lock 退化为行锁。
- 优化 2:索引上的等值查询,向右遍历时且最后一个值不满足等值条件的时候,next key lock 退化为间隙锁。
- 一个 bug:唯一索引上的范围查询会访问到不满足条件的第一个值为止。
我还是以上篇文章的表 t 为例,和你解释一下这些规则。表 t 的建表语句和初始化语句如
下。
CREATE TABLEt
(
id
int(11) NOT NULL,
c
int(11) DEFAULT NULL,
d
int(11) DEFAULT NULL,
PRIMARY KEY (id
),
KEYc
(c
)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);
案例一:等值查询间隙锁
由于表 t 中没有 id=7 的记录,所以用我们上面提到的加锁规则判断一下的话:
- 根据原则 1,加锁单位是 next-key lock,session A 加锁范围就是 (5,10];
- 同时根据优化 2,这是一个等值查询 (id=7),而 id=10 不满足查询条件,next-key lock 退化成间隙锁,因此最终加锁的范围是 (5,10)。
所以,session B 要往这个间隙里面插入 id=8 的记录会被锁住,但是 session C 修改id=10 这行是可以的。
案例二:非唯一索引等值锁
这里的sessionA要给c=5这行增加行锁。
-
根据原则1,加锁单位是 next-key lock,session A 加锁范围就是 (0,5];
-
由于c是普通索引,因此仅访问 c=5 这一条记录是不能马上停下来的,需要向右遍历,查到 c=10 才放弃。根据原则 2,访问到的都要加锁,因此要给 (5,10] 加 next-key lock。
-
根据优化2,索引的等值查询,向右遍历,最后一个值不满足 c=5 这个等值条件,因此退化成间隙锁 (5,10)
-
根据原则 2 ,只有访问到的对象才会加锁,这个查询使用覆盖索引,并不需要访问主键索引,所以主键索引上没有加任何锁,这就是为什么 session B 的 update 语句可以执行完成。
但 session C 要插入一个 (7,7,7) 的记录,就会被 session A 的间隙锁 (5,10) 锁住。
需要注意,在这个例子中,lock in share mode 只锁覆盖索引,但是如果是 for update就不一样了。 执行 for update 时,系统会认为你接下来要更新数据,因此会顺便给主键索引上满足条件的行加上行锁。
6、SQL优化
案例一:条件字段函数操作
select count(*) from tradelog where month(t_modified)=7;
由于t_modified 字段上有索引,如果条件用 where t_modified='2018-7-1’的话,引擎就会快速定位到 t_modified='2018-7-1’需要的结果。B+ 树提供的这个快速定位能力,来源于同一层兄弟节点的有序性。
但是,如果计算 month() 函数的话,你会看到传入 7 的时候,在树的第一层就不知道该怎么办了。
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能,导致了全索引扫描。
比如,对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句,这个加 1 操作并不会改变有序性,但是 MySQL 优化器还是不能用 id 索引快速定位到9999 这一行。所以,需要你在写 SQL 语句的时候,手动改写成 where id = 10000 -1 才可以。
案例二:隐式类型转换
select * from tradelog where tradeid=110717;
交易编号 tradeid 这个字段上,本来就有索引,但是 explain 的结果却显示,这条语句需要走全表扫描。你可能也发现了,tradeid 的字段类型是 varchar(32),而输入的参数却是整型,所以需要做类型转换
在 MySQL 中,字符串和数字做比较的话,是将字符串转换成数字。
所以上面的SQL对于优化器来说相当于:
select * from tradelog where CAST(tradid AS signed int) = 110717;
也就是说,这条语句触发了我们上面说到的规则:对索引字段做函数操作,优化器会放弃走树搜索功能。
案例三:隐式字符编码转换
不同字符集表之间的关联(utf8mb4,utf8),连接过程中要求在被驱动表的索引字段上加函数操作,是直接导致对被驱动表做全表扫描。如:
select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4)
所以对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
案例四:身份证ID做主键的优缺点
优点:可以直接从主键索引中定位数据,查询效率高,不需要回表。比如只有一个索引的并且必须是唯一索引的场景可以直接用身份证ID做主键。
缺点:
1、如果用身份证号做主键,每个二级索引的叶子节点占用约20个字节,而如果用整型做主键,则只要4个字节,如果是长整型(bigint)则是8个字节。
主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小。
2、每次插入一条新记录,于身份证ID不是连续的,需要涉及到挪动其他记录,会触发叶子节点的分裂。
自增主键每次插入都是追加操作,不涉及到挪动其他记录,不会触发叶子节点的分裂。
从性能和存储空间方面考量,自增主键往往是更合理的选择
根据 B+ 树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。
InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在InnoDB 中,每个数据页的大小默认是 16KB。因为引擎是按页读写的,所以说,当找到 k=5 的记录的时候,它所在的数据页就都在内存里了。那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。
7、组合索引
比较简单的是单列索引(b+tree)。遇到多条件查询时,不可避免会使用到多列索引。联合索引又叫复合索引。
b+tree结构如下:
每一个磁盘块在mysql中是一个页,页大小是固定的,mysql innodb的默认的页大小是16k,每个索引会分配在页上的数量是由字段的大小决定。当字段值的长度越长,每一页上的数量就会越少,因此在一定数据量的情况下,索引的深度会越深,影响索引的查找效率。
基于组合索引遵循最左侧原则,从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key idx_key (a,b,c). 可以支持a a,b a,b,c 3种组合进行查找,但不支持 b,c进行查找。当使用最左侧字段时,索引就十分有效。
创建表test如下:
create table test(
a int,
b int,
c int,
KEY a(a,b,c));
1、多列索引在and查询中应用
select * from test where a=? and b=? and c=?;
//查询效率最高,索引全覆盖。
select * from test where a=? and b=?;
//索引覆盖a和b。
select * from test where b=? and a=?;
//经过mysql的查询分析器的优化,索引覆盖a和b。
select * from test where a=?;
//索引覆盖a。
select * from test where b=? and c=?;
//没有a列,不走索引,索引失效。
select * from test where c=?;
//没有a列,不走索引,索引失效。
2、多列索引在范围查询中应用
select * from test where a=? and b between ? and ? and c=?;
//索引覆盖a和b,因b列是范围查询,因此c列不能走索引。
select * from test where a between ? and ? and b=?;
//a列走索引,因a列是范围查询,因此b列是无法使用索引。
select * from test where a between ? and ? and b between ? and ? and c=?;
//a列走索引,因a列是范围查询,b列是范围查询也不能使用索引。
3、多列索引在排序中应用
select * from test where a=? and b=? order by c;
//a、b、c三列全覆盖索引,查询效率最高。
select * from test where a=? and b between ? and ? order by c;
//a、b列使用索引查找,因b列是范围查询,因此c列不能使用索引,会出现file sort。