Loading presentation...

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

Mysql: Indici, Query e Performance

No description
by

lorenzo iannone

on 29 November 2013

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Mysql: Indici, Query e Performance

My
SQL
Indici, Query e Performance

Architettura
di
MySQL

E' composta da tre livelli
Connection Manager
Server
Storage Engine
Il primo livello e` il
connection manager
che si occupa di gestire le connessioni verso i client autenticando gli utenti e controllandone i privilegi
Il secondo livello e` il
Server
che si occupa di analizzare, parsare, ottimizzare, eseguire e fare caching delle query. Esporta tutte le funzionalita` built-in:
- funzioni per date, math, encryption, ecc..
- stored procedures
- trigger
- view
Il terzo livello sono gli
Storage Engine
che si occupano del salvataggio e recupero dei dati. Comunicano con il Server attraverso una interfaccia comune chiamata Storage Engine API.
Il Server non conosce lo Storage Engine che eseguira` il recupero dei dati ma puo` chiedere quali feature supporta e quanto costano al fine di scegliere le strategie opportune e riscrivere la query come ritiene meglio
InnoDB
Caratteristiche principali
Usa MVCC (multiversion concurrency control) per garantire sempre copie coerenti di dati
E' molto rapido nelle Primary Key Lookup
Tutti gli indici contengono sempre anche la colonna (o le colonne) PRIMARY: primary grandi implicano che tutti gli indici secondari sono grandi
Supporta le transazioni e l'integrita` referenziale (Foreign key ma non check constraint)
row level lock
MyISAM
caratteristiche principali
non supporta transazioni (il problema della concorrenza di accesso ai dati non si pone proprio)
indici Full Text
indici compressi (occupano meno spazio)
funzioni GIS
non supporta integrita` referenziale
table level lock
Altri Engine
Memory:
tabelle in memoria, molto veloci ma non esistono piu` dopo un restart.
Infobright:
column store, indicato per data warehousing
Percona:
InnoDB rivisitato in features
Disegnare schema performanti
Solo qualche consiglio, dal momento che ci troviamo nella situazione in cui abbiamo uno schema gia` definito: dobbiamo fare i conti con l'esistente senza grandi possibilita` di modifiche.
Keep it simple!
Come spesso accade, semplice vuol dire veloce e manutenibile.
non affidare relazioni alla semantica dei dati (se in questo campo c'e` 'AG' allora JOIN su AGENZIA) - non si possono esprimere senza
stored procedure
dati semplici offrono performance migliori: in genere gli interi sono piu` veloci dei (var)char
Esempio
un ip puo` essere un varchar(15) e contenere '192.168.1.2' oppure un bigint e contenere 192168001002

8 byte anziche` 16 e possibilita` di confronti piu` rapidi
occupa tanti byte quanti contiene piu`

1 se la lunghezza del campo arriva fino a 255 byte
2 se supera 255 byte
VARCHAR(100): "pippo" occupa 6 byte
VARCHAR(500) "pippo" occupa 7 byte
L'update un campo varchar con un dato che supera la lunghezza del precedente, MySQL deve compiere piu` lavoro trovando una nuova locazione per il dato e frammentando la tabella
VARCHAR
VARCHAR
CHAR
A lunghezza fissa: occupa sempre tanti byte quanti dichiarati
VARCHAR
Se siamo troppo generosi con i varchar consumiamo piu` memoria quando mysql opera sui dati:
il server alloca la memoria a blocchi avendo come indicazione su quanta riservarne solo la dimensione del varchar.
Accade ad esempio con le in-memory temporary table durante il sorting.
Particolarita` sui tipi di dato
quando possibile:
usare enum o set anziche` stringhe (fare i conti con le alter se si cambiano valori)
dimensionare gli interi correttamente e non dimenticarsi che esiste anche UNSIGNED
evitare chiavi primarie (var)char: 3==4 e` molto piu` veloce che 'foo' == 'foo'
evitare tabelle con molte colonne (molte inteso come tante decine): appesantiscono il passaggio di dati tra lo storage engine e il server attraverso i buffer offerti dalle Storage Engine API, in tal caso dividere le colonne si piu` tabelle in base alla frequenza d'uso.
facile a dirsi meno a farsi, trovare un giusto bilanciamento tra normalizzazione e denormalizzazione
Indici
i piu` comuni tipi di indice sono
- hash index
- btree
- bree+

Hash Index
L'hash index e` sostanzialmente un'array associativo che lega l'hash della primary key al relativo record o un riferimento ad esso.
Hash Index
Hash Index
supportano solo match esatti (idAnnuncio = 123456), ma in questo sono velocissimi (fintanto che le collisioni sono gestibili)
non aiutano negli ordinamenti
non c'e` modo di sapere quanti elementi ci sono tra un record e un altro
Hash Index
Chi implementa gli hash index?
Memory Engine
InnoDB
(ne ha una variante)
Emulare gli hash index
CREATE TABLE test (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
link_annuncio VARCHAR(1024) NOT NULL,
link_annuncio_crc INTEGER UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY(id),
KEY(link_annuncio_crc)
) ENGINE = MYISAM;

SELECT *
FROM test
WHERE
link_annuncio_crc = crc32('http://www.immobiliare.it/41936939-Vendita.html')
AND
link_annuncio = 'http://www.immobiliare.it/41936939-Vendita.html');
BTREE
Un indice btree e` un albero di ricerca ordinato in cui ogni nodo e` di valore inferiore al nodo che lo linka
BTree
BTree
Btree+
Il Btree+ e` un Btree in cui i nodi foglia sono collegati da un link in modo tale che raggiunta una foglia sia possibile puntare ai valori successivi senza risalire l'albero e i valori degli elementi indcizzati sono sempre e solo nelle foglie.
Btree+
Btree / Btree+
ideali per
match esatto sulle prime n colonne e match aperto a destra sulla n+1 esima: where nome='lorenzo' and cognome like 'ian%'
match esatti su tutte le colonne dell'indice
match esatto sulle prime n colonne e match su range di valori (100<x<110) sulla n+1
Chi implementa i btree/btree+
myISAM
- implementa i
Btree
innoDB
- implementa
Btree+
con
adaptive hash index
: vuol dire che quando l'engine si accorge che un indice viene acceduto spesso con match esatto crea in memoria (quindi non in modo permanente) un hash index
order by
STRATEGIE
DI
INDICIZZAZIONE

definire indici performanti
usare gli indici in modo performante
Prefix Index
Indicizzare i campi di testo
Indicizzare interamente un campo di testo puo` non essere una buona strategia.
Rendera` l'indice piu` grande e piu` lento.

Nella maggioranza dei casi si possono definire indici altrettanto selettivi (o quasi) su un un subset della stringa.
Abruzzo
Basilicata
Calabria
Campania
Emilia-Romagna
Friuli-Venezia Giulia
Lazio
Liguria
Lombardia
Marche
Molise
Piemonte
Puglia
Sardegna
Sicilia
Toscana
Trentino-Alto Adige
Umbria
Valle d'Aosta
Veneto

Indice su tutta la lunghezza:
ADD KEY (regione)
Selettivita`: 20/20 = 1
Indice sul primo carattere:
ADD KEY(regione(1))
Selettivita`: 11/20 = 0.55
Selettivita` = DISTINCT(key) / COUNT(*)
Indice sui primi 3 caratteri:
ADD KEY(regione(3))
Selettivita`: 20/20 = 1
In questo esempio.. un indice sui primi 3 caratteri ha la stessa selettivita` dell'indice completo ma performance migliori.
Non c'e` comunque bisogno di ricercare una selettivita` identica all'indice intero
. Possiamo in molti casi accontentarci di averne una comparabile
Regioni
------------
Controindicazioni
I prefix index non possono essere usati per order by e group by (e` un limite di mySQL)


Non concorrono a creare covering index (vedremo in seguito che vuol dire)
Indici multicolonna
Sono gli indici definiti su piu` di una colonna di una tabella
Nella pratica comune si e` soliti definire gli indici sui campi cosi` come appaiono nella WHERE
E' possibile trarre vantaggio da un giusto ordine delle colonne indicizzando prima le colonne che produrranno selettivita` maggiore: in tal modo si scorrera` il minor numero possibile di elementi dell'indice
Covering index
E' un indice che copre le colonne richieste in una select.
Covering index

InnoDB clustered index
Gli indici di InnoDB sono clustered. Vuol dire che che i valori delle righe sono memorizzate nei nodi foglia dell'indice Primary o del primo unique index se manca una primary (ecco perche` le tabelle senza primary fanno schifo!)
Covering Index
Definire un indice secondario che sia 'covering' evita che alla fine della consultazione dell'indice si debba eseguire una lookup sull'indice primary per recuperare i valori: sono gia` tutti nell'indice secondario
Covering index
Quando una query sfrutta un covering index troveremo la dicitura Using Index nel campo EXTRA dell'explain. Pertanto raggiungiamo elevata efficienza quando anche in TYPE avremo "index": mysql prevede di scorrere l'indice e l'indice e` tutto quello che serve per eseguire la query

tabella: annuncio, key: KEY `annunci_attivi_agenzia` (`fkAgenzia`,`fkStatoAnnuncio`)
database: devel


explain select SQL_NO_CACHE fkAgenzia, fkStatoAnnuncio from annuncio USE INDEX (annunci_attivi_agenzia) where fkAgenzia = 93434

select_type: SIMPLE
table: annuncio
type: ref
possible_keys: annunci_attivi_agenzia
key: annunci_attivi_agenzia
key_len: 5
ref: const
rows: 156
Extra: Using where;
Using index

Execution time: 156 rows in set (0,06 sec)
explain select SQL_NO_CACHE fkAgenzia, fkStatoAnnuncio,
numeroCivico
from annuncio USE INDEX (annunci_attivi_agenzia) where fkAgenzia = 93434

select_type: SIMPLE
table: annuncio
type: ref
possible_keys: annunci_attivi_agenzia
key: annunci_attivi_agenzia
key_len: 5
ref: const
rows: 156
Extra: Using where

Execution time: 156 rows in set (0,36 sec)
ESEMPIO
tabella: annuncio, key: KEY annuncio_count_zona (`fkComune`,`fkStatoAnnuncio`,`fkTipologia`,`fkContratto`,`fkZona`,`fkFasciaPrezzo`)
database: devel


explain select SQL_NO_CACHE fkComune, fkStatoAnnuncio from annuncio USE INDEX (annuncio_count_zona) where fkComune = 6737 and fkStatoAnnuncio > 0

select_type: SIMPLE
table: annuncio
type: range
possible_keys: annuncio_count_zona
key: annuncio_count_zona
key_len: 10
ref: NULL
rows: 298760
Extra: Using where;
Using index

Execution time: 171761 rows in set (1,63 sec)
explain select SQL_NO_CACHE fkComune, fkStatoAnnuncio,
indirizzo
from annuncio USE INDEX (annuncio_count_zona) where fkComune = 6737 and fkStatoAnnuncio > 0

select_type: SIMPLE
table: annuncio
type: range
possible_keys: annuncio_count_zona
key: annuncio_count_zona
key_len: 10
ref: NULL
rows: 298760
Extra: Using where;

Execution time: 171761 rows in set (8,84 sec)
ESEMPIO
Ottimizzazione
delle Query

qual e` la causa piu` frequente della lentezza delle query?
banalmente, richiediamo piu` dati di quanti ce ne servano
si richiedono piu` record del necessario
mysql recupera invece tutto il recordset in un solo colpo: per cui scorre una porzione maggiore di indice, esegue un numero maggiore di lookup, genera piu` traffico di rete
in molti dbms i recordset sono dinamici: i risultati vengono paginati in automatico, per cui richiedere tutta la tabella e prendere i primi n elementi ha piu` o meno lo stesso peso che prendere solo i primi n.
si richiedono piu` campi del necessario
si riduce la possibilita` di usare covering index
si aumenta l' I/O, soprattutto quando si devono generare in memory temporary table. Si saturano prima i buffer di comunicazione tra server e storage engine, si genera un maggiore traffico sulla rete e anche il client occupa piu` memoria per contenere il recordset richiesto.
Misurare le performance
il tempo di risposta di una query e` diviso in due parti:
-queue time: tempo di accodamento, che e` il tempo che passa da quando la query viene sottoposta a quando e` eseguita

- service time: il tempo impiegato da quando il server manda in esecuzione la query a quando invia i dati al client
Misurare le performance
rows examined: il numero di righe che mysql esamina per restituire il risultato
(si trova nell'explain)

rows returned: la dimensione finale del recordset.

in un mondo ideale coincidono,
altrove praticamente mai
Misurare le performance
Per avere un idea di quanto sia buono il piano di accesso di una query si puo` guardare al campo Ref dell'explain: rappresenta la strategia scelta per individuare una singola riga del resultset.
valori del ref
const: nessun tipo di lookup

unique index lookup: ricerca su indice univoco (molto veloce su btree o btree+)

range scan: scorrimento di una porzione di indice

index scan: scorrimento di un intero indice

full table scan: scorrimento di un intero indice e lookup finale per i valori
Misurare le performance
in caso di join e` utile guardare al campo
type che indica le politiche di join scelte dall'optimizer.
valori del type
system – la tabella ha zero o una riga, praticamene non esiste overhead di join

const – la tabella ha una sola riga matchata ed e` indicizzata. E` il tipo piu` veloce di jon perche` la tabella ha bisogno di essere letta una sola volta dopo di che i valori del della colonna joinata possono essere trattati come costanti.
select * from agenzia join contrattoAgenzia on idAgenzia = fkAgenzia and fkAgenzia = 93434

eq_ref – tutte le componenti di un indice sono usate nella join e l'indice e` una chave primaria o unique not null.
select * from agenzia join contrattoAgenzia on idAgenzia = fkAgenzia;

ref – come eq_ref, ma l'indice usato non e` unique oppure quando usa solo una porzione di indice (as esempio i primi campi di un indice multicolonna).
select * from agenzia join annuncio on fkAgenzia = idAgenzia

fulltext – join effettuato usando un indice fulltext.

ref_or_null – stesso discorso di ref, ma la strategia di join ricerca anche i valori nulli dei campi.

index_merge – la join e` effettuata usando una combinazione di indici parziali.

unique_subquery – viene usato un operatore di tipo IN che ritorna solo una riga recuperata dalla primary key

index_subquery – come unique subquery ma con righe multiple restituite

range – e` usato un indice per cercare le righe che matchano in un intervallo specifico (ad esempio usando gli operatori IN e BETWEEN o tipcamente <)

index – viene eseguito uno scannng di un intero indice

all – viene scandita tutta la tabella senza uso di indici (il caso peggiore che c'e`)
Esecuzione di una query
Per capire come ottimizzare le query e` utile conoscere il modello di esecuzione delle stesse:
1 - il client invia al server la query
2 - il server controlla la query cache ed eventualmente restituisce il risultato in cache, altrimenti prosegue
3 - il server parsa e ottimizza la query ottenendo un piano di esecuzione che leggiamo dall'explain
4 - vengono invocate le api dello storage engine per eseguire il piano di esecuzione
3 - query optimizer
Mysql utilizza un optimizer basato sul costo di una query. Il costo e` una misura del numero di pagine di 4kb di dati recuperati pesato sul tempo di valutazione delle condizioni di where.
Il costo di esecuzione dell'ultima query si vede con SHOW STATUS LIKE 'Last_query_cost';
E` basato su statistiche: numero di pagine per tabella/indice, grandezza del record, selettivita` dell'indice. Non contempla eventuali cache
L'optimizer non sempre sceglie il piano di esecuzione migliore:
Le statistiche possono essere non aggiornate, il costo non e` sempre uguale al costo reale (misura del tempo) di esecuzione: le politiche di I/O possono rendere piu` efficiente recuperare piu` pagine in sequenza che meno pagine sparse sul disco
3 - query optimizer
3 - query optimizer
- riordina le join: riscrive le join in ordine tale da massimizzare l'efficienza di accesso

- converte il tipo di join: con le opportune condizioni di where una left join si puo` riscrivere come una inner.

- minimizza le condizioni algebriche sulle where (a=1 or a>=2 diventa a>=1)

- lookup per min e max (prende il primo o l'ultmo elemento del btree)

- espande le costanti all'interno di una query (select * from annuncio join agenzia on fkAgenzia = idAgenzia where idAgenzia = 93434)
- seleziona i covering index

- riordina e riscrive le subquery

- termina la query se necessario (limit oppure where che diventano irrisolvibili) select * from annuncio where idAnnuncio is null

Limiti dell'optimizer
Esaminiamo qualche caso in cui l'optimizer va in sciopero!
Uso di IN
L' IN su una lista e molto veloce (piu` veloce di una serie di OR)
L'IN con una subquery invece fa schifo!
select * from annuncio where fkAgenzia in (select idAgenzia from agenzia where idAgenzia > 1 and idAgenzia < 100)
ci aspetteremmo che la query sia riscritta come
select * from annuncio where fkAgenzia in (2,3,....,99)
invece diventa
SELECT * FROM annuncio WHERE EXISTS (
SELECT * FROM agenzia WHERE agenzia.idAgenzia > 1 and agenzia.idAgenzia<100
AND agenzia.idAgenzia = annuncio.fkAgenzia);
invece noi possiamo scriverla come:
SELECT * from annuncio join agenzia on idAgenzia = fkAgenzia WHERE idAgenzia>1 and idAgenzia<100;
UNION
select data from gtxRichiestePortali order by data
union
select data from incrocio order by data
limit 100;
in questo caso mysql crea una temporary table con l'unione delle due query e solo successivamente estrae i 100 elementi. Possiamo fare in modo di creare una temporary table piu` piccola:
select data from gtxRichiestePortali order by data limit 150
union
select data from incrocio order by data limit 150
limit 100;
Un caso pratico
Selezionare tutte le agenzie attive con contratto getrix 2 che non abbiano entry in gtxHashAgenzia e per le quali esiste almeno un annuncio
Traduciamo il linguaggio naturale uno a uno con sql
SELECT ag.idAgenzia
FROM agenzia ag
JOIN contrattoAgenzia ca ON ag.idAgenzia = ca.fkAgenzia
WHERE
ag.idAgenzia NOT IN (
SELECT idAgenzia
FROM gtxHashAgenzia
WHERE idAgenzia in
(select fkAgenzia
from annuncio
join agenzia ON annuncio.fkAgenzia = agenzia.idAgenzia
WHERE fkTipologiaAgenzia in (1 , 2, 6))) and ca.getrix = 2;

Esempio pratico
Va da se che non e` una query ideale, per via di quanto detto sulle IN SELECT
Una tecnica di riscrittura tipica delle IN SELECT e` la decomposizione in LEFT JOIN.
SELECT DISTINCT(idAgenzia)
FROM agenzia ag
JOIN contrattoAgenzia ca ON ag.idAgenzia = ca.fkAgenzia
LEFT JOIN gtxHashAgenzia ha ON ha.fkAgenzia = ag.idAgenzia
LEFT JOIN annuncio an ON an.fkAgenzia = ag.idAgenzia
WHERE ag.fkTipologiaAgenzia in (1 , 2, 6)
AND ca.getrix = 2
AND ha.fkAgenzia IS NULL
AND an.fkAgenzia IS NOT NULL
Da notare che la join con annuncio duplicherebbe gli idAgenzia restituiti, quindi necessitiamo di aggiungere l'operatore DISTINCT
Abbiamo migliorato di molto il piano di accesso originario riscrivendo le IN come LEFT JOIN.
Restano un paio di questioni: using temporary e distinct. La Temp Table viene creata per salvare i valori di idAgenzia gia` selezionati, eliminando la distinct pertanto eliminamo la Temp.
select distinct(t1.qualcosa) FROM t1 LEFT JOIN .. t2 where t2.qualcosa IS NOT NULL e` semanticamente equivalente a dire "prendimi qualcosa da t1 se quel qualcosa esiste anche in t2". Praticamente richiediamo che t2.qualcosa esista.. ma non ci interessa in quante istanze esiste. Per questo EXISTS termina appena trova il primo valore in t2, cosa che invece non fa select distinct. In questo caso richiedere l'esistenza premia.
SELECT ag.idAgenzia
FROM agenzia ag
JOIN contrattoAgenzia ca ON ag.idAgenzia = ca.fkAgenzia
WHERE
NOT EXISTS( SELECT 1
FROM gtxHashAgenzia k
WHERE k.fkAgenzia = ag.idAgenzia)
AND EXISTS( SELECT 1
FROM annuncio
WHERE fkAgenzia = ag.idAgenzia)
AND ag.fkTipologiaAgenzia in (1 , 2, 6)
AND ca.getrix = 2;
Full transcript