MySQL
MySQL Prestatietips

Selecteer Statement-optimalisatie

Hieronder staan enkele tips om te onthouden tijdens het schrijven van een selecte vraag in MySQL die ons kunnen helpen en onze zoektijd kunnen verkorten: -

1. Wanneer we waar in een grote tabel gebruiken, moeten we ervoor zorgen dat de kolom waarin clausule index is of niet. Vb: - Selecteer * van werknemer waarbij user_id> 2000. user_id indien geïndexeerd, dan zal de evaluatie van het query-atlot versnellen. Indexen zijn ook erg belangrijk tijdens joins en externe sleutels.

2. Als u het kleinere gedeelte met inhoud nodig hebt in plaats van hele gegevens uit de tabel op te halen, probeer dan limiet te gebruiken. In plaats van Ex te schrijven: - Selecteer * van medewerker. Als u alleen eerst 20 werknemers van Lakhs nodig hebt, gebruik dan gewoon limiet Ex: - Selecteer * van werknemer LIMIT 20.

3. U kunt uw zoekopdracht ook optimaliseren door de kolomnaam op te geven die u in de resultatenset wilt. In plaats van Ex te schrijven: - Selecteer * van medewerker. Vermeld gewoon de kolomnaam waarvan u gegevens nodig hebt als uw tabel veel kolommen bevat en u voor weinig gegevens wilt hebben. Vb: - Selecteer id, naam van werknemer.

4. Indexkolom als u gebruikt om te controleren op NULL in waar clausule. Als je een statement hebt als SELECT * FROM tbl_name WHERE key_col IS NULL; als key_col wordt geïndexeerd, wordt de zoekopdracht sneller geëvalueerd.

Opslagindeling optimaliseren voor InnoDB-tabellen

1. In InnoDB verspilt het hebben van een lange PRIMAIRE KEY (ofwel een enkele kolom met een lange waarde, of meerdere kolommen die een lange samengestelde waarde vormen) veel schijfruimte. De primaire sleutelwaarde voor een rij wordt gedupliceerd in alle secundaire indexrecords die naar dezelfde rij verwijzen. Maak een kolom AUTO_INCREMENT als de primaire sleutel als uw primaire sleutel lang is.
2. Gebruik het gegevenstype VARCHAR in plaats van CHAR om tekenreeksen met variabele lengte op te slaan of voor kolommen met veel NULL-waarden. Een kolom CHAR (N) heeft altijd N tekens nodig om gegevens op te slaan, zelfs als de tekenreeks korter is of de waarde NULL is. Kleinere tabellen passen beter in de bufferpool en verminderen schijf-I / O.

Bij gebruik van de COMPACTE rij-indeling (de standaard InnoDB-indeling) en tekensets met variabele lengte, zoals utf8 of sjis, nemen CHAR (N) -kolommen een variabele hoeveelheid ruimte in, maar nog steeds ten minste N bytes.

3. Voor grote tabellen of veel repetitieve tekst of numerieke gegevens, overweeg het gebruik van de COMPRESSED-rijindeling. Er is minder schijf-I / O nodig om gegevens in de bufferpool te brengen of om volledige scans van de tabel uit te voeren. Voordat u een definitieve beslissing neemt, meet u de hoeveelheid compressie die u kunt bereiken met behulp van COMPRESSED versus COMPACT rijformaat. Voorbehoud: Benchmarks laten zelden een betere compressie zien dan 2: 1 en er is veel overhead in de bufferpool voor COMPRESSED.
4. Zodra uw gegevens een stabiele grootte hebben bereikt, of als een groeiende tafel met tientallen of enkele honderden megabytes is toegenomen, kunt u overwegen de instructie OPTIMIZE TABLE te gebruiken om de tabel te reorganiseren en verspilde ruimte te comprimeren. Voor de gereorganiseerde tabellen is minder schijf-I / O nodig om volledige scans uit te voeren. Dit is een eenvoudige techniek die de prestaties kan verbeteren wanneer andere technieken, zoals het verbeteren van het indexgebruik of het afstemmen van toepassingscode, niet praktisch zijn. Voorbehoud : ongeacht de tabelgrootte, moet de OPTIMALISATIETAFEL slechts zelden worden uitgevoerd. Dit komt omdat het duur is en de tafel zelden genoeg verbetert om het waard te zijn. InnoDB is redelijk goed in het vrijhouden van zijn B + Trees van veel verspilde ruimte.

OPTIMIZE TABLE kopieert het gegevensgedeelte van de tabel en bouwt de indexen opnieuw op. De voordelen zijn afkomstig van een verbeterde gegevensverzameling binnen indexen en een verminderde fragmentatie binnen de tabellen en op schijf. De voordelen variëren afhankelijk van de gegevens in elke tabel. Het kan zijn dat er voor sommigen aanzienlijke winsten zijn en niet voor anderen, of dat de winsten in de loop van de tijd afnemen totdat u de volgende tabel optimaliseert. Deze bewerking kan langzaam zijn als de tabel groot is of als de opnieuw samengestelde indexen niet in de bufferpool passen. De eerste run na het toevoegen van veel gegevens aan een tabel is vaak veel langzamer dan latere runs.

Een samengestelde index maken

In veel situaties presteert een samengestelde index beter dan een index met een enkele kolom. Om een optimale samengestelde index te maken, vult u deze in deze volgorde in met kolommen.

• = kolom (men) eerst uit de WHERE clausule. (bijvoorbeeld INDEX(a,b,...) voor WHERE a=12 AND b='xyz' ... )
• IN kolom (men); de optimizer kan mogelijk door de index springen.
• Eén "bereik" (bijv. x BETWEEN 3 AND 9 , name LIKE 'J%' ) Het zal niets gebruiken voorbij de eerste bereikkolom.
• Alle kolommen in GROUP BY , in volgorde
• Alle kolommen in ORDER BY , in volgorde. Werkt alleen als alle ASC of allemaal DESC of u 8.0 gebruikt.

Opmerkingen en uitzonderingen:

• Dupliceer geen kolommen.
• Sla alle gevallen over die niet van toepassing zijn.
• Als je niet alle kolommen van WHERE , hoef je niet verder te gaan naar GROUP BY , etc.
• Er zijn gevallen waarin het handig is om alleen de ORDER BY kolom (men) te indexeren, waarbij WHERE wordt genegeerd.
• "Verberg" een kolom in een functie niet (bijv. DATE(x) = ... kan x niet gebruiken in de index.)
• ' text_col(99) indexering (bijvoorbeeld text_col(99) ) is waarschijnlijk niet nuttig; kan pijn doen.

Meer details en tips .