Hledání optimálního dotazu pro filtraci a řazení produktů s atributy

Následující příspěvek se zabývá rychlostí databáze MySQL a hledáním optimálního SQL dotazu pro zobrazení doplňkových atributů u produktů.

Máme dvě tabulky, jednu s produkty, který obsahuje základní atributy a druhou s dodatečnými atributy, jejichž typ je definován sloupcem type. Tabulka atributy obsahuje cizí klíč produkt do tabulky produkty a položky s dvojicí klíčů (produkt, type) nemusí být unikátní. Znamená to, že můžeme mít více atributů stejného typu u jednoho produktu. Tabulky jsou v MySQL definovány následovně:

CREATE TABLE IF NOT EXISTS `produkty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_czech_ci NOT NULL,
  `price` decimal(8,2) NOT NULL,
  `active` int(11) NOT NULL,
  `text` text COLLATE utf8_czech_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `atributy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  `value_number` int(11) DEFAULT NULL,
  `value_text` text COLLATE utf8_czech_ci,
  `value_datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product` (`product`)
) ENGINE=InnoDB;

Tabulka pro produkty obsahuje 10 000 záznamů a pro atributy 100 000. Problémem, kterým jsem se zabýval, je vypsání produktů spolu s jejich atributy, přičemž produkty mají být omezeny podle hodnot atributů a seřazeny podle jednoho z atributů.

Konkrétně chceme například vypsat prvních 10 produktů, řazených podle datumu, starších jak červen, aktivní, cena větší než 100 a s textem obsahující řetězec 'sd'.

Dotaz pomocí spojování tabulek - JOIN

SELECT 
	main . * , 
	attr1.value_number AS p_price, 
	attr2.value_text AS p_text, 
	attr3.value_datetime AS p_datetime
FROM `produkty` AS main
LEFT JOIN atributy AS attr1 ON main.id = attr1.produkt
	AND attr1.type =2
LEFT JOIN atributy AS attr2 ON main.id = attr2.produkt
	AND attr2.type =3
LEFT JOIN atributy AS attr3 ON main.id = attr3.produkt
	AND attr3.type =4
WHERE 
	attr3.value_datetime < '2009-06-01 00:00:00'
	AND main.active =1
	AND attr1.value_number >100
	AND attr2.value_text LIKE '%sd%'
GROUP BY main.id
ORDER BY attr3.value_datetime
LIMIT 10

Dotaz trval 4s a mě zajímalo, která část dotazu je nejpomalejší. Zkusil jsem odebraz příkaz ORDER, dotaz trval pouze 2,5s, což je samozřejmě stále špatné. Další změnou bylo úplné vyhození omezujících podmíne, tedy celý příkaz WHERE.

Pokud jsem použil řazení, trval opět 4s, ovšem bez použití podmínek i řazení se rychlost změnila o 2 řády, na použitelných 0,04s. Důvod je zřejmý, při použití řazení musí databáze vypočítat všechny řádky a potom teprve seřadit, zatímco bez použití ORDER příkazu stačí připravit několik řádků, aby byl splněn LIMIT, a tím to hasne.

Bohužel se nemůžeme smířit s tím, že na stránkách budeme zobrazovat produkty náhodně seřazené a nenabídneme uživateli jejich filtrování. Pojďme tedy zkusit jiný typ dotazu, který nám vrátí stejná data.

Dotaz pomocí vloženého dotazu

SELECT 
	main.* 
FROM produkty AS main WHERE
(
	SELECT count(*) 
	FROM atributy AS attr1 
	WHERE attr1.product = main.id 
	AND attr1.type = 2 
	AND attr1.value_number > 700
) AND (
	SELECT count(*) 
	FROM atributy AS attr2 
	WHERE attr2.product = main.id 
	AND attr2.type = 3 
	AND attr2.value_text like '%sd%'
) AND (
	SELECT count(*) 
	FROM atributy AS attr3 
	WHERE attr3.product = main.id 
	AND attr3.type = 4 
	AND attr3.value_datetime > '2009-08-07 23:11:22'
)

Výsledek? Rychlost načítání je srovnatelná s předešlým, ale nelze použít řazení. Atributy se musejí načítat dodatečně. Další možnost, jak načítat podobná data je tu:

Dotaz pomocí obyčejného spojení dvou tabulek

SELECT main . *
FROM produkty AS main, atributy AS a
WHERE main.id = a.product 
AND (
	(a.type =2 AND a.value_number >500)
	OR 
	(a.type =3 AND a.value_text LIKE '%sd%')
	OR 
	(a.type =4 AND a.value_datetime > '2009-06-01 00:00:00')
)
GROUP BY main.id

Rychlost opět srovnatelná, při použití ORDER BY příkazu pomalejší o dva až tři řády (v rozsahu uvedeném na začátku článku řádově minuty).

Závěr:

Ať se snažím, jak se snažím, vymyslet dotaz, který by dokázal řadit i filtrovat produkty s atributy takto rozsáhlých tabulek, se mi nedaří. Na druhou stranu, jak rád říkám, "život je kompromis". Jde tedy o to zvolit ten správný kompromis, na výběr máme následující:

• využít JOIN tabulek a omezit filtrování produktů na jeden, maximálně dva atributy, s rostoucím počtem filtrovaných atributů je dotaz složitější
• nepoužívat řazení podle atributů, ale pouze podle sloupců tabulky produkty
• atributy, podle kterých se nefiltruje, nenačítat pomocí spojování tabulek (nebo obecně při samotném výběru produktů), ale načíst je dodatečně

K poslední možnosti ještě poznámka: Často omezujeme výběr pomocí příkazu LIMIT a tím pádem je daleko výhodnější načíst data u všech takto vybraných produktů najednou (maximálně je jich zpravidla desítky) a přiřadit je k produktům až následně v aplikaci. Načtení atributů samotných je celkem rychlé například takto:

SELECT * FROM atributy WHERE produkt IN (...)

Pokud máte někdo nápady, jak sestavit zmíněné dotazy efektivněji, určitě se o ně podělte v diskuzi.

related posts

Testování rychlosti dotazů v MySQL 5.1 (26.11.2009)Běžná práce se systemd (26.10.2011)Démonizace po startu a zrychleni bootování (25.10.2011)Cheet sheets - GDB příkazy, Vim, Latex ( 8.03.2011)Služby OpenID a MojeID - jak to vlastně zjednodušeně funguje (27.10.2010)