Créer un index - Lindorm - Centre de documentation Alibaba Cloud

CREATE INDEX crée un index sur une ou plusieurs colonnes d'une table large LindormTable. Utilisez cette instruction pour activer les recherches hors clé primaire, la recherche en texte intégral et les requêtes analytiques sans balayage complet de la table.

Applicabilité

L'instruction CREATE INDEX s'applique uniquement à LindormTable. Aucune version minimale n'est requise pour les index secondaires.

Important

Pour créer un index de recherche ou un index columnstore à l'aide de CREATE INDEX, utilisez Lindorm SQL V2.6.1 ou une version ultérieure. Pour vérifier votre version de Lindorm SQL, consultez le Guide des versions SQL.

Prérequis

La création d'index secondaires, d'index columnstore et d'index de recherche nécessite des lectures de données, qui génèrent des opérations de lecture. Si vous avez activé la séparation des données chaudes et froides pour votre instance, surveillez la limitation du débit de lecture du stockage de capacité sur le stockage froid. Les lectures limitées sur le stockage froid ralentissent la création d'index et peuvent exercer une contre-pression sur les opérations d'écriture.

Syntaxe

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

Sous-productions :

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

Différences

Les éléments de syntaxe pris en charge varient selon le type d'index.

Élément de syntaxe	Index secondaire	Index de recherche
Type d'index (index_method_definition)	✔	0
Expression de clé d'index (index_key_expression)	✔	0
Colonnes incluses (include_expression)	0	✖
Partition d'index (partition_definition)	✖	0
Mode de création d'index (ASYNC\|SYNC) Important Seules les versions LindormTable 2.6.3 et ultérieures prennent en charge le mode de création `SYNC`.	0	0
Propriétés d'index (index_options)	0	0

Élément de syntaxe

Index secondaire

Index de recherche

Mot-clé USING

Facultatif (KV par défaut)

Facultatif

Expression de clé d'index

Obligatoire

INCLUDE

Facultatif

Non pris en charge

PARTITION BY

Non pris en charge

Facultatif

ASYNC / SYNC

Facultatif

Propriétés d'index WITH

Facultatif

Notes d'utilisation

Vous pouvez créer au maximum 3 index secondaires et 1 index de recherche par table large.

Type d'index (`USING`)

Utilisez le mot-clé USING pour spécifier le type d'index. S'il est omis, un index secondaire (KV) est créé par défaut.

Valeur USING

Type d'index

Cas d'utilisation

KV (ou omission)

Index secondaire

Recherches d'égalité et de plage hors clé primaire. Une instance prend en charge au maximum 8 tâches de création d'index secondaire simultanées ; une neuvième demande échoue immédiatement.

SEARCH

Index de recherche

Recherche en texte intégral, requêtes floues, filtrage multidimensionnel, agrégation et tri avec pagination. Aucune limite sur les tâches de création simultanées. Nécessite l'activation préalable de la fonctionnalité d'index de recherche — les nœuds de recherche et les nœuds Lindorm Tunnel Service (LTS) engendrent des frais. Consultez Activer les index de recherche. La clé d'index doit inclure au moins une colonne hors clé primaire. Types de données pris en charge : tous les types de base sauf DATE, TIME et DECIMAL. Consultez Types de données de base.

Expression de clé d'index (`index_key_expression`)

Définissez une ou plusieurs colonnes comme clés d'index. Un index comportant plusieurs clés d'index est un index composite.

Définition de clé d'index (`index_key_definition`)

Pour les index secondaires, chaque définition de clé peut être :

Un nom de colonne, éventuellement suivi de DESC pour un ordre décroissant
Une expression de fonction (Z-ORDER, S2, CAST, MD5 ou SHA256)

Pour les index de recherche, chaque définition de clé peut être :

Un nom de colonne avec des propriétés de clé d'index facultatives
La constante joker * pour indexer toutes les colonnes existantes

Propriétés de clé d'index de recherche (`option_definition`)

Spécifiez les propriétés par colonne à l'aide de la syntaxe column(property=value, ...). Par exemple : c3(type=text, analyzer=ik). Vous pouvez également spécifier ces propriétés lors de l'ajout d'une colonne d'index à l'aide de l'instruction ALTER INDEX.

Propriété

Type

Description

indexed

STRING

Indique s'il faut créer un index pour cette colonne. Un index inversé est créé pour les colonnes de type chaîne ; un index numérique BKD-Tree pour les colonnes numériques. Valeurs valides : true (par défaut) ou false

rowStored

STRING

Indique s'il faut stocker la valeur brute de la colonne. Valeurs valides : true ou false (par défaut)

columnStored

STRING

Indique s'il faut utiliser le stockage en colonnes pour accélérer le tri et l'analyse. Valeurs valides : true (par défaut) ou false

type

STRING

Définissez sur text pour les champs en texte intégral tokenisés. Doit être utilisé avec analyzer

analyzer

STRING

Tokeniseur pour les colonnes type=text. Valeurs valides : standard, english, ik, whitespace, comma. Doit être utilisé avec type

mapping

STRING

Propriétés de clé d'index personnalisées sous forme de chaîne JSON, compatible avec la syntaxe de mapping Elasticsearch. S'applique uniquement aux versions compatibles Elasticsearch. Remplace toutes les autres propriétés pour cette clé.

Expressions de fonction d'index secondaire (`function_expression`)

Lors de la création d'un index secondaire, vous pouvez spécifier une expression de fonction comme clé d'index.

Important

Les expressions de fonction MD5 et SHA256 nécessitent LindormTable 2.6.7.5 ou une version ultérieure. Si vous ne pouvez pas effectuer la mise à niveau, contactez le support technique Lindorm sur DingTalk à l'adresse s0s3eg3.

Fonction

Syntaxe

Cas d'utilisation

Z-ORDER

Z-ORDER(col1, col2, ...)

Index secondaire spatio-temporel ; les colonnes doivent être d'un type de données spatio-temporel. Consultez Index spatio-temporels.

S2(col, level)

Index de grille S2 pour les colonnes POLYGON ou MULTIPOLYGON ; level varie de 1 à 30. Consultez Fonction d'index S2.

CAST

CAST(col AS type)

Index sur le résultat d'une conversion de type de données. Consultez Types de données de base.

MD5

MD5(col)

Index sur la valeur encodée MD5 d'une colonne VARCHAR. Nécessite LindormTable 2.6.7.5+. Consultez Fonction MD5.

SHA256

SHA256(col)

Index sur la valeur encodée SHA256 d'une colonne VARCHAR. Nécessite LindormTable 2.6.7.5+. Consultez Fonction SHA256.

Constante joker (`wildcard_string_literal`)

Seuls les index de recherche prennent en charge la constante joker *. Utilisez * pour créer un index sur toutes les colonnes existantes au moment de l'exécution de l'instruction.

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

Important

Les colonnes ajoutées après l'exécution de l'instruction ne sont pas automatiquement indexées. Ajoutez-les manuellement à l'aide de ALTER INDEX.
Les colonnes dynamiques ne sont pas incluses. Consultez Colonnes dynamiques.

Colonnes incluses (`INCLUDE`)

La clause INCLUDE ajoute des colonnes non clés de la table principale dans la table d'index, formant ainsi un index couvrant. Les requêtes qui exploitent l'index peuvent récupérer les valeurs des colonnes incluses sans consulter la table principale, ce qui améliore les performances de lecture.

Pour les index secondaires, utilisez le mot-clé WITH pour inclure les colonnes dynamiques en définissant INDEX_COVERED_TYPE . Consultez Index secondaires .

Partition d'index (`PARTITION BY`)

Seuls les index de recherche prennent en charge le partitionnement d'index. Le serveur divise et stocke les données automatiquement. Au moment de la requête, le système applique l'élagage de partition pour ignorer les partitions non pertinentes.

Types de partition pris en charge : RANGE et HASH. Consultez Index partitionnés.

Mode de création d'index (`ASYNC` | `SYNC`)

Spécifiez ASYNC ou SYNC pour contrôler le moment où l'instruction renvoie un résultat.

Mode

Comportement

L'instruction bloque-t-elle ?

L'index est-il utilisable immédiatement après le retour de l'instruction ?

Cas d'utilisation

ASYNC (par défaut depuis LindormTable 2.6.1)

Renvoie immédiatement après le démarrage de la tâche de création

Non

Non — la création se poursuit en arrière-plan

Production ; les écritures continuent sans interruption pendant la création de l'index

SYNC

Renvoie uniquement après la fin de la tâche de création

Oui

Scripts de migration de schéma ; tests ; cas où l'index doit être prêt avant l'étape suivante

Important

SYNC nécessite LindormTable 2.6.3 ou une version ultérieure.

Le tableau suivant indique les modes pris en charge par chaque type d'index.deuxindex secondaires et index de recherchedeux

Mode de création

Index secondaire

Index de recherche

ASYNC (par défaut depuis LindormTable 2.6.1)

Pris en charge

SYNC (nécessite LindormTable 2.6.3+)

Pris en charge

Mode de création d'index	Index secondaire	Index de recherche
ASYNC Important À partir de LindormTable 2.6.1, le mode de création d'index par défaut pour l'instruction `CREATE INDEX` est asynchrone.	0	0
SYNC Important Seules les versions LindormTable 2.6.3 et ultérieures prennent en charge la création synchrone d'index.	0	✔

Propriétés d'index (`WITH`)

Propriétés d'index secondaire

Propriété

Type

Description

COMPRESSION

STRING

Algorithme de compression pour la table d'index. Valeurs valides : SNAPPY, ZSTD, LZ4

INDEX_COVERED_TYPE

STRING

Méthode de redondance pour les colonnes incluses. COVERED_ALL_COLUMNS_IN_SCHEMA : inclut toutes les colonnes non clés prédéfinies. COVERED_DYNAMIC_COLUMNS : inclut toutes les colonnes non clés prédéfinies et les colonnes dynamiques. Lorsque cette propriété est définie, omettez la clause INCLUDE. Avant d'inclure les colonnes dynamiques, activez la fonctionnalité de colonne dynamique. Consultez Colonnes dynamiques.

STARTKEY

STRING

Clé de début pour la table d'index. Ne peut pas être définie pour les colonnes de type timestamp ou spatial.

ENDKEY

STRING

Clé de fin pour la table d'index. Ne peut pas être définie pour les colonnes de type timestamp ou spatial.

NUMREGIONS

INTEGER

Nombre de pré-partitions pour la table d'index. Ne peut pas être défini pour les colonnes de type timestamp ou spatial.

Propriétés d'index de recherche

Propriété

Type

Description

indexState

STRING

État initial de l'index. Valeurs valides : ACTIVE (disponible), INACTIVE (indisponible), DISABLED (désactivé)

numShards

INTEGER

Nombre de shards. Par défaut : deux fois le nombre de nœuds de recherche. Maintenez chaque shard entre 30 et 100 millions de lignes et 30 à 50 Go. Un shard dépassant 2 milliards de lignes affecte la stabilité du système. Planifiez le nombre de shards avant de créer des index de production. Pour les données de séries chronologiques avec une croissance significative du volume (telles que les commandes ou les journaux), utilisez plutôt un index partitionné par temps.

RANGE_TIME_PARTITION_START

INTEGER

Jours avant la création de l'index pour commencer à créer des partitions, pour les données historiques. Si les données historiques ont un horodatage antérieur à l'heure de début de partition, une erreur se produit. Obligatoire lors de la création d'un index partitionné par temps.

RANGE_TIME_PARTITION_INTERVAL

INTEGER

Intervalle en jours entre les nouvelles partitions. Par exemple, 7 crée une nouvelle partition chaque semaine. Obligatoire lors de la création d'un index partitionné par temps.

RANGE_TIME_PARTITION_TTL

INTEGER

Période de rétention des données en jours. Par exemple, 180 conserve six mois de données et efface automatiquement les partitions plus anciennes. Si non défini, les données ne sont jamais effacées. Obligatoire lors de la création d'un index partitionné par temps.

RANGE_TIME_PARTITION_MAX_OVERLAP

INTEGER

Décalage d'horodatage futur maximal autorisé en jours. Par défaut : 1 jour.

RANGE_TIME_PARTITION_FIELD_TIMEUNIT

LONG

Unité du champ de partition temporelle. Par défaut : millisecondes (ms). Si l'unité est la seconde (s), la valeur du champ doit comporter 10 chiffres. Si l'unité est la milliseconde (ms), elle doit comporter 13 chiffres.

RANGE_TIME_PARTITION_CHS

INTEGER

Limite de données chaudes/froides en secondes. Par exemple, 864000 archive les données de plus de 10 jours vers le stockage froid. Si non défini, la séparation des données chaudes et froides n'est pas activée pour cet index.

INDEX_SETTINGS

STRING

Propriétés d'index personnalisées sous forme de chaîne JSON, compatible avec la syntaxe des paramètres d'index Elasticsearch. S'applique uniquement aux versions compatibles Elasticsearch.

SOURCE_SETTINGS

STRING

Politique de stockage des données brutes pour les colonnes d'index, sous forme de chaîne JSON compatible avec les paramètres _source d'Elasticsearch. Par défaut, les index de recherche ne stockent pas les données brutes des colonnes. Configurez ce paramètre uniquement lorsque vous avez besoin de requêtes directes de données via l'interface utilisateur de visualisation du moteur de recherche. Consultez requêtes de données. Paramètres pris en charge : enabled (Booléen ; true stocke toutes les colonnes, false n'en stocke aucune ; ne peut pas être utilisé avec includes ou excludes), includes (tableau de chaînes ; colonnes dont les données brutes doivent être stockées ; prend en charge le joker *), excludes (tableau de chaînes ; colonnes dont les données brutes doivent être exclues ; prend en charge le joker *).

Exemples

Tous les exemples utilisent la table principale suivante :

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

Index secondaires

Créer un index de manière asynchrone

Le mode par défaut est ASYNC. L'instruction renvoie un résultat immédiatement.

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Créer un index composite de manière synchrone

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Créer un index secondaire spatio-temporel

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

Consultez Index spatio-temporels.

Inclure toutes les colonnes prédéfinies

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Inclure toutes les colonnes dynamiques

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Définir des pré-partitions pour la table d'index

Créez un index avec 32 pré-partitions.

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Définir les clés de début et de fin avec des pré-partitions

Créez une table d'index avec 32 pré-partitions entre 11111111 et 9999999.

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Créer un index secondaire Z-ORDER

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Créer un index secondaire de grille S2

Les index S2 ne prennent en charge que la création asynchrone. Exécutez BUILD INDEX pour déclencher la création après avoir créé l'index.

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Indexer une colonne après une conversion de type de données

Convertissez la colonne c3 en INTEGER, puis créez un index secondaire sur le résultat.

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Index de recherche

Créer un index de recherche de manière asynchrone

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Indexer toutes les colonnes

Si aucune propriété de colonne n'est spécifiée, les valeurs par défaut s'appliquent.

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

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Ajouter des propriétés de clé d'index

Indexez toutes les colonnes et configurez la colonne c3 pour la tokenisation IK :

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

Pour utiliser un mapping personnalisé compatible Elasticsearch pour la colonne c3 :

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

Important

Le paramètre mapping s'applique uniquement aux versions compatibles Elasticsearch et remplace toutes les autres propriétés pour la colonne.

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Définir l'état de l'index et le nombre de shards

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

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Définir des paramètres d'index personnalisés

Créez un index de recherche avec compression ZSTD et un intervalle d'actualisation de 10 secondes :

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"
  }
}');

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Créer un index de recherche partitionné par temps

Partitionnez par la colonne c4, en commençant il y a 30 jours, avec une nouvelle partition tous les 7 jours et une période de rétention de 90 jours.

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

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Stocker les données brutes dans l'index de recherche

Par défaut, les index de recherche filtrent les données mais ne stockent pas les valeurs brutes des colonnes. Activez le stockage des données brutes pour interroger directement via le moteur de recherche.

Stocker toutes les colonnes d'index :

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Stocker un sous-ensemble de colonnes (c2, c3, c4, en excluant c1) :

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

Vérifiez le résultat :

SHOW INDEX FROM test;

Pour surveiller la progression de la création, consultez Afficher la progression complète de la création d'un index de recherche .

Applicabilité

Prérequis

Syntaxe

Différences

Notes d'utilisation

Type d'index (USING)

Expression de clé d'index (index_key_expression)

Définition de clé d'index (index_key_definition)

Propriétés de clé d'index de recherche (option_definition)

Expressions de fonction d'index secondaire (function_expression)

Constante joker (wildcard_string_literal)

Colonnes incluses (INCLUDE)

Partition d'index (PARTITION BY)

Mode de création d'index (ASYNC | SYNC)

Propriétés d'index (WITH)

Propriétés d'index secondaire

Propriétés d'index de recherche

Exemples

Index secondaires

Créer un index de manière asynchrone

Créer un index composite de manière synchrone

Créer un index secondaire spatio-temporel

Inclure toutes les colonnes prédéfinies

Inclure toutes les colonnes dynamiques

Définir des pré-partitions pour la table d'index

Définir les clés de début et de fin avec des pré-partitions

Créer un index secondaire Z-ORDER

Créer un index secondaire de grille S2

Indexer une colonne après une conversion de type de données

Index de recherche

Créer un index de recherche de manière asynchrone

Indexer toutes les colonnes

Ajouter des propriétés de clé d'index

Définir l'état de l'index et le nombre de shards

Définir des paramètres d'index personnalisés

Créer un index de recherche partitionné par temps

Stocker les données brutes dans l'index de recherche

Type d'index (`USING`)

Expression de clé d'index (`index_key_expression`)

Définition de clé d'index (`index_key_definition`)

Propriétés de clé d'index de recherche (`option_definition`)

Expressions de fonction d'index secondaire (`function_expression`)

Constante joker (`wildcard_string_literal`)

Colonnes incluses (`INCLUDE`)

Partition d'index (`PARTITION BY`)

Mode de création d'index (`ASYNC` | `SYNC`)

Propriétés d'index (`WITH`)