352      阿裏雲  技術社區[雲棲]

MySql分區表性能測試及切換案例

背景

互聯網公司的業務變化很快，數據庫表結構設計相對比較直接，很少會在前期設計的很完善。當業務存活並發展起來後，就需要在擴展性、安全性等方麵進行改進。

比如，我們一張記錄用戶狀態的表，存儲在RDS for MySql（InnoDB存儲引擎）中。此業務表最近膨脹到1.5億條記錄，存儲占用30多G，且數據還在不斷增長。

雖然目前整體性能表現尚可，但部分操作耗時越來越長，鎖表衝突事件也開始出現。考慮到數據量的快速增長，以及數據庫本身的雪崩特點，我們認為這張表存在很大的性能風險，急需優化。

性能分析

下麵我們深入分析MySql InnoDB表數據量大小對CRUD及DDL操作的性能影響：

• 插入數據：由於使用自增列作為主鍵（大數據表比較推薦這種方式，索引占用存儲空間會大幅減少），增加業務數據的消耗時間為O(1)。但由於各索引數據需要排序，因此增加索引數據的消耗時間為O(logN)；

• 查詢數據：如果通過索引查詢，消耗時間為O(logN)；如果不通過索引查詢，消耗時間為O(N)；

• 刪除數據：消耗時間與查詢數據表現一致，但**需要特別注意鎖的問題**。雖然我們經常說InnoDB是行鎖，但這種說法是針對MyISAM的表鎖而言。實際上，InnoDB的“行鎖”是“索引鍵鎖”，其鎖機製的實現是基於索引鍵實現的。如果刪除數據時沒有匹配到索引鍵，或即使匹配上索引鍵，但如果索引鍵匹配的數據記錄過多，依然會導致大範圍加鎖從而引起訪問衝突，極大的降低係統性能；

• 更新數據：消耗時間和加鎖分析基本與刪除數據一致，隻是多了個索引的重排序；

• DDL操作：MySql 從5.6開始支持在線DDL操作。其原理是進行DDL操作前，將原始數據文件拷貝到新文件中，然後執行DDL操作。同時，將DML操作日誌存儲到緩存中，待DDL執行完成後再執行到新的數據文件中。那麼，當表數據量較大時，數據文件拷貝時間必然較長，如果這期間存在大量的DML操作，超過緩存上限，則DDL操作會失敗。

• DML(Data Manipulation Language)，數據操縱語言，包括UPDATE、INSERT、DELETE，表示對數據記錄層麵的操作
• DDL(Data Definition Language)，數據定義語言，主要包括CREATE、ALTER、DROP等，表示對表結構層麵的操作

優化方案

通過上麵的分析，我們可以看出，InnoDB表數據量過大對各種操作都存在較大的性能影響。針對這些問題，有以下三種優化方案：

將此業務表切換到分布式數據庫產品中

此方案最簡單直接。但我們的業務中，隻有此表數據量較大且需要查詢詳細單據。僅為了一張表就引入一種存儲機製，考慮到運維和經濟成本，總覺得不劃算。另外，此表還與其他表有一定的聯合查詢操作，分離出去後會增加應用層的複雜度；

對此業務表分庫分表

分庫分表是處理大數據表的利器。但我們數據庫係統的CPU和IO資源很富餘（CPU僅10%，IOPS僅300多），完全沒有分庫的必要。而分表會使得應用層的修改工作量巨大，代碼的可讀性也會變差。如果為了業務層的邏輯清晰再引入中間件進行代理訪問，又有殺雞用牛刀之感；

使用MySql自帶的分區表

分區表是MySql 5.1引入的特性。根據官網alter-table-partition-operations的介紹，其本質是將分庫分表直接集成到MySql中。我們知道，傳統的分庫分表功能，存在業務層、中間件、數據庫三層：業務層通過調用中間件的API訪問數據庫，不知道具體的物理存儲細節；中間件將一張很大的邏輯表映射到數據庫中多張較小的物理表，並對業務層的訪問請求進行分解後分別放到對應物理庫中執行，再將執行結果在中間件合並後返回給業務層，從而對業務層屏蔽物理存儲細節；數據庫則提供實際的物理存儲。

而MySql的分區表，借助MySql本身的邏輯架構，將分庫分表功能進行了下沉。MySql邏輯架構中的客戶端即對應業務層，Server層對應中間件層，存儲引擎層對應物理存儲層。簡單的說，分庫表就是我們在數據庫層麵看到是一張表，但物理上是分成多個文件獨立存儲。

邏輯上分析，分區表的優點很明顯：既能解決大數據量的性能問題，又能對應用層無縫切換。但是，其真實性能表現和穩定性到底怎麼樣? 還是得通過測試來驗證。

分區表性能測試

為方便說明，我們將此業務表邏輯結構簡化為隻包含以下4列：id(自增列)，depart_id(部門ID)，user_id(員工ID)，mark(員工業績)。

1. 非分區表 table_no_par：物理包含以上4列，其中 id 為主鍵，depart_id+user_id存在唯一鍵索引，mark列上有索引；
2. 分區表 table_par：物理包含以上4列，其中id為索引，depart_id+user_id存在唯一鍵索引，mark列上有索引。分區表以depart_id作為分區鍵，hash水平分成100個區。

• 分區表中id作為索引而不是主鍵，原因是由於MySql要求分區鍵必須包含主鍵和唯一鍵索引，但實際上id作為自增列並不具有業務意義，不適合作為分區鍵。同時為了保證分區表和非分區表邏輯結構一致，需要在分區表創建id列，並在其上創建索引及自增。
• 盡管分區表的user_id字段在業務上不應為NULL, 但為了避免 depart_id+user_id 的唯一鍵索引被MySql用作聚簇索引，也需要將其設置可為NULL。這樣一方麵可以減少分區表占用的存儲空間；另一方麵可以避免數據在DML時頻繁的進行頁麵分裂、合並、重組，優化寫入性能。

下麵分別針對 插入、查詢、DDL、存儲空間 等幾個關鍵性能指標進行測試（更新和刪除數據的性能表現與查詢數據比較一致，不單獨分析）。測試結果如下：

可以看出：

1. 分區表的插入性能較非分區表要差11%~15%，但這個差距隨著記錄數的增多而減少。也就是說，隨著數據量的增大，分區表和非分區表的插入性能越來越接近。
2. 查詢性能方麵，如果按照分區鍵查詢，分區表比非分區表的性能要好20%，而且數據量越大性能差距越大；如果按照非分區鍵的索引查詢，非分區表的性能表現更好。
3. DDL性能，通過新增索引的執行時間來看分區表優於非分區表，這是由於分區表的索引是針對單個分區，排序的基數少。尤其是針對大數據量表，N條數據排序消耗時間為O(N·logN)，大於100次N/100條數據排序100·O(N/100·logN/100)。至於DDL執行失敗的情況，測試環境未模擬出。但在實際生產環境中，確實是存在這個風險的。這一點分區表和非分區表是一致的。
4. 存儲空間方麵，分區表會比非分區表占用多一些，但隨著數據量的增大，差距越來越小。一方麵是由於每條記錄非分區表比分區表少6字節的聚簇索引；另一方麵是由於在存儲引擎層麵，分區表是100張表，在申請頁麵時各自獨立申請，導致頁麵空間中未分配空間要更多。但大數據表兩者差異不大。

通過以上的分析，我們得到以下的結論：

針對大數據表，分區表的插入性能、存儲空間與非分區表基本一致，查詢性能在分區鍵上比非分區鍵好，DDL執行時間比非分區表短。

於是，我們認為可以通過將非分區表切換到分區表來降低該數據表存在的性能風險。

分區表切換

為了避免業務中斷，我們參考pt-online-schema-change的模式進行切換。

MySql在5.5及以前的版本，對Online DDL支持不太好，會導致鎖表。因此，percona推出pt-online-schema-change，利用觸發器實現在DDL過程中不會造成讀寫阻塞。

具體步驟如下：

1. 整改數據表的使用模式，所有操作均必須帶上擬作為分區鍵的字段；
2. 創建分區表，其邏輯結構與非分區表完全一致；
3. 在非分區表上對update、delete動作創建觸發器，將其update、delete動作在分區表也執行一次；
4. 根據自增列ID，將非分區表中的數據分批次順序拷貝到分區表中；
5. 最後一批次數據拷貝完成後，立即通過rename將分區表與非分區表的表名互換；
6. 刪除觸發器，切換完成。

切換完成後，我們進行了一周的性能觀察：CPU維持在10%，IOPS有8%左右的下降，存儲空間有3%的上升。
單純從整體的性能指標來看，切換前後變化並不是特別明顯。但之前耗時越來越長的操作，耗時穩定了下來，鎖表衝突事件也基本沒有再出現。通過PARTITIONS的分析，最大的一個分區也隻有500萬行。即使數據量再擴大10倍，最大分區的數據量也才5000萬，對於單個存儲引擎文件來說，這完全無壓力，理論上性能表現也不會出現大幅下滑。但如果放到非分區表，估計業務高峰流量稍微一衝擊，或者硬件性能出現波動（在資源共享的雲計算環境中，較為常見），就有崩潰的風險。

目前，分區表已經穩定在生產環境運行了近一個月。

總體來看，切換分區表比較好的解決了我們當前對於數據量快速增長的數據庫性能的擔憂，至少數據量再增長2、3倍應該是能扛住的。但它是否能如我們預期的在高並發下支持10倍數據量（即單表15億記錄）而性能表現依然穩定，仍有待實踐證明。

最後更新：2017-05-03 12:30:39

  上一篇： Java基本語法-----java數據類型的轉換

  下一篇： 掃碼領紅包係統功能開發