本文介绍: 欢迎关注博主 六月暴雪飞梨花 或加入【六月暴雪飞梨花】一起学习分享Linux、C、C++、Python、Matlab机器人运动控制、多机器人协作智能优化算法滤波估计、多传感器信息融合机器学习,人工智能相关领域知识技术

概述

分区概述

在 MySQL 中, InnoDB存储引擎长期以来一直支持空间概念。在 MySQL 8.0 中,同一个分区表的所有分区必须使用相同存储引擎。但是,也可以为同一 MySQL 服务器甚至同一数据库中的不同分区表使用不同的存储引擎

通俗地讲表分区是将一大表,根据条件分割成若干个小表。MySQL 5.1开始支持数据表分区操作。为了改善大型表以及具有各种访问模式的表的可伸缩性,可管理性和提高数据库效率我们做出了表分区的概念。表分区有如下优点:

存储更多的数据
对于失去保存意义的数据删除有关的分区,很容易地删除那些数据,相比较delete语句truncate语句删除分区更容易。
查询可以得到极大优化,虽然可以增加索引主键外键
很容易地进行并行处理聚合函数SUM()和COUNT()
增加查询吞吐量
重要:分区适用于表的所有数据索引;您不能只对数据进行分区而不对索引进行分区,反之亦然,也不能只对表的一部分进行分区。

分区类型

MySQL 8.0 中可用的分区类型。其中包括此处列出的类型:

范围分区。 这种类型的分区根据落在给定范围内的列值将行分配给分区。
LIST 分区。 类似于分区 by RANGE不同之处在于分区是根据与一组离散值中的一个匹配的列来选择的。
哈希分区。 使用这种类型的分区,根据用户定义表达式返回的值选择分区,该表达式对要插入表的行中的列值进行操作。
KEY分区。 这种类型的分区类似于分区 by HASH,只是只提供了一个多个评估的列,并且 MySQL 服务器提供了自己散列函数
列分区包含列范围分区(RANGE COLUMNS partitioning)和列集合分区(LIST COLUMNS partitioning)。
子分区。子分区(也称为 复合分区(Subpartitioning))是对分区表每个分区的进一步划分
数据库分区的一个非常常见用途是按日期分隔数据。一些数据库系统支持显式日期分区,而 MySQL 在 8.0 中没有实现。但是,在 MySQL 中创建基于[DATE]、 [TIME]、 或 [DATETIME]列或基于使用这些列的表达式的分区方案并不困难 。MySQL的分区是采用优化 [TO_DAYS()], [YEAR()]和 [TO_SECONDS()]功能,也可以使用其他日期时间函数返回一个整数或者NULL。

重要:要记住——无论您使用哪种分区类型——分区总是在创建自动顺序编号,从 0. 当新行插入到分区表中时,这些分区号用于标识正确的分区。

1 分区管理

RANGE 分区和 LIST 分区的管理新增删除差不多,下面我就用比较常用的RANGE 分区作为实战

1.1 创建带有分区的表

可以命令行执行,也可以工具Navicat界面工具里面执行下面的语句,下面我将展示我在Navicat界面工具里面执行情况以及执行后返回结果

【插入数据脚本

CREATE TABLE tb_tr (
  id INT COMMENT "ID编号", 
  name VARCHAR(50) COMMENT "名称", 
  purchased DATE COMMENT "购买时间",
  PRIMARY KEY (`id`, `purchased`) USING BTREE COMMENT "主键",
  INDEX `idx_id`(id) COMMENT "索引-id",
  INDEX `idx_name`(name) COMMENT "索引-名称",
  INDEX `idx_purchased`(purchased) COMMENT "索引-购买"
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic 
    COMMENT '购买日志'
PARTITION BY RANGE( YEAR(purchased) ) (
  PARTITION p0 VALUES LESS THAN (1990) ENGINE = InnoDB,
  PARTITION p1 VALUES LESS THAN (1995) ENGINE = InnoDB,
  PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
  PARTITION p3 VALUES LESS THAN (2005) ENGINE = InnoDB,
  PARTITION p4 VALUES LESS THAN (2010) ENGINE = InnoDB,
  PARTITION p5 VALUES LESS THAN (2015) ENGINE = InnoDB,
  PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB
  PARTITION pmax VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
);

OK, Time: 0.055000s

【插入数据结果展示
在这里插入图片描述

这里创建了名称为p0、p1、p2、p3、p4、p5、pmax 六个分区。其中p0和pmax分区比较特别,这里可以理解数学上面的分区函数分段函数,从函数上,很好理解分区到底是什么概念
在这里插入图片描述

1.2 插入几条数

【插入数据脚本

INSERT INTO tb_tr VALUES
    (1, 'desk organiser', '2003-10-15'),
    (2, 'alarm clock', '1997-11-05'),
    (3, 'chair', '2009-03-10'),
    (4, 'bookcase', '1989-01-10'),
    (5, 'exercise bike', '2014-05-09'),
    (6, 'sofa', '1987-06-05'),
    (7, 'espresso maker', '2011-11-22'),
    (8, 'aquarium', '1992-08-04'),
    (9, 'study desk', '2006-09-16'),
    (10, 'lava lamp', '1998-12-25');
    
Affected rows: 10, Time: 0.004000s

【插入数据结果展示
在这里插入图片描述

1.3 查看分区内的数据

下面的结果是一样的,但是效果不一样。可以使用EXPLAIN查看下执行计划

SELECT * FROM tb_tr PARTITION (p2);

SELECT * FROM tb_tr WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';

1.4 删除分区

删除分区时,也会删除该分区中存储的所有数据。必须先拥有该[DROP](表的 权限然后才能 ALTER TABLE ... DROP PARTITION对该表执行。

ALTER TABLE tb_tr DROP PARTITION p2;
OK, Time: 0.022000s

那有没有可以在不删除数据的情况下,删除分区呢?答案是有的,请使用ALTER TABLE ... REORGANIZE PARTITION改用。对于按范围分区的表,您只能 ADD PARTITION将新分区添加到分区列表的高端。这就意味着,需要pmax分区再次分出来一张空间例如

1.5 增加分区

1.5.1 增加分区

如果使用这种方式增加分区,那么你得到的将会是错误提示

1481 - MAXVALUE can only be used in last partition definition, Time: 0.002000s

1.5.2 重新组织两个新分区

ALTER TABLE tb_tr
    REORGANIZE PARTITION pmax INTO (
      PARTITION p6 VALUES LESS THAN (2020) ENGINE = InnoDB, 
      PARTITION n1 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
    );

在增加分区时,新的RANGE分区方案不能有任何重叠范围;新的LIST 分区方案不能有任何重叠的值集。分区的表RANGE,您只能重组相邻的分区;您不能跳过范围分区。分区也不是无限制的分区下去,不使用[NDB]存储引擎给定表的最大可能分区数是 8192。

1.6 查询已经创建分区

查询数据脚本展示

SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema = 【dbName】 and table_name = "【tableName】";

查询数据结果展示
在这里插入图片描述

2 分区限制条件

2.1 禁止的构造

分区表达式中不允许使用以下结构

存储过程、存储函数、可加载函数或插件
声明变量用户变量
当然下面自带的函数除外:
ABS() CEILING() DATEDIFF() DAY() DAYOFMONTH() DAYOFWEEK() DAYOFYEAR() EXTRACT() FLOOR() HOUR() MICROSECOND() MINUTE() MOD() MONTH() 常用 QUARTER() SECOND() TIME_TO_SEC() TO_DAYS() 常用 TO_SECONDS() UNIX_TIMESTAMP() (with TIMESTAMP columns) WEEKDAY() YEAR() YEARWEEK()

上面的函数可以在分区中使用,这样子就可以按照需求来制定自己的分区。

2.2 允许在分区中使用运算符

算术逻辑运算
允许在分区表达式中 使用算术运算符 +、 -和 *。但是,结果必须是整数值或NULL

运算符 | 、&amp;、 ^、 <<、 &gt;&gt;、 ~不允许在分区表达式中使用。
分区 InnoDB 表不支持外键
全文索引。 分区表不支持FULLTEXT 索引搜索
空间列。 具有空间数据类型例如POINT 或GEOMETRY不能在分区表中使用)的列。
临时表。 临时表不能分区。
日志表。 无法对日志表进行分区;[ALTER TABLE ... PARTITION BY ...]此类表上的 语句因错误失败
分区键的数据类型。 分区键必须是整数列解析整数的表达式。[ENUM]不能使用使用列的表达式 。列或表达式值也可能是NULL; 例外情况参考官网

F&amp;Q

有些时候,写着,写着,也会翻车,例如,我在实际操作过程遇到很多问题,但是有了网络之后,就开始搜罗,一点点解决

Q1:定时处理这些数据

需求描述

搜索了一番,将上述的表分区整理成为了按照月度来进行调度分区,然后根据月度来将3个月前的数据迁移到第三张表history表,history基本上不使用的表,结构业务表。

具体的思路
1:创建相同结构的表;
2:创建一个函数,这个函数用于分区
3:创建一个事件,在每月的1号调用分区函数,创建分区,这个分区是两张表(业务表和业务_history表)
4:创建一个函数,用于查询业务数据插入到业务历史表,并删除业务表的数据和分区
5:创建一个事件用于每月1号调用处理数据函数,迁移数据、删除分区

Q2:SQL语句、单词拼写错误

问题描述

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 ‘NULL’ at line 1

多数情况下,应该是SQL脚本写错了,单词拼写错了,或者SQL语句中的分割拼接错误,导致不能执行,这个需要检查下,然后再次执行即可。我就是拼接SQL时忘记写了空格,导致被执行的SQL。例如我下面的的拼接SQL的where前面么有空格,导致SQL是一个不可以执行的语句

CONCAT('INSERT INTO ', TO_TABLE, ' SELECT * FROM ', FROM_TABLE, 'WHERE data_date < DATE_SUB(CURDATE(), INTERVAL 3 MONTH);');

Q3:权限问题

问题描述

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

这个很明显,是权限的问题。我当时在一个库执行脚本,提示我这个权限问题,换到一个连接权限大一些即可,或给当前连接用户权限大一些。

原文地址:https://blog.csdn.net/L_Lycos/article/details/134790566

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任

如若转载,请注明出处:http://www.7code.cn/show_42464.html

如若内容造成侵权/违法违规/事实不符,请联系代码007邮箱suwngjj01@126.com进行投诉反馈,一经查实,立即删除!

发表回复

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