MySQL分割槽（Partition）詳解
自5.1開始對分割槽(Partition)有支援，6.0應比較穩定
= 水平分割槽（根據列屬性按行分）=
舉個簡單例子：一個包含十年發票記錄的表可以被分割槽為十個不同的分割槽，每個分割槽包含的是其中一年的記錄。
=== 水平分割槽的幾種模式：===
* Range（範圍） – 這種模式允許DBA將資料劃分不同範圍。例如DBA可以將一個表透過年份劃分成三個分割槽，80年代（1980's）的資料，90年代（1990's）的資料以及任何在2000年（包括2000年）後的資料。
* Hash（雜湊） – 這中模式允許DBA透過對錶的一個或多個列的Hash Key進行計算，最後透過這個Hash碼不同數值對應的資料區域進行分割槽，。例如DBA可以建立一個對錶主鍵進行分割槽的表。
* Key（鍵值） – 上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
* List（預定義列表） – 這種模式允許系統透過DBA定義的列表的值所對應的行資料進行分割。例如：DBA建立了一個橫跨三個分割槽的表，分別根據2004年2005年和2006年值所對應的資料。
* Composite（複合模式） - 很神秘吧，哈哈，其實是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經進行了Range範圍分割槽的表上，我們可以對其中一個分割槽再進行hash雜湊分割槽。
= 垂直分割槽（按列分）=
舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分割槽，在保證它們資料相關性的同時還能提高訪問速度。
[分割槽表和未分割槽表試驗過程]
*建立分割槽表,按日期的年份拆分
mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
注意最後一行，考慮到可能的最大值
*建立未分割槽表
mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;
*透過儲存過程灌入800萬條測試資料
mysql> set sql_mode=''; /* 如果建立儲存過程失敗，則先需設定此變數, bug? */
mysql> delimiter // /* 設定語句終結符為 //，因儲存過程語句用;結束 */
mysql> CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end
//
mysql> delimiter ;
mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
* 測試SQL效能
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.55 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (4.69 sec)
結果表明分割槽表比未分割槽表的執行時間少90%。
* 透過explain語句來分析執行情況
mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
/* 結尾的\G使得mysql的輸出改為列模式 */
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)
explain語句顯示了SQL查詢要處理的記錄數目
* 試驗建立索引後情況
mysql> create index idx_of_c3 on no_part_tab (c3);
Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
mysql> create index idx_of_c3 on part_tab (c3);
Query OK, 8000000 rows affected (1 min 19.19 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
建立索引後的資料庫檔案大小列表：
2008-05-24 09:23 8,608 no_part_tab.frm
2008-05-24 09:24 255,999,996 no_part_tab.MYD
2008-05-24 09:24 81,611,776 no_part_tab.MYI
2008-05-24 09:25 0 part_tab#P#p0.MYD
2008-05-24 09:26 1,024 part_tab#P#p0.MYI
2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD
2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI
2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD
2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI
2008-05-24 09:25 0 part_tab#P#p11.MYD
2008-05-24 09:26 1,024 part_tab#P#p11.MYI
2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI
2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI
2008-05-24 09:26 25,585,696 part_tab#P#p4.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p4.MYI
2008-05-24 09:26 25,585,216 part_tab#P#p5.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p5.MYI
2008-05-24 09:26 25,655,740 part_tab#P#p6.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p6.MYI
2008-05-24 09:26 25,586,528 part_tab#P#p7.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p7.MYI
2008-05-24 09:26 25,586,752 part_tab#P#p8.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p8.MYI
2008-05-24 09:26 25,585,824 part_tab#P#p9.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p9.MYI
2008-05-24 09:25 8,608 part_tab.frm
2008-05-24 09:25 68 part_tab.par
* 再次測試SQL效能
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (2.42 sec) /* 為原來4.69 sec 的51%*/
重啟mysql ( net stop mysql, net start mysql)後，查詢時間降為0.89 sec,幾乎與分割槽表相同。
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.86 sec)
* 更進一步的試驗
** 增加日期範圍
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';
+----------+
| count(*) |
+----------+
| 2396524 |
+----------+
1 row in set (5.42 sec)
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';
+----------+
| count(*) |
+----------+
| 2396524 |
+----------+
1 row in set (2.63 sec)
** 增加未索引欄位查詢
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1996-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.75 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1996-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (11.52 sec)
= 初步結論 =
* 分割槽和未分割槽佔用檔案空間大致相同 （資料和索引檔案）
* 如果查詢語句中有未建立索引欄位，分割槽時間遠遠優於未分割槽時間
* 如果查詢語句中欄位建立了索引，分割槽和未分割槽的差別縮小，分割槽略優於未分割槽。
= 最終結論 =
* 對於大資料量，建議使用分割槽功能。
* 去除不必要的欄位
* 根據手冊， 增加myisam_max_sort_file_size 會增加分割槽效能
[分割槽命令詳解]
= 分割槽例子 =
* RANGE 型別
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES LESS THAN (9000000)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
在這裡，將使用者表分成4個分割槽，以每300萬條記錄為界限，每個分割槽都有自己獨立的資料、索引檔案的存放目錄，與此同時，這些目錄所在的物理磁碟分割槽可能也都是完全獨立的，可以提高磁碟IO吞吐量。
* LIST 型別
CREATE TABLE category (
cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY LIST (cid) (
PARTITION p0 VALUES IN (0,4,8,12)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES IN (1,5,9,13)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES IN (2,6,10,14)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES IN (3,7,11,15)
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
分成4個區，資料檔案和索引檔案單獨存放。
* HASH 型別
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY HASH (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
分成4個區，資料檔案和索引檔案單獨存放。
例子：
CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=myisam
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
CREATE PROCEDURE load_ti2()
begin
declare v int default 0;
while v < 80000
do
insert into ti2
values (v,'3.14',adddate('1995-01-01',(rand(v)*3652) mod 365));
set v = v + 1;
end while;
end
//
* KEY 型別
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY KEY (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
分成4個區，資料檔案和索引檔案單獨存放。
* 子分割槽
子分割槽是針對 RANGE/LIST 型別的分割槽表中每個分割槽的再次分割。再次分割可以是 HASH/KEY 等型別。例如：
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx'
);
對 RANGE 分割槽再次進行子分割槽劃分，子分割槽採用 HASH 型別。
或者
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx'
);
對 RANGE 分割槽再次進行子分割槽劃分，子分割槽採用 KEY 型別。
= 分割槽管理 =
* 刪除分割槽
ALERT TABLE users DROP PARTITION p0;
刪除分割槽 p0。
* 重建分割槽
o RANGE 分割槽重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
將原來的 p0,p1 分割槽合併起來，放到新的 p0 分割槽中。
o LIST 分割槽重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
將原來的 p0,p1 分割槽合併起來，放到新的 p0 分割槽中。
o HASH/KEY 分割槽重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
用 REORGANIZE 方式重建分割槽的數量變成2，在這裡數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。
* 新增分割槽
o 新增 RANGE 分割槽
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');
新增一個RANGE分割槽。
o 新增 HASH/KEY 分割槽
ALTER TABLE users ADD PARTITION PARTITIONS 8;
將分割槽總數擴充套件到8個。
[ 給已有的表加上分割槽 ]
alter table results partition by RANGE (month(ttime))
(PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13) );
預設分割槽限制分割槽欄位必須是主鍵（PRIMARY KEY)的一部分，為了去除此
限制：
[方法1] 使用ID
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
However, this statement using the id column for the partitioning column is valid, as shown here:
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
[方法2] 將原有PK去掉生成新PK
mysql> alter table results drop PRIMARY KEY;
Query OK, 5374850 rows affected (7 min 4.05 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
mysql> alter table results add PRIMARY KEY(id, ttime);
Query OK, 5374850 rows affected (6 min 14.86 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
= 水平分割槽（根據列屬性按行分）=
舉個簡單例子：一個包含十年發票記錄的表可以被分割槽為十個不同的分割槽，每個分割槽包含的是其中一年的記錄。
=== 水平分割槽的幾種模式：===
* Range（範圍） – 這種模式允許DBA將資料劃分不同範圍。例如DBA可以將一個表透過年份劃分成三個分割槽，80年代（1980's）的資料，90年代（1990's）的資料以及任何在2000年（包括2000年）後的資料。
* Hash（雜湊） – 這中模式允許DBA透過對錶的一個或多個列的Hash Key進行計算，最後透過這個Hash碼不同數值對應的資料區域進行分割槽，。例如DBA可以建立一個對錶主鍵進行分割槽的表。
* Key（鍵值） – 上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
* List（預定義列表） – 這種模式允許系統透過DBA定義的列表的值所對應的行資料進行分割。例如：DBA建立了一個橫跨三個分割槽的表，分別根據2004年2005年和2006年值所對應的資料。
* Composite（複合模式） - 很神秘吧，哈哈，其實是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經進行了Range範圍分割槽的表上，我們可以對其中一個分割槽再進行hash雜湊分割槽。
= 垂直分割槽（按列分）=
舉個簡單例子：一個包含了大text和BLOB列的表，這些text和BLOB列又不經常被訪問，這時候就要把這些不經常使用的text和BLOB了劃分到另一個分割槽，在保證它們資料相關性的同時還能提高訪問速度。
[分割槽表和未分割槽表試驗過程]
*建立分割槽表,按日期的年份拆分
mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,
PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,
PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,
PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,
PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),
PARTITION p11 VALUES LESS THAN MAXVALUE );
注意最後一行，考慮到可能的最大值
*建立未分割槽表
mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;
*透過儲存過程灌入800萬條測試資料
mysql> set sql_mode=''; /* 如果建立儲存過程失敗，則先需設定此變數, bug? */
mysql> delimiter // /* 設定語句終結符為 //，因儲存過程語句用;結束 */
mysql> CREATE PROCEDURE load_part_tab()
begin
declare v int default 0;
while v < 8000000
do
insert into part_tab
values (v,'testing partitions',adddate('1995-01-01',(rand(v)*36520) mod 3652));
set v = v + 1;
end while;
end
//
mysql> delimiter ;
mysql> call load_part_tab();
Query OK, 1 row affected (8 min 17.75 sec)
mysql> insert into no_part_tab select * from part_tab;
Query OK, 8000000 rows affected (51.59 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
* 測試SQL效能
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.55 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (4.69 sec)
結果表明分割槽表比未分割槽表的執行時間少90%。
* 透過explain語句來分析執行情況
mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
/* 結尾的\G使得mysql的輸出改為列模式 */
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: no_part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8000000
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: part_tab
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 798458
Extra: Using where
1 row in set (0.00 sec)
explain語句顯示了SQL查詢要處理的記錄數目
* 試驗建立索引後情況
mysql> create index idx_of_c3 on no_part_tab (c3);
Query OK, 8000000 rows affected (1 min 18.08 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
mysql> create index idx_of_c3 on part_tab (c3);
Query OK, 8000000 rows affected (1 min 19.19 sec)
Records: 8000000 Duplicates: 0 Warnings: 0
建立索引後的資料庫檔案大小列表：
2008-05-24 09:23 8,608 no_part_tab.frm
2008-05-24 09:24 255,999,996 no_part_tab.MYD
2008-05-24 09:24 81,611,776 no_part_tab.MYI
2008-05-24 09:25 0 part_tab#P#p0.MYD
2008-05-24 09:26 1,024 part_tab#P#p0.MYI
2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD
2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI
2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD
2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI
2008-05-24 09:25 0 part_tab#P#p11.MYD
2008-05-24 09:26 1,024 part_tab#P#p11.MYI
2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI
2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI
2008-05-24 09:26 25,585,696 part_tab#P#p4.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p4.MYI
2008-05-24 09:26 25,585,216 part_tab#P#p5.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p5.MYI
2008-05-24 09:26 25,655,740 part_tab#P#p6.MYD
2008-05-24 09:26 8,181,760 part_tab#P#p6.MYI
2008-05-24 09:26 25,586,528 part_tab#P#p7.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p7.MYI
2008-05-24 09:26 25,586,752 part_tab#P#p8.MYD
2008-05-24 09:26 8,160,256 part_tab#P#p8.MYI
2008-05-24 09:26 25,585,824 part_tab#P#p9.MYD
2008-05-24 09:26 8,159,232 part_tab#P#p9.MYI
2008-05-24 09:25 8,608 part_tab.frm
2008-05-24 09:25 68 part_tab.par
* 再次測試SQL效能
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'; +----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (2.42 sec) /* 為原來4.69 sec 的51%*/
重啟mysql ( net stop mysql, net start mysql)後，查詢時間降為0.89 sec,幾乎與分割槽表相同。
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';
+----------+
| count(*) |
+----------+
| 795181 |
+----------+
1 row in set (0.86 sec)
* 更進一步的試驗
** 增加日期範圍
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';
+----------+
| count(*) |
+----------+
| 2396524 |
+----------+
1 row in set (5.42 sec)
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1997-12-31';
+----------+
| count(*) |
+----------+
| 2396524 |
+----------+
1 row in set (2.63 sec)
** 增加未索引欄位查詢
mysql> select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date
'1996-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.75 sec)
mysql> select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < da
te '1996-12-31' and c2='hello';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (11.52 sec)
= 初步結論 =
* 分割槽和未分割槽佔用檔案空間大致相同 （資料和索引檔案）
* 如果查詢語句中有未建立索引欄位，分割槽時間遠遠優於未分割槽時間
* 如果查詢語句中欄位建立了索引，分割槽和未分割槽的差別縮小，分割槽略優於未分割槽。
= 最終結論 =
* 對於大資料量，建議使用分割槽功能。
* 去除不必要的欄位
* 根據手冊， 增加myisam_max_sort_file_size 會增加分割槽效能
[分割槽命令詳解]
= 分割槽例子 =
* RANGE 型別
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES LESS THAN (9000000)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
在這裡，將使用者表分成4個分割槽，以每300萬條記錄為界限，每個分割槽都有自己獨立的資料、索引檔案的存放目錄，與此同時，這些目錄所在的物理磁碟分割槽可能也都是完全獨立的，可以提高磁碟IO吞吐量。
* LIST 型別
CREATE TABLE category (
cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY LIST (cid) (
PARTITION p0 VALUES IN (0,4,8,12)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES IN (1,5,9,13)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2 VALUES IN (2,6,10,14)
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3 VALUES IN (3,7,11,15)
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
分成4個區，資料檔案和索引檔案單獨存放。
* HASH 型別
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY HASH (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
分成4個區，資料檔案和索引檔案單獨存放。
例子：
CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE)
ENGINE=myisam
PARTITION BY HASH( MONTH(tr_date) )
PARTITIONS 6;
CREATE PROCEDURE load_ti2()
begin
declare v int default 0;
while v < 80000
do
insert into ti2
values (v,'3.14',adddate('1995-01-01',(rand(v)*3652) mod 365));
set v = v + 1;
end while;
end
//
* KEY 型別
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY KEY (uid) PARTITIONS 4 (
PARTITION p0
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx',
PARTITION p2
DATA DIRECTORY = '/data4/data'
INDEX DIRECTORY = '/data5/idx',
PARTITION p3
DATA DIRECTORY = '/data6/data'
INDEX DIRECTORY = '/data7/idx'
);
分成4個區，資料檔案和索引檔案單獨存放。
* 子分割槽
子分割槽是針對 RANGE/LIST 型別的分割槽表中每個分割槽的再次分割。再次分割可以是 HASH/KEY 等型別。例如：
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx'
);
對 RANGE 分割槽再次進行子分割槽劃分，子分割槽採用 HASH 型別。
或者
CREATE TABLE users (
uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(30) NOT NULL DEFAULT '',
email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(
PARTITION p0 VALUES LESS THAN (3000000)
DATA DIRECTORY = '/data0/data'
INDEX DIRECTORY = '/data1/idx',
PARTITION p1 VALUES LESS THAN (6000000)
DATA DIRECTORY = '/data2/data'
INDEX DIRECTORY = '/data3/idx'
);
對 RANGE 分割槽再次進行子分割槽劃分，子分割槽採用 KEY 型別。
= 分割槽管理 =
* 刪除分割槽
ALERT TABLE users DROP PARTITION p0;
刪除分割槽 p0。
* 重建分割槽
o RANGE 分割槽重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
將原來的 p0,p1 分割槽合併起來，放到新的 p0 分割槽中。
o LIST 分割槽重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
將原來的 p0,p1 分割槽合併起來，放到新的 p0 分割槽中。
o HASH/KEY 分割槽重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
用 REORGANIZE 方式重建分割槽的數量變成2，在這裡數量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。
* 新增分割槽
o 新增 RANGE 分割槽
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');
新增一個RANGE分割槽。
o 新增 HASH/KEY 分割槽
ALTER TABLE users ADD PARTITION PARTITIONS 8;
將分割槽總數擴充套件到8個。
[ 給已有的表加上分割槽 ]
alter table results partition by RANGE (month(ttime))
(PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,
PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,
PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,
PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,
PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),
PARTITION p11 VALUES LESS THAN (12),
PARTITION P12 VALUES LESS THAN (13) );
預設分割槽限制分割槽欄位必須是主鍵（PRIMARY KEY)的一部分，為了去除此
限制：
[方法1] 使用ID
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH( TO_DAYS(added) )
-> PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
However, this statement using the id column for the partitioning column is valid, as shown here:
mysql> ALTER TABLE np_pk
-> PARTITION BY HASH(id)
-> PARTITIONS 4;
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
[方法2] 將原有PK去掉生成新PK
mysql> alter table results drop PRIMARY KEY;
Query OK, 5374850 rows affected (7 min 4.05 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
mysql> alter table results add PRIMARY KEY(id, ttime);
Query OK, 5374850 rows affected (6 min 14.86 sec)
Records: 5374850 Duplicates: 0 Warnings: 0
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1063353/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 分割槽表 partition線上修改分割槽欄位MySql
- 分割槽Partition
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- Oracle Partition 分割槽詳細總結Oracle
- 轉：深入解析MySQL分割槽（Partition）功能MySql
- 【實驗】【PARTITION】RANGE分割槽表截斷表分割槽(Truncate Partition)
- 【實驗】【PARTITION】RANGE分割槽表移動表分割槽(Move Partition)
- 分割槽表PARTITION table
- ORACLE 範圍分割槽 partition-range分割槽Oracle
- oracle分割槽及分割槽索引partition_partition index_維護(一)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(二)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(三)Oracle索引Index
- oracle分割槽及分割槽索引partition_partition index_維護(四)Oracle索引Index
- 【實驗】【PARTITION】RANGE分割槽表重新命名錶分割槽(Rename Partition)
- MySQL 分割槽表原理及使用詳解MySql
- 【實驗】【PARTITION】RANGE分割槽表合併分割槽
- 【實驗】【PARTITION】RANGE分割槽表增加分割槽
- 【實驗】【PARTITION】RANGE分割槽表刪除分割槽
- 分割槽partition知識點
- Oracle 分割槽(partition)技術Oracle
- Oracle分割槽表(Partition Table)Oracle
- 分割槽剪除 (partition pruning)
- 分割槽表PARTITION table(轉)
- 融合（merge partition）分割槽
- 合併分割槽（coalesce partition)
- oracle分割槽及分割槽索引partition_partition index_維護(五)_快捷方法Oracle索引Index
- oracle partition分割槽_分割槽列為空測試(一)Oracle
- 理解 MySQL（4）：並行資料庫與分割槽(Partition)MySql並行資料庫
- Spark學習——分割槽Partition數Spark
- oracle reference partition引用分割槽(一)Oracle
- 深入解析partition-range分割槽
- 深入解析partition-hash分割槽
- 深入解析partition-list 分割槽
- oracle list partition列表分割槽(一)Oracle
- 【實驗】【PARTITION】RANGE分割槽建立
- partition 分割槽表重新命名
- 非分割槽錶轉換為分割槽表和partition indexIndex
- 聊聊分割槽Partition——我們為什麼要分割槽（下）