Forstå forespørgsler med Forklar

du er i dit nye job som databaseadministrator eller dataingeniør, og du er lige gået tabt ved at prøve at finde ud af, hvad disse vanvittige forespørgsler skal betyde og gøre. Hvorfor er der 5 joinforbindelser, og hvorfor er der en ORDER BY brugt inden for en underforespørgsel, før en af joinforbindelserne endda sker? Husk, du blev ansat af en grund – sandsynligvis har den grund også at gøre med mange indviklede forespørgsler, der blev oprettet og redigeret i løbet af det sidste årti.

artikelomslag

EXPLAIN søgeordet bruges i forskellige databaser og giver oplysninger om, hvordan din database udfører en forespørgsel. EXPLAIN kan bruges foran en forespørgsel, der begynder med SELECTINSERTDELETEREPLACE, og UPDATE. For en simpel forespørgsel ser det ud som følgende:

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

i stedet for det sædvanlige resultatoutput viser Myskl derefter sin udførelsesplan for udsagn ved at forklare, hvilke processer der finder sted i hvilken rækkefølge, når udsagnet udføres.

Bemærk: HvisEXPLAIN ikke virker for dig, har din databasebruger muligvis ikkeSELECT privilegiet for de tabeller eller visninger, du bruger i din erklæring.

EXPLAIN er et godt værktøj til hurtigt at afhjælpe langsomme forespørgsler. Selvom det helt sikkert kan hjælpe dig, fjerner det ikke behovet for strukturel tænkning og et godt overblik over de eksisterende datamodeller. Ofte er den enkleste løsning og hurtigste rådgivning at tilføje et indeks til en bestemt tabels kolonner, hvis de bruges i mange forespørgsler med ydelsesproblemer. Pas på, men brug ikke for mange indekser, da det kan være kontraproduktivt. Læsning af indekset og tabellen giver kun mening, hvis tabellen har en betydelig mængde rækker, og du kun har brug for et par datapunkter. Hvis du henter et stort resultatsæt fra en tabel og ofte spørger forskellige kolonner, giver et indeks på hver kolonne ikke mening og forhindrer ydeevnen mere, end det hjælper. For mere om de faktiske beregninger af indeks vs intet indeks, Læs estimering af ydeevne i den officielle Myskl-dokumentation.

de ting, du vil undgå, hvor det er muligt og relevant, er sortering og beregninger inden for forespørgsler. Hvis du tror, du ikke kan undgå beregninger i dine forespørgsler: ja, du kan. Skriv resultatsættet et andet sted, og beregn dit datapunkt uden for forespørgslen, det vil lægge mindre belastning på databasen og derfor generelt være bedre for din applikation. Bare sørg for at dokumentere, hvorfor du beregner i din ansøgning i stedet for at få et resultat produceret med det samme. Ellers kommer den næste databaseadministrator eller udvikler sammen og har den herlige ide om at bruge en beregning inden for forespørgslen i retning af: “Åh se, min forgænger vidste ikke engang, at du kan gøre det i KVM!”Nogle udviklerteam, der endnu ikke har haft de uundgåelige problemer med at dø databaser, kan bruge beregninger i forespørgslen til talforskelle mellem datoer eller lignende datapunkter.

den generelle tommelfingerregel for forespørgsler er som følger:

Vær præcis og generer kun de resultater, du har brug for.

lad os tjekke en lidt mere kompliceret forespørgsel…

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 output.

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

kolonnerne iEXPLAIN output med dem, der har brug for særlig opmærksomhed for at identificere problemer med fed skrift, er:

id (forespørgsel id)
select_type (type sætning)
tabel (tabel refereret)
type (join type)
possible_keys (hvilke nøgler kunne have været brugt)
nøgle (nøgle, der blev brugt)
key_len (længde af brugt nøgle)
ref (kolonner sammenlignet med indeks)
rækker (antal søgte rækker)
ekstra (yderligere information)

jo højere antal søgte rækker er, desto bedre skal optimeringsniveauet vedrørende indekser og forespørgselspræcision være for at maksimere ydeevnen. Kolonnen ekstra viser mulige handlinger, som du kan fokusere på for at forbedre din forespørgsel, hvis relevant.

Vis advarsler;

hvis forespørgslen, som du brugte med EXPLAIN ikke analyserer korrekt, kan du skrive SHOW WARNINGS; i din forespørgselseditor for at vise oplysninger om den sidste sætning, der blev kørt og ikke var diagnostisk, dvs.den vil ikke vise oplysninger for udsagn som SHOW FULL PROCESSLIST;. Selvom det ikke kan give en ordentlig forespørgselsudførelsesplan som EXPLAIN gør det, kan det give dig tip om de forespørgselsfragmenter, det kunne behandle. Lad os sige, at vi bruger forespørgslen EXPLAIN SELECT * FROM foo WHERE foo.bar = 'infrastructure as a service' OR foo.bar = 'iaas'; på en given database, der faktisk ikke har en tabel foo.

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

Hvis vi skriver SHOW WARNINGS; udgangen er som følger:

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

lad os prøve dette med en bevidst syntaksfejl.

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 advarselsudgang er ret ligetil og vises med det samme som resultatudgang, men for mere komplicerede forespørgsler, der ikke analyserer, er det stadig muligt at se på, hvad der sker i de forespørgselsfragmenter, der kan analyseres. SHOW WARNINGS; inkluderer specielle markører, der kan levere nyttige oplysninger, såsom:

<index_lookup>(query fragment) : et indeksopslag ville ske, hvis forespørgslen var blevet analyseret korrekt
<if>(condition, expr1, expr2): en if-tilstand forekommer i denne specifikke del af forespørgslen
<primary_index_lookup>(query fragment): et indeksopslag ville ske via primærnøgle
<temporary table>: der oprettes en intern tabel her for at gemme midlertidige resultater, for eksempel i underforespørgsler før sammenføjning

for at finde ud af mere om disse specielle markører, Læs udvidet forklar outputformat i den officielle myskl-dokumentation.

den langsigtede løsning

der er flere måder at løse årsagen til dårlig databaseydelse. Det første punkt at se på er datamodellen, dvs. hvordan er dataene struktureret, og bruger du den rigtige database? For mange produkter er en database helt fint. En vigtig ting at huske er altid at adskille adgangslogfilerne fra den almindelige produktionsdatabase, hvilket desværre ikke sker i mange virksomheder. For det meste i disse tilfælde startede et firma i det små, voksede sig større og bruger i det væsentlige stadig den samme database, hvilket betyder, at de får adgang til den samme database til både logningsfunktionalitet såvel som andre transaktioner. Dette reducerer den samlede præstation betydeligt, især da virksomheden bliver større. Derfor er det meget vigtigt at skabe en datamodel, der passer og er bæredygtig.

datamodel

valg af datamodel vil sandsynligvis også afsløre den rigtige form for database(er). Medmindre dit produkt er meget grundlæggende, vil du sandsynligvis have flere databaser til flere brugssager – hvis du har brug for at vise næsten realtidsnumre for adgangslogfiler, vil du sandsynligvis have et meget performant datalager, mens regelmæssige transaktioner kan ske via en database, og du kan have en grafdatabase, der også akkumulerer de relevante datapunkter i begge databaser i en anbefalingsmotor.den samlede produktarkitektur er lige så vigtig som selve databasen, da dårligt design her vil resultere i flaskehalse, der går mod databasen og bremser alt ned både fra programsiden og hvad databasen kan output. Du bliver nødt til at vælge, om containere passer til dit produkt, om en monolit er den bedre måde at håndtere ting på, om du måske vil have en kernemonolit med flere mikroservices, der er målrettet mod anden funktionalitet spredt andre steder, og hvordan du får adgang til, indsamle, behandle og gemme data.

udstyr

lige så vigtigt som din generelle struktur er dit udstyr en nøglekomponent i din databases ydeevne. Eksoskala tilbyder dig forskellige instansindstillinger, som du kan bruge afhængigt af din transaktion og lagringsvolumen samt din ønskede responstid.

det er afgørende at bestemme spidsbelastningsperioderne for din ansøgning og dermed vide, hvornår man skal udelade de langsommere administrative forespørgsler, hvis det er muligt. Disk I/O-og netværksstatistikker skal også overvejes, når du designer tidspunktet for dine databasetransaktioner og analyser.

Resume

afslutningsvis er her hovedpunkterne for langsigtet ydeevne opsummeret:

Opret en bæredygtig datamodel, der passer til din virksomheds behov
vælg den rigtige databaseform
brug en programarkitektur, der matcher dit produkt
gå gennem regelmæssige gentagelser af at se på strukturen i dine forespørgsler og brug EXPLAIN på de mere indviklede, Optimer brugen til din valgte database(er), også med hensyn til databaseopdateringer, og hvordan de kan påvirke dig
vælg de forekomster, der bedst passer til din applikation og databasebehov i overensstemmelse med ydeevne og båndbredde

Maybaygiare.org