MySQL subselect sebessége

Már egy egyszerű weboldalnál is gyakran előfordul, hogy a kapcsolódó adatokat egy subselect állítja elő, így csökkentve a lekérések számát az oldal generálásakor. Csak épp nem mindegy, hogy hogyan is csináljuk. A MySQL sebességét nagyon erősen az határozza meg, hogy hány sorral kell dolgoznia. Ezért is nagyon fontos, hogy mindig a lehető legkevesebb sorral dolgozzon az adatbázis, akkor is, ha csak egy köztes folyamatról van szó. Az alábbi 2 query ugyanazt az eredményt adja, csak épp tízezerszeres (!) sebességkülönbséggel: Olvass tovább…

13:32 - legyes

Több sor és oszlop egy cellaként MySQL és MariaDB-ben: JSON függvények és a GROUP_CONCAT() együtt

A fejlesztők 2 fajtája nem foglalkozik az adatbázis lekérdezések sebességével több kapcsolódó adatnál: a kezdők és a profik.

A kezdők lekérdezik az alap adatokat, majd egy foreach()-en belül futatják le a kapcsolódó adatok lekérdezését:

foreach ( $products as $product ) {
   $db->query( "SELECT id, thumbnail, url FROM product_images WHERE product_id = :product_id" );
}

A profik is ez csinálják, csak sokkal csillibb kivitelben. Először csinálnak egy Image class-t. Majd egy getAllByProduct( int $product_id ) metódust hozzá. A termék lekérdezésénél pedig vagy a Product konstruktorába teszik – amit a FETCH_CLASS -al használnak ki -, vagy a Product::get( int $product_id ) -be, egy ciklusban. Ami megint ugyanaz csak pepitában.   Olvass tovább…

2017.08.28. 02:22 - legyes

MySQL: GROUP_CONCAT() méretének növelése a sessionben

Sokan elkövetik azt a hibát, hogy a MySQL lekéréseket PHP ciklusba teszik, mert másképpen szerintük nem megoldható, hogy több soros eredményt tegyünk bele 1 SQL cellába a lekérdezés eredményénél. Ez a rossz módszer természetesen nagyon erőforrás pazarló és a weboldalak sebesség problémáinak nagy részét is ez okozza.

Az egyik jó megoldás ennek elkerülésére, ha a GROUP_CONCAT() paranccsal egyesítjük a subquery sorait. Viszont alapértelmezetten ebbe csak viszonyag rövid eredmény fér, így nagy eséllyel le lesz vágva a válasz vége. A méret néveléshez adjuk ki az SQL kérés előtt a következő parancsot:

SET SESSION group_concat_max_len = 100000;

Olvass tovább…

2017.04.03. 13:37 - legyes

JSON képes MySQL 5.7 telepítése Debian linuxra

A NoSQL hullám nagyon hódít, amivel én nem feltétlenül értek egyet, mert azt vallom, hogy mindent arra kell használni, amire való (azaz adatok tárolására, szűrésére, stb. SQL-t). Az egyik fő indok mellette azt szokott lenni, hogy JSON objektumokat lehet benne tárolni és azokkal lehet dolgozni. Így  kliens oldal eleve készen kapja, nem kell a backenden összerakni és szétszedni újra és újra. Olvass tovább…

2017.02.06. 15:03 - legyes

Súlyos MySQL sebezhetőség: CVE-2016-6662

Sajnos megjelent egy olyan rendkívül súlyos MySQL sebezhetőség, ami a korlátozott jogokkal rendelkező felhasználókat is hozzásegíti a teljes MySQL jogosultság megszerzéséhez. Ez a jelenleg elérhető legfrissebb rendszereken is megtalálható, a biztonsági csomagok még nem tartalmazzák. Ez a sebezhetőség ugyanúgy problémát okoz a weblapokon keresztül – így a PHPMyAdminban – is. Érintett a MySQL és azok klónjai (pl. MariaDB).

Affected MySQL versions (including the latest):
<= 5.7.15
<= 5.6.33
<= 5.5.52

 

MariaDB frissítés és info: https://mariadb.com/kb/en/mariadb/mariadb-10117-release-notes/ a hivatalos telepítési (stabil) leírás szerinti változat a Debian 8 stabilban ez a csomag szerepel: 10.1_10.1.17+maria-1~jessie_amd64.deb .

MySQL infó: http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-15.html#mysqld-5-7-15-bug

Bővebb információ a sebezhetőségről itt: http://legalhackers.com/advisories/MySQL-Exploit-Remote-Root-Code-Execution-Privesc-CVE-2016-6662.html és a több módszer közül az egyik már szerepel a MySQL bugfix changelogban itt: https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-52.html .

Magyar hír róla pedig itt: http://hup.hu/cikkek/20160914/mysql_tavoli_root_privilegium-szint_emeles_0day

2016.09.14. 17:02 - legyes

HeidiSQL

Próbáltam már használni a MySQL Workbenchet, a ToadSQL-t és egyéb népszerűbb programot, de valahogy mindig a HeidiSQL-nél kötöttem ki. Nem profi cucc, de az alap dolgokhoz épp elég. Jelenleg 3 féle adatbázist kezel: MySQL (MariaDB specifikus dolgokat is kezeli!), Microsoft SQL, PostgreSQL. Ezekhez több módon is képes kapcsolódni, akár SSH tunnelen keresztül is, amit a plink segítségével valósít meg. Ja és beszélni magyar 🙂 Van benne pár bug, ezért érdemes menteni ha a FullHD monitor kicsi a lekérdezés áttekintéséhez. Jellemzően akkor száll el, ha a kapcsolat/tunnel megszakad alatta (pl. elaltatjuk a gépet, vagy magentás ADSL-ünk van). Linux verzió nincs, de működik Wine-nal.

HeidiSQL2
A támogatott adatbázis típusok

Olvass tovább…

2016.08.23. 16:10 - legyes

Településlista betöltése MySQL adatbázisba

Az egyik projektemben szükség van a magyar települések listájára, lehetőleg koordinátákkal a későbbi távolság számítás és térképen ábrázolások miatt. Nagyon sok szolgáltató kínál ilyen adatbázisokat vagy webes API-kat horror árakon. Találtam egyet, ahonnan letölthető ez a lista txt-ben: http://download.geonames.org/export/zip/ . A fájl UTF-8 formátumú, tabbal elválasztott szöveges fájl, ahol a sorvége jel a \n.

telepuleslista


Olvass tovább…

2016.08.22. 14:13 - legyes

SQL: Ügyes trükk 2 állapot közötti váltogatásra

Sok esetben előfordult már, hogy szükségem volt egy elem láthatóságának 2 állapota közötti váltogatásra. Igazi amatőr módon eddig vizsgálgattam, hogy látható-e és ha igen, akkor el kell rejteni és fordítva. Na, ettől jóval egyszerűbb a megoldás:

UPDATE table SET field = 1 - field

forrás: http://stackoverflow.com/questions/603835/mysql-simple-way-to-toggle-a-value-of-an-int-field

2016.03.23. 14:57 - legyes

MySQL, még mindig az indexek

Íratlan törvény, hogy a táblák kapcsolódási pontjaiból érdemes indexet csinálni. A Zandagort kódban ezt már korábban átnéztem. Viszont volt néhány query, ami viszonylag sok tábla kapcsolatából állt és egyre nagyobb részt vett ki a körváltóból, így utána kellett nézni, nem-e lehet gyorsabbá tenni.

zanda_s12-week[1]

cpu-week[1]

load-week[1]

Végig néztem a használt táblákat és volt már rajtuk index, szép számmal, több helyen már redundánsnak is tűntek. Próbáltam a lekérdezés WHERE feltételének sorrendjét módosítani, de kb. semmi változást nem hozott. Próbálkoztam az EXPLAIN EXTENDED és persze az SQL_NO_CACHE kombóval, hogy hátha valahol még lehet faragni. Látszólag mindenhol a PRIMARY KEY-eket használta, ami nekem úgy tűnt, hogy rendben van. Az egyetlen probléma az volt, hogy viszonylag nagy adatmennyiséggel kellett dolgoznia. Nézegettem a JOIN és WHERE feltételeket, úgy éreztem, hogy egyesével kell végignézni minden oszlopot. Ezek az oszlopok már szerepeltek kulcsokban, többségük PRIMARY KEY is, viszont találtam kettőt, ami csak többed magával szerepel kulcsokban. Látszólag nem volt értelme belőlük kulcsot csinálni, de egy próbát megért. A változás látszik a grafikon végén.

Egyik oldalt az esztelen kulcsgyártás van, a másik oldalt pedig a tapasztalatok és ajánlások szigorú követése. Sajnos eddig a részben esztelen kulcsgyártás áll nyerésre.

A nagy mennyiségű adatokkal való munka az adatbázisban már egy külön szakma lett szerintem. A 18 magos Xeon procik korában mindenki hajlamos az SQL-t sima adattárolónak használni, ráadásul hódítanak a NoSQL megoldások is, ami szerint pont az SQL optimalizációk miatt nem jó ötlet, kivéve, ha kis mennyiségű adattal kell dolgozni. Egy jól kioptimalizál SQL adatbázis nagy forgalom esetén egy $25-os robotos TV játékoson is gyorsabban tud futni, mint az optimalizálatlan változat egy 15 milliós szerveren.

mysql_queries-week[1]

2015.10.13. 09:25 - legyes

PHP és MySQL adattípus túlcsordulások

Azt ügye tudjuk, hogy a PHP-ben automatikus típuskonverzió van, amire oda kellene figyelni. A PHP-ban a számok ábrázolására 2 típus áll rendelkezésre (+boolean):
– (signed) integer
– (signed) float

Ezek minden esetben előjellel vannak ellátva, így az integer típusban maximum 2^63 érték tárolható egy 64 bites rendszeren, 64 bites PHP-val. Viszont! A MySQL-ben rendelkezésre áll az előjel nélküli változat, az unsigned bigint, ami ugyanezen körülmények között 2^64 tud ábrázolni, előjel nélkül. Így marad egy nagy hézag a kettő között.  Mivel a PHP-ban automatikus típuskonverzió van, így az (unsigned big)int átalakul a PHP-ben float típussá, amiből mi semmit sem veszünk észre. A float típussal ügye csak pontosságot vesztettünk.

De mi van akkor, ha a PHP által adott választ egy szigorúbb nyelvvel akarjuk feldolgozni – vagy mert int-et várunk -, erőltetjük az int típust:

echo (int) ((int) PHP_INT_MAX+1); // -9223372036854775808

Akkor az bizony a PHP-ban is túlcsordul, ahogy azt a MySQL-ben is tenné ha signed bigint lenne és ebből mi semmit sem veszünk észre. Ugyanez, automatikus típuskonverzióval:
echo(PHP_INT_MAX+1); // 9.2233720368548E+18

Hogy ez miért okozhat gondot?

Elterjedt szokás, hogy biztonsági okok miatt lehetőleg csak ID-ket (azonosító számokat) küldünk szerver oldalra, mivel ezeket jóval könnyebb szűrni, mint a stringeket. Az adatbázisban pedig az azonosítók legtöbb esetben integer típusok, leginkább unsigned bigint-ek, autoincrement tulajdonsággal. Később pedig a szerver oldali vizsgálatnál – mivel számot várunk és az autotincrement alapból 1-ről indul – lustaságból ezt alkalmazzuk és úgy gondoljuk, hogy ezzel macera nélkül (prepared statements) meg is oldottuk az SQL injection elleni védelmet:


$id = abs($_GET['id']); // barmi nem szamra 0-t ad, ami az alap autoincerement 1-es kezdosorszama miatt nem letezo sorra mutat az adatbazisban
$results = $pdo->query('UPDATE users SET money=money+1000 WHERE id='.$id);

Ok, de eddig még sohasem okozott gondot a túlcsordulás, miért kellene rá figyelnem? 

Hát például ezért:
echo (int) PHP_INT_MAX; // 9223372036854775807
echo (int) ((PHP_INT_MAX+1000000)-1000000); // 9223372036854774784

Természetesen ez oda-vissza érvényes, azaz figyelni kell arra, hogy a PHP integer típusa több egész szám tárolására képes, mint a MySQL bizonyos integer típusai. A MySQL persze dob warningot, de ha külön nem kérdezzük meg róla, akkor jó eséllyel nem fogjuk észrevenni PHP-ben.

 

Sok forráskódban láttam már a fent említett lustaságból alkalmazott szám-konverziós védelmet. A baj az, hogy úgy hiszik elegendő csupán (int) -el erőltetni a típust. Csak hogy az negatív számokat is elfogad, így a túlcsordítással máris pozitív számmá változtathatjuk, ami viszont már tutira létezik az adatbázisban a tipikus autoincrementes megoldásoknál. Kiszámolni pedig könnyű a megfelelő mértékű túlcsordítást okozó számot, hogy a kért ID-t kapjuk eredményül. 

2015.07.19. 15:10 - legyes

MySQL view importálás hiba ( SELECT command denied to user ”@’ )

Amennyiben adatbázisokat költöztetünk, előfordulhat, hogy a view-t létrehozó felhasználó az új helyen már nem létezik. Ez jó kis galibát tud okozni, mert a hibaüzenetek nem utalnak egyértelműen a hiba valódi okára.

root@z:~# ERROR 1143 (42000) at line 1:
SELECT command denied to user ''@'85.90.....' for column 'oszlop'
 in table 'tabla'

Persze a szokásos MySQL használatot megkönnyítő programok nem működnek. A PHPMyAdmin csak részben listázza a táblákat és az information schema-ban kutakodva is több helyen errort kapunk. A HeidiSQL pedig egyáltalán nem tudja megnyitni az adatbázist, amiben a hibás jogosultság szerepel.
A neten a válaszok 99%-a sajnos nem oldja meg a problémát, mert nem GRANT-olni kell neki jogot.

Első lépésben meg kell nézni, hogy melyik VIEW lehet a hibás.
Sajnos ebben az INFORMATION SCHEMA és a MYSQL.PROC tábla nem lesz barátunk, – ha segédprogramot használunk – mert egyáltalán nem mutatja a hibás VIEW-t a tábla sorai között. Marad a jó öreg konzolos módszer:

SHOW FULL TABLES IN adatbazis WHERE TABLE_TYPE NOT LIKE '%table%';

Majd a SHOW CREATE VIEW … segítségével megnézhetjük, hogy ki a DEFINER. A nem létező felhasználót hozzuk létre, vagy módosítsuk a VIEW-ban, pl.:

UPDATE mysql.proc SET definer='felhasznalo@localhost' WHERE db=adatbazis
 AND name=nezet_statisztika

A definer eltávolítása:

sed -i 's/DEFINER=[^*]*\*/\*/g' mydump.sql
2014.06.16. 10:32 - legyes

Az aktuális MySQL okosság #1

Arra adtam a fejem, hogy egy picit bővebben belevetem magam a MySQL-be. Ez nem jelenti azt, hogy enterspájz adatbázisokat optimalizálok, csak picit tovább megyek a “SELECT * FROM tabla ORDER BY RAND()” szinten. Erre igazából azért van szükség, mert úgy gondoltam, hogy összeütök magamnak egy parányi űrstratégiát PHP, MySQL, JavaScript, HTML4 alapon (lásd: www.zandagort.hu). No nem azért, hogy játsszak kicsit, hanem azért, hogy fejlesszem picit képességeimet.

Korábban már használtam az “EXPLAIN” SQL parancsot, utasítások végrehajtási idejének vizsgálatára. Viszont sajnos ez tárolt eljárásra nem működik. Szerencsére a Google ezt dobta:

set profiling=1;
call proc_name();
show profiles;

Forrás: http://stackoverflow.com/questions/5389611/using-explain-for-mysql-stored-procedure-calls

2012.10.31. 08:42 - legyes

PHP: felesleges nullák eltüntetése a MySQL DECIMAL típusból

Gondoltam lusta leszek és rákeresek a Google tudástárban, hogy hátha van ilyen paramétere mondjuk a numer_format() -nak.

A probléma az, hogy ha az adatbázisból pl. DECIMAL 6,2 formátumban érkezik egy egész szám, akkor a “.00” ott lesz a végén, pedig értelme ebben az esetben nem sok.

// $szam = "12.00";
echo $szam;
// 12.00

Aztán eszembe jutott az automatikus típuskonverzió, így a megoldás:

echo ($szam*1);
2012.05.07. 07:21 - legyes
1 2