Transaksiyaları nəzərə alaraq prosedurların optimallaşdırılması: performans analizi və geri qaytarmalar

Prosedurlar yazanda onlar tez-tez bazanın "ürəyi" olur, bir çox əməliyyatları yerinə yetirir. Amma eyni zamanda bu prosedurlar "dar boğaz" da ola bilər, xüsusən də əgər:

1. Lazımsız əməliyyatlar yerinə yetirirlər (məsələn, eyni dataya tez-tez müraciət edirlər).
2. İndekslərdən səmərəsiz istifadə edirlər.
3. Bir transaksiyanın içində çoxlu əməliyyat yerinə yetirirlər.

Bir ağıllı proqramçı demişdi: "Pis yazılmış kodu sürətləndirmək — tənbəl dostdan daha tez qaçmağı istəmək kimidir". Ona görə də prosedurların optimallaşdırılması təkcə sürəti artırmaq deyil, əsasını yaxşılaşdırmaqdır!

Transaksiya daxilində əməliyyatların sayını minimuma endirmək

PostgreSQL-də hər bir transaksiyanın öz əməliyyatlarını idarə etmək üçün əlavə xərcləri olur. Transaksiya nə qədər böyükdürsə, o qədər uzun müddət bloklama saxlayır və digər istifadəçilər üçün bloklama ehtimalı artır. Bu effektləri azaltmaq üçün:

1. Bir transaksiyaya çoxlu əməliyyatı birləşdirmə.
2. EXCEPTION END istifadə et, dəyişiklikləri lokal məhdudlaşdırmaq üçün. Bu, yalnız bir hissə əməliyyatın geri qaytarılması lazım olanda faydalıdır.
3. Böyük transaksiyaları bir neçə kiçik hissəyə böl (əgər tətbiqinin məntiqi buna imkan verirsə).

Nümunə: kütləvi data insertini "paketlərə" bölmək:

-- Nümunə: Addım-addım commit ilə batch yükləmə üçün prosedur
CREATE PROCEDURE batch_load()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
    batch_cnt INT := 0;
BEGIN
    FOR r IN SELECT * FROM staging_table LOOP
        BEGIN
            INSERT INTO target_table (col1, col2) VALUES (r.col1, r.col2);
            batch_cnt := batch_cnt + 1;
        EXCEPTION
            WHEN OTHERS THEN
                -- Səhvi loglayırıq; bu element üzrə dəyişikliklər geri qaytarılacaq
                INSERT INTO load_errors(msg) VALUES (SQLERRM);
        END;
        IF batch_cnt >= 1000 THEN
            COMMIT; -- hər 1000 əməliyyatı təsdiqləyirik
            batch_cnt := 0;
        END IF;
    END LOOP;
    COMMIT; -- son commit
END;
$$;

Məsləhət: unutma ki, hər bir COMMIT dəyişiklikləri təsdiqləyir, ona görə əvvəlcədən əmin ol ki, transaksiyanın bölünməsi data bütövlüyünü pozmayacaq.

Sorğuları sürətləndirmək üçün indekslərdən istifadə

Tutaq ki, orders adlı cədvəldə bir milyon sətr var və tez-tez customer_id ilə sorğu edirsən. İndeks olmadan sorğu bütün sətrləri skan edəcək:

CREATE INDEX idx_customer_id ON orders(customer_id);

İndi bu kimi sorğular:

SELECT * FROM orders WHERE customer_id = 42;

çox daha sürətli işləyəcək, bütün cədvəli skan etmədən.

Vacibdir: prosedur yazanda istifadə olunan sahələrin indeksdə olduğuna əmin ol, xüsusən filter, sort və join şərtlərində.

EXPLAIN ANALYZE ilə performans analizi

EXPLAIN sorğunun icra planını göstərir (PostgreSQL onu necə icra edəcək), ANALYZE isə real icra statistikası əlavə edir (məsələn, icra üçün nə qədər vaxt sərf olundu). Tipik nümunə:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Bunu prosedur daxilində necə istifadə etmək olar?

Prosedurundakı mürəkkəb sorğuları ayrıca EXPLAIN ANALYZE ilə "ayırıb" yoxlaya bilərsən:

DO $$
BEGIN
    RAISE NOTICE 'Sorğu Planı: %',
    (
        SELECT query_plan
        FROM pg_stat_statements
        WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
    );
END $$;

Analiz və təkmilləşdirmə nümunəsi

İlkin prosedur (yavaş):

CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
    UPDATE sales
    SET total = (
        SELECT SUM(amount)
        FROM orders
        WHERE orders.sales_id = sales.id
    );
END $$ LANGUAGE plpgsql;

Nə baş verir? sales cədvəlindəki hər sətr üçün SUM(amount) subquery-si işləyir, bu da çoxlu əməliyyata səbəb olur. Bu, yavaşdır.

Təkmilləşdirilmiş variant:

CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
    UPDATE sales as s
    SET total = o.total_amount
    FROM (
        SELECT sales_id, SUM(amount) as total_amount
        FROM orders
        GROUP BY sales_id
    ) o
    WHERE o.sales_id = s.id;
END $$ LANGUAGE plpgsql;

İndi SUM subquery-si bir dəfə işləyəcək və bütün datalar dərhal yenilənəcək.

Səhvlər zamanı datanın geri qaytarılması

Əgər prosedur daxilində nəsə səhv gedirsə, yalnız bir hissə transaksiyanı geri qaytara bilərsən. Məsələn:

BEGIN
    -- Data əlavə edirik
    INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
    WHEN OTHERS THEN
        -- Bu blok daxili savepoint-ə geri qaytarmaq kimidir!
        RAISE WARNING 'Data yenilənəndə səhv: %', SQLERRM;
END;

Təcrübə: dayanıqlı sifariş emalı prosedurunun reallaşdırılması

Tutaq ki, tapşırığın budur: sifarişi emal et. Əgər prosesdə səhv baş verərsə (məsələn, məhsul çatışmır), sifariş ləğv olunur və səhv loglanır.

CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
    v_in_stock INT;
BEGIN
    -- Qalıqları yoxlayırıq
    SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;

    BEGIN
        IF v_in_stock < 1 THEN
            RAISE EXCEPTION 'Anbarda məhsul yoxdur';
        END IF;
        UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
        -- ... digər əməliyyatlar
    EXCEPTION
        WHEN OTHERS THEN
            -- Bu blokdakı bütün dəyişikliklər geri qaytarılır!
            INSERT INTO order_logs(order_id, log_message)
                VALUES (p_order_id, 'Emal zamanı səhv: ' || SQLERRM);
            RAISE NOTICE 'Sifarişin emalında səhv: %', SQLERRM;
    END;

    -- Əgər səhv yoxdursa, qalan kod davam edir
    -- Loglaya bilərsən: sifariş uğurla emal olundu
END;
$$;

• Hətta səhv olsa belə, sifariş emal olunmayacaq və log order_logs cədvəlində görünəcək.
• Səhv zamanı daxili savepoint işləyəcək və bütün konteksti itirməyəcəksən.

Prosedurların optimallaşdırılması və dayanıqlığı üçün əsas qaydalar

1. Prosedur daxilindəki sorğular üçün indekslərdən istifadə et.
2. Böyük əməliyyatları kiçik batch-lərə böl, addım-addım emal et.
3. Səhvləri loglamağı bacar — kütləvi əməliyyatlar üçün ayrıca error log cədvəli yarat.
4. "Qismən" geri qaytarmalar üçün yalnız EXCEPTION ilə nested bloklardan istifadə et.
5. PL/pgSQL-də ROLLBACK TO SAVEPOINT istifadə etmə — bu, sintaksis səhvi verəcək.
6. Prosedurlarda COMMIT/SAVEPOINT yalnız autocommit rejimində çağıranda istifadə et!
7. Ağır sorğuların icra planını (EXPLAIN ANALYZE) prosedura əlavə etməzdən əvvəl ayrıca analiz et.