MYSQL分区表功能测试简析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:
 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));  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/
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
192
分享
相关文章
使用崖山YMP 迁移 Oracle/MySQL 至YashanDB 23.2 验证测试
这篇文章是作者尚雷关于使用崖山YMP迁移Oracle/MySQL至YashanDB 23.2的验证测试分享。介绍了YMP的产品信息,包括架构、版本支持等,还详细阐述了外置库部署、YMP部署、访问YMP、数据源管理、任务管理(创建任务、迁移配置、离线迁移、校验初始化、一致性校验)及MySQL迁移的全过程。
【赵渝强老师】MySQL的基准测试与sysbench
本文介绍了MySQL数据库的基准测试及其重要性,并详细讲解了如何使用sysbench工具进行测试。内容涵盖sysbench的安装、基本使用方法,以及具体测试MySQL数据库的步骤,包括创建测试数据库、准备测试数据、执行测试和清理测试数据。通过这些步骤,可以帮助读者掌握如何有效地评估MySQL数据库的性能。
194 5
自动化测试项目实战笔记(一):JDK、Tomcat、MySQL、Jpress环境安装和搭建
这篇文章是关于自动化测试项目实战笔记,涵盖了JDK、Tomcat、MySQL、Jpress环境的安装和搭建过程,以及测试用例和常见问题总结。
122 1
自动化测试项目实战笔记(一):JDK、Tomcat、MySQL、Jpress环境安装和搭建
性能工具之 MySQL OLTP Sysbench BenchMark 测试示例
【8月更文挑战第6天】使用 pt-query-digest 工具分析 MySQL 慢日志性能工具之 MySQL OLTP Sysbench BenchMark 测试示例
423 0
性能工具之 MySQL OLTP Sysbench BenchMark 测试示例
|
8月前
|
基于PHP+MYSQL开发制作的趣味测试网站源码
基于PHP+MYSQL开发制作的趣味测试网站源码。可在后台提前设置好缘分, 自己手动在数据库里修改数据,数据库里有就会优先查询数据库的信息, 没设置的话第一次查询缘分都是非常好的 95-99,第二次查就比较差 , 所以如果要你女朋友查询你的名字觉得很好 那就得是她第一反应是查和你的缘分, 如果查的是别人,那不好意思,第二个可能是你。
103 3
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。