美文网首页mysql
MySQL性能优化(五)-- using filesort

MySQL性能优化(五)-- using filesort

作者: 码咖 | 来源:发表于2019-06-29 11:02 被阅读429次

    Using filesort表示在索引之外,需要额外进行外部的排序动作。导致该问题的原因一般和order by有者直接关系,一般可以通过合适的索引来减少或者避免。

    一、order by产生using filesort详解

    1.首先建表和索引(以下使用的sql版本是5.5.54)

    /*课程表*/
    create table course(
        id int primary key auto_increment,/* 主键自增*/
        title varchar(50) not null,/* 标题*/
        category_id int not null,/* 属于哪个类目*/
        school_id int not null,/* 属于哪个学校*/
        buy_times int not null,/* 购买次数*/
        browse_times int not null/* 浏览次数*/
    );
    insert into course(title,category_id,school_id,buy_times,browse_times) values('java课程',1,1,800,8680);
    insert into course(title,category_id,school_id,buy_times,browse_times) values('android课程',2,1,400,8030);
    insert into course(title,category_id,school_id,buy_times,browse_times) values('mysql课程',3,2,200,2902);
    insert into course(title,category_id,school_id,buy_times,browse_times) values('oracle课程',2,2,100,6710);
    insert into course(title,category_id,school_id,buy_times,browse_times) values('C#课程',1,3,620,2890);
    insert into course(title,category_id,school_id,buy_times,browse_times) values('PS课程',4,4,210,4300);
    insert into course(title,category_id,school_id,buy_times,browse_times) values('CAD课程',5,1,403,6080);
    
    /*在category_id和buy_times上建立组合索引*/
    create index idx_cate_buy on course(category_id,buy_times);
    

    2.order by 和 group by 会产生 using filesort的有哪些?

    (1)explain select id from course where category_id>1 order by category_id;

    根据最左前缀原则,order by后面的的category_id会用到组合索引

    (2)explain select id from course where category_id>1 order by category_id,buy_times;

    根据最左前缀原则,order by后面的的category_id buy_times会用到组合索引,因为索引就是这两个字段

    (3)explain select id from course where category_id>1 order by buy_times;

    根据最左前缀原则,order by后面的字段是缺少了最左边的category_id,所以会产生 using filesort

    (4)explain select id from course where category_id>1 order by buy_times,category_id;

    order by后面的字段顺序不符合组合索引中的顺序,所以order by后面的不会走索引,即会产生using filesort

    (5)explain select id from course order by category_id;

    根据最左前缀原则,order by后面存在索引中的最左列,所以会用到索引

    (6)explain select id from course order by buy_times;

    根据最左前缀原则,order by后面的字段 没有索引中的最左列的字段,所以不会走索引,会产生using filesort

    (7)explain select id from course where buy_times > 1 order by buy_times;

    根据最左前缀原则,order by后面的字段 没有索引中的最左列的字段,所以不会走索引,会产生using fillesort

    (8)explain select id from course where buy_times > 1 order by category_id;

    根据最左前缀原则,order by后面的字段存在于索引中最左列,所以会走索引

    (9)explain select id from course order by buy_times desc,category_id asc;

    根据最最左前缀原则,order by后面的字段顺序和索引中的不符合,则会产生using filesort

    (10)explain select id from course order by category_id desc,buy_times asc;

    这一条虽然order by后面的字段和索引中字段顺序相同,但是一个是降序,一个是升序,所以也会产生using filesort,同时升序和同时降序就不会产生using filesort了

    总结:终上所述,(3)(4)(6)(7)(9)(10)都会产生using filesort.

    欢迎关注我的公众号,第一时间接收文章推送~ 搜索公众号: 码咖 或者 扫描下方二维码:


    qrcode_wx.jpg

    相关文章

      网友评论

        本文标题:MySQL性能优化(五)-- using filesort

        本文链接:https://www.haomeiwen.com/subject/dhnucctx.html