Sorğuların optimallaşdırılması və indekslər

On milyon sifarişli cədvəldə bir müştərinin sifarişlərini tapan sorğu 8 saniyə çəkir; düzgün indeksdən sonra eyni sorğu millisaniyələrlə işləyir. SQL deklarativ dildir: biz nə istədiyimizi yazırıq, necə tapmağı isə VBİS-in optimallaşdırıcısı seçir. O, bir neçə mümkün planı müqayisə edir və cədvəl statistikası əsasında ən ucuzunu götürür. Bu dərsdə optimallaşdırıcının «düşüncə tərzini» öyrənəcəyik: indeksin daxili quruluşu, planın oxunması və onun işini asanlaşdıran sorğular. Xoş xəbər: bunun üçün xüsusi alət lazım deyil — hər şey EXPLAIN əmrindən və bir neçə sadə qaydadan başlayır.

B-ağac indeksi içəridən

Əksər VBİS-lərdə susmaya görə indeks B-ağacdır (dəqiq desək, B+ ağac): açarlar sıralanmış halda disk səhifələrində saxlanılır, hər daxili səhifədə yüzlərlə açar və aşağı səviyyəyə göstəricilər olur, yarpaqlar isə sətirlərə istinad edir və bir-biri ilə zəncirlənir. Ağac həmişə balanslıdır: kökdən istənilən yarpağa qədər yol eyni uzunluqdadır. Ona görə axtarış bir neçə səhifə oxumaqla bitir, yarpaq zənciri isə aralıq sorğularını (BETWEEN, >, ORDER BY) ucuz edir.

Sargable şərtlər

Sargable (search argument able) — indeks ilə axtarışa yararlı şərt deməkdir: sütun müqayisənin bir tərəfində «çılpaq» dayanır. Sütuna funksiya və ya hesab tətbiq edəndə (strftime('%m', order_date) = '03', price * 1.18 > 100) indeksdəki sıralanmış qiymətlər işə yaramır və VBİS hər sətri yoxlamalı olur. Eyni şərti aralıq kimi yenidən yazmaq kifayətdir.

CREATE INDEX idx_orders_date ON orders (order_date);

-- not sargable: a function on the column
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE strftime('%m', order_date) = '03';

id | parent | notused | detail
2 | 0 | 214 | SCAN orders USING COVERING INDEX idx_orders_date

CREATE INDEX idx_orders_date ON orders (order_date);

-- sargable: a range on the bare column
EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';

id | parent | notused | detail
2 | 0 | 154 | SEARCH orders USING COVERING INDEX idx_orders_date (order_date>? AND order_date<?)

Kompozit və örtücü indekslər

Kompozit indeks bir neçə sütun üzrə sıralanır: (customer_id, order_date) əvvəlcə müştəriyə, eyni müştəri daxilində isə tarixə görə. O, telefon kitabçası kimi işləyir: soyadla, soyad və adla axtarmaq olar, amma yalnız adla yox — bu, sol prefiks qaydasıdır. İndeks sorğunun bütün sütunlarını ehtiva edirsə, VBİS cədvələ heç müraciət etmir — belə indeks örtücü (covering) adlanır. Əlavə olaraq, indeksin sırası ORDER BY ilə üst-üstə düşəndə ayrıca sıralama addımı da aradan qalxır.

-- before: function on the column, no suitable index
EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders
WHERE customer_id = 1 AND strftime('%Y', order_date) = '2025'
ORDER BY order_date;

id | parent | notused | detail
3 | 0 | 216 | SCAN orders
15 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY

-- after: sargable range + composite index
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT id, order_date FROM orders
WHERE customer_id = 1
  AND order_date >= '2025-01-01' AND order_date < '2026-01-01'
ORDER BY order_date;

id | parent | notused | detail
3 | 0 | 46 | SEARCH orders USING COVERING INDEX idx_orders_cust_date (customer_id=? AND order_date>? AND order_date<?)

PostgreSQL-də EXPLAIN planı və təxmini dəyəri, EXPLAIN ANALYZE isə sorğunu həqiqətən icra edib real vaxtı göstərir. Aşağıda 1 000 000 sətirli cədvəldə indeksdən əvvəl və sonra alınan tipik nəticə var (rəqəmlər avadanlıqdan asılıdır). cost=başlanğıc..cəmi şərti vahidlərdədir, rows isə optimallaşdırıcının təxminidir; təxmin faktiki rows-dan çox fərqlənirsə, statistika köhnəlib və ANALYZE lazımdır.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

CREATE INDEX idx_orders_customer ON orders (customer_id);

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Seq Scan on orders  (cost=0.00..20834.00 rows=10 width=24) (actual time=0.018..61.922 rows=12 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 999988
Planning Time: 0.081 ms
Execution Time: 61.950 ms

Index Scan using idx_orders_customer on orders  (cost=0.42..44.58 rows=10 width=24) (actual time=0.024..0.039 rows=12 loops=1)
  Index Cond: (customer_id = 42)
Planning Time: 0.210 ms
Execution Time: 0.061 ms

İndeks nə vaxt lazım deyil

• Kiçik cədvəllər — bir neçə səhifəlik cədvəli skan etmək ağacdan enməkdən ucuzdur.
• Aşağı seçicilik — is_active kimi iki qiymətli sütunda şərt sətirlərin yarısını qaytarır.
• Çox yazılan cədvəllər — hər indeks hər INSERT, UPDATE, DELETE-də yenilənir; jurnal və sensor cədvəllərində bu, bahalıdır.
• Təkrarlanan indekslər — (customer_id, order_date) varsa, ayrıca (customer_id) indeksi artıqdır: sol prefiks onu əvəz edir.

CREATE INDEX idx_orders_date ON orders (order_date);

-- slow version: WHERE strftime('%m', order_date) IN ('01', '02', '03')
SELECT id, order_date
FROM orders
-- WHERE: a range on the bare column
ORDER BY order_date, id;

id | order_date
1 | 2025-01-15
2 | 2025-01-15
3 | 2025-02-02
4 | 2025-02-10
5 | 2025-03-05
6 | 2025-03-18

SELECT category,
       COUNT(*) AS rows_count
       -- selectivity_pct = 100 * n / N
FROM products
GROUP BY category
ORDER BY rows_count DESC, category;

category | rows_count | selectivity_pct
Electronics | 4 | 40
Accessories | 2 | 20
Stationery | 2 | 20
Games | 1 | 10
Home | 1 | 10