Mysql

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

  • September 27, 2012

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

每個數據源都有一個表。

對於日誌查看器,我想

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

所有表都包含一個稱為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