mysql - 我怎样才能用一个大的 IN 列表、大的 LIMIT 偏移量和 ORDER BY 来提高这个 MySQL 查询速度

Question

SELECT 
mp_library.`lid` AS itemId, 
mp_library.`cid` AS itemCategory, 
mp_library.`photo` AS itemPhoto, 
mpl_names.`name` AS itemName, 
mp_users.`uid` AS userId 
FROM mvc_public_library AS mp_library 
INNER JOIN mvc_public_library_names AS mpl_names ON mpl_names.lid = mp_library.lid 
LEFT JOIN mvc_public_wishlist AS mp_wishlist ON mp_wishlist.lid = mp_library.lid AND 
          mp_wishlist.uid = 31 AND mp_wishlist.status = "1" 
LEFT JOIN mvc_public_users AS mp_users ON mp_users.uid = mp_wishlist.uid AND 
          mp_users. STATUS = "2" 
WHERE 
      mp_library.status = "1" AND 
      mpl_names.language = "en" AND 
      mp_library.cid IN (3, 9967, 4, 20, 5, 9950, 6, 9971, 7, 9979, 12, 271, 272, 13, 2502, 2503, 15, 9925, 630, 778, ... , 3184 ) 
ORDER BY mp_library.lid DESC 
LIMIT 268320, 48

上面的查询在 IN (...) 部分中有 1000 到 3000 个 ID。

EXPLAIN 语句输出:

在所有连接的表中我都设置了索引。查询的持续时间大约为 8 秒，这是一个非常长的时间。如果我消除 JOINS 并只运行 SELECT * FROM mvc_public_library WHERE cid IN (...) 查询需要 3 秒，同样是很长的时间。

您有什么建议可以减少查询时间吗？我在其他帖子中注意到，大的 IN 列表是一个问题，大的 LIMIT 偏移量是一个问题，ORDER BY 是一个问题，这里我有所有 3 :)

@稍后编辑

我设法将查询减少到这个(没有列表):

SELECT mp_library.`lid` AS itemId,mp_library.`cid` AS itemCategory,mp_library.`photo` AS itemPhoto,mpl_names.`name` AS itemName,mp_users.`uid` AS userId 
FROM mvc_public_library AS mp_library 
INNER JOIN mvc_public_library_names AS mpl_names ON mpl_names.lid = mp_library.lid 
LEFT JOIN mvc_public_wishlist AS mp_wishlist ON mp_wishlist.lid = mp_library.lid AND 
          mp_wishlist.uid = 31 AND 
          mp_wishlist.status = "1" 
LEFT JOIN mvc_public_users AS mp_users ON mp_users.uid = mp_wishlist.uid AND 
          mp_users.status = "2" 
WHERE 1 AND mp_library.status = "1" AND 
            mpl_names.language = "ro" 
ORDER BY mp_library.lid DESC 
LIMIT 268320,48

我还添加了@DRapp 建议的索引，并将时间从 8 秒缩短到 5 秒。我想知道它是否可以进一步改进。这是新查询的解释语句:

Answer 1

我怀疑问题的关键在于您需要在 mp_library(status, cid) 上建立索引。事实上，以下索引将覆盖查询:

create index on mp_library(status, cid, lid, photo)