Optimalizace tabulky – pomalé SELECTY, INSERTY i DELETY

5. 12. 2016 13:44:00

Dobrý den, rád bych poprosil zkušenější o pomoc. Pokouším se optimalizovat DB jedné aplikace. Z logu SQL dotazů, který jsem vytvořil, se jeví nejhůře tato tabulka:

Problematické jsou tyto dotazy:

1. Zde mně MySQL Workbench ukazuje, že pokud změním PRIMARY klíč na PRIMARY(id_subjektu, cislo_kola, id_trhu, mezni_cena), sníží se náklady na vykonávání dotazu na 55%.

Jak bych mohl urychlit tento dotaz? Jediné, co mě napadá, je úprava indexu - jako PRIMARY klíč použít nějaké id_poptavky, ale původní klíč (obsahující 4 sloupce) musí zůstat. Přidání PRIMARY klíče id_poptavky navíc opět zpomalí dotaz číslo 1.

3. Jak bych mohl urychlit tyto dotazy?

Ještě doplním, že id_subjektu i id_meny jsem upravil z varchar na smallint, ale nepřineslo to výrazné zlepšení.

Děkuji za ochotu.

5. 12. 2016 13:44:00

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241480

Marek Urban

(7 hodnocení)

5. 12. 2016 14:03:17

Osobně bych doporučil mít jeden PK, co to máte teď jako PK mít UK. Ale pro smysluplnější radu bych doporučil hodit sem celý model a kolik je v tabulkách dat.

5. 12. 2016 14:03:17

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241479

Pavel Straka

(23 hodnocení)

5. 12. 2016 14:05:27

Ano, to mě také napadlo (PK id_poptavky), ale jak jsem psal, velmi to zpomalí dotaz č.1.

Poptavky mají cca 20 000 řádků, subjekty 150, meny 15, trhy 20.

5. 12. 2016 14:05:27

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241478

Marek Urban

(7 hodnocení)

5. 12. 2016 14:12:24

Napsal Pavel Straka;1343974
Ano, to mě také napadlo (PK id_poptavky), ale jak jsem psal, zpomalí to dotaz č.1.

Chce to vidět celý model, oprimalizujte i ty závislé tabulky.

Opravdu je nutné mít poptávané množsví jako poptavane_mnozstvi a mezni_cenu jako int a bigint ? Nemohou být decimal ?

O kolik se jedná vět v tabulce poptávky ?

id_trhu už předpokládám že máte také již jako int nebo smallint ne ?

5. 12. 2016 14:12:24

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241477

Pavel Straka

(23 hodnocení)

5. 12. 2016 14:17:34

Chce to vidět celý model, oprimalizujte i ty závislé tabulky.

Ano, já jsem tam ten model doplnil, takto Vám to stačí, prosím?

Opravdu je nutné mít poptávané množsví jako poptavane_mnozstvi a mezni_cenu jako int a bigint ? Nemohou být decimal ?

Změna typu na decimal by přinesla zrychlení?

O kolik se jedná vět v tabulce poptávky ?

Vět? To znamená počet řádků?

id_trhu už předpokládám že máte také již jako int nebo smallint ne ?

Ano, u tabulky trhy je PRIMARY klíč jako AUTO_INCREMENT INT.

5. 12. 2016 14:17:34

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241476

Marek Urban

(7 hodnocení)

5. 12. 2016 14:17:50

Napsal Pavel Straka;1343974
Ano, to mě také napadlo (PK id_poptavky), ale jak jsem psal, velmi to zpomalí dotaz č.1.
Poptavky mají cca 20 000 řádků, subjekty 150, meny 15, trhy 20.

Začněte tím, že všechny IDčka uděláte jako integer např. int(11)

Tabulka ID_meny se jeví jako naprosto zbytečná když nemá žádný sloupec.

V 20tis. větách bych to viděl maximálně na vteřiny.

Obecně varchar a text jsou pro databázi nejbolestivější na spojování a dohledávání.

5. 12. 2016 14:17:50

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241475

Pavel Straka

(23 hodnocení)

5. 12. 2016 14:18:48

Začněte tím, že všechny IDčka uděláte jako integer např. int(11)

Ano, mám, bohužel prakticky žádné zlepšení.

Tabulka ID_meny se jeví jako naprosto zbytečná když nemá žádný sloupec.

Souhlasím, zřejmě to měl být jakýsi číselník (nejsem autorem aplikace ani návrhu DB). Pokud do tabulky měny přidám id_meny jako PRIMARY AUTO_INCREMENT TINYINT a stávající sloupec přejmenuji na nazev_meny, dává již tabulka smysl?

V 20tis. větách bych to viděl maximálně na vteřiny.

Ano, jsou to maximálně vteřiny, přesto bych chtěl tento čas snížit, nevidíte, prosím, nějaké možné zlepšení?

5. 12. 2016 14:18:48

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241474

Marek Urban

(7 hodnocení)

5. 12. 2016 14:26:50

Napsal Pavel Straka;1343983
Ano, mám, bohužel prakticky žádné zlepšení.

Souhlasím, zřejmě to měl být jakýsi číselník (nejsem autorem aplikace ani návrhu DB). Pokud do tabulky měny přidám id_meny jako PRIMARY AUTO_INCREMENT TINYINT a stávající sloupec přejmenuji na nazev_meny, dává již tabulka smysl?

Ano, jsou to maximálně vteřiny, přesto bych chtěl tento čas snížit, nevidíte, prosím, nějaké možné zlepšení?

Hoďte sem model po opravě :) Mám i tabulky kde jsou miliony řádků a dotazy jsou v řádech vteřin. Hodně dělá i server s SSD diskem.

5. 12. 2016 14:26:50

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241473

Pavel Straka

(23 hodnocení)

5. 12. 2016 14:30:52

PRIMARY klíč je id_poptavky, cislo_kola je UNIQUE(id_subjektu, cislo_kola, id_trhu, mezni_cena).

V tomto modelu je ale dle MySQL Workbench náročný dotaz č.1 (stejná náročnost jako ve starém modelu). Pomůže klíč PRIMARY(id_subjektu, cislo_kola, id_trhu, mezni_cena) a id_poptavky vyhodit.

Mám i tabulky kde jsou miliony řádků a dotazy jsou v řádech vteřin. Hodně dělá i server s SSD diskem.

Právě proto mně to přijde jako velmi dlouhá doba, vzhledem k tomu, že mám 20.000 řádků.

Ohledně hardwaru nemohu provést žádnou změnu.

5. 12. 2016 14:30:52

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241472

Marek Urban

(7 hodnocení)

5. 12. 2016 14:39:05

Napsal Pavel Straka;1343986

PRIMARY klíč je id_poptavky, cislo_kola je UNIQUE(id_subjektu, cislo_kola, id_trhu, mezni_cena).
V tomto modelu je ale dle MySQL Workbench náročný dotaz č.1 (stejná náročnost jako ve starém modelu). Pomůže klíč PRIMARY(id_subjektu, cislo_kola, id_trhu, mezni_cena) a id_poptavky vyhodit.
Právě proto mně to přijde jako velmi dlouhá doba, vzhledem k tomu, že mám 20.000 řádků.
Ohledně hardwaru nemohu provést žádnou změnu.

Přidat k id_trhy Primary key, napříč celým modelem používat pro id_ int. apod.

Dotaz č.1 jedna už musí vypadat, protože parametr byl stringový a teď je int.

5. 12. 2016 14:39:05

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241471

Pavel Straka

(23 hodnocení)

5. 12. 2016 14:48:24

id_trhu je PRIMARY KEY, nechápu, proč se to v modelu nevygenerovalo.

Když jsem zkoušel experimentálně naměřit rychlost provádění dotazů č.1 ve staré a nové formě (varchar vs int) - pustil jsem několiksetkrát dotaz, zaznamenával čas a ten pak zprůměroval - nedošlo k prakticky žádnému zlepšení.

VISUAL EXPLAIN pro dotaz č.1 (nový model)

vypadá takto s PRIMARY(id_poptavky)

a takto s PRIMARY(id_subjektu, cislo_kola, id_trhu, mezni_cena)

Tak nevím, jsem z toho zmatený.

PS: Obecně bych chtěl poprosit o radu, jak mohu testovat dobu provádění dotazu, protože i když si udělám v PHP skriptu simulaci, kdy dotaz volám např. 10000x a pak časy zprůměruju, průměrný čas se pořád velmi liší. Jednou je okolo vteřiny, podruhé přes 3 vteřiny, potřetí pod vteřinu.

5. 12. 2016 14:48:24

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241470

Lukáš Oreška

(3 hodnocení)

5. 12. 2016 15:49:22

Jak je nastavená SQL cache v průběhu testování?

Neběží na serveru nějaká databáze, která by spotřebovala většinu výkonu serveru?

Průměrný čas na jeden dotaz je při 20 tis. řádků i 3 vteřiny? Jak moc se ty řádky liší?

Jak moc se bude lišit čas, pokud budou všechny sloupce v order by řazeny stejně (ASC)?

5. 12. 2016 15:49:22

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241469

Pavel Straka

(23 hodnocení)

5. 12. 2016 16:57:08

Jak je nastavená SQL cache v průběhu testování?

Nevím, jak to, prosím, zjistím?

Neběží na serveru nějaká databáze, která by spotřebovala většinu výkonu serveru?

Bohužel nevím a i kdyby, nic s tím neudělám. Jde o optimalizaci DB v rámci diplomové práce, tzn., že se zaměřuji skutečně na optimalizaci DB a pokud je dejme tomu slabý HW nebo je server vytěžován jinou aplikací, je to už mimo téma práce. Jinými slovy, je to možné, ale pokud jako závěr z této diskuse vyjde, že další optimalizace dotazu/schématu DB už nepřipadá v úvahu, budu spokojený.

Průměrný čas na jeden dotaz je při 20 tis. řádků i 3 vteřiny? Jak moc se ty řádky liší?

V logu dotazů vystupuje INSERT do této tabulky často v časech větších než 0.5 vteřiny, pak jsou tam 2 případy, kdy zabral dokonce 10 a 11 vteřin. Ty 3 vteřiny - to je čas vycházející z mého testu na localhostu pro vložení 10 000 záznamů.

Jak moc se bude lišit čas, pokud budou všechny sloupce v order by řazeny stejně (ASC)?

Je to opět prakticky stejné, ale jak jsem psal, nevím přesně, jak to experimentálně měřit, protože ty hodnoty času pořád dost kolísají.

5. 12. 2016 16:57:08

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241468

Lukáš Oreška

(3 hodnocení)

5. 12. 2016 17:28:34

První co bych zkusil je toto přidat do dotazu SQL_NO_CACHE, podobně: SELECT SQL_NO_CACHE * FROM TABLE.

Případně použil SET SESSION query_cache_type=0;

Nevím, jestli jsem nepřehlédl výsledky u EXPLAIN, možná by se z toho dalo také něco vyčíst.

5. 12. 2016 17:28:34

https://webtrh.cz/diskuse/optimalizace-tabulky-pomale-selecty-inserty-i-delety#reply1241467

Pavel Straka

(23 hodnocení)

5. 12. 2016 20:23:54

První co bych zkusil je toto přidat do dotazu SQL_NO_CACHE, podobně: SELECT SQL_NO_CACHE * FROM TABLE.
Případně použil SET SESSION query_cache_type=0;

Bohužel, ani jedno nepomáhá, ty průměrné časy pořád kolísají.