針對(duì)mysql，sqlserver等關(guān)系型數(shù)據(jù)庫(kù)單表數(shù)據(jù)過(guò)大的處理方式

如果不是阿里云的分布式數(shù)據(jù)庫(kù) DRDS 那種多機(jī)器集群方案的話： 先考慮表分區(qū) ；然后考慮分表 ；然后考慮分庫(kù)。

這個(gè)題目是我所經(jīng)歷過(guò)的，我的GPS汽車(chē)定位系統(tǒng)，早期就是選用的Sql Server數(shù)據(jù)庫(kù)。當(dāng)時(shí)我選取的方案就是第一種：表分區(qū)。 表分區(qū)的優(yōu)勢(shì)是，如果表結(jié)構(gòu)合理，可以不涉及到程序修改。也就是說(shuō)，對(duì)程序來(lái)講依然是單表讀寫(xiě)的效果！

所有軌跡數(shù)據(jù)存入到一個(gè)巨大的表里。有多大呢？

最大存儲(chǔ)量超過(guò)10億行。具體數(shù)值應(yīng)該是12億多點(diǎn)，由于系統(tǒng)設(shè)計(jì)為只存儲(chǔ)30天軌跡，所以線上期間最大存儲(chǔ)只到這個(gè)數(shù)，再后來(lái)采用云架構(gòu)，上云替換成非關(guān)系性數(shù)據(jù)庫(kù)，獲得了更高的寫(xiě)入性能和存儲(chǔ)壓縮能力。

每日寫(xiě)入量就超過(guò)1500萬(wàn)行。上下班交通高峰時(shí)候每秒寫(xiě)入量平均超過(guò)500行。也就是500iops，距離系統(tǒng)設(shè)計(jì)的壓測(cè)指標(biāo)3000還有一大截

這張大型單表設(shè)計(jì)要點(diǎn)：（一個(gè)聚集索引用于寫(xiě)入，一個(gè)聯(lián)合索引用于查詢，沒(méi)有主鍵，使用表分區(qū)）

明確主鍵用途：

真的需要查詢單行數(shù)據(jù)時(shí)候才需要主鍵！

我采用無(wú)主鍵設(shè)計(jì)，用于避免寫(xiě)入時(shí)候浪費(fèi)維護(hù)插入數(shù)據(jù)的性能。最早使用聚集的類似自增的id主鍵，壓測(cè)寫(xiě)入超過(guò)5億行的時(shí)候，寫(xiě)入性能縮減一半

準(zhǔn)確適用聚集：

寫(xiě)入的數(shù)據(jù)在硬盤(pán)物理順序上是追加，而不是插入！

我把時(shí)間戳字段設(shè)置為聚集索引，用于聚集寫(xiě)入目的設(shè)計(jì)。保證硬盤(pán)上的物理寫(xiě)入順序，不浪費(fèi)性能用于插入數(shù)據(jù)

職責(zé)足夠單一：

用于精準(zhǔn)索引！

使用時(shí)間+設(shè)備聯(lián)合索引，保證這張表只有一個(gè)查詢用途。保證系統(tǒng)只有一種查詢目的：按照設(shè)備號(hào)，查詢一個(gè)時(shí)間段的數(shù)據(jù)。

精確的表分區(qū)：

要求查詢時(shí)候限定最大量或者最大取值范圍！

按天進(jìn)行表分區(qū)，實(shí)現(xiàn)大數(shù)據(jù)量下的高效查詢。這里是本文重點(diǎn)，按照聚集索引進(jìn)行，可以讓目標(biāo)數(shù)據(jù)局限在更小的范圍進(jìn)行，雖然單表數(shù)據(jù)上億，但是查詢基本上只在某一天的的幾千萬(wàn)里進(jìn)行索引查詢

每張表會(huì)有各自的特點(diǎn)，不可生搬硬套，總結(jié)下我這張表的特點(diǎn)：

只增，不刪，不改！

關(guān)于不刪除中：每天使用作業(yè)刪除超過(guò)30天的那個(gè)分區(qū)數(shù)據(jù)除外，因?yàn)橐蹇张f的表分區(qū)，騰出新的表分區(qū)！

只有一個(gè)業(yè)務(wù)查詢：只按照設(shè)備編碼查詢某個(gè)時(shí)間段

只有一個(gè)運(yùn)維刪除：刪除舊的分區(qū)數(shù)據(jù)

參考文章： https://www.opengps.cn/Blog/View.aspx?id=284

總結(jié)

以上是生活随笔為你收集整理的MySQL单表数据量过大的处理方式经验的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。