一，      分區(qū)概念 

分區(qū)允許根據(jù)指定的規(guī)則，跨文件系統(tǒng)分配單個(gè)表的多個(gè)部分。表的不同部分在不同的位置被存儲(chǔ)為單獨(dú)的表。MySQL從5.1.3開始支持Partition。

分區(qū)和手動(dòng)分表對(duì)比

手動(dòng)分表	分區(qū)
多張數(shù)據(jù)表	一張數(shù)據(jù)表
重復(fù)數(shù)據(jù)的風(fēng)險(xiǎn)	沒有數(shù)據(jù)重復(fù)的風(fēng)險(xiǎn)
寫入多張表	寫入一張表
沒有統(tǒng)一的約束限制	強(qiáng)制的約束限制

 

MySQL支持RANGE，LIST，HASH，KEY分區(qū)類型，其中以RANGE最為常用：

• Range（范圍）–這種模式允許將數(shù)據(jù)劃分不同范圍。例如可以將一個(gè)表通過年份劃分成若干個(gè)分區(qū)。
• Hash（哈希）–這中模式允許通過對(duì)表的一個(gè)或多個(gè)列的Hash Key進(jìn)行計(jì)算，最后通過這個(gè)Hash碼不同數(shù)值對(duì)應(yīng)的數(shù)據(jù)區(qū)域進(jìn)行分區(qū)。例如可以建立一個(gè)對(duì)表主鍵進(jìn)行分區(qū)的表。
• Key（鍵值）-上面Hash模式的一種延伸，這里的Hash Key是MySQL系統(tǒng)產(chǎn)生的。
• List（預(yù)定義列表）–這種模式允許系統(tǒng)通過預(yù)定義的列表的值來對(duì)數(shù)據(jù)進(jìn)行分割。
• Composite（復(fù)合模式） –以上模式的組合使用　

 

二，分區(qū)能做什么

• 邏輯數(shù)據(jù)分割
• 提高單一的寫和讀應(yīng)用速度
• 提高分區(qū)范圍讀查詢的速度
• 分割數(shù)據(jù)能夠有多個(gè)不同的物理文件路徑
• 高效的保存歷史數(shù)據(jù)
• 一個(gè)表上的約束檢查
• 不同的主從服務(wù)器分區(qū)策略，例如master按Hash分區(qū)，slave按range分區(qū)

 

三，分區(qū)的限制(截止5.1.44版)

•   只能對(duì)數(shù)據(jù)表的整型列進(jìn)行分區(qū)，或者數(shù)據(jù)列可以通過分區(qū)函數(shù)轉(zhuǎn)化成整型列

•   最大分區(qū)數(shù)目不能超過1024

•   如果含有唯一索引或者主鍵，則分區(qū)列必須包含在所有的唯一索引或者主鍵在內(nèi)

•   不支持外鍵

•   不支持全文索引（fulltext）

• 按日期進(jìn)行分區(qū)很非常適合，因?yàn)楹芏嗳掌诤瘮?shù)可以用。但是對(duì)于字符串來說合適的分區(qū)函數(shù)不太多

四，什么時(shí)候使用分區(qū)

•   海量數(shù)據(jù)表

•   歷史表快速的查詢，可以采用ARCHIVE+PARTITION的方式。

•   數(shù)據(jù)表索引大于服務(wù)器有效內(nèi)存

•   對(duì)于大表，特別是索引遠(yuǎn)遠(yuǎn)大于服務(wù)器有效內(nèi)存時(shí)，可以不用索引，此時(shí)分區(qū)效率會(huì)更有效。

五，分區(qū)實(shí)驗(yàn)

實(shí)驗(yàn)一：

使用 US Bureau of Transportation Statistics發(fā)布的數(shù)據(jù)(CSV格式).目前, 包括 1.13 億條記錄，7.5 GB數(shù)據(jù)5.2 GB索引。時(shí)間從1987到2007。

服務(wù)器使用4GB內(nèi)存，這樣數(shù)據(jù)和索引的大小都超過了內(nèi)存大小。設(shè)置為4GB原因是數(shù)據(jù)倉庫大小遠(yuǎn)遠(yuǎn)超過可能內(nèi)存的大小，可能達(dá)幾TB。對(duì)普通OLTP數(shù)據(jù)庫來說，索引緩存在內(nèi)存中，可以快速檢索。如果數(shù)據(jù)超出內(nèi)存大小，需要使用不同的方式。

創(chuàng)建有主鍵的表，因?yàn)橥ǔ１矶紩?huì)有主鍵。表的主鍵太大導(dǎo)致索引無法讀入內(nèi)存，這樣一般來說不是高效的，意味著要經(jīng)常訪問磁盤，訪問速度完全取決于你的磁盤和處理器。目前在設(shè)計(jì)很大的數(shù)據(jù)倉庫里，有一種普遍的做法是不使用索引。所以也會(huì)比較有和沒有主鍵的性能。

測(cè)試方法：

使用三種數(shù)據(jù)引擘MyISAM, InnoDB, Archive.
對(duì)于每一種引擘, 創(chuàng)建一個(gè)帶主鍵的未分區(qū)表 (除了archive) 和兩個(gè)分區(qū)表，一個(gè)按月一個(gè)按年。分區(qū)表分區(qū)方式如下：

CREATE TABLE by_year (

   d DATE

)

PARTITION BY RANGE (YEAR(d))

(

PARTITION P1 VALUES LESS THAN (2001),

PARTITION P2 VALUES LESS THAN (2002),

PARTITION P3 VALUES LESS THAN (2003),

PARTITION P4 VALUES LESS THAN (MAXVALUE)

)

CREATE TABLE by_month (

   d DATE

)

PARTITION BY RANGE (TO_DAYS(d))

(

PARTITION P1 VALUES LESS THAN (to_days(‘2001-02-01′)), — January

PARTITION P2 VALUES LESS THAN (to_days(‘2001-03-01′)), — February

PARTITION P3 VALUES LESS THAN (to_days(‘2001-04-01′)), — March

PARTITION P4 VALUES LESS THAN (MAXVALUE)

)

每一個(gè)都在 mysql服務(wù)器上的單獨(dú)的實(shí)例上測(cè)試, 每實(shí)例只有一個(gè)庫一個(gè)表。每種引擘, 都會(huì)啟動(dòng)服務(wù), 運(yùn)行查詢并記錄結(jié)果, 然后關(guān)閉服務(wù)。服務(wù)實(shí)例通過MySQL Sandbox創(chuàng)建。

加載數(shù)據(jù)的情況如下：

ID	引擘	是否分區(qū)	數(shù)據(jù)	大小	備注	加載時(shí)間 (*)
1	MyISAM	none	1.13億	13 GB	with PK	37 min
2	MyISAM	by month	1.13億	8 GB	without PK	19 min
3	MyISAM	by year	1.13億	8 GB	without PK	18 min
4	InnoDB	none	1.13億	16 GB	with PK	63 min
5	InnoDB	by month	1.13億	10 GB	without PK	59 min
6	InnoDB	by year	1.13億	10 GB	without PK	57 min
7	Archive	none	1.13億	1.8 GB	no keys	20 min
8	Archive	by month	1.13億	1.8 GB	no keys	21 min
9	Archive	by year	1.13億	1.8 GB	no keys	20 min

*在dual-Xeon服務(wù)器上

為了對(duì)比分區(qū)在大的和小的數(shù)據(jù)集上的效果，創(chuàng)建了另外9個(gè)實(shí)例，每一個(gè)包含略小于2GB的數(shù)據(jù)。

查詢語句有兩種

• 聚集查詢

SELECT COUNT(*)

FROM table_name

WHERE date_column BETWEEN start_date and end_date

• 指定記錄查詢

 SELECT column_list

FROM table_name

WHERE column1 = x and  column2 = y and column3 = z

對(duì)于第一種查詢，創(chuàng)建不同的日期范圍的語句。對(duì)于每一個(gè)范圍，創(chuàng)建一組額外的相同范圍日期的查詢。每個(gè)日期范圍的第一個(gè)查詢是冷查詢，意味著是第一次命中，隨后的在同樣范圍內(nèi)的查詢是暖查詢，意味著至少部分被緩存。查詢語句在the Forge上。

結(jié)果：

1帶主鍵的分區(qū)表

第一個(gè)測(cè)試使用復(fù)合主鍵，就像原始數(shù)據(jù)表使用的一樣。主鍵索引文件達(dá)到5.5 GB. 可以看出，分區(qū)不僅沒有提高性能，主鍵還減緩了操作。因?yàn)槿绻褂弥麈I索引查詢，而索引又不能讀入內(nèi)存，則表現(xiàn)很差。提示我們分區(qū)很有用，但是必須使用得當(dāng)。

+——–+—————–+—————–+—————–+

| 狀態(tài)   | myisam 不分區(qū)   |   myisam 月分區(qū) |  myisam 年分區(qū)  |

+——–+—————–+—————–+—————–+

| cold   | 2.6574570285714 |       2.9169642 | 3.0373419714286 |

| warm   | 2.5720722571429 | 3.1249698285714 | 3.1294000571429 |

+——–+—————–+—————–+—————–+

ARCHIVE引擘

+——–+—————-+—————–+—————–+

|  狀態(tài)  | archive不分區(qū)  |   archive月分區(qū)|   archive年分區(qū) |

+——–+—————-+—————–+—————–+

| cold   |     249.849563 | 1.2436211111111 | 12.632532527778 |

| warm   |     235.814442 | 1.0889786388889 | 12.600520777778 |

+——–+—————-+—————–+—————–+

注意ARCHIVE引擘月分區(qū)的響應(yīng)時(shí)間比使用MyISAM好。

2不帶主鍵的分區(qū)表

因?yàn)槿绻麈I的大小超出了可用的key buffer，甚至全部?jī)?nèi)存，所有使用主鍵的查詢都會(huì)使用磁盤。新的方式只使用分區(qū)，不要主鍵。性能有顯著的提高。

按月分區(qū)表得到了70%-90%的性能提高。

+——–+——————+——————+——————+

| 狀態(tài)   | myisam 不分區(qū)    |   myisam 月分區(qū)  |  myisam 年分區(qū)   |

+——–+——————+——————+——————+

| cold   |  2.6864490285714 | 0.64206445714286 |  2.6343286285714 |

| warm   |  2.8157905714286 | 0.18774977142857 |  2.2084743714286 |

+——–+——————+——————+——————+

為了使區(qū)別更明顯, 我使用了兩個(gè)大規(guī)模查詢，可以利用分區(qū)的分區(qū)消除功能。

# query 1 – 按年統(tǒng)計(jì)

SELECT year(FlightDate) as y, count(*)

FROM flightstats

WHERE FlightDate BETWEEN  “2001-01-01″ and “2003-12-31″

GROUP BY y

# query 2 – 按月統(tǒng)計(jì)

SELECT date_format(FlightDate,”%Y-%m”) as m, count(*)

FROM flightstats 

WHERE FlightDate BETWEEN “2001-01-01″ and “2003-12-31″

GROUP BY m

結(jié)果顯示按月分區(qū)表有30%-60%，按年分區(qū)表有15%-30%性能提升。

+———-+———–+———–+———–+

| query_id | 不分       | 月分     |   年分    |

+———-+———–+———–+———–+

|        1 | 97.779958 | 36.296519 | 82.327554 |

|        2 |  69.61055 | 47.644986 |  47.60223 |

+———-+———–+———–+———–+

處理器因素

當(dāng)以上測(cè)試在家用機(jī)(Intel Dual Core 2.3 MHz CPU)上測(cè)試的時(shí)候。對(duì)于原來的對(duì)于dual Xeon 2.66 MHz來說，發(fā)現(xiàn)新服務(wù)器更快！。

重復(fù)上面的測(cè)試，令人吃驚:

+——–+——————-+————-+—————–+

|狀態(tài)    | myisam 不分區(qū)     |myisam 月分區(qū)|  myisam 年分區(qū)  |

+——–+——————-+————-+—————–+

| cold   | 0.051063428571429 |   0.6577062 | 1.6663527428571 |

| warm   | 0.063645485714286 |   0.1093724 | 1.2369152285714 |

+——–+——————-+————-+—————–+

myisam 不分區(qū)帶主鍵的表比分區(qū)表更快. 分區(qū)表的表現(xiàn)和原來一樣，但未分區(qū)表性能提高了，使得分區(qū)顯得不必要。既然這臺(tái)服務(wù)器似乎充分利用了索引的好處，我在分區(qū)表的分區(qū)列上加入了索引。

# 原始表

create table flightstats (

AirlineID int not null,

UniqueCarrier char(3) not null,

Carrier char(3) not null,

FlightDate date not null,

FlightNum char(5) not null,

TailNum char(8) not null,

ArrDelay double not null,

ArrTime datetime not null,

DepDelay double not null,

DepTime datetime not null,

Origin char(3) not null,

Dest char(3) not null,

Distance int not null,

Cancelled char(1) default ‘n',

primary key (FlightDate, AirlineID, Carrier, UniqueCarrier, FlightNum, Origin, DepTime, Dest)

)

# 分區(qū)表

create table flightstats (

AirlineID int not null,

UniqueCarrier char(3) not null,

Carrier char(3) not null,

FlightDate date not null,

FlightNum char(5) not null,

TailNum char(8) not null,

ArrDelay double not null,

ArrTime datetime not null,

DepDelay double not null,

DepTime datetime not null,

Origin char(3) not null,

Dest char(3) not null,

Distance int not null,

Cancelled char(1) default ‘n',

KEY (FlightDate)

)

PARTITION BY RANGE …

結(jié)果是讓人滿意的,得到35% 性能提高。

+——–+——————-+——————-+——————-+

|狀態(tài)    | myisam 不分區(qū)     |myisam 月分區(qū)      |  myisam 年分區(qū)   |

+——–+——————-+——————-+——————-+

| cold   | 0.075289714285714 | 0.025491685714286 | 0.072398542857143 |

| warm   | 0.064401257142857 | 0.031563085714286 | 0.056638085714286 |

+——–+——————-+——————-+——————-+

結(jié)論：

1.  使用表分區(qū)并不是性能提高的保證。它依賴于以下因素：

• 分區(qū)使用的列the column used for partitioning;
• 分區(qū)函數(shù)，如果原始字段不是int型;
• 服務(wù)器速度;
• 內(nèi)存數(shù)量.

2.  在應(yīng)用到生產(chǎn)系統(tǒng)前運(yùn)行基準(zhǔn)測(cè)試和性能測(cè)試

依賴于你的數(shù)據(jù)庫的用途，你可能得到巨大的性能提高也可能一無所獲。如果不小心，甚至有可能會(huì)降低性能。

比如：一個(gè)使用月分區(qū)的表，在總是進(jìn)行日期范圍查詢時(shí)可以得到極優(yōu)的速度。但如果沒有日期查詢，那么會(huì)進(jìn)行全表掃描。 

分區(qū)對(duì)于海量數(shù)據(jù)性能提高是一個(gè)關(guān)鍵的工具。什么才是海量的數(shù)據(jù)取決于部署的硬件。盲目使用分區(qū)不能保證提高性能，但是在前期基準(zhǔn)測(cè)試和性能測(cè)試的幫助下，可以成為完美的解決方案。

3.  Archive 表可以成為一個(gè)很好的折衷方案

Archive 表分區(qū)后可以得到巨大的性能提高。當(dāng)然也依賴于你的用途，沒有分區(qū)時(shí)任何查詢都是全表掃描。如果你有不需要變更的歷史數(shù)據(jù)，還要進(jìn)行按時(shí)間的分析統(tǒng)計(jì)，使用Archive引擘是極佳的選擇。它會(huì)使用10-20％的原空間，對(duì)于聚集查詢有比MyISAM /InnoDB表更好的性能。

雖然一個(gè)很好的優(yōu)化的分區(qū)MyISAM 表性能可能好于對(duì)應(yīng)的Archive表, 但是需要10倍的空間。

 

實(shí)驗(yàn)二：

1.建兩個(gè)表，一個(gè)按時(shí)間字段分區(qū)，一個(gè)不分區(qū)。

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

create table no_part_tab

(c1 int(11) default NULL,

c2 varchar(30) default NULL,

c3 date default NULL) engine=myisam;

2.建一個(gè)存儲(chǔ)過程, 利用該過程向兩個(gè)表插入各8百萬條不同數(shù)據(jù)。

delimiter //

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

//

然后執(zhí)行

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

3.開始對(duì)這兩表中的數(shù)據(jù)進(jìn)行簡(jiǎn)單的范圍查詢吧。并顯示執(zhí)行過程解析：

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 (38.30 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 (3.88 sec)

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

*************************** 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 partitions 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

   partitions: p1

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 798458

        Extra: Using where

1 row in set (0.00 sec)

從上面結(jié)果可以看出，使用表分區(qū)比非分區(qū)的減少90％的響應(yīng)時(shí)間。命令解析Explain程序可以看出在對(duì)已分區(qū)的表的查詢過程中僅對(duì)第一個(gè)分區(qū)進(jìn)行了掃描，其余跳過。進(jìn)一步測(cè)試：

– 增加日期范圍

mysql> select count(*) from no_part_tab where c3 > date ‘-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 ‘-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 ‘-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 ‘-01-01′and c3 < da

te ‘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ū)。
• 對(duì)于大數(shù)據(jù)量，建議使用分區(qū)功能。
• 去除不必要的字段
• 根據(jù)手冊(cè)，增加myisam_max_sort_file_size 會(huì)增加分區(qū)性能

最新評(píng)論