MySQL：在內部查詢中使用“ORDER BY”優化 UNION

我剛剛建立了一個日誌系統，它由多個具有相同佈局的表組成。

每個數據源都有一個表。

對於日誌查看器，我想

• 聯合所有日誌表，
• 按帳戶過濾它們，
• 添加一個偽列用於辨識源，
• 按時間排序，
• 並限制它們進行分頁。

所有表都包含一個稱為zeitpunkt索引日期/時間列的欄位。

我的第一次嘗試是：

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
'hp' AS source FROM is_log AS l WHERE l.account_id = 730)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730)

ORDER BY zeit DESC LIMIT 10;

優化器不能在此處使用索引，因為兩個表中的所有行都由子查詢返回並在UNION.

我的解決方法如下：

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
'hp' AS source FROM is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)

UNION

(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)

ORDER BY zeit DESC LIMIT 10;

我期待查詢引擎會在這裡使用索引，因為兩個子查詢都應該在 之前進行排序和限制UNION，然後合併和排序行。

我真的以為就是這樣，但是EXPLAIN在查詢上執行告訴我子查詢仍在搜尋兩個表。

EXPLAINing子查詢本身向我展示了所需的優化，但UNIONing它們在一起卻沒有。

我錯過了什麼？

我知道子查詢中的ORDER BY子句在UNION沒有 a 的情況下會被忽略LIMIT，但是有一個限制。

編輯：

實際上，可能還會有沒有account_id條件的查詢。

這些表已經存在並且充滿了數據。根據來源的不同，佈局可能會有所變化，所以我想將它們分開。此外，日誌記錄客戶端使用不同的憑據是有原因的。

我必須在日誌閱讀器和實際表格之間保留一層。

以下是整個查詢和第一個子查詢的執行計劃以及詳細的表佈局：

https://gist.github.com/ca8fc1093cd95b1c6fc0

只是出於好奇，你可以試試這個版本嗎？它可能會欺騙優化器使用子查詢單獨使用的相同索引：

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt AS zeit,
'hp' AS source FROM is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10) 
   AS a

UNION ALL

SELECT *
FROM
(SELECT l.id, l.account_id, l.vnum, l.count, l.preis, l.zeitpunkt,
'ig' AS source FROM ig_is_log AS l WHERE l.account_id = 730
ORDER BY l.zeitpunkt DESC LIMIT 10)
   AS b

ORDER BY zeit DESC LIMIT 10;

---

我仍然認為您可以擁有的最佳索引是化合物(account_id, zeitpunkt)。它將快速生成 10 行，並且不需要任何技巧。

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