ugrás a tartalomhoz

Lekérdezés optimalizálás

minczerl · 2008. Júl. 15. (K), 09.45
Sziasztok!

Besegítek egy oldal fejlesztésébe/adminisztrálásába, és tegnap szólt a tulajdonos hogy a szolgáltató leállította az oldalt, mert túlságosan leterheli az adatbázisszerverüket. A lassan lefutott lekérdezések naplóját elküldték. A hibát egy olyan lekérdezés okozza a napló szerint , ami az oldalon található 10 legtöbbet letöltött letöltést adja vissza. Amihez 3 táblát használ

download: itt vannak a letöltések 789 rekord
download_category: letöltések kategórizálása 32 rekord
download_request: a felhasználó által inditott letöltéseket naplózza, ki, mikor, melyik fájlt töltötte le, ez a tábla már 100.000 feletti rekordszámmal bír.

Kérdésem az hogy az alábbi lekérdezést miként lehetne gyorsítani/optimalizálni hogy ne feküdjön ki a szerver tőle. Utolsó megoldásként megcsinálom úgy hogy ne kelljen belenyúlnia a request táblába, de hátha valakinek van jobb ötlete.
  1. SELECT d.*, COUNT(ds.download_request_download_id) AS statcnt   
  2.              FROM download AS d  
  3.   
  4.         LEFT JOIN download_category AS dcs ON dcs.download_category_id = d.download_category  
  5.   
  6.         LEFT JOIN download_category AS dc ON dc.download_category_id = dcs.download_category_parent  
  7.   
  8.         LEFT JOIN download_requests AS ds ON d.download_id = ds.download_request_download_id   
  9.                                          AND ds.download_request_datestamp >= 1214863200  
  10.   
  11.             WHERE dc.download_category_class IN (252,251,0)  
  12.   
  13.               AND dcs.download_category_class IN (252,251,0)  
  14.   
  15.               AND d.download_visible IN (252,251,0)  
  16.   
  17.          GROUP BY d.download_id  
  18.   
  19.          ORDER BY statcnt DESC LIMIT 0, 10;  
Köszönöm
‹ SQLite korlátozott hozzáférés létrehozása Tárolt eljárás Tranzakció kezelés ›
 ■
1

Gyorstárazz!

Nagy Gusztáv · 2008. Júl. 15. (K), 10.30
Szerintem nem sokat veszít az értékéből, ha nem minden alkalommal, hanem csak időnként futtatod le, pl. cron-nal. (Óránként, vagy naponta?)

Ekkora rekordszámnál nem fog túl gyorsan változni.
2

Schema

tolmi · 2008. Júl. 15. (K), 10.46
Beküldhetnéd a táblák schema-ját is, hogy lássunk van-e megfelelően kiszotott index és egyéb finomságok. Addig sokat nem lehet hozzáfűzni.

Extrém nagy esélyt látok arra, hogy valamilyen index hibádzik a dologban.
Ha MySQL-t használsz (ezt se írtad, bár a LIMIT-ből feltételezem hogy igen), akkor érdemes megvizsgálni hogy használ-e index-eket megfelelően a lekérdezés:
  1. explain ....sql lekerdezesem...;  
Hasznos olvasmány lehet a MySQL Query Optimizing oldala, valamint továbblépésnek egy kis leírás az explain extended-ről.
3

LEFT JOIN vs INNER JOIN

Poetro · 2008. Júl. 15. (K), 11.20
Nem lehet lecserélni a LEFT JOIN-okat INNER JOIN-ra cserélni, mert az sokat dobna a teljesítményen? Akár csak egy részét a LEFT JOIN-oknak
6

Kategóriák kellenek?

Poetro · 2008. Júl. 15. (K), 11.35
Mivel szerintem a kategóriák behívása a lekérdezésbe semmilyen pozitív hatással nincs a lekérdezés eredményére én kidobnám őket, ezáltal valami ilyen formát venne fel az eredmény:
  1. SELECT d.*, COUNT(ds.download_request_download_id) AS statcnt  
  2.   FROM download AS d  
  3.   LEFT JOIN download_requests AS ds   
  4.     ON d.download_id = ds.download_request_download_id AND ds.download_request_datestamp >= 1214863200  
  5.   WHERE d.download_visible IN (252,251,0)  
  6.   GROUP BY d.download_id  
  7.   ORDER BY statcnt DESC  
  8.   LIMIT 0, 10;  
Ez több okból lesz gyorsabb, de a legfontosabb, nem fogy el a memória, mivel nem sokszorozódik meg a GROUP BY előtti eredményhalmaz, ezért minden valószínűség szerint kiesik a filesort.
7

Pont fordítva tudom

tolmi · 2008. Júl. 15. (K), 11.53
Szerintem a LEFT JOIN gyorsabb, mint az INNER JOIN. Más is így tudja: Inner join vs. left join.

Szóval nem hiszem hogy megérné átírni (ha egyáltalán át lehet) INNER JOIN-ra.
10

Lassulás

Poetro · 2008. Júl. 15. (K), 12.58
A lassulás legfőbb oka a 
Using temporary; Using filesort
Vagyis ha ezt el tudjuk kerülni maga a lekérdezés lesz sokkal gyorsabb. Az INNER JOIN miatt kevesebb sor lesz kiválasztva, ezzel csökkenthető az esélye hogy a fentiek előforduljanak.
11

Okok

tolmi · 2008. Júl. 15. (K), 14.29
Az okkal egyetértek, az inner vs. left témára nem tudok reagálni, azt sem értem hogy te mire alapozod. Nem ismerjük a rekordok természetét. Szerintem a temp table, filesort azért van, mert ORDER-t használ, nem pedig a JOIN miatt.
12

Filesort

Poetro · 2008. Júl. 15. (K), 15.14
Az ORDER BY-nak nem egyenes következménye a filesort, és a temporary sem, hanem az hogy az eredménytábla a rendezéshez nem fér el memóriában. Ha kisebb az eredménytábla, kevesebb memória kell, mehet a rendezés memóriában.
mysql> EXPLAIN SELECT * FROM advpoll WHERE active = 1 ORDER BY nid ASC ;
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | advpoll | index | NULL          | PRIMARY | 4       | NULL |    4 | Using where |
+----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.03 sec)
13

Ez így van

tolmi · 2008. Júl. 15. (K), 21.53
Valóban nem egyenes következménye, de elég ritka valós helyzetben hogy memóriában képes rendezni ;)
4

számlálók használata

virág · 2008. Júl. 15. (K), 11.26
Ez tipikusan az a feladat amihez érdemes számlálókat csinálni és nem lekérdezgetni minden lefutásnál, gyorstárazni is lehet, ahogyan ajánlották feljebb.

Képzeld el, hogy nagy mennyiségű adat esetén ez a lekérdezés mekkora terhelést okozhat! Ennek ellenére az indexeket érdemes átnézegetni, mert ilyen kevés adatmennyiségnél ennek a lekérdezésnek nem kellene gondot okoznia. A szolgáltatót pedig cseréljétek le ha szó nélkül állította le az oldalt, mert az nem szép dolog.
5

Számláló hozzáadása

minczerl · 2008. Júl. 15. (K), 11.31
Igen ha ezt már nem lehet sehogysem javítani, akkor a download táblához hozzáadok még egy mezőt amiben a letöltések számát tárolom + gyorstárazás. Indexeket átnézem, mert nem én alakítottam ki az adatbázis szerkezetet és lehet még egy-két dolgok lehet indexelni, a meglévő indexeken kivül. Ez lehet kicsit hosszú lesz, de az adatbázis séma a következő
  1. CREATE TABLE `download` (  
  2.   `download_id` int(10) unsigned NOT NULL auto_increment,  
  3.   `download_name` varchar(100) collate utf8_hungarian_ci NOT NULL,  
  4.   `download_url` varchar(150) collate utf8_hungarian_ci NOT NULL,  
  5.   `download_author` varchar(100) collate utf8_hungarian_ci NOT NULL,  
  6.   `download_author_email` varchar(200) collate utf8_hungarian_ci NOT NULL,  
  7.   `download_author_website` varchar(200) collate utf8_hungarian_ci NOT NULL,  
  8.   `download_description` text collate utf8_hungarian_ci NOT NULL,  
  9.   `download_filesize` varchar(20) collate utf8_hungarian_ci NOT NULL,  
  10.   `download_requested` int(10) unsigned NOT NULL default '0',  
  11.   `download_category` int(10) unsigned NOT NULL default '0',  
  12.   `download_active` tinyint(3) unsigned NOT NULL default '0',  
  13.   `download_datestamp` int(10) unsigned NOT NULL default '0',  
  14.   `download_thumb` varchar(150) collate utf8_hungarian_ci NOT NULL,  
  15.   `download_image` varchar(150) collate utf8_hungarian_ci NOT NULL,  
  16.   `download_comment` tinyint(3) unsigned NOT NULL default '0',  
  17.   `download_class` varchar(255) collate utf8_hungarian_ci NOT NULL default '0',  
  18.   `download_mirror` text collate utf8_hungarian_ci NOT NULL,  
  19.   `download_mirror_type` tinyint(1) unsigned NOT NULL default '0',  
  20.   `download_visible` varchar(255) collate utf8_hungarian_ci NOT NULL default '0',  
  21.   PRIMARY KEY  (`download_id`),  
  22.   UNIQUE KEY `download_name` (`download_name`),  
  23.   KEY `download_category` (`download_category`)  
  24. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=813 ;  
  25.   
  26. CREATE TABLE `download_category` (  
  27.   `download_category_id` int(10) unsigned NOT NULL auto_increment,  
  28.   `download_category_name` varchar(100) collate utf8_hungarian_ci NOT NULL,  
  29.   `download_category_description` text collate utf8_hungarian_ci NOT NULL,  
  30.   `download_category_icon` varchar(100) collate utf8_hungarian_ci NOT NULL,  
  31.   `download_category_parent` int(10) unsigned NOT NULL default '0',  
  32.   `download_category_class` varchar(255) collate utf8_hungarian_ci NOT NULL default '0',  
  33.   `download_category_order` int(10) unsigned NOT NULL default '0',  
  34.   PRIMARY KEY  (`download_category_id`)  
  35. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=38 ;  
  36.   
  37.   
  38. CREATE TABLE `download_requests` (  
  39.   `download_request_id` int(10) unsigned NOT NULL auto_increment,  
  40.   `download_request_userid` int(10) unsigned NOT NULL default '0',  
  41.   `download_request_ip` varchar(30) collate utf8_hungarian_ci NOT NULL,  
  42.   `download_request_download_id` int(10) unsigned NOT NULL default '0',  
  43.   `download_request_datestamp` int(10) unsigned NOT NULL default '0',  
  44.   PRIMARY KEY  (`download_request_id`),  
  45.   KEY `download_request_userid` (`download_request_userid`),  
  46.   KEY `download_request_download_id` (`download_request_download_id`)  
  47. ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=100786 ;  
Kiraktam PDF-be az Explain kimenetét is
Explain PDF
9

Memcache

janoszen · 2008. Júl. 15. (K), 12.49
Ha van memcache a szerveren, próbáld meg kihasználni, jótékony hatással lesz a működésre. Ha ügyes vagy, megsokszorozhatod a kiszolgálási sebességed.

Ja igen, és ahogy megmondták már: az oldal letöltésének 90%-a nem a HTML kód, hanem a járulékos dolgok. Azokra is illik gyúrni (gzip, cache headerek, stb).
8

Köszönöm

minczerl · 2008. Júl. 15. (K), 11.56
Köszi szépen a tanácsokat.