使用临时表和文件排序优化MySql查询

Question

我有一个查询（如下所示），目前使用临时表和文件排序，以生成一组有序的结果。如果可能的话，我想摆脱它们的使用。我已经研究了此查询中使用的底层索引，但就是看不到缺少什么。

SELECT 
  b.institutionid AS b__institutionid,
  b.name AS b__name,  
  COUNT(DISTINCT f2.facebook_id) AS f2__0 
FROM education_institutions b 
LEFT JOIN facebook_education_matches f ON b.institutionid = f.institutionid 
LEFT JOIN facebook_education f2 ON f.school_uid = f2.school_uid 
WHERE 
  (
  b.approved = '1' 
  AND f2.facebook_id IN ( [lots of facebook ids here ])
  ) 
GROUP BY b__institutionid 
ORDER BY f2__0 DESC
LIMIT 10

这是使用EXPLAIN EXTENDED命令的输出结果：

+----+-------------+-------+--------+--------------------------------+----------------+---------+----------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                  | key            | key_len | ref                              | rows | filtered | Extra                                        |
+----+-------------+-------+--------+--------------------------------+----------------+---------+----------------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | f     | index  | PRIMARY,institutionId          | institutionId  | 4       | NULL                             |  308 |   100.00 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | f2    | ref    | facebook_id_idx,school_uid_idx | school_uid_idx | 9       | f.school_uid                     |    1 |   100.00 | Using where                                  |
|  1 | SIMPLE      | b     | eq_ref | PRIMARY                        | PRIMARY        | 4       | f.institutionId                  |    1 |   100.00 | Using where                                  |
+----+-------------+-------+--------+--------------------------------+----------------+---------+----------------------------------+------+----------+----------------------------------------------+

每个表的CREATE TABLE语句如下，以便您了解模式。

CREATE TABLE facebook_education (
  education_id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(255) DEFAULT NULL,
  school_uid bigint(20) DEFAULT NULL,
  school_type varchar(255) DEFAULT NULL,
  year smallint(6) DEFAULT NULL,
  facebook_id bigint(20) DEFAULT NULL,
  degree varchar(255) DEFAULT NULL,
  PRIMARY KEY (education_id),
  KEY facebook_id_idx (facebook_id),
  KEY school_uid_idx (school_uid),
  CONSTRAINT facebook_education_facebook_id_facebook_user_facebook_id FOREIGN KEY (facebook_id) REFERENCES facebook_user (facebook_id)
) ENGINE=InnoDB AUTO_INCREMENT=484 DEFAULT CHARSET=utf8;

CREATE TABLE facebook_education_matches (
  school_uid bigint(20) NOT NULL,
  institutionId int(11) NOT NULL,
  created_at timestamp NULL DEFAULT NULL,
  updated_at timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (school_uid),
  KEY institutionId (institutionId),
  CONSTRAINT fk_facebook_education FOREIGN KEY (school_uid) REFERENCES facebook_education (school_uid) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_education_institutions FOREIGN KEY (institutionId) REFERENCES education_institutions (institutionId) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT;

CREATE TABLE education_institutions (
  institutionId int(11) NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  type enum('School','Degree') DEFAULT NULL,
  approved tinyint(1) NOT NULL DEFAULT '0',
  deleted tinyint(1) NOT NULL DEFAULT '0',
  normalisedName varchar(100) NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (institutionId)
) ENGINE=InnoDB AUTO_INCREMENT=101327 DEFAULT CHARSET=utf8;

任何指导将不胜感激。

- GordyD

Answer 1

文件排序可能发生是因为你没有适当的索引来进行ORDER BY操作。MySQL "ORDER BY Optimization"文档中提到了这一点。你可以做的是加载一个临时表，然后从中选择。在加载临时表时，使用ORDER BY NULL。当你从临时表中选择时，使用ORDER BY .. LIMIT。问题在于group by会添加一个隐含的order by <group by clause> ASC，除非你通过添加order by null来禁用该行为。这是MySQL特有的注意事项之一。

- gbn

---

当我删除GROUP BY子句时，'using filesort'就消失了！ - GordyD

@GordyD：这就是为什么我给出了这个答案的原因 :-) - gbn

将ORDER BY子句更改为具有索引的内容并不会使其不使用filesort。只有当我完全删除GROUP BY时，才不会使用filesort。我完全不明白你的答案与此有何关系。 - GordyD

GROUP BY 强制排序，假设 ORDER BY 将是相同的 (b__institutionid)。ORDER BY NULL 告诉优化器不要这样做。这在我的链接中有解释。 - gbn

好的，很公平，我现在明白了，谢谢你澄清。不过，我认为创建临时表并不是一个可行的解决方案。这个查询是由Doctrine ORM从DQL查询生成的，所以我希望通过添加所需的索引来摆脱这个问题。有没有其他方法可以避免使用COUNT字段进行排序时出现文件排序的情况？ - GordyD

@GordyD：在这种情况下我不确定。我觉得MySQL在这些问题上有点愚蠢。 - gbn

Answer 2

我可以看到两个可能的优化：

1. b.approved = '1' - 你需要在approved列上建立索引以进行快速过滤。

2. f2.facebook_id IN ( [这里有很多Facebook ID]) - 将Facebook ID存储在临时表中。然后在临时表上创建索引，然后与临时表连接而不是使用IN子句。

- Vivek Viswanathan

---

1.b.approved = '1' - 你绝对需要在approved列上建立索引以进行快速过滤。但这不起作用，因为approved是一个布尔字段，并且具有低基数，MySQL将拒绝使用索引。 - Johan