+36 30 355 0880

0-24 Tudástár

Témakörök:

Találj meg minket a Google+-on

Mysql optimalizálás és honlap gyorsítás

Ebben a cikkben főleg technikai információkat fogunk megosztani arról, hogy a mysql lekéréseket hogyan lehet optimalizálni és ezzel gyorsítani az oldalad.

Ha az oldalad néha 500 Internal server error hibával leáll vagy túl lassan tölt be, akkor érdemes megvizsgálni a mysql lekéréseket főleg akkor, ha vannak olyan tábláink, ami túl sok sort tartalmaz.

Sok programozó feleslegesnek tartja ezeket a beállításokat, mondván elhanyagolható a különbség. Az igazság azonban az, hogy minél több adatról van szó, annál jelentősebb. Főleg akkor, ha a táblák össze vannak kötve (JOINT), mert akkor a méretek sokszorozódnak.

Volt olyan webáruház, ahol 638 termékhez voltak tárolva ajánlott termékek. Az ajánlott termékek táblában 10 000 sor volt. Egyetlen módosítással 9,1 másodperc helyett, 1,2 másodpercre gyorsult a webáruház. Nem mindegy, az ügyfeleknek sem. Ki szeretne várni 9 másodepercet minden oldal betöltés előtt? Te is továbbállnál.

 

Tábla módosítások:

Típusok: Nézzük meg, hogy a Mezőknek megfelelő típusokat adtunk e és azoknak az értéke nincs e eltúlozva. Ugyanis ha nem is használjuk ki, akkor is foglalásra kerülnek ezek az értékek.

• Hiba, hogy az Int(11) minden szám, miközben, ha 9 999 999 sor lesz, akkor is elég az Int(7).
• Hiba, hogy a varchar(255). Ezt főleg szöveges résznél használjuk, pl tartalom neve. Azonban itt is felesleges a 255, mivel sok esetben a 100 karakter sem férne ki..Gondolj bele ez a bekezdés 211 karakterből áll.
• Hiba, hogy minden szám Int, még a százalék is. Pedig oda inkább a Tinyint típust kellene használni.
• Ha szöveget mentesz, akkor ne varchar legyen, hanem text a típusa
  
  

Adatbázis motor: Általában ennek egyáltalán nem szentel senki semmilyen figyelmet. Importáljuk az adatbázist és hagyunk mindent úgy, ahogy találtuk. Azonban van választási lehetőségünk.

• InnoDB. Ezt  kevesen ismerik, ezért kevesen is használják. Azonban minden olyan táblánál, ami nagy mennyiségű adatot fog tartalmazni ezt a tároló motort kell választanunk. Gyorsulnak a lekérdezések és számos hasznos kiegészítőt tartalmaz. Egyedül a fulltext indexet nem támogatja. Bővebben
  
  Így tudsz átállni InnoDB-re: ALTER TABLE [tábla neve] ENGINE=InnoDB
  
  
• MyISAM: Ez a PhpMyAdmin alapértelmezett tároló motorja. Ha nem tárolunk sok adatot egy táblában, akkor erre nem lesz szükségünk, azonban te ezt a cikket olvasod, így valószínű nem ebbe a kategóriába tartozol. Olvasnivalók: Angolul és Magyarul.
  

 A legnagyobb gyorsulást azonban akkor érhetjük el, ha átgondoltabban fogalmazzuk meg a mysql lekéréseket. Nem mondom, hogy írd meg újra a szoftvert és tervezd át az adatbázist, még akkor sem, ha ez lenne a legjobb megoldás.

 

Mysql lekérdezések optimalizálása:

• Ne használj SELECT * kérést. Mindig csak azt kérd le, amire valóban szükséged van. 
• Lehetőleg legyen limit a kérés végén. Ha lapozót használsz, akkor se egybe kérd le a tábla tartalmát, hanem LIMIT(0,10) majd LIMIT (10,20) stb
  
•  Használd a WHERE kapcsolót és tegyél indexet a mezőre, amit a WHERE után írsz! Az indexről lejjebb bővebben olvashatsz
• GROUP helyett pedig inkább ORDER BY

Mysql Index - Gyorsabb adatbázis lekérések:

 

 Remélem hallottál már a táblák indexeléséről. Ez egy nagyon egyszerű módszer arra, hogyan tudod megkönnyíteni az életedet és gyorsítani a lekérdezéseket.

A táblák indexelése egyszerű folyamat, egyszerűen csak a PhpMyAdminben a megfelelő táblában a kiválasztott mező sorában a kis villám ikonra kattintunk. Egyszerű és mennyit segít!

 

Mi az indexelés?
Amikor lekérdezed egy tábla tartalmát, akkor végigolvassa az egészet. Azonban ha indexelve van és a lekérdezésben benne van a where=, akkor anélkül, hogy végignyálazná a táblát, kidobja az eredményt.

Pl: select ID from hirlevel where email="info@domainem.hu"

indexelés nélkül végignézi a hirlevel táblát, amíg nem találja meg a kívánt mail címet. Ha indexeljuk az email mezőt, akkor egyből kidobja nekünk az id -t. Gondold el, ha a feliratkozottak táblája 50 000 sort tartalmaz...mennyivel gyorsabb az index!!

 

Mit indexeljek?

 Az indexelés csak a select kéréseket gyorsítja, így az insert és az update utasításokra nincs jótékony hatása, sőt lassítja azokat. Célszerű tehát olyan táblákat indexelni, amikbe nincs gyakori írás. PL: log táblát nem érdemes.

 

EXPLAIN avagy tudd meg hogyan használd az indexet!

 Van egy nagyon jó kis utasítás, úgy hívják EXPLAIN. Megmutatja azt, hogy a select utasítást hogyan lehet optimalizálni.

• Kimásolod a php program által futtatott sql kérést. 
• Belépsz a phpMyAdminba
• Az sql kérést futtatod úgy, hogy elé írod, hogy EXPLAIN

Az eredmény egy táblázat lesz, ami megmutatja neked,  hogy a kérés milyen terhelést jelent az adatbázis számára. Mi lefutatuk a példa selectet és annak eredményét fogjuk elemezni. A táblázatot a következő módon kell értelmezni:

Select Type: SIMPLE
Table: hirlevel
Type: ref
Possible Keys: EMAIL
Keys: EMAIL
Key Len: 63
Ref: Const
Rows 1
Extra Using where

Ezek a fontossak:
Possible Keys (A lehetséges indexek)
Keys (Az indexek (vagy csak 1) amit valójában felhasznál
KeyLen (Az index hossza)
Rows (Sorok száma a lekérdezésben, ha túl nagy akkor komolyan kell foglalkozni a kéréssel). Ellenőrizzük, hogy a (Rows) mennyi sorra hivatkozik. Ha itt túl nagy szám található, az azt jelenti, hogy a motornak sok sort kell átvizsgálnia az eredmény eléréséhez, és ezt feltételezhetően nem megfelelő index, vagy hiányzó index okozza)
Extra (Extra információk: Ez nagyon fontos lehet). Az Extra tartalmazza, hogy készült e ideiglenes tábla a lekérdezés folyamán (Using temporary), vagy esetleg a sorba rendezést indexek figyelembe vétele nélkül végezte (Using fileshort). Ez akkor érdekes, ha túl nagy a row.

 

 Összetett lekérdezések esetén:

Továbbá komplex lekérdezések esetén, amikor több feltétel szerint is szűrünk, lehetséges kombinált indexeket készíteni a gyorsabb elérés céljából.
Pl: van egy táblánk, ahol a cég ügyfelei vannak nyilvántartva.
Van egy  RENDELES, (rendelt szolgáltatás típusok pl: domain, tárhely, email stb), és egy LAKCIM (hol lakik az ügyfél), mező.
Szeretnénk lekérdezni, hogy Budapestről kik rendeltek domain nevet:
Select * from ugyfelek where LAKCIM=”Budapest” and RENDELES=”domain”

Az alábbi index a legcélravezetőbb, mivel pontosan illeszkedik a lekérdezéshez:
KEY `kulcsneve` (`LAKCIM`,`RENDELES`)

 

Explan - MySQL dokkumentáció | Explan syntax fejlesztőknek

 

 LEFT JOIN vs (vagy) INNER JOIN

Gondolom a JOIN használatával tisztában vagy. Arra szolgál, hogy több táblát összekapcsolj. Nem is akarok bővebben írni a funkcióbeli különbségekről, akit érdekel olvassa el: Left join, Inner join, Right Join.

 

Hogyan használd gyorsításra:

• Futtasd le a SELECT kérést a phpMyAdminben és néz meg, hogy mi az eredmény. Nem túl szerencsés, ha túl nagy a kapott eredmény (LIMIT), azonban most az érdekel minket, hogy mennyi idő alatt fut le a kérés.
• A futás ideje határozza meg, hogy mennyi ideig kell a látogatónak várnia az eredményre illetve azt is, hogy menynire terheli a szervert.
• Cseréld ki a LEFT JOIN vagy RIGHT JOIN kifejezéseket az INNER JOIN parancsra. Futtasd újra az sql -t
• Ha az eredmény ugyan az, de gyorsult a lekérdezés, akkor gratulálok a gyorsításhoz :)
• 
  

Mysql View avagy Gyorsítsd fel a bonyolult lekérdezéseket.

Ez az a rész, amiről már kevesen hallottak. Lényegében arról van szó, hogy a MySQL táblában létrehozol egy view -t, ami innentől kezdve egy virtuális táblaként viselkedik és mindig naprakészen tartja magát a hozzá csatolt táblákból.

A nagy előnye, hogy nem kell mindig JOIN -t használni az sql lekérésekben, elég csak meghívni az elnevezést és máris kapjuk az adatokat.

 

Mik a view  előnyei:

• Hihetetlenül gyorsítja a mysql lekéréseket
• Egyszerűsödik a sql kérés, hiszen nem kell mindig JOIN -t használni
• Ha módosul egy kérés, elég a view -t változtatni és nem kell mindenhol átírni az sql kérést

 

Gyakorlatban így néz ki:

A következő kérést fogjuk lefuttatni a MySQL ben.

1. CREATE OR REPLACE VIEW `tagok_view` AS
2. SELECT tagtabla.nev
3. FROM tagok tagtabla;

Csak nagy vonalakban lássuk mi történik, nem sorban megyünk(!):

1. sor:

- CREATE OR REPLACE mivel folyamatosan átfogalmazgatjuk majd a view(nézet) definícióját, ezért használjuk a létrehozás/felülírás opciót, hogy mindig a létrehozott view változzon, ahogy átfogalmazzuk a kérést

- tagok_view - a nézet neve, amit létrehozunk

3. sor:

- itt egy elnevezés történik: a tagok adatbázis táblának adunk egy álnevet. Ez lehet bármi, én a tagtabla elnevezés mellett döntöttem. Innentől fogva a tagtabla néven hivatkozhatunk a tagok táblára (alias).

2. sor:

- SELECT tagtabla.nev - már az aliast használva megmondjuk, hogy a tagtabla táblából (ami jelen esetben a tagok táblát jelenti) a nev mezőre lesz szükségünk.

 

Összetettebb lekérdezések esetén:

CREATE OR REPLACE VIEW `tag_konyvvel_view` AS
SELECT tag.nev, konyv.cim
FROM kolcsonzes as kolcs
JOIN tagok tag ON tag.id = kolcs.tag_id
JOIN konyvek konyv ON konyv.id = kolcs.konyv_id

•  Innentől a kolcsonzes tablara úgy fogunk hivatkozni, hogy kolcs
• A tagok tablara úgy, hogy tag
• A konyvek tablara pedig úgy, hogy konyv

PhpMyAdminban is létrehozhatjuk ezeket a virtuális táblákat úgy, hogya tábla tartalmi nézetében az oldal aljára görgetünk. Látni fogjuk az újabb verziókban a view linket. kattintsunk rá és már csak be kell vinni az sql utasítást.

 

Szintaktika:

Létrehozás: 

CREATE
  [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]
VIEW [database_name].  [view_name
AS
[SELECT  statement]

Példa:

CREATE VIEW SalePerOrder
   AS
  SELECT orderNumber,
  SUM  (quantityOrdered * priceEach) total
  FROM orderDetails
  GROUP by orderNumber
  ORDER BY total DESC

Törlés:

DROP VIEW [IF EXISTS] [database_name].[view_name]

Példa:

DROP VIEW IF EXISTS organization

 

Remélem hasznosnak találtad a cikket. Jó optimalizálást kívánok. Hidd el nagyon nagy eredményekre számíthatsz ha ezeket mind betartod.

Ha találtál valami hasznosat a cikkben, nyomj egy tetszik gombot:

Kérlek írj egy köszönömöt, ha tetszett!

Csatlakozz a beszélgetéshez!

Ezeket már olvastad?