Meilleures pratiques relatives à la résolution des problèmes d’Azure Database pour MySQL - Serveur flexible

Utilisez les sections suivantes pour assurer le bon fonctionnement de vos bases de données Azure Database pour MySQL - Serveur flexible. Utilisez ces informations en tant que principes directeurs afin de garantir une conception optimale des schémas ainsi que les meilleures performances possibles pour vos applications.

Vérifier le nombre d’index

Dans un environnement de base de données occupé, vous pouvez observer une utilisation élevée des E/S, ce qui peut être un indicateur de modèles d’accès aux données médiocres. Les index inutilisés peuvent avoir un impact négatif sur les performances, car ils consomment de l’espace disque et du cache, et ralentissent les opérations d’écriture (INSERT/ DELETE /UPDATE). Les index inutilisés consomment inutilement de l’espace de stockage supplémentaire et augmentent la taille de sauvegarde.

Avant de supprimer un index, veillez à collecter suffisamment d’informations pour vérifier qu’il n’est plus utilisé. Cette vérification peut vous aider à éviter de supprimer par inadvertance un index qui est essentiel pour une requête qui s’exécute uniquement tous les trimestres ou chaque année. Veillez également à déterminer si un index est utilisé pour appliquer l’unicité ou l’ordre.

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;

(ou)

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));

Répertorier les index les plus utilisés sur le serveur

La sortie de la requête suivante fournit des informations sur les index les plus utilisés sur toutes les tables et schémas sur le serveur de base de données. Ces informations sont utiles pour identifier le rapport entre les écritures et les lectures pour chaque index et les chiffres de latence pour les lectures ainsi que les opérations d’écriture individuelles, ce qui peut indiquer qu’un réglage supplémentaire est nécessaire pour la table sous-jacente et les requêtes dépendantes.

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

Passer en revue la conception de clé primaire

Azure Database pour MySQL - Serveur flexible utilise le moteur de stockage InnoDB pour toutes les tables non temporaires. Avec InnoDB, les données sont stockées dans un index cluster à l’aide d’une structure B-Tree. La table est organisée physiquement en fonction des valeurs de clé primaire, ce qui signifie que les lignes sont stockées dans l’ordre de clé primaire.

Chaque entrée de clé secondaire dans une table InnoDB contient un pointeur vers la valeur de clé primaire dans laquelle les données sont stockées. En d’autres termes, une entrée d’index secondaire contient une copie de la valeur de clé primaire vers laquelle l’entrée pointe. Par conséquent, les choix de clé primaire ont un effet direct sur la quantité de surcharge de stockage dans vos tables.

Si une clé est dérivée des données réelles (par exemple, nom d’utilisateur, e-mail, SSN, etc.), elle est appelée clé naturelle. Si une clé est artificielle et non dérivée de données (par exemple, un entier incrémenté automatiquement), elle est appelée clé synthétique ou clé de substitution.

Il est généralement recommandé d’éviter d’utiliser des clés primaires naturelles. Ces clés sont souvent très larges et contiennent des valeurs longues d’une ou plusieurs colonnes. Cela peut à son tour introduire une surcharge de stockage grave avec la valeur de clé primaire copiée dans chaque entrée de clé secondaire. En outre, les clés naturelles ne suivent généralement pas un ordre prédéfinit, ce qui réduit considérablement les performances et provoque la fragmentation des pages lorsque les lignes sont insérées ou mises à jour. Pour éviter ces problèmes, utilisez des clés de substitution monotoniquement croissantes plutôt que des clés naturelles. Une colonne d’incrémentation automatique (big)integer est un bon exemple d’une clé de substitution monotoniquement croissante. Si vous avez besoin d’une certaine combinaison de colonnes, soyez unique, déclarez ces colonnes comme une clé secondaire unique.

Pendant les phases initiales de création d’une application, vous ne pensez peut-être pas à l’idée d’imaginer un moment où votre table commence à s’approcher de deux milliards de lignes. Par conséquent, vous pouvez choisir d’utiliser un entier de 4 octets signé pour le type de données d’une colonne ID (clé primaire). Veillez à vérifier toutes les clés primaires de table et à basculer pour utiliser 8 colonnes d’entiers de 8 octets (BIGINT) pour prendre en compte le potentiel d’un volume élevé ou d’une croissance.

Utiliser les index de couverture

La section précédente explique comment les index dans MySQL sont organisés en tant qu’arborescences B et dans un index cluster, les nœuds feuille contiennent les pages de données de la table sous-jacente. Les index secondaires ont la même structure d’arborescence B que les index cluster, et vous pouvez les définir sur une table ou une vue avec un index cluster ou un segment de mémoire. Chaque ligne d’index dans l’index secondaire contient la valeur de la clé non groupée et un localisateur de ligne. Le localisateur pointe vers la ligne de données dans l'index cluster ou dans le segment doté de la valeur clé. Par conséquent, toute recherche impliquant un index secondaire doit naviguer à partir du nœud racine par le biais des nœuds de branche vers le nœud feuille approprié pour prendre la valeur de la clé primaire. Le système exécute ensuite une lecture aléatoire d’E/S sur l’index de clé primaire (une fois de plus, en accédant du nœud racine par le biais des nœuds de branche vers le nœud feuille approprié) pour obtenir la ligne de données.

Pour éviter cette lecture d’E/S aléatoire supplémentaire sur l’index de clé primaire pour obtenir la ligne de données, utilisez un index de couverture, qui inclut tous les champs requis par la requête. En règle générale, l’utilisation de cette approche est bénéfique pour les charges de travail liées aux E/S et les charges de travail mises en cache. Par conséquent, il est recommandé d’utiliser la couverture des index, car ils s’intègrent en mémoire et sont plus petits et plus efficaces à lire que d’analyser toutes les lignes.

Prenons l’exemple d’un tableau que vous utilisez pour essayer de trouver tous les employés qui ont rejoint l’entreprise après le 1er janvier 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';

Si vous exécutez un plan EXPLAIN sur cette requête, vous remarquerez qu’aucun index n’est actuellement utilisé et qu’une clause where seule est utilisée pour filtrer les enregistrements des employés.

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)

Toutefois, si vous ajoutez un index qui couvre la colonne dans la clause where, ainsi que les colonnes projetées, vous verrez que l’index est utilisé pour localiser les colonnes beaucoup plus rapidement et efficacement.

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

À présent, si vous exécutez le plan EXPLAIN sur la même requête, la valeur « Utilisation de l’index » apparaît dans le champ « Extra », ce qui signifie que InnoDB exécute la requête à l’aide de l’index que nous avons créé précédemment, ce qui confirme cela comme index couvrant.

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)

Dans l’exemple précédent, nous avons constaté que la présence d’un index couvrant pour une requête fournit des chemins d’extraction d’enregistrements plus efficaces et optimise les performances dans un environnement de base de données hautement simultané.

Étape suivante