Mysql "Using temporary"但此查询中没有匹配的官方原因

Question

如果你查看MySql临时表的官方文档:

http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html

给出的理由是:

   The server creates temporary tables under conditions such as these:

      Evaluation of UNION statements.

      Evaluation of some views, such those that use the TEMPTABLE algorithm,
      UNION, or aggregation.

      Evaluation of statements that contain an ORDER BY clause and a
      different GROUP BY clause, or for which the ORDER BY or GROUP BY
      contains columns from tables other than the first table in the join queue.

      Evaluation of DISTINCT combined with ORDER BY may require a temporary table.

      For queries that use the SQL_SMALL_RESULT option, MySQL uses an
      in-memory temporary table, unless the query also contains elements
      (described later) that require on-disk storage.

      Evaluation of multiple-table UPDATE statements.

      Evaluation of GROUP_CONCAT() or COUNT(DISTINCT) expressions. 

此查询中没有满足以下条件:

  select ttl.id AS id,
  ttl.name AS name,
  ttl.updated_at AS last_update_on,
  ttl.user_id AS list_creator,
  ttl.retailer_nomination_list AS nomination_list,
  ttl.created_at AS created_on,
  tv.name AS venue_name,
  from haha_title_lists ttl 
    left join haha_title_list_to_users tltu on  ((ttl.id = tltu.title_list_id))
    left join users u on  ((tltu.user_id = u.id)) 
    left join users u2 on  ((tltu.user_id = u2.id)) 
    left join haha_title_list_to_venues tlv on  ((ttl.id = tlv.title_list)) 
    left join haha_venue_properties tvp on  ((tlv.venue_id = tvp.id)) 
    left join haha_venues tv on  ((tvp.venue_id = tv.id))
    join haha_title_list_to_books tlb on  ((ttl.id = tlb.title_list_id))
    join wawa_title ot on  ((tlb.title_id = ot.title_id))
    join wawa_title_to_author ota on  ((ot.title_id = ota.title_id))
    join wawa_author oa on  ((ota.author_id = oa.author_id))
    group by ttl.id;

对于该表:

  CREATE TABLE  haha_title_lists  (
     id  int(11) unsigned NOT NULL AUTO_INCREMENT,
     name  varchar(255) DEFAULT NULL,
     isbn  varchar(15) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
     created_at  datetime NOT NULL,
     updated_at  datetime NOT NULL,
     user_id  int(11) DEFAULT NULL,
     list_note  text,
     retailer_nomination_list  int(11) DEFAULT NULL,
    PRIMARY KEY ( id )
  ) ENGINE=InnoDB AUTO_INCREMENT=460 DEFAULT CHARSET=utf8

我希望使用主键，因为该表仅与 id 匹配。什么会导致使用临时表？

如果我对此查询运行 EXPLAIN，我会得到:

    +----+-------------+-------+--------+------------------------------------------------------------------------+---------------------------------------+---------+---------------------------------------+------+---------------------------------+
  | id | select_type | table | type   | possible_keys                                                          | key                                   | key_len | ref                                   | rows | Extra                           |
  +----+-------------+-------+--------+------------------------------------------------------------------------+---------------------------------------+---------+---------------------------------------+------+---------------------------------+
  |  1 | SIMPLE      | ttl   | ALL    | PRIMARY                                                                | NULL                                  | NULL    | NULL                                  |  307 | Using temporary; Using filesort |
  |  1 | SIMPLE      | tltu  | ref    | idx_title_list_to_user                                                 | idx_title_list_to_user                | 4       | wawa_ripple_development.ttl.id        |    1 | Using index                     |
  |  1 | SIMPLE      | u     | eq_ref | PRIMARY                                                                | PRIMARY                               | 4       | wawa_ripple_development.tltu.user_id  |    1 | Using index                     |
  |  1 | SIMPLE      | u2    | eq_ref | PRIMARY                                                                | PRIMARY                               | 4       | wawa_ripple_development.tltu.user_id  |    1 | Using index                     |
  |  1 | SIMPLE      | tlb   | ref    | idx_title_list_to_books_title_id,idx_title_list_to_books_title_list_id | idx_title_list_to_books_title_list_id | 4       | wawa_ripple_development.ttl.id        |   49 | Using where                     |
  |  1 | SIMPLE      | ot    | eq_ref | PRIMARY                                                                | PRIMARY                               | 4       | wawa_ripple_development.tlb.title_id  |    1 | Using index                     |
  |  1 | SIMPLE      | ota   | ref    | PRIMARY,title_id                                                       | title_id                              | 4       | wawa_ripple_development.ot.title_id   |    1 | Using where; Using index        |
  |  1 | SIMPLE      | oa    | eq_ref | PRIMARY                                                                | PRIMARY                               | 4       | wawa_ripple_development.ota.author_id |    1 | Using index                     |
  |  1 | SIMPLE      | tlv   | ALL    | NULL                                                                   | NULL                                  | NULL    | NULL                                  |  175 |                                 |
  |  1 | SIMPLE      | tvp   | eq_ref | PRIMARY                                                                | PRIMARY                               | 4       | wawa_ripple_development.tlv.venue_id  |    1 |                                 |
  |  1 | SIMPLE      | tv    | eq_ref | PRIMARY                                                                | PRIMARY                               | 4       | wawa_ripple_development.tvp.venue_id  |    1 |                                 |
  +----+-------------+-------+--------+------------------------------------------------------------------------+---------------------------------------+---------+---------------------------------------+------+---------------------------------+

为什么我收到“使用临时；使用文件排序”？

Answer 1

首先，一些评论...

“using temp, using filesort”通常位于EXPLAIN的第一行，但它们的实际位置可以在任何地方。此外，可能有多个 tmp和/或排序，甚至对于1表查询。例如:... GROUP BY aaa ORDER BY bbb 可以使用一个 tmp 进行分组，使用另一个 tmp 进行排序。

在较新的版本中，您可以执行 EXPLAIN FORMAT=JSON SELECT... 来获得详细的帐户 - 那里会清楚有多少 tmp 和类别。

“文件排序”是一个用词不当。在许多情况下，数据实际上可能被收集在内存中并在那里排序。即，“在查询的拍摄中没有文件受到损害”。决定(预先或稍后)使用基于磁盘的排序的原因有很多；我不会在这个答案中提供这些细节。一种检查方法是 SHOW STATUS LIKE 'Created_tmp%tables';。另一种是通过 Slowlog。

直到最近，一些 UNION 才得到改进以避免临时表——在明显不需要它们的情况下。唉，联合仍然是单线程的。

回到你的问题...是的，你的GROUP BY适用于第一个表。但是，无论出于何种原因，优化器选择收集数据，然后排序。另一种选择是使用 PRIMARY KEY(id) 进行排序和分组。嗯...我想知道如果您添加 ORDER BY ttl.id 会发生什么？我猜测优化器专注于如何进行 GROUP BY 操作——通过文件排序或在 ram 中收集哈希值，并且它决定所有的 JOIN 都将被删除。太多的事情需要思考。