MYSQL:优化表排序中的Order By

Question

我正在为我学院的网站开发一个应用程序，我想从数据库中按日期升序提取所有事件。一共有四张表:

表事件1

event_id, mediumint(8), Unsigned
date,     date,                   

Index -> Primary Key (event_id)
Index -> (date)

表 events_users

event_id, smallint(5),  Unsigned
user_id,  mediumint(8), Unsigned

Index -> PRIMARY (event_id, user_id)

表 user_bm

link,    varchar(26)
user_id, mediumint(8)

Index -> PRIMARY (link, user_id)

表 user_eoc

link,    varchar(8)
user_id, mediumint(8)

Index -> Primary (link, user_id)

查询:

EXPLAIN SELECT * FROM events1 E INNER JOIN event_users EU ON E.event_id = EU.event_id
 RIGHT JOIN user_eoc EOC ON EU.user_id = EOC.user_id
 INNER JOIN user_bm BM ON EOC.user_id = BM.user_id
WHERE E.date >= '2013-01-01' AND E.date <= '2013-01-31'
  AND EOC.link = "E690"
  AND BM.link like "1.1%"
ORDER BY E.date

解释:

上面的查询做了两件事。

1) 通过user_bm 和user_eoc 表搜索并过滤掉所有学生。 “链接”列是非规范化列，可以按专业/年级/校区等快速过滤学生。

2) 应用过滤器后，MYSQL 抓取所有匹配学生的 user_id 并找到他们参加的所有事件并按升序输出。

查询优化器解释:

id  select_type     table   type      possible_keys     key     key_len     ref rows    Extra
1   SIMPLE  EOC     ref     PRIMARY     PRIMARY     26  const   47  Using where; Using index; Using temporary; Using f...
1   SIMPLE  BM  ref     PRIMARY,user_id-link    user_id-link    3   test.EOC.user_id    1   Using where; Using index
1   SIMPLE  EU  ref     PRIMARY,user_id     user_id     3   test.EOC.user_id    1   Using index
1   SIMPLE  E   eq_ref  PRIMARY,date-event_id   PRIMARY     3   test.EU.event_id    1   Using where

问题:

查询工作正常但可以优化。具体来说-使用文件排序和使用临时文件的成本很高，我想避免这种情况。我不确定这是否可行，因为我想按与匹配用户具有 1:n 关系的日期“排序依据”事件。 Order BY 适用于连接表。

如有任何帮助或指导，我们将不胜感激。谢谢，节日快乐!

Answer 1

可以通过两种方式进行订购。按索引或按临时表。您在表 Events1 中按日期排序，但它使用的是不包含日期的主键，因此在这种情况下，结果需要在临时表中排序。

但不一定贵。如果结果小到足以放入内存，那么它就不会是磁盘上的临时表，而是内存中的临时表，这并不昂贵。

文件排序也不是。 “使用文件排序”并不意味着它会使用任何文件，它只是意味着它不按索引排序。

因此，如果您的查询执行得很快，您应该很高兴。如果结果集很小，它将在内存中排序并且不会创建任何文件。