Indexation de la base de données - des cartes pour chercher plus vite

Cartographes ! Le consul Caesar.js a remarqué que la recherche des tributs dans nos registres prend de plus en plus de temps : avec vingt mille entrées, la base lit toute la table pour en trouver une seule. Il est temps de créer des index, des cartes spéciales qui permettront de retrouver chaque tribut en un éclair.

Que sont les index dans le monde des légionnaires ?

Imagine des milliers de cartes de tributs dans un grand coffre. Sans organisation :

• chaque recherche consiste à passer en revue toutes les cartes une par une,
• trouver un tribut peut prendre des heures,
• plus il y a de cartes, plus la recherche est longue.

Un index fonctionne comme le catalogue d'une bibliothèque. Il a pourtant un prix : il accélère les lectures mais ralentit les écritures, car à chaque INSERT et UPDATE la base met aussi à jour le catalogue, qui occupe de la place sur le disque. Tu indexes donc les colonnes sur lesquelles tu cherches vraiment.

Les index de base dans TypeORM

Dans TypeORM, les index se déclarent avec le décorateur @Index() : sur un champ, il crée un index d'une colonne, sur la classe, un index composite de plusieurs colonnes. Voici une entité pour PostgreSQL :

1// tribute.entity.ts
2import { Entity, Column, Index, PrimaryGeneratedColumn } from 'typeorm';
3import type { Point } from 'typeorm';
4
5@Entity('tributes')
6@Index(['location', 'collectedAt']) // Composite index
7@Index(['value'], { where: 'value > 1000' }) // Partial index
8export class Tribute {
9  @PrimaryGeneratedColumn()
10  id: number;
11
12  @Column()
13  @Index() // Index simple sur name
14  name: string;
15
16  @Column('decimal')
17  @Index('idx_tribute_value') // Named index
18  value: number;
19
20  @Column()
21  @Index() // Index sur location pour filtrer par province
22  location: string;
23
24  @Column({ type: 'enum', enum: ['GOLD', 'SILVER', 'GEMS'] })
25  @Index() // Index sur type pour le filtrage
26  type: string;
27
28  @Column({ name: 'collected_at', type: 'timestamp' })
29  @Index() // Index sur le timestamp pour les plages de dates
30  collectedAt: Date;
31
32  @Column({ unique: true })
33  serialNumber: string; // Index unique automatique
34
35  @Column('text', { nullable: true })
36  description: string;
37
38  @Column({ type: 'geography', spatialFeatureType: 'Point', srid: 4326 })
39  @Index({ spatial: true }) // Index GiST pour les requêtes géographiques (PostGIS)
40  coordinates: Point;
41}

Un index partiel avec l'option where ne couvre que les tributs coûteux, il est donc plus petit. unique: true crée une contrainte d'unicité appuyée sur un index. La colonne collected_at a un nom explicite pour que le SQL brut et l'entité parlent de la même chose. Point est un type GeoJSON de TypeORM, et geography exige l'extension PostGIS. Attention : le pilote PostgreSQL renvoie decimal sous forme de texte pour ne pas perdre de précision.

Les index dans d'autres bases

La première version de cette entité avait aussi un index fulltext, mais TypeORM ne le gère que dans MySQL. Là-bas, il ressemble à ceci :

1// Variante pour MySQL - un index plein texte
2@Column('text')
3@Index({ fulltext: true })
4description: string;

Dans PostgreSQL, la recherche plein texte se fait autrement, tu le verras plus bas. Dans MongoDB avec Mongoose, comme dans l'éditeur à côté, tu décris l'index dans le schéma :

1// soldier.schema.ts - MongoDB (Mongoose)
2@Schema()
3export class Soldier {
4  @Prop({ index: true })
5  rank: string;
6
7  @Prop({ unique: true })
8  registrationNumber: string;
9}
10
11export const SoldierSchema = SchemaFactory.createForClass(Soldier);
12SoldierSchema.index({ legionId: 1, enrolledAt: -1 }); // index composé

L'idée ne change pas : champ simple, unicité et index composé, seule l'écriture diffère.

Le plan d'exécution

C'est EXPLAIN qui te dit si la base utilise un index, en montrant le plan de la requête. EXPLAIN ANALYZE exécute en plus la requête et donne les vrais temps :

1// indexing-strategy.service.ts
2@Injectable()
3export class IndexingStrategyService {
4  constructor(
5    @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
6    @InjectDataSource() private dataSource: DataSource,
7  ) {}
8
9  // Vérifier l'utilisation des index
10  async analyzeIndexUsage(): Promise<any> {
11    const queries = [
12      // Vérifier le plan d'exécution de la requête
13      `EXPLAIN SELECT * FROM tributes WHERE type = 'GOLD' AND value > 1000`,
14      `EXPLAIN SELECT * FROM tributes WHERE location LIKE 'Hispania%'`,
15      `EXPLAIN ANALYZE SELECT * FROM tributes WHERE collected_at BETWEEN '2023-01-01' AND '2023-12-31'`,
16    ];
17
18    const results = [];
19    for (const query of queries) {
20      const plan = await this.dataSource.query(query);
21      results.push({ query, plan });
22    }
23
24    return results;
25  }

Sur 20 000 lignes, la première requête a obtenu un Bitmap Index Scan, mais la deuxième un Seq Scan : avec une locale autre que C, un index B-tree ne sert LIKE 'Hispania%' qu'avec la classe d'opérateurs text_pattern_ops. La troisième a aussi lu toute la table, car une année entière représentait la moitié des données, et dans ce cas parcourir le tout coûte moins cher.

Des index créés en connaissance de cause

On peut créer à la main des index pour des requêtes précises :

1  // Créer des index dynamiquement
2  async createPerformanceIndexes(): Promise<void> {
3    const queryRunner = this.dataSource.createQueryRunner();
4
5    try {
6      // Index pour les recherches fréquentes par valeur et par type
7      await queryRunner.query(`
8        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_type_value
9        ON tributes(type, value DESC)
10      `);
11
12      // Index partiel uniquement pour les tributs coûteux
13      await queryRunner.query(`
14        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_expensive_tributes
15        ON tributes(collected_at)
16        WHERE value > 10000
17      `);
18
19      // Index composite pour les requêtes par province et par date
20      await queryRunner.query(`
21        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_location_time
22        ON tributes(location, collected_at)
23      `);
24
25      // Index couvrant (contient toutes les colonnes nécessaires)
26      await queryRunner.query(`
27        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_summary
28        ON tributes(type, location)
29        INCLUDE (name, value, collected_at)
30      `);
31
32      console.log('Index de performance créés !');
33    } catch (error) {
34      console.error('Erreur lors de la création des index :', error);
35    } finally {
36      await queryRunner.release();
37    }
38  }

Un CREATE INDEX ordinaire bloque les écritures jusqu'à la fin de la construction ; CONCURRENTLY ne les bloque pas, mais ne fonctionne pas dans une transaction. INCLUDE ajoute des colonnes pour que la requête puisse tout lire dans l'index lui-même. En production, garde ces commandes dans une migration TypeORM avec transaction = false (cela exige migrationsTransactionMode: 'each'), pas dans un service.

Des requêtes qui touchent l'index

Un index n'aide qu'une requête qui correspond à ses colonnes :

1  // Optimiser les requêtes qui utilisent des index
2  async getOptimizedTributeSearches() {
3    // Utilise l'index sur (type, value) et ne récupère que les colonnes utiles
4    const goldTributes = await this.tributeRepo
5      .createQueryBuilder('tribute')
6      .select(['tribute.id', 'tribute.name', 'tribute.value'])
7      .where('tribute.type = :type AND tribute.value > :minValue', {
8        type: 'GOLD',
9        minValue: 1000
10      })
11      .orderBy('tribute.value', 'DESC')
12      .limit(100)
13      .getMany();
14
15    // Utilise l'index partiel sur les tributs coûteux
16    const expensiveTributes = await this.tributeRepo
17      .createQueryBuilder('tribute')
18      .where('tribute.value > 10000')
19      .andWhere('tribute.collectedAt > :date', {
20        date: new Date('2023-01-01')
21      })
22      .getMany();
23
24    // Utilise l'index spatial : tributs à moins de 1000 m du Forum Romanum
25    const nearbyTributes = await this.dataSource.query(`
26      SELECT * FROM tributes
27      WHERE ST_DWithin(
28        coordinates,
29        ST_GeogFromText('SRID=4326;POINT(12.4853 41.8925)'),
30        1000
31      )
32    `);
33
34    return { goldTributes, expensiveTributes, nearbyTributes };
35  }
36}

select() dans le QueryBuilder prend des colonnes préfixées par l'alias (tribute.name) et remplace SELECT *. Le nombre 10000 est écrit en dur, car la documentation de PostgreSQL prévient qu'une condition avec paramètre ne correspondra pas à un index partiel ; dans le test, le plan a utilisé idx_expensive_tributes. Pour le type geography, la distance dans ST_DWithin s'exprime en mètres.

Un processus simple met de l'ordre dans le travail sur les index :

1. trouve les requêtes lentes dans le slow query log (dans PostgreSQL : log_min_duration_statement),
2. analyse le plan avec EXPLAIN,
3. ajoute un index ou réécris la requête,
4. mesure l'amélioration.

Full-text search pour les descriptions des tributs

PostgreSQL stocke le texte destiné à la recherche dans le type tsvector, et un index GIN y retrouve les mots :

1// full-text-search.service.ts
2@Injectable()
3export class FullTextSearchService {
4  constructor(
5    @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
6    @InjectDataSource() private dataSource: DataSource,
7  ) {}
8
9  async setupFullTextSearch(): Promise<void> {
10    const queryRunner = this.dataSource.createQueryRunner();
11
12    try {
13      // PostgreSQL full-text search
14      await queryRunner.query(`
15        ALTER TABLE tributes
16        ADD COLUMN IF NOT EXISTS search_vector tsvector
17      `);
18
19      await queryRunner.query(`
20        UPDATE tributes
21        SET search_vector = to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
22      `);
23
24      await queryRunner.query(`
25        CREATE INDEX IF NOT EXISTS idx_tribute_search
26        ON tributes USING gin(search_vector)
27      `);
28
29      // Trigger pour la mise à jour automatique
30      await queryRunner.query(`
31        CREATE OR REPLACE FUNCTION update_tribute_search_vector()
32        RETURNS TRIGGER AS $$
33        BEGIN
34          NEW.search_vector := to_tsvector('english', coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, ''));
35          RETURN NEW;
36        END;
37        $$ LANGUAGE plpgsql;
38      `);
39
40      await queryRunner.query(`
41        DROP TRIGGER IF EXISTS tribute_search_vector_update ON tributes;
42        CREATE TRIGGER tribute_search_vector_update
43        BEFORE INSERT OR UPDATE ON tributes
44        FOR EACH ROW EXECUTE FUNCTION update_tribute_search_vector();
45      `);
46
47      console.log('Full-text search configuré !');
48    } catch (error) {
49      console.error('Erreur de configuration du full-text search :', error);
50    } finally {
51      await queryRunner.release();
52    }
53  }

coalesce() est une correction : concaténer du texte avec NULL donne NULL, donc sans elle, 21 tributs sans description disparaissaient de la recherche dans le test. Depuis PostgreSQL 12, tu peux utiliser une colonne générée au lieu d'un trigger, et TypeORM 1.x déclare un index GIN dans l'entité avec @Index({ type: 'gin' }).

La recherche elle-même trie les résultats par pertinence :

1  async searchTributes(searchTerm: string): Promise<any[]> {
2    // Full-text search PostgreSQL avec classement
3    const results = await this.dataSource.query(`
4      SELECT
5        t.*,
6        ts_rank(search_vector, plainto_tsquery('english', $1)) as rank
7      FROM tributes t
8      WHERE search_vector @@ plainto_tsquery('english', $1)
9      ORDER BY rank DESC, value DESC
10      LIMIT 50
11    `, [searchTerm]);
12
13    return results;
14  }

plainto_tsquery() transforme des mots ordinaires en requête, l'opérateur @@ vérifie la correspondance, et ts_rank() calcule la pertinence. Le texte de l'utilisateur passe en paramètre $1, jamais par concaténation.

La version avec le QueryBuilder combine le texte et les filtres :

1  async advancedSearch(query: {
2    text?: string;
3    type?: string;
4    minValue?: number;
5    location?: string;
6    dateRange?: { start: Date; end: Date };
7  }): Promise<{ entities: Tribute[]; raw: any[] }> {
8    const qb = this.tributeRepo.createQueryBuilder('tribute');
9
10    // Full-text search condition
11    if (query.text) {
12      qb.addSelect(`ts_rank(tribute.search_vector, plainto_tsquery('english', :searchText))`, 'rank')
13        .where("tribute.search_vector @@ plainto_tsquery('english', :searchText)", {
14          searchText: query.text
15        });
16    }
17
18    // Filtres sur des colonnes indexées
19    if (query.type) {
20      qb.andWhere('tribute.type = :type', { type: query.type });
21    }
22
23    if (query.minValue) {
24      qb.andWhere('tribute.value >= :minValue', { minValue: query.minValue });
25    }
26
27    // Un LIKE commençant par % ne peut pas utiliser un index B-tree ordinaire
28    if (query.location) {
29      qb.andWhere('tribute.location LIKE :location', {
30        location: `%${query.location}%`
31      });
32    }
33
34    if (query.dateRange) {
35      qb.andWhere('tribute.collectedAt BETWEEN :start AND :end', {
36        start: query.dateRange.start,
37        end: query.dateRange.end
38      });
39    }
40
41    // Tri : d'abord la pertinence, puis la valeur
42    if (query.text) {
43      qb.orderBy('rank', 'DESC').addOrderBy('tribute.value', 'DESC');
44    } else {
45      qb.orderBy('tribute.value', 'DESC');
46    }
47
48    return await qb.limit(100).getRawAndEntities();
49  }
50}

La version précédente avait la condition entre apostrophes, coupées par le mot 'english', donc le code ne compilait pas : d'où les guillemets doubles. getRawAndEntities() renvoie un objet { entities, raw }, et la pertinence se trouve dans raw.

Le pool de connexions

Ouvrir une connexion à la base coûte cher, alors TypeORM garde un pool de connexions prêtes :

1// app.module.ts - le pool de connexions
2TypeOrmModule.forRoot({
3  type: 'postgres',
4  url: process.env.DATABASE_URL,
5  autoLoadEntities: true,
6  poolSize: 20, // nombre maximal de connexions dans le pool
7}),

Par défaut, le pilote pg ouvre jusqu'à 10 connexions. Tu ajustes la taille au nombre d'utilisateurs simultanés et à la capacité de la base : PostgreSQL accepte par défaut 100 connexions, et cinq instances avec un pool de 20 les prennent toutes.

L'échelle d'optimisation

De la plus simple à la plus complexe :

1. des index sur les colonnes les plus recherchées - une ligne dans l'entité,
2. select au lieu de SELECT * - un changement dans les requêtes,
3. la pagination et le lazy loading - un changement du contrat de l'API,
4. les read replicas et le sharding - un changement d'infrastructure.

Ne monte à l'échelon suivant que lorsque le précédent ne suffit plus - je te recommande cette patience. Dans la prochaine leçon, nous répartirons le trafic entre plusieurs instances de l'application.

Retiens : un index est une carte - sans elle, tu cherches un tribut dans chaque coffre, avec elle, tu sais tout de suite lequel ouvrir.

1// Database Indexing - Cartes pour une recherche plus rapide
2// Les index accélèrent les requêtes dans la base de données
3
4// 1. Index dans Mongoose (MongoDB)
5import { Schema, Prop, SchemaFactory } from '@nestjs/mongoose';
6import { Document } from 'mongoose';
7
8@Schema()
9export class Soldier extends Document {
10  // Index sur un seul champ
11  @Prop({ index: true })
12  rank: string;
13
14  @Prop()
15  name: string;
16
17  // Index sur un champ avec unicité
18  @Prop({ unique: true })
19  registrationNumber: string;
20
21  // Index sur un champ souvent recherché
22  @Prop({ index: true })
23  legionId: string;
24
25  @Prop()
26  province: string;
27
28  @Prop({ default: Date.now })
29  enrolledAt: Date;
30}
31
32export const SoldierSchema = SchemaFactory.createForClass(Soldier);
33
34// 2. Index composé (compound index)
35SoldierSchema.index({ legionId: 1, rank: 1 });
36
37// 3. Index textuel (pour la recherche full-text)
38SoldierSchema.index({ name: 'text', province: 'text' });
39
40// 4. Index TTL (suppression automatique après un délai)
41// SoldierSchema.index({ enrolledAt: 1 }, { expireAfterSeconds: 86400 });
42
43// 5. Optimisation des requêtes
44
45// SANS index (Collection Scan - O(n)) :
46// db.soldiers.find({ rank: 'centurion' })
47// Parcourt CHAQUE document de la collection !
48
49// AVEC index (Index Scan - O(log n)) :
50// db.soldiers.find({ rank: 'centurion' })
51// Utilise l'index pour trouver rapidement !
52
53// 6. Explain - analyse des requêtes
54// const result = await SoldierModel
55//   .find({ rank: 'centurion' })
56//   .explain('executionStats');
57// console.log(result.executionStats.totalDocsExamined);
58// console.log(result.executionStats.executionTimeMillis);
59
60// 7. Projection - sélectionner uniquement les champs nécessaires
61// LENT (charge tout le document) :
62// await SoldierModel.find({ legionId: '1' });
63//
64// RAPIDE (charge uniquement les champs nécessaires) :
65// await SoldierModel
66//   .find({ legionId: '1' })
67//   .select('name rank -_id');
68
69// 8. Règles d'indexation :
70// - Indexer les champs utilisés dans WHERE/find
71// - Indexer les champs utilisés dans ORDER BY/sort
72// - Ne pas indexer les champs rarement recherchés
73// - Les index consomment de la mémoire - ne pas en abuser !
74// - Surveiller les performances avec explain()
75