MySQL查询优化[加入]

Question

我有下一个问题

SELECT 
i.*,
gu.*
vs.*
FROM 
common.global_users gu
LEFT JOIN common.global_users_perms gup ON (gu.global_user_id=gup.global_user_id)
LEFT JOIN p.individuals i ON (gup.parent_id = i.member_id)
LEFT JOIN p.vs ON (i.member_id=vs.member_id AND vs.status IN (1,4))
WHERE gup.region = 'us'
AND gu.user_type=2
AND ((gu.email='specific@email.com') OR (i.email='specific@email.com') OR (gu.username='specific@email.com'))
ORDER BY i.status, vs.member_id;

和下一个计划

+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------------------+--------+----------+---------------------------------+
| id | select_type | table | type   | possible_keys                            | key              | key_len | ref                         | rows   | filtered | Extra                           |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------------------+--------+----------+---------------------------------+
|  1 | SIMPLE      | gu    | ref    | PRIMARY,username,idx_user_type,idx_email | idx_user_type    | 1       | const                       | 524243 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | gup   | ref    | global_user_id_2                         | global_user_id_2 | 4       | common.gu.global_user_id    |      1 |   100.00 | Using where                     |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY                                  | PRIMARY          | 4       | common.gup.parent_id        |      1 |   100.00 | Using where                     |
|  1 | SIMPLE      |  vs   | ref    | member_id,status                         | member_id        | 4       | p.i.member_id               |      1 |   100.00 |                                 |
+----+-------------+-------+--------+------------------------------------------+------------------+---------+-----------------------------+--------+----------+---------------------------------+

有没有可能让它更快？现在大约需要 12 秒

附言在 gu 表中只有两个唯一的 type 值。并且该表中的所有记录数均超过 1M。

Answer 1

您的计划结果可能表明“使用临时；使用 filesort' 意味着排序是在磁盘上完成的，可能是因为它太大而无法放入内存。如果可能，您可能希望增加内存限制。

这也可能与您的排序依据的列有关。如果你删除你的 ORDER BY 查询执行得更快吗？如果是这种情况，您可能需要查看 ORDER BY Optimization