MySQL principle and optimization of Group By optimization techniques

​

今天来看看MySQL how muchGroup By 语句进行优化的.

先创建tb_user 表如下

通过show index from tb_user; 命令查看表,No index exists.

执行如下代码,查看SQL 执行情况

explain select profession, count(*) from tb_user group by profession ;

发现返回结果中 type 为“ALL” ,Extra 返回“Using temporary” 说明没有使用索引.

于是,创建基于profession,age和status 的索引如下

create index index_user_pro_age_sta on tb_user(profession ,age, status);

Here the order of creating indexes from left to right is profession ,age, status.

此时再次执行SQL执行计划如下：

explain select profession, count(*) from tb_user group by profession ;

found that the index was used“index_user_pro_age_sta”.说明在执行 group by操作的时候,Using a federated index is valid.

Then look at using the following code：

explain select age, count(*) from tb_user group by age;

SQL 语句使用age 进行group by,查看explain的结果如下：

在Extra Field found used“Using temporary”,说明没有走索引,It is because the index is not satisfied最左前缀法则.

联合索引 index_user_pro_age_staThe order is from left to right respectively profession ,age, status.

上面的SQL 语句Group by 后面接着的是age ,因此出现“Using temporary”.

这里对SQL 进行修改.如下：

explain select profession,age, count(*) from tb_user group by profession, age;

由于group by 后面跟着profession, age ,Consistent with the order in which the federated index was created,So the index works.

Let's try again and add the filter condition,加入profession = 软件工程,此时group by 里面只显示 age,Then whether the index will be taken at this time, 答案是 using index.Because the leftmost prefix rule is satisfied.

explain select age, count(*) from tb_user where profession = '软件工程' group by age;

总结一下：

SQLwhen operating in groups,可以通过索引来提高效率.

When doing group operations,The use of indexes needs to satisfy the leftmost prefix rule.