mysql - 我怎样才能使这个查询更有效率？

Question

编辑:这是原始查询的简化版本(在 475K 行的产品表上运行 3.6 秒)

SELECT p.*, shop FROM products p JOIN
users u ON p.date >= u.prior_login and u.user_id = 22 JOIN
shops s ON p.shop_id = s.shop_id
ORDER BY shop, date, product_id;

这是解释计划

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  u   const   PRIMARY,prior_login,user_id PRIMARY 4   const   1   Using temporary; Using filesort
1   SIMPLE  s   ALL PRIMARY NULL    NULL    NULL    90   
1   SIMPLE  p   ref shop_id,date,shop_id_2,shop_id_3    shop_id 4   bitt3n_minxa.s.shop_id  5338    Using where

瓶颈似乎是ORDER BY date,product_id。删除这两个排序，查询运行时间为 0.06 秒。 (删除两者中的任何一个(但不是同时删除两者)实际上没有任何效果，查询仍然需要 3 秒以上。)我在产品表中的 product_id 和 date 都有索引。我还在 (product,date) 上添加了一个索引，但没有任何改进。

newtover 表明问题在于 INNER JOIN users u1 ON products.date >= u1.prior_login 要求阻止在 products.date 上使用索引

有人向我建议了在 ~0.006 秒内执行的查询的两个变体(而不是原来的 3.6 秒)(不是来自此线程)。

这个使用子查询，它似乎强制连接的顺序

SELECT p.*, shop 
  FROM 
  (
    SELECT p.*
    FROM products p 
    WHERE p.date >= (select prior_login FROM users where user_id = 22)
  ) as p
  JOIN shops s 
    ON p.shop_id = s.shop_id
  ORDER BY shop, date, product_id;

这个使用 WHERE 子句做同样的事情(尽管 SQL_SMALL_RESULT 的存在不会改变执行时间，没有它也是 0.006 秒)

SELECT SQL_SMALL_RESULT p . * , shop
FROM products p
INNER JOIN shops s ON p.shop_id = s.shop_id
WHERE p.date >= ( 
SELECT prior_login
FROM users
WHERE user_id =22 ) 
ORDER BY shop, DATE, product_id;

我的理解是，由于在将产品表加入商店表之前减少了产品表的相关行数，这些查询的运行速度要快得多。我想知道这是否正确。

Answer 1

使用EXPLAIN 语句查看执行计划。您也可以尝试向 products.date 和 u1.prior_login 添加索引。

另外请确保您已经定义了外键并且它们已编入索引。

祝你好运。