MYSQL分区

一:概念

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一个索引,但是实际上这个表可能由数10个物理分区对象组成,每个分区都是一个独立的对象,可以独自处理,可以作为表的一部分进行处理。分区对应用来说是完全透明的,不影响应用的业务逻辑。

二:分区优点

  1. 性能提升: 分区可以提高查询性能,特别是当查询只涉及到某个分区的数据时,数据库可以仅扫描相关分区,而不是整个表。这样可以减少IO操作,提高查询速度。
  2. 数据维护简化: 分区使得数据的维护更加灵活和简便。可以更容易地执行针对某个特定分区的数据备份、恢复、重新构建索引等操作,而不会影响整个表的数据。
  3. 空间管理: 分区可以帮助更有效地管理存储空间。例如,可以将历史数据移动到不同的分区,以便更容易地进行归档或删除。这有助于降低整个数据库的存储成本。
  4. 更好的并发控制: 在某些情况下,使用分区可以提高并发性,因为不同的分区可以并行处理不同的查询请求。
  5. 更容易维护大型表: 对于非常大的表,分区可以帮助提高查询性能和维护效率,使其更容易处理和管理。
  6. 维护成本低。如果一个成熟的业务遇到瓶颈后引入表分区技术,与分表比起来代码维护量小,基本不用改动,且不需额外创建子表。

三:分区局限性

  1. 必须使用分区字段才行,不然分区查询就会失败。走所有分区,这样反而导致查询变慢,性能不升反降
  2. 分区键选择选择不当后,可能会导致不均匀的数据分布,进而影响性能
  3. 分区的实施和管理可能会增加数据库的复杂性。在设计和维护分区方案时,需要考虑额外的管理和维护工作,包括分区键的选择、分区策略等。

四:分区介绍

目前MySQL支持一下四种类型的分区:

  • RANGE分区:基于一个给定区间边界,得到若干个连续区间范围,按照分区键的落点,把数据分配到不同的分区;
  • LIST分区:类似RANGE分区,区别在于LIST分区是基于枚举出的值列表分区,RANGE是基于给定连续区间范围分区;
  • HASH分区:基于用户自定义的表达式的返回值,对其根据分区数来取模,从而进行记录在分区间的分配的模式。这个用户自定义的表达式,就是MySQL希望用户填入的哈希函数。
  • KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且使用MySQL 服务器提供的自身的哈希函数。

如果表存在主键或者唯一索引时,分区列必须是唯一索引的一个组成部分,即将分区字段和唯一索引创建组合索引。

1:RANGE分区

如下创建一个test1表,创建三个分区,当time字段值小于1704038400时放入part0分区,当time字段值小于1735660800时放入part1分区,其余数据放入part2分区

CREATE TABLE `test1` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `time` int(11) NOT NULL DEFAULT 0 COMMENT '时间',
    PRIMARY KEY (`id`,`time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (time) PARTITIONS 3 (
PARTITION part0 VALUES LESS THAN (1704038400), 
PARTITION part1 VALUES LESS THAN (1735660800),
PARTITION part2 VALUES LESS THAN MAXVALUE
);

2:LIST分区

如下创建一个test2表,创建两个分区,将status值为0和1的放入part0分区,将status值为2和3的放入part1分区

CREATE TABLE `test2` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'status',
    PRIMARY KEY (`id`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY LIST(status) (
        PARTITION part0 VALUES IN (0,1),
        PARTITION part1 VALUES IN (2,3)
);

LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。所以将要匹配的任何值都必须在值列表中能够找到

3:HASH分区

如下创建一个test3表,并创建三个HASH分区,在HASH分区中,MySQL自动完成分配记录到区间的工作,你所要做的只是确定一个用来做哈希的字段或者表达式,以及指定被分区的表将要被分割成的分区数量

CREATE TABLE `test3` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'status',
    PRIMARY KEY (`id`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY HASH (status) PARTITIONS 3;
  • hash分区的字段不能太复杂,否则插入将会有性能的影响
  • hash分区的优势在对单条数据的查找,范围查找的性能不如RANGE分区
  • hash分区只支持数字分区,或用表达式将字符串转成数字

4:KEY分区

如下创建一个test4表,并创建三个KEY分区,key分区类似于hash分区,本质区别是hash分区使用的是用户自定义的表达式,而key分区函数是由MySQL 服务器提供的,不同的存储引擎使用不同的内部函数。 创建key分区的语法和hash分区差不多

CREATE TABLE `test4` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT 'status',
    PRIMARY KEY (`id`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY KEY (status) PARTITIONS 3;

KEY分区和HASH分区区别

  1. 关键字由HASH替换为KEY,例如PARTITION BY KEY()
  2. KEY中包含0个或者多个列名。如果一个表有主键的话那么任何被用于key分区的列必须是表中主键的一部分。若表中有定义主键,且key分区中不包含任何一个列名,则表的主键列将会被用于key分区

五:分区注意事项

  1. MySQL中的分区在禁止空值(NULL)上没有进行处理。在RANGE分区中,无论是插入一个列值为NULL或者表达式值为NULL的记录,都被当作是小于任何其他值,会默认被保存在从低到高排好序的第一个分区。在LIST分区中,如果所有分区LIST列表值里都没有NULL值,则插入含有NULL值的记录时会报错。在hash和key分区中NULL值则都当作0处理。
  2. 一个表最多能有1024个分区,在5.7版本及以上可以有8196个分区
  3. 常见的InnoDB 、 MyISAM引擎都支持分区

六:分区常用操作sql

1:删除分区并删除数据

alter table test(表名) drop partition p1(分区名);

2:删除分区的数据,保留分区

alter table test(表名) truncate partition p1(分区名);

3:移除整个表的分区,不删除数据

alert table test(表名) remove PARTITIONING ;

文章来源:https://www.cnaaa.net,转载请注明出处:https://www.cnaaa.net/archives/11933

(0)
凯影的头像凯影
上一篇 2024年6月3日 下午2:34
下一篇 2024年6月4日 下午2:48

相关推荐

  • Mysql5.5中文乱码问题

    查看MySQL的字符集 发现有部分不是utf-8 一(推荐,本人实验有用) 修改MySQL配置文件,来到mysql安装目录,找到my.ini文件(可能没有此文件,没有就自己建一个,本人就是没有该文件) 若有my.ini文件 进去之后,在三个地方增加配置 第一个 第二个 第三个 若没有my.ini文件,则添加一个,内容为: 注意!!! 记得重启mysql服务 …

    2022年8月21日
    50600
  • Centos7.6安装MySQL(超详细)

    1、查看是否已经安装Mysql rpm -qa | grep mysql 我已经安装过了 如果你查看出来有东西,可以使用下面命令将其删除 rpm -e 文件名 如果提示warning: waiting for transaction lock on /var/lib/rpm/.rpm.lock,则需要执行 2、下载官方Mysql包 wget -i -c ht…

    2022年6月9日
    71300
  • 如何快速实现 Redis 持久化

    RDB快照(Redis DataBase) RDB是一种快照存储持久化方式,具体就是将Redis某一时刻的内存数据保存到硬盘的文件当中,默认保存的文件名为dump.rdb,而在Redis服务器启动时,会重新加载dump.rdb文件的数据到内存当中恢复数据。 开启RDB持久化方式 开启RDB持久化方式很简单,客户端可以通过向Redis服务器发送save或bgs…

    2023年6月29日
    24900
  • mysql的主从延迟问题主要原因及解决

    MySQL数据库主从的安装搭建方法 在实际的生产中,为了解决Mysql的单点故障已经提高MySQL的整体服务性能,一般都会采用主从复制。 比如:在复杂的业务系统中,有一句sql执行后导致锁表,并且这条sql的的执行时间有比较长,那么此sql执行的期间导致服务不可用,这样就会严重影响用户的体验度。 主从复制中分为主服务器(master)和从服务器(slave)…

    2022年6月14日
    90900
  • 构建SQL自动审核系统

    Inception Inception是一个开源的Mysql自动化工具,具有SQL审核、执行、回滚等实用的功能,由国内大神基于mysql源码开发,可以很明确的,详细的,准确的审核Mysql的SQL语句,工作模式与Mysql完全相同,可以直接使用mysql客户端来连接。但遗憾的是2年前已停止更新,不过兼容大部分的mysql版本,仍然是开源SQL审核工具的翘楚。…

    2023年1月17日
    50900

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

在线咨询: QQ交谈

邮件:712342017@qq.com

工作时间:周一至周五,8:30-17:30,节假日休息

关注微信