Day10——排序分组优化

Dear 丶 2022-11-27 08:44 39阅读 0赞

### 文章目录 ###

*  一. 回顾
 *  二. 排序分组优化

# 一. 回顾 #

前面学习了[Day9——子查询优化][Day9]，今天来学习排序分组优化

# 二. 排序分组优化 #

注意代码中的注释，如下：

#清除索引
    call proc_drop_index("mydb", "emp");
    call proc_drop_index("mydb", "dept");
    
    -----------------------------------------------------------------------------
    
    #创建索引
    create index idx_age_deptid_name on emp(age, deptid, name);
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp order by age, deptId;#用不上索引
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp order by age, deptId limit 10;#用上索引
    
    #总结：无过滤，不索引。指没有where条件过滤或者limit，order by是用不上索引的
    
    
    --------------------------------------------------------------------------------------
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp where age = 45 ORDER BY deptid;#用上索引
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp where age = 45 ORDER BY deptid, name;#用上索引
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp where age = 45 ORDER BY deptid, empno;#用上索引，但是有using filesort
    
    #用上索引，但是有using filesort
    #因为索引列的顺序是age_deptid_name,但优化器调整sql顺序的前提是不影响结果。order by先对name排序，才能对deptid排序。
    #因此优化器不能调整order by字段的顺序
    EXPLAIN SELECT SQL_NO_CACHE * from emp where age = 45 ORDER BY name, deptid;
    #总结：order by的索引字段顺序错了，必然有using filesort手动排序
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp where deptid = 45 ORDER BY age;#顺序错了，必排序
    
    --------------------------------------------------------------------------------------
    
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp where age = 45 ORDER BY deptid desc, name desc;#能用上索引，无using filesort
    #总结：无论是升序、降序都能用索引。（可以从索引结构那幅图思考）
    
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp where age = 45 ORDER BY deptid asc, name desc;#能用上索引，有using filesort
    
    #总结：方向反，必有using filesort排序
    
    #总结：order by子句尽量使用索引方式排序，避免使用filesort方式排序
    
    --------------------------------------------------------------------------------------
    
    #清除索引
    call proc_drop_index("mydb", "emp");
    call proc_drop_index("mydb", "dept");
    
    #索引的选择
    
    EXPLAIN SELECT SQL_NO_CACHE * from emp 
    where age = 30 and empno < 101000 ORDER BY name;#选择了idx_age_empno，因为扫描的行数少
    
    create index idx_age_empno on emp(age, empno);
    create index idx_age_name on emp(age, name);
    
    --------------------------------------------------------------------------
    
    #总结：单路排序比双路排序快。但是单路排序需要使用内存，通过设置sort_buffer_size=1M~8M都可以提高效率
    # 这个1M~8M只是提供给一个sql的。几百个sql就需要几百个1M~8M的内存（单路排序多路排序了解即可）
    
    --------------------------------------------------------------------------
    
    #总结：group by的排序规则与order by相似，只有一点区别。group by无过滤条件，也能用上索引。
    
    --------------------------------------------------------------------------
    
    #使用覆盖索引
    
    #覆盖索引：select到from之间查询的列 <= 使用索引的列+主键
    
    #总结：尽量不使用‘*’作为查询的列，要将列一一写出来

[Day9]: https://blog.csdn.net/qq_40634846/article/details/107998389