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的变体，而分区标志的基数为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+秒，因为它返回200 k行，而且索引可能不是给定查询的最佳索引。

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

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

Answer 2

查询的速度很慢，仅仅是因为磁盘速度慢。

在设计索引时，基数并不重要。

该查询的最佳索引是

INDEX(companyid, activityname, partitionflag)  -- in any order

它是“覆盖”，因为它包括SELECT中提到的所有列。这由EXPLAIN中的“使用索引”表示。

删除其他3列将使查询速度更快，因为它必须在磁盘上读取更少的内容。

如果您对查询进行了任何更改(添加列、从'=‘change =’>到‘>、添加ORDER BY等等)，那么索引可能不再是最优的。

此外，如果从上述索引中移除分区标志，则甚至不使用索引。--这是因为它不再是“覆盖”。

请记住，索引可以使用两种方式--“覆盖”，而不是查找数据的方法。当您没有“覆盖”索引时，优化器会在使用索引和在索引和数据之间跳跃之间进行选择，而不是简单地忽略索引和扫描表。