分区表的简介

概念

分区表就是将一个表或者索引分解为多个更小、更可管理的部分。就访问数据库而言，从逻辑上讲，只有一个表或者一个索引，但是在物理上这个表或者索引可能由多个物理分区组成。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分处理。MySQL数据库的分区是局部分区，一个分区中既能存放数据又能存放索引。而全局分区是指数据存放在各个分区中，但是所有数据的索引放在一个对象中，但是目前MySQL不支持全局分区。

目的与应用

分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起，另外，如果想一次批量删除整个分区的数据也会变得很方便。下面是分区表常见的应用场景：

  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  • 分区表的数据更容易维护，分区独立优化，检查，修复等
  • 分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备
  • 可以使用分区表来避免某些特殊的瓶颈，例如 InnoDB 的单个索引的互斥访问、ext3 文件系统的 inode 锁竞争等
  • 如果需要，还可以备份和恢复独立的分区，这在非常大的数据集的场景下效果非常好。

目前MySQL支持一下几种类型的分区：

  1. RANGE分区：基于一个给定连续区间边界，得到若干个连续区间范围，按照分区键的落点，把数据分配到不同的分区；
  2. LIST分区：类似RANGE分区，区别在于LIST分区是基于枚举出（离散）的值列表分区，RANGE是基于给定连续区间范围分区；
  3. HASH分区：基于用户自定义的表达式的返回值，对其根据分区数来取模，从而进行记录在分区间的分配的模式。这个用户自定义的表达式，就是MySQL希望用户填入的哈希函数；
  4. KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且使用MySQL 服务器提供的自身的哈希函数。

    注意：

    • 如果表存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分
    • 唯一索引可以是允许NULL值的，并且分区列只要是唯一索引的一个组成部分，不要求需要整个唯一索引列都是分区列

image.png

分区类型

RANGE分区

RANGE分区是实战最常用的一种分区类型，行数据基于属于一个给定的连续区间的列值被放入分区。但是要特别注意，如果插入的数据不在指定的分区范围内，就会抛出错误。RANGE分区主要用于日期列的分区，比如交易表啊，销售表，可以根据年月来存放数据。

注意： 如果分区走的唯一索引中date类型的数据，优化器只能对YEAR()，TO_DAYS()，TO_SECONDS()，UNIX_TIMESTAMP()这类函数进行优化选择。一般将日期转为时间戳分区比较方便。

例如：

  1. create table foo_range (
  2. id int not null auto_increment,
  3. created DATETIME,
  4. primary key (id, created)
  5. ) engine = innodb partition by range (TO_DAYS(created))(
  6. PARTITION foo_1 VALUES LESS THAN (TO_DAYS('2016-10-18')),
  7. PARTITION foo_2 VALUES LESS THAN (TO_DAYS('2017-01-01'))
  8. );
  10. insert into `foo_range` (`id`, `created`) values (1, '2016-10-17'),(2, '2016-10-20'),(3, '2016-1-25');

image.png
image.png

LIST分区

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

create table foo_list
(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)
);

HASH分区

基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中。在HASH分区中，MySQL 自动完成这些工作，所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。例如：

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

KEY分区

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

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

注意： 分区不一定能带来查询性能上的提升，只要合适情况下合理的分区才能带来性能上的提升。

OLTP和OLAP

数据库的应用一般分为两类，一类是OLTP（在线事务处理），比如：Blog、电子商务、网络游戏、转账金融系统等等；另一类就是OLAP（在线分析处理），比如：数据仓库、数据集市。
image.png
image.png

参考文章： https://www.cnblogs.com/shanyaohui/p/13496478.html