MYSQL视图查询性能问题

Question

我有5个SQL表

• 存储
• 工作人员
• 部门
• SOLD_Items
• staff_rating

我创建了一个视图，将这四个表连接在一起。最后一个表(staff_rating)，我希望在接近项目被出售的时间(sold_items.date)获得视图行的rating列。我尝试了以下SQL查询，这些查询工作正常，但存在性能问题。

SQL查询1

SELECT s.name,
       s.country,
       d.name,
       si.item, 
       si.date, 
       (SELECT rating 
        FROM staff_ratings 
        WHERE staff_id = s.id 
        ORDER BY DATEDIFF(date, si.date) LIMIT 1) AS rating, 
       st.name, 
       st.owner
   FROM store st
   LEFT OUTER JOIN staff s ON s.store_id = st.id
   LFET JOIN departments d ON d.store_id = st.id
   LEFT JOIN sold_items si ON si.store_id = st.id

SQL查询2

SELECT s.name,
       s.country,
       d.name,
       si.item, 
       si.date, 
       si.rating , 
       st.name, 
       st.owner
FROM store st
LEFT OUTER JOIN staff s ON s.store_id = st.id
LFET JOIN departments d ON d.store_id = st.id
LEFT JOIN (SELECT *,
                  (SELECT rating 
                   FROM staff_ratings 
                   WHERE staff_id = si.staff_id 
                   ORDER BY DATEDIFF(date, si.date) LIMIT 1) AS rating 
           FROM sold_items) si ON si.store_id = st.id

SQL查询%2比SQL查询%1快。但这两种方法都存在性能问题。感谢对具有更好性能的查询的帮助。提前道谢。

Answer 1

您的查询在我看来不对（正如在原始帖子的评论中提到的；在销售的连接中缺少staff_id等）

忽略这一点，你最大的表演之一可能是这个...

ORDER BY DATEDIFF(date, si.date) LIMIT 1

该订单只能通过将该员工的每个记录与当前销售记录进行比较来回答。

理想情况下，您希望能够从索引中找到适当的员工评级，而不必运行包含评级表和销售表中的日期的计算。

例如，如果您想要“出售前的最新评级”，则查询可以得到实质性的改进...

SELECT
   s.name,
   s.country,
   d.name,
   si.item, 
   si.date, 
   (
     SELECT sr.rating 
       FROM staff_ratings sr
      WHERE sr.staff_id = s.id
        AND sr.date <= si.date
   ORDER BY sr.date DESC
      LIMIT 1
   )
    AS rating, 
   st.name, 
   st.owner
FROM store st
LEFT JOIN staff s ON s.store_id = st.id
LFET JOIN departments d ON d.store_id = st.id
LEFT JOIN sold_items si ON si.store_id = st.id

然后，通过staff_ratings(staff_id,date,rating)的索引，优化器可以非常快速地查找要使用的评级，而不必扫描该工作人员的每个评级。