"经常混迹于技术社区,频繁看到这个题目,今天干脆在自己博客重复一遍解决办法: 针对 mysql,sqlserver 等关系型数据库单表数据过大的处理方式 如果不是[链接]的[链接]那种多机器集群方案的话:先考虑表分区 ;然后考虑分表 ;然后考虑分库。 这个题目是我所经历过的,我的 GPS 汽车定位系统,早期就是选用的 S .."

mysql,sqlserver 数据库单表数据过大的处理方式

经常混迹于技术社区,频繁看到这个题目,今天干脆在自己博客重复一遍解决办法:

针对 mysql,sqlserver 等关系型数据库单表数据过大的处理方式

如果不是阿里云分布式数据库 DRDS那种多机器集群方案的话:先考虑表分区 ;然后考虑分表 ;然后考虑分库。

这个题目是我所经历过的,我的 GPS 汽车定位系统,早期就是选用的 Sql Server 数据库。当时我选取的方案就是第一种:表分区。 表分区的优势是,如果表结构合理,可以不涉及到程序修改。也就是说,对程序来讲依然是单表读写的效果!

所有轨迹数据存入到一个巨大的表里。有多大呢?

这张大型单表设计要点:(一个聚集索引用于写入,一个联合索引用于查询,没有主键,使用表分区)

明确主键用途:

真的需要查询单行数据时候才需要主键!

我采用无主键设计,用于避免写入时候浪费维护插入数据的性能。最早使用聚集的类似自增的 id 主键,压测写入超过 5 亿行的时候,写入性能缩减一半

准确适用聚集:

写入的数据在硬盘物理顺序上是追加,而不是插入!

我把时间戳字段设置为聚集索引,用于聚集写入目的设计。保证硬盘上的物理写入顺序,不浪费性能用于插入数据

职责足够单一:

用于精准索引!

使用时间 + 设备联合索引,保证这张表只有一个查询用途。保证系统只有一种查询目的:按照设备号,查询一个时间段的数据。

精确的表分区:

要求查询时候限定最大量或者最大取值范围!

按天进行表分区,实现大数据量下的高效查询。这里是本文重点,按照聚集索引进行,可以让目标数据局限在更小的范围进行,虽然单表数据上亿,但是查询基本上只在某一天的的几千万里进行索引查询

每张表会有各自的特点,不可生搬硬套,总结下我这张表的特点:

只增,不删,不改!

关于不删除中:每天使用作业删除超过 30 天的那个分区数据除外,因为要清空旧的表分区,腾出新的表分区!

只有一个业务查询:只按照设备编码查询某个时间段

只有一个运维删除:删除旧的分区数据

这张表,是我技术生涯中进步的一个大阶梯,让我我体会到了系统架构的意义。

虽然我的这张举行表看似只有 4 个关键点,但是这四个非常精准的关键点设计,耗费了我一个月之久!正是这么足够精准的表结构设计,才撑起了后来压测并发量超过 3000 的并发写入量!压测的指标跟数据库所在的硬盘有直接关系,当时选取的硬盘是 4 块 10000 转的 SAS 盘做了 Raid10 的环境

关于后来为什么没有更高的实际应用数值,是因为系统后来改版为云架构,使用了阿里云,更改为写入性能更高的非关系型数据库 MongoDB存储轨迹数据。所以虽然距离压测指标还差很远,但是也没有实际跑到这个数据!单机应用再怎么改造,每次升级都是一件麻烦事,所以应当尽可能将瓶颈点提高,甚至消除,云架构的意义就在于弹性扩展,虽然我在数据库方面还没有这方面的成功案例可分享,但是这种架构的意义很明白:将来面对更大的压力,只需要增加服务器数量!

最后提一句, 很多人觉得 SSD 就足够高的性能了,但是对于云服务器,ssd 的性能才跟传统物理机的 iops 相持平,这是由于虚拟化层面的损失导致的!

原文地址:https://www.opengps.cn/Blog/View.aspx?id=284文章的更新编辑依此链接为准。欢迎关注源站原创文章!

  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    393 引用 • 430 回帖 • 1008 关注
  • SQLServer

    SQL Server 是由 [微软] 开发和推广的关系数据库管理系统(DBMS),它最初是由 微软、Sybase 和 Ashton-Tate 三家公司共同开发的,并于 1988 年推出了第一个 OS/2 版本。

    13 引用 • 25 回帖 • 8 关注
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    230 引用 • 513 回帖
感谢    关注    收藏    赞同    反对    举报    分享
回帖    
请输入回帖内容...