关于java:MySQL-表分区涨知识了

9次阅读

共计 6412 个字符，预计需要花费 17 分钟才能阅读完成。

@[toc]
松哥之前写过文章跟大家介绍过用 MyCat 实现 MySQL 的分库分表，不晓得有没有小伙伴钻研过，MySQL 其实也自带了分区性能，咱们能够创立一个带有分区的表，而且不须要借助任何内部工具，明天咱们就一起来看看。

小伙伴们晓得，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 等就不反对分区，因而在应用此分区性能前，应该对抉择的存储引擎对分区的反对有所理解。

不同于 MyCat 中既能够垂直切分又能够程度切分，MySQL 数据库反对的分区类型为程度分区，它不反对垂直分区。

先来一张简略的示意图，大家感受一下什么是程度切分：

假如我的 DB 中有 table-1、table-2 以及 table-3 三张表，程度切分就是拿着我 40 米大刀，对准彩色的线条，砍一剑或者砍 N 剑！

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

这样，本来放在一个 DB 中的 table 当初放在两个 DB 中了，察看之后咱们发现：

两个 DB 中表的个数都是残缺的，就是原来 DB 中有几张表，当初还是几张。
每张表中的数据是不残缺的，数据被拆分到了不同的 DB 中去了。

这就是数据库的程度切分，也能够了解为依照数据行进行切分，即依照表中某个字段的 某种规定 来将表数据扩散到多个库之中，每个表中蕴含一部分数据，即程度切分不扭转表构造。

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

所谓的垂直切分就是拿着我 40 米大刀，对准了彩色的线条砍。砍完之后，将不同的表放到不同的数据库实例中去，变成上面这个样子：

这个时候咱们发现如下几个特点：

每一个数据库实例中的表的数量都是不残缺的。
每一个数据库实例中表的数据是残缺的。

这就是垂直切分。一般来说，垂直切分咱们能够依照业务来划分，不同业务的表放到不同的数据库实例中。

MySQL 数据库反对的分区类型为程度分区。

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

能够让单表存储更多的数据。
分区表的数据更容易保护，能够通过革除整个分区批量删除大量数据，也能够减少新的分区来反对新插入的数据。另外，还能够对一个独立分区进行优化、查看、修复等操作。
局部查问可能从查问条件确定只落在多数分区上，查问速度会很快。
分区表的数据还能够散布在不同的物理设施上，从而高效利用多个硬件设施。
能够应用分区表来防止某些非凡瓶颈，例如 InnoDB 单个索引的互斥拜访、ext3 文件系统的 inode 锁竞争。
能够备份和复原单个分区。

分区的限度和毛病：

一个表最多只能有 1024 个分区。
如果分区字段中有主键或者惟一索引的列，那么所有主键列和惟一索引列都必须蕴含进来。
分区表无奈应用外键束缚。
NULL 值会使分区过滤有效。
所有分区必须应用雷同的存储引擎。

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

首先咱们先来查看一下以后的 MySQL 是否反对分区。

在 MySQL5.6.1 之前能够通过命令 show variables like '%have_partitioning%' 来查看 MySQL 是否反对分区。如果 have_partitioning 的值为 YES，则示意反对分区。

从 MySQL5.6.1 开始，have_partitioning 参数曾经被去掉了，而是用 SHOW PLUGINS 来代替。若有 partition 行且 STATUS 列的值为 ACTIVE，则示意反对分区，如下所示：

确认咱们的 MySQL 反对分区后，咱们就能够开始分区啦！

接下来咱们来看几种不同的分区策略。

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;

由上图能够看到，删除之后，数据就没了。

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 如果取其余值，执行就会出错，最终执行后果如下：

这样分区之后，未来查问男性或者查问女性效率都会比拟高，删除某一性别的用户时删除效率也高。

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;

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;

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')
);

增加分区：

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));

不晓得小伙伴们是否还记得松哥 2019 年写的 MyCat 教程（公众号江南一点雨后盾回复 2019 有文章索引），这些分区策略是不是和 MyCat 中的策略十分类似呀？感兴趣的小伙伴连忙去试一把吧～

参考资料：

https://www.cnblogs.com/dw330…

正文完

java

发表至： java

2022-03-27

0

关于java:Java算法系列KMP算法三

关于java:Elasticsearch官方已支持SQL查询用起来贼方便

关于java:CS9053-Java

08.Java反射问题

关于html:小程序完成后的自我总结一

关于java:MySQL-表分区涨知识了

1. 什么是表分区

2. 分区的两种形式

2.1 程度切分

2.2 垂直切分

3. 为什么须要表分区

4. 分区实际

4.1 RANGE 分区

4.2 LIST 分区

4.3 HASH 分区

4.4 KEY 分区

4.5 COLUMNS 分区

5. 常见分区命令

6. 小结

站内搜索