為什麼在 MySQL 中批量多列鍵查詢這麼慢？

（對於這個問題，我將 AWS/Aurora MySQL 與合理規格的 RDS 實例一起使用）

考慮以下架構：

Table T:
   col0: the usual autoincrement primary key
   col1: varchar
   col2: varchar
   col3: varchar
   col4...N: various data

考慮有一個唯一索引：

<col1, col2, col3>

以及關於以下內容的非唯一索引：

<col1, col2>

並考慮以下查詢：

SELECT * FROM T
WHERE
   (col1 = 'val1' AND col2 = 'id1') OR
   (col1 = 'val2' AND col2 = 'id2') OR
   ...
   (col1 = 'valN' AND col2 = 'idN');

我會（也許天真地）期望 MySQL 找出 OR 集的每個元素都匹配（非唯一）索引，並以我說過的方式執行查詢：

WHERE col0 in (v1, v2, ... , vN)

但它似乎沒有這樣做：這兩個查詢的時間是 WAY OFF，對於“or of ands”查詢慢了 10 倍。即使使用輔助鍵查找，而且它是字元串列查找，10x 似乎有點嚴重。請注意，無論我指定(col1, col2)還是(col1, col2, col3)，EXPLAIN 都聲稱使用正確/預期的索引

另請注意：

SELECT * from T
WHERE
   col1 in (list1)
AND
   col2 in (list2);

當list1和list2中有很多不同的值時也很慢。對三列執行“和”幾乎是非常緩慢的。

也許不足為奇的是，當list1的長度為 1 時，此查詢比 “or of ands” 效果更好。

使用“行建構子”，您可能會得到優化：

WHERE (col1, col2) IN (('v1', 'id1'), ('v2', 'id2'), ...)

但是……在舊版本中，這會起作用，但會導致表掃描。我無法具體說明您正在執行的版本。

當您擁有這對索引時：

UNIQUE(col1, col2, col3)  -- (or plain INDEX)
INDEX(col1, col2)

不需要後者，因為前者可以處理任何需要它的查詢。

也許編寫查詢的最佳方式是

WHERE col1 in ('v1', 'v2', ...)
 AND (col1, col2) IN (('v1', 'id1'), ('v2', 'id2'), ...)

這樣，它將使用任何以開頭的索引col1作為粗過濾器，然後將另一部分用於其餘的過濾。

重新“轉換為 in 方法”——MySQL 最初是一個乾淨而平均的數據庫；它完成了任何人需要的大部分工作，並且做得相當好。那是開發的 90%。我們現在進入了另外 90% 的開發階段——“長尾”。很可能某處的某些列表包括“轉換為 in 方法”。如果是這樣，它將與成千上萬其他罕見和模糊的優化一起被優先考慮。隨時在 bugs.mysql.com 送出“功能請求”；這就是將其添加到列表中的方法，或者將其提高優先級。

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