翼度科技»论坛 编程开发 mysql 查看内容

MySQL的varchar存储原理:InnoDB记录存储结构

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
摘要:varchar(M) 能存多少个字符,为什么提示最大16383?innodb怎么知道varchar真正有多长?记录为NULL,innodb如何处理?某个列数据占用的字节数非常多怎么办?影响每行实际可用空间的因素有哪些?本篇围绕innodb默认行格式dynamic来说说原理。
本文分享自华为云社区《MySQL的varchar水真的太深了——InnoDB记录存储结构》,作者:砖业洋__ 。
1. InnoDB是干嘛的?

InnoDB是一个将表中的数据存储到磁盘上的存储引擎。
2. InnoDB是如何读写数据的?

InnoDB处理数据的过程是发生在内存中的,需要把磁盘中的数据加载到内存中,如果是处理写入或修改请求的话,还需要把内存中的内容刷新到磁盘上。
读写磁盘的速度非常慢,和内存读写差了几个数量级,所以当我们想从表中获取某些记录时,InnoDB存储引擎将数据划分为若干个页,以页作为磁盘和内存之间交互的基本单位,InnoDB中页的大小默认为 16 KB。也就是在一般情况下,一次最少从磁盘中读取16KB的内容到内存中,或者一次最少把内存中的16KB内容刷新到磁盘中。
所以当你用postman测试一个HTTP分页查询接口(每页10条数据)时,发现第一次打印耗时300 ~ 400ms,往后不停的查找下一页10条数据时都是30 ~ 40ms,原因就是第一次请求接口时,读数据库的时候需要读磁盘,从磁盘加载16KB的数据到内存,往后HTTP请求每次查10条数据的时候都是从内存中获取,没有再读磁盘,除非在内存中的16KB的数据中找不到,才会再次读磁盘获取下一个16KB的数据到内存中。(我们不讨论mysql 8.0舍弃的查询缓存特性,我测试过mysql 5.7中关闭了查询缓存,也仍然是第一次慢,后续查询很快,查询时间相差大概10倍的样子)
温馨提示:分页查询和数据库的一页16KB中的"页"是两个概念。
总结:由于磁盘I/O速度相对内存来说较慢,因此第一次查询可能会比较耗时。一旦数据被加载到内存中,后续的查询就可以直接从内存中读取数据,这样的速度要比从磁盘读取数据快得多。这就解释了为什么第一次查询可能会比后续的查询慢。
查看磁盘和内存之间进行数据交换的页有多大
注意:innodb_page_size变量在服务器运行过程中不可以更改,只能在第一次初始化MySQL数据目录时指定。所以页在运行时的大小不可更改。
3. varchar疑问千千万——InnoDB行格式

看到这里,你一定有着和我相同的疑问,比如varchar(255)后面这个最大长度应该怎么选择呢?为什么不能varchar(65535)而最大只能varchar(16383)呢?我来带你看!
我们平时是以记录为单位来向表中插入数据的,这些记录在磁盘上的存放方式也被称为行格式或者记录格式。行格式有4种,分别是Dynamic、Compact、Redundant和Compressed
MySQL 5+默认行格式都是Dynamic, 在MySQL 5 和 MySQL 8经过验证确实是的。
  1. SHOW VARIABLES LIKE "innodb_default_row_format"
复制代码
大家在业务中和平时使用中都几乎没有修改过或者注意过InnoDB行格式,那么我就只重点讲默认行格式dynamic,让大家更深层次理解平时开发中的varchar。
请记住这个表结构,后面会围绕这个来讲
  1. CREATE TABLE test (
  2. c1 VARCHAR(10),
  3. c2 VARCHAR(10) NOT NULL,
  4. c3 CHAR(10),
  5. c4 VARCHAR(10)) CHARSET = utf8mb4;
复制代码
现在业务数据库字符集都是utf8mb4,我就以这个来讲,把理解难度降到最低。
  1. INSERT INTO test ( c1, c2, c3, c4 )
  2. VALUES('aaaa', '你好啊', 'cc', 'd'),('eeee', 'fff', NULL, NULL);
复制代码
现在,表中的记录就是这样
3.1 dynamic——innodb默认行格式

关于记录的额外信息这部分,是服务器为了描述这条记录而不得不额外添加的一些信息,这些额外信息分为3类,分别是变长字段长度列表、NULL值列表和记录头信息。
在这里我只讲变长字段长度列表、NULL值列表。因为记录头信息非常的绕和本篇没多大关系。
3.2 innodb怎么知道varchar真正有多长?——变长字段长度列表

一些变长的数据类型,比如VARCHAR(M)、各种TEXT类型,各种BLOB类型,变长数据类型的字段中存储多少字节的数据是不固定的,在存储真实数据的时候需要把这些数据占用的字节数也存起来。
就像设计String类型,不仅仅是存放真实数据的char数组,还有length变量去记录字符串长度。又比如input输入框最大限制500字,但是你还得有一个变量去统计真实在输入框内有多少字符。同理,varchar也有记录真实数据长度的变量(假设为L,后文沿用方便描述),L表示varchar真实占用的字节数,innodb最多分配2个字节去表示这个L,就像unsigned short类型,2个字节,寄存器最多只有16位来让你存这个长度,所以L记录范围是2^16 - 1 = 65535。
这些变长字段(比如varchar)占用的存储空间分为两部分:

  • 真正的数据内容部分,放在对应的列
  • 真实占用的字节数,放在变长字段列表部分
我们拿test表中的第一条记录来举个例子。因为test表的c1、c2、c4列都是VARCHAR(10)类型的,说明最大10个字符,所以这三个列的值的长度都需要保存在记录开头处,因为test表中的各个列都使用的是utf8mb4字符集,每个字符最大需要4个字节来进行编码(不使用utf8而是utf8mb4是因为可能存储emoji表情,如果只是文字,utf8就足够),来看一下第一条记录各变长字段内容的长度:
怎么确定这些字段有多少字节?
比如这里c2的"你好啊",使用如下sql可以确定
  1. SELECT LENGTH(c2) from test where c1='aaaa';
复制代码
各变长字段数据占用的字节数按照列的顺序逆序存放!!
由于第一行记录中c1、c2、c4列中的字符串都比较短,也就是说varchar真实占用的字节数比较小,L用1个字节(8个bit位) 就可以表示,但是如果varchar真实占用的字节数比较多,L可能就需要用2个字节(16个bit位) 来表示。到底varchar能存多少字节呢?继续往下看。
3.3 varchar(M) 能存多少个字符,为什么提示最大16383?

首先要理解varchar(M)的M是说字符个数,而不是字节。
为什么不能varchar(20000)之类的,是20000个字符放不下吗?
为什么提示只能最大16383个字符呢?这个数字是怎么算出来的?
这个我就得和你好好唠嗑了!
varchar是变长的,varchar(64) 能存放0~64个字符不等,并不一定是存了最大64个字符,谁知道这个类型到底存了几个字符呢?innodb设计的时候,就已经考虑到了,不过是用字节作为单位,后续我们可以根据对应字符集转变为字符来理解,innodb必须记录变长字段varchar真实占用的字节数L。前面说过了,innodb最多分配2个字节(16个bit位)的空间去记录这个L。
InnoDB有它的一套规则,我们引入W、M和L这几个符号:
1.假设某个字符集中最多需要W字节来表示一个字符

  • utf8mb4字符集中的W就是4
  • utf8字符集中W就是3
  • gbk字符集中的W就是2
  • ascii字符集中的W就是1。
2.对于变长类型VARCHAR(M)来说,这种类型表示能存储最多M个字符(注意是字符不是字节)
所以这个类型能表示的字符串最多占用的字节数就是M × W。
3.假设它实际存储的字符串占用的字节数是L。
来看极限边界情况,innodb为了记录一下varchar真实存储多少个字节,最多分配2个字节的空间去记录,2个字节16个比特位,全部为1,最大能记录的数字是2^16-1是65535个,innodb最大能记录varchar占用的字节数就是65535个,utf8mb4字符集一个字符是最大是4个字节,65535 / 4 = 16383.75,只要varchar字符数不超过16383个,innodb就可以记录真实占用的长度L,再多就记录不了了!所以就能解释刚刚的图了,varchar(20000)不行,最大也就16383个字符
但是!这里强调是有但是的!
行最大长度是65535字节,行里面有很多东西,包括变长字段列表、NULL值列表、记录头信息。你得考虑该字段如果允许为NULL,NULL值列表会占用一个字节(只要没超过8个字段),每一列字段的变长字段实际长度会花费1~2个字节,如果该字段的数据太大,会变成溢出列,该字段的数据会分成很多行存储(后面会讲,你可以看完NULL值列表和溢出列后再回来看这个例子)。所以即便提示16383个字符,你也绝对不可能存到16383。
我做了个测试
  1. create table t2 ( name varchar(16383))charset=utf8mb4;
复制代码
不断往这个字段添加字符保存测试,最后发现,这些字符总长度到极限也就是48545字节。
如果超过就会报错
这里48545个字节,再多一个字符就会报错,远不到65535字节,差了1W多字节。主要是因为溢出列的原因,数据分散在不同的行中,所以,很长的数据,建议往text类型考虑。这个现象可以看出,varchar(M)的M很大,实际是达不到M这个边界值的。
我使用的是英文字母测试而不是中文字符,大部分不是4字节的,所以能够存储更多的字符。如果考虑到额外的元数据,实际能够存储的VARCHAR字符数会更少,关于影响每行的实际可用空间有哪些因素,请接着往下看后面小节。
下面说明一下规则(讲解中字符集用utf8mb4,W=4)

<strong>规则一:如果允许存储的最大字节数M × W

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具