Indexy SQL – mariadb

Jiří

(1 hodnocení)

1. 6. 2022 10:31:30

To je starou verzí DB:

https://mariadb.com/kb/en/with/

WITH

MariaDB starting with 10.2.1

Common Table Expressions were introduced in MariaDB 10.2.1.

1. 6. 2022 10:31:30

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502589

TomasX

(4 hodnocení)

1. 6. 2022 11:00:11

verze 10.1 je 7 let stará, tam může chybět spousty chování, to teď už nevím a nikde v produkci takovou verzi nemám.

Nenapadá mě způsob jak tohle zrychlit, spatial indexy tady také nejsou, pokud je výstup ve stovkách tisíc záznamů, nepomůže rozdělení na více dotazů a where in syntaxe. Pak tady jsou externí nástroje, dernomalizaci těch skladových sloupců jsem již zmiňoval (aneb přidat další sloupec product_availability, který může naplňovat trigger podle podmínek _quanity > 0 a _sklad > 0), můžeš materializovat pohled (klasicky vytvořit si novou tabulku, kam předpočítáš ty hodnoty pro všechny kategorie a budeš jí aktualizovat třeba po 10 minutách) nebo můžeš výsledky cachovat, pokud teda jsou dotazy pořád stejné (u počtu položek na kategorii by to možné mohlo být).

S tím samotným dotazem mě řešení nenapadá, třeba někdo jiný se chytne.

1. 6. 2022 11:00:11

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502588

puhy.cz

(5 hodnocení)

1. 6. 2022 11:30:39

Přidal jsem sloupec product_available a ten 1x za 15 minut cronem aktualizuji podle products_status, quantity a sklad. Pak ten výsledný count trvá ale 0,8s,

po přidání indexu INDEX products_available, products_id trvá 0,062s a to už je to co chci.

Jen ještě nějak zoptimalizovat tu aktualizaci, protože to samozřejmě trvá celkem dlouho, mám to takto:

Je tam nějaký zásadní prostor pro zlepšení?

1. 6. 2022 11:30:39

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502587

skorozacatecnik

1. 6. 2022 11:39:42

Napsal puhy.cz;1645940
Jen ještě nějak zoptimalizovat tu aktualizaci, protože to samozřejmě trvá celkem dlouho, mám to takto:

Je tam nějaký zásadní prostor pro zlepšení?

To psal někde výše TomášX, pomocí TRIGGERů na zápis či update, nebo ty UPDATEs zabal do transakce.

1. 6. 2022 11:39:42

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502586

TomasX

(4 hodnocení)

1. 6. 2022 11:50:36

skvělé, ano, i takhle se to občas optimalizuje :). Ten index products_available_products_id mi sice připadá nějaký zběsilý, ale ok, když to tak funguje, super.

U toho updatu bych viděl prostor pro zlepšení, nemusíš aktualizovat jeden produkt za druhým, ale můžeš je aktualizovat všechny ve dvou dotazech (zrychlí se to asi 10x):

Teda ono to jde i v jednom dotazu:

Případně s nějakou podmínkou na aktivnost produktu:

1. 6. 2022 11:50:36

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502585

puhy.cz

(5 hodnocení)

1. 6. 2022 11:52:17

Jaky vliv má přidání triggerů na výkon? Když se tam každou hodinu aktualizuje 1 000 000 položek, aby ty inserty update netrvala 2x tak dlouho?

Co myslíš tím zabalit UPDATEs do transakce?

1. 6. 2022 11:52:17

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502584

TomasX

(4 hodnocení)

1. 6. 2022 12:00:06

trigger zpomalý samotné uložení dat asi o 20 % (to je ale jen ta část na enginu innodb, kdy se trigger vykonává), běžně součástí dotazu jsou věci okolo, jako komunikace s databází po síti, zamčení tabulky, vrácení výsledku. Reálně lze tedy čekat zpomalení asi o 5 %. Daleko více ti zápis zpomaluje to množství indexů, musí se totiž při každém zápisu přepočítat, je tedy lepší jich mít co nejméně a nemít takové, které nepoužíváš.

To jak to máš teď napsané znamená, že produkty svoji availability získávají postupně jak ti jede tvůj skript. Dá se to celé uzavřít do transakce tak, že všechny produkty dostanou celou availability najednou. V Mysql se to dělá:

Dibi pak na to má nějaké bulk rozhraní (z hlavy to již nedám).

V tomhle případě bych ale nedoporučoval to strkat do transakcí, zamčeš si totiž databázi na poměrně dlouhou dobu a mezitím se neudělá žádný zápis do těhle tabulek. Sebere to strašně moc paměti (aktualizuje se vesměs celá tabulka). V tomhle případě by nekonzistentní data (kdy část tabulky je již aktualizovaná a část ne) neměla ničemu vadit, i tak už máš stav starý 15 minut, tak nevadí, když se o pár vteřin opozdí. Cena za transakci je za mě příliš vysoká. Osobně bych ale volil ten zápis v jednom/dvou dotazech, viz můj minulý příspěvek.

1. 6. 2022 12:00:06

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502583

(20 hodnocení)

1. 6. 2022 12:17:06

Pokud tu tabulku neaktualizuje nic jineho tak se da pouzivat takovej dirty trick, kdy se vytvori nebo drzi jeste druha tabulka se stejnymi zaznamy... deaktivuji se na ni indexy, provede se aktualizace, ktera je bez indexu uplne jina liga v rychlosti, pak se zavedou indexy, a prejmenuje se stara neco jineho a nova s aktualizovanymi daty na jmeno te puvodni... Samozrejme je treba brat v potaz ze to jde jen u tabulek kde opravdu se nemuze stat ze behem te aktualizace jeste probehne nakej jinej update pak by ti ten update timhle zmizel no... Ale co vim tak do vykonu a rychlosti to bylo super...

1. 6. 2022 12:17:06

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502582

puhy.cz

(5 hodnocení)

1. 6. 2022 12:56:28

Tohle trvá asi 3 sekundy, takže bude možné to aktualizovat častěji než 1x za 15 minut, to je dobré:

update products SET products_available = case when ((products_quantity > 0 or products_sklad > 0) and products_status = 1) then 1 else 0 end;

---------- Příspěvek doplněn 01.06.2022 v 13:20 ----------

Pak tady mám ještě další špek se sort, pokud je produktů v kategorii hodně, tak samozřejmě řazení podle name trvá hodně dlouho. Co hledat pro urychlení?

select p.products_image, pd.products_name, p.products_quantity, p.products_id, p.products_price, p.products_sort_order

from zs_products_description pd, zs_products p, zs_products_to_categories p2c

where p.products_available = 1 and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and p2c.categories_id IN (791)

order by p.products_sort_order, pd.products_name limit 72, 24;

Když dám pryč ten order, tak to je za 0,2sec což by mi stačilo, pokud tam ten order je, tak to trvá 3s a to už je moc. Indexy mám jak na products_name tak products_sort_order. Co hledat dál?

Explain

---------- Příspěvek doplněn 01.06.2022 v 13:21 ----------

Napsal Aleš Jiříček;1645945
Pokud tu tabulku neaktualizuje nic jineho tak se da pouzivat takovej dirty trick, kdy se vytvori nebo drzi jeste druha tabulka se stejnymi zaznamy... deaktivuji se na ni indexy, provede se aktualizace, ktera je bez indexu uplne jina liga v rychlosti, pak se zavedou indexy, a prejmenuje se stara neco jineho a nova s aktualizovanymi daty na jmeno te puvodni... Samozrejme je treba brat v potaz ze to jde jen u tabulek kde opravdu se nemuze stat ze behem te aktualizace jeste probehne nakej jinej update pak by ti ten update timhle zmizel no... Ale co vim tak do vykonu a rychlosti to bylo super...

Díky, tohle používám při načítání XML od dodavatelů do db, ale u týhle tabulky si to nemůžu dovolit, protože tam do toho zasahuje tolik věcí, že to je hodně živý.

1. 6. 2022 12:56:28

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502581

TomasX

(4 hodnocení)

1. 6. 2022 13:43:50

mariadb použije index na sortování, když sloupce v order by odpovídají indexu, který má, zároveň vše by mělo být ASC. Tj. v tomhle případě udělat složený index (products_sort_order, products_name).

A ještě lepší rychlosti dosáhneš pokud všechny sloupce, které máš v selectu máš zároveň v order by a nad nimi je složený index ve stejné pořadí, pak se vynechá úplně scan fáze a veškerá data se načtou z indexu. Tak praví teorie, v praxi se databáze umí podle svých statistik rozhodnout trochu jinak, měření času a explain jsou tvoji kamarádi (v explainu hledej použití indexu, který odpovídá sloupcům v order by).

Tady těch sloupců máš hodně, je ale možné dotaz rozdělit na dva, v jednom si vytáhnout id produktů a jejich pořadí a v druhém samotné produkty přes where in. Tady máš ještě špek v podobě, kdy řadíš přes dvě tabulky, to je problém, nelze to řadit přes např. products_id? Nebo to lze ještě optimalizovat trikem, který může poskytovat skoro přesné výsledky, ale řádově rychleji, nejprve si načteš id produktů s malým přesahem a poté to seřadíš a odfiltruješ v druhém dotazu nějako takhle:

(místo 72 vybírám dvojnásobek záznamů - 144). Nad tím mít poté index (products_sort_order, products_id). A druhým dotazem získáš data k produktům a dořadíš podle jména a ořízneš:

Obecně vícesloupcové řazení je opět náročná procedura a opět se to dá denormalizovat vypočítáním vah pro řazení dopředu a ty používat. Často u velkých databází produktů/položek máme speciální tabulku, která je předpočítaná a obsahuje pouze váhy položek pro řazení, tak aby to vše bylo v jednom sloupci. Případně lze použít nějaké in-memory databáze k tomu určené, které drží seznamy položek a jejich pořadí (redis, elasticsearch aj.).

1. 6. 2022 13:43:50

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502580

puhy.cz

(5 hodnocení)

1. 6. 2022 13:50:19

Základní problém je v tom, že products_sort_order, products_name jsou každý v jiný tabulce :-(

---------- Příspěvek doplněn 01.06.2022 v 13:54 ----------

Nešlo by nějak tu tabulku s tím products_name udržovat defaultně seřazenou podle products name?

Když dám vypsat, tak je vidět, že to je seřazený vůbec nevím podle čeho. Pak bych ten order by mohl dát úplně pryč, kdyby ta tabulka byla seřazená defaultně. Jak udělám aby se ta tabulka sama řadila podle products_name?

---------- Příspěvek doplněn 01.06.2022 v 13:56 ----------

Na vyhledávání používám sphinx, to by asi mohlo být dobré i na toto, ale zase to generování těch indexů není úplně rychlé a přijde mi, že pro stránkování produktů bude lepší zůstat u přímo u databáze. Případně šla by ta tabulka nějak podle products_name řadit opět občas cronem? Samozřejmě umím udělat order by, ale aby to tak i zůstalo?

1. 6. 2022 13:50:19

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502579

TomasX

(4 hodnocení)

1. 6. 2022 14:19:16

výchozí zařezní je vlastně náhodné, ANSI SQL 92 to nechává na implementaci každého enginu. Pokud chceš stabilní řazení, musíš použít order by.

S zařením si můžeš pomoci pomocnou tabulkou nebo opět sloupcem. Např. takhle si mohu naplnit řadící sloupec products_sort_index

(Snad bude fungovat i v mariadb 10.1). Nad tím sloupcem bys měl mít index, ale nedokáži takhle z hlavy říct, jak se to bude chovat v celém dotazu, bude to ale dobře fungovat, když použiješ ten způsob z mého předchozího příspěvku, nejprve si vytáhneš seřazené produkty a pak si k nim načteš data přes where in.

1. 6. 2022 14:19:16

https://webtrh.cz/diskuse/indexy-sql-mariadb/strana/2/#reply1502578

puhy.cz

(5 hodnocení)

2. 6. 2022 09:48:23

Maria db je aktualizovaná na: 10.2.44

Co mám tady blbě? Bez p. nebo pd. to taky nebere ...

update zs_products

inner join (

select (@row_number:=@row_number + 1) AS sort_index, p.products_id

from zs_products p, (SELECT @row_number:=0) AS t

join zs_products_description pd using(pd.products_id)