[edit]
1 Clustered Index Seek螳 襷 螳 Seek瑚? #
ろ螻 蠍 覃 Seek, Scan 碁煙るゼ 蟆郁骸 螳語讌 覲願 . 蠏碁 伎 豐覲企 Clustered Index Scan企手 る 碁煙り 一企 譴 . (覓朱 磯殊 一 蟆曙磯 .) 譟郁 螳 讌覃 Clustered Index Seek襯 り 譬 . 讌襷 譴 蟆 豎る. 覦襦 襷朱 譟壱讌 覃 豌企ゼ 譟壱讌 蟆曙一 る 蟆企. 企覿 讌 2~3谿 DB企る 覈襯企 蟆曙郁 朱襦 覺蠍 覦. れ螻 螳 蟆曙郁 蠏碁.
--譴觜
WITH temp(num)
AS
(
SELECT 0 num
UNION ALL
SELECT num + 1 FROM temp
WHERE num + 1 <= 10000
)
SELECT
num
, newid() guid_col
, REPLICATE('0', 5000) chr_data
INTO #temp
FROM temp
OPTION (MAXRECURSION 0);
CREATE CLUSTERED INDEX cix
ON #temp(num);
--ろ
SET STATISTICS IO ON
SELECT * FROM #temp
WHERE num > 0;
磯Μ 螳覦 襷一るゼ 蠏碁 ろ螻 螳碁 螻 Clustered Index Seek手 譬 蟆企. 讌襷 願 讌讌 轟 Clustered Index Seek瑚? . 願唄 Clustered Index Scan螻 螳 蟆企. SET STATISTICS IO ON る I/O 覲 蟆企. 蟆曙 れ螻 螳.
/* SET STATISTICS IO ON SELECT * FROM #temp WHERE num > 0; --Scan count 1, logical reads 10023, physical reads 0, read-ahead reads 6 */ SELECT * FROM #temp --Scan count 1, logical reads 10023, physical reads 0, read-ahead reads 6
覲企れ I/O 螳. 觜 觜蟲企慨覃 れ螻 螳 Clustered Index Seek Clustered Index Scan 狩る 蟆 . 蠏碁覩襦 覿 Clustered Index Seek襯 り 覃 . 覦 I/O 覺 .
SELECT * FROM #temp WHERE num > 0 SELECT * FROM #temp;
螳讌 譯殊伎 企蟆 2螳 伎 SQL覓語 ろ ろ螻 覈 覃 蠏碁 ろ螻 朱 襷一るゼ 企Ν 蟆螳 ろ螻 覲 る 蟆企. 覈 SQL ろ螻 焔蠍 ろ螻 朱 蠍磯 れ螻 螳 豌 ろ螻 150%螳 殊 覯蠏碁ゼ 覲 . 谿瑚襦 SQL Server 2008覯企.
覓朱 企 GUI豌伎 覓語. 蟆朱り れ ろ螻朱 螳蟇磯 豕 豕覃 覃伎 螳煙, 伎 覦襦 螳煙讌 朱襦 譯殊伎 . 覲蟇 .
[edit]
2 Clustered Index螳 Non-Clustered Index覲企 觜襯企? #
MS-SQL Server Oracle Server 螳 DBMS 豕 /豢 襯 螳讌. MS-SQL Server7.0覯 伎覿磯 8KB 伎螳 豕 /豢 企. 伎 る襯 觜朱 伎 8060 Byte蟾讌襷 ル. 蠏碁覩襦 襦 襷れ 企 1Row 5000 Byte螳 朱襦 1伎 1Row襷 ル . 3000 Byte 讌 覈詩 觜 覦. 企 蟆曙 Clustered Index襯 襷る 100Row襯 蠏狩 企 100伎 伎 曙伎 覦.
SELECT num FROM #temp WHERE num BETWEEN 1 AND 100 --Scan count 1, logical reads 105, physical reads 0, read-ahead reads 0
襷 num貉殊 Non-Clustered Index襯 燕覃, SQL Server 覓語 轟壱 Non-Clustered Index襯 曙 蟆企. 覃 轟壱 觜 蟆 り鍵 覓語企. Non-Clustered Index襯 燕覃 覈 Page 曙蟾?
create index nix on #temp(num); SELECT num FROM #temp WHERE num BETWEEN 1 AND 100 --Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0
蠎企 2伎 曙. 企 覓語 覓危焔螻 Non-Clusterd Index覲企 Clustered Index螳 觜襯企り 覈 蟆 . 朱蠍 曙企. 企 譬襯 貎朱Μ Covered Index襯 蟆碁, 襷 覓語 蟆壱 碁煙るゼ 伎手鍵 覦 朱 譴 蟆企. 襷 Clustered Index襷 螳 伎 貎朱Μ螳 譯 觜覯蟆 螻 る Clustered Index Non-Clustered Index 螻旧ヾ伎 . 企 蟆曙磯 蠏豪 蟆曙一伎襷 螳 貉殊 2譬襯 碁煙り 焔 覿覿 Non-Clustered Index襯 讌襷 企 る 蟆 螻[1]伎 .
[edit]
3 伎(豕 /豢 ) 覦覯 #
磯Μ 企 り 磯殊 螻糾 朱 讌 覈詩 危ｴ覲伎. char_data螳 xml覓語手 螳螻, 5000 Byte手 螳. num貉殊 Primary Key(危 PK)手 螳. 襷 譟郁唄朱 num guid_col 覯 蟆螻, 蠏 譴 xml覓語襷 螻朱慨 [2] 覲伎碁る 企至 伎朱 蟾? 襭 '讌覿'企. 讀, A{num, guid_col}, B{num, char_data}襦 2螳 (企) 蟲燕 蟆企. 蠏碁 蟆 A企 伎螻, 一危磯 B 1螳 覃 蟆企.
{* 企 伎企.}
蠏碁る '朱 リ概螳 朱 螻, 焔レ 襴 覦覯 蟾' 螻 蟠蠍讀 螳讌 蟆企. 譯 譬 蟠蠍讀企. 覦覯 蟆螳? 蠏 覦覯 覦襦 '蠏'. 蠏碁 蠏螳 豕螳 焔ロ 蟲伎 豕螻 覓願屋 覲伎 蟲手 蟆企. 蠏 DBMS 豕 /豢 る譟磯 一危郁 覈 蟆 伎. 蠏碁覩襦 リ概螳 觜襯 襷螻, 焔レ レ貅譯朱伎 覓願屋炎讌 覲危 一危磯伎れ 豕螳 蟲. 豕螳 蟲. 豕螻 蟲. 譬觜讌. 讒譯殊. 豸! 磯! 覘讌? .. 蠏 (--)b
[edit]
4 update #
--clustered index update test
drop table test
create table test(seq int identity primary key, id varchar(8000))
go
set statistics io off
set nocount on
insert test(id) values(replicate('a',3000))
go 10000
set statistics io on
set nocount on
select * from test
--企 'test'. 蟆 1, 朱Μ 所鍵 5020, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0, LOB 朱Μ 所鍵 0, LOB 覓朱Μ 所鍵 0, LOB 覩碁Μ 所鍵 0.
select * from test
where seq = 100
--企 'test'. 蟆 0, 朱Μ 所鍵 3, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0, LOB 朱Μ 所鍵 0, LOB 覓朱Μ 所鍵 0, LOB 覩碁Μ 所鍵 0.
update test
set id = replicate('a',6000)
where seq = 100
select * from test
--企 'test'. 蟆 1, 朱Μ 所鍵 5021, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0, LOB 朱Μ 所鍵 0, LOB 覓朱Μ 所鍵 0, LOB 覩碁Μ 所鍵 0.
select * from test
where seq = 100
--企 'test'. 蟆 0, 朱Μ 所鍵 3, 覓朱Μ 所鍵 0, 覩碁Μ 所鍵 0, LOB 朱Μ 所鍵 0, LOB 覓朱Μ 所鍵 0, LOB 覩碁Μ 所鍵 0.
--varchar(500)朱 螻 fill_factor 譟一 企慨..
drop table test2
create table test2(
seq int identity
, id varchar(500)
, constraint pk_test2 primary key clustered(seq) with(fillfactor=90)
)
go
alter table test2
drop constraint pk_test2
alter table test2
add constraint pk_test2 primary key clustered(seq)
with(fillfactor = 90)
