MySQL实战45讲学习笔记：第四十三讲 要不要使⽤分区表

我经常被问到这样⼀个问题：分区表有什么问题，为什么公司规范不让使⽤分区表呢？今天，我们就来聊聊分区表的使⽤⾏ 为，然后再⼀起回答这个问题。

分区表是什么？

为了说明分区表的组织形式，我先创建⼀个表t：

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

 图1 表t的磁盘⽂件

我在表t中初始化插⼊了两⾏记录，按照定义的分区规则，这两⾏记录分别落在p_2018和p_2019这两个分区上。

可以看到，这个表包含了⼀个.frm⽂件和4个.ibd⽂件，每个分区对应⼀个.ibd⽂件。也就是说：

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

对于Server层来说，这是1个表。

你可能会觉得这两句都是废话。其实不然，这两句话⾮常重要，可以帮我们理解分区表的执⾏逻辑。

分区表的引擎层⾏为

我先给你举个在分区表加间隙锁的例⼦，⽬的是说明对于InnoDB来说，这是4个表。

 图2 分区表间隙锁示例

这⾥顺便复习⼀下，我在第21篇⽂章和你介绍的间隙锁加锁规则。

我们初始化表t的时候，只插⼊了两⾏数据， ftime的值分别是，‘2017-4-1’ 和’2018-4-1’ 。session A的select语句对索引ftime 上这两个记录之间的间隙加了锁。如果是⼀个普通表的话，那么T1时刻，在表t的ftime索引上，间隙和加锁状态应该是图3这 样的。

 图3 普通表的加锁范围

也就是说，‘2017-4-1’ 和’2018-4-1’ 这两个记录之间的间隙是会被锁住的。那么，sesion B的两条插⼊语句应该都要进⼊锁等 待状态。

但是，从上⾯的实验效果可以看出，session B的第⼀个insert语句是可以执⾏成功的。这是因为，对于引擎来说，p_2018和 p_2019是两个不同的表，也就是说2017-4-1的下⼀个记录并不是2018-4-1，⽽是p_2018分区的supremum。所以T1时刻，在 表t的ftime索引上，间隙和加锁的状态其实是图4这样的：

 图4 分区表t的加锁范围

由于分区表的规则，session A的select语句其实只操作了分区p_2018，因此加锁范围就是图4中深绿⾊的部分。 所以，session B要写⼊⼀⾏ftime是2018-2-1的时候是可以成功的，⽽要写⼊2017-12-1这个记录，就要等session A的间隙 锁。

图5就是这时候的show engine innodb status的部分结果。

 图5 session B被锁住信息

看完InnoDB引擎的例⼦，我们再来⼀个MyISAM分区表的例⼦。

我⾸先⽤alter table t engine=myisam，把表t改成MyISAM表；然后，我再⽤下⾯这个例⼦说明，对于MyISAM引擎来说，这 是4个表。

 图6 ⽤MyISAM表锁验证

在session A⾥⾯，我⽤sleep(100)将这条语句的执⾏时间设置为100秒。由于MyISAM引擎只⽀持表锁，所以这条update语句 会锁住整个表t上的读。

但我们看到的结果是，session B的第⼀条查询语句是可以正常执⾏的，第⼆条语句才进⼊锁等待状态。

这正是因为MyISAM的表锁是在引擎层实现的，session A加的表锁，其实是锁在分区p_2018上。因此，只会堵住在这个分区 上执⾏的查询，落到其他分区的查询是不受影响的。

看到这⾥，你可能会说，分区表看来还不错嘛，为什么不让⽤呢？我们使⽤分区表的⼀个重要原因就是单表过⼤。那么，如果 不使⽤分区表的话，我们就是要使⽤⼿动分表的⽅式。

接下来，我们⼀起看看⼿动分表和分区表有什么区别。

⽐如，按照年份来划分，我们就分别创建普通表t_2017、t_2018、t_2019等等。⼿⼯分表的逻辑，也是找到需要更新的所有 分表，然后依次执⾏更新。在性能上，这和分区表并没有实质的差别。

分区表和⼿⼯分表，⼀个是由server层来决定使⽤哪个分区，⼀个是由应⽤层代码来决定使⽤哪个分表。因此，从引擎层看， 这两种⽅式也是没有差别的。

其实这两个⽅案的区别，主要是在server层上。从server层看，我们就不得不提到分区表⼀个被⼴为诟病的问题：打开表的⾏ 为。

分区策略

每当第⼀次访问⼀个分区表的时候，MySQL需要把所有的分区都访问⼀遍。

⼀个典型的报错情况是这样的：如果⼀个分区表 的分区很多，⽐如超过了1000个，⽽MySQL启动的时候，open_files_limit参数使⽤的是默认值1024，那么就会在访问这个表 的时候，由于需要打开所有的⽂件，导致打开表⽂件的个数超过了上限⽽报错。

下图就是我创建的⼀个包含了很多分区的表t_myisam，执⾏⼀条插⼊语句后报错的情况。

 图 7 insert 语句报错

可以看到，这条insert语句，明显只需要访问⼀个分区，但语句却⽆法执⾏。

这时，你⼀定从表名猜到了，这个表我⽤的是MyISAM引擎。是的，因为使⽤InnoDB引擎的话，并不会出现这个问题。

MyISAM分区表使⽤的分区策略，我们称为通⽤分区策略（generic partitioning），每次访问分区都由server层控制。通⽤分区 策略，是MySQL⼀开始⽀持分区表的时候就存在的代码，在⽂件管理、表管理的实现上很粗糙，因此有⽐较严重的性能问 题。

从MySQL 5.7.9开始，InnoDB引擎引⼊了本地分区策略（native partitioning）。这个策略是在InnoDB内部⾃⼰管理打开分区 的⾏为。

MySQL从5.7.17开始，将MyISAM分区表标记为即将弃⽤(deprecated)，意思是“从这个版本开始不建议这么使⽤，请使⽤替代 ⽅案。在将来的版本中会废弃这个功能”。

从MySQL 8.0版本开始，就不允许创建MyISAM分区表了，只允许创建已经实现了本地分区策略的引擎。⽬前来看，只有 InnoDB和NDB这两个引擎⽀持了本地分区策略。

接下来，我们再看⼀下分区表在server层的⾏为。

分区表的server层⾏为

如果从server层看的话，⼀个分区表就只是⼀个表。

这句话是什么意思呢？接下来，我就⽤下⾯这个例⼦来和你说明。如图8和图9所示，分别是这个例⼦的操作序列和执⾏结果 图。

 

图8 分区表的MDL锁

  图9 show processlist结果

可以看到，虽然session B只需要操作p_2107这个分区，但是由于session A持有整个表t的MDL锁，就导致了session B的alter 语句被堵住。

这也是DBA同学经常说的，分区表，在做DDL的时候，影响会更⼤。如果你使⽤的是普通分表，那么当你在truncate⼀个分表 的时候，肯定不会跟另外⼀个分表上的查询语句，出现MDL锁冲突。

到这⾥我们⼩结⼀下：

1. MySQL在第⼀次打开分区表的时候，需要访问所有的分区；

2. 在server层，认为这是同⼀张表，因此所有分区共⽤同⼀个MDL锁；

3. 在引擎层，认为这是不同的表，因此MDL锁之后的执⾏过程，会根据分区表规则，只访问必要的分区。

⽽关于“必要的分区”的判断，就是根据SQL语句中的where条件，结合分区规则来实现的。⽐如我们上⾯的例⼦中，where ftime=‘2018-4-1’，根据分区规则year函数算出来的值是2018，那么就会落在p_2019这个分区。

但是，如果这个where 条件改成 where ftime>=‘2018-4-1’，虽然查询结果相同，但是这时候根据where条件，就要访问p_2019 和p_others这两个分区。

如果查询语句的where条件中没有分区key，那就只能访问所有分区了。当然，这并不是分区表的问题。即使是使⽤业务分表 的⽅式，where条件中没有使⽤分表的key，也必须访问所有的分表。

我们已经理解了分区表的概念，那么什么场景下适合使⽤分区表呢？

分区表的应⽤场景

分区表的⼀个显⽽易⻅的优势是对业务透明，相对于⽤户分表来说，使⽤分区表的业务代码更简洁。还有，分区表可以很⽅便 的清理历史数据。

如果⼀项业务跑的时间⾜够⻓，往往就会有根据时间删除历史数据的需求。这时候，按照时间分区的分区表，就可以直接通过 alter table t drop partition …这个语法删掉分区，从⽽删掉过期的历史数据。

这个alter table t drop partition …操作是直接删除分区⽂件，效果跟drop普通表类似。与使⽤delete语句删除数据相⽐，优势 是速度快、对系统影响⼩。

⼩结

这篇⽂章，我主要和你介绍的是server层和引擎层对分区表的处理⽅式。我希望通过这些介绍，你能够对是否选择使⽤分区 表，有更清晰的想法。

需要注意的是，我是以范围分区（range）为例和你介绍的。实际上，MySQL还⽀持hash分区、list分区等分区⽅法。你可以 在需要⽤到的时候，再翻翻⼿册。

实际使⽤时，分区表跟⽤户分表⽐起来，有两个绕不开的问题：⼀个是第⼀次访问的时候需要访问所有分区，另⼀个是共⽤ MDL锁。

因此，如果要使⽤分区表，就不要创建太多的分区。我⻅过⼀个⽤户做了按天分区策略，然后预先创建了10年的分区。这种 情况下，访问分区表的性能⾃然是不好的。这⾥有两个问题需要注意：

1. 分区并不是越细越好。实际上，单表或者单分区的数据⼀千万⾏，只要没有特别⼤的索引，对于现在的硬件能⼒来说都已 经是⼩表了。

2. 分区也不要提前预留太多，在使⽤之前预先创建即可。⽐如，如果是按⽉分区，每年年底时再把下⼀年度的12个新分区创 建上即可。对于没有数据的历史分区，要及时的drop掉。

⾄于分区表的其他问题，⽐如查询需要跨多个分区取数据，查询性能就会⽐较慢，基本上就不是分区表本身的问题，⽽是数据 量的问题或者说是使⽤⽅式的问题了。

当然，如果你的团队已经维护了成熟的分库分表中间件，⽤业务分表，对业务开发同学没有额外的复杂性，对DBA也更直观， ⾃然是更好的。

最后，我给你留下⼀个思考题吧。

我们举例的表中没有⽤到⾃增主键，假设现在要创建⼀个⾃增字段id。MySQL要求分区表中的主键必须包含分区字段。如果要 在表t的基础上做修改，你会怎么定义这个表的主键呢？为什么这么定义呢？

你可以把你的结论和分析写在留⾔区，我会在下⼀篇⽂章的末尾和你讨论这个问题。感谢你的收听，也欢迎你把这篇⽂章分享 给更多的朋友⼀起阅读。