有关数据库表的冗余字段

有关数据库表的冗余字段

之前看一个开发人员的技术研讨视频，提到了一个数据库表设计中的表拆分字段冗余问题，就是一张表做纵向分表，拆分为a和b以做冷热数据分离存储，但是会有一种情况就是相同的字段值在a，b表中重复出现.这是因为查a但是需要这个字段就需要再去查b，多了一次查询，但是把这个字段存储在a中就可以减少很多次再查询，那么就有了这个必要冗余

实习开发中就遇到了这种情况的一个例子，加深了自己对这个问题的理解，做一个记录分享:

实例

有一个分页查询，查询条件里有一个筛选字段是用户姓名，这个分页数据是从表a中取出的

我们假设表a中有用户姓名字段，那么使用mybatis-plus查询时，只需要通过wrapper上通过like拼接上用户姓名就可以了，如下:

queryWrapper.like(StrUtil.isNotBlank(query.getUserName())， OperateLogDO::getUserName， query.getUserName())

可是如果表a只存有userId，没有username会是什么逻辑呢?

那就需要先到user表里先根据username进行一次模糊匹配查询，获取到符合条件的所有userIds，然后在wrapper中利用in拼接userIds条件，那么这多余一次查询在数据量很大或是没有索引的情况下的时间开销还是不容忽视的

//先根据username查出userIds，然后组装分页参数
private Boolean rebuildParamByUsername(OperationLogQueryParam queryParam) {Set<Long> userIds = userManager.allLikeQueryByName(queryParam.getUserName());//构建包含这些用户ID的查询条件  return buildIncludeUserIds(queryParam，userIds);}
//...
//in(id1，id2...)
queryWrapper.in(CollUtil.isNotEmpty(queryParam.getIncludeUserIds())， OperationLogDO::getUserId， queryParam.getIncludeUserIds())

经过这个例子就对之前提到的"字段冗余"有了更深的理解了，其实就是一种空间换时间的思想

更多思考

1.冗余字段数据一致性

既然在两个地方存了相同的数据，那么保证数据一致性就是少不了的话题，这里提出两种场景的方案

强一致性

是指要确保任何时刻查询到的数据都是最新的，也就是二者的数据要保证即使高度一致，不能容忍不一致性的出现，常见于金融交易/库存扣减等对一致性要求极高的业务.

一般通过数据库事务/分布式事务来保证

• 数据库事务

  • 单数据库情况下利用事务提交机制来实现

    BEGIN TRANSACTION;
    -- 更新主表（热表）
    UPDATE order_hot SET user_id = 456 WHERE order_id = 123;
    -- 同步更新冗余表（冷表）
    UPDATE order_cold SET user_id = 456 WHERE order_id = 123;
    COMMIT;
    

  • 缺点是跨表事务可能引发锁竞争，影响并发性能

• 分布式事务

  • 两阶段提交（2PC）

    • Prepare阶段：协调者询问所有参与者是否可提交。
    • Commit/Abort阶段：根据Prepare结果提交或回滚。

  • MySQL XA事务

    -- 参与者1（热表所在库）
    XA START 'tx1';
    UPDATE order_hot SET user_id = 456 WHERE order_id = 123;
    XA END 'tx1';
    XA PREPARE 'tx1';-- 参与者2（冷表所在库）
    XA START 'tx2';
    UPDATE order_cold SET user_id = 456 WHERE order_id = 123;
    XA END 'tx2';
    XA PREPARE 'tx2';-- 协调者提交
    XA COMMIT 'tx1';
    XA COMMIT 'tx2';
    

  • 缺点:性能较差，XA事务成功率依赖网络和参与者稳定性

弱一致性

允许冗余字段出现短暂不一致，适用于日志记录等对实时性要求不高的场景

通过**异步机制(消息队列，定时任务)**实现最终一致

• MQ消息队列

  • 场景:高并发写入，容忍秒级延迟

  • 流程

  • 实例

    生产者（更新热表后发消息）

    jdbc.update("UPDATE order_hot SET user_id=? WHERE order_id=?"， 456， 123);
    // 发送消息
    mq.send("topic:user_update"， "{order_id:123， user_id:456}");
    

    消费者（消费消息更新冷表）

    mq.subscribe("topic:user_update"， message -> {jdbc.update("UPDATE order_cold SET user_id=? WHERE order_id=?"， message.user_id， message.order_id);
    });
    

  • 缺点：依赖消息队列的可靠性（需解决消息丢失、重复消费问题）

• 版本号+定时任务

  • 场景：数据更新频率较低，允许分钟级延迟。

  • 实现

    1. 热表和冷表均增加版本号字段。
    2. 更新热表时递增版本号。
    3. 定时任务扫描版本号不一致的数据并修复。

    -- 表结构
    ALTER TABLE order_hot ADD COLUMN version INT DEFAULT 0;
    ALTER TABLE order_cold ADD COLUMN version INT DEFAULT 0;-- 定时任务（伪代码）
    SELECT h.order_id， h.user_id， h.version 
    FROM order_hot h
    JOIN order_cold c ON h.order_id = c.order_id
    WHERE h.version > c.version;-- 修复不一致数据
    UPDATE order_cold 
    SET user_id = {热表.user_id}， version = {热表.version} 
    WHERE order_id = {不一致的订单ID};
    

  • 缺点:定时任务存在延迟，可能影响用户体验。

2.冗余字段的设计原则

哪些字段适合做冗余?如何评判优化效果?

1. 关联查询字段

• 场景：多表关联查询（JOIN）的字段，冗余后可直接查询单表。
• 比如:
  • 在评论表中冗余用户头像URL（avatar_url），避免关联用户表。
  • 在文章表中冗余作者姓名（author_name），避免关联作者表。
• 评估效果
  • 冗余后的查询时间 = 单表查询时间
  • 冗余前的查询时间 = 单表查询时间 + JOIN时间 + 网络延迟
  • 若单表查询时间 < 50%总时间，冗余可能有效。

2. 高频查询&低频更新字段

   • 场景：该字段被频繁用于查询条件或结果展示且更新频率低，冗余后维护成本可控。

   • 比如:

     • 订单表中冗余用户ID（user_id），避免查询订单时关联用户表。
     • 商品表中冗余分类名称（category_name），避免关联分类表获取名称。
     • 用户表中的地区编码（region_code），区域信息几乎不变。
     • 商品表中的品牌名称（brand_name），品牌更名频率极低。

   • 判断依据

     • 通过SQL慢查询日志或APM工具（如Arthas、New Relic）统计该字段的查询频率。若该字段出现在80%以上的查询中，优先考虑冗余。

     • 统计字段的日更新率（如每天更新次数 / 总数据量）。若更新率低于1%，适合冗余；高于5%需谨慎。

3. 实例

电商订单表设计

1. 业务需求
   • 高频查询订单列表时需要显示用户昵称（nickname）和商品标题（product_title）。
   • 原方案需JOIN用户表和商品表，平均查询时间120ms。
2. 冗余方案
   • 在订单表中冗余nickname和product_title字段。
   • 更新逻辑：
     • 下单时从用户表和商品表写入冗余字段（强一致性）。
     • 用户修改昵称时异步更新历史订单（最终一致性）(如果订单表中的冗余字段 nickname 未更新，查询订单列表时会显示旧昵称（“TechGuy2020”），与用户当前信息不一致)。
3. 效果评估
   • 性能提升：
     • 单表查询时间降至40ms，QPS从500提升至1500。
   • 存储成本：
     • 新增两个VARCHAR(100)字段，总存储增加18GB（1亿订单）。
   • 一致性风险：
     • 用户修改昵称后，历史订单更新延迟1分钟内完成（业务可接受）。

同时要注意遵循“最小化冗余”原则，优先冗余最关键的1-2个字段。比如不要冗余10个字段以消除所有JOIN，导致更新逻辑复杂化。

3.连表查及三种方案对比

维度	字段冗余	JOIN联表查	先查ID再用IN查询
查询速度	⭐⭐⭐⭐（单表快速）	⭐⭐（JOIN开销大）	⭐（两次查询+IN性能差）
存储成本	⭐（冗余字段占用空间）	⭐⭐⭐⭐（无冗余）	⭐⭐⭐⭐（无冗余）
数据一致性维护成本	⭐（需同步更新冗余字段）	⭐⭐⭐⭐（无需维护）	⭐⭐⭐⭐（无需维护）
高并发写入场景	⚠️ 差（更新需同步冗余字段）	⭐⭐⭐⭐（无额外压力）	⭐⭐⭐⭐（无额外压力）
模糊查询性能	⭐⭐⭐（可走索引）	⚠️ 依赖用户表索引优化	⚠️ 依赖用户表模糊查询效率
IN查询限制	无	无	⚠️ IN列表过长导致性能骤降

---

详细分析与设计建议

1. 字段冗余方案

• 适用场景：

  • 高频根据username查询表A（如日志查询、报表展示）。
  • username更新频率低（如用户每月修改一次昵称）。

• 优化建议：

  • 为冗余字段username添加索引（如联合索引）。
  • 使用最终一致性更新策略（异步消息同步）。

• 示例优化：sql

  -- 添加联合索引（假设常按时间范围+username查询）
  CREATE INDEX idx_username_created ON table_a (username， created_time);
  

2. JOIN联表查方案

• 适用场景：

  • 表A与用户表数据量较小（如百万级以内）。
  • 用户表username字段有高效索引（如前缀索引）。

• 优化建议：

  • 避免全模糊查询（LIKE '%xxx%'），改用右模糊（LIKE 'xxx%'）以利用索引。

  • 使用覆盖索引减少回表：

    -- 用户表索引优化
    CREATE INDEX idx_username ON user (username);
    

3. 先查ID再用IN查询方案

• 适用场景：

  • 不推荐

• 分页陷阱规避：

  // 分批查询（避免IN列表过长）
  List<Long> userIds = userService.listUserIdsByName(query.getUserName());
  List<List<Long>> batches = Lists.partition(userIds， 1000);
  batches.forEach(batch -> {queryWrapper.in(CollUtil.isNotEmpty(batch)， OperationLogDO::getUserId， batch);// 执行分页查询并合并结果
  });
  

---

如何选择方案？

1. 是否高频查询？
   • ✅ 是 → 字段冗余。
   • ❌ 否 → 进入下一步。
2. 用户表模糊查询是否高效？
   • ✅ 是（如索引优化后右模糊） → JOIN联表查。

再往后就没有下一步了，从上面的表格我们可以看出，在轻量级数据查询情况下，join是碾压in的，现在来看原本代码的in方案是非常不可取的

笔者只是从遇到的一个简单实例中回想起看过的这个知识点，所以做了一些搜索总结，一查才发现还有数据一致性的问题，然后又拓展到了冗余字段的设计原则和其他方案的对比，不得感叹ai的出现减少了多少学习成本.本人也只是个小白，只是对内容做搜索总结，如果有错误的地方，也恳请指出交流.

此外上文还隐含一个一个点，就是in查询列表过长导致的性能崩坏问题，这个下篇文章再做讨论…