mysql，sqlserver資料庫單表資料過大的處理方式

經常混迹于技術社群，頻繁看到這個題目，今天幹脆在自己部落格重複一遍解決辦法：

針對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

 文章的更新編輯依此連結為準。歡迎關注源站原創文章！