mysql的分区表的使用

1.分区表的概念

mysql的分区表就是在物理层面把一张表分作多张子表存贮，在逻辑上是多张表对外部表现为同一张表。mysql的分区表对于用户来说是一个黑盒，用户可以像使用一般的表一样使用分区表，但是只有在对分区表足够了解的情况下，才能够正确的使用分区表，否则可能会带来一些性能上的问题。

2.分区表的适用场景

1.表的数据比较多，但一次只查一部分热点数据，比如按时间，或者按别的某种规则
2.分区表的索引是每个子表都会单独建一个索引，这点和oracle不同，oracle可以选择是否建立全局索引。根据键值进行分区可以减少innodb互斥量的竞争。
3.数据可以以较粗的粒度区分开，可以以分区的形式对数据进行管理。

3.分区表的限制

1. 分区数最多只能有1024个分区
2. 分区表达式必须是整数，或者返回整数的表达式。也可以使用列分区，例如 range,key，list都支持列分区。
3. 如果表中有主键，或者唯一索引的话，分区键必须包含在主键和唯一索引中，否则会报错。

4.分区表的使用

mysql有hash，key,range，list分区，分区还可以包含子分区，子分区用的很少，这里不做介绍。

1.在可以在创建表时，通过partition by定义分区表，也可以通过alter语句，将表变为分区表，获取新增，合并，删除，拆分分区。
create部分定义如下，分区定义语句

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
    
........
partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

4.1 hash分区

hash分区，分区表达式返回的一个整数的hash值和分区数取模，得到最后的分区数。可以通过添加 linear关键字，改变其hash算法，linear hash是线性hash，是一种动态hash扩容技术，可以将hash扩容的时间每次分散到一个桶中。

create table `tbl_hash_partition_test`(
	`id` int UNSIGNED  not null AUTO_INCREMENT,
	`tname` varchar(200), 
	 create_date date not null,
	PRIMARY key(id,tname)
)ENGINE = innodb DEFAULT charset = utf8mb4 
PARTITION BY HASH(id)
PARTITIONS 4;

create table `tbl_hash_partition_test`(
	`id` int UNSIGNED  not null AUTO_INCREMENT,
	`tname` varchar(200), 
	 create_date date not null,
	PRIMARY key(id,tname)
)ENGINE = innodb DEFAULT charset = utf8mb4 
PARTITION BY LINEAR HASH(id)
PARTITIONS 4;

4.2 key分区

KEY(column_list),分区键可以选择一个或多个列，效果和hash分区类似，其所用的hash函数保证数据分布的均匀性，最多可以包含16列，
也可以添加关键字linear，采用线性hash算法。

-- key分区
create table `tbl_key_partition_test`(
	`id` int UNSIGNED  not null AUTO_INCREMENT,
	`tname` varchar(200), 
	 create_date date not null,
	PRIMARY key(id,tname)
)ENGINE = innodb DEFAULT charset = utf8mb4 
PARTITION BY key(id,tname)
PARTITIONS 4;

4.3 range分区

范围分区应该是我们日常用的最多的分区方式了，
范围分区分区键可以使用表达式，表达式必须返回一个整数，
也可以使用 range column(col1,col2),
最多16列。

create table `tbl_date_partition_test`(
	`id` int UNSIGNED  not null AUTO_INCREMENT,
	`tname` varchar(200), 
	 create_date date not null,
	PRIMARY key(id,create_date)
)ENGINE = innodb DEFAULT charset = utf8mb4
PARTITION by RANGE (TO_DAYS(create_date))
(
	PARTITION p201910 VALUES less THAN (TO_DAYS('2019-11-01')),
	PARTITION p201911 VALUES less THAN (TO_DAYS('2019-12-01')),
	PARTITION p201912 VALUES less THAN (TO_DAYS('2020-01-01')),
	PARTITION p202001 VALUES less THAN (TO_DAYS('2020-02-01')),
	PARTITION p202002 VALUES less THAN (TO_DAYS('2020-03-01')),
	PARTITION p202003 VALUES less THAN (TO_DAYS('2020-04-01')),
	PARTITION p202004 VALUES less THAN (TO_DAYS('2020-05-01')),
	PARTITION p202005 VALUES less THAN (TO_DAYS('2020-06-01')),
	PARTITION p202006 VALUES less THAN (TO_DAYS('2020-07-01')),
	PARTITION p202007 VALUES less THAN (TO_DAYS('2020-08-01')),
	PARTITION p202008 VALUES less THAN (TO_DAYS('2020-09-01')),
	PARTITION p202009 VALUES less THAN (TO_DAYS('2020-10-01')),
	PARTITION pleft VALUES less THAN MAXVALUE
);

PARTITION p201910 VALUES less THAN子句后的表达式的值必须依次递增，比如 时间小于 2019-11-01会落在 p201910 分区，这里有点像switch 或者 if else的写法，只是这里的分区值是依次递增的。

CREATE TABLE tbl_range_column_partition_test(
    a INT NOT NULL,
    b INT NOT NULL
)
PARTITION BY RANGE COLUMNS(a,b) (
    PARTITION p0 VALUES LESS THAN (10,5),
    PARTITION p1 VALUES LESS THAN (20,10),
    PARTITION p2 VALUES LESS THAN (50,MAXVALUE),
    PARTITION p3 VALUES LESS THAN (65,MAXVALUE),
    PARTITION p4 VALUES LESS THAN (MAXVALUE,MAXVALUE)
);

4.4 List分区

list分区，适合对按照几组特定集合中的数据来进行分区,也可以采用表达式，表达式必须返回一个整数，可以按照列来分。List分区 VALUES IN 中的值可以是NULL,而range不行。

CREATE TABLE tbl_list_partition_test(
    id   INT,
    name VARCHAR(35)
)
PARTITION BY LIST (id) (
    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21,NULL),
    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);

列最多16列

CREATE TABLE tbl_list_column_partition_test(
    a INT NULL,
    b INT NULL
)
PARTITION BY LIST COLUMNS(a,b) (
    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),
    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),
    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),
    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )
);

5 分区表使用中需要注意的点

1.分区表的查询尽量都带上分区条件，这可以避免扫描所有分区，更新和删除操作也是如此。
如上的range范围分区。通过带上分区条件可以快速过滤不需要的分区，这里大家会奇怪为什么会有p201910分区呢？因为range 分区表达可能出现非法值，这种情况下数据都会插入到第一个分区中，所以第一个分区会被检索。

2.查询时分区键不能用于函数运算和算数运算，这会导致无法过滤分区，这个和索引类似。
3.所有分区必须使用相同的存贮引擎

总结

mysql分区并不是什么银弹，还需要在理解其原理上才能正确的使用。