今天看了篇帖子,被几个地方转载,但是性能不太好,因为不能评论,所以把优化思路写在这里。
1.确定需求: 根据部门来分组,显示各员工在部门里按薪水排名名次.
2.来创建实例数据:
drop table if exists heyf_t10;
create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2) );insert into heyf_t10 values(1,10,5500.00),(2,10,4500.00),(3,20,1900.00),(4,20,4800.00),(5,40,6500.00),(6,40,14500.00),(7,40,44500.00),(8,50,6500.00),(9,50,7500.00);
3. http://www.kaishixue.com/mysql/14.html 帖子中SQL的实现
select empid,deptid,salary,rank
from(select heyf_tmp.empid, heyf_tmp.deptid, heyf_tmp.salary, @rownum:=@rownum+1,if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank,@pdept:=heyf_tmp.deptidfrom(select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc ) heyf_tmp,(select @rownum:=0, @pdept:=null,@rank:=0) a ) result;
执行计划如下
idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARY<derived2>ALLNULLNULLNULLNULL9100.00
2DERIVED<derived4>systemNULLNULLNULLNULL1100.00
2DERIVED<derived3>ALLNULLNULLNULLNULL9100.00
4DERIVEDNULLNULLNULLNULLNULLNULLNULLNULLNo tables used
3DERIVEDheyf_t10ALLNULLNULLNULLNULL9100.00Using filesort
如果大数据量直接就挂了。换个思路来写
select h.`empid`,h.`deptid`,h.`salary`,count(*) as rank
from heyf_t10 as h
left outer join heyf_t10 as r
on h.deptid = r.deptid
and h.`salary`<= r.`salary`
group by h.`empid`,h.`deptid`,h.`salary`
order by h.deptid, h.salary desc;
idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEhALLNULLNULLNULLNULL9100.00Using temporary; Using filesort
1SIMPLErALLNULLNULLNULLNULL9100.00
需要优化的时候建立个索引优化掉filesort,很直观,效率也提高了很多。有时候不能太赶需求,遇到复杂的SQL还是让DBA来优化一下。
--EOF--