mysql - mysql 分区表上的索引

Question

我有一个包含两个分区的表。分区为 pactive = 1 和 pinactive = 0。我知道两个分区并没有带来太大的好处，但我已经用它来截断和加载一个分区，并在另一个分区中进行普通插入。

当我创建索引时，问题就来了。

查询是这样进行的

select partitionflag,companyid,activityname
from customformattributes
where companyid=47
      and activityname = 'Activity 1'
      and partitionflag=0

创建索引 -

create index idx_try on customformattributes(partitionflag,companyid,activityname,completiondate,attributename,isclosed)

从上述查询中将检索到大约 200000 条记录。但是查询连同提到的索引需要 30 多秒。这么长时间的原因是什么？此外，如果从提到的索引中删除分区标志，则甚至不会使用该索引。

并且是理解，

1. 即使有可用的分区，优化器也需要在索引定义中提到所需的分区，以便它只命中所需的分区 ---- 正确吗？

任何理解这一点的想法都会非常有帮助

Answer 1

您可以通过重新排序其中的列来优化您的索引。通常索引中的列按其基数排序(从最高开始到最低)。基数是给定列中数据的唯一性。因此，在您的情况下，我想 customformattributes 表中的 companyid 有很多变体，而 partitionflag 的基数为 2(如果此列的所有选项都是 1 和 0)。您的查询将首先过滤所有 partitionflag=0 的行，然后按公司 ID 等过滤。当您从索引中删除分区标志时，查询没有使用索引，因为优化器可能决定进行全表扫描而不是使用索引会更快(在大多数情况下优化器是正确的)

对于给定的查询:

select partitionflag,companyid,activityname
from customformattributes
where companyid=47
  and activityname = 'Activity 1'
  and partitionflag=0

以下索引可能会更好(但当然:

create index idx_try on customformattributes(companyid,activityname, completiondate,attributename, partitionflag, isclosed)

对于使用索引的查询，必须满足以下规则 - 索引中最左边的列应该出现在 where 子句中......并且根据您使用的 mysql 版本，可能需要额外的查询要求。例如，如果您使用的是旧版本的 mysql - 您可能需要按照它们在索引中列出的相同顺序对 where 子句中的列进行排序。在最新版本的 mysql 中，查询优化器负责以正确的顺序对 where 子句中的列进行排序。

您的 SELECT 查询花费了 30 多秒，因为它返回 20 万行，而且索引可能不是给定查询的最佳索引。

关于分区的第二个问题:常见的规则是你分区的列必须是表中所有 UNIQUE 键的一部分(根据定义，主键也是唯一键所以该列也应添加到 PK 中)。如果表结构和逻辑允许您将分区列添加到表中的所有 UNIQUE 索引，那么您可以添加它并对表进行分区。正确进行分区后，您可以利用分区修剪 - 这是 SELECT 查询仅在存储给定数据的分区中搜索数据的时候(否则它会在所有分区中查找)

您可以在此处阅读有关分区的更多信息: https://dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html