第07章 InnoDB数据存储结构【2.索引及调优篇】【MySQL高级】3

简介: 第07章 InnoDB数据存储结构【2.索引及调优篇】【MySQL高级】3

从图中可以看出来,删除第2条记录前后主要发生了这些变化:

-第2条记录并没有从存储空间中移除,而是把该条记录的delete_mask值设置为1。.

-第2条记录的next_record值变为了0,意味着该记录没有下一条记录了。-第1条记录的next_record指向了第3条记录。

-最大记录的n_owned值从5变成了4。


为什么是4,在Page Drectory(页目录)中解释

所以,不论我们怎么对页中的记录做增删改操作,InnoDB始终会维护一条记录的单链表,链表中的各个节点是按照主键值由小到大的顺序连接起来的。


备注




演示:添加操作添加操作:

主键值为2的记录被我们删掉了,但是存储空间却没有回收,如果我们再次把这条记录插入到表中,会发生什么事呢?

mysql> INSERT INTO page_demo VALUES(2,200, "tong");
Query OK, 1 row affected (0.o0 sec)

我们看一下记录的存储情况:


直接复用了原来被删除记录的存储空间。

说明:

当数据页中存在多条被删除掉的记录时,这些记录的next_record属性将会把这些被删除掉的记录组成一个垃圾链表,以备之后重用这部分存储空间。


备注






继续讲页的内部结构:第3部分


④ 记录的真实数据

记录的真实数据除了我们自己定义的列的数据以外,还会有三个隐藏列:

实际上这几个列的真正名称其实是:DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。


一个表没有手动定义主键,则会选取一个Unique键作为主键,如果连Unique键都没

有定义的话,则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

事务ID和回滚指针在后面的《第14章_MySQL事务日志》章节中讲解。

举例:分析Compact行记录的内部结构;

CREATE TABLE mytest(
  col1 VARCHAR(10),
  col2 VARCHAR(10),
  col3 CHAR(10),
  col4 VARCHAR(10)
)ENGINE=INNODB CHARSET=LATIN1 ROW_FORMAT=COMPACT;
INSERT INTO mytest
VALUES('a','bb', 'bb' , 'ccc');
INSERT INTO mytest
VALUES( 'd','ee' ,'ee','ff');
INSERT INTO mytest
VALUES( 'd' ,NULL,NULL,'fff' );


找到数据表存储位置


拓展:

可以看到,mysql存储位置

mysql --version
D:\ROUTE\mysql8\bin\mysql.exe  Ver 8.0.25 for Win64 on x86_64 (MySQL Community Server - GPL)

用nodepad++ 打开mytest.ibd

在Windows操作系统下,可以选择通过程序UltraEdit打开表空间文件mytest.ibd这个二进制文件。内容如下:

该行记录从0000c078开始,若整理一下,相信大家会有更好的理解:



03 02 01          /*变长字段长度列表,逆序*/
00            /*NULL标志位,第一行没有NULL值*/
00 00 10 00 2c      /*Record Header*/
00 00 00 2b 68 00   /*RowID*/
00 00 00 00 06 05   /*TransactionID*/
80 00 00 00 32 01 10  /*Roll Pointer*/
61            /*列1数据'a'*/
62 62           /*列2数据'bb'*/
62 62 八个20      /*列2数据'bb'*/  char 定长字段  16进制的20=10进制32,asii码对应空格
63 63 63        /*列4数据'ccc*/

第二行将不做整理,除了RowID不同外,它和第一行大同小异,现在来分析有NULL值的第三行:

-------------------------------------
03 01           /*变长字段长度列表,逆序*/
06            /*NULL标志位,第三行有NULL值*/  0110 第2 3列是null
00 00 20 ff 98      /*Record Header*/
00 00 00 2b 68 02   /*RowID*/
00 00 00 00 06 07   /*TransactionID*/
80 00 00 00 32 01 10  /*Roll Pointer*/
64            /*列1数据'd'*/
66 66 66        /*列4数据'fff*/
-------------------------------------

第三行有NULL值,因此NULL标志位不再是00而是06,转换成二进制为00000110,为1的值代表第2列和第3列的数据为NULL。在其后存储列数据的部分,用户会发现没有存储NULL列,而只存储了第1列和第4列非NULL的值。


因此这个例子很好地说明了:不管是CHAR类型还是VARCHAR类型,在compact格式下NULL值都不占用任何存储空间。


备注







3.3 Dynamic和Compressed行格式

Dynamic和Compressed行格式


备注




1 行溢出

InnoDB存储引擎可以将一条记录中的某些数据存储在真正的数据页面之外。

很多DBA喜欢MySQL数据库提供的VARCHAR(M)类型,认为可以存放65535字节。这是真的吗?如果我们使用ascii字符集的话,一个字符就代表一个字节,我们看看VARCHAR(65535)是否可用。


CREATE TABLE varchar_size_demo(
  c VARCHAR(65535)
)CHARSET=ascii RoW_FORMAT=Compact;

结果如下:

ERROR 1118(42000):Row size too large.The maximum row size for the used tabletype, not counting BLOBs, is 65535.This includes storage overhead, check the manual.You have to change some columns to TEXT or BLOBs

报错信息表达的意思是:MySQL对一条记录占用的最大存储空间是有限制的,除BLOB或者TEXT类型的列之外,其他所有的列(不包括隐藏列和记录头信息)占用的字节长度加起来不能超过65535个字节。


这个65535个字节除了列本身的数据之外,还包括一些其他的数据,以Compact行格式为例,比如说我们为了存储一个VARCHAR(M)类型的列,除了真实数据占有空间以外,还需要记录的额外信息。


如果该VARCHAR类型的列没有NOT NULL属性,那最多只能存储65532个字节的数据,因为变长字段的长度占用2个字节,NULL值标识需要占用1个字节。

# 执行成功
CREATE TABLE varchar_size_demo(
  c VARCHAR(65532) # +2个字节的变长字段的长度+NULl值标识
)CHARSET=ascii RoW_FORMAT=Compact;

如果有not null属性,那么就不需要NULL值标识,也就可以多存储一个字节,即

CREATE TABLEvarchar_size_demo1 (
  c VARCHAR(65533) NOT NULL #65533+2个字节的变长字段的长度)
)CHARSET=ASCII ROW_FORMAT=COMPACT;

通过上面的案例,我们可以知道一个页的大小一般是16KB,也就是16384字节,而一个VARCHAR(M)类型的列就最多可以存储65533个字节,这样就可能出现一个页存放不了一条记录,这种现象称为行溢出。


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


这称为页的扩展,举例如下:


备注






2 Dynamic和Compressed行格式

在MySQL 8.0中,默认行格式就是Dynamic,Dynamic、Compressed行格式和Compact行格式挺像,只不过在处理行溢出数据时有分歧:


Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图,在数据页中只存放20个字节的指针(溢出页的地址),实际的数据都存放在Off Page(溢出页)中。

Compact和Redundant两种格式会在记录的真实数据处存储一部分数据(存放768个前缀字节)。

Compressed行记录格式的另一个功能就是,存储在其中的行数据会以zlib的算法进行压缩,因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储


备注




3.4 Redundant行格式

Redundant是MySQL 5.0版本之前InnoDB的行记录存储方式,MySQL 5.0支持Redundant是为了兼容之前版本的页格式。

现在我们把表record_test_table的行格式修改为Redundant:

ALTER TABLE record_test_table ROW_FORMAT=Redundant;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0


从上图可以看到,不同于Compact行记录格式,Redundant行格式的首部是一个字段长度偏移列表,同样是按照列的顺序逆序放置的。

下边我们从各个方面看一下Redundant行格式有什么不同的地方。


备注




1 字段长度偏移列表

注意Compact行格式的开头是变长字段长度列表,而Redundant行格式的开头是字段长度偏移列表,与变长字段长度列表有两处不同:


少了“变长”两个字:Redundant行格式会把该条记录中所有列(包括隐藏列)的

长度信息都按照逆序存储到字段长度偏移列表。

多了“偏移”两个字:这意味着计算列值长度的方式不像Compact行格式那么直观,它是采用两个相邻数值的差值来计算各个列值的长度。

举例:比如第一条记录的字段长度偏移列表就是:

2B 25 1F 1B 13 oC06


因为它是逆序排放的,所以按照列的顺序排列就是:

06 0C 13 17 1A 24 25

按照两个相邻数值的差值来计算各个列值的长度的意思就是:


第一列(row_id)的长度就是Ox06个字节,也就是6个字节。

第二列(transaction_id)的长度就是(OxOC - ox06)个字节,也就是6个字节。

第三列(roll_pointer)的长度就是(Ox13 - oxOC)个字节,也就是7个字节。

第四列(col1)的长度就是(Ox1B - Ox13)个字节,也就是8个字节。

第五列(col2)的长度就是(Ox1F - Ox1B)个字节,也就是4个字节。

第六列(col3)的长度就是(Ox25 - Ox1F)个字节,也就是6个字节。

第七列(col4)的长度就是(Ox2B - 0x25)个字节,也就是6个字节。


备注





相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
387 4
|
存储 关系型数据库 MySQL
介绍MySQL的InnoDB引擎特性
总结而言 , Inno DB 引搞 是 MySQL 中 高 性 能 , 高 可靠 的 存 储选项 , 宽泛 应用于要求强 复杂交易处理场景 。
550 15
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
430 2
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
500 9
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
440 12
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
777 158
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
2000 152

推荐镜像

更多