一. 回顾
前面学习了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之间查询的列 <= 使用索引的列+主键
#总结:尽量不使用‘*’作为查询的列,要将列一一写出来