手机移动端网站案例,做爰全的网站,网站开发所用的技术,余姚网站推广有关数据库表的冗余字段 之前看一个开发人员的技术研讨视频#xff0c;提到了一个数据库表设计中的表拆分字段冗余问题#xff0c;就是一张表做纵向分表#xff0c;拆分为a和b以做冷热数据分离存储#xff0c;但是会有一种情况就是相同的字段值在a#xff0c;b表中重复出现…有关数据库表的冗余字段 之前看一个开发人员的技术研讨视频提到了一个数据库表设计中的表拆分字段冗余问题就是一张表做纵向分表拆分为a和b以做冷热数据分离存储但是会有一种情况就是相同的字段值在ab表中重复出现.这是因为查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) {SetLong userIds userManager.allLikeQueryByName(queryParam.getUserName());//构建包含这些用户ID的查询条件 return buildIncludeUserIds(queryParamuserIds);}
//...
//in(id1id2...)
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消息队列 场景:高并发写入容忍秒级延迟 流程 #mermaid-svg-tf4PWssgsbo9rcMW {font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;fill:#333;}#mermaid-svg-tf4PWssgsbo9rcMW .error-icon{fill:#552222;}#mermaid-svg-tf4PWssgsbo9rcMW .error-text{fill:#552222;stroke:#552222;}#mermaid-svg-tf4PWssgsbo9rcMW .edge-thickness-normal{stroke-width:2px;}#mermaid-svg-tf4PWssgsbo9rcMW .edge-thickness-thick{stroke-width:3.5px;}#mermaid-svg-tf4PWssgsbo9rcMW .edge-pattern-solid{stroke-dasharray:0;}#mermaid-svg-tf4PWssgsbo9rcMW .edge-pattern-dashed{stroke-dasharray:3;}#mermaid-svg-tf4PWssgsbo9rcMW .edge-pattern-dotted{stroke-dasharray:2;}#mermaid-svg-tf4PWssgsbo9rcMW .marker{fill:#333333;stroke:#333333;}#mermaid-svg-tf4PWssgsbo9rcMW .marker.cross{stroke:#333333;}#mermaid-svg-tf4PWssgsbo9rcMW svg{font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:16px;}#mermaid-svg-tf4PWssgsbo9rcMW .label{font-family:"trebuchet ms",verdana,arial,sans-serif;color:#333;}#mermaid-svg-tf4PWssgsbo9rcMW .cluster-label text{fill:#333;}#mermaid-svg-tf4PWssgsbo9rcMW .cluster-label span{color:#333;}#mermaid-svg-tf4PWssgsbo9rcMW .label text,#mermaid-svg-tf4PWssgsbo9rcMW span{fill:#333;color:#333;}#mermaid-svg-tf4PWssgsbo9rcMW .node rect,#mermaid-svg-tf4PWssgsbo9rcMW .node circle,#mermaid-svg-tf4PWssgsbo9rcMW .node ellipse,#mermaid-svg-tf4PWssgsbo9rcMW .node polygon,#mermaid-svg-tf4PWssgsbo9rcMW .node path{fill:#ECECFF;stroke:#9370DB;stroke-width:1px;}#mermaid-svg-tf4PWssgsbo9rcMW .node .label{text-align:center;}#mermaid-svg-tf4PWssgsbo9rcMW .node.clickable{cursor:pointer;}#mermaid-svg-tf4PWssgsbo9rcMW .arrowheadPath{fill:#333333;}#mermaid-svg-tf4PWssgsbo9rcMW .edgePath .path{stroke:#333333;stroke-width:2.0px;}#mermaid-svg-tf4PWssgsbo9rcMW .flowchart-link{stroke:#333333;fill:none;}#mermaid-svg-tf4PWssgsbo9rcMW .edgeLabel{background-color:#e8e8e8;text-align:center;}#mermaid-svg-tf4PWssgsbo9rcMW .edgeLabel rect{opacity:0.5;background-color:#e8e8e8;fill:#e8e8e8;}#mermaid-svg-tf4PWssgsbo9rcMW .cluster rect{fill:#ffffde;stroke:#aaaa33;stroke-width:1px;}#mermaid-svg-tf4PWssgsbo9rcMW .cluster text{fill:#333;}#mermaid-svg-tf4PWssgsbo9rcMW .cluster span{color:#333;}#mermaid-svg-tf4PWssgsbo9rcMW div.mermaidTooltip{position:absolute;text-align:center;max-width:200px;padding:2px;font-family:"trebuchet ms",verdana,arial,sans-serif;font-size:12px;background:hsl(80, 100%, 96.2745098039%);border:1px solid #aaaa33;border-radius:2px;pointer-events:none;z-index:100;}#mermaid-svg-tf4PWssgsbo9rcMW :root{--mermaid-font-family:"trebuchet ms",verdana,arial,sans-serif;} 更新热表 发送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);
});缺点依赖消息队列的可靠性需解决消息丢失、重复消费问题 版本号定时任务 场景数据更新频率较低允许分钟级延迟。 实现 热表和冷表均增加版本号字段。更新热表时递增版本号。定时任务扫描版本号不一致的数据并修复。 -- 表结构
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.冗余字段的设计原则
哪些字段适合做冗余?如何评判优化效果?
关联查询字段
场景多表关联查询JOIN的字段冗余后可直接查询单表。比如: 在评论表中冗余用户头像URLavatar_url避免关联用户表。在文章表中冗余作者姓名author_name避免关联作者表。 评估效果 冗余后的查询时间 单表查询时间冗余前的查询时间 单表查询时间 JOIN时间 网络延迟若单表查询时间 50%总时间冗余可能有效。 高频查询低频更新字段 场景该字段被频繁用于查询条件或结果展示且更新频率低冗余后维护成本可控。 比如: 订单表中冗余用户IDuser_id避免查询订单时关联用户表。商品表中冗余分类名称category_name避免关联分类表获取名称。用户表中的地区编码region_code区域信息几乎不变。商品表中的品牌名称brand_name品牌更名频率极低。 判断依据 通过SQL慢查询日志或APM工具如Arthas、New Relic统计该字段的查询频率。若该字段出现在80%以上的查询中优先考虑冗余。 统计字段的日更新率如每天更新次数 / 总数据量。若更新率低于1%适合冗余高于5%需谨慎。 实例 电商订单表设计 业务需求 高频查询订单列表时需要显示用户昵称nickname和商品标题product_title。原方案需JOIN用户表和商品表平均查询时间120ms。 冗余方案 在订单表中冗余nickname和product_title字段。更新逻辑 下单时从用户表和商品表写入冗余字段强一致性。用户修改昵称时异步更新历史订单最终一致性(如果订单表中的冗余字段 nickname 未更新查询订单列表时会显示旧昵称“TechGuy2020”与用户当前信息不一致)。 效果评估 性能提升 单表查询时间降至40msQPS从500提升至1500。 存储成本 新增两个VARCHAR(100)字段总存储增加18GB1亿订单。 一致性风险 用户修改昵称后历史订单更新延迟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列表过长
ListLong userIds userService.listUserIdsByName(query.getUserName());
ListListLong batches Lists.partition(userIds 1000);
batches.forEach(batch - {queryWrapper.in(CollUtil.isNotEmpty(batch) OperationLogDO::getUserId batch);// 执行分页查询并合并结果
});如何选择方案
是否高频查询 ✅ 是 → 字段冗余。❌ 否 → 进入下一步。 用户表模糊查询是否高效 ✅ 是如索引优化后右模糊 → JOIN联表查。
再往后就没有下一步了从上面的表格我们可以看出在轻量级数据查询情况下join是碾压in的现在来看原本代码的in方案是非常不可取的 笔者只是从遇到的一个简单实例中回想起看过的这个知识点所以做了一些搜索总结一查才发现还有数据一致性的问题然后又拓展到了冗余字段的设计原则和其他方案的对比不得感叹ai的出现减少了多少学习成本.本人也只是个小白只是对内容做搜索总结如果有错误的地方也恳请指出交流. 此外上文还隐含一个一个点就是in查询列表过长导致的性能崩坏问题这个下篇文章再做讨论…
