关系型数据库大数据性能优化解决方案之：分表(当前表历史表)、表分区、数据清理原则

最新推荐文章于 2024-08-16 09:52:33 发布
最新推荐文章于 2024-08-16 09:52:33 发布
阅读量744 收藏 3
点赞数 1
分类专栏： shardingdbtable

原因和目的

  • 由于交易量大或者日积月累造成数据库的数据量越来越大。会导致系统性能大幅下降，所以要对部分业务的表数据作备份和清理
  • 减少数据量，来提升请求响应的速度，提升用户体验

数据是否需要清理的阀值判断

通常当表的磁盘大小超过 5GB，或对于 OLTP 系统(联机事务处理)，表的记录超过 3000 万，都应考虑对表进行分区或者分表。
除了上述阀值之外，还可以根据数据库性能指标情况来考虑分区或者分表，比如在已经充分挖掘了表设计、索引设计、查询设计等单表性能优化手段之后，依然不能满足业务的需要，这个时候，即使表的容量或记录尚未达到上述阀值那也要考虑分区或者分表了，这个时间点的记录数即为该表阀值。
一般来讲，用记录数标记阀值会比表的磁盘容量更容易操作一些，所以一般以达到该阀值时的记录数作为阀值，下文也会以此来作为阀值。每个表的数据格式不同、索引设计、单位事务处理时间容忍度不同，所以这个阀值也不同。

满负载周期判断

也就是说，一张空表，需要多久才能达到它的阀值，这个周期我们称之为满负载周期。
如果业务量已经稳定，数据量积累到当前阀值所需要的时间即为该表满负载周期；如果业务量稳定上升，即数据量在递增，根据递增速度估算出下一个满负载周期——也就是说，什么时候到达阀值什么时候进行迁移，但我们要做到提前规划，心中有数。

迁移周期判断

拿到该表阀值之后，需要对迁移周期进行判断，即多久对该表迁移一次？
可以一天迁移到历史表一次，即迁移周期就是一天。这样操作很简单，完全可以做成定时任务，所以很多系统这么干，只是有些频繁。

也可以用三分法，即满负载周期后，找出后 1/3 周期的数据进行迁移，那么迁移周期就是 1/3 个满负载周期。这样虽然比较有计划性，但是操作稍微复杂(特别是业务量不稳定的时候)，而且每次迁移可能需要开发、运维共同计划、参与。

数据的流向历史

当前表 -> 历史表 -> 备份表

数据在当前表历史表备份表中的迁移历史.png

如上图所示，绿色代表当前表，表示虽然存在高频率的写入操作，但是表的查询性能是非常高的；黄色代表历史表，表示写入频率已经很小，但是还是有一些查询需求，查询性能很高；灰色代表备份表，表示数据到了这张表中已经不再提供写入操作，视情况会提供查询操作，查询性能一般。

不同类型数据的分区方案

  • 联机交易(实时类交易，写频繁)只提供当日交易查询，建立AB表进行日切，每日对切换下来不提供服务的那张表进行迁移；迁移后的联机交易进入联机历史表，之后对原表(A 或 B 表)执行 truncate 操作；历史表以交易创建时间(或更新时间，或完成时间)进行 range 分区操作(比如按月分区，具体看业务量递增情况)
  • 清分交易(非实时类交易，读频繁)供当日之外的联机交易查询、平台查询以及清分等数据操作；可以按月对清分交易进行分表操作，但是这样做的话前端应用需要对清分查询进行数据库路由，并对跨月查询结果进行整合过滤；所以清分表最好还是建立当前表历史表机制，根据迁移周期进行定期迁移
  • 日志类数据同非实时类交易
  • 用户认证信息(访问度不高，读大于写)，根据业务查询需要可以考虑按照时间进行 range 分区(推荐 range，可扩展性较高，一般来讲即使业务量暴增也能满足需要)，或者按照用户 id 进行 hash 或 range 分区，分区字段的选取可以参考"注意的点"中列举的事项

历史表的清理方案

  • 交易类(包括联机、清分、分润)的数据5年清理一次
  • 日志类的数据5年清理一次
  • 用户认证、商户认证类数据一直保留

注意的点

  • 日志类表(操作日志、系统日志、结果记录、任务记录等)的分区处理：按时间进行分区
  • 交易类表(流水、明细、对账、差异等)的分区处理：按创建时间或更新时间进行分区
  • 通知类表的分区处理：按时间进行分区
  • 分区字段的选取：一般是以最经常查询的那个字段来进行分区，这样会有助于提高查询速度。不建议使用 id 进行分区，除非业务系统里固定的 id=? 查询特别多，否则不仅是对分区索引的浪费，而且可能会比没有分区还要慢
  • 不管是 range 分区，还是 hash 分区，在做分区的时候都要考虑到分区的可扩展性，原则上分区后的 2 年内不应该再考虑重新分区的事情，分区到期后根据业务量增长情况再加 2 年的分区…以此类推
  • 各个分区内的数据要较为均匀，不要太多也不要太少，而且根据分区字段可以很快定位到分区范围
  • 具体每个分区内数据量多少合适？原则上是把数据的范围缩小到一个即使做全盘扫描也不会慢的时候为最佳，视具体情况为几十万或上百万不等

  • 相关业务操作(SQL)尽量在同一个分区内部完成。必须跨分区提取的话建议并行提取以提高速度






https://blog.csdn.net/defonds/article/details/52241432



varyall
关注
专栏目录
【大数据 OLAP ClickHouse 引擎】ClickHouse 系统架构和存储引擎实现原理 : 为什么 ClickHouse 这么快？ Why is ClickHouse so fast?
AI天才研究院
03-01 4万+
ClickHouse 是一款由俄罗斯 Yandex 公司开发的 C++ 开源高性能 OLAP 组件。在 Yandex 内部, ClickHouse 主要用于在线流量分析产品 Yandex Metrica，类似于 Google Analytics 或者百度统计。ClickHouse是一款MPP架构的列式存储数据库，但MPP和列式存储并不是什么"稀罕"的设计。拥有类似架构的其他数据库产品也有很多，但是为什么偏偏只有ClickHouse的性能如此出众呢？
分库分表之历史表如何选择最佳分片路由规则
myqq-835500231的博客
03-17 395
分库分表之历史表如何选择最佳分片路由规则 前言 背景 首先我相信很多人使用分表分库一定有这么一个情况,就是目前我们的系统有一张表可能会非常的庞大,然后希望通过分片技术将其进行水平拆分,但是如何拆分或者说如何拆分可以保证让目前的数据性能达到最优解，是一个很值得探讨的问题。 这边简单举一个例子，譬如我们的订单表,目前我们的订单表可能已经达到一定的数量级了比如百万或者千万级别了，可能光是简单的查询性能是很高的，但是新增订单可能就没这么乐观了，随着索引的增多新增的数目也会不断地变慢，不仅仅是查询一个维度迫使
关系型数据库大数据性能优化解决方案之：分表(当前表历史表)、表分区、数据清理原则...
dawuafang
08-18 648
原因和目的 由于交易量大或者日积月累造成数据库的数据量越来越大。会导致系统性能大幅下降，所以要对部分业务的表数据作备份和清理 减少数据量，来提升请求响应的速度，提升用户体验 数据是否需要清理的阀值判断通常当表的磁盘大小超过 5GB，或对于 OLTP 系统(联机事务处理)，表的记录超过 3000 万，都应考虑对表进行分区或者分表。除了上述阀值之外，还可以根据数据库性能指标情况来考虑分区或者分表，...
数据库分库分表的介绍
最新发布
0
08-16 1395
概览 :: ShardingSphere (apache.org)ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈，它由、Sharding-Proxy和Sharding-Sidecar（计划中）这3款相互独立的产品组成。他们均提供标准化的数据分片、分布式事务和数据库治理功能，可适用于如Java同构、异构语言、云原生等各种多样化的应用场景。Sharding-JDBC是ShardingSphere的第一个产品，也是ShardingSphere的前身。
数据库优化查询的方法以及大访问量到数据库时的优化
weixin_34183910的博客
03-25 284
一.数据库优化查询的方法 1.使用索引： 应尽量避免全表扫描，首先考虑在where 以及 order by ,group by 涉及的列上建立索引 2.优化SQL语句： 1>通过explain(查询优化神器)用来查看SQL语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。通常我们可以对比较复杂的尤其是涉及到多表的SELECT语句，把关键字explain加到...
数据库表分区真能加速数据库查询速度吗？
Jarvan_Song的博客
11-15 7229
数据库表分区
查询历史表和当前出现的问题
z1340954953
11-21 752
union ，union all
MySQL数据库之分库分表方案
CXY_QIQI的博客
05-14 8569
数据库之互联网常用分库分表方案 一、数据库瓶颈 1、IO瓶颈 2、CPU瓶颈 二、分库分表 1、水平分库 2、水平分表 3、垂直分库 4、垂直分表 三、分库分表工具 四、分库分表步骤 五、分库分表问题 1、非partition key的查询问题（水平分库分表，拆分策略为常用的hash法） 2、非partition key跨库跨表分页查询问题（水平分库分表，拆分策略为常用的hash法） 3、扩容问题（水平分库分表，拆分策略为常用的hash法） 六、分库分表总结 七、分库分表示例
【大数据处理篇】：大数据时代的数据库解决方案！MySQLdb高效处理策略
[【大数据处理篇】：大数据时代的数据库解决方案！MySQLdb高效处理策略](https://www.codegrepper.com/codeimages/python-and-mysql-connectivity.png) # 1. 大数据处理与数据库挑战 随着信息技术的飞速发展，大...
MySQL如何查看当前表结构
分享--------留住生活
12-06 2786
以user用户表为例，显示查看表结构的两种方式： 1. desc（describe的缩写） user（表名）： 2. show create table user（表名）：
数据库分表
cfp008的博客
10-12 292
当业务数据量非常巨大时，分表是常用的优化方式。一张表的数据量过大，这样在日常操作时速度非常缓慢。怎样提高它的响应速度？实际就是将一些不常用的数据移动到另一张表中，这里的另一张表叫历史表。历史表和原来的表结构一致。这样当前表由于只使用当前的部分数据，所以性能得到提高。因为实际业务中很少再去查询历史表，即使查询历史表由于使用的人也少，所以它的性能还能适应。 历史表引发一个骨牌效应。（流程）例如：
【订单号存储】最硬核实用的分库分表操作指南
wuhuagu_wuhuaguo的博客
03-28 1617
两种方案分库分表：按照用户id来切分(推荐)、按照订单号来切分。 方案一、按照用户id打散订单数据(推荐) (2.1)某个范围的uid订单到哪些库。 0到2千万uid，对应的订单数据到a库、a表。2千万到4千万对应的订单到b库。 ---缺：某个范围内的用户，下单量比较多，那么造成这个库的压力特别大。 (2.2)使用uid取模运算(推荐) 库名称计算【用户id末尾4位 mod 32】：对用...
大数据量查询优化
Pbeenig的博客
07-27 920
一、数据库结构的设计     如果不能设计一个合理的数据库模型，不仅会增加客户端和服务器段程序的编程和维护的难度，而且将会影响系统实际运行的性能。所以，在一个系统开始实施之前，完备的数据库模型的设计是必须的。     在一个系统分析、设计阶段，因为数据量较小，负荷较低。我们往往只注意到功能的实现，而很难注意到性能的薄弱之处，等到系统投入实际运行一段时间后，才发现系统的性能在降低，这时再
MySQL查询当前数据库所有表和表中字段
wqww_1的博客
04-25 4001
1、查询当前数据库 # 查询当前数据库 select database(); 2、查询当前数据库所有表 # 查询当前数据中所有表 select table_name tableName,engine ,table_comment tableComment, create_time createTime from information_schema.tables where table_schema = (select database()) order by create_time desc; 3
历史数据处理
L.的博客
12-03 1920
处理大数据量数据访问速度缓慢的问题 方案1 水平分库分表，比如根据年份划分，XXX_2019数据库、XXX_2020数据库等等 查询的时候根据查询时间，去不同的数据库查询，然后组合结果返回 示例代码 HistoricalDataManage 方案2 建立历史数据库和实时数据库，历史数据库存储所有数据，实时数据库仅保留常用数据 定时将实时数据库的数据存入历史数据库，并删除实时数据库中的旧数据 参考资料 数据库中的历史数据的处理问题 ...
mysql查看当前表字符集_查看mysql字符集及修改表结构--表字符集，字段字符集
weixin_35266799的博客
02-02 3149
MySQL 乱码的根源是的 MySQL 字符集设置不当的问题，本文汇总了有关查看 MySQL 字符集的命令。包括查看 MySQL 数据库服务器字符集、查看 MySQL 数据库字符集，以及数据表和字段的字符集、当前安装的 MySQL 所支持的字符集等。一、查看 MySQL 数据库服务器和数据库字符集。mysql> show variables like '%char%';+----------...
Oracle 查询当前数据库所有表名+表名注释
热门推荐
思维不断的改变
06-20 1万+
原因：没办法起一个望名知意的表名，或者英文不太好没办法看表名知道这个表作用的。 Oracle 查询当前数据库获得 所有表名+表名注释 SELECT a.TABLE_NAME,b.COMMENTS FROM user_tables a,user_tab_comments b WHERE a.TABLE_NAME=b.TABLE_NAME ORDER ...
导出当前表
莉兹Liz的博客
02-24 146
import * as http from './http'; tableExport = () => { http.tableExport(this.state.params); } // render中 <a className='export-btn' onClick={this.tableExport}>导出当前表</a> // http.js import httpServer from '../../crm-assets/js/httpServer'; /
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值