The Ultimate Guide to High Availability with MariaDB

SELECT

Sintassi

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    espr_select [, espr_select ...]
    [ INTO OUTFILE 'nome_file' [export_options]
    | INTO DUMPFILE 'nome_file'
    | INTO nome_var [, nome_var] ]
    [ FROM riferimenti_tabelle
      [WHERE condizione_where]
      [GROUP BY {nome_col | espr | posizione} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {nome_col | espr | posizione} [ASC | DESC], ...]
      [LIMIT {[scarto,] num_righe | num_righe OFFSET scarto}]
      [PROCEDURE nome_procedura(lista_argomenti)]
      [FOR UPDATE | LOCK IN SHARE MODE] ]

Contents

  1. Sintassi
  2. Spiegazione
  3. Suggerimenti all'ottimizzatore
  4. Espressioni Select
  5. DISTINCT
  6. ORDER BY
  7. LIMIT
  8. GROUP BY
    1. Esempi di GROUP BY
  9. La clausola PROCEDURE
  10. Le clausole LOCK IN SHARE MODE e FOR UPDATE

Spiegazione

SELECT serve ad estrarre righe selezionate da una o più tabelle, e può includere istruzioni UNION e subquery. Si vedano UNION e http://dev.mysql.com/doc/refman/5.1/en/subqueries.html.

  • Ogniespr_select indica una colonna o dato che si vuole estrarre. In ogni istruzione deve essere presente almeno una espressione select. Si vedaEspressioni Select, sotto.
  • La clausola FROM indica la tabella o le tabelle dalle quali devono essere estratte le righe. Si può usare un solo nome di tabella oppure un'espressione JOIN. Si veda JOIN per i dettagli.
  • La clausola WHERE, se presente, indica la condizione o le condizioni che una riga deve soddisfare per essere selezionata. condizione_where è un'espressione che deve risultare vera perché le righe vengano selezionate. Se non è presente una clausola WHERE, vengono selezionate tutte le righe.
  • La clausola ORDER BY ordina i risultati.
  • La clausola LIMIT permette di restringere i risultati a un certo numero di righe, eventualmente con uno scarto iniziale.
  • Le clausole GROUP BY e HAVING raggruppano le righe insieme quando queste hanno valori di colonne o valori calcolati in comune.

Con SELECT è anche possibile di estrarre righe calcolate senza accedere ad alcuna tabella.

Suggerimenti all'ottimizzatore

Esistono alcune opzioni che possono influenzare il piano di esecuzione.

HIGH_PRIORITY assegna all'istruzione una priorità più alta. Se la tabella ha un lock, le SELECT ad alta priorità vengono eseguite non appena il lock viene rilasciato, anche se ci sono altre istruzioni in coda. HIGH_PRIORITY si applica solo se lo Storage Engine supporta soltanto i lock a livello di tabella (MyISAM, MEMORY, MERGE).

Se la variabile di sistema query_cache_type è impostata a 2 o a DEMAND, e l'istruzione è compatibile con la cache, SQL_CACHE fa sì che la query venga scritta nella cache e SQL_NO_CACHE fa sì che la query non venga scritta. Nel caso delle UNION, SQL_CACHE e SQL_NO_CACHE devono essere specificate nella prima query. Vedi anche: La Query Cache.

SQL_BUFFER_RESULT forza l'ottimizzatore a utilizzare una tabella temporanea per elaborare il risultato. Ciò è utile per liberare i lock appena possibile.

SQL_SMALL_RESULT e SQL_BIG_RESULT dicono all'ottimizzatore se il risultato è molto grande oppure no. Solitamente, le operazioni GROUP BY e DICTINCT vengono eseguite con l'ausilio di una tabella temporanea. Questa tecnica non è conveniente solo se il risultato è molto grande. L'ottimizzatore sa automaticamente se il risultato è molto grande, ma è possibile forzarlo a utilizzare una tabella temporanea con SQL_SMALL_RESULT, o evitare la tabella temporanea con SQL_BIG_RESULT.

STRAIGHT_JOIN si applica alle JOIN e dice all'ottimizzatore che le tabelle devono essere lette nell'ordine in cui appaiono nella SELECT. Per le tabelle const e system a volte questa opzione viene ignorata.

SQL_CALC_FOUND_ROWS si applica solo quando è presente la clausola LIMIT. Se questa opzione è presente, MariaDB conta quante righe soddisfarebbero la query, se non ci fosse LIMIT. Tale numero può essere estratto con la query successiva, utilizzando FOUND_ROWS().

Per ulteriori informazioni su queste opzioni, si veda Come forzare i piani delle query.

Espressioni Select

Le istruzioni SELECT possono contenere una o più istruzioni select, separate da virgola. Ogni istruzione select può essere:

  • Il nome di una colonna.
  • Qualsiasi espressione che usa funzioni e operatori.
  • * per selezionare tutte le colonne da tutte le tabelle nella clausola FROM.
  • nome_tabella.* per selezionare tutte le colonne dalla tabella nome_tabella.

Se si specifica una colonna, è possibile scrivere solo il suo nome oppure qualificarlo aggiungendo il nome della tabella, così: nome_tabella.nome_colonna. La forma qualificata serve quando si effettua una join fra più tabelle nella clausola FROM. Se non si qualificano i nomi, MariaDB tenta di trovare le colonne in tutte le tabelle. Se una colonna esiste in più di una tabella, è un errore.

E' possibile virgolettare i nomi utilizzando i backtick. Se si qualificano i nomi delle colonna con i nomi delle tabelle, ogni parte va virgolettata separatamente in questo modo: `nome_tabella`.`nome_colonne`.

Se si utilizza una qualsiasi funzione di raggruppamento in una espressione select, tutte le righe dei risultati vengono implicitamente raggruppate come se fosse presente un GROUP BY NULL.

DISTINCT

Una query potrebbe restituire righe identiche. Per default vengono estratte tutte le righe, anche quelle che hanno valori identici. Per specificare esplicitamente che si desidera estrarre i duplicati, si può usare l'opzione ALL. Se invece si desidera che i duplicati vengano rimossi dall'insieme dei risultati, si usa l'opzione DISTINCT. DISTINCTROW è un sinonimo di DISTINCT. Si veda anche COUNT DISTINCT.

ORDER BY

La clausola ORDER BY serve a ordinare i risultati restituiti dall'istruzione SELECT. E' possibile specificare solo una colonna, oppure utilizzare espressioni con le funzioni. Se la clausola GROUP BY è presente, è possibile anche usare le funzioni di raggruppamento in ORDER BY. L'ordinamento viene effettuato dopo il raggruppamento.

E' possibile utilizzare più espressioni separate da virgola. Le righe vengono ordinate per la prima espressione, poi se alcune hanno lo stesso valore per la prima espressione vengono ordinate per la seconda, e così via.

E' possibile utilizzare le parole chiave ASC e DESC dopo l'espressione di ordinamento per far sì che l'ordine sia, rispettivamente, ascendente o discendente. Per default è ascendente.

E' inoltre possibile usare un singolo numero intero come espressione di ordinamento. Se si usa un intero n, i risultati verranno ordinati per l'nesima espressione select.

La comparazione delle stringhe avviene secondo lo stesso meccanismo usato dalla funzione STRCMP. Essa ignora gli spazi bianchi e potrebbe normalizzare i caratteri ignorando la differenza tra lettere minuscole e maiuscole, a seconda della collation in uso.

LIMIT

La clausola LIMIT restringe il numero di righe restituite. Se si specifica un solo numero intero n, vengono restituite le prime n righe. Con la clausola ORDER BY si può determinare quali righe vengono prima. E' anche possibile selezionare un certo numero di righe dopo un certo scarto (offset) con una delle seguenti sintassi:

LIMIT scarto, num_righe
LIMIT num_righe OFFSET scarto

Se si specifica uno scarto m e un limite n, le prime m righe vengono ignorate e le n righe seguenti vengono restituite.

A partire da MariaDB 5.5.21, c'è un'ottimizzazione chiamata LIMIT ROWS EXAMINED che serve a terminare l'esecuzione di un'istruzione SELECT che esamina troppe righe, usando quindi troppe risorse. Si veda la pagina LIMIT ROWS EXAMINED per i dettagli.

GROUP BY

La clausola GROUP BY raggruppa insieme quelle righe che hanno lo stesso valore in una o più colonne, o lo stesso valore calcolato usando le funzioni e gli operatori, eccetto le funzioni di raggruppamento. Se si specifica una clausola GROUP BY, si ottiene come risultato una singola riga per ogni gruppo di righe che hanno lo stesso valore per l'espressione indicata in GROUP BY.

Nel raggruppare le righe, le espressioni di raggruppamento sono comparate con l'operatore =. Per i valori stringa, operatore = ignora gli spazi finali e potrebbe normalizzare i caratteri ignorando la differenza tra lettere minuscole e maiuscole, a seconda della collation in uso.

E' possibile utilizzare le funzioni di raggruppamento come espressioni select. I loro valori verranno calcolati basandosi su tutte le righe che sono state raggruppate insieme per ogni riga di risultato. Se si selezionano colonne non raggruppate o valori calcolati da colonne non raggruppate, non è possibile determinare da qualche riga verrà estratto il valore.

E' possibile specificare più espressioni nella clausola GROUP BY, separate da virgole. Le righe vengono raggruppate insieme se tutte le espressioni corrispondono.

E' anche possibile specificare un singolo numero intero come espressione di raggruppamento. Se si specifica un intero n, i risultati vengono raggruppati in base alla colonna numero n nell'epressione select.

La clausola WHERE si applica prima della GROUP BY. Essa filtra le righe non aggregate prima che le righe vengano raggruppate insieme. Per filtrare le righe raggruppate basandosi sui valori aggregati, esiste la clausola HAVING. Essa accetta qualsiasi espressione e la elabora come valore booleano, proprio come fa la clausola WHERE. E' possibile usare funzioni aggreganti nella clausola HAVING. Come per le espressioni select, se si fa riferimento a colonne non aggregate nella HAVING, il comportamento è imprevedibile.

Per default, se è presente la clausola GROUP BY, le righe nell'output vengono ordinate basandosi sulle espressioni presenti nella GROUP BY. E' possibile anche specificare ASC o DESC (ascendente, discendente) dopo ogni espressione, come nella ORDER BY. Il default è ASC.

Se si vuole che le righe vengano ordinate in base a un'altra colonna, si può aggiungere una ORDER BY esplicita. Se non si desidera che siano ordinate, si può specificare ORDER BY NULL.

Esempi di GROUP BY

Si consideri la tabella seguente, che registra quante volte un utente ha vinto a un gioco:

CREATE TABLE plays (name VARCHAR(16), plays INT, wins INT);
INSERT INTO plays VALUES ("John", 20, 5), ("Robert", 22, 8), ("Wanda", 32, 8), ("Susan", 17, 3);

Get a list of win counts along with a count:

MariaDB> SELECT wins, COUNT(*) FROM plays GROUP BY wins;
+------+----------+
| wins | COUNT(*) |
+------+----------+
|    3 |        1 |
|    5 |        1 |
|    8 |        2 |
+------+----------+
3 rows in set (0.00 sec)

L'espressione GROUP BY può essere un valore calcolato e può fare riferimento a un identificatore specificato con una AS. Ecco come ottenere un elenco delle medie delle vittorie, insieme al conteggio:

MariaDB> SELECT (wins / plays) AS winavg, COUNT(*) FROM plays GROUP BY winavg;
+--------+----------+
| winavg | COUNT(*) |
+--------+----------+
| 0.1765 |        1 |
| 0.2500 |        2 |
| 0.3636 |        1 |
+--------+----------+
3 rows in set (0.00 sec)

E' possibile usare qualsiasi funzione di raggruppamento come espressione select. Per ogni media delle vittorie, otteniamo la media delle partite giocate:

MariaDB> SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.1765 |    17.0000 |
| 0.2500 |    26.0000 |
| 0.3636 |    22.0000 |
+--------+------------+
3 rows in set (0.00 sec)

E' possibile filtrare le informazioni aggregate con la clausola HAVING. Essa si applica dopo GROUP BY e permette di filtrare i dati aggregati, che non possono essere visti dalla clausola WHERE. Ecco come restringere l'esempio sopra a quei risultati la cui media delle partite è superiore a 20:

MariaDB> SELECT (wins / plays) AS winavg, AVG(plays) FROM plays GROUP BY winavg HAVING AVG(plays) > 20;
+--------+------------+
| winavg | AVG(plays) |
+--------+------------+
| 0.2500 |    26.0000 |
| 0.3636 |    22.0000 |
+--------+------------+
2 rows in set (0.00 sec)

La clausola PROCEDURE

La clausola PROCEDURE passa l'intero insieme dei risultati a una Procedura che lo rielabora. Queste Procedure non sono Stored Procedure. Possono essere scritte solo in linguaggio C.

Attualmente l'unica procedura esistente è ANALYSE, che esamina l'insieme dei risultati e suggerisce il tipo di dato ottimale per ogni colonna. E' definita nel file sql/sql_analyse.cc e può essere usata come esempio per creare altre Procedure.

Le clausole LOCK IN SHARE MODE e FOR UPDATE

InnoDB/XtraDB supporta i lock a livello di riga. Le righe selezionate possono essere bloccate utilizzando LOCK IN SHARE MODE o FOR UPDATE. In entrambi i casi viene acquisito un lock sulle righe lette dalla query, che verrà rilasciato al commit della transazione corrente.

Se si specifica LOCK IN SHARE MODE, l'istruzione attende il termine di tutte le transazioni che hanno modificato le righe in questione. Poi viene acquisito un lock in scrittura. Le altre transazioni possono leggere le righe, ma se vogliono modificarle, devono attendere finché la transazione che ha acquisito il lock non è terminata.

FOR UPDATE si applica solo se autocommit è impostata a 1 o l'istruzione SELECT è racchiusa in una transazione. Viene acquisito un lock sulle righe, e le altre transazioni non potranno modificarle, acquisire un lock, né leggerle (a meno che il loro isolation level sia READ UNCOMMITTED).

Se l'isolation level è impostato a SERIALIZABLE e autocommit è impostata a 0, tutte le SELECT semplici sono convertite in SELECT ... LOCK IN SHARE MODE.

Commenti

Sto caricando i commenti......
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.