MYSQL-实现ORACLE- row_number() over(partition by ) 分组排序功能优化

缺乏、安全感 2022-01-17 05:27 596阅读 0赞

今天看了篇帖子，被几个地方转载，但是性能不太好，因为不能评论，所以把优化思路写在这里。

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.deptid
> 
> from
> 
> (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--

作者：Buro\#79xxd 出处：http://www.cnblogs.com/buro79xxd/ 文章版权归本人所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。