MYSQL分区表功能测试简析-白红宇

强烈建议你试试无所不能的chatGPT，快点击我

MYSQL分区表功能测试简析

阅读量：5880 次

发布时间：2019-06-19

本文共 7573 字，大约阅读时间需要 25 分钟。

　1.查看版本是否支持分区

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

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

　　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.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/

转载地址：http://rujix.baihongyu.com/

你可能感兴趣的文章

jsoup (网页获取与解析)

【玩转数据系列十】利用阿里云机器学习在深度学习框架下实现智能图片分类...

解决之道：从互联网安全到IoT安全，如何关上潘多拉魔盒？

Activity过渡动画

Android 四种常见的线程池

【阿里云资讯】阿里云Serverless产品函数服务（Function Compute）预计年底发布

Spark Shuffle Write阶段磁盘文件分析

apt-get install nginx

利用HAProxy取代nginx代理activemq

函数中分配内存的问题

阿里技术高P访谈之“呆萌”程序员蒋晓伟为何从Facebook到阿里巴巴

OSSIM插件开发实战（配视频）

深入Protobuf源码－Descriptor、Message、RPC框架

图像处理------调整亮度与饱和度

Otter-入门篇4(单向同步实践)

【阿里在线技术峰会】方超：阿里聚安全在互联网业务中的创新实践

SOCKET 编程TCP/IP、UDP

CAN总线基础知识（二）

MySQL内核月报 2014.09-MySQL·　限制改进·GTID和升级

Angular Input格式化

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！

当前时间: 2024-12-26 05:00:12 当前IP: 3.145.106.176 联系邮箱:javaeecc@qq.com Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2

强烈建议你试试无所不能的CHAT-GPT，快点击我

强烈建议你试试无所不能的CHAT-GPT，快点击我