本文共 3253 字，大约阅读时间需要 10 分钟。

MySQL 分区简介

数据库分区是一种物理数据库设计技术，其主要目的是通过将数据分割成多个独立的物理存储单元来减少SQL操作的响应时间，同时为数据管理提供更高的效率。MySQL从5.1版本开始支持分区技术，分区完全透明于应用程序，应用无需做出任何修改即可享受分区带来的好处。

MySQL的分区主要有两种形式：水平分区和垂直分区。

MySQL 分区类型

根据分区规则的不同，MySQL分区可以分为以下几种类型：

MySQL 分区表常用操作示例

以下是以部门员工表为例的分区操作示例：

create table emp (    empno varchar(20) not null,    empname varchar(20),    deptno int,    birthdate date not null,    salary int) partition by range(year(birthdate)) (    partition p1 values less than (1980),    partition p2 values less than (1990),    partition p3 values less than maxvalue);

2. 创建LIST分区：

create table emp (    empno varchar(20) not null,    empname varchar(20),    deptno int,    birthdate date not null,    salary int) partition by list(deptno) (    partition p1 values in (10),    partition p2 values in (20),    partition p3 values in (30));

3. 创建HASH分区：

create table emp (    empno varchar(20) not null,    empname varchar(20),    deptno int,    birthdate date not null,    salary int) partition by hash(year(birthdate)) partitions 4;

4. 创建KEY分区：

create table emp (    empno varchar(20) not null,    empname varchar(20),    deptno int,    birthdate date not null,    salary int) partition by key(birthdate) partitions 4;

5. 创建复合分区：

create table emp (    empno varchar(20) not null,    empname varchar(20),    deptno int,    birthdate date not null,    salary int) partition by range(salary) subpartition by hash(year(birthdate)) subpartitions 3 (    partition p1 values less than (2000),    partition p2 values less than maxvalue);

分区表的管理操作

分区表的管理操作包括：

• 删除分区：

  alter table emp drop partition p1;alter table emp drop partition p1, p2;

• 增加分区：

  alter table emp add partition (partition p3 values less than (4000));alter table emp add partition (partition p3 values in (40));

• 分解分区：

  alter table emp reorganize partition p1 into (    partition p1 values less than (100),    partition p3 values less than (1000));

• 合并分区：

  alter table emp reorganize partition p1, p3 into (    partition p1 values less than (1000));

• 重新定义分区：

  alter table emp partition by range(salary) partitions 7;

• 删除所有分区：

  alter table emp removepartitioning;

• 重建分区：

  alter table emp rebuild partition p1, p2;

• 优化分区：

  alter table emp optimize partition p1, p2;

• 分析分区：

  alter table emp analyze partition p1, p2;

• 修补分区：

  alter table emp repairpartition p1, p2;

• 检查分区：

  alter table emp check partition p1, p2;

MySQL 分区表的局限性

6. 在5.1版本中，分区表对唯一约束有明确规定，每个唯一约束必须包含在分区键中。
7. 分区键所在列没有NOT NULL约束时，NULL值的处理方式取决于分区类型。
8. 在HASH和KEY分区中，分区键不能是BLOB或TEXT类型。
9. 只有RANGE和LIST分区支持子分区，HASH和KEY分区不支持子分区。
10. 临时表不能被分区。

获取MySQL分区表信息的几种方法

11. 使用show create table 表名查看创建分区表的语句。
12. 使用show table status查看表是否为分区表。
13. 使用information_schema.partitions查看分区信息。
14. 使用explain partitions语句分析执行情况。

分区表性能比较

通过存储过程插入800万条数据并进行查询测试可以看出，分区表的执行时间比普通表少70%。以下是测试结果：

• 分区表查询：

  select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

  返回结果为795181条数据，耗时2.62秒。

• 普通表查询：

  select count(*) from part_tab where c3 > date '1995-01-01' and c3 < date '1995-12-31';

  返回结果为795181条数据，耗时7.33秒。

通过explain语句可以看到，分区表的查询仅扫描了7980796行数据，而普通表则扫描了8000206行数据，进一步证明了分区表的查询效率优势。

参考资料

本文内容参考自MySQL官方文档和相关技术博客，具体引用来源已去除。

转载地址：http://ebbfk.baihongyu.com/