Heading

In dit artikel zullen we ons richten op de makkelijkste en beste tips die ontwikkelaars kunnen implementeren voor hun dagelijkse SQL performance tuning inspanningen. Dit artikel zal zich richten op MySQL queries performance tuning, maar dezelfde concepten kunnen worden toegepast op vele andere relationele databases.

Nu meer dan ooit, moeten software engineers beschikken over uitgebreide kennis in SQL performance tuning.
De verschuiving vindt plaats in zowel kleine startups als grote ondernemingen. Tegenwoordig zijn ontwikkelaars degenen die de SQL queries en de database toegangslaag schrijven.

Het maakt niet echt uit of je een database abstractielaag gebruikt (Hibernate, JOOQ, Entity Framework, Sqlalchemy, Django, of anderen) of native SQL queries schrijft, je zult uiteindelijk worden uitgedaagd met het tunen van de queries die je naar je database stuurt.

Dus wat kun je doen om je SQL queries te optimaliseren?

Maak indexen, maar doe het verstandig

Indexeren is waarschijnlijk het belangrijkste onderdeel van het query tuning proces. Zorg er dus eerst voor dat je bekend bent met de verschillende aspecten die je moet overwegen bij het kiezen van de optimale indexen. voor je database.

Als je nadenkt over welke indexen je moet maken, moet je goed letten op de WHERE-clausule en table JOINs van de query, omdat die statements de kritieke indexeerbare delen van de query bevatten.

Ook kunnen belangrijke knelpunten ontstaan in de GROUP BY- en ORDER BY-gedeelten. Dat gezegd hebbende, een potentieel probleem zal zijn dat je in sommige gevallen niet in staat bent om ze te indexeren, zoals we hier hebben uitgelegd. Daarom moet je misschien het ontwerp van je query heroverwegen voordat je de indexen maakt, om er zeker van te zijn dat je goede queries schrijft, maar ook queries die geïndexeerd kunnen worden.

Als je het indexeren voor één query eenmaal doorhebt, stop daar dan niet mee. Verbreed je blik en kijk naar andere belangrijke queries in je applicatie. Zorg ervoor dat je indexen combineert waar mogelijk, en verwijder indexen die niet worden gebruikt. Het is altijd beter om naar de hele applicatie te kijken dan naar de scope van een enkele query.

Je moet ook in gedachten houden dat het maken van meer indexen dan je nodig hebt ook averechts kan werken, omdat ze schrijfoperaties kunnen vertragen (zoals INSERT / UPDATE statements). Dus maak indexen om de prestaties van uw SQL queries te optimaliseren, maar doe het verstandig.

Ga niet in de weg staan van indexen

We worden veel benaderd door klanten die ons vragen “waarom de database mijn index niet gebruikt?”. Nou, dat is een geweldige vraag, met eindeloos veel mogelijke antwoorden. Maar in dit artikel zullen we proberen om een aantal veel voorkomende scenario’s te behandelen, zodat je ze hopelijk nuttig vindt voor je eigen use case.

Example #1 – Vermijd het omwikkelen van geïndexeerde kolommen met functies

Overweeg deze query, die het aantal hotdogs telt dat in 2018 in de VS is gekocht. Voor het geval je nieuwsgierig bent, er zijn 18.000.000.000 hotdogs verkocht in de VS in 2018.

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE YEAR(purchase_time) = ‘2018’

Zoals je kunt zien, gebruiken we de functie YEAR om het jaardeel uit de kolom purchase_time te halen. Deze functie-aanroep voorkomt dat de database een index kan gebruiken voor het zoeken in de kolom purchase_time, omdat we de waarde purchase_time hebben geïndexeerd, maar niet de return-waarde van YEAR(purchase_time).

Om deze uitdaging te overwinnen en deze SQL query aan te passen, kunt u het resultaat van de functie indexeren, door gebruik te maken van Generated Columns, die beschikbaar zijn vanaf MySQL 5.7.5.

Een andere oplossing kan zijn om een alternatieve manier te vinden om dezelfde query te schrijven, zonder gebruik te maken van de functie-aanroep. In dit voorbeeld kunnen we die voorwaarde omzetten in een 2-weg-bereikvoorwaarde, die dezelfde resultaten zal opleveren:

SELECT COUNT(*)FROM us_hotdog_purchasesWHERE purchased_at >= '2018-01-01' AND purchased_at < '2019-01-01'

Example #2 – vermijd OR-condities

Overweeg deze query, die het aantal berichten op Facebook selecteert dat na nieuwjaar is gepost, of gepost door een gebruiker met de naam Mark.

SELECT COUNT(*)FROM fb_postsWHERE username = ‘Mark’ OR post_time > ‘2018-01-01’

Het hebben van een index op zowel de gebruikersnaam als de post_time kolommen klinkt misschien handig, maar in de meeste gevallen zal de database er geen gebruik van maken, althans niet in zijn geheel. De reden hiervoor is de verbinding tussen de twee voorwaarden – de OR-operator, die ervoor zorgt dat de database de resultaten van elk deel van de voorwaarde afzonderlijk ophaalt.

Een alternatieve manier om naar deze query te kijken kan zijn om de OR-voorwaarde te ‘splitsen’ en te ‘combineren’ met behulp van een UNION-clausule. Met dit alternatief kunt u elk van de voorwaarden afzonderlijk indexeren, zodat de database de indexen gebruikt om naar de resultaten te zoeken en de resultaten vervolgens combineert met de UNION-clausule.

SELECT …FROM …WHERE username = ‘Mark’ UNIONSELECT …FROM …WHERE post_time > ‘2018-01-01’

Als u dubbele records in uw resultaatverzameling niet erg vindt, kunt u ook UNION ALL gebruiken (dat presteert beter dan de standaard UNION DISTINCT).

Voorbeeld #3 – Voorkom sorteren met een gemengde volgorde

Overweeg deze query, die alle berichten van Facebook selecteert en ze sorteert op de gebruikersnaam in een oplopende volgorde, en vervolgens op de datum van het bericht in een aflopende volgorde.

SELECT username, post_typeFROM fb_postsORDER BY username ASC , post_type DESC

MySQL (en veel andere relationele databases), kan geen indexen gebruiken bij sorteren met een gemengde volgorde (zowel ASC als DESC in dezelfde ORDER BY clausule). Dit is veranderd met de release van de omgekeerde indexen functionaliteit en MySQL 8.x.

Dus wat kunt u doen als u nog niet hebt geupgrade naar de laatste MySQL versie? Ten eerste raden we u aan de mixed order sort te heroverwegen. Heeft u het echt nodig? Zo niet, vermijd het.

Dus u hebt besloten dat u het nodig hebt, of uw product manager zei: “We kunnen echt niet zonder”? Een andere optie is om gebruik te maken van Generated columns (beschikbaar op MySQL 5.7.5+) om een omgekeerde kolom te maken en op die kolom te sorteren in plaats van op de originele. Als voorbeeld, stel dat je sorteert op een numerieke kolom, dan kun je een gegenereerde kolom maken met de negatieve numerieke waarde die overeenkomt met het originele getal en sorteer op die nieuwe kolom in de omgekeerde volgorde. Op die manier hebben alle kolommen dezelfde sorteervolgorde in de ORDER BY-clausule, maar wordt er gesorteerd zoals oorspronkelijk in de vereisten voor uw product is vastgelegd.

De laatste mogelijke oplossing is niet altijd een optie, dus uw laatste redmiddel is upgraden naar de nieuwste MySQL-versie die sorteren op gemengde volgorde met behulp van indexen ondersteunt.

Voorbeeld #4 – Vermijd condities met verschillende kolomtypen

Kijk eens naar deze query, die het aantal rode vruchten in een bos selecteert.

SELECT COUNT(*)FROM forestWHERE fruit_color = 5; /* 5 = red */

Aannemende dat het type van de kolom fruit_color VARCHAR is, of gewoon iets niet-numerieks, dan zal indexeren van die kolom niet veel helpen, omdat de vereiste impliciete cast zal voorkomen dat de database de index gebruikt voor het filterproces.

Dus hoe kun je deze SQL query afstemmen? Je hebt twee mogelijkheden om deze query te optimaliseren. De eerste zou zijn om de kolom te vergelijken met een constante waarde die overeenkomt met het type van de kolom, dus als het een VARCHAR kolom is, vergelijk deze dan met ‘5’ (met enkele aanhalingstekens) en niet met 5 (wat een numerieke vergelijking is die zal resulteren in een impliciete cast).

Een betere optie zal zijn om het type van de kolom aan te passen zodat deze overeenkomt met het meest geschikte type voor de waarden die de kolom bevat. In dit voorbeeld moet de kolom worden gewijzigd in een INT type. Houd er rekening mee dat het wijzigen van het type van een kolom een ingewikkelde taak kan zijn, dus lees over de uitdagingen van die taak voordat je eraan begint.

Vermijd LIKE-zoekopdrachten met prefix wildcards

Overweeg deze query, die alle Facebook-posts doorzoekt van een gebruikersnaam die de string ‘Mar’ bevat, dus we zoeken naar alle posts die zijn geschreven door gebruikers met de naam Mark, Marcus, Almar, enzovoort.

SELECT *FROM fb_postsWHERE username LIKE '%Mar%'

Een jokerteken ‘%’ aan het begin van het patroon voorkomt dat de database een index gebruikt voor het zoeken in deze kolom. Dergelijke zoekopdrachten kunnen een tijdje duren.

In dit geval zijn er twee mogelijkheden om de prestaties van deze query te verbeteren. De eerste is triviaal – overweeg of de prefix wildcard belangrijk genoeg is.

Een andere optie is het gebruik van full-text indexen. Merk echter op dat deze indexen en de MATCH … AGAINST syntaxis niet vrij zijn van uitdagingen en een aantal verschillen hebben in vergelijking met de bekende LIKE expressies in MySQL.

Conclusie

In dit eerste deel van onze SQL query optimalisatie serie hebben we het belang van verstandig indexeren behandeld, we hebben een aantal voorbeelden doorgenomen van mogelijke obstakels bij het gebruik van geïndexeerde kolommen in queries, en we hebben ook een aantal andere tips en trucs beschreven die nuttig kunnen zijn voor betere query prestaties. Tot ziens in de volgende post.