Mysql分区裁切问题

在看这个问题前，我默认您已经了解了mysql分区的相关概念，以及它的优点和缺点。本篇就不再赘述了，网上有非常多很优秀的总结，但是还是建议有能力的同学直接看官网内容。

一、简介

最近在做针对一张大表的查询优化，表结构相对简单，字段20多个，但有些varchar类型字段长度非常长。但该表每天都会被提交上百万的数据，时间长该表的查询与插入就变得非常非常的慢。调研后决定通过分区的形式优化该表。第一次分区后使用业务sql无法对分区查询进行正确的裁剪。

二、选择使用分区机制的理由：

（1）因为业务相对简单，并且都为单表查询，95%的查询都跟时间范围查询相关。

（2）数据需要定期清理数据，无需保留全部数据

（3）数据无更新操作，只有插入操作。

三、业务描述

该表类似于一个日志记录表，用于记录一次交易请求的全部登记信息。因为是前置服务，不用一直保留全部的记录，所以与需求人员沟通后决定只保留7天的数据。并且要提升插入数据的性能。

四、设计

方案一：

通过对表中数据 write_time（datetime类型） 字段进行取模操作，使其按照日期分别落入7天中的相应分区内。相应的操作语句如下。MOD(TO_DAYS(write_time), 7)为分区逻辑，使用RANGE 方式进行分区。

ALTER TABLE tableName PARTITION BY RANGE(MOD(TO_DAYS(write_time), 7)) 
(
    PARTITION p0 VALUES LESS THAN (1) ENGINE = InnoDB,
    PARTITION p1 VALUES LESS THAN (2) ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN (3) ENGINE = InnoDB,
    PARTITION p3 VALUES LESS THAN (4) ENGINE = InnoDB,
    PARTITION p4 VALUES LESS THAN (5) ENGINE = InnoDB,
    PARTITION p5 VALUES LESS THAN (6) ENGINE = InnoDB,
    PARTITION p6 VALUES LESS THAN (7) ENGINE = InnoDB,
)

分区后 mysql会根据分区逻辑，将数据划分到不同的分区中。通过如下语句可以知道各个分区中数据的数量。到目前位置数据已经进入了相应的分区。

select Partition_name, table_rows from information_schema.`PARTITIONS` where table_name='tableName ';</pre>

按照业务逻辑对数据进行范围查询与统计

（1）时间范围查询（<= >=未正确对分区进行裁剪）

EXPLAIN select * from tableName where write_time <= "2021-10-01 23:59:00" and write_time >= "2021-10-01 22:00:00";

发现并没有对分区进行正确的裁剪（Partition Pruning）而是扫描了所有的分区，产生的执行计划如下：

1655564818013.png

（2）指定日期查询（=可以正确裁剪分区）

EXPLAIN select * from tableName where write_time = "2021-10-01 23:59:00"</pre>

这次执行计划中展示的裁剪分区后 , 查询的分区符合我们的期，但是这不符合我们需求。

image.png

最终各种尝试后这个方案被否掉。

方案二：（可行方案）

使用 TO_DAYS 函数对 write_time（datetime类型）字段进行处理，使其落入相应的天分区内。同样可以对数据进行正确的划分，但带来的问题就是要动态的删除分区与新增分区，因为时间时是不停的向前走的， LESS THAN（）中的值是要递增的。所以可以通过建立定时任务，去定时执行对 pmax 分区（最后一个分区）的重新分区，并且删掉指定分区。例如在每天的 23：59：00 执行该定时任务。

ALTER TABLE tableName PARTITION BY RANGE ( TO_DAYS(write_time) ) (
    PARTITION p1 VALUES LESS THAN (738429) ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN (738430) ENGINE = InnoDB,
    PARTITION p3 VALUES LESS THAN (738431) ENGINE = InnoDB,
    PARTITION p4 VALUES LESS THAN (739532) ENGINE = InnoDB,
    PARTITION p5 VALUES LESS THAN (740533) ENGINE = InnoDB,
    PARTITION p6 VALUES LESS THAN (741534) ENGINE = InnoDB,
    PARTITION p7 VALUES LESS THAN (742535) ENGINE = InnoDB,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

按照业务逻辑对数据进行范围查询与统计

（1）时间范围查询（<= >= 未正确对分区进行裁剪）

EXPLAIN select * from tableName where write_time <= "2021-10-01 23:59:00" and write_time >= "2021-10-01 22:00:00";

通过执行计划查看，该处理方式复核预期：

image.png

并且对需求的所有业务场景尝试（指定日期时间的查询或统计，时间范围的查询或统计）后，均可符合预期。

四、一点思考

1. 为什么使用取模的方式，无法对分区进行正确的裁切？

（1）其实 mysql官网已经明确了对于 DATE， DATETIME 类型的段可以通过 YEAR() , TO_DAYS() 函数进行处理。官网上的写比较明确了。

This type of optimization can be applied whenever the partitioning expression consists of an equality or a range which can be reduced to a set of equalities, or when the partitioning expression represents an increasing or decreasing relationship. Pruning can also be applied for tables partitioned on a DATE or DATETIME column when the partitioning expression uses the YEAR() or TO_DAYS() function. Pruning can also be applied for such tables when the partitioning expression uses the TO_SECONDS() function.
官网链接：https://dev.mysql.com/doc/refman/8.0/en/partitioning-pruning.html

除了YEAR(), TO_DAYS() 这两个官网文章中的函数外，我还尝试了 MONTH()， DAYOFWEEK()，DAYOFYEAR()等其他 Mysql 的函数，均未能正确的裁切分区。

（2）其实未能正确裁切分区的结论也是应该的，因为我们设计d的取模逻辑是周而复始的是一个循环，数据通过取模后不会不停的落到 LESS THAN 1 到 7的分区中，我们给定一个时间范围是不容易快速正确计算出分区区间的。

举例解释说明一下我的猜想与思考。

例1：日期范围是【2022-06-13】至【2022-06-20】刚好为期 7天（一周），那么根据开始日期与结束日期计算产生的结果都是同一分区（假设P1），那我们查询数据时的范围不应该只是这一个分区，而是 P1 - P7所有的分区都要搜索。

例2：日期范围是【2022-06-13】至【2022-06-21】的与【2022-06-13】至【2022-06-14】根据开始日期与结束日期计算产生的分区都是 P1，P2分区。但是前者应该要扫描P1-P7的所有分区，而后者应该只扫描 P1和P2分区就好。

小结：

所以对于这种场景设计的分区，其实在对分区裁剪时包含很多的场景与可能。所以解析器在解析这个过程时，可能会不如直接扫描所有分区来的快。

2. 对于 YEAR() , TO_DAYS() 这种可以正确裁剪分区函数的思考。

这两种函数所产生的结果，都是线性的。比如说年 YEAR()，产生的结果都是一直递增的，并不会有两个不同年份的日期经过 YEAR()函数得出同一个结果。TO_DAYS()函数也同样是这样，结果永远是线性的，不会循环往复，或有重复。

参考文章：

MySQL :: MySQL 8.0 Reference Manual :: 24.4 Partition Pruning
MySQL RANGE分区 - pursuer.chen - 博客园
 为什么这个MySql查询(在分区表上)总是命中第一个分区？ - 码客