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

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

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

查询：

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

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

分析

要确定最佳索引，我们可以使用 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'
  AND filetime 
输出显示 INDEX(ext, filetime) (ef) 的行数显着降低，表明扫描效率更高。
Optimizer Trace
为了进一步分析优化器的行为，我们可以使用优化器跟踪：
SELECT explain_format = 'JSON';

SELECT COUNT(*), AVG(fsize)
FROM files
WHERE ext = 'gif'
  AND filetime >= '2015-01-01'
  AND filetime 
跟踪显示优化器选择 INDEX(ext, filetime) 因为它可以使用索引的两列来过滤和获取数据。相比之下，INDEX(filetime, ext)只能使用第一列(filetime)进行过滤。
结论
根据分析，可以得出以下结论绘制：

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