记一次SQL优化

INDEX：The most important preconditions for using indexes for GROUP BY are that all GROUP BY columns reference attributes from the same index, and that the index stores its keys in order

写了这么一条SQL

EXPLAIN SELECT UserId, Id, count(UserId) Num
FROM m_periodmaterial
WHERE UserId IN (?) AND Id IN (?)
GROUP BY UserId, Id

然而， EXPLAIN出来的结果却是Using where; Using index; Using temporary; Using filesort。

Using temporary 出现原因

先来探寻一下GROUP BY出现Using temporary的原因， MySQL文档关于GROUP BY的部分：

The most general way to satisfy a GROUP BY clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any). In some cases, MySQL is able to do much better than that and avoid creation of temporary tables by using index access.
The most important preconditions for using indexes for GROUP BY are that all GROUP BY columns reference attributes from the same index, and that the index stores its keys in order (as is true, for example, for a BTREE index, but not for a HASH index).

简单就是说：GROUP BY会全表扫描，并创建临时表，然后在临时表中使用聚合函数得出结果。但是如果GROUP BY所使用的列是索引的话（顺序需要和索引相同），可以通过索引来完成GROUP BY，不需要建立临时表。
然而我们的SQL语句UserId和Id都是主键，按理说不应该出现Using temporary。

又查看了MySQL文档关于的临时表的部分：有这么一种情况和我们的情况比较接近：

Evaluation of statements that contain an ORDER BY clause and a different GROUP BY clause, or for which the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue.

需要知道的是，GROUP BY会自动使用GROUP BY所使用的列来帮你把结果排序。但是，UserId和Id都是主键，按理说不可能啊！

结果

GROUP BY多个列的时候，应该使用逗号分割，而不是AND！！！！

其它

这次这个小小的失误，让我发现了MySQL文档的强大。解决BUG的过程中不仅仅清楚了Using temporary和Using filesort的各种情况，还学到了：

1. GROUP BY会自动帮你排序，想要不排序需要使用ORDER BY NULL；
2. Using filesort是指MySQL没有使用索引来排序。