《MySQL数据库》MySQL分区表

前言

我们在开发中经常随着业务量的增大，时间等原因，某些表变得非常大几百万，几千万，甚至上亿的数据量。像这样的表查询效率是很低的，有索引也不能根本解决。

分区表

分区表说明

1. 可以存放更多数据。

2. 更加容易删除不需要的数据，直接删除分区即可。

3. 查询效率可以得到极大的提升。

4. 聚合函数（sum，count 等）可以并行，再最后合并。

5. 分区规则可以自定义。

分区表语法

create table tablename(
 id int,
 PRIMARY KEY(id))      -- 分区字段必须是主键
PARTITION BY RANGE/LIST/HASH(YEAR(order_day))     -- RANGE,LIST,HASH 三种分区方式
(
   partition pname values less than/in(1960),
   ...
   partition pname values less than MAXVALUE
);

分区类型

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。

create table t1(
         id         bigint not null auto_increment,
         mobile     bigint,
         password   varchar(64),
         username   varchar(64),
         sex        tinyint not null default 1,
         birthday   datetime,  
         amount     decimal(18,2), 
         ismaster   bool,
         istest     bit(1), 
         updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`,`birthday`))
partition by range (YEAR(birthday))(
          partition p1 values less than(1960),
          partition p2 values less than(1970),
          partition p3 values less than(1980),
          partition p4 values less than(1990),
          partition p5 values less than(2000),
          partition p6 values less than(2010),
          partition p7 values less than(2020),
          partition p8 values less than MAXVALUE);

创建分区要注意，分区字段要被主键包含，所以选择使用什么字段作为分区字段是一个严谨的事情。

-- 查询表的分区情况
SELECT
    PARTITION_NAME,
    TABLE_ROWS,
    PARTITION_EXPRESSION,
    PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA. PARTITIONS
WHERE TABLE_SCHEMA = 'stu'
AND TABLE_NAME = 't1';

增加分区：

ALTER TABLE t1 ADD PARTITION
( 
partition p8 VALUES LESS THAN (2030),
partition p9 values less than MAXVALUE);
);

删除分区：

ALTER TABLE t1 DROP PARTITION p8;

清空分区：

ALTER TABLE t1 TRUNCATE PARTITION p7;     -- 清空分区的数据

检查分区

ALTER TABLE t1 CHECK PARTITION p8;

修复分区

ALTER TABLE t1 REPAIR PARTITION p8;

重建分区（有整理碎片的功能）

ALTER TABLE t1 rebuild PARTITION p8;     -- 删除原分区数据，重建并导入数据　　

优化分区（整理分区碎片）

ALTER TABLE t1 OPTIMIZE PARTITION p8;

LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

create table t2(
         id         bigint not null auto_increment,
         mobile     bigint,
         password   varchar(64),
         username   varchar(64),
         sex        tinyint not null default 1,
         birthday   datetime,  
         amount     decimal(18,2), 
         ismaster   bool,
         istest     bit(1), 
         updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`,`birthday`))
partition by list (YEAR(birthday))(
          partition p1 values in(1960),
          partition p2 values in(1970),
          partition p3 values in(1980),
          partition p4 values in(1990),
          partition p5 values in(2000),
          partition p6 values in(2010),
          partition p7 values in(2020));

HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式，适合等值查询。

create table t3(
         id         bigint not null auto_increment,
         mobile     bigint,
         password   varchar(64),
         username   varchar(64),
         sex        tinyint not null default 1,
         birthday   datetime,  
         amount     decimal(18,2), 
         ismaster   bool,
         istest     bit(1), 
         updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`,`birthday`))
partition by hash (YEAR(birthday))
          PARTITIONS 6;

分成六个区，mysql自动根据hash值去mod 来分配分区。

KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

create table t4(
         id         bigint not null auto_increment,
         mobile     bigint,
         password   varchar(64),
         username   varchar(64),
         sex        tinyint not null default 1,
         birthday   datetime,  
         amount     decimal(18,2), 
         ismaster   bool,
         istest     bit(1), 
         updatetime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         PRIMARY KEY (`id`,`birthday`))
partition by KEY(birthday)
          PARTITIONS 6;

就是使用主键或者唯一键作为分区字段。

总结

分区的出现是为了解决日趋庞大的业务量，分区能大概提升10倍的查询效率，分区在工作中应用较多，到业务数据更大的时候，又出现了水平拆分，垂直拆分技术。