Kurs NestJS · Moduł 8: Cache i wydajność

Database Indexing - mapy do szybszego wyszukiwania

9 min czytania ·
W tej lekcji9

Kartografowie! Konsul Caesar.js zauważył, że wyszukiwanie tributów w naszych dziennikach trwa coraz dłużej: przy dwudziestu tysiącach wpisów baza czyta całą tabelę, żeby znaleźć jeden. Czas stworzyć indeksy - specjalne mapy, które pozwolą błyskawicznie odnaleźć każdy tribut.

Czym są indeksy w świecie legionariuszy?

Wyobraź sobie tysiące map tributów w wielkiej skrzyni. Bez organizacji:

  • każde wyszukiwanie to przejrzenie wszystkich map po kolei,
  • znalezienie tributu może zająć godziny,
  • im więcej map, tym dłuższe wyszukiwanie.

Indeks działa jak katalog biblioteczny. Ma jednak cenę: przyspiesza odczyty, ale spowalnia zapisy, bo przy każdym INSERT i UPDATE baza aktualizuje też katalog, który zajmuje miejsce na dysku. Indeksujesz więc kolumny, po których naprawdę szukasz.

Podstawowe indeksy w TypeORM

W TypeORM indeksy deklaruje dekorator @Index(): na polu tworzy indeks jednej kolumny, na klasie - indeks złożony z kilku kolumn. Oto encja dla PostgreSQL:

// tribute.entity.ts
import { Entity, Column, Index, PrimaryGeneratedColumn } from 'typeorm';
import type { Point } from 'typeorm';

@Entity('tributes')
@Index(['location', 'collectedAt']) // Composite index
@Index(['value'], { where: 'value > 1000' }) // Partial index
export class Tribute {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  @Index() // Simple index na name
  name: string;

  @Column('decimal')
  @Index('idx_tribute_value') // Named index
  value: number;

  @Column()
  @Index() // Index na location dla filtrowania po prowincji
  location: string;

  @Column({ type: 'enum', enum: ['GOLD', 'SILVER', 'GEMS'] })
  @Index() // Index na type dla filtering
  type: string;

  @Column({ name: 'collected_at', type: 'timestamp' })
  @Index() // Index na timestamp dla date ranges
  collectedAt: Date;

  @Column({ unique: true })
  serialNumber: string; // Automatyczny unique index

  @Column('text', { nullable: true })
  description: string;

  @Column({ type: 'geography', spatialFeatureType: 'Point', srid: 4326 })
  @Index({ spatial: true }) // Indeks GiST dla zapytań geograficznych (PostGIS)
  coordinates: Point;
}

Indeks częściowy z opcją where obejmuje tylko drogie tributy, więc jest mniejszy. unique: true tworzy ograniczenie unikalności, za którym stoi indeks. Kolumna collected_at ma jawną nazwę, żeby surowy SQL i encja mówiły o tym samym. Point to typ GeoJSON z TypeORM, a geography wymaga rozszerzenia PostGIS. Uwaga: sterownik PostgreSQL zwraca decimal jako tekst, żeby nie gubić precyzji.

Indeksy w innych bazach

Pierwsza wersja tej encji miała też indeks fulltext, ale TypeORM obsługuje go tylko w MySQL. Tam wygląda tak:

// Wariant dla MySQL - indeks pełnotekstowy
@Column('text')
@Index({ fulltext: true })
description: string;

W PostgreSQL wyszukiwanie pełnotekstowe robimy inaczej - zobaczysz to niżej. W MongoDB z Mongoose, jak w edytorze obok, indeks opisujesz w schemacie:

// soldier.schema.ts - MongoDB (Mongoose)
@Schema()
export class Soldier {
  @Prop({ index: true })
  rank: string;

  @Prop({ unique: true })
  registrationNumber: string;
}

export const SoldierSchema = SchemaFactory.createForClass(Soldier);
SoldierSchema.index({ legionId: 1, enrolledAt: -1 }); // indeks złożony

Idea się nie zmienia: pojedyncze pole, unikalność i indeks złożony, tylko zapis jest inny.

Plan wykonania

Czy baza korzysta z indeksu, powie EXPLAIN, który pokazuje plan zapytania. EXPLAIN ANALYZE dodatkowo wykonuje zapytanie i podaje prawdziwe czasy:

// indexing-strategy.service.ts
@Injectable()
export class IndexingStrategyService {
  constructor(
    @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
    @InjectDataSource() private dataSource: DataSource,
  ) {}

  // Sprawdzenie wykorzystania indeksów
  async analyzeIndexUsage(): Promise<any> {
    const queries = [
      // Sprawdź plan wykonania zapytania
      `EXPLAIN SELECT * FROM tributes WHERE type = 'GOLD' AND value > 1000`,
      `EXPLAIN SELECT * FROM tributes WHERE location LIKE 'Hispania%'`,
      `EXPLAIN ANALYZE SELECT * FROM tributes WHERE collected_at BETWEEN '2023-01-01' AND '2023-12-31'`,
    ];

    const results = [];
    for (const query of queries) {
      const plan = await this.dataSource.query(query);
      results.push({ query, plan });
    }

    return results;
  }

Na 20 000 wierszach pierwsze zapytanie dostało Bitmap Index Scan, ale drugie - Seq Scan: przy lokalizacji innej niż C indeks B-tree obsłuży LIKE 'Hispania%' dopiero z klasą operatorów text_pattern_ops. Trzecie też czytało całą tabelę, bo cały rok to połowa danych, a wtedy przegląd całości jest tańszy.

Indeksy tworzone świadomie

Indeksy pod konkretne zapytania można założyć ręcznie:

  // Tworzenie indeksów dynamicznych
  async createPerformanceIndexes(): Promise<void> {
    const queryRunner = this.dataSource.createQueryRunner();

    try {
      // Index dla częstych wyszukiwań według wartości i typu
      await queryRunner.query(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_type_value
        ON tributes(type, value DESC)
      `);

      // Partial index tylko dla drogich tributów
      await queryRunner.query(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_expensive_tributes
        ON tributes(collected_at)
        WHERE value > 10000
      `);

      // Composite index dla zapytań po prowincji i czasie
      await queryRunner.query(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_location_time
        ON tributes(location, collected_at)
      `);

      // Covering index (zawiera wszystkie potrzebne kolumny)
      await queryRunner.query(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_summary
        ON tributes(type, location)
        INCLUDE (name, value, collected_at)
      `);

      console.log('Indeksy wydajnościowe utworzone!');
    } catch (error) {
      console.error('Błąd podczas tworzenia indeksów:', error);
    } finally {
      await queryRunner.release();
    }
  }

Zwykłe CREATE INDEX blokuje zapisy do końca budowy; CONCURRENTLY ich nie blokuje, ale nie działa w transakcji. INCLUDE dokłada kolumny, dzięki którym zapytanie odczyta wszystko z samego indeksu. Na produkcji takie polecenia trzymaj w migracji TypeORM z transaction = false (wymaga trybu migrationsTransactionMode: 'each'), a nie w serwisie.

Zapytania, które trafiają w indeks

Indeks pomaga tylko zapytaniu, które pasuje do jego kolumn:

  // Optymalizacja zapytań używających indeksów
  async getOptimizedTributeSearches() {
    // Wykorzystuje index na (type, value) i pobiera tylko potrzebne kolumny
    const goldTributes = await this.tributeRepo
      .createQueryBuilder('tribute')
      .select(['tribute.id', 'tribute.name', 'tribute.value'])
      .where('tribute.type = :type AND tribute.value > :minValue', {
        type: 'GOLD',
        minValue: 1000
      })
      .orderBy('tribute.value', 'DESC')
      .limit(100)
      .getMany();

    // Wykorzystuje partial index na expensive tributes
    const expensiveTributes = await this.tributeRepo
      .createQueryBuilder('tribute')
      .where('tribute.value > 10000')
      .andWhere('tribute.collectedAt > :date', {
        date: new Date('2023-01-01')
      })
      .getMany();

    // Wykorzystuje spatial index: tributy do 1000 m od Forum Romanum
    const nearbyTributes = await this.dataSource.query(`
      SELECT * FROM tributes
      WHERE ST_DWithin(
        coordinates,
        ST_GeogFromText('SRID=4326;POINT(12.4853 41.8925)'),
        1000
      )
    `);

    return { goldTributes, expensiveTributes, nearbyTributes };
  }
}

select() w QueryBuilderze przyjmuje kolumny z aliasem (tribute.name) i zastępuje SELECT *. Liczba 10000 jest wpisana wprost, bo dokumentacja PostgreSQL ostrzega, że warunek z parametrem nie dopasuje indeksu częściowego; w teście plan użył idx_expensive_tributes. Dla typu geography odległość w ST_DWithin podajemy w metrach.

Pracę z indeksami porządkuje prosty proces:

  1. znajdź wolne zapytania w slow query logu (w PostgreSQL: log_min_duration_statement),
  2. przeanalizuj plan przez EXPLAIN,
  3. dodaj indeks albo przepisz zapytanie,
  4. zmierz poprawę.

Full-text Search dla opisów tributów

PostgreSQL przechowuje przeszukiwalny tekst w typie tsvector, a indeks GIN znajduje w nim słowa:

// full-text-search.service.ts
@Injectable()
export class FullTextSearchService {
  constructor(
    @InjectRepository(Tribute) private tributeRepo: Repository<Tribute>,
    @InjectDataSource() private dataSource: DataSource,
  ) {}

  async setupFullTextSearch(): Promise<void> {
    const queryRunner = this.dataSource.createQueryRunner();

    try {
      // PostgreSQL full-text search
      await queryRunner.query(`
        ALTER TABLE tributes
        ADD COLUMN IF NOT EXISTS search_vector tsvector
      `);

      await queryRunner.query(`
        UPDATE tributes
        SET search_vector = to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
      `);

      await queryRunner.query(`
        CREATE INDEX IF NOT EXISTS idx_tribute_search
        ON tributes USING gin(search_vector)
      `);

      // Trigger dla automatycznej aktualizacji
      await queryRunner.query(`
        CREATE OR REPLACE FUNCTION update_tribute_search_vector()
        RETURNS TRIGGER AS $$
        BEGIN
          NEW.search_vector := to_tsvector('english', coalesce(NEW.name, '') || ' ' || coalesce(NEW.description, ''));
          RETURN NEW;
        END;
        $$ LANGUAGE plpgsql;
      `);

      await queryRunner.query(`
        DROP TRIGGER IF EXISTS tribute_search_vector_update ON tributes;
        CREATE TRIGGER tribute_search_vector_update
        BEFORE INSERT OR UPDATE ON tributes
        FOR EACH ROW EXECUTE FUNCTION update_tribute_search_vector();
      `);

      console.log('Full-text search skonfigurowany!');
    } catch (error) {
      console.error('Błąd konfiguracji full-text search:', error);
    } finally {
      await queryRunner.release();
    }
  }

coalesce() to poprawka: sklejenie tekstu z NULL daje NULL, więc bez niej w teście 21 tributów bez opisu wypadło z wyszukiwania. Od PostgreSQL 12 zamiast wyzwalacza możesz użyć kolumny generowanej, a TypeORM 1.x zadeklaruje indeks GIN w encji przez @Index({ type: 'gin' }).

Samo wyszukiwanie sortuje wyniki według trafności:

  async searchTributes(searchTerm: string): Promise<any[]> {
    // PostgreSQL full-text search z ranking
    const results = await this.dataSource.query(`
      SELECT
        t.*,
        ts_rank(search_vector, plainto_tsquery('english', $1)) as rank
      FROM tributes t
      WHERE search_vector @@ plainto_tsquery('english', $1)
      ORDER BY rank DESC, value DESC
      LIMIT 50
    `, [searchTerm]);

    return results;
  }

plainto_tsquery() zamienia zwykłe słowa w zapytanie, operator @@ sprawdza dopasowanie, a ts_rank() liczy trafność. Tekst użytkownika idzie jako parametr $1, nigdy przez sklejanie.

Wersja z QueryBuilderem łączy tekst z filtrami:

  async advancedSearch(query: {
    text?: string;
    type?: string;
    minValue?: number;
    location?: string;
    dateRange?: { start: Date; end: Date };
  }): Promise<{ entities: Tribute[]; raw: any[] }> {
    const qb = this.tributeRepo.createQueryBuilder('tribute');

    // Full-text search condition
    if (query.text) {
      qb.addSelect(`ts_rank(tribute.search_vector, plainto_tsquery('english', :searchText))`, 'rank')
        .where("tribute.search_vector @@ plainto_tsquery('english', :searchText)", {
          searchText: query.text
        });
    }

    // Filtry na kolumnach z indeksami
    if (query.type) {
      qb.andWhere('tribute.type = :type', { type: query.type });
    }

    if (query.minValue) {
      qb.andWhere('tribute.value >= :minValue', { minValue: query.minValue });
    }

    // LIKE z % na początku nie skorzysta ze zwykłego indeksu B-tree
    if (query.location) {
      qb.andWhere('tribute.location LIKE :location', {
        location: `%${query.location}%`
      });
    }

    if (query.dateRange) {
      qb.andWhere('tribute.collectedAt BETWEEN :start AND :end', {
        start: query.dateRange.start,
        end: query.dateRange.end
      });
    }

    // Sortowanie: najpierw relevance, potem wartość
    if (query.text) {
      qb.orderBy('rank', 'DESC').addOrderBy('tribute.value', 'DESC');
    } else {
      qb.orderBy('tribute.value', 'DESC');
    }

    return await qb.limit(100).getRawAndEntities();
  }
}

Poprzednia wersja miała warunek w apostrofach, które przecinało słowo 'english', więc kod się nie kompilował - stąd cudzysłów. getRawAndEntities() zwraca obiekt { entities, raw }, a trafność leży w raw.

Pula połączeń

Otwarcie połączenia z bazą kosztuje, więc TypeORM trzyma pulę gotowych połączeń:

// app.module.ts - pula połączeń
TypeOrmModule.forRoot({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  autoLoadEntities: true,
  poolSize: 20, // maksymalna liczba połączeń w puli
}),

Domyślnie sterownik pg otwiera do 10 połączeń. Rozmiar dostrajasz do liczby równoczesnych użytkowników i możliwości bazy: PostgreSQL domyślnie przyjmuje 100 połączeń, a pięć instancji z pulą 20 zabierze je wszystkie.

Drabina optymalizacji

Od najprostszej do najbardziej złożonej:

  1. indeksy na najczęściej wyszukiwanych kolumnach - jedna linijka w encji,
  2. select zamiast SELECT * - zmiana w zapytaniach,
  3. paginacja i lazy loading - zmiana kontraktu API,
  4. read replicas i sharding - zmiana infrastruktury.

Wchodź na kolejny szczebel dopiero wtedy, gdy poprzedni nie wystarcza - polecam Ci tę cierpliwość. W następnej lekcji rozłożymy ruch między kilka instancji aplikacji.

Pamiętaj: indeks to mapa - bez niej szukasz tributu w każdej skrzyni, z nią od razu wiesz, którą otworzyć.

Kod do tej lekcji: src/database-indexing.ts
// Database Indexing - Mapy do Szybszego Wyszukiwania
// Indeksy przyspieszaja zapytania w bazie danych

// 1. Indeksy w Mongoose (MongoDB)
import { Schema, Prop, SchemaFactory } from '@nestjs/mongoose';
import { Document } from 'mongoose';

@Schema()
export class Soldier extends Document {
  // Indeks na pojedynczym polu
  @Prop({ index: true })
  rank: string;

  @Prop()
  name: string;

  // Indeks na polu z unikalnosccia
  @Prop({ unique: true })
  registrationNumber: string;

  // Indeks na polu czesto wyszukiwanym
  @Prop({ index: true })
  legionId: string;

  @Prop()
  province: string;

  @Prop({ default: Date.now })
  enrolledAt: Date;
}

export const SoldierSchema = SchemaFactory.createForClass(Soldier);

// 2. Indeks zlozony (compound index)
SoldierSchema.index({ legionId: 1, rank: 1 });

// 3. Indeks tekstowy (do wyszukiwania full-text)
SoldierSchema.index({ name: 'text', province: 'text' });

// 4. Indeks TTL (automatyczne usuwanie po czasie)
// SoldierSchema.index({ enrolledAt: 1 }, { expireAfterSeconds: 86400 });

// 5. Optymalizacja zapytan

// BEZ indeksu (Collection Scan - O(n)):
// db.soldiers.find({ rank: 'centurion' })
// Przeszukuje KAZDY dokument w kolekcji!

// Z indeksem (Index Scan - O(log n)):
// db.soldiers.find({ rank: 'centurion' })
// Uzywa indeksu do szybkiego znalezienia!

// 6. Explain - analiza zapytan
// const result = await SoldierModel
//   .find({ rank: 'centurion' })
//   .explain('executionStats');
// console.log(result.executionStats.totalDocsExamined);
// console.log(result.executionStats.executionTimeMillis);

// 7. Projection - wybierz tylko potrzebne pola
// WOLNO (laduje caly dokument):
// await SoldierModel.find({ legionId: '1' });
//
// SZYBKO (laduje tylko potrzebne pola):
// await SoldierModel
//   .find({ legionId: '1' })
//   .select('name rank -_id');

// 8. Zasady indeksowania:
// - Indeksuj pola uzywane w WHERE/find
// - Indeksuj pola uzywane w ORDER BY/sort
// - Nie indeksuj pol rzadko wyszukiwanych
// - Indeksy zajmuja pamiec - nie przesadzaj!
// - Monitoruj wydajnosc explain()

Hasła z dokumentacji w tej lekcji

Widzisz błąd w tej lekcji?

Sprawdź się

Odpowiedz na pytania z tej lekcji. Wybierz odpowiedź, a od razu zobaczysz, czy jest poprawna.

  1. 1. Indeksy w bazie danych:

  2. 2. Connection pool size w bazie danych powinien:

Zadania praktyczne w grze

  • Edytor kodu

    Dodaj indeksy i użyj select() zamiast pobierania wszystkich kolumn

  • Klikanie w kolejności

    Ułóż proces optymalizacji zapytań bazy danych:

  • Układanie w pionie

    Ułóż techniki od najprostszej do najzłożonej optymalizacji DB:

  1. PytanieIndeksy w bazie danych
  2. PytanieConnection pooling
  3. Zadanie z kodemPraktyka: Query optimization
  4. Kliknij w kolejnościOptymalizacja zapytania DB
  5. Ułóż w kolejnościEtapy optymalizacji bazy

Przydatne artykuły