mysql添加索引,性能对比

首先清空mysql缓存

FLUSH TABLES

-------------------------------------------------------------

不加索引

set @score_min = (select min(score) from stu);

set @score_max = (select max(score) from stu);

select concat_ws("-",start,end) as score_range,count from(

select (score_range1*10+@score_min) as start

,if(@score_max<(score_range1*10+@score_min+9),@score_max,(score_range1*10+@score_min+9)) as end,count from(

select score_range1,sum(score_num) as count from (

select floor((score - @score_min)/10) as score_range1,sum(score_num) as score_num from (

select score,count(1) score_num from stu group by score) a group by score

) as t group by score_range1

) as tt order by score_range1 desc

) as ttt

;

[SQL] set @score_min = (select min(score) from stu);

受影响的行: 0

时间: 0.161s

[SQL]

set @score_max = (select max(score) from stu);

受影响的行: 0

时间: 0.158s

[SQL]

select concat_ws("-",start,end) as score_range,count from(

select (score_range1*10+@score_min) as start

,if(@score_max<(score_range1*10+@score_min+9),@score_max,(score_range1*10+@score_min+9)) as end,count from(

select score_range1,sum(score_num) as count from (

select floor((score - @score_min)/10) as score_range1,sum(score_num) as score_num from (

select score,count(1) score_num from stu group by score) a group by score

) as t group by score_range1

) as tt order by score_range1 desc

) as ttt

;

受影响的行: 0

时间: 0.159s

----------------------------------------------------------------------

//////////////////////////////////////////////////////////////////////////////////

--------------------------------------------------------------------------

加索引

FLUSH TABLES

create index idx_stu_score on stu(score);

set @score_min = (select min(score) from stu);

set @score_max = (select max(score) from stu);

select concat_ws("-",start,end) as score_range,count from(

select (score_range1*10+@score_min) as start

,if(@score_max<(score_range1*10+@score_min+9),@score_max,(score_range1*10+@score_min+9)) as end,count from(

select score_range1,sum(score_num) as count from (

select floor((score - @score_min)/10) as score_range1,sum(score_num) as score_num from (

select score,count(1) score_num from stu group by score) a group by score

) as t group by score_range1

) as tt order by score_range1 desc

) as ttt

;

drop index idx_stu_score on stu;

[SQL] create index idx_stu_score on stu(score);

受影响的行: 0

时间: 0.319s

[SQL]

set @score_min = (select min(score) from stu);

受影响的行: 0

时间: 0.032s

[SQL]

set @score_max = (select max(score) from stu);

受影响的行: 0

时间: 0.000s

[SQL]

select concat_ws("-",start,end) as score_range,count from(

select (score_range1*10+@score_min) as start

,if(@score_max<(score_range1*10+@score_min+9),@score_max,(score_range1*10+@score_min+9)) as end,count from(

select score_range1,sum(score_num) as count from (

select floor((score - @score_min)/10) as score_range1,sum(score_num) as score_num from (

select score,count(1) score_num from stu group by score) a group by score

) as t group by score_range1

) as tt order by score_range1 desc

) as ttt

;

受影响的行: 0

时间: 0.031s

[SQL]

drop index idx_stu_score on stu;

受影响的行: 0

时间: 0.015s