数据的垂直分表

垂直分表的出现背景

• 银行工作经验：曾在银行工作，核心业务表通常有上百个字段，当时认为这是习以为常的事情。
• 现状反思：现在认为当时的做法存在问题，在大厂应用中必须进行垂直分表，否则系统处理将成为灾难。

水平分表 vs 垂直分表

水平分表：

• 以行为单位对数据进行切分。
• 分片算法：范围法、哈希法。
• 解决数据量大的存储问题。
• 表结构相同，数据存储在不同数据库的不同表中。

垂直分表：

• 将一张大表按列切分成两张以上的小表分别存储。
• 优化SQL查询，通过主键关联完成数据查询。

MySQL InnoDB 存储引擎机制

• 数据存储结构：每一行数据称为“行”，管理数据的基本单位称为“页”，每页默认大小为16K。
• 压缩页：对页的数据进行压缩，存储空间比逻辑占用更小，但带来压缩和解压缩的效率问题。
• 设计优化：尽可能多存储行数据，减少跨页检索，提高数据检索效率。

垂直分表的实际案例

商品表拆分

• 假设商品表有100个字段，拆分为商品基本信息表和商品详情信息表。
• 不拆分时，商品完整信息占用1K字节，16K的页最多存储16条数据, 需要625 万页。
• 拆分后，基本信息表每行64个字节，1亿条数据需要39万页，数据扫描范围缩小16倍，提高执行效率。

SELECT * FROM 商品表 WHERE 商品标题='AD钙奶';
SELECT * FROM 商品基本信息表 a, 商品详情表 b WHERE a.商品id=b.商品id and a.商品标题='AD钙奶';

垂直分表的依据

通过将重要字段单独剥离出一张小表，让每一页能够容纳更多的行，进而缩小数据扫描的范围，达到提高执行效率的目的。

• 条件：

  • 单表数据总量可能超过千万。

  • 字段超过20个，且包含了超长的 Varchar、CLOB、BLOB 等字段

• 字段分类：

  • 小表字段：查询排序时使用的字段（如分类编号、商品ID、品牌编号、逻辑删除标志）、高频访问的小字段（如商品名称、子标题、价格）。
  • 大表字段：低频访问字段（如商品的配送信息、售后声明、最后更新时间）、大字段（如商品图文详情、图片二进制数据、JSON原数据）。

总结

• 垂直分表的目的：通过将重要字段单独剥离在小表中，让每页容纳更多数据，缩小数据扫描范围，提高执行效率。
• 大字段处理：大字段对存储和检索性能影响大，必要时为大字段单独建表。

实践中的注意事项

• 不需要垂直分表的情况：日志表等关键字段不多且数据总量小的表。
• 满足条件：单表数据量大且字段多时，才考虑垂直分表。