MySQL 表分区 Partition

MySQL InnoDB，MyISAM, 和 NDB 存储引擎都支持分区。

分区的过程就是将一个表和索引分成多个小的部分；逻辑上讲只有一个表和索引，但实际上由多个物理分区组成。

这点和分表不同，分表实际上是将一张表数据根据某些业务字段存储在多个表中。

1、查询是否支持分区功能

使用查询语句：show PLUGINS 查看：

partition    ACTIVE    STORAGE ENGINE        GPL

 

2、分区类型

1）RANGE 分区

根据给定的连续区间的列值进行分区。

2）LIST 分区

根据离散的值进行分区，创建分区时必须指定。

3）HASH 分区

根据用户自定义的表达式的返回值进行分区，返回值不能为负值。

创建分区时， 用户只需要进行哈希分区的列值或表达式，MySQL 自动选择数据的分区。

4）KEY 分区

使用数据库提供的哈希函数进行分区。

 

3、分区的使用

RANGE 分区主要用于日期列的分区，例如按年份分区，

create table stock_order(
id int ,
store_id int,
price decimal(10,2),
order_time datetime not null
)engine=INNODB
PARTITION BY RANGE(year(order_time)) 
(PARTITION p2018 VALUES less than (2018),
PARTITION p2019 VALUES less than (2019),
PARTITION p2020 VALUES less than (2020));

往表中写入几条数据：

insert into stock_order values (1, 101, 50, str_to_date('2018-06-20 10:25:30','%Y-%m-%d %T'));
insert into stock_order values (2, 102, 30.50, str_to_date('2018-05-20 15:25:30','%Y-%m-%d %T'));
insert into stock_order values (10001, 101, 60, str_to_date('2019-05-20 14:25:30','%Y-%m-%d %T'));
insert into stock_order values (10002, 103, 35, str_to_date('2019-06-20 11:55:20','%Y-%m-%d %T'));

添加数据落在不存在的分区的话，会报错:

insert into stock_order values (10002, 103, 35, str_to_date('2021-06-20 11:55:20','%Y-%m-%d %T'));

错误提示：

[SQL] insert into stock_order values (10002, 103, 35, str_to_date('2021-06-20 11:55:20','%Y-%m-%d %T'));
[Err] 1526 - Table has no partition for value 2021

添加分区：

alter table stock_order add PARTITION(PARTITION p2021 values less than (2021))

alter table stock_order add PARTITION(PARTITION p2022 values less than (2022))

4、查看分区数据

select table_name,partition_name, table_rows 
from information_schema.`PARTITIONS`
where table_schema=DATABASE() and table_name='stock_order';

返回：

stock_order    p2018    0
stock_order    p2019    2
stock_order    p2020    2
stock_order    p2021    0
stock_order    p2022    0
stock_order    p2022    0
 

5、分区好处

1）删除分区数据很方便：

alter table book_order drop partition p2018;

2）查询数据落在某个分区的话，速度会提升：

EXPLAIN PARTITIONS
select * from stock_order 
where order_time <='2019-12-31' and order_time>='2019-01-01'

1    SIMPLE    stock_order    p2020    ALL                    2    Using where
 

6、分区限制

1）如果表中存在主键或者唯一索引时，分区列必须是唯一索引的一个组成部分；如果表没有主键和唯一索引，则任意列都可以为分区列。

2）RANGE 类型分区，优化器只能对year(), to_days(), to_seconds(), unix_timestamp()这些函数进行优化选择