1. 背景 —— 公司物聯網項目

海量設備通過物聯網服務接入雲端，設備每30s上報一次自身數據（以下稱為動態數據）。 物聯網服務將設備上報的數據轉發給數據處理網關，由數據入庫網關執行批量入庫操作插入數據庫。 項目大致技術架構如下圖：

2. 問題

接入的設備數量較大時，上報的動態數據數據量過大，導致單表查詢過慢。

假設有1萬臺設備，每臺設備每30秒上報一次動態數據，那每分鐘就會產生2萬條數據，每天會產生2880萬條數據，一年將會產生100億條以上的數據。

這麼大的數據量如果進行單表查詢數據庫分析等操作延遲是完全無法接受的，故需要尋找一種解決方案。

3. 技術背景

3.1 分表

這裡的分表指的是根據設備的序列號將一定數量的設備拆分存儲在不同的表中，減少單表的數據量級。

3.2 分區

MySql數據庫中的數據是以文件的形勢存在磁盤上的，默認放在/mysql/data下面（可以通過my.cnf中的datadir來查看）。

一張表主要對應著三個文件，一個是frm存放表結構，一個是myd存放表數據，一個是myi存表索引。如果一張表的數據量太大，mydmyi就會變的很大，查找數據就會變的很慢。

MySql的分區功能，在物理上將這一張表對應的三個文件，分割成許多個小塊，這樣查找一條數據時，就不用全部查找了，只要知道這條數據在哪一塊，然後在那一塊找就行了，這樣就可以很大的提高數據查詢的效率。

MySql5.1及以上版本支持分區功能。 MySql的分區方法主要有：

3.2.1 range分區：

range分區意思就是以某個字段為基準的連續分區，比如id小於3的1個分區，id小於6的一個分區，id小於100的一個分區。

3.2.2 list分區：

list分區就是以某個字段為基準，該字段從屬於一個列表範圍內的分區，比如id為1,3,5,7的一個分區，2,4,6,8的一個分區。

3.2.3 hash分區：

hash分區用於確保數據在預先設定數目的分區中平均分佈，比如預先設置分區數量為3個，則所有數據都會被平均分佈在3個分區內。

3.2.4 key分區：

按照KEY進行分區類似於按照HASH分區，除了HASH分區使用的用戶定義的表達式，而KEY分區的 哈希函數是由MySQL 服務器提供。

3.2.5 子分區：

子分區是分區表中每個分區的再次分割，子分區既可以使用HASH希分區，也可以使用KEY分區。這也被稱為複合分區（composite partitioning）。 子分區需遵循以下規則：

• 如果一個分區中創建了子分區，其他分區也要有子分區
• 如果創建了了分區，每個分區中的子分區數必有相同
• 同一分區內的子分區，名字不相同，不同分區內的子分區名子可以相同（5.1.50不適用）

4. 解決方案

4.1 分表設計

設計為每1000個設備一張表，表名為t_data_序號。

假設有1萬臺設備，則根據設備序列號將數據分散存儲在t_data_1 ~ t_data_10 十張表中。

同時增加一張設備-動態數據關係表（表名t_device_table_map）來存儲設備和動態數據表的關係，以便對設備數據做增刪改查操作時能找到它對應的表，t_device_table_map表的結構如下：

應用平臺導入設備時，根據設備數量判斷導入設備的動態數據應該存儲在哪張表，並將設備和動態數據表關係寫入到t_device_table_map中。數據處理啟動時載入t_device_table_map表數據到自己的內存中，然後在將設備上報的數據入庫前從自身內存讀取該設備屬於哪個動態數據表，再組裝Sql執行入庫操作。

4.2 分區設計

由於設備的數據是持續性上報的，所以考慮使用Range分區。

分區設計為以數據採集時間為基礎，每週一個分區，每張表預設10年的分區。 按每個設備每30秒上報一條數據計算，每個分區大約有 10002460*2 = 2880000條數據。

建表語句如下：

CREATE TABLE `t_data_1` (
 `i_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `i_status` bit(1) DEFAULT NULL,
 `c_device_sequence` varchar(32) DEFAULT NULL COMMENT '設備序列號',
 `t_collect_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '數據採集時間',
 ...
 PRIMARY KEY (`i_id`,`t_collect_time`),
 KEY `index_c_device_sequence` (`c_device_sequence`,`t_collect_time`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=398404 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(t_collect_time)
(PARTITION p20171224 VALUES LESS THAN ('2017-12-24 00:00:00') ENGINE = MyISAM,
 PARTITION p20171231 VALUES LESS THAN ('2017-12-31 00:00:00') ENGINE = MyISAM,
 PARTITION p20180107 VALUES LESS THAN ('2018-01-07 00:00:00') ENGINE = MyISAM,
 ... 

 PARTITION p20271212 VALUES LESS THAN ('2027-12-12 00:00:00') ENGINE = MyISAM,
 PARTITION p20271219 VALUES LESS THAN ('2027-12-19 00:00:00') ENGINE = MyISAM) */;
/*!40101 SET character_set_client = @saved_cs_client */;

5. 測試

以120萬條數據測試，分表（10張）分區查詢時間為0.1秒左右，見下圖：

不分表也不分區，查詢時間需要1秒以上，見下圖：

分表分區帶來的性能提升是很明顯的。

6. 思考

分區的數量是不是越多越好呢？肯定不是的。

因為MySQL在執行查詢操作的時候首先要去檢索查詢範圍在哪些分區內，分區太多，這部分的操作耗時就增加了。此外分區過多，可能會導致內存佔用升高的問題。

怎麼樣分區，分多少個區才最合適，還需要長期的觀察和大量數據的實驗。關注、轉發、評論頭條號每天分享java 知識，私信回覆“555”贈送一些Dubbo、Redis、Netty、zookeeper、Spring cloud、分佈式資料

閱讀更多 Java小可愛 的文章

關鍵字: 上報 物聯網 技術