MySql Order by isnull() 性能問題

我下面的 sql 用於列出 10 天前添加的股票。使用 isnull(Price) 排序，這樣沒有任何價格的股票仍然會被列出。

AddDate 和 Price 有一個索引。

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
order by isnull(Price), Price asc limit 50

解釋顯示它沒有使用價格索引的 sql。所以我嘗試改進查詢並得出以下 sql

SELECT Id, Price FROM tblStock
where AddDate >= date_sub(curdate(),interval 10 day)
and Price is not null
order by Price asc limit 50

新的 sql 工作得更快，並且解釋顯示它使用 Price 索引，但問題是永遠不會選擇具有 null 值的 Price。

尋找有關如何解決此問題的任何意見或建議。謝謝。

您的原始條款：

order by isnull(Price), Price asc limit 50

在價格列上使用函式。在您的情況下，功能是isnull(); 但沒關係 - 您列上的任何函式，例如COALESCE(Price, ...),Price + 1等，都會產生相同的結果。

您會看到，一旦列上有函式，MySQL 就無法使用該列。考慮：索引使用價格上的 BTREE，按升序索引數百萬行。但是您的請求，order by isnull(Price)請求完全不同的東西：您只想提供NULLs 或不提供NULL；好吧，MySQL 不會為此使用索引。

我想 MySQL 最好能辨識一組與索引單調的“安全”函式；但是 MySQL 在它看到一個函式的那一刻就放棄了一個索引。它只是不能預測函式的結果如何。

其他數據庫支持索引功能；MariaDB fork 支持虛擬列和虛擬列上的索引——這兩種解決方案都可以幫助您完成查詢。普通的 MySQL 吃不下。

我不知道你為什麼有這個複雜ORDER BY的。我認為這就足夠了：

SELECT Id, Price 
FROM tblStock
WHERE AddDate >= date_sub(curdate(), interval 10 day)
ORDER BY Price ASC 
 LIMIT 50 ;

真正的問題是如何優化它。由於它有一個範圍條件（在子句中的列(AddDate)上WHERE和不同列的排序（Price），這真的很難。

啟用索引可能會提高效率，(Price, AddDate, Id)但這確實是在黑暗中嘗試。它有時會很快工作（當價格最低的許多行也有目前日期時），而其他時候它會完全低效（當價格最低的行大多有較舊的日期時，所以幾乎所有或所有索引都必須被掃描，直到找到 50 個匹配項。）

幾個月前我問過一個關於幾乎相同查詢的問題，@Jack Douglas提供了一個很好的解決方案，而@Erwin Brandstetter進一步改進了它：：空間索引可以幫助“範圍 - 按限制排序”查詢

它適用於 Postgres，但我認為可以對其進行修改以在 MySQL 中工作。

---

在我看來，這個NULLS LAST問題不是主要問題。可以通過以下方式解決UNION：

 ( SELECT Id, Price, 1 AS Ord 
   FROM tblStock
   WHERE AddDate >= date_sub(curdate(), interval 10 day)
     AND Price >= 0 
   ORDER BY Price ASC 
     LIMIT 50
 )
UNION ALL
 ( SELECT Id, Price, 2
   FROM tblStock
   WHERE AddDate >= date_sub(curdate(), interval 10 day)
     AND Price IS NULL
   -- ORDER BY Price ASC 
     LIMIT 50 
 ( 
ORDER BY Ord, Price 
 LIMIT 50 ;

雖然第二部分會很有效（使用(Price, AddDate, Id)索引），但第一部分與您的查詢和我的其他建議有相同的問題。

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