MySQL分区表简介

我们的业务只存近一段时间的数据，因此有大量表需要清理 历史 数据，目前使用的delete清理数据，存在以下问题。为避免同时支持大量delete，我们的清理任务只在低峰期串行执行，导致任务过多时需要排队，甚至失败的情况；数据清理使用delete语句，表数据量较大时，对数据库造成很大压力；即使我们删除了旧数据，已删除的数据仍占据存储空间，底层数据文件并没有立刻变小，以至于形成数据空洞。

查看MySQL官方文档时，发现了分区表，因此基于官方文档总结一下。

MySQL逻辑上为一个表，物理上存储在多个文件中，这是 MySQL 支持的功能（5.1 开始）, 8.0 版本只 InnoDB 和 NDB 支持分区表。

优点：

缺点：

根据分区表键值的范围把数据存储到表的不同分区中，适用于以时间或日期作为分区类型，方便数据清理。

小提示：

1.当插入数据分区不存在时会报错：Table has no partition for value xxx

2.Range类型分区字段必须是数值，时间类型可用函数转换为数值；

3.分区字段列值可以为null，所有为null的数据将存在最小的分区中；

按分区键取值的列表进行分区，每一行数据须找到对应的分区列表，否则数据插入失败

小提示：

根据指定分区表达式的整数值以及分区数进行数据划分(mod函数)

小提示：

按键分区类似于按哈希分区，只是哈希分区使用用户定义的表达式，用于键分区的哈希函数由 MySQL 服务器提供。NDB 集群为此使用 MD5() 对于使用其他存储引擎的表，服务器使用自己的内部哈希函数。

小提示：

子分区(subpartitioning)也称为复合分区(composite partitioning) ，是已分区表中每个分区的进一步划分

小提示：

小提示：

INNODB引擎只支持DATA DIRECTORY，不支持INDEX DIRECTORY, 另外 目录要空目录权限归属mysql 组mysql 用户，命令

mkdir /data

chown mysql:mysql /data

主键必须包含分区字段，PRIMARY KEY (id,store_id)

CREATE TABLE orders_list2 (

  id INT AUTO_INCREMENT,

  customer_surname VARCHAR(30),

  store_id INT,

  salesperson_id INT,

  order_date DATE,

  note VARCHAR(500),

  PRIMARY KEY (id,store_id),

  INDEX idx (id)

) ENGINE = INNODB

  PARTITION BY LIST(store_id) (

  PARTITION p1

  VALUES IN (1, 3, 4, 17)

  DATA DIRECTORY = '/data/p1',

  PARTITION p2

  VALUES IN (2, 12, 14)

  DATA DIRECTORY = '/data/p2',

  PARTITION p3

  VALUES IN (6, 8, 20)

  DATA DIRECTORY = '/data/p3',

  PARTITION p4

  VALUES IN (5, 7, 9, 11, 16)

  DATA DIRECTORY = '/data/p4',

  PARTITION p5

  VALUES IN (10, 13, 15, 18)

  DATA DIRECTORY = '/data/p5'

)

分区表扩充

ALTER TABLE orders_list2  ADD PARTITION (PARTITION p6 VALUES IN  (21,22,23)

DATA DIRECTORY = '/var/lib/mysql/monitor/orders/p6' 

子分区

CREATE TABLE tb_sub_dir (id INT, purchased DATE)

ENGINE='MYISAM'

    PARTITION BY RANGE( YEAR(purchased) )

    SUBPARTITION BY HASH( TO_DAYS(purchased) ) (

        PARTITION p0 VALUES LESS THAN (1990) 

        (

            SUBPARTITION s0

                DATA DIRECTORY = '/data/data_sub1'

                INDEX DIRECTORY = '/data/idx_sub1',

            SUBPARTITION s1

                DATA DIRECTORY = '/data/data_sub1'

                INDEX DIRECTORY = '/data/idx_sub1'

        ),

        PARTITION p1 VALUES LESS THAN (2000) 

        (

            SUBPARTITION s2

                DATA DIRECTORY = '/data/data_sub2'

                INDEX DIRECTORY = '/data/idx_sub2',

            SUBPARTITION s3

                DATA DIRECTORY = '/data/data_sub2'

                INDEX DIRECTORY = '/data/idx_sub2'

        ),

        PARTITION p2 VALUES LESS THAN MAXVALUE 

        (

            SUBPARTITION s4

                DATA DIRECTORY = '/data/data_sub3'

                INDEX DIRECTORY = '/data/idx_sub3',

            SUBPARTITION s5

                DATA DIRECTORY = '/data/data_sub3'

                INDEX DIRECTORY = '/data/idx_sub3'

        )

    )

日期字段区分

CREATE TABLE employees1 (

    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,

    store_id INT

)

PARTITION BY RANGE ( YEAR(separated) ) (

    PARTITION p0 VALUES LESS THAN (1991),

    PARTITION p1 VALUES LESS THAN (1996),

    PARTITION p2 VALUES LESS THAN (2001),

    PARTITION p3 VALUES LESS THAN MAXVALUE

)

---