mysql - 为什么这个 MySQL 查询没有正确使用索引？

Question

抱歉，这是一个如此具体且可能陈词滥调的问题，但它确实给我带来了重大问题。

每天我都必须做几十万个看起来像这两个的选择语句(这是一个例子，但它们几乎完全相同，只是 word1 不同):

SELECT pibn,COUNT(*) AS aaa FROM research_storage1
USE INDEX (word21pibn)
WHERE word1=270299 AND word2=0
GROUP BY pibn
ORDER BY aaa DESC
LIMIT 1000;

SELECT pibn,page FROM research_storage1
USE INDEX (word12num)
WHERE word1=270299 AND word2=0
ORDER BY num DESC
LIMIT 1000;

第一个语句非常快，只需几分之一秒。第二个语句大约需要 2 秒，考虑到我有数十万个任务，这太长了。

索引是:

word21pibn: word2, word1, pibn
word12num: word1, word2, num

解释的结果(对于扩展和分区都是):

mysql> explain extended SELECT pibn,COUNT(*) AS aaa FROM research_storage1 USE INDEX (word21pibn) WHERE word1=270299 AND word2=0 GROUP BY pibn ORDER BY aaa DESC LIMIT 1000;
+----+-------------+-------------------+------+---------------+------------+---------+-------------+------+----------+-----------------------------------------------------------+
| id | select_type | table             | type | possible_keys | key        | key_len | ref         | rows | filtered | Extra                                                     |
+----+-------------+-------------------+------+---------------+------------+---------+-------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | research_storage1 | ref  | word21pibn    | word21pibn | 6       | const,const | 1549 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------------------+------+---------------+------------+---------+-------------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain partitions SELECT pibn,COUNT(*) AS aaa FROM research_storage1 USE INDEX (word21pibn) WHERE word1=270299 AND word2=0 GROUP BY pibn ORDER BY aaa DESC LIMIT 1000;
+----+-------------+-------------------+------------+------+---------------+------------+---------+-------------+------+-----------------------------------------------------------+
| id | select_type | table             | partitions | type | possible_keys | key        | key_len | ref         | rows | Extra                                                     |
+----+-------------+-------------------+------------+------+---------------+------------+---------+-------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | research_storage1 | p99        | ref  | word21pibn    | word21pibn | 6       | const,const | 1549 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------------------+------------+------+---------------+------------+---------+-------------+------+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> explain extended SELECT pibn,page FROM research_storage1 USE INDEX (word12num) WHERE word1=270299 AND word2=0 ORDER BY num DESC LIMIT 1000;
+----+-------------+-------------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
| id | select_type | table             | type | possible_keys | key       | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | research_storage1 | ref  | word12num     | word12num | 6       | const,const |  818 |   100.00 | Using where |
+----+-------------+-------------------+------+---------------+-----------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain partitions SELECT pibn,page FROM research_storage1 USE INDEX (word12num) WHERE word1=270299 AND word2=0 ORDER BY num DESC LIMIT 1000;
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------------+------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key       | key_len | ref         | rows | Extra       |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------------+------+-------------+
|  1 | SIMPLE      | research_storage1 | p99        | ref  | word12num     | word12num | 6       | const,const |  818 | Using where |
+----+-------------+-------------------+------------+------+---------------+-----------+---------+-------------+------+-------------+
1 row in set (0.00 sec)

我看到的唯一区别是第二条语句在 describe 的额外列中没有 Using index。虽然这没有意义，因为索引是为该语句设计的，所以我不明白为什么不使用它。

有什么想法吗？

Answer 1

尝试将 pbin 和 page 列添加到 word12num 复合索引中。然后您查询所需的所有信息都将在索引中，就像在您的第一个查询中一样。

编辑 我错过了您选择的 pbin 列；对于那个很抱歉。

如果您的复合索引结果包含 (word1, word2, num, pbin, page) 那么您第二个查询中的所有内容都可以来自索引。

如果您查看第一个查询的 EXPLAIN 下的 Extra 列，其中的一个简介是 Using index. @sebas pointed这个出来实际上，这意味着 仅使用索引。 这意味着服务器只需查询索引即可满足您的查询，而无需查询表。这就是它如此之快的原因:服务器不必在随机访问表的周围敲击磁盘磁头来获取额外的列。 Using index 不在您的第二个查询的 EXPLAIN.

中

WHERE 中提到的列排在第一位。然后我们在 ORDER BY 中有列。最后，我们有您正在SELECTing 的列。为什么对索引中的列使用这种特定顺序？服务器找到与 SELECT 匹配的第一个索引条目，然后可以顺序读取索引以满足查询。

在大表上构建和维护复合索引确实开销很大。您正在考虑 DBMS 设计中的一个基本权衡:您想花时间构建表还是在其中查找内容？只有您知道是在构建表格时还是在其中查找内容时承担成本更好。