高基数与低基数：范围索引中哪一列应该排在第一位？

范围索引中的高基数列排序

在数据库设计中，当在多个列上创建索引时，这些列的顺序可以显着影响范围查询的性能。特别是，对于同时涉及高基数列和低基数列的查询，索引中的最佳列排序可能是违反直觉的。

场景：

考虑具有以下架构的表：

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中文网其他相关文章！