PolarDB:Criar e usar GSIs

Definir um GSI na criação da tabela

O exemplo abaixo cria a tabela t_order com sharding por order_id, definindo g_i_seller como um GSI inline particionado por seller_id:

CREATE TABLE t_order (
  `id` BIGINT(11) NOT NULL AUTO_INCREMENT,
  `order_id` VARCHAR(20) DEFAULT NULL,
  `buyer_id` VARCHAR(20) DEFAULT NULL,
  `seller_id` VARCHAR(20) DEFAULT NULL,
  `order_snapshot` LONGTEXT DEFAULT NULL,
  `order_detail` LONGTEXT DEFAULT NULL,
  PRIMARY KEY (`id`),
  GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition BY hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition BY hash(`order_id`);

Adicionar um GSI a uma tabela existente

Este exemplo adiciona um GSI único chamado g_i_buyer sobre a coluna buyer_id, aplicando sharding tanto no banco de dados quanto na tabela:

CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`)
    COVERING(`seller_id`, `order_snapshot`)
    dbpartition BY hash(`buyer_id`) tbpartition BY hash(`buyer_id`) tbpartitions 3

Após a criação do GSI, o PolarDB-X valida os dados do índice antes de concluir a instrução DDL. Para verifique ou reparar manualmente esses dados, execute CHECK GLOBAL INDEX.

Seleção automática de índice

O PolarDB-X seleciona automaticamente o índice de cobertura de menor custo para consultas em tabelas com GSIs. Use EXPLAIN para confirme qual índice o otimizador escolheu:

EXPLAIN SELECT t_order.id, t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';

Resultado do plano de execução:

IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")

O otimizador escolhe g_i_seller porque todas as colunas consultadas (id, order_snapshot e seller_id) estão cobertas pelo índice. Além disso, a condição de igualdade em seller_id corresponde à chave de shard do índice, eliminando tanto a busca na tabela base quanto a varredura completa das partições.

Se a consulta exigir colunas ausentes no índice, o PolarDB-X buscará primeiro as chaves primárias e as chaves de shard da tabela base no GSI e, em seguida, recuperará as colunas faltantes na tabela base. Para mais detalhes, consulte INDEX HINT .

Forçar um índice específico

Use uma das sintaxes a seguir para substituir a seleção automática de índice:

FORCE INDEX:

FORCE INDEX({index_name})

Exemplo:

SELECT a.order_id FROM t_order a FORCE INDEX(g_i_seller) WHERE a.buyer_id = 123;

Hint TDDL:

/*+TDDL:INDEX({table_name/table_alias}, {index_name})*/

Exemplo:

/*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123

Ignorar ou sugerir um índice

IGNORE INDEX — exclui um índice específico da avaliação do otimizador:

IGNORE INDEX({index_name},...)

Exemplo:

SELECT t_order.id, t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

USE INDEX — sugere um índice preferencial ao otimizador:

USE INDEX({index_name},...)

Exemplo:

SELECT t_order.id, t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

Restrições na criação de GSIs

• Não é possível criar GSIs em tabelas não particionadas ou tabelas broadcast.

• GSIs únicos não aceitam índices de prefixo.

• Toda tabela de índice deve ter um nome explícito.

• É obrigatório especifique uma regra de sharding de banco de dados ou regras combinadas de banco de dados e tabela. Não há suporte para definir apenas a regra de sharding de tabela.

• As chaves de índice de uma tabela de índice devem conter todas as chaves de shard dessa mesma tabela.

• Uma mesma coluna não pode atuar simultaneamente como coluna de chave de índice e coluna de cobertura.

• Por padrão, a tabela de índice armazena as colunas da chave primária e todas as colunas de chave de shard da tabela base. As colunas não definidas como chaves de índice funcionam como colunas de cobertura.

• No modo DRDS: se todas as colunas de índice de um índice local da tabela base estiverem presentes na tabela de índice, esse índice local será adicionado automaticamente à tabela de índice.

• Se não existirem índices locais nas colunas de chave de índice de um GSI, o sistema criará automaticamente um índice local para cada coluna de chave de índice.

• Um GSI criado sobre múltiplas colunas gera automaticamente um índice composto que abrange todas as colunas de chave de índice.

• O parâmetro Length define exclusivamente o comprimento do prefixo de uma chave de sharding para a criação de um índice local.

Limitações do ALTER TABLE

A matriz a seguir indica quais operações de coluna via ALTER TABLE têm suporte em tabelas com GSIs.

Suportado\*: Aplicável somente a instâncias que permitem alteração de tipo de coluna sem bloqueio (lock-free).

Observações adicionais sobre o comando ALTER TABLE:

• Para renomear um GSI, remova-o com DROP INDEX e crie-o novamente. Renomear um GSI diretamente pode degradar seu desempenho. Para obter assistência, entre em contato conosco.

• Se uma coluna se enquadrar em múltiplos tipos de coluna na matriz acima e a operação não tiver suporte para qualquer um desses tipos, a execução será bloqueada.

A tabela abaixo lista as operações de gerenciamento de índices via ALTER TABLE:

Restrições do ALTER GSI TABLE

• Não é possível execute instruções DDL e DML diretamente em um GSI.

• Instruções DML com NODE HINTs não atualize a tabela base e os GSIs simultaneamente.

Outras instruções suportadas

As instruções listadas abaixo funcionam em tabelas que possuem GSIs:

Por que recebo o erro "Does not support create Global Secondary Index on single or broadcast table"?

Esse erro indica que a tabela alvo é não particionada ou do tipo broadcast, as quais não aceitam GSIs.

Verifique como a tabela foi criada:

• Tabela não particionada (modo DRDS): Criada em um único banco de dados, sem sharding. Consulte CREATE TABLE (modo DRDS).

• Tabela não particionada (modo AUTO): Criada com a palavra-chave SINGLE. Consulte CREATE TABLE (modo AUTO).

• Tabela broadcast: Criada com a palavra-chave BROADCAST; uma cópia idêntica da tabela existe em cada nó de dados. Consulte CREATE TABLE (modo DRDS) ou CREATE TABLE (modo AUTO).

GSIs só podem ser criados em tabelas particionadas que não sejam do tipo broadcast.