【数据库】数据表存在大数据字段时的优化

数据库引擎

InnoDB

页大小

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

字段之字符串类型

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个字节，如何存储的下呢？所以行溢出就来了嘛

溢出有什么危害

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

什么情况下会溢出

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

多列总和大字段vs一列大字段

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

对TEXT/BLOB型字段存取优化

mysql的 io 以page为单位，因此不必要的数据（大字段）也会随着需要操作的数据一同被读取到内存中来，这样带来的问题由于大字段会占用较大的内存（相比其他小字段），使得内存利用率较差，造成更多的随机读取。从上面的分析来看，我们已经看到性能的瓶颈在于由于大字段存放在数据页中，造成了内存利用较差，带来过多的随机读，那怎么来优化掉这个大字段的影响

压缩&合并

1. innodb提供了barracuda文件格式，将大字段完全存放在溢出段中，数据段中只存放20个字节，这样就大大的减小了数据页的空间占用，使得一个数据页能够存放更多的数据行，也就提高了内存的命中率（对于本实例，大多数行的长度并没有超过8k，所以优化的幅度有限）；如果对溢出段的数据进行压缩，那么在空间使用上也会大大的降低，具体的的压缩比率可以设置key_blok_size来实现。
2. 可以把大字段用COMPRESS()压缩后再存为BLOB，或者在发送到MySQL前在应用程序中进行压缩
3. 一张表有多个类blob字段，把它们组合起来如 <f_big_col1>long…</f_big_col1>
   <f_content>long…</f_content> ，再压缩存储
4. 如果预期长度范围varchar就满足，就避免使用TEXT

拆分
将主表拆分为一对一的两个关联表，将大字段单独放到另外一张表后，单行长度变的非常的小，page的行密度相比原来的表大很多，这样就能够缓存足够多的行，buffer pool的命中率就会提高，应用程序需要额外维护的是一张大字段的子表，还可以通过覆盖索引来优化，将索引和原表结构分开，从访问密度较小的数据页改为访问密度很大的索引页，随机io转换为顺序io

如何对大字段进行优化

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

BLOB和TEXT区别是BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

来源

MySql大字段优化
优化sql大字段记录（最好是建立表结构时就要考虑）
字节跳动技术团队：慢 SQL 分析与优化