Créer et gérer des index sur des instances dupliquées avec accès en lecture

Cette page explique comment ajouter et supprimer des index sur des instances dupliquées avec accès en lecture Cloud SQL. Bien qu'une instance dupliquée soit normalement en lecture seule, il peut arriver que vous souhaitiez créer des index secondaires sur des tables à des fins de création de rapports. Cloud SQL propose un ensemble de procédures stockées pour la gestion de ces index.

Terminologie

  • Index en cluster : Index principal d'une table MySQL qui trie physiquement les lignes sur le disque. Lorsque vous définissez une clé primaire sur une table, MySQL l'utilise comme index en cluster. Il ne peut y avoir qu'un seul index en cluster sur une table.
  • Index secondaire. Index supplémentaire sur une table MySQL qui optimise les performances des requêtes.

Procédures stockées pour les index

Cloud SQL inclut deux procédures stockées dans le schéma mysql que vous pouvez utiliser pour ajouter et supprimer des index secondaires sur une instance dupliquée avec accès en lecture MySQL. Notez que même si ces procédures peuvent être exécutées sur une instance source principale, elles sont conçues pour les instances dupliquées avec accès en lecture.

mysql.addSecondaryIdxOnReplica
Ajoute un index secondaire sur la base de données. Cette procédure stockée est un wrapper pour l'instruction LDD CREATE INDEX.

Paramètres :

  • idxType : type d'index à créer. Par exemple, transmettez UNIQUE pour créer un index unique.
  • idxName : nom de l'index.
  • tableName : nom de la table au format schema.name.
  • idxDefinition : définition de l'index. N'incluez pas de parenthèses externes.
  • idxOption : options supplémentaires à transmettre lors de la création de l'index. Par exemple, dans MySQL 8.0, une option peut transmettre INVISIBLE pour un index invisible.

Syntaxe :

mysql.addSecondaryIdxOnReplica(idxType, idxName, tableName, idxDefinition, idxOption)
mysql.dropSecondaryIdxOnReplica
Supprime un index secondaire sur la base de données. Cette procédure stockée est un wrapper pour l'instruction LDD DROP INDEX.

Paramètres :

  • idxName : nom de l'index.
  • tableName : nom de la table au format schema.name.
  • idxOption - Toutes les options supplémentaires à transmettre lors de la suppression d'un index. Par exemple, une option d'algorithme telle que INPLACE.

Syntaxe :

mysql.dropSecondaryIdxOnReplica(idxName, tableName, idxOption)

Pour les paramètres idxType et idxOption, consultez la documentation de la version majeure de MySQL exécutée sur l'instance Cloud SQL.

Exemples

Utilisation correcte

Voici quelques exemples d'appels des procédures. Supposons que nous ayons une table avec la définition suivante.

CREATE TABLE sampletest.t1(
   id int(10) unsigned NOT NULL AUTO_INCREMENT,
   first_name varchar(64) NOT NULL,
   last_name varchar(64) NOT NULL,
   license_id int NOT NULL,
   PRIMARY KEY (id),
   KEY idx_fname (first_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Si vous souhaitez créer un index standard nommé t1_fname_lname sur les colonnes first_name et last_name, exécutez la commande suivante:

call mysql.addSecondaryIdxOnReplica('', 't1_fname_lname', 'sampletest.t1', 'first_name, last_name', '')

Si vous souhaitez également créer un index unique nommé t1_license_id sur la colonne license_id avec le commentaire "identifiant de licence unique", exécutez la commande suivante:

call mysql.addSecondaryIdxOnReplica('unique', 't1_license_id', 'sampletest.t1', 'license_id', 'comment \"unique license id\"')

Si vous souhaitez ensuite supprimer l'index t1_fname_lname, exécutez la commande suivante:

call mysql.dropSecondaryIdxOnReplica('t1_fname_lname', 'sampletest.t1', '')

Utilisation incorrecte

La tentative suivante de création d'un index sur les colonnes first_name et last_name échoue en raison des parenthèses externes du paramètre idxDefinition.

call mysql.addSecondaryIdxOnReplica('', 't1_extra_parenthesis', 'sampletest.t1', '(first_name, last_name)', '')

Vous ne pouvez ajouter des index que sur les tables créées par le client. La tentative suivante de création d'un index sur la colonne hôte de la table mysql.servers échoue.

call mysql.addSecondaryIdxOnReplica('', 'idx_invalid', 'mysql.servers', 'host', '')

Vous ne pouvez utiliser la procédure dropSecondaryIdxOnReplica que pour supprimer des index précédemment créés à l'aide de la procédure addSecondaryIdxOnReplica. Par exemple, l'appel suivant pour supprimer l'index idx_fname existant échoue.

call mysql.dropSecondaryIdxOnReplica('idx_fname', 'sampletest.t1', '')

L'injection SQL dans ces appels de procédure échouera. Par exemple, l'injection SQL suivante avec une séquence de commentaires échouera.

call mysql.addSecondaryIdxOnReplica(\"user 'a'@'%' --\", 'idx_fname', 'sampletest.t1', 'first_name', '')

De même, cette tentative d'injection SQL avec un délimiteur échoue.

call mysql.addSecondaryIdxOnReplica('', 'idx_fname', 'sampletest.t1', 'first_name', ';flush status')

Recréation d'instances dupliquées avec accès en lecture

Parfois, en cas de problème, Cloud SQL recrée une instance dupliquée avec accès en lecture à partir de la source principale afin de récupérer rapidement l'instance. Les index créés sur l'instance dupliquée avec accès en lecture avant une opération de recréation ne sont pas conservés. Il incombe au client de recréer ces index à l'aide des procédures stockées sur l'instance dupliquée avec accès en lecture.

Étape suivante