MySQL的实战系列:大字段如何优化

MySQL的实战系列:大字段如何优化

# 背景

线上发现一张表,1亿的数据量,物理大小尽然惊人的大,1.2T
最终发现,原来有很多字段,10个VARCHAR,1个文本
这么大的表,会给运维带来很大的痛苦:DDL咋办恢复咋办备份咋办???

基本知识:InnoDB磁盘格式的InnoDB存储架构

蓝图:数据库 - >表空间 - >页面 - >行 - >列

InnoDB物理结构存储结构

database_file

InnoDB逻辑存储结构

表空间

InnoDB页面存储结构

页类型

数据页(B-tree Node)
撤消页面(撤消日志页面)
系统页面(系统页面)
事务数据页(事务系统页面)
插入缓冲位图页(插入缓冲页面)
未压缩的二进制大对象页面(未压缩的BLOB页面) )
压缩的二进制大对象页(压缩BLOB页面)

页大小

默认16K(若果没有特殊情况,下面介绍的都是默认16K大小为准)
一个页内必须存储2行记录,否则就不是B +树,而是链表了

结构图

innodb_page

InnoDB row存储结构

行文件格式总体规划图

row_file_format

行fomat为紧凑型的结构图

紧凑

行fomat为冗余的结构图

不常用

compress&dynamic与Compact的区别之处

动态

字段之字符串类型

char(N)vs varchar(N)

不管是char,还是varchar,在compact row-format格式下,NULL都不占用任何存储空间
在多字节字符集的情况下,CHAR vs VARCHAR的实际行存储基本没区别
CHAR不管是否是多字符集,对未能占满长度的字符还是会填充为0x20
规范中:对字符和VARCHAR可以不做要求

varchar(N):255 vs 256

当实际长度大于255的时候,变长字段长度列表需要用两个字节存储,也就意味着每一行数据都会增加1字节个
实测下来存储空间增长并不算大,且性能影响也不大,所以,尽量在256之内吧

varchar(N)&char(N)的最大限制

char的最大限制是:N <= 255
varchar的最大限制是:N <= 65535,注意官方文档说的是N是字节,并且说的是一行的所有字段的总和小于65535,而varchar(N)中的ñ表示的是字符。
测试后发现,65535并不是最大限制,最大的限制是65532

off-page:行溢出

假设创建了一张表,里面有一个字段是一个varchar(30000),innoDB的页才16384个字节,如何存储的下呢?所以行溢出就来了嘛

可以通过姜承尧写的工具查看
其中溢出的页有未压缩的BLOB页面:243453

溢出的数据不再存储在B + tree中
溢出的数据使用的是解压缩BLOB页面,并且存储独享,这就是存储越来越大的真正原因
通过下面的测试,你会发现,t_long插入的数据仅仅比t_short多了几个字节,但是最终的存储却是2~3倍的差距

原则:只要一行记录的总和超过8k,就会溢出。
所以:varchar(9000)或者varchar(3000)+ varchar(3000)+ varchar(3000),当实际长度大于8k的时候,就会溢出
所以:Blob ,文字,一行数据如果实际长度大于8K会溢出,如果实际长度小于8K则不会溢出,并非所有的斑点,文本都会溢出

多个大字段会导致多次关闭页

如何对大字段进行优化

如果有多个大字段,尽量序列化后,存储在同一列中,避免多次off-page
将文等大字段从主表中拆分出来,a)存储到键值中b)存储在单独的一张子表中,压缩并且
必须保证一行记录小于8K