MySQL on maailman toiseksi suosituin avoimen lähdekoodin relaatiotietokannan hallintajärjestelmä. Sitä käytetään maailmanlaajuisesti sen jatkuvan nopean suorituskyvyn, korkean luotettavuuden ja helppokäyttöisyyden vuoksi. Tässä artikkelissa esitellään joitain MySQL:n parhaista käytännöistä.

Niiden avulla voit harjoitella SQL:ää ja tehdä itsellesi SQL-harjoituksia, oppia SQL-rajoituksesta ja SQL-käytännöstä verkossa sekä yleisimmistä SQL-harjoitusongelmista.

Se auttaa sinua ymmärtämään rivinumeron SQL:n, SQL-topin, MySQL-rajapoikkeaman ja SQL-verkkokäytännön.

Jos etsit mukavaa ja helppoa tapaa näyttää suuren määrän rivejä ja sarakkeita WordPress-verkkosivustosi vierailijoille värikkäillä, informatiivisilla ja reagoivilla interaktiivisilla taulukoilla ja kaavioilla, voit käyttää sitä wpDataTables-laajennuksella, tarkistaa hinnat ja kaikki toiminnot, jotka se tarjoaa täällä.

1 Käytä aina oikeaa tietotyyppiä

Yksi tärkeimmistä MySQL:n parhaista käytännöistä on käyttää datatyyppejä, jotka perustuvat tiedon luonteeseen. Epäolennaisten tietotyyppien käyttö voi kuluttaa enemmän tilaa tai johtaa virheisiin.

Esimerkki: Varcharin (20) käyttäminen DATETIME-tietotyypin sijaan päivämäärä-aika-arvojen tallentamiseen johtaa virheisiin päivämäärä-aika-laskennassa. On myös mahdollista, että virheellisiä tietoja tallennetaan.

2 Käytä CHAR (1) VARCHAR (1) sijaan

VARCHAR (1) vie ylimääräisiä tavuja tietojen tallentamiseen, joten jos kirjoitat yhden merkin, on parempi käyttää CHAR (1).

3 Käytä CHAR-tietotyyppiä vain kiinteäpituisten tietojen tallentamiseen

Esimerkki: Jos datan pituus on alle 1000, char (1000) käyttäminen varchar (1000) sijaan kuluttaa enemmän tilaa.

4 Vältä alueellisten päivämäärämuotojen käyttöä

Kun käytät DATETIME- tai DATE-tietotyyppiä, käytä aina VVVV-KK-PP päivämäärämuotoa tai ISO-päivämäärämuotoa, joka sopii SQL-moottorillesi. Alueellisia muotoja, kuten PP-KK-VVVV tai KK-PP-VVVV, ei tallenneta oikein.

5 Hakemistoavainsaraketta

On toivottavaa, että kysely palauttaa tuloksen nopeasti, joten muista indeksoida JOIN-lauseissa käytetyt sarakkeet.

Jos käytät UPDATE-käskyä, jossa on useampi kuin yksi taulukko, muista indeksoida kaikki sarakkeet, joita käytetään taulukoiden yhdistämiseen.

6 Älä käytä funktioita indeksoitujen sarakkeiden päällä…

… koska silloin indeksi menettää tarkoituksensa.

Oletetaan esimerkiksi, että haluat saada tietoja, joissa asiakaskoodin kaksi ensimmäistä merkkiä ovat AK. Kirjoittaa:

SELECT columns FROM table WHERE customer_code like 'AK%'

ja älä kirjoita

SELECT columns FROM table WHERE left (customer_code,2)='AK’

Miksi? Ensimmäinen esimerkki käyttää indeksiä, mikä johtaa nopeampaan vasteaikaan.

**7 Käytä SQL SELECT * -toimintoa vain tarvittaessa**

MySQL:n parhaiden käytäntöjen luettelon mukaisesti älä käytä koodissa vain sokeasti SELECT * -näppäintä. Jos taulukossa on monta saraketta, kaikki palautetaan. Tämä hidastaa vasteaikaa, varsinkin jos lähetät tuloksen käyttöliittymäsovellukseen.

Kirjoita sen sijaan nimenomaisesti sarakkeiden nimet, joita todella tarvitaan.

Huomaa: muista, että kaikki SELECT-käskyt vaativat WHERE-lauseen.

8 Käytä ORDER BY -lauseketta vain tarvittaessa

Jos haluat näyttää tuloksen käyttöliittymäsovelluksessa, anna sen TILATA tulosjoukko. Jos teet tämän SQL:ssä, vasteaika saattaa hidastua usean käyttäjän ympäristössä.

9 Valitse oikea tietokantakone

Jos kehität sovellusta, joka lukee tietoja useammin kuin kirjoittaa (esim. hakukone), valitse MyISAM-tallennuskone.

Väärän tallennusmoottorin valinta vaikuttaa suorituskykyyn.

10 Käytä EXISTS-lausetta tarvittaessa

Käytä tietojen olemassaolon tarkistamiseen EXISTS-lausetta, joka on nopeampi vasteaika. Käytä esimerkiksi:

If EXISTS(SELECT * from Table WHERE col=’some value’)

Älä käytä :

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

11 SELITYS SELECT-kyselysi

Jos käytät EXPLAIN-avainsanaa, saat käsityksen siitä, mitä MySQL tekee kyselysi suorittamiseksi. Tämä voi auttaa sinua havaitsemaan ongelmia kyselysi tai taulukkorakenteissasi (esim. pullonkauloja).

EXPLAIN-kysely näyttää, mitä indeksejä käytetään, kuinka taulukkoa tarkistetaan, lajitellaan jne.

Sinun tarvitsee vain lisätä avainsana EXPLAIN SELECT-kyselyn eteen (mieluiten monimutkainen, jossa on liitoksia). Lisäksi, jos käytät phpmyadminia tähän, tulokset näkyvät mukavassa taulukossa.

12 Käytä RAJAA 1 saadaksesi yksilöllisen rivin

Joskus tiedät etukäteen, että etsit vain yhtä riviä kyselyitä tehdessäsi. Saatat esimerkiksi hakea ainutlaatuista tietuetta tai voit vain tarkistaa WHERE-lauseesi täyttävien tietueiden määrän.

Tällaisissa tapauksissa sinun kannattaa käyttää MySQL-rajoitustoimintoa suorituskyvyn parantamiseksi. Tässä on toinen MySQL:n parhaista käytännöistä: lisää vain LIMIT 1 kyselyyn. Näin tietokantakoneen ei tarvitse käydä läpi koko taulukkoa tai hakemistoa. Se lopettaa skannauksen, kun se löytää vain yhden tietueen siitä, mitä etsit.

// do I have anyusersfrom Alabama?

// mitä EI tehdä:
$r = mysql_query("SELECT * FROM user WHERE state = ’Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// paljon parempi:
$r = mysql_query("SELECT 1 FROM user WHERE state = ’Alabama’ RAJA 1");
if (mysql_num_rows($r) > 0) {
// …
}

13 Indeksoi ja käytä samoja saraketyyppejä liitoksissa

Toinen tärkeä MySQL-käytäntöjen vinkki – jos sovelluksessasi on useita JOIN-kyselyjä, varmista, että liittämäsi sarakkeet on indeksoitu molemmissa taulukoissa. Tämä vaikuttaa MySQL:n yhdistämistoiminnan sisäiseen optimointiin.

Myös yhdistettyjen sarakkeiden on oltava samantyyppisiä. Jos esimerkiksi liität DECIMAL-sarakkeen INT-sarakkeeseen toisesta taulukosta, MySQL ei voi käyttää mitään indeksejä. Jopa merkkikoodausten on oltava samaa tyyppiä merkkijonotyyppisissä sarakkeissa.

// looking for companies in my state $r = mysql_query("SELECT company_name FROM users LEFT JOIN companies ON (users.state = companies.state) WHERE users.id = $user_id");

// molempien tilasarakkeiden tulee olla indeksoituja
// ja molempien tulee olla samaa tyyppiä ja merkkikoodausta
// tai MySQL voi suorittaa koko taulukkotarkistuksen

14 Piilota MySQL Internetistä

Kokeneet tietokannan ylläpitäjät ja turvallisuushenkilöstö tietävät sen – älä koskaan isännöi tietokantaa Web-palvelimen juuren alla.

Web-yhteensopivissa sovelluksissa MySQL on piilotettava palomuurin taakse. Viestintä tulee ottaa käyttöön vain sovelluspalvelimien ja Web-palvelimien välillä.

Toinen vaihtoehto on käyttää MySQL skip-networkingia. Kun se on käytössä, MySQL kuuntelee vain paikallisia socket-yhteyksiä ja jättää huomioimatta kaikki TCP-portit.

15 Käytä pienimpiä mahdollisia tietotyyppejä

Anna minun kertoa sinulle tarina. Kun kävin yliopistossa, filosofia oli, että "muisti on niukka". Ne olivat 256 Mt:n kiintolevyjen aikoja. Nykyään kukaan ei näytä välittävän hivenenkään muistista tai kiintolevytilasta. Uusi filosofia on, että "muisti on halpaa". Se saattaa olla totta dollareissa, mutta suurten tietotyyppien lukeminen kestää silti kauemmin kuin pienempien lukeminen. Suuret tietotyypit vaativat enemmän levysektoreita, jotta ne voidaan lukea muistiin.

Moraali on jättää huomiotta kiusaus siirtyä välittömästi suurimpaan tietotyyppiin, kun suunnittelet taulukoita. Harkitse int:n käyttöä bigintin sijaan.

Vältä myös largechar (255) -tekstikenttiä, kun varchar tai pienempi merkki riittää.

Jos käytät oikeaa tietotyyppiä, muistiin tai indeksiavainlohkoon mahtuu enemmän tietueita. Tämä johtaa vähemmän lukemiseen ja nopeampaan suorituskykyyn.

16 Hyödynnä kyselyjen välimuisti

Kyselyjen välimuisti on yksi tehokkaimmista tavoista parantaa suorituskykyä. Useimmissa MySQL-palvelimissa se on oletusarvoisesti käytössä.

Kyselyvälimuisti tallentaa SELECT-käskyn tekstin yhdessä vastaavan tulosjoukon kanssa. Jos palvelin saa myöhemmin samanlaisen käskyn, se hakee tulokset kyselyvälimuistista sen sijaan, että jäsentäisi ja suorittaisi käskyn uudelleen. Kyselyvälimuisti on jaettu istuntojen kesken, joten yhden asiakkaan luoma tulosjoukko voidaan lähettää vastauksena toisen asiakkaan samaan kyselyyn.

Kuitenkin niin hienoa kuin se onkin, kyselyn välimuistilla on rajoituksensa. Ota seuraava lausunto:

Ongelmana tässä on se, että kyselyt sisältävät tiettyjä ei-deterministisiä funktioita, kuten NOW() ja RAND(). MySQL ei voi laskea tällaisia toimintoja etukäteen, joten niitä ei lopulta tallenneta välimuistiin.

Onneksi siihen on helppo ratkaisu: voit tallentaa funktion tulokset muuttujaan.

17 Älä muokkaa vedostiedostoja

Dump-tiedostot ovat erittäin harhaanjohtavia ja voivat aiheuttaa korruptiota. Miksi? Jos olet koskaan nähnyt mysqldumpin luomia dump-tiedostoja, olet samaa mieltä, että ne näyttävät tavallisilta, harmittomilta tekstitiedostoilta. Siksi useimmat ihmiset muokkaavat niitä tavallisessa tekstieditorissa, mikä aiheuttaa vikoja.

Jos olet koskaan yrittänyt muokata vedostiedostoja, opit nopeasti, että ne ovat kaikkea muuta kuin tekstitiedosto. Joten ainoa taattu tapa välttää ongelmia on jättää vedostiedostot rauhaan.

18 Käytä MyISAM-lohkokokoasetusta

MyISAM-taulukkojen indeksien lohkokokojen asetus on yksi MySQL:n parhaista käytännöistä. Se löytyy avainpuskurin .MYI-tiedostoista sekä levyltä. Asetus näyttää tältä: myisam_block_size.

Sen oletusarvo on 1k. Se on melko pieni ollakseen optimaalinen nykyaikaisessa järjestelmässä. Useimmat tiedostojärjestelmät käyttävät suurempia lohkoja. Ja tiedämme, että yhden indeksilohkon kirjoittaminen vaatii lukemisen ja sen jälkeen kirjoittamisen. Käyttöjärjestelmän ei koskaan tarvitse kirjoittaa alla olevalle levylle, kunhan lohkon koko on yhtä suuri tai suurempi kuin tiedostojärjestelmän lohkokoko.

19 Ota delay_key_write käyttöön

Delay_key_write on oletusarvoisesti pois päältä. Siihen on syynsä. Jos koit kaatumisen kesken projektin, tietokantasi voi vioittua.

Joten miksi haluat ottaa sen käyttöön? Syy on yksinkertainen. Koska delay_key_write varmistaa, että tietokanta ei huuhtele MyISAM-avaintiedostoa jokaisen kirjoittamisen jälkeen. Siksi, jos kirjoitat lähitulevaisuudessa, säästät melko paljon aikaa.

Tässä on toinen hieno vinkki, jonka valitsimme MySQL:n parhaista käytännöistä: delay_key_write-toiminnon käyttöönotto on erilainen jokaisessa versiossa. Jos haluat nähdä, kuinka se otetaan käyttöön tietyssä versiossa, katso virallinen MySQL-sivuston käyttöopas.

20 Käytä Stack Tracea virheiden eristämiseen

Näiden MySQL:n parhaiden käytäntöjen mukaisesti tämä vihje on lainattu Sky SQL:stä, koska se on aivan liian yksinkertainen ja liian kätevä jätettäväksi pois.

MySQL stack_tracea voidaan käyttää erilaisten virheiden eristämiseen. Sen sijaan, että kaivautuisi kaikkeen mahdolliseen käyttötarkoitukseen, ohjelmoija saattaa haluta huomioida, kuinka helposti nollaosoitin voi pilata koodisi.

Tämän Sky SQL -vinkin avulla havaitseminen, seuranta ja korjaaminen on paljon helpompaa.

21 MySQL ROOT-salasanan vaihtaminen

ROOT-salasanan vaihtaminen saattaa tuntua yksinkertaiselta, mutta sen tekeminen on yhtä tärkeää kotikäyttöjärjestelmälle kuin MySQL-palvelimille.

Joskus käy niin, että liian innokkaat aloittelijat ja hajamieliset kokeneet käyttäjät eivät ymmärrä, miksi he eivät voi muuttaa tiettyjä asetuksia ja palauttavat virheitä. Se voi olla niin yksinkertaista kuin antaa itsellesi ROOT-käyttöoikeus. Käyttäjän ei tarvitse edes googlettaa sitä.

Näin asetat ROOT-salasanan ja vaihdat käyttäjän ROOT-salasanan:

//Straightforward MySQL 101 $mysqladmin -u rootpassword [Type in selectedpassword] //Changingusers ROOT password $mysqladmin -u root -p [type oldpassword] newpass [hit enter and type new password. Pressenter] //Use mysqlsqlcommand $mysql -u root -p //prompt "mysql>" pops up. Enter: $use MySQL; //Enter usernameyouwant to change the password for $update user set password=PASSWORD (Type new PasswordHere) where User = 'username'; //Don'tforget the previoussemicolon, nowreload the settings for the user'sprivileges $flush privileges; $quit

22 Korjaa asetustiedostot

MySQL Tuner on Perl-skripti, joka voi jotenkin optimoida suorituskykyäsi ehdottamalla muutoksia asetustiedostoihisi.

Jos jotkut MySQL:n vinkit ja temput ovat hämmästyttävän käteviä, MySQL Tunerin kaltaiset työkalut ovat jumalan lahja, joka ansaitsee kuulua omaan kategoriaansa.

Se on jotain, jota voivat käyttää niin aloittelijat kuin ammattilaisetkin. MySQL Tuner ei ole erityinen MySQL-vihje, joten siihen voidaan soveltaa useita säätöjä ja modifikaatioita. Mitä enemmän käytät sitä, sitä enemmän säätöjä voit oppia hakemaan omaan käyttöön.

Se voi aluksi tuntua pelottavalta, mutta siksi manuaalin –man db sekä kaikki muistiinpanot, tarvittavat lukemat ja asetukset löydät projektin viralliselta kotisivulta. Kaiken tämän vuoksi MySQL Tunerista tulee nopeasti työkalu MySQL:n nopeuttamiseen ja asetustiedostojesi testaamiseen.

Lopettaa ajatukset parhaista MySQL-käytännöistä

Tietojenkäsittelytieteen maailmassa MySQL on epäilemättä yksi tärkeimmistä ja vaikutusvaltaisimmista koskaan ilmestyneistä ohjelmista. Se on niin mukaansatempaava ja monipuolinen, että juuri kun näyttää siltä, että kaikki mahdolliset temput ja pikakuvakkeet on löydetty, joku muu soittaa uudella tavalla käyttää uutta argumenttia tai asetusta.

Olemme hahmotellut vain MySQL:n parhaita käytäntöjä, jotka kaikkien tulisi tietää. Mutta mitä enemmän käytät MySQL:ää, sitä enemmän käytät näitä. Voit harjoitella SQL:ää verkossa ja asettaa itsellesi SQL-testejä. Kehität sitten omia temppujasi ja löydät omat huippuvinkkesi, joihin voit luottaa, kuten SQL:n rajoittaminen, SQL-rivinumerot, ja ymmärrät, miksi sinun pitäisi ylipäätään valita MySQL.

Jos pidit tämän artikkelin lukemisesta MySQL:n parhaista käytännöistä, sinun tulee lukea myös nämä:

: wpdatatables.com