A MySQL lekérdezések megértése az Explain

segítségével új munkát végez Adatbázis-adminisztrátorként vagy Adatmérnökként, és csak eltévedt, hogy kitalálja, mit jelentenek ezek az őrült kinézetű lekérdezések. Miért van 5 csatlakozás, és miért van egy ORDER BY egy alkérdezésen belül, mielőtt az egyik csatlakozás megtörténik? Ne feledje, hogy okkal vették fel – valószínűleg ez az ok az elmúlt évtizedben létrehozott és szerkesztett sok bonyolult lekérdezéshez is kapcsolódik.

cikkborító

a EXPLAIN kulcsszó különböző SQL adatbázisokban használatos, és információt nyújt arról, hogy az SQL adatbázis hogyan hajt végre egy lekérdezést. A MySQL-ben a EXPLAIN használható egy SELECTINSERTDELETEREPLACE és UPDATE. Egy egyszerű lekérdezéshez a következőképpen nézne ki:

EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas';

a szokásos eredménykimenet helyett a MySQL megmutatja az utasítás végrehajtási tervét azzal, hogy elmagyarázza, mely folyamatok milyen sorrendben zajlanak le az utasítás végrehajtásakor.

Megjegyzés: Ha a EXPLAIN nem működik az Ön számára, előfordulhat, hogy az adatbázis-felhasználó nem rendelkezik a SELECT jogosultsággal az utasításban használt táblákhoz vagy nézetekhez.

EXPLAIN egy nagyszerű eszköz a lassú lekérdezések gyors orvoslására. Bár minden bizonnyal segíthet Önnek, nem veszi el a strukturális gondolkodás szükségességét és a meglévő adatmodellek jó áttekintését. Gyakran a legegyszerűbb javítás és a leggyorsabb tanács az, ha indexet ad hozzá egy adott tábla oszlopaihoz, ha sok teljesítményproblémával rendelkező lekérdezésben használják őket. Óvakodj azonban, ne használjon túl sok indexet, mivel ez kontraproduktív lehet. Az index és a táblázat olvasása csak akkor van értelme, ha a táblázat jelentős mennyiségű sorral rendelkezik, és csak néhány adatpontra van szüksége. Ha egy hatalmas eredményhalmazt tölt le egy táblázatból, és gyakran lekérdezi a különböző oszlopokat, akkor minden oszlop indexének nincs értelme, és jobban akadályozza a teljesítményt, mint segít. Ha többet szeretne megtudni az index vs no index tényleges számításairól, olvassa el a teljesítmény becslését a hivatalos MySQL dokumentációban.

ahol csak lehetséges és alkalmazható, el szeretné kerülni a lekérdezéseken belüli rendezést és számításokat. Ha úgy gondolja, hogy nem tudja elkerülni a lekérdezéseken belüli számításokat: igen, megteheti. Írja be az eredményhalmazt máshova, és számítsa ki az adatpontot a lekérdezésen kívül, ez kevésbé terheli az adatbázist, ezért összességében jobb az alkalmazás számára. Csak győződjön meg róla, hogy dokumentálja, miért számít az alkalmazáson belül, ahelyett, hogy az SQL-ben előállított eredményt azonnal elkészítené. Ellenkező esetben a következő adatbázis-adminisztrátor vagy Fejlesztő jön, és az a dicsőséges ötlet, hogy egy számítást használjon a lekérdezésen belül, ” Ó, nézd, az elődöm nem is tudta, hogy ezt megteheti az SQL-ben!”Egyes fejlesztői csapatok, akiknek még nem volt elkerülhetetlen problémája az adatbázisok elpusztításával, lekérdezésen belüli számításokat használhatnak a dátumok vagy hasonló adatpontok közötti számkülönbségekre.

az SQL lekérdezések általános szabálya a következő:

legyen pontos, és csak a szükséges eredményeket hozza létre.

nézzünk meg egy kicsit bonyolultabb lekérdezést…

SELECT site_options.domain, sites_users.user, site_taxes.monthly_statement_fee, site.name, AVG(price) AS average_product_price FROM sites_orders_products, site_taxes, site, sites_users, site_options WHERE site_options.site_id = site.id AND sites_users.id = site.user_id AND site_taxes.site_id = site.id AND sites_orders_products.site_id = site.id GROUP BY site.id ORDER BY site.date_modified desc LIMIT 5;+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| domain | user | monthly_statement_fee | name | average_product_price |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+| www.xxxxxxxxxxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxx | 3.254781 || www.xxxxxxxxxxx.com | [email protected] | 0.50 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | 9.471022 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxx | 8.646297 || | [email protected] | 0.00 | xxxxxxxxxxxxxxx | 9.042460 || | [email protected] | 0.00 | xxxxxxxxxxxxxxxxxx | 6.679182 |+-----------------------------+-----------------------------+-----------------------+------------------------------------------+-----------------------+5 rows in set (0.00 sec)

…és annak EXPLAIN kimenetét.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 1 | SIMPLE | sites | index | PRIMARY,user_id | PRIMARY | 4 | NULL | 858 | Using temporary; Using filesort || 1 | SIMPLE | sites_options | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_taxes | ref | site_id | site_id | 4 | service.sites.id | 1 | || 1 | SIMPLE | sites_users | eq_ref | PRIMARY | PRIMARY | 4 | service.sites.user_id | 1 | || 1 | SIMPLE | sites_orders_products | ref | site_id | site_id | 4 | service.sites.id | 4153 | |//+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+5 rows in set (0.00 sec)

az oszlopok a EXPLAIN kimenet az is, hogy különös figyelmet kell fordítani a problémák azonosítása félkövér:

id (lekérdezés id)
select_type (utasítás típusa)
táblázat (hivatkozott táblázat)
típus (csatlakozás típusa)
possible_keys (mely kulcsokat lehetett volna használni)
kulcs (használt kulcs hossza)
ref (oszlopok az indexhez képest)
sorok (keresett sorok száma)
extra (további információk)

minél nagyobb a keresett sorok száma, annál jobb az indexek és a lekérdezések pontosságának optimalizálási szintje a teljesítmény maximalizálása érdekében. Az Extra oszlop azokat a lehetséges műveleteket mutatja be, amelyekre összpontosíthat a lekérdezés javítása érdekében, ha van ilyen.

figyelmeztetések megjelenítése;

Ha a EXPLAIN által használt lekérdezés nem megfelelően értelmezhető, beírhatja a SHOW WARNINGS; parancsot a MySQL lekérdezésszerkesztőjébe, hogy információkat jelenítsen meg az utoljára Futtatott és nem diagnosztikai utasításokról, azaz nem jelenít meg információkat az olyan utasításokról, mint a SHOW FULL PROCESSLIST;. Bár nem tud megfelelő lekérdezési végrehajtási tervet adni, mint például a EXPLAIN, tippeket adhat azokról a lekérdezési töredékekről, amelyeket feldolgozhat. Tegyük fel, hogy a EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; lekérdezést használjuk minden olyan adatbázisban, amely valójában nem rendelkezik táblázattal foo. A MySQL kimenet a következő lenne:

ERROR 1146 (42S02): Table 'db.foo' doesn't exist

ha beírjuk SHOW WARNINGS; A kimenet a következő:

+-------+------+-------------------------------------+| Level | Code | Message |+-------+------+-------------------------------------+| Error | 1146 | Table 'db.foo' doesn't exist |+-------+------+-------------------------------------+1 row in set (0.00 sec)

próbáljuk meg ezt szándékos szintaktikai hibával.

EXPLAIN SELECT * FROM foo WHERE name = ///;

Ez a következő figyelmeztetéseket generálja:

> SHOW WARNINGS;+-------+------+---------------------------------------------------------------------+| Level | Code | Message |+-------+------+---------------------------------------------------------------------+| Error | 1064 | You have an error in your SQL syntax; (...) near '///' at line 1 |+-------+------+---------------------------------------------------------------------+

Ez a figyelmeztetések kimenete meglehetősen egyszerű, és a MySQL azonnal eredménykimenetként jeleníti meg, de a bonyolultabb lekérdezések esetében, amelyek nem elemzik, még mindig lehetséges, hogy megnézzük, mi történik azokban a lekérdezési töredékekben, amelyeket elemezni lehet. SHOW WARNINGS; speciális jelölőket tartalmaz, amelyek hasznos információkat szolgáltathatnak, például:

<index_lookup>(query fragment): indexkeresés történne, ha a lekérdezést megfelelően elemezték volna
<if>(condition, expr1, expr2): egy if feltétel fordul elő ebben a konkrét része a lekérdezés
<primary_index_lookup>(query fragment): egy index keresés történne keresztül elsődleges kulcs
<temporary table>: egy belső tábla jön létre itt a Mentés ideiglenes eredmények, például a subqueries előtt csatlakozik

Ha többet szeretne megtudni ezekről a speciális markerek, olvassa el a következő cikket: bővített magyarázza kimeneti formátum a hivatalos MySQL dokumentációt.

A hosszú távú Fix

számos módja van, hogy rögzítse a kiváltó oka a rossz adatbázis teljesítményét. Az első szempont, amelyet meg kell vizsgálni, az adatmodell, azaz. hogyan épül fel az adat, és a megfelelő adatbázist használja? Sok termék esetében az SQL adatbázis csak finom. Fontos megjegyezni, hogy a hozzáférési naplókat mindig külön kell választani a szokásos termelési adatbázistól, ami sajnos sok vállalatnál nem történik meg. Többnyire ezekben az esetekben egy vállalat kicsiben indult, nagyobb lett, és lényegében továbbra is ugyanazt az adatbázist használja, ami azt jelenti, hogy ugyanazt az adatbázist érik el mind a naplózási funkciókhoz, mind az egyéb tranzakciókhoz. Ez jelentősen csökkenti az általános teljesítményt,különösen a vállalat növekedésével. Ezért nagyon fontos egy olyan adatmodell létrehozása, amely illeszkedik és fenntartható.

adatmodell

Az adatmodell kiválasztása nagy valószínűséggel felfedi az adatbázis(ok) megfelelő formáját is. Kivéve, ha a termék nagyon egyszerű, akkor valószínűleg több adatbázisok több felhasználási esetek – ha meg kell mutatni közel valós idejű számok hozzáférési naplók, akkor valószínűleg szeretne egy nagy teljesítményű adattárház mivel a rendszeres tranzakciók történhetnek keresztül SQL adatbázis, és lehet, hogy egy grafikon adatbázis, amely felhalmozza a vonatkozó adatpontok mindkét adatbázisok egy ajánló motor is.

a teljes termék szoftverarchitektúrája ugyanolyan fontos, mint maga az adatbázis, mivel a rossz tervezés Itt szűk keresztmetszeteket eredményez, amelyek az adatbázis felé haladnak, és mindent lelassítanak mind a szoftver oldalról, mind az adatbázis kimenetéről. Ki kell választania, hogy a konténerek megfelelőek-e a termékéhez, hogy a monolit a jobb módja-e a dolgok kezelésének, hogy szeretne-e egy mag monolitot több mikroszolgáltatással, amelyek más funkciókat céloznak meg máshol, és hogyan érheti el, gyűjti, dolgozza fel és tárolja az adatokat.

hardver

ugyanolyan fontos, mint az általános szerkezet, a hardver kulcsfontosságú eleme az adatbázis teljesítményét. Az Exoscale különféle példányopciókat kínál, amelyeket a tranzakciótól és a tárolási mennyiségtől, valamint a kívánt válaszidőtől függően használhat.

fontos meghatározni az alkalmazás csúcsidőszakait, és ezért tudni kell, mikor kell kihagyni a lassabb adminisztratív lekérdezéseket, ha lehetséges. A lemez I / O és a hálózati statisztikákat is figyelembe kell venni az adatbázis-tranzakciók és elemzések időzítésének megtervezésekor.

Összefoglalás

összefoglalva, itt vannak a hosszú távú teljesítmény főbb pontjai:

hozzon létre egy fenntartható adatmodellt, amely megfelel a vállalat igényeinek
válassza ki az adatbázis megfelelő formáját
használjon olyan szoftverarchitektúrát, amely megfelel a termékének
menjen végig a lekérdezések struktúrájának rendszeres iterációin, és használja aEXPLAIN a zavartabbaknál optimalizálja a választott adatbázis(ok) használatát, az adatbázis-frissítések tekintetében is, és hogyan befolyásolhatják Önt
válassza ki azokat a példányokat, amelyek a legjobban megfelelnek az alkalmazás és az adatbázis igényeinek a teljesítmény és a sávszélesség szerint

Maybaygiare.org