Prosba o pomoc s optimalizací databáze

17.10.2005 22:27 | Přečteno: 1287× | Abíčko | poslední úprava: 17.10.2005 22:28

Právě jsem si zkušebně zapnul logování SQL dotazů a mé černé představy se naplnily. Opravdu je načítání rubrik velmi neefektivní a pomalé. Viz bug 337. Z fleku mě napadl jedna optimalizace (o které přemýšlím ve skutečnosti už nějakou dobu), ale ta pomůže jen z části. A jelikož více hlav více ví, chci vás poprosit o radu. Třeba mi poradíte.

Nejdříve krátce popíšu datový model. Na úrovni Javy je popsán v článku Strč prst skrz AbcLinuxu - I (a přes ta léta a spoustu nových služeb se prakticky nezměnil), o databázovém schématu se zmíním teď. Každý objekt má svou tabulku s číselným primárním klíčem. Pak je zde tabulka relace, která vytváří vztah rodič-potomek mezi dvěma řádkami libovolných tabulek pomocí kódu tabulky plus čísla řádky.

Když načtu v Javě nějaký objekt, musím pro něj zjistit i jeho potomky. To zvládne jeden select do tebulky relace, kde vyplním identifikaci předka. Když mě zajímá potomek, už znám kód jeho tabulky a číslo řádky, není problém jej načíst. Tohle je velice jednoduchý a rozšiřitelný postup, který ale má své meze v efektivitě načítání.

A teď konkrétně. Pro načtení rubriky musím nejdříve zvolit aktuální články a načíst jejich relace. Není problém. A pak pro každý článek načíst seznam jeho potomků, načíst dvě položky (honorář, hlavička diskuse), načíst záznam (obsah článku) a zjistit počet přečtení. Tedy čtyři dotazy na každý článek. Naštěstí používám efektivní cache, ale ta je k ničemu, když je prázdná nebo tu stránku ještě nikdo nenačetl.

Dovedu si představit, jak načíst ve třech dotazu celkem všechny záznamy, položky i počty přečtení. Trocha kódování a je to. Jen musím být opatrný a nezaneřádit kód nějakým hackem. Ale neznám cestu, jak v jednom dotazu načíst potomky všech objektu a vyznat se ve výsledku, ke kterému výsledku co patří. A vůbec, jak zadat do jednoho dotazu N dvojic. Leda to shlukovat podle typu předka do tolika dotazů, kolik různých typů předků potřebuji načíst.

Takže jsem si ujasnil myšlenky a nejspíše si na vše přišel sám . Ale možná vás napadne lepší způsob.

CREATE TABLE relace (
 cislo INT AUTO_INCREMENT PRIMARY KEY,  -- identifikator vazby
 predchozi INT NOT NULL,                -- id predchozi vazby
 typ_predka CHAR(1) NOT NULL,           -- id tabulky predka
 predek INT NOT NULL,                   -- id predka
 typ_potomka CHAR(1) NOT NULL,          -- id tabulky obsahu
 potomek INT NOT NULL,                  -- id obsahu
 url VARCHAR(255) DEFAULT NULL,         -- URL stranky
 data TEXT DEFAULT NULL                 -- volitelne jmeno vazby
);

SELECT * FROM relace WHERE typ_predka=? AND predek=?

       

Komentáře

[Michal Čihař]

No možná sis ujasnil myšlenky, ale já jsem se v tomhle zápise dokonale ztratil . Co třeba napsat ty dotazy co provádíš?

[Michal Čihař]

Nenapadlo mě podívat se do bugu a že tam bude ten log .

[Leoš Literák]

select * from relace where typ_predka='P' and predek=42802 
select * from polozka where cislo in (42803,42831) order by cislo
select * from zaznam where cislo in (57550) order by cislo
select soucet from citac where typ like 'P' and cislo=42802

Ctverice zminenych dotazu. Posledni tri by sly celkem snadno aplikovat hromadne na vsechny clanky. Proste by tam tech cisilek trosku pribudlo

Ale ten prvni je v obecnem pripade nejhorsi. Zde ale vim, ze se bude menit jen cislo predka. Jenze stejne radeji hledam obecne reseni. Takze kdyz mam treba dvojice (A,1), (A,2), (B,3), (C,1), IMHO by resenim bylo:

select * from relace where typ_predka='A' and predek in (1,2)
select * from relace where typ_predka='B' and predek in (3)
select * from relace where typ_predka='C' and predek in (1)

A pak rucne projit vracene radky a podle sloupecku predek rozhazet udaje ke spravnym objektum.

[Michal Čihař]

A potřebuješ vůbec všechny ty data? Potomci jsou diskuze?

[Filip Jirsák]

Tohle jde určitě zkrátit na

select * from relace where
  (typ_predka='A' and predek in (1,2)) or
  (typ_predka='B' and predek in (3)) or
  (typ_predka='C' and predek in (1))

Ale jinak se v tom zatím moc nevyznám Je to tak, že v tabulce polozka je honorář a hlavička diskuze a v zaznam je obsah článku? Šlo by sem ještě dát create table pro polozka a zaznam?

Předpokládám, že ta čísla (42803,42831) a (57550) jsou z relace, a které je nebo je polozka a zaznam se pozna podle typ_potomka?

[Leoš Literák]

Aha, no jasne Easy.

Ta cisla jsou potomci v tabulce relace, tabulku urcuje sloupecek typ_potomka. Napriklad pro polozku je to 'P.

Deleni obsahu clanku (zaznam) od hlavicky (polozka) je umyslne, mela to byt optimalizace, aby se dlouhy obsah nenacital pri listovani rubrik. Jak jsem vcera zjistil, tak se to nepovedlo Ale pujde to zoptimalizovat, kdyz uz o tom vim.

Ted frcim na obed ..

[Michal Kubeček]

Deleni obsahu clanku (zaznam) od hlavicky (polozka) je umyslne, mela to byt optimalizace, aby se dlouhy obsah nenacital pri listovani rubrik.

Nestačilo by nepoužívat 'select *'? Navíc u databází bývá zvykem, že v záznamu nejsou celé BLOBy, ale jen jakési handly na ně, ale nevím, jestli to tak dělá i MySQL.

[Leoš Literák]

Jenze ja si ihned nafetchuji cely obsah vcetne blobu a ulozim do cache.

[Filip Jirsák]

Otázka je, jak moc to optimalizovat na počet dotazů – ono by to samozřejmě šlo napsat jako jeden dotaz (OUTER JOIN), který bude v každém řádku ve spoustě sloupečků mít NULL, a pak si to v Javě rozebrat - jenže tím se IMHO moc neušetří. Předpokládám, že všechny dotazy běží nad jedním Connection – navázání spojení je pomalé, ale režie na dotaz/odpověď podle mne nebude tak velká, aby se vyplatilo vše uměle cpát do jednoho dotazu.

No ale jak teď na to koukám, na vypsání seznamu článků v rubrice by měl stačit 1 SQL dotaz Bez zdrojáků a struktury DB se to těžko odhaduje, ale zkusím to popsat abstraktně Tož:

SELECT *
FROM relace AS Rserial, relace AS Rclanky
WHERE Rserial.url = ? AND
  Rserial.cislo = Rclanky.cislo AND Rclanky.typ = 'P'
ORDER BY Rclanky.cislo

Tím bych měl získat čísla všech článků v seriálu. K nim potřebuji přidat obsah (zaznam) a třeba dvě položky z polozka – dejme tomu autor a honorar. Upravím tedy SELECT:

SELECT *
FROM relace AS Rserial, relace AS Rclanky, zaznam, polozka AS Pautor, polozka AS Phonorar
WHERE Rserial.url = ? AND
  Rserial.cislo = Rclanky.cislo AND Rclanky.typ = 'P' AND
  zaznam.cislo = Rclanky.cislo AND
  Pautor.cislo = Rclanky.cislo AND
  Phonorar.cislo = Rclanky.cislo
ORDER BY Rclanky.cislo

Stejně by se do SELECTu přidal i počet položek. Na spojování tabulek jsou databáze optimalizované, pokud jsou na příslušných sloupečcích indexy, mělo by to být OK.

Výsledkem SELECTu by měl být 1 řádek = 1 článek. Místo SELECT * si samozřejmě vyberu jen ty sloupečky, které potřebuji.

Doufám, že tu nepíšu úplné nesmysly a půjde to na strukturu databáze napasovat

[Filip Jirsák]

Jak tak koukám do toho logu, asi nejde pospojovat položku s článkem přímo, ale zase pře relaci. Tedy:

SELECT *
FROM
  relace AS Rserial,
  relace AS Rclanky,
  zaznam,
  polozka AS Pautor,
  polozka AS Phonorar,
  relace AS Rautor,
  relace AS Rhonorar
WHERE Rserial.url = ? AND
  Rserial.cislo = Rclanky.cislo AND Rclanky.typ = 'P' AND
  zaznam.cislo = Rclanky.cislo AND
  Pautor.cislo = Rautor.cislo AND
  Rautor.predek = Rclanky.cislo AND
  Rautor.typ_predka = 'P' AND
  Phonorar.cislo = Rhonorar.cislo AND
  Rhonorar.predek = Rclanky.cislo AND
  Rhonorar.typ_predka = 'P'
ORDER BY Rclanky.cislo

[Leoš Literák]

Ono ani neni cilem mit vsechno v jedinem SQL dotazu. Pak by byl docela problem, jak cachovat vysledky a vubec, jak tvorit SQL dotazy a parsovat jejich vysledek. Preci jen univerzalni rozhrani ma vyhodu, ze jak se jednou napise, nemusi se o ne jiz starat, proste funguje. Takze ted tam vzdy je zaznam, ktery ale nepotrebuji a nekdy tam byvaji diskuse a honorare. V budoucnu muze pribyt janevimco a rozhodne nechci kvuli tomu studovat, co vsechno prestane fungovat.

Takze spise mi jde o zefektivneni vrstvy persistence, aby zvladala hromadne nacitani dat nad skupinou vysledku. A to si myslim, ze zapis + diskuse uz resi.

CREATE TABLE polozka (
 cislo INT AUTO_INCREMENT PRIMARY KEY,  -- jednoznacny identifikator
 typ SMALLINT,                          -- typ polozky (diskuse, faq, ..)
 podtyp VARCHAR(30) NULL,               -- podtyp
 data TEXT NOT NULL,                    -- XML 
 pridal INT(6) NOT NULL,                -- odkaz na uzivatele
 vytvoreno DATETIME,                    -- cas vytvoreni
 zmeneno TIMESTAMP NOT NULL             -- cas posledni zmeny
);

CREATE TABLE zaznam (
 cislo INT AUTO_INCREMENT PRIMARY KEY,  -- jednoznacny identifikator
 typ SMALLINT,                          -- typ zaznamu (HW, SW, clanek ..)
 podtyp VARCHAR(30) NULL,               -- podtyp
 data LONGTEXT NOT NULL,                -- XML 
 pridal INT(6) NOT NULL,                -- odkaz na uzivatele
 vytvoreno DATETIME,                    -- cas vytvoreni
 zmeneno TIMESTAMP NOT NULL             -- cas posledni zmeny
);

[Leoš Literák]

Ted se nestahuji zaznamy. Celkove nacteni rubriky jadernych novin kleslo z 2403 ms na 1466 ms. 39% je slusny vysledek, ale mi nestaci, tudiz delam ted na hromadnem nacitani potomku. Pak budu moci hromadne nacist i diskuse a verim, ze se dostanu nekde k 750 milisekundam. A az nejak rozumne vyresim nacitani ctennosti clanku, mohlo by to klesnout na ctvrtinu skundy. Uvidime. Sledujte bug pro aktuality

[Michal Čihař]

• Proč text porovnáváš pomocí LIKE když ho nepotřebuješ?
• Jaké tam jsou indexy? Některé dotazy mi připadají dost pomalé.
• Zkoušel ses podívat na EXPLAIN u složitějších dotazů?

[Leoš Literák]

ALTER TABLE relace ADD INDEX in_potomek (typ_potomka,potomek);
ALTER TABLE relace ADD INDEX in_predek (typ_predka,predek);
ALTER TABLE relace ADD INDEX in_predchozi (predchozi);
ALTER TABLE relace ADD INDEX in_url (url);

Indexy jsem delal podle EXPLAIN, ale spise jen amatersky.

Pokud jde o prvni dotaz, mas pravdu, take jsem si ted vsimnul, ze ten LIKE je zbytecny, kdyz znam presnou hdonotu. Ale bude to poznat na vykonnosti, kdyz ma sloupecek vzdy jediny znak a ten strcim do vyrazu? Pak snad nebude rozdil mezi porovnanim a LIKE, ne?

[Michal Čihař]

Nevím nakolik je optimalizátor chytrý, jestli zkoumá že LIKE je zbytečný a nechce se mi generovat dost dat, abych to vyzkoušel .

A vůbec co vnořené dotazy? Připadá mi, že by se tam daly použít a pak ti dotazů pár ubyde .

[Martin Tůma]

Chápu to dobře, že každej jednotlivej příspěvek v diskusi získáváš jedním SQL dotazem? V případě, že ano, nebylo by mnohem efektivnější získat všechny příspěvky z jednoho vlákna pomocí 1 dotazu (například podle UID prvního-kořenového záznamu) a ty pak srávně "pospojovat" podle nějakého identifikátoru udávající pozici ve stromu příspěvků u jednotlivých příspěvků až v aplikaci?

[Zdeněk Štěpánek]

Zdravim

Nejsem zadny DB expert, ale jen premyslim nahlas. Ty tema dotazama nactes data a pak z nekolika nactenych tabulek vybiras data a spojujes je dohromady. Otazka je, zda je to zpomaleni v databazi nebo v jave (kde se ty data spojujou a vybiraj z nactenych tabulek).

Osobne si myslim, ze nejdyl trva java, protoze ty dotazy opravdu nejsou zadny slozity veci. A jelikoz si myslim, ze prace s daty by mela byt v databazi rychlejsi nez to rucne kodovat v jave, nechal bych co nejvice hruby prace na databazi. Takze bych vyuzil spojovani tabulek a/nebo vnorene dotazy.

Nevim ted co je to za databazi (nebudu to lovit, tipuju Postgres), neslo by si tam nadefinovat vlastni procedury, mohly by byt vykonnejsi nez java.

Priklad na spojovani tabulek z praxe, prijde mi to skoro to samy. I kdyz nevim, jestli to zrovna tobe ma smysl vykladat.

V jedny tabulce mam hash ID klienta a jeho IP adresu (id, ip). V druhe tabulce mam opet hash ID klienta a jeho cele jmeno (id, name). Ve vysledku potrebuju dvojice cele jmeno a ip adresa (name, ip). Klasicke reseni by bylo nacist tabulku s ip adresama, cyklicky parsovat radky a ziskane hash ID davat do dotazu do druhe tabulky (selectname from druha_tabulka where id=$id). Tohle funguje a na kazdy radek to sezere dva dotazy. Lze ale pouzit spojeni tabulek, sql dotaz vypadfa takhkle:

select name,ip from prvni_tabulka, druha_tabulka where prvni_tabulka.id = druha_tabulka.id

Co to vraci je snad videt jasne.

Zdenek

[Honza Král]

myslim, ze se jedna o MySQL, taky jak na to koukam, tak si rikam, ze by dost slo resit/zjednodusit pomoci ulozenych procedur a hlavne vhodne nadefinovane view...