分组依据/排序的MySQL索引 [英] MySQL Index for Group By / Order By

问题描述

请参阅下面的查询。我应该在表上创建什么索引，以便查询将使用索引并避免使用临时和filesort？我尝试了许多不同的索引组合并阅读这里的建议，但我似乎无法弄明白。我的解释是说使用Where （无索引），或使用Where临时使用，使用Filesort

See query below. What index should I create on the table so the query will use the index and avoid using temporary and filesort? I've tried many different combinations of indices and read advice here, but I can't seem to figure it out. My explain either says Using Where (no index), or Using Where Using Temporary, Using Filesort

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

Here is a simplified query. All columns are Integers.

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

推荐答案

这应该可以帮到你。重写您的查询，如下所示：

This should help you. Rewrite your query as follows:

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）上创建一个复合索引，其列为IN THAT ORDER。索引中的列应与WHERE子句中的列的顺序相同。否则索引将无法正常工作。这个索引的选择性足够窄，临时表上的文件排序（对于group by）应该很快。

Now create a composite index on columns (c4, c5, c6) with the columns IN THAT ORDER. The columns in your index should appear in the same order as the columns in your WHERE clause. Otherwise the index will not work. The selectivity of this index is narrow enough that a filesort on the temporary table (for the group by) should be fast.

将c3移动到结尾的原因查询如下。作为一个例子，让我们假设c3可以取0到100之间的值（或者它可以是NULL）。如果运行IS NOT NULL查询，则Mysql需要遍历几乎所有B-Tree索引，除了对应于NULL的边。因此，MySQL决定全表扫描比遍历索引中的所有不同路径更容易。另一方面，您将看到如果您的查询是IS NULL并且您的索引是（c3，c4，c5，c6），那么Mysql将使用此索引。这是因为在这种情况下，Mysql只需要遍历对应于NULL值的索引树部分。

The reason to move c3 to the end of the query is the following. As an example, let us assume that c3 can take values between 0 and 100 (or it can be NULL). If you run a "IS NOT NULL" query, then Mysql needs to traverse almost all of the B-Tree index except for the edges that correspond to NULL. Therefore, MySQL decides that a full table scan is an easier option than walking through all the different paths in the index. On the other hand, you will see that if your query was an "IS NULL" and your index was (c3, c4, c5, c6) then Mysql will infact use this index. This is because in this case Mysql only needs to traverse the part of the index tree corresponding to the NULL value.

MySQL需要的索引类型非常依赖于查询有问题。正如@louis建议的那样，在所有列上创建索引不是一个好主意！

The kind of indexes MySQL needs is very much dependent on the query in question. Creating indexes on all the columns, as @louis suggested, is NOT a good idea!

这篇关于分组依据/排序的MySQL索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！