Create an index - Lindorm - Centro de documentación de Alibaba Cloud

CREATE INDEX compila un índice sobre una o más columnas de una tabla ancha de LindormTable. Úselo para habilitar búsquedas por claves no primarias, búsquedas de texto completo y consultas analíticas sin necesidad de escanear toda la tabla.

Aplicabilidad

CREATE INDEX aplica únicamente a LindormTable. No se requiere ninguna versión mínima para los índices secundarios.

Importante

Para compilar un índice de búsqueda o un índice columnstore con CREATE INDEX, utilice Lindorm SQL V2.6.1 o posterior. Para comprobar su versión de Lindorm SQL, consulte SQL version guide.

Antes de empezar

La compilación de índices para secondary indexes, columnstore indexes y search indexes requiere búsquedas de datos que generan operaciones de lectura. Si habilitó hot and cold data separation en su instancia, supervise Capacity storage read throttling en el almacenamiento en frío. Las lecturas limitadas en el almacenamiento en frío ralentizan la compilación del índice y pueden generar contrapresión en las operaciones de escritura.

Sintaxis

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 ')']

Subproducciones:

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

Diferencias

Los elementos de sintaxis compatibles varían según el tipo de índice.

Elemento de sintaxis	Índice secundario	Índice de búsqueda
Index type (index_method_definition)	✔	0
Index key expression (index_key_expression)	✔	0
Included columns (include_expression)	0	✖
Index partition (partition_definition)	✖	0
Index building mode (ASYNC\|SYNC) Importante Solo LindormTable 2.6.3 y versiones posteriores admiten el modo de compilación `SYNC`.	0	0
Index properties (index_options)	0	0

Syntax element	Secondary index	Search index
Palabra clave `USING`	Opcional (el valor predeterminado es KV)	Opcional
Expresión de clave de índice	Obligatorio	Obligatorio
`INCLUDE`	Opcional	No compatible
`PARTITION BY`	No compatible	Opcional
`ASYNC` / `SYNC`	Opcional	Opcional
Propiedades de índice `WITH`	Opcional	Opcional

Notas de uso

Se pueden crear como máximo 3 índices secundarios y 1 índice de búsqueda por cada tabla ancha.

Tipo de índice (`USING`)

Utilice la palabra clave USING para especificar el tipo de índice. Si se omite, se crea un índice secundario (KV) de forma predeterminada.

`USING` value	Index type	Use when
`KV` (u omitir)	Índice secundario	Búsquedas de igualdad y de rango por claves no primarias. Una instancia admite como máximo 8 tareas simultáneas de compilación de índices secundarios; una novena solicitud falla de inmediato
`SEARCH`	Índice de búsqueda	Búsqueda de texto completo, consultas difusas, filtrado multidimensional, agregación y ordenamiento con paginación. Sin límite de tareas de compilación simultáneas. Requiere habilitar primero la función de índice de búsqueda, ya que los nodos de búsqueda y los nodos de Lindorm Tunnel Service (LTS) generan cargos. Consulte Enable search indexes. La clave del índice debe incluir como mínimo una columna que no sea clave primaria. Tipos de datos compatibles: todos los tipos básicos excepto DATE, TIME y DECIMAL. Consulte Basic data types

Expresión de clave de índice (`index_key_expression`)

Defina una o más columnas como claves de índice. Un índice con varias claves de índice es un índice compuesto.

Definición de clave de índice (`index_key_definition`)

Para los índices secundarios, cada definición de clave puede ser:

Un nombre de columna, de forma opcional con DESC para orden descendente
Una expresión de función (Z-ORDER, S2, CAST, MD5 o SHA256)

Para los índices de búsqueda, cada definición de clave puede ser:

Un nombre de columna con propiedades de clave de índice opcionales
La constante comodín * para indexar todas las columnas existentes

Propiedades de clave de índice de búsqueda (`option_definition`)

Especifique las propiedades por columna con la sintaxis column(property=value, ...). Por ejemplo: c3(type=text, analyzer=ik). También puede especificar estas propiedades al agregar una columna de índice mediante la instrucción ALTER INDEX.

Property	Type	Description
`indexed`	STRING	Indica si se compila un índice para esta columna. Se compila un índice invertido para las columnas de cadena y un índice numérico BKD-Tree para las columnas numéricas. Valores válidos: `true` (predeterminado) o `false`
`rowStored`	STRING	Indica si se almacena el valor original de la columna. Valores válidos: `true` o `false` (predeterminado)
`columnStored`	STRING	Indica si se utiliza el almacenamiento por columnas para acelerar el ordenamiento y el análisis. Valores válidos: `true` (predeterminado) o `false`
`type`	STRING	Establezca el valor `text` para campos de texto completo segmentados. Debe usarse junto con `analyzer`
`analyzer`	STRING	Tokenizer para columnas con `type=text`. Valores válidos: `standard`, `english`, `ik`, `whitespace`, `comma`. Debe usarse junto con `type`
`mapping`	STRING	Propiedades personalizadas de la clave de índice como una cadena JSON, compatible con la sintaxis de mapping de Elasticsearch. Aplica solo a versiones compatibles con Elasticsearch. Anula todas las demás propiedades de esta clave

Expresiones de función de índice secundario (`function_expression`)

Al crear un índice secundario, puede especificar una expresión de función como clave de índice.

Importante

Las expresiones de función MD5 y SHA256 requieren LindormTable 2.6.7.5 o posterior. Si no puede actualizar, comuníquese con el soporte técnico de Lindorm en DingTalk mediante el identificador s0s3eg3.

Function	Syntax	Use when
Z-ORDER	`Z-ORDER(col1, col2, ...)`	Índice secundario espaciotemporal; las columnas deben tener un tipo de datos espaciotemporal. Consulte Spatio-temporal indexes
S2	`S2(col, level)`	Índice de cuadrícula S2 para columnas POLYGON o MULTIPOLYGON; `level` varía de 1 a 30. Consulte S2 index function
CAST	`CAST(col AS type)`	Índice sobre el resultado de una conversión de tipo de datos. Consulte Basic data types
MD5	`MD5(col)`	Índice sobre el valor codificado con MD5 de una columna VARCHAR. Requiere LindormTable 2.6.7.5 o posterior. Consulte MD5 function
SHA256	`SHA256(col)`	Índice sobre el valor codificado con SHA256 de una columna VARCHAR. Requiere LindormTable 2.6.7.5 o posterior. Consulte SHA256 function

Constante comodín (`wildcard_string_literal`)

Solo los índices de búsqueda admiten la constante comodín *. Utilice * para compilar un índice sobre todas las columnas existentes en el momento en que se ejecuta la instrucción.

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

Importante

Las columnas agregadas después de ejecutar la instrucción no se indexan automáticamente. Agréguelas manualmente con ALTER INDEX.
Las columnas dinámicas no se incluyen. Consulte Dynamic columns.

Columnas incluidas (`INCLUDE`)

La cláusula INCLUDE agrega columnas que no son clave de la tabla principal a la tabla de índice, lo que forma un índice de cobertura. Las consultas que utilizan el índice pueden recuperar los valores de las columnas incluidas sin consultar la tabla principal, lo que mejora el rendimiento de lectura.

En los índices secundarios, utilice la palabra clave WITH para incluir columnas dinámicas mediante la configuración de INDEX_COVERED_TYPE . Consulte Secondary indexes .

Partición de índice (`PARTITION BY`)

Solo los índices de búsqueda admiten la partición de índices. El servidor divide y almacena los datos automáticamente. Durante la consulta, el sistema aplica la eliminación de particiones para omitir aquellas que no son relevantes.

Tipos de partición compatibles: RANGE y HASH. Consulte Partitioned indexes.

Modo de compilación de índice (`ASYNC` | `SYNC`)

Especifique ASYNC o SYNC para controlar cuándo devuelve el control la instrucción.

Mode	Behavior	Statement blocks?	Index usable immediately after the statement returns?	Use when
`ASYNC` (predeterminado desde LindormTable 2.6.1)	Devuelve el control inmediatamente después de iniciar la tarea de compilación	No	No: la compilación continúa en segundo plano	Producción; las escrituras continúan sin verse afectadas mientras se compila el índice
`SYNC`	Devuelve el control solo cuando finaliza la tarea de compilación	Sí	Sí	Scripts de migración de esquemas; pruebas; casos donde el índice debe estar listo antes del siguiente paso

Importante

SYNC requiere LindormTable 2.6.3 o posterior.

A continuación se muestra qué modos admite cada tipo de índice.dosíndices secundarios e índices de búsquedados

Building mode	Secondary index	Search index
`ASYNC` (predeterminado desde LindormTable 2.6.1)	Compatible	Compatible
`SYNC` (requiere LindormTable 2.6.3+)	Compatible	Compatible

Modo de compilación de índice	Índice secundario	Índice de búsqueda
ASYNC Importante A partir de LindormTable 2.6.1, el modo de compilación de índice predeterminado para la instrucción `CREATE INDEX` es asíncrono.	0	0
SYNC Importante Solo LindormTable 2.6.3 y versiones posteriores admiten la compilación síncrona de índices.	0	✔

Propiedades de índice (`WITH`)

Propiedades de índice secundario

Property	Type	Description
`COMPRESSION`	STRING	Algoritmo de compresión para la tabla de índice. Valores válidos: `SNAPPY`, `ZSTD`, `LZ4`
`INDEX_COVERED_TYPE`	STRING	Método de redundancia para las columnas incluidas. `COVERED_ALL_COLUMNS_IN_SCHEMA`: incluye todas las columnas predefinidas que no son clave primaria. `COVERED_DYNAMIC_COLUMNS`: incluye todas las columnas predefinidas que no son clave primaria y las columnas dinámicas. Cuando se establece este valor, omita la cláusula `INCLUDE`. Antes de incluir columnas dinámicas, habilite la función de columnas dinámicas. Consulte Dynamic columns
`STARTKEY`	STRING	Clave inicial para la tabla de índice. No se puede establecer para columnas de tipo de datos de marca de tiempo o espaciales
`ENDKEY`	STRING	Clave final para la tabla de índice. No se puede establecer para columnas de tipo de datos de marca de tiempo o espaciales
`NUMREGIONS`	INTEGER	Número de particiones previas para la tabla de índice. No se puede establecer para columnas de tipo de datos de marca de tiempo o espaciales

Propiedades de índice de búsqueda

Property	Type	Description
`indexState`	STRING	Estado inicial del índice. Valores válidos: `ACTIVE` (disponible), `INACTIVE` (no disponible), `DISABLED` (deshabilitado)
`numShards`	INTEGER	Número de fragmentos. Valor predeterminado: el doble del número de nodos de búsqueda. Mantenga cada fragmento entre 30 y 100 millones de filas, y entre 30 y 50 GB. Un fragmento que supere los 2 mil millones de filas afecta la estabilidad del sistema. Planifique la cantidad de fragmentos antes de crear índices en producción. Para datos de series temporales con un crecimiento de volumen significativo (como pedidos o registros), utilice un time-partitioned index en su lugar
`RANGE_TIME_PARTITION_START`	INTEGER	Días anteriores a la creación del índice desde los cuales se empiezan a crear particiones para los datos históricos. Si los datos históricos tienen una marca de tiempo anterior a la hora de inicio de la partición, se produce un error. Obligatorio al crear un índice particionado por tiempo
`RANGE_TIME_PARTITION_INTERVAL`	INTEGER	Intervalo en días entre particiones nuevas. Por ejemplo, `7` crea una partición nueva cada semana. Obligatorio al crear un índice particionado por tiempo
`RANGE_TIME_PARTITION_TTL`	INTEGER	Periodo de retención de datos en días. Por ejemplo, `180` retiene seis meses de datos y elimina automáticamente las particiones más antiguas. Si no se establece, los datos nunca se eliminan. Obligatorio al crear un índice particionado por tiempo
`RANGE_TIME_PARTITION_MAX_OVERLAP`	INTEGER	Desplazamiento máximo permitido de marca de tiempo futura en días. Valor predeterminado: 1 día
`RANGE_TIME_PARTITION_FIELD_TIMEUNIT`	LONG	Unidad del campo de partición de tiempo. Valor predeterminado: milisegundos (ms). Si la unidad es segundos (s), el valor del campo debe tener 10 dígitos. Si la unidad es milisegundos (ms), debe tener 13 dígitos
`RANGE_TIME_PARTITION_CHS`	INTEGER	Límite entre datos calientes y fríos en segundos. Por ejemplo, `864000` archiva en el almacenamiento en frío los datos con más de 10 días de antigüedad. Si no se establece, no se habilita la separación de datos calientes y fríos para este índice
`INDEX_SETTINGS`	STRING	Propiedades personalizadas del índice como una cadena JSON, compatible con la sintaxis de configuración de índices de Elasticsearch. Aplica solo a versiones compatibles con Elasticsearch
`SOURCE_SETTINGS`	STRING	Política de almacenamiento de datos originales para las columnas de índice, como una cadena JSON compatible con la configuración `_source` de Elasticsearch. De forma predeterminada, los índices de búsqueda no almacenan los datos originales de las columnas. Configure este parámetro solo cuando necesite consultas directas de datos a través de la interfaz de visualización del motor de búsqueda. Consulte data queries. Parámetros compatibles: `enabled` (booleano; `true` almacena todas las columnas, `false` no almacena ninguna; no se puede usar con `includes` ni con `excludes`), `includes` (matriz de cadenas; columnas cuyos datos originales se almacenarán; admite el comodín ``), `excludes` (matriz de cadenas; columnas cuyos datos originales se excluirán; admite el comodín ``)

Ejemplos

Todos los ejemplos utilizan la siguiente tabla 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 secundarios

Compilar un índice de forma asíncrona

El modo predeterminado es ASYNC. La instrucción devuelve el control de inmediato.

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

Compruebe el resultado:

SHOW INDEX FROM test;

Crear un índice compuesto de forma síncrona

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

Compruebe el resultado:

SHOW INDEX FROM test;

Crear un índice secundario espaciotemporal

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

Consulte Spatio-temporal indexes.

Incluir todas las columnas predefinidas

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

Compruebe el resultado:

SHOW INDEX FROM test;

Incluir todas las columnas dinámicas

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

Compruebe el resultado:

SHOW INDEX FROM test;

Establecer particiones previas para la tabla de índice

Cree un índice con 32 particiones previas.

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

Compruebe el resultado:

SHOW INDEX FROM test;

Establecer claves de inicio y fin con particiones previas

Cree una tabla de índice con 32 particiones previas entre 11111111 y 9999999.

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

Compruebe el resultado:

SHOW INDEX FROM test;

Crear un índice secundario Z-ORDER

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

Compruebe el resultado:

SHOW INDEX FROM test;

Crear un índice secundario de cuadrícula S2

Los índices S2 solo admiten compilación asíncrona. Ejecute BUILD INDEX para desencadenar la compilación después de crear el índice.

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

Compruebe el resultado:

SHOW INDEX FROM test;

Indexar una columna después de una conversión de tipo de datos

Convierta la columna c3 a INTEGER y luego cree un índice secundario sobre el resultado.

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

Compruebe el resultado:

SHOW INDEX FROM test;

Índices de búsqueda

Compilar un índice de búsqueda de forma asíncrona

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

Compruebe el resultado:

SHOW INDEX FROM test;

Para supervisar el progreso de la compilación, consulte View the full build progress of a search index .

Indexar todas las columnas

Si no se especifican propiedades de columna, se aplican los valores predeterminados.

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

Para supervisar el progreso de la compilación, consulte View the full build progress of a search index .

Agregar propiedades de clave de índice

Indexe todas las columnas y configure la columna c3 para la segmentación con IK:

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

Para utilizar un mapping personalizado compatible con Elasticsearch en la columna c3:

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

Importante

El parámetro mapping aplica solo a versiones compatibles con Elasticsearch y anula todas las demás propiedades de la columna.

Para supervisar el progreso de la compilación, consulte View the full build progress of a search index .

Establecer el estado del índice y la cantidad de fragmentos

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

Para supervisar el progreso de la compilación, consulte View the full build progress of a search index .

Establecer configuraciones de índice personalizadas

Cree un índice de búsqueda con compresión ZSTD y un intervalo de actualización 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 supervisar el progreso de la compilación, consulte View the full build progress of a search index .

Crear un índice de búsqueda particionado por tiempo

Particione por la columna c4, comenzando desde hace 30 días, con una partición nueva cada 7 días y un periodo de retención de 90 días.

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 supervisar el progreso de la compilación, consulte View the full build progress of a search index .

Almacenar datos sin procesar en el search index

De forma predeterminada, los search indexes filtran los datos, pero no almacenan los valores originales de las columnas. Habilite el almacenamiento de datos sin procesar para consultar directamente a través del motor de búsqueda.

Almacenar todas las columnas del índice:

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

Compruebe el resultado:

SHOW INDEX FROM test;

Almacenar un subconjunto de columnas (c2, c3 y c4, excepto c1):

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

Compruebe el resultado:

SHOW INDEX FROM test;

Para monitorear el progreso de compilación, consulte View the full build progress of a search index .

Aplicabilidad

Antes de empezar

Sintaxis

Diferencias

Notas de uso

Tipo de índice (USING)

Expresión de clave de índice (index_key_expression)

Definición de clave de índice (index_key_definition)

Propiedades de clave de índice de búsqueda (option_definition)

Expresiones de función de índice secundario (function_expression)

Constante comodín (wildcard_string_literal)

Columnas incluidas (INCLUDE)

Partición de índice (PARTITION BY)

Modo de compilación de índice (ASYNC | SYNC)

Propiedades de índice (WITH)

Propiedades de índice secundario

Propiedades de índice de búsqueda

Ejemplos

Índices secundarios

Compilar un índice de forma asíncrona

Crear un índice compuesto de forma síncrona

Crear un índice secundario espaciotemporal

Incluir todas las columnas predefinidas

Incluir todas las columnas dinámicas

Establecer particiones previas para la tabla de índice

Establecer claves de inicio y fin con particiones previas

Crear un índice secundario Z-ORDER

Crear un índice secundario de cuadrícula S2

Indexar una columna después de una conversión de tipo de datos

Índices de búsqueda

Compilar un índice de búsqueda de forma asíncrona

Indexar todas las columnas

Agregar propiedades de clave de índice

Establecer el estado del índice y la cantidad de fragmentos

Establecer configuraciones de índice personalizadas

Crear un índice de búsqueda particionado por tiempo

Almacenar datos sin procesar en el search index

Tipo de índice (`USING`)

Expresión de clave de índice (`index_key_expression`)

Definición de clave de índice (`index_key_definition`)

Propiedades de clave de índice de búsqueda (`option_definition`)

Expresiones de función de índice secundario (`function_expression`)

Constante comodín (`wildcard_string_literal`)

Columnas incluidas (`INCLUDE`)

Partición de índice (`PARTITION BY`)

Modo de compilación de índice (`ASYNC` | `SYNC`)

Propiedades de índice (`WITH`)