Manuprāt visbūtiskākās daļas rakstā, kam ir vērts pievērst uzmanību, ir Include daļa (tādēļ, ka tā bieži tiek nepelnīti aizmirsta) un FILLFACTOR.
Rakstā aprakstīti arī With daļas parametri, kas nav obligāti jānorāda- un visbiežāk arī nav pēc tā vajadzība. Izņēmums ir FillFactor, kura vērtību vienmēr būtu rūpīgi jāapdomā.
Indeksu izveidi aprakstīšu ar piemēra palīdzību, bet formālais pieraksts MSDN ir atrodams šeit.
Piemērs no Management Studio vides:
Piemērs, skriptā:
CREATE UNIQUE NONCLUSTERED INDEX [IX_MansIndekss]
ON [dbo].[TabulasNosaukums]
(
Col1 ASC
)
INCLUDE
(
Col2 ASC,
Col3 ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 80,
MAXDOP = 0
)
ON [PRIMARY]
* UNIQUE
Atslēgas vārdu var norādīt un var nenorādīt. Ja nenorāda, indekss nepieprasa, lai tiktu veidots uz kolonnām, kurās ir unikālas vērtības. Ja norāda, tad indeksā visām vērtībām jābūt unikālām (ja viena kolona- kolonnas vērtībām jābūt unikālām, ja vairākas kolonnas, tad norādīto kolonnu kopējai kombinācijai jābūt unikālai katrā rindā). Indeksi ar unikālām vērtībām parasti ir efektīvāki.
* NONCLUSTERED
Indekss var būt Clustered un NonClustered- attiecīgi šī emuāra raksti par klusterētiem indeksu un parastiem indeksu. Ja šo atslēgas vārdu nenorāda, tad tiek veidots parasts (non-clustered) indekss.
* [IX_MansIndekss]
Indeksa nosaukums, brīvi izvēlēts teksts. Parasti būtu labi pieturēties pie viena stila- tas kā cenšos darīt es- veidot indeksa nosaukumu no "IX_" un klāt pierakstīt kolonnas nosaukumus kas tiek iekļati indeksā. Šajā gadījumā indeksa nosaukums būtu "IX_Col1".
* Col1 ASC
nozīmē to, ka indekss tiks veidots uz tabulas kolonnas "Col1" un tiks kārtots augošā secībā (ASC). Kolonnas var kārtot arī dilstošā secībā, tad jānorāda DSC. Var arī nenorādīt neko, tādā gadījumā kolonna tiks kārtota augošā secībā. Jēga no tā ir tad, ja vaicājumos tiek norādīta Order By daļa.
* INCLUDE (Col2, Col3)
Indeksā iekļautās kolonnas- jānorāda tikai tad, ja nepieciešams viedot indeksu ar iekļautām kolonnām. Par iekļautajām kolonām šajā emuārā rakstīts rakstā "Indeksi (non-clustered indexes)".
* WITH (..) daļa
Tiek norādīti citi indeksa parametri. Tālāk rakstā par tiem.
* PAD_INDEX = OFF
Noklusētā vērtība ir OFF. Mainīt šo ir jēga tikai tad, ja norādīts arī FillFactor parametrs. Ja OFF, tad SQL Servers visas indeksa lapas izņemot beigu virsotnes aizpilda maksimāli pilnas. Ja ON, tad FillFactor attiecas ne tikai uz indeksa gala virsotnēm (parasta indeksa gadījumā gala virsotnēs glabājas- indeksa atslēgas vērtība un klusterētā indeksa atslēga, bet klusterēta indeksa gadījumā gala virsotnēs glabājas tabulas datu rindas- raksts emuārā šeit), bet arī uz pārējām indeksa virsotnēm.
* STATISTICS_NORECOMPUTE = OFF
Gandrīz vienmēr (manā pieredzē nav bijusi izņēmuma situācija) vislabāk atstāt noklusēto vērtību. par šo būs skaidrojums vēlāk, kad būs raksts par kolonu statistiku SQL Server. <plānots: atsauce uz rakstu>
* SORT_IN_TEMPDB = OFF
Par šo jāsāk domāt tad, ja TempDB atrodas uz cita disku masīva. Parasti noklusētā vērtība gana laba.
* IGNORE_DUP_KEY = OFF
Noklusētā vērtība ir OFF (praktiski vienmēr tas kas vajadzīgs). Norādīt ON var tikai unikāliem indeksiem. Manuprāt uz maldinošu domu vedinošs nosaukums. Ja šī parametra vērtība ir ON, tad ievietojot tabulā datus, tiek ignorētas tās rindas, kas jau ir ievietotas unikālajā indeksā. Šeit arī Piemērs: Indeks ar unikālām vērtībām, IGNORE_DUP_KEY.
* DROP_EXISTING = OFF
Noklusētā vērtība ir OFF, tas nozīmē, ka ja indekss ar tādu vārdu eksistē, tiks izmests kļūdas paziņojums. Ja parametra vērtība ir ON, tad eksistējošs indekss ar šādu vārdu tiks nomests uz izveidots ar jaunajiem norādītajiem parametriem, ja tie nav norādīti- tad ar noklusētājām vērtībām (nevis atstājot jau iepriekš izveidotā indeksa vērtības!)
* ONLINE = OFF
Noklusētā vērtība ir OFF un maināma tikai tikai Enterprise versijā! Noklusēti OFF nozīmē to, ka indeksa izveides laikā tabulā datu mainīšana nevar notikt. parasti indeksa izveide neaizņem pārmērīgi daudz laika, jeb arī tos iespējams veidot tad, kad datu bāzi nelieto. Ja tas nav iespējams, un ir Enterprise versija, tad var sākt domāt par šo parametru.
* ALLOW_ROW_LOCKS = ON
Noklusētā vērtība ir ON, kas pārsvarā ir labākais variants. Atļaut slēgt rindu. <plānots: atsauce uz rakstu>
* ALLOW_PAGE_LOCKS = ON
Noklusētā vērtība ir ON, kas pārsvarā ir labākais variants. Atļaut slēgt lapu. <plānots: atsauce uz rakstu>
* FILLFACTOR = 80
Ļoti būtisks parametrs, kas gan iespaido indeksu tikai tā izveides laikā. Ideja šim parametram- norādīt cik pilnas ir indeksa beigu lapas. Ja parametrs nav norādīts, tad tiek izmantots noklusētais FillFactor vērtība (kas parasti ir 0 vai 100, kas abos gadījumos nozīmē to pašu- beigu lapas ir pilnībā pilnas).
Lai apskatītu noklusēto parametra vērtību, jāpārkonfigurē serveris, lai tas rādītu padziļinātus servera parametrus (jābūt tiesībām to darīt):
sp_configure 'show advanced options', 1Pēc tam (lai apskatītu) jāizpilda komanda:
reconfigure
sp_configure 'fill factor (%)'Lai mainītu vērtību, jānorāda otrs parametrs, bet to neierosinu darīt, tādēļ piemēra šeit nebūs. Lai atstātu tā kā bija (pēc noklusējuma padziļinātus parametrus nerāda), izpildam komandu:
sp_configure 'show advanced options', 0Pirms norādīt šo parametru, ir īpaša uzmanība jāpievērš tam, kādai kolonnai indekss tiek veidots. Daži ieteikumi:
reconfigure
- ja indekss ir klusterētais indekss (Tabulas ar klusterētu (clustered) indeksu), kura vērtības ir veidotas uz Identity kolonnas (vērtības visu laiku pieaug) un tabulā nav mainīga garuma kolonnu, tad šim parametram vērtīga būtu vērtība "100", jo dati tiks ievietoti vienmēr indeksa beigās un lapu dalīšanās netiks novērota.
- ja klusterēts indekss, bet tabulā ir mainīga garuma vērtības, būtu jādomā par mazāku vērtību (piemēram, 90) atkarībā no tā, cik bieži un kā mainās mainīgo kolonu garumu vērtības, cik gara ir datu rinda. Tas ir tāpēc, lai maksimāli novērstu lapu dalīšanos. Par to aprakstīts rakstā Tabulas ar klusterētu (clustered) indeksu un dots konkrēts piemērs, kā rodas lapu dalīšanās Piemērs: Lapu dalīšanās (Page splits).
- ja tabula ir bez kluseterēta indeksa (Tabula bez klusterētā (clustered) indeksa), tad šim parametram nav īpašas nozīmes, jo datu mainīšanas rezultātā tādās tabulās rodas pāredresētie ieraksti (Piemērs: pāredresētie ieraksti (Forwarded records)...). Tomēr- lai "sakārtotu" tabulu bez klusterētā indeksa, tai var uz brīdi izveidot klusterēto indeksu norādot šo parametru tā, lai esošās lapas tiktu sakārtotas un aizpildītas līdz kādam noteiktam procentam (lai datu maiņas gadījumā nerastos pāredresējošie ieraksti). Pēc tam klusterēto indeksu dzēst. Taču pozitīvais efekts būs īslaicīgs, jo datu ievietošana tabulā bez klusterētā indeksa notiek pirmajā brīvajā vietā.
- ja tiek veidots indekss uz tabulas, kurā plānots ievietot daudz datus, tad šo vērtību būtu jāatstāj relatīvi mazu, lai novērstu lapu dalīšanos Piemērs: Lapu dalīšanās (Page splits).
- ja indekss tiek veidots uz tabulas, kurā dati praktiski netiks mainīt, tad labāka, ja norādīta pēc iespējas liela vērtība.
- jāņem vērā, ka jebkurā gadījumā lasīšanas operāciju parasti ir daudz vairāk kā rakstītšanas operāciju- tāpēc pārāk tukšu lapu atstāšana var būtiski pasliktināt vaicājumu efektivitāti- sevišķi klusterētā indeksa gadījumā. Piemēram, FillFactor uz tabulas ar klusterēto indeksu ar vērtību 50 nozīmē, ka visas lapas ir apmēram 50% pilnas- tas nozīmē ka tabulas nolasīšanai vajadzēs divas reizes vairāk nolasīšanas operāciju kā gadījumā, kad visas lapas ir pilnībā pilnas!
* MAXDOP = 0
Noklusētā vērtība 0, kas parasti arī labākā. Ja ir norādīta "0", tas nozīmē ka SQL Server izlems cik procesorus izmantot indeksa izveidē. Norādot citu vērtību, tiek norādīts maksimālais procesoru daudzums cik var izmantot indeksa izveidē.
Papildus literatūra
MSDN bibliotēkā raksts par indeksu izveidi- šeit.
No šī emuāra rakstu papildinātu (bez rakstā pieminētajiem teorijas rakstiem un piemēriem): Piemērs: Indeksi, vaicājumu analīze.
Nav komentāru:
Ierakstīt komentāru