trešdiena, 2010. gada 27. oktobris

Piemērs: Indeksi, vaicājumu analīze

Rakstā doti 4 piemēri:
* klusterētā indeksa izmantošana, 
* datu nolasīšana pēc vērtības, kas nav klusterētā indeksa atslēga,  
* datu nolasīšana pēc vērtības, kas ir parasta (non-clustered) indeksa atslēga
* ilustrācija- (non-clustered) indekss satur klusterētā indeksa atslēgu

Piemēra tabulas izveide un aizpilde ar datiem

Tabulas ar klusterēto indeksu (indekss uz TestTableID, jo tas ir primary key) izveide:
Create Table dbo.TestTable
(
   TestTableID int Identity primary key,
   Skaitlis int,
   TrisBurti char(3),
   MainigsGarums NVarChar(20),
   GarsTeksts NVarChar(100)
)
Piemēra tabula ir diezgan ļoti pārdomāta, lai pēc iespējas labi attēlotu situāciju. Viena no būtiskākajām niansēm- ir kolonnā "Skaitlis" ievietotās vērtības. Tās ir ar gadījumu skaitļu palīdzību ģenerētas tā, lai vidēji katra vērtība atkārtotos 10 reizes uz 10000 ierakstiem (skaidrojums kādā no turpmākajiem rakstiem).
Datu ievietošana (10000 rindiņu, apmēram 2.4 MB):
Declare @i int
Set @i = 0
While @i < 10000
Begin
   Insert Into dbo.TestTable(Skaitlis, TrisBurti, MainigsGarums, GarsTeksts)
      Values(Cast(Rand()*1000 as int), 'abc', Replicate('x', Cast(Rand()*20 as int)), Replicate('x',100));
   Set @i = @i + 1;
End
1. Klusterētais indekss
Tabulas izveides laikā tai ir izveidots klusterētais indekss uz kolonnas TestTableID. Demonstrācijai, kā tas strādā, izpildīsim sekojošu vaicājumu:
Select * From dbo.TestTable Where TestTableID = 105
Analizējot izpildes rezultātus, var pārliecināties, ka tika izmantots klusterētais indekss:
Izpildes laikā tika veikta 2 lapu nolasīšana (kā to uzzināt?):

Table 'TestTable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Šajā gadījumā- tika nolasīta viena indeksa lapa (meklējot atslēgu "105"), pēc kuras uzreiz tika atrasta arī vajadzīgā datu lapa, kas kopā nozīmē- divu lapu nolasīšanu. Ja būtu daudz vairāk datu, un tiktu izveidoti vairāk kā viens līmenis indeksu lapām, tad attiecīgi nolasīto lapu daudzums palielinātos atkarībā no tā, cik līmeņi ir indeksam.

2. Datu nolasīšana pēc vērtības, kas nav klusterētā indeksa atslēga
Demonstrācijai, kā tas strādā, izpildīsim sekojošu vaicājumu:
Select * From dbo.TestTable Where Skaitlis = 105
Šajā gadījumā vaicājuma izpildes plāns izskatās šādi:
Izpildes laikā tika veikta 310 lapu nolasīšana (kā to uzzināt?):

Table 'TestTable'. Scan count 1, logical reads 310, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Tas, ka tika nolasītas 310 lapas nozīmē to, ka notika pilnīga tabulas pārlasīšana- tika nolasītas gan indeksu lapas, gan arī datu lapas. Klusterēto indeksu ātrākai meklēšanai nevarēja izmantot, jo klusterētā indeksa atslēga nesatur kolonnu "Skaitlis". Attiecīgās kolonnas dati tiek glabāti datu lapās, tādēļ tika pārlasītas visas datu lapas meklējot atbilstošo vērtību.

3. Datu nolasīšana pēc vērtības, kas ir parasta (non-clustered) indeksa atslēga
Doto piemēru papildināsim ar parastu indeksu, kas izveidots uz kolonnas "Skaitlis".
Indeksa izveide (šis indekss tiks izmantots arī nākamajā piemērā):
Create Index IX_Skaitlis On dbo.TestTable
(
    Skaitlis
)
Izpildīsim to pāsu vaicājumu ko 2. piemērā:
Select * From dbo.TestTable Where TestTableID = 105
Šajā gadījumā tiks iegūts pilnīgi cits izpildes plāns:
Arī nolasīts daudz mazāk lapu (kā to uzzināt?):
Table 'TestTable'. Scan count 1, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Šajā gadījumā situācijas analīze ir mazliet sarežģītāka. Manā gadījumā konkrētais vaicājums atgrieza 8 rindas datu. kāpēc 18 nolasīšanas operāciju?
Izveidotais indekss (IX_Skaitlis) satur divus līmeņus- tātad, lai nonāktu no indeksu veidojošā koka saknes līdz lapām ir jānolasa 2 lapas. šajā gadījumā- pārmeklējot indeksu meklējot vērtību "105" vajadzēja pārmeklēt tikai divas indeksa lapas (vienu lapu- indeksa sakni, otru lapu- indeksa datu lapu). 
Indeksu datu lapā glabājas klusterētā indeksa atslēga. Pēc tās tad mēs arī katrai atrastajai indeksa rindiņai meklējam klusterētajā indeksā atbilstošo datu lapu (kā 1. piemērā). Katras rindas atrašana prasa divas nolasīšanas operācijas. 
Sanāk: 
nolasīto lapu skaits = parastā (non-clustered) indeksa nolasīšanas operāciju skaits + (rindu skaits * klusterētā indeksā meklēšana katrai rindai)
nolasīto lapu skaits = 2 + 8*2 = 18 nolasīšanas operāciju (PIEZĪME- tā kā dati ievietoti ar Rand funkcijas palīdzību, jums gala cipars var atšķirties atkarībā no tā, cik rindu tiek atgriezts).

4. Ilustrācija- (non-clustered) indekss satur klusterētā indeksa atslēgu
Šis piemērs demonstrē, ka parasts indeksa beigu virsotnes satur sevī klusterētā indeksa atslēgu. Izmantosim iepriekšējā (3.) piemērā doto indeksu (IX_Skaitlis), kas šobrīd jau ir izveidots.
Izpildīsim vaicājumu: 
Select Skaitlis, TestTableID From dbo.TestTable Where Skaitlis = 105
Izveidotais izpildes plāns:
Un veikta 2 lapu nolasīšana (kā to uzzināt?):
Table 'TestTable'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Šajā gadījumā varētu mulsināt tas, ka datu meklēšanai tiek izmantots tikai un vienīgi indekss- IX_Skaitlis, neskatoties uz to, ka indeksa izveidē tika norādīta tikai viena kolonna "Skaitlis". 
Tātad ideja kā aprakstīts teorijas rakstā- indekss izveidots uz kolonnas "Skaitlis", bet indeksa koka gala virsotnē tiek glabāta klusterētā indeksa atslēga (tas ļauj izdarīt ļoti labu secinājumu- indeksā nav vajadzīgs iekļaut klusterētā indeksa atslēgu).
Arī nolasīšanas operāciju skaits liecina to pašu- tika nolasītas 2 lapas- viena indeksa sakne, otra indeksa virsotne, kurā glabājas attiecīgās indeksa atslēgas (mēs meklējām pēc "Skaitlis = 105") klusterētā indeksa atslēgas (katrai rindai sava). pašu klusterēto indeksu- tātad tabulu- nav vajadzības pārmeklēt, jo visi vajadzīgie dati ir mūsu indeksā.

Lai izdzēstu piemēra tabulu var izpildīt šo komandu:
Drop Table dbo.TestTable

Nav komentāru:

Komentāra publicēšana