Share
Databasebewerkingen zijn vaak de belangrijkste bottleneck voor de meeste webapplicaties van vandaag. Het zijn niet alleen de DBA's (databasebeheerders) die zich zorgen moeten maken over deze prestatieproblemen. Wij als programmeurs moeten ons deel doen door tabellen correct te structureren, geoptimaliseerde zoekopdrachten en betere code te schrijven. In dit artikel zal ik enkele MySQL-optimalisatietechnieken voor programmeurs vermelden.
Voordat we beginnen, moet u weten dat u een heleboel nuttige MySQL-scripts en -hulpprogramma's kunt vinden op Envato Market.
MySQL-scripts en hulpprogramma's op Envato Market De meeste MySQL-servers hebben query-caching ingeschakeld. Het is een van de meest effectieve methoden om de prestaties te verbeteren, die stilletjes wordt afgehandeld door de database-engine. Wanneer dezelfde query meerdere keren wordt uitgevoerd, wordt het resultaat opgehaald uit de cache, wat vrij snel is.
Het grootste probleem is dat het zo gemakkelijk en verborgen is voor de programmeur dat de meesten van ons de neiging hebben het te negeren. Sommige dingen die we doen, kunnen zelfs voorkomen dat de query-cache zijn taak uitvoert.
Accentsconagua
// query cache werkt NIET $ r = mysql_query ("SELECT gebruikersnaam FROM gebruiker WHERE signup_date> = CURDATE ()"); // query cache werkt! $ today = date ("Y-m-d"); $ r = mysql_query ("SELECTEER gebruikersnaam FROM gebruiker WHERE signup_date> = '$ today'"); De reden waarom query-cache niet werkt op de eerste regel is het gebruik van de functie CURDATE (). Dit is van toepassing op alle niet-deterministische functies zoals NOW () en RAND () enz ... Omdat het resultaat van de terugkeer van de functie kan veranderen, besluit MySQL query-caching voor die query uit te schakelen. Alles wat we moesten doen, was een extra regel PHP toevoegen vóór de zoekopdracht om te voorkomen dat dit zou gebeuren.
Het gebruik van het trefwoord EXPLAIN kan u inzicht geven in wat MySQL doet om uw zoekopdracht uit te voeren. Dit kan u helpen de knelpunten en andere problemen met uw query- of tabelstructuren op te sporen.
De resultaten van een EXPLAIN-query laten zien welke indexen worden gebruikt, hoe de tabel wordt gescand en gesorteerd enz ...
Neem een SELECT-query (bij voorkeur een complexe query) en voeg het trefwoord EXPLAIN ervoor toe. U kunt hiervoor gewoon phpmyadmin gebruiken. Het zal je de resultaten laten zien in een leuke tabel. Laten we bijvoorbeeld zeggen dat ik vergat een index toe te voegen aan een kolom, waarop ik joins uitvoer:
Na het toevoegen van de index aan het veld group_id:
In plaats van 7883 rijen te scannen, scant het alleen 9 en 16 rijen van de 2 tabellen. Een goede vuistregel is om alle getallen te vermenigvuldigen onder de kolom "rijen", en de prestaties van uw zoekopdracht zullen enigszins in verhouding staan tot het resulterende aantal.
Soms als u uw tabellen bevraagt, weet u al dat u slechts één rij zoekt. Misschien haalt u een uniek record, of controleert u misschien gewoon het bestaan van een aantal records die voldoen aan uw WHERE-clausule.
In dergelijke gevallen kan het toevoegen van LIMIT 1 aan uw query de prestaties verhogen. Op deze manier stopt de database-engine met zoeken naar records nadat deze slechts 1 heeft gevonden, in plaats van door de hele tabel of index te gaan.
// heb ik gebruikers uit Alabama? // wat NIET te doen: $ r = mysql_query ("SELECT * FROM user WHERE state = 'Alabama'"); if (mysql_num_rows ($ r)> 0) // ... // veel beter: $ r = mysql_query ("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1"); if (mysql_num_rows ($ r)> 0) // ... Indexen zijn niet alleen voor de primaire sleutels of de unieke sleutels. Als er kolommen in uw tabel zijn die u wilt doorzoeken, moet u ze bijna altijd indexeren.
Zoals u kunt zien, is deze regel ook van toepassing op een gedeeltelijke tekenreekszoekopdracht zoals "last_name LIKE 'a%'". Bij het zoeken vanaf het begin van de string kan MySQL de index in die kolom gebruiken.
U moet ook begrijpen welke soorten zoekopdrachten de reguliere indexen niet kunnen gebruiken. Wanneer u bijvoorbeeld naar een woord zoekt (bijvoorbeeld "WHERE post_content LIKE '% apple%'"), ziet u geen voordeel van een normale index. Je zult beter af zijn met het gebruik van MySQL full-text-zoekopdrachten of het bouwen van je eigen indexeringsoplossing.
Als uw toepassing veel JOIN-query's bevat, moet u ervoor zorgen dat de kolommen waaraan u deelneemt aan beide tabellen worden geïndexeerd. Dit is van invloed op hoe MySQL intern de join-bewerking optimaliseert.
De kolommen die worden samengevoegd, moeten ook van hetzelfde type zijn. Als u bijvoorbeeld lid wordt van een DECIMAL-kolom, naar een INT-kolom vanuit een andere tabel, kan MySQL ten minste één van de indexen niet gebruiken. Zelfs de karaktercoderingen moeten van hetzelfde type zijn voor tekenreekskolommen.
// op zoek naar bedrijven in mijn staat $ r = mysql_query ("SELECT bedrijfsnaam VAN gebruikers LINKS JOIN bedrijven AAN (users.state = companies.state) WHERE users.id = $ user_id"); // beide statuskolommen moeten worden geïndexeerd // en ze moeten allebei hetzelfde type en tekencodering zijn // of MySQL kan volledige scans uitvoeren Dit is een van die trucs die in eerste instantie cool klinkt, en veel rookie-programmeurs vallen voor deze val. U zult zich misschien niet realiseren wat voor vreselijk knelpunt u kunt creëren als u dit in uw vragen begint te gebruiken.
Als je echt willekeurige rijen uit je resultaten nodig hebt, zijn er veel betere manieren om het te doen. Toegegeven, er is extra code voor nodig, maar u voorkomt een knelpunt dat exponentieel slechter wordt naarmate uw gegevens groter worden. Het probleem is dat MySQL de bewerking RAND () (die processorkracht vereist) voor elke afzonderlijke rij in de tabel moet uitvoeren voordat deze wordt gesorteerd en u slechts 1 rij krijgt.
// wat NIET te doen: $ r = mysql_query ("SELECT gebruikersnaam VAN gebruiker ORDER BY RAND () LIMIT 1"); // veel beter: $ r = mysql_query ("SELECT aantal (*) VAN gebruiker"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECTEER gebruikersnaam FROM gebruiker LIMIT $ rand, 1"); U kiest dus een willekeurig getal dat kleiner is dan het aantal resultaten en gebruikt dat als de offset in uw LIMIT-clausule.
Hoe meer gegevens er uit de tabellen worden gelezen, hoe langzamer de vraag zal worden. Het verhoogt de tijd die nodig is voor de schijfbewerkingen. Ook wanneer de databaseserver gescheiden is van de webserver, hebt u langere netwerkvertragingen doordat de gegevens tussen de servers moeten worden overgedragen.
Het is een goede gewoonte om altijd op te geven welke kolommen je nodig hebt wanneer je je SELECT's doet.
// niet de voorkeur $ r = mysql_query ("SELECT * FROM user WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Welkom $ d ['gebruikersnaam']"; // beter: $ r = mysql_query ("SELECTEER gebruikersnaam FROM gebruiker WHERE user_id = 1"); $ d = mysql_fetch_assoc ($ r); echo "Welkom $ d ['gebruikersnaam']"; // de verschillen zijn groter met grotere resultaatsets In elke tabel staat een id-kolom die de PRIMAIRE SLEUTEL, AUTO_INCREMENT en een van de smaken van INT is. Ook bij voorkeur UNSIGNED, omdat de waarde niet negatief kan zijn.
Zelfs als u een tabel voor gebruikers heeft met een uniek veld voor de gebruikersnaam, moet u niet uw primaire sleutel zijn. VARCHAR-velden als primaire sleutels zijn langzamer. En u krijgt een betere structuur in uw code door alle gebruikers met hun id's intern te verwijzen.
Er zijn ook operaties achter de schermen uitgevoerd door de MySQL-engine zelf, die intern het primaire sleutelveld gebruikt. Wat nog belangrijker wordt, hoe ingewikkelder de database-instelling is. (clusters, partitionering, enz ...).
Een mogelijke uitzondering op de regel zijn de "associatietabellen", die worden gebruikt voor het veel-op-veel-type associaties tussen twee tabellen. Bijvoorbeeld een tabel met "posts_tags" die twee kolommen bevat: post_id, tag_id, die wordt gebruikt voor de relaties tussen twee tabellen met de naam "post" en "tags". Deze tabellen kunnen een PRIMAIRE sleutel hebben die beide ID-velden bevat.
ENUM-type kolommen zijn erg snel en compact. Intern worden ze opgeslagen zoals TINYINT, maar ze kunnen stringwaarden bevatten en weergeven. Dit maakt ze een perfecte kandidaat voor bepaalde velden.
Als u een veld hebt dat slechts een paar verschillende soorten waarden bevat, gebruikt u ENUM in plaats van VARCHAR. Het kan bijvoorbeeld een kolom zijn met de naam 'status' en alleen waarden bevatten zoals 'actief', 'inactief', 'in behandeling', 'verlopen' enzovoort ...
Er is zelfs een manier om een "suggestie" van MySQL zelf te krijgen over hoe je je tafel kunt herstructureren. Wanneer u een VARCHAR-veld hebt, kan het u eigenlijk aanraden om dat kolomtype in plaats daarvan te wijzigen in ENUM. Dit is gedaan met de aanroep PROCEDURE ANALYSE (). Wat ons brengt om:
PROCEDURE ANALYSE () laat MySQL de kolommenstructuren analyseren en de feitelijke gegevens in uw tabel om met bepaalde suggesties voor u te komen. Het is alleen nuttig als er echte gegevens in uw tabellen staan, omdat dat een grote rol speelt in de besluitvorming.
Als u bijvoorbeeld een INT-veld voor uw primaire sleutel hebt gemaakt, maar niet te veel rijen hebt, zou het kunnen zijn dat u in de plaats daarvan een MIDDELSTUK gebruikt. Of als u een VARCHAR-veld gebruikt, krijgt u mogelijk een suggestie om het naar ENUM te converteren, als er maar weinig unieke waarden zijn.
U kunt dit ook doen door te klikken op de link "Tabelstructuur voorstellen" in phpmyadmin, in een van uw tabelaanzichten.
Houd er rekening mee dat dit alleen maar suggesties zijn. En als je tafel groter wordt, zijn ze misschien niet eens de juiste suggesties om te volgen. De beslissing is uiteindelijk van jou.
Tenzij u een zeer specifieke reden hebt om een NULL-waarde te gebruiken, moet u uw kolommen altijd instellen als NOT NULL.
Vraag jezelf eerst af of er een verschil is tussen een lege tekenreekswaarde en een NULL-waarde (voor INT-velden: 0 versus NULL). Als er geen reden is om beide te gebruiken, hebt u geen veld NULL nodig. (Wist je dat Oracle NULL en lege tekenreeks als hetzelfde beschouwt?)
NULL-kolommen vereisen extra ruimte en ze kunnen complexiteit toevoegen aan uw vergelijkingsinstructies. Vermijd ze gewoon wanneer je kunt. Ik begrijp echter dat sommige mensen zeer specifieke redenen hebben om NULL-waarden te hebben, wat niet altijd een slechte zaak is.
Van MySQL-documenten:
"NULL-kolommen vereisen extra ruimte in de rij om te registreren of hun waarden NULL zijn.Voor MyISAM-tabellen neemt elke NULL-kolom een bit extra, afgerond naar de dichtstbijzijnde byte."
Er zijn meerdere voordelen aan het gebruik van voorbereide instructies, zowel om redenen van prestaties als om veiligheidsredenen.
Prepared Statements filteren de variabelen die u standaard aan hen koppelt, wat geweldig is om uw applicatie te beschermen tegen SQL-injectie-aanvallen. Je kunt natuurlijk ook je variabelen handmatig filteren, maar die methoden zijn meer vatbaar voor menselijke fouten en vergeetachtigheid door de programmeur. Dit is minder een probleem bij het gebruik van een soort framework of ORM.
Aangezien onze focus ligt op prestaties, moet ik ook de voordelen op dat gebied noemen. Deze voordelen zijn belangrijker wanneer dezelfde query meerdere keren wordt gebruikt in uw toepassing. U kunt verschillende waarden toewijzen aan dezelfde voorbereide instructie, maar MySQL hoeft het maar één keer te ontleden.
De nieuwste versies van MySQL verzenden voorbereide instructies in een native binaire vorm, die efficiënter zijn en ook kunnen helpen vertragingen in het netwerk te verminderen.
Er was een tijd dat veel programmeurs om voorbereidende redenen bewuste uitspraken vermeden, om één enkele belangrijke reden. Ze werden niet in de cache opgeslagen door de MySQL-querycache. Maar sinds enige tijd rond versie 5.1 wordt query-caching ook ondersteund.
Als u voorbereide instructies in PHP wilt gebruiken, bekijkt u de mysqli-extensie of gebruikt u een database-abstractielaag zoals PDO.
// maak een voorbereide instructie als ($ stmt = $ mysqli-> prepare ("SELECT gebruikersnaam FROM gebruiker WHERE state =?")) // bind parameters $ stmt-> bind_param ("s", $ state); // voer $ stmt-> execute () uit; // bind resultaatvariabelen $ stmt-> bind_result ($ gebruikersnaam); // haal waarde $ stmt-> fetch () op; printf ("% s is van% s \ n", $ gebruikersnaam, $ staat); $ Stmt-> close (); Als u een query uitvoert vanuit een script, wacht deze normaal gesproken totdat de uitvoering van die query is voltooid voordat deze kan worden voortgezet. U kunt dit wijzigen door niet-gebufferde query's te gebruiken.
Er is een goede uitleg in de PHP-documenten voor de functie mysql_unbuffered_query ():
"mysql_unbuffered_query () verzendt de SQL-query naar MySQL zonder de resultaatregels automatisch op te halen en te bufferen zoals mysql_query (). Hiermee bespaart u een aanzienlijke hoeveelheid geheugen met SQL-query's die grote resultatensets produceren en kunt u aan de resultaatset gaan werken onmiddellijk nadat de eerste rij is opgehaald, omdat u niet hoeft te wachten totdat de volledige SQL-query is uitgevoerd. "
Er zijn echter bepaalde beperkingen. U moet alle rijen lezen of mysql_free_result () aanroepen voordat u een nieuwe query kunt uitvoeren. Het is ook niet toegestaan om mysql_num_rows () of mysql_data_seek () te gebruiken in de resultatenset.
Veel programmeurs maken een VARCHAR (15) -veld zonder te beseffen dat ze IP-adressen kunnen opslaan als integerwaarden. Met een INT ga je naar slechts 4 bytes ruimte en in plaats daarvan heb je een veld met een vaste grootte.
U moet ervoor zorgen dat uw kolom een UNSIGNED INT is, omdat IP-adressen het hele bereik van een 32-bits niet-ondertekend geheel getal gebruiken.
In uw query's kunt u de INET_ATON () gebruiken om te converteren en IP naar een geheel getal en INET_NTOA () voor andersom. Er zijn ook soortgelijke functies in PHP genaamd ip2long () en long2ip ().
$ r = "Gebruikers UPDATE SET ip = INET_ATON ('$ _ SERVER [' REMOTE_ADDR ']' WHERE user_id = $ user_id"; Wanneer elke afzonderlijke kolom in een tabel "vaste lengte" heeft, wordt de tabel ook beschouwd als "statisch" of "vaste lengte". Voorbeelden van kolomtypen die NIET een vaste lengte hebben, zijn: VARCHAR, TEXT, BLOB. Als u zelfs slechts één van deze typen kolommen opneemt, is de tabel niet langer van vaste lengte en moet deze anders worden behandeld door de MySQL-engine.
Tabellen met een vaste lengte kunnen de prestaties verbeteren omdat het sneller is voor de MySQL-engine om door de records te zoeken. Wanneer het een specifieke rij in een tabel wil lezen, kan het snel de positie ervan berekenen. Als de rijgrootte niet is vastgesteld, moet deze telkens wanneer deze een zoekopdracht moet uitvoeren de index van de primaire sleutel raadplegen.
Ze zijn ook gemakkelijker te cachen en gemakkelijker te reconstrueren na een crash. Maar ze kunnen ook meer ruimte innemen. Als u bijvoorbeeld een VARCHAR (20) -veld converteert naar een CHAR-veld (20), heeft het altijd 20 bytes ruimte nodig, ongeacht waar het zich bevindt.
Door de "Vertical Partitioning" -technieken te gebruiken, kunt u de kolommen met variabele lengte scheiden in een aparte tabel. Wat ons brengt om:
Verticaal partitioneren is het splitsen van uw tabelstructuur op een verticale manier om redenen van optimalisatie.
voorbeeld 1: Mogelijk hebt u een tabel met gebruikers die thuisadressen bevat, die niet vaak worden gelezen. U kunt ervoor kiezen om uw tabel te splitsen en de adresinformatie op te slaan op een aparte tafel. Op deze manier krimpt uw tabel met hoofdgebruikers in omvang. Zoals u weet, presteren kleinere tabellen sneller.
Voorbeeld 2: U hebt een "last_login" -veld in uw tabel. Het wordt elke keer bijgewerkt als een gebruiker zich aanmeldt bij de website. Maar elke update van een tabel zorgt ervoor dat de query-cache voor die tabel wordt leeggemaakt. U kunt dat veld in een andere tabel plaatsen om updates voor uw gebruikerstabel tot een minimum te beperken.
Maar u moet er ook voor zorgen dat u na de partitionering niet steeds aan deze 2 tabellen hoeft deel te nemen, anders loopt u mogelijk een achteruitgang in de prestaties.
Als u een grote DELETE- of INSERT-query op een live website moet uitvoeren, moet u erop letten het internetverkeer niet te storen. Wanneer een dergelijke grote zoekopdracht wordt uitgevoerd, kan deze uw tabellen vergrendelen en uw webtoepassing tot stilstand brengen.
Apache voert veel parallelle processen / threads uit. Daarom werkt het het meest efficiënt wanneer scripts zo snel mogelijk worden uitgevoerd, zodat de servers niet te veel open verbindingen en processen tegelijk ervaren die bronnen verbruiken, vooral het geheugen.
Als u uiteindelijk uw tabellen vergrendelt voor een langere periode (zoals 30 seconden of meer), zorgt u op een website met veel verkeer voor een proces- en querystackup, waardoor het lang kan duren om uw web te wissen of zelfs te crashen server.
Als u een onderhoudsscript hebt dat grote aantallen rijen moet verwijderen, gebruikt u de LIMIT-component om het in kleinere batches te doen om deze congestie te voorkomen.
while (1) mysql_query ("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000"); if (mysql_affected_rows() == 0) // done deleting break; // you can even pause a bit usleep(50000); Met databasemotoren is schijf misschien de belangrijkste bottleneck. De dingen kleiner en compacter houden, is meestal nuttig in termen van prestaties, om de hoeveelheid schijfoverdracht te verminderen.
MySQL-documenten hebben een lijst met opslagvereisten voor alle gegevenstypen.
Als wordt verwacht dat een tabel zeer weinig rijen bevat, is er geen reden om van de primaire sleutel een INT te maken in plaats van MEDIUMINT, SMALLINT of zelfs in sommige gevallen TINYINT. Als u de tijdcomponent niet nodig hebt, gebruikt u DATE in plaats van DATETIME.
Zorg er wel voor dat je redelijke ruimte laat om te groeien of dat je als Slashdot terechtkomt.
De twee belangrijkste opslagmachines in MySQL zijn MyISAM en InnoDB. Elk heeft zijn eigen voor- en nadelen.
MyISAM is goed voor lees-zware toepassingen, maar het schaalt niet erg goed als er veel wordt geschreven. Zelfs als u één veld van een rij bijwerkt, wordt de hele tabel vergrendeld en kan er geen ander proces van lezen totdat de query is voltooid. MyISAM is erg snel in het berekenen van SELECT COUNT (*) soorten query's.
InnoDB heeft de neiging om een ingewikkelder opslagmechanisme te zijn en kan voor de meeste kleine toepassingen langzamer zijn dan MyISAM. Maar het ondersteunt op rijen gebaseerde vergrendeling, die zich beter aanpast. Het ondersteunt ook enkele geavanceerdere functies zoals transacties.
Door een ORM (Object Relational Mapper) te gebruiken, kunt u bepaalde prestatievoordelen behalen. Alles wat een ORM kan doen, kan ook handmatig worden gecodeerd. Maar dit kan te veel extra werk betekenen en een hoge mate van expertise vereisen.
ORM's zijn geweldig voor "Lazy Loading". Dit betekent dat ze alleen waarden kunnen ophalen als ze nodig zijn. Maar u moet voorzichtig zijn met hen of u kunt uiteindelijk veel miniquery's maken die de prestaties kunnen verminderen.
ORM's kunnen uw vragen ook in transacties verwerken, die veel sneller werken dan het verzenden van individuele query's naar de database.
Momenteel is mijn favoriete ORM voor PHP Doctrine. Ik schreef een artikel over het installeren van Doctrine met CodeIgniter.
Persistente verbindingen zijn bedoeld om de overhead van het maken van verbindingen met MySQL te verminderen. Wanneer een permanente verbinding wordt gemaakt, blijft deze open, zelfs nadat het script is voltooid. Omdat Apache de onderliggende processen hergebruikt, zal het de volgende keer dat het proces voor een nieuw script wordt uitgevoerd, dezelfde MySQL-verbinding opnieuw gebruiken.
Het klinkt geweldig in theorie. Maar vanuit mijn persoonlijke ervaring (en vele anderen), blijken deze functies de moeite niet waard te zijn. U kunt ernstige problemen ondervinden met verbindingslimieten, geheugenproblemen enzovoort.
Apache werkt extreem parallel en maakt veel onderliggende processen. Dit is de belangrijkste reden dat hardnekkige verbindingen niet erg goed werken in deze omgeving. Overweeg voordat u overweegt om de functie mysql_pconnect () te gebruiken uw systeembeheerder.
