分區(qū)歷史：
Mysql5.1.3版本帶著分區(qū)功能與大家見面了，在這之前如果想分表，可以先建立單獨的表，再使用Merge引擎聯(lián)合各表。Mysql分區(qū)功能走在老大ORACLE的后面，不過性能也不及oracle。
導(dǎo)讀：
分區(qū)是一把雙刃劍，在應(yīng)用的時候要根據(jù)業(yè)務(wù)場景指定特定的規(guī)則。分區(qū)主要功能用于切分?jǐn)?shù)據(jù)，用于保證在大數(shù)量的情況能快速的定位記錄，前提是分區(qū)規(guī)則適合你的應(yīng)用，其實就好比如果你要猜謎一樣，對方給了一個提示，對于你找到謎底相對簡單多了。
分區(qū)類型\應(yīng)用場景：
四種類型：RANGE、LIST、COLUMN（range\list）、HASH、KEY
應(yīng)用場景：存儲歷史記錄，大數(shù)量的在線業(yè)務(wù)，數(shù)據(jù)分析系統(tǒng)，主要用于傳統(tǒng)業(yè)務(wù)場景，另外請與sharding區(qū)分開來。
注意事項：
實例測試：
目的：測試分區(qū)數(shù)為1,16,128,256,512,1024的各種情況下,INSERT和UPDATE有多大區(qū)別？
環(huán)境及數(shù)據(jù)：mysql5.5,mysql5.1,MEM 62G,2.5T，RHEL5 x86_64
innodb_additional_mem_pool_size=16M
建立一張測試表sbtest_part, 數(shù)據(jù)1024000行，以id作為RANGE分區(qū)列
1 | CREATE TABLE `sbtest_part` ( |
2 | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, |
3 | `k` int(10) unsigned NOT NULL DEFAULT ’0′, |
4 | `c` char(120) NOT NULL DEFAULT ”, |
5 | `pad` char(60) NOT NULL DEFAULT ”, |
6 | PRIMARY KEY (`id`), |
7 | KEY `k` (`k`) |
8 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 | 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 |
2 |
3 | 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 |
4 |
5 | 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 |
結(jié)果：
| 分區(qū)數(shù) | INSERT平均時間 | UPDATE c平均時間 | UPDATE k平均時間 |
| 1 | 0.155 | 0.140 | 0.143 |
| 16 | 0.184 | 0.133 | 0.163 |
| 64 | 0.267 | 0.170 | 0.207 |
| 128 | 0.338 | 0.232 | 0.259 |
| 256 | 0.524 | 0.355 | 0.409 |
| 512 | 0.900 | 0.667 | 0.698 |
| 1024 | 1.603 | 1.529 | 1.521 |
在1,16,128,256,512,1024個分區(qū)情況下中，兩個相鄰分區(qū)數(shù)之間增加的百分比對比表：
| 分區(qū)數(shù) | INSERT平均時間增加的百分比(%) | UPDATE c平均時間增加的百分比(%) | UPDATE k平均時間增加的百分比(%) |
| 1 | / | / | / |
| 16 | 18.7 | 誤差 | 14.0 |
| 64 | 45.1 | 27.8 | 27.0 |
| 128 | 26.6 | 36.5 | 25.1 |
| 256 | 55.0 | 53.0 | 57.9 |
| 512 | 71.8 | 87.9 | 70.7 |
| 1024 | 78.1 | 129.2 | 117.9 |
根據(jù)以上測試，初步可以判斷分區(qū)數(shù)在128-256這個區(qū)間對INSERT、UPDATE操作影響比較大，所以在部署之前就要考慮這些效率問題。
當(dāng)分區(qū)數(shù)在512、1024時，經(jīng)過show processlist查看System lock、closing tables這兩種狀態(tài)在耗時比較長，這個應(yīng)該是由于分區(qū)表數(shù)目過大。
另外模擬個情景：在100W數(shù)據(jù)或者更多的情況下，經(jīng)常我們有這樣的需求查找某段時間之內(nèi),某個任務(wù)的某個狀態(tài)的那些人的所有信息？
表結(jié)構(gòu)：
01 | CREATE TABLE `task_1` ( |
02 | `UID` bigint(20) unsigned NOT NULL DEFAULT ’0′, |
03 | `TDID` int(10) unsigned NOT NULL, |
04 | `TYPE` tinyint(3) unsigned NOT NULL DEFAULT ’0′, |
05 | `s` tinyint(3) unsigned NOT NULL DEFAULT ’0′, |
06 | `date` int(8) unsigned NOT NULL DEFAULT ’0′, |
07 | PRIMARY KEY (`UID`,`TDID`,`date`) |
08 | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
09 | /*!50100 PARTITION BY HASH (UID) |
10 | PARTITIONS 16 */ |
版本：5.1和5.5
我們會有兩種方案：
| 版本 | 5.1版本 | 5.5版本 | ||
| task_1建立key(date,tdid)耗時 | task_1沒有索引耗時 | task_1建立key(date,tdid)耗時 | task_1沒有索引耗時 | |
| 方案1 | 0.00 | 0.70 | 0.04 sec | 0.62 sec |
| 方案2 | 0.17 | 0.11 | 0.13 sec | 0.13 sec |
第一種方案利于在搜索更加快速，弊于索引維護成本高，會跨分區(qū)進行索引IO會增大，而且在5.1生產(chǎn)環(huán)境上鎖表時間長；方案二利處不修改原表，而且能夠大幅提高SELECT性能，弊處冗余了數(shù)據(jù)
文章出處：http://www.mysqlops.com/2011/10/27/mysql5-5-partition-performance.html
