mysql - 如何使用组合索引进行昂贵的聚合查询？

Question

我在一个相对较大的表(约 2000 万行)上使用以下查询:

SELECT 
    MAX(`col_1`) 
FROM `table` 
WHERE  
    col_2 = X AND
    col_3 = Y AND
    col_4 = Z

我在 col_2、col_3 和 col_4 列上有一个组合索引，在 col_1 上有一个单独的索引，但该查询仍然比没有 WHERE 部分的同一查询慢多个数量级。

如何使用索引来提高性能？

Answer 1

如 How MySQL Uses Indexes 下所述:

MySQL uses indexes for these operations:

[ deletia ]

• To find the MIN() or MAX() value for a specific indexed column key_col. This is optimized by a preprocessor that checks whether you are using WHERE key_part_N = constant on all key parts that occur before key_col in the index. In this case, MySQL does a single key lookup for each MIN() or MAX() expression and replaces it with a constant. If all expressions are replaced with constants, the query returns at once. For example:

  SELECT MIN(key_part2),MAX(key_part2)FROM tbl_name WHERE key_part1=10;

因此，当您应用过滤器时，MySQL 无法使用您在 col_1 上定义的简单索引来查找 MAX(col_1):它必须扫描所有匹配的行(尽管它可以通过对简单索引进行排序，按 col_1 的降序执行此操作)，如查询的 EXPLAIN 输出所示。

您应该在(col_2, col_3, col_4, col_1)上使用索引。