MySQL查询时间太长--应该建立什么索引？

Question

这是我的查询：

CREATE TEMPORARY TABLE temptbl (
  pibn INT UNSIGNED NOT NULL, page SMALLINT UNSIGNED NOT NULL)
  ENGINE=MEMORY;
INSERT INTO temptbl (
  SELECT pibn,page FROM mytable
  WHERE word1=429907 AND word2=0);
ALTER TABLE temptbl ADD INDEX (pibn,page);
SELECT word1,COUNT(*) AS aaa
  FROM mytable a
  INNER JOIN temptbl b
  ON a.pibn=b.pibn AND a.page=b.page
  WHERE word2=0
  GROUP BY word1 ORDER BY aaa DESC LIMIT 10;
DROP TABLE temptbl;

问题在于SELECT word1,COUNT(*) AS aaa，具体来说是COUNT函数。这个SELECT语句需要16秒的时间。

EXPLAIN显示：

+----+-------------+-------+------+---------------------------------+-------------+---------+-------------------------------------------------------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys                   | key         | key_len | ref                                                         | rows  | Extra                           |
+----+-------------+-------+------+---------------------------------+-------------+---------+-------------------------------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | b     | ALL  | pibn                            | NULL        | NULL    | NULL                                                        | 26778 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | w2pibnpage1,word21pibn,pibnpage | w2pibnpage1 | 9       | const,db.b.pibn,db.b.page                                   |     4 | Using index                     |
+----+-------------+-------+------+---------------------------------+-------------+---------+-------------------------------------------------------------+-------+---------------------------------+

使用的索引（w2pibnpage1）位于以下位置：

word2,pibn,page,word1,id

我已经为此苦苦挣扎了数天，尝试了不同的索引列（这很烦人，因为重建索引需要一个小时 - 数据量有数百万行）。

我的索引应该是什么样的，或者我应该怎么做才能使这个查询在几秒钟内运行（正常情况下应该如此）？

- Alasdair

---

这些表大约包含多少行数据？ - Jocelyn

目前是1.5亿，但很快就会达到数十亿。 - Alasdair

对于您的测试，我建议创建两个空表的副本。更改这些空表的索引。 使用复制表的名称重写查询。您可以使用修改后的查询运行EXPLAIN。由于表是空的，更改索引或运行EXPLAIN将非常快速。 - Jocelyn

Answer 1

这里有一个建议。

临时表可能很小。您可以删除该表上的索引，因为在那里进行全表扫描是可以的。实际上，这就是您想要的。

然后，您希望在大表上使用索引。首先，索引需要匹配连接条件，然后匹配where条件，最后匹配group by条件。因此，建议如下：

mytable(pibn, page, word2, word1, aaa)

我正在加入 order by 列，这样就不必从原始数据中提取该值了。

- Gordon Linoff

---

好的。但实际上，我更喜欢首先根据where条件排除。我的查询难道不是这样做的吗？ - Alasdair

临时表可能包含大约10,000个左右。 - Alasdair

同时，aaa并不是一个真正的列，它只是COUNT的标签，这就是问题所在。 - Alasdair

执行计划显示当前正在执行全表扫描？ - symcbean

我尝试了您的索引建议，但速度没有任何改变。 - Alasdair

@Alasdair . . . 有两个建议（在从索引中删除aaa之后）。首先，将where子句中的条件移动到on子句中。其次，给出一个提示（use index）来强制MySQL使用索引。 - Gordon Linoff

Answer 2

如果您的temptbl比较小，您希望先限制更大的表（mytable），然后再将其连接到您的temptbl（最终通过索引）。

现在，MySQL认为使用更大的表的索引进行联接更加优越。

您可以通过进行直接联接来避免这种情况：

  SELECT word1,COUNT(*) AS aaa
    FROM mytable a
    STRAIGHT_JOIN temptbl b
      ON a.pibn=b.pibn AND a.page=b.page
  WHERE word2=0
  GROUP BY word1 
  ORDER BY aaa DESC LIMIT 10;

这应该在 where 子句中使用 mytable 中的索引，并通过 temptbl 中的索引将 mytable 与 temptbl 进行连接。

如果 MySQL 仍然想要使用不同的方式，可以使用 FORCE INDEX 强制它使用索引。

- drunken_monkey

---

我尝试了你的查询。对于相同的结果，它花费了1分半钟，因此效率较低。 - Alasdair

你能否运行一下我的查询解释器？这可能会显示问题所在。 - drunken_monkey

Answer 3

查询时间很长，但昂贵的部分似乎是访问'mytable'（您没有提供其结构），然而优化器似乎认为它只需要使用索引从中获取4行 - 这应该非常快。即数据似乎非常倾斜 - 最后一个查询检查了多少行（计数的总和）？

如果不看数据的确切分布，很难下定论 - 当然，您可能需要提示查询以使其有效地工作。设计索引的问题在于它们应该使所有查询更快 - 或者至少给出合理的权衡。

查看您提供的查询中的谓词...

WHERE word1=429907 AND word2=0

最好在 word1，word2，... 或 word2，word1，... 上建立索引。

ON a.pibn=b.pibn AND a.page=b.page
WHERE a.word2=0

在我的表格中，最好使用以word2 + pibn + page作为前导列的索引。

mytable.word1和mytable.word2有多少个不同的值？如果word2的不同值数量很低（少于20个左右），那么它对索引的筛选性能贡献很小，可以省略。

在word2、pibn、page、word1上创建一个索引可以为第二个查询提供覆盖索引。

- symcbean

---

word1 和 word2 有 60 万个不同的取值。行数达数亿级别。pibn 有数十万个不同的取值。第一个用于填充临时表的选择语句平均需要10,000行，最多可达到100,000行。我已经追踪到COUNT(*)导致的减速问题。 - Alasdair

你是指word1+word2有600,000个值，还是word1有600,000个值，word2也有600,000个值？如果是后者，那么你的索引需要重建。 - symcbean

Answer 4

由于您的数据量很大，无论您做什么都不可能快速运行，除非更改模式。

如果我理解正确，您正在寻找与429907在同一页面上的顶部单词。

您现在的模型需要每次运行查询时重新计算所有这些单词。

为了加快速度，您需要创建一个额外的统计表：

CREATE TABLE word_pairs
        (
        word1_1 INT NOT NULL,
        word1_2 INT NOT NULL,
        cnt BIGINT NOT NULL,
        PRIMARY KEY (word1_1, word1_2),
        INDEX (word1_1, cnt),
        INDEX (word1_2, cnt)
        )

每次向大表插入记录时，更新它（增加新插入的单词和与其在同一页上的所有单词的cnt）。

对于单个服务器来说，这种更新可能会太慢，因为这些更新需要一些时间，所以您还需要将该表分片到多个服务器上。

如果您有这样的表，只需运行：

SELECT  *
FROM    word_pairs
WHERE   word1_1 = 429907
ORDER BY
        cnt DESC
LIMIT   10

这将是即时的。

- Quassnoi

---

@Alasdair：顺便问一下，查询完成后返回的是哪些最高计数？ - Quassnoi

429907 26778，657171 15886，657271 14193，657272 11459，等等。看起来有一些重复，这意味着该组不正常工作。 - Alasdair

我的错误...没有重复数据，数字只是巧合地非常接近。可能是同一个单词的变体，或者是拼写不同。 - Alasdair

这将需要我在处理期间在内存中保存600000*600000=360000000000个整数，以记录映射到所有其他单词的每个单词的出现次数。我将需要8TB的RAM。 - Alasdair

让我们在聊天中继续这个讨论：http://chat.stackoverflow.com/rooms/28314/discussion-between-alasdair-and-quassnoi - Alasdair

Answer 5

我想到了这个：

CREATE TEMPORARY TABLE temp1 (
  pibn INT UNSIGNED NOT NULL, page SMALLINT UNSIGNED NOT NULL)
  ENGINE=MEMORY;
INSERT INTO temp1 (
  SELECT pibn,page FROM mytable
  WHERE word1=429907 AND word2=0);
CREATE TEMPORARY TABLE temp2 (
  word1 MEDIUMINT UNSIGNED NOT NULL)
  ENGINE=MEMORY;
INSERT INTO temp2 (
SELECT a.word1
  FROM mytable a, temp1 b
  WHERE a.word2=0 AND a.pibn=b.pibn AND a.page=b.page);
DROP TABLE temp1;
CREATE INDEX index1 ON temp2 (word1) USING BTREE;
CREATE TEMPORARY TABLE temp3 (
  word1 MEDIUMINT UNSIGNED NOT NULL, num INT UNSIGNED NOT NULL)
  ENGINE=MEMORY;
INSERT INTO temp3 (SELECT word1,COUNT(*) AS aaa FROM temp2 USE INDEX (index1) GROUP BY word1);
DROP TABLE temp2;
CREATE INDEX index1 ON temp3 (num) USING BTREE;
SELECT word1,num FROM temp3 USE INDEX (index1) ORDER BY num DESC LIMIT 10;
DROP TABLE temp3;

只需要5秒钟。

- Alasdair