Cum să optimizezi interogări SQL — EXPLAIN, subquery, indexing

In this tutorial, you'll learn about Cum să optimizezi interogări SQL. We cover key concepts, practical examples, and best practices.

Cum să optimizezi interogări SQL folosind EXPLAIN, subquery-uri eficiente și indecși pentru a reduce timpul de execuție și a îmbunătăți performanța bazei de date.

Problema

Interogările SQL devin lente pe măsură ce volumul de date crește. Fără instrumente de analiză și tehnici de optimizare, dezvoltătorii aplică soluții arbitrare fără să înțeleagă cauza reală a încetinirii.

The Wrong Way

Optimizarea fără diagnosticare, prin adăugarea de indecși la întâmplare sau rescrierea interogării fără a înțelege planul de execuție:

-- Adăugare index fără analiză
CREATE INDEX idx_random ON orders (id);

-- interogare lentă, neschimbată
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.created_at > '2024-01-01'
ORDER BY o.total DESC;

Problema: Indexul pe orders.id nu ajută deloc această interogare. Resursele sunt irosite pe un index inutil.

The Right Way

Analizează planul de execuție înainte de a optimiza:

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.created_at > '2024-01-01'
ORDER BY o.total DESC;

Output:

Sort  (cost=1234.56..1245.67 rows=4432 width=120)
  Sort Key: o.total DESC
  ->  Hash Join  (cost=345.67..890.12 rows=4432 width=120)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o  (cost=0.00..432.10 rows=43210 width=80)
        ->  Hash  (cost=234.56..234.56 rows=5678 width=40)
              ->  Seq Scan on customers c
                    Filter: (created_at > '2024-01-01')

Analiza arată un Seq Scan pe ambele tabele. Optimizarea corectă:

CREATE INDEX idx_customers_created_at ON customers (created_at);
CREATE INDEX idx_orders_customer_total ON orders (customer_id, total DESC);

EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.created_at > '2024-01-01'
ORDER BY o.total DESC;

Output:

Nested Loop  (cost=0.56..234.56 rows=4432 width=120)
  ->  Index Scan using idx_customers_created_at on customers c
        Index Cond: (created_at > '2024-01-01')
  ->  Index Scan using idx_orders_customer_total on orders o
        Index Cond: (customer_id = c.id)
Execution Time: 12.34 ms

Step-by-Step Fix

1. Rulează EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT ... -- vezi planul real, nu estimat

2. Identifică Seq Scan-urile

Caută Seq Scan în plan — acolo unde baza de date scanează întreaga tabelă.

3. Optimizează subquery-urile

Subquery în WHERE (ineficient):

SELECT * FROM products
WHERE id IN (SELECT product_id FROM order_items WHERE quantity > 5);

Rescrie cu JOIN (mai eficient):

SELECT DISTINCT p.*
FROM products p
JOIN order_items oi ON p.id = oi.product_id
WHERE oi.quantity > 5;

4. Folosește indecși pe măsură

• Index pe coloanele WHERE
• Index compus pentru JOIN + ORDER BY
• Index covering pentru a evita accesul la tabel

Prevention Tips

• Rulează EXPLAIN ANALYZE înainte de orice optimizare
• Urmărește Seq Scan, Sort și Nested Loop în planuri
• Evită subquery-uri în WHERE când un JOIN face aceeași treabă
• Limitează numărul de JOIN-uri la maximum 5-6 per interogare

Greșeli comune la optimizare

1. Optimizare fără măsurători — nu știi ce să optimizezi
2. Indecși nefolosiți — indexul creat nu este utilizat de planner
3. Subquery-uri în loc de JOIN — subquery-urile corelate execută pentru fiecare rând
4. SELECT *** în interogări complexe — aduce coloane inutile care încetinesc
5. Ignorarea statisticilor — statistici învechite duc la planuri proaste

Exercițiu practic

Analizează interogarea SELECT name FROM users WHERE last_login < '2024-06-01' ORDER BY name; cu EXPLAIN ANALYZE, apoi creează indexul potrivit și compară timpii.

Soluție:

CREATE INDEX idx_users_last_login ON users (last_login);
-- Pentru acoperire completă:
CREATE INDEX idx_users_last_login_name ON users (last_login, name);

FAQ

Construit de dezvoltătorii Doda Browser, DodaZIP și Durga Antivirus Pro. Uneltele DodaTech se integrează nativ cu bazele de date pentru productivitate și securitate sporite.