Day10——排序分组优化

一. 回顾

前面学习了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之间查询的列 <= 使用索引的列+主键

#总结：尽量不使用‘*’作为查询的列，要将列一一写出来