笔试中遇到的题，关于你如何优化sql的？

一、优化办法：
1.尽量避免在where子句中对字段进行null值判断，否则可能会扫面全表。
SELECT id from emp where sal is null。
2.尽量避免where子句中使用！=或者<>等 操作符，否则可能引擎放弃使用索引而进行全表扫描。
3.尽量避免在 where 子句中使用 or 来连接条件，否则会扫面全表。
4.in 和 not in 也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据
5.必要时强制查询优化器使用某个索引，如在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
6.应尽量避免在 where 子句中对字段进行表达式操作，否则扫面全表。
7.应尽量避免在where子句中对字段进行函数操作，否则扫面全表。
8.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
9.很多时候用 exists是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select top 1 from b where num=a.num)
10.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
11.避免频繁的创建和删除临时表，以减少系统表资源的消耗。
12..临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
13.尽量避免大事务操作，提高系统并发能力。
14.避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:
SELECT name FROM employee WHERE salary > 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。
15.充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE 子句中将连接条件完整的写上，有可能大大提高查询速度。
例： 

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO 
 SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO


第二句将比第一句执行快得多。
16.使用视图加速查询
把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序 操作，而且在其他方面还能简化优化器的工作。例如： 

SELECT cust.name，rcvbles.balance，……other columns 
 FROM cust，rcvbles 
 WHERE cust.customer_id = rcvlbes.customer_id 
 AND rcvblls.balance>0 
 AND cust.postcode>“98000” 
 ORDER BY cust.name


如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个视图中，并按客户的名字进行排序： CREA

TE VIEW DBO.V_CUST_RCVLBES 
 AS 
 SELECT cust.name，rcvbles.balance，……other columns 
 FROM cust，rcvbles 
 WHERE cust.customer_id = rcvlbes.customer_id 
 AND rcvblls.balance>0 
 ORDER BY cust.name


然后以下面的方式在视图中查询： 

SELECT ＊ FROM V_CUST_RCVLBES 
 WHERE postcode>“98000”


视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。
17.能用DISTINCT的就不用GROUP BY 

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID


可改为： 

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10


18.能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源
19.尽量不要用SELECT INTO语句。
SELECT INOT 语句会导致表锁定，阻止其他用户访问该表。
————————————————

下面是网上的一些问题和相关的解决办法：

请问以下语句如何优化: 

CREATE TABLE aa_001 
 ( ip VARCHAR2(28), 
 name VARCHAR2(10), 
 password VARCHAR2(30) ) 
 select * from aa_001 where ip in (1,2,3) order by name desc;


–目前表中记录有一千多万条左右，而且in中的值个数是不确定的。
以上就是优化的需要优化的语句和情况。
不少人在后面跟帖：有的说没办法优化，有的说将ＩＮ该为ＥＸＩＳＴＳ，有的说在ip上建立索引复合索引（ip，name)等等。
二，提出问题 那这样的情况，能优化吗，如何优化？今天就来讨论这个问题。
三，分析问题 1，数据量1千万多条。
2，in中的值个数是不确定
3.1 分析数据分布 这里作者没有提到ip列的数据的分布情况，目前ip列的数据分布可能有以下几种：
1，ip列（数据唯一，或者数据重复的概率很小）
2，ip列 (数据不均匀，可能有些数据重复多，有些重复少）
3，ip列 （数据分布比较均匀，数据大量重复，主要就是一些同样的数据（可能只有上万级别不同的ip数据等)
解决问题：
1，对于第一种数据分布情况，只要在ip列建立一个索引即可。这时不管表有多少行， in个数是不确定的情况下，都很快。
2，对应第二中数据分布情况，在ip列建立索引，效果不好。因为数据分布不均匀，可能有些快，有些慢
3，对应第三种数据分布情况，在ip列建立索引，速度肯定慢。
注意：这里的 order by name desc 是在取出数据后再排序的。而不是取数据前排序
对于2，3两个情况，因为都是可能需要取出大量的数据，优化器就采用表扫描(table scan)，而不是索引查找(index seek) ,速度很慢，因为这时表扫描效率要优于索引查找，特别是高并发情况下，效率很低。
那对应2，3中情况，如何处理。是将in改成exists。其实在sql server 2005和oracle里的优化器在in后面数据少时，效率是一样的。这时采用一般的索引效率很低。这时如果在ip列上建立聚集索引，效率会比较高。我们在SQL server 2005中做个测试。
表：[dbo].[[zping.com]]]中有约200万条数据。包含列Userid, id, Ruleid等列。按照上面的情况查询一下类似语句：
select * from [dbo].[[zping.com]]] where
userid in (‘402881410ca47925010cb329c7670ffb’,’402881ba0d5dc94e010d5dced05a0008’
,’4028814111a735e90111a77fa8e30384’) order by Ruleid desc
我们先看userid的数据分布情况，执行下面语句：
select userid,count(*) from [dbo].[[zping.com]]] group by userid order by 2
这时我们看看数据分布：总共有379条数据，数据两从1到15万都有，数据分布倾斜严重。下图是其中一部分。
这时如果在ip上建立非聚集索引，效率很低，而且就是强行索引扫描，效率也很低，会发现IO次数比表扫描还高。这时只能在ip上建立聚集索引。这时看看结果。
这时发现，搜索采用了（clustered index seek）聚集搜索扫描。
在看看查询返回的结果：
(156603 行受影响)
表 ‘[zping.com]’。扫描计数 8，逻辑读取 5877 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 ‘Worktable’。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
返回15万行，才不到6千次IO。效率较高，因为这15万行要排序，查询成本里排序占了51%。当然可以建立（userid，Ruleid）复合聚集索引，提高性能，但这样做DML维护成本较高。建议不采用。
从上面的测试例子可以看出， 优化的解决办法：
数据分布为1：建立ip索引即可
数据分布为2，3：在ip列建立聚集索引。