MySQL系列---分区表实验

最新推荐文章于 2024-08-17 22:34:52 发布
最新推荐文章于 2024-08-17 22:34:52 发布
阅读量249 收藏
点赞数
分类专栏： MySQL系列 文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lipviolet/article/details/132058352
版权

目录

通用核心

  1. 分区字段必须是唯一索引和主键索引的一部分，原因就是，mysql中的主键、唯一限制都是通过索引实现的，唯一索引和主键索引只有一个区别那就是后者不能为null，同时，加了主键和唯一之后，该字段其实就可以省略index了，因为前两者本身就是特殊的索引，已经包含了索引的功能。
  2. 分区字段可以配合分区函数，只有数字类型和日期类型有可用的分区函数，字符类型等等是没有的

range分区

语法： PARTITION BY RANGE (expr) (PARTITION p0 VALUES LESS THAN (number))

核心： 整数表达式 + 连续递增区间 + switch判断逻辑

场景： 分区表达式范围变化

分区表达式结果必须为整数

DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(`name`)(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE)

// 失败：Field 'name' is of a not allowed type for this type of partitioning

DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(MD5(`name`))(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE)

// 失败： This partition function is not allowed

DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (30),
PARTITION p3 VALUES LESS THAN MAXVALUE)

// 成功

必须连续递增区间

DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (30),
PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN (50),
PARTITION p3 VALUES LESS THAN MAXVALUE)

// 失败：VALUES LESS THAN value must be strictly increasing for each partition

插入区间必须存在

DROP TABLE if EXISTS `test_range`;
CREATE TABLE `test_range` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by RANGE(year(`time`))(
PARTITION p0 VALUES LESS THAN (30),
PARTITION p1 VALUES LESS THAN (40),
PARTITION p2 VALUES LESS THAN (50))

// 成功

无法插入分区之外的值：
在这里插入图片描述

list分区

语法： PARTITION BY LIST(expr) (PARTITION p0 VALUES IN (number1,number2))

核心： 整数表达式 + 不重复离散值逗号分隔

场景： 分区表达式为穷尽离散值

分区表达式结果必须为整数

DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by list(`name`)(
PARTITION p0 VALUES in ("h"),
PARTITION p1 VALUES in ("w"),
PARTITION p2 VALUES in ("m"))

// 失败：1697 - VALUES value for partition 'p0' must have type INT

DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by list(`age`)(
PARTITION p0 VALUES in (10,11),
PARTITION p1 VALUES in (20,21),
PARTITION p2 VALUES in (30,31))

// 成功

分区枚举不可以重复

DROP TABLE if EXISTS `test_list`;
CREATE TABLE `test_list` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='range分区测试'
PARTITION by list(`age`)(
PARTITION p0 VALUES in (10,11),
PARTITION p1 VALUES in (20,21,11),
PARTITION p2 VALUES in (30,31))

// 失败：> 1495 - Multiple definition of same constant in list partitioning

插入区间必须存在

同 range分区

hash分区

语法： PARTITION BY HASH(expr) PARTITIONS number；

核心： 整数表达式 + 正整数分区数量

场景： 分区表达式where查询

分区表达式结果必须为整数

DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`name`)PARTITIONS 4;

分区数量必须为正整数

DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`age`)PARTITIONS -1;

// 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 '-1' at line 8

DROP TABLE if EXISTS `test_hash`;
CREATE TABLE `test_hash` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by HASH(`age`)PARTITIONS 0;

// 1504 - Number of partitions = 0 is not an allowed value

余数取摸决定分区

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

key分区

语法： PARTITION BY KSY(expr) PARTITIONS number；

核心： 任意表达式 + 正整数分区数量 + 服务器内置算法对任意数据类型求hash + 分区表达式非整数时极易分区不均衡

场景： 分区表达式where查询

分区表达式可以是任意类型

DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`time`)PARTITIONS 4;

// 成功

DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`name`)PARTITIONS 4;

// 成功

DROP TABLE if EXISTS `test_key`;
CREATE TABLE `test_key` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(99) NOT NULL,
	`age` int NOT NULL,
	`time` datetime NOT NULL,
  PRIMARY KEY (`id`,`name`,`time`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION by KEY(`age`)PARTITIONS 4;

// 成功

分区表达式的类型决定了分区算法

  1. 分区表达式的类型为整数：此时key分区完全等于hash分区
  2. 分区表达式的类型为字符串：对字符串求hash值进行hash算法
  3. 分区表达式的类型为日期：对日期求hash值进行hash算法
  4. 分区表达式的类型为其他：求hash值进行hash算法

非整数分区表达式时极易分区不均衡

  1. name VARCHAR(99) NOT NULL, 为key分区字段
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

  2. time datetime NOT NULL, 为key分区字段
    在这里插入图片描述
    在这里插入图片描述

总述

  1. range和list基本类似，区别就是前者必须是递增离散值，后者是不重复枚举值，相同点是一旦分区未包含插入值会插入失败，插入查询性能也类似。
  2. hash和key基本类似，区别就是前者只支持整数表达式，后者可以接收任意表达式，但是其算法也都是把其他的数据类型转化成整数再求hash值，相同点是任意数据都可以插入成功
  3. key分区比较特殊，虽然可支持范围比较大，但是非整数表达式就行分区时极易造成分区不均衡，实际操作时key分区能支持的极限就是字符串了，同时尽量避免使用key分区。
lipviolet
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
Mysql分区、分表
の博客
04-20 299
1.range分区 使用场景：需要保存历史数据时候；某一数据表数据量过大时可以根据时间字段进行分区；需要根据时间字段查询的需求； 2.list分区
常见的几种表分区
lijia6626369的专栏
11-09 1129
一、Range分区（范围分区） （1）按照时间划分进行分区
MySQL分区表：万字详解与实践指南
热门推荐
码到三十五
05-17 6万+
MySQL分区是一种数据库优化的技术，它允许将一个大的表或一个索引分割成多个较小的、更易于管理的片段，称为分区。这种技术可以显著提高查询性能、维护的方便性以及数据管理效率。本文将详细介绍MySQL分区的基本概念、工作原理、使用场景以及操作。
MySQL_17_分区表
爱喝可乐的w
05-15 455
MySQL中优化存储大量数据表的检索性能
mysql hash分区 创建_如何建mysql hash数据库分区
weixin_39601056的博客
01-19 482
用户需要在 rule.xml 中定义 partitionLength[] 和 partitionCount[] 两个数组和 hashSlice 二元组。在 DBLE 的启动阶段，点乘两个数组得到模数，也是逻辑分片的数量并且根据两个数组的叉乘，得到各个逻辑分片到物理分片的映射表(物理分片数量由 partitionCount[] 数组的元素值之和)此外根据 hashSlice 二元组，约定把分片索引值...
Mysql分区表为什么唯一键必须添加分区字段
Csong的专栏
04-13 5674
Hi，all: 我们从昨天开始纠结，为什么MySQL的分区表中唯一键必须加上分区字段？ 以主键ID为例。 我们陷入了误区就是，我们一直认为加了分区字段前ID是唯一，加了分区字段后ID还是唯一的？ 我们忽略的一点是，我们的表结构变了，索引结构变了，现在的唯一键并不能保证之前的字段是唯一的了，换句话说就是，Mysql的分区表并不能保证单字段唯一。...
mysql分区讲解及操作语句
蓝天⊙白云的博客
07-06 2734
1. 创建分区 （1）创建range分区 根据数值范围： 根据DATE、DATETIME范围： 添加COLUMNS关键字可定义非integer范围及多列范围，不过需要注意COLUMNS括号内只能是列名，不支持函数；多列范围时，多列范围必须呈递增趋势： 根据多列范围： 当需要删除一个分区上的“旧的”数据时，只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用”alter table staff drop partition p0;”来删除所有在1991年前就已经停止工作的雇员相对应
tidb-stable-zh-manual.pdf
02-20
在基本功能上，TiDB V4.0提供了多种数据类型、运算符、字符集、函数、SQL语句支持，并支持分区表、视图和约束等高级特性。总的来说，TiDB V4.0为用户提供了更多选择和更优性能，满足了不同的业务需求。
数据库课程设计实验报告-2012-模板.doc
10-03
虽然在提供的部分内容中未提及，但通常还包括物理结构设计，这涉及到如何在特定的数据库管理系统（如MySQL、Oracle、SQL Server等）上实现逻辑结构，考虑因素包括存储方式、索引类型、分区策略等，以优化查询效率和...
软件开发技术--数据库基础+精通.zip
01-01
关系型数据库是最常见的一种数据库类型，例如MySQL、Oracle和SQL Server等。这些数据库基于关系模型，以表格的形式存储数据，每个表格由列和行组成，列定义了数据类型，行则代表具体的数据实例。了解表的创建、修改...
数据库实验.zip
07-11
这个"数据库实验.zip"压缩包文件包含了华南理工大学计算机学院在2019年进行的一系列数据库实验报告，旨在帮助学生深入理解数据库的基本原理、操作和应用。 首先，数据库实验通常会涵盖以下几个核心知识点： 1. **...
数据库实验
06-16
这可能包括合理设计索引、调整查询语句、分区表以及使用存储过程等方法，以提高查询速度和系统整体性能。 通过这个2014年的数据库实验，你将有机会亲手操作数据库，解决实际问题，这将极大地加深你对数据库理论的...
MySQL分区表详解
qq_35597828的博客
03-28 7900
一、简介 MySQL分区能够根据一些规则将表中的数据分配给不同的物理分区
MySQL数据库表分区功能详解
deng1228的博客
07-24 2569
1、什么是表分区？ mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过my.cnf中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理...
MySQL数据库分区（Database partition）
拿笔小星的博客
05-31 3162
数据库分区是一种物理数据库设计技术，DBA和数据库建模人员对其相当熟悉。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。
深入理解MySQL——分区表
JC的博客
03-17 1884
分区表由多个相关的底层表实现，这些底层表也是由句柄对象（Handler object）表示，所以我们也可以直接访问各个分区。MySQL 支持多种分区表。我们看到最多的是根据范围进行分区，每个分区存储落在某个范围的记录，分区表达式可以是列，也可以是包含列的表达式。理解分区时还可以将其当作索引的最初形态。...
深入解析MySQL分区（Partition）功能
y19910825的博客
07-05 5111
深入解析MySQL分区（Partition）功能 转自：https://www.cnblogs.com/alamps/p/6740572.html 参考：https://blog.csdn.net/tjcyjd/article/details/11194489 第18章：分区 目录 18.1. MySQL中的分区概述 18.2. 分区类型 18.2.1. RANGE分区 18.2.2. ...
MySQL分区相关sql语法
IT_心如止水的博客
08-29 2134
分区的sql语句：  partition BY RANGE (store_id) (     partition p0 VALUES LESS THAN (6),     partition p1 VALUES LESS THAN (11),     partition p2 VALUES LESS THAN (16),     partition p3 VALUES LESS THAN (21)...
[数据库][知识]SQL Server、MySQL 和 Oracle 的默认端口和数据库链接
最新发布
一名不太专业的程序员
08-17 328
请确保在实际使用中，根据您自己的数据库配置（如主机名、端口、数据库名、用户名和密码等）来调整上述连接信息。同时，确保已经添加了相应数据库的 JDBC 驱动到您的项目依赖中。SQL Server、MySQL 和 Oracle 的默认端口号、连接 URL 和驱动类名。请注意，SQL Server 的 JDBC URL 格式可能需要根据实际的数据库实例进行调整，例如，如果数据库实例名称不是默认的。参数用于指定字符集，如果需要其他字符集设置，可能需要调整这些参数。指定了连接的主机和端口，
mysql-drop分区表
01-19
下面是MySQL中删除分区表的方法： 1. 使用ALTER TABLE语句删除分区： ```sql ALTER TABLE 表名 DROP PARTITION 分区名; ``` 例如，如果要删除名为emp的表中的名为p0的分区，可以执行以下命令： ```sql ALTER TABLE emp DROP PARTITION p0; ``` 2. 使用ALTER TABLE语句删除所有分区： ```sql ALTER TABLE 表名 REMOVE PARTITIONING; ``` 例如，如果要删除名为emp的表的所有分区，可以执行以下命令： ```sql ALTER TABLE emp REMOVE PARTITIONING;``` 请注意，删除分区表将永久删除分区及其数据，因此在执行此操作之前，请确保已经备份了重要数据。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

lipviolet

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值