什么是表分区？有MySQL数据库吗？

MySQL自带分区功能。我们可以在不使用任何外部工具的情况下创建分区表。今天我们就来看看吧。

1。什么是表分区？

朋友们都知道，MySQL数据库中的数据是以文件的形式存储在磁盘上的。默认情况下，它位于文件夹/var/lib/mysql/ 下。我们可以通过命令show Variables like '%datadir%'来查看；命令：

当我们进入这个文件夹时，我们可以看到我们定义的所有数据库。数据库是一个文件夹，一个库有它的表信息，如下：

在MySQL中，如果存储引擎是MyISAM，你会在数据文件夹中看到3种类型的文件：.frm， .myi、.myd，功能如下：

1. *.frm：这是一个表定义，是一个描述表结构的文件。
2. *.myd：这是一个数据信息文件，表格数据文件。
3. *.myi：这是一个索引信息文件。

如果存储引擎为InnoDB，则data文件夹中会看到两种类型的文件：.frm、.ibd，功能如下如下：

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

无论是哪种存储引擎，只要一个表的数据量太大，就会导致*.myd、*.myi和 *.ibd 如果文件太大，查找数据会变得非常慢。

为了解决这个问题，我们可以利用MySQL的分区功能，将这张表对应的文件物理上分成很多小块。这样，当我们查找一条数据时，我们不必在特定的文件中查找它。当我们完成完整的遍历后，我们需要做的就是找出该数据位于哪个数据块中，然后在该数据块上进行搜索。另一方面，如果表中的数据量太大，一个磁盘可能无法容纳它。目前我们可以通过表分区的方式将数据分配到不同的磁盘上。

MySQL 从 5.1 开始添加了对分区的支持，即将表或索引划分为更小、更易于管理的部分的过程。对于开发者来说，分区表的使用与不分区的情况基本相同，只不过在物理存储方面，表原本只有一个数据文件，现在有多个数据文件，每个分区都是一个独立的对象。 。 ，可以单独处理或作为较大对象的一部分进行处理。

需要注意的是，分区功能并不是在存储引擎层完成的。常见的存储引擎如InnoDB、MyISAM、NDB等都支持分区。但并非所有存储引擎都支持这一点。例如CSV、FEDORATED、MERGE等不支持分区，所以在使用该分区功能之前，对所选的存储引擎应该有一定的了解它对分区的支持。

2。两种分区方式

与MyCat可以垂直和水平分区不同，MySQL数据库支持的分区类型是水平分区，不支持垂直分区。 ？ ，横砍是指我拿着我的40码剑，瞄准黑线，用一剑或N剑砍！

切割完成后，将切割的部分放到另一个数据库实例中，就会是这样的：

这样，原来放在一个数据库中的表，现在放到了两个数据库中。经过观察，我们发现：

1. 两个数据库的表数都是完整的，即原来的数据库有几张表，现在还有几张表。
2. 每个表的数据不完整，数据被拆分到不同的数据库中。

这就是数据库的水平切分，也可以理解为数据行切分，即表数据按照一定的规则表中的某个字段分布在多个库中、每个表包含一部分数据，即水平切分不改变表结构。

2.2垂直切割

先来个简单的示意图，让大家感受一下垂直分割：

所谓垂直分割就是拿我40米的剑，瞄准黑线。切割完成后，将不同的表放在不同的数据库实例中，它们会是这样的：

此时我们发现以下属性：

1. 在每个数据库实例中的数字表都是不完整的。
2. 各数据库实例中表数据完整。

这是垂直分割。一般来说，我们可以按照公司进行垂直细分，将不同公司的表放在不同的数据库实例中。

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

而且，MySQL数据库分区是本地分区索引，即数据和索引都存储在一个分区中。目前，MySQL数据库不支持全局分区（数据存储在每个分区中，但所有数据的索引存储在一个对象中）。

3。为什么需要表分区

1. 允许单个表存储更多数据。
2. 分区表数据更容易维护。您可以通过清除整个分区来批量删除大量数据，也可以添加新的分区来支持新插入的数据。此外，还可以对独立分区进行优化、检查、修复等操作。
3. 有些查询可以根据查询条件来确定，这样就只落在少数几个分区上，查询速度会很快。
4. 分区表数据还可以分布到不同的物理设备上，有效地利用多个硬件设备。
5. 分区表可以用来避免一些特殊的瓶颈，比如InnoDB中单个索引的互斥访问、ext3文件系统中inode锁竞争等。
6. 可以备份和恢复单个分区。

分区的局限性和缺点：

1. 一个表最多可以包含1024个分区。
2. 如果分区字段中有主键列或唯一索引列，则必须包含所有主键列和唯一索引列。
3. 分区表不能使用外键约束。
4. NULL值使分区过滤无效。
5. 所有分区必须使用相同的存储引擎。

4。分区练习

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

我们先检查一下当前的MySQL是否支持分区。

在MySQL 5.6.1之前，可以使用命令show Variables such as '%have_partitioning%'来检查MySQL是否支持分区。如果have_partitioning的值为YES，则表示支持分区。

从 MySQL 5.6.1 开始，参数 have_partitioning 已被删除并替换为 SHOW PLUGINS。如果有分区行，并且STATUS列的值为ACTIVE，则表示支持分区，如下图：

确认我们的MySQL支持分区后，我们就可以开始分区了！

接下来我们看看不同的分区策略。

4.1 RANGE分区

RANGE分区比较简单，就是根据给定字段的值进行分区。 不过这个字段有一个要求，就是必须是主键或者共享主键中的字段。 ？然后插入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  
);

建表成功后，我们运行/ lib/mysql/test08中的var/查看刚刚创建的表文件：

可以看到此时数据文件被分成了几个。

表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分区，通过解释实施方案可以印证我们的想法：

如果要删除2022年注册的用户，只需要删除分区即可：

alter table user drop partition p2022;

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

4.2 LIST 分区

LIST 分区与 RANGE 分区类似。不同之处在于，LIST 分区基于与离散值集中的值对应的列值进行选择，而不是连续的。比如你看一下就会明白：

假设我有一个users表。用户有性别。现在我想按性别分别保存用户。男性存放在一个分区，女性存放在一个分区。 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));

这个表以后会有两个分区，分别存储男性和女性。性别的取值为1或0。如果性别取其他值，则执行失败。最终的执行结果如下：

这样划分之后，以后搜索男性或者女性的效率会更高，去除某个性别的用户也会更高效。

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 分区支持除文本和 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 分区：

1. 日期字段分区不再需要使用函数进行转换。
2. COLUMN 分区支持多个字段作为分区键，但不支持表达式作为分区键。

COLUMNS 支持的类型

• 整数支持：tinyint、smallint、mediumint、int、bigint；不支持小数和浮点数。
• 时间类型支持：日期、日期时间。
• 支持字符类型：char、varchar、binary、varbinary；不支持文本和 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。常用分区命令

1. 添加分区：

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

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

1. 删除表分区（删除数据）：

alter table user drop partition p30;

1. 删除表中所有分区（不丢失数据）：

alter table user remove partitioning;

1. 重新定义范围分区表（无数据丢失）：

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

1. 重新定义哈希分区表（无数据丢失）：

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

1. 合并分区：将 2 个分区合并为一个而不丢失数据：

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