Top 20 aanbevelingen voor MySQL

De MySQL-database is de populairste open source relationele database ter wereld geworden vanwege de hoge prestaties, consistentie, hoge betrouwbaarheid en gebruiksgemak. Deze voordelen die het ons biedt, worden echter vaak beïnvloed door de manier waarop we eraan werken.

In deze zelfstudie leert u een reeks tips die zeer nuttig zullen zijn en ons in staat zullen stellen het meeste uit beide te halen vanuit het oogpunt van de programmeur en de databasebeheerder.

Welk hulpmiddel gaan we gebruiken?MySQL-opdrachtconsole '] MySQL heeft een programma, genaamd met dezelfde naam als de database (mysql) dat wordt gebruikt om de database via de opdrachtregel te beheren.

ramenHet staat in een directory zoals:

C: \ Program Files \ MySQL \ MySQL Server 5.6 \ bin

De map kan variëren, het kan bijvoorbeeld in de hoofdmap van de C:-schijf staan, of op een andere plaats waar we MySQL hebben geïnstalleerd. Om toegang te krijgen tot de MySQL-console in Windows, moeten we ons in die map bevinden.

LinuxZie de volgende link:

Toegang tot MySQL vanuit Linux

MacComputers met systeem Mac OS X ze hebben de opdrachtregelterminal ingebouwd tussen de beschikbare applicaties. Om toegang te krijgen wordt hetzelfde commando gebruikt als in Linux.

phpMijnAdminHet is een gratis softwaretool geschreven in PHP, die wordt gebruikt om MySQL via internet te beheren. Als je een lokale ontwikkelomgeving hebt geïnstalleerd, zoals XAMPP of WAMP, is deze tool al geïnstalleerd. Als je een hostingplan hebt met een administratief paneel, bieden de meeste van hen deze tool aan als MySQL-databasebeheerder.

[kleur = rgb (169,169,169)] phpMyAdmin van XAMPP [/ kleur]

VERGROTEN

[kleur = # a9a9a9] phpMyAdmin van CPanel [/ kleur]

Met deze 2 tools bij de hand kunnen we beginnen met het testen van al deze goede praktijken die we hieronder laten zien.

Dit zijn Top 20 MySQL-best practices:

1. Naamgeving

Algemene regelsHoud de volgende regels in gedachten om problemen te voorkomen.

• Gebruik kleine letters omdat het u helpt bij het typen van snelheid, het voorkomt fouten in het geval van kleine letters en hoofdlettergevoelige functies, enz.

• Gebruik geen witruimte, gebruik in plaats daarvan het onderstrepingsteken (_).

• Gebruik geen cijfers in de namen, alleen Engelse alfabetische tekens.

• Gebruik geldige begrijpelijke namen.

• Namen moeten voor zichzelf spreken.

• Namen mogen niet meer dan 64 tekens bevatten.

• Vermijd het gebruik van voorvoegsels.

Regels voor databasenamenVolg alle bovenstaande algemene regels.

• De naam kan zowel enkelvoud als meervoud zijn, maar de database vertegenwoordigt een database, dus deze moet zoveel mogelijk in het enkelvoud staan.

• Vermijd voorvoegsels waar mogelijk.

Regels voor tabelnamenGebruik kleine letters voor tabelnamen: MySQL wordt meestal gehost op Linux-servers, het is hoofdlettergevoelig, dus het beste is om tabelnamen in kleine letters te gebruiken.

• De namen van de tabellen moeten in het enkelvoud staan: de tabel is een enkele entiteit, net als het model, dus het is vreemd om de naam van de tabel in het meervoud te hebben.

• Voorvoegsels in de tabelnaam: We hebben vaak gezien dat tabellen worden voorafgegaan door de naam van de database of de naam van het project. Soms is dit nodig wanneer we in ons project veel databases hebben om de beperking van sommige hostingproviders te omzeilen. Maar als het niet nodig is en ons project klein is, vermijd dan het gebruik van voorvoegsels.

Naam van de veldenGebruik alle bovenstaande regels, dat wil zeggen, gebruik kleine letters, gebruik geen spaties, gebruik geen cijfers en vermijd voorvoegsels.

• Gebruik waar mogelijk een of twee korte woorden.

• De namen van de velden moeten begrijpelijk zijn, bijvoorbeeld: prijs, bedrijfsnaam, etc.

• Primaire kolomnaam: Primaire sleutel kan id-naam of tabelnaam _id hebben. Dat zal afhangen van de keuze.

• Vermijd het gebruik van gereserveerde woorden in de velden: *****, datum, etc. In deze gevallen verdient het de voorkeur om voorvoegsels zoals record_date, enz.

• Vermijd het gebruik van kolomnamen met dezelfde tabelnaam. Dit kan verwarring veroorzaken bij het schrijven van uw vragen.

• Vermijd namen in afgekorte of aaneengeschakelde acroniemen.

2. Gebruik altijd het juiste gegevenstype

VoorbeeldHet gebruik van varchar (20) om datetime-waarden op te slaan in plaats van DATETIME kan fouten veroorzaken tijdens datumgerelateerde tijdberekeningen en is ook mogelijk in het geval van ongeldige gegevensopslag.

3. Gebruik CHAR (1) over VARCHAR (1)

4. CHAR voor vaste gegevens

Voorbeeld:

Het gebruik van CHAR (1000) in plaats van VARCHAR (1000) verbruikt meer ruimte als de lengte van de gegevens kleiner is dan 1000.

5. Vermijd het gebruik van regionale datumnotaties

• DATUM TIJD
• DATUM

Gebruik altijd het formaat JJJJ-MM-DD of het ISO-datumformaat dat bij uw SQL-engine past. Andere regionale formaten zoals DD-MM-YYY, DD-MM-YYYY worden niet correct opgeslagen.

6. Optimaliseer uw verzoeken voor de cache

 // Cache werkt NIET $ r = mysql_query ("SELECT name FROM users WHERE record> = CURDATE ()"); 

 // Cache werkt WEL $ vandaag = datum ("J-m-d"); $ r = mysql_query ("SELECT naam FROM gebruikers WHERE record> = '$ vandaag'"); 

7. Vermijd het gebruik van "SELECT *" in uw zoekopdrachten

Het is een goede gewoonte specificeer de benodigde kolommen in uw SELECT-instructie.

8. Gebruik LIMIT 1 Als u slechts één rij wilt

In deze gevallen kan het toevoegen van LIMIT 1 aan uw zoekopdracht de snelheid aanzienlijk verbeteren. Op deze manier stopt de database met het scannen van resultaten op het moment dat hij er een vindt, in plaats van de hele tabel of een index te doorlopen.

 // Ik heb gebruikers uit Madrid? // wat NIET te doen: $ r = mysql_query ("SELECT * FROM gebruiker WHERE city =‘ Madrid ’"); if (mysql_num_rows ($ r)> 0) {//…} // veel beter: $ r = mysql_query ("SELECT 1 FROM gebruiker WHERE city =' Madrid 'LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//…}

9. Gebruik van ***** BY

Maak geen misbruik van het gebruik ervan.

10. Kies geschikte basismotor:

Als u een toepassing ontwikkelt die vaker gegevens schrijft dan leest
(bijvoorbeeld: realtime bankieren), kies de InnoDB storage engine.

Het kiezen van de verkeerde opslagengine heeft invloed op de prestaties van uw zoekopdrachten.

11. Gebruik de EXISTS-component wanneer nodig

 If (SELECT count (*) from Table WHERE col = 'some value')> 0

 Indien BESTAAT (SELECT * uit tabel WHERE col = 'some value')

12. Gebruik EXPLAIN in uw SELECT-query's

Het resultaat van een EXPLAIN-query toont u de indexen die worden gebruikt, hoe de tabel wordt onderzocht, hoe deze wordt geordend, enz.

Selecteer een SELECT-query (bij voorkeur een complexe, met joins) en voeg het woord EXPLAIN toe aan het begin van alles. Het geeft de resultaten terug in een eenvoudige tabel. Stel dat ik bijvoorbeeld ben vergeten een kolom te indexeren, dan zou het volgende scherm verschijnen:

VERGROTEN

Na het toevoegen van de index aan de toestandstabel ziet het er als volgt uit:

VERGROTEN

13. Index en gebruik hetzelfde type kolom voor Joins

Ook moeten de kolommen die u samenvoegt van hetzelfde type zijn. Als u bijvoorbeeld een kolom van het type DECIMAL samenvoegt met een kolom van het type INT uit een andere tabel, kan MySQL niet ten minste een van de twee indexen gebruiken. Zelfs de karaktercodering moet van hetzelfde type zijn voor de kolommen van Tekenreekstype.

 // op zoek naar bedrijven in mijn stad $ r = mysql_query ("SELECT company_name FROM users LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");

14. Gebruik NOT NULL als je kunt

Vraag jezelf allereerst af of er een verschil zou zijn tussen het hebben van een lege string en een NULL-waarde (of voor INT-velden: 0 versus NULL). Als er geen probleem is tussen de twee waarden, hebt u geen NULL-veld nodig. NULL-kolommen vereisen extra ruimte en kunnen uw vergelijkingsstatements ingewikkelder maken. Vermijd ze gewoon wanneer je kunt.

We begrijpen in ieder geval dat er in sommige zeer specifieke gevallen een reden is om NULL-kolommen te gebruiken, wat niet altijd een slechte zaak is.

15. Tabellen met vaste afmetingen (statisch) zijn sneller

Enkele voorbeelden van kolomtypen die GEEN vaste grootte hebben, zijn:

• VARCHAR
• TEKST
• BLOB

Als u slechts één van deze kolomtypen opneemt, heeft de tabel niet langer een vaste grootte en moet deze anders worden behandeld door de MySQL-engine.

Tabellen met een vast formaat kunnen de productiviteit verhogen omdat de MySQL-engine sneller door uw records kan zoeken. Wanneer u een specifieke rij in de tabel wilt lezen, kunt u snel de positie ervan berekenen. Als de rijgrootte niet vaststaat, moet u elke keer dat u moet zoeken eerst de index van de primaire sleutel opvragen.

Ze zijn ook gemakkelijker te zoeken en weer op te bouwen na een ongeval. Maar aan de andere kant kunnen ze ook meer ruimte innemen.

16. Verticale partitionering

Voorbeeld 1:

U zult zeker een gebruikerstabel hebben die een postadres bevat, dat niet vaak wordt gebruikt. Hier zou je de tabel kunnen splitsen en de adressen in een aparte tabel kunnen opslaan. Op deze manier zou uw hoofdgebruikerstabel kleiner zijn. Zoals u weet, hoe kleiner hoe sneller de tafels.

Voorbeeld 2:

Je hebt een veld "last_access" in je tabel. Elke keer dat een gebruiker uw pagina bezoekt, wordt deze bijgewerkt. Maar elke toegang zorgt ervoor dat de querycache voor die tabel wordt vrijgemaakt. Wat u kunt doen is dit veld in een andere tabel plaatsen, zodat de wijzigingen in uw gebruikerstabel tot een minimum worden beperkt.

Maar je moet er ook voor zorgen dat je na het partitioneren niet constant aan de twee tafels hoeft te zitten, anders krijg je te maken met prestatieverlies, precies het tegenovergestelde van wat we zochten.

17. Slaat IP-adressen op als UNSIGNED INT

Maar je moet ervoor zorgen dat de kolom is NIET ONDERTEKEND INT (unsigned integer) omdat IP-adressen gebruik maken van het volledige 32-bits niet-ondertekende bereik.

In uw vragen kunt u de INET_ATON () functie om een ​​IP-adres naar een geheel getal te converteren, en INET_NTOA () om het tegenovergestelde te doen. Er zijn ook soortgelijke functies in PHP genaamd ip2lang () Y lang2ip ().

18. Maak weergaven om algemeen gebruik in tabellen te vereenvoudigen

Het kan ook worden gebruikt om niet-geïndexeerde kolommen te filteren, zodat alleen de velden overblijven die sneller worden weergegeven in de zoekopdracht.

19. Gebruik geen ***** BY RAND ()

Als je echt willekeurige tabellen nodig hebt voor je resultaat, zijn er veel betere manieren om dit te doen. Het is duidelijk dat ze meer code in beslag zullen nemen, maar je voorkomt een mogelijke bottleneck die exponentieel toeneemt naarmate je content groeit.

Het probleem is dat MySQL moet worden uitgevoerd RAND () (wat verwerkingskracht vereist) voor elk van de rijen voordat ze worden gesorteerd en een enkele rij wordt geretourneerd.

 // de manier om het NIET te doen: $ r = mysql_query ("SELECT gebruikersnaam FROM gebruiker ***** BY RAND () LIMIT 1"); // veel beter: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT gebruikersnaam FROM gebruikersnaam LIMIT $ rand, 1");

20. Optimaliseer de WHERE-component

• Verwijder onnodige haakjes. Bijvoorbeeld:

 Van naar5 EN b = c EN a = 5

 SELECTEER AANTAL (*) UIT tabel.