mysql order by优化
来源:互联网 发布:reveal for mac破解版 编辑:程序博客网 时间:2024/05/22 17:34
工作过程中,各种业务需求在访问数据库的时候要求有order by排序。有时候不必要的或者不合理的排序操作很可能导致数据库系统崩溃。如何处理好order by排序呢?本文从原理以及优化层面介绍 order by 。
在某些情况下,MySQL不能使用索引来解决ORDER BY,尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括:
· 对不同的关键字使用ORDER BY:
· SELECT * FROM t1 ORDER BY key1, key2;
· 对关键字的非连续元素使用ORDER BY:
· SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
· 混合ASC和DESC:
· SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
· 用于查询行的关键字与ORDER BY中所使用的不相同:
· SELECT * FROM t1 WHERE key2=constant ORDER BY key1;通过EXPLAIN SELECT ...ORDER BY,可以检查MySQL是否可以使用索引来解决查询。如果Extra列内有Using filesort,则不能解决查询。为了避免速度变慢,该优化只用于排序元组中的extra列的总大小不超过max_length_for_sort_data系统变量值的时候。(将该变量设置得太高的的迹象是将看到硬盘活动太频繁而CPU活动较低)。
如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能,可以尝试下面的策略:
· 增加sort_buffer_size变量的大小。
· 增加read_rnd_buffer_size变量的大小。
一 MySQL中order by的原理
1 利用索引的有序性获取有序数据
当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 rang,ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得已经排好序的数据。这种方式的 order BY 基本上可以说是最优的排序方式了,因为 MySQL 不需要进行实际的排序操作。需要注意的是使用索引排序也有很多限制。这个在后文中中解释。
2 利用内存/磁盘文件排序获取结果
由于没有可以利用的有序索引取得有序的数据,MySQL需要通过相应的排序算法,将取得的数据在sort_buffer_size系统变量所设置大小的排序区进行排序,这个排序区是每个Thread 独享的,所以说可能在同一时刻在 MySQL 中可能存在多个 sort buffer 内存区域。
在MySQL中filesort 的实现算法有两种:
1) 双路排序:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息,然后在sort buffer 中进行排序。
2) 单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序。
在 MySQL4.1 版本之前只有第一种排序算法,第二种算法是从MySQL4.1开始的改进算法,主要目的是为了减少第一次算法中需要两次访问表数据的IO操作,将两次变成了一次,但相应也会耗用更多的 sort buffer 空间。典型的以空间换时间的优化方式。当然,MySQL4.1开始的以后所有版本同时也支持第一种算法,MySQL主要通过比较系统参数 max_length_for_sort_data的大小和Query语句所取出的字段类型大小总和来判定需要使用哪一种排序算法。如果max_length_for_sort_data更大,则使用第二种优化后的算法,反之使用第一种算法。所以如果希望 order BY 操作的效率尽可能的高,需要注意max_length_for_sort_data参数的设置。
二 优化order by
当无法避免排序操作时,又该如何来优化呢?很显然,优先选择第一种using index 的排序方式,在第一种方式无法满足的情况下,尽可能让 MySQL 选择使用第二种单路算法来进行排序。这样可以减少大量的随机IO操作,很大幅度地提高排序工作的效率。
1 加大 max_length_for_sort_data 参数的设置
在 MySQL 中,决定使用老式排序算法还是改进版排序算法是通过参数 max_length_for_ sort_data 来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL 就会选择改进后的排序算法,反之,则选择老式的算法。所以,如果有充足的内存让MySQL 存放须要返回的非排序字段,就可以加大这个参数的值来让 MySQL 选择使用改进版的排序算法。
2 去掉不必要的返回字段
当内存不是很充裕时,不能简单地通过强行加大上面的参数来强迫 MySQL 去使用改进版的排序算法,否则可能会造成 MySQL 不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时就须要去掉不必要的返回字段,让返回结果长度适应 max_length_for_sort_data 参数的限制。
3 增大 sort_buffer_size 参数设置
这个值如果过小的话,再加上你一次返回的条数过多,那么很可能就会分很多次进行排序,然后最后将每次的排序结果再串联起来,这样就会更慢,增大 sort_buffer_size 并不是为了让 MySQL选择改进版的排序算法,而是为了让MySQL尽量减少在排序过程中对须要排序的数据进行分段,因为分段会造成 MySQL 不得不使用临时表来进行交换排序。
但是这个值不是越大越好:
1 Sort_Buffer_Size 是一个connection级参数,在每个connection第一次需要使用这个buffer的时候,一次性分配设置的内存。
2 Sort_Buffer_Size 并不是越大越好,由于是connection级的参数,过大的设置+高并发可能会耗尽系统内存资源。
3 据说Sort_Buffer_Size 超过2M的时候,就会使用mmap() 而不是 malloc() 来进行内存分配,导致效率降低。
0 0
- mysql order by优化
- order by优化-mysql
- mysql order by优化
- MySQL 如何优化 ORDER BY
- MySQL Order By索引优化
- MySQL Order By索引优化
- MySQL Order By索引优化
- MySQL Order By索引优化
- MySQL Order By索引优化
- mysql 中 order by 优化
- mysql order by 查询优化
- MySQL如何优化ORDER BY
- MySQL order by性能优化
- MySql ORDER BY 索引优化
- MySql ORDER BY 索引优化
- MySql Order by 、Group by优化
- mysql order by 优化 |order by 索引的应用
- mysql的ORDER BY RAND()优化
- POJ1003--Hangover 翻译
- Cocos2dx使用 TexturePacker导出的.plist
- NFC
- 三角形类1
- 三角形类2
- mysql order by优化
- linux上ln命令
- 欢迎使用CSDN-markdown编辑器
- mysql组合索引与字段顺序
- 文章标题
- 使用HTML5的十大原因
- Windows核心编程:内存体系结构
- 2015.3.20程序多文件组织
- ScheduledThreadPoolExecutor