mysql - 查询使用没有强制索引的临时表

Question

询问

SELECT SQL_NO_CACHE contacts.id,
       contacts.date_modified contacts__date_modified
FROM contacts
INNER JOIN
  (SELECT tst.team_set_id
   FROM team_sets_teams tst
   INNER JOIN team_memberships team_membershipscontacts ON (team_membershipscontacts.team_id = tst.team_id)
   AND (team_membershipscontacts.user_id = '5daa2e92-c347-11e9-afc5-525400a80916')
   AND (team_membershipscontacts.deleted = 0)
   GROUP BY tst.team_set_id) contacts_tf ON contacts_tf.team_set_id = contacts.team_set_id
LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c = contacts.id
WHERE contacts.deleted = 0
ORDER BY contacts.date_modified DESC,
         contacts.id DESC
LIMIT 21;

耗时极长（2M 条记录需要 2 分钟）。我无法更改此查询，因为它是系统生成的

这是它的解释：

+----+-------------+--------------------------+------------+--------+-------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
| id | select_type | table                    | partitions | type   | possible_keys                                                                                         | key                        | key_len | ref                                       | rows    | filtered | Extra                                                               |
+----+-------------+--------------------------+------------+--------+-------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
|  1 | PRIMARY     | contacts                 | NULL       | ref    | idx_contacts_tmst_id,idx_del_date_modified,idx_contacts_del_last,idx_cont_del_reports,idx_del_id_user | idx_del_date_modified      | 2       | const                                     | 1113718 |   100.00 | Using temporary; Using filesort                                     |
|  1 | PRIMARY     | <derived3>               | NULL       | ALL    | NULL                                                                                                  | NULL                       | NULL    | NULL                                      |       2 |    50.00 | Using where; Using join buffer (Block Nested Loop)                  |
|  1 | PRIMARY     | contacts_cstm            | NULL       | eq_ref | PRIMARY                                                                                               | PRIMARY                    | 144     | sugarcrm.contacts.id                      |       1 |   100.00 | Using index                                                         |
|  3 | DERIVED     | team_membershipscontacts | NULL       | ref    | idx_team_membership,idx_teammemb_team_user,idx_del_team_user                                          | idx_team_membership        | 145     | const                                     |       2 |    99.36 | Using index condition; Using where; Using temporary; Using filesort |
|  3 | DERIVED     | tst                      | NULL       | ref    | idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id,idx_ud_team_id_team_set_id                            | idx_ud_team_id_team_set_id | 144     | sugarcrm.team_membershipscontacts.team_id |       1 |   100.00 | Using index                                                         |
+----+-------------+--------------------------+------------+--------+-------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+

但是当我使用force index(idx_del_date_modified) （与解释中使用的索引相同）时，查询只需要 0.01 秒，我得到的解释略有不同。

+----+-------------+--------------------------+------------+--------+----------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
| id | select_type | table                    | partitions | type   | possible_keys                                                              | key                        | key_len | ref                                       | rows    | filtered | Extra                                                               |
+----+-------------+--------------------------+------------+--------+----------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
|  1 | PRIMARY     | contacts                 | NULL       | ref    | idx_del_date_modified                                                      | idx_del_date_modified      | 2       | const                                     | 1113718 |   100.00 | Using where                                                         |
|  1 | PRIMARY     | <derived2>               | NULL       | ALL    | NULL                                                                       | NULL                       | NULL    | NULL                                      |       2 |    50.00 | Using where                                                         |
|  1 | PRIMARY     | contacts_cstm            | NULL       | eq_ref | PRIMARY                                                                    | PRIMARY                    | 144     | sugarcrm.contacts.id                      |       1 |   100.00 | Using index                                                         |
|  2 | DERIVED     | team_membershipscontacts | NULL       | ref    | idx_team_membership,idx_teammemb_team_user,idx_del_team_user               | idx_team_membership        | 145     | const                                     |       2 |    99.36 | Using index condition; Using where; Using temporary; Using filesort |
|  2 | DERIVED     | tst                      | NULL       | ref    | idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id,idx_ud_team_id_team_set_id | idx_ud_team_id_team_set_id | 144     | sugarcrm.team_membershipscontacts.team_id |       1 |   100.00 | Using index                                                         |
+----+-------------+--------------------------+------------+--------+----------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+

第一个查询使用临时表和文件排序，但查询force index只使用 where。查询不应该相同吗？为什么使用强制索引的查询要快得多 - 使用的索引仍然是一样的。

标签： mysqlindexing

Answer 1

根据 MySQL 手册：

可以在以下条件下创建临时表：

如果存在 ORDER BY 子句和不同的 GROUP BY 子句，或者如果 ORDER BY 或 GROUP BY 包含来自连接队列中第一个表以外的表的列，则会创建一个临时表。

DISTINCT 与 ORDER BY 结合可能需要一个临时表。

如果使用 SQL_SMALL_RESULT 选项，MySQL 将使用内存中的临时表，除非查询还包含需要磁盘存储的元素（稍后描述）。

很可能，您有更好的性能，因为在 MySQL 中有查询优化器组件。

如果创建索引，即使索引存在，查询优化器也无法使用索引列。使用force index(..)你强制 MySql 使用索引，而不是。

请在此处考虑一个详细的示例。