Contents

1 ろ碁ゼ 譴觜
2 Loop Join 蠍磯蓋
3 牛磯伎 employee 企覿 曙蟾?
4 employee.job_id Non-Clustered Index襯 燕覃?
5 employee.job_id Clustered Index襯 燕覃?
6 讓 郁屋螻襴[1] 碁煙り る?
7 Network Join
8 Loop Join 襴
9 Advanced Loop Join
10 谿瑚襭


1 ろ碁ゼ 譴觜 #

郁屋 ′語 覿 伎 企螳 蟯螻襯 襾殊 . 螳 ERD螳 朱 譬. 蠍一 MSSQL Server れ伎願係 蟆. れ MSSQL Server 一危磯伎れ Pubs 一危磯伎れ Employees 企螻 Jobs 企 螳讌螻 る. 企 蠍磯蓋 覲企 れ螻 螳.

loop_join01.jpg

use pubs
exec sp_helpindex 'employee'
exec sp_helpindex 'jobs'
go
loop_join02.jpg

INDEX_KEYS 貉殊 覲企 企 貉殊 碁煙り 焔 讌 覲 . 蠍一 EMPLOYEE企螻 JOBS企 譟一誤 蟆碁 郁屋螻襴(FOREIGN KEY)螳 貉(蠍一 EMPLOYEE.JOB_ID 貉) 碁煙り ″讌 蟆 覲 . 蠏碁る れ螻 螳 貎朱Μ襯 MSSQL SERVER 2000 企至 ろ螻 語 蟆瑚? (覓朱 蠍一 朱 糾覲企ゼ 一危誤螻 る 螳 襷 蟆企.)

2 Loop Join 蠍磯蓋 #


set statistics io on --豢レ  覲願鍵  れ

--1
use pubs
select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from employee e inner join jobs j
on e.job_id = j.job_id
go

--企 'jobs'. 蟆  0, 朱Μ 所鍵  86
--企 'employee'. 蟆  1, 朱Μ 所鍵  2
loop_join03.jpg

ろ螻 覲企 EMPLOYEE 企覿 渚讌 蟆 覲伎碁. ろ螻 るジ讓 一, 讓 一朱 襾殊 ろ. 碁Μ襯 螳覃 . ろ螻螻 /豢 牛 貎朱Μ襯 覿 覲企襦 . 蟆磯覿 伎手鍵覃 貎朱Μ NESTED LOOP JOIN譟一語企, 企譟一(INNER JOIN)企. 蠏碁Μ螻 EMPLOYEE企 襾殊 渚譟. 讀, れ螻 螳 襦 企 渚讌 蟆企.

loop_join04.jpg

蟆郁骸 讌 JOB_ID 襯 覲企 13, 14, 6, 12 ... . EMPLOYEE 企 襾殊 れ 螻殊 JOBS企 企ろ磯 碁煙(JOB_ID) 觜蟲 螳 蟆 蟆郁骸讌朱 觸. /豢 覲企 れ螳 43 蟆 覲 . 願 EMPLOYEE 企 43螳 伎 JOBS 企 JOB_ID襯 觜蟲 蠏碁 豺螳 蟆企. 襾殊 れ(企ろ磯 碁煙 れ れ螻 螳. 企ろ磯 碁煙れ 蟲譟一..) 語 EMPLOYEE.JOB_ID 碁煙り ″讌 蠍 覓語企.

loop_join05.jpg

襷 JOBS企 襾殊 暑り る 企 ろ螻 ″讌蟾? れ螻 螳 螳襦 JOBS 企 襾殊 暑襦 貎朱Μ企慨.

--2
use pubs
select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from jobs j inner join employee e --襯 覦蠑瑚
on e.job_id = j.job_id
option (force order, loop join) --旧朱 from 企 襦 譟一誤襦 .
go
--企 'employee'. 蟆  1, 朱Μ 所鍵  29
--企 'jobs'. 蟆  1, 朱Μ 所鍵  2
loop_join06.jpg

蟆郁骸 れ.
loop_join07.jpg

豐 朱Μ 所鍵 (i/o) 豌 覯讌 貎朱Μ 2+86願, 覯讌 貎朱Μ 2+29襦 谿願 . 覲 豌襴 觜[2] れ伎 螻 . 襷 蟾讌 る Order By job_id襯 貎朱Μ 觜谿襯 觜蟲伎 觜 貎朱Μ襯 .

貎朱Μ襯 貅 觜 觜蟲襯 企慨.
use pubs

--1
select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from employee e inner join jobs j
on e.job_id = j.job_id
go

--2
select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from jobs j inner join employee e --襯 覦蠑瑚
on e.job_id = j.job_id
option (force order, loop join) --旧朱 from 企 襦 譟一誤襦 .
go

loop_join08.jpg

3 牛磯伎 employee 企覿 曙蟾? #

觜 觜蟲 覲 蟆郁骸 貎朱Μ1:貎朱Μ2 = 57:43朱 觜谿願 覿蟲螻 牛磯伎 觜 襷 覦 覦(employee 企覿 暑 覦)朱 ろ螻 語蟾? 襷 employee 企 Row螳 1覦焔 蟇伎企朱 覯讌 貎朱Μ螳 觜 蟆 覦蟾? employee 企 1覦焔 蟇 讌襴 覲旧覲語朱 ろ 企慨.
--drop table #employee
select top 1000000 a.* into #employee
from employee a, employee b, employee c, employee d

ろ碁ゼ 企慨 Loop Join 讌 螻, Hash Join朱 覯襴磯. 朱 蠍一 Loop Join 覲願鍵 螻褐企襦 Join Hint襯 譯殊 Loop Join朱 覦蠖 覲願.

select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from #employee e inner loop join jobs j 
on e.job_id = j.job_id
--企 'jobs'. 蟆  0, 朱Μ 所鍵  2000000
--企 '#employee___000000000006'. 蟆  1, 朱Μ 所鍵  6455

覿 企慨. 貎朱Μ #employee 企 れ. 6455 伎 襷殊 れ 企. れ朱 jobs 企 曙. jobs 企 Row螳 朱讌 伎 l . 讌襷 Clustered Index螳 焔 伎 2伎襯 曙伎 . Clustered Index Seek襯 企 伎 豕 2伎襯 曙伎 . #employee 企 1覦焔 蟇伎企襦 1覦焔 覯 Loop襯 螳覃伎 jobs 企 れる 蟆企. 蠏碁 jobs企 I/O 2000000朱 螳 蟆企.

select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from jobs j inner join #employee e --襯 覦蠑瑚
on e.job_id = j.job_id
option (force order, loop join, maxdop 1)
--企 'Worktable'. 蟆  1, 朱Μ 所鍵  3009266
--企 '#employee___000000000006'. 蟆  1, 朱Μ 所鍵  6455
--企 'jobs'. 蟆  1, 朱Μ 所鍵  2

貎朱Μ 螳 覿 企慨. jobs 企 2 伎襯 曙螻, #employee 企 碁煙り 朱襦 6455 伎襯 jobs 企 Row 襷 曙伎 れ語 . 蠏碁覩襦 螻一朱 企慨覃 14(jobs)蟇 * 6455 = 90370 伎朱 蟆郁骸螳 . 讀, jobs 企 1 れ, #employee 企 14 れ企. 讌襷 一危磯伎 ろ 一危磯 蟆 螻旧 蟆 蠍磯蓋企襦 企 一伎 れ 蟆 蠍( 螻旧蠍) 蠍磯襦 所 觚襦轟 覦蟆 . 譴螳 るジ 碁 豺螻 れ伎 Lock企朱 蟇碁 譟壱 焔レ 蠍蟆 觜讌. 蠏碁 SQL Server 企 一危磯ゼ 曙 14覯 れ Join 一一 企 蟆 煙 碁. set statistics io on 蟆郁骸 'Worktable' 企( 蠏碁 ろ螻 TableSpool) 襷.

loop_join09.jpg

螻壱 覲 I/O覲企 3009266朱 襷 I/O . 企蟆 螳覃 . 企 ロ 覦 I/O 企 碁煙り 焔 Seek 3伎 I/O螳 り 螳覃 . れ TableSpool 襷一るゼ 螳碁 覃 14(jobs)*1000000(#employee) 襷殊 れ 螳 . れ襦 碁ゼ 觜朱 牛磯伎螳 企 伎蟲 ろ螻 語一 朱 蟇煙讌 .

4 employee.job_id Non-Clustered Index襯 燕覃? #

#employee.job_id Non-Clustered Index襯 燕覃 ろ螻 企至 覲蟾? Loop Join 碁ゼ 觜手 ろ 企慨.

create index idx_job_id
on #employee(job_id)
go

--1
select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from #employee e inner join jobs j
on e.job_id = j.job_id
go

--Table '#employee'. Scan count 1, logical reads 6455
--Table 'jobs'. Scan count 1, logical reads 2
loop_join10.jpg

蟆郁骸 Hash Join企. 朱Μ 所鍵 企 れ 襷殊企. 企 襦蟆 焔 碁煙るゼ 伎蟶朱朱 蟆碁 れ る . Loop Join 朱 Index Seek 一一襯 覲 蟆讌襷[3] SQL Server Loop Join Hash Join ろ螻 豕貅磯. 蠏碁り る讌 . 磯Μ螳 #employee 企 emp_id 螳 譬讌 蠍 覓語 燕伎 碁煙る 蟇一 碁螳 . 豕豐 '#employee.job_id 碁煙るゼ 燕覃 ろ螻 企至 覲蟾?'朱 讌覓 豌願 觚 螳 蟆伎.

5 employee.job_id Clustered Index襯 燕覃? #

employee.job_id Clustered Index襯 燕覃 覦. Clustered Index 轟煙 郁屋螻襴螳 Clustered Index朱 Merge Join 螳レ煙 , jobs 企 Hash Join 螳ル 襷れ . 覓朱 Loop Join Merge Join襷殊企 螳レ煙 . 2000覯 蟆曙磯 企蟆 讓 郁屋螻襴螳 朱 Merge Join朱 ろ螻 碁. 讌襷 2000危 覯 Loop Join朱 ろ螻 襴 螳レ煙 .
select  e.emp_id, e.fname + e.lname as name, j.job_id, j.job_desc
from #employee e inner join jobs j
on e.job_id = j.job_id
go
loop_join11.jpg

6 讓 郁屋螻襴[4] 碁煙り る? #

讓 郁屋螻襴 碁煙り る Loop Join 螳豺 襷 . 螳 Loop Join朱 ろ螻 襴 蟆朱 Index Spool Table Spool 覦 蟆企. 郁屋螻襴螳 郁屋 2螳 伎 企 ′語 譴 譴 るジ 譟郁唄 譟伎る Loop Join . 讓 郁屋螻襴 碁煙り 蟆曙 覿覿 Merge JoinHash Join朱 襴磯. 螳レ煙 Hash Join 襷.

7 Network Join #

覿磯 覯 蟆曙 覓朱Μ朱 覿襴 覯 螳 企螻 譟一誤 蟆曙郁 豺 蟆 . 企 蟆曙磯ゼ 'Network Join'企手 [5]. れ るゼ 覲伎.

SELECT
,	A.Col1
,	A.Col2
,	(SELECT TOP 1 Col3 FROM Remote.TestDB.dbo.TableB WHERE A.Key = B.Key) Col3
FROM dbo.TabeA A
WHERE A.Col4 BETWEEN 1 AND 1000
--蟆郁骸 1000蟇
nt_join.jpg

蟆郁骸螳 1000蟇伎企朱 Localhost Remote Server螳 ろ語 血 1000 譯手 覦朱伎 Loop Join 蟆 . 襷れ 譬 蟆曙一企. 磯殊 企蟆 ろ語襯 牛 血 譯手 覦朱伎 Join 蟆覲企 蟆 覯 郁屋 企 れ伎 螳語 企 伎 , 企螻 Join 蟆 觜襯 蟆曙磯 . 螳ロる Network Join 覦る ろ螻 危エ覲伎 . 螳ロ覃 蟆 るゼ 襾殊 暑 覦覯 豬 蟆 覦讌.

8 Loop Join 襴 #

Join 覦 螳讌螳 讌襷 蠍一 Loop Join る 螳 襴 蟆企. 覲 豌襴 ろ螻企 れ覩襦 蠍一 讌豌襴(CPU 1螳襷 ) ろ螻 蟆曙磯朱 . 覿 蟆曙一 磯殊 企 ′語 蟆暑[6]螳 襴 蟆語 蟆一 伎 .

  • Join Key 譴 讓曙 碁煙り 蟆曙
  • Join Key 譴 讓曙 碁煙り 蟆曙
    • 讓 郁屋 螻襴螳 覈 Clustered Index 蟆曙
    • 讓 郁屋 螻襴襷 Clustered Index 蟆曙
    • 讓 郁屋 螻襴螳 覈 Non-Clustered Index 蟆曙
  • Join Key 譴 讓曙 碁煙り 蟆曙

9 Advanced Loop Join #

oracle 覯覲 讌. れ螻 螳 企 .
01.png

企れ 覓朱Μ朱 れ螻 螳 螳 觚襦 覿 .

02.png

れ螻 螳 . 蠍一 覓朱ΜI/O れ′語るゼ 襷. 企 襷豺 sql server 企 '覿ID' 企ろ磯 碁煙るゼ 燕 螳 I/O螳 譴企. 企 企ろ磯 磯殊 焔レ る 覩誤.

03.png

10 谿瑚襭 #