使用内连接和排序优化 SQL 查询

Question

我正在尝试优化以下查询，但没有成功。知道在哪里可以对其进行索引以防止临时表和文件排序吗？

EXPLAIN SELECT SQL_NO_CACHE `groups`.*
FROM `groups`
INNER JOIN `memberships` ON `groups`.id = `memberships`.group_id
WHERE ((`memberships`.user_id = 1) 
  AND (`memberships`.`status_code` = 1 AND `memberships`.`manager` = 0))
ORDER BY groups.created_at DESC LIMIT 5;`

+----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+
| id | select_type | table       | type   | possible_keys            | key     | key_len | ref                                         | rows | Extra                                        |
+----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | memberships | ref    | grp_usr,grp,usr,grp_mngr | usr     | 5       | const                                       |    5 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | groups      | eq_ref | PRIMARY                  | PRIMARY | 4       | sportspool_development.memberships.group_id |    1 |                                              | 
+----+-------------+-------------+--------+--------------------------+---------+---------+---------------------------------------------+------+----------------------------------------------+
2 rows in set (0.00 sec)


    +--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name                          | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| groups |          0 | PRIMARY                           |            1 | id              | A         |           6 |     NULL | NULL   |      | BTREE      |         | 
| groups |          1 | index_groups_on_name              |            1 | name            | A         |           6 |     NULL | NULL   | YES  | BTREE      |         | 
| groups |          1 | index_groups_on_privacy_setting   |            1 | privacy_setting | A         |           6 |     NULL | NULL   | YES  | BTREE      |         | 
| groups |          1 | index_groups_on_created_at        |            1 | created_at      | A         |           6 |     NULL | NULL   | YES  | BTREE      |         | 
| groups |          1 | index_groups_on_id_and_created_at |            1 | id              | A         |           6 |     NULL | NULL   |      | BTREE      |         | 
| groups |          1 | index_groups_on_id_and_created_at |            2 | created_at      | A         |           6 |     NULL | NULL   | YES  | BTREE      |         | 
+--------+------------+-----------------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+


     +-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table       | Non_unique | Key_name                                                 | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| memberships |          0 | PRIMARY                                                  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         | 
| memberships |          0 | grp_usr                                                  |            1 | group_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          0 | grp_usr                                                  |            2 | user_id     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | grp                                                      |            1 | group_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | usr                                                      |            1 | user_id     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | grp_mngr                                                 |            1 | group_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | grp_mngr                                                 |            2 | manager     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | complex_index                                            |            1 | group_id    | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | complex_index                                            |            2 | user_id     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | complex_index                                            |            3 | status_code | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | complex_index                                            |            4 | manager     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | index_memberships_on_user_id_and_status_code_and_manager |            1 | user_id     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | index_memberships_on_user_id_and_status_code_and_manager |            2 | status_code | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
| memberships |          1 | index_memberships_on_user_id_and_status_code_and_manager |            3 | manager     | A         |           2 |     NULL | NULL   | YES  | BTREE      |         | 
+-------------+------------+----------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Answer 1

• memberships 列 user_id 上的索引（如果是 PK，则应该已有一个）
• memberships 列 status_code 和 上的索引>manager（两者都在同一索引上）
• groups列created_at上的索引（如果可能的话使用默认的DESC，我不知道你是否可以在mySql中）

这就是我在 MS SQL Server 中要做的事情，但我想同样的优化也可以在 mySql 中使用。

Answer 2

您是否拥有您加入的字段、where 子句中的字段以及您排序所依据的created_at 字段上的所有“明显”单列索引？

Answer 3

问题是您需要组索引才能消除文件排序，但所有 where 条件都与成员资格有关。

尝试在 (id,created_at) 上的组上添加索引。

如果这不起作用，请尝试像这样使用子查询来欺骗优化器（在组上保留上述索引）：

SELECT SQL_NO_CACHE `groups`.*
    FROM `groups`
    INNER JOIN (select group_id from `memberships`
       WHERE 
           `memberships`.user_id = 1
           AND `memberships`.`status_code` = 1
           AND `memberships`.`manager` = 0
    ) m on m.group_id=`groups`.id
    ORDER BY groups.created_at DESC LIMIT 5;

至少在memberships.user_id上应该有一个索引，但您也可以从像(user_id)这样的索引中获得一些好处、状态、经理）。我假设 status 和 manager 是没有大范围可能值的标志，因此只要 user_id 上有索引，它就不那么重要。

Answer 4

关于会员资格的（user_id，status_code，manager）（以任何顺序）索引会有所帮助。

避免排序会很困难，因为这样您必须在 groups 表中启动联接，这意味着您不能使用引用 memberships 表的所有（大概是非常有选择性的）where 子句，直到它太晚了。

Answer 5

感谢您发布有关您正在使用的索引的详细信息。

我已经对此进行了测试，并尝试省略一些索引。最重要的索引是memberships.complex_index，它用作覆盖索引。这允许查询通过仅读取索引来获得其结果；它根本不需要读取memberships 的数据行。

groups 上的所有索引都没有任何区别。无论如何，它似乎都使用文件排序。 MySQL 中的文件排序仅仅意味着它正在执行表扫描，在某些情况下，这可能比使用索引成本更低。例如，如果无论如何都需要读取 groups 的每一行来生成查询结果，那么为什么还要使用索引进行不必要的双重查找呢？优化器可以感知这些情况并可以适当地拒绝使用索引。

因此，除了主键索引和complex_index之外，我会放弃所有其他索引，因为它们没有帮助，而且只会增加维护这些表的成本。