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

本贴最后更新于 2317 天前,其中的信息可能已经时移世改

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

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

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

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

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

  • 最大存储量超过 10 亿行。具体数值应该是 12 亿多点,由于系统设计为只存储 30 天轨迹,所以线上期间最大存储只到这个数,再后来采用云架构,上云替换成非关系性数据库,获得了更高的写入性能和存储压缩能力。  

  • 每日写入量就超过 1500 万行。上下班交通高峰时候每秒写入量平均超过 500 行。也就是 500iops,距离系统设计的压测指标 3000 还有一大截

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

明确主键用途:

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

我采用无主键设计,用于避免写入时候浪费维护插入数据的性能。最早使用聚集的类似自增的 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 是最流行的关系型数据库管理系统之一。

    694 引用 • 537 回帖
  • SQLServer

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

    21 引用 • 31 回帖 • 3 关注
  • 数据库

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

    346 引用 • 760 回帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...

推荐标签 标签

  • Solidity

    Solidity 是一种智能合约高级语言,运行在 [以太坊] 虚拟机(EVM)之上。它的语法接近于 JavaScript,是一种面向对象的语言。

    3 引用 • 18 回帖 • 448 关注
  • 周末

    星期六到星期天晚,实行五天工作制后,指每周的最后两天。再过几年可能就是三天了。

    14 引用 • 297 回帖 • 3 关注
  • SMTP

    SMTP(Simple Mail Transfer Protocol)即简单邮件传输协议,它是一组用于由源地址到目的地址传送邮件的规则,由它来控制信件的中转方式。SMTP 协议属于 TCP/IP 协议簇,它帮助每台计算机在发送或中转信件时找到下一个目的地。

    4 引用 • 18 回帖 • 644 关注
  • SQLite

    SQLite 是一个进程内的库,实现了自给自足的、无服务器的、零配置的、事务性的 SQL 数据库引擎。SQLite 是全世界使用最为广泛的数据库引擎。

    4 引用 • 7 回帖 • 1 关注
  • Openfire

    Openfire 是开源的、基于可拓展通讯和表示协议 (XMPP)、采用 Java 编程语言开发的实时协作服务器。Openfire 的效率很高,单台服务器可支持上万并发用户。

    6 引用 • 7 回帖 • 118 关注
  • Ubuntu

    Ubuntu(友帮拓、优般图、乌班图)是一个以桌面应用为主的 Linux 操作系统,其名称来自非洲南部祖鲁语或豪萨语的“ubuntu”一词,意思是“人性”、“我的存在是因为大家的存在”,是非洲传统的一种价值观,类似华人社会的“仁爱”思想。Ubuntu 的目标在于为一般用户提供一个最新的、同时又相当稳定的主要由自由软件构建而成的操作系统。

    127 引用 • 169 回帖 • 2 关注
  • uTools

    uTools 是一个极简、插件化、跨平台的现代桌面软件。通过自由选配丰富的插件,打造你得心应手的工具集合。

    7 引用 • 28 回帖
  • SSL

    SSL(Secure Sockets Layer 安全套接层),及其继任者传输层安全(Transport Layer Security,TLS)是为网络通信提供安全及数据完整性的一种安全协议。TLS 与 SSL 在传输层对网络连接进行加密。

    70 引用 • 193 回帖 • 409 关注
  • Word
    13 引用 • 41 回帖
  • Caddy

    Caddy 是一款默认自动启用 HTTPS 的 HTTP/2 Web 服务器。

    10 引用 • 54 回帖 • 183 关注
  • 服务

    提供一个服务绝不仅仅是简单的把硬件和软件累加在一起,它包括了服务的可靠性、服务的标准化、以及对服务的监控、维护、技术支持等。

    41 引用 • 24 回帖 • 1 关注
  • Bug

    Bug 本意是指臭虫、缺陷、损坏、犯贫、窃听器、小虫等。现在人们把在程序中一些缺陷或问题统称为 bug(漏洞)。

    76 引用 • 1742 回帖
  • wolai

    我来 wolai:不仅仅是未来的云端笔记!

    2 引用 • 14 回帖 • 1 关注
  • 工具

    子曰:“工欲善其事,必先利其器。”

    301 引用 • 768 回帖
  • 印象笔记
    3 引用 • 16 回帖 • 1 关注
  • Gzip

    gzip (GNU zip)是 GNU 自由软件的文件压缩程序。我们在 Linux 中经常会用到后缀为 .gz 的文件,它们就是 Gzip 格式的。现今已经成为互联网上使用非常普遍的一种数据压缩格式,或者说一种文件格式。

    9 引用 • 12 回帖 • 184 关注
  • Jenkins

    Jenkins 是一套开源的持续集成工具。它提供了非常丰富的插件,让构建、部署、自动化集成项目变得简单易用。

    54 引用 • 37 回帖 • 1 关注
  • JetBrains

    JetBrains 是一家捷克的软件开发公司,该公司位于捷克的布拉格,并在俄国的圣彼得堡及美国麻州波士顿都设有办公室,该公司最为人所熟知的产品是 Java 编程语言开发撰写时所用的集成开发环境:IntelliJ IDEA

    18 引用 • 54 回帖 • 2 关注
  • 数据库

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

    346 引用 • 760 回帖
  • webpack

    webpack 是一个用于前端开发的模块加载器和打包工具,它能把各种资源,例如 JS、CSS(less/sass)、图片等都作为模块来使用和处理。

    42 引用 • 130 回帖 • 251 关注
  • 程序员

    程序员是从事程序开发、程序维护的专业人员。

    593 引用 • 3533 回帖
  • Firefox

    Mozilla Firefox 中文俗称“火狐”(正式缩写为 Fx 或 fx,非正式缩写为 FF),是一个开源的网页浏览器,使用 Gecko 排版引擎,支持多种操作系统,如 Windows、OSX 及 Linux 等。

    7 引用 • 30 回帖 • 365 关注
  • 链书

    链书(Chainbook)是 B3log 开源社区提供的区块链纸质书交易平台,通过 B3T 实现共享激励与价值链。可将你的闲置书籍上架到链书,我们共同构建这个全新的交易平台,让闲置书籍继续发挥它的价值。

    链书社

    链书目前已经下线,也许以后还有计划重制上线。

    14 引用 • 257 回帖 • 4 关注
  • ActiveMQ

    ActiveMQ 是 Apache 旗下的一款开源消息总线系统,它完整实现了 JMS 规范,是一个企业级的消息中间件。

    19 引用 • 13 回帖 • 687 关注
  • 生活

    生活是指人类生存过程中的各项活动的总和,范畴较广,一般指为幸福的意义而存在。生活实际上是对人生的一种诠释。生活包括人类在社会中与自己息息相关的日常活动和心理影射。

    230 引用 • 1432 回帖
  • CAP

    CAP 指的是在一个分布式系统中, Consistency(一致性)、 Availability(可用性)、Partition tolerance(分区容错性),三者不可兼得。

    12 引用 • 5 回帖 • 635 关注
  • SpaceVim

    SpaceVim 是一个社区驱动的模块化 vim/neovim 配置集合,以模块的方式组织管理插件以
    及相关配置,为不同的语言开发量身定制了相关的开发模块,该模块提供代码自动补全,
    语法检查、格式化、调试、REPL 等特性。用户仅需载入相关语言的模块即可得到一个开箱
    即用的 Vim-IDE。

    3 引用 • 31 回帖 • 114 关注