MySQL rychlost filtrování

MySQL rychlost filtrování
« kdy: 21. 02. 2020, 14:39:53 »
Mám tabulku produktů, která obsahuje něco kolem 10 000 záznamů. Tabulka obsahuje údaje, které jsou vyžadovány pro každý produkt. Dalších cca 6 tabulek obsahuje informace o ceně, skladových zásobách, použitých materiálech, výrobcích aj.
Mám několik případů, kdy zákazník potřebuje filtrovat z informací obsažených ve všech 7 tabulkách. JOIN všech tabulek obsahuje 900 000 záznamů.
25 paralelně filtrujících klientů obdrží výsledky do 5 sec.. Indexy jsou nastavené podle nejhledanějších parametrů.

Pokud si zdrojovou filtrovací tabulku předpřipravím a vyhnu se "joinu", 25 klientů obdrží výsledky do 0,3 vteřin. Toto řešení vyžaduje zdrojovou filtrovací tabulku po určité době obnovovat s čímž můžu žít.

Je tohle řešení nevhodné?
Na co bych se měl případně zaměřit?

Kód: [Vybrat]
id select_type table type        possible_keys key
1 SIMPLE           A index         PRIMARY
1 SIMPLE           B eq_ref PRIMARY PRIMARY
1 SIMPLE           C eq_ref PRIMARY PRIMARY
1 SIMPLE           D eq_ref PRIMARY PRIMARY
1 SIMPLE           E eq_ref PRIMARY PRIMARY
1 SIMPLE           F ref         PRIMARY PRIMARY
1 SIMPLE           G ref         PRIMARY PRIMARY


Pfff... Píšu to znovu, protože než jsem stihl příspěvek vytvořit tak jsem byl odhlášen
« Poslední změna: 21. 02. 2020, 15:38:35 od Petr Krčmář »


Re:MYSQL rychlost filtrování
« Odpověď #1 kdy: 21. 02. 2020, 15:09:23 »
Ano, to řešení je nevhodné. Nicméně v některých případech to může být nejlepší řešení.

Nenapsal jste nic o tom, jak vypadají ty dotazy a indexy. Klíčová je tahle věta:

Indexy jsou nastavené podle nejhledanějších parametrů.
To může znamenat, že se v jednom indexu najde jeden záznam, který se pak použije pro dohledání tisíce záznamů v hlavní tabulce. Nebo to může znamenat, že se v indexu najde tisíc záznamů, a ty se pak dohledají v hlavní tabulce. A také to může znamenat, že se ve třech indexech najde v každém deset tisíc záznamů, následně databáze musí udělat jejich průnik a tím získá výslednou tisícovku záznamů. Ve všech třech případech je výsledkem 1000 záznamů, ale je diametrálně odlišný způsob, jak k nim databáze dojde. Obecně je potřeba, aby vám výslednou sedu co nejvíc omezil jeden index. Jakmile dostanete z několika indexů spousty záznamů a omezí to teprve jejich průnik, je potřeba hledat jinou reprezentaci dat, tak, abyste ta data vytvářející ten průnik dostal k sobě.

Re:MySQL rychlost filtrování
« Odpověď #2 kdy: 21. 02. 2020, 17:35:47 »
Děkuji za odpověď.


Jde o široký sortiment produktů. Každá skupina produktů má svoje vlastnosti, podle kterých je následovně filtrováno.
Abych nemusel použít extra tabulku pro každou skupinu produktů mám jednu tabulku "product", která obsahuje parametry společné pro všechny produkty (výška, šířka, barva atd.).
Pokud skupina produktů vyžaduje specifické parametry jsou zaneseny do EAV tabulky. Zpracování dotazu nabere zpoždění v momentě, kdy potřebuju všechny EAV parametry spojit.



Kód: [Vybrat]
product_feature_group
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name_cz | varchar(45)      | YES  |     | NULL    |                |
| name_en | varchar(45)      | YES  |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+

Kód: [Vybrat]
product_feature_group_item
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| id               | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name_cz          | varchar(45)      | YES  |     | NULL    |                |
| name_en          | varchar(45)      | YES  |     | NULL    |                |
| feature_group_id | int(10) unsigned | NO |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+


Kód: [Vybrat]
product_feature_group_item_value;
+-----------------------+------------------+------+-----+---------+----------------+
| Field                 | Type             | Null | Key | Default | Extra          |
+-----------------------+------------------+------+-----+---------+----------------+
| id                    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| feature_group_id      | int(10) unsigned | NO |     | NULL    |                |
| value_cz              | varchar(100)     | YES  |     | NULL    |                |
| value_en              | varchar(100)     | YES  |     | NULL    |                |
| feature_group_item_id | int(10) unsigned | NO |     | NULL    |                |
+-----------------------+------------------+------+-----+---------+----------------+


Kód: [Vybrat]
product_has_feature
+--------------------------+------------------+------+-----+---------+----------------+
| Field                    | Type             | Null | Key | Default | Extra          |
+--------------------------+------------------+------+-----+---------+----------------+
| id                       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| product_id               | int(10) unsigned | NO   | MUL | NULL    |                |
| feature_group_id         | int(10) unsigned | NO   |     | NULL    |                |
| feature_group_item_id    | int(10) unsigned | NO   |     | NULL    |                |
| feature_group_item_value_id| int(10) unsigned | NO   |     | NULL    |                |
+--------------------------+------------------+------+-----+---------+----------------+



 
product_features_group (Zvuk) -> product_feature_group_item (Výkon, Počet pásem, Impedance) -> product_filter_group_item_value (V12,V15,V20,PP2,PP3,I4,I8)

Skupina parametrů                = product_feature_group
Podmnožina parametrů             = product_feature_group_item
Hodnoty parametrů                = product_feature_group_item_value
Asociování parametrů k produktům = product_has_feature



Kód: [Vybrat]
show indexes from product_has_feature;
+---------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name                       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product_has_feature |          0 | PRIMARY                        |            1 | id          | A         |      812822 |     NULL | NULL   |      | BTREE      |         |               |
| product_has_feature |          1 | product_has_feature_product_id |            1 | product_id  | A         |       22578 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+--------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.001 sec)

Kód: [Vybrat]
show indexes from product_feature_group

+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| product_feature_group |          0 | PRIMARY  |            1 | id          | A         |          13 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


Všechny tabulky pak spojuji následujícím postupem:

Kód: [Vybrat]
SELECT product.id,product_feature_group.name_cz from product
join product_has_feature on product_has_feature.product_id = product.id
join product_feature_group on product_feature_group.id = product_has_feature.feature_group_id

zabere 3,3 vteřiny/klient

Uvědomuju si že EAV není v souladu s SQL, ale potřebuji vyhovět požadavku použití SQL.

Re:MySQL rychlost filtrování
« Odpověď #3 kdy: 21. 02. 2020, 18:12:59 »
Zpracování dotazu nabere zpoždění v momentě, kdy potřebuju všechny EAV parametry spojit.

Uvědomuju si že EAV není v souladu s SQL, ale potřebuji vyhovět požadavku použití SQL.
Teď už i víte, proč je EAV špatně.

Pokud máte zadání použít špatně špatný nástroj, nemůžete se divit, že je pak i výsledek špatný…


Všechny tabulky pak spojuji následujícím postupem:

Kód: [Vybrat]
SELECT product.id,product_feature_group.name_cz from product
join product_has_feature on product_has_feature.product_id = product.id
join product_feature_group on product_feature_group.id = product_has_feature.feature_group_id
Moc nechápu, k čemu je takový dotaz dobrý. Proč potřebujete vypsat opakovaně ID produktu a k němu vždy název skupiny vlastností, která k němu patří? Pokud tohle není dotaz, který reálně používáte, napište ten konkrétní dotaz – včetně podmínek. Teprve pak se dá usuzovat, které indexy se mohou použít a jestli se ten dotaz případně nedá přeformulovat.