Procedure consigliate per la risoluzione dei problemi del server flessibile del Database di Azure per MySQL

Usare le sezioni seguenti per mantenere i database del server flessibile del Database di Azure per MySQL in esecuzione senza problemi e usare queste informazioni come principi guida per garantire che gli schemi siano progettati in modo ottimale e per offrire prestazioni ottimali per le applicazioni.

Ridurre il numero di indici

In un ambiente di database occupato, è possibile osservare un utilizzo elevato di I/O, che può essere un indicatore di modelli di accesso ai dati inefficaci. Gli indici inutilizzati possono avere un impatto negativo sulle prestazioni quando utilizzano spazio su disco e cache e rallentano le operazioni di scrittura (INSERT/DELETE/UPDATE). Gli indici inutilizzati consumano inutilmente più spazio di archiviazione e aumentano le dimensioni del backup.

Prima di rimuovere qualsiasi indice, accertarsi di raccogliere informazioni sufficienti per verificare che non sia più in uso. Questa verifica consente di evitare inavvertitamente di rimuovere un indice fondamentale per una query che viene eseguita solo trimestralmente o annualmente. Accertarsi inoltre di considerare se un indice viene usato per applicare l'univocità o l'ordinamento.

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

(oppure)

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

Elencare gli indici più attivi nel server

L'output della query seguente fornisce informazioni sugli indici più usati in tutte le tabelle e gli schemi nel server del database. Queste informazioni sono utili per identificare il rapporto tra scritture e letture rispetto a ogni indice e i numeri di latenza per le letture e le singole operazioni di scrittura, che possono indicare che è necessaria un'ulteriore ottimizzazione rispetto alla tabella soggiacente e alle query correlate.

SELECT
object_schema AS table_schema,
object_name AS table_name,
index_name, count_star AS all_accesses,
count_read,
count_write,
Concat(Truncate(count_read / count_star * 100, 0), ':',
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio,
 count_fetch AS rows_selected ,
 count_insert AS rows_inserted,
 count_update AS rows_updated,
 count_delete AS rows_deleted,
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency ,
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency,
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency,
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency,
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star > 0
ORDER BY sum_timer_wait DESC

Esaminare la progettazione della chiave primaria

Il server flessibile del Database di Azure per MySQL usa il motore di archiviazione InnoDB per tutte le tabelle nontemporary. Con InnoDB, i dati vengono archiviati all'interno di un indice clusterizzato usando una struttura ad albero B. La tabella è organizzata fisicamente in base ai valori della chiave primaria, ovvero le righe vengono archiviate nell'ordine della chiave primaria.

Ogni voce di chiave secondaria in una tabella InnoDB contiene un puntatore al valore della chiave primaria in cui vengono archiviati i dati. In altre parole, una voce di indice secondario contiene una copia del valore della chiave primaria a cui si riferisce la voce. Pertanto, le scelte di chiave primaria hanno un effetto diretto sulla quantità di sovraccarico di archiviazione nelle tabelle.

Se una chiave deriva da dati effettivi ,ad esempio nome utente, posta elettronica, SSN e così via, viene chiamata chiave naturale. Se una chiave è artificiale e non deriva dai dati (ad esempio, un intero autoincremented), viene definito chiave sintetica o chiave surrogata.

È in genere consigliabile evitare di usare chiavi primarie naturali. Queste chiavi sono spesso molto ampie e contengono valori lunghi provenienti da una o più colonne. Ciò a sua volta può comportare un sovraccarico di archiviazione severo con il valore della chiave primaria copiato in ogni voce della chiave secondaria. Inoltre, le chiavi naturali non seguono in genere un ordine predeterminato, che riduce notevolmente le prestazioni e provoca la frammentazione della pagina quando le righe vengono inserite o aggiornate. Per evitare questi problemi, usare chiavi surrogate che aumentano in modo monotonico anziché chiavi naturali. Una colonna Autoincrement (big)integer è un buon esempio di chiave surrogata che aumenta in modo monotonico. Se è necessaria una determinata combinazione di colonne, mantenere l’univocità, dichiarare tali colonne come chiave secondaria univoca.

Durante le fasi iniziali di creazione di un'applicazione, potrebbe non essere opportuno immaginare un momento in cui la tabella inizia ad avvicinarsi a due miliardi di righe. Di conseguenza, è possibile scegliere di usare un integer a 4 byte per il tipo di dati di una colonna ID (chiave primaria). Accertarsi di controllare tutte le chiavi primarie della tabella e passare all'uso di colonne BIGINT (integer a 8 byte) per supportare il potenziale per un volume o una crescita elevata.

Usare indici di copertura

La sezione precedente illustra in che modo gli indici in MySQL sono organizzati come alberi B e in un indice clusterizzato mentre i nodi foglia contengono le pagine di dati della tabella soggiacente. Gli indici secondari hanno la stessa struttura ad albero B degli indici clusterizzati ed è possibile definirli in una tabella o visualizzazione con un indice clusterizzato o con un heap. Ogni riga di indice nell'indice secondario contiene il valore della chiave non clusterizzata e un indicatore di posizione delle righe. Questo indicatore punta alla riga di dati nell'indice cluster o nell'heap contenente il valore della chiave. Di conseguenza, qualsiasi ricerca che interessa un indice secondario deve passare dal nodo radice attraverso i nodi ramo fino al nodo foglia corretto per accettare il valore della chiave primaria. Il sistema esegue quindi un I/O casuale letto sull'indice della chiave primaria (ancora una volta passando dal nodo radice attraverso i nodi ramo fino al nodo foglia corretto) per ottenere la riga di dati.

Per evitare questo I/O casuale aggiuntivo letto sull'indice di chiave primaria per ottenere la riga di dati, usare un indice di copertura, che include tutti i campi richiesti dalla query. In genere, l'uso di questo approccio è utile per i carichi di lavoro associati a I/O e per quelli memorizzati nella cache. Pertanto, come procedura consigliata, usare gli indici di copertura perché si adattano alla memoria e sono più piccoli ed efficienti per la lettura rispetto all'analisi di tutte le righe.

Si consideri, ad esempio, una tabella che si sta usando per cercare di trovare tutti i dipendenti che l'azienda ha assunto dopo il 1° gennaio 2000.

mysql> show create table employee\G
****************** 1. row ******************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

Se si esegue un piano EXPLAIN su questa query, si noterà che attualmente non vengono usati indici e che viene usata una clausola where da sola per filtrare i record dei dipendenti.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
****************** 1. row ******************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

Tuttavia, se è stato aggiunto un indice che copre la colonna nella clausola where, insieme alle colonne proiettate si noterà che l'indice viene usato per individuare le colonne in modo molto più rapido ed efficiente.

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

Ora, se si esegue il piano EXPLAIN nella stessa query, il valore "Using Index" viene visualizzato nel campo "Extra", il che significa che InnoDB esegue la query usando l'indice creato in precedenza, che conferma questa operazione come indice di copertura.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
****************** 1. row ******************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

Nell'esempio precedente si è visto che la presenza di un indice di copertura per una query offre percorsi di recupero dei record più efficienti e ottimizza le prestazioni in un ambiente di database altamente simultaneo.

Passaggio successivo