Mysql查询优化_排序、分组优化
目录
- 一、order by
-
- 1.总结:无过滤 不索引
- 2.总结:顺序错,必排序
- 3.总结:方向反 必排序
- 二、索引的选择
- 三、双路排序和单路排序
-
- 1.双路排序(慢)
- 2.单路排序(快)
- 3.双路排序和单路排序的总结
- 4.优化策略
- 5.提高Order By的速度
- 四、GROUP BY关键字优化
一、order by
1.总结:无过滤 不索引
创建索引
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10;
创建索引
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME);
增加limit过滤条件,使用上索引了。
2.总结:顺序错,必排序
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid;
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid,NAME;
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid,empno;
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY NAME,deptid;
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME)
CREATE INDEX idx_age_deptid_empno ON emp (age,deptid,empno);
3.总结:方向反 必排序
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid DESC, NAME DESC ;
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY deptid ASC, NAME DESC ;
ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
二、索引的选择
当【范围条件】和【group by 或者 order by】的字段出现二选一时,优先观察条件字段的过滤数量,
如果过滤的数据足够多,而需要排序的数据并不多时,优先把索引放在范围字段上。反之,亦然。
三、双路排序和单路排序
如果不在索引列上,filesort有两种算法: mysql就要启动双路排序和单路排序
1.双路排序(慢)
- MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据, 读取行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
- 从磁盘取排序字段,在buffer进行排序,再从磁盘取其他字段。
- 取一批数据,要对磁盘进行两次扫描,众所周知,I\\O是很耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序。
2.单路排序(快)
从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出, 它的效率更快一些,避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间, 因为它把每一行都保存在内存中了。
3.双路排序和单路排序的总结
双路排序:两次io
单路排序:一次io 但是需要调整sort_buffer_size 的容量,避免发生多次io!
4.优化策略
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
减少select 后面的查询的字段。 禁止使用select *
5.提高Order By的速度
增大sort_buffer_size参数的设置 1M-8M
增大max_length_for_sort_data参数的设置 1024-8192字节
减少select 后面的查询的字段。 禁止使用select *
四、GROUP BY关键字优化
group by 先排序再分组,遵照索引建的最佳左前缀法则
当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要写在having中了
group by没有过滤条件,也可以用上索引。Order By 必须有过滤条件才能使用上索引。