laacz.lv

Kaspara F. neoficiālā mājaslapa (Anno 1997)

MySQL lesson learned

Šodienas mācība gūta. Jau labu laiku neesmu augstās domās par MySQL pieprasījumu optimizatoru (runtime query optimizer).

Vēsturiski ir iegājies, ka man ir viena tabula ar apmēram 1.6 miljoniem ierakstu un tajā, diemžēl, tajā ir viens lauks, kura definīcija ir apmēram šāda:

CREATE TABLE tabula ( -- ... status INT(11) DEFAULT NULL, -- ... KEY status (status), -- ... );

Kamēr tabulā dominē ieraksti, kuriem šī lauka vērtība ir kāds skaitlis, viss ir labi. Selekti notiek ātri arī pēc šī lauka (lai arī kardinalitāte ir līdzvērtīga ierakstu skaitam tabulā).

Un tad nu vienu dienu piesēdos, lai saprastu, kālab viss tik nenormāli bremzē. SELECT pēc šī lauka notiek ~10 sekundes, lauka UPDATE – ~1-2.

Izrādās, ka brīdī, kad tabulā sāk parādīties, teiksim, 100+ tūkstoši ar ierakstiem, kuriem šī lauka vērtība ir NULL, MySQL sāk neizpildīties. Mācība ir gūta. Turpmāk (kamēr šī problēma netiks atrisināta) tiek izmantota starptabula, kurā attiecīgā stafa ģenerētājs liek visu iekšā, un, tiklīdz darba tabulā NULL ierakstu skaits ir samazinājies zem 10K, no starptabulas tajā tiek iekrāmēta kārtējā 10K ierakstu porcija.

Man vienu brīdi bija pārliecība, ka NULL vērtības ir ļaunas. Ir, protams, gadījumi, kad tās noder. Taču, ir specifiski gadījumi, kad tā lietošana dara lielāku ļaunumu, nekā labumu.

P.S. Izskatās, ka velti esmu lamājis QE.

Pavisam konkrēti sāku domāt par to, ka būtu nepieciešams apmeklēt kādus MySQL in-depth administrācijas vai līdzīgus kursus. Pašlaik diezgan bieži nākas ko jaunu uzzināt.

irgan:-)

vot i “free” softs. oracle maksaa naudu, bet taadus briinumus netaisa :-) a kas notiks, ka tu to kolonu no default null uz default -1234567 (jebkaadu neizmantojamu konstani) paartaisiitu? moka mysql tieshi uz NULLiem atmetaas?

laacz

irgan, katrai DB ir savas “fīčas”, kā arī detaļas un sīkumi. Katra DBVS ir jāmācās un jālieto praksē, lai saprastu, kāpēc tā dara tā šeit un šitā tur. Katrai no tām ir arī savs pielietojums. Manām vajadzībām MySQL ir ideāls.

Kristaps Kaupe

Es brīvos brīžos mēdzu paskatīties dažādu MySQL ļaužu lekciju videoierakstus (no Google TechTalks, u.c.). Jāsaka, gandrīz katru reizi uzzini kādu jaunu niansi un vēlāk tas mēdz noderēt.

Bet vispār parāva interese, vai Latvijā ir kādi kursi, kur kaut cik normālā līmenī padziļināti stāsta par MySQL?

Grrr

Lai arī īpaši nemīlu MySQL, mans priekšstats ir, ka tomēr parastio sanāk nevainot vis šo programmu, bet gan tizlu tabulu strukturēšanu un aizpildīšanu.

Mani protams izbrīna, cik cilvēki maz zin par DB dizainu un ar ko to ēd, bet neskatoties uz to mēdz apgalvot, ka labi zina, kā taisīt datubāzes priekš savām programmām un ka viss taču ir kruta, “jo viņiem strādā”.

Vainoju pie tā PHP+MySQL grāmatas, kuras arī rakstīt mēdz katrs kuram nav slinkums, bet par to kā strukturēt datubāzes arī nojausma ir kā kamielim par lietussargu.

aqp

IMHO, labāk būtu vispār izvairīties no NULL lietošanas un, ja vien tas ir iespējams, NULL vietā lietot kādu “zīmīgu” ne-NULL vērtību. Tas atvieglo darbu ne tikai query optimizētājiem, bet arī programmētājiem, piemēram, ANSI SQL’ā ir čakars ar NULL vērtību salīdzināšanu. Par null vērtību aizvietošanu OOP kontekstā skat. arī NULL object design patterna piemērus.

irgan:-)

Piemeeram oracle shajaa gadiijumaa vienmeer taisa full-scan, tapeec, ka NULL netiek indekseeti. Un ja NULL vietaa tiktu likta kaada ne-NULL veertiiba, oracle ar lielu varbuutiibu taisiitu full-scan vienalga, jo 100K peec indexa atlasiit (kur patiesiibaa sanaak leekaat starp indexu un tabulu) ir leenaak, nekaa uztaisiit 1,6M rindaam full-scan.

DD

Optimizatori strādā uz statistikas, ja nav statistiku, tad neko nevar optimizēt un ir fullscan. Turpretī, ja optimizators redz, ka statistikā vērtība ir ļoti bieži sastopama arī taisīs fullscan.

Ļoti noder execution_plan fīčas. tad tur uzreiz ir rakstīts, cik %cpu aizņem no selekta un t.t.

Livingston

aqp: pieņemsim, ka Tu uztaisi sistēmu, kurā NULL vietā tiek izmantots skaitlis -666, pēc kāda laika Tu aizej strādat citur. Vēl pēc kāda laika pamainās situācija, likumdošana vai vienalga kas un kādreiz absurdā vērtība -666 tagad jau ir pilnīgi normāla … NULL ir NULL un ne bez iemesla!

docenc

piekriitu agp – NULL is/are evil. NULL nav veertiiba, bet gan staavoklis (state) – attieciigi arii jaalieto. ( dazhiem vajag laiku, dazhiem to nesaprast :)

Livingston, piemeers diezgan banaals – paarsvaraa var iztikt ar 0, vai empty string. Nosauc piemeeru, kur bez NULL nu nekaadi.

Shadowbird

Mūžā nav nācies saskarties ar DB, kur bez NULL nu nekādi nevar iztikt. Turklāt, pat ja vajag, zinot visu to čakaru, dažreiz var būt prātīgāk vienkārši izveidot vēl vienu vērtību (ENUM) `null` = ‘yes’/’no’ (tas tāds teorētisks minējums, kā jau teicu, nav nekad bijis baigās vajadzības pēc NULL).

e-remit

Bieži NULL ir neobligātajās ārējās atslēgās – vai nu ir norāde uz klasifikatoru, vai arī NULL. Klasifikatoros likt visādas feikās vērtības nav īsti korekti, tāpēc arī jāsadzīvo ar NULL vērtībām.

aqp

e-remit: Dīvaini, bet rakstot biju domājis tieši enumerācijas un ārējās atslēgas :) Var jau izdomāt (un ielikt referencētajā tabulā) sakarīgu “NULL” vērtību gan klasifikatoriem (“Nav norādīta”, ” ” vai tml.), gan pat citām referencēm. Toties mazāk jāņemas ar outer joiniem…

Livingston, docenc: “skalārām” vērtībām tiešām ne vienmēr var atrast sakarīgu NULL aizstājēju. Piemēram, man galīgi negribētos nenorādītas skaitliskas vērtības (kas nav pārskaitījums vai ārēja atslēga) vietā rakstīt 0 vai vienalga kādu citu maģisko skaitli. Toties laukā “Spēkā līdz” es NULL vietā labprāt rakstītu 2100-01-01…

irgan:-) : pieņemsim, mums ir lauks ‘statuss’. Ir indekss pa to lauku. Un ir SELECT statuss, COUNT(*) FROM whateva GROUP BY statuss. Man ir aizdomas, ka gadījumā, ja statuss būs definēts kā NOT NULL, ORACLE (un varbūt arī vēl kas, kas NULL vērtības neliek indeksā) SELECTs izmantos indeksu (un tikai). Ja nebūs tas NOT NULL, tad FULL SCAN pa visu tabulu. Kas parasti ir ilgāk.

Anyway, nav tādu “balts-melns” noteikumu. Viss atkarīgs no konteksta un iespējām.

Livingston

aqp, labāk raksti 4000. gadu, jo 2100. gads pienāks ātri.

Un ko darīt ar “spēkā no” laukiem? ierakstīt pāris gadus pirms mūsu ēras?
Piemēram, ja runa ir par līgumu, tad Tu nedrīksti rakstīt, ka tas ir spēkā no aizpagājušā gadsimta vai kopš 4000. gada.

Datu bāzes primāri ir paredzētas faktu, nevis prozas glabāšanai un apstrādei.

Protams, nav situāciju, kad nevar NULL vietā iesmērēt kaut ko citu, ja to ļoti gribas un tas pat strādās – līdz zināmam momentam. Un zināmais moments parasti pienāk tad, kad tiek taisītas jaunas sistēmas, kas strādā ar tiem pašiem datiem. Jaunie programmētāji visticamāk nezinās, ka iepriekšējā kodera draudzenes vārds kādā no laukiem patiesībā nozīmē, ka konkrētā vērtība nebija zināma.

aqp

Livingston: Ar “Spēkā līdz” bija domāts datums, līdz kuram ieraksta vērtība ir spēkā (šobrīd), ne jau nu tur kaut kāds līguma datums. Ir aizdomas, ka līdz tam 2100. gadam tādā izskatā sistēma vairs netiks darbināta.
Are “Spēkā no” laukiem (vai vienalga kādiem laukiem) kaut ko darīt NAV OBLIGĀTI. Centos to jau pateikt, bet laikam ne pietiekami skaidri: NOT NULL neuzskatu par dogmu. Tavs absurdais piemērs ar draudzenes vārdu liek domāt, ka tieši tā to esi uztvēris.
Tomēr nelielas viennozīmīgi reversējamas (kaut vai ar skatījumu) dokumentētas atkāpes no datu “tīrības”, lai samazinātu koda apjomu un sarežģītību un atvieglotu darbu DB enginei varbūt ne vienmēr ir pats lielākais grēks?

Grrr

>> Sc, tieši nesen lasījos caur PostgreSQL manuāli (http://www.postgresql.org/docs/8.1/static/indexes-types.html) un arī netīšām ievēroju teikumu: “IS NULL is not indexable”. Liekas līki, bet pieņemšu zināšanai.

Cik atceros, ne Postgres ne Oracle indeksēja NULL laukus , BET indeksā iekļāva tikai tās lauka vērtības, kas reāli nav NULL. Domāju arī, ka MySQL to dara tāpat, bet neesmu skatījies. T.i. buildojot lauka indeksu, tās lauka vērtības, kas ir NULL, netiek iekļautas indeksa veidošanā.

Attiecībā uz NULL kā tādu, pamatproblēmas ir pirmkārt tā, ka pastāv DB layeri (parasti jau prog. valodu līmenī), kas neatšķir NULL no 0, un otrkārt, ka NULL mēdz lietot galīgi kreisi.

Principā, apgalvojums, ka NULL var aizvietot ar vērtību, kas nav lauka “normālajā” diapazonā ir diezgan kreiss. Jā, var aizvietot, tādā pašā nozīmē, kā var aplipināt skoču ap elektrības vadu normālas izolācijas vietā. Principā tas strādā… līdz zināmam brīdim.

Piemērs veselo skaitļu laukam, kurā uzskaitām dajebkādas tendences, kas var būt gan + gan -, da kaut vai iedzīvotāju skaita pieaugumu gadā.

Varam protams uzlikt, ka “NAV DATU” (NULL principiālā nozīme) vietā mums ir kāds bezdievīgi liels skaitlis – piemēram -500 miljardi. Bet tas nozīmē, ka visās programmas, kas IZMANTOS šo lauku ir jāparedz šī “exception” vērtība, gan tagad, gan nākotnē, gan mūžīgi mūžos. Diezgan līki.

Grrr

iepr. komentāram protams bija jāskan:
“Cik atceros, gan Postgres, gan Oracle indeksēja NULL laukus , BET indeksā iekļāva tikai tās lauka vērtības, kas reāli nav NULL”

sql

Man domāt,ka NULL lietot statusa vietā (it sevišķi ja šis statuss nav reference uz statusu apraksta tabulu) nav labākais risinājums, bet darīt tā Tev protams neviens nevar aizliegt :)
Un aqp piedāvātie “spēkā līdz”=’4000-01-01′ un spēkā no “1800-01-01” ir ļoti prakstiski gan ātrāk sql serveris sagremos (tā pati šaize ar indeksēšanu), gan programieriem vienkāršāki selecti sanāk:
nav jāraksta
select … where ((“spēkā līdz” is NUUL) or (“spēkā līdz>datums))
tā vietā pietiek ar
select … where “spēkā līdz>datums

sd

NULL ir slikts un to labāk nelietot, vismaz šajā konkrētajā gadījumā tur varēja rakstīt konkrētu skaitli.

Par MySQL nezinu, bet vismaz senākas Ora versijas NULL vērtības vispār neindeksēja, iespējams tagad ir labāl.

Iesniegt savu viedokli

Atruna par moderāciju. Daži vārdi, var gadīties, ka ir iz melnās listes (viagra and stuff). Tādi komentāri tiek aizturēti, pirms parādās lapā. Ja Tavs komentārs neparādās uzreizi, būs vien jāpagaida, līdz es jamo izlasīšu. Protams, ka paturu tiesības sev netīkošos komentārus dzēst, iemeslu neminot.