基於MySQL分區效能的詳細介紹_php執行個體

一，      分區概念 

分區允許根據指定的規則，跨檔案系統分配單個表的多個部分。表的不同部分在不同的位置被儲存為單獨的表。MySQL從5.1.3開始支援Partition。

分區和手動分表對比

手動分表	分區
多張資料表	一張資料表
重複資料的風險	沒有資料重複的風險
寫入多張表	寫入一張表
沒有統一的約束限制	強制的約束限制

 

MySQL支援RANGE，LIST，HASH，KEY分區類型，其中以RANGE最為常用：

• Range（範圍）–這種模式允許將資料劃分不同範圍。例如可以將一個表通過年份劃分成若干個分區。
• Hash（雜湊）–這中模式允許通過對錶的一個或多個列的Hash Key進行計算，最後通過這個Hash碼不同數值對應的資料區域進行分區。例如可以建立一個對錶主鍵進行分區的表。
• Key（索引值）-上面Hash模式的一種延伸，這裡的Hash Key是MySQL系統產生的。
• List（預定義列表）–這種模式允許系統通過預定義的列表的值來對資料進行分割。
• Composite（複合模式） –以上模式的組合使用　

 

二，分區能做什麼

• 邏輯資料分割
• 提高單一的寫和讀應用速度
• 提高分區範圍讀查詢的速度
• 分割資料能夠有多個不同的物理檔案路徑
• 高效的儲存曆史資料
• 一個表上的約束檢查
• 不同的主從伺服器分區策略，例如master按Hash分區，slave按range分區

 

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

•   只能對資料表的整型列進行分區，或者資料列可以通過分區函數轉化成整型列

•   最大分區數目不能超過1024

•   如果含有唯一索引或者主鍵，則分區列必須包含在所有的唯一索引或者主鍵在內

•   不支援外鍵

•   不支援全文索引（fulltext）

• 按日期進行分區很非常適合，因為很多日期函數可以用。但是對於字串來說合適的分區函數不太多

四，什麼時候使用分區

•   海量資料表

•   曆史錶快速的查詢，可以採用ARCHIVE+PARTITION的方式。

•   資料表索引大於伺服器有效記憶體

•   對於大表，特別是索引遠遠大於伺服器有效記憶體時，可以不用索引，此時分區效率會更有效。

五，分區實驗

實驗一：

使用 US Bureau of Transportation Statistics發布的資料(CSV格式).目前, 包括 1.13 億條記錄，7.5 GB資料5.2 GB索引。時間從1987到2007。

伺服器使用4GB記憶體，這樣資料和索引的大小都超過了記憶體大小。設定為4GB原因是資料倉儲大小遠遠超過可能記憶體的大小，可能達幾TB。對普通OLTP資料庫來說，索引緩衝在記憶體中，可以快速檢索。如果資料超出記憶體大小，需要使用不同的方式。

建立有主鍵的表，因為通常表都會有主鍵。表的主鍵太大導致索引無法讀入記憶體，這樣一般來說不是高效的，意味著要經常訪問磁碟，訪問速度完全取決於你的磁碟和處理器。目前在設計很大的資料倉儲裡，有一種普遍的做法是不使用索引。所以也會比較有和沒有主鍵的效能。

測試方法：

使用三種資料引擘MyISAM, InnoDB, Archive.
對於每一種引擘, 建立一個帶主鍵的未分區表 (除了archive) 和兩個分區表，一個按月一個按年。分區表分區方式如下：

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)

)

每一個都在 mysql伺服器上的單獨的執行個體上測試, 每執行個體只有一個庫一個表。每種引擘, 都會啟動服務, 執行查詢並記錄結果, 然後關閉服務。服務執行個體通過MySQL Sandbox建立。

載入資料的情況如下：

ID	引擘	是否分區	資料	大小	備忘	載入時間 (*)
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伺服器上

為了對比分區在大的和小的資料集上的效果，建立了另外9個執行個體，每一個包含略小於2GB的資料。

查詢語句有兩種

• 聚集查詢

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

對於第一種查詢，建立不同的日期範圍的語句。對於每一個範圍，建立一組額外的相同範圍日期的查詢。每個日期範圍的第一個查詢是冷查詢，意味著是第一次命中，隨後的在同樣範圍內的查詢是暖查詢，意味著至少部分被緩衝。查詢語句在the Forge上。

結果：

1帶主鍵的分區表

第一個測試使用複合主鍵，就像未經處理資料表使用的一樣。主鍵索引檔案達到5.5 GB. 可以看出，分區不僅沒有提高效能，主鍵還減緩了操作。因為如果使用主鍵索引查詢，而索引又不能讀入記憶體，則表現很差。提示我們分區很有用，但是必須使用得當。

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

| 狀態   | myisam 不分區   |   myisam 月分區 |  myisam 年分區  |

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

| cold   | 2.6574570285714 |       2.9169642 | 3.0373419714286 |

| warm   | 2.5720722571429 | 3.1249698285714 | 3.1294000571429 |

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

ARCHIVE引擘

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

|  狀態  | archive不分區  |   archive月分區|   archive年分區 |

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

| cold   |     249.849563 | 1.2436211111111 | 12.632532527778 |

| warm   |     235.814442 | 1.0889786388889 | 12.600520777778 |

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

注意ARCHIVE引擘月分區的回應時間比使用MyISAM好。

2不帶主鍵的分區表

因為如果主鍵的大小超出了可用的key buffer，甚至全部記憶體，所有使用主鍵的查詢都會使用磁碟。新的方式只使用分區，不要主鍵。效能有顯著的提高。

按月分區表得到了70%-90%的效能提高。

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

| 狀態   | myisam 不分區    |   myisam 月分區  |  myisam 年分區   |

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

| cold   |  2.6864490285714 | 0.64206445714286 |  2.6343286285714 |

| warm   |  2.8157905714286 | 0.18774977142857 |  2.2084743714286 |

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

為了使區別更明顯, 我使用了兩個大規模查詢，可以利用分區的分區消除功能。

# query 1 – 按年統計

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

FROM flightstats

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

GROUP BY y

# query 2 – 按月統計

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

FROM flightstats 

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

GROUP BY m

結果顯示按月分區表有30%-60%，按年分區表有15%-30%效能提升。

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

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

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

|        1 | 97.779958 | 36.296519 | 82.327554 |

|        2 |  69.61055 | 47.644986 |  47.60223 |

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

處理器因素

當以上測試在家用機(Intel Dual Core 2.3 MHz CPU)上測試的時候。對於原來的對於dual Xeon 2.66 MHz來說，發現新伺服器更快！。

重複上面的測試，令人吃驚:

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

|狀態    | myisam 不分區     |myisam 月分區|  myisam 年分區  |

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

| cold   | 0.051063428571429 |   0.6577062 | 1.6663527428571 |

| warm   | 0.063645485714286 |   0.1093724 | 1.2369152285714 |

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

myisam 不分區帶主鍵的表比分區表更快. 分區表的表現和原來一樣，但未分區表效能提高了，使得分區顯得不必要。既然這台伺服器似乎充分利用了索引的好處，我在分區表的分區列上加入了索引。

# 原始表

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)

)

# 分區表

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 …

結果是讓人滿意的,得到35% 效能提高。

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

|狀態    | myisam 不分區     |myisam 月分區      |  myisam 年分區   |

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

| cold   | 0.075289714285714 | 0.025491685714286 | 0.072398542857143 |

| warm   | 0.064401257142857 | 0.031563085714286 | 0.056638085714286 |

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

結論：

1.  使用表分區並不是效能提高的保證。它依賴於以下因素：

• 分區使用的列the column used for partitioning;
• 分區函數，如果原始欄位不是int型;
• 伺服器速度;
• 記憶體數量.

2.  在應用到生產系統前運行基準測試和效能測試

依賴於你的資料庫的用途，你可能得到巨大的效能提高也可能一無所獲。如果不小心，甚至有可能會降低效能。

比如：一個使用月分區的表，在總是進行日期範圍查詢時可以得到極優的速度。但如果沒有日期查詢，那麼會進行全表掃描。 

分區對于海量資料效能提高是一個關鍵的工具。什麼才是海量的資料取決於部署的硬體。盲目使用分區不能保證提高效能，但是在前期基準測試和效能測試的協助下，可以成為完美的解決方案。

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

Archive 表分區後可以得到巨大的效能提高。當然也依賴於你的用途，沒有分區時任何查詢都是全表掃描。如果你有不需要變更的曆史資料，還要進行按時間的分析統計，使用Archive引擘是極佳的選擇。它會使用10-20％的原空間，對於聚集查詢有比MyISAM /InnoDB表更好的效能。

雖然一個很好的最佳化的分區MyISAM 表效能可能好於對應的Archive表, 但是需要10倍的空間。

 

實驗二：

1.建兩個表，一個按時間欄位分區，一個不分區。

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.建一個預存程序, 利用該過程向兩個表插入各8百萬條不同資料。

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

//

然後執行

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.開始對這兩表中的資料進行簡單的範圍查詢吧。並顯示執行過程解析：

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)

從上面結果可以看出，使用表分區比非分區的減少90％的回應時間。命令解析Explain程式可以看出在對已分區的表的查詢過程中僅對第一個分區進行了掃描，其餘跳過。進一步測試：

– 增加日期範圍

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)

 

結論：

• 分區和未分區佔用檔案空間大致相同 （資料和索引檔案）
• 如果查詢語句中有未建立索引欄位，分區時間遠遠優於未分區時間
• 如果查詢語句中欄位建立了索引，分區和未分區的差別縮小，分區略優於未分區。
• 對於大資料量，建議使用資料分割函數。
• 去除不必要的欄位
• 根據手冊，增加myisam_max_sort_file_size 會增加分區效能