202_mysql_innodb_3_表空间_共享表空间

System Tablespace(共享表空间)

存储方式 ibdata1~ibdataN, 5.5版本默认的表空间类型。 ## ibdata1共享表空间在各个版本的变化 5.5版本： 系统相关:（全局）数据字典信息（表基本结构信息、状态、系统参数、属性..），UNDO回滚信息(记录撤销操作)、Double Write buffer信息、 临时表信息、change buffer 用户数据： 表数据行、表的索引数据 5.6版本：共享表空间只存储于系统数据，把用户数据独立了。 系统相关：（全局）数据字典信息、UNDO回滚信息、Double Write信息、临时表信息、change buffer 5.7版本：在5.6基础上，把临时表独立出来，UNDO也可以设定为独立 系统相关：（全局）数据字典信息、UNDO回滚信息、Double Write信息、change buffer 8.0.19版本：在5.7的基础上将UNDO回滚信息默认独立，数据字典不再集中存储了 系统相关：Double Write信息、change buffer 8.0.20版本:在之前版本基础上，独立 Double Write信息

查看共享表空间命令

select @@innodb_data_file_path;

select @@innodb_autoextend_increment;

参数用途：ibdata1文件，默认初始大小12M，不够用会自动扩展，默认每次扩 展64M

配置文件设定为和实际大小一致： innodb_data_file_path=ibdata1:76M;ibdata2:100M;ibdata3:100M:autoextend 5.7 中建议：设置共享表空间2-3个，大小建议512M或者1G，最后一个定制为 自动扩展。 8.0 中建议：设置1-2个就ok，大小建议512M或者1G

一 系统表空间 表空间 ID 是0

系统表空间的结构和独立表空间基本类似，只不过由于整个 Mysql 进程只有一个系统表空间，

在系统表空间中会额外记录一些有关整个系统信息的页面，所以会比独立表空间多出一些记录这些信息的页面。

系统表空间的整体结构

系统表空间与独立表空间的一个非常明显的不同之处就是在表空间开头有许多记录整个系统属性的页面，示意图如下：

可以看到，系统表空间和独立表空间的前三个页面(页号分别为0、1、2，类型分别是 FSP_HDR、IBUF_BITMAP、INODE)的类型是一致的，

1.1 只是页号3 ~ 7的页面是系统表空间特有的

页号

页面类型

英文描述

描述

3

SYS

Insert Buffer Header

存储 Insert Buffer 的头部信息

4

INDEX

Insert Buffer Root

存储 Insert Buffer 的根页面

5

TRX_SYS

Trasction System

事务系统的相关信息

6

SYS

First Rollback Segment

第一个回滚段的页面

7

SYS

Data Dictionary Header

数据字典头部信息

1.2 DWB（double write bufffer）

除了这个记录系统属性的页面外，系统表空间的 extent 1 和 extent 2这两个区，也就是页号从64 ~ 191这128个页面被称为 Doublewrite buffer，也就是双写缓冲区

作用： MySQL，最小IO单元page（16KB），OS中最小的IO单元是block（4KB） 为了防止出现以下问题： mysqld process crash in the middle of a page write DWB每次1M，2次写完。数据页再刷盘。

1.3 InnoDB 数据字典

insert数据为例, 先要找到表聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应索引的 B+树中

InnoDB 存储引擎特意定义了一些列的内部系统表来记录这些元数据

表名

描述

SYS_TABLES

整个 InnoDB 存储引擎中所有的表的信息

SYS_COLUMNS

整个InnoDB存储引擎中所有的列的信息

SYS_INDEXES

整个InnoDB存储引擎中所有的索引的信息

SYS_FIELDS

整个InnoDB存储引擎中所有的索引对应的列的信息

SYS_FOREIGN

整个InnoDB存储引擎中所有的外键的信息

SYS_FOREIGN_COLS

整个InnoDB存储引擎中所有的外键对应的列的信息

SYS_TABLESPACES

整个InnoDB存储引擎中所有的表空间信息

SYS_DATAFILES

整个InnoDB存储引擎中所有的表空间对应文件系统的文件路径信息

SYS_VIRTUAL

整个InnoDB存储引擎中所有的虚拟生成列的信息

1.4 Data Dictionary Header 页面

InnoDB 的设计者又拿出一个固定的页面来记录这4个表的聚簇索引和二级索引对应的 B+树的位置，

这个页面就是页号为7的页面，类型为 SYS，记录了 Data Dictionary Header，也就是数据字典的头部信息

名称

中文名

占用空间大小

简单描述

File Header

文件头部

38字节

页的一些通用信息

Data Dictionary Header

数据字典头部信息

56字节

记录一些基本系统表的根页面位置以及 InnoDB 存储引擎的一些全局信息

Segment Header

段头部信息

10字节

记录本页面所在段对应的 INODE Entry 位置信息

Empty Space

尚未使用空间

16272字节

用于页结构的填充，没啥实际意义

File Trailer

文件尾部

8字节

校验页是否完整

Data Dictionary Header部分的各个字段

Max Row ID:如果我们不显示的为表定义主键，而且表中没有 UNIQUE 索引，那么 InnoDB存储引擎会默认生成一个名为 row_id 的列为主键。因为它是主键，所以每条记录的 row_id 列的值不能重复。原则上只要一个表中的 row_id 列不重复就可以了，也就是说表 a 和表 b 拥有一样的 row_id 列也没啥关系，不过不过 InnoDB 只提供了这个 Max Row ID 字段，不论哪个拥有 row_id 列的表插入一条记录时，该记录的 row_id 列的值就是 Max Row ID 对应的值，然后再把Max Row ID对应的值加1，也就是说这个Max Row ID是全局共享的。 Max Table ID:InnoDB存储引擎中的所有的表都对应一个唯一的 ID，每次新建一个表时，就会把本字段的值作为该表的 ID，然后自增本字段的值。 Max Index ID:InnoDB存储引擎中的所有的索引都对应一个唯一的 ID，每次新建一个索引时，就会把本字段的值作为该索引的 ID，然后自增本字段的值。 Max Space ID:InnoDB存储引擎中的所有的表空间都对应一个唯一的 ID，每次新建一个表空间时，就会把本字段的值作为该表空间的 ID，然后自增本字段的值。 Mix ID Low(Unused)：这个字段没啥用，忽略。 Root of SYS_TABLES clust index：本字段代表 SYS_TABLES 表聚簇索引的根页面的页号。 Root of SYS_TABLE_IDS sec index：本字段代表 SYS_TABLES 表 ID 列建立的二级索引的根页面的页号。 Root of SYS_COLUMNS clust index：本字段代表 SYS_COLUMNS 表聚簇索引的根页面的页号。 Root of SYS_INDEXES clust index：本字段代表 SYS_INDEXES 表聚簇索引的跟页面的页号。 Root of SYS_FIELDS clust index：本字段代表 SYS_FIELDS 表聚簇索引的根页面的页号。

information_schema 系统数据库

需要注意的一点是，用户是不能直接访问InnoDB的这些内部系统表的，除非直接去解析系统表空间对应文件系统上的文件。

InnoDB的设计者考虑到查看这些表的内容可能有助于大家分析文件，所以在系统数据库information_schema中提供了一些以INNODB_SYS头的表

5.7

mysql> use information_schema; mysql> show tables like 'INNODB_SYS%';

8.0

show tables like 'INNODB_%';

INNODB_COLUMNS

INNODB_FIELDS

INNODB_TABLES

INNODB_INDEXES

二 Innodb统计数据如何收集

统计数据的存储方式 1 永久性存储统计数据(磁盘) 2 非永久存储(内存)

show variables like "%innodb_stats_persistent" # 控制存储方式 on 代表磁盘永久存储

Innodb 在存储表和索引时候 还会维护这些数据的统计信息

show tables from mysql like "innodb%stats"

innodb_table_stats 存储表的统计数据，每条记录对应一个表统计数据 n_rows 记录数（估算值 从聚簇索引取出几个叶子节点数据页, 算出平均记录数, 再乘以全部叶子节点的数量） clustered_index_size 表的聚簇索引占的页面数量 sum_of_other_index_sizes 表的其它索引占用的页面数量

innodb_index_stats 存储索引的统计数据 index_name 索引名 stat_name 针对这个所用用到统计项名称 stat_value 对应的统计项的值 sample_size 为生成统计数据而采样的页面数量 stat_description 用来描述该统计项的信息 统计项概述 n_leaf_pages 表示该索引的叶子节点占用多少页面 size 该索引占多少页面（包括分配给叶子节点段或非叶子节点段但暂未使用的） n_diff_pfxNN : 对应索引列不重复的值；NN 可以被 01,02等代替

定期更新统计信息

1 自动 开启 innodb_stats_auto_recalc

2 手动  analyze table  table_name

非永久存储(内存) null 处理

Innodb_stats_method

show variables like "%Innodb_stats_method"

nulls_equal  认为所有的null值都相等, 如果索引上null值很多, 优化器可能会选择不适用索引进行访问

nulls_unequal null值不相等

nulls_igored  把null 值忽略

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。