欧美三区_成人在线免费观看视频_欧美极品少妇xxxxⅹ免费视频_a级毛片免费播放_鲁一鲁中文字幕久久_亚洲一级特黄

深入解析MySQL分區(qū)（Partition）功能

系統(tǒng) 2336 0

自5.1開始對(duì)分區(qū)(Partition)有支持

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

=== 水平分區(qū)的幾種模式：===
* Range（范圍） – 這種模式允許DBA將數(shù)據(jù)劃分不同范圍。例如DBA可以將一個(gè)表通過年份劃分成三個(gè)分區(qū)，80年代（1980's）的數(shù)據(jù)，90年代（1990's）的數(shù)據(jù)以及任何在2000年（包括2000年）后的數(shù)據(jù)。?

* Hash（哈希） – 這中模式允許DBA通過對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)，。例如DBA可以建立一個(gè)對(duì)表主鍵進(jìn)行分區(qū)的表。?

* Key（鍵值） – 上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。?

* List（預(yù)定義列表） – 這種模式允許系統(tǒng)通過DBA定義的列表的值所對(duì)應(yīng)的行數(shù)據(jù)進(jìn)行分割。例如：DBA建立了一個(gè)橫跨三個(gè)分區(qū)的表，分別根據(jù)2004年2005年和2006年值所對(duì)應(yīng)的數(shù)據(jù)。?

* Composite（復(fù)合模式） - 很神秘吧，哈哈，其實(shí)是以上模式的組合使用而已，就不解釋了。舉例：在初始化已經(jīng)進(jìn)行了Range范圍分區(qū)的表上，我們可以對(duì)其中一個(gè)分區(qū)再進(jìn)行hash哈希分區(qū)。?

= 垂直分區(qū)（按列分）=
舉個(gè)簡單例子：一個(gè)包含了大text和BLOB列的表，這些text和BLOB列又不經(jīng)常被訪問，這時(shí)候就要把這些不經(jīng)常使用的text和BLOB了劃分到另一個(gè)分區(qū)，在保證它們數(shù)據(jù)相關(guān)性的同時(shí)還能提高訪問速度。


[分區(qū)表和未分區(qū)表試驗(yàn)過程]

*創(chuàng)建分區(qū)表,按日期的年份拆分 

    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 );

注意最后一行，考慮到可能的最大值

*創(chuàng)建未分區(qū)表 

    mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;


*通過存儲(chǔ)過程灌入800萬條測試數(shù)據(jù)

mysql> set sql_mode=''; /* 如果創(chuàng)建存儲(chǔ)過程失敗，則先需設(shè)置此變量, bug? */

mysql> delimiter //?? /* 設(shè)定語句終結(jié)符為 //，因存儲(chǔ)過程語句用;結(jié)束 */ 


    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)
結(jié)果表明分區(qū)表比未分區(qū)表的執(zhí)行時(shí)間少90%。

* 通過explain語句來分析執(zhí)行情況 

    mysql > explain select count(*) from no_part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31'\G

/* 結(jié)尾的\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查詢要處理的記錄數(shù)目

* 試驗(yàn)創(chuàng)建索引后情況 

    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
創(chuàng)建索引后的數(shù)據(jù)庫文件大小列表：
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)后，查詢時(shí)間降為0.89 sec,幾乎與分區(qū)表相同。 

    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)

* 更進(jìn)一步的試驗(yàn)
** 增加日期范圍 

    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)


= 初步結(jié)論 =
* 分區(qū)和未分區(qū)占用文件空間大致相同 （數(shù)據(jù)和索引文件）
* 如果查詢語句中有未建立索引字段，分區(qū)時(shí)間遠(yuǎn)遠(yuǎn)優(yōu)于未分區(qū)時(shí)間
* 如果查詢語句中字段建立了索引，分區(qū)和未分區(qū)的差別縮小，分區(qū)略優(yōu)于未分區(qū)。


= 最終結(jié)論 =
* 對(duì)于大數(shù)據(jù)量，建議使用分區(qū)功能。
* 去除不必要的字段
* 根據(jù)手冊， 增加myisam_max_sort_file_size 會(huì)增加分區(qū)性能

[分區(qū)命令詳解]

= 分區(qū)例子 =?
* 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個(gè)分區(qū)，以每300萬條記錄為界限，每個(gè)分區(qū)都有自己獨(dú)立的數(shù)據(jù)、索引文件的存放目錄，與此同時(shí)，這些目錄所在的物理磁盤分區(qū)可能也都是完全獨(dú)立的，可以提高磁盤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個(gè)區(qū)，數(shù)據(jù)文件和索引文件單獨(dú)存放。

* 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個(gè)區(qū)，數(shù)據(jù)文件和索引文件單獨(dú)存放。

例子： 

    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個(gè)區(qū)，數(shù)據(jù)文件和索引文件單獨(dú)存放。

* 子分區(qū)
子分區(qū)是針對(duì) RANGE/LIST 類型的分區(qū)表中每個(gè)分區(qū)的再次分割。再次分割可以是 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'

);

對(duì) RANGE 分區(qū)再次進(jìn)行子分區(qū)劃分，子分區(qū)采用 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'

);

對(duì) RANGE 分區(qū)再次進(jìn)行子分區(qū)劃分，子分區(qū)采用 KEY 類型。

= 分區(qū)管理 =

??? * 刪除分區(qū) ?? 

    ALERT TABLE users DROP PARTITION p0;

????? 刪除分區(qū) p0。


??? * 重建分區(qū)
????????? o RANGE 分區(qū)重建 

    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

??????????? 將原來的 p0,p1 分區(qū)合并起來，放到新的 p0 分區(qū)中。
????????? o LIST 分區(qū)重建 

    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

??????????? 將原來的 p0,p1 分區(qū)合并起來，放到新的 p0 分區(qū)中。
????????? o HASH/KEY 分區(qū)重建 

     ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

??????????? 用 REORGANIZE 方式重建分區(qū)的數(shù)量變成2，在這里數(shù)量只能減少不能增加。想要增加可以用 ADD PARTITION 方法。
??? * 新增分區(qū)
????????? o 新增 RANGE 分區(qū) ? ? 

     ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)

            DATA DIRECTORY = '/data8/data'

            INDEX DIRECTORY = '/data9/idx');

??????????? 新增一個(gè)RANGE分區(qū)。
????????? o 新增 HASH/KEY 分區(qū) 

      ALTER TABLE users ADD PARTITION PARTITIONS 8;

??????????? 將分區(qū)總數(shù)擴(kuò)展到8個(gè)。

[ 給已有的表加上分區(qū) ] 

    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) );



默認(rèn)分區(qū)限制分區(qū)字段必須是主鍵（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分區(qū)（Partition）功能


更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號(hào)聯(lián)系： 360901061

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

【本文對(duì)您有幫助就好】元

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

發(fā)表我的評(píng)論
最新評(píng)論 總共0條評(píng)論
主站蜘蛛池模板： 深夜爽爽爽gif福利免费 | 99国产欧美久久精品 | 中文字幕av一区二区 | 天天干天天操天天射 | 久久久久无码国产精品一区 | 最新日韩精品在线观看 | 91九色精品国产 | 亚洲欧美日韩激情在线观看 | 日韩欧美精品综合一区二区三区 | 欧美成人久久一级c片免费 91在线免费视频 | 欧美国产日韩在线观看 | 国产成年人在线观看 | 一区二区三区网站在线免费线观看 | 欧美成人激情 | 亚洲欧美在线观看 | 国产色片在线观看 | 天天干干干 | 片一级片在线观看 | 国产精品国产三级国产aⅴ原创 | 日韩av免费看 | 久久机热| 伊人222综合 | 99久久亚洲精品日本无码 | 成人蜜桃av| 日本一区午夜爱爱 | 欧美双插 | 日本色婷婷 | 成人国产精品免费视频不卡 | 久久成人免费视频 | 日韩欧美在线观看一区 | 亚洲欧美视频一区 | 久久精品人人做人人看最新章 | 999精品久久久 | 国产网站免费视频 | 超91视频| 亚洲精品国产精品国自产在线 | 亚洲高清国产品国语在线观看 | 一区二区三区毛片 | 中文字幕av在线 | 国产精品永久免费视频 | 国产精品视频网站 |