MySQL-optimalisatie: indexen, langzame queries, configuratievertaling. Gebruik NOT NULL in plaats van NULL

Over hoe goed de zoekopdrachten naar de database zijn geoptimaliseerd mySQL-gegevens, hangt de mate van belasting van de server, en dus de snelheid waarmee de site wordt geladen, sterk af. Dit zal de serverbelasting helpen verlichten en de laadtijd van uw website verkorten. mySQL-optimalisatie verzoeken.

Waarom databasequery's optimaliseren?

Eigenaars van websites die worden beheerd door zelfgeschreven administratiesystemen moeten eenvoudigweg goed begrijpen welke zoekopdrachten naar de database snel en eenvoudig worden uitgevoerd, en die de belasting van de server aanzienlijk verhogen en de laadsnelheid van de site aanzienlijk vertragen.

Het zou de webmasters die dit gebruiken niet schaden bekende systemen administratie en koppelt graag allerlei plug-ins externe ontwikkelaars, en pas thema's voor uzelf aan, bijvoorbeeld op het populairste gratis CMS - WordPress.

Sommige acties kunnen worden uitgevoerd op verschillende manieren U kunt bijvoorbeeld het aantal gevonden records in een tabel tellen met behulp van de functie mysql_num_rows (maar dit wordt niet aanbevolen), of u kunt ook de constructie SELECT COUNT() gebruiken. We hebben persoonlijk een onderzoek uitgevoerd waarbij we een enorme gegevenstabel hebben gemaakt met enkele honderdduizenden records en een gewicht van meer dan één gigabyte, en vervolgens hebben geprobeerd het aantal rijen te tellen met behulp van de opgegeven methoden.

Het resultaat was met het blote oog zichtbaar, want bij gebruik van mysql_num_rows bevroor de pagina 5 seconden, waarna het resultaat werd weergegeven. In het tweede geval ontvingen we vrijwel onmiddellijk het resultaat in de vorm van het aantal records in de tabel. We hoefden de laadtijd van het script niet eens te meten met een microtimer, omdat het resultaat meer dan duidelijk was.

Hetzelfde geldt voor andere ontwerpen. Sommige databasebewerkingen kunnen op twee, drie, vier of meer manieren worden uitgevoerd, en elk ervan zal qua snelheid verschillen, terwijl het resultaat in alle gevallen even correct zal zijn.

Databasequery's optimaliseren

Om precies te begrijpen hoe u zoekopdrachten kunt optimaliseren en welke constructies sneller en welke langzamer werken, zullen we opnieuw een klein experiment uitvoeren en dat nu meteen doen.

We zullen voor hulp de interface van de populaire en zeer handige phpmyadmin moeten gebruiken. Om aan de slag te gaan, moeten we een van de beschikbare databases selecteren en daarin een testtabel maken. De naam zal in ons geval nogal banaal zijn - test.

MAAK TABEL `test` (`ID` INT NOT NULL AUTO_INCREMENT , `TITLE` VARCHAR(100) KARAKTERSET utf8 COLLATE utf8_unicode_ci NOT NULL , `ANNOUNCEMENT` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL , `TEXT` TEKSTSET utf8 COLLATE utf8_unicode _ci NIET NULL, PRIMAIRE SLEUTEL (`ID`)) ENGINE = MYISAM;

Nu we al een testtabel hebben, moeten we deze vullen met abstracte gegevens. Zoals je kunt zien aan de structuur van de tabel die we zojuist hebben gemaakt, hebben we de volgende gegevens nodig om in te vullen:

Rubriek
Aankondiging
Volledige tekst

Voor abstracte teksten gaan we uit gewoonte naar de service Yandex.Abstracts, speciaal gemaakt voor dergelijke doeleinden. We hadden het geluk om het onderwerp “Torsiefoton in de 21e eeuw” tegen te komen, dus laten we het maar aannemen.

We gaven een willekeurig geselecteerd onderwerp als titel aan, namen een middelmatige paragraaf tekst als aankondiging, en volledige tekst artikel hebben we een tekst van 4000 tekens lang met spaties. Om het aantal tekens in de tekst te tellen, hebben we onze eigen service gebruikt en we raden u aan dit daar te tellen, omdat er is een optie om al dan niet rekening te houden met spaties.

We zullen het resulterende verzoek hier niet kopiëren, omdat het meer dan 4000 tekens aan niet-unieke tekst zal zijn, afkomstig van Yandex zelf, wat behoorlijk gedurfd is, en je hebt het ook niet nodig. Het is beter om een eenvoudige lus in PHP te schrijven die snel zoveel records aan de database toevoegt als we willen. Om te beginnen zullen het 100.000 artikelen zijn.

Hoe minder databasequery's, hoe beter

Al in dit stadium zullen we u een veel voorkomende fout laten zien, die we nu zelf bewust zullen maken.

For($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }

Als verzoek hebben we de uit phpmyadmin gekopieerde code geplakt, die op het scherm werd weergegeven nadat het eerste artikel één keer handmatig was toegevoegd. Ik wil er meteen op wijzen dat u op deze manier geen queries naar de database moet bouwen. We hebben dit alleen gedaan omdat we de tabel snel moesten vullen met willekeurige gegevens, en deze query wordt sneller geschreven dan de query die optimaal is. In deze cyclus eindigden we met 99999 afzonderlijke databaseoproepen (de eerste deden we handmatig vanuit phpmyadmin), wat een zeer slechte vorm is.

Een veel correctere oplossing zou zijn om dezelfde bewerking uit te voeren met slechts één oproep naar de database, waarbij alle regels worden vermeld, gescheiden door komma's.

INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) WAARDEN (NULL, "Titel", "Aankondiging", "Volledige tekst"), (NULL, "Titel", "Aankondiging" , "Volledige tekst"), (NULL, "Titel", "Aankondiging", "Volledige tekst"), ...

Als we terugkeren naar onze eerste methode, zou deze er als volgt uitzien:

INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) WAARDEN (NULL, "Titel", "Aankondiging", "Volledige tekst") INSERT INTO `test` (`ID`, ` TITEL`, `ANNOUNCEMENT`, `TEXT`) WAARDEN (NULL, "Titel", "Aankondiging", "Volledige tekst") INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) WAARDEN (NULL, "Titel", "Aankondiging", "Volledige tekst") ...

Voel je het verschil? De optie die slechts één toegang tot de database gebruikt, is optimaal. De werkingssnelheid van deze twee methoden, die tot hetzelfde resultaat leiden, verschilt aanzienlijk en is zonder enige meting met het blote oog zichtbaar, geloof me, dit is echt zo.

Selecteer alleen de velden die vereist zijn door het script

Alles is hier heel eenvoudig: deze of gene functie heeft bepaalde gegevens uit de doeltabel nodig. Heel vaak blijkt dat je alle velden helemaal moet verwijderen, vooral als de tabel behoorlijk groot is en er meer dan 10 van deze velden zijn.

SELECTEER * UIT `test`

In deze query betekent het sterretje dat gegevens uit alle velden van de testtabel worden opgehaald. Wat moet ik doen als er 20-30 of meer van deze velden in de tabel staan? Het script heeft hoogstwaarschijnlijk slechts enkele ervan nodig, en de rest, die op geen enkele manier zal worden gebruikt, zal tevergeefs worden geselecteerd. Deze bewerking zal langzamer zijn dan wanneer u alleen de velden opgeeft die u op dat moment echt nodig heeft, gescheiden door komma's.

SELECTEER `ID`, `TITEL` UIT `test`

In dit voorbeeld zullen we de aankondiging en de volledige tekst van het artikel helemaal niet aanraken, wat het script aanzienlijk zal versnellen. We concluderen dus dat het optimaliseren van databasequery's ook betekent dat de vereiste velden in queries specifiek moeten worden aangegeven en dat de universaliteit in de vorm van een asterisk moet worden opgegeven.

Meerdere verzoeken combineren in één

We hebben al met u besproken dat een goede optimalisatie van het werken met MySQL het gebruik van een zo laag mogelijk aantal queries inhoudt en gaven een voorbeeld van het toevoegen van gegevens aan een tabel.

Naast het toevoegen kan en moet deze techniek ook worden gebruikt bij het bemonsteren van gegevens. Laten we nu het eenvoudigste voorbeeld geven. Stel je voor dat je twee tabellen in je database hebt: de eerste tabel bevat informatie over geregistreerde gebruikers en de tweede bevat artikelen die door deze gebruikers zijn geschreven.

Stel dat u een willekeurig artikel op het scherm moet weergeven en het moet ondertekenen met de naam van de auteur onderaan. De verbinding tussen de tabellen is in dit geval duidelijk en vindt plaats via gebruikers-ID, dat wil zeggen dat het gebruikers-ID in de gebruikerstabel moet overeenkomen met het veld USER_ID in de berichtentabel. Deze verbinding is standaard en zou voor iedereen, zonder uitzondering, begrijpelijk moeten zijn.

Dus om een willekeurig artikel te selecteren, schrijft u een zoekopdracht als volgt:

$rs_post = mysql_query("SELECT `ID`, `USER_ID`, `TITLE`, `TEXT` FROM `posts` ORDER by RAND() LIMIT 1");

Er wordt willekeurig één artikel geselecteerd uit de berichtentabel. Daarna zullen onze acties er ongeveer zo uitzien:

$row_post = mysql_fetch_assoc($rs_post); $userID = $row_post["USER_ID"];

Nu bevat de variabele $userID de gebruikers-ID van de gebruiker die de auteur van dit artikel is en om zijn gegevens op te halen, bijvoorbeeld NAME (voornaam) en SURNAME (achternaam), krijgt u toegang tot de gebruikerstabel en de query zal er ongeveer zo uitzien:

$rs_user = mysql_query("SELECT `NAAM`, `ACHTERNAAM` VAN `gebruikers` WAAR `ID` = "".$row_post["USER_ID"]."" LIMIET 1");

Vergeet trouwens niet om variabelen in verzoeken tussen enkele aanhalingstekens te plaatsen, vooral als de gegevens van buitenaf komen, met behulp van GET of POST. Dit zal een extra obstakel vormen voor aanvallers en is een van de maatregelen gericht op bescherming tegen SQL-injecties. Laten we dus teruggaan naar ons voorbeeld. Nadat het verzoek aan de database is gedaan, is alles eenvoudig: we krijgen de voor- en achternaam en geven deze weer als handtekening bij het artikel. De taak is voltooid.

Maar deze twee query's kunnen worden geoptimaliseerd tot één. Om dit te doen, gebruiken we de LEFT JOIN-constructie:

SELECT `posts`.`ID`, `posts`.`USER_ID`, `posts`.`TITLE`, `posts`.`TEXT`, `users`.`NAME`, `users`.`SURNAME` FROM ` posts` LINKS JOIN `users` ON ``posts`.`USER_ID` = `users`.`ID` ORDER by RAND() LIMIT 1

Zoals je kunt zien, is er niets ingewikkelds aan het bovenstaande ontwerp en is alles intuïtief. Het enige waar ik uw aandacht op wil vestigen is de expliciete aanduiding van tabellen gekoppeld aan velden, aangezien er sprake is van een selectie uit meerdere tabellen tegelijk. Als de namen van sommige velden samenvallen, moet u zogenaamde mySQL-aliassen gebruiken om later niet in verwarring te raken bij het weergeven van het resultaat.

Conclusie

Zoals u kunt zien, is het mogelijk en noodzakelijk om databasequery's te optimaliseren. Als u denkt dat, omdat alles zo snel voor u werkt, het geen zin heeft om iets te veranderen, wacht dan tot de database van uw site meerdere keren groeit, en daarmee ook het verkeer. Veel verkeer impliceert een frequentere gelijktijdige toegang tot de database, waarvan de omvang ook van invloed is op de snelheid van de bewerkingen.

Slechte zoekoptimalisatie kan iets later worden ontdekt, wanneer de site voldoende is gegroeid, en na verloop van tijd zal het steeds moeilijker worden om wijzigingen aan te brengen, omdat de bestandsgroottes en het aantal functies alleen maar toenemen. Er worden nieuwe functies aan de site toegevoegd die gericht zijn op het gemak van gebruikers. Met andere woorden: als de zaken een bepaald kookpunt bereiken, kun je mogelijk geen oplossingen vinden en zal het enkele dagen of misschien zelfs weken duren om alle oproepen naar de database, verspreid over honderden bestanden, te optimaliseren. Daarom is het beter om te proberen het meteen goed te doen, om in de toekomst geen onnodige problemen voor jezelf te creëren.

Het beheren van indexen, dat wil zeggen de manier waarop ze worden gemaakt en onderhouden, kan een aanzienlijke invloed hebben op de prestaties van SQL-query's.

Vaak kunnen de volgende optimalisaties worden toegepast:

verwijder ongebruikte indexen
identificeer ongebruikte en ineffectieve indexen
indexen verbeteren
vermijd SQL-query's helemaal!
vereenvoudig SQL-query's
en magische caching-opties

DDL-query's combineren

Query's die de gegevensstructuur wijzigen, blokkeren doorgaans de tabel. Historisch gezien vereiste het uitvoeren van een ALTER-query het maken van een nieuwe kopie van de tabel, wat erg tijd- en schijfintensief kan zijn. Daarom is het veel winstgevender om, in plaats van drie zoekopdrachten met kleine wijzigingen, één gecombineerde zoekopdracht uit te voeren. Dit kan een aanzienlijke hoeveelheid tijd besparen bij databasebeheertaken.

Dubbele indexen verwijderen

Dubbele indexen zijn om twee redenen schadelijk: alle verzoeken om gegevens te wijzigen zullen langzamer zijn omdat er dubbel werk wordt verricht om de volledigheid van de index te behouden. Bovendien zorgt dit voor een extra belasting van het bestandssysteem, omdat de omvang van de database fysiek groot wordt, wat leidt tot een toename van de tijd voor het maken van back-ups en de hersteltijd.

Een paar eenvoudige omstandigheden kunnen resulteren in dubbele indexen. MySQL heeft bijvoorbeeld geen index op PRIMARY-velden nodig.

Er kan ook sprake zijn van een dubbele index als de linkerkant van een van de indexen precies hetzelfde is als een andere index.

Het hulpprogramma pt-duplicate-key-checker van perkona-toolkit is een eenvoudige en snelle manier om uw databasestructuur te controleren op de aanwezigheid van onnodige indexen.

Ongebruikte indexen verwijderen

Naast indexen die nooit worden gebruikt omdat ze duplicaten zijn, kunnen er niet-duplicaatindexen zijn die simpelweg nooit worden gebruikt. Dergelijke indexen hebben dezelfde impact als dubbele indexen. In standaard mysql is er geen manier om te bepalen welke indexen niet worden gebruikt, maar sommige versies hebben een vergelijkbare functie, bijvoorbeeld bij gebruik van de Google MySQL-patch.

Deze patch introduceerde een functie: SHOW INDEX_STATISTICS.

En in gewone mysql moet je eerst alle gebruikte SQL-query's verzamelen, deze uitvoeren en het uitvoeringsplan bekijken, terwijl je informatie verzamelt over de indexen die in elk geval worden gebruikt en dit in één enkele tabel brengt. Het is in ieder geval een nuttige ervaring.

Optimalisatie van indexvelden.

Naast het maken van nieuwe indexen om de prestaties te verbeteren, kunt u de prestaties verbeteren door aanvullende ontwerpoptimalisaties. Deze optimalisaties omvatten het gebruik van aangepaste gegevens en veldtypen. Het voordeel in dit geval is een lagere schijfbelasting en een groter volume aan indexen dat in het RAM past.

Gegevenstypen

Sommige typen kunnen pijnloos vervangen worden op de huidige bestaande ondergronden.

BIGINT versus INT

Wanneer de PRIMARY-sleutel is gedefinieerd als BIGINT AUTO INCREMENT, is er doorgaans geen reden om deze te gebruiken. Het gegevenstype INT UNSIGNED AUTO_INCREMENT kan een maximum aantal van maximaal 4294967295 opslaan. Als u feitelijk meer records heeft dan dit aantal, heeft u waarschijnlijk een andere architectuur nodig.

Door een dergelijke verandering van BIGINT naar INT UNSIGNED begint elke rij van de tabel 2 keer minder ruimte op de schijf in beslag te nemen, bovendien wordt de grootte die wordt ingenomen door de PRIMARY-sleutel teruggebracht van 8 bytes naar 4.

Dit is misschien wel een van de meest tastbare eenvoudige verbeteringen die vrij pijnloos kunnen worden doorgevoerd.

DATETIME versus TIJDSTEMPEL

Alles is hier eenvoudig: tijdstempel - 4 bytes, datetime - 8 bytes.

Moet waar mogelijk worden gebruikt omdat:

extra controle van de gegevensintegriteit
zo'n veld gebruikt slechts 1 byte om 255 unieke waarden op te slaan
Dergelijke velden zijn handiger om te lezen :)

Historisch gezien resulteerde het gebruik van enumvelden erin dat de basis afhankelijk was van veranderingen in de mogelijke waarden in de enum. Dit was een blokkerend DDL-verzoek. Sinds MySQL 5.1 is het toevoegen van nieuwe varianten aan een enum erg snel en niet gerelateerd aan de tabelgrootte.

NULL versus NIET NULL

Als u niet zeker weet of een kolom een nulwaarde (NULL) kan bevatten, kunt u deze beter definiëren als NOT NULL. De index op zo'n kolom zal kleiner zijn en gemakkelijker te verwerken.

Automatische typeconversies

Wanneer u een gegevenstype selecteert voor het samenvoegen van velden, kan het voorkomen dat het gegevenstype van het veld niet gedefinieerd is. Ingebouwde conversie kan een volledig onnodige overhead zijn.

Zorg er bij gehele velden voor dat SIGNED en UNSIGNED overeenkomen. Voor variabele veldtypen kan het onnodig werk zijn om de codering te converteren bij het samenvoegen, dus zorg ervoor dat u deze ook controleert. Een veel voorkomend probleem is de automatische conversie tussen latin1- en utf8-coderingen.

Kolomtypen

Sommige soorten gegevens worden vaak in de verkeerde kolommen opgeslagen. Als u daarbij het type wijzigt, kan dit resulteren in efficiëntere opslag, vooral als die kolommen in de index zijn opgenomen. Laten we een paar typische voorbeelden bekijken.

IP-adres

Het IPv4-adres kan worden opgeslagen in het veld INT UNSIGNED, dat slechts 4 bytes in beslag neemt. Een veel voorkomende situatie doet zich voor wanneer het IP-adres wordt opgeslagen in het VARCHAR(15)-veld, dat 12 bytes in beslag neemt. Deze ene wijziging kan de grootte met 2/3 verkleinen. De functies INET_ATON() en INET_NTOA worden gebruikt om te converteren tussen een string met een IP-adres en een numerieke waarde.

Voor IPv6-adressen, die steeds populairder worden, is het belangrijk om hun 128-bit numerieke waarde op te slaan in BINARY(16)-velden en niet VARCHAR te gebruiken voor een voor mensen leesbaar formaat.

Het is gebruikelijk om MD5-velden op te slaan als CHAR(32). Als u een VARCHAR(32)-veld gebruikt, voegt u voor elke waarde ook een extra tekenreekslengte toe. Een MD5-tekenreeks is echter een hexadecimale waarde en kan efficiënter worden opgeslagen met behulp van de functies UNHEX() en HEX(). In dit geval kunnen de gegevens worden opgeslagen in BINARY(16)-velden. Deze eenvoudige actie verkleint de veldgrootte van 32 bytes naar 16 bytes. Een soortgelijk principe kan worden toegepast op alle hexadecimale waarden.

Gebaseerd op het boek van Ronald Bradford.

In het dagelijkse werk kom je redelijk vergelijkbare fouten tegen bij het schrijven van queries.

In dit artikel wil ik voorbeelden geven van hoe je GEEN queries schrijft.

Selecteer alle velden
SELECT * UIT tabel
Gebruik bij het schrijven van query's geen selectie van alle velden - "*". Vermeld alleen de velden die u echt nodig heeft. Hierdoor wordt de hoeveelheid opgehaalde en verzonden gegevens verminderd. Vergeet ook de dekking van indexen niet. Zelfs als u alle velden in de tabel daadwerkelijk nodig heeft, is het beter om ze op te sommen. Ten eerste verbetert het de leesbaarheid van de code. Als u een asterisk gebruikt, is het onmogelijk om te weten welke velden in de tabel staan zonder ernaar te kijken. Ten tweede kan het aantal kolommen in uw tabel in de loop van de tijd veranderen, en als er vandaag vijf INT-kolommen zijn, kunnen binnen een maand TEXT- en BLOB-velden worden toegevoegd, wat de selectie zal vertragen.
Verzoeken in een cyclus.
U moet duidelijk begrijpen dat SQL een set-operationele taal is. Soms vinden programmeurs die gewend zijn te denken in termen van procedurele talen het moeilijk om hun denken te verschuiven naar de taal van sets. Dit kan heel eenvoudig worden gedaan door een eenvoudige regel aan te nemen: “voer zoekopdrachten nooit in een lus uit.” Voorbeelden van hoe dit gedaan kan worden:
1. Monsters
$news_ids = get_list("SELECT news_id FROM today_news ");
while($news_id = get_next($news_ids))
$news = get_row("SELECT titel, body FROM nieuws WHERE news_id = ". $news_id);
De regel is heel eenvoudig: hoe minder verzoeken, hoe beter (hoewel hier, zoals op elke regel, uitzonderingen op bestaan). Vergeet de IN()-constructie niet. De bovenstaande code kan in één query worden geschreven:
SELECT titel, body FROM today_news INNER JOIN news USING(news_id)
2. Inzetstukken
$log = parse_log();
while($record = volgende($log))
query("INSERT INTO logt SET value = ". $log["value"]);!}
Het is veel efficiënter om één query samen te voegen en uit te voeren:
INSERT INTO logs (waarde) VALUES (...), (...)
3. Updates
Soms moet u meerdere rijen in één tabel bijwerken. Als de bijgewerkte waarde hetzelfde is, is alles eenvoudig:
UPDATE nieuws SET title="test" WHERE id IN (1, 2, 3).!}
Als de waarde die wordt gewijzigd voor elk record verschillend is, dan kan dit met de volgende query:
UPDATE nieuws SET
titel = GEVAL
WANNEER nieuws_id = 1 DAN "aa"
WANNEER news_id = 2 DAN "bb" EINDE
WAAR nieuws_id IN (1, 2)
Uit onze tests blijkt dat zo’n verzoek 2-3 keer sneller gaat dan meerdere afzonderlijke verzoeken.
Bewerkingen uitvoeren op geïndexeerde velden
SELECTEER user_id VAN gebruikers WAAR blogs_count * 2 = $value
Deze query maakt geen gebruik van de index, zelfs niet als de kolom blogs_count is geïndexeerd. Om een index te kunnen gebruiken, mogen er geen transformaties worden uitgevoerd op het geïndexeerde veld in de query. Verplaats voor dergelijke verzoeken de conversiefuncties naar een ander deel:
SELECTEER user_id VAN gebruikers WAAR blogs_count = $value / 2;
Soortgelijk voorbeeld:
SELECT user_id FROM gebruikers WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(geregistreerd)<= 10;
Zal geen index gebruiken op het geregistreerde veld, terwijl
SELECT user_id FROM gebruikers WAAR geregistreerd >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
zullen.
Rijen alleen ophalen om hun aantal te tellen
$result = mysql_query("SELECT * FROM tabel", $link);
$num_rows = mysql_num_rows($result);
Als u het aantal rijen moet selecteren dat aan een bepaalde voorwaarde voldoet, gebruikt u de tabelquery SELECT COUNT(*) FROM in plaats van alle rijen te selecteren om alleen het aantal rijen te tellen.
Extra rijen ophalen
$result = mysql_query("SELECT * UIT tabel1", $link);
while($row = mysql_fetch_assoc($result) && $i< 20) {
…
}
Als u slechts n ophaalrijen nodig heeft, gebruik dan LIMIT in plaats van de extra rijen in de toepassing weg te gooien.
ORDER BY RAND() gebruiken
SELECT * FROM tabel ORDER BY RAND() LIMIT 1;
Als de tabel meer dan 4-5 duizend rijen heeft, zal ORDER BY RAND() erg langzaam werken. Het zou veel efficiënter zijn om twee query's uit te voeren:
Als de tabel een primaire sleutel auto_increment heeft en geen gaten bevat:
$rnd = rand(1, query("SELECT MAX(id) FROM tabel"));
$row = query("SELECT * FROM tabel WHERE id = ".$rnd);
Of:
$cnt = query("SELECT COUNT(*) FROM tabel");
$row = query("SELECT * FROM tabel LIMIT ".$cnt.", 1");
wat echter ook langzaam kan zijn als de tabel een zeer groot aantal rijen bevat.
Een groot aantal JOIN's gebruiken
SELECTEER
v.video_id
een.naam,
g.genre
VAN
video's AS v
LINKS AANMELDEN
link_actors_videos AS la ON la.video_id = v.video_id
LINKS AANMELDEN
acteurs AS a ON a.actor_id = la.actor_id
LINKS AANMELDEN
link_genre_video AS lg AAN lg.video_id = v.video_id
LINKS AANMELDEN
genres AS g ON g.genre_id = lg.genre_id
Houd er rekening mee dat bij het één-op-veel verbinden van tabellen het aantal rijen in de selectie bij elke volgende JOIN zal toenemen. In dergelijke gevallen is het sneller om een dergelijke zoekopdracht in meerdere eenvoudige te splitsen.
LIMIT gebruiken
SELECT… FROM tabel LIMIT $start, $per_page
Veel mensen denken dat een dergelijke zoekopdracht $per_pagina aan records oplevert (meestal 10-20) en daarom snel zal werken. Het zal snel werken gedurende de eerste paar pagina's. Maar als het aantal records groot is en u een SELECT... FROM tabel LIMIT 1000000, 1000020-query moet uitvoeren, dan zal MySQL, om zo'n query uit te voeren, eerst 1000020 records selecteren, de eerste miljoen weggooien en 20 retourneren. misschien helemaal niet snel. Er zijn geen triviale manieren om het probleem op te lossen. Velen beperken eenvoudigweg het aantal beschikbare pagina's tot een redelijk aantal. U kunt dergelijke zoekopdrachten ook versnellen met behulp van dekkingsindexen of oplossingen van derden (bijvoorbeeld Sphinx).
Wordt niet gebruikt bij DUPLICATE KEY UPDATE
$row = query("SELECT * FROM tabel WHERE id=1");
Als($rij)
query("UPDATE tabel SET kolom = kolom + 1 WHERE id=1")
anders
query("INSERT INTO tabel SET kolom = 1, id=1");
Een soortgelijke constructie kan worden vervangen door één query, op voorwaarde dat er een primaire of unieke sleutel is voor het id-veld:
INSERT INTO tabel SET kolom = 1, id=1 ON DUPLICATE KEY UPDATE kolom = kolom + 1

Lezen

MySQL is nog steeds de populairste relationele database ter wereld, maar ook de minst geoptimaliseerde. Veel mensen blijven bij de standaardinstellingen zonder dieper te graven. In dit artikel zullen we enkele MySQL-optimalisatietips bekijken, gecombineerd met enkele nieuwe functies die relatief recentelijk zijn uitgekomen.

Configuratie-optimalisatie

Het eerste dat elke MySQL-gebruiker moet doen om de prestaties te verbeteren, is de configuratie aanpassen. De meeste mensen slaan deze stap echter over. In 5.7 (de huidige versie) zijn de standaardinstellingen veel beter dan die van zijn voorgangers, maar het is nog steeds mogelijk en eenvoudig om deze te verbeteren.

We hopen dat je Linux gebruikt of iets als Vagrant -box (zoals onze Homestead Verbeterd) en dat je configuratiebestand dienovereenkomstig in /etc/mysql/my.cnf zal staan. Het is mogelijk dat uw installatie daadwerkelijk een extra configuratiebestand in dit bestand laadt. Dus kijk, als het bestand my.cnf weinig bevat, kijk dan in /etc/mysql/mysql.conf.d/mysqld.cnf .

Handmatig afstemmen

De volgende instellingen moeten standaard worden uitgevoerd. Voeg volgens deze tips toe aan het configuratiebestand in de sectie:

Innodb_buffer_pool_size = 1G # (hier verander je ongeveer 50%-70% van het totale RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # kan worden gewijzigd in 2 of 0 innodb_flush_method = O_DIRECT

innodb_buffer_pool_size . De bufferpool is een soort ‘magazijn’ voor het cachen van gegevens en indexen in het geheugen. Het wordt gebruikt om veelgebruikte gegevens in het geheugen op te slaan. En als u een dedicated of virtuele server gebruikt, waarbij de database vaak het knelpunt is, is het zinvol om deze het grootste deel van het RAM-geheugen te geven. Daarom geven we het 50-70% van het totale RAM-geheugen. Er is een handleiding voor het opzetten van deze pool in de MySQL-documentatie.
innodb_log_bestandsgrootte . Het instellen van de logbestandsgrootte is goed beschreven, maar in een notendop is het de hoeveelheid gegevens die in de logs wordt opgeslagen voordat deze worden gewist. Houd er rekening mee dat het logboek in dit geval geen foutrecords is, maar een soort delta-snapshot van wijzigingen die nog niet naar schijf zijn gespoeld in de belangrijkste innodb-bestanden. MySQL schrijft op de achtergrond, maar dit heeft nog steeds invloed op de prestaties op het moment van schrijven. Een groter logbestand betekent hogere prestaties doordat er minder nieuwe en kleinere controlepunten worden aangemaakt, maar ook een langere hersteltijd in geval van een crash (er moeten meer gegevens naar de database worden geschreven).
innodb_flush_log_at_trx_commit wordt beschreven en laat zien wat er met het logbestand gebeurt. Waarde 1 is het veiligst, omdat het logboek na elke transactie naar schijf wordt gewist. Bij waarden 0 en 2 is ACID minder gegarandeerd, maar zijn de prestaties hoger. Het verschil is niet groot genoeg om op te wegen tegen de stabiliteitsvoordelen bij 1.
innodb_flush_methode . Als klap op de vuurpijl als het gaat om het opschonen van gegevens, moet deze instelling worden ingesteld op O_DIRECT - om dubbele buffering te voorkomen. Ik raad u aan dit altijd te doen zolang het I/O-systeem erg traag blijft. Hoewel je op de meeste hostingsites, zoals DigitalOcean, over SSD-schijven beschikt, zal het I/O-systeem productiever zijn.

Er is een tool van Percona waarmee we de resterende problemen automatisch kunnen opsporen. Merk op dat als we het zonder deze handmatige instelling zouden uitvoeren, slechts 1 van de 4 instellingen zou worden gedefinieerd, aangezien de andere 3 afhankelijk zijn van de voorkeuren van de gebruiker en de applicatieomgeving.

Variabele inspecteur

Variabele-inspecteur installeren op Ubuntu:

Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all. deb sudo apt-get update sudo apt-get install percona-toolkit

Voor andere systemen volgt u deze instructies.

Voer vervolgens de toolkit uit:

Pt-variabele-adviseur h=localhost,u=homestead,p=geheim

Je zult dit resultaat zien:

# WAARSCHUW delay_key_write: MyISAM-indexblokken worden nooit leeggemaakt totdat dit nodig is. # OPMERKING max_binlog_size: De max_binlog_size is kleiner dan de standaardwaarde van 1GB. # OPMERKING sort_buffer_size-1: De sort_buffer_size variabele moet over het algemeen op de standaardwaarde blijven staan, tenzij een expert bepaalt dat het nodig is om deze te wijzigen. # OPMERKING innodb_data_file_path: Het automatisch uitbreiden van InnoDB-bestanden kan veel schijfruimte in beslag nemen, wat later erg moeilijk terug te winnen is. # WARN log_bin: Binaire logboekregistratie is uitgeschakeld, dus herstel en replicatie op een bepaald tijdstip zijn niet mogelijk.

Opmerking vertaler:
Op mijn lokale computer ontving ik daarnaast ook de volgende waarschuwing:

# OPMERKING innodb_flush_method: De meeste productiedatabaseservers die InnoDB gebruiken, moeten innodb_flush_method instellen op O_DIRECT om dubbele buffering te voorkomen, tenzij het I/O-systeem zeer lage prestaties levert.

Het feit dat de parameter innodb_flush_method moet worden ingesteld op O_DIRECT en waarom werd hierboven besproken. En als u de afstemmingsvolgorde hebt gevolgd zoals in het artikel, ziet u deze waarschuwing niet.

Geen van deze ( ca.: aangegeven door de auteur) waarschuwingen zijn niet kritisch en hoeven niet te worden gecorrigeerd. Het enige dat kan worden gecorrigeerd, is het opzetten van een binair logboek voor replicatie en snapshots.

Let op: in nieuwe versies is de standaard binlog-grootte 1G en deze waarschuwing zal niet verschijnen.

Max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "RIJ"

max_binlog_size . Bepaalt hoe groot de binaire logboeken zullen zijn. Ze registreren uw transacties en verzoeken en maken controlepunten. Als een transactie het maximum overschrijdt, kan het logbestand groter zijn wanneer het op schijf wordt opgeslagen; anders ondersteunt MySQL het binnen deze limiet.
log_bin. Met deze optie wordt binaire logboekregistratie in het algemeen ingeschakeld. Zonder dit zijn snapshots of replicaties onmogelijk. Houd er rekening mee dat dit een grote impact kan hebben op uw schijfruimte. server-id is een vereiste optie bij het inschakelen van binaire logboekregistratie, zodat de logs "weten" van welke server ze afkomstig zijn (voor replicatie), en binlog-formaat is eenvoudigweg de manier waarop ze zijn geschreven.

Zoals u kunt zien, heeft de nieuwe MySQL standaardinstellingen die bijna productieklaar zijn. Natuurlijk is elke applicatie anders en zijn er extra trucs en aanpassingen die van toepassing zijn.

MySQL-tuner

Ondersteunende tools: Percona Toolkit voor het identificeren van dubbele indexen

De Percona Toolkit die we eerder hebben geïnstalleerd, bevat ook een tool voor het detecteren van dubbele indexen, wat handig kan zijn bij het gebruik van CMS'en van derden of om zelf te controleren of u per ongeluk meer indexen hebt toegevoegd dan nodig. De standaard WordPress-installatie heeft bijvoorbeeld dubbele indexen in de wp_posts-tabel:

Pt-duplicate-key-checker h=localhost,u=homestead,p=geheim # ########################## #################################### # homestead.wp_posts # #### ########################################### ################# # Sleuteltype_status_date eindigt met een voorvoegsel van de geclusterde index # Sleuteldefinities: # KEY `type_status_date` (`post_type`,`post_status`,`post_date` ,`ID`), # PRIMARY KEY (`ID`), # Kolomtypen: # `post_type` varchar(20) collate utf8mb4_unicode_520_ci niet null standaard "post" # `post_status` varchar(20) collate utf8mb4_unicode_520_ci niet null standaard "publish " " # `post_date` datetime not null default "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # Om deze dubbele geclusterde index in te korten, voert u het volgende uit: ALTER TABLE `homestead`. ` wp_posts` DROP INDEX `type_status_date`, ADD INDEX `type_status_date` (`post_type`,`post_status`,`post_date`);

Zoals u op de laatste regel kunt zien, geeft deze tool u ook tips over hoe u dubbele indexen kunt verwijderen.

Hulptools: Percona Toolkit voor ongebruikte indexen

Percona Toolkit kan ook ongebruikte indexen detecteren. Als u langzame query's registreert (zie het gedeelte over knelpunten hieronder), kunt u het hulpprogramma uitvoeren en controleren of en hoe die query's indexen op tabellen gebruiken.

Pt-index-gebruik /var/log/mysql/mysql-slow.log

Voor gedetailleerde informatie over het gebruik van dit hulpprogramma, zie .

Knelpunten

In deze sectie wordt beschreven hoe u databaseknelpunten kunt detecteren en bewaken.

Laten we eerst het loggen van langzame queries inschakelen:

Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1

De bovenstaande regels moeten worden toegevoegd aan de mysql-configuratie. De database houdt zoekopdrachten bij die meer dan 1 seconde in beslag namen en zoekopdrachten die geen indexen gebruiken.

Zodra er wat gegevens in dit logboek staan, kunt u deze analyseren voor indexgebruik met behulp van het bovenstaande hulpprogramma pt-index-usage, of met pt-query-digest, wat ongeveer de volgende resultaten zal opleveren:

Pt-query-digest /var/log/mysql/mysql-slow.log # 360ms gebruikerstijd, 20ms systeemtijd, 24,66M rss, 92,02M vsz # Huidige datum: do 13 februari 22:39:29 2014 # Hostnaam: * # Bestanden: mysql-slow.log # Totaal: 8 totaal, 6 uniek, 1,14 QPS, 0,00x gelijktijdigheid ________________ # Tijdbereik: 2014-02-13 22:23:52 tot 22:23:59 # Attribuut totaal min max gem. 95% stddev mediaan # ============ ======= ======= ======= ======= ===== == ======= ======= # Uitvoeringstijd 3 ms 267us 406us 343us 403us 39us 348us # Vergrendelingstijd 827us 88us 125us 103us 119us 12us 98us # Verzonden rijen 36 1 15 4,50 14,52 4,18 3,89 # Rij s onderzoeken 87 4 30 10,88 28,75 7,37 7,70 # Querygrootte 2,15k 153 296 245,11 284,79 48,90 258,32 # === = === ========== === ===== ====== = ==== =============== # Profiel # Rang Zoekopdracht ID Reactietijd Oproepen R/Call V/M Item # ==== ==== ====== ====== ===== ====== === == =============== # 1 0x728E539F7617C14D 0,0011 41,0% 3 0,0004 0,00 SELECTEER blog_artikel # 2 0x1290EEE0B201F3FF 0,0003 12,8% 1 0,0003 0,00 SELECTEER portfolio_item # 3 0x31DE4535BDBFA465 0,0003 12,6% 1 0,0003 0,00 SELECTEER portfolio_item # 4 0xF14E15D0F47A5742 0,0003 12,1% 1 0,0003 0,00 SELECTEER portfolio_categorie # 5 0x8F848005A09C9588 0,0003 11,8% 1 0,0003 0,00 SELECTEER blog_categorie # 6 0x55F49 C753CA2ED64 0,0003 9,7% 1 0,0003 0,00 SELECTEER blog_artikel # ==== ============ ====== ============ ===== ==== == ===== =============== # Query 1: 0 QPS, 0x gelijktijdigheid, ID 0x728E539F7617C14D bij byte 736 ______ # Scores: V/M = 0,00 # Tijdsbereik: alle gebeurtenissen hebben plaatsgevonden op 13-02-2014 22:23:52 # Attribuut pct totaal min max avg 95 % stddev mediaan # ============ === ======= == ===== ======= ======= === ==== ======= ======= # Aantal 37 3 # Exec tijd 40 1ms 352us 406us 375us 403us 22us 366us # Vergrendeltijd 42 351us 103us 125us 117us 119us 9us 119us # Verzonden rijen 25 9 1 4 3 3,89 1,37 3,89 # Rijen onderzoeken 24 21 5 8 7 7,70 1,29 7,7 0 # Querygrootte 47 1,02k 261 262 261,25 258,32 0 258,32 # Tekenreeks: # Hosts localhost # Gebruikers * # Query_time distributie # 1us # 10us # 100us #### ####################### #################### ########## # 1ms # 10ms # 100ms # 1s # 10s+ # Tabellen # TOON TABELSTATUS ZOALS " blog_article"\G # TOON TABEL MAKEN `blog_article`\G # UITLEG /*!50100 PARTITIES*/ SELECTEER b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS externe_link4, b0_.description AS beschrijving5, b0_.created AS gemaakt6, b0_.updated AS bijgewerkt7 VAN blog_artikel b0_ BESTEL OP b0_.created DESC LIMIET 10

Als u deze logboeken liever handmatig analyseert, kunt u hetzelfde doen, maar eerst moet u het logboek exporteren naar een beter parseerbaar formaat. Dit kan als volgt worden gedaan:

Mysqldumpslow /var/log/mysql/mysql-slow.log

Met extra opties kunt u de gegevens filteren, zodat u alleen datgene exporteert dat u nodig heeft. Bijvoorbeeld de top 10 van zoekopdrachten, gesorteerd op gemiddelde uitvoeringstijd:

Mysqldumpslow -t 10 -s op /var/log/mysql/localhost-slow.log

Conclusie

In deze uitgebreide MySQL-optimalisatiepost hebben we verschillende methoden en technieken besproken waarmee we onze MySQL kunnen laten vliegen.

We hebben configuratie-optimalisatie bedacht, we hebben de indexen geüpgraded en we hebben een aantal knelpunten opgelost. Dit was allemaal grotendeels theorie, maar het is allemaal toepasbaar op toepassingen in de echte wereld.

Geplaatst door Nikolaj Korotkov
Datum: 8 december 2012 om 14:04 uur

Waar is dit allemaal voor? Wat heeft het invloed? Hoe maak je het werkelijkheid? Op al deze vragen probeer ik in dit bericht een duidelijk antwoord te geven!

En nu een beetje achtergrond. Over het algemeen heb ik onlangs een brief op mijn e-mailadres ontvangen met de volgende inhoud:

Het gemiddelde laadniveau dat door uw account is gegenereerd in de afgelopen drie dagen ******* , samengesteld 119% vanaf het toegestane niveau van uw tariefplan. Wij raden u aan om over te stappen naar VPS-tarieven. Houd er rekening mee dat als de limieten regelmatig worden overschreden, wij ons het recht voorbehouden om uw account te blokkeren in overeenstemming met de clausule van de Overeenkomst...

Ze zijn er allebei, dacht ik op dat moment! Mee eens, het is niet erg prettig om dergelijke brieven te ontvangen. En aangezien ik dit soort problemen voor het eerst tegenkwam, kun je je voorstellen hoe perplex ik was? Mijn verontwaardiging kende geen grenzen! Wat is in hemelsnaam een VPS? Ik kan zeggen dat ik me maar op mijn gemak voel met één tarief, maar hier bieden ze me aan om over te stappen naar virtuele hosting, wat drie keer duurder is. Nou nee, jongens, ik denk dat het nog te vroeg is.

Ik schrijf een retourbrief naar mijn host en vraag hem mij uit te leggen waarom ik zo bang ben dat mijn werkdruk de pan uit rijt? Mijn blog bestaat tenslotte nog maar iets meer dan twee maanden. En de opkomst is niet groot. Over het algemeen schrijf ik dat ik categorisch tegen het overstappen naar VPS ben, ik geloof dat dit niet aan te raden is in zo'n vroeg stadium van de ontwikkeling van hulpbronnen, en ik vraag u om op mijn fouten te wijzen, wat u ermee moet doen en hoe u deze kunt controleren ze in de toekomst!

Als reactie ontvang ik het volgende:

Beste abonnee, we gaan je nu niet verbreken, dit is een banale waarschuwing, maar ze zeggen dat we er iets aan moeten doen. Het probleem van overbelasting is niet direct afhankelijk van het verkeer, maar hangt in grotere mate af van een onjuiste optimalisatie van uw bron. Om de belasting te volgen, hebben we in het controlepaneel een teller weergegeven die elke 10 minuten wordt bijgewerkt:

Nou, bedankt voor de opheldering, denk ik bij mezelf. Ik ga het probleem bestuderen. Nadat ik de vraag "hoe de belasting van hosting te verminderen" op internet had getypt, besefte ik dat ik niet de enige was, en in feite is het probleem behoorlijk urgent. En vroeg of laat zal het velen treffen. Nadat ik mezelf meer in detail met het probleem had vertrouwd, besefte ik dat ik twee manieren heb om uit deze situatie te komen:

Wend u tot professionals (freelancers) voor hulp door hen een bepaald bedrag te betalen, wat altijd op tijd zal gebeuren.
Probeer het probleem zelf op te lossen.

Ik heb dus voor de tweede optie gekozen en ik zal je eerlijk zeggen: tot nu toe heb ik er nog geen moment spijt van gehad. Ik heb de belasting van de hosting twee tot drie keer kunnen verminderen. Kijk zelf maar:

Het verschil is duidelijk! Nu zal ik het je laten zien en vertellen wat ik hiervoor heb gedaan:

— optimaliseerde de mysql-database, waardoor de belasting van de hosting aanzienlijk werd verminderd en WordPress werd versneld;
- ongeveer 8 onnodige plug-ins verwijderd.
— WordPress versneld door verschillende themabestanden voor mijn blog te bewerken.

Omdat het materiaal behoorlijk omvangrijk is, heb ik besloten het in drie delen op te delen. In dit artikel leert u hoe u de belasting van uw hosting kunt verminderen door uw database te optimaliseren. In het volgende artikel vertel ik je... En het laatste artikel zal over dit onderwerp gaan. Toen ik dit allemaal met mijn hulpbron deed, was ik geschokt door de manier waarop mijn blog begon te laden! Vergeleken met wat het was, begon hij te vliegen.

Over het algemeen zal het materiaal dat u uit deze drie berichten haalt, gewoonweg geweldig zijn. Mis het niet!

Database-optimalisatie

Voordat u verschillende dingen met de database gaat doen, zorg ervoor dat u een reservekopie maakt. Zodat als er zich problemen voordoen, alles snel hersteld kan worden. De database bevat de volledige geschiedenis van uw bron; het slaat alle vermeldingen op uw blog op! Over het algemeen raad ik u aan om er een regel van te maken om uw database elke dag op te slaan! Het kost je letterlijk 1 minuut, maar je slaapt altijd rustig. Je begrijpt dat er van alles kan gebeuren.

1. Maak een reservekopie van de database

Om gemakkelijk verbinding te maken met de server en gegevens te verwerken, gebruik ik . Heel gaaf, ooit zal ik een aparte post over deze klant schrijven. Kortom, u moet naar uw server gaan en het tabblad "Databases" of "MySQL Databases" zoeken, zoiets. Elke server beschikt over een database; tijdens de transitie kan de server om een wachtwoord vragen. Je moet het hebben. Bij de aanschaf van hosting wordt een wachtwoord verstrekt.

Als gevolg hiervan zou u op een pagina als deze moeten belanden, phpMyAdmin:

Ga naar de database door op de naam ervan te klikken. Er wordt een databasetabel voor u geopend (klik om te vergroten):

Klik op "Exporteren" en "OK". Bewaar het op uw pc. Dat is alles, de database is opgeslagen, nu kunnen we beginnen met het optimaliseren ervan. Let op: als uw hosting het veld “Opslaan als bestand” heeft, vergeet dan niet het vakje ernaast aan te vinken! En onthoud ook hoeveel uw database momenteel weegt, en kijk vervolgens hoeveel deze na optimalisatie zal wegen.

Voor mij woog het vóór optimalisatie 26 Mb - dit is HORROR, maar wat nu? En nu weegt hij nog maar 2 Mb! Kun je je voorstellen hoeveel onnodige rommel erin zat? Kunt u zich voorstellen welke belasting dit op de server veroorzaakte? Na het optimaliseren van de database begon mijn blog te vliegen als een straalvliegtuig! Over het algemeen zult u, nadat u alle hieronder beschreven stappen heeft uitgevoerd, een aanzienlijk verschil voelen!

2. Schakel berichtrevisies uit en stel de minimale opslagperiode in voor verwijderde bestanden in de prullenbak

Wat is een postrevisie? Wanneer u een blogpost schrijft, slaat WordPress na een bepaalde periode automatisch een back-up van elk bericht op in de database. Over het algemeen wordt dit automatisch opgeslagen. Stel je nu voor dat je 50 blogposts schrijft? Hoeveel exemplaren van berichten bewaart u? Dit is HARD! Terwijl je een bericht schrijft, heb je al minstens 10 automatische opslagen voltooid!

Bovendien, als u bestanden verwijdert, stapelen ze zich op in uw prullenbak, waardoor ook de database wordt geladen. Het is natuurlijk goed als je het bestand meteen uit de prullenbak verwijdert, maar het komt vaak voor dat veel mensen dit vergeten, en sommigen vergeten het gewoon! En dit is niet goed... De database groeit, de belasting op de server wordt steeds groter, de blog laadt steeds langzamer... Heb je ooit nagedacht over de gevolgen waar dit toe kan leiden?

Hier zijn het grootste deel van de gevolgen, maar niet allemaal: afname, frequente weigeringen, verslechtering, verlaging van posities in de resultaten van zoekmachines... En dan wanhoopt de auteur over ongerechtvaardigde verwachtingen. De wens om te bloggen verdwijnt na verloop van tijd en dat is alles! CRASH!

Waarom zeg ik dit allemaal? De database moet voortdurend worden gecontroleerd en in goede staat worden gehouden. Begrijp dat de database het hart van de blog is. Met een constante belasting van het hart met onnodige onzin, zal het na verloop van tijd het niet uithouden en zal het STOPPEN! Ik denk dat je mij begrijpt? Genoeg horrorverhalen dus en laten we verder gaan met database-optimalisatie.

Open dus het bestand wp-config.php, het bevindt zich in de hoofdmap van uw blog, d.w.z. uw hosting/httpdocs of public_html (afhankelijk van de hosting)/wp-config.php. En plaats er twee regels in:

1 2	definiëren ("WP_POST_REVISIONS", false) ; definieer ("EMPTY_TRASH_DAYS" , 1);

Regel nr. 1 schakelt postrevisie uit, regel nr. 2 betekent hoeveel dagen verwijderde bestanden in uw prullenbak worden opgeslagen. Zoals je kunt zien, heb ik "1" gezet, je kunt natuurlijk "0" zetten, maar als je hand plotseling, door nalatigheid, trilt en je op de link "verwijderen" klikt, is alles FUCKED!

En geloof me, na 5-8 uur achter de computer te hebben gezeten, het is mogelijk! Daarom laat ik liever het cijfer “1” staan. Natuurlijk is het beter om na het verwijderen van een bestand de prullenbak meteen handmatig te legen, maar ook als je dit vergeet te doen, wordt het bestand na een dag automatisch uit de prullenbak verwijderd! Zo ziet het er voor mij uit:

3. Verwijder berichtrevisies

Als we in de vorige paragraaf berichtrevisies hebben uitgeschakeld, moeten we in deze paragraaf alle berichtrevisies verwijderen die zich gedurende de gehele blogperiode hebben verzameld. Als je dit nog nooit hebt gedaan, dan heb je er nog steeds een ongelooflijk groot aantal! Laten we het doen. Laten we deze regel kopiëren:

Laten we teruggaan naar de MySQL-database, zoals beschreven in de eerste paragraaf. Ga naar het tabblad SQL, plak de gekopieerde regel in het veld en klik op “OK”:

De database zal vragen:

We antwoorden met “OK” en zien hoeveel onnodige postrevisies uw database bevatte, en hoe lang het duurde om het verzoek te verwerken. En elk beetje tijd geeft zijn eigen lading:

Ik heb de schoonmaak 3 dagen geleden uitgevoerd, dus er zijn nog geen herzieningen aangebracht. Toen ik de database voor de eerste keer opschoonde, werden er maar liefst 1800 rijen verwijderd! Kun je je voorstellen hoeveel kopieën van onnodige berichten erin zijn opgeslagen? Laten we verder gaan.

4. Berichten optimaliseren in wp-post

De map wp-post bevat alle blogposts. Precies hetzelfde als in de vorige paragraaf, kopieer de regel:

OPTIMALISEER TABEL wp_posts;

En plak het in het SQL-queryveld. Klik op “OK” en kijk:

Dat is alles, het verzoek is voltooid!

5. Maak wp-postmeta schoon

Wat gaan we precies schoonmaken? De map wp-postmeta bevat:

— tijdstip waarop een van de berichten voor het laatst is bewerkt. Het maakt geen enkel verschil, maar het belast de server wel;
— de inhoud van de vorige (voor mensen begrijpelijke URL). Als je ooit de permalink in een bericht hebt gewijzigd. Wanneer u het vervolgens wijzigt, wordt het niet verwijderd, maar nestelt het zich in de map wp-postmeta en laadt het uw database.

We doen hetzelfde, kopieer deze code:

Plak het in het SQL-queryveld en klik op OK. Laten we eens kijken naar het resultaat:

6. Verwijder spamreacties

Dit doe je op dezelfde manier, kopieer de code:

Plak het in het SQL-queryveld, klik op "OK", bekijk het resultaat:

Zoals je kunt zien "0". Na het voltooien van dit verzoek vergeet u spamreacties!

7. Verwijder pingbacks

Pingbacks zijn meldingen dat iemand naar uw bericht of pagina linkt. Dit hebben wij niet nodig, het is een extra last! Laten we verwijderen!

8. Schakel pingbacks uit

Uit de vorige paragraaf kwamen we erachter dat pingbacks geen enkel voordeel voor onze bron opleveren, maar deze alleen maar verstoppen. Laten we ze dus volledig uitschakelen. Laten we deze code kopiëren:

UPDATE wp_posts p SET p. ping_status = "gesloten"

Hoe vind je deze schoonmaak? Vond je het leuk? Kijk nu eens hoeveel uw database weegt na de optimalisatie? Is de omvang merkbaar afgenomen? Ik zei het toch! Kijk hoe je blog laadt! Hij moet vliegen! Maar dat is niet alles voor vandaag. Nu zullen we naar een laatste punt kijken, dat ook de optimalisatie aanzienlijk zal verbeteren.

9. Installeer de Optimize DB-plug-in

Ik heb deze plug-in al kort genoemd. Laten we eens nader bekijken hoe we het kunnen gebruiken. Deze plug-in helpt, zoals je misschien al geraden hebt, de database te optimaliseren! Download het archief met de plug-in naar uw pc en activeer het:

Dat is alles, uw database wordt verder geoptimaliseerd met behulp van de plug-in:

Na de optimalisatie deactiveert u de plug-in zodat deze uw bron niet extra belast. Over het algemeen raad ik u aan om alle bovenstaande acties één keer per maand of zelfs vaker uit te voeren. En dan laadt je blog razendsnel en is de belasting op de server minimaal.

En in het volgende deel van het bericht zal ik je laten zien hoe je enkele onnodige plug-ins kunt vervangen door codes. Zorg ervoor dat je niets mist. Dit wordt een krachtige post, waarna je server zo licht als een veertje zal zijn!

En hiermee neem ik afscheid van je. Dat is alles voor vandaag. Ik wens u succes, en onthoud dat dit een enorme vermindering van de belasting van uw hulpbronnen is. Dag allemaal en tot snel.

En tot slot een portie grappen:

Nou, wat vind je van het artikel? Ik ben er zeker van dat u tevreden zult zijn nadat u het hebt gelezen en aanbevelingen hebt gedaan met uw hulpbron! Ik kijk uit naar uw opmerkingen!

Vond je het artikel leuk? Deel met je vrienden!

Een boek voor elke commentator!

Het boek bevat een gedetailleerde beschrijving van de meest effectieve methoden om uw bron te promoten!

60 opmerkingen

Alexander 8 december 2012 15:18
En ik weet waarom je werkdruk zo veel is toegenomen. Het is gewoon dat ik hier bij jou ben neergestreken en dat ik voortdurend iets leer. Wat te doen als de informatie hier goed is. Maar serieus, ik raad alle bloggers aan om eerst alle bovenstaande tips te doen. Ik heb dit lang geleden gedaan, dus ik slaap rustig. En toch is de Optimize DB-plug-in over het algemeen een verplicht kenmerk van elke blog. Bedankt Kolya, zoals altijd is alles nuttig en relevant. Maar ik kijk erg uit naar het volgende bericht. Dus ga je gang en schrijf
9 december 2012 16:19
Ik ben bang om aan de database te sleutelen, maar na het installeren en opschonen met de WP-Cleanup plugin is deze afgenomen van bijna 50 naar 7Mb. De blog laadde eigenlijk veel sneller.
9 december 2012 20:39
Strikt genomen is het tijdens bewerkingen met de database niet de database zelf die vraagt (in het algemeen zijn alle acties hetzelfde, het DBMS vraagt nergens om), maar de client, phpMySql.
Met betrekking tot pingbacks: “Uit de vorige paragraaf kwamen we erachter dat pingbacks geen enkel voordeel voor onze bron opleveren, maar deze alleen maar verstoppen.” — Strikt genomen zijn we niets te weten gekomen.
Je zei eenvoudigweg, zonder tegenspraak, dat ze niet nodig zijn, dat is alles. In feite kunnen ze heel nuttig zijn, u hoeft deze tool alleen maar te gebruiken voor het beoogde doel. Zegt het zoekwoord ‘semantisch web’ u bijvoorbeeld iets?
10 december 2012 08:36
Joeri 16 december 2012 23:49
Hallo vriend!
Je bericht is echt cool. Er staat zoveel onzin op internet dat je beetje bij beetje naar informatie moet zoeken. En hier kwam ik, en voor jou is alles begrijpelijk en begrijpelijk. Ik heb net een probleem gehad met de belasting van de server. Ik raad ook aan om de WP Super Cache-plug-in te installeren. Het moet alleen correct worden geconfigureerd. Coole plug-in! Misschien heb je in andere berichten iets over hem gezegd, maar ik heb het nog niet gelezen. Ik haast me om verder te gaan met het tweede deel van de optimalisatie. Veel succes voor jou en je blog
25 december 2012 11:40 uur
28 januari 2013 11:24
Goedemiddag Heel interessant, maar wat moet ik met een blog op Blogger? Niet alle plug-ins voor WP zijn geschikt voor Blogspot; je moet zelf op internet zoeken naar optimalisatiemethoden.
Met vriendelijke groet, Vadim.
Anton 2 april 2013 20:34
Bedankt, dit is echt een goed bericht. Trouwens, na het doen van punt nr. 3 - “zijn 4145 regels verwijderd. (Het verzoek duurde 7,0269 seconden)"
14 juli 2013 19:04
Ik vraag me af of er een manier is om de database van oude plug-ins te wissen? Daar zijn toch zeker ook sporen van achtergebleven?
14 juli 2013 19:06
Ter vervolg: het lijkt ook erg op jouw tekst hier dayafternight.ru/wordpress/baza-dannih-mysql-optimizacia
12 september 2013 12:57
Bedankt Nikolay, een noodzakelijke zaak.
Alles is toegankelijk en helder geschreven.
Is het artikel over codes al gepubliceerd?
12 september 2013 13:05 uur
Nikolai vergat het te vragen, vertel het me alsjeblieft. Toen ik bezig was met optimaliseren, ontdekte ik een nieuwe information_schema-database in mijn PhpMyadmin
Vertel me eens waar het vandaan zou kunnen komen?
De laatste tijd heb ik alleen de Yandex-statistiekcode ingevoegd.
Natalia Geger
Let hier niet op... De meeste moderne servers hebben het! Dit komt door de release van MySQL versie 5.0 en hoger...
INFORMATION_SCHEMA is een virtuele database die wordt gevormd tijdens het opstarten van de server en die metadata van alle databases bevat, d.w.z. informatie over de structuur van databases. Het is alleen-lezen.
27 oktober 2013 01:06
oh, ik heb de basis schoongemaakt met jouw methode + met mijn eigen handen, de resultaten zijn duidelijk. Voorheen woog de database 20 MB, nu is dat 5 MB
29 oktober 2013 23:34
Hartelijk dank voor het artikel. Vandaag kreeg ik ook een bug van de host. Als gevolg van de acties werd de database van 25 MB 5.2. Er zijn 2 vragen: moeten al deze manipulaties periodiek worden uitgevoerd? En de tweede vraag, ik heb de plug-in geïnstalleerd, ik klik op optimaliseren, met als resultaat dat naast elke regel die wordt geschreven,
opmerking: Tabel ondersteunt geen optimaliseren, maar doet in plaats daarvan opnieuw maken + analyseren
Ziet het er niet naar uit dat alles in orde is?!
Alsjeblieft! Ja, ik doe al deze manipulaties ongeveer een keer per maand. Maar over de plugin kan ik nog niets zeggen, blijkbaar heb je iets verkeerd gedaan. Zoek op internet naar informatie hierover. Maar er zijn ook leuke evenementen. Je hebt de 2100e reactie op mijn blog achtergelaten en hiervoor heb je recht op een prijs van 100 roebel:
Stuur mij uw wmr-portemonneenummer en ik maak het geld naar u over.
30 oktober 2013 13:27
Bedankt, de prijs is ontvangen. Hoe ben ik op jullie site terechtgekomen?! Gisteren stopte de site opnieuw met werken en op het scherm stond 'Fout bij verbinding maken met de database'. Ik schreef de host, zij bevestigden dat MySQL zwaar belast wordt en doen er iets aan, maar zijn ondertussen overgestapt op een hoger tarief. Ik ging meteen op zoek naar wat ik moest doen en vond je artikel, waardoor de database met 5 keer werd verkleind. De plug-in, die aanvankelijk niet wilde werken, werkte nog steeds, maar het grootste probleem, het verwijderen van onnodige zoekopdrachten, werd nooit opgelost. Ik heb de WP Super Cache-plug-in al, maar deze slaat pagina's op in de cache, geen databasequery's. En dus zocht ik tot vier uur in de ochtend naar een plug-in die me kon helpen met verzoeken, en die heb ik gevonden. WP File Cache slaat verzoeken op in de cache, het aantal verzoeken en het MB geheugen worden aanzienlijk verminderd. Op pagina's waar voorheen 40 verzoeken en 35 MB waren, zijn er nu 9 en 12 MB verzoeken. Het enige is dat de laadsnelheid iets lijkt te zijn toegenomen, maar niet significant, aangezien de laadsnelheid van mijn pagina gemiddeld 0,15-0,5 seconden is. Misschien is iemand geïnteresseerd in deze informatie.
7 december 2013 15:41
Kunnen de bovenstaande acties de werking van de nrelate-flyout-plug-in beïnvloeden?