Procedure consigliate per la risoluzione dei problemi Database di Azure per MySQL - Server flessibile

SI APPLICA A: Database di Azure per MySQL - Server singolo Database di Azure per MySQL - Server flessibile

Importante

Database di Azure per MySQL server singolo si trova nel percorso di ritiro. È consigliabile eseguire l'aggiornamento a Database di Azure per MySQL server flessibile. Per altre informazioni sulla migrazione a Database di Azure per MySQL server flessibile, vedere Che cosa accade a Database di Azure per MySQL server singolo?

Usare le sezioni seguenti per mantenere i database server flessibili 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 offrire prestazioni ottimali per le applicazioni.

Controllare 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 scarsi. Gli indici inutilizzati possono avere un impatto negativo sulle prestazioni quando utilizzano spazio su disco e cache e rallentano le operazioni di scrittura (IN edizione Standard RT/DELETE/UPDATE). Gli indici inutilizzati consumano inutilmente più spazio di archiviazione e aumentano le dimensioni del backup.

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

Nota

Ricordarsi di esaminare periodicamente gli indici ed eseguire gli aggiornamenti necessari in base alle modifiche apportate ai dati della tabella.

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 di 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 sottostante e alle query dipendenti.

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

Database di Azure per MySQL server flessibile usa il motore di archiviazione InnoDB per tutte le tabelle non standard. Con InnoDB, i dati vengono archiviati all'interno di un indice cluster usando una struttura albero B. La tabella è organizzata fisicamente in base ai valori di 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 punta 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 da una o più colonne. Ciò a sua volta può comportare un sovraccarico di archiviazione grave con il valore della chiave primaria copiato in ogni voce di 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, essere univoca, 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 con due miliardi di righe. Di conseguenza, è possibile scegliere di usare un intero con segno a 4 byte per il tipo di dati di una colonna ID (chiave primaria). Assicurarsi 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.

Nota

Per altre informazioni sui tipi di dati e sui relativi valori massimi, vedere Manuale di riferimento di MySQL.

Usare indici di copertura

La sezione precedente illustra in che modo gli indici in MySQL sono organizzati come alberi B e in un indice cluster, i nodi foglia contengono le pagine di dati della tabella sottostante. Gli indici secondari hanno la stessa struttura ad albero B degli indici cluster ed è possibile definirli in una tabella o vista con un indice cluster o un heap. Ogni riga di indice nell'indice secondario contiene il valore della chiave non cluster e un localizzatore di 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 del ramo 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 del ramo 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 i carichi di lavoro 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 si sono aggiunti all'azienda 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)

Nota

È importante scegliere l'ordine corretto delle colonne nell'indice di copertura per gestire correttamente la query. La regola generale consiste nel scegliere le colonne per filtrare prima (clausola WHERE), quindi ordinare/raggruppare (ORDER BY e GROUP BY) e infine la proiezione dei dati (edizione Standard LECT).

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.

Passaggi successivi

Per trovare risposte peer alle domande più importanti o per pubblicare o rispondere a domande, visitare Stack Overflow.