mysql - 优化长 MySql 查询的索引

Question

抱歉，我不能在标题中更具体。

所以我得到了这个查询:

CREATE TABLE RecordPoints AS (
SELECT competitionId, personId, personCountryId, eventId, year, date,
if(regionalAverageRecord = 'WR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.average > 0), 0) wrAveragePoints,
if(regionalSingleRecord = 'WR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date), 0) wrSinglePoints,
if(NOT regionalAverageRecord in('WR', 'NR'),
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND average > 0 AND rd.personCountryId in
(SELECT Countries.id FROM Countries JOIN Continents on Countries.continentId=Continents.id where recordName = rd2.regionalAverageRecord)), 0) crAveragePoints,
if(NOT regionalAverageRecord in('WR', 'NR'),
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.personCountryId in
(SELECT Countries.id FROM Countries JOIN Continents on Countries.continentId=Continents.id where recordName = rd2.regionalSingleRecord)), 0) crSinglePoints,
if(regionalAverageRecord = 'NR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.personCountryId=rd2.personCountryId AND rd.average > 0 ), 0) nrAveragePoints,
if(regionalSingleRecord = 'NR',
(SELECT COUNT(DISTINCT personId) FROM ResultDates rd
WHERE rd.eventId=rd2.eventId AND rd.date <= rd2.date AND rd.personCountryId=rd2.personCountryId), 0) nrSinglePoints
FROM ResultDates rd2 WHERE (NOT regionalAverageRecord='' OR NOT regionalSingleRecord = ''));

并且花了 9 个小时才完成。为了分解它，我正在创建一个表，其中 6 列是整个子查询，以计算在我正在查看的第一件事发生之前，personId 在同一个表中出现的次数基于日期和一些其他列。我认为使用 CREATE INDEX date ON ResultDates (date) 创建日期索引加快了一点速度，但它仍然需要大量时间。

ResultDates 中的行看起来像

+------------+-----------------+---------------+---------+---------+-----+---------+----------------------+-----------------------+-------+-----+------+------------+
| personId   | personCountryId | competitionId | eventId | roundId | pos | average | regionalSingleRecord | regionalAverageRecord | month | day | year | date       |
+------------+-----------------+---------------+---------+---------+-----+---------+----------------------+-----------------------+-------+-----+------+------------+
| 1982THAI01 | USA             | WC1982        | 333     | f       |   1 |       0 | WR                   |                       |     6 |   5 | 1982 | 1982-06-05 |
+------------+-----------------+---------------+---------+---------+-----+---------+----------------------+-----------------------+-------+-----+------+------------+

其中 regionalSingleRecord 和 regionalAverageRecord 可以是这些“RecordNames”中的任何一个:WR、NR，大多数时候什么都不是，或者 AfR、AsR、ER、NAR、OcR 或 SAR，然后我使用它们来查找 countryId这些记录名称连接到哪个大陆。

我已经创建了索引以将这些 recordNames 连接到大洲，并将大洲 ID 连接到 countryId，但不确定这会提高多少速度。

在它上面运行 EXPLAIN 返回给我:

+----+--------------------+------------+------------+------+-------------------+--------------+---------+----------------------------------+--------+----------+---------------------------------------------------------------+
| id | select_type        | table      | partitions | type | possible_keys     | key          | key_len | ref                              | rows   | filtered | Extra                                                         |
+----+--------------------+------------+------------+------+-------------------+--------------+---------+----------------------------------+--------+----------+---------------------------------------------------------------+
|  1 | PRIMARY            | rd2        | NULL       | ref  | idx_personId      | idx_personId | 32      | const                            |    567 |    99.00 | Using where                                                   |
|  9 | DEPENDENT SUBQUERY | rd         | NULL       | ALL  | date,idx_personId | NULL         | NULL    | NULL                             | 992294 |     0.33 | Range checked for each record (index map: 0x3)                |
|  8 | DEPENDENT SUBQUERY | rd         | NULL       | ALL  | date,idx_personId | NULL         | NULL    | NULL                             | 992294 |     0.11 | Range checked for each record (index map: 0x3)                |
|  6 | DEPENDENT SUBQUERY | Continents | NULL       | ref  | P_id,recordIndex  | recordIndex  | 9       | cubing.rd2.regionalSingleRecord  |      1 |   100.00 | Using index; Start temporary                                  |
|  6 | DEPENDENT SUBQUERY | Countries  | NULL       | ALL  | NULL              | NULL         | NULL    | NULL                             |    203 |    10.00 | Using where; Using join buffer (Block Nested Loop)            |
|  6 | DEPENDENT SUBQUERY | rd         | NULL       | ALL  | date              | NULL         | NULL    | NULL                             | 992294 |     0.33 | Range checked for each record (index map: 0x1); End temporary |
|  4 | DEPENDENT SUBQUERY | Continents | NULL       | ref  | P_id,recordIndex  | recordIndex  | 9       | cubing.rd2.regionalAverageRecord |      1 |   100.00 | Using index; Start temporary                                  |
|  4 | DEPENDENT SUBQUERY | Countries  | NULL       | ALL  | NULL              | NULL         | NULL    | NULL                             |    203 |    10.00 | Using where; Using join buffer (Block Nested Loop)            |
|  4 | DEPENDENT SUBQUERY | rd         | NULL       | ALL  | date              | NULL         | NULL    | NULL                             | 992294 |     0.11 | Range checked for each record (index map: 0x1); End temporary |
|  3 | DEPENDENT SUBQUERY | rd         | NULL       | ALL  | date,idx_personId | NULL         | NULL    | NULL                             | 992294 |     3.33 | Range checked for each record (index map: 0x3)                |
|  2 | DEPENDENT SUBQUERY | rd         | NULL       | ALL  | date,idx_personId | NULL         | NULL    | NULL                             | 992294 |     1.11 | Range checked for each record (index map: 0x3)                |
+----+--------------------+------------+------------+------+-------------------+--------------+---------+----------------------------------+--------+----------+---------------------------------------------------------------+

我一直在谷歌搜索如何提高它的速度。根据我的谷歌搜索，我知道它看起来不太好。特别是我正在查看的初始表中的 992294 行。

但我的问题是，我不知道如何进行优化以加快所有这一切。我读到精心制作的索引可以大大提高速度，所以我很好奇这里可以使用什么样的索引。

Answer 1

select 子句中的子查询可能非常昂贵。相关子查询通常性能较差，通常有更好的选择。

我没有时间给出一个彻底的答案，但我浏览查询的总体印象是，您可以在主查询中一次将其重构为 JOIN ResultDates 到自身；然后在 SELECT 子句中使用条件聚合。像这样的……

SELECT rd.competitionId, rd.personId, rd.personCountryId, rd.eventId
   , rd.year, rd.date
   , COUNT(DISTINCT IF(rd.regionalAverageRecord = 'WR' AND rdPrev.average > 0, rdPrev.person_id, NULL) AS wrAveragePoints
   , COUNT(DISTINCT IF(regionalSingleRecord = 'WR', rdPrev.person_id, NULL) AS wrSinglePoints
   , [etc....]
FROM ResultDates AS rd 
LEFT JOIN ResultDates AS rdPrev 
   ON rd.eventId=rdPrev.eventId 
   AND rdPrev.date <= rd.date
WHERE (NOT rd.regionalAverageRecord='' OR NOT rd.regionalSingleRecord = '')
;

编辑:对于涉及Countries 和Continents 表的子查询/字段，您可以对这些表进行LEFT JOIN，如下所示好，并以类似于我演示在 wrAveragePoints 计算中使用 rdPrev.average 的方式使用连接值。

注意:COUNT() 和大多数其他聚合函数忽略 NULL 值。