通过组优化范围查询

Question

在查询方面遇到麻烦。这是轮廓 -

表结构：

CREATE TABLE `world` (
  `placeRef` int NOT NULL,
  `forenameRef` int NOT NULL,
  `surnameRef` int NOT NULL,
  `incidence` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb3;

ALTER TABLE `world`
  ADD KEY `surnameRef_forenameRef` (`surnameRef`,`forenameRef`),
  ADD KEY `forenameRef_surnameRef` (`forenameRef`,`surnameRef`),
  ADD KEY `forenameRef` (`forenameRef`,`placeRef`);
COMMIT;

此表包含类似于600,000,000行的数据：

placeRef    forenameRef    surnameRef    incidence
1           1              2             100
2           1              3             600

这表示在某个地方具有给定的forename -sermame组合的人数。

我希望能够查询姓氏所附加的所有前令；然后对这些前命名的存在的位置进行另一次搜索，并以总和的计数。例如：获取所有具有“史密斯”姓氏的人；然后获取所有这些前命名的列表，该列表按位置和总和发病率分组。我可以通过以下查询来执行此操作：

SELECT placeRef, SUM( incidence )
FROM world
WHERE forenameRef IN
(
    SELECT DISTINCT forenameRef
    FROM world
    WHERE surnameRef = 214488
)
GROUP BY world.placeRef

但是，此查询大约需要一分钟的时间来执行，如果搜索姓氏很常见，则需要更多时间。

根问题是：使用组执行范围查询不会使用完整的索引。

有任何建议如何提高速度？

Answer 1

根据我的经验，如果您的查询具有范围条件（即除=或以外的任何类型的谓词是null），则该条件的列是您的最后一列可用于优化搜索，排序或分组的索引。

换句话说，假设您在列上有一个索引（a，b，c）。

以下使用所有三列。它能够通过C优化订单，因为由于所有与A和B的特定值匹配的行都将被绑定，然后这些匹配的行已经按c按顺序进行，因此订购是一个no-op。

SELECT * FROM mytable WHERE a = 1 AND b = 2 ORDER BY c;

但是下一个示例仅使用列a，b。需要执行Filesort的订单，因为该索引不是按c按顺序订购的。

SELECT * FROM mytable WHERE a = 1 AND b > 2 ORDER BY c;

组对小组也是如此。以下使用A，B进行行选择，并且还可以通过使用索引来优化组，因为c的每个不同值的每个值都可以保证将其分组在一起。因此，它可以计算c的每个值的行，并且当使用一个组完成时，可以保证，以后不会再有c的值。

SELECT c, COUNT(*) FROM mytable WHERE a = 1 AND b = 2 GROUP BY c;

但是范围条件会破坏这一点。 c的每个值的行未分组在一起。假定c的每个值的行可以分散在b的每个较高值之间。

SELECT c, COUNT(*) FROM mytable WHERE a = 1 AND b > 2 GROUP BY c;

在这种情况下，MySQL无法通过此查询中的组来优化组。它必须使用临时表来计数c的不同值的行。

MySQL 8.0.13引入了一种新型的优化器行为，跳过扫描范围访问方法。但据我所知，它仅适用于范围条件，而不是按或组成。

确实，如果您有范围条件，这会破坏订单和组的索引优化。

Answer 2

除非我不了解任务，否则似乎有效：

SELECT placeRef, SUM( incidence )
    FROM world
    WHERE surnameRef = 214488
    GROUP BY placeRef;

尝试一下。

它将从此顺序中的复合索引中受益：

INDEX(surnameRef, placeRef, incidence)

发病率被大量更新吗？如果是这样，请将其放下我的索引。

您应该考虑从Myisam转移到InnoDB。它可能需要合适的PK，

PRIMARY KEY(placeRef, surnameRef, forenameRef)

并且需要2倍-3x磁盘空间。