GROUP BY和JOIN的MySQL性能

Question

在花了很多时间研究这个问题的变体之后，我想知道是否有人可以帮助我优化这个查询或索引。

我有三个临时表ref1、ref2和ref3，都定义如下，其中ref1和ref2各有6000行，而ref3只有3行：

CREATE TEMPORARY TABLE ref1 (
  id INT NOT NULL AUTO_INCREMENT,
  val INT,
  PRIMARY KEY (id)
)
ENGINE = MEMORY;

慢查询针对的是这样一个表，大约有1M行：

CREATE TABLE t1 (
  d DATETIME NOT NULL,
  id1 INT NOT NULL,
  id2 INT NOT NULL,
  id3 INT NOT NULL,
  x INT NULL,
  PRIMARY KEY (id1, d, id2, id3)
)
ENGINE = INNODB;

有问题的查询：

SELECT id1, SUM(x)
  FROM t1
  INNER JOIN ref1 ON ref1.id = t1.id1
  INNER JOIN ref2 ON ref2.id = t1.id2
  INNER JOIN ref3 ON ref3.id = t1.id3
  WHERE d BETWEEN '2011-03-01' AND '2011-04-01'
  GROUP BY id1;

临时表用于将结果集筛选为用户正在查找的项目。

解释

+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref              | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+
|  1 | SIMPLE      | ref1  | ALL    | PRIMARY       | NULL    | NULL    | NULL             | 6000 | Using temporary; Using filesort |
|  1 | SIMPLE      | t1    | ref    | PRIMARY       | PRIMARY | 4       | med31new.ref1.id |   38 | Using where                     |
|  1 | SIMPLE      | ref3  | ALL    | PRIMARY       | NULL    | NULL    | NULL             |    3 | Using where; Using join buffer  |
|  1 | SIMPLE      | ref2  | eq_ref | PRIMARY       | PRIMARY | 4       | med31new.t1.id2  |    1 |                                 |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+---------------------------------+

(在另一个大约有5M行的系统上，EXPLAIN show t1在列表中排在第一位，“使用where；使用索引；使用临时文件；使用文件排序”)

有没有什么明显的遗漏会阻止临时表的使用？

Answer 1

仅在日期添加索引。由于这是第一个表的条件，而其他表只是连接，因此它将首先根据日期进行优化……连接是次要的。

Answer 2

First filesort并不意味着要在磁盘上写入一个文件来执行排序，它是mySQL中快速排序算法的名称，请检查what-does-using-filesort-mean-in-mysql。

所以在解释中有问题的关键字是Using temporary，而不是Using filesort。为此，您可以尝试使用tmp_table_size和max_heap_table_size(在两者上设置相同的值)，以允许更多的内存工作并避免临时表创建，请查看this link on the subject with remarks about documentation mistakes。

然后，您可以尝试不同的索引策略，并查看结果，但不要尝试避免文件排序。

最后一件不相关的事情，你做了一个SUM(x)，但是x可以接受空值，如果你不希望组上的任何空值使你的和为空，SUM(COALESCE(x) , 0)可能更好。

Answer 3

不是这样的吗：

SELECT id1, SUM(x)
  FROM t1
  INNER JOIN ref1 ON ref1.id = t1.id1
  INNER JOIN ref2 ON ref2.id = t1.id2
  INNER JOIN ref3 ON ref3.id = t1.id3
  WHERE d BETWEEN '2011-03-01' AND '2011-04-01'
  GROUP BY id1;

完全等同于：

select id1, SUM(x)
  FROM t1
  WHERE d BETWEEN '2011-03-01' AND '2011-04-01'
  group by id1;

额外的表是用来做什么的？我认为在另一个答案中提到的临时表是指MySQL在查询执行期间创建临时表。如果您希望创建子查询(或表)来最小化连接中所需的操作数量，那么这可能会加快查询速度，但我没有看到连接的数据被选中。