sql 语句优化

2019独角兽企业重金招聘Python工程师标准>>>

1.避免where子句使用 or 来连接条件

select * from orders where (customer_num=104 and order_num>100) or order_num = 1008

虽然在customer_num 和order_num 上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离行的集合，改成

select * from orders where (customer_num=104 and order_num>100)
    union
select * from order where  order_num = 1008

2.避免相关子查询

一个 例的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次，查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免那么要在子查询中过滤掉尽可能我的行

3.避免困难的正规表达式 like

matches 和 like 关键字支持通配符匹配，技术上叫正规表达式，但这种匹配特别耗费时间，例：

select * from customer where zipcode like '98_ _ _'
select id from customer where name like '%abc%';

即使在zipcode字段上建立了索引，在这种情况下也是采用扫描的方式。如果把语句改为

select * from customer where zipcode > '98000'

 在执行查询时就会利用索引来查询，显然会大大提高速度。

4.使用临时表加速查询

把表的一个子集进行排序并创建临时表，有时能加速查询。有助于避免多重排序操作，而且在其他方面还有简化优化器的工作。例如：

select cus.name , rcvbles.balance , ,,,
from cust , rcvbles
where cus.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
and cus.postcode > '98000'
order by cust.name

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

select cus.name , rcvbles.balance , ,,,
from cust , rcvbles
where cus.customer_id = rcvlbes.customer_id
and rcvblls.balance>0
order by cust.nameinto temp cust_with_balance

然后以下面的方式在临时表中查询：

select * from cust_with_balance
where poscode > '98000'

临时表中的行要比主表中和行少，而且物理顺序就是所要求的顺序，减少了磁盘i/o，所以查询工作量可以得到大幅减少。

 临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据  

5.考虑在where 及 order by 涉及的列上建立索引

6. 避免在 where 子句中对字段进行null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如

select id from t where num is null

可以在num 上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num = 0

7.避免在wher 子句中使用 != 或 <> 操作符，否则将导致引擎放弃索引进行全表扫描

8. in 和 not in 也要慎用，否则会导致全表扫描，如

select id from t where num in(1,2,3)

对于连续的数值，能用between就不要用in 了：

select id from t where num between 1 and 3

9.如果在where 子句中使用参数，也会导致全表扫描

select id from t where num=@num <mailto:num=@num>

可改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num <mailto:num=@num>

因为sql只有在运行时都会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值学是未知的，因而无法作为索引选择的输入项。

10. 避免在where 子句中对字段进行表达式操作，这将导致引擎放弃索引

如

select id from t where num/2 = 100

改

select id from t where num = 100*2

11. 避免在where 子句中地字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

如：

select id from t where substring(name,1,3) = 'abc'

select id from t where datediff(day,createdate,'2005-11-30')

改

select id from t where name like 'abc%'

select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

12.不要在where 子句的 ‘=’ 左边进行函数，算术运算或其他形式运算，否则系统将可能无法正确使用索引

13.使用数字型字段

若只含数据值的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符中每一个字符，而对于数字型而言只需要比较一次就够了。

14.尽可能的使用 varchar/nvarchar 代替 char/nchar

因为首先变长字段存储空间小，可节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

15.任何地方都不要使用select * from t, 用具体的字段列表代替 ‘*’ ，不要返回用不到的任何字段

16.用表变量代替临时表

如果表变量包含大量数据，请注意索引非常有限（只有主键索引）

17.避免频繁创建和删除临时表，以减少系统资源的消耗

18. 临时表并不是不可使用，适当地使用它们可以使某些例程理有效，例如，当需要重复引用大型表或常用表中的某个数据集时，
但是，对于一次性事件，最好使用导出表

19.在临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table ，避免造成大量 log,以提高速度;
如果数据量不大，为了缓和系统表的资源，应先 create table ,然后 insert

20.如果使用到了临时表，在存储过程最后务必将所有的临时表显示删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定

21.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写

22.在所有的存储过程和触发器的开始处设置 set nocount no ,在结束时设置 set nocount off,无需在执行存储过程和触发器的每个语句后向客户发送 done_in_proc 消息

23.尽量避免大事务操作，提高系统并发能力。

24.量避免向客户端返回在数据量，若数据量过大，应该考虑相应需求是否合理

转载于:https://my.oschina.net/u/2552286/blog/731590