Forstå MySQL-Spørringer med Forklar

Du er i din nye jobb som Databaseadministrator eller Dataingeniør, og du har bare gått deg vill og prøver å finne ut hva disse vanvittige søkene skal bety og gjøre. Hvorfor er det 5 sammenføyninger og hvorfor er det en ORDER BY brukt i en subquery før en av sammenføyningene selv skjer? Husk at du ble ansatt av en grunn-mest sannsynlig har den grunnen også å gjøre med mange innviklede spørringer som ble opprettet og redigert i løpet av det siste tiåret.

EXPLAIN nøkkelord brukes i ulike SQL-databaser og gir informasjon om hvordan SQL-databasen utfører en spørring. I MySQL kan EXPLAIN brukes foran en spørring som begynner med SELECTINSERTDELETEREPLACE, og UPDATE. For en enkel spørring, vil det se ut som følgende:

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

I Stedet For den vanlige resultatutgangen, Vil MySQL da vise sin utførelsesplan ved å forklare hvilke prosesser som foregår i hvilken rekkefølge når du utfører setningen.

Merk: hvisEXPLAIN ikke fungerer for deg, kan det hende at databasebrukeren ikke harSELECT – privilegiet for tabellene eller visningene du bruker i kontoutskriften.

EXPLAIN er et flott verktøy for å raskt rette opp sakte spørringer. Selv om det sikkert kan hjelpe deg, vil det ikke ta bort behovet for strukturell tenkning og en god oversikt over datamodellene på plass. Ofte er den enkleste løsningen og raskeste råd å legge til en indeks til en bestemt tabellens kolonner i spørsmålet hvis de brukes i mange spørringer med ytelsesproblemer. Pass på, men ikke bruk for mange indekser som det kan være kontraproduktivt. Å lese indeksen og tabellen gir bare mening hvis tabellen har en betydelig mengde rader, og du trenger bare noen få datapunkter. Hvis du henter et stort resultatsett fra et bord og spør forskjellige kolonner ofte, gir en indeks på hver kolonne ikke mening og hindrer ytelsen mer enn det hjelper. For mer om de faktiske beregningene av indeks vs ingen indeks, les Estimering Av Ytelse i den offisielle MySQL-dokumentasjonen.

de tingene du vil unngå der det er mulig og aktuelt, er sortering og beregninger i spørringer. Hvis du tror du ikke kan unngå beregninger i dine spørsmål: ja, det kan du. Skriv resultatsettet et annet sted og beregne datapunktet ditt utenfor spørringen, det vil legge mindre belastning på databasen og derfor være generelt bedre for søknaden din. Bare vær sikker på at du dokumenterer hvorfor du beregner i søknaden din, i stedet for å ha et resultat produsert i SQL med en gang. Ellers vil neste Databaseadministrator eller utvikler komme sammen og ha den strålende ideen om å bruke en beregning i spørringen i tråd med, » å se, min forgjenger visste ikke engang at du kan gjøre det i SQL!»Noen utviklerteam som ennå ikke har hatt det uunngåelige problemet med å dø databaser, kan bruke beregninger i spørringen for tallforskjeller mellom datoer eller lignende datapunkter.

DEN generelle tommelfingerregelen for SQL-spørringer er som følger:

vær presis og generer bare resultatene du trenger.

La oss sjekke ut en litt mer komplisert spørring…

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)

…og densEXPLAIN utgang.

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

kolonnene iEXPLAIN utdata med de som trenger spesiell oppmerksomhet for å identifisere problemer i fet skrift er:

• select_type (type setning)
• tabell (tabell referert)
• type (sammenføyningstype)
• possible_keys (hvilke nøkler kunne ha blitt brukt)
• nøkkel (nøkkel som ble brukt)
• key_len (lengden på brukt nøkkel)
• ref (kolonner sammenlignet med indeks)
• rader (antall rader som søkes)
• ekstra (tilleggsinformasjon)

jo høyere antall rader som søkes, desto bedre må optimaliseringsnivået for indekser og spørringspresisjon være for å maksimere ytelsen. Ekstra-kolonnen viser mulige handlinger du kan fokusere på for å forbedre spørringen hvis det er aktuelt.

Vis Advarsler;

hvis spørringen du brukte med EXPLAIN ikke analyserer riktig, kan du skrive SHOW WARNINGS; i MySQL-spørringseditoren din for å vise informasjon om den siste setningen som ble kjørt og ikke var diagnostisk, dvs.det vil ikke vise informasjon for uttalelser som SHOW FULL PROCESSLIST;. Selv om det ikke kan gi en riktig spørringsplan som EXPLAIN gjør det, kan det gi deg hint om de spørringsfragmentene det kan behandle. La oss si at vi bruker spørringen EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; på en gitt database som egentlig ikke har et bord foo. MySQL-utgangen vil være:

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

hvis vi skriver SHOW WARNINGS; utgangen er som følger:

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

La oss prøve dette med en bevisst syntaksfeil.

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

dette genererer følgende advarsler:

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

denne advarselsutgangen er ganske grei og vises av MySQL som resultatutgang med en gang, men for mer kompliserte spørringer som ikke analyserer, er det fortsatt mulig å se på hva som skjer i de spørringsfragmentene som kan analyseres. SHOW WARNINGS; inkluderer spesielle markører som kan levere nyttig informasjon, for eksempel:

• <index_lookup>(query fragment): et indeksoppslag ville skje hvis spørringen hadde blitt riktig analysert

<if>(condition, expr1, expr2): en hvis tilstanden oppstår i denne spesifikke delen av spørringen <primary_index_lookup>(query fragment): en indeks oppslag ville skje via primærnøkkel

• <temporary table>: en intern tabell vil bli opprettet her for å lagre midlertidige resultater, for eksempel i subqueries før tiltrer

for å finne Ut mer om disse spesielle markører, Les Utvidet forklar utdataformat i den offisielle mysql-dokumentasjonen.

Den Langsiktige Løsningen

det er flere måter å fikse årsaken til dårlig databaseytelse på. Det første punktet å se på er datamodellen, dvs. hvordan er dataene strukturert og bruker du riktig database? FOR mange produkter er EN SQL-database helt fint. En viktig ting å huske er å alltid skille tilgangsloggene fra den vanlige produksjonsdatabasen, som dessverre ikke skjer i mange selskaper. For det meste i disse tilfellene startet et selskap lite, vokste seg større, og bruker i hovedsak fortsatt den samme databasen, noe som betyr at de får tilgang til samme database for både loggfunksjonalitet og andre transaksjoner. Dette reduserer den generelle ytelsen betydelig, spesielt ettersom selskapet vokser seg større. Derfor er det svært viktig å lage en datamodell som passer og er bærekraftig.

Datamodell

Å Velge en datamodell vil mest sannsynlig avsløre riktig form for database (er) også. Hvis du trenger å vise nær sanntidsnumre for tilgangslogger , vil du mest sannsynlig ha et svært effektivt datalager, mens vanlige transaksjoner kan skje via EN SQL-database, og du kan ha en grafdatabase som samler de relevante datapunktene til begge databasene i en anbefalingsmotor også.programvarearkitekturen til det samlede produktet er like viktig som selve databasen, siden dårlig design her vil resultere i flaskehalser som går mot databasen og senker alt ned både fra programvaresiden og hva databasen kan sende ut. Du må velge om containere er riktig for produktet ditt, om en monolit er den bedre måten å håndtere ting på, om du kanskje vil ha en kjernemonolit med flere mikrotjenester rettet mot annen funksjonalitet spredt ut andre steder og hvordan du får tilgang til, samler, behandler og lagrer data.

Maskinvare

like viktig som din generelle struktur, er maskinvaren din en nøkkelkomponent i databasens ytelse. Exoscale tilbyr deg ulike instansalternativer som du kan bruke avhengig av transaksjon og lagringsvolum, samt ønsket responstid.

Det er viktig å bestemme toppperiodene for søknaden din og dermed vite når du skal utelate de langsommere administrative spørringene hvis det er mulig. Disk I / O og nettverksstatistikk må vurderes også når du utformer tidspunktet for databasetransaksjoner og analyser.

Sammendrag

til slutt er her hovedpoengene for langsiktig ytelse oppsummert:

• lag en bærekraftig datamodell som passer til bedriftens behov
• velg riktig databaseform
• bruk en programvarearkitektur som samsvarer med produktet
• gå gjennom regelmessige gjentakelser av å se på strukturen i spørringene dine og brukEXPLAIN på de mer innviklede, optimaliser bruken for den valgte databasen(e), også med hensyn til databaseoppdateringer og hvordan de kan påvirke deg
• velg de instansene som passer best til din søknad og databasebehov i samsvar med ytelse og båndbredde