07. Ottimizzare le istruzioni SQL
Introduzione
Union, subquery e Join
Il piano di esecuzione
L’ottimizzatore
Le statistiche
Il comando EXPLAIN
Gli Hint
A.A. 2005
Laboratorio di basi di dati - LB
2
La UNION (1)
L’operazione di UNION esegue l’unione
insiemistica di 2 query aventi le stesse
colonne (numero e tipo).
A.A. 2005
Laboratorio di basi di dati - LB
3
La UNION (2)
La UNION DISTINCT (è il default),
ritorna l’unione dei due insiemi senza
duplicati.
La UNION ALL, ritorna l’unione dei due
insiemi, senza effettuare la DISTINCT.
A.A. 2005
Laboratorio di basi di dati - LB
4
La UNION: ESEMPIO (1)
select empno, ename from emp;
+-------+---------+
| empno | ename
|
+-------+---------+
| 1
| bianchi |
| 2
| rossi
|
| 3
| neri
|
+-------+---------+
A.A. 2005
Laboratorio di basi di dati - LB
5
La UNION: ESEMPIO (2)
select mgrno, mgrname from manager;
+-------+---------+
| mgrno | mgrname |
+-------+---------+
| 1
| bianchi |
+-------+---------+
A.A. 2005
Laboratorio di basi di dati - LB
6
La UNION: ESEMPIO (3)
SELECT empno, ename FROM emp
UNION DISTINCT
SELECT mgrno, mgrname FROM manager;
La clausola DISTINCT è opzionale.
A.A. 2005
Laboratorio di basi di dati - LB
7
La UNION: ESEMPIO (4)
+-------+---------+
| empno | ename
|
+-------+---------+
| 2
| rossi
|
| 1
| bianchi |
| 3
| neri
|
+-------+---------+
3 rows in set (0.00 sec)
La riga (1,bianchi) è ripetuta una sola volta (la UNION
effettua la distinct)
A.A. 2005
Laboratorio di basi di dati - LB
8
La UNION: ESEMPIO (5)
SELECT empno, ename FROM emp
UNION ALL
SELECT mgrno, mgrname FROM manager;
A.A. 2005
Laboratorio di basi di dati - LB
9
La UNION: ESEMPIO (6)
+-------+---------+
| empno | ename
|
+-------+---------+
| 2
| rossi
|
| 1
| bianchi |
| 3
| neri
|
| 1
| bianchi |
+-------+---------+
4 rows in set (0.00 sec)
La riga (1,bianchi) è ripetuta 2 volte (la UNION ALL
non effettua la distinct)
A.A. 2005
Laboratorio di basi di dati - LB
10
Le subquery
Una subquery è una query annidata
(interna) in un’altra query (esterna).
Sono supportate in mysql a partire dalla
versione >= 4.1.
A.A. 2005
Laboratorio di basi di dati - LB
11
Subquery “semplici”
Una subquery che viene eseguita una
sola volta per tutte le righe della query
“esterna” è detta subquery “semplice”.
Normalmente viene utilizzata con
l’operatore di uguaglianza (=)
A.A. 2005
Laboratorio di basi di dati - LB
12
Le SUBQUERY “semplici”:
un ESEMPIO
SELECT * FROM emp WHERE
deptno=(SELECT max(deptno) FROM dept);
A.A. 2005
Laboratorio di basi di dati - LB
13
Subquery correlate/dipendenti
Una subquery che fa riferimento alle
colonne della query esterna è detta
correllata o dipendente.
Viene eseguita n volte una per ogni riga
della query esterna.
Normalmente viene utilizzata con gli
operatori IN, EXISTS
A.A. 2005
Laboratorio di basi di dati - LB
14
Le SUBQUERY “dipendenti”:
un ESEMPIO
SELECT * FROM emp e
WHERE EXISTS (
SELECT 1 FROM dept d
WHERE d.deptno=e.deptno);
A.A. 2005
Laboratorio di basi di dati - LB
15
Subquery derivate
Una subquery utilizzata nella clausola
FROM di una query esterna.
E’ necessario assegnare un “alias” alla
subquery.
A.A. 2005
Laboratorio di basi di dati - LB
16
Le SUBQUERY “derivate”:
un ESEMPIO
SELECT AVG(s.summed_salary) FROM
(SELECT sum(salary) summed_salary
FROM emp
GROUP BY deptno) s;
A.A. 2005
Laboratorio di basi di dati - LB
17
Il Join
Consente di selezionare e confrontare
dati presenti in diverse tabelle con
un’unica operazione di SELECT.
La condizione di join (quando presente)
mette in relazione colonne “comuni” di 2
o più tabelle, tipicamente colonne legate
da una Foreign-Key.
A.A. 2005
Laboratorio di basi di dati - LB
18
Il Join: la sintassi
Esistono 2 sintassi (largamente diffuse
ed utilizzate) per scrivere una
operazione di join:
Theta-style
ANSI-style
A.A. 2005
Laboratorio di basi di dati - LB
19
Il Join: theta-style (1)
Le tabelle in join sono elencate nella
clausola FROM separate dalla “,”
(virgola).
All’interno della clausola WHERE sono
espresse sia le condizioni di join che
altre eventuali condizioni “di filtro”.
A.A. 2005
Laboratorio di basi di dati - LB
20
Il Join: theta-style (2)
NON permette l’outer join (se non
tramite estensioni non ANSI).
E’ stato largamente utilizzato come
sintassi “standard” dall’rdbms oracle
fino alla versione 9.
A.A. 2005
Laboratorio di basi di dati - LB
21
Theta-style join: un ESEMPIO
SELECT e.ename, d.descr
FROM
emp e, dept d
WHERE e.deptno=d.deptno AND
e.ename like ‘B%’;
A.A. 2005
Laboratorio di basi di dati - LB
22
Il Join: ansi-style (1)
Le tabelle in join sono elencate nella
clausola FROM separate dalla clausola
“JOIN”.
La clausola ON è utilizzata per
esprimere le condizioni di join.
La clausola WHERE è utilizzata per
altre, eventuali, condizioni “di filtro”.
A.A. 2005
Laboratorio di basi di dati - LB
23
Il Join: ansi-style (2)
E’ supportato da tutti i maggiori rdbms
(alcuni hanno estensioni proprietarie).
Permette tutti i tipi di join.
A.A. 2005
Laboratorio di basi di dati - LB
24
Ansi-style join: un ESEMPIO
SELECT
FROM
ON
WHERE
A.A. 2005
e.ename, d.descr
emp e JOIN dept d
e.deptno=d.deptno
e.ename like ‘B%’;
Laboratorio di basi di dati - LB
25
I diversi tipi Join (1)
L’operazione di join può essere di tipo
diverso a secondo di come le tabelle
sono messe in join e di come sono
trattate le righe che non soddisfano la
condizione di join.
A.A. 2005
Laboratorio di basi di dati - LB
26
I diversi tipi Join (2)
CROSS JOIN
Non viene specificata alcuna condizione di
join, ossia viene effettuato il prodotto
cartesiano delle tabelle.
INNER JOIN
Vengono restituite solo le righe che
soddisfano la condizione di join.
A.A. 2005
Laboratorio di basi di dati - LB
27
I diversi tipi Join (3)
LEFT (OUTER) JOIN
Vengono restituite tutte le righe della left-table
che soddisfano o meno la condizione di join.
Per le righe che non soddisfano la condizione di
join, le colonne della right-table sono impostate
a NULL.
RIGHT (OUTER) JOIN
“Speculare” al left-join.
A.A. 2005
Laboratorio di basi di dati - LB
28
I diversi tipi Join (4)
FULL JOIN
Vengono restituite tutte le righe delle tabelle
in join, che soddisfano o meno la condizione
di join.
Per le righe che non soddisfano la condizione
di join, le colonne della left/right-table sono
impostate a NULL.
NON è supportato come ANSI sql.
A.A. 2005
Laboratorio di basi di dati - LB
29
I diversi tipi Join (5)
NATURAL JOIN
La condizione di join è data dall’uguaglianza
di tutte le colonne in comune.
E’ una estensione NON ANSI di Mysql.
La query “dipende” dalla struttura delle
tabelle (dalle colonne in comune).
SELF JOIN
Le tabella viene messa in join con se stessa.
A.A. 2005
Laboratorio di basi di dati - LB
30
tipi di join: un ESEMPIO (1)
SELECT * FROM emp;
+-------+---------+--------+
| empno | ename
| deptno |
+-------+---------+--------+
| 2
| rossi
| 15
|
| 1
| bianchi | 10
|
| 3
| bassi
| 10
|
+-------+---------+--------+
A.A. 2005
Laboratorio di basi di dati - LB
31
tipi di join: un ESEMPIO (2)
SELECT * FROM dept;
+--------+---------+
| deptno | descr
|
+--------+---------+
| 10
| dept 10 |
| 15
| sales
|
| 20
| dept 20 |
+--------+---------+
A.A. 2005
Laboratorio di basi di dati - LB
32
cross join: un ESEMPIO (1)
SELECT e.ename, d.descr
FROM
emp e CROSS JOIN dept d
WHERE d.descr like ‘D%’;
La clausola CROSS è opzionale.
A.A. 2005
Laboratorio di basi di dati - LB
33
cross join: un ESEMPIO (2)
+---------+---------+
| ename
| descr
|
+---------+---------+
| rossi
| dept 10 |
| bianchi | dept 10 |
| bassi
| dept 10 |
| rossi
| dept 20 |
| bianchi | dept 20 |
| bassi
| dept 20 |
+---------+---------+
Viene restituito il prodotto cartesiano.
A.A. 2005
Laboratorio di basi di dati - LB
34
inner join: un ESEMPIO (1)
SELECT
FROM
ON
WHERE
A.A. 2005
e.ename, d.descr
emp e JOIN dept d
e.deptno=d.deptno
d.descr like ‘D%’;
Laboratorio di basi di dati - LB
35
inner join: un ESEMPIO (2)
+---------+---------+
| ename
| descr
|
+---------+---------+
| bianchi | dept 10 |
| bassi
| dept 10 |
+---------+---------+
Vengono restituite le sole righe che hanno un match.
A.A. 2005
Laboratorio di basi di dati - LB
36
rigth outer join: ESEMPIO (1)
SELECT
FROM
ON
WHERE
e.ename, d.descr
emp e RIGHT OUTER JOIN dept d
e.deptno=d.deptno
d.descr like ‘D%’;
La clausola OUTER è opzionale.
La colonna e.ename assumerà NULL per tutte le righe
di “dept” che non soddisfano la condizione di join.
A.A. 2005
Laboratorio di basi di dati - LB
37
rigth outer join: ESEMPIO (2)
+---------+---------+
| ename
| descr
|
+---------+---------+
| bianchi | dept 10 |
| bassi
| dept 10 |
|
| dept 20 |
+---------+---------+
Vengono restituite le “matching row” e le “non
matching” con le colonne della left-table a NULL.
A.A. 2005
Laboratorio di basi di dati - LB
38
natural join: un ESEMPIO (1)
SELECT e.ename, d.descr
FROM
emp e NATURAL JOIN dept d
WHERE d.descr like ‘D%’;
La condizione “implicita” di join è: “e.deptno=d.deptno”
NON è ANSI SQL (ma una estensione di mysql)
A.A. 2005
Laboratorio di basi di dati - LB
39
natural join: un ESEMPIO (2)
+---------+---------+
| ename
| descr
|
+---------+---------+
| bianchi | dept 10 |
| bassi
| dept 10 |
+---------+---------+
La condizione “implicita” di join è: “e.deptno=d.deptno”
NON è ANSI SQL (ma una estensione di mysql)
A.A. 2005
Laboratorio di basi di dati - LB
40
self join: un ESEMPIO (1)
SELECT
FROM
ON
WHERE
A.A. 2005
e2.empno, e2.ename
emp e1 JOIN emp e2
e1.deptno=e2.deptno
e1.ename = ‘BIANCHI’;
Laboratorio di basi di dati - LB
41
self join: un ESEMPIO (2)
+-------+---------+
| empno | ename
|
+-------+---------+
| 1
| bianchi |
| 3
| bassi
|
+-------+---------+
A.A. 2005
Laboratorio di basi di dati - LB
42
I diversi algoritmi di join
Nested Loop Join
Sort Merge Join
Hash Join
(*) Non sono trattati nel corso.
A.A. 2005
(*)
Laboratorio di basi di dati - LB
43
Il Nested Loop Join
E’ l’unico algoritmo supportato da MySql
Si definisce “outer-table”/“driving-table”
la tabella esterna, ed “inner-table” la
tabella interna.
Per ogni riga della “outer-table” si
cercano tutte le righe della “inner-table”
che soddisfano la condizione di join.
A.A. 2005
Laboratorio di basi di dati - LB
44
Il Sort Merge Join
NON è supportato da MySql
Le due tabelle vengono prima ordinate
rispetto alle colonne presenti nella
condizione di join e successivamente
vengono “unite” le righe delle 2 tabelle
che soddisfano la condizione di join.
A.A. 2005
Laboratorio di basi di dati - LB
45
Quando utilizzare il Nested
Loop Join (1)
La query restituisce un numero limitato
di righe.
L’obiettivo della query è minimizzare il
tempo di restituzione della prima riga.
Almeno una delle tabelle coinvolte nel
join è “piccola” (rispetto alle dimensioni
della cache).
A.A. 2005
Laboratorio di basi di dati - LB
46
Quando utilizzare il Nested
Loop Join (2)
Esiste un indice selettivo sulle colonne
utilizzate nella condizione di join di
almeno una delle 2 tabelle.
La tabella con indice selettivo deve
essere scelta come inner-table ed
acceduta tramite l’indice.
La outer-table, normalmente, viene
acceduta in full-table-scan.
A.A. 2005
Laboratorio di basi di dati - LB
47
Quando utilizzare il Sort
Merge Join (1)
La query restituisce un numero grande
di righe.
L’obiettivo della query è minimizzare il
tempo di restituzione di tutte le righe.
Le tabelle coinvolte nel join sono
“grandi” (rispetto alla dimensione della
cache).
A.A. 2005
Laboratorio di basi di dati - LB
48
Quando utilizzare il Sort
Merge Join (2)
Se esistono degli indici selettivi sulle
colonne utilizzate nella condizione di
join può essere conveniente utilizzarli
(per non effettuare il sort delle tabelle).
A.A. 2005
Laboratorio di basi di dati - LB
49
ESEMPIO: il Nested Loop Join
T_DEPT
T_OFFICE
Per ogni riga della tabella “T_DEPT”
viene scandita la tabella “T_OFFICE”
ricercando un “match” (o viceversa).
A.A. 2005
Laboratorio di basi di dati - LB
50
ESEMPIO: il Sort Merge Join
T_DEPT
T_OFFICE
Le tabelle “T_DEPT” e “T_OFFICE”
vengono prima ordinate (rispetto alla
colonna di join) e poi vengono “unite” le
righe che rispettano la condizione di join.
A.A. 2005
Laboratorio di basi di dati - LB
51
Il linguaggio SQL
E’ un linguaggio NON procedurale
A.A. 2005
Laboratorio di basi di dati - LB
52
Le fasi di un’istruzione SQL
PARSE
A.A. 2005
BIND
EXECUTE
Laboratorio di basi di dati - LB
FETCH
53
L’esecuzione di istruzioni SQL
Per essere eseguita un’istruzione SQL
deve essere “compilata” e deve essere
prodotto “il codice eseguibile” (detto
piano di esecuzione).
A.A. 2005
Laboratorio di basi di dati - LB
54
Il piano di esecuzione
E’ l’insieme dei passi elementari che
permettono l’esecuzione di una
istruzione SQL.
Ogni istruzione ha diversi possibili piani
di esecuzione.
A.A. 2005
Laboratorio di basi di dati - LB
55
L’ottimizzatore (1)
E’ il modulo dell’rdbms che si occupa di
determinare il miglior piano di
esecuzione possibile di uno statement
SQL.
A.A. 2005
Laboratorio di basi di dati - LB
56
L’ottimizzatore (2)
L’ottimizzatore per poter determinare il
piano di esecuzione migliore ha bisogno
di “conoscere” le caratteristiche degli
oggetti coinvolti nell’istruzione SQL.
A.A. 2005
Laboratorio di basi di dati - LB
57
Le statistiche (1)
Sono necessarie per conoscere la
cardinalità e la distribuzione dei dati.
E’ fondamentale che le statistiche siano
mantenute aggiornate.
A.A. 2005
Laboratorio di basi di dati - LB
58
Le statistiche (2)
Numero di righe
Numero di valori distinti per ogni
colonna (distribuzione uniforme)
Dimensione della tabella/indice
Lunghezza media delle colonne
A.A. 2005
Laboratorio di basi di dati - LB
59
Collezionare le statistiche
ANALYZE TABLE <nome-tabella>;
Il comando ANALYZE colleziona le
statistiche per la tabella e per tutti gli
indici definiti sulla tabella.
Per InnoDB, il comando ANALYZE
colleziona statistiche su un campione
dei dati, quindi non esatte.
A.A. 2005
Laboratorio di basi di dati - LB
60
I limiti dell’ottimizzatore
E’ possibile che l’ottimizzatore NON
determini il migliore piano di
esecuzione.
Le istruzioni SQL devono essere
ottimizzate da chi le scrive e non ci si
deve affidare all’ottimizzatore.
A.A. 2005
Laboratorio di basi di dati - LB
61
Il comando EXPLAIN (1)
Permette di visualizzare il piano di
esecuzione di una SELECT.
MySql non permette di visualizzare il
piano di esecuzione di una DML o di
una DDL.
A.A. 2005
Laboratorio di basi di dati - LB
62
Il comando EXPLAIN (2)
Altri rdbms hanno una implementazione
diversa del comando EXPLAIN.
Il comando EXPLAIN contiene dettagli
che dipendono sia dall’rdbms che dalla
versione scelta.
A.A. 2005
Laboratorio di basi di dati - LB
63
EXPLAIN: esempio (1)
CREATE
INSERT
INSERT
CREATE
TABLE t1 (a INT,b VARCHAR(20));
INTO t1 VALUES (1,’a’);
INTO t1 VALUES (2,’b’);
INDEX ix1_t1 ON t1(a);
EXPLAIN SELECT * FROM t1 WHERE a=1 \G
A.A. 2005
Laboratorio di basi di dati - LB
64
EXPLAIN: esempio (2)
**************
id:
select_type:
table:
type:
possible_keys:
A.A. 2005
1. row *************
1
SIMPLE
T1
ref
IX1_T1
Laboratorio di basi di dati - LB
65
EXPLAIN: esempio (3)
key: IX1_T1
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.01 sec)
A.A. 2005
Laboratorio di basi di dati - LB
66
EXPLAIN: le informazioni (1)
Una query è composta da n passi, ossia
da n “query elementari”.
Data una query il comando EXPLAIN
ritorna n righe, una per ogni passo da
effettuare per portare a termine la query
stessa.
Ogni riga contiene le informazioni di
dettaglio su come il singolo passo viene
eseguito.
A.A. 2005
Laboratorio di basi di dati - LB
67
EXPLAIN: le informazioni (2)
ID
= identificativo select
SELECT_TYPE = ruolo
TABLE
= nome tabella
TYPE
= tipo di accesso
POSSIBLE_KEYS = indici possibili
A.A. 2005
Laboratorio di basi di dati - LB
68
EXPLAIN: le informazioni (3)
KEY
KEY_LEN
REF
=
=
=
ROWS
EXTRA
=
=
A.A. 2005
indice utilizzato
lunghezza chiave
colonne/valori per
selezionare la chiave
numero di righe
altro
Laboratorio di basi di dati - LB
69
EXPLAIN: ID
Identificativo (progressivo) della select
all’interno dell’intera query.
Il suo “significato” è cambiato rispetto alla
versione < 5.0.
A.A. 2005
Laboratorio di basi di dati - LB
70
EXPLAIN: SELECT_TYPE (1)
Identifica il ruolo del singolo passo, ossia la
sua funzione all’interno della query
principale.
A.A. 2005
Laboratorio di basi di dati - LB
71
EXPLAIN: SELECT_TYPE (2)
SIMPLE
query semplice (che non usa UNION o
subquery).
PRIMARY
query più esterna.
A.A. 2005
Laboratorio di basi di dati - LB
72
EXPLAIN: SELECT_TYPE (3)
UNION
seconda (o successiva) select in una UNION.
DEPENDENT UNION
seconda (o successiva) select in una UNION,
dipendente da una query esterna.
UNION RESULT
il risultato di una UNION.
A.A. 2005
Laboratorio di basi di dati - LB
73
EXPLAIN: SELECT_TYPE (4)
SUBQUERY
subquery “semplice”.
DEPENDENT SUBQUERY
subquery dipendente da una query esterna.
DERIVED
subquery in una FROM.
A.A. 2005
Laboratorio di basi di dati - LB
74
ESEMPIO (1):
SELECT_TYPE=SIMPLE
EXPLAIN
SELECT * FROM emp
WHERE empno=2\G
A.A. 2005
Laboratorio di basi di dati - LB
75
ESEMPIO (2):
SELECT_TYPE=SIMPLE
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
key_len:
ref:
rows:
Extra:
A.A. 2005
row *****
1
SIMPLE
EMP
const
PRIMARY
PRIMARY
4
const
1
Laboratorio di basi di dati - LB
76
ESEMPIO (1):
SELECT_TYPE=PRIMARY
EXPLAIN SELECT * FROM emp
WHERE
deptno=
(SELECT max(deptno) FROM dept)\G
A.A. 2005
Laboratorio di basi di dati - LB
77
ESEMPIO (2):
SELECT_TYPE=PRIMARY
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
...
A.A. 2005
row *****
1
PRIMARY
emp
row *****
2
SUBQUERY
NULL
Laboratorio di basi di dati - LB
78
ESEMPIO: (1)
SELECT_TYPE=UNION
EXPLAIN
SELECT * FROM emp
UNION ALL
SELECT * FROM manager \G
A.A. 2005
Laboratorio di basi di dati - LB
79
ESEMPIO: (2)
SELECT_TYPE=UNION
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
A.A. 2005
row ******
1
PRIMARY
emp
row ******
2
UNION
manager
Laboratorio di basi di dati - LB
80
ESEMPIO: (3)
SELECT_TYPE=UNION
*********** 3.
id:
select_type:
table:
...
A.A. 2005
row ******
NULL
UNION RESULT
<union1,2>
Laboratorio di basi di dati - LB
81
ESEMPIO: (1)
SELECT_TYPE=SUBQUERY
EXPLAIN SELECT * FROM emp WHERE
deptno=(SELECT max(deptno) FROM dept)\G
A.A. 2005
Laboratorio di basi di dati - LB
82
ESEMPIO: (2)
SELECT_TYPE=SUBQUERY
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
...
A.A. 2005
row *****
1
PRIMARY
emp
row *****
2
SUBQUERY
dept
Laboratorio di basi di dati - LB
83
ESEMPIO: (1) SELECT_TYPE
= DEPENDENT SUBQUERY
EXPLAIN
SELECT * FROM emp e
WHERE EXISTS (
SELECT 1 FROM dept d
WHERE d.deptno=e.deptno)\G
A.A. 2005
Laboratorio di basi di dati - LB
84
ESEMPIO: (2) SELECT_TYPE
= DEPENDENT SUBQUERY
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
...
A.A. 2005
row *****
1
PRIMARY
e
row *****
2
DEPENDENT SUBQUERY
d
Laboratorio di basi di dati - LB
85
ESEMPIO: (1)
SELECT_TYPE=DERIVED
EXPLAIN
SELECT AVG(s.summed_salary) FROM
(SELECT sum(salary) summed_salary
FROM emp
GROUP BY deptno) s \G
A.A. 2005
Laboratorio di basi di dati - LB
86
ESEMPIO: (2)
SELECT_TYPE=DERIVED
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
...
A.A. 2005
row *****
1
PRIMARY
<derived2>
row *****
2
DERIVED
emp
Laboratorio di basi di dati - LB
87
EXPLAIN: TYPE (1)
Identifica il tipo di accesso effettuato sulla
tabella del passo n-esimo, ossia
informazioni di dettaglio su come la tabella
viene acceduta.
A.A. 2005
Laboratorio di basi di dati - LB
88
EXPLAIN: TYPE (2)
ALL
si accede a tutte le righe di una tabella, ossia in
FULL TABLE SCAN.
INDEX
si accede a tutte le chiavi di un indice, ossia in
FULL INDEX SCAN.
A.A. 2005
Laboratorio di basi di dati - LB
89
EXPLAIN: TYPE (2)
RANGE
si accede alla tabella tramite un indice per un
“range” di valori possibili.
CONST
una sola riga soddisfa le condizioni. La tabella
viene acceduta una sola volta all’interno
dell’intera query.
A.A. 2005
Laboratorio di basi di dati - LB
90
EXPLAIN: TYPE (3)
EQ_REF
la tabella ha una “matching-row” per ogni riga
ritornata al passo precedente (quando in un join
la tabella viene acceduta tramite una PK).
REF
la tabella ha più “matching-row” per ogni riga
ritornata al passo precedente, (quando in un join
la tabella viene acceduta tramite un indice non
univoco).
A.A. 2005
Laboratorio di basi di dati - LB
91
EXPLAIN: TYPE (4)
INDEX_MERGE
si accede alla tabella utilizzando più indici e poi si
effettua il “merge” degli stessi per recuperare le
righe richieste.
E’ utilizzato quando all’interno della condizione di
WHERE ci sono delle espressioni in AND/OR.
Questa feature è disponibile dalla versione >=
5.0, in precedenza ogni singolo passo poteva
utilizzare un solo indice.
A.A. 2005
Laboratorio di basi di dati - LB
92
ESEMPIO: (1)
TYPE=ALL
EXPLAIN
SELECT distinct(ename) FROM emp \G
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
emp
ALL
NULL
NULL
Laboratorio di basi di dati - LB
93
ESEMPIO: (1)
TYPE=INDEX
EXPLAIN
SELECT count(*) FROM emp \G
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
emp
index
NULL
PRIMARY
Laboratorio di basi di dati - LB
94
ESEMPIO: (1)
TYPE=RANGE
EXPLAIN
SELECT * FROM emp
WHERE empno BETWEEN 10 AND 100 \G
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
EMP
range
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
95
ESEMPIO: (1)
TYPE=CONST
EXPLAIN
SELECT * FROM emp WHERE empno=2\G
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
EMP
const
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
96
ESEMPIO: (2)
TYPE=CONST
EXPLAIN
SELECT * FROM emp
WHERE deptno =
(SELECT deptno FROM dept
WHERE deptno=7) \G
A.A. 2005
Laboratorio di basi di dati - LB
97
ESEMPIO: (3)
TYPE=CONST
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
PRIMARY
EMP
row *****
2
SUBQUERY
dept
const
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
98
ESEMPIO: (1)
TYPE=EQ_REF
EXPLAIN
SELECT /*! STRAIGHT_JOIN */ *
FROM emp e, dept d
WHERE e.deptno=d.deptno \G
A.A. 2005
Laboratorio di basi di dati - LB
99
ESEMPIO: (2)
TYPE=EQ_REF
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
e
row *****
2
SIMPLE
d
eq_ref
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
100
ESEMPIO: (1)
TYPE=REF
EXPLAIN
SELECT /*+ STRAIGHT_JOIN */ *
FROM dept d, emp e
WHERE e.deptno=d.deptno \G
A.A. 2005
Laboratorio di basi di dati - LB
101
ESEMPIO: (2)
TYPE=REF
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
d
row *****
2
SIMPLE
e
ref
FK_EMP_DEPT
FK_EMP_DEPT
Laboratorio di basi di dati - LB
102
ESEMPIO: (1)
TYPE=INDEX_MERGE
EXPLAIN
SELECT * FROM emp
/*! FORCE INDEX (PRIMARY,fk_emp_dept) */
WHERE empno=10 or deptno=20 \G
A.A. 2005
Laboratorio di basi di dati - LB
103
ESEMPIO: (2)
TYPE=INDEX_MERGE
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
key_len:
ref:
rows:
Extra:
A.A. 2005
row *****
1
SIMPLE
emp
index_merge
PRIMARY,fk_emp_dept
PRIMARY,fk_emp_dept
4,5
NULL
2
Using union...
Laboratorio di basi di dati - LB
104
EXPLAIN:
POSSIBLE_KEYS/KEY
POSSIBLE_KEYS
La lista degli indici che possono essere utilizzati
per portare a termine la singola query.
KEY
La lista degli indici effettivamente utilizzati per
portare a termine la singola query.
A.A. 2005
Laboratorio di basi di dati - LB
105
ESEMPIO: (1)
POSSIBLE_KEYS/KEY
EXPLAIN
SELECT * FROM emp
WHERE empno=10 or deptno=20 \G
A.A. 2005
Laboratorio di basi di dati - LB
106
ESEMPIO: (2)
POSSIBLE_KEYS/KEY
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
key_len:
ref:
...
row *****
1
SIMPLE
emp
ALL
PRIMARY,fk_emp_dept
NULL
NULL
NULL
Gli indici PRIMARY e fk_emp_dept NON sono
utilizzati.
A.A. 2005
Laboratorio di basi di dati - LB
107
EXPLAIN: ROWS
ROWS
il numero di righe che mysql “crede” di dover
esaminare per eseguire la query.
A.A. 2005
Laboratorio di basi di dati - LB
108
EXPLAIN: EXTRA (1)
Contiene informazioni di dettaglio su come
viene eseguito il singolo passo.
Nelle prossime slide vengono elencati i
valori più significativi.
A.A. 2005
Laboratorio di basi di dati - LB
109
EXPLAIN: EXTRA (2)
USING FILESORT
Viene utilizzato un file temporaneo per effettuare
un ordinamento.
USING TEMPORARY
Viene utilizzata una tabella temporanea per
memorizzare i risultati parziali.
A.A. 2005
Laboratorio di basi di dati - LB
110
EXPLAIN: EXTRA (3)
USING INDEX
Viene utilizzato il solo indice per recuperare le
informazioni richieste. Non è necessario
accedere alla tabella.
USING WHERE
Viene utilizzata una condizione di where come
filtro per restituire le sole righe richieste.
A.A. 2005
Laboratorio di basi di dati - LB
111
ESEMPIO: (1)
EXTRA=USING FILESORT
EXPLAIN
SELECT * FROM emp ORDER BY ename\G
La select è costretta ad effettuare un sort perché non
esiste un indice sulla colonna (ENAME).
A.A. 2005
Laboratorio di basi di dati - LB
112
ESEMPIO: (2)
EXTRA=USING FILESORT
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
key_len:
ref:
rows:
Extra:
A.A. 2005
row *****
1
SIMPLE
emp
ALL
NULL
NULL
NULL
NULL
3
Using filesort
Laboratorio di basi di dati - LB
113
ESEMPIO: (1)
EXTRA=USING TEMPORARY
EXPLAIN
SELECT DISTINCT ename
FROM emp WHERE deptno=10\G
La select utilizza una tabella temporanea per poter
effettuare la DISTINCT, perchè non esiste un indice
sulle colonne (DEPTNO,ENAME).
A.A. 2005
Laboratorio di basi di dati - LB
114
ESEMPIO: (2)
EXTRA=USING TEMPORARY
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
key_len:
ref:
rows:
Extra:
temporary
A.A. 2005
row *****
1
SIMPLE
emp
ref
fk_emp_dept
fk_emp_dept
5
const
1
Using where; Using
Laboratorio di basi di dati - LB
115
ESEMPIO: (1)
EXTRA=USING INDEX
EXPLAIN
SELECT deptno FROM emp
WHERE deptno >= 10\G
La select utilizza il solo indice per recuperare i dati,
NON deve accedere alla tabella.
A.A. 2005
Laboratorio di basi di dati - LB
116
ESEMPIO: (2)
EXTRA=USING INDEX
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
key_len:
ref:
rows:
Extra:
A.A. 2005
row *****
1
SIMPLE
emp
index
fk_emp_dept
fk_emp_dept
5
NULL
3
Using where; Using index
Laboratorio di basi di dati - LB
117
Tuning delle istruzioni SQL
Scrivere istruzioni SQL “semplici”.
Verificare i piani di esecuzione.
Ottimizzare l’accesso alla singola
tabella.
Ottimizzare l’ordine dei passi.
Riscrivere la query.
A.A. 2005
Laboratorio di basi di dati - LB
118
Scrivere istruzioni SQL
“semplici”
Istruzioni SQL con una logica
complessa sono difficili da ottimizzare e
da manutenere.
Query complesse sono il risultato di una
progettazione fisica non corretta
(mancata denormalizzazione).
A.A. 2005
Laboratorio di basi di dati - LB
119
Verificare i piani di esecuzione
E’ fondamentale, per ogni query,
verificare i piani di esecuzione per
ottimizzare gli accessi.
I piani di esecuzione possono essere
forzati mediante l’uso di HINT.
A.A. 2005
Laboratorio di basi di dati - LB
120
Ottimizzare l’accesso alla
singola tabella (1)
Un accesso per indice è preferibile se la
quantità dei dati selezionati è inferiore al
10% (del totale), altrimenti è preferibile
un “full table scan”.
La percentuale varia a secondo della
“engine”. Per MyIsam è conveniente un
accesso per indice fino al 30% delle
righe selezionate.
A.A. 2005
Laboratorio di basi di dati - LB
121
Ottimizzare l’accesso alla
singola tabella (2)
Per tabelle con poche righe è,
normalmente, preferibile un accesso in
full table scan.
Se necessario/conveniente aggiungere
degli indici per selezionare e ordinare i
dati.
A.A. 2005
Laboratorio di basi di dati - LB
122
Nota
La tabella EMP utilizzata negli esempi
successivi ha un elevato numero di
righe (circa 100.000), mentre la tabella
DEPT è di dimensioni ridotte ( < 100).
A.A. 2005
Laboratorio di basi di dati - LB
123
Ottimizzare l’accesso alla
singola tabella: un esempio (1)
DESC emp;
+--------+--------------+------+-----+
| Field | Type
| Null | Key |
+--------+--------------+------+-----+
| empno | int(11)
|
| PRI |
| ename | varchar(255) | YES |
|
| deptno | int(11)
| YES | MUL |
+--------+--------------+------+-----+
A.A. 2005
Laboratorio di basi di dati - LB
124
Ottimizzare l’accesso alla
singola tabella: un esempio (2)
SELECT * FROM emp WHERE
ename LIKE 'nome14045%';
+-------+-----------+--------+
| empno | ename
| deptno |
+-------+-----------+--------+
| 14045 | nome14045 |
5 |
+-------+-----------+--------+
1 row in set (1.76 sec)
A.A. 2005
Laboratorio di basi di dati - LB
125
Ottimizzare l’accesso alla
singola tabella: un esempio (3)
EXPLAIN SELECT * FROM emp WHERE
ename LIKE 'nome14045%‘ \G
*********** 1.
id:
select_type:
table:
type:
...
A.A. 2005
Row ****
1
SIMPLE
emp
ALL
Laboratorio di basi di dati - LB
126
Ottimizzare l’accesso alla
singola tabella: un esempio (4)
CREATE INDEX IX_ENAME
ON emp(ename);
A.A. 2005
Laboratorio di basi di dati - LB
127
Ottimizzare l’accesso alla
singola tabella: un esempio (5)
SELECT * FROM emp WHERE
ename LIKE 'nome14045%';
+-------+-----------+--------+
| empno | ename
| deptno |
+-------+-----------+--------+
| 14045 | nome14045 |
5 |
+-------+-----------+--------+
1 row in set (0.33 sec)
A.A. 2005
Laboratorio di basi di dati - LB
128
Ottimizzare l’accesso alla
singola tabella: un esempio (6)
EXPLAIN SELECT * FROM emp WHERE
ename LIKE 'nome14045%‘ \G
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
A.A. 2005
Row ****
1
SIMPLE
emp
range
IX_ENAME
IX_ENAME
Laboratorio di basi di dati - LB
129
Ottimizzare l’accesso alla
singola tabella: un esempio (7)
Spiegazione: la creazione di un indice
sulla colonna ENAME, velocizza le
query che utilizzano l’indice in modo
SELETTIVO (ossia che recuperano
poche righe).
A.A. 2005
Laboratorio di basi di dati - LB
130
Ottimizzare l’accesso alla
singola tabella: un esempio (8)
SELECT count(*) FROM emp WHERE
ename LIKE 'nome%';
+----------+
| count(*) |
+----------+
|
128000 |
+----------+
1 row in set (1.32 sec)
A.A. 2005
Laboratorio di basi di dati - LB
131
Ottimizzare l’accesso alla
singola tabella: un esempio (9)
EXPLAIN SELECT count(*) FROM emp WHERE
ename LIKE 'nome%‘ \G
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
A.A. 2005
Row ****
1
SIMPLE
emp
range
IX_ENAME
IX_ENAME
Laboratorio di basi di dati - LB
132
Ottimizzare l’accesso alla
singola tabella: un esempio (10)
DROP INDEX IX_ENAME ON emp;
A.A. 2005
Laboratorio di basi di dati - LB
133
Ottimizzare l’accesso alla
singola tabella: un esempio (11)
SELECT count(*) FROM emp WHERE
ename LIKE 'nome%';
+----------+
| count(*) |
+----------+
|
128000 |
+----------+
1 row in set (0.88 sec)
A.A. 2005
Laboratorio di basi di dati - LB
134
Ottimizzare l’accesso alla
singola tabella: un esempio (12)
EXPLAIN SELECT count(*) FROM emp WHERE
ename LIKE 'nome%' \G
*********** 1.
id:
select_type:
table:
type:
…
A.A. 2005
Row ****
1
SIMPLE
emp
ALL
Laboratorio di basi di dati - LB
135
Ottimizzare l’accesso alla
singola tabella: un esempio (13)
Spiegazione: la creazione di un indice
sulla colonna ENAME, rallenta le query
che utilizzano l’indice in modo NON
SELETTIVO (ossia che recuperano
molte righe tramite l’indice).
A.A. 2005
Laboratorio di basi di dati - LB
136
Ottimizzare l’ordine dei passi (1)
La scelta dell’ordine in cui le tabelle
sono messe in join è fondamentale se si
usa l’algoritmo di Nested Loop Join.
E’ particolarmente importante quando si
effettua il join di molte tabelle.
A.A. 2005
Laboratorio di basi di dati - LB
137
Ottimizzare l’ordine dei passi (2)
In caso di “inner” join un qualsiasi
ordine di join è possibile, l’ordine NON è
determinato dall’ordine in cui le tabelle
appaiono nella clausola FROM.
In caso di “outer” join l’ordine è fissato
dalla condizione di outer-join.
A.A. 2005
Laboratorio di basi di dati - LB
138
Ottimizzare l’ordine dei passi (3)
In genere è conveniente scegliere come
driving-table (outer-table) la tabella con
la condizione di filtro più selettiva.
La selettività deve essere considerata
anche rispetto al join con le altre tabelle.
A.A. 2005
Laboratorio di basi di dati - LB
139
Ottimizzare l’ordine dei passi (4)
La driving-table può essere acceduta in
full-table-scan o tramite un indice
selettivo sulle colonne di filtro.
La “inner-table” deve essere acceduta
tramite un indice, preferibilmente
univoco, sulle colonne di join.
A.A. 2005
Laboratorio di basi di dati - LB
140
Ottimizzare l’ordine dei passi (5)
Nell’ottimizzare gli accessi al disco, si
deve considerare la cache dei
dati/indici.
Se la inner-table è “piccola” rispetto alle
dimensioni della cache ci si può
aspettare che i blocchi della tabella (e
dei suoi indici) siano prevalentemente in
cache.
A.A. 2005
Laboratorio di basi di dati - LB
141
Ottimizzare l’ordine dei passi:
un esempio (1)
SELECT e.ename, d.descr
FROM
emp e, dept d
WHERE
e.deptno=d.deptno AND
e.ename like 'nome12345%';
......
11 rows in set (3.05 sec)
A.A. 2005
Laboratorio di basi di dati - LB
142
Ottimizzare l’ordine dei passi:
un esempio (2)
EXPLAIN
SELECT e.ename, d.descr
FROM
emp e, dept d
WHERE
e.deptno=d.deptno AND
e.ename like 'nome12345%‘\G
A.A. 2005
Laboratorio di basi di dati - LB
143
Ottimizzare l’ordine dei passi:
un esempio (3)
*********** 1.
id:
select_type:
table:
type:
…
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
…
A.A. 2005
row ***
1
SIMPLE
d
ALL
row ****
1
SIMPLE
e
ref
FK_EMP_DEPT
FK_EMP_DEPT
Laboratorio di basi di dati - LB
144
Ottimizzare l’ordine dei passi:
un esempio (4)
SELECT /*! STRAIGHT_JOIN */
e.ename, d.descr
FROM emp e, dept d
WHERE
e.deptno=d.deptno and
e.ename like 'nome12345%';
......
11 rows in set (0.40 sec)
A.A. 2005
Laboratorio di basi di dati - LB
145
Ottimizzare l’ordine dei passi:
un esempio (5)
EXPLAIN
SELECT /*! STRAIGHT_JOIN */
e.ename, d.descr
FROM emp e, dept d
WHERE
e.deptno=d.deptno AND
e.ename like 'nome12345%‘\G
A.A. 2005
Laboratorio di basi di dati - LB
146
Ottimizzare l’ordine dei passi:
un esempio (6)
*********** 1.
id:
select_type:
table:
type:
…
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
…
A.A. 2005
row ***
1
SIMPLE
e
ALL
row ****
1
SIMPLE
d
eq_ref
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
147
Ottimizzare l’ordine dei passi:
un esempio (7)
Spiegazione: L’accesso tramite un
indice (per ogni riga della tabella DEPT)
alla tabella EMP è un accesso casuale
(Î alto seek time). E’ preferibile
accedere prima alla tabella EMP (che
ha un numero elevato di righe) in modo
sequenziale e poi alla tabella DEPT
tramite la sua PK.
A.A. 2005
Laboratorio di basi di dati - LB
148
Ottimizzare l’ordine dei passi:
un esempio (8)
Nota: Se la condizione sulla colonna
“ename” è selettiva, si può ottimizzare
ulteriormente la query creando un indice
sulla colonna “ename” della tabella
EMP ed evitando in questo modo il fulltable-scan sulla tabella stessa.
A.A. 2005
Laboratorio di basi di dati - LB
149
Ottimizzare l’ordine dei passi:
un esempio (9)
SELECT e.ename, d.descr
FROM
emp e, dept d
WHERE
e.deptno=d.deptno AND
d.descr like ‘ufficio 6%’ AND
e.ename like 'nome12345%';
......
3 rows in set (0.05 sec)
A.A. 2005
Laboratorio di basi di dati - LB
150
Ottimizzare l’ordine dei passi:
un esempio (10)
EXPLAIN
SELECT e.ename, d.descr
FROM
emp e, dept d
WHERE
e.deptno=d.deptno AND
d.descr like ‘ufficio 6%’ AND
e.ename like 'nome12345%‘\G
A.A. 2005
Laboratorio di basi di dati - LB
151
Ottimizzare l’ordine dei passi:
un esempio (11)
*********** 1.
id:
select_type:
table:
type:
…
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
…
A.A. 2005
row ***
1
SIMPLE
d
ALL
row ****
1
SIMPLE
e
ref
FK_EMP_DEPT
FK_EMP_DEPT
Laboratorio di basi di dati - LB
152
Ottimizzare l’ordine dei passi:
un esempio (12)
SELECT /*! STRAIGHT_JOIN */
e.ename, d.descr
FROM emp e, dept d
WHERE
e.deptno=d.deptno and
d.descr like ‘ufficio 6%’ AND
e.ename like 'nome12345%';
......
11 rows in set (0.35 sec)
A.A. 2005
Laboratorio di basi di dati - LB
153
Ottimizzare l’ordine dei passi:
un esempio (13)
EXPLAIN
SELECT /*! STRAIGHT_JOIN */
e.ename, d.descr
FROM emp e, dept d
WHERE
e.deptno=d.deptno AND
d.descr like ‘ufficio 6%’ AND
e.ename like 'nome12345%‘\G
A.A. 2005
Laboratorio di basi di dati - LB
154
Ottimizzare l’ordine dei passi:
un esempio (14)
*********** 1.
id:
select_type:
table:
type:
…
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
…
A.A. 2005
row ***
1
SIMPLE
e
ALL
row ****
1
SIMPLE
d
eq_ref
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
155
Ottimizzare l’ordine dei passi:
un esempio (15)
Spiegazione: L’utilizzo della tabella
DEPT come driving-table è conveniente
se il filtro applicato (ossia le righe
selezionate dalla DEPT) comporta l’uso
selettivo dell’indice definito sulla FK
della tabella EMP.
A.A. 2005
Laboratorio di basi di dati - LB
156
Ottimizzare l’ordine dei passi:
un esempio (16)
Nota: Nell’esempio riportato l’uso della
tabella DEPT come driving-table è
conveniente perché “poche” righe della
tabella EMP sono legate ad un
“dipartimento” la cui descrizione inizia
con la stringa “ufficio 6”.
A.A. 2005
Laboratorio di basi di dati - LB
157
Riscrivere la query
Spesso le query possono essere scritte
in diversi modi, tra loro equivalenti.
E’ preferibile scrivere la query in modo
opportuno, l’ottimizzatore non sempre
riesce ad individuare la query più
performante.
A.A. 2005
Laboratorio di basi di dati - LB
158
Riscrivere la query:
un esempio (1)
SELECT * FROM emp WHERE
(ename like 'nome12345%') OR
(empno = 10000);
......
12 rows in set (0.81 sec)
A.A. 2005
Laboratorio di basi di dati - LB
159
Riscrivere la query:
un esempio (2)
EXPLAIN
SELECT * FROM emp WHERE
(ename like 'nome12345%') OR
(empno = 10000) \G
A.A. 2005
Laboratorio di basi di dati - LB
160
Riscrivere la query:
un esempio (3)
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row **
1
SIMPLE
emp
ALL
PRIMARY,ix_ename
NULL
Laboratorio di basi di dati - LB
161
Riscrivere la query:
un esempio (4)
SELECT * FROM emp WHERE
(ename like 'nome12345%')
UNION
SELECT * FROM emp WHERE
(empno = 10000);
......
12 rows in set (0.01 sec)
A.A. 2005
Laboratorio di basi di dati - LB
162
Riscrivere la query:
un esempio (5)
EXPLAIN
SELECT * FROM emp WHERE
(ename like 'nome12345%')
UNION
SELECT * FROM emp WHERE
(empno = 10000) \G
A.A. 2005
Laboratorio di basi di dati - LB
163
Riscrivere la query:
un esempio (6)
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *******
1
PRIMARY
emp
range
ix_ename
ix_ename
Laboratorio di basi di dati - LB
164
Riscrivere la query:
un esempio (7)
*********** 2.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row ******
2
UNION
emp
const
PRIMARY
PRIMARY
Laboratorio di basi di dati - LB
165
Riscrivere la query:
un esempio (8)
*********** 3.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
NULL
UNION RESULT
<union1,2>
ALL
NULL
NULL
Laboratorio di basi di dati - LB
166
Riscrivere la query:
un esempio (9)
Spiegazione: MySql utilizza un solo
indice per query (nella versione < 5.0).
Se non può utilizzare nessun indice per
soddisfare tutte le condizione effettua
un full table scan. Se la query viene
“divisa” in più union ha la possibilità di
utilizzare un indice per ogni union.
A.A. 2005
Laboratorio di basi di dati - LB
167
Gli Hint (1)
E’ un suggerimento all’ottimizzatore per
guidarlo verso la scelta di un piano di
esecuzione.
A.A. 2005
Laboratorio di basi di dati - LB
168
Gli Hint (2)
Non è uno standard SQL, ogni rdbms
ha una sintassi proprietaria.
Permettono di ridurre il tempo di
PARSE.
Permettono (se corretti) di impostare il
piano di esecuzione ottimale.
A.A. 2005
Laboratorio di basi di dati - LB
169
Gli Hint (3)
E’ fortemente consigliabile racchiudere
gli HINT tra /*! HINT */
La maggior parte degli rdbms
considerano tutto quello che si trova tra
/* e */ un commento, quindi non
segnalano un errore sintattico.
A.A. 2005
Laboratorio di basi di dati - LB
170
I principali HINT
STRAIGHT_JOIN
USE INDEX
IGNORE INDEX
FORCE INDEX
A.A. 2005
Laboratorio di basi di dati - LB
171
L’hint STRAIGHT_JOIN
Forza l’ottimizzatore ad effettuare il
JOIN nella sequenza specificata nella
clausola FROM.
A.A. 2005
Laboratorio di basi di dati - LB
172
STRAIGHT_JOIN: la sintassi
SINTASSI:
SELECT /*! STRAIGHT_JOIN */
<column-list>
FROM …
A.A. 2005
Laboratorio di basi di dati - LB
173
ESEMPIO: STRAIGHT_JOIN (1)
EXPLAIN
SELECT /*! STRAIGHT_JOIN */ *
FROM emp e, dept d
WHERE e.deptno=d.deptno \G
A.A. 2005
Laboratorio di basi di dati - LB
174
ESEMPIO: STRAIGHT_JOIN (2)
*********** 1.
id:
select_type:
table:
...
*********** 2.
id:
select_type:
table:
...
row *****
1
SIMPLE
e
row *****
1
SIMPLE
d
Accede prima alla tabella EMP (E) e alla DEPT (d)
A.A. 2005
Laboratorio di basi di dati - LB
175
Gli hint INDEX
Forza l’ottimizzatore a valutare (USE),
ignorare (IGNORE) o utilizzare
(FORCE) l’uso di un indice.
Non sono validi per forzare il piano di
esecuzione di ORDER BY o GROUP
BY.
A.A. 2005
Laboratorio di basi di dati - LB
176
Hint INDEX: la sintassi
SINTASSI:
SELECT … FROM <table>
[/*! {USE INDEX | IGNORE INDEX |
FORCE INDEX} (index-list) */]
…
A.A. 2005
Laboratorio di basi di dati - LB
177
ESEMPIO: hint INDEX (1)
SELECT count(*) FROM emp
WHERE ename like ‘nome%’;
......
1 row in set (1.57 sec)
A.A. 2005
Laboratorio di basi di dati - LB
178
ESEMPIO: hint INDEX (1)
EXPLAIN
SELECT count(*) FROM emp
WHERE ename like ‘nome%’ \G
A.A. 2005
Laboratorio di basi di dati - LB
179
ESEMPIO: hint INDEX (3)
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
emp
range
ix_ename
ix_ename
Laboratorio di basi di dati - LB
180
ESEMPIO: hint INDEX (4)
SELECT count(*) FROM emp
/*! IGNORE INDEX (ix_ename) */
WHERE ename like ‘nome%’;
......
1 row in set (0.40 sec)
A.A. 2005
Laboratorio di basi di dati - LB
181
ESEMPIO: hint INDEX (5)
EXPLAIN
SELECT count(*) FROM emp
/*! IGNORE INDEX (ix_ename) */
WHERE ename like ‘nome%’ \G
A.A. 2005
Laboratorio di basi di dati - LB
182
ESEMPIO: hint INDEX (6)
*********** 1.
id:
select_type:
table:
type:
possible_keys:
key:
...
A.A. 2005
row *****
1
SIMPLE
emp
ALL
NULL
NULL
Laboratorio di basi di dati - LB
183
Riepilogo
Union, subquery e Join
Il piano di esecuzione
L’ottimizzatore
Le statistiche
Il comando EXPLAIN
Gli Hint
A.A. 2005
Laboratorio di basi di dati - LB
184
Scarica
