- 1. Convenzione di denominazione
- 2. Usa sempre il tipo di dati corretto
- 3. Usa CHAR (1) su VARCHAR (1)
- 4. CHAR per dati fissi
- 5. Evita di utilizzare formati di data regionali
- 6. Ottimizza le tue richieste per la cache
- 7. Evita di usare "SELECT *" nelle tue query
- 8. Usa LIMIT 1 Quando vuoi solo una singola riga
- 9. Uso di ***** BY
- 10. Scegli il motore base adatto
- 11. Usa la clausola EXISTS ogni volta che è necessario
- 12. Usa EXPLAIN nelle tue query SELECT
- 13. Indicizza e usa lo stesso tipo di colonna per i join
- 14. Usa NOT NULL se puoi
- 15. Le tabelle a dimensione fissa (statica) sono più veloci
- 16. Partizionamento verticale
- 17. Memorizza gli indirizzi IP come UNSIGNED INT
- 18. Crea viste per semplificare l'uso comune nelle tabelle
- 19. Non usare ***** PER RAND ()
- 20. Ottimizza la clausola WHERE
Il database MySQL è diventato il database relazionale open source più popolare al mondo grazie alle sue elevate prestazioni, coerenza, elevata affidabilità e facilità d'uso. Tuttavia, questi vantaggi che ci offre sono spesso influenzati dal modo in cui ci lavoriamo.
In questo tutorial imparerai una serie di suggerimenti che saranno molto utili e ci permetteranno di ottenere il massimo sia dal punto di vista del programmatore che dell'amministratore del database.
Quale strumento utilizzeremo?Console di comando MySQL '] MySQL ha un programma, chiamato con lo stesso nome del database (mysql) che viene utilizzato per gestire il database dalla riga di comando.
finestreÈ in una directory come:
C: \ Programmi \ MySQL \ MySQL Server 5.6 \ bin
La directory può variare, ad esempio può essere posizionata nella radice del disco C: o in qualsiasi altro luogo in cui potremmo aver installato MySQL. Per accedere alla console MySQL in Windows dovremo trovarci all'interno di quella directory.
LinuxVedere il seguente collegamento:
Accedi a MySQL da Linux
MacComputer con sistema Mac OS X hanno il terminale della riga di comando integrato tra le applicazioni disponibili. Per accedere allo stesso comando viene utilizzato come in Linux.
phpMyAdminÈ uno strumento software gratuito scritto in PHP, utilizzato per amministrare MySQL tramite Internet. Se hai installato un ambiente di sviluppo locale come XAMPP o WAMP, questo strumento sarà già installato. Se hai un piano di hosting con un pannello amministrativo, la maggior parte di loro offre questo strumento come amministratore di database MySQL.
[color = rgb (169,169,169)] phpMyAdmin da XAMPP [/ color]
INGRANDIRE
[color = # a9a9a9] phpMyAdmin da CPanel [/ color]
Avendo a portata di mano questi 2 strumenti possiamo iniziare a testare tutte queste buone pratiche che vi mostriamo di seguito.
Questi sono Le 20 migliori pratiche migliori di MySQL:
1. Convenzione di denominazione
Esistono standard di codifica per ogni linguaggio di programmazione, ma in termini di MySQL non abbiamo trovato pratiche di codifica universali che tutti seguano. Tuttavia, esaminando diversi framework open source basati su PHP, filtriamo alcune regole applicative generali che ci aiuteranno a scrivere query SQL più rapidamente, eliminando confusione e conflitti, sia nella query che nel linguaggio di programmazione che utilizziamo.
Regole generaliTieni a mente le seguenti regole per evitare problemi.
- Usa le lettere minuscole in quanto ti aiuta nella velocità di digitazione, eviterà errori in caso di funzioni sensibili a lettere minuscole e maiuscole, ecc.
- Non usare spazi bianchi, usa invece il carattere di sottolineatura (_).
- Non utilizzare numeri nei nomi, solo caratteri alfabetici inglesi.
- Utilizzare nomi comprensibili validi.
- I nomi dovrebbero essere autoesplicativi.
- I nomi non devono contenere più di 64 caratteri.
- Evita di usare prefissi.
Regole per i nomi dei databaseSegui tutte le regole generali di cui sopra.
- Il nome può essere sia singolare che plurale, ma il database rappresenta un database quindi dovrebbe essere il più possibile singolare.
- Evita i prefissi dove possibile.
Regole per i nomi delle tabelleUsa lettere minuscole per i nomi delle tabelle: MySQL è solitamente ospitato su server Linux, fa distinzione tra maiuscole e minuscole, quindi la migliore pratica è usare i nomi delle tabelle in minuscolo.
- I nomi delle tabelle devono essere al singolare: la tabella è un'unica entità così come il modello, quindi è strano avere il nome della tabella al plurale.
- Prefissi nel nome della tabella: Abbiamo visto molte volte che le tabelle sono precedute dal nome del database o dal nome del progetto. A volte questo è necessario quando nel nostro progetto abbiamo molti database per superare la limitazione di alcuni provider di hosting. Ma se non è necessario e il nostro progetto è piccolo, evita di usare i prefissi.
Nome dei campiUtilizzare tutte le regole precedenti, ovvero utilizzare lettere minuscole, non utilizzare spazi vuoti, non utilizzare numeri ed evitare prefissi.
- Usa una o due parole brevi dove possibile.
- I nomi dei campi devono poter essere compresi, ad esempio: prezzo, nome_azienda, ecc.
- Nome della colonna primaria: la chiave primaria può avere il nome id o il nome della tabella _id. Dipenderà dalla scelta.
- Evita di utilizzare parole riservate nei campi: *****, data, ecc. In questi casi è preferibile utilizzare prefissi come record_date, ecc.
- Evita di usare nomi di colonna con lo stesso nome di tabella. Ciò può causare confusione durante la scrittura delle query.
- Evita i nomi in acronimi abbreviati o concatenati.
2. Usa sempre il tipo di dati corretto
Utilizzare i tipi di dati in base alla natura dei dati. L'utilizzo di tipi di dati irrilevanti può occupare più spazio o causare errori.
EsempioL'utilizzo di varchar (20) per memorizzare i valori datetime anziché DATETIME può causare errori durante i calcoli dell'ora relativi alla data ed è possibile anche in caso di archiviazione dati non valida.
3. Usa CHAR (1) su VARCHAR (1)
Se hai memorizzato una singola stringa di caratteri, usa CHAR (1) invece di VARCHAR (1) perché VARCHAR (1) richiederà un byte in più per memorizzare le informazioni. Con cui tenerne conto nella gestione di questi personaggi.
4. CHAR per dati fissi
Usa i dati CHAR per memorizzare solo i dati a lunghezza fissa
Esempio:
L'utilizzo di CHAR (1000) invece di VARCHAR (1000) consuma più spazio se la lunghezza dei dati è inferiore a 1000.
5. Evita di utilizzare formati di data regionali
Quando si utilizzano i tipi di dati:
- APPUNTAMENTO
- DATA
Utilizza sempre il formato AAAA-MM-GG o il formato della data ISO adatto al tuo motore SQL. Altri formati regionali come DD-MM-YYY, DD-MM-YYYY non verranno archiviati correttamente.
6. Ottimizza le tue richieste per la cache
la maggior parte I server MySQL hanno il sistema di cache abilitato. È uno dei metodi più efficaci per migliorare le prestazioni, che deriva dalla mano del motore di database. Quando la stessa richiesta viene eseguita più volte, il risultato viene recuperato dalla cache, che è molto più veloce.
Il seguente esempio è in PHP:
// La cache NON funziona $ r = mysql_query ("SELECT name FROM users WHERE record> = CURDATE ()");
// La cache FUNZIONA $ oggi = data ("Y-m-d"); $ r = mysql_query ("SELECT nome FROM utenti WHERE record> = '$ oggi'"); Il motivo per cui non funziona nel primo caso è a causa dell'uso di CURDATE (). Può essere applicato a tutte le funzioni non deterministiche, come NOW () e RAND (). Poiché il risultato restituito dalla funzione può cambiare, MySQL decide di disabilitare la cache su quella query.
7. Evita di usare "SELECT *" nelle tue query
Come regola generale, più dati vengono letti dalle tabelle, più lenta viene eseguita una query. Considerando che alcune tabelle di produzione possono contenere decine di colonne, alcune delle quali sono costituite da tipi di dati di grandi dimensioni, non sarebbe saggio selezionarle tutte.
È una buona abitudine specifica le colonne necessarie nell'istruzione SELECT.
8. Usa LIMIT 1 Quando vuoi solo una singola riga
A volte quando stai interrogando le tue tabelle e sai che hai bisogno solo di una singola riga. In questi casi, è necessario richiedere un singolo risultato al database, altrimenti controllerà tutte le corrispondenze nella clausola WHERE.
In questi casi, l'aggiunta di LIMIT 1 alla query può migliorare notevolmente la velocità. In questo modo il database interromperà la scansione dei risultati nel momento in cui ne trova uno, invece di esaminare l'intera tabella o un indice.
// Ho utenti di Madrid? // cosa NON fare: $ r = mysql_query ("SELECT * FROM user WHERE city =' Madrid '"); if (mysql_num_rows ($ r)> 0) {//…} // molto meglio: $ r = mysql_query ("SELECT 1 FROM user WHERE city =' Madrid 'LIMIT 1"); if (mysql_num_rows ($ r)> 0) {//…}[colore = # a9a9a9]Raccomandazione LIMIT[/Colore]
9. Uso di ***** BY
L'uso di ***** DI può rallentare il tempo di risposta in ambienti multiutente. Pertanto si consiglia di utilizzare la clausola ***** BY solo quando necessario.
Non abusare del suo utilizzo.
10. Scegli il motore base adatto
Se sviluppi un'applicazione che legge i dati più spesso della scrittura.
(ad esempio: motore di ricerca), selezionare il motore di archiviazione MyISAM.
Se sviluppi un'applicazione che scrive dati più frequentemente della lettura
(ad esempio: banca in tempo reale), scegli il motore di archiviazione InnoDB.
La scelta del motore di archiviazione sbagliato influirà sulle prestazioni delle query.
11. Usa la clausola EXISTS ogni volta che è necessario
Se vuoi controllare i dati, non usare:
If (SELECT count (*) from Table WHERE col = 'some value')> 0Utilizzare invece la clausola EXISTS:
If EXISTS (SELECT * from Table WHERE col = 'some value')Che è più veloce nei tempi di risposta.
12. Usa EXPLAIN nelle tue query SELECT
L'uso della parola chiave EXPLAIN ti fornirà molti dettagli interni su ciò che MySQL fa per eseguire la tua query. Questo può aiutarti a individuare i colli di bottiglia e altri problemi con la tua query o la struttura della tabella.
Il risultato di una query EXPLAIN ti mostrerà gli indici che vengono utilizzati, come viene esplorata la tabella, come viene ordinata, ecc.
Seleziona una query SELECT (preferibilmente complessa, con join) e aggiungi la parola EXPLAIN all'inizio di tutto. Restituirà i risultati in una semplice tabella. Ad esempio, supponiamo di aver dimenticato di indicizzare una colonna, ci mostrerebbe la seguente schermata:
INGRANDIRE
Dopo aver aggiunto l'indice alla tabella di stato, sarebbe simile a questo:
INGRANDIRE
13. Indicizza e usa lo stesso tipo di colonna per i join
Se la tua applicazione contiene molte istruzioni JOIN, devi assicurarti che le colonne a cui unisci siano indicizzate in entrambe le tabelle. Questo influenza come MySQL ottimizza internamente operazioni di UNIONE.
Inoltre, le colonne a cui stai unendo devono essere dello stesso tipo. Ad esempio, se stai unendo una colonna di tipo DECIMAL con una colonna di tipo INT di un'altra tabella, MySQL non sarà in grado di utilizzare almeno uno dei due indici. Anche la codifica dei caratteri deve essere dello stesso tipo per le colonne di Tipo di stringa.
// cerco aziende nella mia città $ r = mysql_query ("SELECT company_name FROM users LEFT JOIN companyias ON (users.city = companyias.city) WHERE users.id = $ user_id");Entrambe le colonne della città devono essere indicizzate ed entrambe dovrebbero essere dello stesso tipo e codifica dei caratteri o MySQL dovrà eseguire una scansione completa delle tabelle.
14. Usa NOT NULL se puoi
A meno che tu non abbia un motivo specifico per utilizzare il valore NULL, dovresti sempre impostare le tue colonne su NOT NULL.
Prima di tutto, chiediti se ci sarebbe una differenza tra avere una stringa vuota e un valore NULL (o per i campi INT: 0 contro NULL). Se non ci sono problemi tra i due valori, non è necessario un campo NULL. Le colonne NULL richiedono spazio aggiuntivo e possono aggiungere complessità alle istruzioni di confronto. Evitali ogni volta che puoi.
In ogni caso, capiamo che in alcuni casi molto specifici c'è un motivo per usare colonne NULL, il che non è sempre un male.
15. Le tabelle a dimensione fissa (statica) sono più veloci
Quando ogni colonna in una tabella è a lunghezza fissa, l'intera tabella è considerata "statica" o "a lunghezza fissa".
Alcuni esempi di tipi di colonne che NON hanno dimensioni fisse sono:
- VARCHAR
- TESTO
- BLOB
Se includi solo uno di questi tipi di colonna, la tabella non sarà più di dimensione fissa e dovrà essere trattata in modo diverso dal Motore MySQL.
Le tabelle di dimensioni fisse possono aumentare la produttività perché è più veloce per il motore MySQL cercare tra i tuoi record. Quando vuoi leggere una riga specifica nella tabella, puoi calcolarne rapidamente la posizione. Se la dimensione della riga non è fissa, ogni volta che devi cercare, devi prima interrogare l'indice della chiave primaria.
Sono anche più facili da cercare e da ricostruire dopo un incidente. Ma d'altra parte potrebbero anche occupare più spazio.
16. Partizionamento verticale
Il partizionamento verticale è l'atto di separare verticalmente la struttura della tabella per motivi di ottimizzazione.
Esempio 1:
Avrai sicuramente una tabella utente che contiene un indirizzo postale, che non viene utilizzato molto spesso. Qui puoi dividere la tabella e memorizzare gli indirizzi in una tabella separata. In questo modo, la tua tabella utente principale avrebbe una dimensione più stretta. Come sai, più piccoli sono i tavoli più veloci.
Esempio 2:
Hai un campo "last_access" nella tua tabella. Viene aggiornato ogni volta che un utente accede alla tua pagina. Ma ogni accesso fa sì che la cache delle query per quella tabella venga liberata. Quello che puoi fare è mettere questo campo in un'altra tabella in modo che le modifiche nella tua tabella utente siano ridotte al minimo.
Ma devi anche assicurarti di non dover unire costantemente le due tabelle dopo il partizionamento o subirai un calo delle prestazioni, proprio l'opposto di quello che stavamo cercando.
17. Memorizza gli indirizzi IP come UNSIGNED INT
Molti programmatori creerebbero a Campo VARCHAR (15) senza rendersi conto che possono memorizzare gli indirizzi IP come numeri interi. Quando usi un INT, usi solo 4 byte in memoria e ha anche una dimensione fissa nella tabella.
Ma devi assicurarti che il la colonna è UNSIGNED INT (intero senza segno) perché gli indirizzi IP utilizzano l'intero intervallo senza segno a 32 bit.
Nelle tue domande puoi usare il INET_ATON () funzione per convertire un indirizzo IP in un numero intero e INET_NTOA() per fare il contrario. Ci sono anche funzioni simili in PHP chiamate ip2long() sì long2ip().
18. Crea viste per semplificare l'uso comune nelle tabelle
Le viste aiutano a semplificare sia gli schemi complessi che l'implementazione della sicurezza. Un modo in cui contribuiscono alla parte di sicurezza è che ti consente di nascondere i nomi dei campi agli sviluppatori.
Può essere utilizzato anche per filtrare le colonne non indicizzate, lasciando solo i campi che vengono visualizzati più velocemente nella ricerca.
19. Non usare ***** PER RAND ()
Questo è uno di quei trucchi che a prima vista suonano benissimo e in cui molti programmatori alle prime armi tendono a cadere. Potresti non aver realizzato l'incredibile collo di bottiglia che può essere causato dall'utilizzo di questa tecnica nelle tue richieste.
Se hai davvero bisogno di tabelle casuali per il tuo risultato, ci sono modi molto migliori per farlo. È chiaro che occuperanno più codice, ma eviterai un possibile collo di bottiglia che aumenta esponenzialmente man mano che il tuo contenuto cresce.
Il problema è che MySQL dovrà essere eseguito BORDO () (che richiede potenza di elaborazione) per ciascuna delle righe prima di ordinarle e restituire una singola riga.
// il modo per NON farlo: $ r = mysql_query ("SELECT username FROM user ***** BY RAND () LIMIT 1"); // molto meglio: $ r = mysql_query ("SELECT count (*) FROM user"); $ d = mysql_fetch_row ($ r); $ rand = mt_rand (0, $ d [0] - 1); $ r = mysql_query ("SELECT username FROM username LIMIT $ rand, 1");Quindi selezioni un numero casuale inferiore al numero di risultati e lo usi come offset nella clausola LIMIT.
20. Ottimizza la clausola WHERE
Questi sono alcuni suggerimenti per ottimizzare la clausola WHERE:
- Rimuovere le parentesi non necessarie. Ad esempio:
Da A5 AND b = c AND a = 5
- CONTANO (*) è ottimizzato per restituire un SELECT molto più velocemente, purché si trovi in una tabella e senza utilizzare WHERE. Ad esempio:
SELEZIONA COUNT (*) FROM tabella.
- Il Opzione SQL_SMALL_RESULT, può essere utilizzato con RAGGRUPPA PER o DISTINTO per indicare che il set di risultati è piccolo. In questo caso, MySQL utilizza tabelle temporanee molto veloci per memorizzare la tabella risultante invece di utilizzare l'ordinamento.
Tutorial MySQL
