Indeks
Meningkatkan Kinerja Kueri di SQL Server
Dean Smith
Founder, Atamai Analytics
Apa itu indeks?
- Struktur untuk mempercepat akses data dari tabel
- Menemukan data cepat tanpa memindai seluruh tabel
- Meningkatkan kinerja kueri dengan kondisi filter
- Diterapkan pada kolom tabel
- Biasanya ditambahkan oleh administrator basis data
Indeks terklaster dan nonterklaster
Indeks Terklaster
- Analogi: kamus
- Halaman data tabel diurutkan oleh kolom indeks
- Hanya satu per tabel
- Mempercepat pencarian
Indeks terklaster dan nonterklaster
Indeks Terklaster
- Analogi: kamus
- Halaman data tabel diurutkan oleh kolom indeks
- Hanya satu per tabel
- Mempercepat pencarian
Indeks Nonterklaster
- Analogi: buku teks dengan indeks di belakang
- Struktur berisi lapisan penunjuk indeks terurut ke halaman data tabel yang tidak terurut
- Satu tabel bisa punya lebih dari satu
- Meningkatkan operasi insert dan update
Indeks terklaster: struktur B-tree
- ROOT NODE
- BRANCH NODES
- PAGE NODES
Indeks terklaster: struktur B-tree
ROOT NODE:
A G O W
BRANCH NODES:
A B E F G H J K O P S T
PAGE NODES:
Halaman 1 Halaman 2 Halaman 3 Halaman 4
Kolom Indeks |... Kolom Indeks |... Kolom Indeks | ... Kolom Indeks | ...
A | ... E | ... I | ... M | ...
B | ... F | ... J | ... N | ...
C | ... G | ... K | ... O | ...
D | ... H | ... L | ... P | ...
... ... ... ...
Tabel Customers tanpa indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Halaman 1
CustomerID |...
ALFKI | ...
ANATR | ...
BLONP | ...
BSBEV | ...
...
Tabel Customers tanpa indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Halaman 1 Halaman 2
CustomerID |... CustomerID|...
ALFKI | ... FOLIG | ...
ANATR | ... FRANK | ...
BLONP | ... GALED | ...
BSBEV | ... GREAL | ...
... ...
Tabel Customers tanpa indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Halaman 1 Halaman 2 Halaman 3
CustomerID |... CustomerID|... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ...
ANATR | ... FRANK | ... LINOD | ...
BLONP | ... GALED | ... MEREP | ...
BSBEV | ... GREAL | ... MORGK | ...
... ... ...
Tabel Customers tanpa indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
PAGE NODES:
Halaman 1 Halaman 2 Halaman 3 Halaman 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ...
BSBEV | ... GREAL | ... MORGK | ...
... ... ... ...
Tabel Customers dengan indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
ALFKI FOLIG OLDWO WOLZA
BRANCH NODES:
ALFKI BONAP DRACD FISSA FOLIG GALED LILAS NORTS OCEAN OLDWO QUICK WOLZA
PAGE NODES:
Halaman 1 Halaman 2 Halaman 3 Halaman 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ... PICCO | ...
BSBEV | ... GREAL | ... MORGK | ... QUICK | ...
... ... ... ...
Tabel Customers dengan indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
ALFKI BONAP DRACD FISSA FOLIG GALED LILAS NORTS OCEAN OLDWO QUICK WOLZA
PAGE NODES:
Halaman 1 Halaman 2 Halaman 3 Halaman 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ... PICCO | ...
BSBEV | ... GREAL | ... MORGK | ... QUICK | ...
... ... ... ...
Tabel Customers dengan indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
OLDWO QUICK
PAGE NODES:
Halaman 1 Halaman 2 Halaman 3 Halaman 4
CustomerID |... CustomerID|... CustomerID | ... CustomerID | ...
ALFKI | ... FOLIG | ... LILAS | ... OCEAN | ...
ANATR | ... FRANK | ... LINOD | ... PARIS | ...
BLONP | ... GALED | ... MEREP | ... PICCO | ...
BSBEV | ... GREAL | ... MORGK | ... QUICK | ...
... ... ... ...
Tabel Customers dengan indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
OLDWO QUICK
PAGE NODES:
Halaman 4
CustomerID | ...
OCEAN | ...
PARIS | ...
PICCO | ...
QUICK | ...
...
Tabel Customers dengan indeks terklaster
SELECT *
FROM Customers
WHERE CustomerID = "PARIS"
ROOT NODE:
OLDWO WOLZA
BRANCH NODES:
OLDWO QUICK
PAGE NODES:
Halaman 4
CustomerID | ...
PARIS | ...
Indeks terklaster: contoh
SET STATISTICS IO ON
SELECT *
FROM PlayerStats
WHERE Team = 'OKC'
Tabel PlayerStats tanpa indeks
Table 'PlayerStats'. ..., logical reads 12, ...
Tabel PlayerStats dengan indeks terklaster pada Team
Table 'PlayerStats'. ..., logical reads 2, ...
Ayo berlatih!
Meningkatkan Kinerja Kueri di SQL Server
