日韩久久久精品,亚洲精品久久久久久久久久久,亚洲欧美一区二区三区国产精品 ,一区二区福利

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

系統 2412 0

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

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

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

);

在這里，將用戶表分成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

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


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧，站長會非常 感謝您的哦！！！

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板： 塘沽区| 庆安县| 乌兰县| 和平县| 阿坝县| 镇雄县| 调兵山市| 天峨县| 安新县| 句容市| 黄大仙区| 独山县| 澄迈县| 双峰县| 呼和浩特市| 贵州省| 辉县市| 大同市| 武冈市| 东至县| 卓尼县| 浦江县| 台山市| 堆龙德庆县| 临夏县| 乌兰察布市| 中卫市| 襄垣县| 扎赉特旗| 西盟| 二连浩特市| 东丰县| 嵊州市| 太仓市| 周口市| 荆州市| 孝义市| 安国市| 皮山县| 桃源县| 清水河县|