关于group by 的一点思考
2017-12-02
示例
CREATE TABLE `tb` (
`c` int(11) DEFAULT NULL,
`d` int(4) DEFAULT NULL,
`e` varchar(1000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Insert into tb values(2,20,’b’);
Insert into tb values(1,10,’a’);
Insert into tb values(2,20,’b’);
Insert into tb values(1,10,’a’);
Insert into tb values(3,30,’c’);
查询现象
explain select c, count(*) from tb group by c having count(d)>=0 ;
Extra Using temporary; Using filesort
explain select c, count(*) from tb group by c having count(d)>=0 order by null;
Extra Using temporary
结论
排序应该是分组之后才进行的。
算法分析：
1 建立一个空的临时表，三个字段分别为索引列、c、count(*)
这里的索引列就是group by后的列计算结果。
2 从原表中一行行读入，先计算索引列的值key。用key在临时表中查找，若key行存在，则update，否则insert。
在这个例子中，第一次读到c=2的行，则向临时表中插入一行(2, 2, 1)，第二次碰到c=2的行，则修改为(2, 2, 2)。
3 原表全部遍历完成后，分组就结束了。因此我们看到加了order by null的查询结果c的出现顺序是2, 1, 3，没有order by null的则是在该基础上进行了排序。
临时表中的查询就是简单的hash查找，我们看到这个算法的分组过程时间负责度为O(n)。
select 没有在group by后的列报错(5.7)
报错信息如下：
mysql> select * from tb group by c;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tb.d' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
上述报错是由于在sql_mode中设置了ONLY_FULL_GROUP_BY。
select @@sql_mode;
set global sql_mode = '';