MySQL è il secondo sistema di gestione di database relazionali open source più popolare al mondo. È utilizzato in tutto il mondo per le sue prestazioni costantemente veloci, l’elevata affidabilità e la facilità d’uso. Questo articolo presenta alcune delle migliori pratiche MySQL.

Con loro, puoi esercitarti con SQL e impostarti alcuni esercizi SQL, conoscere il limite SQL e la pratica SQL online, nonché sui problemi di pratica SQL più comuni.

Ti aiuterà a comprendere il numero di riga SQL, SQL superiore, offset limite MySQL e pratica SQL online.

Se stai cercando un modo semplice e piacevole per visualizzare un gran numero di righe e colonne ai visitatori del tuo sito Web WordPress con tabelle e grafici interattivi colorati, informativi e reattivi, puoi usarlo con il plug-in wpDataTables, controlla i prezzi e tutti i funzionalità che fornisce qui.

1 Utilizzare sempre il tipo di dati corretto

Una delle migliori pratiche MySQL più importanti consiste nell’utilizzare i tipi di dati in base alla natura dei dati. L’utilizzo di tipi di dati irrilevanti può consumare più spazio o causare errori.

Ad esempio: l’utilizzo di varchar (20) invece del tipo di dati DATETIME per la memorizzazione dei valori di data e ora comporterà errori nei calcoli relativi alla data e all’ora. Inoltre, è possibile che vengano memorizzati dati non validi.

2 Utilizzare CHAR (1) su VARCHAR (1)

VARCHAR (1) richiede byte extra per memorizzare le informazioni, quindi se si esegue una stringa di un singolo carattere, è meglio usare CHAR (1).

3 Utilizzare il tipo di dati CHAR per memorizzare solo dati di lunghezza fissa

Ad esempio: se la lunghezza dei dati è inferiore a 1000, l’utilizzo di char (1000) anziché varchar (1000) consumerà più spazio.

4 Evitare di utilizzare formati di data regionali

Quando si utilizza il tipo di dati DATETIME o DATE, utilizzare sempre il formato data AAAA-MM-GG o il formato data ISO adatto al proprio motore SQL. I formati regionali come GG-MM-AAAA o MM-GG-AAAA non verranno archiviati correttamente.

5 Colonne chiave dell’indice

È auspicabile che la query restituisca il risultato velocemente, quindi assicurati di indicizzare le colonne utilizzate nelle clausole JOIN.

Nel caso in cui utilizzi l’istruzione UPDATE che coinvolge più di una tabella, assicurati di indicizzare tutte le colonne utilizzate per unire le tabelle.

6 Non utilizzare funzioni su colonne indicizzate…

… perché poi l’indice perde il suo scopo.

Ad esempio, supponiamo di voler ottenere dati in cui i primi due caratteri del codice cliente sono AK. Scrivere:

SELECT columns FROM table WHERE customer_code like 'AK%'

e non scrivere

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

Come mai? Il primo esempio utilizzerà l’indice, che risulterà in un tempo di risposta più rapido.

**7 Utilizzare SQL SELECT * solo se necessario**

Seguendo l’elenco delle migliori pratiche MySQL, non utilizzare alla cieca SELECT * nel codice. Se la tabella ha molte colonne, verranno restituite tutte. Ciò rallenterà il tempo di risposta, soprattutto se invii il risultato a un’applicazione front-end.

Invece, digita in modo esplicito i nomi delle colonne che sono effettivamente necessari.

Nota: ricorda che tutte le istruzioni SELECT richiedono una clausola WHERE.

8 Utilizzare la clausola ORDER BY solo se necessario

Se vuoi mostrare il risultato nell’applicazione front-end, lascia che ORDINA il set di risultati. Se si esegue questa operazione in SQL, il tempo di risposta potrebbe essere rallentato nell’ambiente multiutente.

9 Scegli un Motore di database appropriato

Se sviluppi un’applicazione che legge i dati più spesso della scrittura (ad esempio un motore di ricerca), scegli il motore di archiviazione MyISAM.

La scelta del motore di archiviazione sbagliato influirà sulle prestazioni.

10 Utilizzare la clausola EXISTS dove necessario

Per verificare l’esistenza dei dati, utilizzare la clausola EXISTS che è più veloce nei tempi di risposta. Ad esempio, utilizzare:

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

Non usare :

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

11 SPIEGARE le vostre query SELECT

Se utilizzi la parola chiave EXPLAIN, puoi ottenere informazioni dettagliate su ciò che MySQL sta facendo per eseguire la tua query. Questo può aiutarti a rilevare problemi con la tua query o con le strutture delle tabelle (ad es. colli di bottiglia).

Una query EXPLAIN mostra quali indici vengono utilizzati, come la tabella viene scansionata, ordinata, ecc.

Tutto quello che devi fare è aggiungere la parola chiave EXPLAIN davanti a una query SELECT (preferibilmente complessa con join). Inoltre, se usi phpmyadmin per questo, i tuoi risultati verranno mostrati in una bella tabella.

12 Utilizzare LIMIT 1 quando si ottiene una riga univoca

A volte sai in anticipo che stai cercando solo una riga quando esegui query sulle tue tabelle. Ad esempio, potresti recuperare un record univoco o semplicemente verificare l’esistenza di un numero qualsiasi di record che soddisfano la tua clausola WHERE.

In questi casi, ti consigliamo di utilizzare la funzione limite di MySQL per aumentare le prestazioni. Ecco un’altra delle migliori pratiche MySQL: aggiungi semplicemente LIMIT 1 alla tua query. In questo modo il motore di database non dovrà passare attraverso l’intera tabella o indice. Interromperà la scansione quando trova solo 1 record di ciò che stai cercando.

// do I have anyusersfrom Alabama?

// cosa NON fare:
$r = mysql_query("SELECT * FROM user WHERE state = ‘Alabama’");
if (mysql_num_rows($r) > 0) {
// …
}

// molto meglio:
$r = mysql_query("SELECT 1 FROM user WHERE state = ‘Alabama’ LIMIT 1");
if (mysql_num_rows($r) > 0) {
// …
}

13 Indicizzare e utilizzare gli stessi tipi di colonna per i join

Un altro suggerimento fondamentale per le migliori pratiche MySQL: se la tua applicazione ha molte query JOIN, assicurati che le colonne in base alle quali ti unisci siano indicizzate su entrambe le tabelle. Ciò influisce sull’ottimizzazione interna dell’operazione di join da parte di MySQL.

Inoltre, le colonne unite devono essere dello stesso tipo. Ad esempio, se unisci una colonna DECIMAL a una colonna INT da un’altra tabella, MySQL non sarà in grado di utilizzare nessuno degli indici. Anche le codifiche dei caratteri devono essere dello stesso tipo per le colonne di tipo stringa.

// 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");

// entrambe le colonne di stato dovrebbero essere indicizzate
// ed entrambe dovrebbero essere dello stesso tipo e codifica dei caratteri
// o MySQL potrebbe eseguire scansioni complete della tabella

14 Nascondere MySQL da Internet

Amministratori di database esperti e personale di sicurezza lo sanno: non ospitare mai il database nella radice del server Web.

Per le applicazioni abilitate al Web, MySQL dovrebbe essere nascosto dietro un firewall. La comunicazione deve essere abilitata solo tra i server delle applicazioni ei server Web.

Un’altra opzione consiste nell’utilizzare la rete di salto MySQL. Quando è abilitato, MySQL ascolta solo le connessioni socket locali e ignora tutte le porte TCP.

15 Utilizzare i tipi di dati più piccoli possibili

Lascia che ti racconti una storia. Quando frequentavo il college, la filosofia era che "la memoria è scarsa". Erano i giorni dei dischi rigidi da 256 MB. Al giorno d’oggi, a nessuno sembra importare un briciolo di memoria o spazio sul disco rigido. La nuova filosofia è che "la memoria costa poco". Potrebbe essere vero in termini di dollari, ma la lettura di tipi di dati di grandi dimensioni richiede ancora più tempo rispetto alla lettura di tipi di dati più piccoli. I tipi di dati di grandi dimensioni richiedono la lettura in memoria di più settori del disco.

La morale è ignorare la tentazione di passare immediatamente al tipo di dati più grande quando si progettano le tabelle. Pensa di usare un int invece di un bigint.

Inoltre, evita i campi di testo largechar (255) quando è sufficiente un varchar o un char più piccolo.

Se si utilizza il tipo di dati corretto, più record si adatteranno alla memoria o al blocco chiave dell’indice. Ciò porta a un minor numero di letture e prestazioni più veloci.

16 Approfitta della memorizzazione nella cache delle query

La memorizzazione nella cache delle query è uno dei metodi più efficaci per migliorare le prestazioni. La maggior parte dei server MySQL lo ha abilitato per impostazione predefinita.

La cache delle query memorizza il testo di un’istruzione SELECT insieme al set di risultati corrispondente. Se in seguito il server riceve un’istruzione identica, recupererà i risultati dalla cache delle query anziché analizzare ed eseguire nuovamente l’istruzione. La cache delle query è condivisa tra le sessioni, quindi un set di risultati generato da un client può essere inviato in risposta alla stessa query emessa da un altro client.

Tuttavia, per quanto grande sia, la memorizzazione nella cache delle query ha i suoi limiti. Prendi la seguente affermazione:

Il problema qui è che le query contengono alcune funzioni non deterministiche, come NOW() e RAND(). MySQL non è in grado di calcolare tali funzioni in anticipo, quindi finiscono per non essere memorizzate nella cache.

Fortunatamente, esiste una soluzione semplice: è possibile memorizzare i risultati della funzione in una variabile.

17 Non modificare i file di dump

I file di dump sono molto ingannevoli e possono causare danni. Come mai? Se hai mai visto i file di dump creati da mysqldump, sarai d’accordo sul fatto che assomiglino a normali file di testo innocui. Questo è il motivo per cui la maggior parte delle persone li modifica in un editor di testo standard, che fa apparire i danneggiamenti.

Se hai mai provato a modificare i file di dump, hai subito imparato che sono tutt’altro che un file di testo. Quindi, l’unico modo garantito per evitare problemi è lasciare i file dump da soli.

18 Utilizzare l’impostazione della dimensione del blocco MyISAM

L’impostazione delle dimensioni dei blocchi negli indici delle tabelle MyISAM è una delle migliori pratiche MySQL. Può essere trovato nei file .MYI nel buffer delle chiavi, così come nel disco. L’impostazione è simile a questa: myisam_block_size.

Ha un valore predefinito di 1k. È abbastanza piccolo per essere ottimale su un sistema moderno. I blocchi di dimensioni maggiori sono utilizzati dalla maggior parte dei file system. E sappiamo che scrivere un singolo blocco di indice richiede una lettura e poi una scrittura. Il sistema operativo non dovrà mai scrivere sul disco sottostante purché la dimensione del blocco sia uguale o maggiore della dimensione del blocco del file system.

19 Attiva delay_key_write

Delay_key_write è disattivato per impostazione predefinita. C’è una ragione per questo. Se si verifica un arresto anomalo nel mezzo del progetto, il database potrebbe danneggiarsi.

Quindi, perché vorresti accenderlo? Il motivo è semplice. Perché delay_key_write assicura che il database non cancellerà il file della chiave MyISAM dopo ogni singola scrittura. Pertanto, se stai scrivendo un’altra scrittura nel prossimo futuro, risparmierai un bel po’ di tempo.

Ecco un altro suggerimento interessante che abbiamo scelto dalle best practice di MySQL: l’attivazione di delay_key_write è diversa per ogni versione. Per vedere come attivarlo in una versione specifica, consulta il manuale ufficiale del sito MySQL.

20 Usa Stack Trace per isolare i bug

Seguendo queste migliori pratiche MySQL, questo suggerimento è preso in prestito da Sky SQL perché è semplicemente troppo semplice e troppo comodo per essere lasciato fuori.

MySQL stack_trace può essere utilizzato per isolare vari bug. Invece di scavare in tutti i possibili usi, il programmatore potrebbe voler prendere nota della facilità con cui un puntatore nullo può rovinare il tuo codice.

Con questo suggerimento Sky SQL, individuare, tracciare e riparare diventa molto più semplice.

21 MySQL modifica della password ROOT

La modifica della password ROOT potrebbe sembrare semplice, ma sapere come farlo è altrettanto importante per il tuo sistema operativo domestico così come per i server MySQL.

A volte capita che i principianti troppo zelanti e gli utenti esperti distratti non riescano a capire perché non possono modificare determinate impostazioni e restituiscono errori. Può essere semplice come concedersi l’accesso alla ROOT. Un utente non dovrebbe nemmeno doverlo cercare su Google.

Ecco come impostare la password ROOT e come modificare la password ROOT di un utente:

//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 Correggi i file di configurazione

MySQL Tuner è uno script Perl che può in qualche modo ottimizzare le prestazioni suggerendo modifiche ai file di configurazione.

Se alcuni suggerimenti e trucchi in MySQL sono incredibilmente convenienti, strumenti come MySQL Tuner sono una manna dal cielo che merita di essere in una categoria tutta loro.

È qualcosa che può essere utilizzato sia dai principianti che dai professionisti. MySQL Tuner non è un suggerimento specifico per MySQL, quindi è possibile applicare una varietà di modifiche e mod. Più lo usi, più modifiche puoi imparare ad applicare per il tuo uso.

All’inizio può sembrare intimidatorio, ma è per questo che puoi trovare il manuale –man db così come tutte le note, le letture necessarie e le impostazioni sulla homepage ufficiale del progetto. Con tutto ciò, MySQL Tuner dovrebbe diventare rapidamente il tuo strumento di riferimento per velocizzare MySQL e testare i tuoi file di configurazione.

Considerazioni finali sulle migliori pratiche MySQL

Nel mondo dell’informatica, MySQL è senza dubbio uno dei programmi più importanti e influenti mai apparsi. È così coinvolgente e versatile, che proprio quando sembra che tutti i possibili trucchi e scorciatoie siano stati scoperti, qualcun altro interviene con un nuovo modo di usare un nuovo argomento o impostazione.

Abbiamo delineato solo le migliori pratiche MySQL che tutti dovrebbero conoscere. Ma più usi MySQL, più li utilizzerai. Puoi esercitarti con SQL online e impostare te stesso test SQL. Quindi svilupperai i tuoi trucchi e troverai i tuoi migliori suggerimenti su cui puoi fare affidamento come limite SQL, numero di riga SQL e capirai perché dovresti selezionare MySQL in primo luogo.

Se ti è piaciuto leggere questo articolo sulle best practice di MySQL, dovresti leggere anche questi:

Fonte di registrazione: wpdatatables.com