Creazione e gestione degli indici sulle repliche di lettura

Questa pagina descrive come aggiungere ed eliminare gli indici sulle repliche di lettura di Cloud SQL. Sebbene una replica sia normalmente di sola lettura, a volte potresti voler creare indici secondari sulle tabelle per scopi di reporting. Cloud SQL offre un insieme di stored procedure per la gestione di questi indici.

Terminologia

  • Indice cluster. L'indice principale di una tabella MySQL che ordina fisicamente le righe sul disco. Quando definisci una chiave primaria in una tabella, MySQL la utilizza come indice cluster. Può esistere un solo indice cluster in una tabella.
  • Indice secondario. Un indice aggiuntivo in una tabella MySQL che ottimizza le prestazioni delle query.

Stored procedure per gli indici

Cloud SQL include due stored procedure nello schema mysql che puoi utilizzare per aggiungere ed eliminare indici secondari in una replica di lettura MySQL. Tieni presente che, sebbene queste procedure possano essere eseguite su un'istanza di origine principale, sono progettate per le repliche di lettura.

mysql.addSecondaryIdxOnReplica
Aggiunge un indice secondario al database. Questa stored procedure è un wrapper per l' istruzione DDL CREATE INDEX.

Parametri:

  • idxType - Tipo di indice da creare. Ad esempio, passa UNIQUE per creare un indice univoco.
  • idxName - Nome dell'indice.
  • tableName - Nome della tabella nel formato schema.nome.
  • idxDefinition - Definizione dell'indice. Non includere parentesi esterne.
  • idxOption - Eventuali opzioni aggiuntive da passare alla creazione dell'indice. Ad esempio, in MySQL 8.0, un'opzione potrebbe passare INVISIBLE per un indice invisibile.

Sintassi:

mysql.addSecondaryIdxOnReplica(idxType, idxName, tableName, idxDefinition, idxOption)
mysql.dropSecondaryIdxOnReplica
Elimina un indice secondario nel database. Questa stored procedure è un wrapper per l' istruzione DDL DROP INDEX.

Parametri:

  • idxName - Nome dell'indice.
  • tableName - Nome della tabella nel formato schema.nome.
  • idxOption - Eventuali opzioni aggiuntive da passare quando si elimina un indice. Ad esempio, un'opzione di algoritmo come INPLACE.

Sintassi:

mysql.dropSecondaryIdxOnReplica(idxName, tableName, idxOption)

Per i parametri idxType e idxOption, consulta la documentazione della versione principale di MySQL in esecuzione sull'istanza Cloud SQL.

Esempi

Utilizzo corretto

Ecco alcuni esempi di chiamate alle procedure. Supponiamo di avere una tabella con la seguente definizione.

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

Se volessi creare un indice normale denominato t1_fname_lname nelle colonne first_name e last_name, dovresti eseguire quanto segue:

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

Se volessi anche creare un indice univoco denominato t1_license_id nella license_id colonna con il commento "unique license id", dovresti eseguire quanto segue:

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

Se poi volessi eliminare l'indice t1_fname_lname, dovresti eseguire quanto segue:

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

Utilizzo errato

Il seguente tentativo di creare un indice nelle colonne first_name e last_name non riesce a causa delle parentesi esterne nel parametro idxDefinition.

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

Puoi aggiungere indici solo alle tabelle create dal cliente. Il seguente tentativo di creare un indice nella colonna host della tabella mysql.servers non riesce.

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

Puoi utilizzare la procedura dropSecondaryIdxOnReplica solo per eliminare gli indici creati in precedenza utilizzando la procedura addSecondaryIdxOnReplica. Ad esempio, la seguente chiamata per eliminare l'indice idx_fname esistente non riesce.

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

L'SQL injection in queste chiamate di procedura non riuscirà. Ad esempio, la seguente SQL injection con una sequenza di commenti non riuscirà.

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

Allo stesso modo, questo tentativo di SQL injection con un delimitatore non riesce.

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

Ricreazione delle repliche di lettura

A volte, in caso di problemi, Cloud SQL ricrea una replica di lettura dall'origine principale per recuperare rapidamente l'istanza. Gli indici creati nella replica di lettura prima di un'operazione di ricreazione non vengono mantenuti. È responsabilità del cliente ricreare questi indici utilizzando le stored procedure nella replica di lettura.

Passaggi successivi