mysql分区表

最新推荐文章于 2025-06-18 00:12:58 发布

原创最新推荐文章于 2025-06-18 00:12:58 发布 · 465 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql分区表 #mysql分区表用途 #mysql分区表原理

mysql 专栏收录该内容

10 篇文章

订阅专栏

一、什么是分区表？

1.1 创建一个分区表并写入数据

CREATE TABLE `t` (
  `ftime` datetime NOT NULL,
  `c` int(11) DEFAULT NULL,
  KEY (`ftime`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(ftime))
(PARTITION p_2017 VALUES LESS THAN (2017) ENGINE = InnoDB,
 PARTITION p_2018 VALUES LESS THAN (2018) ENGINE = InnoDB,
 PARTITION p_2019 VALUES LESS THAN (2019) ENGINE = InnoDB,
PARTITION p_others VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

insert into t values('2017-4-1',1),('2018-4-1',1),('2019-4-1',1);

1.2 对于mysql 引擎层、server 层的不同表现
在这里插入图片描述
可以看到，这个表包含了一个.frm文件和4个.ibd文件，对应每个分区对应一个.ibd 文件。也就是说：

对于引擎层来说，这是4个表
对于Server层来说，这是1个表

1.3 分区表的引擎层行为
用InnoDB引擎，会是什么样的情况？
在这里插入图片描述
我们在初始化表t的时候，直插入两行数据，ftime 的值分别是：‘2017-4-1’ 、‘2018-4-1’。session A 的 select 语句对索引 ftime 上这两个记录之间的间隙加了锁。如果普通表，那么T1时刻，在表t的ftime 索引上，间隙和加锁状态应该如下图：
在这里插入图片描述
也就是说，‘2017-4-1’ 、‘2018-4-1’ 两个记录间的间隙是被锁住的。那么session的两条插入语句应该都要进入锁等待状态。

但，从上面实验效果看出， session B 的第一个insert 语句是可以执行成功的。这是因为，对于引擎来说 p_2018 和 p_2019 是两个不同的表，也就是说2017-4-1 的下一条记录并不是 2018-4-1，而是 p_2018 分区的 supermum。所以T1时刻，在表的ftime索引上，间隙和加锁的状态如下图：
在这里插入图片描述
由于分区表的规则， session A 的select 语句其实只操作了分区 p_2018,因此加锁范围就是图中深绿色的部分。
所以， session B 要写入一行ftime 是 2018-2-1 的时候是可以成功的，而要写入 2017-12-1 这个记录，就要等session A的间隙锁。
上面的例子都是基于 InnoDB 引擎的情况，
下面看一下 MyISAM 引擎会出现什么情况？

在这里插入图片描述
由于MyISAM引擎只支持表锁，所以这条update 语句会锁住整个表t上的读。
看上图结果是，session B的第一条查询语句是可以正常执行的，第二条语句才进入锁等待状态。
这是因为MyISAM的表锁是在引擎层实现的， session A 加的表锁，其实是锁在分区 p_2017 上，因此会堵住在这个分区上的查询，其它分区查询不受影响。

1.4 分区表的Server层行为
从server层看来，一个分区表就是一个表。
在server层，认为这是同一张表，因此所有分区公用MDL锁；
接下来，验证一下：
在这里插入图片描述

虽然session B只需操作p_2017这个分区，但由于session A持有整个表t的 MDL锁，就导致session B的 alter 语句被堵住。

二、分区表适用于什么场景？

单表数量太大的时候，可以选用分区表；
如果一项业务跑的时间足够长，往往需要删除历史数据，这时候，按时间分区的分区表，就可以直接通过 alter table drop
partition … 这语句删除分区，从而删除过期的历史数据；

三、为什么公司不推荐使用分区表？

每当第一次访问一个分区表的时候，MySql 需要将所有分区访问一遍；
如果一个分区表的分区很多，比如超过 1000个，而MySQL 启动的时候，open_files_limit 参数是默认值 1024 ，由于访问这表的时候，需要打开所有分区文件，导致这个数量超限而报错；
分区表，在做DDL的时候，影响会更大；