Förstå MySQL-frågor med Explain

Du är i ditt nya jobb som databasadministratör eller dataingenjör och du har bara gått vilse och försökt ta reda på vad dessa vansinniga sökfrågor ska betyda och göra. Varför finns det 5 kopplingar och varför finns det ett ORDER BY som används i en underfråga innan en av anslutningarna ens händer? Kom ihåg att du anställdes av en anledning – troligtvis har den anledningen också att göra med många invecklade frågor som skapades och redigerades under det senaste decenniet.

artikelomslag

nyckelordetEXPLAIN används i olika SQL-databaser och ger information om hur din SQL-databas utför en fråga. I MySQL kan EXPLAIN användas framför en fråga som börjar med SELECTINSERTDELETEREPLACE och UPDATE. För en enkel fråga skulle det se ut som följande:

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

istället för den vanliga resultatutgången skulle MySQL sedan visa sin uttalande exekveringsplan genom att förklara vilka processer som äger rum i vilken ordning när uttalandet körs.

Obs: OmEXPLAIN inte fungerar för dig kanske databasanvändaren inte harSELECT behörighet för de tabeller eller vyer som du använder i ditt uttalande.

EXPLAIN är ett utmärkt verktyg för att snabbt åtgärda långsamma frågor. Även om det säkert kan hjälpa dig, kommer det inte att ta bort behovet av strukturellt tänkande och en bra översikt över datamodellerna på plats. Ofta är det enklaste och snabbaste rådet att lägga till ett index i en viss tabells kolumner i fråga om de används i många frågor med prestandaproblem. Se upp, men använd inte för många index eftersom det kan vara kontraproduktivt. Att läsa indexet och tabellen är bara meningsfullt om tabellen har en betydande mängd rader och du behöver bara några datapunkter. Om du hämtar en enorm resultatuppsättning från en tabell och ofta frågar olika kolumner, är ett index på varje kolumn inte meningsfullt och hindrar prestanda mer än det hjälper. För mer information om de faktiska beräkningarna av index vs no index, läs Estimating Performance i den officiella MySQL-dokumentationen.

de saker du vill undvika där det är möjligt och tillämpligt är sortering och beräkningar inom frågor. Om du tror att du inte kan undvika beräkningar inom dina frågor: ja, det kan du. Skriv resultatuppsättningen någon annanstans och beräkna din datapunkt utanför frågan, det kommer att lägga mindre belastning på databasen och därför vara övergripande bättre för din ansökan. Se bara till att du dokumenterar varför du beräknar i din ansökan snarare än att få ett resultat som produceras i SQL direkt. Annars kommer nästa Databasadministratör eller utvecklare att följa med och ha den härliga tanken på att använda en beräkning inom frågan i linje med ”Åh, min föregångare visste inte ens att du kan göra det i SQL!”Vissa utvecklarteam som ännu inte har haft det oundvikliga problemet med att dö databaser kan använda beräkningar i fråga för talskillnader mellan datum eller liknande datapunkter.

den allmänna tumregeln för SQL-frågor är följande:

var exakt och generera bara de resultat du behöver.

låt oss kolla in en lite mer komplicerad fråga…

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)

…och dess EXPLAIN utgång.

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

kolumnerna iEXPLAIN med de som behöver särskild uppmärksamhet för att identifiera problem i fetstil är:

id (query id)
select_type (typ av uttalande)
tabell (tabell refererad)
typ (join type)
possible_keys (vilka nycklar kunde ha använts)
nyckel (nyckel som användes)
key_len (längd på Använd nyckel)
ref (kolumner jämfört med index)
rader (antal sökta rader)
extra (ytterligare information)

ju högre antal sökta rader, desto bättre optimeringsnivå för index och frågeprecision måste vara för att maximera prestanda. Den Extra kolumnen visar möjliga åtgärder som du kan fokusera på för att förbättra din fråga om tillämpligt.

visa varningar;

om frågan som du använde medEXPLAIN inte tolkas korrekt kan du skrivaSHOW WARNINGS; I din MySQL-frågeredigerare för att visa information om det senaste uttalandet som kördes och inte var diagnostiskt, dvs det kommer inte att visa information för uttalanden somSHOW FULL PROCESSLIST;. Även om det inte kan ge en ordentlig fråga exekveringsplan som EXPLAIN gör det, kan det ge dig tips om de frågefragment som det kan bearbeta. Låt oss säga att vi använder frågan EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; på en given databas som faktiskt inte har en tabell foo. MySQL-utgången skulle vara:

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

om vi skriver SHOW WARNINGS; utgången är som följer:

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

låt oss prova detta med ett avsiktligt syntaxfel.

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

detta genererar följande varningar:

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

denna varningsutmatning är ganska enkel och visas av MySQL som resultatutmatning direkt, men för mer komplicerade frågor som inte tolkar är det fortfarande möjligt att ta en titt på vad som händer i de frågefragment som kan analyseras. SHOW WARNINGS; innehåller speciella markörer som kan leverera användbar information, till exempel:

<index_lookup>(query fragment) : ett indexuppslag skulle hända om frågan hade tolkats korrekt
<if>(condition, expr1, expr2): ett if-villkor förekommer i den här specifika delen av frågan
<primary_index_lookup>(query fragment): en indexuppslagning skulle hända via primärnyckel
<temporary table>: en intern tabell skulle skapas här för att spara tillfälliga resultat, till exempel i underfrågor innan du går med

för att ta reda på mer om dessa specialmarkörer, läs utökad förklara utdataformat i den officiella MySQL dokumentation.

den långsiktiga fixen

det finns flera sätt att fixa grundorsaken till dålig databasprestanda. Den första punkten att titta på är datamodellen, dvs. hur är uppgifterna strukturerade och använder du rätt databas? För många produkter är en SQL-databas bara bra. En viktig sak att komma ihåg är att alltid separera åtkomstloggarna från den vanliga produktionsdatabasen, vilket tyvärr inte händer i många företag. För det mesta i dessa fall startade ett företag litet, blev större och använder i huvudsak fortfarande samma databas, vilket innebär att de får tillgång till samma databas för både loggningsfunktionalitet och andra transaktioner. Detta minskar den totala prestandan avsevärt, särskilt när företaget blir större. Därför är det mycket viktigt att skapa en datamodell som passar och är hållbar.

datamodell

att välja en datamodell kommer sannolikt att avslöja rätt form av Databas (er) också. Om inte din produkt är väldigt grundläggande kommer du förmodligen att ha flera databaser för flera användningsfall – om du behöver visa nära realtidsnummer för åtkomstloggar, kommer du sannolikt att ha ett mycket presterande datalager medan vanliga transaktioner kan hända via en SQL-databas, och du kan ha en grafdatabas som ackumulerar relevanta datapunkter för båda databaserna i en rekommendationsmotor också.

mjukvaruarkitekturen för den totala produkten är lika viktig som själva databasen eftersom dålig design här kommer att resultera i flaskhalsar som går mot databasen och saktar ner allt både från mjukvarusidan och vad databasen kan mata ut. Du måste välja om behållare är rätt för din produkt, om en monolit är det bättre sättet att hantera saker, om du kanske vill ha en kärnmonolit med flera mikrotjänster som riktar sig till andra funktioner utspridda någon annanstans och hur du kommer åt, samla, bearbeta och lagra data.

hårdvara

lika viktigt som din allmänna struktur är din hårdvara en nyckelkomponent i din databasprestanda. Exoscale erbjuder dig olika instansalternativ som du kan använda beroende på din transaktion och lagringsvolym samt önskad svarstid.

det är viktigt att bestämma toppperioderna för din ansökan och därmed veta när man ska utelämna de långsammare administrativa frågorna om möjligt. Disk I / O och nätverksstatistik måste också beaktas när du utformar tidpunkten för dina databastransaktioner och analyser.

sammanfattning

Sammanfattningsvis är här huvudpunkterna för långsiktig prestanda sammanfattad:

skapa en hållbar datamodell som passar ditt företags behov
Välj rätt databasform
använd en programvaruarkitektur som matchar din produkt
gå igenom regelbundna iterationer för att titta på strukturen i dina frågor och användEXPLAIN på de mer invecklade, optimera användningen för din valda databas(er), även när det gäller databasuppdateringar och hur de kan påverka dig
välj de instanser som bäst passar dina applikations-och databasbehov i enlighet med prestanda och bandbredd

Maybaygiare.org