MySQL分区修剪

最近，我们学到了有关MySQL分区修剪的昂贵课程。 在那里，最好在这里共享它，这样其他人就不会重复我们的错误。

背景

在我们的系统中，有一个大的统计表，其中没有主键和索引。 该表已分区，但是缺少索引通常会导致在查询时进行全分区甚至全表扫描。 更糟的是，系统仍然继续写入该表，从而使其每天变慢。

为了解决性能问题，我们希望清除旧数据并添加新索引。 但是，这并不容易，因为表太大了。 因此，我们选择了长途方法，即仅将所需数据从该旧表迁移到具有适当模式的新表。

按哈希分区

如果我们只做我们最初打算做的事，那会很好。 但是，为了方便起见，我们更改了分区类型，这使新表变慢了。

在原始表中，分区基于timestamp列，该列将时间表示为从纪元开始的小时数。 例如，格林尼治标准时间2017年的第一秒是
从纪元1483228800秒。 要获得小时数，我们将数字除以3600得到1483228800 div 3600）= 412008。

由于按范围类型进行分区，我们需要一个维护脚本来创建明年的每月分区。 这种分区方式不是很理想，因为分区大小很大，甚至不均匀。 因此，我们将每月分区转换为每周分区，但懒得定义每个范围，并从一个分区切换到一个哈希分区。

这是如果按范围进行分区的哈希定义的简短形式

PARTITION BY RANGE (hour_epoch)
(PARTITION pOct2016 VALUES LESS THAN (419304),
 PARTITION pNov2017 VALUES LESS THAN (420024) ENGINE = InnoDB,
 PARTITION pDec2017 VALUES LESS THAN (420768) ENGINE = InnoDB,
 PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

如果我们按哈希分区，这就是分区定义的样子

partition by hash (hour_epoch div 168) partitions 157;

按哈希类型进行分区不仅可以缩短语法。 MySQL将尝试通过应用模函数来选择一个分区来平均分割记录。 但是，为了使一个分区的持续时间为一周，我们将hour_epoch数除以168，以有效地获得week_epoch。

使用新的表架构，我们对较小的分区，较短的描述和更多索引感到满意。

性能问题

由于数据量巨大，我们无法将数据完全迁移到新架构以验证性能。 我们仅使用2周的数据进行了初步性能测试，没有发现任何性能问题。 但是，在最终测试中，我们惊讶地观察到了混合结果。 大多数查询比预期的要快，但有些则要慢。

经过调查，我们意识到MySQL不仅扫描了几个分区，还进行了全表扫描以进行时间范围查询。 甚至更奇怪的是，这种行为仅在小于3周的日期范围内发生。 对此结果感到完全惊讶，我们克服了拖延的做法，仔细阅读MySQL文档并意识到原因。

“ 对于通过HASH或[LINEAR] KEY进行分区的表，如果WHERE子句对分区表达式中使用的列使用simple =关系，则也可以进行分区修剪 ”

正如文档中清楚解释的那样，分区修剪仅在按哈希类型进行分区的条件相同的情况下起作用。 但是，我们没有更早地检测到此问题，因为如果范围条件之间的不同值的数量足够短，查询优化器将自动将范围条件转换为相等条件。 不幸的是，在我们的早期测试中，两周的数据不足以使查询优化器向我们隐藏问题。

解

了解该问题后，我们努力寻找一种解决性能问题的方法。 有2种建议的解决方案

• 通过将一个大范围拆分为多个小范围（每个都适合一个分区）来欺骗查询优化器来完成工作。 这样，查询优化器将在每个单独的小范围内工作。
• 使用正确的分区类型再次重建架构。

第一种解决方案很快但很脏，而第二种解决方案又很费时。 最终，我们几乎决定使用第一个解决方案启动新表，直到找到实现第二个解决方案的快速方法。

我们翻阅了MySQL文档，并了解到重新划分基本上是一种复制和粘贴操作。 但是，MySQL还具有另一个命令，该命令使我们能够进行一些分区更改而无需花费太多精力。

ALTER TABLE pt
    EXCHANGE PARTITION p
    WITH TABLE nt;

在此命令中，MySQL允许我们在一个表和另一个表的分区之间交换分区。 即使这不是2个表的2个分区之间的直接交换，也不方便在临时表中再进行一次中间交换。

这就是我们的分区交换的样子

ALTER TABLE origin_table EXCHANGE PARTITION p1 WITH TABLE temp_table;

ALTER TABLE final_table EXCHANGE PARTITION p1 WITH TABLE temp_table;

即使这没有您想象的那样快，因为MySQL会逐行进行验证以确保临时表的每个记录都可清晰地存储在最终表分区中。 如果我们使用MySQL 5.7，则可以通过添加“
无验证”到第二个命令的末尾。

因为我们使用的Aurora仅支持MySQl 5.6，所以仍然需要花两天的时间来完全更新分区类型。 但是，如果不使用分区交换，那将是一个月。

幸运的是，这次我们设法从错误中恢复了过来。 我们希望您能从我们的错误中学习，并记得在使用任何奇特的方法之前，请仔细阅读文档。

翻译自: https://www.javacodegeeks.com/2017/04/mysql-partition-pruning.html