弱小和无知并不是生存的障碍，傲慢才是。

---- ---- 面试者

总结

MySQL不止简单的CRUD，深入MySQL底层原理了解数据库的实现机制，一通百通。本文涉及MySQL逻辑架构、文件存储格式、SQL查询/更新流程、索引、事务实现、主从同步原理等。

MySQL逻辑架构

客户端连接器

担任MySQL服务客户端连接器的角色，负责与MySQL服务建立连接，基于API进行数据操作。

MySQL服务端

数据库服务端是整个数据库服务器的核心，负责接收客户端的请求，对发送的SQL进行解析、优化制定执行计划，然后调用API到存储引擎进行数据读取或写入操作。

1. 系统管理和控制工具

提供数据库备份恢复、安全管理、复制集群、元数据管理等能力。

2. 连接池

负责接收管理客户端建立的连接，每一个客户端请求都会由线程池中的一个线程来管理，并对连接请求进行用户认证、权限认证等。

3. SQL接口

负责接收处理客户端发送的DML、DDL、存储过程、视图、触发器等SQL相关操作。

4. SQL解析器

对用户请求的SQL语句进行语法词法解析，生成语法树，如果语法错误则返回相关错误信息。

5. 查询优化器

对查询语句进行优化，系统会根据SQL语句涉及的表和索引，分析选择合适的执行计划，然后与存储引擎进行交互进行数据操作。

6. 缓存

MySQL针对一次SQL的查询，会进行缓存，如果是同一个SQL查询会直接从缓存读取返回。如果SQL中涉及的任何一个表数据发生了变更，则缓存失效。

存储引擎

1. 数据引擎

由于MySQL支持多个引擎（Innodb、MyISAM），且是可插拔，通过API标准屏蔽引擎之间的差异。在接收到服务端的数据操作请求之后，负责具体的文件操作。

2. 存储文件

存储的文件主要包括两部分，一个是日志文件，一个是数据文件。

日志文件：

binlog二进制文件：记录MySQL数据库的所有写操作；

redo-log重放日志文件：记录事务操作日志，在MySQL宕机之后，对未落盘的数据会重放。

undo-log回滚日志文件：对事务提交前的数据进行备份，用于回滚事务。

error-log日志文件：错误日志，记录MySQL启动运行停止时的错误信息。

general-log日志文件：常规日志，记录MySQL收到的每一个查询或是SQL命令。

slow-log日志文件：慢查询日志，主要记录执行时间较长的SQL;

relay-log日志文件：中继日志，主要用于主从复制做数据拷贝。

数据文件：

db.opt文件：主要记录当前数据库使用的字符集和验证规则等信息。

.frm文件：存储表结构的元数据信息文件，每张表都会有一个这样的文件。

.MYD文件：用于存储表中所有数据的文件（MyISAM引擎独有的）。

.MYI文件：用于存储表中索引信息的文件（MyISAM引擎独有的）。

.ibd文件：用于存储表数据和索引信息的文件（InnoDB引擎独有的）。

.ibdata文件：用于存储共享表空间的数据和索引的文件（InnoDB引擎独有）。

.ibdata1文件：这个主要是用于存储MySQL系统（自带）表数据及结构的文件。

.ib_logfile0/.ib_logfile1文件：用于故障数据恢复时的日志文件。

.cnf/.ini：MySQL的配置文件，Windows下是.ini，其他系统大多为.cnf。

数据文件结构

1. 表空间（Tablespace）

在MySQL5.7版本，分为系统表空间（The System Tablespace），是InnoDB数据字典（表、索引、字段元数据）、doublewrite缓冲区、change缓冲区和undo日志的存储区域；表独立表空间（File-Per-Table Tablespace）即一个表的数据和索引独立一个表空间文件进行存储；通用表空间（General Tablespace）为用户自定义表空间，可共享；Undo表空间（Undo Tablespace）存储 Undo Log（MySQL 8.0 后独立）和临时表空间（Temporary Tablespace）存储临时表数据。

其中系统表空间和通用表空间都是使用一个文件进行多个表数据存储，这样减少文件数量，但是会导致文件可能过大，以及表删除之后，文件不会自动缩小；表独立文件空间一个表的数据和索引独立一个文件存储，这样方便表数据的迁移备份以及空间回收，但是可能因为小文件过多影响文件系统性能。

2. 段（segment）

常见的Segment有数据段、索引段、回滚段等，由于MySQL数据是B+树，数据段为B+树的叶子节点(Leaf node segment)、索引段为B+树的非叶子节点(Non-leaf node segment)。

3. 区（Extend）

区是构成段的基本元素，在物理上是一段连续的空间，每个区固定位1MB，一个段所管理的空间大小是无线的，区是由页组成，为保证区中页的连续性，通常Innodb会一次从磁盘中申请4-5个区。在默认页的大小为16KB的情况下，一个区则由64个连续的页组成。

4. 页（Page）

页是构成区的基本单位，是InnoDB磁盘管理的最小单位。在逻辑上（页面号都是从小到大连续的）及物理上都是连续的。在向表中插入数据时，如果一个页面已经被写完，系统会从当前区中分配一个新的空闲页面处理使用，如果当前区中的64个页面都被分配完，系统会从当前页面所在段中分配一个新的区，然后再从这个区中分配一个新的页面来使用。

5. 行（Row）

InnoDB按照行进行存放数据，每个页存放的数据有硬性规定，最多存放16KB（由于页内还需要存储页头、行头等信息，可能实际可用空间不足16KB），当数据大于16KB的时候会发生行溢出，Innodb默认的行格式为Dynamic，当行业溢出之后，数据会转存到溢出页，而当前行数据只存储溢出页的指针。

行数据格式

在MySQL中行的数据格式有多种，其中MySQL5.7默认的是Dynamic，其中各种格式与区别如下。

1. Redundant行格式

Redundant行格式是一个比较老的行格式了,现在也就只有MySQL的一些系统表会使用它了。其中“字段长度偏移列表”记录了所有字段真实数据占用的长度，按照字段顺序逆序存储，“头部信息”使用6个字节48个二进制记录当前行的一些基本信息，“真实数据”记录每一列的数据。其中定长字段例如char(10)，如果值为null，真实数据会占用10字节存储null，而通过字段长度的偏移量二进制值，最高位如果是1则表示null，而如果字段是变长的，则不会在真实数据占用空间，因为基于偏移量计算长度为0即可。

例如Col2列长度偏移量是0xA4，二进制是 10100100，最高位是1，表明该列值是NULL,将高位去掉变成 0100100(十进制的36)， Col1列对应偏移量是0x1A(十进制的26),因此其长度是36-26=10

2. Compact行格式

Compact行格式中，“变长字段长度列表”只记录变长字段的长度，顺序与字段顺序相反，其中定长的字段数据按照固定长度直接存储即可。“Null值列表”基于字段允许为空按照二进制倒序记录“00000110”，其中0表示不为空，1表示为空，如果整行都不允许为空，则不需要Null值列表。头部信息同样记录相关基本信息，“真实数据”存储一行字段的数据值。

其中如果存出现一行数据太多，则会行溢出，溢出部分的数据会使用单独的行溢出页存储，存储真实数据的地方会存储一部分数据 + 溢出页的地址。

3. Dynamic & Compressed行格式

Dynamic和Compressed行格式在Compact的基础上，只是针对溢出页进行了调整，存储真实数据的地方直接只存储溢出页的地址。

4. 真实数据隐藏字段

在真实数据前会存储三个字段，row_id如果一行数据没有主键没有唯一键，则Innodb就会添加row_id字段，row_id不是必须的；trx_id事务id，表示这个数据是由哪个事务生成的，必须；roll_pointer记录上一个版本的指针，必须。

5. 头信息

Redundant格式与Compact格式的头信息略有不同，一些共有的主要信息如下：

delete_mask：标记此数据是否被删除。表明Innodb在删除一行数据时不会立刻直接物理删除，而是先标注删除状态，后续再通过异步线程进行数据清理。

next_record：下一条记录的位置，这里可以知道记录与记录之间是链表关系，同时指向的是下一条记录“头信息”和“真实数据”之间的位置，这样往左读是记录头信息，往右便是真实数据。

查询数据流程

1. 客户端与数据库建立连接，客户端一般也会维护一个数据库连接池，服务端在收到客户端的请求之后，在连接器中同样会对该请求连接进行维护管理。由于是长连接，MySQL默认8小时之后空闲连接会被断开，不过客户端的连接池一般都会自动检测连接的可用性。
2. MySQL服务端在收到请求之后，会基于SQL作为key查询缓存，如果缓存存在则直接返回，不存在则进行SQL执行，缓存数据涉及的表如果数据发生了变化，则缓存会失效。
3. 在执行SQL之前，需要先进行词法、语法解析，在此阶段如果SQL语句存在语法错误，则会返回异常，最后将SQL解析成语法树。

4. 优化器拿到SQL之后，先进行预处理（比如针对*会转换成对应的字段名，并对SQL中的字段表进行元数据信息进行检测），再通过分析SQL语句，选择查询成本最小的执行计划。
5. 拟定执行计划之后，则调用存储引擎的API进行数据文件查询，其中查询数据时数据页会在缓存池中缓存。

更新存储数据流程

1. 客户端发送请求到服务端，连接器处理请求进行认证权限相关操作之后，然后由解析器将SQL解析成语法树交由查询优化器处理，查询优化器基于SQL涉及的表、字段、索引分析出执行计划；
2. 调用存储引擎的API执行更新SQL，先从buffer pool中的查询要更新的数据，没有则从数据库文件加载，在更新前先将更新前的数据写入undo page并且undo page的写入操作日志记入redo buffer进行redo log持久化。然后更新buffer pool中的数据所在的data page（不会立刻持久化），同样记录更新数据操作的redo log。
3. 开始提交事务，将SQL操作记入binlog，对binlog持久化，同时将redo log的事务状态更新成commit，这样整个数据更新事务操作成功。
4. 目前数据只是更新到buffer pool中了，并没有落盘，系统会有一个Flush链表记录脏page，然后在合适的时机进行异步刷盘。
5. double writer机制，将脏页持久化到磁盘，为什么是double writer呢？因为一个page的大小事16kb，而磁盘的一个page cache是4kb，这样的话持久化就需要随机写，如果在随机写的过程中宕机，导致页数据损坏，这种损坏时redo无法修复的，故需要开辟一个共享表空间连续写入，完成之后再将脏页随机写存储到数据文件中。如果写入数据文件时宕机故障，则可以从共享表中读取数据进行恢复。

日志文件

1. undo log 回滚日志：每一次写（增删改）数据，都会生成一个undo log日志，用于数据的回滚。其中生成的undo log数据会先写入buffer pool的undo page中，再由异步线程刷新到磁盘文件。
2. redo log 重放日志：每一次写（增删改）数据，都会生成一个redo log日志，用于事务提交之后，数据还缓存在buffer pool中时，宕机之后进行数据恢复。redo log同样是先更新到缓存再进行刷盘，刷盘逻辑根据参数innodb_flush_log_at_trx_commit控制：

设置为0：先将redo log写入redo log buffer，然后异步线程每一秒持久化到磁盘；

设置为1：每次提交事务时，将redo log持久化到磁盘，这样的话性能损耗大；

设置为2：每次提交事务时，将redo log写入redo log文件，但是由于系统文件存在page cache，可能存在并未持久化到文件，然后再异步每一秒基于fsync将磁盘缓存进行刷盘持久化。

3. binlog 操作日志：每一个事务是一个完整的binlog日志，MySQL会给每一个线程开辟一个空间用来缓存，在提交事务时，将binlog存入系统文件的page cache，至于page cache合适持久化到系统文件，有sync_binlog参数来控制：

设置0：表示每次提交事务只写到page cache，不进行fsync刷盘，后续交由操作系统决定进行合适持久化；

设置1：表示每次提交事务都会write，然后马上执行fsync；

设置N（N >1）:表示每次提交事务都会写到page cache，但是累计N个事务之后才fsync;

binlog有3种格式类型，分别是Statement（默认格式）、Row、Mixed，区别如下：

Statement：每一条修改数据的SQL都会被记录到binlog种，相当于记录了逻辑操作，但是在主从复制时，如果SQL中使用了动态函数（uuid、now等）会导致数据不一致；

Row：记录行数据被修改成了什么样，但是这样的每一条数据的修改都会记录日志，会导致日志文件过大；

Mixed：Statement和Row的混合，针对Statement无法处理的动态参数问题，就使用Row格式，这个由系统自动判定。

事务

TODO : 补充读写锁问题

MySQL的事务隔离级别分为读未提交、读已提交、可重复读和串行化，其中读未提交可以读取到未提交事务修改的数据，所以直接读取最新的数据即可，串行化通过读写锁的方式避免并行访问，只有读已提交、可重复读需要基于MVCC（多版本并发控制）机制来实现。

在MVCC机制下，针对数据的读取会创建一个数据库快照Read View，其中可重复读是在每次查询前生成一个，而可重复读则是在启动事务时生成一个Read View，然后整个事务都是使用这个。

creator_trx_id：创建该Read View的事务id；

m_ids：指在创建Read View时，当前数据库活跃的启动但未提交事务的id列表；

min_trx_id：活跃事务id的最小值，即m_ids中的最小值；

max_trx_id：创建Read View时下一个事务id，当全局事务中最大的事务id值+1；

Read View工作机制

Read View工作机制主要是为了解决并发事务读写下，不同事务互不干扰，其中，我们在了解数据Row的格式时，我们知道真实数据的存储是有隐藏列的，例如trx_id事务id、roll_pointer旧版本指针，当数据发生变更时，就会产生一个旧版本的undo log，其中roll_pointer就指向它。

然后当一个事务查询数据时，除了自己更新的数据一致可见之外，还有以下情况：

1. 如果记录的trx_id小于Read View中的min_trx_id的值，表示这个版本在当前Read View创建之前就已经创建，则该版本的记录对当前事务可见；
2. 如果记录的trx_id大于等于Read View中的max_trx_id值，表示这个版本是在当前Read View创建后才启动的事务生成的，那么这个版本记录对当前事务不可见；
3. 如果记录的trx_id在min_trx_id和max_trx_id之间，但是m_ids列表中不存在，说明该事务已提交，则记录可见，如果m_ids中存在，则表示事务未提交则记录不可见。

读已提交

结合上图，如果当前事务trx_id = 99，事务98已提交，100、101还未提交，则第一次查询数据创建的Read View为[min_trx_id=99, m_ids=99,100,101, max_trx_id=102]，由于当前事务id为99，则只有98事务数据记录可见，待100、101事务提交之后，第二次查询数据创建的Read View为[min_trx_id=102, m_ids=102,105, max_trx_id=107]，这样101事务记录的数据就可见了。

可重复读

结合上图，如果当前事务trx_id = 99，事务98已提交，100、101还未提交，则事务开始时创建Read View为[min_trx_id=99, m_ids=99,100,101, max_trx_id=102]，由于当前事务id为99，则只有98事务数据记录可见，待100、101事务提交之后，第二次查询数据时，依旧沿用之前的Read Veiw，故虽然100、101已提交事务，但是在当前事务认为它还是未提交事务，依旧不可见，故查询的数据还是98事务的记录。

锁

1. 全局锁

锁定整个数据库实例，禁止所有的写操作，只允许读操作，通过FLUSH TABLES WITH READ LOCK 显式加全局锁。

由于全局锁会导致数据库只读影响业务，一般在数据库为“可重复读”事务隔离级别时，基于mysqldump工具备份数据时，加上-single-transaction参数开启事务，保证备份数据的完整性。

2. 表级锁

// 表级别的共享锁（S锁），读锁，该表只允许读不允许任何会话（包括当前会话）写，其他会话可读
// 同时当前线程如果锁住了t_table_01，对表t_table_01可以读，但是当前会话读其他表也会报错
lock tables t_table_name read;

// 表级独占锁（X锁），写锁，该表只允许当前会话读写，同时当前会话也只允许操作当前表，不可操作其他表。
lock tables t_table_name write

3. 元数据锁

元数据锁MDL不需要我们显示调用，在对一张表进行CRUD操作时，添加的是MDL读锁，避免在进行数据操作时被修改了表结构，对一张表做结构变更时，添加的是MDL写锁；

MDL读锁在事务执行期间，MDL是一直持有的，当一个长事务执行时，其他事务读数据不互斥，但是如果一个线程需要更改表结构，这样会导致申请MDL写锁阻塞，而由于MDL锁申请是一个队列，并且写锁优先级高于读锁，这样的话MDL写锁阻塞，这样就会导致后续的线程读取数据也会被阻塞掉。

4. 行锁

Record Lock 记录锁，锁住的是一条记录，而且记录锁是有S锁和X锁之分的；

Gap Lock间隙锁（区间锁），只存在于可重复读隔离级别，目的是防止在间隙锁区间插入数据，故不同事务同一区间的间隙锁允许存在。间隙锁本质是为了解决幻读现象，比如表汇总有一个id范围为（1,4）的间隙锁，那么其他事务是无法插入一条id=3的数据的。

Next-Key Lock临键锁，是Record Lock + Gap Lock的组合，即间隙锁变成了区间闭合，比如一个id临键锁（1,4]，这样既不能插入id=3的数据，也不能修改id=5的数据；

插入意向锁：是一个相当于一个点的间隙锁，当一个事务在想要插入数据的地方，发现改地方被加了一个间隙锁，那么该事务就会生成一个插入意向锁，等待间隙锁释放之后，完成插入。允许不冲突的插入操作并发执行。

5. 意向锁

由于表里会对某些行添加行共享锁或是行独占锁，这样的话，如果需要添加表级的独占锁的话，就需要检测每一行数据，这样效率太差，于是在对行添加共享锁或独占锁时，就对当前表添加表意向共享锁或意向独占锁。

6. AUTO-INC锁

表里的主键通常都会设置成自增的，这里数据库会给主键添加AUTO-INC锁，该锁不是在事务提交后才释放，而是在执行完插入语句后立即释放。

索引

在MySQL5.7版本，索引的构建主要都是B+树，B+树分为非叶子节点和叶子结点，其中每一个节点都是一个Page，非叶子节点存储索引数据，叶子结点存储索引和真实数据，其中所有叶子结点之间会通过指针形成一个链表。

1. 为什么是B+树而不是平衡二叉树或是B树？

平衡二叉树虽然不会像二叉树那样形成只有单边有数据的情况，但是平衡二叉树一个节点只有两个节点，这样的话当数据过多时，就会导致树的层级很高，这样的话就会导致更多次的磁盘I/O读取文件，性能不佳。

而B树（多叉树）虽然解决了树层级高的问题，但是由于B树的非叶子节点是存储索引+数据的，这样就导致一个节点存储的数据量有限，节点会变的更多，这样一次查询就需要更多的磁盘I/O来获取所需的数据。

2. 使用B+树的优势？

B+树在B树的基础上做了进一步优化，首先非叶子节点不再存储数据，只存储索引，所有的数据都存储在叶子结点，同时所有叶子节点形成一个有序链表，方便范围查询。

索引分类

聚簇索引：由主键字段构成的索引，标准的B+树索引。

非聚簇索引：由非主键字段构成的索引，B+树的叶子节点存储的是主键数据，也就是说通过非聚簇索引查询数据，会遍历两次B+树；

联合索引：由多个字段联合组成的索引，会按照创建索引的字段顺序进行索引建设和排序。例如联合索引（a , b），其中索引的建设会先基于a建设，然后再对b进行排序，也就是说a是有序的，b全局无序，在某一个a节点下是有序的。

回表&索引覆盖

在非聚簇索引下，由于叶子节点存储的都是主键，故查询到主键之后，还需要基于主键去查询其他字段的数据，这种情况叫回表；如果在查询索引时，索引字段刚好能够覆盖所需要查询的字段，这样就可以避免回表，这样叫做索引覆盖。

索引下推&区分度

对于联合索引（a,b）在执行select * from table_name where a > 1 and b = 2时，只有a能用到索引，在MySQL5.6之前，根据a查询主键之后，需要回表查询数据然后再判断b，在MySQL5.6时进了了优化，在存储引擎侧查询到a>1时，会同时进行b=2的条件匹配，返回符合的数据，这种行为叫索引下推；

建立联合索引时，要把区分度大的字段排在前边，这样区分度大的字段越有可能被更多的SQL使用到。

主从同步

主库在提交事务之后，写入binlog日志，主库的log dump线程会通知从库并发送binlog日志，从库会有一个专门的I/O线程，读取binlog日志写入中继日志（replay log）然后响应主库，从库会再起一个线程用于会放中继日志的binlog，将数据更新到存储引擎。

一般主从是binlog文件+position，即binlog.00001 + 120偏移量进行日志同步，其中binlog与position需要手动维护，后边有支持了binlog + gtid模式，gtid全局事务id，MySQL在提交事务写入binlog时，会把当前事务的gtid写入，同时维护在gtid_executed 集合中，从库进行同步之后，会先读取日志文件中的gtid，在本地的gtid_executed 集合中判定是否执行过，如果执行过了则跳过，未执行则进行回放，这样主从同步就不需要用户再关心同步细节了。

学的越多，懂的越少！