讲讲MySQL的分区

讲讲MySQL的分区

导言

随着MySQL越来越流行，Mysql里面的保存的数据也越来越大。在日常的工作中，我们经常遇到一张表里面保存了上亿甚至过十亿的记录。这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情，由于所有的数据都一个普通的表里。所以只能是启用一个或多个带where条件的delete语句去删除（一般where条件是时间）。 这对数据库的造成了很大压力。即使我们把这些删除了，但底层的数据文件并没有变小。面对这类问题，最有效的方法就是在使用分区表。最常见的分区方法就是按照时间进行分区。 分区一个最大的优点就是可以非常高效的进行历史数据的清理。

同时，分区之后表数据文件分为多个，从一定程度上也提高了性能。

什么是分区

从逻辑上来讲，都是同一个表，但是从物理上来看，分区将数据按照分区策略存储到不同的表文件中，目前MySQL支持RANGE 分区、LIST 分区、HASH 分区、KEY 分区四种。

分区特性

• 数据分类

对于不同分区的数据，可以有不同的处理策略，以及不同的保留策略。

• 查询优化

实现分区之后，就像SUM、COUNT等聚合操作，实际可以实现多分区的并行计算，提高效率。

• 多磁盘吞吐

MySQL5.6.6之后支持指定分区文件的存储路径，这从某种程度上，增强了分区的意义，实现了跨磁盘分区，具备更大的吞吐量。

//like below
PARTITION BY LIST(store_id) (
  PARTITION p1
  VALUES IN (1, 3, 4, 17)
  INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
  PARTITION p2
  VALUES IN (2, 12, 14)
  INDEX DIRECTORY = '/var/orders/district2'
  DATA DIRECTORY = '/var/orders/district2',

以上都是分区的特性，对于企业级的生产环境DB，如果是大表，建议必须使用分区，从而无论从可维护性、可拓展性、性能上都有较好的提升。

---

如何管理分区

讲了分区的好处，那么怎么使用分区是一个问题：

创建表格

#by range 是一种常用的分区策略，可以按日期
create table employees (
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null,
    store_id int not null
) partition by range (store_id) (
    partition p0 values less than (6),
    partition p1 values less than (11),
    partition p2 values less than (16),
    partition p3 values less than (21)，
    partition p3 values less than maxvalue
);
#by list，通过枚举，实际应用的可能会少很多
create table employees (
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null,
    store_id int not null
) partition by list(store_id)
    partition pNorth values in (3,5,6,9,17),
    partition pEast values in (1,2,10,11,19,20),
    partition pWest values in (4,12,13,14,18),
    partition pCentral values in (7,8,15,16)
)；
#by hash；hash (expr) 注意expr必须是一个整型表达式
create table employees (
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null,
    store_id int not null
) partition by hash(store_id)
partitions 4；
# partitions num 指定分区的数量

#KEY分区，可以指定一列或者多列；而hash分区只能是一列
create table tk (
    col1 int not null,
    col2 char(5),
    col3 date
) partition by linear key (col1)
partitions 3;

注意，分区限制：

1. MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。
2. 对于原生的RANGE分区，LIST分区，HASH分区，分区对象返回的只能是整数值。
3. 分区字段不能为NULL，要不然怎么确定分区范围呢，所以尽量NOT NULL

指定磁盘

create table employees (
    id int not null,
    fname varchar(30),
    lname varchar(30),
    hired date not null default '1970-01-01',
    separated date not null default '9999-12-31',
    job_code int not null,
    store_id int not null
) partition by range (store_id) (
    partition p0 values less than (6),
     INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
    partition p1 values less than (11),
     INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
    partition p2 values less than (16),
     INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
    partition p3 values less than (21)，
     INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
    partition p3 values less than maxvalue
     INDEX DIRECTORY = '/var/orders/district1'
  DATA DIRECTORY = '/var/orders/district1',
);

分区查看

show table status like '%part_tab%';

分区基准测试

ignore~留给读者动手测试大量数据下的分区性能，相信能让你更加印象深刻。

---

分区劣势

如果说有什么劣势，那么我想就是：MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。

这一点大大限制了分区的使用范围，只能满足基于唯一键需求的表。

---

ignore~留给读者动手测试大量数据下的分区性能，相信能让你更加印象深刻。

---

分区劣势

如果说有什么劣势，那么我想就是：MySQL分区中如果存在主键或唯一键，则分区列必须包含在其中。

这一点大大限制了分区的使用范围，只能满足基于唯一键需求的表。

---