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

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

1.主要区别

1.1 分区表

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

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

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

CREATE TABLE 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 个物理表组成。

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

图片

MySQL 分区表的索引是按照底层的物理子表来定义的,并没有全局索引,这一点跟其他数据库有所不同。分区表也有一些限制:

  • 一个表最多有 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 分表和分区表本质上解决都是单表数据量过大导致的性能问题。分区表通过分区过滤的方式来减少查询数据量,分表则通过单表操作提升性能。

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

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

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

2026年最新版百万八股面试宝典《Java》《大模型》《大并发》《大数据》

本题库由资深讲师联合大厂面试官精心打造,专为高级IT岗位设计!!!

【220万字八股文面试宝典(领取)】

Logo

更多推荐