面试官：MySQL 分区表和分表有什么区别？分别适合什么场景？

大家好，我是君哥。

使用 MySQL 时，随着单表数据量越来越大，操作性能会受到影响，这时我们会考虑引入分区表或分表提高操作性能。

MySQL 分表和分区表解决的问题是一样的，都是解决单表数据量过大导致的性能问题，但它们的实现方式却有很大差别。今天来聊一聊这个话题。

1.主要区别

1.1 分区表

分区表，在用户看来逻辑上是一个单表，但底层是由多个物理子表组成，每个子表对应一个独立的物理文件。对分区表的请求，会被转换成对存储引擎的接口调用。存储引擎根据请求参数找到对应的物理表。

从 INNODB 看，分区表跟普通表没有区别，INNODB 引擎也无需知道一张表是普通表还是分区表。

下面我们创建一张订单分区表,按照月进行分区，每个月一张分区表：

CREATETABLE tb_order (
    order_id INT AUTO_INCREMENT,
    order_amount DECIMAL(19,4),
    order_date DATE,
    PRIMARY KEY (order_id, order_date))
PARTITIONBYRANGE(TO_DAYS(order_date))(
PARTITION p0 VALUESLESSTHAN (TO_DAYS('2023-02-01')),
PARTITION p1 VALUESLESSTHAN (TO_DAYS('2023-03-01')),
PARTITION p2 VALUESLESSTHAN (TO_DAYS('2023-04-01')),
PARTITION p3 VALUESLESSTHAN (TO_DAYS('2023-05-01')),
PARTITION pMAX VALUESLESSTHAN MAXVALUE
);

插入 4 条数据，可以在磁盘上看到有 4 个文件，也就是由 4 个物理表组成。

INSERTINTO tb_order(order_amount, order_date) VALUE(20.3, '2023-02-02');
INSERTINTO tb_order(order_amount, order_date) VALUE(30.3, '2023-03-02');
INSERTINTO tb_order(order_amount, order_date) VALUE(40.3, '2023-04-02');
INSERTINTO tb_order(order_amount, order_date) VALUE(50.3, '2023-05-02');

• 一个表最多有 1024 个分区；
• 分区中无法使用外键约束；
• 所有底层子表必须使用相同的存储引擎；
• 分区函数和表达式有一定限制。

MySQL 在操作分区表时，往往会先打开并锁住所有的底层子表，这可能会引起锁竞争。但一些情况下支持过滤，比如更新操作，如果 where 条件和分区表达式匹配，就可以将不包含更新记录的子表过滤掉。

1.2 分表

跟分区表不同的是，分表是手动创建多个结构完全相同的表（例如 tb_order_1, tb_order_2, tb_order_3, tb_order_4），在应用程序中决定数据应该写入哪个表，已经从哪个表读取数据。对 MySQL 来说，这是多个独立的表。

使用分表，需要在应用层加一层代理，或者使用分库分表中间件，来方便地确定底层操纵的表。

跟分区表相比，分表有如下特点：

• 如果没有代理层或中间件，操作分表必须指定操作的是哪一张表；
• 备份、恢复、DDL 等操作都需要单独地在每一张表上面执行，管理成本较高；
• 因为分表都是独立的表，操作时不会打开并锁住所有的底层子表，并发性能更高，锁冲突概率更低，可以更好地利用多核 CPU 和磁盘 IO；
• 跨分区查询效率低，因为需要在应用程序或中间件中实现数据聚会；
• 每个分表有自己独立的索引，索引维护成本较低。

2.使用场景

2.1 分区表

分区表的使用主要包括以下场景：

• 表数据量太大导致无法全部放入内存，或者只有表的最新数据是热点数据，其他数据都是历史数据；
• 查询条件集中，比如按照范围查询；
• 因为数据量巨大，全表扫描代价大，维护索引成本高；
• 想要整表删除大量数据，比如半年前的日志记录；
• 可以按照数据范围独立备份、恢复、归档；
• 需要减少锁竞争。

MySQL 使用最多的分区方式是范围分区，同时还支持键值分区、哈希分区、列表分区等，还有子分区，使用较少。使用分区表时，要注意下面几个方面：

• 要保证分区键值不为 NULL，否则过多的 NULL 值的数据可能会被分到同一个底层表；
• 分区键要使用索引列；
• 要限制分区的数量，否则查找分区成本会很高；
• 执行 SQL 时，如果不能做分区过滤，打开并锁住所有底层表的成本会很高；
• 维护分区的成本可能会很高，比如重组分区或其他 DDL 操作。

2.2 分表

分表一般用在下面场景：

• 单表数据量巨大，查询性能下降，需要通过分表甚至分库将数据分散到不同的库表中；
• 查询条件不集中；
• 对数据有更细粒度的管理要求；
• 频繁查询且对性能要求较高；
• 不考虑分表维护成本。

3.总结

MySQL 分表和分区表本质上解决都是单表数据量过大导致的性能问题。分区表通过分区过滤的方式来减少查询数据量，分表则通过单表操作提升性能。

对于架构上已经使用分库分表的情况，采用分表是最好的选择。

如果架构上没有采用分库分表，又不想有太高的维护成本，可以选择使用分区表。

对于分区表，一定要考虑分区键的选择，保证数据分布均匀，减少跨区查询的可能。对于分表，则要多考虑各个表之间的数据一致性问题。