sql的优化是sql语句书写的重中之重，好的sql语句可以带来不一样的使用体验，所以好好学习呀
本篇主要记录一些sql优化的常见优化方案

一、sql语句的优化

1、索引

①、对查询进行优化，要精良避免全表扫描，首先应考虑在where及order by涉及的列上建立索引
②、若查询条件使用 like “%”,前面加入%会导致全表扫描
③、使用group by，不会使用全表扫描，可以禁止排序order by null，提高效率
④、若使用or，or中所有的条件必须加索引，否则会全表扫描
⑤、select * 中使用具体的字段列表代替“*”，不要返回无用字段

1
2
3
4
5
6

# 例：(一个字段有索引，一个字段没索引)
	select id from t where num=10 or name='admin'
# 可换为：
	select id from t where num=10
	union all
	select id from t where name='admin'

索引并不是越多越好，索引固然能提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建索引要是否必

应尽可能避免更新clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引

2、操作符的使用

①、避免在where子句中使用 ！= 或 <>操作符，否则引擎将放弃使用索引而进行全表扫描
例：可将 a<>0 修改为 a>0 or a<0
②、应尽量避免在where子句中对字段进行null值半段，否则将导致引擎放弃使用索引而镜像全表扫描
③、char(100)类型，在字段建立时，空间就固定了，不管是否插入值(NULL也包含在内)，都占用100个字符的空间；varchar类型，是变长字段，null不占用空间

尽量使用 数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和链接的性能，并会增加存储开销。这里因为引擎再处理查询和来连接时会逐个比较字符串中每一个字符，而对于数字型只需比较一次

3、in 和 exists

①、in 先执行子查询，子查询去重后，再执行主查询
②、exists 用于检查子查询是否至少返回一行数据，该子查询实际上并不返回仍和 数据，而是返回值True或False
③、若两张表数据一样大，那么用in和exists差别不大，但是若不一样大，子查询小的用in，主查询小的用exists
④、避免不必要的类型转换，比如将字符串与数字类型标胶，这样会将字符串进行转换，导致全表扫描

4、where子句

在where子句中使用参数，也会导致全表扫描，因sql只有再运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须再编译时进行选择。

1
2

select id from t where num=@num
select id from t with(index(索引名)) where num=@num

应尽量避免再where子句中对字段进行表达式/函数操作，这将导致引擎放弃使用索引而进行全表扫描

1
2
3
4
5
6
7
8
9
10
11
12

# 例：
select id from t where num/2 = 100
# 改为：
select id from t where num = 100*2

# 例：
select id from t where substring(name,1,3)='abc'
select id from t wherre datediff(day,createdate,'2005-11-30') = 0

# 改为：
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'

5、创建表

①、避免频繁创建和删除临时表，以减少系统表资源的消耗。
②、在新建临时表，若一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度；若数据量不大，可先create table，然后insert

③、若使用到了临时表，再存储过程的最后无比将所有的临时表显示删除，先truncate table，然后drop table，这样可避免系统表的较长时间锁定

6、游标

①、尽量避免使用游标，因为游标的效率较差，若游标操作的数据超过1万行，应考虑改写
②、使用基于游标的方法或临时表方法之前，应先照基于集的解决方案来解决问题，基于集的方法通常更有效

7、其他

①、在所有的存储过程和触发器的开始处设置 set nocount on ,在结束时设置 set nocount off 。无序在执行存储过程和触发器的每个语句后向客户端发送done_in_proc消息

②、尽量避免大事务操作，提高系统并发能力