数据库优化篇

从几个方面总结了数据库优化方案:

一、数据库设计

1、索引
创建适当的索引，每当为一个表添加一个索引，select会更快，可insert和delete却大大变慢，因为创建了维护索引需要许多额外的工作。

1. 采用函数处理的字段不能利用索引
2. 条件内包括了多个本表的字段运算时不能进行索引
3. where子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描。例如：
   （低效）select … from [dept] where [sal]*12>25000;
   （高效）select … from [dept] where [sal]>25000/12;
4. 不同类型的索引效能是不一样的，应尽可能先使用效能高的，数字类型的索引查找效率高于字符串类型，定长字符串char、nchar的索引效率高于变长字符串varchar、nvarchar的索引。
   （低效）select … from tableName where username=‘张三’ and age>=21
   （高效）select … from tableName where age>=21 and username=‘张三’

2、游标

1. 尽量不要使用游标，因为使用游标不仅占用内存，而且还用不可思议的方式锁定表，它们可以使DBA所能做的一切性能优化等于没做。游标里每执行一次fetch就等于执行一次select。
2. 与服务器端游标比起来，客户端游标可以减少服务器和网络的系统开销，并且还减少锁定时间。

3、事务
对于一些耗时的操作，使用事务可以达到很好的优化效果。

4、死锁
按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。 如果某个存储过程先锁定表B，再锁定表A，这可能会导致一个死锁。

5、数据集
不要打开大的数据集

6、数据类型
尽量不要使用text数据类型，除非使用text处理一个很大的数据，否则不要使用它。因为它不易于查询，速度慢，用的不好还会浪费大量的空间。一般varchar可以更好的处理数据。

二、SQL语句

1、不要使用select *

在select中指定所需要的列，将带来的好处：

（1）减少内存耗费和网络的带宽

（2）更安全

（3）给查询优化器机会从索引读取所有需要的列

2、使用参数查询

主要是防止SQL注入，提高安全性。

3、使用exists或not exists代替in或not in

4、is null或is not null操作
判断字段是否为空一般是不会应用索引的，因为索引不索引空值。不能用null作索引，任何包含null值的列都将不会被包含在索引中。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。任何在where子句中使用is null或is not null的语句优化器都不允许使用索引。

推荐方案：用其他相同功能的操作运算代替，如：a is not null改为a>0或a>’'等。

5、like操作

like操作可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用不好则会产生性能上的问题，如lide ‘%5400%’ 这种查询不会引用索引，而like ‘X5400%’ 则会引用范围索引。

6、where后面的条件顺序影响

where子句后面的条件顺序对大数据量表的查询会产生直接的影响。如：

select * from zl_yhjbqk where dy_dj=‘1KV以下’ and xh_bz=1;

select * from zl_yhjbqk where dy_dj=1 and dy_dj=‘1KV以下’;

7、用union替换or（适用于索引列）

8、优化group by

9、使用存储过程

可以考虑使用存储过程封装那些复杂的SQL语句或业务逻辑，这样有几个好处：

（1）存储过程的执行计划可以被缓存在内存中较长的时间，减少了重新编译的时间。

（2）存储过程减少了客户端和服务器的繁复交互。

（3）如果程序发布后需要做某些改变你可以直接修改存储过程而不用修改程序，避免需要重新安装部署程序。

三、处理百万级以上数据提高查询速度的方法

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

2、应考虑在where及order by涉及的列上建立索引。

3、尽量避免在where子句中对字段进行null值判断，否则将导致全表扫描。

4、就是避免在where子句中使用or来连接条件，否则将导致全表扫描。

四、数据库主键选取

常见的数据库主键选取方式有：

1. 自动增长字段
2. Uniqueidentifier
3. “COMB（Combine）”类型

1、 自动增长字段
优点：
（1）简单、效率高。
缺点：
（1）自增一般使用int型，有数据条数的限制。
（2）在数据库进行数据合并时会 比较麻烦。

2、GUID（）
优点：
（1）安全，保证唯一性。
（2）不会产生自增字段那样数据合并时的问题。
缺点：
（1）它的长度是16字节，占用大量存储空间。
（2）该数据类型毫无规律，要在上面建立索引很耗时，所以效率要比使用自增字段低。