SQL开发知识：弄懂MySQL 表分区

管理员 2023-09-06 08:03:48 互联网圈 0 ℃ 0 评论 17893字收藏

松哥之前写过文章跟大家介绍过用 MyCat 实现 MySQL 的分库分表，不知道有无小火伴研究过，MySQL 其实也自带了分区功能，我们可以创建一个带有分区的表，而且不需要借助任何外部工具，今天我们就一起来看看。

1. 甚么是表分区

小火伴们知道，MySQL 数据库中的数据是以文件的情势存在磁盘上的，默许放在 /var/lib/mysql/ 目录下面，我们可以通过 show variables like '%datadir%'; 命令来查看：

我们进入到这个目录下，就能够看到我们定义的所有数据库了，一个数据库就是一个文件夹，一个库中，有其对应的表的信息，以下：

在 MySQL 中，如果存储引擎是 MyISAM，那末在 data 目录下会看到 3 类文件：.frm、.myi、.myd，作用以下：

*.frm：这个是表定义，是描写表结构的文件。
*.myd：这个是数据信息文件，是表的数据文件。
*.myi：这个是索引信息文件。

如果存储引擎是 InnoDB, 那末在 data 目录下会看到两类文件：.frm、.ibd，作用分别以下：

*.frm：表结构文件。
*.ibd：表数据和索引的文件。

不管是哪一种存储引擎，只要一张表的数据量过大，就会致使 *.myd、*.myi 和 *.ibd 文件过大，数据的查找就会变的很慢。

为了解决这个问题，我们可以利用 MySQL 的分区功能，在物理上将这一张表对应的文件，分割成许多小块，如此，当我们查找一条数据时，就不用在某一个文件中进行全部遍历了，我们只需要知道这条数据位于哪个数据块，然后在那一个数据块上查找就好了；另外一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区我们就能够把数据分配到区别的磁盘里面去。

MySQL 从 5.1 开始添加了对分区的支持，分区的进程是将一个表或索引分解为多个更小、更可管理的部份。对开发者而言，分区后的表使用方式和不分区基本上或者如出一辙，只不过在物理存储上，本来该表只有一个数据文件，现在变成了多个，每一个分区都是独立的对象，可以独自处理，也能够作为一个更大对象的一部份进行处理。

需要注意的是，分区功能其实不是在存储引擎层完成的，常见的存储引擎如 InnoDB、MyISAM、NDB 等都支持分区。但其实不是所有的存储引擎都支持，如 CSV、FEDORATED、MERGE 等就不支持分区，因此在使用此分区功能前，应当对选择的存储引擎对分区的支持有所了解。

2. 分区的两种方式

区别于 MyCat 中既可以垂直切分又可以水平切分，MySQL 数据库支持的分区类型为水平分区，它不支持垂直分区。

2.1 水平切分

先来一张简单的示意图，大家感受一下甚么是水平切分：

假定我的 DB 中有 table⑴、table⑵ 和 table⑶ 三张表，水平切分就是拿着我 40 米大刀，对准黑色的线条，砍一剑或砍 N 剑！

砍完以后，将砍掉的部份放到另外一个数据库实例中，变成下面这样：

这样，本来放在一个 DB 中的 table 现在放在两个 DB 中了，视察以后我们发现：

两个 DB 中表的个数都是完全的，就是原来 DB 中有几张表，现在或者几张。每张表中的数据是不完全的，数据被拆分到了区别的 DB 中去了。

这就是数据库的水平切分，也能够理解为依照数据行进行切分，即依照表中某个字段的某种规则来将表数据分散到多个库当中，每一个表中包括一部份数据，即水平切分不改变表结构。

2.2 垂直切分

先来一张简单的示意图，大家感受一下垂直切分：

所谓的垂直切分就是拿着我 40 米大刀，对准了黑色的线条砍。砍完以后，将区别的表放到区别的数据库实例中去，变成下面这个模样：

这个时候我们发现以下几个特点：

每个数据库实例中的表的数量都是不完全的。每个数据库实例中表的数据是完全的。

这就是垂直切分。一般来讲，垂直切分我们可以依照业务来划分，区别业务的表放到区别的数据库实例中。

MySQL 数据库支持的分区类型为水平分区。

另外，MySQL 数据库的分区是局部份区索引，即一个分区中既寄存了数据又寄存了索引，目前，MySQL数据库还不支持全局分区（数据寄存在各个分区中，但是所有数据的索引放在一个对象中）。

3. 为何需要表分区

可让单表存储更多的数据。
分区表的数据更容易保护，可以通过清除全部分区批量删除大量数据，也能够增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作。
部份查询能够从查询条件肯定只落在少数分区上，查询速度会很快。
分区表的数据还可以散布在区别的物理装备上，从而高效利用多个硬件装备。
可使用分区表来避免某些特殊瓶颈，例如 InnoDB 单个索引的互斥访问、ext3 文件系统的 inode 锁竞争。
可以备份和恢复单个分区。

分区的限制和缺点：

一个表最多只能有 1024 个分区。
如果分区字段中有主键或唯一索引的列，那末所有主键列和唯一索引列都一定要包括进来。
分区表没法使用外键束缚。
NULL 值会使分区过滤无效。
所有分区一定要使用相同的存储引擎。

4. 分区实践

说了这么多，来个例子看一下。

首先我们先来查看一下当前的 MySQL 会不会支持分区。

在 MySQL5.6.1 之前可以通过命令 show variables like '%have_partitioning%' 来查看 MySQL 会不会支持分区。如果 have_partitioning 的值为 YES，则表示支持分区。

从 MySQL5.6.1 开始，have_partitioning 参数已被去掉了，而是用 SHOW PLUGINS 来代替。若有 partition 行且 STATUS 列的值为 ACTIVE，则表示支持分区，以下所示：

确认我们的 MySQL 支持分区后，我们就能够开始分区啦！

接下来我们来看几种区别的分区策略。

4.1 RANGE 分区

RANGE 分区比较简单，就是根据某一个字段的值进行分区。不过这个字段有一个要求，就是一定要是主键或是联合主键中的某个字段。

例如根据 user 表的 id 进行分区：

当 id 小于 100，数据插入 p0 分区；
当 id 大于等于 100 小于 200 的时候，插入 p1 分区；
如果 id 大于等于 200 则插入 p2 分区。

上面的规则触及到了 id 的所有范围了，如果没有第三条规则，那末插入一个 id 为 300 的记录时，就会报错。

建表 SQL 以下：

create table user(
id int primary key,
username varchar(255)
)engine=innodb
partition by range(id)(
partition p0 values less than(100),
partition p1 values less than(200),
partition p2 values less than maxvalue
);

表创建成功后，我们进入到 /var/lib/mysql/test08 文件夹中，来看刚刚创建的表文件：

可以看到，此时的数据文件分为好几个了。

在 information_schema.partitions 表中，我们可以查看分区的详细信息：

也能够自己写个 SQL 去查询：

select * from information_schema.partitions where table_schema=’test08′ and table_name=’user’\G

每行展现一个分区的信息，包括分区的方式、该区的范围、分区的字段、该区目前有几条记录等等。

RANGE 分区有一个比较典型的使用处景，就是依照日期对表进行分区，例犹如一年注册的用户放在一个分区中，以下：

create table user(
id int,
username varchar(255),
password varchar(255),
createDate date,
primary key (id,createDate)
)engine=innodb
partition by range(year(createDate))(
partition p2022 values less than(2023),
partition p2023 values less than(2024),
partition p2024 values less than(2025)
);

**注意，createDate 是联合主键的一员。**如果 createDate 不是主键，只是一个普通字段，那末创建时就会抛出以下毛病：

现在，如果我们要查询 2022 年注册的用户，系统就只会去搜索 p2022 这个分区，通过 explain 履行计划可以证实我们的想法：

如果想要删除 2022 年注册的用户，则只需要删除该分区便可：

alter table user drop partition p2022;

由上图可以看到，删除以后，数据就没了。

4.2 LIST 分区

LIST 分区和 RANGE 分区类似，区分在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择，而非连续的。举个例子大家看下就明白了：

假定我有一个用户表，用户有性别，现在想依照性别将用户分开存储，男性存储在一个分区中，女性存储在一个分区中，SQL 以下：

create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
primary key(id, gender)
)engine=innodb
partition by list(gender)(
partition man values in (1),
partition woman values in (0));

这个表将来就两个分区，分别存储男性和女性，gender 的取值为 1 或 0，gender 如果取其他值，履行就会出错，终究履行结果以下：

这样分区以后，将来查询男性或查询女性效力都会比较高，删除某一性别的用户时删除效力也高。

4.3 HASH 分区

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

使用 HASH 分区来分割一个表，要在 CREATE TABLE 语句上添加 PARTITION BY HASH (expr)，其中 expr 是一个字段或是一个返回整数的表达式；另外通过 PARTITIONS 属性指定分区的数量，如果没有指定，那末分区的数量默许为 1，另外，HASH 分区不能删除分区，所以不能使用 DROP PARTITION 操作进行分区删除操作。

create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
primary key(id, gender)
)engine=innodb partition by hash(id) partitions 4;

4.4 KEY 分区

KEY 分区和 HASH 分区类似，但是 KEY 分区支持除 text 和 BLOB 以外的所有数据类型的分区，而 HASH 分区只支持数字分区。

KEY 分区不允许使用用户自定义的表达式进行分区，KEY 分区使用系统提供的 HASH 函数进行分区。

当表中存在主键或唯一索引时，如果创建 KEY 分区时没有指定字段系统默许会首选主键列作为分区字段,如果不存在主键列会选择非空唯一索引列作为分区字段。

举个例子：

create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
primary key(id, gender)
)engine=innodb partition by key(id) partitions 4;

4.5 COLUMNS 分区

COLUMN 分区是 5.5 开始引入的分区功能，只有 RANGE COLUMN 和 LIST COLUMN 这两种分区；支持整形、日期、字符串；这类分区方式和 RANGE、LIST 的分区方式非常的类似。

COLUMNS Vs RANGE Vs LIST 分区：

针对日期字段的分区不需要再使用函数进行转换了。
COLUMN 分区支持多个字段作为分区键但是不支持表达式作为分区键。

COLUMNS 支持的类型

整形支持：tinyint、smallint、mediumint、int、bigint；不支持 decimal 和 float。
时间类型支持：date、datetime。
字符类型支持：char、varchar、binary、varbinary；不支持text、blob。

举个例子看下：

create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
createDate date,
primary key(id, createDate)
)engine=innodb PARTITION BY RANGE COLUMNS(createDate) (
PARTITION p0 VALUES LESS THAN (‘1990-01-01’),
PARTITION p1 VALUES LESS THAN (‘2000-01-01’),
PARTITION p2 VALUES LESS THAN (‘2010-01-01’),
PARTITION p3 VALUES LESS THAN (‘2020-01-01’),
PARTITION p4 VALUES LESS THAN MAXVALUE
);

这是 RANGE COLUMNS，分区值是连续的。

再来看 LIST COLUMNS 分区，这个就类似于枚举了：

create table user(
id int,
username varchar(255),
password varchar(255),
gender int,
createDate date,
primary key(id, createDate)
)engine=innodb PARTITION BY LIST COLUMNS(createDate) (
PARTITION p0 VALUES IN (‘1990-01-01’),
PARTITION p1 VALUES IN (‘2000-01-01’),
PARTITION p2 VALUES IN (‘2010-01-01’),
PARTITION p3 VALUES IN (‘2020-01-01’)
);

5. 常见分区命令

添加分区：

alter table user add partition (partition p3 values less than (4000)); — range 分区

alter table user add partition (partition p3 values in (40)); — lists分区

删除表分区（会删除数据）：

alter table user drop partition p30;

删除表的所有分区（不会丢失数据）：

alter table user remove partitioning;

重新定义 range 分区表（不会丢失数据）：

alter table user partition by range(salary)(
partition p1 values less than (2000),
partition p2 values less than (4000));

重新定义 hash 分区表（不会丢失数据）：

alter table user partition by hash(salary) partitions 7;

合并分区：把 2 个分区合并为一个，不会丢失数据：

alter table user reorganize partition p1,p2 into (partition p1 values less than (1000));

6. 小结

不知道小火伴们会不会还记得松哥 2019 年写的 MyCat 教程（公众号江南一点雨后台回复 2019 有文章索引），这些分区策略是不是是和 MyCat 中的策略非常类似呀？感兴趣的小火伴赶快去试一把吧～

到此这篇关于一文读懂MySQL 表分区的文章就介绍到这了,更多相关MySQL 表分区内容请搜索之前的文章或继续浏览下面的相关文章希望大家以后多多支持！

文章来源：丸子建站

文章标题：SQL开发知识：弄懂MySQL 表分区

https://www.wanzijz.com/view/77035.html