Optimalisatie van de bemonsteringssnelheid van gegevens

6.1 Inleiding

Laten we nu van theorie naar praktijk gaan.

We leven in de echte wereld en alle softwareproducten zijn uiteindelijk gemaakt voor levende mensen. En deze levende mensen ergeren zich erg aan sites die langzaam laden en programma's die vertragen.

En als een databasequery meer dan een seconde duurt, is dit onaanvaardbaar . Gebruikers zullen gewoon geen product gebruiken met zo trage pagina's/functionaliteit.

Maar vaak moet u enkele tientallen zoekopdrachten naar de database uitvoeren om één pagina weer te geven. En als ze sequentieel worden uitgevoerd, dan heb je geen tweede limiet meer, maar laten we zeggen 100 ms per verzoek.

Hier zijn de top 5 manieren waarop programmeurs databasequery's versnellen:

1. Indexen toevoegen aan tabellen in de database.
2. Query's herschrijven en optimaliseren.
3. Schakel caching in (en configureer) aan de databasezijde.
4. Schakel caching aan de clientzijde in.
5. Database denormalisatie uitvoeren.

Met al deze dingen ben je grotendeels al bekend, dus het volgende is slechts een praktisch advies.

6.2 Indexen

Het is geen geheim dat het werken met een database het meeste werk van bijna elke site in beslag neemt. En het werken met de database is meestal de bottleneck van webapplicaties.

In dit artikel wil ik graag praktisch advies geven over het gebruik van MySQL.

Ik zal meteen zeggen:

• dit artikel is geschreven over MySQL, hoewel de algemene dingen waarschijnlijk waar zijn voor elk DBMS.
• alles wat in het artikel is geschreven, is mijn persoonlijke mening en is niet de ultieme waarheid.
• advies pretendeert niet nieuw te zijn en is het resultaat van een generalisatie van de gelezen literatuur en persoonlijke ervaring.
• in het kader van dit artikel zal ik MySQL-configuratieproblemen niet bespreken.

Problemen bij het gebruik van MySQL kunnen worden onderverdeeld in de volgende drie groepen (in volgorde van belangrijkheid):

1. Niet-gebruik of misbruik van indexen.
2. Verkeerde databasestructuur.
3. Onjuiste \ suboptimale SQL-query's.

Laten we elk van deze groepen eens nader bekijken.

Indexen gebruiken

Het niet gebruiken of misbruiken van indexen is wat query's het vaakst vertraagt. Voor degenen die niet bekend zijn met het mechanisme van hoe indexen werken of er nog niet over hebben gelezen in de handleiding, raad ik ten zeerste aan om het te lezen.

Tips voor het gebruik van indexen:

• U hoeft niet alles te indexeren . Heel vaak, zonder de betekenis te begrijpen, indexeren mensen gewoon alle velden van een tabel. Indexen versnellen het ophalen, maar vertragen het invoegen en bijwerken van rijen, dus de keuze van elke index moet zinvol zijn.
• Een van de belangrijkste parameters die de index kenmerkt, is selectiviteit, het aantal verschillende elementen in de index. Het heeft geen zin om een ​​veld te indexeren dat twee of drie mogelijke waarden heeft. Er zal weinig voordeel zijn van een dergelijke index.
• De keuze van indexen moet beginnen met een analyse van alle query's tegen een bepaalde tabel. Heel vaak kunt u na zo'n analyse in plaats van drie of vier indices één samengestelde indices maken.
• Bij het gebruik van samengestelde indexen is de volgorde van de velden in de index van cruciaal belang.
• Vergeet indexen niet af te dekken. Als alle gegevens in een query uit een index kunnen worden opgehaald, heeft MySQL niet rechtstreeks toegang tot de tabel. Dergelijke verzoeken zullen zeer snel worden uitgevoerd. Voor een query SELECT name FROM user WHERE login='test'met een index (login, naam) is bijvoorbeeld geen toegang tot de tabel vereist. Soms is het zinvol om een ​​extra veld toe te voegen aan een samengestelde index, waardoor de index dekking krijgt en zoekopdrachten sneller gaan.
• Voor rij-indexen is het vaak voldoende om slechts een deel van de rij te indexeren. Dit kan de indexgrootte aanzienlijk verkleinen.
• Als %het aan het begin staat, LIKE(SELECT * FROM table WHERE field LIKE '%test')worden er geen indexen gebruikt.
• De FULLTEXT- index wordt alleen gebruikt met de MATCH ... AGAINST- syntaxis .

6.3 Databasestructuur

Een goed ontworpen database is de sleutel tot snel en efficiënt werken met de database. Aan de andere kant is een slecht ontworpen database altijd een probleem voor ontwikkelaars.

Tips voor het ontwerpen van databases:

1. Gebruik de kleinst mogelijke datatypes. Hoe groter het gegevenstype, hoe groter de tabel, hoe meer schijftoegang er nodig is om de gegevens te verkrijgen. Gebruik een zeer handige procedure: SELECT * FROM table_name PROCEDURE ANALYSE();om de minimaal mogelijke gegevenstypen te bepalen.
2. Observeer normaalvormen tijdens de ontwerpfase. Vaak nemen programmeurs al in dit stadium hun toevlucht tot denormalisatie. In de meeste gevallen is het aan het begin van het project echter verre van duidelijk hoe dit tot stand kan komen. Het denormaliseren van een tafel is veel gemakkelijker dan lijden aan een suboptimaal gedenormaliseerde tafel. En JOINsoms werkt het sneller dan onjuist gedenormaliseerde tabellen.
3. Gebruik geen NULLkolommen tenzij je ze bewust nodig hebt.

6.4 SQL-query's.

Even vaak bestaat de wens om alle query's in native SQL te herschrijven, zodat de query zo snel mogelijk is. Als u besluit dit te doen, volgen hier enkele tips:

1. Vermijd verzoeken in een lus. SQL is een taal van sets en het schrijven van queries moet niet in de taal van functies worden benaderd, maar in de taal van sets.
2. Vermijd *(sterretjes) in zoekopdrachten. Voel je vrij om precies de velden te vermelden die je kiest. Dit vermindert de hoeveelheid gegevens die wordt opgehaald en verzonden. Vergeet ook niet om indexen af ​​te dekken. Zelfs als u alle velden in de tabel selecteert, is het beter om ze op te sommen. Ten eerste verbetert het de leesbaarheid van de code. Als u sterretjes gebruikt, is het onmogelijk om erachter te komen welke velden in de tabel staan ​​zonder ernaar te kijken. Ten tweede heeft uw tabel vandaag vijf INT- kolommen, en een maand later werden er nog een TEXT en BLOB toegevoegd , en het sterretje bleef zoals het was.
3. Wanneer gepagineerd, om het totale aantal records te krijgen, gebruikt u SQL_CALC_FOUND_ROWSen SELECT FOUND_ROWS();Wanneer gebruikt SQL_CALC_FOUND_ROWS MySQL, wordt het geselecteerde aantal rijen in de cache geplaatst (voordat LIMIT wordt toegepast), en wanneer gebruikt, wordt SELECT FOUND_ROWS()alleen deze waarde in de cache geretourneerd zonder de query opnieuw uit te voeren.
4. Vergeet niet dat er INSERTeen syntax is voor meerdere inserts. Eén query wordt een orde van grootte sneller uitgevoerd dan meerdere query's in een lus.
5. Gebruik LIMITwaar u niet alle gegevens nodig heeft.
6. Gebruik INSERT… ON DUPLICATE KEY UPDATE…in plaats van en INSERTof UPDATEna selectie, en vaak in plaats van REPLACE.
7. Vergeet deze geweldige functie niet GROUP_CONCAT. Het kan helpen bij complexe vragen.