mysql分区概述

mysql分区实践

分区规则，partitions

mysql官方文档 猛击查看

分区主要有4种类型:

==这边需要注意的点是:==

1.无论使用哪一种分区，分区表的主键/唯一键都必须包含分区键，或者分区表上没有主键和唯一键，也就是不能使用主键或者唯一键之外的其它字段分区，
所以用做HASH 或者 KEY 分区的字段需要唯一或者是主键的一部分

2.一个表最多只能有1024个分区

3.SQL语句需要加上分区键查询

every unique key on the table must use every column in the table's partitioning expression

具体参考：mysql分区键区别

1. RANGE Partitioning 范围分区

连续区间范围，区间要求连续并且不能重叠，适合用日期做为范围查询，或者是有固定范围类型的字段

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,
    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
);

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 MAXVALUE
);

1. LIST Partitioning 列表分区

和range分区类似，区别只是在于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,
    store_id INT
)
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)
);

1. HASH Partitioning hash分区

hash分区有分2种类型，固定hash和线性hash

PARTITIONS 表示要分区的数量

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,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

线性hash

线性的好处是具有良好的扩展性，当后期需要扩容的时候，可以使数据均匀分布，区别是建表的时候分区多加LINEAR

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH(col1)
    PARTITIONS 6;

用于数字类型的拆分键

1. KEY Partitioning key分区

类似hash分区，只是key的分区可以用于字符串做为拆分键

CREATE TABLE tm1 (
    s1 CHAR(32) PRIMARY KEY
)
PARTITION BY KEY(s1)
PARTITIONS 10;

线性key

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

查询分区中的数据

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'table_name';

结尾: 分区之前需要预估下表的数据量，mysql单表数据最大不要超过2000W，如果后面超过2000W且表只是作为查询用的，可以考虑用分区，如果数据量远大于2000W，例如几个亿之类的，建议采用水平分表，分区还是有一定的局限性。

水平分表可以参考：mycat简易使用指南