Mysql 分区表测试

本文详细介绍了MySQL分区表的测试,包括查看MySQL版本、测试不同存储引擎的支持情况、分区引擎的选择限制、分区类型(如RANGE、LIST、HASH、KEY)以及分区管理等。在测试中发现,同一个分区表的所有分区必须使用相同存储引擎,且分区键必须包含在主键内。此外,文章还讨论了子分区、时间作为分区键的限制,以及NULL值在分区中的处理方式。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

        刚接触MYSQL 不久,对其分区表功能学习了一下。

 

1.查看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));

   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引擎

   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

  

  同一个分区表中的所有分区必须使用同一个存储引擎,并且存储引擎要和主表的保持一致。


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

    

    使用年函数也可以分区。

 

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));
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));
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 可以重新组织分区。

 

 



 

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值