mysql - 在查询中使用 GROUP BY 时有关 MySQL 表索引的帮助

Question

感谢您的关注。

有两个 INNODB 表:

表格作者

id        INT
nickname  VARCHAR(50) 
status    ENUM('active', 'blocked')
about     TEXT

表格书籍

author_id  INT
title      VARCHAR(150)

我正在对这些表运行查询，以获取每个作者及其拥有的书籍数量:

SELECT a. * , COUNT( b.id ) AS book_count
FROM authors AS a, books AS b
WHERE a.status != 'blocked'
AND b.author_id = a.id
GROUP BY a.id
ORDER BY a.nickname

此查询非常慢(执行大约需要 6 秒)。我在 books.author_id 上有一个索引，它工作得很好，但我不知道如何在 authors 表上创建索引，以便此查询可以使用它。

以下是当前 EXPLAIN 的外观:

id   select_type   table    type    possible_keys               key            key_len   ref     rows    Extra
1    SIMPLE        a        ALL     PRIMARY,id_status_nickname  NULL           NULL      NULL    3305    Using where; Using temporary; Using filesort
1    SIMPLE        b        ref     key_author_id               key_author_id  5         a.id    2       Using where; Using index

我看过MySQL manual on optimizing queries with group by ，但无法弄清楚如何将其应用到我的查询中。

如果有任何帮助和提示，我将不胜感激 - 索引结构必须是什么，以便 MySQL 可以使用它？

编辑

我试过:

(id, status, nickname)
(status, nickname)

两者都导致了相同的情况。

Answer 1

我假设 id_status_nickname 是一个复合索引(id、status、nickname)。在您的查询中，您可以通过说 a.status !=blocked 来过滤行。这有以下问题:

1. 您没有可用于此目的的索引。无法使用 (id,status,nickname)，因为 status 不是该索引的前缀
2. 假设您有状态索引，则在使用 != 时无法使用它。您必须将其更改为 status='active'
3. 此外，status 是一个只有两个值的枚举字段，基数会很低。所以mysql最终可能根本不使用索引。

您可以尝试以下操作:将索引创建为 (status,id,nickname) 并使用 status='active'。我的猜测是，由于您使用的是“=”并且状态是索引的前缀，因此应该选择该索引，然后将其用于分组依据，然后进行排序。希望这会有所帮助。

更新:看来当 WHERE 子句没有 ORDER BY 中使用的字段时，无法避免文件排序。