Creazione e gestione degli indici sulle repliche di lettura

Questa pagina descrive come aggiungere e rimuovere indici nelle repliche di lettura Cloud SQL. Sebbene una replica sia normalmente di sola lettura, a volte potresti voler creare indici secondari sulle tabelle per la generazione di report. Cloud SQL offre un insieme di procedure memorizzate per la gestione di questi indici.

Terminologia

  • Indice clusterizzato. L'indice principale di una tabella MySQL che ordina fisicamente le righe sul disco. Quando definisci una chiave primaria su una tabella, MySQL la utilizza come indice in cluster. In una tabella può essere presente un solo indice cluster.
  • Indice secondario. Un indice aggiuntivo in una tabella MySQL che ottimizza il rendimento delle query.

Stored procedure per gli indici

Cloud SQL include due stored procedure nello schema mysql che puoi utilizzare per aggiungere e rimuovere gli indici secondari in una replica di lettura MySQL. Tieni presente che, anche se queste procedure possono 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: il tipo di indice da creare. Ad esempio, passa UNIQUE per creare un indice univoco.
  • idxName: il nome dell'indice.
  • tableName: il nome della tabella nel formato schema.name.
  • idxDefinition: definizione dell'indice. Non includere le 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
Consente di eliminare un indice secondario nel database. Questa stored procedure è un wrapper per l'istruzione DDL DROP INDEX.

Parametri:

  • idxName: il nome dell'indice.
  • tableName: il nome della tabella nel formato schema.name.
  • 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 relativa alla versione principale di MySQL in esecuzione nell'istanza Cloud SQL.

Esempi

Utilizzo corretto

Ecco alcuni esempi di invocazioni delle 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 vuoi creare un indice normale denominato t1_fname_lname sulle colonne first_name e last_name, esegui quanto segue:

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

Se vuoi anche creare un indice univoco denominato t1_license_id nella colonna license_id con il commento "ID licenza univoco", devi eseguire quanto segue:

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

Se poi vuoi eliminare l'indice t1_fname_lname, esegui il seguente comando:

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

Utilizzo non corretto

Il seguente tentativo di creare un indice sulle colonne first_name e last_name non va a buon fine a causa delle parentesi graffe 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 va a buon fine.

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 va a buon fine.

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

L'attacco di SQL injection in queste chiamate di procedura non andrà a buon fine. Ad esempio, la seguente attacco SQL con una sequenza di commenti non andrà a buon fine.

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

Analogamente, questo tentativo di attacco SQL injection con un delimitatore non va a buon fine.

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

Ricostruzione 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 sulla replica di lettura prima di un'operazione di ricreazione non vengono mantenuti. È responsabilità del cliente ricreare questi indici utilizzando le procedure memorizzate nella replica di lettura.

Passaggi successivi