用于分组依据/排序依据的 MySQL 索引

Question

请参阅下面的查询。我应该在表上创建什么索引，以便查询将使用索引并避免使用临时和文件排序？我尝试了许多不同的索引组合并阅读了 advice here ，但我似乎无法弄清楚。我的解释要么说Using Where(无索引)，要么Using Where Using Temporary, Using Filesort

这是一个简化的查询。所有列都是整数。

SELECT c1, Sum(c2) 
FROM table 
WHERE c3 IS NOT NULL 
AND c4 = 2011 
AND c5 = 0 
AND c6 In (6,9,11)  
GROUP BY c1

Answer 1

这应该对你有帮助。按如下方式重写您的查询:

SELECT c1, Sum(c2) 
FROM table 
WHERE c4 = 2011 
AND c5 = 0 
AND c6 In (6,9,11)
AND c3 IS NOT NULL   
GROUP BY c1

现在在列(c4、c5、c6)上创建一个复合索引，列按顺序排列。索引中的列应该以与 WHERE 子句中的列相同的顺序出现。否则索引将不起作用。该索引的选择性足够窄，临时表上的文件排序(对于分组依据)应该很快。

将 c3 移动到查询末尾的原因如下。例如，让我们假设 c3 可以取 0 到 100 之间的值(或者可以为 NULL)。如果运行“IS NOT NULL”查询，那么Mysql需要遍历几乎所有的B-Tree索引，除了对应NULL的边。因此，MySQL 决定全表扫描比遍历索引中的所有不同路径更容易。另一方面，您会看到，如果您的查询是“IS NULL”并且您的索引是 (c3, c4, c5, c6)，那么 Mysql 实际上会使用该索引。这是因为此时Mysql只需要遍历索引树中NULL值对应的部分即可。

MySQL 需要的索引类型在很大程度上取决于所讨论的查询。正如@louis 建议的那样，在所有列上创建索引不是一个好主意!