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

相关推荐

  • sql server 日志文件过大的处理方法

    选择文件–日志-限制大小为500m

    2023年6月13日
    43800
  • Mysql5.5中文乱码问题

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

    2022年8月21日
    68100
  • Mysql备份策略(windows版Mysql)图文详解

    1.建立备份BAT文件脚本 脚本保存未bat文件,放在备份文件夹中。 2.设置定时任务进入定时任务界面,创建任务: 设置触发器,凌晨为比较合适备份时间,系统负载小 操作设置执行刚刚编写的BAT处理脚本 条件设置 最后设置选项 3.灾备编写COPY脚本将备份的文件复制到备份储存盘中BAT脚本内容: 设置定时任务

    2022年8月5日
    89800
  • 数据库慢查询及其优化

    我们经常会谈到数据库慢查询。那么什么是数据库慢查询以及导致数据库慢查询的常见原因,以及对应的解决方法。 1、什么是数据库慢查询 数据库慢查询,就是查询时间超过了我们设定的时间的语句。可以通过语句查看设定的时间: 默认的设定时间是10秒,也可以通过下面这个语句修改默认的设定时间: 2、MySQL 慢查询的相关参数解释 3、慢查询日志配置 默认情况下slow_q…

    2022年6月11日
    80300
  • MySQL数据库断电修复(Database page corruption on disk or a failed)

    一、报错信息 启动日志如下: 看日志的大体的意思是数据页的损坏。 二、解决方案 2.1 修改配置  /etc/my.cnf 配置文件修改innodb 启动参数修改 如果innodb_force_recovery = 1不生效,则可尝试2-6几个数字。 然后重启mysql,重启成功。然后使用mysqldump或 pma 导出数据,执行修复操作等。修复完成后,把…

    2023年12月29日
    66600

发表回复

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

在线咨询: QQ交谈

邮件:712342017@qq.com

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

关注微信