Mysql

MySQL 查詢優化：如果沒有過期則選擇所有重複項，如果一個過期則選擇最新的

  • April 3, 2020

我有這個查詢，它有效，我只是想優化，如果可能的話，這是一個完整的工作範例，您可以在其中對其進行測試

  • 如果兩個項目具有相同的 bundle_id 或 package_id 或 service_id，則它們被視為重複項
  • 如果滿足以下條件，則項目被視為已過期並處於寬限期內(expires + INTERVAL 5 DAY) >= NOW()
  • 一個項目沒有過期，如果expires >=NOW()

我想做以下

  • 如果存在重複，它們都沒有過期，我想全選
  • 如果存在重複，其中一些已過期，而另一些則沒有，我想選擇未過期的項目
  • 如果存在重複，兩個過期都在寬限期內，我想選擇最近的。
  • 我不想選擇過期且不再處於寬限期內的項目
  • 如果某個項目沒有重複項並且處於寬限期或尚未過期，請選擇它。
  • 該查詢應該在 MySQL 5.5 及更高版本上執行，需要此查詢的客戶端正在升級他的數據庫，尚未完成。

我選擇寬限期內或尚未過期的項目，order by expire獲取最新的日期，我LIMIT用來強制group by遵守此問題的訂單。然後我group by用來過濾重複項。然後我補充說，我知道的項目還沒有過期

         SELECT * FROM (
                   SELECT
                   wtb_orders.orderid,
                   wtb_orders.duration,
                   wtb_orders.is_renew,
                   wtb_orders.bundle_id,
                   wtb_orders.service_id,
                   wtb_orders.object_name,
                   wtb_orders.pkg_id_fk,
                   expires,
                   wtb_tokens.disconnect_time
                   FROM wtb_orders
                   JOIN wtb_tokens ON wtb_orders.orderid=wtb_tokens.orderid
                   WHERE
                       wtb_orders.username= `96190000002` AND
                       (
                           (
                               ((expires + INTERVAL 5 DAY) >= NOW())
                               OR
                               (expires=0 AND wtb_tokens.orderid IS NOT NULL)
                           ) OR
                           expires=0
                       )
                   ORDER by expires DESC LIMIT 1000000
               ) as tmp
               GROUP BY
           tmp.pkg_id_fk,
           tmp.bundle_id,
               tmp.service_id
                   UNION
               (
                   SELECT ord.orderid,ord.duration,ord.is_renew,ord.bundle_id,ord.service_id,ord.object_name,ord.pkg_id_fk,tk.expires,tk.disconnect_time FROM wtb_orders AS ord
                   JOIN wtb_tokens AS tk ON ord.orderid=tk.orderid 
                   WHERE tk.username= ``96190000002``
                   AND (((tk.expires >= NOW())
                   OR (tk.expires=0 AND tk.orderid IS NOT NULL))
                   OR tk.expires=0)
               )

我嘗試使用eversql對其進行優化，這在大多數情況下都有幫助，它給了我這個查詢，它沒有執行，我收到一個錯誤，UNION DISTINCT (SELECT wtb_orders.orderid ...你可以在這裡檢查錯誤，請滾動到底部Unrecognized statement near type DISTINCT``tmp.pkg_id_fk' in 'group statement

SELECT
       *
   FROM
       (SELECT
           wtb_orders_orderid,
           wtb_orders_duration,
           wtb_orders_is_renew,
           wtb_orders_bundle_id,
           wtb_orders_service_id,
           wtb_orders_object_name,
           wtb_orders_pkg_id_fk,
           wtb_tokens_expires,
           wtb_tokens_disconn_time
       FROM
           ((SELECT
               wtb_orders.orderid AS wtb_orders_orderid,
               wtb_orders.duration AS wtb_orders_duration,
               wtb_orders.is_renew AS wtb_orders_is_renew,
               wtb_orders.bundle_id AS wtb_orders_bundle_id,
               wtb_orders.service_id AS wtb_orders_service_id,
               wtb_orders.object_name AS wtb_orders_object_name,
               wtb_orders.pkg_id_fk AS wtb_orders_pkg_id_fk,
               wtb_tokens.expires AS wtb_tokens_expires,
               wtb_tokens.disconnect_time AS wtb_tokens_disconn_time
           FROM
               wtb_orders
           JOIN
               wtb_tokens
                   ON wtb_orders.orderid = wtb_tokens.orderid
           WHERE
               wtb_orders.username = '96190000002'
               AND (
                   wtb_tokens.expires = 0
               )
           ORDER BY
               wtb_tokens.expires DESC LIMIT 1000000)
       UNION
       DISTINCT (SELECT
           wtb_orders.orderid AS wtb_orders_orderid,
           wtb_orders.duration AS wtb_orders_duration,
           wtb_orders.is_renew AS wtb_orders_is_renew,
           wtb_orders.bundle_id AS wtb_orders_bundle_id,
           wtb_orders.service_id AS wtb_orders_service_id,
           wtb_orders.object_name AS wtb_orders_object_name,
           wtb_orders.pkg_id_fk AS wtb_orders_pkg_id_fk,
           wtb_tokens.expires AS wtb_tokens_expires,
           wtb_tokens.disconnect_time AS wtb_tokens_disconn_time
       FROM
           wtb_orders
       JOIN
           wtb_tokens
               ON wtb_orders.orderid = wtb_tokens.orderid
       WHERE
           wtb_orders.username = '96190000002'
           AND (((wtb_tokens.expires = 0
           AND wtb_tokens.orderid IS NOT NULL)))
       ORDER BY
           wtb_tokens.expires DESC LIMIT 1000000)
   UNION
   DISTINCT (SELECT
       wtb_orders.orderid AS wtb_orders_orderid,
       wtb_orders.duration AS wtb_orders_duration,
       wtb_orders.is_renew AS wtb_orders_is_renew,
       wtb_orders.bundle_id AS wtb_orders_bundle_id,
       wtb_orders.service_id AS wtb_orders_service_id,
       wtb_orders.object_name AS wtb_orders_object_name,
       wtb_orders.pkg_id_fk AS wtb_orders_pkg_id_fk,
       wtb_tokens.expires AS wtb_tokens_expires,
       wtb_tokens.disconnect_time AS wtb_tokens_disconn_time
   FROM
       wtb_orders
   JOIN
       wtb_tokens
           ON wtb_orders.orderid = wtb_tokens.orderid
   WHERE
       wtb_orders.username = '96190000002'
       AND ((((wtb_tokens.expires + INTERVAL 5 DAY) >= NOW())))
   ORDER BY
       wtb_tokens.expires DESC LIMIT 1000000)
) AS union1
ORDER BY
union1.wtb_tokens_expires DESC LIMIT 1000000) AS tmp
GROUP BY
tmp.pkg_id_fk,
tmp.bundle_id,
tmp.service_id
UNION
(
SELECT
   ord.orderid,
   ord.duration,
   ord.is_renew,
   ord.bundle_id,
   ord.service_id,
   ord.object_name,
   ord.pkg_id_fk,
   tk.expires,
   tk.disconnect_time
FROM
   wtb_orders AS ord
JOIN
   wtb_tokens AS tk
       ON ord.orderid = tk.orderid
WHERE
   tk.username = '96190000002'
   AND (
       tk.expires = 0
   )
)
UNION
DISTINCT (SELECT
ord.orderid,
ord.duration,
ord.is_renew,
ord.bundle_id,
ord.service_id,
ord.object_name,
ord.pkg_id_fk,
tk.expires,
tk.disconnect_time
FROM
wtb_orders AS ord
JOIN
wtb_tokens AS tk
ON ord.orderid = tk.orderid
WHERE
tk.username = '96190000002'
AND (((tk.expires = 0
AND tk.orderid IS NOT NULL))))
UNION
DISTINCT (SELECT
ord.orderid,
ord.duration,
ord.is_renew,
ord.bundle_id,
ord.service_id,
ord.object_name,
ord.pkg_id_fk,
tk.expires,
tk.disconnect_time
FROM
wtb_orders AS ord
JOIN
wtb_tokens AS tk
ON ord.orderid = tk.orderid
WHERE
tk.username = '96190000002'
AND (((tk.expires >= NOW()))))

即使我設法修復了eversql查詢，我也不相信它比我的更好，好吧它刪除了OR並使用了聯合，但我不得不使用 LIMIT強制分組來尊重順序，而不是因為它是一件好事。我確信這是一個不好的做法，但我需要一個解決方案。

優化的查詢使用 2 個限制，因此它創建了 2 個臨時表。

為了性能，不要試圖在一個語句中完成所有操作。

避免使用非 sargable 表達式，例如(expires + INTERVAL 5 DAY) >= NOW(); 而是說(expires >= NOW() - INTERVAL 5 DAY)

可能最好做 3 個查詢來測試 3 個可能的重複（bundle_id等）。

如果可以在bundle_id不查看其他兩個是否有更新的 dup 的情況下選擇最新的過期 dup，那麼就這樣做。那是 3 個單獨的查詢，並在找到一個時停止。如果所有 3 個都找不到 dup，則進入第 4 個查詢 - 查找所有 dup。

如果那不行，那麼這 3 個重複中的一個可能UNION是最佳的：

SELECT
   FROM (
       ( SELECT id FROM ... ) -- find latest bundle_id dup, if any
       UNION DISTINCT
       ( SELECT id FROM ... ) -- find latest ... dup, if any
       UNION DISTINCT
       ( SELECT id FROM ... ) -- find latest ... dup, if any
        ) AS x
   JOIN ... to get other columns
   ORDER BY ...  LIMIT 1  -- Get the latest of the 3 or fewer

由於我在談論多個查詢，因此您需要使用儲存過程以便擁有 IF 語句或使用客戶端程式碼來編排查詢。

IF the above query returned no rows
THEN
   SELECT ... -- all dups
END IF

一旦您以這種方式重組了查詢，我將幫助您優化索引。請提供SHOW CREATE TABLE以便我們查看數據類型、索引等。

（我忽略了有關寬限期的問題；也許這不會使查詢數量增加一倍。）

5.5 可能不會太舊而無法進行優化。

引用自：https://dba.stackexchange.com/questions/262377

相關問答

Mysql

Mysql：是否可以在 CPU 較低的情況下處理 1000 個並發？

  • October 23, 2019
檢視問答
Mysql

MySQL - 如何優化大表查詢中的多個 OR 條件

  • January 19, 2022
檢視問答
Mysql

MySQL - 計劃成本高於磁碟中佔用的塊數

  • December 20, 2021
檢視問答
Mysql

MySQL/MariaDB 中未使 用的列會顯著影響性能嗎？

  • May 17, 2021
檢視問答
Mysql

我應該如何為我的數據庫建模 - 無法決定多個選項

  • January 25, 2021
檢視問答
Mysql

處理數據輸入中的外鍵

  • December 15, 2020
檢視問答