记一次MySQL Group by 的坑

记一次MySQL Group by 的坑

表结构如下：

CREATE TABLE `tableA` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column1` varchar(32) DEFAULT NULL,
  `column2` varchar(32) DEFAULT NULL,
  `column3` varchar(32) DEFAULT NULL,
  `column4` varchar(10) DEFAULT NULL,
  `column5` varchar(10) DEFAULT NULL,
  `column6` varchar(32) DEFAULT NULL,
  `column7` varchar(32) DEFAULT NULL,
  `column8` datetime DEFAULT null,
  `column9` varchar(32) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `column1_column2_index` (`column1`,`column2`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

业务需要将表中重复数据删除，所以需要按照 组合唯一索引键筛选出重复的数据进行删除。SQL如下：

select column2  from am_cm_relationship WHERE column9 = 0
group by column1,column2,column3,column4   HAVING num>1;

表中有符合索引 KEY column1_column2_index (column1,column2)

sql语句 Group by 也是按照最左匹配原则顺序写的 group by 的字段，但是每次执行SQL耗时都是好几十秒

explain 该 sql 发现，并没有走表中存在的复合索引，而是直接走的 File sorted（文件排序）；group by 语句其实是有要先排序再分组的；

问题的关键定位到没有没有命中表中的复合索引，那为何 group by 字段前两个就是复合索引，只是最后两个不是，为何没有走索引呢？不是索引只要满足最左匹配原则就可以命中吗？

分析后发现，索引可以用在两个地方，1 被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。2 快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

本处就是可以使用索引做排序使用，而避免文件排序；此处要命中索引，走索引排序，必须要表中有一个复合索引包含 group by 的所有字段且顺序一致；

网上有部分博客说 group by 自带的排序和 order by 排序，走不走索引的规则是一样的，这里本人测试了一下，添加 group by 后面所有顺序字段的复合索引对 group by 的查询时间有直接的影响，从 30多秒 优化到 3秒；

但是对如下SQL 的执行时间也有影响，但是远远没有对group by 的影响大，如下sql，添加 order by 的全索引后 只能从30多秒优化到 10 多秒

select column2 from am_cm_relationship order by column1,column2,column3,column4 ;