SQL查询与优化

精彩回答问题一:
exists的用法?
答:EXISTS的执行流程
select * from t1 where exists ( select null from t2 where y = x )
可以理解为:
for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop
对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了
另外IN是不对NULL进行处理
如：
select 1 from dual where null in (0,1,2,null) 为空

精彩回答问题二:
如何处理空值排序?
答:
例:select * from emp order by comm desc nulls last(first);
使用nulls last,把将要排序的列,凡是空值的排在最后面
使用nulls first,把将要排序的列,凡是空值的排在最前面

精彩回答问题三:
如何得到当前行上一行或者下一行的数据?
答:
例:select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(order by sal) bbb from emp;
这些函数为oracle的窗口函数,在mysql不适用,但是mysql会有这种功能的函数吗??(疑问)

疑问一:如果没有索引,id!=0与id<0 or id>0,哪个速度更快?

疑问二:检索字符串与数值时,哪个速度比较快?例:dy=”1KV以下” and dy_2=1与 dy_2=1 and dy=”1KV以下”

———————update———————

B树索引速度的问题

我们都知道，用索引快，可是为什么快，到底会快多少？
比如：有100000条记录，需要定位name=‘yannaiyong’的这条记录，在没有索引的情况下，可能最大要查询10W次，才能定位到该记录，如果有了索引，最多要查询多少次？这样比较一下，就知道有索引和无索引查询速度不是同一个数量级的。
请问：如果有了索引，最多要查询多少次？同时讲解下B树索引定位或排序的原理？

———————update———————

若在索引列在使用函数或者进行计算，则在查询的时候，不会用到该索引。如：student表（id,name,age),在age字段上有索引。则
select * from student where age>30 (会用到索引）
select * from student where age-20>10(不会用到索引）

但如果在age字段上建立这样的索引
create index age_index on student(age-20);则：
select * from student where age-20>10(就会用到索引）

备注：在字段上建立这样的索引，需要比较高的权限。比如sys或者system

———————update———————

精彩回答问题三:
如何得到当前行上一行或者下一行的数据?
答:
例:select ename,sal,lead(sal) over(order by sal) aaa ,lag(sal) over(order by sal) bbb from emp;
这些函数为oracle的窗口函数,在mysql不适用,但是mysql会有这种功能的函数吗??(疑问)

MYSQL没有查到有这样的函数。
在Oracle中，窗口函数其实还有很多，比如:sum(sal) over(),sum(sal) over(order by deptno),sum(sal) over(partition by deptno)等都存在细微差别，大家可以执行下，看看结果就知道差别了。

———————update———————

疑问一:如果没有索引,id!=0与id<0 or id>0,哪个速度更快?

回答：

没有索引，检索表都是全表扫描，速度并没有明显差别。

实验：

1000条的数据，用id!=0 用时17.1m
id<0 or id>0 用时16.8m

建议没有索引的情况，用id!=0,因为毕竟看起来比较舒服。

———————update———————

疑问二:检索字符串与数值时,哪个速度比较快?例:dy=”1KV以下” and dy_2=1与 dy_2=1 and dy=”1KV以下”

回答：

与上面的回答一样，没有索引的情况下。都是全表扫描，而Oracle在数据排序优化上已经比较成熟，检索字符串与数值，可能会有略微差别，但速度基本一致。

SQL的性能最重要的因素还是取决于是否有充分利用索引。