程序员必须知道的SQLSERVER数据库优化技巧

摘要：数据库优化不仅是数据库管理员的任务，程序员也必须知道一些优化技巧，有利于开发高效的数据库系统。
关键字：数据库 优化 技巧

如果是团队开发，作为程序员必须知道本文描述的数据库优化技巧，如果你的sa水平比较差，那即使你再努力也些不出高效的数据库应用系统。
如果是单独开发，那就更不必说了。

多数公司的数据库管理员(sa)是不够格的，即使拿了各种认证证件，也差不多还是垃圾管理员，无非是可以混个好职位，多拿一些工资。
如果你所在的公司没有合格的sa，作为程序员的你必须执行做许多数据库优化的工作了。
市面上的数据库类图书也不过是骗钱的把戏，无非为了出书而出书，为了出名而出书。
95％以上的作者没有实践的经验，99％以上的作者没有优化的经验。他们编写图书的来源无非是外文（不见得好啊）或者是互联网上的资讯。

废话不说了，开始吧。程序员级别的优化有哪些手段？

（1）数据库的设置：如果你的数据库记录数不会超过30万条？如果你的数据库记录超过100万条？该如何设置数据库？一个或多个？
（2）数据库表的设置：当你的某个数据库表记录超过100万级别，而且每天大量增长，这是一个不得不考虑的问题。如果你的系统浏览量很大，即使是30万条记录也是需要考虑的。
（3）索引的使用：索引可以大大提高数据库访问速度。什么时候用？哪些字段使用？
（4）存储过程的使用：存储过程终归是比较好的，但是如果需要维护成百上千的存储过程，未必是划算的工程。
（5）高效的分页技术：数据库记录分页列表是大量必须使用的基本技术，怎样的分页是快速的？

宗旨你需要从上述5个方面考虑数据库的优化。

什么时候需要数据库优化？
（1）编写代码之前；
（2）系统速度慢了的时候；

下面就是一些具体的优化技巧了。

（1）超大量记录数据库的优化技巧

如果你的数据库表记录有超过100万级别，而且不断增长中。可以采取两个手段：
第一：将数据库表拆分到不同的库中，比如 tblMEMBER 就可以拆分到 DB1 与 DB2 中去。
实际上，可以拆分到 DB001 ... DB100 甚至更多的库中间去。
DB1 与 DB2 最好不在一块硬盘上。
第二：如果更大量级的数据，则最好拆分到不同的数据库服务器中去。

数据库的拆分带来的是查询等操作的复杂性。简单地可以通过 hash 或者 按序号 匹配不同的数据库。复杂一些，应该设置一个独立的应用服务器（软件）协调其中的操作。

（2）中等量级数据库的优化技巧

所谓中等量级数据库是指数据库100万－500万条记录左右（单个数据库表）。这样的数据库为了提高访问（响应）速度，可以将表拆分到更小的表。比如 tblMEMBER 可以拆分为 tblMEMBER_00 ... tblMEMBER_99 。
这样可以保证每个表的记录数不超过50万，那速度是"相当"快了。

（3）避免使用视图(viewport)与关联

视图viewport与关联都是为了程序员处理相对复杂的数据管理提供方便的手段。万物有其利，必有其弊。视图和关联提高了编程效率，都会较大地影响数据库的访问效率（事实上并不像一般资料说介绍的的那样高效），因此如果是web应用，则建议一般不要使用视图与关联。

（4）不要忘记索引(index)也不要滥用索引(index)

索引是提高数据库效率的简单又高效的方法。只要是设置了数据库表(table)，就不要忘记设置索引(index)。将索引设置在经常用于排序的字段上，其他字段就不要设置了。
索引不是越多越好，也不是什么字段都适合建立索引的。数据重复性太多的字段不要设置索引。比如 tblMEMBER 的 iSex 字段只有 0 1 两个值，就不要设置索引。

（5）二进制的 text image 等字段应该单独设置别的表中

一般的数据库应用难免都需要保存比如描述、图片等信息；一般描述类信息用 text 字段，图片类信息用 image 字段；这里要说的是，不要将这些字段与其他字段放在一个表中。
比如：

> 纯文本方式 > 拷贝到裁剪板 > 打印

1. tblMEMBER   
2. id (int)   
3. cName (varchar)(64)   
4. cDescription (text)   
5. bPhoto (image)   
6. dDate (datetime)   
7. 就应该拆分为3个表   
8. tblMEMBER   
9. id (int)   
10. cName (varchar)(64)   
11. dDate (datetime)   
12. tblMEMBER_DESC   
13. id (int)   
14. cDescription (text)   
15. dDate (datetime)   
16. tblMEMBER_PHOTO   
17. id (int)   
18. bPhoto (image)   
19. dDate (datetime)  

tblMEMBER id (int) cName (varchar)(64) cDescription (text) bPhoto (image) dDate (datetime) 就应该拆分为3个表 tblMEMBER id (int) cName (varchar)(64) dDate (datetime) tblMEMBER_DESC id (int) cDescription (text) dDate (datetime) tblMEMBER_PHOTO id (int) bPhoto (image) dDate (datetime)         

 

 

（6）不要使用文本类型的 id

一般的数据库表都会以一个种子字段作为主键。可以在与不少年青的程序员朋友沟通过程中，发现他们很喜欢用字符串类型的作为系统的 id 号。
比如：id = XX XX XX XX 这样的字符串，每两个位置代表不同的类别等含义。
不知道是那本教材如此误人子弟，作出这样的表率 :<
作为系统的 id 号，一定要使用数字型的。

（7）数据库表table的字段field不要太多

本以为无需说明，也是发现不少的朋友，为了省事，一股脑把所有的相关字段都放在一个表中间。这样做的后果便是，程序写起来简单了，运行效率下来了。
无论字段多少，有两类字段是必须独立出去的：一是进程更新的字段，比如文章的点击次数字段iShow，二是二进制或者是text字段；

（8）将字符串(varchar)比较变成数字型(int)比较

每个系统都会有用户管理，其中必然有 昵称，密码，邮件等的字符串类型数据比较的问题。在数据库操作中，字符串比较的效率是相当低下的。因此遇到字符串的比较，必须将其转换为数字型比较。
具体做法是：在数据库表中增加相应的数字字段，比如 cNickname -> iNickNumber ，其中 iNickNumber 的数值为 cNickname 的 哈希值（如何计算字符串的哈希值？请参阅本站的其他文章）。
通过这样的转换，系统效率可以提高 100 倍哦！！！

（9）为每个数据库表(table)设置 datetime 字段

在许多情况下，很多的表是不需要 datetime 字段用于保存时间的。本文的建议是你应该为每个表都设置 datetime 字段，而且默认值为 getdate()。
我们的经验是，datetime 是实数，占用字节不多；在进行系统维护，远程备份等环节都会发挥意想不到的效果。

（10）适当使用存储过程(Stored Processing)

存储过程(sp)已经被大大地宣传了，本文也不例外地赞许采用存储过程。本文的建议是只在下列情况才使用存储过程：一是一个业务处理是事务，包含了多个处理过程；二是一种处理被高频使用，使用存储过程可以提高效率；

（11）使用高效的分页(ination)技术

数据库记录分页列表是大量必须使用的基本技术，因此本文建议你在每个数据库中建立下面的存储过程：

> 纯文本方式 > 拷贝到裁剪板 > 打印

1. CREATE PROCEDURE xsp_ination   
2. (   
3. @tblName   varchar(64),                   
4. @strGetFields varchar(256) = "*",    
5. @fldName varchar(64)="",                   
6. @PageSize   int = 20,                      
7. @PageIndex  int = 1,                           
8. @OrderType bit = 1,                        
9. @strWhere  varchar(256) = ""       
10. )   
11. AS    
12. BEGIN  
13. declare @strSQL   varchar(1000)      
14. declare @strTmp   varchar(110)        
15. declare @strOrder varchar(400)      
16. SET NOCOUNT ON  
17. if @OrderType != 0   
18.     begin  
19.         set @strTmp = "<(select min"    
20.         set @strOrder = " order by [" + @fldName +"] desc"    
21.     end  
22. else    
23.     begin    
24.         set @strTmp = ">(select max"    
25.         set @strOrder = " order by [" + @fldName +"] asc"    
26.     end    
27. if @PageIndex = 1   
28.     begin  
29.         if @strWhere != ""      
30.             set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from " + @tblName + " where " + @strWhere + " " + @strOrder   
31.         else    
32.             set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from "+ @tblName + " "+ @strOrder   
33.     end  
34. else    
35.     begin  
36.         set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from "  
37.                             + @tblName + " where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from " + @tblName + " " + @strOrder + ") as tblTmp)"+ @strOrder   
38.         if @strWhere != ""    
39.             set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ "  from "  
40.                             + @tblName + " where [" + @fldName + "]" + @strTmp + "(["  
41.                             + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["    
42.                             + @fldName + "] from " + @tblName + " where " + @strWhere + " "  
43.                             + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder    
44.     end  
45. EXEC (@strSQL)   
46. if @@error=0 return 1   
47. SET NOCOUNT OFF  
48. END  
49. GO  

CREATE PROCEDURE xsp_ination ( @tblName varchar(64), @strGetFields varchar(256) = "*", @fldName varchar(64)="", @PageSize int = 20, @PageIndex int = 1, @OrderType bit = 1, @strWhere varchar(256) = "" ) AS BEGIN declare @strSQL varchar(1000) declare @strTmp varchar(110) declare @strOrder varchar(400) SET NOCOUNT ON if @OrderType != 0 begin set @strTmp = "<(select min" set @strOrder = " order by [" + @fldName +"] desc" end else begin set @strTmp = ">(select max" set @strOrder = " order by [" + @fldName +"] asc" end if @PageIndex = 1 begin if @strWhere != "" set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from " + @tblName + " where " + @strWhere + " " + @strOrder else set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from "+ @tblName + " "+ @strOrder end else begin set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from " + @tblName + " where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from " + @tblName + " " + @strOrder + ") as tblTmp)"+ @strOrder if @strWhere != "" set @strSQL = "select top " + str(@PageSize) +" "+@strGetFields+ " from " + @tblName + " where [" + @fldName + "]" + @strTmp + "([" + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " [" + @fldName + "] from " + @tblName + " where " + @strWhere + " " + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder end EXEC (@strSQL) if @@error=0 return 1 SET NOCOUNT OFF END GO
使用方法是(C#)：

 

1. sql = "EXEC [dbo].[xsp_ination] /"tblNEWS/",/"*/",/"id/",40," + pindex.ToString() + ",1,/"iType=" + type.ToString();   
2. SqlDataReader sr = ExecuteReader(sql);   
3. while (sr.Read())   
4. {   
5.    ...   
6. }   
7. sr.Close();  

sql = "EXEC [dbo].[xsp_ination] /"tblNEWS/",/"*/",/"id/",40," + pindex.ToString() + ",1,/"iType=" + type.ToString(); SqlDataReader sr = ExecuteReader(sql); while (sr.Read()) { ... } sr.Close();

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

上面的优化技巧仅是一些常见的手段，如果你的系统（小系统就算了）遇到效率问题，可以与联高软件联系。