MySQL 物理结构

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL 物理结构

魏庄 2020-06-09 22:35:03 浏览851
展开阅读全文

在上一篇文章中通俗的讲了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使用越高,反之亦然。
image.png
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表空间中有多少个回滚段。
image.png
另外事务在没有达到事务并发限制的时候会提示:并发事务达到上限,可能因为一个事务被分配的回滚段中的undo slot(回滚槽)不足。

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

网友评论

登录后评论
0/500
评论
魏庄
+ 关注