mysql中 distinct 和 group by 的性能对比

建张表

CREATE TABLE `test_a` (
 `id` int(11) NOT NULL auto_increment,
 `num` int(11) NOT NULL default '0',
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

存储过程

create procedure p_test_a(pa int(11))
 begin

  declare max_num int(11) default 100000;
  declare i int default 0;
  declare rand_num int;

  select count(id) into max_num from test_a;

  while i < pa do
          if max_num < 100000 then
                  select cast(rand()*100 as unsigned) into rand_num;
                  insert into test_a(num)values(rand_num);
          end if;
          set i = i +1;
  end while;
 end

生成数据
call p_test_a(100000);

不加索引

set profiling=1;
select distinct num from test_a;
select num from test_a group by num;
show profiles;

加索引

ALTER TABLE `test_a` ADD INDEX `num_index` (`num`) ;

总结

• 加了索引之后 distinct 比没加索引的 distinct 快了 27 倍。
• 加了索引之后 group by 比没加索引的 group by 快了 50 倍。

再来对比 ：distinct 和 group by
不管是加不加索引 group by 都比 distinct 快。
因此使用的时候建议选 group by

MySQL中distinct和group by性能比较[转]