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.

SELECT d.*, COUNT(ds.download_request_download_id) AS statcnt 
             FROM download AS d

        LEFT JOIN download_category AS dcs ON dcs.download_category_id = d.download_category

        LEFT JOIN download_category AS dc ON dc.download_category_id = dcs.download_category_parent

        LEFT JOIN download_requests AS ds ON d.download_id = ds.download_request_download_id 
                                         AND ds.download_request_datestamp >= 1214863200

            WHERE dc.download_category_class IN (252,251,0)

              AND dcs.download_category_class IN (252,251,0)

              AND d.download_visible IN (252,251,0)

         GROUP BY d.download_id

         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:

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:

SELECT d.*, COUNT(ds.download_request_download_id) AS statcnt
  FROM download AS d
  LEFT JOIN download_requests AS ds 
    ON d.download_id = ds.download_request_download_id AND ds.download_request_datestamp >= 1214863200
  WHERE d.download_visible IN (252,251,0)
  GROUP BY d.download_id
  ORDER BY statcnt DESC
  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ő
CREATE TABLE `download` (
  `download_id` int(10) unsigned NOT NULL auto_increment,
  `download_name` varchar(100) collate utf8_hungarian_ci NOT NULL,
  `download_url` varchar(150) collate utf8_hungarian_ci NOT NULL,
  `download_author` varchar(100) collate utf8_hungarian_ci NOT NULL,
  `download_author_email` varchar(200) collate utf8_hungarian_ci NOT NULL,
  `download_author_website` varchar(200) collate utf8_hungarian_ci NOT NULL,
  `download_description` text collate utf8_hungarian_ci NOT NULL,
  `download_filesize` varchar(20) collate utf8_hungarian_ci NOT NULL,
  `download_requested` int(10) unsigned NOT NULL default '0',
  `download_category` int(10) unsigned NOT NULL default '0',
  `download_active` tinyint(3) unsigned NOT NULL default '0',
  `download_datestamp` int(10) unsigned NOT NULL default '0',
  `download_thumb` varchar(150) collate utf8_hungarian_ci NOT NULL,
  `download_image` varchar(150) collate utf8_hungarian_ci NOT NULL,
  `download_comment` tinyint(3) unsigned NOT NULL default '0',
  `download_class` varchar(255) collate utf8_hungarian_ci NOT NULL default '0',
  `download_mirror` text collate utf8_hungarian_ci NOT NULL,
  `download_mirror_type` tinyint(1) unsigned NOT NULL default '0',
  `download_visible` varchar(255) collate utf8_hungarian_ci NOT NULL default '0',
  PRIMARY KEY  (`download_id`),
  UNIQUE KEY `download_name` (`download_name`),
  KEY `download_category` (`download_category`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=813 ;

CREATE TABLE `download_category` (
  `download_category_id` int(10) unsigned NOT NULL auto_increment,
  `download_category_name` varchar(100) collate utf8_hungarian_ci NOT NULL,
  `download_category_description` text collate utf8_hungarian_ci NOT NULL,
  `download_category_icon` varchar(100) collate utf8_hungarian_ci NOT NULL,
  `download_category_parent` int(10) unsigned NOT NULL default '0',
  `download_category_class` varchar(255) collate utf8_hungarian_ci NOT NULL default '0',
  `download_category_order` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`download_category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci AUTO_INCREMENT=38 ;


CREATE TABLE `download_requests` (
  `download_request_id` int(10) unsigned NOT NULL auto_increment,
  `download_request_userid` int(10) unsigned NOT NULL default '0',
  `download_request_ip` varchar(30) collate utf8_hungarian_ci NOT NULL,
  `download_request_download_id` int(10) unsigned NOT NULL default '0',
  `download_request_datestamp` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`download_request_id`),
  KEY `download_request_userid` (`download_request_userid`),
  KEY `download_request_download_id` (`download_request_download_id`)
) 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.