MySQL分区表——要不要使用分区表？

扫地僧的故事 2020-08-23

4074

最近好几个研发同学来咨询分区表的问题，赶紧补一补相关知识，以免误人子弟。。

什么是分区表？

对于用户来说，分区表是一个独立的逻辑表，但其实底层是由多个物理子表组成。实现分区的代码实际上是对一组底层表的句柄对象的封装。对分区表的请求，都会通过句柄对象转换成对存储引擎的接口调用。

直观点，直接上图吧，创建了分区表 e，并分了4个区：

在data目录下看到，表e包含5个文件，1个frm文件和4个ibd文件，每个分区对应1个ibd文件。

所以，对MySQL的server层来说，这是1个表；但是对InnoDB引擎层来说，这是4个表。

从MySQL Server层来看，一个分区表就只是一个表。

所以，对业务来说是透明的，并且只需要维护一个表的数据结构即可。但是，在对一个分区进行DDL操作时，需要拿到整个表的MDL锁，会导致所有分区上的操作都会被阻塞，如果此时有大量并发请求，很可能会导致系统夯住。所以，如果和手工分表相比，在做DDL时，分区表的影响范围会更大。

从InnoDB 引擎层来看，一个分区对应一个表。

所以，DML操作加锁时，仅影响操作的分区，不会影响其他未访问的分区。所以，如果和手工分表相比，在做DML时，分区表的性能和手工分表没有实质差别。

可能有人会指出MySQL分区表的一个广为诟病的问题：打开表的行为。

在MySQL开始支持分区表时，分区表的访问控制操作都是放在MySQL Server层实现的，在文件管理和表管理等方面实现较为粗糙，这种分区策略称为通用分区策略。每当第一次访问一个分区表的时候，MySQL需要把所有分区都访问一遍，因此很可能会导致打开表文件的个数超过上限(open_files_limits)而报错。

但是，从MySQL 5.7.9 开始，InnoDB引入了本地分区策略，InnoDB内部自己管理打开分区的行为，所以就不存在上面这种问题了。

从MySQL 从 5.7.17 开始，将 MyISAM 分区表标记为即将弃用 (deprecated)；从MySQL 8.0 版本开始，就不允许创建 MyISAM 分区表了，只允许创建已经实现了本地分区策略的引擎。目前来看，只有 InnoDB 和 NDB 这两个引擎支持了本地分区策略。（现在应该很少使用5.6了吧？所以，分区表打开表的行为也不必太纠结了～）

分区表的应用场景

通过对分区表在server层和innodb引擎层不同行为的分析，应该对分区表的概念已经有了基本的了解。

总结下分区表的优缺点，让我们对要不要使用分区表，有一个更清楚的概念吧。

优点：

1. 对业务透明，使用分区表的业务代码更简洁

2. DML操作加锁只影响访问的分区，不影响未访问分区，能有效提高并发

3. 可通过truncate，drop特定分区或交换分区的方式清理数据，速度快，对系统影响小，数据更容易维护。

4. 通过只扫描特定分区的数据，提升查询性能

5. SUM()和COUNT()等聚合函数的查询，可在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果

缺点：

1. 对分区表进行DDL或其他运维操作，风险较高

2. 分区表存在未知风险，BUG较多

3. 不支持外键，不支持全文索引

任何功能，都有利有弊，不是用了分区，数据库运行就会变快的，千万不要滥用分区。分区表的使用可能会给某些sql语句性能带来提升，但是分区表主要的功能还是用于数据库高可用性的管理。

今天只写了一些基础概念，后面应该会写分区表的继续篇，关于MySQL分区表对NULL值的处理，MAXVALUE的隐患，交换分区的使用场景，以及其他一些注意点等等。

mysql

文章转载自扫地僧的故事，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL分区表——要不要使用分区表？

评论

相关阅读