在具有范围查询的多列索引中，基数较高的列应该排在第一位吗？

涉及范围时首先使用较高基数列进行索引

考虑下表：

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 ;

使用百万行以及文件时间的高基数和 ext 的较低基数，出现了问题哪个索引更有利：fe 或 ef。

使用 Force Index 和 EXPLAIN 进行分析

使用 FORCE INDEX 测试两个索引，可以看到性能上的明显差异：

-- Forcing the range on filetime first
mysql> 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;

-- Forcing the low-cardinality ext first
mysql> 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;

EXPLAIN 输出表明 ef 明显更快，使用更少的行来检索结果。

使用优化器跟踪进行分析

优化器跟踪证实了 ef 的优越性：

"potential_range_indices": [
    ...
    {
        "index": "fe",
        "usable": true,
        ...
    },
    {
        "index": "ef",
        "usable": true,
        ...
    }
],
"analyzing_range_alternatives": {
    "range_scan_alternatives": [
        {
            "index": "fe",
            "ranges": [
                "2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
            ],
            "cost": 20022,   -- Higher cost
        },
        {
            "index": "ef",
            "ranges": [
                "gif <= ext <= gif AND 2015-01-01 00:00:00 <= filetime < 2015-02-01 00:00:00"
            ],
            "cost": 646.61,  -- Lower cost
        }
    ],
}

跟踪表明 ef可以使用索引的两列，从而实现更高效的搜索。此外，它强调优化器只会检查第一个“范围”列，使得 ext 的基数无关紧要。

结论

根据分析，它是明确在处理涉及多个索引列的范围查询时，列的顺序应该为：

• 将涉及等式测试的列放在前面，无论基数如何。
• 范围内涉及的其他列应放在等式列之后。

这种方法可确保最有效地使用索引，从而获得最佳的查询性能。

以上是在具有范围查询的多列索引中，基数较高的列应该排在第一位吗？的详细内容。更多信息请关注PHP中文网其他相关文章！