mysql 使用group by时会导致索引失效吗 group by能用到索引吗

一、Group By 用法

基本用法：
Group By 可以结合一些聚合函数(count,sum,avg,max,min) 对一列或者多列结果集进行分组。
基本格式如下：
按照学号+最高分，进行分组

select student_number,max(score) from achievement group by student_number;

正常情况下，我们一般使用 select A,B … group By A,B

也就是select 的列，都会在group by 列中出现；

那有没有可能存在以下格式呢？

select A,B,C … group by A，即查询的多个列，但只group by其中一个；

二、数据准备

-- 创建学生表
create table student(
    `student_number` varchar(255) not null comment '学号',
    `student_name` varchar(255) not null comment '学生姓名',
    `student_birth` date not null comment '出生日期',
    `student_sex` varchar(255) not null comment '性别',
    PRIMARY KEY (student_number)
) engine=InnoDB default charset = utf8 comment '学生表';

-- 初始化学生表数据
insert into student(student_number, student_name, student_birth, student_sex)
values ('S0001','猴子','1989-01-01','男'),
       ('S0002','猴子','1990-12-21','女'),
       ('S0003','马云','1991-12-21','男'),
       ('S0004','王思聪','1990-05-20','男');

三、测试

以下测试，基于Mysql 5.7.29

测试一：

-- student_number是主键
select student_number,student_birth from student group by student_number;

测试二：

select student_number,student_birth from student group by student_birth;

报错原因是：only_full_group_by

查了一下mysql官方网站：https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html 意思大概是：查询的字段，在进行分组的时候，都要出现在group by后面。

那为什么第一个测试可以成功？

官方解释是：当group by 后面跟上主键或者不为空唯一索引时，查询是有效的，因为此时的每一笔数据都具有唯一性。

注意:只有 mysql 5.7.5 之后的版本才有启用这个功能。

为了证明官方的这个说法，给student表添加一个student_birth 唯一不为空索引。

ALTER TABLE `student` ADD UNIQUE ( `student_birth` );

然后再执行刚刚上面那个报错的语句

select student_number,student_birth from student group by student_birth;

结果： 果然查询成功了

测试三：

普通索引行不行呢？

-- 删除上面那个唯一索引
drop index student_birth on student;

-- 再添加一个普通索引
alter table `student` add index index_name(student_birth);

再执行一下分组：

select student_number,student_birth from student group by student_birth;

结果：果然普通索引无法执行成功

四、总结

1. 当Group By 跟上主键或者唯一不为空索引时，其虽然能成功执行，但是其中分组效果是失效的或者说无意义，用官方的说法是：每一组都是唯一。
这也就告诫我们，为了分组而使用group by的时候，最好不要用主键或者唯一不为空索引进行分组。

2. Mysql 5.7.5以后的所有版本，都默认添加了only_full_group_by属性

-- 查看属性
SELECT @@sql_mode;

所有查询的字段，在进行分组的时候，都要出现在group by后面，否则就会报错，
但是如果说group by 后面跟上主键或者唯一不为空索引时，可以允许这种查询语句存在，虽然查询结果意义不大。

-- mysql 5.7.5 之前版本，这种语法是正确的，
-- mysql 5.7.5（包括自身）之后的版本是错误的，除非A是主键或者唯一索引
select A,B ... group by A;

3. 当然可以通过关闭这个属性，或者查询语句时使用 any_value() 关键字。
怎么关闭（不推荐），但重启Mysql服务依然还会存在：

set @@GLOBAL.sql_mode='';
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

any_value()：

select student_name,any_value(student_sex) from student group by student_name;