mysql分区

已于 2022-10-08 15:26:05 修改
阅读量2.1k 收藏 5
点赞数
分类专栏： 文档 笔记 文章标签： mysql 数据库
于 2022-09-28 10:57:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35138724/article/details/127082966
版权

mysql分区


如果一个表有任何唯一键，那么该表的分区表达式中使用的每个列都必须是每个唯一键的一部分，包括主键
删除分区也会删除该分区中的所有数据

概述

  • 分区的意思是指将同一表中不同行的记录分配到不同的物理文件中，几个分区就有几个.idb文件
  • 分区是将一个表或索引分解成多个更小，更可管理的部分。
  • 每个区都是独立的，可以独立处理，也可以作为一个更大对象的一部分进行处理。这个是MySQL支持的功能，业务代码无需改动。
  • 对于分区的使用应该非常小心，如果不清楚如何使用分区可能会对性能产生负面的影响。
  • 无论哪种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分。

在 MySQL 8.0 中，唯一支持分区的存储引擎是InnoDB和 NDB. 不支持分区的存储引擎不能使用分区；其中包括
MyISAM、MERGE、 CSV和FEDERATED存储引擎。

MySQL 8.0 不支持 垂直分区，其中一个表的不同列被分配到不同的物理分区。目前没有计划将垂直分区引入 MySQL。

要创建分区表，您必须使用支持它们的存储引擎。在 MySQL 8.0
中，同一个分区表的所有分区必须使用相同的存储引擎。但是，没有什么可以阻止您对同一 MySQL
服务器甚至同一数据库中的不同分区表使用不同的存储引擎。

分区适用于表的所有数据和索引；您不能只分区数据而不分区索引，反之亦然，也不能只分区表的一部分。
可以使用用于创建分区表 的语句的子句 的DATA DIRECTORY and INDEX DIRECTORY选项将 每个分区的数据和索引分配给特定目录

在MySQL 8.0中，分区支持由InnoDB和NDB存储引擎提供。
MySQL 8.0目前不支持使用InnoDB或NDB以外的任何存储引擎（如MyISAM）对表进行分区。

优点

  • 分区可以在一个表中存储比单个磁盘或文件系统分区上更多的数据。
  • 通过删除仅包含该数据的分区（或多个分区），通常可以轻松地从分区表中删除失去其用途的数据。相反，在某些情况下，通过添加一个或多个新分区来专门存储该数据，可以极大地促进添加新数据的过程。
  • 由于满足给定WHERE子句的数据只能存储在一个或多个分区上，因此可以极大地优化某些查询，这会自动从搜索中排除任何剩余的分区。因为在创建分区表后可以更改分区，所以您可以重新组织数据以增强在首次设置分区方案时可能不经常使用的频繁查询。这种排除不匹配分区（以及它们包含的任何行）的能力通常称为 分区修剪

分区限制

分区键、主键和唯一键的

分区表的分区表达式中使用的所有列都必须是该表可能具有的每个唯一键的一部分。

错误例子

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
) ENGINE = INNODB
PARTITION BY HASH(col3)
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function
# col3并不是唯一键的一部分   将col3也设为唯一键即可成功


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
) ENGINE = INNODB
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function   
# PRIMARY KEY必须包括表分区函数中的所有列 

CREATE TABLE t3 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1,col3),
    UNIQUE KEY (col2,col4)
) ENGINE = INNODB
PARTITION BY HASH(col1 + col4)
PARTITIONS 4;
# 语法错误  不存在包含唯一键的共同列

正确例子

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;


CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

因为根据定义，每个主键都是唯一的键，所以如果表有主键，那么这个限制还包括该表的主键(也就是唯一键和主键都得有共同列)

错误例子

CREATE TABLE t5 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col2)
)ENGINE=INNODB
PARTITION BY HASH(col3)
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function
# PRIMARY KEY必须包括表分区函数中的所有列
# col3 不在主键列表中

CREATE TABLE t6 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY(col1, col3),
    UNIQUE KEY(col2)
)ENGINE=INNODB
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
# A PRIMARY KEY must include all columns in the table's partitioning function
# PRIMARY KEY必须包括表分区函数中的所有列
# 主键列表未包含col2列，虽然col2列为唯一键依然不行

主键并不包括分区表达式中引用的所有列

如果表没有唯一键（包括没有主键），则此限制不适用，并且只要列类型与分区类型兼容，就可以在分区表达式中使用任何列。

出于同样的原因，除非键包括表的分区表达式所使用的所有列，否则以后不能向分区表添加唯一键。

存储引擎相关的分区限制

在MySQL 8.0中，分区支持实际上不是由MySQL Server提供的，而是由表存储引擎自己的或本机分区处理程序提供的。在MySQL 8.0中，只有InnoDB和NDB存储引擎提供本机分区处理程序。这意味着分区表不能使用除这些之外的任何其他存储引擎创建。

函数相关的分区限制

分区表达式中只允许使用下表所示的MySQL函数：

ABS()

CEILING() (see CEILING() and FLOOR())

DATEDIFF()

DAY()

DAYOFMONTH()

DAYOFWEEK()

DAYOFYEAR()

EXTRACT() (see EXTRACT() function with WEEK specifier)

FLOOR() (see CEILING() and FLOOR())

HOUR()

MICROSECOND()

MINUTE()

MOD()

MONTH()

QUARTER()

SECOND()

TIME_TO_SEC()

TO_DAYS()

TO_SECONDS()

UNIX_TIMESTAMP() (with TIMESTAMP columns)

WEEKDAY()

YEAR()

YEARWEEK()

分区类型

目前MySQL支持一下几种类型的分区，RANGE分区，LIST分区，HASH分区，KEY分区。

如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分。实战十有八九都是用RANGE分区。

RANGE分区

按范围分区的表的分区方式是，每个分区都包含分区表达式值位于给定范围内的行。范围应连续但不重叠，并使用VALUES LESS THAN运算符定义

当插入的数据不在一个分区中定义的值的时候，会抛异常。RANGE分区主要用于日期列的分区，比如交易表啊，销售表啊等。可以根据年月来存放数据。

语法 PARTITION BY RANGE()

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)ENGINE=INNODB
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

对timestamp分区

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

使用DATE或DATETIME列作为分区列，按RANGE COLUMNS对表进行分区

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(joined) (
    PARTITION p0 VALUES LESS THAN ('1960-01-01'),
    PARTITION p1 VALUES LESS THAN ('1970-01-01'),
    PARTITION p2 VALUES LESS THAN ('1980-01-01'),
    PARTITION p3 VALUES LESS THAN ('1990-01-01'),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

RANGE columns不支持使用除date或DATETIME之外的日期或时间类型的分区列

MySQL的分区实现将NULL视为小于任何非NULL值
使用RANGE分区处理NULL。如果在按RANGE分区的表中插入一行，使用于确定分区的列值为NULL，则该行将插入最低的分区

范围列分区

RANGE COLUMNS分区与RANGE分区有以下显著区别：

  • RANGE COLUMNS不接受表达式，只接受列的名称。
  • RANGE COLUMNS接受一个或多个列的列表。
  • RANGE COLUMNS分区基于元组（列值列表）之间的比较，而不是标量值之间的比较。RANGE COLUMNS分区中的行的放置也基于元组之间的比较；
  • RANGE COLUMNS分区列不限于整数列；字符串、DATE和DATETIME列也可以用作分区列。

语法：

CREATE TABLE table_name
PARTITION BY RANGE COLUMNS(column_list) (
    PARTITION partition_name VALUES LESS THAN (value_list)[,
    PARTITION partition_name VALUES LESS THAN (value_list)][,
    ...]
)

column_list: #一个或多个列的列表
    column_name[, column_name][, ...]

value_list:#一个值列表
    value[, value][, ...]

分区列列表和定义每个分区的值列表中的元素必须以相同的顺序出现。此外，值列表中的每个元素必须与列列表中的相应元素具有相同的数据类型。
例：

CREATE TABLE rcx (
  a INT,
  b INT,
  c CHAR(3),
  d INT
 )
 	PARTITION BY RANGE COLUMNS(a,d,c) (
    PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    PARTITION p1 VALUES LESS THAN (10,20,'mmm'),
    PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
 );

在RANGE分区中，生成的表达式值等于VALUES LESS THAN中的限制值的行永远不会放在相应的分区中；但是，在使用RANGE COLUMNS分区时，有时可能会将分区列列表的第一个元素的值与VALUES LESS THAN值列表中第一个元素值相等的行放在相应的分区中。

注意：多字段的分区键比较是基于数组的比较。它先用插入的数据的第一个字段值和分区的第一个值进行比较，如果插入的第一个值小于分区的第一个值那么就不需要比较第二个值就属于该分区；如果第一个值等于分区的第一个值，开始比较第二个值同样如果第二个值小于分区的第二个值那么就属于该分区。

LIST分区

LIST分区和RANGE分区很相似，只是分区列的值是离散的，不是连续的。LIST分区使用VALUES IN，因为每个分区的值是离散的，因此只能定义值。

  • 当按list进行分区时，可以只匹配整数列表
  • 使用LIST column分区时，值列表中可能会使用其他列类型
  • 与按范围定义分区的情况不同，列表分区不需要按任何特定顺序声明

语法例子

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

分区表达式的所有预期值都应该包含在PARTITION…values in（…）子句中。包含不匹配的分区列值的INSERT语句失败，并出现错误

当使用单个INSERT语句将多行插入到单个InnoDB表中时，InnoDB将该语句视为单个事务，因此如果存在任何不匹配的值，则会导致语句完全失败，因此不会插入任何行。

可以使用IGNORE关键字忽略此类错误。如果这样做，则不会插入包含不匹配的分区列值的行，但会插入具有匹配值的任何行，并且不会报告错误

使用LIST分区处理NULL。由LIST分区的表如果且仅当其中一个分区是使用包含NULL的值列表定义的，则允许NULL值。相反，由LIST分区的表如果在值列表中未显式使用NULL，则会拒绝导致分区表达式为NULL值的行
也就是分区包含null可以插入否则插入失败

LIST COLUMNS partitioning

例

CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

与按RANGE COLUMNS进行分区一样，不需要在COLUMNS（）子句中使用表达式将列值转换为整数。

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

HASH分区

HASH分区主要用于确保在预定数量的分区之间均匀分布数据。

该类型的扩展LINEAR HASH也可用

使用HASH分区对表进行分区，需要在CREATE table语句中附加partition BY HASH（expr）子句，其中expr是返回整数的表达式

语法例子

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

如果不包含PARTITIONS子句，分区数默认为1；使用后面没有数字的PARTITIONS关键字会导致语法错误。

使用PARTITION BY HASH时，存储引擎根据表达式结果的模数确定要使用num分区中的哪个分区。换句话说，对于给定的表达式expr，存储记录的分区是分区号N，其中N=MOD（expr，num）

线性HASH分区

线性哈希使用两种算法的线性幂，而常规哈希使用哈希函数值的模。

从语法上讲，线性哈希分区和常规哈希之间的唯一区别是在PARTITION BY子句中添加了linear关键字
语法：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

优点：分区的添加、删除、合并和拆分速度快得多，这在处理包含大量（TB）数据的表时非常有用。
缺点：与使用常规哈希分区获得的分布相比，数据不太可能在分区之间均匀分布

KEY分区

KEY分区和HASH分区相似，不同之处在于HASH分区使用用户定义的函数进行分区，KEY分区使用数据库提供的函数进行分区。

提供了一个或多个要评估的列，这些列可以包含除整数值以外的其他值，因为MySQL提供的哈希函数保证了整数结果，而不管列数据类型如何。

分区键的任何列必须包含表主键的一部分或全部（如果表有主键的话）。如果没有指定列名作为分区键，则使用表的主键（如果有），如果没有主键，但有唯一键，那么唯一键用于分区键：但是，如果唯一键列未定义为NOTNULL 则语句失败

该类型的扩展LINEAR KEY也可用。

按KEY或LINEAR KEY进行分区时，可以使用DATE、TIME或DATETIME列作为分区列，而无需对列值进行任何修改。

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)ENGINE=INNODB
PARTITION BY KEY(joined)
PARTITIONS 6;

使用HASH和KEY分区处理NULL。对于由HASH或KEY分区的表，NULL的处理有些不同。在这些情况下，任何产生NULL值的分区表达式都被视为其返回值为零

线性key分区

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

子分区

子分区也称为复合分区，是对分区表中每个分区的进一步划分
例句：

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN MAXVALUE (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );

注意：

  1. 每个分区必须具有相同数量的子分区
  2. 如果在分区表的任何分区上使用SUBPARITION显式定义任何子分区，则必须全部定义
  3. 子分区名称在整个表中必须是唯一的

分区维护

MySQL分区针对TO_DAYS（）、YEAR（）和TO_SECONDS（）函数进行了优化。但是，您可以使用其他返回整数或NULL的日期和时间函数，例如WEEKDAY（）、DAYOFYEAR（）或MONTH（）。

分区名称通常遵循管理其他MySQL标识符的规则，例如表和数据库的标识符。但是，应该注意分区名称不区分大小写。

删除分区 比delete删除效率要高很多

ALTER table 表名 DROP PARTITION p0；

对已存在的表进行分区

ALTER TABLE 表名 PARTITION BY 分区类型() (分区语句)

查看每个分区的记录行数和占用大小

SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_NAME,SUBPARTITION_METHOD,SUBPARTITION_EXPRESSION ,DATA_LENGTH
FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='md_rfid';

在这里插入图片描述
##清理分区数据为空，保留分区不删除，仅仅是清理数据

alter table bm_scenes_data_reminder truncate partition 分区名;

增加分区

范围分区

ALTER TABLE 表名 ADD PARTITION (PARTITION 分区名 VALUES LESS THAN (条件) ENGINE = InnoDB);

CREATE TABLE t5 (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)ENGINE=INNODB
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);

ALTER TABLE t5 ADD PARTITION (PARTITION p5 VALUES LESS THAN (500) );

在这里插入图片描述
因此如果需要在中间添加分区只能将需要添加的分区后边的分区都删除，再增加要加的分区再将删除的分区加回去。
但是这种方式会将删除的分区数据删除

list分区

list分区添加分区和rabge分区一样，只是list分区添加分区时不能将包含现有分区的值列表中的值添加进新分区否则会报错
解决：
可以通过添加一个只包含不冲突值的分区来处理此问题，然后重新组织新分区和现有分区，以便将存储在现有分区中的值移到新分区中

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO (
    PARTITION p1 VALUES IN (6, 18),
    PARTITION np VALUES in (4, 8, 12)
);

在不丢失数据的情况下，重新定义分区

ALTER TABLE t5 REORGANIZE PARTITION p0 INTO (
	PARTITION n0 VALUES LESS THAN (1950),
	PARTITION n1 VALUES LESS THAN (1960)
);

这个命令实际上是将p0分区拆分为两个新分区n0和n1,它还根据两个partition…VALUES…子句中包含的规则，将存储在p0中的数据移动到新分区中
在这里插入图片描述
REORGANIZE PARTITION子句也可以用于合并相邻分区

ALTER TABLE t5 REORGANIZE PARTITION n0,n1 INTO(PARTITION p0 VALUES LESS THAN (1960))

在这里插入图片描述
使用REORGANIZE PARTITION拆分或合并分区时不会丢失任何数据。在执行上述语句时，MySQL将存储在分区n0和n1中的所有记录移动到分区p0中

HASH和KEY

不能像从按RANGE或LIST分区的表中删除分区一样，从按HASH或KEY分区的表删除分区。然而，您可以使用ALTER TABLE…COALESCE PARTITION合并HASH或KEY分区

#创建一张表同时生成12个分区
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

#将分区从12个降为8个
ALTER TABLE clients COALESCE PARTITION 4;
# 将分区从12个添加到18个
ALTER TABLE clients ADD PARTITION PARTITIONS 6;

COALESCE同样适用于按HASH、KEY、LINEAR HASH或LINEAR KEY分区的表

COALESCE PARTITION后面的数字是要合并到其余分区中的分区数，换句话说，它是要从表中删除的分区数

使用语法

查询

SELECT id, CONCAT(fname, ' ', lname) AS name FROM employees PARTITION (p0) ORDER BY lname;
# 关联写法
SELECT e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name, s.city AS City, d.name AS department
   FROM employees AS e
   JOIN stores PARTITION (p1) AS s ON e.store_id=s.id
   JOIN departments PARTITION (p0) AS d ON e.department_id=d.id
   ORDER BY e.lname;

修改

UPDATE employees PARTITION (p0)  SET store_id = 2 WHERE fname = 'Jill';

取消分区

ALTER TABLE 表名 REMOVE PARTITIONING

分区的限制

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

  • 存储过程、存储函数、可加载函数或插件。
  • 声明的变量或用户变量。
  • 不允许使用/运算符。
  • 分区表达式中不允许使用位运算符|、&、^、<<、>>和~。

性能注意事项：

  1. 文件系统操作。分区和重新分区操作（如ALTER TABLE with PARTITION BY…、REORGANIZE PARTITION或REMOVE PARTTIONING）的实现依赖于文件系统操作。这意味着这些操作的速度受文件系统类型和特征、磁盘速度、交换空间、操作系统的文件处理效率以及MySQL服务器选项和与文件处理相关的变量等因素的影响。特别是，您应该确保启用了large_files_support，并正确设置了open_files_limit。通过启用InnoDB_file_per_table，可以提高涉及InnoDB表的分区和重新分区操作的效率。
  2. 在表上执行分区操作的进程会对该表进行写锁定。这些表的读数相对不受影响；挂起的INSERT和UPDATE操作将在分区操作完成后立即执行。
  3. 分区修剪。与非分区表一样，正确使用索引可以显著加快对分区表的查询。
  4. 分区表支持索引条件下推。
  5. MySQL 8.0中，LOAD DATA使用缓冲来提高性能。您应该知道，缓冲区每个分区使用130 KB内存来实现这一点。
  6. 最大分区数。不使用NDB存储引擎的给定表的最大可能分区数为8192。此数字包括子分区。
  7. 使用NDB存储引擎的表的用户定义分区的最大可能数量是根据所使用的NDB群集软件的版本、数据节点数和其他因素确定的
  8. 使用用户定义分区的InnoDB表的定义不能包含外键引用；不能对定义中包含外键引用的InnoDB表进行分区。
  9. InnoDB表定义不能包含对用户分区表的外键引用；具有用户定义分区的InnoDB表不能包含外键引用的列。
  10. ALTER TABLE…排序依据。对分区表运行的ALTER TABLE…ORDER BY列语句只会导致对每个分区内的行进行排序。
  11. 添加列…ALGORITHM=INSTANT。一旦对分区表执行ALTER TABLE…ADD COLUMN…ALGORITHM=INSTANT，就无法再与该表交换分区。
  12. 修改主键对REPLACE语句的影响。在某些情况下，可以修改表的主键。请注意，如果您的应用程序使用REPLACE语句，并且您这样做了，那么这些语句的结果可能会发生很大变化。
  13. FULLTEXT索引。分区表不支持FULLTEXT索引或搜索。
  14. 空间列。分区表中不能使用空间数据类型为POINT或GEOMETRY的列。
  15. 临时表。无法对临时表进行分区。
  16. 日志表。无法对日志表进行分区；此类表上的ALTER TABLE…PARTITION BY…语句失败，并返回错误。
  17. 分区键的数据类型。分区键必须是整数列或解析为整数的表达式。不能使用使用ENUM列的表达式。列或表达式值也可以为NULL；

当按[LINEAR]KEY进行分区时，可以使用除TEXT或BLOB之外的任何有效MySQL数据类型的列作为分区键，因为内部密钥哈希函数从这些类型中生成正确的数据类型。
按RANGE COLUMNS或LIST COLUMNS分区时，可以使用字符串、DATE和DATETIME列

季__末
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 表分区
zqinghai的专栏
02-13 545
数据统计：中间表太大优化的出路分区 按照时间分区，一个月一个区，关键是前期数据已经跑了2年多了。要重新创建带有分区的表。 总结一下里面的坑： 1、建表： 分区必须根表一根创建；如果表已经建好，那就麻烦一点。 （1）分区和表同时创建 CREATE TABLE IF NOT EXISTS `table_data_range` (   `dataid` int(11) NOT NULL,
mysql表分区
qq_42158153的博客
11-16 782
分区介绍： 一、什么是分区？ 　　所谓分区，就是将一个表分成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。 二、分区能干什么? 　　1.可以逻辑数据分割，分割数据能够有多个不同的物理文件路径。 　　2.可以存储更多的数据，突破系统单个文件最大限制。 　　3.提升性能，提高每个分区的读写速度，提高分区范围查询的速度。 　　4.可以通过删除相关分区来快速
MySQL分区知识及操作详解(完整版)
这个时代，作为程序员可能要学习小程序
09-11 4748
击上方关注 “终端研发部”设为“星标”，和你一起掌握更多数据库知识1.什么是分区？所谓的分区就是将一个表分解成多个区块进行操作和保存，从而降低每次操作的数据，提高性能，而对应用来说是透明的，从逻辑上看就只是一个表（这里跟分库分表的访问不一样），但是物理上的这个表可能是由多个物理分区组成，每个分区都是一个独立的对象，可以进行独立处理。2.分区目的？1.进行逻辑数据分割，分割数据能够有多个不同的物理文...
『Mysql』Mysql四种分区方式及组合分区落地实现
老陈聊架构
11-02 8361
📣读完这篇文章里你能收获到， Mysql分区的概念， Mysql分区四种分区方式的落地及案例， Mysql分区的管理
MySQL：概念、逻辑与物理结构设计详解
最新发布
Java领域优秀创作者
06-18 1016
MySQL是一个流行的开源关系型数据库管理系统（RDBMS），广泛应用于各种规模和类型的应用程序中。在设计和实现一个MySQL数据库时，理解其基本概念、逻辑结构设计和物理结构设计是至关重要的。本文将深入探讨MySQL的这三个方面，并详细解释每个部分的内容和重要性。
MySQL表分区
热门推荐
atwdy的博客
06-13 1万+
主要介绍mysql几种表分区的基本使用，包括range分区、list分区、hash分区、key分区、子分区和columns分区。
mysql数据库分区
weixin_49114503的博客
08-06 2479
所谓分区，就是将一个表分成多个区块进行操作和保存，从而降低每次操作的数据，提高性能。而对于应用来说则是透明的，从逻辑上看只有一张表，但在物理上这个表可能是由多个物理分区组成的，每个分区都是独立的对象，可以进行独立处理。未分区的表所有数据物理层面存储在同一个文件中，分区的表会按分区数量存储到不同的文件中，提高查询效率以下为分区和不分区物理层面的区别。分区是一种表的设计模式，正确的分区可以极大地提升数据库的查询效率，完成更高质量的SQL编程。但是如果错误地使用分区，那么分区可能带来毁灭性的的结果。
Mysql数据库的分区
Lyzxii
03-10 686
Mysql分区 Mysql从5.1版本开始支持分区的功能，分区是指根据一定的规则，数据库把一个表分解成多个更小的、更容易管理的部分，就访问数据库而言，逻辑上只有一个表或一个索引，但是实际上这个表可能由数个物理分区对象组成，每个分区都是一个独立的对象，可以作为表的一部分独立的处理。分区对应用来说完全透明的，不影响应用业务逻辑。 Mysql分区的好处： 和单个磁盘或者文件系统相比，分区可以存储更多数据 优化查询，在where子句中包含分区条件，可以只扫描必要的一个或者多个分区而非全表扫描，同时在涉及su
MySQL分区表
01-27
优点：提高并发量，减小锁的粒度缺点：代码维护成本高，相关sql都需要改动2、分区，所有的数据还在一个表中，但物理存储数据根据一定的规则存放在不同的文件中，文件也可以放到另外磁盘上优点：代码维护量小，基本...
MySQL 分区
04-02
MySQL分区是一种数据库优化技术，它将大型表分成更小、更易管理的部分，每个部分称为一个分区。这种技术有助于提高查询性能，特别是对于那些需要处理大量数据或执行复杂查询的应用程序。MySQL支持多种分区类型，包括...
详解MySQL分区表
12-14
【MySQL分区表详解】 分区表是数据库管理中一种高级的数据组织策略，主要目的是为了提高大表的查询效率和数据管理的便利性。在MySQL中，分区表是通过`PARTITION BY`子句来实现的，它允许我们将一个大的表根据特定的...
mysql分区文档
07-11
MySQL 分区是数据库管理系统中的一种优化策略，它将大型表的数据分布在不同的物理存储上，以提高查询性能和便于管理大量数据。分区的本质是将一张大表逻辑上分成多个部分，但用户在操作时仍然将其视为单个表。MySQL ...
MySQL分区管理工具
05-19
MySQL分区管理工具是一种优化数据库性能的技术，它将大型表分解为更小、更易管理的部分，以提高查询效率和数据管理的便利性。在MySQL中，分区主要基于表的数据，根据预定义的规则将数据分布到不同的物理部分。这样...
mysql分区功能详解,以及实例分析
09-09
以下是关于MySQL分区功能的详细解释和实例分析： 一、什么是数据库分区 数据库分区是将一张大表逻辑上视为一个整体，但在物理存储上将其分散到不同的磁盘或文件组中，以便更有效地管理和访问数据。在MySQL中，数据...
Oracle10个分区和Mysql分区区别详解
09-09
Oracle 和 MySQL 数据库在分区方面的区别主要体现在分区策略和适用场景上。分区是一种数据库优化技术，它将大表分成更小、更易管理的部分，从而提高查询性能和数据管理效率。下面详细介绍这两种数据库的分区类型及其...
MySQL分区知识及操作详解
小胡子
04-03 1715
MySQL分区知识及操作详解
MySQL分区表的正确使用方法，适用场景，建立分区的条件
qq_43842093的博客
08-20 7562
MySQL 数据库中的数据是以文件的形势存在磁盘上的，默认放在目录下面，我们可以通过命令来查看：我们进入到这个目录下，就可以看到我们定义的所有数据库了，一个数据库就是一个文件夹，一个库中，有其对应的表的信息，如下：.frm.myi.myd*.frm：这个是表定义，是描述表结构的文件。*.myd：这个是数据信息文件，是表的数据文件。*.myi：这个是索引信息文件。如果存储引擎是InnoDB.frm.ibd*.frm：表结构文件。*.ibd：表数据和索引的文件。
mysql 分区
10-16
MySQL 分区是一种将表分割成多个部分的技术，每个部分称为一个分区。分区可以根据指定的规则进行划分，例如按照某个列的值进行划分，或者按照时间范围进行划分等。使用分区可以提高查询效率，减少索引的大小，同时也可以方便地进行数据维护和管理。 MySQL 支持多种分区方式，包括 RANGE、LIST、HASH 和 KEY 四种方式。其中，RANGE 和 LIST 方式是基于列值的范围或列表进行分区，HASH 方式是基于哈希值进行分区，而 KEY 方式则是基于列值的哈希值进行分区。 在使用分区时，需要注意以下几点： 1. 分区表必须有主键或唯一键。 2. 分区表的所有索引都必须包含分区键。 3. 分区表的所有分区必须使用相同的存储引擎。 4. 分区表的所有分区必须在同一个文件系统上。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值