Problem

Mağazanın orders cədvəli böyüyüb və "mənim sifarişlərim" səhifəsi yavaşlayıb. Sorğu sadədir:

SELECT id, total, created_atFROM ordersWHERE customer_id = 42ORDER BY created_at DESCLIMIT 20;

Sorğunu heç kim dəyişməyib. Sadəcə, data bazanın sətirləri tapma üsulunu üstələyib. Uyğun indeks olmadıqda PostgreSQL-in yalnız bir yolu qalır: cədvəlin bütün sətirlərini oxuyub şərti yoxlamaq. Bu, sequential scan-dır və onun qiyməti cavabın yox, cədvəlin ölçüsü ilə artır.

B-tree indeksinin ideyası

PostgreSQL-də default indeks tipi B-tree-dir. Onu bir və ya bir neçə sütunun sıralanmış nüsxəsi kimi təsəvvür edin, dayaz ağac şəklində təşkil olunub. Hər qeyddə indekslənmiş dəyər və cədvəldəki sətrə göstərici var. customer_id = 42 tapmaq üçün baza kökdən bir neçə səviyyə aşağı enir və uyğun qeydlərə çatır, əlaqəsiz sətirlərə baxmır. Qeydlər sıralı olduğundan eyni struktur aralıq şərtlərini (created_at >= ...), ORDER BY və min/max sorğularını da ucuz cavablandırır.

İndeksi yaratmaq bir əmrdir:

CREATE INDEX idx_orders_customer ON orders (customer_id);

Yük altında olan cədvəldə CREATE INDEX CONCURRENTLY qurulma zamanı yazmaları bloklamır; əvəzində qurulma yavaş gedir və transaction bloku daxilində işləmir.

EXPLAIN-i oxumaq

EXPLAIN planlaşdırıcının seçdiyi planı göstərir; EXPLAIN (ANALYZE, BUFFERS) sorğunu həqiqətən icra edir və real nəticəni bildirir. İndeksdən əvvəl plan belə görünür (rəqəmlər qəsdən buraxılıb):

Limit
  -> Sort  (Sort Key: created_at DESC)
       -> Seq Scan on orders
            Filter: (customer_id = 42)
            Rows Removed by Filter: (çox sayda)

Əsas sətirlər node adları və Rows Removed by Filter-dir. Sətirlərin çoxunu atan Seq Scan indeksin olmadığının tipik siqnalıdır. customer_id üzrə indeks yaradıldıqdan sonra plan təxminən belə dəyişir:

Limit
  -> Sort  (Sort Key: created_at DESC)
       -> Bitmap Heap Scan on orders
            Recheck Cond: (customer_id = 42)
            -> Bitmap Index Scan on idx_orders_customer

Bitmap Index Scan uyğun sətirlərin yerini toplayır, Bitmap Heap Scan isə cədvəl səhifələrini fiziki sırada ziyarət edir. Çox seçici şərtlərdə sadə Index Scan görəcəksiniz. Planlaşdırıcı cədvəl statistikasına əsasən qərar verir, ona görə təxminlər səhv görünürsə ANALYZE işlədin (adətən autovacuum bunu edir). ANALYZE nəticəsində təxmini rows= ilə real sətir sayını müqayisə edin; böyük fərq köhnəlmiş statistikaya və ya əlaqəli sütunlara işarədir.

Composite indeks və sütun sırası

Sorğumuz customer_id üzrə süzür və created_at üzrə sıralayır. Bir indeks hər ikisinə xidmət edə bilər:

CREATE INDEX idx_orders_customer_created    ON orders (customer_id, created_at DESC);

İndi baza customer_id = 42 nöqtəsinə enir və artıq created_at üzrə sıralanmış qeydləri oxuyur. Sort node-u yox olur, LIMIT 20 isə iyirmi qeyddən sonra dayana bilir. Plan birbaşa Limit altında Index Scan olur.

Sütun sırası kosmetik məsələ deyil. Composite indeks əvvəlcə birinci sütuna, sonra hər birinci sütun dəyəri daxilində ikinciyə görə sıralanır. Təcrübi qayda:

  1. Bərabərlik ilə müqayisə olunan sütunları əvvələ qoyun.
  2. Sonra aralıq şərti və ya sıralama üçün istifadə olunan sütunu.
  3. İndeks sütunlarının sol prefiksini istifadə edən sorğulara kömək edir. (customer_id, created_at) indeksi WHERE customer_id = ? üçün faydalıdır, amma təkcə WHERE created_at > ? üçün faydasızdır.

Covering indekslər

Sorğunun lazım olan bütün sütunları indeksin içindədirsə, PostgreSQL cədvələ toxunmadan yalnız indeksdən Index Only Scan ilə cavab verə bilər. Axtarış açarının hissəsi olmayan sütunları əlavə etmək üçün INCLUDE istifadə edin:

CREATE INDEX idx_orders_customer_covering    ON orders (customer_id, created_at DESC) INCLUDE (total);

Index-only scan yenə visibility map-a baxır, ona görə müntəzəm vacuum edilən cədvəllərdə ən yaxşı işləyir. Geniş sütunları sadəcə mümkün olduğu üçün daxil etməyin: indeks böyüyür və saxlanması yavaşlayır.

İndeks nə zaman zərər verir

Hər indeks əlavə datadır və hər INSERT, DELETE və indekslənmiş sütunun UPDATE-i zamanı yenilənməlidir. Çox indeks yazmaların yavaşlaması, daha çox disk və yaddaş, vacuum üçün isə daha çox iş deməkdir. İndeksin kömək etmədiyi digər hallar:

  • Aşağı seçicilik: sətirlərin yarısının uyğun gəldiyi boolean sütun. Planlaşdırıcı haqlı olaraq sequential scan seçəcək.
  • Sütun üzərində funksiya: WHERE lower(email) = ... email indeksindən istifadə edə bilməz. Bunun əvəzinə lower(email) üzrə expression indeksi yaradın.
  • Gizli tip çevirmələri və LIKE '%abc' kimi öndə wildcard.
  • Kiçik cədvəllər: hamısını oxumaq onsuz da ucuzdur.

Yalnız alt çoxluq vacibdirsə, partial indeks xərci azaldır, məsələn CREATE INDEX ... ON orders (customer_id) WHERE status = 'OPEN'.

Tipik səhvlər

  • "Ehtiyat üçün" hər sütuna indeks əlavə etmək.
  • Sorğuya uyğun tək composite indeks əvəzinə filtrin hər sütununa ayrıca indeks yaratmaq.
  • Planı boş development bazasında oxumaq; kiçik cədvəllərdə planlaşdırıcı fərqli seçir.
  • ANALYZE-in sorğunu icra etdiyini unutmaq: UPDATE və ya DELETE-i geri qaytaracağınız transaction-a bükün.
  • İstifadə olunmayan indeksləri nəzərə almamaq. pg_stat_user_indexes görünüşü hər indeksin neçə dəfə oxunduğunu göstərir.

İndeksi necə yoxlamalı

İndeks yaratdıqdan sonra işi bitmiş saymayın. Eyni sorğunu yenidən EXPLAIN (ANALYZE, BUFFERS) ilə işlədin və üç şeyə baxın: node adı dəyişibmi (Seq Scan əvəzinə Index Scan və ya Bitmap Heap Scan), Sort node-u yox olubmu və oxunan buffer sayı azalıbmı. Planlaşdırıcı indeksi görməzdən gəlirsə, səbəb adətən ya aşağı seçicilik, ya sütun üzərində funksiya, ya da köhnə statistikadır.

Sınaq üçün realist həcmdə data lazımdır. Yüz sətirlik cədvəldə planlaşdırıcı hər zaman sequential scan seçəcək və indeksin faydasını görməyəcəksiniz. Staging bazasında və ya generasiya olunmuş data ilə yoxlayın. Eyni zamanda yazma tərəfini də ölçün: eyni cədvələ edilən insert və update-lərin indeksdən sonra qəbuledilməz dərəcədə yavaşlamadığına əmin olun.

Real həyatdan nümunə düşüncəsi

Tutaq ki, status = 'OPEN' olan sifarişlər cədvəlin kiçik hissəsidir və əməliyyat paneli yalnız onları göstərir. Bütün sətirləri əhatə edən böyük indeks əvəzinə, yuxarıda göstərilən partial indeks həm kiçik, həm də sürətli olur: o yalnız açıq sifarişləri saxlayır və bağlanan sifariş indeksdən çıxır. Bu cür dizayn indeksi sorğunun real ehtiyacına uyğunlaşdırır. Buna görə də indeksi cədvəlin deyil, sorğuların xüsusiyyəti kimi düşünün: əvvəl ən vacib sorğuları siyahıya alın, sonra onları ən az indekslə qarşılayın.

Bir daha xatırladaq: indeks sorğunu sürətləndirmək üçün vasitədir, məqsəd deyil. Əvvəl ölçün, sonra dəyişin, sonra yenidən ölçün. Bu üç addım indeks haqqında bir çox mübahisəni təxminlərdən faktlara köçürür və komandada qərarların izlənə bilməsini təmin edir.

Yoxlama siyahısı

  • Təxminlərdən yox, yavaş sorğudan və onun EXPLAIN (ANALYZE, BUFFERS) nəticəsindən başlayın.
  • Composite indeksi əvvəlcə bərabərlik sütunları, sonra aralıq və ya sıralama sütunu ilə qurun.
  • Planın dəyişdiyini və cədvəldə yazmaların hələ də məqbul qaldığını yoxlayın.
  • İstifadə olunmayan və təkrarlanan indeksləri vaxtaşırı nəzərdən keçirin.
  • Miqrasiyaları təhlükəsiz saxlayın: böyük canlı cədvəllərdə CONCURRENTLY.

Növbəti addım

Bir çox "yavaş sorğu" indeksdən yox, ORM-dən gəlir; JPA-da N+1 problemi məqaləsinə baxın. Sorğu düzgün və indeksli olduğu halda yenə də ağırdırsa, növbəti vasitə caching strategiyalarıdır. SQL biliklərinizin harada olduğunu görmək üçün bacarıq qiymətləndirməsini keçin.