如何使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在第一个表上使用的全索引扫描进行排序，但不使用y索引进行连接（type: index在解释中）。这对性能非常不利，并且会杀死整个数据库服务器，因为这是一个非常频繁的查询。

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

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

===更新===

我真的很绝望。也许某种形式的非规范化可以在这里有所帮助？

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它不支持此功能，您将必须手动创建这样的表并编写其他代码以使其与基本表保持同步。