PA152: Efektivní využívání DB 10. Ladění schéma Vlastislav Dohnal PA152, Vlastislav Dohnal, FI MUNI, 2016 2 Schéma  Schéma relace Seznam atributů, jejich typů a integritních omezení Např.  Relace student(uco, jmeno, prijmeni, datum_narozeni)  Schéma databáze Schéma všech relací PA152, Vlastislav Dohnal, FI MUNI, 2016 3 Rozdíly ve schématech  Stejná data lze uložit různými způsoby  Příklad Dodavatelé  Adresa Objednávky  Výrobek, počet, dodavatel PA152, Vlastislav Dohnal, FI MUNI, 2016 4 Rozdíly ve schématech  Alternativy Schéma 1  Objednávka1(dodavatel_id, výrobek_id, počet, dodavatel_adresa) Schéma 2  Objednávka2(dodavatel_id, výrobek_id, počet)  Dodavatel(id, adresa)  Rozdíly Schéma 2 šetří místem Schéma 1 nemusí zachovat adresu, pokud není objednávka PA152, Vlastislav Dohnal, FI MUNI, 2016 5 Rozdíly ve schématech  Rozdíly ve výkonu Častý přístup k adrese dodavatele konkrétního výrobku   schéma 1 je vhodnější (není potřeba spojení) Mnoho objednávek   schéma 1 plýtvá místem (opakování adresy)   relace bude mít mnoho bloků PA152, Vlastislav Dohnal, FI MUNI, 2016 6 Teorie pro správný návrh schéma  Normální formy 1NF, 2NF, 3NF, Boyce-Coddova NF, …  Funkční závislost A  B  B funkčně závisí na A  Hodnotu atributu B zjistíme, pokud známe hodnotu atributu A  Nechť t, s jsou lib. řádky relace, pak platí: t[A] = s[A]  t[B] = s[B] PA152, Vlastislav Dohnal, FI MUNI, 2016 7 Teorie pro správný návrh schéma  Objednávka1(dodavatel_id, výrobek_id, počet, dodavatel_adresa)  Příklad funkčních závislostí dodavatel_id  dodavatel_adresa dodavatel_id, výrobek_id  počet PA152, Vlastislav Dohnal, FI MUNI, 2016 8 Teorie pro správný návrh schéma  K je primární klíč K  R L  R pro libovolné L  K  Tj. pro každý atribut A z R platí: K  A a L  A  Příklad Dodavatel(id, adresa) id  adresa id je primární klíč PA152, Vlastislav Dohnal, FI MUNI, 2016 9 Teorie pro správný návrh schéma  Příklad Objednávka1(dodavatel_id, výrobek_id, počet, dodavatel_adresa) dodavatel_id  dodavatel_adresa dodavatel_id, výrobek_id  počet dodavatel_id, výrobek_id je primární klíč PA152, Vlastislav Dohnal, FI MUNI, 2016 10 Normalizace schématu  1NF – všechny atributy jsou atomické  2NF – všechny atributy závisí na celém klíči  3NF – všechny atributy závisí přímo na klíči není tranzitivní závislost  Normalizace = převod do 3NF PA152, Vlastislav Dohnal, FI MUNI, 2016 11 Normalizace schématu  Relace R je normalizovaná pokud Pro každou funkční závislost X  A nad atributy relace R platí, že X je (super-)klíč.  Příklad Objednávka1(dodavatel_id, výrobek_id, počet, dodavatel_adresa)  dodavatel_id  dodavatel_adresa  dodavatel_id, výrobek_id  počet Není normalizovaná PA152, Vlastislav Dohnal, FI MUNI, 2016 12 Normalizace schématu  Příklad Objednávka2(dodavatel_id, výrobek_id, počet)  dodavatel_id, výrobek_id  počet Dodavatel(id, adresa)  id  adresa Schéma je normalizované PA152, Vlastislav Dohnal, FI MUNI, 2016 13 Příklad normalizace  Banka Zákazník má otevřený účet Zákazník má korespondenční adresu Účet je vedený konkrétní pobočkou banky  Je relace normalizovaná? Banka(zákazník, účet, adresa, pobočka) PA152, Vlastislav Dohnal, FI MUNI, 2016 14 Příklad normalizace  Relace Banka(zákazník, účet, adresa, pobočka) zákazník  účet zákazník  adresa účet  pobočka  Primární klíč je zákazník Důkaz lze provést pomocí funkčních závislostí  Relace není normalizovaná Máme tranzitivní závislost PA152, Vlastislav Dohnal, FI MUNI, 2016 15 Příklad normalizace  Rozklad relace na Banka(zákazník, účet, adresa)  zákazník  účet  zákazník  adresa Účet(účet, pobočka)  účet  pobočka Nyní již je normalizované PA152, Vlastislav Dohnal, FI MUNI, 2016 16 Postup návrhu schématu  Identifikace entit Zákazník, dodavatel, objednávka, …  Každá entita má atributy Zákazník má adresu, telefon, …  Entita musí splňovat: 1. Atribut nemá další atribut (je atomický). 2. Musí existovat funkční závislost pro každý atribut, který není součástí klíče. PA152, Vlastislav Dohnal, FI MUNI, 2016 17 Postup návrhu schématu  Každá entita je nová relace  Vztah mezi entitami je vyjádřen samostatnou relací PracujeNa(zaměstananec_id, projekt_id)  Nalezení funkčních závislostí mezi všemi atributy a kontrola normalizace schématu Pokud existuje závislost AB  C, pak ABC musí být ve stejné relaci. PA152, Vlastislav Dohnal, FI MUNI, 2016 18 Vertikální dělení  Příklad: Telefonní operátor Entita zákazník má id, adresu a zbývající kredit  Závislosti:  id  adresa  id  kredit Normalizované schéma  Zákazník(id, adresa, kredit)  Nebo  ZákazníkAdresa(id, adresa)  ZákazníkKredit(id, kredit) Které je vhodnější? PA152, Vlastislav Dohnal, FI MUNI, 2016 19 Vertikální dělení  Volba správného schéma závisí na způsobu používání (dotazování) Výpisy z účtu jsou posílány jednou měsíčně. Kredit je měněn po každém telefonním hovoru.   Druhé schéma je vhodnější Relace ZákazníkKredit bude menší  Méně bloků, může se vejít do paměti   rychlejší table/index scan PA152, Vlastislav Dohnal, FI MUNI, 2016 20 Vertikální dělení  Jedna relace je vhodnější než dvě Pokud jsou atributy přistupovány současně  není potřeba operace spojení  Dvě relace jsou vhodnější Atributy jsou přistupovány samostatně (nebo některé řádově častěji) Atributy jsou velké (dlouhé řetězce, …)  Pozor: LOB jsou uloženy mimo relace. PA152, Vlastislav Dohnal, FI MUNI, 2016 21 Vertikální dělení  Jiný příklad Zákazník má id a adresu (ulice, město, psč)  Jaký má smysl schéma: ZákazníkUlice(id, ulice) ZákazníkMěsto(id, město, psč) PA152, Vlastislav Dohnal, FI MUNI, 2016 22 Vertikální dělení – výkonnost  R(X,Y,Z) - X číslo, Y a Z dlouhé řetězce Rychlost závisí na stylu dotazování 0 0,005 0,01 0,015 0,02 No Partitioning Query XYZ Vertical Partitioning Query XYZ No Partitioning Query XY Vertical Partitioning Query XY Througput(queries/sec)Table-scan Bez dělení: R(X,Y,Z) Vert. dělení: R1(X,Y) R2(X,Z) SQLServer 2k Windows 2k PA152, Vlastislav Dohnal, FI MUNI, 2016 23 Vertikální dělení – výkonnost  R(X,Y,Z) - X číslo, Y a Z dlouhé řetězce Selekce X=?, projekce XY nebo XYZ 0 200 400 600 800 1000 0 20 40 60 80 100 Throughput(queries/sec) % of access that only concern XY no vertical partitioning vertical partitioning Vert. dělení Vhodné, pokud vybírám XY častěji než v 25% případů. Spojení znamená 2 přístupy do indexu. PA152, Vlastislav Dohnal, FI MUNI, 2016 24 Vertikální spojování (antipartitioning)  Začínáme s normalizovaným schématem  Přidáváme atributy k jedné z relací  Příklad Akciový trh  Historie cen akcií za posledních 3 000 dní  Makléř se rozhoduje hlavně podle posledních 10 dnů Schéma  AkcieDetail(akcie_id, datum_vydání, firma)  AkcieCena(akcie_id, datum, cena) PA152, Vlastislav Dohnal, FI MUNI, 2016 25 Vertikální spojování (antipartitioning)  Schéma AkcieDetail(akcie_id, datum_vydání, firma) AkcieCena(akcie_id, datum, cena)  Dotazy na 10ti denní historii jsou náročné I když je index na akcie_id, datum Navíc pro další informace je třeba spojení s AkcieDetail PA152, Vlastislav Dohnal, FI MUNI, 2016 26 Vertikální spojování (antipartitioning)  Proveďme replikaci dat  Schéma AkcieDetail(akcie_id, datum_vydání, firma, cena_dnes, cena_včera, …, cena_před_10_dny) AkcieCena(akcie_id, datum, cena)  Dotazování na 10ti denní historii je 1x prohledání indexu, není třeba spojení PA152, Vlastislav Dohnal, FI MUNI, 2016 27 Vertikální spojování (antipartitioning)  Nevýhoda Replikace dat  Ne příliš velká  Lze odstranit neukládáním v AkcieCena   dotazy na průměrné ceny se komplikují, … PA152, Vlastislav Dohnal, FI MUNI, 2016 28 Ladění denormalizace  Denormalizace Porušení normalizace schéma Pouze z důvodu rychlosti!  Vhodné pro Současný výběr atributů z různých relací  Nevhodné pro Časté aktualizace dat   vyhledání „zdrojových“ dat kvůli replikaci PA152, Vlastislav Dohnal, FI MUNI, 2016 29 Ladění denormalizace  Příklad  region(r_regionkey, r_name, r_comment)  nation(n_nationkey, n_name, n_regionkey, n_comment)  supplier(s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment)  item(i_orderkey, i_partkey, i_suppkey, i_linenumber, i_quantity, i_extendedprice, i_discount, i_tax, i_returnflag, i_linestatus, i_shipdate, i_commitdate, i_receiptdate, i_shipmode, i_comment)  T(item) = 600 000 T(supplier) = 500, T(nation) = 25, T(region) = 5  Dotaz: vyhledání položek (items) evropských výrobců PA152, Vlastislav Dohnal, FI MUNI, 2016 30 Ladění denormalizace  Denormalizovaná relace item  itemdenormalized (i_orderkey, i_partkey , i_suppkey, i_linenumber, i_quantity, i_extendedprice, i_discount, i_tax, i_returnflag, i_linestatus, i_shipdate, i_commitdate, i_receiptdate, i_shipmode, i_comment, i_regionname);  600 000 řádků PA152, Vlastislav Dohnal, FI MUNI, 2016 31 Ladění denormalizace  Dotazy: SELECT i_orderkey, i_partkey, i_suppkey, i_linenumber, i_quantity, i_extendedprice, i_discount, i_tax, i_returnflag, i_linestatus, i_shipdate, i_commitdate, i_receiptdate, i_shipinstruct, i_shipmode, i_comment, r_name FROM item, supplier, nation, region WHERE i_suppkey = s_suppkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = 'Europe'; SELECT i_orderkey, i_partkey, i_suppkey, i_linenumber, i_quantity, i_extendedprice, i_discount, i_tax, i_returnflag, i_linestatus, i_shipdate, i_commitdate, i_receiptdate, i_shipinstruct, i_shipmode, i_comment, i_regionname FROM itemdenormalized WHERE i_regionname = 'Europe'; PA152, Vlastislav Dohnal, FI MUNI, 2016 32 Ladění denormalizace – výkonnost  Dotaz Najdi všechny položky evropských výrobců 0,0000 0,0005 0,0010 0,0015 0,0020 normalized denormalized Throughput(Queries/sec) Normalized: spojení 4 relací Denormalized: jediná relace 54% vylepšení Oracle 8i EE Windows 2k 3x 18GB disk (10 000 ot.) 54% gain PA152, Vlastislav Dohnal, FI MUNI, 2016 33 Shlukované uložení relací  Alternativa k denormalizaci  Není vždy podporováno DB systémem  Oracle Shlukované uložení dvou relací  Objednávka2(dodavatel_id, výrobek_id, počet)  Dodavatel(id, adresa) Uložení  U záznamu dodavatele jsou uloženy jeho objednávky PA152, Vlastislav Dohnal, FI MUNI, 2016 34 Shlukované uložení relací  Příklad  Objednávka2(dodavatel_id, výrobek_id, počet)  Dodavatel(id, adresa) 10, Inter-pro.cz Hodonín 10, 235, 5 10, 545, 10 11, Unikov Bzenec 11, 123, 30 11, 234, 2 11, 648, 10 11, 956, 1 12, Školex Modřice 12, 12, 50 12, 34, 120 … PA152, Vlastislav Dohnal, FI MUNI, 2016 35 Horizontální dělení  Rozděluje obsah tabulky podle řádků Vertikální podle sloupců  Důvod Snížení objemu dat, se kterým se pracuje Usnadnění mazání  Použití Archivace dat Prostorové dělení … PA152, Vlastislav Dohnal, FI MUNI, 2016 36 Horizontální dělení  Automaticky Moderní (komerční) DB systémy  MS SQL Server 2005 a novější  Oracle 9i a novější, …  Ručně S podporou DB systému  Optimalizátor dotazů Bez podpory DB systému PA152, Vlastislav Dohnal, FI MUNI, 2016 37 Horizontální dělení  Změny dotazů: Automaticky  Beze změny Ručně  S podporou DB systému  Beze změny  Dědičnost tabulek / definice pohledu (UNION ALL)  Bez podpory DB systému  Ruční změna dotazu  Je třeba upravit seznam používaných tabulek ve FROM části. PA152, Vlastislav Dohnal, FI MUNI, 2016 38 Horizontální dělení – SQL Server  MS SQL Server 2005 a novější  Vytvoření dělicí funkce  CREATE PARTITION FUNCTION  Dělení na intervaly  Vytvoření dělicího schéma  CREATE PARTITION SCHEME  Kam se budou data ukládat (na jaké oddíly úložiště)  Vytvoření dělené tabulky  CREATE TABLE … ON dělicí schéma  Ukládaná data jsou automaticky dělena do oddílů  Vytváření indexů  CREATE INDEX  Indexy jsou vytvářeny na oddílech tabulky, tj. automaticky děleny PA152, Vlastislav Dohnal, FI MUNI, 2016 39 Horizontální dělení – Oracle  Oracle 9i a novější Dělení podle rozsahu, výčtu (seznamu), hašování  Podporuje i dvojité rozdělení  Oddíly se dělí na pododdíly Přímo v CREATE TABLE http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7002.htm#i2129707 Horizontální dělení - MySQL  Part of SQL syntax, applies to indexes  Types:  hash, range, list; also double partitioning  Limitation on UNIQUE constraints  All columns used in the table's partitioning expression must be part of every unique key the table may have. PA152, Vlastislav Dohnal, FI MUNI, 2016 40 CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=MyISAM PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6 CREATE TABLE ti … PARTITION BY RANGE (MONTH(tr_date)) ( PARTITION spring VALUES LESS THAN (4), PARTITION summer VALUES LESS THAN (7), PARTITION fall VALUES LESS THAN (10), PARTITION winter VALUES LESS THAN MAXVALUE ); Including primary key PA152, Vlastislav Dohnal, FI MUNI, 2016 41 Horizontální dělení – PostgreSQL  PostgreSQL 8.2 a vyšší Dělení podle rozsahu, výčtu (seznamu)  Princip (http://www.postgresql.org/docs/current/static/ddl-partitioning.html) Využití dědičnosti tabulek  Vytvoření základní tabulky  Nebude ukládat data, bez indexů, …  Jednotlivé oddíly budou zděděné tabulky  Pro každou tabulku definovat CHECK omezení povolených dat  Vytvoření případných indexů Nevýhoda: nelze používat cizí klíče. PA152, Vlastislav Dohnal, FI MUNI, 2016 42 Horizontální dělení – PostgreSQL  Princip Vkládání záznamů  Vkládání do primární tabulky  Primární tabulka má pravidla pro vkládání  Vkládání pouze do „nejnovějšího“ oddílu  jeden RULE  Obecně je třeba mít RULE pro každý oddíl  Lze realizovat i triggerem (instead-of type)… V případě použití pohledů  Lze definovat INSTEAD OF trigger PA152, Vlastislav Dohnal, FI MUNI, 2016 43 Horizontální dělení – PostgreSQL  Příklad v db.fi.muni.cz, schéma xdohnal Nerozdělená tabulka account  Primární klíč id  R(account) = 200 000  V(account,home_city) = 5 Rozdělená tabulka account_parted  Podle home_city (5 oddílů)  Oddíly account_parted1 .. account_parted5 home_city | count home_city1 | 40020 home_city2 | 40186 home_city3 | 39836 home_city4 | 39959 home_city5 | 39999 PA152, Vlastislav Dohnal, FI MUNI, 2016 44 Horizontální dělení – PostgreSQL  Statistiky Tabulka Řádků Velikost Indexy account 200 000 41 984 kB 4 408 kB account_parted 0 0 kB 8 kB account_parted1 40 020 8 432 kB 896 kB account_parted2 40 186 8 464 kB 896 kB account_parted3 39 836 8 392 kB 888 kB account_parted4 39 959 8 416 kB 896 kB account_parted5 39 999 8 424 kB 896 kB Celkem: 200 000 42 128 kB 4 472 kB PA152, Vlastislav Dohnal, FI MUNI, 2016 45 Horizontální dělení – PostgreSQL  Optimalizátor dotazů Povolení kontroly omezení na oddílech  Dotazy (porovnejte plány provádění) select * from account where id=8; select * from account_parted where id=8; select count(*) from account where home_city='home_city1'; select count(*) from account_parted where home_city='home_city1'; select * from account where home_city='home_city1' and id=8; select * from account_parted where home_city='home_city1' and id=8; set constraint_exclusion=on; -- or set it to partition