Database Indexing - mapy do szybszego wyszukiwania

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:

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() // Simple index na name
14  name: string;
15
16  @Column('decimal')
17  @Index('idx_tribute_value') // Named index
18  value: number;
19
20  @Column()
21  @Index() // Index na location dla filtrowania po prowincji
22  location: string;
23
24  @Column({ type: 'enum', enum: ['GOLD', 'SILVER', 'GEMS'] })
25  @Index() // Index na type dla filtering
26  type: string;
27
28  @Column({ name: 'collected_at', type: 'timestamp' })
29  @Index() // Index na timestamp dla date ranges
30  collectedAt: Date;
31
32  @Column({ unique: true })
33  serialNumber: string; // Automatyczny unique index
34
35  @Column('text', { nullable: true })
36  description: string;
37
38  @Column({ type: 'geography', spatialFeatureType: 'Point', srid: 4326 })
39  @Index({ spatial: true }) // Indeks GiST dla zapytań geograficznych (PostGIS)
40  coordinates: Point;
41}

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:

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

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

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 }); // 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:

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  // Sprawdzenie wykorzystania indeksów
10  async analyzeIndexUsage(): Promise<any> {
11    const queries = [
12      // Sprawdź plan wykonania zapytania
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  }

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:

1  // Tworzenie indeksów dynamicznych
2  async createPerformanceIndexes(): Promise<void> {
3    const queryRunner = this.dataSource.createQueryRunner();
4
5    try {
6      // Index dla częstych wyszukiwań według wartości i typu
7      await queryRunner.query(`
8        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tribute_type_value
9        ON tributes(type, value DESC)
10      `);
11
12      // Partial index tylko dla drogich tributów
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      // Composite index dla zapytań po prowincji i czasie
20      await queryRunner.query(`
21        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_location_time
22        ON tributes(location, collected_at)
23      `);
24
25      // Covering index (zawiera wszystkie potrzebne kolumny)
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('Indeksy wydajnościowe utworzone!');
33    } catch (error) {
34      console.error('Błąd podczas tworzenia indeksów:', error);
35    } finally {
36      await queryRunner.release();
37    }
38  }

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:

1  // Optymalizacja zapytań używających indeksów
2  async getOptimizedTributeSearches() {
3    // Wykorzystuje index na (type, value) i pobiera tylko potrzebne kolumny
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    // Wykorzystuje partial index na expensive tributes
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    // Wykorzystuje spatial index: tributy do 1000 m od 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() 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:

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 dla automatycznej aktualizacji
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 skonfigurowany!');
48    } catch (error) {
49      console.error('Błąd konfiguracji full-text search:', error);
50    } finally {
51      await queryRunner.release();
52    }
53  }

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:

1  async searchTributes(searchTerm: string): Promise<any[]> {
2    // PostgreSQL full-text search z ranking
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() 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:

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    // Filtry na kolumnach z indeksami
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    // LIKE z % na początku nie skorzysta ze zwykłego indeksu B-tree
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    // Sortowanie: najpierw relevance, potem wartość
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}

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ń:

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

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ć.

1// Database Indexing - Mapy do Szybszego Wyszukiwania
2// Indeksy przyspieszaja zapytania w bazie danych
3
4// 1. Indeksy w Mongoose (MongoDB)
5import { Schema, Prop, SchemaFactory } from '@nestjs/mongoose';
6import { Document } from 'mongoose';
7
8@Schema()
9export class Soldier extends Document {
10  // Indeks na pojedynczym polu
11  @Prop({ index: true })
12  rank: string;
13
14  @Prop()
15  name: string;
16
17  // Indeks na polu z unikalnosccia
18  @Prop({ unique: true })
19  registrationNumber: string;
20
21  // Indeks na polu czesto wyszukiwanym
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. Indeks zlozony (compound index)
35SoldierSchema.index({ legionId: 1, rank: 1 });
36
37// 3. Indeks tekstowy (do wyszukiwania full-text)
38SoldierSchema.index({ name: 'text', province: 'text' });
39
40// 4. Indeks TTL (automatyczne usuwanie po czasie)
41// SoldierSchema.index({ enrolledAt: 1 }, { expireAfterSeconds: 86400 });
42
43// 5. Optymalizacja zapytan
44
45// BEZ indeksu (Collection Scan - O(n)):
46// db.soldiers.find({ rank: 'centurion' })
47// Przeszukuje KAZDY dokument w kolekcji!
48
49// Z indeksem (Index Scan - O(log n)):
50// db.soldiers.find({ rank: 'centurion' })
51// Uzywa indeksu do szybkiego znalezienia!
52
53// 6. Explain - analiza zapytan
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 - wybierz tylko potrzebne pola
61// WOLNO (laduje caly dokument):
62// await SoldierModel.find({ legionId: '1' });
63//
64// SZYBKO (laduje tylko potrzebne pola):
65// await SoldierModel
66//   .find({ legionId: '1' })
67//   .select('name rank -_id');
68
69// 8. Zasady indeksowania:
70// - Indeksuj pola uzywane w WHERE/find
71// - Indeksuj pola uzywane w ORDER BY/sort
72// - Nie indeksuj pol rzadko wyszukiwanych
73// - Indeksy zajmuja pamiec - nie przesadzaj!
74// - Monitoruj wydajnosc explain()
75