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

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

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

针对 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 回帖 • 1 关注
  • 数据库

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

    346 引用 • 757 回帖 • 1 关注

相关帖子

欢迎来到这里!

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

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

推荐标签 标签

  • Markdown

    Markdown 是一种轻量级标记语言,用户可使用纯文本编辑器来排版文档,最终通过 Markdown 引擎将文档转换为所需格式(比如 HTML、PDF 等)。

    172 引用 • 1538 回帖
  • 黑曜石

    黑曜石是一款强大的知识库工具,支持本地 Markdown 文件编辑,支持双向链接和关系图。

    A second brain, for you, forever.

    24 引用 • 246 回帖
  • IDEA

    IDEA 全称 IntelliJ IDEA,是一款 Java 语言开发的集成环境,在业界被公认为最好的 Java 开发工具之一。IDEA 是 JetBrains 公司的产品,这家公司总部位于捷克共和国的首都布拉格,开发人员以严谨著称的东欧程序员为主。

    181 引用 • 400 回帖
  • OpenCV
    15 引用 • 36 回帖
  • 外包

    有空闲时间是接外包好呢还是学习好呢?

    26 引用 • 233 回帖
  • 京东

    京东是中国最大的自营式电商企业,2015 年第一季度在中国自营式 B2C 电商市场的占有率为 56.3%。2014 年 5 月,京东在美国纳斯达克证券交易所正式挂牌上市(股票代码:JD),是中国第一个成功赴美上市的大型综合型电商平台,与腾讯、百度等中国互联网巨头共同跻身全球前十大互联网公司排行榜。

    14 引用 • 102 回帖 • 314 关注
  • Ubuntu

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

    127 引用 • 169 回帖
  • 智能合约

    智能合约(Smart contract)是一种旨在以信息化方式传播、验证或执行合同的计算机协议。智能合约允许在没有第三方的情况下进行可信交易,这些交易可追踪且不可逆转。智能合约概念于 1994 年由 Nick Szabo 首次提出。

    1 引用 • 11 回帖 • 2 关注
  • Gitea

    Gitea 是一个开源社区驱动的轻量级代码托管解决方案,后端采用 Go 编写,采用 MIT 许可证。

    5 引用 • 16 回帖 • 1 关注
  • 服务

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

    41 引用 • 24 回帖
  • 锤子科技

    锤子科技(Smartisan)成立于 2012 年 5 月,是一家制造移动互联网终端设备的公司,公司的使命是用完美主义的工匠精神,打造用户体验一流的数码消费类产品(智能手机为主),改善人们的生活质量。

    4 引用 • 31 回帖 • 2 关注
  • SQLServer

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

    21 引用 • 31 回帖 • 2 关注
  • WiFiDog

    WiFiDog 是一套开源的无线热点认证管理工具,主要功能包括:位置相关的内容递送;用户认证和授权;集中式网络监控。

    1 引用 • 7 回帖 • 614 关注
  • 安装

    你若安好,便是晴天。

    132 引用 • 1184 回帖
  • 持续集成

    持续集成(Continuous Integration)是一种软件开发实践,即团队开发成员经常集成他们的工作,通过每个成员每天至少集成一次,也就意味着每天可能会发生多次集成。每次集成都通过自动化的构建(包括编译,发布,自动化测试)来验证,从而尽早地发现集成错误。

    15 引用 • 7 回帖 • 1 关注
  • 30Seconds

    📙 前端知识精选集,包含 HTML、CSS、JavaScript、React、Node、安全等方面,每天仅需 30 秒。

    • 精选常见面试题,帮助您准备下一次面试
    • 精选常见交互,帮助您拥有简洁酷炫的站点
    • 精选有用的 React 片段,帮助你获取最佳实践
    • 精选常见代码集,帮助您提高打码效率
    • 整理前端界的最新资讯,邀您一同探索新世界
    488 引用 • 384 回帖 • 3 关注
  • BND

    BND(Baidu Netdisk Downloader)是一款图形界面的百度网盘不限速下载器,支持 Windows、Linux 和 Mac,详细介绍请看这里

    107 引用 • 1281 回帖 • 37 关注
  • 博客

    记录并分享人生的经历。

    273 引用 • 2389 回帖 • 1 关注
  • 倾城之链
    23 引用 • 66 回帖 • 166 关注
  • Bootstrap

    Bootstrap 是 Twitter 推出的一个用于前端开发的开源工具包。它由 Twitter 的设计师 Mark Otto 和 Jacob Thornton 合作开发,是一个 CSS / HTML 框架。

    18 引用 • 33 回帖 • 650 关注
  • CloudFoundry

    Cloud Foundry 是 VMware 推出的业界第一个开源 PaaS 云平台,它支持多种框架、语言、运行时环境、云平台及应用服务,使开发人员能够在几秒钟内进行应用程序的部署和扩展,无需担心任何基础架构的问题。

    4 引用 • 16 回帖 • 196 关注
  • 以太坊

    以太坊(Ethereum)并不是一个机构,而是一款能够在区块链上实现智能合约、开源的底层系统。以太坊是一个平台和一种编程语言 Solidity,使开发人员能够建立和发布下一代去中心化应用。 以太坊可以用来编程、分散、担保和交易任何事物:投票、域名、金融交易所、众筹、公司管理、合同和知识产权等等。

    34 引用 • 367 回帖
  • Git

    Git 是 Linux Torvalds 为了帮助管理 Linux 内核开发而开发的一个开放源码的版本控制软件。

    211 引用 • 358 回帖
  • 爬虫

    网络爬虫(Spider、Crawler),是一种按照一定的规则,自动地抓取万维网信息的程序。

    106 引用 • 275 回帖
  • 前端

    前端技术一般分为前端设计和前端开发,前端设计可以理解为网站的视觉设计,前端开发则是网站的前台代码实现,包括 HTML、CSS 以及 JavaScript 等。

    246 引用 • 1338 回帖
  • 微服务

    微服务架构是一种架构模式,它提倡将单一应用划分成一组小的服务。服务之间互相协调,互相配合,为用户提供最终价值。每个服务运行在独立的进程中。服务于服务之间才用轻量级的通信机制互相沟通。每个服务都围绕着具体业务构建,能够被独立的部署。

    96 引用 • 155 回帖
  • FlowUs

    FlowUs.息流 个人及团队的新一代生产力工具。

    让复杂的信息管理更轻松、自由、充满创意。

    1 引用 • 2 关注