摘要:数据库优化不仅是数据库管理员的任务,程序员也必须知道一些优化技巧,有利于开发高效的数据库系统。
关键字:数据库 优化 技巧
如果是团队开发,作为程序员必须知道本文描述的数据库优化技巧,如果你的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 字段;这里要说的是,不要将这些字段与其他字段放在一个表中。
比如:
- view plaincopy to clipboardprint?
- 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)
- 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)技术
数据库记录分页列表是大量必须使用的基本技术,因此本文建议你在每个数据库中建立下面的存储过程:
- view plaincopy to clipboardprint?
- 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
- 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#):
- view plaincopy to clipboardprint?
- sql = "EXEC [dbo].[xsp_ination] /"tblNEWS/",/"*/",/"id/",40," + pindex.ToString() + ",1,/"iType=" + type.ToString();
- SqlDataReader sr = ExecuteReader(sql);
- while (sr.Read())
- {
- ...
- }
- 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();