如何让 JOIN 查询使用索引？

Question

我有两张桌子：

 CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(1000) DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `last_updated` (`last_updated`),
) ENGINE=InnoDB AUTO_INCREMENT=799681 DEFAULT CHARSET=utf8

CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

这是我的查询：

 SELECT a.*
FROM
    articles AS a,
    article_categories AS c
WHERE
    a.id = c.article_id
    AND c.category_id = 78
    AND a.comment_cnt > 0
    AND a.deleted = 0
ORDER BY a.last_updated
LIMIT 100, 20

还有一个 EXPLAIN ：

 *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: index
possible_keys: PRIMARY
          key: last_updated
      key_len: 9
          ref: NULL
         rows: 2040
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY,fandom_id
          key: PRIMARY
      key_len: 8
          ref: db.a.id,const
         rows: 1
        Extra: Using index

它在第一个表上使用 last_updated 的完整索引扫描进行排序，但不使用任何索引进行连接（解释中的 type: index ）。这对性能非常不利，并且会杀死整个数据库服务器，因为这是一个非常频繁的查询。

我尝试使用 STRAIGHT_JOIN 反转表格顺序，但这给出了 filesort, using_temporary ，这更糟。

有什么方法可以让 MySQL 同时使用索引进行连接和排序？

=== 更新 ===

我真的很绝望。也许某种非规范化可以在这里提供帮助？

原文由 Silver Light 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

如果您有很多类别，则此查询无法高效。没有一个索引可以同时覆盖 MySQL 中的两个表。

您必须进行非规范化：将 last_updated 、 has_comments 和 deleted 添加到 article_categories

 CREATE TABLE `article_categories` (
  `article_id` int(11) NOT NULL DEFAULT '0',
  `category_id` int(11) NOT NULL DEFAULT '0',
  `last_updated` timestamp NOT NULL,
  `has_comments` boolean NOT NULL,
  `deleted` boolean NOT NULL,
  PRIMARY KEY (`article_id`,`category_id`),
  KEY `category_id` (`category_id`),
  KEY `ix_articlecategories_category_comments_deleted_updated` (category_id, has_comments, deleted, last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

并运行此查询：

 SELECT  *
FROM    (
        SELECT  article_id
        FROM    article_categories
        WHERE   (category_id, has_comments, deleted) = (78, 1, 0)
        ORDER BY
                last_updated DESC
        LIMIT   100, 20
        ) q
JOIN    articles a
ON      a.id = q.article_id

当然，每当您更新 --- 中的相关列时，您都应该更新 article_categories article 。这可以在触发器中完成。

请注意，列 has_comments 是布尔值：这将允许使用相等谓词对索引进行单个范围扫描。

另请注意， LIMIT 进入子查询。这使得 MySQL 使用默认情况下不使用的延迟行查找。请参阅我的博客中的这篇文章，了解它们为什么会提高性能：

• MySQL ORDER BY / LIMIT 性能：延迟行查找

如果你在 SQL Server 上，你可以在你的查询上创建一个可索引的视图，这实际上会创建一个 article_categories 的非规范化索引副本，其中包含额外的字段，由服务器自动维护。

不幸的是， MySQL 不支持这一点，您必须手动创建这样的表并编写额外的代码以使其与基表保持同步。

原文由 Quassnoi 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

首先，我建议阅读文章 MySQL 使用索引的 3 种方式。

现在，当您了解基础知识后，您就可以优化这个特定的查询了。

MySQL不能使用索引进行排序，它只能按照索引的顺序输出数据。由于 MySQL 使用嵌套循环进行连接，因此您想要排序的字段应该在连接的第一个表中（您在 EXPLAIN 结果中看到连接顺序，并且可以通过创建特定索引来影响它（如果它没有帮助） ) 通过强制要求的索引)。

另一个重要的事情是，在订购之前，您从 a 表中获取所有过滤行的所有列，然后可能跳过其中的大部分。获取所需行 ID 的列表并仅获取这些行会更有效。

要完成这项工作，您需要一个覆盖索引 (deleted, comment_cnt, last_updated) 在表 a 上，现在您可以重写查询如下：

 SELECT *
FROM (
  SELECT a.id
  FROM articles AS a,
  JOIN article_categories AS c
    ON a.id = c.article_id AND c.category_id = 78
  WHERE a.comment_cnt > 0 AND a.deleted = 0
  ORDER BY a.last_updated
  LIMIT 100, 20
) as ids
JOIN articles USING (id);

PS您的表定义表 a 不包含 comment_cnt 列;）

原文由 newtover 发布，翻译遵循 CC BY-SA 3.0 许可协议