Quels types d'index existent en SQL?

• Arbre-B (B-arbre) : Le type le plus courant. Convient pour la plupart des opérations de lecture, y compris WHERE, LIKE, ORDER BY.

  CREATE INDEX idx_column ON table_name (column_name);
  

• Hash (Hash) : Optimisé pour les correspondances exactes (=). Ne supporte pas les requêtes de plage ou le tri.

  -- Exemple de syntaxe, peut varier selon le SGBD
  CREATE INDEX idx_column ON table_name USING HASH (column_name);
  

• Texte intégral (Full-Text) : Utilisé pour la recherche dans de grands champs de texte (VARCHAR, TEXT).

  -- Exemple de syntaxe dans MySQL
  CREATE FULLTEXT INDEX idx_text ON table_name (text_column);
  -- Exemple de syntaxe dans PostgreSQL
  CREATE INDEX idx_text ON table_name USING GIN (to_tsvector('russian', text_column));
  

• Spatial (Spatial) : Pour l'indexation des données géographiques (points, lignes, polygones). Utilisé avec des types de données comme GEOMETRY ou GEOGRAPHY.

  -- Exemple de syntaxe dans MySQL
  CREATE SPATIAL INDEX idx_geo ON table_name (spatial_column);
  -- Exemple de syntaxe dans PostgreSQL
  CREATE INDEX idx_geo ON table_name USING GIST (spatial_column);
  

• GIN (Index inversé généralisé) et GiST (Arbre de recherche généralisé) dans PostgreSQL : Types d'index flexibles, adaptés à diverses tâches, y compris la recherche en texte intégral, l'indexation de tableaux et JSON.

• Cluster (Clustered) : Définit l'ordre physique des lignes dans la table. Une table ne peut avoir qu'un seul index cluster. Souvent basé sur la clé primaire.

  -- Lors de la déclaration de la clé primaire, un index cluster est souvent créé
  CREATE TABLE table_name (
      id INT PRIMARY KEY CLUSTERED, -- En SQL Server
      ...
  );
  

• Non cluster (Non-Clustered) : Structure de données séparée stockant des paires "valeur de colonne (ou colonnes) + pointeur vers la ligne de données". La table peut avoir plusieurs index non cluster.

Comparaison :

Type d'index	Application	Opérations
B-arbre	Général, la plupart des types de données	=, <, >, <=, >=, BETWEEN, LIKE (début)
Hash	Correspondance exacte (=)	=
Texte intégral	Recherche dans le texte	MATCH AGAINST (MySQL), to_tsquery (PostgreSQL)
Spatial	Données géographiques	Fonctions spatiales
Cluster	Ordonnancement physique des lignes	Accélère la sélection de plages
Non-cluster	Accélère la recherche sur les colonnes indexées	=, <, >, <= et autres