聚合（聚集）索引____非聚合（聚集）索引
聚合（聚集）索引____Order of?data records?is the same as the order of?index data entries（只能有一個(gè)）
聚合（聚集）索引____Otherwise
1. ?估算結(jié)果記錄集大小
expected?size of the result?(number of tuples and/or number of pages)
size of relation * PAI (reduction factors ?= ?RF) ? ? ? ? ? ? PAI相當(dāng)于連續(xù)相乘
??Sailors (S):
–Each tuple is 50 bytes long, 80 tuples per page,?500 pages
–N = NPages(S) = 500, pS=NTuplesPerPage(S) = 80
–NTuples(S) = 500*80 = 40000
??Reserves (R):
–Each tuple is 40 bytes long, 100 tuples per page,?1000 pages
–M= NPages(R) = 1000, pR=NTuplesPerPage(R) =100
–NTuples(R) = 100000
2. ?no index,?unsorted:
Cost = Number of Pages of Relation, i.e. NPages(R)
Example: Reserves cost(R)= 1000 IO (1000 pages)
3. ?no index, but file is sorted:
Cost = log2(NPages(R)) + (RF*NPages(R))
Example: Reserves cost(R)= 10 I/O +?(RF*NPages(R))
4. ?Clustered index:
Cost = (NPages(I) +?NPages(R))*RF ? ? ? ? ? ?NPages(I)：索引頁數(shù)
5. ?Unclustered index: ?
Cost = (NPages(I) +?NTuples(R))*RF
6. B樹
B樹：二叉搜索（查找）樹
B-樹：是一種平衡的多路查找樹（并不是二叉的）巷怜。關(guān)鍵字集合分布在整顆樹中竿拆。在非葉子節(jié)點(diǎn)，可以查找成功并結(jié)束扭弧。
B+樹：是B-樹的一種變形蕉陋。所有關(guān)鍵字都在葉子結(jié)點(diǎn)出現(xiàn)捐凭。不保存數(shù)據(jù)，只用于索引凳鬓，不可能在非葉子結(jié)點(diǎn)命中茁肠。
下面我們就來看一下在1000萬條數(shù)據(jù)量的情況下各種查詢的速度表現(xiàn)（3個(gè)月內(nèi)的數(shù)據(jù)為25萬條）：???? ?
（1）僅在主鍵上建立聚集索引，并且不劃分時(shí)間段：???? ?
Select gid,fariqi,neibuyonghu,title from tgongwen? 用時(shí)：128470毫秒（即：128秒）???? ?
（2）在主鍵上建立聚集索引缩举，在fariq上建立非聚集索引：??? ?
select gid,fariqi,neibuyonghu,title from Tgongwen where? fariqi> dateadd(day,-90,getdate())?? 用時(shí)：53763毫秒（54秒）???? ?
（3）將聚合索引建立在日期列（fariqi）上：??? ?
select gid,fariqi,neibuyonghu,title from Tgongwen where? fariqi> dateadd(day,-90,getdate()) 用時(shí)：2423毫秒（2秒）??
