Create an index - Lindorm: Central de Documentação do Alibaba Cloud

O comando CREATE INDEX cria um índice em uma ou mais colunas de uma wide table do LindormTable. Use este recurso para ativar consultas fora da chave primária, busca textual e consultas analíticas sem varredura completa da tabela.

Aplicabilidade

O comando CREATE INDEX aplica-se exclusivamente ao LindormTable. Não há versão mínima exigida para índices secundários.

Importante

Para criar um índice de busca ou um índice columnstore com CREATE INDEX, use o Lindorm SQL V2.6.1 ou superior. Para verificar sua versão do Lindorm SQL, consulte Guia de versões do SQL.

Antes de começar

A construção de índices para índices secundários, índices columnstore e índices de busca exige consultas aos dados, gerando operações de leitura. Se você ativou a separação de dados quentes e frios na instância, monitore o throttling no armazenamento frio. Leituras limitadas nesse armazenamento tornam a criação do índice mais lenta e podem causar backpressure nas operações de escrita.

Sintaxe

CREATE INDEX [IF NOT EXISTS] [index_identifier]
  [USING {KV | SEARCH | COLUMNAR}]
  ON table_identifier '(' index_key_expression ')'
  [INCLUDE '(' column_identifier (, column_identifier)* ')']
  [PARTITION BY partition_definition]
  [{ASYNC | SYNC}]
  [WITH '(' index_options ')']

Subproduções:

index_key_expression  ::= index_key_definition (, index_key_definition)*
                        | wildcard_string_literal

index_key_definition  ::= column_identifier [DESC]
                        | column_identifier '(' option_definition (, option_definition)* ')'
                        | function_expression

function_expression   ::= Z-ORDER '(' column_identifier (, column_identifier)* ')'
                        | S2 '(' column_identifier, level ')'
                        | CAST '(' column_identifier AS type ')'
                        | MD5 '(' column_identifier ')'
                        | SHA256 '(' column_identifier ')'

partition_definition  ::= {RANGE TIME} '(' column_identifier ')' [PARTITIONS number_literal]
                        | HASH '(' column_identifier (, column_identifier)* ')' [PARTITIONS number_literal]
                        | ENUMERABLE '(' column_identifier (, column_identifier)* ')'

index_options         ::= option_definition (, option_definition)*
option_definition     ::= option_identifier = string_literal

Diferenças

Os elementos de sintaxe suportados variam conforme o tipo de índice.

Elemento de sintaxe	Índice secundário	Índice de busca
Tipo de índice (index_method_definition)	✔	0
Expressão da chave de índice (index_key_expression)	✔	0
Colunas incluídas (include_expression)	0	✖
Particionamento do índice (partition_definition)	✖	0
Modo de construção do índice (ASYNC\|SYNC) Importante Somente o LindormTable 2.6.3 e versões posteriores suportam o modo de construção `SYNC`.	0	0
Propriedades do índice (index_options)	0	0

Elemento de sintaxe	Índice secundário	Índice de busca
Palavra-chave `USING`	Opcional (KV é o padrão)	Opcional
Expressão da chave de índice	Obrigatório	Obrigatório
`INCLUDE`	Opcional	Não suportado
`PARTITION BY`	Não suportado	Opcional
`ASYNC` / `SYNC`	Opcional	Opcional
Propriedades do índice com `WITH`	Opcional	Opcional

Observações de uso

É possível criar no máximo 3 índices secundários e 1 índice de busca por wide table.

Tipo de índice (`USING`)

Use a palavra-chave USING para especificar o tipo de índice. Se omitida, um índice secundário (KV) será criado por padrão.

Valor de `USING`	Tipo de índice	Cenário recomendado
`KV` (ou omitir)	Índice secundário	Consultas de igualdade e intervalo fora da chave primária. Uma instância suporta no máximo 8 tarefas simultâneas de construção de índice secundário; uma nona solicitação falha imediatamente
`SEARCH`	Índice de busca	Busca textual, consultas fuzzy, filtragem multidimensional, agregação e ordenação com paginação. Sem limite de tarefas simultâneas de construção. Requer ativação prévia do recurso de índice de busca — nós de busca e nós do Lindorm Tunnel Service (LTS) geram custos. Consulte Ativar índices de busca. A chave do índice deve incluir pelo menos uma coluna que não seja chave primária. Tipos de dados suportados: todos os tipos básicos, exceto DATE, TIME e DECIMAL. Consulte Tipos de dados básicos

Expressão da chave de índice (`index_key_expression`)

Defina uma ou mais colunas como chaves de índice. Um índice com múltiplas chaves é considerado um índice composto.

Definição da chave de índice (`index_key_definition`)

Para índices secundários, cada definição de chave pode ser:

Nome de uma coluna, opcionalmente com DESC para ordem decrescente
Uma expressão de função (Z-ORDER, S2, CAST, MD5 ou SHA256)

Para índices de busca, cada definição de chave pode ser:

Nome de uma coluna com propriedades opcionais da chave de índice
A constante curinga * para indexar todas as colunas existentes

Propriedades da chave de índice de busca (`option_definition`)

Especifique propriedades por coluna usando a sintaxe column(property=value, ...). Exemplo: c3(type=text, analyzer=ik). Também é possível definir essas propriedades ao adicionar uma coluna de índice com o comando ALTER INDEX.

Propriedade	Tipo	Descrição
`indexed`	STRING	Define se um índice será criado para esta coluna. Um índice invertido é construído para colunas de texto; já para colunas numéricas, utiliza-se um índice BKD-Tree. Valores válidos: `true` (padrão) ou `false`
`rowStored`	STRING	Determina se o valor original da coluna deve ser armazenado. Valores válidos: `true` ou `false` (padrão)
`columnStored`	STRING	Indica se o armazenamento colunar deve ser usado para acelerar ordenação e análise. Valores válidos: `true` (padrão) ou `false`
`type`	STRING	Defina como `text` para campos de texto completo tokenizados. Uso obrigatório com `analyzer`
`analyzer`	STRING	Tokenizador para colunas com `type=text`. Valores válidos: `standard`, `english`, `ik`, `whitespace`, `comma`. Uso obrigatório com `type`
`mapping`	STRING	Propriedades personalizadas da chave de índice em formato JSON, compatível com a sintaxe de mapping do Elasticsearch. Aplica-se apenas a versões compatíveis com Elasticsearch. Sobrescreve todas as outras propriedades desta chave

Expressões de função para índice secundário (`function_expression`)

Ao criar um índice secundário, especifique uma expressão de função como chave do índice.

Importante

As expressões de função MD5 e SHA256 exigem o LindormTable 2.6.7.5 ou superior. Caso não possa atualizar, entre em contato com o suporte técnico do Lindorm no DingTalk através do ID s0s3eg3.

Função	Sintaxe	Cenário recomendado
Z-ORDER	`Z-ORDER(col1, col2, ...)`	Índice secundário espaço-temporal; as colunas devem ser de um tipo de dado espaço-temporal. Consulte Índices espaço-temporais
S2	`S2(col, level)`	Índice de grade S2 para colunas POLYGON ou MULTIPOLYGON; `level` varia de 1 a 30. Consulte Função de índice S2
CAST	`CAST(col AS type)`	Índice sobre o resultado de uma conversão de tipo de dado. Consulte Tipos de dados básicos
MD5	`MD5(col)`	Índice sobre o valor codificado em MD5 de uma coluna VARCHAR. Requer LindormTable 2.6.7.5+. Consulte Função MD5
SHA256	`SHA256(col)`	Índice sobre o valor codificado em SHA256 de uma coluna VARCHAR. Requer LindormTable 2.6.7.5+. Consulte Função SHA256

Constante curinga (`wildcard_string_literal`)

Apenas índices de busca suportam a constante curinga *. Use * para criar um índice em todas as colunas existentes no momento da execução do comando.

CREATE INDEX IF NOT EXISTS idx5 USING SEARCH ON test(*);

Importante

Colunas adicionadas após a execução do comando não são indexadas automaticamente. Adicione-as manualmente usando ALTER INDEX.
Colunas dinâmicas não são incluídas. Consulte Colunas dinâmicas.

Colunas incluídas (`INCLUDE`)

A cláusula INCLUDE adiciona colunas que não são chave da tabela principal à tabela de índice, formando um índice de cobertura. Consultas que utilizam esse índice recuperam os valores das colunas incluídas sem consultar a tabela principal, melhorando o desempenho de leitura.

Para índices secundários, use a palavra-chave WITH para incluir colunas dinâmicas definindo INDEX_COVERED_TYPE . Consulte Índices secundários .

Particionamento do índice (`PARTITION BY`)

Somente índices de busca suportam particionamento. O servidor divide e armazena os dados automaticamente. Durante a consulta, o sistema aplica poda de partições para ignorar aquelas irrelevantes.

Tipos de partição suportados: RANGE e HASH. Consulte Índices particionados.

Modo de construção do índice (`ASYNC` | `SYNC`)

Especifique ASYNC ou SYNC para controlar quando o comando retorna.

Modo	Comportamento	Bloqueia o comando?	Índice utilizável imediatamente após o retorno?	Cenário recomendado
`ASYNC` (padrão a partir do LindormTable 2.6.1)	Retorna assim que a tarefa de construção inicia	Não	Não — a construção continua em segundo plano	Produção; as escritas continuam inalteradas enquanto o índice é construído
`SYNC`	Retorna apenas após a conclusão da tarefa de construção	Sim	Sim	Scripts de migração de schema; testes; casos em que o índice precisa estar pronto antes da próxima etapa

Importante

O modo SYNC requer LindormTable 2.6.3 ou superior.

A tabela abaixo mostra quais modos cada tipo de índice suporta.doisíndices secundários e índices de buscadois

Modo de construção	Índice secundário	Índice de busca
`ASYNC` (padrão a partir do LindormTable 2.6.1)	Suportado	Suportado
`SYNC` (requer LindormTable 2.6.3+)	Suportado	Suportado

Modo de construção do índice	Índice secundário	Índice de busca
ASYNC Importante A partir do LindormTable 2.6.1, o modo padrão de construção de índice para o comando `CREATE INDEX` é assíncrono.	0	0
SYNC Importante Somente o LindormTable 2.6.3 e versões posteriores suportam construção síncrona de índices.	0	✔

Propriedades do índice (`WITH`)

Propriedades de índice secundário

Propriedade	Tipo	Descrição
`COMPRESSION`	STRING	Algoritmo de compressão para a tabela de índice. Valores válidos: `SNAPPY`, `ZSTD`, `LZ4`
`INDEX_COVERED_TYPE`	STRING	Método de redundância para colunas incluídas. `COVERED_ALL_COLUMNS_IN_SCHEMA`: inclui todas as colunas predefinidas que não são chave primária. `COVERED_DYNAMIC_COLUMNS`: inclui todas as colunas predefinidas que não são chave primária e também as colunas dinâmicas. Quando definido, omita a cláusula `INCLUDE`. Antes de incluir colunas dinâmicas, ative o recurso correspondente. Consulte Colunas dinâmicas
`STARTKEY`	STRING	Chave inicial para a tabela de índice. Não pode ser definida para colunas de tipo timestamp ou dados espaciais
`ENDKEY`	STRING	Chave final para a tabela de índice. Não pode ser definida para colunas de tipo timestamp ou dados espaciais
`NUMREGIONS`	INTEGER	Número de pré-partições para a tabela de índice. Não pode ser definido para colunas de tipo timestamp ou dados espaciais

Propriedades de índice de busca

Propriedade	Tipo	Descrição
`indexState`	STRING	Estado inicial do índice. Valores válidos: `ACTIVE` (disponível), `INACTIVE` (indisponível), `DISABLED` (desativado)
`numShards`	INTEGER	Número de shards. Padrão: o dobro do número de nós de busca. Mantenha cada shard entre 30 milhões e 100 milhões de linhas e entre 30 e 50 GB. Um shard com mais de 2 bilhões de linhas afeta a estabilidade do sistema. Planeje a quantidade de shards antes de criar índices em produção. Para dados de séries temporais com crescimento significativo de volume (como pedidos ou logs), prefira um índice particionado por tempo
`RANGE_TIME_PARTITION_START`	INTEGER	Dias anteriores à criação do índice para iniciar a criação de partições, destinado a dados históricos. Se os dados históricos tiverem um timestamp anterior ao início da partição, ocorrerá um erro. Obrigatório ao criar um índice particionado por tempo
`RANGE_TIME_PARTITION_INTERVAL`	INTEGER	Intervalo em dias entre novas partições. Por exemplo, `7` cria uma nova partição toda semana. Obrigatório ao criar um índice particionado por tempo
`RANGE_TIME_PARTITION_TTL`	INTEGER	Período de retenção de dados em dias. Por exemplo, `180` retém seis meses de dados e limpa automaticamente partições mais antigas. Se não definido, os dados nunca são limpos. Obrigatório ao criar um índice particionado por tempo
`RANGE_TIME_PARTITION_MAX_OVERLAP`	INTEGER	Deslocamento máximo permitido de timestamp futuro em dias. Padrão: 1 dia
`RANGE_TIME_PARTITION_FIELD_TIMEUNIT`	LONG	Unidade do campo de partição de tempo. Padrão: milissegundos (ms). Se a unidade for segundos (s), o valor do campo deve ter 10 dígitos. Se for milissegundos (ms), deve ter 13 dígitos
`RANGE_TIME_PARTITION_CHS`	INTEGER	Limite entre dados quentes e frios em segundos. Por exemplo, `864000` arquiva dados com mais de 10 dias para o armazenamento frio. Se não definido, a separação de dados quentes e frios não é ativada para este índice
`INDEX_SETTINGS`	STRING	Propriedades personalizadas do índice em formato JSON, compatível com a sintaxe de configurações de índice do Elasticsearch. Aplica-se apenas a versões compatíveis com Elasticsearch
`SOURCE_SETTINGS`	STRING	Política de armazenamento de dados brutos para colunas do índice, em formato JSON compatível com as configurações `_source` do Elasticsearch. Por padrão, índices de busca não armazenam dados brutos das colunas. Configure este parâmetro apenas quando precisar de consultas diretas aos dados através da interface de visualização do mecanismo de busca. Consulte consultas de dados. Parâmetros suportados: `enabled` (Booleano; `true` armazena todas as colunas, `false` não armazena nenhuma; não pode ser usado com `includes` ou `excludes`), `includes` (array de strings; colunas cujos dados brutos devem ser armazenados; suporta o curinga ``), `excludes` (array de strings; colunas cujos dados brutos devem ser excluídos; suporta o curinga ``)

Exemplos

Todos os exemplos utilizam a seguinte tabela principal:

CREATE TABLE test (
  p1 VARCHAR NOT NULL,
  p2 INTEGER NOT NULL,
  c1 BIGINT,
  c2 DOUBLE,
  c3 VARCHAR,
  c4 TIMESTAMP,
  c5 GEOMETRY(POINT),
  PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');

Índices secundários

Construir um índice assincronamente

O modo padrão é ASYNC. O comando retorna imediatamente.

CREATE INDEX idx1 ON test(c1 DESC) INCLUDE(c3, c4) WITH (COMPRESSION='ZSTD');

Verifique o resultado:

SHOW INDEX FROM test;

Criar um índice composto sincronamente

CREATE INDEX idx1 ON test(c1, c2, c3) INCLUDE(c4) SYNC WITH (COMPRESSION='ZSTD');

Verifique o resultado:

SHOW INDEX FROM test;

Criar um índice secundário espaço-temporal

CREATE INDEX idx ON roads (Z-ORDER(g1));
CREATE INDEX idt ON roads (Z-ORDER(g1, t));

Consulte Índices espaço-temporais.

Incluir todas as colunas predefinidas

CREATE INDEX idx1 ON test(c4 DESC) WITH (INDEX_COVERED_TYPE='COVERED_ALL_COLUMNS_IN_SCHEMA');

Verifique o resultado:

SHOW INDEX FROM test;

Incluir todas as colunas dinâmicas

CREATE INDEX idx1 ON test(c4 DESC) WITH (INDEX_COVERED_TYPE='COVERED_DYNAMIC_COLUMNS');

Verifique o resultado:

SHOW INDEX FROM test;

Definir pré-partições para a tabela de índice

Crie um índice com 32 pré-partições.

CREATE INDEX idx1 ON test(c4 DESC) INCLUDE(c5, c6) WITH (NUMREGIONS='32');

Verifique o resultado:

SHOW INDEX FROM test;

Definir chaves inicial e final com pré-partições

Crie uma tabela de índice com 32 pré-partições entre 11111111 e 9999999.

CREATE INDEX idx1 ON test(c3 DESC) INCLUDE(c5, c6)
WITH (NUMREGIONS='32', STARTKEY='11111111', ENDKEY='9999999');

Verifique o resultado:

SHOW INDEX FROM test;

Criar um índice secundário Z-ORDER

CREATE INDEX idx1 ON test(Z-ORDER(c5));

Verifique o resultado:

SHOW INDEX FROM test;

Criar um índice secundário de grade S2

Índices S2 suportam apenas construção assíncrona. Execute BUILD INDEX para acionar a construção após criar o índice.

CREATE INDEX idx1 ON test(S2(c5, 10));
BUILD INDEX s2_idx ON test;

Verifique o resultado:

SHOW INDEX FROM test;

Indexar uma coluna após conversão de tipo de dado

Converta a coluna c3 para INTEGER e, em seguida, crie um índice secundário sobre o resultado.

CREATE INDEX idx1 ON test(CAST(c3 AS INTEGER));

Verifique o resultado:

SHOW INDEX FROM test;

Índices de busca

Construir um índice de busca assincronamente

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c1, c2, c3);

Verifique o resultado:

SHOW INDEX FROM test;

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Indexar todas as colunas

Se nenhuma propriedade de coluna for especificada, os valores padrão serão aplicados.

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*');

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Adicionar propriedades à chave de índice

Indexe todas as colunas e configure a coluna c3 para tokenização IK:

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*', c3(type=text, analyzer=ik, indexed=true));

Para usar um mapping personalizado compatível com Elasticsearch na coluna c3:

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test('*', c3(mapping='{
  "type": "text",
  "analyzer": "ik_max_word"
}'));

Importante

O parâmetro mapping aplica-se apenas a versões compatíveis com Elasticsearch e sobrescreve todas as outras propriedades da coluna.

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Definir o estado do índice e a contagem de shards

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text, analyzer=ik))
WITH (indexState=ACTIVE, numShards=4);

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Definir configurações personalizadas de índice

Crie um índice de busca com compressão ZSTD e intervalo de atualização de 10 segundos:

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c3(type=text, analyzer=ik))
WITH (indexState=ACTIVE, INDEX_SETTINGS='{
  "index": {
    "codec": "zstd",
    "refresh_interval": "10s"
  }
}');

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Criar um índice de busca particionado por tempo

Particione pela coluna c4, começando de 30 dias atrás, com uma nova partição a cada 7 dias e período de retenção de 90 dias.

CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(c1, c2, c3, c4)
PARTITION BY RANGE TIME(c4) PARTITIONS 16
WITH (
  indexState=ACTIVE,
  RANGE_TIME_PARTITION_START='30',
  RANGE_TIME_PARTITION_INTERVAL='7',
  RANGE_TIME_PARTITION_TTL='90',
  RANGE_TIME_PARTITION_MAX_OVERLAP='90'
);

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Armazenar dados brutos no índice de busca

Por padrão, índices de busca filtram dados, mas não armazenam os valores brutos das colunas. Ative o armazenamento de dados brutos para consultar diretamente através do mecanismo de busca.

Armazenar todas as colunas do índice:

CREATE INDEX idx2 USING SEARCH ON test(c1, c2, c3, c4)
WITH (SOURCE_SETTINGS='{
  "enabled": true
}');

Verifique o resultado:

SHOW INDEX FROM test;

Armazenar um subconjunto de colunas (c2, c3, c4, excluindo c1):

CREATE INDEX idx2 USING SEARCH ON test(c1, c2, c3, c4)
WITH (SOURCE_SETTINGS='{
  "includes": ["c*"],
  "excludes": ["c1"]
}');

Verifique o resultado:

SHOW INDEX FROM test;

Para monitorar o progresso da construção, consulte Visualizar o progresso completo da construção de um índice de busca .

Aplicabilidade

Antes de começar

Sintaxe

Diferenças

Observações de uso

Tipo de índice (USING)

Expressão da chave de índice (index_key_expression)

Definição da chave de índice (index_key_definition)

Propriedades da chave de índice de busca (option_definition)

Expressões de função para índice secundário (function_expression)

Constante curinga (wildcard_string_literal)

Colunas incluídas (INCLUDE)

Particionamento do índice (PARTITION BY)

Modo de construção do índice (ASYNC | SYNC)

Propriedades do índice (WITH)

Propriedades de índice secundário

Propriedades de índice de busca

Exemplos

Índices secundários

Construir um índice assincronamente

Criar um índice composto sincronamente

Criar um índice secundário espaço-temporal

Incluir todas as colunas predefinidas

Incluir todas as colunas dinâmicas

Definir pré-partições para a tabela de índice

Definir chaves inicial e final com pré-partições

Criar um índice secundário Z-ORDER

Criar um índice secundário de grade S2

Indexar uma coluna após conversão de tipo de dado

Índices de busca

Construir um índice de busca assincronamente

Indexar todas as colunas

Adicionar propriedades à chave de índice

Definir o estado do índice e a contagem de shards

Definir configurações personalizadas de índice

Criar um índice de busca particionado por tempo

Armazenar dados brutos no índice de busca

Tipo de índice (`USING`)

Expressão da chave de índice (`index_key_expression`)

Definição da chave de índice (`index_key_definition`)

Propriedades da chave de índice de busca (`option_definition`)

Expressões de função para índice secundário (`function_expression`)

Constante curinga (`wildcard_string_literal`)

Colunas incluídas (`INCLUDE`)

Particionamento do índice (`PARTITION BY`)

Modo de construção do índice (`ASYNC` | `SYNC`)

Propriedades do índice (`WITH`)