svētdiena, 2010. gada 31. oktobris

Indeksēti (materializētie) skati

Raksta sākuma pavisam īsi teorija par to, kas ir indeksētais skats. Tālāk dots piemērs indeksētā skata izmantošanai, norādot uz dažām, manuprāt būtiskākajām, niansēm, kas jāņem vērā veidojot indeksētu skatu.

Indeksēts skats atšķirībā no parasta skata ir materializēts- tas nozīmē, ka dati, ko atgriež skats, tiek uzglabāti datu bāzē tādā formā, kā tos atgriež skats. Atšķirība star indeksētu skatu un parastu skatu rodas brīdī, kad parastam skatam tiek izveidots klusterēts indekss. Klusterētā indeksa uzbūve ir gandrīz tāda pati, kā tabulām (Tabulas ar klusterētu (clustered) indeksu). Atšķirība ir tā, ka datu lapās tiek glabāti tie dati, kas norādīti skatā- ja dati ir iegūti agregātfunkciju rezultātā, tad datu lapās tiek glabāti jau agregātfunkciju rezultāti.

Tas, ka skatā tiek glabāti jau sagatavoti dati, uzliek zināmus ierobežojumus. Jāņem vērā arī tas, ka indeksētais skats aizņems vietu uz diska. Tas arī nozīmē to, ka katra DML (Datu Manipulācijas Valoda- Insert, Update, Delete) operācija nozīmē datu mainīšanu arī indeksētajā skatā. Tālāk rakstā ar piemēra palīdzību daļa būtiskāko ierobežojumu ir aprakstīta smalkāk.

Turpmākajā rakstā izmantošu to pašu piemēru, kas tika izmantots rakstā Piemērs: Indeksi, vaicājumu analīze. skripts bija šāds:
Create Table dbo.TestTable
(
   TestTableID int Identity primary key,
   Skaitlis int,
   TrisBurti char(3),
   MainigsGarums NVarChar(20),
   GarsTeksts NVarChar(100)
)
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
Pēc skripta izpildes, ir izveidota tabula un tā ir aizpildīta ar datiem.
Lai ilustrētu indeksētā skata priekšrocības, apskatīsim šādu vaicājumu:
Select MainigsGarums, Sum(Skaitlis) Skaits, count(*)
From dbo.TestTable
Group By MainigsGarums
Šajā gadījumā grūti atrast vaicājuma saturisko jēgu, bet ideja- mēs grupējam datus pēc lauka "MainigsGarums" un summējam kolonnas "Skaitlis" vērtības. Papildus tam, vēlamies redzēt, cik datu rindu atbilst katrai unikālajai "MainigsGarums" kolonnas vērtībai.
Vaicājuma izpildes plāns:
Izpildes laikā tika veikta 310 datu lapu nolasīšana (kā to uzzināt?), kas principā nozīmē to, ka tika nolasīti visi tabulas dati, kas arī pareizi, jo mums nav norādīti nekādi atlases kritēriji vaicājumam.

Šajā brīdī indeksētais skats ir tas, kas mums var palīdzēt optimizēt šo vaicājumu.
Pirmais kas jāizdara, ir jāizveido skats, kuru būs iespējams indeksēt. Piedāvātais variants ir šāds:
Create View dbo.vw_IndeksetsSkats
With SchemaBinding
As
    Select MainigsGarums, Sum(IsNull(Skaitlis,0)) Skaits, Count_Big(*) RinduSkaits
    From dbo.TestTable
    Group By MainigsGarums
Izskatās vienkārši. Tomēr ir dažas būtiskas nianses:
  • Obligāti jāizmanto "With SchemaBinding" (jau reiz šajā emuārā pieminēts šeit)
  • Sum(Skaitlis) ir aizvietots ar Sum(IsNull(Skaitlis,0)). Tas ir tādēļ, ka indeksēts skats nedrīkst saturēt kolonnu, kurai ir teorētiski iespējama null vērtība agregātfunkcijas rezultātā. Ja veidojot tabulu, būtu norādīts, ka kolonnai nevar būt null vērtības, no šī soļa varētu izvairīties. Pie tam- labā prakse arī ir pēc iespējas izvairīties no kolonnām kurās atļauj izmantot null vērtības, tā vietā tiek ierosināts lietot noklusēto vērtību (piemēram, 0).
  • Tiek izmantots Count_Big(*). Count(*) un Count_Big(*) ir pilnīgi identiski, tikai atšķirība ir tā, ka Count(*) atgriež int vērtību, bet Count_Big(*) atgriež bigint vērtību (tātad- vairāk iespējamu skaitļa vērtību). Kādēļ neizmantot Count(*)? Skaidrojumu var palasīt šeit, bet tikpat drošs pamatojums ir "tā vienkārši vajag".
  • Papildus piebilde- ja skatā tiek izmantots Group By, tad Count_Big(*) ir obligāta skata sastāvdaļa, lai tam varētu izveidot indeksu!
  • Vaicājuma From daļā- tabulas nosaukumam jābūt pilnīgi norādītam (ar to gribu teikt- jābūt norādītam objekta shēmai- šajā gadījumā "dbo"). Patiesībā šis punkts arī ir uzskatāms par tādu, kas vienkārši vienmēr būtu jādara- tātad vienmēr norādīt pilnu objekta nosaukumu- tas izskatās labi, tas strādā droši un tas arī var ietekmēt ātrdarbību (tā bija SQL 2000 gadījumā, bet ar jaunākām versijām ir šādi tādi uzlabojumi nākuši klāt). Par šo jautājumu kādreiz varbūt sanāks uzrakstīt nelielu rakstiņu.
  • Indeksētos skatos nevar izmantot tādas agregātfunkcijas kā Min, Max, Avg un citas. Kāpēc? Tāpēc, ka pie tabulas datu mainīšanas operācijām indeksētā skata vērtības nevar tikt pārrēķināts tikai ņemot vērā mainītās rindas vērtības- tātad, tas ir tāpēc, lai indeksētie skati rezultātā nenodarītu vairāk kaitējumu kā labumu (iedomājieties 500 rindu summu. Un tad mainat šīs 500 rindiņas- kas notiktu, ja pie vienas rindas izmaiņām vajadzētu nolasīt vēl 499 citas? Indeksēts skats krasi pazeminātu vātrdarbību. Tāpēc tas nav atļauts).
  • Indeksētajiem skatiem ir pietiekami daudz citu ierobežojumu- pilns saraksts pieejams MSDN bibliotēkā šeit. Es neesmu pētījis vai ierobežojumu daudzums ir samazināts SQL Server 2008 versijā, bet salīdzinot SQL Server 2000 un SQL Server 2005- pēdējā indeksētie skati piedzīvoja būtiskus uzlabojumus- ar to gribu teikt, ka skatoties MSDN jānorāda vajadzīgā SQL Server versija. Šeit aprakstītie ierobežojumi bija jau tad un ir joprojām.
Ja skats ir pareizi izveidots, tā pārveidošana par indeksētu skatu ir vienkārša- tam jāziveido indekss:
Create Unique Clustered Index CL_MainigsGarums on dbo.vw_IndeksetsSkats
(
    MainigsGarums
)
Būtiskākais ierobežojums pašam indeksam ir- tam ir jābūt unikālam, un klusterētam! Tiklīdz ir izveidots šis indeks, skats tiek materializēts un tam var veidot arī parastus indeksus.

Mums ir izveidots indeksēts skats. Ko tālāk?
Izpildīsim šādu vaicājumu:
Select MainigsGarums, Skaits, RinduSkaits
From dbo.vw_IndeksetsSkats
Vaicājums atgriezīs tos pašus datus, ko vaicājums, ko mēs analizējām iepriekš (tomēr atšķirība ir tā, ka šajā gadījumā ja grupā kolonnas "Skaitlis" visas vērtības ir "null", tiks atgriezta "0", kamēr sākotnējais vaicājums atgrieztu "null"). Tomēr vaicājums būs daudz efektīvāks kā iepriekšējais, jo mēs izmantojam indeksēto skatu. Izpildes plāns izskatās šādi:
un tiek nolasītas tikai 2 (310 vietā!!) datu lapas (kā to uzzināt? un kāpēc divas?)!

Skaisti! Bet Gribētos vēl vairāk. Pārrakstīsim mūsu optimizējamo vaicājumu savādāk:
Select MainigsGarums, Sum(IsNull(Skaitlis,0)) Skaits, count(*)
From dbo.TestTable
Group By MainigsGarums
Vaicājumā nekur nav minēts izveidotais indeksētais skats. Tomēr izpildes plāns pierāda, ka tas tiek izmantots:
Šī īpašība ir ļoti būtiska- tas nozīmē, ka visos vaicājumos, kur SQL Server uzskata, ka tas var izmantot indeksētā skata priekšrocības- tas tās arī izmantos.
Piezīme: vaicājumā mēs izmantojam Count(*), kamēr indeksētajā skatā bija Count_Big(*)- SQL Server šajā gadījumā vienalga izmanto ineksēto skatu, un izpildes plānā redzamais kvadrātiņš, kam apakšā ir rakstīts "Compute Scalar" nozīmē to, ka izpildes laikā bigint tiek pārveidots par int. Pamainot šajā vaicājumā Count uz Count_Big, tas vairs nebūs jāpārveido par int un arī šis kvadrātiņš pazudīs.

Tomēr, indeksētā skata priekšrocības netiek automātiski izmantotas visās SQL Server versijās (tikai Developer un Enterprise)! Piemēram, SQL Express versijā indeksēto skatu var izveidot, tomēr, ja vien nebūs konkrēti vaicājumā norādīts, ka jālasa dati no indeksētā skata un norādīts NOEXPAND, tad vaicājumu optimizētājs indeksētos skatus neņems vērā. Lai indeksētais skats tiktu ņemts vērā SQL Express, vaicājumam jāizskatās tā:
Select MainigsGarums, Skaits, RinduSkaits
From dbo.vw_IndeksetsSkats With (NoExpand)

Pēc sevis satīram:
Drop view dbo.vw_IndeksetsSkats
Drop Table dbo.TestTable
Papildus lieteratūra:
Indeksēti skati, MSDN: šeit.
Kā uzlabot ātradarbību ar indeksētajiem skatiem  SQL Server 2005- šeit un SQL Server 2008- šeit.

Nav komentāru:

Ierakstīt komentāru