高基數與低基數：範圍索引哪一列應該排在第一位？

範圍索引中的高基數列排序

在資料庫設計中，當在多個列上建立索引時，這些列的順序可以顯著影響範圍查詢的效能。特別是，對於同時涉及高基數列和低基數列的查詢，索引中的最佳列排序可能是違反直覺的。

場景：

考慮具有以下架構的表：

CREATE TABLE `files` (
  `did` int(10) unsigned NOT NULL DEFAULT '0',
  `filename` varbinary(200) NOT NULL,
  `ext` varbinary(5) DEFAULT NULL,
  `fsize` double DEFAULT NULL,
  `filetime` datetime DEFAULT NULL,
  PRIMARY KEY (`did`,`filename`),
  KEY `fe` (`filetime`,`ext`),          -- This?
  KEY `ef` (`ext`,`filetime`)           -- or This?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

該表存儲文件元數據，大約有 100 萬行。 filetime 列大部分具有不同的值，表示高基數，而 ext 列具有有限數量的值，表示低基數。

查詢：

以下查詢用於根據ext 和filetime 擷取檔案資訊：

WHERE ext = '...'
  AND filetime BETWEEN ... AND ...

最佳索引選擇：

問題是哪個索引fe 或ef 比較適合此查詢。直觀上，人們可能會認為首先具有高基數列（文件時間）的索引會更有效。然而，分析顯示事實恰恰相反。

解釋：

使用EXPLAIN 命令，我們可以檢查不同索引選擇的查詢計劃：

-- Force index on fe (filetime first)
EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(fe)
WHERE ext = 'gif' AND filetime >= '2015-01-01' AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

-- Force index on ef (ext first)
EXPLAIN SELECT COUNT(*), AVG(fsize)
FROM files FORCE INDEX(ef)
WHERE ext = 'gif' AND filetime >= '2015-01-01' AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

結果表明，低基數ext 列在前的索引ef的性能明顯優於fe.

使用優化器追蹤的進一步分析表明，使用 fe 索引，優化器估計會掃描大量行 (16684) 以過濾 ext 值。透過 ef 索引，它可以有效地使用兩個索引列並僅檢索相關行 (538)。

結論：

建立複合索引以支援範圍時查詢時，建議首先放置參與相等性測試的列（在本例中為ext），無論其基數如何。這使得索引可以在查詢執行計劃中更有效地使用，從而提高效能。

以上是高基數與低基數：範圍索引哪一列應該排在第一位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！