莱芜梆子网站,seo 网站地图,小型服务器做网站,电子商务网站整体策划概述
分区概述
在 MySQL 中#xff0c; InnoDB存储引擎长期以来一直支持表空间的概念。在 MySQL 8.0 中#xff0c;同一个分区表的所有分区必须使用相同的存储引擎。但是#xff0c;也可以为同一 MySQL 服务器甚至同一数据库中的不同分区表使用不同的存储引擎。
通俗地讲…概述
分区概述
在 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 InnoDBPARTITION 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.002000s1.5.2 重新组织为两个新分区
ALTER TABLE tb_trREORGANIZE 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
位运算符 、、 ^、 、 、 ~不允许在分区表达式中使用。 分区 InnoDB 表不支持外键 全文索引。 分区表不支持FULLTEXT 索引或搜索。 空间列。 具有空间数据类型例如POINT 或GEOMETRY不能在分区表中使用的列。 临时表。 临时表不能分区。 日志表。 无法对日志表进行分区[ALTER TABLE ... PARTITION BY ...]此类表上的 语句因错误而失败。 分区键的数据类型。 分区键必须是整数列或解析为整数的表达式。[ENUM]不能使用使用列的表达式 。列或表达式值也可能是NULL; 例外情况参考官网。
FQ
有些时候写着写着也会翻车例如我在实际操作过程中遇到很多问题但是有了网络之后就开始搜罗一点点解决。
Q1定时处理这些数据
需求描述 我搜索了一番将上述的表分区整理成为了按照月度来进行调度分区然后根据月度来将3个月前的数据迁移到第三张表history表history是基本上不使用的表结构同业务表。 具体的思路 1创建相同结构的表 2创建一个函数这个函数用于分区 3创建一个事件在每月的1号调用分区函数创建分区这个分区是两张表业务表和业务_history表 4创建一个函数用于查询业务数据插入到业务历史表并删除业务表的数据和分区 5创建一个事件用于每月1号调用处理数据函数迁移数据、删除分区
Q2SQL语句、单词拼写错误
问题描述 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 这个很明显是权限的问题。我当时在一个库执行脚本提示我这个权限问题换到一个连接权限大一些即可或给当前连接用户权限大一些。
