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


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

    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/ 文章版权归本人所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
  • 相关阅读:
    阿里SRE体系如何支撑24小时峰值压力、220+个国家“剁手党”?
    《算法技术手册》一2.4.4 线性算法的性能
    8月7日云栖精选夜读:五分钟读懂SIGIR 2017前沿技术研究成果
    作业9 DFA最小化,语法分析初步
    作业8 非确定的自动机NFA确定化为DFA
    作业7 正规式、正规文法与自动机
    作业6 正规文法与正规式
    作业5 词法分析程序的设计与实现
    作业4 文法和语言总结与梳理
    作业3 语法树,短语,直接短语,句柄
  • 原文地址:https://www.cnblogs.com/buro79xxd/p/2662489.html
Copyright © 2020-2023  润新知