php - 使用 ORDER BY(并建立索引)时 MySQL 查询非常缓慢

Question

我一直在研究，但无法在相当大的表(500k 行)上解决这个缓慢的 ORDER by 子句。

我有以下查询:

SELECT gifts.gift_id, gifts.gift_title, gifts.gift_price, gifts.gift_image, gifts.gift_slug
FROM gifts
LEFT JOIN tags_gifts_occasion_specific AS os ON gifts.gift_id = os.gift_id
LEFT JOIN popularity ON popularity.gift_id = gifts.gift_id
WHERE published = '1'
AND (
os.tag_id IS NULL
)
ORDER BY popularity.popularity DESC , gift_id DESC

它运行速度很快(0.0007秒)，直到添加ORDER BYpopularity.popularity，然后需要4.7秒!

流行度表确实有索引，尽管我知道这与排序无关。我只是不明白为什么运行得这么慢。当我在同一个表中很受欢迎时，速度很快，但现在我已将其移至单独的表中，以提高性能并增加功能。

对此的任何建议都非常感谢。

解释:

1   SIMPLE  gifts   ref     index_published     index_published     1   const   494384  Using where; Using temporary; Using filesort
1   SIMPLE  os  ref     Gift ID     Gift ID     4   gifts.gift_id   1   Using where; Using index; Not exists
1   SIMPLE  popularity  ref     Unique,Index Gift ID    Unique  4   gifts.gift_id   1   

人气表索引:

该表有 3 列，所有列都有自己的索引(gift_id、tag_id、受欢迎度)此搜索中未使用标签 ID三列还有一个UNIQUE索引

Edit    Drop Drop   Unique  BTREE   Yes No  gift_id 26019   A   No  
tag_id  26019   A   No
Edit Edit   Drop Drop   Index Gift ID   BTREE   No  No  gift_id 26019   A   No  
Edit Edit   Drop Drop   Index Tag ID    BTREE   No  No  tag_id  3   A   No  
Edit Edit   Drop Drop   Index Popularity    BTREE   No  No  popularity  351 A   No  

Answer 1

我很好奇当您拥有索引popularity(popularity desc,gift_id)时，此版本的查询如何执行:

select g.gift_id, g.gift_title, g.gift_price, g.gift_image, g.gift_slug, p.popularity
from popularity p join
     gifts g
     on p.gift_id = g.gift_id
where g.published = '1' and
      not exists (select 1
                  from tags_gifts_occasion_specific tgos
                  where tgos.gift_id = p.gift_id
                 )
order by p.popularity DESC, g.gift_id DESC;

这与您的查询并不完全相同，因为不受欢迎的礼物不会包含在结果集中。此版本可能会说服 MySQL 在流行上使用上述索引，其余工作通过索引查找完成。

编辑:

如果您愿意将其作为两个查询来运行，您可以执行上述查询，然后:

select g.gift_id, g.gift_title, g.gift_price, g.gift_image, g.gift_slug
from gifts g
where g.published = '1' and
      not exists (select 1
                  from tags_gifts_occasion_specific tgos
                  where tgos.gift_id = p.gift_id
                 ) and
      not exists (select 1
                  from popularity p
                  where p.gift_id = g.gift_i
order by g.gift_id DESC;

您甚至可以将它们与union all结合起来。我有点不鼓励这样做。它在实践中可能会起作用，但没有明确保证union all的结果来自第一个查询，然后是第二个查询。