AKCE! Doména CZ zdarma ke každému ONEbit hostingu
Zobrazují se odpovědi 1 až 25 z 25

Optimalizace tabulky - pomalé SELECTY, INSERTY i DELETY

  1. 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:
    Kód:
    CREATE TABLE `poptavky` (
     `cislo_kola` int(11) NOT NULL,
     `id_subjektu` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
     `id_trhu` int(11) NOT NULL,
     `mezni_cena` int(11) NOT NULL,
     `id_meny` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
     `poptavane_mnozstvi` bigint(20) DEFAULT NULL,
     PRIMARY KEY (`cislo_kola`,`id_subjektu`,`id_trhu`,`mezni_cena`),
     KEY `fk_Poptavky_Trhy1` (`id_trhu`),
     KEY `fk_Poptavky_Subjekty1` (`id_subjektu`),
     KEY `fk_Poptavky_Meny1` (`id_meny`),
     CONSTRAINT `fk_Poptavky_Meny1` FOREIGN KEY (`id_meny`) REFERENCES `meny` (`id_meny`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_Poptavky_Subjekty1` FOREIGN KEY (`id_subjektu`) REFERENCES `subjekty` (`id_subjektu`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fk_Poptavky_Trhy1` FOREIGN KEY (`id_trhu`) REFERENCES `trhy` (`id_trhu`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
    Problematické jsou tyto dotazy:
    1.
    Kód:
    SELECT cislo_kola, mezni_cena, poptavane_mnozstvi, nazev_trhu FROM poptavky, trhy WHERE id_subjektu = 'xyz' AND poptavky.id_trhu = trhy.id_trhu ORDER BY cislo_kola ASC, nazev_trhu, mezni_cena DESC;
    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%.
    2.
    Kód:
    INSERT INTO poptavky VALUES (16, 'xyz', 17, 166, 'koruna', 30) , (16, 'xyz', 17, 164, 'koruna', 30) , (16, 'xyz', 17, 162, 'koruna', 31);
    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.
    Kód:
    SELECT * FROM poptavky WHERE cislo_kola = 15 AND id_subjektu =  'xyz' AND id_trhu = 19 ; 
    DELETE  FROM poptavky WHERE cislo_kola = 15 AND id_subjektu =  'xyz' AND id_trhu = 19 ;
    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.

  2. Co se právě děje na Webtrhu?
  3. 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.

  4. 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. Citace Původně odeslal Pavel Straka Zobrazit příspěvek
    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 ?

  6. 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.

  7. Citace Původně odeslal Pavel Straka Zobrazit příspěvek
    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í.
    Naposledy upravil Marek Urban : 05.12.2016 v 14:23

  8. 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í?

  9. Citace Původně odeslal Pavel Straka Zobrazit příspěvek
    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.


  10. 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.

  11. Citace Původně odeslal Pavel Straka Zobrazit příspěvek

    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.

  12. 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)
    Kód:
    SELECT cislo_kola, mezni_cena, poptavane_mnozstvi, nazev_trhu FROM poptavky, trhy WHERE id_subjektu = 'xyz' AND poptavky.id_trhu = trhy.id_trhu ORDER BY cislo_kola ASC, nazev_trhu, mezni_cena DESC;
    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.

  13. 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)?

  14. 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í.

  15. 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.

  16. 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í.

    Nevím, jestli jsem nepřehlédl výsledky u EXPLAIN, možná by se z toho dalo také něco vyčíst.
    Nerozumím, VISUAL EXPLAIN výsledky jsou v mém příspěvku trochu výše.

  17. Poprosil bych o klasický EXPLAIN (stačí napsat před onen select).

    Moc se mi tam nelíbí ten filesort a tmp table, ale hádám, že to bude tím rozdílným řazením. Třeba pomůže někdo zkušenější.


  18. Předpokládám dobře, že je to explain pro select, který obsahuje join? Když tak sem hoď celý dotaz.

  19. Ano, je to pořád ten dotaz č.1:
    Kód:
    SELECT cislo_kola, mezni_cena, poptavane_mnozstvi, nazev_trhu FROM poptavky, trhy WHERE id_subjektu = 'xyz' AND poptavky.id_trhu = trhy.id_trhu ORDER BY cislo_kola ASC, nazev_trhu, mezni_cena DESC;

  20. Jasně, jsem slepej. Tabulku trhy tam máš jen kvůli tomu, aby si dostal název trhu?

    Pokud ano, odstraň tuto část z dotazu a zkus to změřit.

  21. Ano.
    Bez toho je to cca 2x rychlejší, ale v tom případě si stejně musím jiným dotazem název trhu zjistit.

  22. 2x rychlejší, to je to pořád v řádech sekund?

    Ten trh vyřešíme joinem nebo vnořeným selectem

  23. Ne, ne, v řádech sekund se to objevuje v SQL logu v ostrém prostředí.
    Teď když si to testuji na localhostu 1000x spustit dotaz a udělat průměr, byl čas okolo 0.005s pro původní a okolo 0.0025s pro nový dotaz.

  24. Tak to je v pořádku.

  25. Dejte atribut "id_subjektu" na první místo v PK. pokud to nesmíte udělat, přidejte index na tento atribut.
    Přítomnost atributu "mezní_cena" v PK nemá v kontextu informací zde opodstatnění.
    Pro testování nemá smysl opakovat stejný dotaz vícekrát, DB dodává výsledek z cache.
    Tipovat pomalé dotazy z logu DB je správná cesta.

Hostujeme u Server powered by TELE3