Een traag scherm bewijst niet dat de database het probleem is, noch dat een index de oplossing is. Dezelfde indruk kan ontstaan door PHP-code, verzadiging van verbindingen, een externe HTTP-aanroep, het serialiseren van responsen, vergrendelingen of een query die te veel gegevens terugstuurt. Weten hoe je trage query's in PHP onderzoekt, betekent bewijs opbouwen voordat je het schema wijzigt of optimalisaties toevoegt die schrijfbewerkingen duurder kunnen maken.
De latentie van de applicatie scheiden van die van de database
Begin met het uitsplitsen van de totale tijd van een verzoek. Leg een herkenbare route of opdracht vast, de start- en eindtijd, de uitgevoerde query's, hun duur en de externe afhankelijkheden. Alleen de gemiddelde tijd meten is niet voldoende: een API kan gezond lijken en toch falen voor bepaalde filters, klanten of diepe pagina's.
Voor elk incident is het zinvol om onderscheid te maken tussen:
- Tijd in PHP: transformatie van collecties, lussen, JSON-serialisatie, documentgeneratie of overmatig geheugengebruik.
- Tijd in de database: duur van elke query, wachten op vergrendelingen, het openen van een verbinding en het aantal overgedragen rijen.
- Tijd in netwerk en afhankelijkheden: externe caches, API's van derden, bestandsopslag, wachtrijen of identiteitsdiensten.
- Wachtrijtijd: verzoeken die wachten op PHP-werkprocessen, beschikbare verbindingen of databaseresources.
Gebruik traceringsgegevens of gestructureerde logbestanden met een verzoek-ID. Een geïsoleerde query van 20 ms kan een probleem van seconden worden als deze honderden keren in dezelfde respons wordt uitgevoerd. Omgekeerd hoeft een query van 500 ms niet de hoofdoorzaak te zijn als het proces meerdere seconden op een externe dienst blijft wachten.
Bewijs verzamelen voordat je de code wijzigt
Leg de geparametriseerde query vast en afzonderlijk daarvan representatieve parameters. Vermijd het vastleggen van geheimen, volledige persoonsgegevens of waarden die niet nodig zijn om het geval te reproduceren. Een zoekopdracht op een veelvoorkomende status gedraagt zich niet hetzelfde als een zoekopdracht op een unieke identificator; alleen het makkelijke geval evalueren leidt tot verkeerde beslissingen.
Het minimale bewijsmateriaal moet bevatten:
- De betrokken route, asynchrone taak of opdracht, plus de frequentie ervan.
- Waargenomen duur, hoge percentielen en het moment waarop dit optreedt.
- SQL, getypeerde parameters en het aantal uitvoeringen per verzoek.
- Teruggestuurde rijen en, waar mogelijk, gelezen of onderzochte rijen.
- Geschatte grootte van de tabellen en verdeling van de gefilterde waarden.
- Gelijktijdigheid, gelijktijdige schrijfbewerkingen en relevante vergrendelingen.
De logs voor trage query's van de database-engine helpen kandidaten te ontdekken, maar vervangen applicatietracering niet: ze geven doorgaans niet aan welk eindpunt een query heeft opgebouwd of hoe vaak die is herhaald. Combineer die informatie in MySQL en PostgreSQL met meetwaarden voor verbindingen, CPU, I/O en wachttijden, zodat je een gebrekkig plan niet verwart met tijdelijk overbelaste infrastructuur.
Patronen opsporen die de kosten vermenigvuldigen
Zoek naar veelvoorkomende patronen voordat je een complexe instructie analyseert. N+1 ontstaat wanneer een lijst de hoofdrijen ophaalt en vervolgens een extra query uitvoert voor elke relatie. Zelfs als elke afzonderlijke query snel is, nemen het aantal heen-en-weerbewegingen naar de database, het planningswerk en de strijd om resources toe met de paginagrootte.
Ook filters met lage selectiviteit, zoals zeer gebruikelijke statussen, functies toegepast op gefilterde kolommen, impliciete typeconversies, zoekopdrachten met een jokerteken aan het begin, sorteringen van grote verzamelingen en paginering met een hoge OFFSET zijn verdacht. Het opvragen van SELECT * kan de overdracht, het geheugen en het leeswerk vergroten, zelfs wanneer het plan al een index gebruikt.
De oplossing is niet altijd één enkele query. Relaties gecontroleerd laden kan een N+1 oplossen, maar eager loading zonder grenzen kan een enorme query of een enorme respons creëren. Bepaal welke relaties de route werkelijk nodig heeft, beperk hun kolommen en meet het effect met de verwachte paginagrootte.
Het uitvoeringsplan interpreteren met echte gegevens
Voer EXPLAIN uit om het voorgestelde plan te kennen en gebruik de variant die de werkelijke uitvoering bevat wanneer die veilig en geschikt is voor de omgeving. In PostgreSQL voert EXPLAIN ANALYZE de query uit; een wijzigingsinstructie mag niet zo worden geanalyseerd zonder het effect ervan te begrijpen. In MySQL hangen de beschikbare modi af van de versie en configuratie, maar het doel is hetzelfde: schattingen vergelijken met werkelijk werk.
Een sequentiële scan is niet automatisch slecht. Als de query een groot deel van een kleine of weinig selectieve tabel nodig heeft, kan die doorlopen minder kosten dan wisselen tussen index en tabel. Onderzoek daarentegen wanneer het plan veel meer werkelijke dan geschatte rijen toont, dure sorteringen, tijdelijke leesbewerkingen, joins op grote verzamelingen of interne lussen die vaak worden uitgevoerd.
Handige vragen bij het beoordelen van een plan
- Hoeveel rijen verwachtte de optimizer en hoeveel heeft deze werkelijk verwerkt?
- Welk knooppunt concentreert de meeste tijd, leesbewerkingen of iteraties?
- Wordt het filter vroeg toegepast of na het combineren van grote verzamelingen?
- Vindt de sortering plaats op meer rijen dan de respons nodig heeft?
- Weerspiegelen de statistieken de huidige gegevensverdeling?
Verschillen tussen schatting en werkelijkheid kunnen vereisen dat je statistieken bijwerkt of typen en voorwaarden controleert, niet dat je onmiddellijk een index maakt. Het plan is een verklaring van een uitvoering onder bepaalde parameters en belasting; geen automatische opdracht tot wijziging.
Kiezen tussen herschrijven, paginering, datatoegang en index
Verminder eerst het onvermijdelijke werk. Selecteer alleen benodigde kolommen, pas redelijke limieten toe, verwijder ongebruikte relaties en vermijd het naar PHP overbrengen van volledige geschiedenissen om ze daarna te filteren. Als het nodig is een groeiende historie te verkennen, vervang diepe paginering dan door paginering op basis van een cursor of sleutel: ga bijvoorbeeld verder vanaf een stabiele combinatie van datum en identificator in plaats van duizenden rijen over te slaan met OFFSET.
Controleer joins en filters zodat ze compatibele kolommen vergelijken en de voorwaarde duidelijk uitdrukken. Soms is het zinvol een relatie in blokken op te vragen; in andere gevallen heeft één goed afgebakende query de voorkeur. De beslissing hangt af van kardinaliteit, antwoordvolume en frequentie, niet van een universele regel.
Een samengestelde index helpt wanneer deze overeenkomt met het toegangspatroon. De volgorde is belangrijk: normaal gesproken moeten gelijkheids- en selectieve kolommen het filteren vergemakkelijken vóór de kolommen die voor bereik of sortering worden gebruikt, maar de concrete query en de database-engine bepalen het resultaat. Een index kan ook helpen een sortering te vermijden als deze een compatibele volgorde dekt, hoewel niet elke combinatie van WHERE en ORDER BY dit toelaat.
Vermijd het indexeren van kolommen alleen omdat ze in een voorwaarde voorkomen. Indexen nemen ruimte in, verbruiken geheugen en voegen werk toe aan INSERT, UPDATE en DELETE. Redundante indexen of indexen met weinig nut kunnen een systeem met veel schrijfbewerkingen verslechteren. Controleer ook of de benodigde kolommen via de index zonder extra leesbewerkingen kunnen worden verkregen, maar voeg geen dekkende kolommen toe zonder de kosten te meten.
Hypothetisch voorbeeld: een historie van bewerkingen
Stel een lijst voor die bewerkingen per account, status en datum toont. Naarmate de historie groeit, verslechtert pagina 200. De eerste hypothese zou kunnen zijn om een index op de datum te maken. De tracering onthult echter een hoofdquery gevolgd door een query voor elke bewerking om de verantwoordelijke gebruiker op te halen: er is een N+1. Het plan van de hoofdquery leest bovendien veel rijen om eerdere rijen via OFFSET weg te gooien.
De logische volgorde zou zijn om de verantwoordelijken gegroepeerd te laden of via een join beperkt tot de benodigde kolommen, diepe paginering te vervangen door een cursor gebaseerd op created_at en id, en opnieuw te meten. Pas daarna beoordeel je een index die aansluit op het accountfilter, de stabiele sortering en de cursor. De wijziging van datatoegang kan meer werk verminderen dan een geïsoleerde index, en bij de uiteindelijke index moet ook de invloed op het aanmaken van nieuwe bewerkingen worden beoordeeld.
Valideren onder belasting en regressies in schrijfbewerkingen bewaken
Vergelijk voor en na met representatieve parameters, gegevensverdeling en gelijktijdigheid. Meet duur, verwerkte rijen, leesbewerkingen, CPU-gebruik, geheugen in PHP, de grootte van de respons en het aantal query's per verzoek. Meet voor een nieuwe index ook de latentie en capaciteit van de betrokken schrijfbewerkingen.
Definieer expliciete acceptatiegrenzen: bijvoorbeeld een verifieerbare verlaging van het hoge percentiel van de route zonder de aanmaak- of bijwerktijd onaanvaardbaar te verhogen. Test lege gevallen, zeer frequente waarden, zeldzame filters, eerste en laatste pagina's en rechten die het bereik van de gegevens veranderen.
Wijzigingen monitorbaar en omkeerbaar uitrollen
Scheid, waar mogelijk, het uitrollen van de code van het aanmaken van de index. Het aanmaken van indexen kan concurreren om resources of vergrendelingen verkrijgen, afhankelijk van de database-engine, de bewerking en de omgeving. Plan het moment, controleer de methode die door je database wordt ondersteund en bewaak de duur, fouten en druk op I/O.
Voer de nieuwe query geleidelijk in als de architectuur dit toelaat en behoud een duidelijke mogelijkheid tot terugdraaien: herstel de vorige query, schakel een alternatief toegangspad uit of verwijder een index die schadelijk blijkt te zijn. Terugdraaien vervangt back-ups of de beoordeling van migraties niet, maar verkort de blootstellingsduur bij onverwacht gedrag.
Checklist voor een herhaalbare verbetering
- Identificeer de route, het symptoom en de parameters die het reproduceren.
- Scheid tijd in PHP, database, netwerk en externe afhankelijkheden.
- Kwantificeer herhalingen, teruggestuurde rijen en verwerkte rijen.
- Zoek naar N+1, diepe paginering, weinig selectieve filters en sorteringen.
- Beoordeel het plan en toets de schattingen aan de werkelijke uitvoering.
- Probeer gegevensreductie, herschrijven en een wijziging van paginering voordat je indexeert.
- Ontwerp de index volgens het volledige patroon van filteren, sorteren en schrijfbewerkingen.
- Valideer lees- en schrijfbewerkingen met representatieve belasting, monitoring en de mogelijkheid tot terugdraaien.
