Autor | Zpráva | ||
---|---|---|---|
Náprstek Aleš Profil * |
#1 · Zasláno: 22. 1. 2015, 08:24:10
Dobrý den,
můžete mi prosím poradit, zda existuje nějaká možnost, jak zrychlit níže uvedený SELECT - používáme databázi MySQL 5.1. Select se zpomaluje s tím, jak přibývají počty řádků v dotyčné tabulce. Jedná se o select, který vypisuje vždy určitý počet položek z vybrané katagorie a vybraného pořadí. Produkt může být v více kategorií a proto se používá vazební tabulka. Nyní je počet řádků v tabulkách asi následující t_e_kategorie 1345 řádků - tabulka kategorií produktů v eshopu t_e_produkty 152 000 řádků - tabulka produktů v eshopu t_e_vazba_kat_prod 167 000 řádků - vazební tabulka pro zařazení produktů do jednotlivých kategorií Select uvádím níže (včetně explainu), poté je záznam z mysql slow logu a níže uvádím i strukturu tabulek. Select běží v součastnosti většinou kolem času 1,5 s. Zkoušel jsem všechno možné a zatím se mi nepodařila select jakýmkoliv způsobem zoptimalizovat, aby byl rychlejší. EXPLAIN SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 300, 15; +----+--------------------+---------------+-----------------+-------------------------------+---------+---------+----------------------+--------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+---------------+-----------------+-------------------------------+---------+---------+----------------------+--------+-----------------------------------------------------------+ | 1 | PRIMARY | kp | index | vazba | vazba | 10 | NULL | 172605 | Using where; Using index; Using temporary; Using filesort | | 1 | PRIMARY | p | eq_ref | PRIMARY,zobrazovat,idx_platny | PRIMARY | 4 | stosek.kp.id_produkt | 1 | Using where | | 2 | DEPENDENT SUBQUERY | t_e_kategorie | unique_subquery | PRIMARY,idx_lft,idx_rft | PRIMARY | 2 | func | 1 | Using where | +----+--------------------+---------------+-----------------+-------------------------------+---------+---------+----------------------+--------+-----------------------------------------------------------+ 3 rows in set (0.00 sec) # Time: 150121 14:08:29 # Query_time: 1.726613 Lock_time: 0.000132 Rows_sent: 15 Rows_examined: 230015 SET timestamp=1421845709; SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 300, 15; # Time: 150121 14:08:33 # Query_time: 1.430588 Lock_time: 0.000142 Rows_sent: 15 Rows_examined: 181380 SET timestamp=1421845713; SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 1785 AND rft <= 1786) ORDER BY p.d_zobrazovat_od DESC LIMIT 180, 15; # Time: 150121 14:08:42 # Query_time: 1.614389 Lock_time: 0.000163 Rows_sent: 15 Rows_examined: 230047 SET timestamp=1421845722; SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 315, 15; # Time: 150121 14:08:55 # Query_time: 1.570270 Lock_time: 0.000154 Rows_sent: 15 Rows_examined: 230079 SET timestamp=1421845735; SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp, t_e_produkty AS p WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 330, 15; CREATE TABLE IF NOT EXISTS `t_e_kategorie` ( `id_e_kategorie` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `nazev_cz` varchar(80) CHARACTER SET utf8 DEFAULT NULL, `popis_cz` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `nazev_en` varchar(80) CHARACTER SET utf8 DEFAULT NULL, `popis_en` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `nazev_de` varchar(80) CHARACTER SET utf8 DEFAULT NULL, `popis_de` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `id_e_kat_rodic` smallint(5) unsigned NOT NULL DEFAULT '0', `poradi` tinyint(3) unsigned NOT NULL DEFAULT '0', `lft` smallint(5) unsigned NOT NULL DEFAULT '0', `rft` smallint(5) unsigned NOT NULL DEFAULT '0', `platny` enum('A','N') CHARACTER SET utf8 NOT NULL DEFAULT 'A', PRIMARY KEY (`id_e_kategorie`), KEY `idx_lft` (`lft`), KEY `idx_rft` (`rft`), KEY `idx_id_e_kat_rodic` (`id_e_kat_rodic`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; CREATE TABLE IF NOT EXISTS `t_e_produkty` ( `id_produkt` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_kvalita` tinyint(3) unsigned DEFAULT NULL, `d_vlozeni` datetime DEFAULT NULL, `d_zobrazovat_od` datetime DEFAULT NULL, `provize` tinyint(3) unsigned DEFAULT NULL, `katalogove_cislo` varchar(60) DEFAULT NULL, `nazev_cz` varchar(255) DEFAULT NULL, `nazev_en` varchar(255) DEFAULT NULL, `nazev_de` varchar(255) DEFAULT NULL, `popis_cz` text, `popis_en` text, `popis_de` text, `cena` int(10) DEFAULT NULL, `doporucujeme` enum('A','N') DEFAULT 'N', `d_doporucujeme` date DEFAULT NULL, `neprehlednete` enum('A','N') DEFAULT 'N', `pocet_zobrazeni` int(11) NOT NULL DEFAULT '0', `zobrazovat` enum('A','N') NOT NULL DEFAULT 'A', `platny` enum('A','N') DEFAULT 'A', `vraceno` enum('A','N') DEFAULT 'N', `dt_vraceno` date DEFAULT NULL, PRIMARY KEY (`id_produkt`), KEY `idx_katalog` (`katalogove_cislo`), KEY `idx_cena` (`cena`), KEY `idx_doporucujeme` (`doporucujeme`), KEY `idx_nazev` (`nazev_cz`), KEY `zobrazovat` (`zobrazovat`), KEY `idx_neprehlednete` (`neprehlednete`), KEY `idx_platny` (`platny`), KEY `idx_d_zobrazovat_od` (`d_zobrazovat_od`), FULLTEXT KEY `idx_flt_nazev` (`nazev_cz`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `t_e_vazba_katprod` ( `id_produkt` int(11) DEFAULT NULL, `id_e_kategorie` int(11) DEFAULT NULL, UNIQUE KEY `vazba` (`id_produkt`,`id_e_kategorie`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Předem děkuji za jakoukoliv radu. Aleš Náprstek |
||
Keeehi Profil |
#2 · Zasláno: 22. 1. 2015, 08:53:32
Tusím že join by mohl pomoct:
SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND id_e_kategorie IN(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 803 AND rft <= 814) ORDER BY p.d_zobrazovat_od DESC LIMIT 300, 15; Ta čárka, co jste tam měl předím, vytvoří kartézský součin kde se párují záznamy každý s každým což vytvoří tabulku o 25 miliardách řádků která se pak musí profiltrovat. Ještě by se dalo zbavit toho vnořeného subselektu: SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) INNER JOIN t_e_kategorie AS k USING(id_e_kategorie) WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND k.lft >= 803 AND k.rft <= 814 ORDER BY p.d_zobrazovat_od DESC LIMIT 300, 15; |
||
Kajman Profil |
A pokud se produkt má vypsat jen jednou (i když je ve více kategoriích), zkusil bych něco jako
SELECT p.id_produkt FROM t_e_produkty AS p INNER JOIN (SELECT DISTINCT kp.id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_kategorie AS k ON kp.id_e_kategorie = k.id_e_kategorie WHERE k.lft >= 803 AND k.rft <= 814) t ON p.id_produkt = t.id_produkt WHERE p.zobrazovat = 'A' AND p.platny = 'A' ORDER BY p.d_zobrazovat_od DESC LIMIT 300, 15; Pokud v p.zobrazovat a v p.platny jsou skoro vždy 'A', občas se v pomůže v mysql takovou podmínku dát do having místo do where, protože se lépe použije index pro řazení. Ale v tomto případě (závislost na dalších tabulkách) to asi nepomůže. Edit: a v tabulce t_e_vazba_katprod by se hodilo přidat i obrácený index, tedy (id_e_kategorie,id_produkt) |
||
Náprstek Aleš Profil * |
#4 · Zasláno: 22. 1. 2015, 10:28:01
Děkuji moc za tip - zkusil jsem první variantu a ta přináší zatím úsporu v řádu desetin vteřin.
Chvíli nechám varinatu první a následně zkusím ještě druhou variantu s vynechám subselectem. # Time: 150122 10:25:05 # Query_time: 1.847019 Lock_time: 0.000157 Rows_sent: 8 Rows_examined: 238735 SET timestamp=1421918705; SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 415 AND rft <= 416) ORDER BY p.d_zobrazovat_od DESC LIMIT 30, 30; # Time: 150122 10:25:20 # Query_time: 1.312420 Lock_time: 0.000130 Rows_sent: 30 Rows_examined: 191522 SET timestamp=1421918720; SELECT kp.id_produkt AS id_produkt FROM t_e_vazba_katprod AS kp INNER JOIN t_e_produkty AS p USING(id_produkt) WHERE p.zobrazovat = 'A' AND p.platny = 'A' AND kp.id_produkt = p.id_produkt AND id_e_kategorie = ANY(SELECT id_e_kategorie FROM t_e_kategorie WHERE lft >= 415 AND rft <= 416) ORDER BY p.d_zobrazovat_od DESC LIMIT 0, 30; |
||
Náprstek Aleš Profil * |
#5 · Zasláno: 22. 1. 2015, 10:33:47
Kajman:
Moc děkuji za optimalizaci selectu - podle měření na mém testovacím prostředí, to vypadá jako správná cesta. Za chvíli to vyzkouším nasadit na produkční server a dám vědět. Stejně tak děkuji i za tip na obrácený index. |
||
Náprstek Aleš Profil * |
#6 · Zasláno: 22. 1. 2015, 14:07:35
Kajman&Keeehi:
Moc vám oběma děkuji, ten select #3 od Kajmana je nejrychlejší. Problém považuji za vyřešený, teď můžu pokračovat v řešení dalších pomalých sql příkazech. Že já jsem nepoprosil o pomoc dříve - týden zkouším všechno možné i nemožné. |
||
Časová prodleva: 10 let
|
0