求数据表的优化

数据表中有千万数据,也根据某些字段建立的索引,但在页面检索时,还是会出现超时.
因为检索时,是用另一表关联这大数据表的.如何对这大数据表进行优化操作?看到SQL2005中有分区操作
这个操作过程是怎样的?

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

SQL SERVER 2005 分区表实际应用例子
    定义,原理网上讲得多了.在这就不费口舌,记录下创建过程.
    一.  最基本,最重要的一步就是创建分区函数.创建分区函数首先要确定分区键--既按照哪字段来进行分区.在这个例子里,我用记录的时间来作为分区键,由于数据量的问题,最终决定每个月的数据放一个单独的分区.CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES (
'20060930 23:59:59.997',   -- 2006 年 9 月
'20061031 23:59:59.997',   -- 2006 年 10 月
'20061130 23:59:59.997',   -- 2006 年 11 月
'20061231 23:59:59.997',   -- 2006 年 12 月'20070131 23:59:59.997',   -- 2007 年 1 月
'20070228 23:59:59.997',   -- 2007 年 2 月
'20070331 23:59:59.997',   -- 2007 年 3 月
'20070430 23:59:59.997',   -- 2007 年 4 月
'20070531 23:59:59.997',   -- 2007 年 5 月
'20070630 23:59:59.997',   -- 2007 年 6 月
'20070731 23:59:59.997',   -- 2007 年 7 月
'20070831 23:59:59.997',   -- 2007 年 8 月
'20070930 23:59:59.997',   -- 2007 年 9 月
'20071031 23:59:59.997',   -- 2007 年 10 月
'20071130 23:59:59.997',   -- 2007 年 11 月
'20071231 23:59:59.997',   -- 2007 年 12 月
.......
}
GO
    二.  上一步是完成一个概念上的分区,接下来要完成一个物理的构建,使得属于不同分区的数据存储到不同的物理文件上去.
    a.创建文件组--File group for 2006
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200609]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200610]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200611]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200612]
--File group for 2007
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200701]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200702]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200703]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200704]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200705]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200706]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200707]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200708]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200709]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200710]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200711]
ALTER DATABASE MyDB ADD FILEGROUP [Teaching200712]
......    b.创建物理文件,在这里,为了方便起见,我把每个物理文件放到了一个单独的文件组里面.--Add file for 2006
ALTER DATABASE MyLuDB
ADD FILE
(NAME = N'Teaching200609',FILENAME = N'D:\MyData\MyLu\Teaching200609.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [Teaching200609]ALTER DATABASE MyLuDB
ADD FILE
(NAME = N'Teaching200610',FILENAME = N'D:\MyData\MyLu\Teaching200610.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [Teaching200610]ALTER DATABASE MyLuDB
ADD FILE
(NAME = N'Teaching200611',FILENAME = N'D:\MyData\MyLu\Teaching200611.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)
TO FILEGROUP [Teaching200611]
......
    三.  创建完分区函数,接下来就要建立分区架构,用来将概念上的分区和文件组(物理文件)关联起来.CREATE PARTITION SCHEME [FiveYearDateRangePScheme]
AS
PARTITION FiveYearDateRangePFN TO
( [Teaching200609],[Teaching200610],[Teaching200611],[Teaching200612],[Teaching200701],[Teaching200702],[Teaching200703],[Teaching200704],
[Teaching200705],[Teaching200706],[Teaching200707],[Teaching200708],
[Teaching200709],[Teaching200710],[Teaching200711],[Teaching200712],
......
[PRIMARY] )
GO    四.  分区表的基础架构到此就完成了,接下来就要建立分区表了.CREATE TABLE [dbo].[ObjTeaching](
    [TeachingID] [uniqueidentifier] NOT NULL,
    [TeacherID] [uniqueidentifier] NULL,
    [TeacherName] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NULL,
    [ClassID] [uniqueidentifier] NULL,
    [ClassName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [CourseID] [uniqueidentifier] NULL,
    [CourseName] [nvarchar](20) COLLATE Chinese_PRC_CI_AS NULL,
    [CourseSequenceID] [uniqueidentifier] NULL,
    [TeachingDate] [datetime] NOT NULL,
    [IsUsingEqt] [bit] NULL,
    [ScoreID] [uniqueidentifier] NULL,
        ......
) ON FiveYearDateRangePScheme(TeachingDate)ALTER TABLE [ObjTeaching]
ADD CONSTRAINT [ObjTeaching_PK]
PRIMARY KEY CLUSTERED ([TeachingID], [TeachingDate])
GO    在建立分区表的时候注意一下分区键的选择就OK了
    接下来呢,就可以往分区表里面插入数据,SQL SERVER会根据分区键的不同将数据放到相应的分区,我们可以通过如下语句来查看数据存在那个分区中:select $partition.FiveYearDateRangePFN(teachingdate),teachingdate,*
from objteaching a
order by a.teachingdate asc    总得说来,SQL SERVER 2005的分区表有了一个非常大的进步,使用起来也比较简单(除了频繁的Copy/Paste脚本,然后在一行行改@_@).至于性能方面,还没时间详细测试,留在以后有空了再说!:)
个人建议转用oracle9i或oracle10g
千万数量级如果sql索引建立的好，还是可以应对。oracle不是人人都买得起，盗版当我废话大数据表可用sql分区方式，乌龟的方法很详细了不过索引覆盖作的好的话，我觉得不用分区也能勉强搞定。我服务器2590跑5000w数据技术上处理了下能半分钟出结果
USE [master]IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')DROP DATABASE [DEMO] CREATE DATABASE [DEMO]--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[ 2001], [2002], [2003], [2004], [>2005]ALTER DATABASE Demo ADD FILEGROUP YEARFG1;ALTER DATABASE Demo ADD FILEGROUP YEARFG2;ALTER DATABASE Demo ADD FILEGROUP YEARFG3;ALTER DATABASE Demo ADD FILEGROUP YEARFG4;ALTER DATABASE Demo ADD FILEGROUP YEARFG5; ALTER DATABASE Demo ADD FILEGROUP YEARFG6;  -- 下面为这些文件组添加文件来进行物理的数据存储ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO -- THE CREATED FILEGROUP VIA A PARTITIONING SCHEMEUSE DEMO;GO --------------------------------------------------------- 创建分区函数-------------------------------------------------------CREATE PARTITION FUNCTION YEARPF(datetime)AS RANGE LEFT FOR VALUES ('01/01/2000'                                          ,'01/01/2001'                                          ,'01/01/2002'                                          ,'01/01/2003'                                          ,'01/01/2004')--------------------------------------------------------- 创建分区架构-------------------------------------------------------CREATE PARTITION SCHEME YEARPSAS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6) -- 创建使用此Schema的表CREATE TABLE PARTITIONEDORDERS(ID INT NOT NULL IDENTITY(1,1),DUEDATE DATETIME NOT NULL,) ON YEARPS(DUEDATE) --为此表填充数据declare @DT datetimeSELECT @DT = '1999-01-01' --start looping, stop at ending dateWHILE (@DT <= '2005-12-21')BEGIN       INSERT INTO PARTITIONEDORDERS VALUES(@DT)        SET @DT=dateadd(yy,1,@DT)END -- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
----现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。ALTER PARTITION SCHEME YEARPSNEXT USED YEARFG6; --更改分区函数ALTER PARTITION FUNCTION YEARPF()SPLIT RANGE ('01/01/2005')   --现在我们可以看一下我们刚才插入的行都分布在哪个Partition？SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
USE [master]IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')DROP DATABASE [DEMO] CREATE DATABASE [DEMO]--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[ 2001], [2002], [2003], [2004], [>2005]ALTER DATABASE Demo ADD FILEGROUP YEARFG1;ALTER DATABASE Demo ADD FILEGROUP YEARFG2;ALTER DATABASE Demo ADD FILEGROUP YEARFG3;ALTER DATABASE Demo ADD FILEGROUP YEARFG4;ALTER DATABASE Demo ADD FILEGROUP YEARFG5; ALTER DATABASE Demo ADD FILEGROUP YEARFG6;  -- 下面为这些文件组添加文件来进行物理的数据存储ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO -- THE CREATED FILEGROUP VIA A PARTITIONING SCHEMEUSE DEMO;GO --------------------------------------------------------- 创建分区函数-------------------------------------------------------CREATE PARTITION FUNCTION YEARPF(datetime)AS RANGE LEFT FOR VALUES ('01/01/2000'                                          ,'01/01/2001'                                          ,'01/01/2002'                                          ,'01/01/2003'                                          ,'01/01/2004')--------------------------------------------------------- 创建分区架构-------------------------------------------------------CREATE PARTITION SCHEME YEARPSAS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6) -- 创建使用此Schema的表CREATE TABLE PARTITIONEDORDERS(ID INT NOT NULL IDENTITY(1,1),DUEDATE DATETIME NOT NULL,) ON YEARPS(DUEDATE) --为此表填充数据declare @DT datetimeSELECT @DT = '1999-01-01' --start looping, stop at ending dateWHILE (@DT <= '2005-12-21')BEGIN       INSERT INTO PARTITIONEDORDERS VALUES(@DT)        SET @DT=dateadd(yy,1,@DT)END -- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
----现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。ALTER PARTITION SCHEME YEARPSNEXT USED YEARFG6; --更改分区函数ALTER PARTITION FUNCTION YEARPF()SPLIT RANGE ('01/01/2005')   --现在我们可以看一下我们刚才插入的行都分布在哪个Partition？SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
USE [master]IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')DROP DATABASE [DEMO] CREATE DATABASE [DEMO]--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[ 2001], [2002], [2003], [2004], [>2005]ALTER DATABASE Demo ADD FILEGROUP YEARFG1;ALTER DATABASE Demo ADD FILEGROUP YEARFG2;ALTER DATABASE Demo ADD FILEGROUP YEARFG3;ALTER DATABASE Demo ADD FILEGROUP YEARFG4;ALTER DATABASE Demo ADD FILEGROUP YEARFG5; ALTER DATABASE Demo ADD FILEGROUP YEARFG6;  -- 下面为这些文件组添加文件来进行物理的数据存储ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO -- THE CREATED FILEGROUP VIA A PARTITIONING SCHEMEUSE DEMO;GO --------------------------------------------------------- 创建分区函数-------------------------------------------------------CREATE PARTITION FUNCTION YEARPF(datetime)AS RANGE LEFT FOR VALUES ('01/01/2000'                                          ,'01/01/2001'                                          ,'01/01/2002'                                          ,'01/01/2003'                                          ,'01/01/2004')--------------------------------------------------------- 创建分区架构-------------------------------------------------------CREATE PARTITION SCHEME YEARPSAS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6) -- 创建使用此Schema的表CREATE TABLE PARTITIONEDORDERS(ID INT NOT NULL IDENTITY(1,1),DUEDATE DATETIME NOT NULL,) ON YEARPS(DUEDATE) --为此表填充数据declare @DT datetimeSELECT @DT = '1999-01-01' --start looping, stop at ending dateWHILE (@DT <= '2005-12-21')BEGIN       INSERT INTO PARTITIONEDORDERS VALUES(@DT)        SET @DT=dateadd(yy,1,@DT)END -- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
----现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。ALTER PARTITION SCHEME YEARPSNEXT USED YEARFG6; --更改分区函数ALTER PARTITION FUNCTION YEARPF()SPLIT RANGE ('01/01/2005')   --现在我们可以看一下我们刚才插入的行都分布在哪个Partition？SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')