Sorguların plan analizinə əsaslanan optimallaşdırılması: EXPLAIN ANALYZE

Budur həqiqət anı: SQL-sorgular sadəcə kod sətrləri deyil, verilənlər bazası ilə əsl dialoqdur. Əgər ona mehriban bir "SELECT *" pıçıldasan, baza yəqin ki, səni düzgün başa düşəcək və əmri problemsiz yerinə yetirəcək. Amma əgər ona struktursuz bir SQL-romanı təqdim etsən, baza bir az düşünəcək... və sonra yavaşımağa başlayacaq.

Sorgu optimallaşdırması — bazayla aydın və qısa dildə danışmaq bacarığıdır. Sorgu dəqiq və effektiv yazılanda, tez işləyir, sistemi yükləmir və digər proseslərin işinə mane olmur. Amma uğursuz yazılmış sorgu bütün sistemi ləngidə bilər: baza daha çox prosessor və yaddaş istifadə edəcək, disk alt sistemi artıq oxuma və yazmalarla məşğul olacaq, hətta bazadan istifadə edən tətbiqlər də yavaşıyacaq.

EXPLAIN ANALYZE belə problemləri tapmağa və sorgunun harada "ağırlaşdığını" başa düşməyə kömək edir. Bu, diaqnostika kimidir — onsuz performansı "müalicə etmək" çətindir.

Sorgulardakı tipik problemlər və onları necə tapmaq olar

İndi performansın pisləşməsində şübhəli olanlarla tanış olmağın vaxtıdır. Bunun üçün EXPLAIN ANALYZE komandasını işə salırıq.

Problem 1: Ardıcıl skanlama (Seq Scan)

Seq Scan (ardıcıl skanlama) — PostgreSQL-in məlumatları tapmaq üçün cədvəldəki hər sətri yoxlamasıdır. Əgər cədvəl balacadırsa, bu normaldır, amma böyük cədvəllərdə bu, əsl işgəncə ola bilər.

Necə bilmək olar ki, Seq Scan istifadə olunur? Sadəcə EXPLAIN ANALYZE ilə analiz et. Nümunə:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE student_id = 123;

Nəticə belə görünə bilər (diqqət et Seq Scan yazısına):

Seq Scan on students  (cost=0.00..35.50 rows=1 width=72) (actual time=0.010..0.015 rows=1 loops=1)

Problemi necə həll etmək olar?

student_id üçün indeks yarat (əgər yoxdursa):

CREATE INDEX idx_student_id ON students(student_id);

Bundan sonra yenidən EXPLAIN ANALYZE işə sal. Seq Scan əvəzinə Index Scan görməlisən.

Problem 2: aşağı selektivlikli şərtlər

Selektivlik — axtardığını tapmaq üçün neçə sətrin işlənməli olduğunu göstərir. Əgər filtr demək olar ki, bütün cədvəli əhatə edirsə, indeks kömək etməyəcək.

Aşağı selektivlikli sorgu nümunəsi:

EXPLAIN ANALYZE
SELECT * 
FROM students 
WHERE program = 'Computer Science';

Əgər cədvəldə tələbələrin 90%-i Computer Science oxuyursa, sorgu program üzərində indeks olsa belə, Seq Scan istifadə edə bilər.

Sorgunu necə yaxşılaşdırmaq olar?

1. Sorgunun məntiqini yenidən bax: bəlkə filtri dəqiqləşdirmək üçün əlavə şərtlər əlavə etməlisən.
2. Cədvəlin statistikası aktualdırmı, yoxla (bu, PostgreSQL-ə selektivliyi düzgün qiymətləndirməyə kömək edir):

ANALYZE students;

3. Əgər sorgu əsassız olaraq indeks əvəzinə ardıcıl skanlama istifadə edirsə, PostgreSQL-ə onu məcburi istifadə etməyi təklif et:

SET enable_seqscan = OFF;

Problem 3: artıq sort əməliyyatları

Sıralama (Sort) baha başa gələ bilər, xüsusən də məlumatlar RAM-a sığmırsa. Tipik olaraq ORDER BY ifadəsi sort tələb edir.

Problem nümunəsi:

EXPLAIN ANALYZE
SELECT * 
FROM students
ORDER BY last_name;

Belə bir şey görə bilərsən:

Sort  (cost=123.00..126.00 rows=300 width=45) (actual time=1.123..1.234 rows=300 loops=1)

Sıralamanı necə sürətləndirmək olar? Əgər tez-tez müəyyən bir sütuna görə sıralama aparırsansa, indeks yarat:

CREATE INDEX idx_last_name ON students(last_name);

İndi PostgreSQL məlumatları sıralı şəkildə çıxarmaq üçün indeksi istifadə edə bilər və əlavə sort əməliyyatından qaça bilər.

Problem 4: Məhdudiyyətlərin (LIMIT) olmaması

SELECT ilə sorğu verəndə və qaytarılan sətrlərin sayını məhdudlaşdırmırsansa, sorgu bütün cədvəli işləyə bilər, hətta sənə sadəcə ilk sətr lazımdırsa belə.

Bu belə görünür:

EXPLAIN ANALYZE
SELECT * 
FROM students
WHERE gpa > 3.5;

Əgər bazada bir milyon sətir varsa və gpa > 3.5 filtri cədvəlin 80%-ni qaytarırsa, yəqin ki, gözləməli olacaqsan.

Əgər sənə sadəcə ən yaxşı 10 tələbə lazımdırsa, LIMIT istifadə et:

SELECT *
FROM students
WHERE gpa > 3.5
ORDER BY gpa DESC
LIMIT 10;

Həmçinin, LIMIT ilə birlikdə OFFSET istifadə edərək səhifələmə (pagination) edə bilərsən.

İcra parametrlərinə nəzarət: SET

PostgreSQL-də SET komandası sessiya və ya sorgunun işləmə parametrlərini dəyişmək üçün istifadə olunur. Bu, sanki müvəqqəti bir ayardır və yalnız cari bağlantı çərçivəsində bazanın davranışına təsir edir.

Sadə desək, SET — PostgreSQL-in "əvvəlcədən əhvalını" idarə etməyin bir yoludur, qlobal ayarları dəyişmədən.

Harada istifadə olunur?

• Hesabatdan əvvəl dili və ya tarix formatını dəyişmək.
• Bir ağır sorgu üçün yaddaşı artırmaq.
• Kütləvi yükləmə zamanı log yazmanı söndürmək.
• Bir müddətlik axtarış yolunu (search_path) dəyişmək.
• Təhlükəsizliyə nəzarət (məsələn, istifadəçi səlahiyyətlərini müvəqqəti azaltmaq).

Ümumi sintaksis

SET parametr = dəyər;

Parametrin cari dəyərinə baxmaq üçün:

SHOW parametr;

Varsayılan dəyərə qaytarmaq üçün:

RESET parametr;

Kompleks optimallaşdırma nümunəsi

Tutaq ki, tapşırıq belədir: Computer Science oxuyan ən yüksək orta balı (GPA) olan son 10 tələbəni tapmaq lazımdır. Əsas sorgu belədir:

SELECT *
FROM students
WHERE program = 'Computer Science'
ORDER BY gpa DESC
LIMIT 10;

1. Sorgunun analizi: Əvvəlcə EXPLAIN ANALYZE işə sal:

   EXPLAIN ANALYZE
   SELECT * 
   FROM students
   WHERE program = 'Computer Science'
   ORDER BY gpa DESC
   LIMIT 10;
   

   Əgər ardıcıl skanlama və sort görürsənsə, bu, optimallaşdırma siqnalıdır.

2. Filtr və sort üçün indeks:

   Hər iki sütunu əhatə edən birləşmiş indeks yarat:

   CREATE INDEX idx_program_gpa
   ON students(program, gpa DESC);
   

3. Yaxşılaşmanı yoxla:

   Yenidən EXPLAIN ANALYZE işə sal. İndi sorgu yuxarıdakı indeksi istifadə etməli, sort və ardıcıl skanlamadan qaçmalıdır.

Sorgu optimallaşdırma metodologiyası

1. Əvvəlcə cari icra planını analiz et. Problemləri tapmaq üçün EXPLAIN ANALYZE istifadə et.

2. Dar boğazları müəyyən et. Planda ən çox vaxt və ya resurs tələb edən node-ları tap.

3. İndekslər qur. Filtrasiya və sortda iştirak edən sütunları yoxla və lazım olan indeksləri yarat.

4. Məlumat həcmini minimallaşdır. LIMIT, OFFSET və dəqiq filtr şərtlərindən istifadə et.

5. Statistikanı yenilə. ANALYZE işə sal ki, PostgreSQL məlumatların paylanması barədə təzə məlumata sahib olsun.

6. Dəyişiklikləri test et. Optimallaşdırmadan sonra yenidən EXPLAIN ANALYZE işə sal və performansın yaxşılaşdığından əmin ol.

Sonra nə?

İndi sən sorgu optimallaşdırmasının ekspress-kursundan keçdin. Təbriklər! EXPLAIN ANALYZE ilə nə qədər çox eksperiment aparsan, PostgreSQL-in daxili mexanizmlərini bir o qədər yaxşı başa düşəcəksən. Və unutma: heç bir sehrli indeks səni xilas etməyəcək, əgər sorgu çox mürəkkəb və ya qeyri-müəyyəndirsə. SQL də digər dillər kimi, aydınlığı sevir.