mysql默认的是表锁还是行锁

转载

mob64ca140c75c7 2024-07-12 02:12:46

文章标签 mysql默认的是表锁还是行锁 mysql innodb 行格式 row_format 文章分类 MySQL 数据库

简介

我们知道读写磁盘的速度非常慢，和内存读写差了几个数量级，所以当我们想从表中获取某些记录时， InnoDB 存储引擎需要一条一条的把记录从磁盘上读出来么？

不，那样会慢死，InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。

也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为 行格式 或者 记录格式 。

InnoDB 存储引擎有4种不同类型的行格式，分别是 Compact 、 Redundant 、
Dynamic 和 Compressed 行格式。

指定行格式的语法

以在创建或修改表的语句中指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

查询InnoDB默认行格式

show variables like 'innodb_default_row_format';

mysql默认的是表锁还是行锁_mysql

COMPACT行格式

格式示意图：

mysql默认的是表锁还是行锁_行格式_02

从图中可以看出来，一条完整的记录其实可以被分为 记录的额外信息 和 记录的真实数据 两大部分。

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是 变长字段长度列表 、 NULL值列表 和 记录头信息。

变长字段长度列表

MySQL 支持一些变长的数据类型，比如VARCHAR(M) 、 VARBINARY(M) 、各种 TEXT 类型，各种 BLOB 类型，我们也可以把拥有这些数据类型的列称为变长字段，变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，这样才不至于把 MySQL 服务器搞懵。

变长字段占用的存储空间分为两部分 真正的数据内容 和 占用的字节数。

NULL值列表

表中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所
以 Compact 行格式把这些值为 NULL 的列统一管理起来，存储到 NULL 值列表中。

记录头信息

记录头信息是由固定的 5 个字节组

成。 5 个字节也就是 40 个二进制位，不同的位代表不同的意思，如图：

mysql默认的是表锁还是行锁_mysql_03

名称	大小（单位：bit）	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除，值为 0 的时候代表记录并没有被删除，为 1 的时候代表记录被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型， 0 表示普通记录， 1 表示B+树非叶子节点记录， 2 表示最小记录， 3表示最大记录
next_record	16	表示下一条记录的相对位置

delete_mask 为1时，被删除的记录还在页中么？
是的，你以为它删除了，可它还在真实的磁盘上。
被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的 垃圾链表 ，在这个链表中的记录占用的空间称之为所谓的 可重用空间 ，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

delete_mask位设置为1和将被删除的记录加入到垃圾链表中其实是两个阶段。

heap_no 属性表示当前记录在本页中的位置。如下图：

mysql默认的是表锁还是行锁_行格式_04

怎么不见 heap_no 值为 0 和 1 的记录呢？

InnoDB自动给每个页里边儿加了两个记录，称为 伪记录 或者 虚拟记录 。这两个伪记录一个代表最小记录 ，一个代表最大记录。

record_type属性表示当前记录的类型，一共有4种类型的记录：

0 表示普通记录
1 表示B+树非叶节点记录
2 表示最小记录
3 表示最大记录

next_record表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

比方说第一条记录的 next_record 值为 32 ，意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据。

mysql默认的是表锁还是行锁_row_format_05

记录的真实数据

记录的真实数据除了自定义的列的数据
以外， MySQL 会为每个记录默认的添加一些列（也称为隐藏列），具体的列如下：

列名	是否必须	占用空间	描述
DB_ROW_ID	否	6 字节	行ID，唯一标识一条记录
DB_TRX_ID	是	6 字节	事务ID
DB_ROLL_PTR	是	7 字节	回滚指针

InnoDB 表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为row_id 的隐藏列作为主键。

Redundant行格式

Redundant 行格式是MySQL5.0 之前用的一种行格式，也就是说它已经非常老了。

mysql默认的是表锁还是行锁_innodb_06

注意 Compact 行格式的开头是 变长字段长度列表 ，而 Redundant 行格式的开头是 字段长度偏移列表 ，与
变长字段长度列表.

记录头信息

Redundant 行格式的记录头信息占用 6 字节， 48 个二进制位，这些二进制位代表的意思如下：

名称	大小（单位：bit）	描述
预留位1	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
n_field	10	表示记录中列的数量
next_record	16	表示下一条记录的相对位置
与 Compact 行格式的记录头信息对比来看，有两处不同：

Redundant 行格式多了 n_field 和 1byte_offs_flag 这两个属性。
Redundant 行格式没有 record_type 这个属性。

Dynamic和Compressed行格式

Dynamic 和 Compressed 行格式和 Compact 行格式相似，在处理 行溢出 数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

Compressed 行格式和 Dynamic 不同的一点是， Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间。

行数据溢出

MySQL 是以页为基本单位来管理存储空间的，我们的记录都会被分配到某个页中存储。而一个页的大小一般是 16KB ，也就是 16384 字节（2的14次方），而一个 VARCHAR(M) 类型的列就最多可以存储 65532 个字节，这样就可能造成一个页存放不了一条记录的尴尬情况。

页是 MySQL 中磁盘和内存交互的基本单位，也是 MySQL 是管理存储空间的基本单位。
一个页一般是 16KB ，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为 行溢出 。
MySQL 对一条记录占用的最大存储空间是有限制的，除了 BLOB 或者 TEXT 类型的列之
外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节（2的16次方）。所以 MySQL 服务器建议我们把存储类型改为 TEXT 或者 BLOB 的类型。这个 65535 个字节除了列本身的数据之外，还包括一些其他的数据（ storage overhead ），比如说我们为了存储一个 VARCHAR(M) 类型的列，其实需要占用3部分存储空间：真实数据、真实数据占用字节的长度、NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分存储空间。

在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在 记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后 记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

mysql默认的是表锁还是行锁_innodb_07