哪种复合索引最适合范围查询：高基数列与低基数列？

范围查询复合索引中的高基数列放置

当使用涉及范围条件的复合索引查询表时，索引中的列可以显着影响性能。

考虑具有主键的表文件(did, filename) 和两个复合索引：INDEX(filetime, ext) 和 INDEX(ext, filetime)。两个索引都包含 filetime 列，该列的基数高于 ext。

查询：

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

需要基于 ext 和 filetime 访问数据。问题出现了：对于这样的查询，哪个索引是最佳的？

分析

要确定最佳索引，我们可以使用 FORCE INDEX 并检查执行计划：

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

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

输出显示 INDEX(ext, filetime) (ef) 的行数明显较低，表明效率更高

优化器跟踪

要进一步分析优化器的行为，我们可以使用优化器跟踪：

SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime < '2015-01-01' + INTERVAL 1 MONTH;

跟踪显示优化器选择INDEX(ext, filetime)，因为它可以使用索引的两列来过滤和获取数据。相比之下，INDEX(filetime, ext)只能使用第一列(filetime)进行过滤。

结论

根据分析，可以得出以下结论绘制：

• 对于范围查询中使用的复合索引，相等谓词涉及的列（本例中为 ext case) 应放在索引定义的第一位。
• 当索引中的列按照它们在 WHERE 子句中的使用顺序进行排序时，查询性能会得到提高。
• 基数单独的因素并不是确定最佳指标的决定性因素。在范围列具有较高基数但等式列涉及等式谓词的场景中，将等式列放在第一位会产生更好的性能。

以上是哪种复合索引最适合范围查询：高基数列与低基数列？的详细内容。更多信息请关注PHP中文网其他相关文章！