非常簡單的“SELECT * FROM”超時（InnoDB）

大家好，很抱歉可能是一個愚蠢的問題，但我完全眼花繚亂。

首先我不確定它是否與 InnoDB 有關，但我已經在 MariaDB 和 MySQL 上嘗試過，結果是一樣的。

讓我們切入正題：

我在最不可預測的地方遇到了巨大的性能問題。為了最好地說明這一點，這裡有一個例子：

我有一個名為“圖像”的表，它將圖像儲存為 BLOB 以及圖像元數據，如image_nameor DPI。它相對較小（約 14000 行），大多數圖像為 250-1000 KB。在範例中，我將使用LOWER()刪除image_name列上的索引。

SELECT id FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

這是超級快，最壞的幾毫秒，我們得到了結果id = 1。

SELECT * FROM image WHERE id = '1'

這又快了，大部分時間都花在將 BLOB 轉移給我上，這並不奇怪（最後，它是 PRIMARY 鍵）。

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

現在這件事只是拒絕執行。我是認真的。在 95% 的情況下，我會收到超時，並且偶爾會在 10 分鐘左右後執行。

EXPLAIN SELECT顯然告訴我這很簡單SELECT。而且我無法執行SHOW PROFILES，因為請求從未完成，所以我什至想不出我可以從哪裡開始調試它。

首先，您可以嘗試以下幾件事來獲取有關您的案例的查詢的更多資訊：

1. 啟動查詢後，您可以通過執行檢查其狀態

SHOW FULL PROCESSLIST

2. 此外，您可以嘗試通過添加另一個謂詞（由索引覆蓋）在較小的行集上執行查詢where，例如：

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg'

---

至於為什麼查詢可能會很慢，我相信第三個查詢會發生什麼

SELECT * FROM image WHERE LOWER(image_name) = 'img_0209.jpg'

是因為不能使用索引，引擎會進行全表掃描並讀取每一行的所有數據（包括完整的 BLOB 數據），然後才嘗試過濾它們。因此，它可能會通過該查詢讀取幾 GB 的數據。

查看查詢期間從磁碟讀取的數據量可能會給出一些提示。您可以嘗試使用innodb_data_read服務狀態變數：

SET @read_metric = 'innodb_data_read';
SET @read_before = (
 SELECT variable_value
 FROM information_schema.session_status
 WHERE variable_name = @read_metric
);

SELECT * FROM image WHERE id < 500 and LOWER(image_name) = 'img_0209.jpg';

SET @read_after = (
 SELECT variable_value
 FROM information_schema.session_status
 WHERE variable_name = @read_metric
);

SELECT (@read_after - @read_before) / 1024 / 1024;

它應該類似於 BLOB 中的數據大小：

SELECT sum(length(image_data)) / 1024 / 1024 FROM image WHERE id < 500;

---

為了加快查詢速度，您需要盡可能避免訪問 BLOB。幾點建議：

1. 在您的特定情況下，將排序規則更改為忽略大小寫應該會有所幫助，因此image_name索引可用於查找您需要的單行
2. 嘗試使用自加入來分離image_name和 BLOB 訪問

SELECT image_data.image_data
FROM image
JOIN image as image_data on image_data.id = image.id
WHERE LOWER(image.image_name) = 'img_0209.jpg';

3. 如BLOB 類型優化手冊中所述，將 BLOB 移動到僅包含兩個列的單獨表中：id和data

不要在函式 ( LOWER) 中隱藏索引列。在這種情況下，排序規則是任何帶有 的..._ci，表示不區分大小寫。然後你可以簡單地說 say WHERE image_name = 'img_0209.jpg' and have INDEX(image_name)。現在您不必在整個表中搜尋那一行。

BLOB拿到之後你會怎麼處理？（那裡可能還有其他優化。）表中有多少行？（這可能會影響等式。）

具體建議

ALTER TABLE `image`
   MODIFY COLUMN `image_name`
       varchar(80) CHARACTER SET ascii COLLATE ascii_general_ci
       DEFAULT NULL';

並刪除使用LOWER()：

WHERE image_name = 'img_0209.jpg'

您現有的索引可以保持不變（除了它將被重建以處理排序規則的更改）：

UNIQUE KEY `image_name` (`image_name`)

警告：這ALTER可能需要很長時間。

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