MYSQL分區表功能測試簡析

　　show variables like '%partition%';

　　+-------------------+-------+

　　| variable_name     | value |

　　| have_partitioning | yes   |

　　如果value 為yes 則支援分區，

　　2.測試那種存儲引擎支援分區

inoodb引擎

mysql> create table engine1(id int) engine=innodb partition by range(id)(partition po values less than(10));

query ok, 0 rows affected (0.01 sec)

mrg_myisam引擎

mysql> create table engine2(id int) engine=mrg_myisam partition by range(id)(partition po values less than(10));

error 1572 (hy000): engine cannot be used in partitioned tables

blackhole引擎

mysql> create table engine3(id int) engine=blackhole partition by range(id)(partition po values less than(10));

csv引擎

mysql> create table engine4(id int) engine=csv partition by range(id)(partition po values less than(10));

memory引擎

mysql> create table engine5(id int) engine=memory partition by range(id)(partition po values less than(10));

federated引擎

mysql> create table engine6(id int) engine=federated partition by range(id)(partition po values less than(10));

archive引擎

mysql> create table engine7(id int) engine=archive partition by range(id)(partition po values less than(10));

myisam 引擎

mysql> create table engine8(id int) engine=myisam partition by range(id)(partition po values less than(10));

　　表分區的存儲引擎相同

　　mysql> create table pengine1(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=myisam);

　　query ok, 0 rows affected (0.05 sec)

　　表分區的存儲引擎不同

　　mysql> create table pengine2(id int) engine=myisam partition by range(id)(partition po values less than(10) engine=myisam, partition p1 values less than(20) engine=innodb);

　　error 1497 (hy000): the mix of handlers in the partitions is not allowed in this version of mysql

　　同一個分區表中的所有分區必須使用同一個存儲引擎，并且存儲引擎要和主表的保持一緻。

　4.分區類型

　　range:基于一個連續區間的列值，把多行配置設定給分區；

　　list：列值比對一個離散集合；

　　hash:基于使用者定義的表達式的傳回值選擇分區，表達式對要插入表中的列值進行計算。這個函數可以包含sql中有效的，産生非負整

　　數值的任何表達式。

　　key：類似于hash分區，差別在于key 分區的表達式可以是一列或多列，且mysql提供自身的hash函數。

　　5.range分區maxvalue值 及加分區測試；

　　建立表 prange，最後分區一個分區值是maxvalue

　　mysql> create table prange(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than maxvalue);

　　query ok, 0 rows affected (0.06 sec)

　　加分區

　　mysql> alter table prange add partition (partition p3 values less than (20));

　　error 1481 (hy000): maxvalue can only be used in last partition definition

　　在分區p0前面加個分區

　　mysql> alter table prange add partition (partition p3 values less than (1));

　　說明有maxvalue值後，直接加分區是不可行的；

　　建立表prange1，無maxvalue值

　　mysql> create table prange1(id int) engine=myisam partition by range(id)(partition po values less than(10), partition p1 values less than(20),partition p2 values less than (30));  www.2cto.com

　　query ok, 0 rows affected (0.08 sec)

　　從最大值後加個分區

　　mysql> alter table prange1 add partition (partition p3 values less than (40));

　　query ok, 0 rows affected (0.02 sec)

　　records: 0 duplicates: 0 warnings: 0

　　從分區的最小值前加個分區

　　mysql> alter table prange1 add partition (partition p43 values less than (1));

　　error 1493 (hy000): values less than value must be strictly increasing for each partition

　　由此可見，range 的分區方式在加分區的時候，隻能從最大值後面加，而最大值前面不可以添加；

　　6. 用時間做分區測試

　　create table ptime2(id int,createdate datetime) engine=myisam partition by range (to_days(createdate))

　　(partition po values less than (20100801),partition p1 values less than (20100901));

　　query ok, 0 rows affected (0.01 sec)

　　mysql> create table ptime3(id int,createdate datetime) engine=myisam partition by range (createdate)

　　error 1491 (hy000): the partition function returns the wrong type

　　直接使用時間列不可以，range分區函數傳回的列需要是整型。

　　mysql> create table ptime6(id int,createdate datetime) engine=myisam partition by range (year(createdate))

　　(partition po values less than (2010),partition p1 values less than (2011));

　　使用年函數也可以分區。

　　7.mysql可用的分區函數

day()

dayofmonth()

dayofweek()

dayofyear()

datediff()

extract()

hour()

microsecond()

minute()

mod()

month()

quarter()

second()

time_to_sec()

to_days()

weekday()

year()

yearweek() 等

　當然，還有floor（），ceiling() 等，前提是使用這兩個分區函數的分區健必須是整型。

　　要小心使用其中的一些函數，避免犯邏輯性的錯誤，引起全表掃描。

　　比如：

create table ptime11(id int,createdate datetime) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));

mysql> insert into ptime11 values (1,'2010-06-17');

mysql> explain partitions select count(1) from ptime11 where createdate>'2010-08-17'\g;

*************************** 1. row ***************************

id: 1

select_type: simple

table: ptime11

partitions: po,p1

type: all

possible_keys: null

key: null

key_len: null

ref: null

rows: 5

extra: using where

1 row in set (0.00 sec)

　　8.主鍵及限制測試

　　分區健不包含在主鍵内

　　mysql> create table pprimary(id int,createdate datetime,primary key(id)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));  www.2cto.com

　　error 1503 (hy000): a primary key must include all columns in the table's partitioning function

　　分區健包含在主鍵内

　　mysql> create table pprimary1(id int,createdate datetime,primary key(id,createdate)) engine=myisam partition by range (day(createdate)) (partition po values less than (15),partition p1 values less than (31));

　　說明分區健必須包含在主鍵裡面。

　　mysql> create table pprimary2(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(uid)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));

　　error 1503 (hy000): a unique index must include all columns in the table's partitioning function

　　說明在表上建限制索引會有問題，必須把限制索引列包含在分區健内。

　　mysql> create table pprimary3(id int,createdate datetime,uid char(10),primary key(id,createdate),unique key(createdate)) engine=myisam partition by range(to_days(createdate))(partition p0 values less than (20100801),partition p1 values less than (20100901));

　　query ok, 0 rows affected (0.00 sec)

　　雖然在表上可以加限制索引，但是隻有包含在分區健内，這種情況在實際應用過程中會遇到問題，這個問題點在以後的mysql 版本中也許會改進。

　　9.子分區測試

　　隻有range和list分區才能有子分區，每個分區的子分區數量必須相同，

　　mysql> create table pprimary7(id int,createdate datetime,uid char(10),primary key(id,createdate)) engine=myisam partition by range(to_days(createdate)) subpartition by hash(to_days(createdate))(partition p0 values less than (20100801) ( subpartition so,subpartition s1) ,partition p1 values less than (20100901) (subpartition s0,subpartition s1));  www.2cto.com

　　error 1517 (hy000): duplicate partition name s1

　　提示了重複的分區名稱錯誤，這和mysql5.1幫助文檔中的說明有出入，不知道是不是這個問題在某個小版本中修改過。

　　10.mysql分區健null值測試；

　　mysql将null值視為0.自動插入最小的分區中。

　　11.mysql分區管理測試

　　mysql> alter table pprimary4 truncate partition p1;

　　error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'truncate partition p1' at line 1

　　5.1版本中還不支援這個文法，5.5中已經支援，很好的一個指令；

　　alter table reorganize 可以重新組織分區。

最新内容請見作者的github頁：http://qaseven.github.io/