海量数据库查询语句

发布时间：2020-12-12 09:34:22 所属栏目：MsSql教程来源：网络整理

导读：以下代码说明了我们实例中数据库的“红头文件”一表的部分数据结构： DIV style="BORDER-RIGHT: #8a8a8a 1px solid; PADDING-RIGHT: 6px; BORDER-TOP: #8a8a8a 1px solid; PADDING-LEFT: 6px; FONT-SIZE: 12px; PADDING-BOTTOM: 6px; MARGIN: 1px; OVERFLOW:

2004112004101

SQL SERVER

IDID1GidSQL SERVERID

IDIDIDIDID

where1328

ID1000325

Select gid,fariqi,title from tgongwen

128470128

2fariq

gid,titleTgongwen
fariqi>dateadd(day,-90,getdate())

用时：5376354

3fariqi

gid,getdate())

用时：24232

251000ID12ID

selectdeclare @d datetime

set @d=getdate()

select

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

23fariqi

1000fariqi5003

compound index

25fariqineibuyonghu

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

2513

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

2516

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

60280

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

3326

select gid,title from Tgongwen where gid<=250000

4470

1/4

2order by

select gid,title from Tgongwen order by fariqi

12936

select gid,title from Tgongwen order by gid

18843

order by3/1010

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

6343100

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

317050

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

3326

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

3280

10020041150505000

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

6390

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

6453

SQL

SQLSQL SERVERSQLSQL SERVER

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

select * 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,3)

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,reader from tgongwen where charindex('',reader)>0 and fariqi>'2004-5-5'

7 4 7155 0 0

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

7 4 7155 0 0

8unionor

whereorunionor

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

68 1 404008 283 392163

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

union

select gid,title from Tgongwen where gid>9990000

9 8 67489 216 7499

unionor

orunionorunionor

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

6423 2 14726 1 7176

select gid,title from Tgongwen wherefariqi='2004-2-5'

11640 8 14806 108 1144

9select *

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

4673

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

1376

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

10count(*)count()

select count(*) from Tgongwen

1500

select count(gid) from Tgongwen

1483

select count(fariqi) from Tgongwen

3140

select count(title) from Tgongwen

52050

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

11order by

gidfariqi

select top 10000 gid,title from tgongwen

196 1 289 1 1527

select top 10000 gid,title from tgongwen order by gid asc

4720 1 41956 0 1287

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

4736 1 55350 10 775

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

173 1 290 0 0

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

156 1 289 0 0

order by order by

12TOP

I/0

select top 10 * from (

select top 10000 gid,title from tgongwen

where neibuyonghu=''

order by gid desc) as a

order by gid asc

1000010I/OI/OTOPTOPSQL SERVERTOPORACLEORACLErownumberTOP

web :ADO ADO

pagination1
(@pagesizeint,
@pageindexint
)

nocounton

@indextable(idintidentity(1,1),nidint)
@PageLowerBoundint
@PageUpperBoundint
@PageLowerBound=(@pageindex-1)*@pagesize
@PageUpperBound=@PageLowerBound+@pagesize
rowcount@PageUpperBound
@indextable(nid)gidTGongwenfariqi>dateadd(day,-365,getdate())fariqi
O.gid,O.mid,O.title,O.fadanwei,O.fariqiTGongwenO,@indextabletO.gid=t.nid
t.id>@PageLowerBoundt.id<=@PageUpperBoundt.id

nocountoff

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

publish n m

TOPm-n+1*
publish
(id
　　　　(TOPn-1id
　　　　publish))

id publish

ASP.NET+ C#SQL SERVER

pagination2
(
@SQLnVARCHAR(4000),
@Pageint,
@RecsPerPageint,
@IDVARCHAR(255),
@SortVARCHAR(255)
)

@StrnVARCHAR(4000)
@Str=+CAST(@RecsPerPageVARCHAR(20))++@SQL++@ID+
(TOP()T9)
PRINT@Str
sp_ExecuteSql@Str

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

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

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

Table1

(*(top(页大小*页数)*table1id)bb.id=a.id)
id

即，用not existsnot in

TOP NOT IN

not existsSQL SERVERTOPTOPTOP

TOPNOT INTOPNOT INNOT IN

max()min()maxmin><SARG

Select top 10 * from table1 where id>200

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

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

1 2 3

1 60 30 76

10 46 16 63

1001076 720130

50054012943 83

1000 17110 470250

1 24796 4500 140

10 38326 42283 1553

25 28140 128720 2330

50 121686 127846 7168

10010001

SQL SERVERSQLWEBSQL

pagination3
@tblNamevarchar(255),
@strGetFieldsvarchar(1000)=,
@fldNamevarchar(255)=,
@PageSizeint=10,
@PageIndexint=1,
@doCountbit=0,
@OrderTypebit=0,
@strWherevarchar(1500)=

@strSQLvarchar(5000)
@strTmpvarchar(110)
@strOrdervarchar(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)

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

113

258330

13ACCESS

fariqiID

maxminID

getdate()UNIQUE

maxmin

2比如
“主键就是聚集索引
这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。”
我觉得应该给主键加上聚集索引。他的测试数据也有问题，同一天内的记录居然有25万条，2004年5月5日的竟然达到了900万条记录。
这样的测试数据就导致了测试结果的普遍性。
也就是说他的测试产生的结论可能只适合1000万条记录、不平均分布的情况，却不适合几十万条记录、平均分布的情况。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

以下代码说明了我们实例中数据库的“红头文件”一表的部分数据结构：
<DIV style="BORDER-RIGHT: #8a8a8a 1px solid; PADDING-RIGHT: 6px; BORDER-TOP: #8a8a8a 1px solid; PADDING-LEFT: 6px; FONT-SIZE: 12px; PADDING-BOTTOM: 6px; MARGIN: 1px; OVERFLOW: auto; BORDER-LEFT: #8a8a8a 1px solid; PADDING-TOP: 6px; BORDER-BOTTOM: #8a8a8a 1px solid; FONT-FAMILY: Courier New; BACKGROUND-COLOR: #eeeeee">CREATETABLE[dbo].[TGongwen](--TGongwen是红头文件表名
[Gid][int]IDENTITY(1,1)NOTNULL,
--本表的id号，也是主键
[title]varcharCOLLATEChinese_PRC_CI_ASNULL,
--红头文件的标题
[fariqi][datetime]NULL,
--发布日期
[neibuYonghu]varcharCOLLATEChinese_PRC_CI_ASNULL,
--发布用户
[reader]varcharCOLLATEChinese_PRC_CI_ASNULL,
--需要浏览的用户。每个用户中间用分隔符“,”分开
)ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]
GO

1000

@iint
@i=1
@i<=250000

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

declare @i int

set @i=1

while @i<=250000

begin

insert into Tgongwen(fariqi,title) values('2004-9-16','',',,,,,,,,,,','25')

set @i=@i+1

end

@hint
@h=1
@h<=100

@iint
@i=2002
@i<=2003

@jint
@j=0
@j<50

@kint
@k=0
@k<50

Tgongwen(fariqi,title)(cast(@ivarchar(4))++cast(@jvarchar(2))++cast(@jvarchar(2)),)
@k=@k+1

@j=@j+1

@i=@i+1

@h=@h+1

@i=1
@i<=9000000

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

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

index,SELECT

SQL SERVERclustered indexnonclustered index

anazazhang

67263390