【已解决】还有Loose索引吗？

zhangshengdong

GROUP BY的实现，两种是在利用现有索引的情况下来完成group by的

一种是Loose索引实现

另外一种是Tight索引实现

Loose索引实现，执行计划的Extra:Using index for group-by

今天我在mysql 5.5中做测试，发现没有Extra:Using index for group-by。然道mysql5.5中的group by实现修改了？

chinafenghao

group by的实现方式真没太多研究，等待大牛回答。

zhangshengdong

这是我做的例子：表结构，样式数据，索引，以及执行计划

表结构及数据：
CREATE TABLE group_message_2(
  id int(11) not null auto_increment,
  group_id int(11) not null,
  gmt_create datetime not null,
  user_id int(11) not null,
  author varchar(32) not null,
  subject varchar(12 not null,
  primary key (id)
);

insert into group_message_2 values(101,1, '2012-09-09 ',1,'jake','one monkey');
insert into group_message_2 values(102,1, '2012-09-11 ',1,'jake','one monkey');
insert into group_message_2 values(103,1, '2012-09-12 ',1,'jake','one monkey');
insert into group_message_2 values(104,1, '2012-09-13 ',2,'jake','one monkey');
insert into group_message_2 values(105,1, '2012-09-14 ',2,'jake','one monkey');
insert into group_message_2 values(106,2, '2012-09-14 ',2,'jake','one monkey');
insert into group_message_2 values(107,2, '2012-09-16 ',2,'jake','one monkey');
insert into group_message_2 values(108,2, '2012-09-14 ',2,'jake','one monkey');
insert into group_message_2 values(109,3, '2012-09-17 ',1,'jake','one monkey');
insert into group_message_2 values(110,3, '2012-09-16 ',1,'jake','one monkey');
insert into group_message_2 values(111,3, '2012-09-18 ',1,'jake','one monkey');
insert into group_message_2 values(112,3, '2012-09-17 ',1,'jake','one monkey');


创建索引：

Create index idx_gid_uid_gc on group_message_2(group_id,user_id,gmt_create)

查看执行计划：

Explain select user_id,max(gmt_create)
From group_message_2
Where group_id <10
Group by group_id ,user_id\G;

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: group_message_2
         type: index
possible_keys: idx_gid_uid_gc
          key: idx_gid_uid_gc
      key_len: 16
          ref: NULL
         rows: 12
        Extra: Using where; Using index
1 row in set (0.00 sec)


我希望出来的是Using index for group-by,结果是Using index。等待高手

rucypli

什么情况下出现Using index for group-by

cenalulu

select user_id,max(gmt_create)
From group_message_2
Where group_id <10
Group by group_id ,user_id\G;

标红部分导致不能loose scan了。
去掉试试

chunhaicao

简朝阳的那本《Mysql性能调优与架构设计》，我也正在读，刚好看到这，同问！

chunhaicao

Where group_id <10

将条件改为 where group_id = 10 的话
Extra:Using where；Using index for group-by
使用max/min这两个聚合函数满足loose索引扫描