MySQL 数据库SQL语句优化Order by 优化_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2001 | 回复: 1   主题: MySQL 数据库SQL语句优化Order by 优化        下一篇 
tngou
注册用户
等级:中校
经验:2433
发帖:192
精华:15
注册:2014-4-28
状态:离线
发送短消息息给tngou 加好友    发送短消息息给tngou 发消息
发表于: IP:您无权察看 2015-6-17 15:28:25 | [全部帖] [楼主帖] 楼主   主页

1、filesort优化算法.

在mysql version()<4.1之前,优化器采用的是filesort第一种优化算法,先提取键值和指针,排序后再去提取数据,前后要搜索数据两次,第 一次若能使用索引则使用,第二次是随机读(当然不同引擎也不同)。mysql version()>=4.1,更新了一个新算法,就是在第一次读的时候也把selcet的列也读出来,然后在sort_buffer_size中 排序(不够大则建临时表保存排序顺序),这算法只需要一次读取数据。所以有这个广为人传的一个优化方法,那就是增大sort_buffer_size。 Filesort第二种算法要用到更的空间,sort_buffer_size不够大反而会影响速度,所以mysql开发团队定了个变量 max_length_for_sort_data,当算法中读出来的需要列的数据的大小超过该变量的值才使用,所以一般性能分析的时候会尝试把 max_length_for_sort_data改小。

2、单独order by 用不了索引,索引考虑加where 或加limit

先建一个索引(last_login),建的过程就不给出了

mysql> explain select * from one order by last_login desc;


开始没limit查询是遍历表的,加了limit后,索引可以使用,看key_len 和key

3、where + orerby 类型,where满足最左前缀原则,且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引,where满足最左前缀原则且order by中列a、b、c的任意组合

mysql> explain select * from one where username=’abgvwfnt’ and password =’123456


上面两条语句明显的区别是多了一个非索引列level的排序,在extra这列对了Using filesort

笔者测试结果:where满足最左前缀且order by中的列是该多列索引的子集时(也就是说orerby中没最左前缀原则限制),不管是否有asc ,desc混合出现,都能用索引来满足order by。

笔者测试过,因为篇幅比较大,这里就不一一列出。

Ps:很优化博文都说order by中的列要where中出现的列(是索引)的顺序一致,笔者认为不够严谨。

4、 where + orerby+limit

这个其实也差不多,只要where最左前缀,orderby也正确,limit在此影响不大

5、如何考虑order by来建索引

这个回归到创建索引的问题来,在比较常用的oder by的列和where中常用的列建立多列索引,这样优化起来的广度和扩张性都比较好,当然如果要考虑UNION、JOIN、COUNT、IN等进来就复杂很多了




赞(0)    操作        顶端 
superstar
注册用户
等级:上尉
经验:525
发帖:2
精华:0
注册:2015-6-3
状态:离线
发送短消息息给superstar 加好友    发送短消息息给superstar 发消息
发表于: IP:您无权察看 2015-6-18 9:23:14 | [全部帖] [楼主帖] 2  楼

单独order by 用不了索引,索引考虑加where 或加limit
这是个问题


赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论