数据库优化原则

最近数据库课程设计，我总结了一下数据库的优化方法，希望对有需要的人能有帮助： 1.对查询进行优化，尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索

最近数据库课程设计，我总结了一下数据库的优化方法，希望对有需要的人能有帮助：
1.对查询进行优化，尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

    select id from p where num is null

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

    select id from p where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

    select id from p where num=8 or num=12

　　可以这样查询：

    select id from p where num=8
　　  union all
　　  select id from p where num=12

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

    select id from p where num in(1，2，3)

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

    select id from p where num between 1 and 3

6.下面的查询也将导致全表扫描：

　  select id from p where name like '%abcd%'

　　若要提高效率，可以考虑全文检索。

7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

    select id from p where num=@number

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

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

8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

    select id from t where num/2=10

应改为：

select id from t where num=10*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name，1，3)='abc'--name以abc开头的id
　　select id from t where datediff(day，createdate，'2014-12-30')=0--‘2014-12-30’生成的id

 

10.主键是必要的，SQL SERVER的主键同时是一个唯一索引，而且在实际应用中，我们往往选择最小的键组合作为主键，所以主键往往适合作为表的聚集索引。在有多个键的表，主键的选择也比较重要，一般选择总的长度小的键，小的键的比较速度快，同时小的键可以使主键的B树结构的层次更少。主键的选择还要注意组合主键的字段次序，对于组合主键来说，不同的字段次序的主键的性能差别可能会很大，一般应该选择重复率低【本文来自鸿网互联 (http://www.68idc.cn)】、单独或者组合查询可能性大的字段放在前面。

11.数据类型尽量用数字型，数字型的比较比字符型的快很多。

12.数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的。

13.尽量不要允许NULL，除非必要，可以用默认值代替。

14.少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

15.自增字段要慎用，不利于数据迁移。

16.适用文件组可以有效把I/O操作分散到不同的物理硬盘，提高并发能力。

17.一个表不要加太多索引，因为索引影响插入和更新的速度。

18.适当的使用冗余的反范式设计，以空间换时间有的时候会很高效。

19.联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。

20.查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。