mysql - 使用 ORDER BY 避免 "Using temporary"和 "Using filesort"

Question

抱歉，但在浏览了几乎所有帖子和有关它的问题之后，我仍然无法在一个简单的查询中摆脱“使用临时”和“使用文件排序”。我知道这是键的问题，但我找不到正确的组合...

我也不知道优化器定义的连接顺序是否正确，我使用 STRAIGHT_JOIN 测试了其他顺序但没有更好的...使用 ORDER BY 查询非常慢，但没有它真的很快当然没有“使用临时”和“使用文件排序”! (点表中有大约 100.000 行)

查询:

SELECT points.id,
points.id_owner,
points.point_title,
points.point_desc,
users.user_id,
users.username
FROM points,
JOIN users ON points.id_owner = users.user_id
JOIN follows ON follows.id_followed = points.id_owner
WHERE points.deleted = 0
AND follows.id_follower = 22
ORDER BY points.id DESC
LIMIT 10

解释:

+----+-------------+---------+--------+---------------+------------+---------+---------------------+------+----------------------------------------------+
| id | select_type | table   | type   | possible_keys | key        | key_len | ref                 | rows | Extra                                        |
+----+-------------+---------+--------+---------------+------------+---------+---------------------+------+----------------------------------------------+
|  1 | SIMPLE      | follows | ref    | FOLLOW_DUO    | FOLLOW_DUO | 4       | const               |    2 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | users   | eq_ref | PRIMARY       | PRIMARY    | 4       | follows.id_followed |    1 |                                              |
|  1 | SIMPLE      | points  | ref    | GETPOINT1     | GETPOINT1  | 5       | users.user_id,const |  460 | Using where                                  |
+----+-------------+---------+--------+---------------+------------+---------+---------------------+------+----------------------------------------------+

这是三个表中的 SHOW INDEX :

SHOW INDEX FROM points
+--------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| Table  | Non_unique | Key_name     | Seq_in_index | Column_name     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+
| points |          0 | PRIMARY      |            1 | id              | A         |       91987 |     NULL | NULL   |      | BTREE      |         |
| points |          0 | GETPOINT1    |            1 | id_owner        | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| points |          0 | GETPOINT1    |            2 | deleted         | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| points |          0 | GETPOINT1    |            3 | id              | A         |       91987 |     NULL | NULL   |      | BTREE      |         |
+--------+------------+--------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+

SHOW INDEX FROM users
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| users |          0 | PRIMARY    |            1 | user_id     | A         |           4 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

SHOW INDEX FROM follows
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| follows |          0 | PRIMARY     |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| follows |          0 | FOLLOW_DUO  |            1 | id_follower | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| follows |          0 | FOLLOW_DUO  |            2 | id_followed | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| follows |          1 | id_follower |            1 | id_follower | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| follows |          1 | id_followed |            1 | id_followed | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

从现在开始我不知道要测试什么来避免“使用临时”和“使用文件排序”......所以如果你对我有想法......提前感谢你的帮助!

Answer 1

看起来有很多行正在从点表中检查。我曾尝试使用以下技巧来避免在我的项目中使用临时表。请执行以下操作并给出解释以查看任何改进:

1. 删除所有名为 'GETPOINT1' 的索引，主键索引除外。
2. 在列 (deleted, id_owner) 上添加覆盖索引。请保持列的顺序如前所述。
3. 如果您仍然没有看到任何改进，请删除上面的索引并按顺序再次添加索引 (id, deleted, id_owner) 和 (deleted, id_owner, id) 列并重试
4. 此外，您可以从 where 子句中删除 follows.id_follower = 22 并将其置于连接条件中，例如 JOIN follows ON follows.id_followed = points.id_owner AND follows.id_follower = 22
5. 请同时在 follows 表中按 (id_follower, id_owner) 添加索引。

我不保证但以上应该可以给你改进。