Delphi - MS SQL adatbázis kezelés Delphi-ből

24. rész

forráskód letöltése
Indexek használata

Az indexek lehetővé teszik, hogy megtaláljunk egy adatot a táblában anélkül, hogy az egészet végigolvasnánk. Így hasznos dolog az index használata, de ismernünk kell nem csak a lehetőségeit, hanem annak előnyeit, hátrányait is. Indexet úgy képzünk, hogy a táblából kimásoljuk azokat a kulcsokat, amelyek értékeinek ismeretében gyakran akarunk az egyes rekordokhoz hozzáférni, és minden egyes kulcs mellé odaírjuk, hogy az ahhoz tartozó rekord hol található meg a táblában. Készíthetünk indexet egyetlen oszlopra, vagy készíthetünk több oszlop kombinációjából összetett indexet is. Az indexállomány a kulcsokat kiegyensúlyozott B-fában tárolja. A B-fa a kulcs szerint rendezett, így rendkívül hatékonyan lehet benne keresni. Egy táblára több indexet is definiálhatunk. Az SQL Server automatikusan előállít indexet a PRIMARY KEY és UNIQUE oszlopokra. Az indexek használatának természetesen ára van. Az index állományok helyet foglalnak a háttértáron, az INSERT, UPDATE és DELETE utasítások végrehajtásakor a táblák manipulálása mellett az indexállományokat is fel kell frissíteni, így ezek a műveletek tovább tartanak; mégpedig annál tovább, minél több indexet definiáltunk a táblára. Ezért az indexek használatakor ügyesen kell egyensúlyozni a lekérdezési idők csökkentése és a karbantartási idők növekedése között.

Amikor az SQL Server végrehajt egy lekérdezést, a lekérdezés optimalizáló (query optimizer) megbecsüli a rendelkezésre álló metódusok költségeit, és a legoptimálisabbat választja a lekérdezéshez. Ez lehet a tábla soronkénti végigolvasása (table scan), vagy lehet index használata is, ha van. Hogy eldönthessük, milyen indexeket definiáljunk egy táblához, tudnunk kell, hogy miben segítenek minket konkrétan az indexek.
  • Ha keresünk egy rekordot, és pontosan tudjuk a hozzá tartozó kulcsot. (exact match query)
  • Ha több rekordot is keresünk, és tudjuk, hogy a hozzájuk tartozó kulcsok milyen tartományba esnek. (range query)
  • A T1 táblában keressük azokat a rekordokat, amelyek egy join-on keresztül megfelelnek bizonyos rekordoknak a T2 táblában. (index nested loops join)
  • Az indexek segítik a PRIMARY KEY és a UNIQUE constraint-ek érvényre jutását INSERT vagy UPDATE utasításnál, hiszen az esetleges duplikációk rögtön lelepleződnek.
  • Két, FOREIGN KEY-el összekapcsolt tábla sorainak megfeleltetése is sokkal gyorsabb indexek segítségével.


Ha eldöntöttük, hogy hová teszünk indexet, már csak létre kell hozni azokat. Az SQL Server kétféle indexet kínál: fürtözött (clustered) és nem fürtözött (non-clustered) indexeket.


Fürtözött indexek

A fürtözött index a tábla sorait a kulcsértékeknek megfelelően fizikailag rendezi. Mivel egy táblának egyszerre csak egyféle fizikai sorrendje lehet, minden táblához csak egy fürtözött index tartozhat. A fürtözött index a leghatékonyabb módszer, hogy az indexkulcs alapján megtaláljunk egy sort, vagy egy sortartományt. Sortartomány esetén például elég a tartomány legelső sorát megkeresni, a szomszédos index értékek garantáltan a szomszédos rekordokat fogják kijelölni. Például ha egy táblából mindig két dátum közti értéket kell leválogatnunk, akkor ha a dátum mezőre fürtözött indexet definiálunk, igen gyors lekérdezéseink lesznek. Hiszen csak az első dátumot kell megkeresni, utána pedig addig olvasni a sorokat sorban, amíg a végső dátumhoz nem érünk. Ha ezen kívül még gyakran kell dátum szerint sorba rendezni az adatokat, sokat spórolhatunk azon, hogy ez a tábla rögtön ebben a sorrendben produkálja a leválogatásokat.

Mikor jó tehát a fürtözött index?
  • Ha az oszlop nem túl sok különböző értéket tartalmaz. Például megyéket, amiből összesen 19 van.
  • Ha olyan lekérdezéseket írunk, amelyek egy tartományt eredményeznek. (BETWEEN, >, >=, <, <=)
  • Nagy eredményhalmazok lekérdezésekor.
  • Ha a lekérdezésben JOIN vagy GROUP BY van, általában az adott oszlopnév egyben FOREIGN KEY is. Ha erre az oszlopra fürtözött indexet definiálunk, megspórolunk egy sorba rendezést az SQL Servernek, mivel az adatok már úgyis sorban vannak.



Nem fürtözött indexek

A nem fürtözött indexek a sorok logikai sorrendjére utalnak. A lekérdezéseket gyorsíthatjuk fel a segítségükkel. A nem fürtözött indexek ugyanúgy B-fában valósulnak meg, mint a fürtözött indexek, csak ebben az esetben nem rendezzük a fát. A nem fürtözött indexek a kulcsok mellett nem a rekord fizikai helyét tartalmazzák, hanem a fürtözött index megfelelő kulcsát, ezért fontos, hogy a fürtözött index oszlopmérete minél kisebb legyen. Egy nagy oszlopméretű fürtözött index rengeteg helyet foglal el azzal, hogy szerepel az összes nem fürtözött indexben is.

A nem fürtözött indexeket mindig a fürtözött indexek után célszerű létrehozni, mert a fürtözött index megváltoztatja a tábla sorainak fizikai sorrendjét. Egy fürtözött index létrehozása vagy törlése után az SQL Server újragenerálja a az összes nem fürtözött indexet.

Mikor jó egy nem fürtözött index?
  • Ha az oszlop nagy számú különböző értéket tartalmaz. Ha csak keveset, akkor fürtözött indexet kell inkább használnunk, ha csak 1-2 különböző értéket, akkor nincs értelme indexet használni.
  • Ha fürtözött indexet kéne használnunk, de már van egy.
  • Kis eredményhalmazok lekérdezésekor.
  • Olyan oszlopokra, amelyek gyakran szerepelnek WHERE utáni feltételben.



Index létrehozása

Az SQL Server automatikusan egyedi indexeket hoz létre, hogy kikényszerítse a PRIMARY KEY és a UNIQUE constraint-ek esetén előírt egyediségek. Az egyedi index biztosítja, hogy egy indexelt oszlop minden adata egyedi legyen, és ne tartalmazzon duplikált értéket. Ha még nem létezik fürtözött index a táblán vagy nem kérünk kifejezetten nem fürtözött indexet, egy egyedi, fürtözött index jön létre, ha definiálunk egy PRIMARY KEY constraint-et. Hasonlóan hacsak nem fürtözött indexet kérünk, egy egyedi, nem fürtözött index jön létre, ha UNIQUE constraint-et definiálunk.

Ha a constraint-ektől függetlenül szeretnénk indexet létrehozni, a CREATE INDEX utasítást kell használnunk. Alapértelmezés szerint nem fürtözött index jön létre. A CREATE INDEX utasítás szintaxisa a következő:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
    INDEX index_name ON table (column [,...n])
[WITH
        [IGNORE_DUP_KEY] [[,] DROP_EXISTING]
]
A UNIQUE kulcsszó definiálja az egyediséget, a CLUSTERED a fürtözött index, a NONCLUSTERED a nem fürtözött index megfelelője. Az indexet el kell neveznünk (index_name). A table és a column határozzák meg, hogy melyik tábla melyik oszlopán értelmezzük az indexet.

Az IGNORE_DUP_KEY kulcsszó határozza meg, hogy mi történik akkor, amikor egy már létező kulcsot akarunk beszúrni a táblába, és az adott oszlopra az egyedi jellemző érvényes. Ha az index definíciójában szerepel az IGNORE_DUP_KEY kulcsszó, az SQL Server figyelmeztetést küld és a beszúrást nem hajtja végre. Ha a kulcsszót nem szerepeltettük, hiba generálódik, és az egész tranzakció visszagörög (rollback).

Ha megadjuk a DROP_EXISTING kulcsszót, az index létrejöttekor törli az ugyanilyen néven esetleg már létező korábbi indexet. Ha emiatt egy fürtözött indexet kell törölni, a nem fürtözött indexek is újra generálódnak.

Ha kíváncsiak vagyunk, hogy az indexeink mennyi helyet foglalnak, az sp_spaceused tárolt eljárást kell meghívnunk.
USE pubs
EXEC sp_spaceused 'titles'
Ha fürtözött indexet hozunk létre, a művelethez körülbelül a tábla méretének 1,2-szerese szükséges ideiglenes tárolás céljából. Ez azért kell, mert a táblát először le kell másolni az új sorrendnek megfelelően. A másolás végén az eredeti tábla törlődik.

Indexeket ideiglenes táblákra is feltehetünk. A tábla megszűntével az index is automatikusan megszűnik.

Ha kíváncsiak vagyunk, hogy egy táblán milyen indexek értelmezettek, az sp_helpindex tárolt eljárást kell használnunk.
Az SQL Server az indexeket a sysindexes nevű rendszertáblában tárolja.

Példák

1. A sysindexes táblában megnézzük, hogy létezik-e már index a pubs adatbázis authors táblájában az au_id mezőre. Ha már létezik, töröljük. Ezután létrehozzuk ezt az indexet. Az SQL Server a műveletek végrehajtása után egy DONE_IN_PROC üzenetben tudatja az ügyfél alkalmazással, hogy az adott művelet hány sort érintett. Ezt egyébként a @@ROWCOUNT globális változóban tárolja. Ha ez a szám minket nem érdekel, az üzenetküldést letilthatjuk a SET NOCOUNT ON utasítással, illetve bekapcsolhatjuk a SET NOCOUNT OFF utasítással. Erre is látunk itt példát.
SET NOCOUNT OFF
USE pubs
IF EXISTS (SELECT name FROM sysindexes 
        WHERE name = 'au_id_ind')
    DROP INDEX authors.au_id_ind
GO

USE pubs
CREATE INDEX au_id_ind
    ON authors (au_id)
GO
2. Újra létrehozzuk az alkalmazottak fizetését tartalmazó táblát, feltöltjük adatokkal, majd egyedi, fürtözött indexet definiálunk rá, és használjuk az IGNORE_DUP_KEY-t.
SET NOCOUNT ON
USE pubs
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_NAME = 'emp_pay')
    DROP TABLE emp_pay
GO

USE pubs
IF EXISTS (SELECT name FROM sysindexes 
        WHERE name = 'employeeID_ind')
    DROP INDEX emp_pay.employeeID_ind
GO

USE pubs
GO
CREATE TABLE emp_pay
(
 employeeID int NOT NULL,
 base_pay money NOT NULL,
 commission decimal(2, 2) NOT NULL
)
INSERT emp_pay
    VALUES (1, 500, .10)
INSERT emp_pay 
    VALUES (2, 1000, .05)
INSERT emp_pay 
    VALUES (3, 800, .07)
INSERT emp_pay
    VALUES (5, 1500, .03)
INSERT emp_pay
    VALUES (9, 750, .06)
GO

SET NOCOUNT OFF
GO

CREATE UNIQUE CLUSTERED INDEX employeeID_ind
    ON emp_pay(employeeID)
    WITH IGNORE_DUP_KEY



Index törlése

Ha az indexre már nincs többé szükségünk, a DROP INDEX utasítás használatával törölhetjük.
DROP INDEX 'table.index'
Az utasítás használatára az előző szakaszban láthatunk példát.
Fürtözött index törlése időigényes művelet, hiszen az összes nem fürtözött indexet újra létre kell hozni.
PRIMARY KEY és UNIQUE constraint-ekkel létrehozott indexek nem törölhetők, ezek megszűntetéséhez magát a constraint-et kell a tábláról levennünk.
A tábla minden indexe a tábla megszűntével automatikusan megszűnik.

Statisztika

Minden index tartalmaz egy eloszlás statisztikát az indexben lévő adatértékekről. Ezeket az információkat az SQL Server költség alapú lekérdezés optimalizálója (query optimizer) használja, hogy eldöntse, milyen stratégiát alkalmazzon az egyes lekérdezések végrehajtásakor. Ha létrehozunk egy indexet, az oszlop értékeiből automatikusan generálódik a statisztika is. A későbbi műveletek a statisztikát nem befolyásolják, ezért előbb vagy utóbb elavulttá válik, és a query optimizer már nem a legoptimálisabb döntéseket hozza meg. Bár egy bizonyos mintavételezési algoritmus szerint maga az SQL Server is frissíti néha a statisztikákat, módunk van ezt a programunkból kikényszeríteni az UPDATE STATISTICS utasítással.
UPDATE STATISTICS table
    [
        index 
        | (statistics_name[,...n])
    ]
    [    WITH
        [
            [FULLSCAN]
            | SAMPLE number {PERCENT | ROWS}]
        ]
        [[,] [ALL | COLUMNS | INDEX]
        [[,] NORECOMPUTE]
    ]
1. Az authors tábla minden indexére újrageneráljuk a statisztikákat.
UPDATE STATISTICS authors
2. Csak egyetlen indexre kérjük a frissítést.
UPDATE STATISTICS authors au_id_ind
3. Több tábla statisztikáját ugyanazzal a névvel elnevezve egy statisztikacsoportot hozhatunk létre, amit egyszerre frissíthetünk. Itt az anames nevű csoport statisztikáit akarjuk újraszámolni. A WITH FULLSCAN kapcsoló előírja, hogy a statisztika kiszámításához az egész táblát végig kell olvasni. Ennek hiányában az SQL Server eldönti, hogy hogyan mintavételez a táblából a statisztika becsléséhez. A SAMPLE kapcsolóval ezt is pontosan szabályozni lehet. A NORECOMPUTE kapcsoló azt írja elő, hogy most előálló statisztikát az SQL Server később automatikusan soha ne frissítse.
UPDATE STATISTICS authors(anames)
    WITH FULLSCAN, NORECOMPUTE
Statisztikát mi is létrehozhatunk a CREATE STATISTICS utasítással.
CREATE STATISTICS names
    ON Customers (CompanyName, ContactName)
    WITH SAMPLE 5 PERCENT
A customer tábla két oszlopára kérünk statisztikát, amiket a names nevű csoporttá fogjuk össze. A statisztikát a tábla 5 százalékának elolvasásából kérjük becsülni.
Ha kíváncsiak vagyunk egy konkrét statisztikára, például hogy mikor frissült utoljára, a DBCC SHOW_STATISTICS utasítást kell használnunk.
USE pubs
DBCC SHOW_STATISTICS (authors, UPKCL_auidind)
A szükségtelen statisztikákat a DROP STATISTICS utasítással törölhetjük.

Példaprogram

A példaprogramban összehasonlítunk két eljárást. Az egyikben egyesével szúrunk be rekordokat egy olyan táblába, amelyikhez több indexet is definiálunk, a másikban először egy átmeneti, index nélküli táblába szúrjuk be a rekordokat, majd az egészet egyszerre másoljuk át az indexelt táblába, de már az SQL Serveren belül.

Az eljárásokat kétféleképpen is kipróbáljuk. Az egyikben az SQL Server tábláját a TTable objektumon keresztül érjük el, és az Append metódussal szúrjuk be az új rekordokat. A másikban tárolt eljárásokat használunk. Természetes ez utóbbi valamivel gyorsabb lesz.

A teszteléshez használt táblát és a tárolt eljárásokat először létre kell hoznunk, ehhez futtassuk le a bte.sql programot az SQL Serveren.
CREATE TABLE indextest (
	it_id int IDENTITY (1, 1) NOT NULL ,
	i1 int,
	i2 int,
	i3 int,
	s1 varchar (200),
	s2 varchar (200),
	s3 varchar (200) 
)
A táblában rekordonként tárolunk 3 egész és 3 szöveges típusú értéket. A táblára definiálunk két nem fürtözött és egy fürtözött indexet.
CREATE  CLUSTERED  INDEX IX_indextest
      ON indextest(s1)
CREATE  INDEX IX2_indextest 
      ON indextest(s3)
ALTER TABLE indextest ADD 
CONSTRAINT PK_indextest
       PRIMARY KEY  NONCLUSTERED (it_id)
A Teszt1 gomb lenyomásakor egy TTable objektumon keresztül töltjük fel a táblát 3000 rekorddal.
  tIndexTest.Open;
  for i := 1 to 3000 do
  with t[i] do
  begin
    tIndexTest.Append;
    tIndexTest['i1'] := i1;
    tIndexTest['i2'] := i2;
    tIndexTest['i3'] := i3;
    tIndexTest['s1'] := s1;
    tIndexTest['s2'] := s2;
    tIndexTest['s3'] := s3;
    tIndexTest.Post;
  end;
  tIndexTest.Close;
A Teszt2-ben először egy 'index-mentes' táblát töltünk fel, majd másolunk.

A Teszt3-ban tárolt eljárás segítségével töltjük fel a táblát:
  for i := 1 to 3000 do
  with t[i] do
  begin
    spInsert.ParamByName('@i1').AsInteger := i1;
    spInsert.ParamByName('@i2').AsInteger := i2;
    spInsert.ParamByName('@i3').AsInteger := i3;
    spInsert.ParamByName('@s1').AsString := s1;
    spInsert.ParamByName('@s2').AsString := s2;
    spInsert.ParamByName('@s3').AsString := s3;
    spInsert.ExecProc;
  end;
Végül a Teszt4 gomb lenyomásakor tárolt eljárással feltöltjük az index-mentes táblát, majd átmásoljuk az adatokat.
Az egyes futási időket a gombokról tudjuk leolvasni. Összevetve az eredményeket az derül ki, hogy az indexelt táblába csak egy egészen kicsit tart tovább beszúrni nagyszámú rekordot, de ez a kis különbség is eltűnik azzal, hogy a beszúrások után a rekordokat még át is kell másolni.

MS SQL cikksorozat

MS SQL adatbázis kezelés Delphi-ből - 1. rész
MS SQL adatbázis kezelés Delphi-ből - 2. rész
MS SQL adatbázis kezelés Delphi-ből - 3. rész
MS SQL adatbázis kezelés Delphi-ből - 4. rész
MS SQL adatbázis kezelés Delphi-ből - 5. rész
MS SQL adatbázis kezelés Delphi-ből - 6. rész
MS SQL adatbázis kezelés Delphi-ből - 7. rész
MS SQL adatbázis kezelés Delphi-ből - 8. rész
MS SQL adatbázis kezelés Delphi-ből - 9. rész
MS SQL adatbázis kezelés Delphi-ből - 10. rész
MS SQL adatbázis kezelés Delphi-ből - 11. rész
MS SQL adatbázis kezelés Delphi-ből - 12. rész
MS SQL adatbázis kezelés Delphi-ből - 13. rész
MS SQL adatbázis kezelés Delphi-ből - 14. rész
MS SQL adatbázis kezelés Delphi-ből - 15. rész
MS SQL adatbázis kezelés Delphi-ből - 16. rész
MS SQL adatbázis kezelés Delphi-ből - 17. rész
MS SQL adatbázis kezelés Delphi-ből - 18. rész
MS SQL adatbázis kezelés Delphi-ből - 19. rész
MS SQL adatbázis kezelés Delphi-ből - 20. rész
MS SQL adatbázis kezelés Delphi-ből - 21. rész
MS SQL adatbázis kezelés Delphi-ből - 22. rész
MS SQL adatbázis kezelés Delphi-ből - 23. rész

MS SQL adatbázis kezelés Delphi-ből - 24. rész

MS SQL adatbázis kezelés Delphi-ből - 25. rész
MS SQL adatbázis kezelés Delphi-ből - 26. rész
MS SQL adatbázis kezelés Delphi-ből - MS SQL 27. rész
MS SQL adatbázis kezelés Delphi-ből - MS SQL 28. rész
MS SQL adatbázis kezelés Delphi-ből - MS SQL 29. rész
MS SQL adatbázis kezelés Delphi-ből - MS SQL 30. rész
MS SQL adatbázis kezelés Delphi-ből - MS SQL 31. rész
MS SQL adatbázis kezelés Delphi-ből - MS SQL 32. rész