关于sql2005索引和优化的问题

Question

我有一个表。表中的数据非常多。
现在有100W条记录。记录还在不但的增加，
现在想给这张表加一个索引，
不知道怎么加能加快数据读取的速度和怎么优化数据库！

Answer 1

至于你的说的优化则无法简单说明了,参考下面的资料.数据表的物理优化方案. 面对一个总量过亿的数据库,如何优化?虽然优化了主键,建立了索引,优化了查询,可是,它为什么还是那么慢呢? 更恶劣的情况是,在月结,转帐,统计汇总时,它总是超时. 
    这的确是让很多人头痛的事.更多人提出的解决方案是:换硬件吧,换硬件真的那么有效吗?就算快一倍,从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 @INT_ROW>=@INT_VALVE BEGIN 
-------------------------------------------- 
--先清理一次日志.因为处理要需要大量的磁盘空间. 
-------------------------------------------- 
SET @NVR_CMD='DUMP TRANSACTION ' + @NVR_DBNAME +  ' WITH NO_LOG' 
EXECUTE(@NVR_CMD) 
  
SET @NVR_CMD='DBCC SHRINKFILE(2, 0)' 
EXECUTE(@NVR_CMD) --断开所有相关连接.要一个个断开. 
DECLARE @INT_SPID INT 
SET @NVR_CMD='' 
SELECT @INT_SPID=MIN(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE DBID=DB_ID() AND SPID <>@@spid 
SET @INT_SPID=ISNULL(@INT_SPID,-1) 
WHILE @INT_SPID>0 BEGIN 
SET @NVR_CMD=N' KILL ' + RTRIM(@INT_SPID) + ';'   
EXECUTE SP_EXECUTESQL @NVR_CMD  
SELECT @INT_SPID=MIN(SPID) FROM MASTER.DBO.SYSPROCESSES WHERE DBID=DB_ID() AND SPID>@INT_SPID  AND SPID <>@@spid 
SET @INT_SPID=ISNULL(@INT_SPID,-1) 
END 
----------------------------------------- SELECT @NVR_TABLENAME=MIN(NVR_TABLENAME) FROM SY_Table_Optimize WHERE NVR_DBNAME=@NVR_DBNAME; 
WHILE LEN(@NVR_TABLENAME)>0 BEGIN SELECT  @NVR_OLEGROUPNAME=NVR_TABLEGROUPNAME, 
@INT_ROWCOUNT=INT_UpRowCount, 
@NVR_DBPATH=LEFT(NVR_TABLEGROUPPATH, CHARINDEX('\' + NVR_TABLEGROUPNAME ,NVR_TABLEGROUPPATH)-1 ) ---这里要特别留意 
FROM SY_Table_Optimize  WHERE NVR_DBNAME=@NVR_DBNAME AND  NVR_TABLENAME=@NVR_TABLENAME; SELECT @INT_NEWROWCOUNT=B.[ROWS] 
FROM SYSOBJECTS AS A,SYSINDEXES AS B   
WHERE A.ID=B.ID AND A.XTYPE='U' AND B.INDID <2 AND A.[NAME]=@NVR_TABLENAME; --如果当前行数改变大于某个值,则优化. 
IF ABS(@INT_NEWROWCOUNT-@INT_ROWCOUNT)>=@INT_VALVE BEGIN 
EXECUTE dt_optimize_table @NVR_DBNAME,@NVR_DBPATH,@NVR_TABLENAME,@NVR_OLEGROUPNAME,@NVR_NEWGOUPNAME OUTPUT; 
--更新优化记录. 
UPDATE SY_Table_Optimize SET NVR_TABLEGROUPNAME=@NVR_NEWGOUPNAME,INT_UpRowCount=@INT_NEWROWCOUNT,DAT_UpDate=GETDATE() WHERE  NVR_DBNAME=@NVR_DBNAME AND  NVR_TABLENAME=@NVR_TABLENAME; 
END  
SELECT @NVR_TABLENAME=MIN([NVR_TABLENAME]) FROM SY_Table_Optimize WHERE  NVR_DBNAME=@NVR_DBNAME AND NVR_TABLENAME>@NVR_TABLENAME; 
SET @NVR_TABLENAME=ISNULL(@NVR_TABLENAME,''); 
END 
END 
END GO 
--------------------------------- 
-- 对指定的表进行物理优化. 
--------------------------------- 
IF EXISTS(SELECT [NAME] FROM SYSOBJECTS WHERE [NAME]='dt_optimize_table') BEGIN 
DROP PROC dt_optimize_table 
END GO CREATE PROC dt_optimize_table ( 
@NVR_DBNAME NVARCHAR(64),  --数据库名. 
@NVR_DBPATH NVARCHAR(256), --数据库所在路径. 
@NVR_TABLENAME NVARCHAR(256), --表名. 
@NVR_OLEGROUPNAME NVARCHAR(256), --上一个文件组的名称. 
@NVR_NEWGOUPNAME NVARCHAR(256) OUTPUT --新的组文件名. 
)  WITH ENCRYPTION  AS BEGIN DECLARE @NVR_NEWID NVARCHAR(16)  --新的表文件编号. 
DECLARE @NVR_CMD NVARCHAR(4000)--命令 
DECLARE @NVR_TMPTABLENAME NVARCHAR(256) --暂时表名 
DECLARE @INT_TRANSACTION INT SET @NVR_NEWID= LEFT(REPLACE(NEWID(),'-',''),16) 
SET @NVR_TMPTABLENAME=@NVR_TABLENAME + '_' + @NVR_NEWID 
SET @NVR_NEWGOUPNAME=@NVR_TMPTABLENAME --添加一个文件组. 
SET @NVR_CMD='ALTER DATABASE [' + @NVR_DBNAME + '] ADD FILEGROUP [' + @NVR_TMPTABLENAME + ']' 
EXECUTE(@NVR_CMD) 
--向文件组中添加一个文件 SET @NVR_CMD=' 
ALTER DATABASE [' + @NVR_DBNAME + '] ADD FILE( 
NAME = N''' + @NVR_TMPTABLENAME + ''',  
FILENAME = N''' + @NVR_DBPATH + '\' + @NVR_TMPTABLENAME + '.NDF'' ,  
SIZE = 3,  
FILEGROWTH = 10%)  
TO FILEGROUP [' +@NVR_TMPTABLENAME + ']' 
EXECUTE(@NVR_CMD) -------------------------------------------- 
--事务段 
-------------------------------------------- 
SET @INT_TRANSACTION=1 
BEGIN TRANSACTION 
-------------------------------------------- 
--在该文件中添加表. **这里要手工修改**. 
-------------------------------------------- 
CREATE TABLE [BL_DATA_2003] ( 
[BIG_DATAAUTOID] [int] NOT NULL , 
[BIG_AREAAUTOID] [int] NOT NULL , 
[BIG_EnterTypeAutoID] [int] NOT NULL , 
[VAR_DATE] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL , 
[FLO_VALUE] [float] NULL , 
CONSTRAINT [PK_DATA_2003] PRIMARY KEY  CLUSTERED  
( 
[BIG_DATAAUTOID], 
[BIG_AREAAUTOID], 
[BIG_EnterTypeAutoID], 
[VAR_DATE] 
)  ON [MC_INDUSTRY_2003]  
) ON [MC_INDUSTRY_2003] SET @NVR_CMD=' 
CREATE TABLE [' + @NVR_TMPTABLENAME + '] ( 
[BIG_DATAAUTOID] [int] NOT NULL , 
[BIG_AREAAUTOID] [int] NOT NULL , 
[BIG_EnterTypeAutoID] [int] NOT NULL , 
[VAR_DATE] [varchar] (9) COLLATE Chinese_PRC_CI_AS NOT NULL , 
[FLO_VALUE] [float] NULL , 
CONSTRAINT [PK_' + @NVR_TMPTABLENAME + '] PRIMARY KEY  
( 
[BIG_DATAAUTOID], 
[BIG_AREAAUTOID], 
[BIG_EnterTypeAutoID], 
[VAR_DATE] 
)  ON [' + @NVR_TMPTABLENAME + ']  
) ON [' +@NVR_TMPTABLENAME + ']' 
EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN 
SET @INT_TRANSACTION=-1 
GOTO RollTRANSACTION 
END --  --在表上建立一个日期的索引.如果有索引的话.在此添加. 
--  SET @NVR_CMD='CREATE INDEX IX_' + @NVR_TMPTABLENAME + '_DATE ON ' + @NVR_TMPTABLENAME + ' (BIG_DATE)' 
--  EXECUTE(@NVR_CMD) --将数据移到新的表. 
SET @NVR_CMD='INSERT INTO ' + @NVR_TMPTABLENAME + ' SELECT * FROM ' + @NVR_TABLENAME  --在此视你实质情况而定,可加入主键一至的 ORDER BY  
EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN 
SET @INT_TRANSACTION=-1 
GOTO RollTRANSACTION 
END --删除原表. 
SET @NVR_CMD='DROP TABLE ' + @NVR_TABLENAME 
EXECUTE(@NVR_CMD) IF @@ERROR <>0 BEGIN 
SET @INT_TRANSACTION=-1 
GOTO RollTRANSACTION 
END 
--提交事务. 
COMMIT TRANSACTION 
SET @INT_TRANSACTION=0 
-------------------------------------------- 
--事务段 
-------------------------------------------- --将表名改为原来表名. 
SET @NVR_CMD='exec sp_rename ''' + @NVR_TMPTABLENAME + ''', ''' + @NVR_TABLENAME + '''' 
EXECUTE(@NVR_CMD) --回滚事务. 
RollTRANSACTION: 
IF @INT_TRANSACTION=-1 BEGIN 
ROLLBACK TRANSACTION 
END ELSE BEGIN 
--删除原来的表文件. 
SET @NVR_CMD=' 
ALTER DATABASE [' + @NVR_DBNAME + ']  
REMOVE FILE ' + @NVR_OLEGROUPNAME  
EXECUTE(@NVR_CMD) 
END -------------------------------------------- 
--清理日志 
-------------------------------------------- 
SET @NVR_CMD='DUMP TRANSACTION ' + @NVR_DBNAME + ' WITH NO_LOG' 
EXECUTE(@NVR_CMD) SET @NVR_CMD='DBCC SHRINKFILE(2, 0)' 
EXECUTE(@NVR_CMD) END