轉載聲明：本文由你的代碼有問題部落客原創文章，經部落客允許轉載。
http://www.cnblogs.com/javaIOException/p/7524945.html
MySQL千萬級資料分區存儲及查詢優化
作為傳統的關系型資料庫，MySQL因其體積小、速度快、總體擁有成本低受到中小企業的熱捧，但是對于大資料量(百萬級以上)的操作顯得有些力不從心，這裡我結合之前開發的一個web系統來介紹一下MySQL資料庫在千萬級資料量的情況下如何優化提升查詢速度。
一、基本業務需求
該系統包括硬體系統和軟體系統，由中科院計算所開發的無線傳感器網絡負責實時資料的監測和回傳到MySQL資料庫，我們開發的軟體系統負責對資料進行實時計算，可視化展示及異常事件報警監測。宮殿的溫濕度等資料都存儲在data表中，由于業務需要，data表中舊的資料要求不能删除，經過初步估算，一年的資料量大概為1200萬條，之前的系統當資料量到達百萬級時查詢響應速度很慢，導緻資料加載延遲很大，是以很有必要進行資料庫的優化查詢，提升響應速度。
結合故宮溫濕度監測系統EasiWeb 7.1的data表查詢，這裡主要從以下三個方面詳解MySQL的分區優化技術：
(1)EasiWeb 7.1系統data表基于分表、分區和索引的優化方案對比。
(2)EasiWeb 7.1系統中采用的優化方案及實施步驟
(3)系統模拟産生1500萬資料的優化前後對比測試
二、data表優化方案選擇
針對故宮系統大資料量時提升響應速度及運作性能的問題，我們團隊通過研究和論證，提出了三種方案：
2.1 data表分表存儲，聯表查詢
原了解釋
分表即将一個表結構分解為多個子表，這些子表可以同一個資料庫下，也可以在不同的資料庫下，查詢的時候通過代碼控制，生成多條查詢語句，進行多項子表聯查，最後彙總結果，整體上的查詢結果與單表一樣，但平均相應速度更快。
實作方式
采用merge分表，劃分的标準可以選取時間(collectTime)作為參數。主表類似于一個殼子，邏輯上封裝了子表，實際上資料都是存儲在子表中。我們在每年的1月1日建立一個子表data_20XX，然後将這些子表union起來構成一個主表。對于插入操作，最新的資料将會被插入到最後一個子表中；對于查詢操作，通過'data'主表查詢的時候，查詢引擎會根據查詢語句口控制選取要查詢的子表集合，實作等效查詢。
優缺點
優點是merge分表可以很友善得實作分表，在進行查詢的時候封裝了查詢過程，使用者編寫的代碼較少。
缺點是破壞了data表的結構，并且在建立子表的時候由于定時器延遲，可能導緻個别資料被錯誤的存儲。
2.2 data表分區存儲
原了解釋
分區把存放資料的檔案分成了許多小塊，存儲在磁盤中不同的區域，通過提升磁盤I/O能力來提升查詢速度。分區不會更改data表的結構，發生變化的是存儲方式。
實作方式
采用range分區，根據資料的時間字段（collectTime）實作分區存儲，以年份為基準，不同的區域存儲的是不同年份的資料，可以采用合并語句進行分區的合并，分區操作由MySQL暗箱完成，從使用者的角度看，data表不會改變，程式代碼無需更改。
優缺點
優點是range分區實作友善，沒有破壞data表的結構，使用者無需更改dao層代碼和查詢方式。而且可以提前預設分區，比如今年是2017年，使用者可以将資料分區預設到2020年，方式靈活，便于擴充。
缺點是資料存儲依賴于分區的存儲磁盤，一旦磁盤損壞，則會造成資料的丢失。
2.3 data表更換為Myisam搜尋引擎
原了解釋
MySQL提供Myisam和InnoDB類型的搜尋引擎，兩種搜尋引擎側重點不同，可以根據實際的需要搭配使用，以達到最優的相應效果。Myisam引擎可以平均分布I/O，獲得更快的速度，InnoDB注重事務處理，适合高并發操作。
圖1 BTREE存儲結構
從圖中就可以看出，B+Tree的内部結點不存儲資料，隻存儲指針，而葉子結點則隻存儲資料，不存儲指針。并且在其每個葉子節點上增加了一個指向資料的指針。
MyISAM引擎的索引結構為B+Tree，其中B+Tree的資料域存儲的内容為實際資料的位址，也就是說它的索引和實際的資料是分開的，隻不過是用索引指向了實際的資料，這種索引就是所謂的非聚集索引。
實作方式
修改data表的搜尋引擎為Myisam，其它資料表不做更改。
優缺點
Myisam優點資料檔案和索引檔案可以放置在不同的目錄，平均分布I/O。缺點是不合适高并發操作，對事務處理(修改和删除操作)的支援較差。
InnoDB優點是提供了具有送出、復原和崩潰恢複能力的事務安全，适合高并發操作的事務處理。缺點是處理效率相對Myisam較差并且會占用更多的磁盤空間以保留資料和索引。
由于data表主要涉及的是查詢和插入操作，提高速度是第一需求，是以可以将data表的搜尋引擎改為Myisam。
2.4 綜合比較
實作方式
分表後的資料實際存儲在子表中，總表隻是一個外殼，merge分表編碼較少，更改了表的結構。
分區後的資料存儲的檔案分成了許多小塊，不更改表的結構。
提高性能
分表側重點是資料分表存儲，聯表查詢，注重提高MySQL的并發能力。
分區側重于突破磁盤的讀寫能力，進而達到提高MySQL性能的目的。
實作的難易度
采用merge分表與range分區兩種方式難易度差不多，如果是用其他分表方式就比分區更為複雜。
range分區實作是比較簡單的，建立分區的表和平常的表沒有什麼區。
兩種方式基本上對開發端代碼都是透明的。
三、最終采用的優化思路
故宮系統中的data表并發通路量不大，是以通過分表提高通路速度和并發效果不太顯著，而且還可能破壞原有表的結構。而分區可以提高磁盤的讀寫能力，配合Myisam搜尋引擎可以很大幅度提升查詢速度。
是以我們采用data表分區存儲+Myisam搜尋引擎+建立索引的方式來優化資料庫的查詢
四、實施步驟及相關操作簡介
實施步驟
1、将data表的搜尋引擎由InnoDB更改為MyISAM
ALTER TABLE `data` ENGINE=MyISAM;
2、建立以collectime、originAddr字段的BTREE索引
ALTER TABLE `data`
ADD INDEX `collectTime` (`collectTime`) USING BTREE ,
ADD INDEX `nodeId` (`originAddr`) USING BTREE ;
3、分區之前将collectTime由char類型改為datetime/date類型，才能進行分區操作(注:char類型不支援分區操作)。
ALTER TABLE `data`
MODIFY COLUMN `collectTime` datetime NOT NULL AFTER `ID`;
4、采用range分區可以保證分區均勻
注:這裡由于最早的資料從12年開始，我們采用了半年分一個區，預分區到2021年1月份，實際分區結合具體情況而定。
ALTER TABLE `data`
partition by range(to_days(collectTime))
(
partition P0 values less than (to_days('2012-01-01')),
partition P1 values less than (to_days('2012-07-01')),
partition P2 values less than (to_days('2013-01-01')),
partition P3 values less than (to_days('2013-07-01')),
partition P4 values less than (to_days('2014-01-01')),
partition P5 values less than (to_days('2014-07-01')),
partition P6 values less than (to_days('2015-01-01')),
partition P7 values less than (to_days('2015-07-01')),
partition P8 values less than (to_days('2016-01-01')),
partition P9 values less than (to_days('2016-07-01')),
partition P10 values less than (to_days('2017-01-01')),
partition P11 values less than (to_days('2017-07-01')),
partition P12 values less than (to_days('2018-01-01')),
partition P12 values less than (to_days('2018-07-01')),
partition P12 values less than (to_days('2019-01-01')),
partition P12 values less than (to_days('2019-07-01')),
partition P12 values less than (to_days('2020-01-01')),
partition P12 values less than (to_days('2020-07-01')),
partition P12 values less than (to_days('2021-01-01')),
)
5、分區情況查詢
SELECT * FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = schema()
AND TABLE_NAME='data';
圖2 檢視分區資訊
相關操作簡介
1、由于range分區函數無法識别char型字段，是以要在分區之前将collectTime由char類型改為datetime類型，才能進行range分區操作。
2、采用range分區時，要用to_days(collectime)的分區方式，采用這種方式，在查詢的時候隻會在相應的分區查找，而如果不加to_days(),在查詢的時候，會對全表進行掃描。
3、分區優化後，查詢速度提升主要展現在非跨區查詢的時候，當查詢條件均屬于一個區域時，資料庫可以快速定位到所查分區，而不會掃描全表。
4、每次插入資料的時候，資料庫會判定對應的collectTime屬于哪個分區，進而存儲到對應的分區中，不會影響其它分區。
五、1500萬資料測試對比
1.經過數十次的多條件跨區與不跨區查詢測試，相比沒有做優化的data表，優化後查詢速度提升90%以上。
2、不跨區域查詢響應速度<=0.5s，跨區查詢在第一次比較慢，但之後在翻頁查詢的時候，相應速度<=1s。
3、查詢的時候由于計算機性能差異，是以同樣的查詢在不同的機器上查詢速度會有所不用，我們采用的測試環境為i7 4500U酷睿 2核 4線程處理器 8G RAM 普通硬碟。
表1 data表查詢對比測試結果
| 編号 | 測試條件 | collectTime字段 | 優化前 查詢時間 | 優化後 查詢時間 |
| 1 | NodeID,data1,data2,collectTime | 2017/1/19—2017/4/19 | 2.59s | 0.74s |
| 2 | NodeID,data1,data2,collectTime | 2017/1/19—2017/7/19 | 16.64s | 0.96s |
| 3 | NodeID,data1,data2,collectTime | 2016/8/19—2017/2/19 | 34.3s | 2.49s |
| 4 | NodeID,data1,data2,collectTime | 2016/1/19—2017/1/19 | 46.52s | 2.50s |
| 5 | NodeID,data1,data2,collectTime | 2015/2/19—2017/3/19 | 78.12s | 3.73s |
| 6 | NodeID,data1,data2,collectTime | 2015/3/19—2017/4/19 | 250.33s | 4.42s |
| 7 | NodeID,data1,data2,collectTime | 2015/1/19—2017/4/19 | 226.10s | 4.39s |
| 8 | NodeID,data1,data2,collectTime | 2014/4/19—2017/4/19 | 410.22s | 5.50s |
| 9 | NodeID,data1,data2,collectTime | 2014/2/19—2017/4/19 | 437.50s | 5.50s |
| 10 | NodeID,data1,data2,collectTime | 2014/1/19—2017/4/19 | 558.05s | 5.70s |
| 11 | NodeID,data1,data2,collectTime | 2012/4/19—2017/4/19 | --（響應時間過長） | 8.70s |
六、測試查詢資料部分截圖