http://www.itpub.net/thread-377823-1-1.html
前几天有过讨论。
我的建议就是尽量避免使用"%黄%",改为如下:
select ui."UID" ui_uid, uc.id uc_id, ui.cn
from um_userinfo ui, um_user_contact uc
where ui.cn like '黄%'
and uc.borrow_org is null
and ui."UID" = uc."UID"(+)
and ui.pafa_is_active = 'true';
当然这样也不一定会使用索引。因为姓黄的很多。最近我也遇到基本相似的问题,类似就是查询时使用%aaa%的问题,我的建议就是让开发人员改程序,而让操作人员决定是否输入百分号。
=============
ORDER BY 通常会有两种实现方法,一个是利用有序索引自动实现,也就是说利用有序索引的有序性就不再另做排序操作了。另一个是把结果选好之后再排序。
一起jquery,17jquery
用有序索引这种,当然是最快的,不过有一些限制条件,来看下面的测试。 17jquery.com
一起jquery,17jquery
测试数据:student表有两个字段id ,sid ,id是主键。一共有20W条记录,id从1到200000,sid也是从1到200000的数据。 一起jquery,17jquery
内容来自17jquery
第一种情况 : 17jquery.com
一起jquery,17jquery
order by的字段不在where条件也不在select中
一起jquery,17jquery
一起jquery,17jquery
select sid from zhuyuehua.student where sid < 50000 order by id;
内容来自17jquery
第二种情况 :
order by的字段不在where条件但在select中。 内容来自17jquery
17jquery.com
select id,sid from zhuyuehua.student where sid < 50000 order by id;
17jquery.com
一起jquery,17jquery
第三种情况 : 17jquery.com
内容来自17jquery
order by的字段在where条件但不在select中。 一起jquery,17jquery
内容来自17jquery
select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id;
内容来自17jquery
第四种情况 :
order by的字段在where条件但不在select中。倒序排列
内容来自17jquery
一起jquery,17jquery
select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc; 17jquery.com
17jquery.com
测试结果:
内容来自17jquery
order by的字段不在where条件不在select中 有排序操作 17jquery.com
内容来自17jquery
order by的字段不在where条件但在select中 有排序操作 内容来自17jquery
order by的字段在where条件但不在select中 无排序操作 17jquery.com
17jquery.com
order by的字段在where条件但不在select中(倒序) 无排序操作
内容来自17jquery
结论:
17jquery.com
内容来自17jquery
当order by 字段出现在where条件中时,才会利用索引而无需排序操作。其他情况,order by不会出现排序操作。
分析: 一起jquery,17jquery
内容来自17jquery
为什么只有order by 字段出现在where条件中时,才会利用该字段的索引而避免排序。这要说到数据库如何取到我们需要的数据了。
一条SQL实际上可以分为三步。 内容来自17jquery
1.得到数据
17jquery.com
2.处理数据 内容来自17jquery
一起jquery,17jquery
3.返回处理后的数据
17jquery.com
比如上面的这条语句select sid from zhuyuehua.student where sid < 50000 and id < 50000 order by id desc 内容来自17jquery
第一步:根据where条件和统计信息生成执行计划,得到数据。
第二步:将得到的数据排序。
17jquery.com
内容来自17jquery
当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则排序操作。 内容来自17jquery
内容来自17jquery
第三步:返回排序后的数据。 一起jquery,17jquery
17jquery.com
另外:
内容来自17jquery
上面的5万的数据sort只用了25ms,也许大家觉得sort不怎么占用资源。可是,由于上面的表的数据是有序的,所以排序花费的时间较少。如果是个比较无序的表,sort时间就会增加很多了。另外排序操作一般都是在内存里进行的,对于数据库来说是一种CPU的消耗,由于现在CPU的性能增强,对于普通的几十条或上百条记录排序对系统的影响也不会很大。但是当你的记录集增加到上百万条以上时,你需要注意是否一定要这么做了,大记录集排序不仅增加了 CPU开销,而且可能会由于内存不足发生硬盘排序的现象,当发生硬盘排序时性能会急剧下降。
内容来自17jquery
注:ORACLE或者DB2都有一个空间来供SORT操作使用(上面所说的内存排序),如ORACLE中是用户全局区(UGA),里面有SORT_AREA_SIZE等参数的设置。如果当排序的数据量大时,就会出现排序溢出(硬盘排序),这时的性能就会降低很多了。
内容来自17jquery
总结: 一起jquery,17jquery
一起jquery,17jquery
当order by 中的字段出现在where条件中时,才会利用索引而不排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。 一起jquery,17jquery
17jquery.com
这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。