mysql - 如何优化以下sql查询？

Question

我在这里所做的是获取重复的预订和用户。此处预订票不能重复打印。如果用户打印一张票据跟踪器表，则该记录会更新。如果用户将同一张票打印两次，则会将其标记为重复。子查询在这里所做的是返回一些标记为重复的预订 ID。

    SELECT t1.id AS res_id,
       t1.tx_id,
       t1.tx_date,
       t1.bx_date,
       t1.method,
       t1.theater_id,
       t1.showtime_id,
       t1.category_id,
       t1.amount,
       t1.fname,
       t1.status,
       t1.mobile,
       u.username,
       t2.*
FROM `reservation` AS t1
INNER JOIN
  ( SELECT *
   FROM `tracker`
   WHERE reservation_id IN
       ( SELECT reservation_id
        FROM `tracker`
        GROUP BY reservation_id HAVING ( METHOD = 1
                                        AND TYPE = 0
                                        AND COUNT(*) > 1 )
        OR ( METHOD = 1
            AND TYPE = 1
            AND COUNT(*) > 1 )
        OR ( METHOD = 2
            AND TYPE = 2
            AND COUNT(*) > 0 )
        OR ( METHOD = 3
            AND TYPE = 0
            AND COUNT(*) > 0 )
        OR ( METHOD = 3
            AND TYPE = 1
            AND COUNT(*) > 1 )
        OR ( METHOD = 3
            AND TYPE = 3
            AND COUNT(*) > 0 )) ) AS t2 ON t1.id = t2.reservation_id
INNER JOIN `users` AS u ON u.id = t2.user_id
WHERE t2.resolved = 0
  AND t2.duplicate = 1
ORDER BY t2.issue_date DESC, t1.id DESC

EXPLAIN 上述查询的命令。

我该怎么办？如果我是索引，我应该使用哪些键？我如何决定索引哪些键？我知道子查询会使我变慢我应该遵循什么程序来消除缓慢？

Answer 1

在 MySQL 中，exists子查询通常比 in 快子查询。您可以尝试:

SELECT t1.id AS res_id, t1.tx_id, t1.tx_date, t1.bx_date,t1.method, t1.theater_id, t1.showtime_id,
       t1.category_id, t1.amount, t1.fname, t1.status, t1.mobile, u.username, t2.*
FROM `reservation` t1 INNER JOIN
     (SELECT *
      FROM `tracker` t
      WHERE EXISTS (SELECT 1
                    FROM `tracker` t3
                    where t3.reservation_id = t.reservation_id
                    GROUP BY reservation_id
                    HAVING (METHOD = 1 AND TYPE = 0 AND COUNT(*) > 1) OR
                           (METHOD = 1 AND TYPE = 1 AND COUNT(*) > 1) OR
                           (METHOD = 2 AND TYPE = 2 AND COUNT(*) > 0) OR
                           (METHOD = 3 AND TYPE = 0 AND COUNT(*) > 0) OR
                           (METHOD = 3 AND TYPE = 1 AND COUNT(*) > 1) OR
                           (METHOD = 3 AND TYPE = 3 AND COUNT(*) > 0)
                   )
     ) t2
     ON t1.id = t2.reservation_id INNER JOIN
     `users` AS u ON u.id = t2.user_id
WHERE t2.resolved = 0 AND t2.duplicate = 1
ORDER BY t2.issue_date DESC, t1.id DESC

我注意到子查询在 having 中使用隐藏列条款。它可能没有按照您的预期进行。通常，查询将包括 method和 type在group by子句或具有类似 max(Method) 的表达式.