MySQL 5.5 分區性能

分區歷史：
Mysql5.1.3版本帶著分區功能與大家見面了，在這之前如果想分表，可以先建立單獨的表，再使用Merge引擎聯合各表。Mysql分區功能走在老大ORACLE的后面，不過性能也不及oracle。

導讀：
分區是一把雙刃劍，在應用的時候要根據業務場景指定特定的規則。分區主要功能用于切分數據，用于保證在大數量的情況能快速的定位記錄，前提是分區規則適合你的應用，其實就好比如果你要猜謎一樣，對方給了一個提示，對于你找到謎底相對簡單多了。

分區類型\應用場景：
四種類型：RANGE、LIST、COLUMN（range\list）、HASH、KEY
應用場景：存儲歷史記錄，大數量的在線業務，數據分析系統，主要用于傳統業務場景，另外請與sharding區分開來。

注意事項：

• 如果表中存在主鍵，則分區函數必須包含主鍵部分
• 各個分區對待分區列值為NULL的方式不同
• 官方非用戶重新編譯mysql版本最大分區數為1024
• Drop partition會將該分區的所有數據刪除
• 目前子分區都必須是同一引擎
• timestamp字段作為partition的分區列是不被允許的，但是可以使用UNIX_TIMESTAMP（）解決該問題，請參考Bug #42849,已經被加入到解決列表中,具體解決時間不清楚
• 分區表達式目前只支持一些函數，具體參考Partitioning Limitations Relating to Functions
• 分區不能太多，要適度。最好再你的生產服務器上測試，接下來會對折部分測試

實例測試：
目的：測試分區數為1,16,128,256,512,1024的各種情況下,INSERT和UPDATE有多大區別？
環境及數據：mysql5.5,mysql5.1,MEM 62G,2.5T，RHEL5 x86_64
innodb_additional_mem_pool_size=16M

建立一張測試表sbtest_part, 數據1024000行，以id作為RANGE分區列

CREATE TABLE `sbtest_part` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT ’0′,
`c` char(120) NOT NULL DEFAULT ”,
`pad` char(60) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysqlslap –delimiter=”;” –number-of-queries=2048 –iterations=1000 –query=”use test;INSERT INTO sbtest_part(k,c,pad) VALUES(1,’cccccckkkkk’,'pppppppkkkkkk’)” –user=root –socket=/data/mysqldata3307/sock/mysql.sock >> /tmp/16_test_insert.txt

mysqlslap –delimiter=”;” –number-of-queries=2048 –iterations=1000 –query=”use test;SET @a=FLOOR(1+RAND()*1024000);UPDATE sbtest_part SET c=’ppppkkk’ WHERE id=@a;” –user=root –socket=/data/mysqldata3307/sock/mysql.sock >> /tmp/16_test_update_c.txt

mysqlslap –delimiter=”;” –number-of-queries=2048 –iterations=1000 –query=”use test;SET @a=FLOOR(1+RAND()*1024000);UPDATE sbtest_part SET k=@a WHERE id=@a;” –user=root –socket=/data/mysqldata3307/sock/mysql.sock >> /tmp/16_test_update_k.txt

結果：

在1,16,128,256,512,1024個分區情況下中，兩個相鄰分區數之間增加的百分比對比表：

根據以上測試，初步可以判斷分區數在128-256這個區間對INSERT、UPDATE操作影響比較大，所以在部署之前就要考慮這些效率問題。

當分區數在512、1024時，經過show processlist查看System lock、closing tables這兩種狀態在耗時比較長，這個應該是由于分區表數目過大。

另外模擬個情景：在100W數據或者更多的情況下，經常我們有這樣的需求查找某段時間之內,某個任務的某個狀態的那些人的所有信息？

表結構：

CREATE TABLE `task_1` (
`UID` bigint(20) unsigned NOT NULL DEFAULT ’0′,
`TDID` int(10) unsigned NOT NULL,
`TYPE` tinyint(3) unsigned NOT NULL DEFAULT ’0′,
`s` tinyint(3) unsigned NOT NULL DEFAULT ’0′,
`date` int(8) unsigned NOT NULL DEFAULT ’0′,
PRIMARY KEY (`UID`,`TDID`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (UID)
PARTITIONS 16 */

版本：5.1和5.5
我們會有兩種方案：

• 為task_1建立索引(date,tdid,s)：
• 創建一張臨時表task_2 ，只有列(date,tdid,s,uid),并對這些列做聯合主鍵，再uid連接task_1，這樣的好處是創建一個global index

第一種方案利于在搜索更加快速，弊于索引維護成本高，會跨分區進行索引IO會增大，而且在5.1生產環境上鎖表時間長；方案二利處不修改原表，而且能夠大幅提高SELECT性能，弊處冗余了數據

文章出處：http://www.mysqlops.com/2011/10/27/mysql5-5-partition-performance.html