[原创] 数据表的物理优化方案.
面对一个总量过亿的数据库,如何优化?虽然优化了主键,建立了索引,优化了查询,可是,它为什么还是那么慢呢? 更恶劣的情况是,在月结,转帐,统计汇总时,它总是超时.
  这的确是让很多人头痛的事.更多人提出的解决方案是:换硬件吧,换硬件真的那么有效吗?就算快一倍,从60秒变成了30秒,你的用户一样是无法忍受.
  两三年前,我也遇到过这个问题,也被这个问题拆磨了好几天,后来,在优化数据库的过程中.我发现,对大型超大型的数据库来说,软优化是远远不够的.就算换硬件,好象效果也不

怎么好.在经过一系列动作之后,你算是用尽了全身解数了.速度还没有质的改变,抓狂吗? 我为这个问题抓狂过.
  哈哈,说了一通废话.开始我今天的真正目的:
  经过了必要的软优化,在同样的硬件条件下,如何让你的数据库快起来.下面的过程,就是实现这个目的,对你的表进行物理优化.
  过程不长,但收到的效果可以说是立竿见影的,如果你尝试过一切都无效的话,那我建议你试试它.
  优化的原理很简单,打个比方吧,数据库中的记录,类似一个结构体,一个数据表,类似一个链表.主键,虽然是B树,本质也是一个指针.无论你对数据库怎样操作,最后增加的记录都

是记录在数据库文件的未尾.唯一改变的,就是记录的指针.虽然,数据文件设置了主键,但它在磁盘上的实体却是无序的.当查找某条记录时,它只能按指针的指向去跳,这要磁盘去移

动,去寻道.我们的效率就消耗在这里.我们要做的,就是重新让数据实体按主键的方向排序.让磁头能迅速地找到我们所要的数据. :)
  下面基本是这个解决方案的全部,也是从实质的库中修改过来的.当然,由于每个人的习惯不一样,可能有些出入,但总体的原理,还是一样的.修改一下,即可使用.
  使用方法,建立一个作业,每周的周六晚上执行 EXECUTE dt_optimize_all_table 即可.过程 dt_optimize_all_table 有一个阀值,如果某表的改变的记录超过了某个值(默认

是5万),那它就执行整理.所以,如果你如果是按日期来表的话,你的历史表是不会被整理的.再详细的情况就不说了,过程里的说明都有.

  注:这个过程其实是有点危险的,在正式应用前,一定要经过几次的测试.另,如果你将本过程用于你的生产中,所产生的任何问题,与我无关.

--------------------------------------
--数据表的物理优化方案.
-- MSTOP
--------------------------------------

-----------------------------------------------
--表优化记录.
-----------------------------------------------
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='SY_Table_Optimize') BEGIN
DROP TABLE SY_Table_Optimize
END

GO

--------------------------------------
--记录需优化表的表信息.
--------------------------------------
CREATE TABLE SY_Table_Optimize (
NVR_DBNAME NVARCHAR(128) NOT NULL,
NVR_TABLENAME NVARCHAR(128) NOT NULL,
NVR_TABLEGROUPNAME NVARCHAR(128) NOT NULL,
NVR_TABLEGROUPPATH NVARCHAR(256) NULL,
INT_UpRowCount INT NULL,
DAT_UpDate DATETIME Null
CONSTRAINT [PK_SY_Table_Optimize] PRIMARY KEY CLUSTERED ([NVR_DBNAME],[NVR_TABLENAME])) 

GO

-----------------------------------------------
-- 初始化 SY_Table_Optimize ,
-- 组名和文件名必须是同名.如:文件名是 ABC.NDF ,则组名是: ABC ,并且,一个文件一个组一个表.
-----------------------------------------------
INSERT INTO SY_Table_Optimize 
SELECT DB_NAME(),A.[NAME],C.groupname,D.[FILENAME],B.[ROWS],GETDATE()
FROM SYSOBJECTS AS A inner join SYSINDEXES AS B  
ON A.ID=B.ID AND A.XTYPE='U' AND B.INDID<2 AND A.[NAME] LIKE 'BL_DATA_%'
INNER JOIN sysfilegroups AS C ON B.GROUPID=C.GROUPID
INNER JOIN sysfiles AS D ON C.GROUPID=D.GROUPID
 
GO

---------------------------------
-- 物理优化所有的表.
-- 在作业里加一个作业: 
-- EXECUTE dt_optimize_all_table
---------------------------------
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='dt_optimize_all_table') BEGIN
DROP PROC dt_optimize_all_table
END

GO

CREATE PROC dt_optimize_all_table(
@INT_VALVE INT=50000 --数据库优化阀值.
) WITH ENCRYPTION AS BEGIN 

DECLARE @NVR_DBNAME NVARCHAR(64) --数据库名.
DECLARE @NVR_DBPATH NVARCHAR(256) --数据库所在路径.
DECLARE @INT_ROWCOUNT INT --当前表的总行数.
DECLARE @INT_NEWROWCOUNT INT
DECLARE @NVR_TABLENAME NVARCHAR(256) --表名.
DECLARE @NVR_OLEGROUPNAME NVARCHAR(256)
DECLARE @NVR_NEWGOUPNAME NVARCHAR(256)
DECLARE @NVR_CMD NVARCHAR(4000)
DECLARE @INT_ROW INT

SET @NVR_DBNAME=DB_NAME();
SELECT @INT_ROW=MAX(ABS(T2.[ROWS]-T1.INT_UpRowCount)) FROM 
(
SELECT NVR_TABLENAME,INT_UpRowCount
FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME
) AS T1 INNER JOIN 
(
SELECT A.[NAME],B.[ROWS]
FROM SYSOBJECTS AS A,SYSINDEXES AS B  
WHERE A.ID=B.ID AND A.XTYPE='U' AND B.INDID<2 
) AS T2 ON T1.NVR_TABLENAME=T2.[NAME]

IF @IN