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;

慢查询是针对这样的表，大约有 100 万行:

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 在列表中首先显示 t1，并使用“使用位置；使用索引；使用临时；使用文件排序”)

是否有明显遗漏的东西会阻止使用临时表？

Answer 1

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 可以采用 NULL 值，SUM(COALESCE(x) , 0) 可能更好，如果您不希望 Group 上的任何 NULL 值使您的总和为 NULL。