MySQL Queries begrijpen met Explain

je zit in je nieuwe taak als Database Administrator of Data Engineer en je bent gewoon verdwaald in het proberen erachter te komen wat deze krankzinnig uitziende queries verondersteld worden te betekenen en te doen. Waarom zijn er 5 joins en waarom is er een ORDER BY gebruikt in een subquery voordat een van de joins zelfs gebeurt? Vergeet niet, je werd ingehuurd om een reden – hoogstwaarschijnlijk, die reden heeft ook te maken met vele ingewikkelde query ‘ s die werden gemaakt en bewerkt in de afgelopen tien jaar.

article cover

het EXPLAIN trefwoord wordt gebruikt in verschillende SQL-databases en geeft informatie over hoe uw SQL-database een query uitvoert. In MySQL kan EXPLAIN gebruikt worden voor een query die begint met SELECTINSERTDELETEREPLACE, en UPDATE. Voor een eenvoudige query, het zou er als volgt uitzien:

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

in plaats van de gebruikelijke resultaatuitvoer, zou MySQL dan zijn uitvoeringsplan voor statement tonen door uit te leggen welke processen in welke volgorde plaatsvinden bij het uitvoeren van de statement.

Opmerking: Als EXPLAIN niet werkt voor u, heeft uw database-gebruiker mogelijk niet het SELECT privilege voor de tabellen of weergaven die u gebruikt in uw statement.

EXPLAIN is een geweldig hulpmiddel om langzame queries snel te verhelpen. Hoewel het u zeker kan helpen, neemt het de behoefte aan structureel denken en een goed overzicht van de bestaande datamodellen niet weg. Vaak is de eenvoudigste oplossing en snelste advies om een index toe te voegen aan de kolommen van een specifieke tabel in kwestie als ze worden gebruikt in veel query ‘ s met prestatieproblemen. Pas echter op, gebruik niet te veel indices omdat dat contraproductief zou kunnen zijn. Het lezen van de index en de tabel heeft alleen zin als de tabel een aanzienlijke hoeveelheid rijen heeft en u slechts een paar gegevenspunten nodig hebt. Als u een enorme resultaatset uit een tabel haalt en vaak verschillende kolommen opvraagt, heeft een index op elke kolom geen zin en belemmert de prestaties meer dan het helpt. Voor meer informatie over de werkelijke berekeningen van index vs geen index, lees het schatten van de prestaties in de officiële MySQL documentatie.

de dingen die u wilt vermijden waar mogelijk en van toepassing zijn sorteren en berekeningen binnen queries. Als u denkt dat u berekeningen binnen uw vragen niet kunt vermijden: ja, dat kunt u. Schrijf de result set ergens anders en bereken uw data punt buiten de query, het zal minder druk op de database te zetten en dus over het algemeen beter voor uw toepassing. Zorg ervoor dat u documenteert waarom u berekent binnen uw applicatie in plaats van een resultaat geproduceerd in SQL meteen. Anders zal de volgende Database Administrator of Ontwikkelaar langs komen en hebben het glorieuze idee van het gebruik van een berekening binnen de query in de trant van, “Oh kijk, mijn voorganger wist niet eens dat je dat kunt doen in SQL!”Sommige ontwikkelaarteams die nog niet de onvermijdelijke problemen van het sterven van databases hebben gehad, kunnen in-query-berekeningen gebruiken voor getalsverschillen tussen datums of soortgelijke gegevenspunten.

de algemene vuistregel voor SQL-queries is als volgt:

wees precies en genereer alleen de resultaten die u nodig hebt.

laten we eens kijken naar een iets ingewikkelder query…

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)

…en zijn EXPLAIN uitvoer.

+------+-------------+---------------------------------+--------+-----------------+---------------+---------+---------------------------------+------+-----------+| 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)

de kolommen in de EXPLAIN uitvoer met de kolommen die speciale aandacht nodig hebben om vetgedrukte problemen te identificeren zijn:

id (query id in)
select_type (type instructie)
tabel (tabel waarnaar wordt verwezen)
type (type)
possible_keys (toetsen die gebruikt kunnen worden)
– toets (de toets die is gebruikt)
key_len (lengte van de gebruikte sleutel)
ref (kolommen ten opzichte van de index)
rijen (aantal rijen gezocht)
Extra (aanvullende informatie)

hoe hoger het bedrag van rijen doorzocht, hoe beter de optimalisatie niveau ten opzichte van indices en query precisie moeten worden om het maximaliseren van de prestaties. De extra kolom toont mogelijke acties waarop u zich kunt richten om uw zoekopdracht te verbeteren indien van toepassing.

Toon waarschuwingen;

als de query die u gebruikte met EXPLAIN niet correct ontleedt, kunt u SHOW WARNINGS; intypen in uw MySQL query-editor om informatie te tonen over het laatste statement dat werd uitgevoerd en niet diagnostisch was, dat wil zeggen dat het geen informatie zal tonen voor statements zoals SHOW FULL PROCESSLIST;. Hoewel het geen goed query uitvoeringsplan kan geven zoals EXPLAIN wel, kan het u tips geven over die query fragmenten die het zou kunnen verwerken. Laten we zeggen dat we de query EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; gebruiken op een bepaalde database die geen tabel heeft foo. De MySQL-uitvoer zou zijn:

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

als we SHOW WARNINGS; de uitvoer is als volgt:

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

laten we dit proberen met een doelbewuste syntaxfout.

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

dit genereert de volgende waarschuwingen:

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

deze uitvoer van waarschuwingen is vrij eenvoudig en wordt door MySQL meteen als resultaat weergegeven, maar voor meer gecompliceerde query ‘ s die niet ontleden, is het nog steeds mogelijk om te kijken naar wat er gebeurt in die query fragmenten die kunnen worden ontleed. SHOW WARNINGS; bevat speciale markers die nuttige informatie kunnen leveren, zoals:

<index_lookup>(query fragment): een index lookup zou plaatsvinden als de query correct was ontleed
<if>(condition, expr1, expr2): een voorwaarde is die zich in dit specifieke deel van de query
<primary_index_lookup>(query fragment): een index lookup zou gebeuren via primaire sleutel
<temporary table>: een interne tabel zou worden gemaakt voor het opslaan van tijdelijke resultaten, bijvoorbeeld in subquery ‘ s voorafgaand aan de joins

om meer Te weten over deze speciale markers, lees de Uitgebreide Uitleg Output Formaat in de officiële MySQL documentatie.

de lange termijn Fix

er zijn verschillende manieren om de hoofdoorzaak van slechte databaseprestaties op te lossen. Het eerste punt om naar te kijken is het datamodel, d.w.z. hoe zijn de gegevens gestructureerd en gebruikt u de juiste database? Voor veel producten is een SQL-database prima. Een belangrijk ding om te onthouden is om altijd de toegang logs te scheiden van de reguliere productie database, die helaas niet gebeurt in veel bedrijven. Meestal in deze gevallen, een bedrijf begon klein, groeide groter, en in wezen gebruikt nog steeds dezelfde database, wat betekent dat ze toegang tot dezelfde database voor zowel logging functionaliteit als andere transacties. Dit vermindert de algehele prestaties aanzienlijk, vooral als het bedrijf groter wordt. Daarom is het erg belangrijk om een datamodel te creëren dat past en duurzaam is.

gegevensmodel

het kiezen van een gegevensmodel zal hoogstwaarschijnlijk ook de juiste vorm van database(s) onthullen. Tenzij uw product is zeer basic, zult u waarschijnlijk meerdere databases voor verschillende use cases-als je nodig hebt om te laten zien in de buurt van real-time nummers voor toegang logs, zult u waarschijnlijk willen een zeer performante Data warehouse terwijl regelmatige transacties kunnen gebeuren via een SQL-database, en je zou kunnen hebben een grafiek database die de relevante datapunten van beide databases accumuleert in een recommender engine ook.

De softwarearchitectuur van het totale product is net zo belangrijk als de database zelf, aangezien slecht ontwerp hier zal resulteren in knelpunten die naar de database gaan en alles vertragen, zowel van de software kant als wat de database kan uitvoeren. U moet kiezen of containers zijn geschikt voor uw product, of een monoliet is de betere manier om dingen te behandelen, of u misschien wilt een Core monoliet met verschillende microservices gericht op andere functionaliteit verspreid elders en hoe u toegang, verzamelen, verwerken, en gegevens op te slaan.

Hardware

net zo belangrijk als uw algemene structuur, uw hardware is een belangrijk onderdeel in de prestaties van uw database. Exoscale biedt u verschillende instance-opties die u kunt gebruiken, afhankelijk van uw transactie-en opslagvolume en uw gewenste responstijd.

Het is van cruciaal belang om de piekperioden van uw applicatie te bepalen en zo te weten wanneer u de langzamere administratieve query ‘ s indien mogelijk moet weglaten. Disk I / O en netwerkstatistieken moeten ook in aanmerking worden genomen wanneer u de timing van uw databasetransacties en analyses ontwerpt.

samenvatting

samenvattend zijn hier de belangrijkste punten voor de prestaties op lange termijn samengevat:

Maak een duurzaam datamodel dat past bij de behoeften van uw bedrijf
kies de juiste vorm van database
gebruik een softwarearchitectuur die overeenkomt met uw product
bekijk regelmatig de structuur van uw query ‘ s en gebruik EXPLAIN Optimaliseer het gebruik voor uw gekozen database(s), ook met betrekking tot database-updates en hoe deze u kunnen beïnvloeden
kies de instanties die het beste passen bij uw toepassing en database behoeften in overeenstemming met prestaties en bandbreedte

Maybaygiare.org