在上一篇文章中通俗的讲了MySQL的内存结构，在本篇文档中，我们重点介绍一下MySQL在on-disk下的架构。
https://yq.aliyun.com/articles/764518?spm=a2c4e.11155435.0.0.71de331269m7Gf

表

不同于其他RDB，MySQL独有的存储引擎，与服务端分离。不同业务场景下可以使用不同的存储引擎。
建表的时候可以指定使用的engine，如果不指定则跟随default_storage_engine参数，我们在本篇中主要讲主流的Innodb引擎。
建表的时候也可以指定创建的表空间（表空间概念请看下面），如果不指定表空间并且没有开启innodb_file_per_table（每张表都是一个表空间，保存在一个数据文件中），则会创建在system表空间中，这个是我们不希望看到的，系统表空间专门是用来存储系统相关表和数据，目前默认也都是开启innodb_file_per_table，当表创建在file-per-table 表空间，那么会创建一个以.ibd后缀的数据文件。
也可以指定data directory(绝对路径)来创建外部表（非默认数据目录，自动创建在external_directory/schema下）。

另外还可以指定innodb表和用户创建的临时表的row的格式，如果不指定则跟随innodb_default_row_format参数，默认为DYNAMIC。行格式决定了行在物理上是如何存储的，也就间接影响了查询性能、存储大小、IO等一些列连锁反应，但目前能够指定的格式不多DYNAMIC、COMPACT、REDUNDANT、 Compressed。总的来说存储量越小，CPU使用越高，反之亦然。

Innodb不管是表还是index，都是根据主键按B-tree索引排列的，在Mysql中primary key和cluster index可以认为是一个东西，cluster index叶子节点数据结构包含了所有的列和数值（其实就是表数据），如果是secondary index，则只包含索引列和主键列。
变长的列（大字段 VARCHAR, VARBINARY, and BLOB and TEXT types）对于index来说是个例外，变长的列对于index page来说太长，在index page中只会存一部分数据（一般为768 bytes），剩余的存储在overflow page（溢出页）中，每个溢出页也有一个自己的list。这样的列也叫做off-page columns。其实不只是大字段会导致这个情况，只要值大小超过768 bytes，那么就会导致这个情况发生。

数据库IO最小单位为page，默认为16k，文件扩展的单元是extent 默认为1M。再往上是表空间和schema。

索引

MySQL innodb是强烈依赖于主键（非空、唯一、少更新的列）的数据库，主要是两周索引cluster index和secondary index。cluster index 类似于oracle 中的index-organized table（索引组织表），如果表上没有定义PK，那么innodb会自动定位非空的unique索引作为cluster 索引。如果没有PK也没有非空唯一索引，则innodb
内部自己生成一个隐藏的cluster index叫做GEN_CLUST_INDEX，在一个自动生成的列上，里面存的是ROW ID。
表里面行都是根据row id进行排序，row id是一个6byte的单调递增值，在物理存储上连续(讲到隐藏列，这里另外补充两个：DB_TRX_ID和DB_ROLL_PTR，都是在实现MVCC中需要用到，而且是肯定存在的，具体讲到MVCC实现的时候再讲)。
为什么cluster index会加速查询？
因为当通过cluster index访问一行的时候，索引直接指向了含有行数据的页，精准获取到需要的数据，IO也是最少的。
如果是secondary index，则只包含索引列和主键列。通过主键再去查找cluster index。
除了spatial indexes以外，innodb的索引都是B-tree的数据结构。spatial indexes使用的是R-trees。
当新的row插入到索引页中时，innodb会试图保留1/16的空间给将来的插入和更新操作，如果是顺序插入，则索引页最大为15/16满，如果是随机插入到索引页，那么索引页可能是1/2到15/16满。

在创建和重建B-tree索引的时候，是按顺序去大批量创建索引（sorted index build），另外可以用参数innodb_fill_factor去控制执行sorted index build的时候，每个页的百分比（oracle的watermark），默认为100，也就是15/16。通过设置MERGE_THRESHOLD来表明页是否是free状态。
sorted index build 分3个步骤。
1、扫描cluster index，放入到sort buffer中，如果sort buffer满了，会写入到一个临时中间文件。
2、合并过个临时中间文件到一个文件中。
3、插入到B-tree中。
在引入sorted index build以前，索引条目通过insert API一次只能插入一条，sorted index build大大缩短时间和资源cost。在执行sorted index build的时候，也会执行一次checkpoin刷脏。

fulltext index

MySQL支持全文索引（创建在CHAR, VARCHAR, TEXT列上），这里不做太多赘述。

表空间（tablespace）

system tablespace

system tablespace是用来存储change buffer（上一篇中有提到）和创建中系统表空间中的表和索引。
另外在8.0之前，也是存储数据字典的和doublewrite buffer的地方。默认为ibdata1，存储在数据目录。
早期版本中，我们可以通过在innodb_data_file_path参数中增加autoextend来自动扩展文件大小，每次扩展的大小由innodb_autoextend_increment控制，默认64M。

File-Per-Table Tablespaces

通过 innodb_file_per_table来开启。开启以后innodb创建的表都会创建一个新的表空间，并且物理上是一个新的文件，文件后缀为.ibd。关闭该参数以后，innodb创建的表会在system 表空间。相对共享的system 表空间和general表空间来说，有以下优势：
1、truncate和drop table以后，可以从操作系统层面等到空闲的空间，性能也更好。
2、在系统表空间中alter table扩展的空间也不会被shrink。
3、理论上，表可以创建在不同的存储设备上。
4、支持DYNAMIC 和 COMPRESSED 行模式。
5、理论上可以提升并发，因为在innodb_flush_method设置为O_DIRECT的时候，一般liunx不支持同时写一个文件。

劣势：
1、可能会导致空间被浪费。
2、可能导致高的fsync()调用。
3、mysql会为每个表空间都保持一个open file handle，可能也会影响性能。
4、drop table的时候会scan buffer pool，

general 表空间

顾名思义，general表空间就是用create tablespace创建出来的表空间，给不同表共享。
比File-Per-Table有个潜在的内存使用上的优势，多表少表空间的情况下，表空间元数据使用的内存会比File-Per-Table少。
8.0.14以后支持add datafile（真的跟oracle越来越像）。

undo 表空间

undo表空间包含了undo log，关于undo的介绍，下面会有涉及。默认系统会自动创建2个undo表空间innodb_undo_001 和innodb_undo_002，文件后缀为.ibu。
如果设置了 innodb_undo_directory，则会在该目录下创建undo文件，不然则在数据文件目录。
8.0.14以后支持在线add datafile。
有两个办法可以truncate undo tablespace：
1、设置innodb_undo_log_truncate，则由innodb自动执行，通过设置innodb_max_undo_log_size控制undo表空间的阈值，一旦超过则自动执行truncate，期间会标记表空间为inactive来避免分配新的事务（当然已经存在的事务不影响使用），释放和清除回滚段，使得表空间到初始化大小（根据page大小不同，初始化大小也不同）。可以设置innodb_purge_rseg_truncate_frequency来控制调用频率。
2、手动truncation。
手动truncation的情况下，则至少需要3个undo表空间，确保有两个在active状态。
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
然后mysql会自动清理和释放空间。改变表空间状态从INACTIVE to EMPTY。
SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE tablespace_name;
ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;

temporary表空间

临时表空间用来存储session级别用户常见的临时表和优化器创建的内部临时表。
会话级别的临时表全部是on-disk的，当会话disconnect，临时表也会被truncate和释放，归还给pool。
当服务启动时，会创建包含10个临时表空间的pool，pool中数量只会增加不会减少。后缀为.ibt。
可以通过innodb_temp_tablespaces_dir制定创建的目录。

临时表空间还分为session和global临时表空间。
global临时表空间（ibtmp1）存储用户创建临时表空间的undo log。随系统启停创建和释放。
创建不成功则mysql startup失败，mysql非normal停止则不会释放全局临时表空间。

8.0.16默认使用innodb存储引擎来存储on-disk的内部临时表，以前的版本可以通过设置internal_tmp_disk_storage_engine来控制(Myisam或Innodb)。

传输表空间，主要用来快速导数据，需要锁表，感觉比较鸡肋。

doublewrite buffer

doublewrite buffer是一个有意思的设计，他不只是一个内存结构，也存在系统表空间（8.0.20以前,8.0.20存储在单独的doublewrite file）中。用来在数据库crash的时候，某些页在写入中损坏（比方说一个页16k，写8k时数据库崩溃了，那么整个页就有问题），那么可以从doublewrite buffer中copy一个好的页用来crash恢复。
通过fsync()函数顺序写入，所以虽然数据需要写两次，但整体开销比两次I/O要小。
通过设置innodb_doublewrite来控制是否开启dw.
innodb_doublewrite_files定义dw file的数量，一般跟bp数对应，包含两个文件：A flush list doublewrite file and an LRU list doublewrite file。

redo log

redo log可以说是数据库的灵魂，有了redo log，那么数据库有算拥有了不死之身。在普通的DML操作中，记录数据的变化和LSN(log sequence number)，在数据库crash的时候用来恢复，也是实现replication的重要组件。
一般情况下是ib_logfile0 和ib_logfile1，写完ib_logfile1又会回到ib_logfile0，循环往复。
可以通过innodb_log_files_in_group来设置redo log数量，默认为2。
可以通过innodb_log_file_size来设置redo log大小，默认为48MB。
在会话执行修改事务的时候，不管事务有没有提交，首先会把修改的数据记入到redo log buffer，然后redo log buffer有专门的线程按照特定的条件（比事务提交）写入到redo log中，并且会把同一时间提交的不同事务一同刷新到redo log（组提交），8.0.17以后可以执行redo归档（redo archive），通过 innodb_redo_log_archive_dirs设置归档路径。
redo在数据库crash或者非normal关闭以后，重新启动时执行instance recovery。
回滚数据文件中未提交的数据，写入已提交但数据文件中还没有修改的数据。

undo log

undo log是用来记录一个事务（未提交）中最近改变的撤销日志。
如果其他事务需要查看原始的数据（为了一致读），那么会从undo log记录中获取未修改的事务。
undo log保存在undo log segment（保存多个事务的undo log）中，也叫在rollback segment。rollback segment保存在undo 表空间和全局临时表空间（global temporary tablespace，在创建了user-defined temporary table的时候会用到，但不记录redo）。
可以用innodb_rollback_segments控制一个undo表空间中有多少个回滚段。

另外事务在没有达到事务并发限制的时候会提示：并发事务达到上限，可能因为一个事务被分配的回滚段中的undo slot（回滚槽）不足。

写作不易，各位看官大方的留下您的痕迹吧！