详解MySQL的数据行和行溢出机制

一、行 有哪些格式？

你可以像下面这样看一下你的MySQL行格式设置。

其实MySQL的数据行有两种格式，一种就是图中的 Compact格式，还有一种是Redundant格式。

Compact是一种紧凑的行格式，设计的初衷就是为了让一个数据页中可以存放更多的数据行。

你品一品，让一个数据页中可以存放更多的数据行是一个多么激动人心的事，MySQL以数据页为单位从磁盘中读数据，如果能做到让一个数据页中有更多的行，那岂不是使用的空间变少了，且整体的效率直线飙升？

官网介绍：Compact能比Redundant格式节约20%的存储。

Compact从MySQL5.0引入，MySQL5.1之后，行格式默认设置成 Compact 。所以本文描述的也是Compact格式。

二、紧凑的行格式长啥样？

你肯定晓得表中有的列允许为null，有的列是变长的varchar类型。

那Compact行格式是如何组织描述这些信息的呢？如下图：

每部分包含的数据可能要比我上面标注的1、2、3还要多。

为了给大家更直观的感受和理解我只是挑了一部分展示给大家看。

三、MySQL单行能存多大体量的数据？

在MySQL的设定中，单行数据最大能存储65535byte的数据（注意是byte，而不是字符）

但是当你像下面这样创建一张数据表时却发生了错误：

MySQL不允许创建一个长度为65535byte的列，因为数据页中每一行中都有我们上图提到的隐藏列。

所以将varchar的长度降低到65532byte即可成功创建该表

注意这里的65535指的是字节，而不是字符。

所以如果你将charset换成utf8这种编码格式，那varchar(N)中的N其实指的N个字符，而不是N个byte。所以如果你像下面这样创建表就会报错。

假如encode=utf8时三个byte表示一个字符。那么65535 / 3 = 21845个字符。

四、Compact格式是如何做到紧凑的？

MySQL每次进行随机的IO读

默认情况下，数据页的大小为16KB。数据页中存储着数行。

那就意味着一个数据页中能存储越多的数据行，MySQL整体的进行的IO次数就越少？性能就越快？

Compact格式的实现思路是：当列的类型为VARCHAR、 VARBINARY、 BLOB、TEXT时，该列超过768byte的数据放到其他数据页中去。

如下图：

看到这里来龙去脉是不是很清晰了呢？

MySQL这样做，有效的防止了单个varchar列或者Text列太大导致单个数据页中存放的行记录过少而让IO飙升的窘境且占内存的。

五、什么是行溢出？

那什么是行溢出呢？

如果数据页默认大小为16KB，换算成byte： 16*1024 = 16384 byte

那你有没有发现，单页能存储的16384byte和单行最大能存储的 65535byte 差了好几倍呢？

也就是说，假如你要存储的数据行很大超过了65532byte那么你是写入不进去的。假如你要存储的单行数据小于65535byte但是大于16384byte，这时你可以成功insert，但是一个数据页又存储不了你插入的数据。这时肯定会行溢出！

其实在MySQL的设定中，发生行溢出并不是达到16384byte边缘才会发生。

对于varchar、text等类型的行。当这种列存储的长度达到几百byte时就会发生行溢。

六、行 如何溢出？

还是看这张图：

在MySQL设定中，当varchar列长度达到768byte后，会将该列的前768byte当作当作prefix存放在行中，多出来的数据溢出存放到溢出页中，然后通过一个偏移量指针将两者关联起来，这就是行溢出机制。

七、思考一个问题

不知道你有没有想过这样一个问题：

首先你肯定知道，MySQL使用的是B+Tree的聚簇索引，在这棵B+Tree中非叶子节点是只存索引不存数据，叶子节点中存储着真实的数据。同时叶子结点指向数据页。

那当单行存不下的时候，为啥不存储在两个数据页中呢？就像下图这样～。

单个节点存储下，我用多个节点存总行吧！说不定这样我的B+Tee还能变大长高（这其实是错误的想法）

这个错误的描述对应的脑图如下：

那MySQL不这样做的原因如下：

MySQL想让一个数据页中能存放更多的数据行，至少也得要存放两行数据。否则就失去了B+Tree的意义。B+Tree也退化成一个低效的链表。

你可以品一下这句蓝色的话，他说的每个数据页至少要存放两行数据的意思不是说 数据页不能只存一行。你确确实实可以只往里面写一行数据，然后去吃个饭，干点别的。一直让这个数据页中只有一行数据。

这句话的意思是，当你往这个数据页中写入一行数据时，即使它很大将达到了数据页的极限，但是通过行溢出机制。依然能保证你的下一条数据还能写入到这个数据页中。

正确的脑图如下：

参考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format.html

https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

作者： 赐我白日梦

出处：https://www.cnblogs.com/ZhuChangwu/p/14035330.html

以上就是详解MySQL的数据行和行溢出机制的详细内容，更多关于MySQL 数据行和行溢出的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • 为什么说MySQL单表数据不要超过500万行

    今天,探讨一个有趣的话题:MySQL 单表数据达到多少时才需要考虑分库分表?有人说 2000 万行,也有人说 500 万行.那么,你觉得这个数值多少才合适呢? 曾经在中国互联网技术圈广为流传着这么一个说法:MySQL 单表数据量大于 2000 万行,性能会明显下降.事实上,这个传闻据说最早起源于百度.具体情况大概是这样的,当年的 DBA 测试 MySQL性能时发现,当单表的量在 2000 万行量级的时候,SQL 操作的性能急剧下降,因此,结论由此而来.然后又据说百度的工程师流动到业界的其它公司,

  • 简单了解操作mysql数据库的命令行神器mycli

    简单了解操作mysql数据库的命令行神器mycli

    GitHub 可谓各种神器都有,今天发现一个操作数据库的命令行工具,可以自动补全和语法高亮,以前在终端敲 SQL 的时候,你不仅要记住表名.字段名,各种函数都需要记住才能敲出一条完整的语句来,否则就是先 Google 百度查文档. 而有了mycli 这个命令行辅助工具之后,省心不少.可以先看下效果图: 如果自己不去尝试,可能感受没那么强烈,所以不如自己尝试一下. 正常情况下直接用 pip 命令就可以安装 pip install mycli 如果安装出错了,可以参考下官方文档,或者升级下你的pip

  • MySQL数据库Event定时执行任务详解

    一.背景 由于项目的业务是不断往前跑的,所以难免数据库的表的量会越来越庞大,不断的挤占硬盘空间.即使再大的空间也支撑不起业务的增长,所以定期删除不必要的数据是很有必要的.在我们项目中由于不清理数据,一个表占的空间竟然达到了4G之多.想想有多可怕... 这里介绍的是用MySQL 建立一个定时器Event,定期清除掉之前的不必要事件. 二.内容 #1.建立存储过程供事件调用 delimiter// drop procedure if exists middle_proce// create proc

  • Java对MySQL数据库进行连接、查询和修改操作方法

    0. 一般过程: (1) 调用Class.forName()方法加载驱动程序. (2) 调用DriverManager对象的getConnection()方法,获得一个Connection对象. (3) 创建一个Statement对象,准备一个SQL语句,这个SQL语句可以是Statement对象(立即执行的的语句).PreparedStatement语句(预编译的语句)或CallableStatement对象(存储过程调用的语句). (4) 调用excuteQuery()等方法执行SQL语句,

  • 解决Windows10下mysql5.5数据库命令行中文乱码问题

    重置系统后,很久之前安装的MySQL数据库出现了控制台查询中文乱码问题,时间太久早已经不记得怎么设置了. 网上查询了许多,各种设置,重启数据库后仍然是中文乱码. 可能是我搜索方式不对,最后还好已经解决,特开一处女博记录一下: 打开安装目录下的my.ini文件 我的是:C:\Program Files\MySQL\MySQL Server 5.5下面,修改如下: [client] default-character-set=utf8 port=3306 [mysql] #网上下面这个 gbk 很多

  • IDEA使用properties配置文件进行mysql数据库连接的教程图解

    Properties类 简要概括: Properties类是 键和值均为字符串的可以永久存储到文件中的key-value集合 java.util.Properties类表示一组持久的属性,是Hashtable的子类. Properties可以保存到流中或从流中加载,可以保存到后缀名为properties的文件中. 属性列表中的每个键及其对应的值都是一个字符串. 即键和值都是String类型. 因为Properties从继承Hashtable时, put种putAll方法可应用于Propertie

  • PHP使用mysql_fetch_row查询获得数据行列表的方法

    本文实例讲述了PHP使用mysql_fetch_row查询获得数据行列表的方法.分享给大家供大家参考.具体分析如下: 这里使用mysql_fetch_row从mysql数据库中查询数据,并保存到list中 语法如下: array mysql_fetch_row (resource $Result_Set) 如果执行成功,则返回list列表,如果失败,返回false 下面是演示代码 <?php $UserName = 'abc'; $Password = '1234'; $DbHandle = m

  • php使用mysqli和pdo扩展，测试对比mysql数据库的执行效率完整示例

    php使用mysqli和pdo扩展，测试对比mysql数据库的执行效率完整示例

    本文实例讲述了php使用mysqli和pdo扩展,测试对比mysql数据库的执行效率.分享给大家供大家参考,具体如下: <?php /** * 测试pdo和mysqli的执行效率 */ header("Content-type:text/html;charset=utf-8"); //通过pdo链接数据库 $pdo_startTime = microtime(true); $pdo = new PDO("mysql:host=localhost;dbname=test&

  • MySQL执行update语句和原数据相同会再次执行吗

    背景 本文主要测试MySQL执行update语句时,针对与原数据(即未修改)相同的update语句会在MySQL内部重新执行吗? 测试环境 MySQL5.7.25 Centos 7.4 binlog_format为ROW 参数 root@localhost : (none) 04:53:15> show variables like 'binlog_row_image'; +------------------+-------+ | Variable_name | Value | +------

  • 5个MySQL GUI工具推荐，帮助你进行数据库管理

    MySQL的数据库管理工具非常多,有哪些优秀的GUI工具可以帮助提高工作效率?不妨看一看这5个MySQL GUI工具. 1.Navicat for MySQL Navicat for MySQL是数据库管理工具,与MySQL,MariaDB和云数据库兼容,是目前非常受欢迎的数据库前端用户界面工具.Navicat提供多达7种语言,它的功能丰富,能帮助简化数据库开发过程,并在执行管理任务时提高工作效率.不过该工具是付费的,有提供14天的免费试用. Navicat for MySQL有什么特点和功能?

  • mysql如何利用binlog进行数据恢复详解

    前言 最近线上误操作了一个数据,由于是直接修改的数据库,所有唯一的恢复方式就在mysql的binlog.binlog使用的是ROW模式,即受影响的每条记录都会生成一个sql.同时利用了binlog2sql项目. MySQL Binary Log也就是常说的bin-log, ,是mysql执行改动产生的二进制日志文件,其主要作用有两个: * 数据回复 * 主从数据库.用于slave端执行增删改,保持与master同步. binlog基本配置和格式 binlog基本配置 binlog需要在mysql

随机推荐