Analitik prosedurlar yaradarkən tipik səhvlərin analizi

Bu gün, PL/pgSQL üzrə bu epik səyahəti yekunlaşdırmaq üçün gəlin bir şeyi başa düşək: analitik prosedurlarda səhvlər qaçılmazdır. Niyə? Çünki analitikada böyük datalar, mürəkkəb hesablamalar və bəzən çox hiyləgər şərtlərlə işləyirlər. Sorğu və ya prosedur nə qədər mürəkkəbdirsə, bir o qədər labirintə bənzəyir, burada bir neçə səhv addım səhv nəticələrə gətirib çıxara bilər.

Şükür ki, səhvlərin çoxu tipikdir və onları qabaqcadan görmək (və qarşısını almaq) olar. Gəlin bir-bir baxaq.

1. Açar sahələrdə indekslərin olmaması

İndekslər — database dünyasında naviqator kimidir. Əgər onlar yoxdursa, database bütün cədvəlin sətirlərini piyada gəzməli olur. Kiçik cədvəllərdə bu dözüləndir, amma data milyonlarla sətrə çatanda, sorğuların işləmə sürəti Windows XP-nin Pentium III-də işləməsindən də yavaş olur.

Tutaq ki, səndə sifarişlər cədvəli var və son ayın satışlarını hesablamaq istəyirsən:

SELECT SUM(order_total)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

Əgər order_date sahəsində indeks yoxdursa, PostgreSQL cədvəlin tam baxışını (Seq Scan) edəcək. Bu isə demək olar ki, həmişə yavaşdır.

Həll: indekslərdən istifadə et! Bunun üçün sadəcə bu əmri yazmaq kifayətdir:

CREATE INDEX idx_order_date ON orders (order_date);

İndi PostgreSQL order_date sahəsi üzrə cədvəldə axtarışı daha sürətli edəcək.

Qeyri-effektiv sorğulardan istifadə

Bəzi sorğular gözəl görünür, amma açar əvəzinə beton kərpic kimi işləyir. Məsələn, əvəzində cədvəllərin birləşdirilməsi (JOIN) ilə əvəz oluna biləcək subquery-lərdən istifadə və ya artıq filtrasiya.

Bunun əvəzinə:

SELECT product_id, SUM(order_total)
FROM orders
WHERE product_id IN (SELECT id FROM products WHERE category = 'electronics')
GROUP BY product_id;

Belə etmək daha yaxşıdır:

SELECT o.product_id, SUM(o.order_total)
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.category = 'electronics'
GROUP BY o.product_id;

Bu, PostgreSQL-i hər sətr üçün subquery işlətməkdən azad edir və icranı xeyli sürətləndirir.

Müvəqqəti cədvəllərin səhv strukturu

Müvəqqəti cədvəllər düzgün istifadə olunsa güclü alətə çevrilir. Amma lazım olan sütunları və ya indeksləri əlavə etməyi unutsan, müvəqqəti cədvəl butulka boğazına çevrilir və bütün proseduru ləngidir.

Nümunə göstərək. Aralıq hesablamalar üçün müvəqqəti cədvəl yaradaq:

CREATE TEMP TABLE temp_sales AS
SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region;

Amma sonra total_sales sütunu üzrə filtrasiya etmək lazım olur, amma bu sahədə indeks yoxdur.

Müvəqqəti cədvəldən istifadə etməzdən əvvəl düşün, onunla necə işləyəcəksən. Əgər sütun üzrə filtr lazımdırsa, indeks əlavə et:

CREATE INDEX idx_temp_sales_total_sales ON temp_sales (total_sales);

Hesablamalarda səhvlər (məsələn, sıfıra bölmə)

Sıfıra bölmə — analitikada klassik problemdir. SQL bu səhvə göz yumub keçməyəcək, sadəcə sorğunu dayandıracaq.

Tutaq ki, sifarişlərin orta dəyərini hesablamaq istəyirsən:

SELECT SUM(order_total) / COUNT(*) AS avg_order_value
FROM orders;

Əgər orders cədvəlində data yoxdursa, sıfıra bölmə baş verəcək və sorğu səhvlə bitəcək.

Problemin qarşısını almaq üçün sayğac sıfır olanda bunu yoxla:

SELECT
    CASE 
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(order_total) / COUNT(*)
    END AS avg_order_value
FROM orders;

Loglama və icra nəzarətinin olmaması

PL/pgSQL prosedurları mürəkkəb ola bilər və bir neçə mərhələdən ibarət olur: aralıq hesablamalardan tutmuş yekun hesabatlara qədər. Əgər bu zəncirdə problem çıxsa, loglama olmadan harada və niyə hər şeyin səhv getdiyini bilməyəcəksən.

Tutaq ki, metriklərin hesablanması üçün prosedur yaradırsan, amma hər mərhələdə gözlənilən datanı yoxlamağı unudursan. Nəticədə prosedur gözlənilməz data (məsələn, boş cədvəllər) ilə qarşılaşanda tamamilə çökür.

Bunun qarşısını almaq üçün prosedurun hər vacib mərhələsində loglama əlavə etmək olar. Məsələn:

RAISE NOTICE 'Satışların hesablanmasına başlanılır';
-- Sənin kodun burda...

RAISE NOTICE 'Modul % uğurla başa çatdı', modul;

Daha mürəkkəb prosedurlar üçün logları xüsusi cədvəldə saxlamaq daha yaxşıdır:

CREATE TABLE log_analytics (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_message TEXT
);

Prosedurda əlavə et:

INSERT INTO log_analytics (log_message)
VALUES ('Prosedur uğurla başa çatdı');

Optimallaşdırmanın olmaması səbəbindən performans problemləri

Optimallaşdırma təkcə sorğular üçün yox, həm də prosedurlar üçün vacibdir. Əgər prosedurdan bir çox istifadəçi istifadə edirsə, onun icrası sistemdə dar boğaza çevrilə bilər.

Məsələn, bütün regionlar üçün metriklərin yenidən hesablanmasını edən prosedur var, amma sənə yalnız bir regionun dataları lazımdır:

CREATE OR REPLACE FUNCTION calculate_sales()
RETURNS VOID AS $$
BEGIN
    -- Bütün regionlar üçün yenidən hesablanma
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

Bu artıq yüklənməyə səbəb olur.

Bununla necə mübarizə aparmalı? Regionu parametr kimi ötürərək datanı filtrləmək imkanı əlavə et:

CREATE OR REPLACE FUNCTION calculate_sales(p_region TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    WHERE region = p_region
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

İndi prosedur lazımsız dataları emal etməyəcək və sorğu daha tez bitəcək.

Performans analiz alətlərinə məhəl qoymamaq

EXPLAIN ANALYZE kimi alətlər — sənin dostun olan köməkçilərdir, onlar göstərir ki, sorğular harada ləngiyir və bunu necə düzəltmək olar. Əgər prosedur yazırsan, amma onun performansını analiz etmirsənsə, sanki kvant kompüteri proqramlaşdırırsan, amma oszilloqrafın yoxdur — guya işləyir, amma əslində nə baş verir, heç kim bilmir.

Nümunə göstərək. Bu sorğudakı problem EXPLAIN ANALYZE ilə görünəcək:

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

Bu sorğu qeyri-effektivdir, çünki EXTRACT() funksiyası indekslərin istifadəsini bloklayır.

Problemi belə həll etmək olar. Sorğunu analiz et:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_date >= DATE '2023-01-01' AND order_date < DATE '2024-01-01';

Tipik səhvlərdən necə qaçmaq olar?

Səhvlərin qarşısını almaq üçün bu praktikaları yadda saxla:

1. Filtrasiya və ya birləşmədə iştirak edən sahələrdə indekslərdən istifadə et.
2. Sorğuları optimallaşdır: artıq subquery-ləri çıxart, JOIN istifadə et.
3. İcranı logla. Əgər nəsə səhv getsə, debug etmək asan olacaq.
4. Prosedurlarını həmişə EXPLAIN ANALYZE kimi alətlərlə yoxla.
5. Performans problemi gördün? Partitioning və ya sorğu məntiqini dəyişməyi düşün.

İndi sən analitiklərini yavaş sorğulara görə kofe-aparat və Wi-Fi-dan məhrum etməyəcək qədər biliklə silahlanmısan.