9 ottobre 2008 alle 23:37 Ottimizzazione delle query MySQL

• MySQL

Nel lavoro quotidiano si riscontrano errori abbastanza simili durante la scrittura delle query.

In questo articolo vorrei fornire esempi di come NON scrivere query.

• Seleziona tutti i campi
  SELEZIONA * DALLA tabella

  Quando si scrivono query, non utilizzare una selezione di tutti i campi - "*". Elenca solo i campi di cui hai veramente bisogno. Ciò ridurrà la quantità di dati recuperati e inviati. Inoltre, non dimenticare di coprire gli indici. Anche se tutti i campi della tabella sono effettivamente necessari, è meglio elencarli. Innanzitutto, migliora la leggibilità del codice. Quando si utilizza un asterisco, è impossibile sapere quali campi sono presenti nella tabella senza guardarla. In secondo luogo, nel tempo, il numero di colonne nella tabella potrebbe cambiare e se oggi ci sono cinque colonne INT, tra un mese potrebbero essere aggiunti campi TEXT e BLOB, il che rallenterà la selezione.

• Richieste in un ciclo.
  È necessario comprendere chiaramente che SQL è un linguaggio operativo di set. A volte i programmatori abituati a pensare in termini di linguaggi procedurali hanno difficoltà a spostare il loro pensiero sul linguaggio degli insiemi. Questo può essere fatto semplicemente adottando una semplice regola: "non eseguire mai le query in un ciclo". Esempi di come ciò può essere fatto:

  1. Campioni
  $news_ids = get_list("SELECT news_id FROM today_news ");
  while($news_id = get_next($news_ids))
  $news = get_row("SELEZIONA titolo, corpo FROM notizie DOVE news_id = ". $news_id);

  La regola è molto semplice: meno richieste sono, meglio è (anche se ci sono delle eccezioni, come ogni regola). Non dimenticare il costrutto IN(). Il codice sopra può essere scritto in una query:
  SELEZIONA titolo, corpo DA today_news INNER JOIN news USING(news_id)

  2. Inserti
  $log = analizza_log();
  while($record = next($log))
  query("INSERT INTO logs SET valore = ". $log["value"]);!}

  È molto più efficiente concatenare ed eseguire una query:
  INSERISCI IN log (valore) VALORI (...), (...)

  3. Aggiornamenti
  A volte è necessario aggiornare più righe in una tabella. Se il valore aggiornato è lo stesso, tutto è semplice:
  AGGIORNAMENTO notizie SET title="test" WHERE id IN (1, 2, 3).!}

  Se il valore da modificare è diverso per ciascun record, è possibile farlo con la seguente query:
  AGGIORNAMENTO novità SET
  titolo = CASO
  QUANDO news_id = 1 POI "aa"
  QUANDO news_id = 2 POI "bb" FINE
  DOVE news_id IN (1, 2)

  I nostri test mostrano che una richiesta di questo tipo è 2-3 volte più veloce di più richieste separate.

• Esecuzione di operazioni sui campi indicizzati
  SELEZIONA user_id DAgli utenti DOVE blogs_count * 2 = $valore

  Questa query non utilizzerà l'indice, anche se la colonna blogs_count è indicizzata. Per utilizzare un indice, non è necessario eseguire trasformazioni sul campo indicizzato nella query. Per tali richieste, spostare le funzioni di conversione in un'altra parte:
  SELEZIONA user_id DAgli utenti DOVE blogs_count = $valore / 2;

  Esempio simile:
  SELEZIONA user_id FROM utenti DOVE TO_DAYS(CURRENT_DATE) - TO_DAYS(registrato) = DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
  Volere.

• Recupero delle righe solo per contarne il numero
  $risultato = mysql_query("SELECT * FROM tabella", $link);
  $num_righe = mysql_num_righe($risultato);
  Se è necessario selezionare il numero di righe che soddisfano una determinata condizione, utilizzare la query di tabella SELECT COUNT(*) FROM anziché selezionare tutte le righe solo per contare il numero di righe.
• Recupero di righe aggiuntive
  $risultato = mysql_query("SELECT * FROM tabella1", $link);
  while($riga = mysql_fetch_assoc($risultato) && $i< 20) {
  …
  }
  Se hai bisogno solo di n righe di recupero, usa LIMIT invece di scartare linee aggiuntive nell'applicazione.
• Utilizzando ORDINA PER CASO()
  SELECT * FROM tabella ORDER BY RAND() LIMITE 1;

  Se la tabella ha più di 4-5mila righe, ORDER BY RAND() funzionerà molto lentamente. Sarebbe molto più efficiente eseguire due query:

  Se la tabella ha una chiave primaria auto_increment e senza spazi vuoti:
  $rnd = rand(1, query("SELECT MAX(id) FROM tabella"));
  $riga = query("SELECT * FROM tabella WHERE id = ".$rnd);

  O:
  $cnt = query("SELEZIONA COUNT(*) DALLA tabella");
  $riga = query("SELECT * FROM tabella LIMIT ".$cnt.", 1");
  che però può essere anche lento se nella tabella sono presenti un numero molto elevato di righe.

• Utilizzo grande quantità UNISCITI a
  SELEZIONARE
  v.video_id
  a.nome,
  g.genere
  DA
  video AS v
  SINISTRA UNISCITI
  link_actors_videos AS la ON la.video_id = v.video_id
  SINISTRA UNISCITI
  attori AS a ON a.actor_id = la.actor_id
  SINISTRA UNISCITI
  link_genre_video AS lg ON lg.video_id = v.video_id
  SINISTRA UNISCITI
  generi AS g ON g.genre_id = lg.genre_id

  È necessario ricordare che quando si collegano le tabelle uno a molti, il numero di righe nella selezione aumenterà con ogni successivo JOIN. In questi casi, è più veloce dividere tale query in più semplici.

• Utilizzando LIMITE
  SELEZIONA… DALLA tabella LIMIT $inizio, $per_pagina

  Molte persone pensano che una query di questo tipo restituirà $ per_pagina di record (solitamente 10-20) e quindi funzionerà rapidamente. Funzionerà rapidamente per le prime pagine. Ma se il numero di record è elevato ed è necessario eseguire una query SELECT... FROM table LIMIT 1000000, 1000020, per eseguire tale query, MySQL selezionerà prima 1000020 record, scarterà il primo milione e restituirà 20. Questo potrebbe non essere affatto veloce. Non esistono soluzioni banali per risolvere il problema. Molti limitano semplicemente il numero di pagine disponibili a un numero ragionevole. Puoi anche velocizzare tali query utilizzando indici di copertura o soluzioni di terze parti(ad esempio la sfinge).

• Non utilizzare SU AGGIORNAMENTO CHIAVE DUPLICATA
  $riga = query("SELECT * FROM tabella WHERE id=1");

  Se($riga)
  query("UPDATE tabella SET colonna = colonna + 1 WHERE id=1")
  altro
  query("INSERISCI NELLA tabella SET colonna = 1, id=1");

  Una costruzione simile può essere sostituita con una query, a condizione che esista una chiave primaria o univoca per il campo id:
  INSERISCI NELLA tabella SET colonna = 1, id=1 SU AGGIORNAMENTO CHIAVE DUPLICATA colonna = colonna + 1

Leggere

Ho già scritto di un'ampia varietà di query SQL, ma è ora di parlare di cose più complesse, ad esempio una query SQL per selezionare record da più tabelle.

Quando tu ed io abbiamo fatto una selezione da una tabella, tutto è stato molto semplice:

SELECT nomi_di_campi_richiesti FROM nome_tabella DOVE condizione_selezione

Tutto è molto semplice e banale, ma quando si campiona da più tabelle contemporaneamente diventa un po' più complicato. Una difficoltà è far corrispondere i nomi dei campi. Ad esempio, ogni tabella ha un campo ID.

Diamo un'occhiata a questa query:

SELEZIONA * DA tabella_1, tabella_2 DOVE tabella_1.id > tabella_2.id_utente

Molti di coloro che non hanno avuto a che fare con tali query penseranno che tutto sia molto semplice, pensando che solo i nomi delle tabelle siano stati aggiunti prima dei nomi dei campi. In effetti, ciò evita conflitti tra nomi di campo identici. Tuttavia, la difficoltà non sta in questo, ma nell'algoritmo di tale query SQL.

L'algoritmo di lavoro è il seguente: il primo record è preso dalla tabella_1. L'ID di questo record viene preso dalla tabella_1. Quindi la tabella table_2 appare completamente. E tutti i record vengono aggiunti in cui il valore del campo user_id è inferiore all'id del record selezionato in table_1 . Pertanto, dopo la prima iterazione, possono esserci da 0 a un numero infinito di record risultanti. Alla successiva iterazione, viene preso il record successivo della tabella table_1. L'intera tabella table_2 viene nuovamente scansionata e la condizione di selezione table_1.id > table_2.user_id viene nuovamente attivata. Tutti i record che soddisfano questa condizione vengono aggiunti al risultato. L'output può essere un numero enorme di record, molte volte più grande della dimensione totale di entrambe le tabelle.

Se capisci come funziona dopo la prima volta, allora è fantastico, ma in caso contrario, leggi finché non lo capisci appieno. Se lo capisci, sarà più facile.

La precedente query SQL, in quanto tale, viene utilizzata raramente. È stato fornito semplicemente per spiegare l'algoritmo di campionamento multitabella. Ora diamo un'occhiata a una query SQL più tozza. Diciamo che abbiamo due tabelle: con i prodotti (c'è un campoowner_id, che è responsabile dell'ID del proprietario del prodotto) e con gli utenti (c'è un campo id). Vogliamo ottenere tutti i record in un'unica query SQL e ognuno contenga informazioni sull'utente e sul suo prodotto. La voce successiva conteneva informazioni sullo stesso utente e sul suo prossimo prodotto. Quando i prodotti di questo utente si esauriscono, passa all'utente successivo. Dobbiamo quindi unire due tabelle e ottenere un risultato in cui ciascun record contenga informazioni sull'utente e su uno dei suoi prodotti.

Una query simile sostituirà 2 query SQL: per selezionare separatamente dalla tabella con le merci e dalla tabella con gli utenti. Inoltre, tale richiesta corrisponderà immediatamente all'utente e al suo prodotto.

La richiesta in sé è molto semplice (se hai capito la precedente):

SELEZIONA * DA utenti, prodotti DOVE utenti.id = prodotti.owner_id

L'algoritmo qui è già semplice: il primo record viene preso dalla tabella degli utenti. Successivamente, viene preso il suo id e vengono analizzati tutti i record della tabella prodotti, aggiungendo al risultato quelli il cui proprietario_id è uguale all'id della tabella utenti. Pertanto, nella prima iterazione, vengono raccolti tutti i beni del primo utente. Alla seconda iterazione vengono raccolti tutti i prodotti del secondo utente e così via.

Come puoi vedere, le query SQL per la selezione da più tabelle non sono le più semplici, ma i vantaggi che ne derivano possono essere enormi, quindi conoscere ed essere in grado di utilizzare tali query è molto desiderabile.

Nell'ultima lezione abbiamo riscontrato un inconveniente. Quando volevamo sapere chi ha creato l’argomento “biciclette”, abbiamo fatto una richiesta corrispondente:

Invece del nome dell'autore, abbiamo ricevuto il suo identificatore. Ciò è comprensibile, perché abbiamo effettuato una query su una tabella - Argomenti, e i nomi degli autori degli argomenti sono memorizzati in un'altra tabella - Utenti. Pertanto, dopo aver trovato l'identificatore dell'autore dell'argomento, dobbiamo fare un'altra query alla tabella Utenti per scoprire il suo nome:

SQL offre la possibilità di combinare tali query in una sola trasformandone una in una sottoquery (query nidificata). Quindi, per scoprire chi ha creato l'argomento "biciclette", faremo la seguente query:

Cioè, dopo la parola chiave DOVE, scriviamo un'altra richiesta nella condizione. MySQL elabora prima la sottoquery, restituisce id_author=2 e questo valore viene passato alla clausola DOVE richiesta esterna.

Possono esserci più sottoquery in una query, la sintassi per tale query è la seguente: Tieni presente che le sottoquery possono selezionare solo una colonna, i cui valori restituiranno alla query esterna. Il tentativo di selezionare più colonne genererà un errore.

Per consolidare questo, facciamo un'altra richiesta e scopriamo quali messaggi ha lasciato sul forum l'autore del topic "biciclette":

Ora complichiamo il compito, scopriamo in quali argomenti l'autore dell'argomento "biciclette" ha lasciato messaggi:

Scopriamo come funziona.

• MySQL eseguirà prima la query più profonda:


• Il risultato risultante (id_author=2) verrà passato a una richiesta esterna, che assumerà la forma:


• Il risultato risultante (id_topic:4,1) verrà passato a una richiesta esterna, che assumerà la forma:


• E darà il risultato finale (nome_argomento: sulla pesca, sulla pesca). Quelli. l'autore dell'argomento "biciclette" ha lasciato messaggi nell'argomento "Informazioni sulla pesca" creato da Sergei (id=1) e nell'argomento "Informazioni sulla pesca" creato da Sveta (id=4).

Questo è tutto ciò che volevo dire sulle query nidificate. Tuttavia, ci sono due punti a cui vale la pena prestare attenzione:

• Non è consigliabile creare query con un grado di nidificazione superiore a tre. Ciò comporta un aumento dei tempi di esecuzione e difficoltà nella comprensione del codice.
• La sintassi fornita per le query nidificate è probabilmente la più comune, ma non l'unica. Ad esempio, invece di chiedere
  
  scrivere
  
  Quelli. possiamo utilizzare qualsiasi operatore utilizzato con parola chiave DOVE (li abbiamo studiati nell'ultima lezione).

In questo breve articolo parleremo di database in particolare MySQL, campionamento e conteggio. Quando si lavora con i database, spesso è necessario contare le quantità righe COUNT() con o senza una determinata condizione, questo è estremamente facile da fare con la seguente richiesta

Visualizza codice MYSQL

La query restituirà un valore con il numero di righe nella tabella.

Conteggio con condizione

Visualizza codice MYSQL

La query restituirà un valore con il numero di righe nella tabella soddisfacente questa condizione: var = 1

Per ottenere più valori di conteggio delle righe con condizioni diverse, è possibile, ad esempio, eseguire più query una per una

Visualizza codice MYSQL

Ma in alcuni casi questo approccio non è né pratico né ottimale. Pertanto, diventa rilevante organizzare una query con diverse sottoquery per ottenere più risultati contemporaneamente in un'unica query. Per esempio

Visualizza codice MYSQL

Pertanto, eseguendo una sola query al database, otteniamo un risultato con un conteggio del numero di righe per diverse condizioni, contenente diversi valori di conteggio, ad esempio

Visualizza codice TESTO

c1|c2|c3 -------- 1 |5 |8

Lo svantaggio dell'utilizzo delle sottoquery, rispetto a diverse query separate, è la velocità di esecuzione e il carico sul database.

Il seguente esempio di una query contenente più COUNT in uno Interrogazione MySQL, è costruito in modo leggermente diverso, utilizza le costruzioni IF(condizione, valore1, valore2) e la sommatoria SUM(). Consente di selezionare i dati in base a criteri specificati all'interno di una query, quindi di riepilogarli e di visualizzare diversi valori come risultato.

Visualizza codice MYSQL

Come si può vedere dalla richiesta, è stata costruita in modo abbastanza succinto, ma anche la velocità della sua esecuzione non è stata soddisfacente, il risultato di questa richiesta ce ne sarà un prossimo,

Visualizza codice TESTO

totale|c1|c2|c3 -------------- 14 |1 |5 |8

Successivamente, fornirò statistiche comparative sulla velocità di esecuzione di tre opzioni di query per la selezione di diversi COUNT(). Per testare la velocità di esecuzione delle query, sono state eseguite 1000 query di ciascun tipo, con una tabella contenente più di tremila record. Inoltre, ogni volta la richiesta conteneva SQL_NO_CACHE per disabilitare la memorizzazione nella cache dei risultati da parte del database.

Velocità di esecuzione
Tre richieste separate: 0,9 sec
Una query con sottoquery: 0,95 sec
Una richiesta con costruzione IF e SUM: 1,5 sec

Conclusione. Pertanto, abbiamo diverse opzioni per creare query sul database Dati MySQL con più COUNT(), la prima opzione con query separate non è molto conveniente, ma offre il miglior risultato in termini di velocità. La seconda opzione con le sottoquery è leggermente più comoda, ma la sua velocità di esecuzione è leggermente inferiore. E infine, la terza versione laconica della query con i costrutti IF e SUM, che sembra la più conveniente, ha il maggior numero di bassa velocità prestazioni, che sono quasi due volte inferiori rispetto alle prime due opzioni. Pertanto, quando si ottimizza il funzionamento del database, consiglio di utilizzare la seconda versione della query contenente sottoquery con COUNT(), in primo luogo, la sua velocità di esecuzione è vicina al risultato più veloce e, in secondo luogo, tale organizzazione all'interno di una query è abbastanza conveniente .