现在的位置: 首页 -> PHP学习 -> MySQL分区详解

MySQL分区详解

2022-07-30 09:57 ⁄ 评论数 6 ⁄ 被浏览 4739 views+

一、分区概述

1. 分区的概念

一句话概括：分区的过程是将一个表或索引分解为多个更小、更可管理的部分。

对应用来说，分区表是一个独立的逻辑表，但是底层是由多个物理子表组成。现实分区的代码实际上

是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转化成对存储引擎的接口调

用。所以分区对于SQL层来说是一个完全封装的黑盒子，对应用是透明的。

分区和分表相似，都是按照规则分解表。不同在于分表将大表分解为若干个独立的实体表，而分区是

将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张

表，但数据散列到多个位置了。应用读写的时候操作的还是大表名字，db自动去组织分区的数据。

2. MySQL支持分区的情况

（1）查看是否支持分区

SHOW PLUGINS;

（2）分区类型支持情况

维度	分区类型	概念	mysql支持与否
水平/垂直	水平分区	将同一个表中不同行的记录分配到不同的物理文件中	支持
水平/垂直	垂直分区	将同一个表中不同列的记录分配到不同的物理文件中	不支持
局部/全局	局部分区索引	一个分区既存放数据又存放索引	支持
局部/全局	全局分区索引	数据放在各个分区中，但所有数据的索引放在一个对象中	不支持

二、分区类型

MySQL支持分区类型如下表所示：

类型	描述
range分区	行数据基于属于一个给定连续区间的列值被放入分区
list分区	和range分区类型类似，只是list分区面向的是离散的值
hash分区	根据用户自定义的表达式的返回值来进行分区，返回值不能为负数
key分区	KEY分区使用MySQL数据库提供的函数进行分区
columns分区	分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型

1. range分区

（1）创建分区

# 创建一个range1表：
CREATE TABLE range1(
  id INT
) ENGINE=INNODB
PARTITION BY RANGE(id)(
  PARTITION p0 VALUES LESS THAN (5),
  PARTITION p1 VALUES LESS THAN (10)
);

创建分区表后，查看表在磁盘上的物理文件，启用分区之后，表不再由一个ibd文件组成了，而是由建立分区时的各个分区ibd文件组成，如下面的t#P#p0.ibd，t#P#p1.ibd：

（2）插入数据

INSERT INTO range1 (id) VALUES (1);
INSERT INTO range1 (id) VALUES (5);
INSERT INTO range1 (id) VALUES (6);
INSERT INTO range1 (id) VALUES (7);
INSERT INTO range1 (id) VALUES (10);
INSERT INTO range1 (id) VALUES (12);

由于我们定义了分区，因此对于插入的值应该严格遵守分区的定义，当插入一个不在分区中定义的值时，MySQL数据库会抛出一个异常。如下所示，我们向表t中插入12这个值。

（3）增加一个分区

# 增加一个分区
ALTER TABLE range1 ADD PARTITION (PARTITION p2 VALUES LESS THAN (20));
# 增加一个无穷大的分区
ALTER TABLE range1 ADD PARTITION (PARTITION p3 VALUES LESS THAN maxvalue);

（4）查看分区信息

# 查看分区信息
SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema=DATABASE() AND table_name='range1';

TABLE_ROWS列反映了每个分区中记录的数量。
PARTITION_METHOD表示分区的类型，这里显示的是 RANGE。

（5）删除一个分区

ALTER TABLE range1 DROP PARTITION p0;

注意：
① 删除分区相应的数据也会被删除；
② 删除分区后无法再重新添加回来。

（6）分析查询语句
如果查询的条件是分区的字段，只用扫描部分分区；否则会扫描全部分区。

EXPLAIN PARTITIONS SELECT * FROM range2 WHERE id>0 AND id <5;
EXPLAIN PARTITIONS SELECT * FROM range1 WHERE `name`='reson';

（7）指定分区的存储路径

CREATE TABLE range7(
  id INT
) ENGINE=INNODB
PARTITION BY RANGE(id)(
  PARTITION p0 VALUES LESS THAN (5)
  DATA DIRECTORY='E:/mysql/data1',
  PARTITION p1 VALUES LESS THAN (10)
  DATA DIRECTORY='E:/mysql/data2'
);

2. list分区

不同于range分区中定义的values less than语句，list分区使用values in，因为每个分区的值是离散的，因此只能定义值。

# 创建一个list1表：
CREATE TABLE list1(
  id INT,
  create_time INT
) ENGINE=INNODB
PARTITION BY LIST(id)(
  PARTITION p0 VALUES IN (1,3,5,7,9),
  PARTITION p1 VALUES IN (0,2,4,6,8)
);

3. hash分区

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。在 RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中， MySQL自动完成这些工作，用户所要做的只是基于将要进行哈希分区的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。

HASH分区的目的是将数据均匀地分布到预先定义的各个分区中，保证各分区的数据数量大致都是一样的。

# 创建一个hash1表：
CREATE TABLE hash1(
  id INT,
create_time DATETIME
) ENGINE=INNODB
PARTITION BY HASH(YEAR(create_time))
PARTITIONS 4;   # 不指定，默认为1

INSERT INTO hash1 (id,create_time) VALUES (3,'2017-12-11 23:00:00');
SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema=DATABASE() AND table_name='hash1'

4. key分区

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

数据库提供的函数进行分区。对于NDB Cluster引擎，MySQL数据库使用MD5函数来分区；对于其他存储引擎，MySQL数据库使用其内部的哈希函数。

# 创建一个key1表：
CREATE TABLE key1(
  id INT,
  create_time DATETIME
) ENGINE=INNODB
PARTITION BY KEY(create_time)
PARTITIONS 4;   # 不指定，默认为1


INSERT INTO key1(id,create_time) VALUES (2,'2017-12-12 21:00:00');

SELECT * FROM information_schema.`PARTITIONS` WHERE table_schema=DATABASE() AND table_name='key1'

5. columns分区

在前面介绍的RANGE、LIST、HASH和KEY这四种分区中，分区的条件是：数据必须是整型（interger），如果不是整型，那应该需要通过函数将其转化为整型，如YEAR()，TO_DAYS()，MONTH()等函数。

MySQL5.5版本开始支持COLUMNS分区，可视为RANGE分区和LIST分区的一种进化。COLUMNS分区可以直接使用非整型的数据进行分区，分区根据类型直接比较而得，不需要转化为整型。

COLUMNS分区支持以下的数据类型：

所有的整型类型，如INT、SMALLINT、TINYINT、BIGINT。FLOAT和DECIMAL则不予支持。

日期类型，如DATE和DATETIME。其余的日期类型不予支持。

字符串类型，如CHAR、VARCHAR、BINARY和VARBINARY。BLOB和TEXT类型不予支持。

对于日期类型的分区，我们不再需要YEAR()和TO_DAYS()函数了，而直接可以使用COLUMNS。

# 创建一个columns1表：
CREATE TABLE columns1(
  a INT,
  b DATETIME
)ENGINE=INNODB
PARTITION BY RANGE COLUMNS(B)(
  PARTITION p0 VALUES LESS THAN('2018-01-01'),
  PARTITION p1 VALUES LESS THAN('2019-01-01'),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

CREATE TABLE columns2(
  nick VARCHAR(25),
  sex TINYINT
)
PARTITION BY LIST COLUMNS(sex)(
  PARTITION p0 VALUES IN(0,1),
  PARTITION p1 VALUES IN(2)
);

# 对于RANGE COLUMNS分区，可以使用多个列进行分区，如：
CREATE TABLE columns2(
  age TINYINT,
  sex TINYINT
)
PARTITION BY RANGE COLUMNS(age, sex)(
  PARTITION p0 VALUES LESS THAN(50, 1),
  PARTITION p1 VALUES LESS THAN(50, 2),
  PARTITION p2 VALUES LESS THAN(120, 1),
  PARTITION p3 VALUES LESS THAN(120, 2)
);

温馨提示：

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分，因此下面创建分区的SQL语句会产生错误。

CREATE TABLE t1(
  col1 INT NOT NULL,
  col2 DATE NOT NULL,
  col3 INT NOT NULL,
  col4 INT NOT NULL,
  UNIQUE KEY(col1,col2)
)
PARTITION BY HASH(col3)
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) ) PARTITION ...
错误代码： 1503
A PRIMARY KEY must include all columns in the table's partitioning function

三、子分区

子分区（subpartitioning）是在分区的基础上再进行分区，有时也称这种分区为复合分区。MySQL数据库允许在RANGE和LIST的分区上再进行HASH或KEY的子分区。

子分区的建立需要注意以下几个问题：

每个子分区的数量必须相同。
要在一个分区表的任何分区上使用SUBPARTITION来明确定义任何子分区，就必须定义所有的子分区。
每个SUBPARTITION子句必须包括子分区的一个名字。
子分区的名字必须是唯一的。

CREATE TABLE my_time(
  a INT,
  b DATE
) ENGINE=innodb
PARTITION BY RANGE(YEAR(b))
SUBPARTITION BY HASH(TO_DAYS(b))(
  PARTITION p0 VALUES LESS THAN(1990)(
    SUBPARTITION s0,
    SUBPARTITION s1
  ),
  PARTITION p1 VALUES LESS THAN(2000)(
    SUBPARTITION s2,
    SUBPARTITION s3
  )
);
INSERT INTO my_time (a,b) VALUES (1, '2019-01-02');

四、分区总结

分区可能会给某些SQL语句性能带来提高，但是分区主要用于数据库高可用性的管理，对于大数据量，建议使用分区功能。

1. 注意事项

无论创建何种类型的分区，如果表中存在主键或唯一索引的列，则分区列必须是主键或唯一索引的一部分。

索引列可以是null值。

在没有主键和唯一索引的表中可以指定任意列为索引列。

表中只能最多有一个唯一索引，即primary key和unique key不能同时存在，primary key包含在unique key中时除外。

CREATE TABLE columns3(
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  relate_id int(10),
  age TINYINT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `relate_id` (`relate_id`)
)
PARTITION BY RANGE COLUMNS(relate_id)(
  PARTITION p0 VALUES LESS THAN(50),
  PARTITION p1 VALUES LESS THAN(120)
);

2. 优缺点

（1）优点

与单个磁盘或文件系统分区相比，可以存储更多的数据。
对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

（2）不足

因为需要根据分区列来确定数据所在分区，所以分区列必须作为查询条件，如果不使用分区列的查询条件，那么就无法进行分区过滤，Mysql最终会扫描所有分区。
所有分区都必须使用相同的存储引擎。
某些存储引擎不支持分区(MERGE、CSV、FEDERATED)。
分区表中无法使用外键。
分区的字段，必须是表上所有的唯一索引(或者主键索引)包含的字段的子集。
分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。
只有RANG和LIST分区能进行子分区，HASH和KEY分区不能进行子分区。

3. 适用场景

表非常大无法全部都放在内存中，或者只是在表的最后部分有热点数据，其他均是历史数据。
分区表的数据更容易维护。例如，想批量删除大量数据可以使用清除整个分区的方式。另外，还可以对一个独立分区进行优化、检查、修复等操作。
分区表的数据分布在不同的物理设备，从而高效的利用多个硬件设备
使用分区表避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问、ext3文件系统的inode锁竞争等。
备份和恢复独立的分区，这在非常大的数据集的场景下更高效。

参考资料：

《MySQL技术内幕 InnoDB存储引擎第2版》第4章表
《高性能MySQL （第三版）》第7章 MySQL高级特性
MySQL分区 https://zhuanlan.zhihu.com/p/37320212

您可能还会对这些文章感兴趣！

目前有 6 条评论 @我要评论

6楼蛋仔派对无限蛋币 : 2024-02-02 11:01:59 评论说： @回复
蛋仔派对无限蛋币http://www.wzryzs.com/
5楼ya : 2024-01-11 20:25:28 评论说： @回复
666
4楼ya : 2024-01-11 20:25:20 评论说： @回复
666
3楼你好123 : 2023-01-25 12:38:19 评论说： @回复
23阿士大夫是f
2楼巨化股份途径有同感 : 2023-01-02 12:01:50 评论说： @回复
gg修改器http://www.98sjj.com/ gg修改器免root版https://www.n012.cn/ gg修改器下载http://www.zavx.cn/
1楼巨化股份途径有同感 : 2023-01-02 11:57:56 评论说： @回复
gg修改器https://www.guiyangtax.com/ 葫芦侠http://www.booj.net/ 八门神器http://www.tusovok.net/ 八门神器http://www.wj9898.com/
test 回复巨化股份途径有同感: 123
2023-02-17 13:24:19 @回复

您的每一个评论都是对我的一份支持

MySQL分区详解

一、分区概述

1. 分区的概念

2. MySQL支持分区的情况

（1）查看是否支持分区

（2）分区类型支持情况

二、分区类型

1. range分区

（1）创建分区

（2）插入数据

（3）增加一个分区

（4）查看分区信息

（5）删除一个分区

（6）分析查询语句
如果查询的条件是分区的字段，只用扫描部分分区；否则会扫描全部分区。

（7）指定分区的存储路径

2. list分区

3. hash分区

4. key分区

5. columns分区

三、子分区

四、分区总结

1. 注意事项

2. 优缺点

（1）优点

（2）不足

3. 适用场景

您可能还会对这些文章感兴趣！

目前有 6 条评论 @我要评论

您的每一个评论都是对我的一份支持

天气小助手

最新文章

最受欢迎

热门排行

最新评论

返回首页

MySQL分区详解

一、分区概述

1. 分区的概念

2. MySQL支持分区的情况

（1）查看是否支持分区

（2）分区类型支持情况

二、分区类型

1. range分区

（1）创建分区

（2）插入数据

（3）增加一个分区

（4）查看分区信息

（5）删除一个分区

（6）分析查询语句如果查询的条件是分区的字段，只用扫描部分分区；否则会扫描全部分区。

（7）指定分区的存储路径

2. list分区

3. hash分区

4. key分区

5. columns分区

三、子分区

四、分区总结

1. 注意事项

2. 优缺点

（1）优点

（2）不足

3. 适用场景

您可能还会对这些文章感兴趣！

目前有 6 条评论 @我要评论

您的每一个评论都是对我的一份支持

天气小助手

最新文章

最受欢迎

热门排行

最新评论

返回首页

（6）分析查询语句
如果查询的条件是分区的字段，只用扫描部分分区；否则会扫描全部分区。