✉️ Saņem šito visu e-pastā. Tā vietā, lai palaistu garām kaut ko no tā, ko es rakstu savā blogā, tagad vari pierakstīties un saņemt e-pastā visu, ko es te rakstu. Tas nav bieži.

← Uz sākumu

MySQL lesson learned

2007. gada 11. jūnijā, 31 komentārs

Š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.

Tu atbildi augstāk redzamajam komentāram. Atcelt

Gravatar irgan:-)

2007. gada 11. jūnijā, plkst. 22:48

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?

Gravatar laacz Autors

2007. gada 11. jūnijā, plkst. 22:50

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.

Gravatar Manas_domas

2007. gada 11. jūnijā, plkst. 23:06

labāk, lai kantoris paņem maksas supportu no izstrādātājiem. vērtīgāk nekā kursi un plezīris :)

Gravatar irgan:-)

2007. gada 11. jūnijā, plkst. 23:14

veel jautaajums - kaads ir tas sql ko tu laidi, t.i. tu mekleeji NULL vai neNULL status?

Gravatar laacz Autors

2007. gada 11. jūnijā, plkst. 23:32

irgan, piemēram: <code lang="sql"> SELECT ... FROM tabula WHERE status IS NULL AND ... ORDER BY blabla DESC LIMIT 10 </code>

Gravatar Kristaps Kaupe

2007. gada 11. jūnijā, plkst. 23:40

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?

Gravatar irgan:-)

2007. gada 12. jūnijā, plkst. 08:35

ljoti incanti. sanaak, ka ja skaits

Gravatar irgan:-)

2007. gada 12. jūnijā, plkst. 08:35

ljoti incanti. sanaak, ka ja skaits mazaaks par 100K, tad MySQL izmanto index lai atrastu NULL, jeb vinsh taisa full scan jebkuraa gadiijumaa, vnk ja skaits ir mazs, tas pabeidzaas aatraak?

Gravatar Grrr

2007. gada 12. jūnijā, plkst. 10:55

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.

Gravatar aqp

2007. gada 12. jūnijā, plkst. 11:31

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.

Gravatar Livingston

2007. gada 12. jūnijā, plkst. 12:13

aqp, tuvredzīgāks risinājums vēl būtu jāpameklē ...

Gravatar irgan:-)

2007. gada 12. jūnijā, plkst. 12:41

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.

Gravatar pods

2007. gada 12. jūnijā, plkst. 13:06

M$ SQL Server

>SET ANSI_NULLS ON >select case when null=null then 1 else 0 end 0

>SET ANSI_NULLS OFF >select case when null=null then 1 else 0 end 1

Gravatar aqp

2007. gada 12. jūnijā, plkst. 13:23

Livingston: ir kas konkrēts padomā kā pamatojums apgalvojumam par tuvredzīgumu? nu lai nesanāk tāda sausa runa?

Gravatar DD

2007. gada 12. jūnijā, plkst. 17:32

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.

Gravatar Livingston

2007. gada 12. jūnijā, plkst. 22:48

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!

Gravatar kayle

2007. gada 12. jūnijā, plkst. 23:49

aiz "taču" - anlaik "hauevā" - nav jāliek komats...

Gravatar docenc

2007. gada 13. jūnijā, plkst. 00:11

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.

Gravatar Kirils

2007. gada 13. jūnijā, plkst. 04:43

var nomainiities kaut kaads shits, kas arii NULL pieshkjir pavisam citu jeegu. tik pat teoreetiski, cik -666.

Gravatar Shadowbird

2007. gada 13. jūnijā, plkst. 09:16

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).

Gravatar e-remit

2007. gada 13. jūnijā, plkst. 09:21

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.

Gravatar aqp

2007. gada 13. jūnijā, plkst. 10:34

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.

Gravatar Livingston

2007. gada 13. jūnijā, plkst. 13:00

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.

Gravatar ulzha

2007. gada 13. jūnijā, plkst. 13:19

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.

Gravatar aqp

2007. gada 13. jūnijā, plkst. 15:05

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?

Gravatar Grrr

2007. gada 13. jūnijā, plkst. 16:48

>> 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.

Gravatar Grrr

2007. gada 13. jūnijā, plkst. 16:49

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"

Gravatar irgan:-)

2007. gada 13. jūnijā, plkst. 17:07

Grr, tu juutami neesi Clipper, FoxPro utt. kodeejis. Tur ljoti labi vareeja bez nuliem iztikt (jo tur taada nemaz nebija) un nekas nost nerita :-)

Gravatar sql

2007. gada 13. jūnijā, plkst. 18:34

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

Gravatar sd

2007. gada 14. jūnijā, plkst. 15:22

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.

Gravatar Livingston

2007. gada 14. jūnijā, plkst. 19:16

Oi, secinājumi tādi - reti kuram no jums es būtu ar mieru dot pieeju savām DB :)