mysql - 我如何通过已经使用索引的查询来加速分组？

Question

我们有一个包含大约 7500 万行和 5 列的 MyISAM 表:

id (int), 
user_id(int), 
page_id (int), 
type (enum with 6 strings)
date_created(datetime).

我们在 ID 列上有一个主索引、一个唯一索引(user_id、page_id、date_created)和一个复合索引(page_id、date_created)

问题是下面的查询最多需要 90 秒才能完成

SELECT SQL_NO_CACHE user_id, count(id) nr 
FROM `table` 
WHERE `page_id`=301 
and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' 
AND page_id<>user_id 
group by `user_id`

这是查询的解释

+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
| id | select_type | table                      | type  | possible_keys | key     | key_len | ref  | rows   | Extra                                        |
+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | table                      | range | page_id       | page_id | 12      | NULL | 520024 | Using where; Using temporary; Using filesort |
+----+-------------+----------------------------+-------+---------------+---------+---------+------+--------+----------------------------------------------+

编辑:在 ypercube 的建议下，我尝试添加一个新索引(page_id、user_id、date_created)。但是 mysql 默认不使用它，所以我不得不向查询优化器建议它。这是新的查询和解释:

SELECT SQL_NO_CACHE user_id, count(*) nr FROM `table` USE INDEX (usridexp) WHERE `page_id`=301 and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' AND page_id<>user_id group by `user_id` ORDER BY NULL


    +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
    | id | select_type | table                      | type | possible_keys | key      | key_len | ref   | rows    | Extra                    |
    +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
    |  1 | SIMPLE      | table                      | ref  | usridexp      | usridexp | 4       | const | 3943444 | Using where; Using index |
    +----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+

Answer 1

可能会改进查询的一些更改:

• 将 COUNT(id) 更改为 COUNT(*)。由于 id 是(我猜)PRIMARY KEY 和 NOT NULL，结果将是相同的。

• 在 GROUP BY 子句之后添加一个 ORDER BY NULL。在 MySQL 中，group by 操作也会对结果进行排序，除非您指定其他方式。

• (page_id, date_created) 可能是 MySQL 可用于此查询的最佳索引，但您也可以尝试 (page_id, user_id, date_created) (加了这个索引也能贴出EXPLAIN吗？)

---

与此查询的性能无关的另一件事:

如果您的 (user_id, page_id, date_created) 是 UNIQUE 并且 id 是自动生成的(除了作为a Primary Key)，您可以将其设为 PRIMARY KEY 并删除 id 列。少一个索引，每行少 4 个字节。