海量数据库查询语句

在以下的文章中，我将以“办公自动化”系统为例，探讨如何在有着1000万条数据的MS SQL SERVER数据库中实现快速的数据提取和数据分页。

以下代码说明了我们实例中数据库的“红头文件”一表的部分数据结构： [dbo].[TGongwen] (
[Gid] [int] IDENTITY (1, 1) ,

[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS ,

[fariqi] [datetime] ,

[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS ,

[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS ,

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

1000

@i int
@i=1
@i<=250000

Tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1

@i int

@i=1

@i<=250000

',',,,,,admin,,,,,,','25')

GO

@h int
@h=1
@h<=100

@i int
@i=2002
@i<=2003

@j int
@j=0
@j<50

@k int
@k=0
@k<50

Tgongwen(fariqi,neibuyonghu,reader,title) (cast(@i varchar(4))++cast(@j varchar(2))++cast(@j varchar(2)),,,)
@k=@k+1

@j=@j+1

@i=@i+1

@h=@h+1

@i int
@i=1
@i<=9000000

Tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1000000

通过以上语句，我们创建了2520042525200496200220031002500502004559001000

index,SELECT

SQL SERVERclustered indexnonclustered index

anazazhang

67263390

2004112004101

1

SQL SERVER

IDID1GidSQL SERVERID

IDIDIDIDID

where1328

ID1000325

1

Select gid

128470128

2fariq

gid,fariqi,neibuyonghu,title Tgongwen
fariqi> dateadd(day,-90,getdate())

用时：5376354

3fariqi

gid,fariqi,neibuyonghu,title Tgongwen
fariqi> dateadd(day,-90,getdate())

用时：24232

251000ID12ID

select @d datetime

@d=getdate()

select

select [(datediff(ms,@d,getdate())

2

23fariqi

1000fariqi5003

3

compound index

25fariqineibuyonghu

1select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'

2513

2select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu=''

2516

3select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''

60280

12

1

25

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

3326

gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

4470

1/4

2order by

gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

12936

gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

18843

order by3/1010

3

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1'

6343100

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-6-6'

317050

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

3326

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' and fariqi<'2004-6-6'

3280

4

10020041150505000

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>'2004-1-1' order by fariqi

6390

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<'2004-1-1' order by fariqi

6453

SQL

SQLSQL SERVERSQLSQL SERVER

* from table1 where name='zhangsan' and tID > 10000

:

* from table1 where tID > 10000 and name='zhangsan'

tID10000name='zhangsan'tID>10000

SQL SERVERwhere

where

SARG

SARGAND

< >

< >

Name=

>5000

5000<

Name= and >5000

SARGSQL SERVERWHERESARG

SARGSARG

1LikeSARG

name like % SARG

name like % ,SARG

%

2or

Name= and >5000 SARGName= or >5000 SARGor

3SARG

SARGNOT!=<>!NOT EXISTSNOT INNOT LIKESARG

ABS()<5000

Name like %

WHERE *2>5000

SQL SERVERSARGSQL SERVER

WHERE >2500/2

SQL SERVER

4IN OR

Select * from table1 where tid in (2

Select * from table1 where tid=2 or tid=3

tid

5NOT

6exists in

existsinnot existsnot innotSQL SERVERpubsSQL SERVERstatistics I/O

1select title,price from titles where title_id in (select title_id from sales where qty>30)

'sales' 18 56 0 0

'titles' 1 2 0 0

2select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)

'sales' 18 56 0 0

'titles' 1 2 0 0

existsin

7charindex()%LIKE

LIKE%charindex()LIKE

select gid,title,fariqi,reader from tgongwen where charindex('',reader)>0 and fariqi>'2004-5-5'

7 4 7155 0 0

select gid,title,fariqi,reader from tgongwen where reader like '%' + '' + '%' and fariqi>'2004-5-5'

7 4 7155 0 0

8unionor

whereorunionor

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or gid>9990000

68 1 404008 283 392163

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

9 8 67489 216 7499

unionor

orunionorunionor

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16' or fariqi='2004-2-5'

6423 2 14726 1 7176

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-9-16'

gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi='2004-2-5'

11640 8 14806 108 1144

9select *

top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

4673

top 10000 gid,fariqi,title from tgongwen order by gid desc

1376

top 10000 gid,fariqi from tgongwen order by gid desc

80

10count(*)count()

*

count(*) from Tgongwen

1500

count(gid) from Tgongwen

1483

count(fariqi) from Tgongwen

3140

count(title) from Tgongwen

52050

count(*)count()count(*)count(*) SQL SERVERcount()

11order by

gidfariqi

top 10000 gid,fariqi,reader,title from tgongwen

196 1 289 1 1527

top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

4720 1 41956 0 1287

top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

4736 1 55350 10 775

top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

173 1 290 0 0

top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

156 1 289 0 0

order by order by

12TOP

I/0

top 10 * from (

top 10000 gid,fariqi,title from tgongwen

where neibuyonghu=''

by gid desc) as a

by gid asc

1000010I/OI/OTOPTOPSQL SERVERTOPORACLEORACLErownumberTOP

web :ADO ADO

pagination1
(@pagesize int,
@pageindex int
)

nocount on

@indextable (id int identity(1,1),nid int)
@PageLowerBound int
@PageUpperBound int
@PageLowerBound=(@pageindex-1)*@pagesize
@PageUpperBound=@PageLowerBound+@pagesize
rowcount @PageUpperBound
@indextable(nid) gid TGongwen fariqi >dateadd(day,-365,getdate()) fariqi
O.gid,O.mid,O.title,O.fadanwei,O.fariqi TGongwen O,@indextable t O.gid=t.nid
t.id>@PageLowerBound t.id<=@PageUpperBound t.id

nocount off

以上存储过程运用了SQL SERVERCREATE TABLE #TempSQL SERVERADO

nm

publish n m

TOP m-n+1 *
publish
(id
　　　　( TOP n-1 id
　　　　 publish))

id publish

ASP.NET+ C#SQL SERVER

pagination2
(
@SQL nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)

@Str nVARCHAR(4000)
@Str=+CAST(@RecsPerPage VARCHAR(20))++@SQL++@ID+
( TOP () T9 )
PRINT @Str
sp_ExecuteSql @Str

其实，以上语句可以简化为：

TOP 页大小 *
Table1
(ID
( TOP 页大小*页数 id
表
id))
ID

但这个存储过程有一个致命的缺点，就是它含有NOT IN

TOP 页大小 *
Table1

( * ( top (页大小*页数) * table1 id) b b.id=a.id )
id

即，用not existsnot in

TOP NOT IN

not existsSQL SERVERTOPTOPTOP

TOPNOT INTOPNOT INNOT IN

max()min()maxmin>

Select top 10 * from table1 where id>200

top 页大小 *
table1
id>
( (id)
( top ((页码-1)*页大小) id table1 id) T
)
id

在选择即不重复值，又容易分辨大小的列时，我们通常会选择主键。下表列出了笔者用有着1000GIDGIDgid,fariqi,title11010050010001102550

123

1

10

100 1076

500 540 12943

1000

1 24796

10

25

50

10010001

SQL SERVERSQLWEBSQL

--

pagination3
@tblName varchar(255),
@strGetFields varchar(1000) = ,
@fldName varchar(255)=,
@PageSize int = 10,
@PageIndex int = 1,
@doCount bit = 0,
@OrderType bit = 0,
@strWhere varchar(1500) =

@strSQL varchar(5000)
@strTmp varchar(110)
@strOrder varchar(400)

@doCount != 0

@strWhere !=
@strSQL = + @tblName + +@strWhere

@strSQL = + @tblName +





@OrderType != 0

@strTmp =
@strOrder = + @fldName +




@strTmp =
@strOrder = + @fldName +


@PageIndex = 1

@strWhere !=
@strSQL = + str(@PageSize) ++@strGetFields+ + @tblName + + @strWhere + + @strOrder

@strSQL = + str(@PageSize) ++@strGetFields+ + @tblName + + @strOrder





@strSQL = + str(@PageSize) ++@strGetFields+
+ @tblName + + @fldName + + @strTmp + + @fldName + + str((@PageIndex-1)*@PageSize) + + @fldName + + @tblName + + @strOrder + + @strOrder

@strWhere !=
@strSQL = + str(@PageSize) ++@strGetFields+
+ @tblName + + @fldName + + @strTmp +
+ @fldName + + str((@PageIndex-1)*@PageSize) +
+ @fldName + + @tblName + + @strWhere +
+ @strOrder + + @strWhere + + @strOrder


(@strSQL)

上面的这个存储过程是一个通用的存储过程，其注释已写在其中了。

9

113

258330

13ACCESS

1

2

12

fariqiID

maxminID

getdate()UNIQUE

maxmin

0

1

2