SQL优化杂记

1. 避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，可以对字段设置默认值0，确保表中该字段没有null值，用=0来判断
2. 避免在where中使用or in not in，同样会放弃使用索引
3. like 后的表达式带以通配符或_开头时，会导致不使用索引 如 like ‘%aa’ 不使用索引，但是like ‘aa%' 会使用索引
4. 避免在where子句中对字段进行表达式操作，如 where num/2 =100; 可改为 where num=2*100；
5. 避免在where中对字段进行函数操作，如：where substring(name, 0 , 4) = 'aa' 可以改为 like 形式
6. 不要在where 中的“=”左边进行函数、算术运算或其他表达式运算，会导致不使用索引
7. 使用复合索引字段查询时，必须使用到该索引的第一个字段为条件，才会使用该索引；尽可能让字段顺序与索引中字段顺序一致
8. 不要写一些没有意义的查询，如需要生成一个空表结构： select col1,col2 into #t from t where 1=0 这类代码不会返回任何结果集，但是会消耗系统资 源的，应改成这样： create table #t(...)
9. 用exists 代替 in 比较好
10. 尽量使用数值型字段，如只包含数值信息时，不要设计为字符型字段
11. 尽可能使用varchar代替char，varchar为变长字段（长度随存储的数据变化）节省存储空间，切查询效率高
12. 不要使用select * from table ,用具体字段代替 *
13. 避免频繁创建删除临时表，以减少系统表资源的消耗
14. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
15. 新建临时表时，如果一次性插入数据量很大，可以使用select into 代替 create table ,避免大量log，以提高速度；如果数据量不大，为缓和系统表资源，应先create table 然后 insert
16. 如果使用了临时表，在存储过程的最后务必将所有临时表显式删除，先truncate table 清空表数据，再drop table，可避免系统表的长时间锁定
17. 尽量避免使用游标
18. 尽量避免大事务操作，提高系统并发能力
19. 尽量避免向客户端返回大数据量，若数据量过大，应考虑相应需求是否合理
20. 优化查询，避免全表扫描，建立索引

索引：单字段索引、唯一索引（不能有重复数据）、组合索引（多个字段组成）、聚集索引（只有一个，如字典的字母索引，数据按照字母顺序排序）、非聚集索引（可以有多个，字典的部首索引）

MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引（in or 走不走索引答案未确定）

建立索引规则：

• 主键一定要建立索引（隐含索引，默认自动创建）

• 外键建立索引

• 经常查询的数据列

• 经常用在where中的数据列

• 经常出现在order by 、group by、distinct后面的字段

• 很少涉及，重复值较多的字段不要建立索引

• 数据类型为text、image、bit的不建立索引

• 经常更新的字段避免建立索引

• 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

• 复合索引，按照字段在查询条件中出现的频率建立索引。将最具有限制、应用频率最高的字段放在复合索引前面

建立索引语法：

CREATE [UNIQUE][CLUSTERED | NONCLUSTERED]  INDEX  index_name  

ON {table_name | view_name} [WITH [index_property [,....n]]

说明：

UNIQUE: 建立唯一索引。

CLUSTERED: 建立聚集索引。

NONCLUSTERED: 建立非聚集索引。

Index_property: 索引属性。

 UNIQUE索引既可以采用聚集索引结构，也可以采用非聚集索引的结构，如果不指明采用的索引结构，则SQL Server系统默认为采用非聚集索引结构。

删除索引语法：

DROP INDEX table_name.index_name[,table_name.index_name]

说明：table_name: 索引所在的表名称。

index_name : 要删除的索引名称。

1.43 显示索引信息：

使用系统存储过程：sp_helpindex 查看指定表的索引信息。

执行代码如下：

Exec sp_helpindex book1;