Buktatók - Adatbázis továbbképzés

Mik a leggyakoribb, fejlesztők által elkövetett hibák, amik terheléshez, és problémákhoz vezetnek az adatábzis karbantartás során.

Transcript

1. Adatbázis továbbképzés #2 Buktatók Faludi Bence
2. •  NULL értékek problémája Ismeretlen érték, amely kiértékeléskor 3VL-t használ. •  DISTINCT ON elmarad Elfeledkezés arról, hogy az adatbázis állapota és adatai soha sem tökéletesek, és minden rendszerben van duplikáció. •  Tárolt eljárások, nézetek kihagyása Kész dolgok újboli feltalálása, hiányosan. •  Hibás összekapcsolások használata Mik is ezek a buktatók?
3. •  Másodlagos Index-ek hibás kezelése Állandó B-fa generálás táblákon, … •  Szűrési feltételek helyében hiba Allekérdezések használatakor feltételek elhelyezésének jelentése és következményei •  Szöveg vizsgálata •  Adatbázis optimalizáció hiánya Nem ismert fogalmak, ismeretlen módszerek, … Mik is ezek a buktatók?
4. •  Nem definiált érték, amely nincs benne semmilyen értéktartományban. •  Háromértékű logika kiértékeléskor. NULL értékek problémája AND F N T F F F F N F N N T F N T OR F N T F F N T N N N T T T T T NOT F T N N T F
5. •  Ha egy aritmetikai művelet egyik operandusa NULL, akkor az eredménye is NULL lesz. •  Ha egy NULL egy összehasonlításban szerepel akkor az összehasonlítás eredménye UNKNOWN lesz. •  Egy állítás így lehet ismeretlen értékű is, WHERE-ben azonban csak az fog találatnak számítani, ami kiértékelve TRUE. NULL értékek problémája
6. •  Adatbázis értékei soha sem tökéletesek, mindig tartalmaznak valamekkora duplikációt. •  Ha a feladat megköveteli a duplikáció mentesítést a lekérdezésben (pl. számosság) mindig tegyük hozzá. •  Paramétereinek listája kritikus! DISTINCT ON elmarad
7. DISTINCT ON elmarad SELECT DISTINCT ON ( co.surname, co.firstname, co.company_name, co.postal_code, co.city, co.street ) co.*, … FROM … ORDER BY co.surname, co.firstname, co.company_name, co.postal_code, co.city, co.street, KRITIKUS RENDEZÉS •  Az ORDER BY-ban szereplő nem DISTINCT ON-ban felsorolt oszlopok sorrendezése dönt arról, melyik értékek maradnak meg az eredményhalmazban. •  Figyelj az oszlopokra, minél több van, annál nagyobb lesz az ismétlődés.
8. •  Ellenőrizzed le, milyen nézetek és tárolt eljárok vannak a rendszerben. Ha pedig még nincs a feladathoz, hozzad létre. •  Könnyebb egy VIEW-t karban tartani, mint akár több száz helyen a PHP kódot. •  Soha ne legyen duplikáció az eljárásokban! •  Mindenhez írj ellenőrzést! Tárolt eljárások, nézetek kihagyása
9. Tárolt eljárások, nézetek kihagyása CREATE OR REPLACE VIEW "public"."valid_ownerships" AS SELECT oin.id, oin.ind_id, oin.vin, … dd.dealer_code AS "odealer_code", COALESCE( oin.start_date_of_ownership, oin.purchase_date, oin.invoice_date ) AS spidate, COALESCE( oin.purchase_date, oin.start_date_of_ownership, oin.invoice_date ) AS psidate FROM ownership oin INNER JOIN individual AS ind ON ( ind.id = oin.ind_id ) INNER JOIN household AS hh ON ( hh.id = ind.hh_id ) LEFT OUTER JOIN dealer AS dd ON ( dd.id = oin.dealer_id ) WHERE oin.vin IS NOT NULL AND CHAR_LENGTH( TRIM( both from oin.vin ) ) = 17 AND TRIM( both from oin.vin ) SIMILAR TO '[a-zA-Z0-9]+’ AND DATE_PART( 'year', COALESCE( oin.start_date_of_ownership, oin.purchase_date, oin.invoice_date ) ) > 1980 AND DATE_PART( 'year', COALESCE( oin.purchase_date, oin.start_date_of_ownership, oin.invoice_date ) ) > 1980 AND COALESCE( oin.start_date_of_ownership, oin.purchase_date, oin.invoice_date ) <= CURRENT_DATE AND COALESCE( oin.purchase_date, oin.start_date_of_ownership, oin.invoice_date ) <= CURRENT_DATE;
10. Tárolt eljárások, nézetek kihagyása CREATE OR REPLACE VIEW "public"."current_ownerships" AS SELECT voin.*, voin.spidate AS "pdate" FROM valid_ownerships voin WHERE voin.end_date_of_ownership IS NULL OR voin.end_date_of_ownership > CURRENT_DATE ORDER BY voin.spidate DESC NULLS LAST; CREATE OR REPLACE VIEW "public"."last_current_ownership" AS SELECT DISTINCT ON ( co.surname, co.firstname, co.company_name, co.postal_code, co.city, co.street ) co.*, m.name AS model_name FROM current_ownerships co LEFT JOIN model AS m ON ( m.id = co.model_id ) ORDER BY co.surname, co.firstname, co.company_name, co.postal_code, co.city, co.street , co.pdate DESC NULLS LAST;
11. •  Összekapcsolások fajtái: Direktszorzat, Természetes, USING utasítással történő, Teljes vagy kétoldali, Tetszőleges feltételen alapuló. •  Összekapcsolások irányai: Belső, Külső, Teljes külső Hibás összekapcsolások használata
12. •  Belső összekapcsolás: Azon összekapcsolások, amelyek két tábla megegyező soraival térnek vissza. •  Külső összekapcsolás: Két tábla olyan összekapcsolását, amely a belső összekapcsolás eredményeihez hozzáveszi a bal (vagy jobboldali) tábla összes sorát, baloldali vagy (jobboldali) külső összekapcsolásnak nevezzük. •  Teljes külső összekapcsolás: Amikor a külső összekapcsolás egyszerre bal- és jobboldali. Hibás összekapcsolások használata
13. #1: Nem kötelező kiegészítő információk gyűjtésekor rekordokhoz mindig külső összekapcsolást használjunk, különben szűrjük az eredményhalmazt indokolatlanul. #2: Ahol extra adatgyűjtés szükséges több értékből ott készüljön tárolt eljárás, nézet, vagy trigger a helyzettől függően. Hibás összekapcsolások használata
14. •  Átlagosan B-fa indexeket használunk PostgreSQL-ben, ennek szerkezetét az adatbázisnak karban kell tartania. •  Fa leveleiben vannak a bejegyzésekek, mellettük a sorazonosítók. •  Levélblokkok minden irányban láncolva vannak. •  Lehet egy- vagy többsoros. Másodlagos Index-ek hibás kezelése
15. •  Minden INSERT és DELETE esetén ami a sort éri a B-fa szerkezetet befolyásolja, ilyenkor újra kell kalkulálnia. •  Minden UPDATE esetén ami az index-elt oszlopban történt újra kell kalkulálnia a friss érték helyét! Másodlagos Index-ek hibás kezelése
16. #1: Ne tegyünk index-et olyan oszlopokra amik állapota nagyon sűrűn változik, vagy a tábla értékei folyamatosan törlődnek, és beszúródnak! Nagyon komoly adatbázis terhelést és lassulást eredményez! #2: Tegyünk azonban index-et minden olyan nem sűrűn változó értékre, amire gyakran szűrűnk, vagy amire gyakran rendezünk. Ne feledjük a NULL értékek nem kerülnek index- elésre. Másodlagos Index-ek hibás kezelése
17. •  Feltételek megfelelő elhelyezése kritikus főleg DISTINCT ON-t tartalmazó allekérdezések szerepeltetése mellett. #1: Figyeljünk a feltételek helyére. Minél bentebb szűrűnk, annál jobban befolyásoljuk a külső lekérdezést, és olyan eredményektől eshetünk el, amit épp keresünk. Szűrési feltételek helyében hiba
18. #1: Szöveget TRIM-eljük bármilyen formátum vizsgálat előtt! #2: LIKE helyett hasznájunk SIMILAR TO-t. #3: Figyeljünk a PostgreSQL verziói eltérésére. (pl.: \d csak 9.1-től, escape-elés \\-el 9.0-ban) #4: Két szöveg bármilyen összehasonlításakor LOWER függvényt használata, UPPER helyett. Szöveg vizsgálata SELECT … FROM ownership oin WHERE oin.vin IS NOT NULL AND CHAR_LENGTH( TRIM( both from oin.vin ) ) = 17 AND TRIM( both from oin.vin ) SIMILAR TO '[a-zA-Z0-9]+’
19. Faludi Bence b.faludi@mito.hu   Köszönöm a figyelmet!