Mysql lekérdezés optimalizálása
Sziasztok!
Egy viszonylag egyszerű ügyben kérném a véleményeteket/segítségeteket:
Adott egy adatbázis, egyszerűség kedvéért 2 tábla:
A: id, b_id
B: id, name
id természetesen primary key, b_id pedig a b.id-vel van kapcsolatban.
Adott a query:Tehát a B elemeiből hány szerepel A-ban és eszerint egy ranglista. Ez szépen működött is, amíg meg nem hízott az adatbázis. Jelenleg A-ban 220.000, B-ben 8000 rekord van. A lekérdezés átlag 4-7 mp alatt fut le.
Hogyan lehetne gyorsítani?
- myisam/innodb? Jelenleg myisam, próbáltam innodb-vel is, még lassabbnak tűnt.
- Van-e jelentősége, hogy B_id foreign key, vagy csak simán key?
- vagy pedig jöhet a denormalizálás, és vegyek fel B-re egy számláló mezőt, amit triggerrel/php-val updatelek?
A táblaszerkezetek:Egyébként a helyzet még bonyolódik egy C táblával is, ahol B-ben van egy C_id oszlop hasonló kapcsolattal, és ezekből kell különböző statisztikákat lekérdezni, de már a 2táblás verziónál gondok vannak.
Köszönöm a segítséget!
■ Egy viszonylag egyszerű ügyben kérném a véleményeteket/segítségeteket:
Adott egy adatbázis, egyszerűség kedvéért 2 tábla:
A: id, b_id
B: id, name
id természetesen primary key, b_id pedig a b.id-vel van kapcsolatban.
Adott a query:
SELECT COUNT(A.id) db, B.name
FROM A
INNER JOIN B ON A.B_id=B.id
GROUP BY B.id
ORDER BY db DESC
LIMIT 0, 10;
Hogyan lehetne gyorsítani?
- myisam/innodb? Jelenleg myisam, próbáltam innodb-vel is, még lassabbnak tűnt.
- Van-e jelentősége, hogy B_id foreign key, vagy csak simán key?
- vagy pedig jöhet a denormalizálás, és vegyek fel B-re egy számláló mezőt, amit triggerrel/php-val updatelek?
A táblaszerkezetek:
CREATE TABLE A (
id bigint(11) unsigned NOT NULL AUTO_INCREMENT,
B_id int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
KEY k_B_id (B_id),
) ENGINE=MyISAM
CREATE TABLE B (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name varchar(120) CHARACTER SET utf8 COLLATE utf8_hungarian_ci NOT NULL,
PRIMARY KEY (id),
) ENGINE=MyISAM
Köszönöm a segítséget!
Query
Ezen felul erdemes lenne megnezni a szerver metrikait peldaul a mysqltuner.pl-el, biztos vagyok benne, hogy 1-2 cache ertek tul alacsonyra van tekerve.
explain
Az explain ezt mondja:
Using temporary; Using
ORDER BY db DESC.Jaigen
igen
"- vagy pedig jöhet a denormalizálás, és vegyek fel B-re egy számláló mezőt, amit triggerrel/php-val updatelek?"
Trigger, vagy php?
ORDER BY
Pontosan
Nekem viszont igencsak hibázik az "AS" is:
Megoldási javaslat:
- Gondolom az A táblába egy tevékenységként egy rekordot szúrsz be (B.id-vel együtt). Itt azért jó lenne tudni a konkrét feladatot is, legalábbis nekem.
- Ha így van, csinálsz szépen egy C táblát a statisztikának, és azt update-eled, mikor A-ba beszúrsz (utána), így ezt a statisztikát egy (vagy B és C) táblából kéred le, amit persze helyesen indexelsz is (és kiegészítheted a neked tetsző dolgokkal).
- Természetesen itt már kötelező lesz az InnoDB, hogy ezt tranzakcióban tedd meg, ne lehessen benne "elírás".
- Ha a rangsorban B-ből nagyon kevés adatra van szükség (pl. csak a name), akkor megteheted azt a csúnyácska megoldást is, hogy amikor a C-be beszúrni kell (mert B-ből új id került A-ba), akkor beszúrod ezt az adatot is. Így egytáblás, indexelt lekérdezésed lesz = szupergyors, ha nem is szép megoldás (mert adatot nem duplikálunk).
- A lényeg, hogy amikor adatot viszel be, akkor frissíts mindent, de egy tranzakcióban, hogy semmi ne akadjon össze, és automatizálva legyen.
- Azért is javaslom az InnoDB-t, mert megadhatsz külső kulcsokat is, amik alapján pl. a B táblából való törléskor törlődnek az A és C tábla rá hivatkozó rekordjai is, vagy nem engedi törölni, ha van rá hivatkozás. Hasznos dolog ám ez, így nem kell PHP-ban ügyeskedni, hogy ki ne felejts valamit. (Azért hogy ellene is szóljak kicsit az InnoDB-nek: már nem emlékszem, de vagy INSERT és UPDATE, vagy SELECT műveletre kicsit lassabb, mint a MyIsam. De cserébe ott a tranzakció, és a többi előny.)
Szerintem az adatbázis nagyon okos felépítése is fontos, hogy a későbbi (változó) lekérdezésekhez is jó legyen. Itt bizony előre kell optimalizálni (most Gábornál biztos kivertem a biztit :)).
Az "AS" nem kötelező.
Vannak helyzetek, amikor kötelező az adatok duplikációja, mint például ha elektronikusan számlát állítasz ki.
Szerintem az nem jó,
Az adatduplikálás akkor sem "szép", ha muszáj. De ez van, néha igen hasznos (vagy mint írod: kötelező), de elméleti szempontból helytelen. Viszont a gyakorlat nem csak elméletekből áll... De amikor csak lehet, kerüljük a duplikálást.
Amit idéztél, egy általam jónak tartott adatbáziskezelés-tanártól hallottam, és ahol lehet, be is tartom.
Az adatduplikálás akkor sem
Ha a duplikálástól gyorsabb lesz az alkalmazásod, akkor by all means, duplikálj. Ezt hívják cache-nek.
Melyik elmélet? Nem mindegy.
Úgy általában elméleti szempontból nem helytelen (ami nem azt jelenti, hogy ne lenne elmélet, ami szerint helytelen).
Adatbázis normalizálás szempontjából nevezhetjük annak. Vagy nézhetnénk a konzisztencia szempontjából, mert úgy meg nem előnyös, mert nehéz lesz azt fenntartani (már a konzisztenciát).
Ha sebesség szempontjából nézzük, akkor elméletileg nem helytelen, ha adattárolás biztonsága felől közelítjük a kérdést, akkor pedig elméletileg is elvárt az adatduplikálás.
Szóval a skála elég széles.
Igen, mindkettőtöknek igaza van
Nekem alapmegközelítésem - adatbázis tervezéskor -, hogy lehetőleg ne kelljen duplikálni. Viszont - épp azért, mert a skála elég széles -, a legtöbb esetben ettől el kell térni, akár "csak" gyorsaság szempontjából is (pl. fórumtéma, kommentek: felh. név).
Talán afelé lehet egy igazság, hogy minél kevesebbet duplikáljunk, és főként: soha ne feleslegesen (mondjuk az már szarvashiba).
Ezért (egyszerűség okán) használom a "csúnya" kifejezést rá, ami persze nem a legmegfelelőbb jelző...
Nekem helyi (BDE, MSAccess) kezelőkön indult a tanulásom, így egy kicsit más szemlélet is "bentmaradt": ezeknél, ha csak elfér fizikai RAM-ban, akkor ott is van a teljes adatbázis, indexekkel együtt. Néha (változások után, külön szálként) ráment a fizikai állományra. Ilyen szituban nemigazán jelent sebességkülönbséget a duplikáció, viszont nagyobb lesz a DB, hamarabb kerül ki a fizikai RAM-ból. Mindez persze kb. 100 000-es rekordszámig volt igaz (ma nem tudom, nemigen használom már).
A MySql-től Pdo, stb.. kezelők egész mások, nyilván nagyobb rugalmasságot követelnek meg, és ritka eset az, hogy "ott pihennek" a RAM-ban, hacsak nem külön "kérjük" erre. Ezeket a különbségeket folyamatosan figyelembe kell vegyem - ez igaz.
tranzakció
Továbbá bonyolódik a helyzet azzal, hogy rész-statisztikákat is kell számolni, vagyis nem a teljes 'A' táblából, hanem csak egy részéből (havi statisztika). Tehát a külön statisztika rögzítése akár külön táblában, akár B tábla külön oszlopában, nem jöhet szóba, mint megoldás.
PG
Tranzakció
Bármikor előfordulhat egyidejűség egy másik szállal.
Ezen kívül ott a lehetőség, hogy ha sikertelen a tranzakció, naplózod, hogy miért (hol akadt el), sleepels 1-2 s-ot, és újra próbálod. Ezt 3x simán megteheted. Egyébként meg kiírod a Júzernek, hogy próbálja kicsit később...
A C táblába csak azokat az adatokat viszed át, amik a statisztika készítéséhez szükségesek, tehát a dátumokat is. Simán megoldod lekérdezésen belül az időintervallumot is. Tehát A tábla minden változásakor aszerint frissíted a C-t.
Ezen a ponton már tényleg a konkrét feladatot kéne ismerni, a mintatáblák - gondolom - elfednek egy csomó fontos infót a szerkezetről-működésről. Így nehéz lesz többet segíteni.
Mi az, hogy "nincs törlés sem"?
Ha ez azt jelenti, hogy A táblából sosem törölsz, akkor még könnyebb a dolgod C-vel.
Használj count(*)-ot, MyISAM
count(*)-ot, MyISAM tábláknál jelentősen gyorsabb lehet.processedflaget, és cronból percenként végigmész aprocessed=0rekordokon, a számukat hozzáadod B-ben a megfelelő sorcountmezőjéhez, és 1-re állítod a flaget. Ha biztosítod, hogy egyszerre csak egy cron fusson, még InnoDB-re sem feltétlenül kell átváltanod miatta.processed=0sorok számát (egy okos indexszel ez gyors művelet).