Mysql去重使用distinct还是group by分析需求

准备数据

首先准备数据库数据

CREATE TABLE `test_test` (
	`id` INT ( 11 ) NOT NULL auto_increment,
	`num` INT ( 11 ) NOT NULL DEFAULT '0',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;

准备存储过程插入10万数据

CREATE DEFINER=`root`@`%` PROCEDURE `p_test`(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_test;
	while i< pa do
	if max_num < 100000 then
	select cast( rand()* 100 AS signed ) into rand_num;
	insert into test_test(num) values(rand_num);
	end if;
	set i = i+1;
	end while;
END

使用mysql自带的分析函数

show variables like '%profiling%';
set profiling=1;(开启profiling)
执行sql查询语句
show  profiles; 

给num字段添加索引