Architettura database Mysql

L’articolo descrive le caratteristiche tecniche di un Database creato in ambiente MySQL, la corrispondenza tra Database e Directory sul filesystem, i tipi di tabella supportati da MySQL e l’uso della memoria.

Caratteristiche generali

Tutti i database creati in ambiente MySQL corrispondono fisicamente a delle sottodirectory della directory definita come datadir nel file di configurazione /etc/mysql/my.cnf

All’interno del file my.cnf troviamo qualcosa di simile a:

user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english

In questo caso la directory definita come datadir è la /var/lib/mysql. Questo significa che se si crea un nuovo database db_name, automaticamente verrà creata la seguente directory sul filesystem: /var/lib/mysql/db_name.

Un database può essere vuoto, in tal caso la directory corrispondente sul filesystem sarà vuota, oppure può contenere alcune tabelle, in questo caso la directory conterrà alcuni file. Un database non può contenere un altro database. A ogni tabella del database corrisponde un file all’interno della directory corrispondente al database sul filesystem. Il file della tabella, conterrà la definizione della struttura della tabella stessa. A seconda del tipo di tabella creata, nella directory del database possiamo trovare diversi tipi di file. Per tutti i tipi di tabelle create in un database MySQL, viene generato un file che ha lo stesso nome della tabella ed estensione .frm, tale file contiene al suo interno la definizione della struttura della tabella. Quando viene creato un nuovo database in MySQL, all’interno della diretory del database viene generato il file db.opt dove vengono registrate le caratteristiche del database.

Il contenuto del file db.opt potrebbe essere simile a:

default-character-set=latin1
default-collation=latin1_swedish_ci

Tipi di tabelle

MySQL supporta i seguenti tipi di tabelle:

Tabelle tipo MyISAM
Tabelle tipo InnoDB
Tabelle tipo MERGE
Tabelle tipo HEAP
Tabelle di tipo MyISAM

Le tabelle di tipo MyISAM hanno le seguenti caratteristiche:

Ogni tabella è rappresentata sul disco da un file che ne descrive il formato con estensione .frm, da un file che contiene i dati con estensione .MYD e da un file contenente gli indici con estensione .MYI. Tutti i file sono memorizzati all’interno della directory del database;
La clausola AUTO_INCREMENT è più flessibile di tutti gli altri tipi di tabelle;
Possono essere utilizzate per creare tabelle di tipo MERGE;
Possono essere convertite in tabelle compresse, a sola lettura, molto veloci;
Supportano il tipo di ricerca FULLTEXT;
Supportano il lock a livello di tabella. In lettura l’accesso è consentito simultaneamente a più query, mentre in scrittura viene utilizzato un lock esclusivo a livello di tabella;
Tabelle di tipo InnoDB

Le tabelle di tipo InnoDB hanno le seguenti caratteristiche:

Ogni tabella è rappresentata sul disco da un file che ne descrive il formato con estensione .frm, mentre i dati e gli indici sono scritti all’interno di uno o più file utilizzati come tablespace comune a tutte le tabelle di questo tipo;
Questo tipo di tabella supporta le transazioni, operazioni definite all’interno degli statement BEGIN, COMMIT, ROLLBACK...
InnoDB fornisce un sistema per il recupero automatico dei dati in caso di crash server MySQL o del pc sul quale il server è in esecuzione;
InnoDB supporta le relazioni (foreign keys) e i vincoli di integrità referenziali;
La gestione della concorrenza per le query è gestita tramite multi- versioning e il lock a livello di riga;
Tabelle di tipo MERGE

Le tabelle di tipo MERGE hanno le seguenti caratteristiche:

Una tabella MERGE è il risultato dell’insieme di più tabelle MyISAM. Ogni tabella MERGE è rappresentata da due file, uno con estenzione .frm che contiene la definizione della struttura della tabella e uno con estensione .MRG contenente la lista dei file MyISAM che costituiscono le tabelle unite nella MERGE;
L’interrogazione di una tabella MERGE comporta l’interrogazione di tutte le tabelle che la compongono;
Una tabella MERGE può essere utile quando si ha la necessità di memorizzare grandi quantità di dati essendo un’unità logica che supera di gran lunga la capacità massima di memorizzazione dati di una tabella MyISAM;
Tabelle di tipo HEAP

Le tabelle di tipo HEAP hanno le seguenti caratteristiche:

Ogni tabella è rappresentata sul disco da un file che ne descrive il formato con estensione .frm, mentre i dati e gli indici sono scritti in memoria. Ne consegue che questo tipo di tabella è molto veloce, per contro i dati memorizzati in queste tabelle non sopravvivono al riavvio del server;
Le tabelle HEAP utilizzano una gran quantità di memoria, quindi non vengono usate per memorizzare grandi moli di dati;
MySQL e la memoria

Il server MySQL è multithread, tecnicamente un thread è un flusso di controllo, ovvero un percorso di esecuzione attraverso il codice indipendente all’interno di un processo. Per ogni client connesso, il server di MySQL crea un thread per gestire quella connessione. Il server mantiene una cache dei gestori dei thread; quando avviene la disconnessione da parte di un client, il thread che gestiva quella connessione viene messo in cache se la cache non è piena, allo stesso modo quando un client si connette al server, gli viene assegnato un thread presente in cache. Questa gestione dei thread in cache aumenta notevolmente le prestazioni in termini di tempi di connessione e disconnessione al database.

Il server MySQL, utilizza una serie di buffer per mantenere in memoria le informazioni necessarie a ridurre al minimo il numero degli accessi al disco. Tra i buffer utilizzati dal server di MySQL troviamo:

le grant tables (tabelle di sistema) dove sono memorizzati i dati degli utenti del database (info, permessi, etc.). In MySQL l’accesso ai dati viene controllato per ogni query inviata dai client, bufferizzando le grant tables i tempi di risposta del server diminuiscono notevolmente;
il key buffer che tiene in memoria i blocchi degli indici delle tabelle;
la table cache che tiene in memoria i descrittori delle tabelle aperte;
la query cache che tiene in memoria il piano di esecuzione delle query eseguite ripetutamente;
la host cache che tiene in memoria la coppia nome_host / indirizzo_ip ottenuta mediante interrogazione del server DNS