未使用帶有 WHERE 子句和 ORDER BY 的查詢索引

Question

我建立了一個表，如下所示

CREATE TABLE IF NOT EXISTS
`table`
  (
     id          VARCHAR(100) NOT NULL,
     seq_id      BIGINT UNSIGNED NOT NULL,
     in_use      BOOLEAN NOT NULL DEFAULT false,
     scheduled   BOOLEAN NOT NULL DEFAULT false,
     dialed      BOOLEAN NOT NULL DEFAULT false,
     priority    INT UNSIGNED NOT NULL DEFAULT 0,
     data_0      VARCHAR(100) NULL,
     data_1      VARCHAR(100) NULL,
     data_2      VARCHAR(40) NULL,
     data_3      VARCHAR(200) NULL,
     data_4      VARCHAR(10) NULL,
     data_5      DECIMAL(65, 20) NULL,
     data_6      DECIMAL(65, 20) NULL,
     PRIMARY KEY (`id`)
  )

有一個大型查詢根據 where 子句選擇行，然後對結果進行排序。

SELECT id
FROM
`table`
WHERE  ( dialed = false
         AND in_use = false
         AND scheduled = false )
ORDER  BY priority DESC,
          data_6 ASC,
          data_5 DESC,
          data_4 ASC,
          data_3 DESC,
          seq_id
LIMIT  100

我試圖透過執行 EXPLAIN 來找到該查詢的最佳索引。 我創建了一些不同的索引；

1. （已撥打、正在使用、已排程、優先權、data_6、data_5、data_4、data_3、seq_id）
2. （優先權、data_6、data_5、data_4、data_3、seq_id）
3. （已排程、正在使用、已撥打、優先順序、data_6）
4. （預定，使用中）

EXPLAIN 查詢顯示以下內容；

possible_keys: [index1],[index3],[index4]         
key: [index4]
key_len: 2
ref: const, const
rows: 448
filtered: 100.0
Extra: Using index condition; Using where; Using filesort

我很好奇為什麼不使用包含 ORDER BY 列（index1 和 index3）的索引，以及為什麼它選擇僅包含 WHERE 子句列的子集的索引？我認為索引 1 具有查詢的完整列覆蓋率，是理想的選擇。

僅覆寫 ORDER BY 列的索引 (index2) 根本沒有顯示在 possible_keys 中。我在這裡定義索引的順序是否錯誤？

查詢是否可以使用一個索引進行過濾，然後使用另一個索引對結果進行排序？

如您所見，我正在執行 448 行的測試。該查詢可以在更大的表上運行；高達一百萬。對於更大的表，其他索引最終會比索引4 效能更高嗎？

最後，像索引 1 這樣具有許多列的索引是否會因為列數而降低效能？

Answer 1

3種可能：

如果這個組合選擇性“足夠”，那麼它是有用的：INDEX(dialed, in_use, Schedule)。這 3 個的順序並不重要。

如果您使用 MySQL 8.0，那麼這可能很有用（按給定的順序）：

INDEX(priority DESC,
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)

舊版忽略 DESC，使它們不使用 INDEX

#甚至（再一次在 8.0 上）：

INDEX(dialed, in_use, schedule,   -- in any order
      priority DESC,    -- the rest, as specified
      data_6 ASC,
      data_5 DESC,
      data_4 ASC,
      data_3 DESC,
      seq_id)