MySQL简明介绍_一个完整的binlog文件是由一个format description event开头,一个rota-CSDN博客

本文链接：https://blog.csdn.net/tommenx/article/details/105141962

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 隐式地将全部的查询语句加上共享锁，解决了幻读的问题；