石家庄网站开发培训,枝江企业网站,工业和信息化部发短信,安徽seo推广目录
MySQL的数据存放在哪个文件#xff1f;
表空间文件的结构是怎么样的#xff1f;
1、行#xff08;row#xff09;
2、页#xff08;page#xff09;
3、区#xff08;extent#xff09;
4、段#xff08;segment#xff09;
InnoDB 行格式有哪些#xf…
目录
MySQL的数据存放在哪个文件
表空间文件的结构是怎么样的
1、行row
2、页page
3、区extent
4、段segment
InnoDB 行格式有哪些
COMPACT行格式长什么样
记录的额外信息
1、变长字段长度列表
2、NULL值列表
3、记录头信息
记录的真实信息
varchar(n) 中 n 最大取值为多少
单字段的情况
多字段的情况
行溢出后MySQL是怎么处理的
总结 MySQL的数据存放在哪个文件
MySQL存储的行为由存储引擎实现的MySQL 支持多种存储引擎不同的存储引擎保存的文件也不同。
InnoDB是我们常用的存储引擎也是 MySQL 默认的存储引擎。所以本文主要围绕 InnoDB 存储引擎展开。
查询MySQL数据库的文件存放在哪个目录
mysql SHOW VARIABLES LIKE datadir;
--------------------------------
| Variable_name | Value |
--------------------------------
| datadir | /var/lib/mysql/ |
--------------------------------
1 row in set (0.00 sec)
我们每创建一个 database 数据库都会在 datadir 目录里创建一个以 database 为名的目录然后保存表结构和表数据的文件都会存放在这个目录里。
比如这里有一个名为 my_test 的 database 该 database 里有一张名为 t_order 数据库表。 然后我们进入 /var/lib/mysql/my_test 目录看看有什么文件
[rootxiaolin ~]#ls /var/lib/mysql/my_test
db.opt
t_order.frm
t_order.ibd
可以看到公有三个文件这三个文件分别代表着
db.opt用来存储当前数据库的默认字符集和字符校验规则t_order.frmt_order 的表结构会保存在这个文件。在MySQL中建立一张表都会生成一个 .frm 文件该文件是用来保存每个表的元数据信息的主要包含表结构定义。t_order.ibdt_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件文件名ibdata1里也可以存放在独占表空间文件文件名表名字.ibd。这个行为是由参数 innodb_file_per_table 控制的若设置了参数 innodb_file_per_table 为 1 则会将存储的数据、索引等信息单独存储在一个独占表空间从 MySQL 5.6.6 版本开始它的默认值就是 1 了因此从这个版本之后 MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。
总结每创建一张表都会生成.opt.frm.ibd三个文件其中 .opt 存储字符集和字符校验规则 .frm 存储表结构定义.ibd 存储每张表的数据。 表空间文件的结构是怎么样的
表空间由 段segment、区extent、页page、行row组成InnoDB存储引擎的逻辑结构大致如下图 1、行row
数据表中的记录都是按行row进行存放的每个记录根据不同的行格式有不同的存储结构。 2、页page
记录是按照行来存储的但是数据库的读取并不以 [行] 为单位否则一次读取也就是一次 I/O操作只能处理一行数据效率会非常低。
因此 InnoDB的数据是按 [页] 为单位读写的也就是说当需要读取一条记录的时候并不是将这个记录从磁盘读出来而是以页为单位将其整体读入内存。
默认每个页的大小为 16 KB 也就是最多能保证 16 KB 的连续存储空间。
页是 InnoDB 存储引擎磁盘管理的最小单元意味着数据库每次读写都是以 16KB 为单位的一次最少从磁盘中读取 16K 的内容到内存中一次最少把内存中的 16 K 内容刷新到磁盘中。
页的类型很多常见的有数据页、undo 日志页、溢出页等等。数据表中的页式用 [数据页] 来管理的。 3、区extent
InnoDB存储引擎是用 B 树来组织数据的。
B 树中每一层都是通过双向链表连接起来的如果是以页为单位来分配存储空间那么链表中相邻的两个页之间的物理位置并不是连续的可能离得非常远那么磁盘查询时就会有大量的随机I/O,随机 I/O 是非常慢的。
解决这个问题也很简单就是让链表中相邻的页的物理位置也相邻这样就可以使用顺序 I/O 了那么在范围查询扫描叶子结点的时候性能就会很高。
解决办法
在表中数据量大的时候为某个索引分配空间的时候就不再按照页为单位分配了而是按照区extent为单位分配。每个区的大小为 1MB 对于 16KB 的页来说连续的 64 个页会被划分为一个区这样就使得链表中相邻的页的物理位置也相邻就能使用顺序 I/O 了。 4、段segment
表空间是由各个段segment组成的段是由多个区extent组成的。段一般分为数据段、索引段和回滚段等。
索引段存放 B 树的非叶子节点的区的集合数据段存放 B 树的叶子节点的集合回滚段存放的是回滚数据的区的集合 InnoDB 行格式有哪些
行格式row_format就是一条记录的存储结构。
InnoDB 提供了 4 种行格式分别是Redundant 、Compact、Dynamic和Compressed行格式。
Redundant 是很古老的行格式了MySQL 5.0 版本之前用的行格式现在基本没人用了由于Redundant不是一种紧凑的行格式所以 MySQL 5.0 之后引入了 Compact 行记录存储方式Compact是一种紧凑的行格式设计的初衷就是为了让一个数据页中可以存放更多的行记录从 MySQL 5.1 版本后行格式默认设置成 CompactDynamic 和 Compressed 两个都是紧凑的行格式它们的行格式都和Compact差不多因为都是基于Compact改进一点东西。从 MySQL5.7版本后默认使用Dynamic 行格式。 COMPACT行格式长什么样 可以看到一条完整的记录分为 [记录的额外信息] 和 [记录的真实数据] 两个部分。 记录的额外信息
记录的额外信息包含三个部分变长字段长度列表、NULL值列表、记录头信息。
1、变长字段长度列表
varchar(n) 和 char(n) 的区别char是定长的varchar 是变长的变长字段实际存储数据的长度大小不固定。
所以在存储数据的时候也要把数据占用的大小存起来存到 [变长字段长度列表] 里面读取数据的时候才能根据这个 [变长字段长度列表] 去读取对应长度的数据。其他 TEXT、BLOB等变长字段也是这么实现的。
为了展示 [变长字段长度列表] 具体是怎么保存 [变长字段的真实数据占用的字节数]我们先创建这样的一张表字符集是 ascii 所以每一个字符占用的是 1 字节行格式是 Compact t_user 表中 name 和 phone 字段都是变长字段
CREATE TABLE t_user (id int(11) NOT NULL,name VARCHAR(20) DEFAULT NULL,phone VARCHAR(20) DEFAULT NULL,age int(11) DEFAULT NULL,PRIMARY KEY (id) USING BTREE
) ENGINE InnoDB DEFAULT CHARACTER SET ascii ROW_FORMAT COMPACT;
现在 t_user 表里有三条记录 接下来我们看看这三条记录的行格式中的 [变长字段长度列表] 是怎么存储的
第一条记录
name 列的值为 a 真实数据占用的字节数是 1 字节十六进制 0x01phone 列的值 为 123真实数据占用的字节数是 3 字节十六进制 0x03age 列和 id 列是不变长字段所以这里不用管
这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放所以 [变长字段长度列表] 里的内容是 [03 01]而不是 [01 03]。 同样可以得出第二条记录的行格式中[变长字段长度列表] 里的内容是 [04 02] 如下图 第三条记录中的 phone 的 列值为 NULL NULL 是不会存放在行格式中记录的真实数据部分里的所以 [变长字段长度列表] 里不需要保存值为 NULL 的变长字段的长度。 为什么 [变长字段长度列表] 的信息要按照逆序存放 主要是因为 [ 记录头信息] 中指向下一个记录的指针指向的是下一条记录的 [记录头信息] 和 [真实数据] 之间的位置这样的好处就是向左读就是记录头信息向右读就是真实数据比较方便。
[变长字段长度列表] 中的信息之所以要逆序存放是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中这样就可以提高 CPU Cache 的命中率。
同样的道理NULL值列表的信息也需要逆序存放。 每个数据库表的行格式都有 [变长字段字节数列表] 吗 变长字段字节数列表不是必须的。
当数据表没有变长字段的时候比如全部都是 int 类型的字段这时候表里的行格式就不会有 [变长字段长度列表] 了因为没必要不如去掉可以节省空间。
所以 [变长字段长度列表] 只出现在数据表中有变长字段的时候。 2、NULL值列表
表中的某些列可能会存储 NULL值如果把这些 NULL 值都放在记录的真实数据中会比较浪费空间所以Compact 行格式把这些值为 NULL的列存储到 NULL 值列表中。
如果存在允许 NULL 值的列则每一个列对应一个二进制位bit二进制位按照列的顺序逆序排列。
二进制位的值为 1 时代表该列的值为 NULL二进制位的值为 0 时代表该列的值不为 NULL
另外NULL的值列表必须用整数个字节的位表示1字节8位如果使用的二进制位个数不足整数个字节则在字节的高位补 0.
还是以 t_user 表的这三条记录作为例子 接下来看看这三条记录的行格式中的 NULL 值列表是怎样存储的
先看第一条记录第一条记录所有列都有值不存在 NULL 值所以用二进制来表示是这样子的 但是 InnoDB 是用整数字节的二进制来表示NULL值列表的现在不足 8 位 所以要在高位补 0 最终用二进制来表示是这样子的 所以对于第一条数据 NULL值列表用十六进制表示是 0x00.
第二条记录第二条记录age列是NULL值所以对于第二条数据NULL值列表用十六进制表示是0x04. 第三条记录第三条记录 phone 列和 age 列是 NULL值所以对于第三条数据NULL值列表用十六进制表示是0x06。 把三条记录的NULL值列表都填充完毕后它们的行格式是这样的 每个数据库表行格式都有 [NULL值列表] 吗 NULL值列表也不是必须的。
当数据库的字段都定义成 NOT NULL 的时候这时候表里的行格式就不会有 NULL值列表了。
所以在设计数据库表的时候通常都是建议将字段设置为 NOT NULL 这样可以至少节省 1 字节的空间NULL值列表至少占用 1 字节空间。 [NULL 值列表] 是固定 1 字节吗如果是这样的话一条记录有九个字段值都是 NULL 这时候怎么表示 [NULL值列表] 的空间不是固定 1 字节的。
当一条记录有 9 个字段值都是 NULL 那么就会创建 2 字节空间的 [NULL 值列表]以此类推。 3、记录头信息
记录头信息包含的内容很多这里例举几个比较重要的
delete_mask标识此条数据是否被删除。从这里可以知道我们执行 delete 删除记录的时候并不会真正的删除记录只是将这个记录的 delete_mask 标记为 1。next_record下一条记录的位置。从这里可以知道记录与记录之间是通过链表组织的。在前面提到过指向的是下一条记录的 [记录头信息] 和 [真实数据] 之间的位置record_type表示当前记录的类型 0 表示 普通记录 1 表示 B树非叶子节点记录 2 则表示最小记录 3 表示最大记录。 记录的真实信息
记录的真实数据部分除了我们定义的字段还有三个隐藏字段分别为 row_id、trx_id、roll_pointer。 row_id如果建表的时候指定了主键或者唯一约束列那么就没有 row_id隐藏字段了。如果既没有指定主键又没有唯一约束那么InnoDB就会记录添加 row_id 隐藏字段。row_id 不是必需的占用 6 个字节trx_id事务id表示这个数据是由哪个事务生成的。trx_id是必需的占用 6 个字节。roll_pointer这条记录上一个版本的指针。roll_pointer是必需的占用 7 个字节。 varchar(n) 中 n 最大取值为多少
MySQL规定除了 TEXT、BLOBs 这种大对象类型之外其他所有的列步包括隐藏列和记录头信息占用的字节长度加起来不能超过 65535 个字节。
也就是说一行记录除了TEXT、BLOBs 类型的列限制最大为 65535 字节注意是一行的总长度不是一列。
PS: varchar(n) 字段类型的 n 代表的是最多存储的字符数量并不是字节大小。
要算varchar(n)最大能允许存储的字节数还需要看数据库表的字符集因为字符集代表着1个字符占用多少字节比如ascii字符集1 个字符占用 1 字节那么 varchar(100)意味着最大能允许存储 100 字节的数据。如果是utf-8字符集一个中文字符占用 3 字节英文占用 1 字节。 单字段的情况
前面我们知道了一行记录最大只能存储 65535 字节的数据
假设数据库表只有一个 varchar(n)类型的列且字符集是 ascii 在这种情况下varchar(n)中 n 取最大值是 65535 吗
我们定义一个 varchar(65535)类型的字段字符集为 ascii 的数据库表
CREATE TABLE test (
name VARCHAR(65535) NULL
) ENGINE InnoDB DEFAULT CHARACTER SET ascii ROW_FORMAT COMPACT;
看看能不能成功创建一张表 可以看到创建失败了
从报错信息可以知道一行数据的最大字节数是 65535 不包含 TEXT、BLOBs 这种大对象类型其中包含了storage overhead.
storage overhead 其实就是 [变长字段长度列表] 和 [NULL值列表]也就是说一行数据的最大字节数 65535 实际上是包含 [变长字段长度列表] 和 [NULL 值列表] 所占用的字节数。所以在计算varchar(n) 中 n 最大值时需要减去 storage overhead 占用的字节数。 这是因为我们存储字段类型为 varchar(n) 的数据时其实分成了三个部分来存储
真实数据真实数据占用的字节数NULL标识如果不允许为NULL这部分不需要 本例中 [NULL值列表] 所占用的字节数是多少 我们创建表时字段允许为NULL的而且行字段数不超过8所以会用 1 字节来表示 [NULL值列表]。 本例中[变长字段长度列表] 所占用的字节数是多少 [变长字段长度列表] 所占用的字节数 所有 [变长字段长度] 占用的字节数之和。
所以我们要先知道每个变长字段的 [变长字段长度] 需要用多少字节表示具体情况分为
条件一如果变长字段允许存储的最大字节数小于等于 255 字节就会用 1 字节表示 [变长字段长度]条件二如果变长字段允许存储的最大字节数大于 255 字节就会用 2 字节表示 [变长字段长度]
因为我们这个案例是只有 1 个变长字段所以「变长字段长度列表」 1 个「变长字段长度」占用的字节数也就是 2 字节。
因为在算 varchar(n) 中 n 最大值时需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下varchar(n) 中 n 最大值 65535 - 2 - 1 65532。 可以看到创建成功了。所以在算 varchar(n) 中 n 最大值时需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。
当然上面这个例子是针对字符集为 ascii 情况如果采用的是 UTF-8varchar(n) 最多能存储的数据计算方式就不一样了
在 UTF-8 字符集下一个字符最多需要3个字节varchar(n) 的 n 最大取值就是 65532/3 21844。
上面所说的只是针对于一个字段的计算方式。 多字段的情况
如果有多个字段的话要保证所有字段的长度 变长字段字节数列表所占用的字节数 NULL值列表所占用的字节数 65535 行溢出后MySQL是怎么处理的
MySQL 中磁盘和内存交互的基本单位是页一个页的大小一般是 16KB也就是 16384字节而一个 varchar(n) 类型的列最多可以存储 65532字节一些大对象如 TEXT、BLOB 可能存储更多的数据这时一个页可能就存不了一条记录。这个时候就会发生行溢出多的数据就会存到另外的「溢出页」中。
如果一个数据页存不了一条记录InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时溢出的数据会存放到「溢出页」中
当发生行溢出时在记录的真实数据处只会保存该列的一部分数据而把剩余的数据放在 [溢出页] 中然后真实数据处用 20 字节存储指向溢出页的地址从而可以找到剩余数据所在的页。 上面这个是 Compact 行格式在发生行溢出后的处理。
Compressed 和 Dynamic 这两个行格式和 Compact 非常类似主要的区别在于处理行溢出数据时有些区别。
这两种格式采用完全的行溢出方式记录的真实数据处不会存储该列的一部分数据只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中看起来就像下面这样 总结 MySQL的NULL值是怎么存放的 MySQL的Compact行格式中会用 [NULL值列表] 来标记值为 NULL 的列。NULL值并不会存储在行格式中的真实数据部分。
NULL值列表会占用 1 字节空间当表中所有的字段都定义成 NOT NULL 行格式中就不会有 NULL 值列表这样可以节省 1 字节的空间。 MySQL怎么知道varchar(n) 实际占用数据的大小 MySQL 的 Compact 行格式中会用 [变长字段长度列表] 存储变长字段实际占用的数据大小。 varchar(n)中 n 最大值为多少 一行记录最大能存储 65535 字节的数据但是这个是包含「变长字段字节数列表所占用的字节数」和「NULL值列表所占用的字节数」。所以 我们在算 varchar(n) 中 n 最大值时需要减去这两个列表所占用的字节数。
如果一张表只有一个 varchar(n) 字段且允许为 NULL字符集为 ascii。varchar(n) 中 n 最大取值为 65535(行最大存储) - 2(变长字段长度列表) - 1(NULL值列表) 65532。
如果有多个字段的话要保证所有字段的长度 变长字段字节数列表所占用的字节数 NULL值列表所占用的字节数 65535。
ps在计算的时候需要考虑字符集、NULL值列表 和 变长字段列表 三部分。 行溢出后MySQL是怎么处理的 如果一个数据页存不了一条记录 InnoDB存储引擎会自动将溢出的数据存放在 [溢出页] 中
Compact 行格式当发生行溢出时在记录的真实数据处只会保存该列的一部分数据而把剩余的数据放在「溢出页」中然后真实数据处用 20 字节存储指向溢出页的地址从而可以找到剩余数据所在的页。
Compressed 和 Dynamic 这两种格式采用完全的行溢出方式记录的真实数据处不会存储该列的一部分数据只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。
