Mysql 分区表

原创已于 2023-03-19 14:42:31 修改 · 419 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

于 2023-03-19 13:41:54 首次发布

分区表是一种优化大数据管理的技术，通过逻辑上分隔数据来提高查询效率和管理便捷性。常见使用场景包括处理大表、优化索引和数据备份。分区类型包括Range、List、Hash和多列分区，而子分区能进一步细化管理。然而，分区表也可能带来额外的开销，如NULL值处理、过多分区影响筛选效率及高昂的维护成本。

1. 什么是分区表

首先分区表是一个独立的逻辑表，但是底层由很多物理子表组成，从底层的文件系统来看，每个分区表都有一个使用#分割命名的表文件。

例如下面的逻辑表sales;

create table sales(order_date datetime not null)
engine=InnoDB partition by range(year(order_date)) (
	partition p_2009 values less than (2010),
    partition p_2010 values less than (2011),
    partition p_catcall values less than maxvalue
);

insert into sales values
('2009/01/01 00:00'),
('2010/01/01 00:00'),
('2011/01/01 00:00'),
('2012/01/01 00:00')
;

查看表存储位置和文件系统结构：

show global variables like "%datadir%";

查看分区信息

select * from sales partition (p_2010);

用户在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就不需要扫描所有分区了，只需要查询包含需要数据的分区即可。

explain select * from sales where order_date ='2010/01/01 00:00';

2. 分区表常见使用场景

表非常大以至于无法全部放在内存中，或者只有最后部分有热点数据，其他均为历史数据。
Innodb单个索引的互斥访问十分严重的时候，可以利用分区表避免这个性能瓶颈
数据量十分庞大的情况下，数据的备份和恢复（因为单个分区表可以单独备份和恢复，这样对于大量数据只需要备份当天或者当月的数据即可，数据恢复也可以只恢复某个分区的数据）

3. 在分区表上执行增删改查会发生什么？

SELECT查询

分区层先打开并锁住所有底层表，优化器会先判断是否可以过滤部分分区，然后再调用存储引擎接口访问各个分区的数据。

INSERT/UPDATE/DELETE

也是在分区曾先打开并锁住所有底层表，确定要处理的数据在哪个分区，然后做相应的处理操作

上述操作都会在分区层先打开并锁住底层表，但这并不是说分区表在处理过程中是锁住全表的，如果存储引擎能够自己实现行级表，则会在分区层释放掉对应表锁。

4. 分区表的类型

Range分区
LIST分区

列值匹配一个离散集合来进行分区。示例如下：

#cid代表商品类别ID
create table test(id int, cid int) partition by list(cid)
(
    partition p_01 values in (1,3),
    partition p_02 values in (2,4),
);

HASH分区

利用数据库自带的hash算法自动帮忙分区。

create table test(id int, cid int) partition by hash(cid) partitions 4;

多列分区

columns关键字允许多个字符串和日期作为分区定义列，同时还允许使用多个列定义一个分区


create table test(a int, b int, c int) partition by range columns(a,b)
(
    partition p_01 values less than (10,10),
    partition p_02 values less than (10,20),
);

4. 子分区

比较重要的分区还有子分区，子分区可以将某一个分区再进行细分，达到进一步有意义的减少分区数据的目的，所以其特别适用大表。

create table sales(order_date datetime not null)
engine=InnoDB partition by range(year(order_date))
subpartition by hash(month(order_date))
subpartitions 12
 (
	partition p_2009 values less than (2010),
    partition p_2010 values less than (2011),
    partition p_catcall values less than maxvalue
);

insert into sales values
('2009/01/01 00:00'),
('2010/01/01 00:00'),
('2011/01/01 00:00'),
('2012/01/01 00:00')
;