Adatbázis rendszerek. Molnár Bence. Szerkesztette: Koppányi Zoltán

Adatbázis rendszerek Molnár Bence

Szerkesztette: Koppányi Zoltán

Tematika — Indexek — Tárolt

(SQL)

eljárások (SQL)

— Triggerek — Tranzakciók — Hibatűrés — Piaci

helyzet

— Adatbázisok

kezelése

Az adatbázis-kezelő rendszerek jelenkora

Indexek —

Adatstruktúra, ami a táblában található adatok elérését gyorsítja. Ezáltal gyorsulnak a lekérdezések!

—

Hátrányai:

—

—

lassabb írás a táblába (az indexet is írni kell),

—

nagyobb kapacitás igény.

Egy vagy több attribútumra (függvények, kifejezések).

vonatkozik

Index architektúrák —

Non-clustered (nem-csoportosított) Az adatok önkényes sorrendjével ellentétben az index kulcsokat sorrendben tároljuk a rekord pointerekkel (pl. sor; oszlop) együtt. Azaz az adatok sorrendje a táblában és az indexben eltérő.

—

Clustered (csoportosított) A fizikai adatsorokat rendezi össze (a sorrendjük megváltoztatásával) az index blokkokkal, amelyek az adatsorokra mutatnak. Így, az adatok egymásutániságának köszönhetően nagymértékben gyorsulhat az olvasási sebesség.

—

Cluster

Index típusok — PostgreSQL: —

Hash tábla

—

B-tree

—

R-tree

—

GiST (geometria)

SP-GiST — GIN (tömbök) —

Hash tábla

B-tree

R-tree

Mit érdemes indexelni? — Sokszor

keresett adatok (WHERE ...)

— Elsődleges

és idegen kulcsok (… JOIN)

— Geometria

(!)

— Nem

érdemes

—

ritkán használt attribútumok

—

bináris adatok

Index létrehozása — PostgreSQL —

példák:

egy adott attribútumra: CREATE UNIQUE INDEX title_idx ON films (title);

—

egy függvényre: CREATE INDEX ON films ((lower(title)));

Tárolt eljárás — Mikor

érdemes alkalmazni?

— Bemenő

adatok függvényében, azokat feldolgozva, figyelembe véve ad eredményt, vagy frissíti az adatokat.

— Triggerek

Tárolt eljárások — Tulajdonképpen

egy „program”, amelyeket programozási eszközökkel (nyelvvel) hozunk létre, melyek lehetnek: —

Oracle: PL/SQL

—

PostgreSQL: PL/pqSQL

—

C, Python, Perl, ...

Tárolt eljárások CREATE OR REPLACE FUNCTION … RETURNS …. AS $$ DECLARE ...; BEGIN SELECT .. INTO ... FROM ..; LIMIT 1; RETURN ...; END; $$ LANGUAGE plpgsql;

Triggerek —

Ravasz :)

—

Esemény-feltétel-művelet szabály (+ tárolt eljárás/függvény)

—

Események bekövetkeztekor

—

—

beszúrás

—

törlés

—

módosítás

—

tranzakciók befejeződése

Ha a feltétel… —

teljesül

—

nem teljesül

Triggerek CREATE OR REPLACE FUNCTION bonusz_fv() RETURNS trigger AS $$ BEGIN -- NEW => az újonnan beszúrt sor -- ha az új sorban a jegy = 5 -- 10 bónusz pont jár IF NEW.jegy = 5 THEN NEW.bonusz = 10; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER bonusz BEFORE INSERT ON jegy FOR EACH ROW EXECUTE PROCEDURE bonusz_fv();

Triggerek — AFTER/BEFORE/INSTEAD — UPDATE — több

OF

OF attribútum/INSERT/DELETE

művelet

Tranzakciók —

Mi a tranzakció? —

SQL utasítások sora (nincs méretkorlát)

—

„mindent vagy semmit elv”

—

COMMIT; ROLLBACK

—

SAVEPOINT

—

ACID: Atomicity, Durability.

Consistency,

Isolaton,

—

Típusai: automatikus, explicit, implicit

ACID::A —

Atomicity (atomi): „mindent vagy semmit”

—

A tipikus példa a banki átutalás szokott lenni: A számláról leveszünk 10000 Ft-ot, B számlára ráteszünk 10000 Ft-ot. Ha a tranzakció sikerül, akkor B-nél lesz a szóban forgó 10000 Ft, ha nem, akkor A-nál marad. A félkész állapotok, miszerint sehol sincs a pénz, vagy éppen mindkét helyen megvan, nem megengedhetőek.

—

History szerepe

ACID::C —

—

Consistency (konzisztens): konzisztencia megőrzés (a tranzakció után is megmarad). —

Nem lehetnek befejezetlen írási műveleteket (nincs „félig” írt rekord).

—

Referenciális integritás: nem lehet a referenciákat, hivatkozásokat (pl. az idegen kulcsokat) megsérteni, törölni (ROLLBACK). Nem lehet egy adott típusban más típust tárolni. Nem lehet megszorításokat (CONSTRAINTS) átlépni.

Több felhasználó esetén: lockolás (lásd később)

ACID::I —

Isolation (izolált): az egyszerre futó tranzakciók nincsenek hatással egymásra (akár nem is láthatják). Ez magába foglalja a konkurens hozzáférés biztonságos használatát. Az —

isolation level hatással)

—

dirty records

—

repeatable read

—

phantom records

(csak

a

SELECT-ekre

van

ACID::D — Durability

(tartós): COMMIT után az eredmény végérvényesen az adatbázisba kerül.

— Ez

igaz bármilyen helyzetben (hirtelen elmegy az áram, a szerver támadás alatt van, becsapódik egy repülőgép.

Tranzakció típusok — Automatikus:

általános eset, felhasználó kiad egy parancsot, a szerver végrehajtja (és közben tranzakciót nyit, zár)

— Explicit:

felhasználó nyitja (BEGIN TRANSACTION) és zárja (COMMIT/ ROLLBACK) a tranzakciót.

— Implicit:

szerver nyitja a tranzakciót, de a felhasználó zárja (COMMIT/ROLLBACK).

Tranzakció kezelés adatbázisokban —A

„heavyweight” adatbázis-kezelők megpróbálnak „ACID-teljesek” lenni, pl.: PostgreSQL, SQL Server, Oracle

— Más

adatbázisok feladnak pár elvet, így hatékonyabb és gyorsabb adatkezelést biztosítanak, de bizonyos funkcionalitásokat ezáltal nem tudnak biztosítani. Erre jó példa a manapság elterjedőben lévő NoSQL adatbázisok!

Zárolás (lock[olás]) — Többfelhasználós — Az

környezetben

adott felhasználó zárolja az adott rekordot, amelyet éppen ír vagy módosít, így az aktuális tranzakció befejeztéig (COMMIT/ROLLBACK) más nem férhet hozzá. —

optimista

—

pesszimista

Zárak — dirty

read

— non-repeatable — phantom — dead — nincs

read

lock

súlya műveleteknek

— ...

read

(fontossága)

az

egyes

Hibatűrés — SPOF:

single point of failure

— Replikáció —

file/blokk szint

—

WAL: Write Ahead Logging

—

PITR: Point-In-Time Recovery

—

Warm Standby

—

Hot Standby

Adatbázis klaszterek — Felhasználók;

kiszolgálók; adatok

Piaci eloszlás

Adatbázisok kezelése — Közvetlenül — Vastag

a szerverről

kliens (desktop)

— „Vékony

kliens” (böngészőből)

Gyakorlati ötletek

Adat generálás —

Pilot projekthez (vagy HF)

—

Teszteléshez – váratlan adatok

—

Excel —

RAND() / VÉL()

—

RANDBETWEEN() / VÉLETLEN.KÖZÖTT()

—

Webes generátorok

—

Máshonnan szerzett adatok

Adat beolvasás —

Excel CSV

—

Elválasztó — Idézőjel — Karakter kódolás – különleges karakterek — Sorvége — Üres mezők PDF —

—

Copy - Paste

—

Text to columns (Szövegből oszlopok)

—

—

OCR —

Optikai karakter felismerés

Elemzések Excel-ben —

Ctrl+F

—

Filter

—

VLOOKUP() / FKERES() —

Rendezett vagy rendezetlen lista

—

Összetett kulcs?

—

HLOOKUP() / VKERES()

—

CONCATENATE() / ÖSSZEFŰZ()

—

TRIM() / KIMETSZ()

Mintavideók — OCR:

http://www.youtube.com/watch?v=mR7E7 -VAWTQ — VLOOKUP: http://www.youtube.com/watch?v=VYOSU iRGmuA — Text to Columns: http://www.youtube.com/watch?v=K7urf2 cKPF8

Forrás —

Wikipedia

—

https://technetklub.hu/blogs/sqlserver2008/archive/2010/08/10/ tranzakci-243-1x1.aspx

—

http://www.bigonehost.com

Köszönöm a figyelmet! Adatbázis rendszerek