Een zoekopdracht die vorig jaar milliseconden duurde, kan vandaag minuten duren, simpelweg omdat de tabel groter werd. SQL-queryoptimalisatie gaat niet zozeer over slimme trucs, maar meer over een herhaalbaar proces: ontdek wat de database doet, verwijder het verspilde werk en controleer of het heeft geholpen.

We hebben elke onderstaande techniek getest op een miljoen gegenereerde rijen in SQLite 3.50. De timing is de beste van meerdere runs vanaf één laptop, waarbij gebruik wordt gemaakt van een database in het geheugen. Ze passen niet bij uw systeem en PostgreSQL, MySQL en SQL Server hebben elk hun eigen optimizers en planformaten. Concentreer u op de omvang van elke verbetering en op de principes erachter.

Resultaten in één oogopslag

De testopstelling

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

Een eenvoudig optimalisatieproces

1. Vind de zoekopdrachten die er toe doen. Gebruik trage zoekopdrachtlogboeken of monitoring. Een zoekopdracht van 200 ms die duizend keer per minuut wordt uitgevoerd, kan er meer toe doen dan een maandelijks rapport van 10 seconden.
2. Lees het plan. Kijk wat de database werkelijk doet.
3. Verander één ding. Voeg een index toe of herschrijf een deel van de query.
4. Meet opnieuw op basis van realistische gegevens. Een plan op 100 testrijen vertelt je heel weinig over een miljoen.
5. Bekijk het in de loop van de tijd. Plannen kunnen veranderen naarmate de gegevens toenemen en de statistieken veranderen.

Lees een uitvoeringsplan

Een uitvoeringsplan is de stapsgewijze strategie van de database voor het uitvoeren van een query, inclusief welke indexen de database gebruikt en in welke volgorde de database aan tabellen wordt toegevoegd. Voeg een voorvoegsel aan een query toe om deze te zien:

Merk op dat EXPLAIN ANALYZE in PostgreSQL de query daadwerkelijk uitvoert, dus wees voorzichtig als je deze gebruikt met UPDATE of DELETE. Hier is waar u eerst op moet letten:

Een volledige scan is niet automatisch slecht. Als een zoekopdracht het grootste deel van een kleine tabel nodig heeft, kan scannen de snelste keuze zijn. Vergelijk in PostgreSQL’s EXPLAIN ANALYZE ook geschatte rijen met werkelijke rijen. Een groot verschil betekent vaak dat tabelstatistieken verouderd zijn.

Voeg de juiste index toe

Met deze zoekopdracht worden de bestellingen van één klant gevonden:

SELECT id, total FROM orders WHERE customer_id = 123;

Voordat een index werd toegevoegd, was het plan een volledige scan:

SCAN orders
-- 20 rows in 73 ms

Vervolgens hebben we een index aan de filterkolom toegevoegd:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

Dezelfde twintig rijen kwamen duizenden keren sneller terug, omdat de database niet langer een miljoen rijen las om ze te vinden. Geen enkele andere techniek in dit artikel heeft ons een grotere overwinning opgeleverd. Goede eerste kandidaten zijn de kolommen die worden gebruikt in WHERE, JOIN en ORDER BY, vooral als ze de resultaten beperken tot een paar rijen. In onze gids voor SQL-indexen wordt uitgelegd hoe ze werken.

Plaats geïndexeerde kolommen niet in functies

Hier zijn twee manieren om bestellingen voor maart te vinden, met een index op created:

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

Beide retourneren identieke rijen, maar de eerste duurde ongeveer drie keer zo lang. Het plan laat zien waarom: met een functie op de kolom kan de database niet naar een positie in de gesorteerde index springen, dus moet hij door elke invoer heen SCAN. De range-versie kan SEARCH meteen op de juiste plaats terechtkomen. Een voorwaarde die op deze manier een index kan gebruiken, wordt sargable genoemd.

Hetzelfde probleem doet zich voor bij deze veelvoorkomende patronen:

Dat laatste hebben we ook gecontroleerd. Er werd een leidende jokerteken geproduceerd SCAN customers, omdat een index gesorteerd vanaf de eerste letter niet kan helpen bij een match aan het eind.

Samengestelde indexen

Wanneer een query op twee kolommen filtert, doet een index met één kolom slechts een deel van de taak:

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

De kolomvolgorde is van groot belang. Een index op (status, created) werkt als een telefoonboek, gesorteerd op achternaam en vervolgens op voornaam. U kunt alleen een achternaam opzoeken, maar niet alleen een voornaam. Alleen al onze zoekopdracht op status zou deze index kunnen gebruiken. Door alleen op created te filteren, kan er doorgaans niet efficiënt gebruik van worden gemaakt. Als vuistregel plaatst u de met gelijkheid geteste kolommen eerst en de bereikkolommen erna.

In dat plan staat ook dat de index wordt gedekt, wat betekent dat de index alles bevatte wat de zoekopdracht nodig had, zodat de tabel zelf niet hoefde te worden gelezen. Door alleen de kolommen te selecteren die je nodig hebt, wordt dit waarschijnlijker, wat nog een reden is om dit te vermijden SELECT *.

Snellere paginering

Het gebruik van OFFSET voor diepe pagina’s brengt verborgen kosten met zich mee, omdat de database nog steeds langs alle overgeslagen rijen moet lopen:

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

Keyset-paginering blijft snel, hoe diep u ook gaat. Het voorkomt ook dat rijen tussen pagina’s verschuiven wanneer nieuwe gegevens worden toegevoegd. Het nadeel is dat gebruikers niet meteen naar pagina 5.000 kunnen springen, wat zelden echt nodig is. Op tafels met brede rijen, of met meer rijen om over te slaan, kan het gat veel groter zijn dan in onze test.

Schrijf slankere query’s

• Selecteer alleen de kolommen die je nodig hebt. SELECT * verzendt meer gegevens en kan voorkomen dat een index de zoekopdracht beantwoordt.
• Vroeg filteren. Gebruik WHERE om rijen te verwijderen vóór het groeperen, en HAVING alleen voor voorwaarden voor aggregaties. Zie SQL GROUP BY en HAVING.
• Gebruik LIMIT als je maar een paar rijen nodig hebt.
• Geef de voorkeur aan UNION ALL als u geen duplicaten wilt verwijderen. Onze test UNION versus UNION ALL laat zien wat dat bespaart.
• Vermijd onnodige DISTINCT. Vaak wordt een join verborgen die duplicaten oplevert.
• Gebruik EXISTS voor bestaanscontroles. Het kan stoppen bij de eerste overeenkomst, zoals uitgelegd in onze gids voor SQL-subquery’s.
• Let op N+1 zoekopdrachten. Eén zoekopdracht per rij in een lus, meestal veroorzaakt door applicatiecode, is vaak het echte probleem. Eén enkele join- of batchquery kan honderden round trips vervangen.

Sluit zich aan

Indexeer de kolommen waaraan u deelneemt. We hebben een rapport over de totale uitgaven gemaakt voor klanten in één stad, en het plan was precies wat u ervan verwachtte:

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

Het scant de klanten en gebruikt vervolgens een index om ieders bestellingen te vinden. Zonder index op orders.customer_id zou de database voor elke klant veel meer werk moeten verzetten. Een index op de externe sleutel is een van de meest voorkomende en waardevolle toevoegingen die u kunt doen. Zie SQL-joins uitgelegd om uw begrip van de basisprincipes op te frissen.

Wanneer indexen u kosten

Indexen zijn niet gratis, dus voeg er niet één toe voor elke kolom:

• Langzamere schrijfbewerkingen. Bij elke invoeging, update en verwijdering moet ook elke index worden bijgewerkt.
• Meer opslagruimte en geheugen. Indexen kunnen groot worden op grote tabellen.
• Weinig voordeel bij niet-selectieve kolommen. Een index op een kolom waar of onwaar is mogelijk niet nuttig als de meeste rijen overeenkomen.
• Redundantie. Een index op (a) is vaak niet nodig als je er al een hebt op (a, b).

Controleer regelmatig de gebruiksstatistieken en verwijder indexen die niets gebruikt. En houd de statistieken actueel met ANALYZE of het equivalent van uw database, omdat de optimalisatie ervan afhankelijk is om een ​​goed plan te kiezen. Soms is het langzame deel een twist in plaats van een vraag. Langdurige transacties die vergrendelingen bevatten, kunnen ervoor zorgen dat een snelle zoekopdracht traag lijkt.

Veel voorkomende fouten

• Optimaliseren zonder te meten. Veel zelfverzekerde gissingen blijken verkeerd te zijn.
• Testen met kleine gegevens. Plannen en timings veranderen dramatisch naarmate de tabellen groter worden.
• Een tip voor een andere database vertrouwen. Advies voor de ene engine is mogelijk niet van toepassing op de andere.
• Het blindelings toevoegen van indexen. Elke index vertraagt ​​het schrijven.
• Het effect van de eerste run wordt genegeerd. Met caches wordt een tweede run sneller uitgevoerd, dus meerdere keren rennen.

Tot slot

De meeste langzame query’s hebben gewone oorzaken: een ontbrekende index, een functie in een kolom of er worden te veel gegevens opgehaald. Neem deze week uw langzaamste echte zoekopdracht, voer EXPLAIN uit en zoek naar een volledige scan op een grote tafel. Corrigeer één ding, meet opnieuw en houd aantekeningen bij van wat u hebt gewijzigd.

Veelgestelde vragen

Blijf leren