Mysql
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
條件的查詢。這些表已經存在並且充滿了數據。根據來源的不同,佈局可能會有所變化,所以我想將它們分開。此外,日誌記錄客戶端使用不同的憑據是有原因的。
我必須在日誌閱讀器和實際表格之間保留一層。
以下是整個查詢和第一個子查詢的執行計劃以及詳細的表佈局:
只是出於好奇,你可以試試這個版本嗎?它可能會欺騙優化器使用子查詢單獨使用的相同索引:
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 行,並且不需要任何技巧。