MYSQL分区表功效测试简析[MySQL防范]

　　1.查看Mysql版本能否支持分区

　　SHOW VARIABLES LIKE '%partition%';

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

　　| Variable_name | Value |

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

　　| have_partitioning | YES |

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

　　假如VALUE 为YES 则支持分区,

　　2.测试那种存储引擎支持分区

　　INOODB引擎 www.110hack.com

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

　　Query OK, 0 rows affected (0.01 sec)

　　CSV引擎

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

　　ERROR 1572 (HY000): Engine cannot be used in partitioned tables

　　Memory引擎 www.110hack.com

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

　　Query OK, 0 rows affected (0.01 sec)

　　federated引擎

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

　　Query OK, 0 rows affected (0.01 sec)

　　archive引擎

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

　　Query OK, 0 rows affected (0.01 sec)

　　myisam 引擎

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

　　Query OK, 0 rows affected (0.01 sec)

　　3.Mysql分区表,分区引擎测试

　　表分区的存储引擎相同

　　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 www.110hack.com

　　同一个分区表中的全部分区必须利用同一个存储引擎,并且存储引擎要和主表的保持一致.

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

　　ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

　　阐明有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.110hack.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)

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

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

　　Query OK, 0 rows affected (0.01 sec) www.110hack.com

　　利用年函数也可以分区.

　　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.110hack.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));

　　Query OK, 0 rows affected (0.05 sec)

　　阐明分区健必须包含在主键里面.

　　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.110hack.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
　　以上是“MYSQL分区表功效测试简析[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：