MySQL索引优化order bygroup by

  • 案例一
    image-20210821184738861

    name符合最左前缀法则,但在age处断了,所以只能用到name列,索引长度202,order by也用到了index_union索引
    树,通过Extra可看出。
    
  • 案例二
    image-20210821185046629

    where后符合最左前缀,所以只用到了name列,而order by处不是用的索引树index_union,因为age还没排序呢,
    position排序肯定是乱的,需要将结果集放在内存中排序。
    
  • 案例三
    image-20210821195324399
    image-20210821195503461

    如第二张图所示,在确定最左列name后,其实下面也按age和position分别也是排好序的。
    
  • 案例四
    image-20210821195758759

    由案例三中的第二张图可知,最左列name确定后,其实是按age,position排序的,但是想要position、age的方式
    排序的话,需要将age,position加载到内存中再冲洗排序,通过Extra字段就可以看出来。
    
  • 案例五
    image-20210821200353902

    age其实已经明确是15了,一个常量值,相当于 order by position。在案例三中第二张图就相当于明确了name是
    王五,age=1的结果集,很明显接下来的position也是排好序的。
    
  • 案例六
    image-20210821200731981

    在案例三中第二张图中,age和position只能是相同顺序的,才能利用到B+树的特性,直接得出,否则还需文件排序
    
  • 案例七
    image-20210821201702926
    image-20210821201736270
    image-20210821202056043

    看第一个图,很懵,即用了索引本身的排序,又用了文件排序,再看第二张图对比下就可知,索引树是给where用的,
    而order by其实就是文件排序,如图三,in中的这三个name虽然是排好序的,但age和position并不是已经排好序的。
    
  • 案例八

    image-20210821202455316

    image-20210821202755758

遇到必须要用大于小于这种情况,可以使用索引覆盖来优化他,注意Extra中的信息,using where 对应的是where条件,
using index对应的是name > 'AAA',因为select的数据都在本索引树上,如果是*肯定不会有using index了,最后
就是order by的文件排序了。

总结

  1. 总计俩种排序方式:index,filesort。using index效率高,using filesort效率低,using index利用了索引树本身的排序特性。
  2. order by和where都需遵循最左前缀原则,类似盖楼房的情景,没有一楼,不可能直接盖三楼的。
  3. 利用索引覆盖减少回表
  4. group by本质就是先排序后分组,遵循最左前缀法则。如果分组不需要排序可以加上order by null禁止排序。
  5. where高于having,能卸载where中的限定条件就不要在having中限定。

image-20210821204046334

单路排序:将所有需要查询的字段放在内存中排序,而双路只会把主键和需要排序的字段
放到内存中排序,最后通过主键id回表查询select所需的字段。

Q.E.D.


一个热爱生活的95后精神小伙