MySQL简明介绍
数据存储
在innodb中,表都是按照主键的顺序存放的,被称为索引组织表,每张表都有一个主键,如果没有显式得指明:
- 表中若有非空唯一索引,则它为主键
- 否则,引擎自动创建6个字节大小的指针作为主键
所有的数据都被存放在逻辑的表空间(tablespace),表又是由段(segment),区(extent),页(page)组成
段segment
常见的分为数据段,索引段,回滚段等,在innodb中数据即索引,数据段为B+树的叶子节点,索引段则是B+树的非叶子节点
区extent
大小为1MB由多个连续的页组成,默认情况下页的大小为16KB,一个区内有64个页组成
页Page
是innodb中磁盘管理的最小单位,每一次都会读取整个页到内存中进行操作,充分利用磁盘的特性,而一个页中的数据段则存放着许多行记录(row)
存储记录
compact模式
- 首部是一个变长字段长度列表,记录了每个列的长度大小,若小于255用一个字节表示,否则最多用2个字节表示,并且按照列的顺序逆序放置
- NULL标志位标识本行中的某个列是不是有NULL的数据,用一个字节表示
- 记录的头部信息固定用5个字节表示
- NULL部分是不占用任何空间的,除了NULL的标志位
- CHAR类型部分使用0x20补全
redundant模式
- 首部使用的是字段长度的偏移列表,同样也是逆序放置的
- NULL数据也会占用空间
- 总体来说,Compact能够比Redundant格式能减少20%的存储空间
行溢出数据
对于某些占用空间较大的数据段,会保存数据的前缀,之后会有一个偏移量指向了行溢出页
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LjtD3tgj-1585292810858)(http://blob.tommenx.top/2020-03-26-070902.png)]
数据页结构
页是InnoDB存储引擎管理数据库的最小磁盘单元,B-Tree 节点就是实际存放表中数据的页面,一个InnoDB页有以下7个部分组成:
File Header/File Trailer关心的是页的头部信息,包含叶子节点之间的指针,Page Header和Page Directory关心的是页的状态信息,中间的部分则是用户记录和空闲的空间了
其中Infimum 和 Supremum这两个虚拟记录用来限定边界,Infimum记录是比该页中任何主键值都要小的值,Supremum指比任何可能的值还要大的值
User Record 是整个页面中真正用于存放行记录的部分,Free Space是指空闲的空间也是用链表存储的,当一条记录被删除后会加入到空闲链表中。为了保证插入和删除的效率,整个页面不会按照主键的值进行排序,因此,行记录在物理上不是顺序存放的。
B+树在查找对应的行记录的时候,只能获取记录也在的页,将整个页加载到内存中,在通过Page Directory中存储的稀疏索引进行查找。其中存放了了记录的相对位置,使用Slot槽来指示,一个槽中可能有多条记录,槽是顺序存放的,在查找的时候可以使用二分法。
索引
索引是存储引擎快速定位记录的工具,是优化查询的有效手段,在InnoDB中常见的索引有B+树索引和自适应的哈希索引
聚集索引
B+树索引并不能找到给定的键对应的值,只能找到数据所在的页
辅助索引
聚集索引(clustered index)中存放的是一条行记录中的全部信息,辅助索引(secondary index)只包含了索引列和一个用于查找对应记录的书签,即相应数据的聚集索引键。
主从复制
其中有一个Master节点,其他的多态服务器作为Slave。Master中的数据自动的复制到Slave中,他们之间通过Binlog二进制日志文件通信,来提供高性能高可用的解决方案。
主从复制类型
基于语句的复制:在主服务器上执行SQL语句,在从服务器上执行相同的语句,默认类型
基于行复制:将数据库中的改变的内容复制过去,操作涉及行数多,对每一列都进行更改,网络和空间开销大
混合类型复制:默认采取基于语句的机制,无法精确复制的时候才去行复制模式
主从复制原理
- 主服务器Master将数据更改记录保存到二进制日志文件binlog中
- 从服务器Slave的I/O线程将Master上的日志写入到本机的中继日志relay log中
- Slave上的SQL线程读取中继日志,将更改应用到自己的数据库上,最终达成数据的一致性
复制不是完全实时得进行同步,而是异步实时,考虑到中间存在时延,因此如果往主库中的数据更改后,立刻从从库中读取并不一定能获得最新的数据,即存在读写延迟。
MHA高可用
适用于一主多从的架构体系,在故障切换过程中,可从宕机的主库上保存二进制日志,最大程度的保证数据不丢失。但是需要 MHA 架构内所有的节点都必须可以 ssh互通。分为两部分组成,包括管理节点Manager和数据节点Node,通常Manager单独部署,Node部署在每个DB和Manager上。
Manager 会定时探测集群中的主库节点,一般为每秒钟探测一次,当主库出现故障后,拉取主库和最新从库的差异日志并应用到该从库上,将该从库提升为新的主库,然后把其他所有的从库重新指向新的主库,由于主库采取 VIP (虚拟IP)方式对外提供服务,整个故障转移的过程对应用程序是完全透明的。
Binlog二进制日志
二进制日志(binary log)是mysql server层维护的,与底层的存储引擎无关。它记录了MySQL执行更改的所有操作,但是不包括SELECT
、SHOW
这类对数据本身没有修改的操作,但是即使某些操作对数据本身没有修改,例如UPDATE 0行数据。
日志结构
一个完整的binlog文件是由一个format description event
开头,用于描述文件的版本和格式;一个rotate event
结尾,用于说明下一个binlog文件;中间由多个其他event组合而成
对于一个event
由两部分组成,包括header
和data
,具体格式如下:
+=====================================+
| event | timestamp 0 : 4 |
| header +----------------------------+
| | type_code 4 : 1 | = 标识了event类型:FORMAT_DESCRIPTION_EVENT
| +----------------------------+
| | server_id 5 : 4 |
| +----------------------------+
| | event_length 9 : 4 |
| +----------------------------+
| | next_position 13 : 4 |
| +----------------------------+
| | flags 17 : 2 |
| +----------------------------+
| | extra_headers 19 : x-19 |
+=====================================+
| event | fixed part x : y |
| data +----------------------------+
| | variable part |
+=====================================+
STATEMENT类型
例如,在数据库的某个表插入一行数据,执行以下语句时,会产生3个event
,会产生3个event
,包括2个query event
和1个xid event
,BEGIN和insert为Query类型,Commit为Xid类型
CREATE TABLEtt(ivarchar(100) DEFAULT NULL) ENGINE=innoDB //表结构
insert into tt values('abc')
当表中有自增主键的时候会用到intvar event
事件,Fixed data部分为空,而Variable data部分为9个字节,第一字节标识自增事件,后8个字节标识自增的ID,因此,插入的语句会带有Intvar event
create table tinc (i int auto_increment primary key, c varchar(10)) engine=innodb; //表结构
INSERT INTO tinc(c) values('abc');
ROW类型
执行该语句的时候会产生一个query event,一个table_map event、一个write_rows event以及一个xid event
CREATE TABLE `trow` (
`i` int(11) NOT NULL,
`c` varchar(10) DEFAULT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1`
INSERT INTO trow VALUES(1, NULL), (2, 'a')
对于更新操作会有update_row_event,删除操作会有delete_row_event
详细内容可以参考MyFlash——美团点评的开源MySQL闪回工具
锁
在InnoDB存储引擎中,使用的是悲观锁(线程在获取资源前加锁,直到完成了操作释放了锁之后,其他线程才能重新操作资源),按照锁的粒度划分可以分为行锁和表锁
锁的种类
InnoDB 实现了标准的行级锁,也就是共享锁(Shared Lock)和互斥锁(Exclusive Lock);共享锁和互斥锁的作用其实非常好理解:
- 共享锁(读锁):允许事务对一条行数据进行读取;
- 互斥锁(写锁):允许事务对一条行数据进行删除或更新;
X | S | |
---|---|---|
X | 不兼容 | 不兼容 |
S | 不兼容 | 不兼容 |
因此,可以在数据库中实现并行读,串行写,保证线程安全
锁的粒度
为了支持多粒度锁定,InnoDB 存储引擎引入了意向锁(Intention Lock),意向锁就是一种表级锁,意向锁也分为两类:
- 意向共享锁:事务想要在获得表中某些记录的共享锁,需要在表上先加意向共享锁;
- 意向互斥锁:事务想要在获得表中某些记录的互斥锁,需要在表上先加意向互斥锁;
意向锁将锁定的对象分为了多个层次,若将上锁对象分为多个层次,若对页上的记录r上X锁,那么需要对数据库A、表、页上意向锁IX,最后对记录r上X锁。若其中有一部分导致等待,需要该操作等待粗粒度的锁完成
IS | IX | S | X | |
---|---|---|---|---|
IS | 兼容 | 兼容 | 兼容 | 不兼容 |
IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
S | 兼容 | 不兼容 | 兼容 | 不兼容 |
X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
例子
无意向锁
一个请求使用行锁对某一行进行修改时,另一个请求要求对全表进行修改,则需要扫描所有行判定是否上锁,效率很低
有意向锁
当某个线程使用行锁对表中的某一行记录修改时,需要为表和记录所在的页添加IX锁,再为记录添加X锁,当有人尝试全表修改的时,只需要等待IX锁释放即可
行锁的算法
InnoDB存储引擎中有3种锁的算法,分别是:
- Record Lock:单个行记录上的锁
- Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
- Next-Key Lock:锁定一个范围,并且包含记录本身
Record Lock
总是会去锁住索引记录,如果InnoDB在表创建的时候没有设置任何一个索引,就会使用隐式的主键进行锁定,假设有一张user
表,具体的规则如下:
CREATE TABLE users(
id INT NOT NULL AUTO_INCREMENT,
last_name VARCHAR(255) NOT NULL,
first_name VARCHAR(255),
age INT,
PRIMARY KEY(id),
KEY(last_name),
KEY(age)
);
使用id
或last_name
作为过滤条件的时候就可以通过B+树查找对应记录并添加锁,但是使用first_name
过滤的时候,就会锁定整个表
Gap Lock
间隙锁是对索引记录中的一段连续区域的锁,阻止其他事务向表中这一连续区间内添加新的记录
Next-Key Lock
Next-Key 锁锁定的是当前值和前面的范围,目的是解决幻读问题
Next-Key 锁相比前两者就稍微有一些复杂,它是记录锁和记录前的间隙锁的结合,在users
表中有如下记录:
+------|-------------|--------------|-------+
| id | last_name | first_name | age |
|------|-------------|--------------|-------|
| 4 | stark | tony | 21 |
| 1 | tom | hiddleston | 30 |
| 3 | morgan | freeman | 40 |
| 5 | jeff | dean | 50 |
| 2 | donald | trump | 80 |
+------|-------------|--------------|-------+
当我们更新一条记录,比如 SELECT * FROM users WHERE age = 30 FOR UPDATE;
,InnoDB 不仅会在范围 (21, 30]
上加 Next-Key 锁,还会在这条记录后面的范围 (30, 40]
加间隙锁,所以插入 (21, 40]
范围内的记录都会被锁定。
事务及隔离级别
事务的隔离级别
ISO 和 ANIS SQL 标准制定了四种事务隔离级别:
RAED UNCOMMITED
:读未提交,使用查询语句不会加锁,可能会读到未提交的行(Dirty Read);READ COMMITED
:只对记录加记录锁,而不会在记录之间加间隙锁,所以允许新的记录插入到被锁定记录的附近,所以再多次使用查询语句时,可能得到不同的结果(Non-Repeatable Read);REPEATABLE READ
:多次读取同一范围的数据会返回第一次查询的快照,不会返回不同的数据行,但是可能发生幻读(Phantom Read);SERIALIZABLE
:InnoDB 隐式地将全部的查询语句加上共享锁,解决了幻读的问题;
Dirty Read | Non-Repeatable Read | Phantom Read | |
---|---|---|---|
Read Uncommited | N | N | N |
Read Commited | Y | N | N |
Repeatable Read | Y | Y | N |
Serializable | Y | Y | Y |
脏读 Dirty Read
在一个事务中,读取了其他事务未提交的数据
在s1两次查询某个记录间隙中,s2可以对该数据修改,但未提交,造成s1两次查询的结果不同
不可重复读 Non-Repeatable Read
在一个事务中,同一行记录被访问了两次却得到了不同的结果
同上,但是s2对数据修改的事务提交后,造成s1两次查询结果不一致
幻读 Phantom Read
在一个事务中,同一个范围内记录被读取时,其他事务向这个范围添加了新的记录
s1在进行两次全表查询的间隙,s2向表中插入1条数据并提交,由于REPEATABLE READ
再次查询全表时,获取得仍是空集,再次插入该条数据时,出错。
事务持久性的实现
Redo log称为重做日志,用来保证事务的原子性和持久性,undo log用来保证事务的一致性,都可以视为恢复机操作
- redo是物理日志,记录的是页的物理修改操作,恢复事务修改的页操作
- undo是逻辑日志,根据行记录进行记录,回滚记录到某个特定的版本
当事务提交的时候,必须将事务的所有日志写入到重做日志文件进行持久化。重做日志在InnoDB中有两个部分组成:
- redo log用来保证事务的持久性,基本上是顺序写
- undo log用来帮助事务回滚和MVCC,需要进行随机读写
每次将重做日志写入到重做日志文件后,存储引擎都需要调用一次fsync操作将日志缓冲写入到到磁盘
总结
本文简单介绍了mysql中的复制、存储、事务和锁的机制,数据库非常复杂且细节较多,需要多花时间深入了解
参考
- 深入浅出MySQL和InnoDB
- MySQL技术内幕(InnoDB存储引擎)