深入解析MySQL分區（Partition）功能

jopen 11年前發布 | 16K 次閱讀 MySQL 數據庫服務器

自5.1開始對分區(Partition)有支持

= 水平分區（根據列屬性按行分）=</span>
舉個簡單例子：一個包含十年發票記錄的表可以被分區為十個不同的分區，每個分區包含的是其中一年的記錄。

=== 水平分區的幾種模式：===
* 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哈希分區。

= 垂直分區（按列分）=</span>
舉個簡單例子：一個包含了大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 < date '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'

);</pre>在這里，將用戶表分成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'

);   </pre>分成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'

);</pre>分成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
    //</pre> 
 

 KEY 類型</span>

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'

);   </pre>分成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'

);</pre>對 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'

);</pre>對 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

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1378776437850.html

MySQL 數據庫服務器

深入解析MySQL分區（Partition）功能

相關經驗

相關資訊

相關文檔

目錄