Wzorce optymalizacji SQL

Opanuj optymalizację zapytań SQL z narzędziami wspieranymi przez AI i serwerami MCP baz danych, przekształcając wolne zapytania w wysokowydajne operacje bazodanowe.

Analiza i optymalizacja zapytań

Zrozumienie planów wykonania zapytań

Analiza EXPLAIN wspomagana przez AI

Wzorzec: Użyj AI z serwerami MCP baz danych do interpretacji złożonych planów wykonania i identyfikacji wąskich gardeł.

-- PRD: Analiza wydajności zapytań
-- Plan: Użyj MCP bazy danych dla kontekstu schematu

-- Najpierw połącz się z MCP PostgreSQL
"Połącz się z MCP PostgreSQL i uzyskaj informacje o schemacie dla odpowiednich tabel"

-- Następnie przeanalizuj zapytanie
"Używając kontekstu schematu, przeanalizuj ten plan wykonania zapytania i zidentyfikuj problemy wydajnościowe:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT c.customer_name, COUNT(o.order_id) as order_count,
       SUM(oi.quantity * oi.unit_price) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.customer_name
ORDER BY total_spent DESC
LIMIT 100;"

Analiza AI dostarcza:

Identyfikacja skanów tabeli vs skanów indeksów
Współczynniki trafień buforów i użycie pamięci
Rekomendacje metod złączeń
Sugestie brakujących indeksów
Możliwości przepisania zapytań

// PRD: Strategia optymalizacji indeksów
// Plan: Przeanalizuj wolne zapytania i utwórz optymalne indeksy

// Użyj MCP bazy danych do analizy
"Połącz się z MCP PostgreSQL i przeanalizuj statystyki tabel"

// Użyj trybu Agent Cursor z kontekstem bazy danych
"@schema @query_stats Na podstawie analizy bazy danych, rekomenduj indeksy:

Todo:
- [ ] Przeanalizuj stosunek odczytu/zapisu
- [ ] Zidentyfikuj brakujące indeksy
- [ ] Zasugeruj indeksy kompozytowe
- [ ] Oszacuj wpływ na wydajność
- [ ] Dostarcz instrukcje CREATE INDEX
- [ ] Przetestuj skuteczność indeksów"

# PRD: Optymalizacja wydajności bazy danych

# Połącz się z MCP bazy danych
"Połącz się z serwerem MCP PostgreSQL do analizy schematu na żywo"

# Z Claude Code i dostępem do bazy danych
claude --add-dir db/slow_query_log

"Używając MCP PostgreSQL, przejrzyj log wolnych zapytań i schemat na żywo aby:

Plan:
1. Zapytać o aktualne statystyki użycia indeksów
2. Przeanalizować wzorce wolnych zapytań
3. Zaprojektować optymalną strategię indeksów

Todo:
- [ ] Zidentyfikować wzorce w wolnych zapytaniach
- [ ] Rekomendować optymalne indeksy na podstawie rzeczywistego użycia
- [ ] Rozważyć narzut utrzymania indeksów
- [ ] Wygenerować i przetestować skrypty migracji"

Zaawansowane wzorce optymalizacji zapytań

Funkcje okna dla wydajności

Zamień podzapytania na funkcje okna

-- Poproś AI o optymalizację tego wzorca
"Przekonwertuj to skorelowane podzapytanie na użycie funkcji okna:

-- Wolna wersja z podzapytaniem
SELECT
  customer_id,
  order_date,
  order_amount,
  (SELECT SUM(order_amount)
   FROM orders o2
   WHERE o2.customer_id = o1.customer_id
   AND o2.order_date <= o1.order_date) as running_total
FROM orders o1

-- AI generuje zoptymalizowaną wersję:
SELECT
  customer_id,
  order_date,
  order_amount,
  SUM(order_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS UNBOUNDED PRECEDING
  ) as running_total
FROM orders"

Wpływ na wydajność: Funkcje okna zwykle działają 5-10x szybciej niż skorelowane podzapytania na dużych zbiorach danych.

Optymalizacja wspólnych wyrażeń tabelowych (CTE)

Identyfikuj powtarzające się podzapytania

"Znajdź powtarzające się podzapytania w tym złożonym zapytaniu i
refaktoryzuj używając CTE dla lepszej czytelności i wydajności"

Materializuj gdy korzystne

-- AI sugeruje kiedy użyć podpowiedzi MATERIALIZED
WITH customer_metrics AS MATERIALIZED (
  SELECT customer_id,
         COUNT(*) as order_count,
         SUM(amount) as total_spent
  FROM orders
  GROUP BY customer_id
)
-- Reszta zapytania używa zmaterializowanego CTE

Wzorce rekurencyjnych CTE

"Wygeneruj rekurencyjne CTE dla danych hierarchicznych:
- Nawigacja po drzewie organizacyjnym pracowników
- Nawigacja po drzewie kategorii
- Rozłożenie listy materiałów
Z właściwymi warunkami zakończenia"

Optymalizacje specyficzne dla baz danych

Wzorce optymalizacji PostgreSQL

Techniki specyficzne dla PostgreSQL

-- PRD: Dostrajanie wydajności PostgreSQL

-- Połącz się z MCP PostgreSQL do analizy
"Użyj MCP PostgreSQL do:
1. Analizy aktualnych statystyk tabel
2. Sprawdzenia rozdęcia indeksów
3. Przeglądu ustawień vacuum
4. Identyfikacji wolnych zapytań"

-- Poproś o optymalizacje specyficzne dla PostgreSQL
"Na podstawie analizy MCP, optymalizuj dla PostgreSQL 15:
- Użyj indeksów częściowych gdzie stosowne
- Rozważ GIN/GiST dla wyszukiwania pełnotekstowego
- Implementuj właściwą strategię vacuum
- Użyj indeksów BRIN dla danych szeregów czasowych"

-- AI generuje:
-- Indeks częściowy dla aktywnych klientów
CREATE INDEX idx_active_customers
ON customers(customer_id)
WHERE status = 'active';

-- Indeks GIN dla wyszukiwania JSON
CREATE INDEX idx_product_attributes
ON products USING gin(attributes);

-- Indeks BRIN dla szeregów czasowych
CREATE INDEX idx_events_timestamp
ON events USING brin(created_at);

Wzorce MySQL/MariaDB

Techniki optymalizacji MySQL

-- PRD: Poprawa wydajności MySQL

-- Połącz się z MCP MySQL
"Połącz się z serwerem MCP MySQL i przeanalizuj:
1. Aktualne użycie indeksów
2. Współczynnik trafień cache zapytań
3. Efektywność buffer pool
4. Wzorce wolnych zapytań"

-- Zapytanie o optymalizację specyficzną dla MySQL
"Używając wglądów MCP MySQL, optymalizuj dla MySQL 8.0:
- Zaprojektuj indeksy pokrywające na podstawie rzeczywistych zapytań
- Optymalizuj użycie bufora JOIN
- Implementuj przycinanie partycji
- Rozważ podpowiedzi indeksów gdy statystyki optymalizatora są nieaktualne"

-- AI sugeruje:
-- Indeks pokrywający aby uniknąć dostępu do tabeli
CREATE INDEX idx_covering
ON orders(customer_id, order_date, status, amount);

-- Wymuś użycie indeksu gdy optymalizator się myli
SELECT /*+ INDEX(orders idx_date_status) */
  * FROM orders
WHERE order_date > '2024-01-01'
  AND status = 'completed';

Wzorce przepisywania zapytań

Optymalizacja złączeń

Optymalizacja kolejności złączeń
Wybór typu złączenia

-- Poproś AI o optymalizację kolejności złączeń
"Przestaw te złączenia dla optymalnej wydajności na podstawie:
- Rozmiarów tabel (customers: 1M, orders: 10M, items: 50M)
- Selektywności złączeń
- Dostępnych indeksów"

-- AI przepisuje z:
SELECT * FROM items i
JOIN orders o ON i.order_id = o.order_id
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country = 'US'

-- Na zoptymalizowaną wersję:
SELECT * FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN items i ON o.order_id = i.order_id
WHERE c.country = 'US'

-- AI pomaga wybrać optymalne strategie złączeń
"Przeanalizuj te tabele i zasugeruj typy złączeń:
- Mała tabela wymiarów (1K wierszy)
- Duża tabela faktów (100M wierszy)
- Średnia tabela odnośników (100K wierszy)"

-- AI rekomenduje:
-- Hash join dla małych tabel wymiarów
SET enable_hashjoin = ON;
-- Nested loop dla wysoce selektywnych złączeń
SET enable_nestloop = ON;
-- Merge join dla wstępnie posortowanych danych
SET enable_mergejoin = ON;

Optymalizacja agregacji

Inteligentne wzorce agregacji

-- Poproś o optymalizację agregacji
"Optymalizuj to zapytanie agregujące przetwarzające 100M wierszy:
- Wstępnie agreguj gdzie możliwe
- Użyj funkcji przybliżonych jeśli akceptowalne
- Implementuj agregację przyrostową"

-- AI sugeruje wiele podejść:

-- 1. Wstępna agregacja z widokiem zmaterializowanym
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
  DATE(order_date) as sale_date,
  product_id,
  SUM(quantity) as total_quantity,
  SUM(amount) as total_amount,
  COUNT(*) as order_count
FROM orders
GROUP BY DATE(order_date), product_id;

-- 2. Przybliżone liczenie dla dużych zbiorów danych
SELECT
  product_category,
  APPROX_COUNT_DISTINCT(customer_id) as unique_customers
FROM orders
GROUP BY product_category;

-- 3. Wzorzec agregacji przyrostowej
INSERT INTO hourly_metrics
SELECT
  DATE_TRUNC('hour', created_at) as hour,
  COUNT(*) as event_count,
  AVG(response_time) as avg_response
FROM events
WHERE created_at >= COALESCE(
  (SELECT MAX(hour) FROM hourly_metrics),
  '2024-01-01'::timestamp
)
GROUP BY DATE_TRUNC('hour', created_at);

Wzorce testowania wydajności

Benchmarking zapytań

Systematyczne testowanie wydajności

-- PRD: Framework testowania wydajności zapytań
-- Plan: Zbuduj zautomatyzowany system benchmarkingu

-- Użyj MCP bazy danych dla metryk
"Połącz się z MCP bazy danych i utwórz bazę wydajności"

-- Utwórz framework benchmarkingu z AI
"Używając MCP bazy danych, wygeneruj framework testowania wydajności zapytań:

Todo:
- [ ] Przechwyć metryki bazowe z MCP
- [ ] Testuj z różnymi objętościami danych
- [ ] Porównaj strategie optymalizacji
- [ ] Wygeneruj raporty wydajności
- [ ] Ustaw ciągłe monitorowanie
- [ ] Utwórz progi alertowania"

-- AI tworzy kompleksową suitę testową:
CREATE TABLE query_benchmarks (
  benchmark_id SERIAL PRIMARY KEY,
  query_name TEXT,
  query_hash TEXT,
  execution_time_ms NUMERIC,
  rows_returned BIGINT,
  buffers_hit BIGINT,
  buffers_read BIGINT,
  tested_at TIMESTAMP DEFAULT NOW()
);

-- Funkcja benchmarkingu
CREATE FUNCTION benchmark_query(
  p_query_name TEXT,
  p_query TEXT
) RETURNS TABLE (
  execution_time_ms NUMERIC,
  rows_returned BIGINT
) AS $$
DECLARE
  v_start_time TIMESTAMP;
  v_end_time TIMESTAMP;
  v_row_count BIGINT;
BEGIN
  v_start_time := clock_timestamp();
  EXECUTE p_query;
  GET DIAGNOSTICS v_row_count = ROW_COUNT;
  v_end_time := clock_timestamp();

  INSERT INTO query_benchmarks (
    query_name,
    query_hash,
    execution_time_ms,
    rows_returned
  ) VALUES (
    p_query_name,
    MD5(p_query),
    EXTRACT(MILLISECONDS FROM v_end_time - v_start_time),
    v_row_count
  );

  RETURN QUERY
  SELECT
    EXTRACT(MILLISECONDS FROM v_end_time - v_start_time),
    v_row_count;
END;
$$ LANGUAGE plpgsql;

Projektowanie schematów dla wydajności

Wzorce denormalizacji

Strategiczna denormalizacja

-- Poproś AI o rekomendacje denormalizacji
"Przeanalizuj ten znormalizowany schemat i zasugeruj denormalizację dla:
- Obciążeń ciężkich odczytowo (95% odczytów)
- Częstych wzorców złączeń
- Akceptowalnej redundancji danych
- Uwagi dotyczące częstotliwości aktualizacji"

-- AI rekomenduje:
-- 1. Zdenormalizowana tabela podsumowań
CREATE TABLE customer_order_summary AS
SELECT
  c.customer_id,
  c.customer_name,
  c.email,
  COUNT(o.order_id) as lifetime_orders,
  SUM(o.total_amount) as lifetime_value,
  MAX(o.order_date) as last_order_date,
  AVG(o.total_amount) as avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.email;

-- 2. Wyzwalacze do utrzymania spójności
CREATE TRIGGER update_customer_summary
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_customer_summary_func();

Strategie partycjonowania

Identyfikuj kandydatów do partycjonowania

"Przeanalizuj nasze tabele i rekomenduj strategie partycjonowania:
- Tabele powyżej 100GB
- Wyraźne wzorce dostępu
- Zapytania oparte na czasie lub zakresie"

Implementuj partycjonowanie

-- AI generuje schemat partycjonowania
CREATE TABLE orders_partitioned (
  LIKE orders INCLUDING ALL
) PARTITION BY RANGE (order_date);

-- Utwórz partycje miesięczne
CREATE TABLE orders_2024_01
PARTITION OF orders_partitioned
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Utrzymanie partycji

"Utwórz zautomatyzowane zarządzanie partycjami:
- Auto-tworzenie przyszłych partycji
- Archiwizacja starych partycji
- Aktualizacja statystyk partycji"

Optymalizacja w czasie rzeczywistym

Optymalizacja cache zapytań

Inteligentne strategie cachowania

-- Poproś o rekomendacje cachowania
"Zaprojektuj strategię cachowania dla naszej aplikacji:
- Zidentyfikuj zapytania nadające się do cachowania
- Ustaw odpowiednie TTL
- Implementuj unieważnianie cache
- Monitoruj współczynniki trafień cache"

-- AI dostarcza kompleksowe rozwiązanie:
-- 1. Warstwa cachowania Redis
-- 2. Cachowanie wyników zapytań
-- 3. Optymalizacja przygotowanych instrukcji
-- 4. Konfiguracja poolingu połączeń

Monitorowanie i utrzymanie

Ciągłe monitorowanie wydajności

Wykrywanie wolnych zapytań

# Użyj MCP bazy danych do monitorowania
"Połącz się z MCP PostgreSQL do monitorowania zapytań"

"Używając statystyk zapytań MCP, utwórz automatyczne wykrywanie wolnych zapytań:
- [ ] Loguj zapytania powyżej 1 sekundy
- [ ] Identyfikuj wzorce zapytań
- [ ] Alertuj o degradacji
- [ ] Sugeruj optymalizacje na podstawie EXPLAIN"

Analiza użycia indeksów

"Przeanalizuj użycie indeksów:
- Znajdź nieużywane indeksy
- Zidentyfikuj brakujące indeksy
- Oblicz rozdęcie indeksów
- Rekomenduj utrzymanie"

Aktualizacje statystyk

"Zautomatyzuj utrzymanie statystyk:
- Aktualizuj statystyki tabel
- Analizuj wzorce zapytań
- Planowanie vacuum
- Monitoruj wzrost tabel"

Trendy wydajności

"Śledź wydajność w czasie:
- Trendy wykonania zapytań
- Wykorzystanie zasobów
- Prognozy wzrostu
- Planowanie pojemności"

Optymalizacja kosztów

Optymalizacja kosztów bazy danych w chmurze

-- PRD: Optymalizacja kosztów bazy danych
-- Plan: Zmniejsz koszty bazy danych w chmurze o 40%

-- Użyj MCP chmury i bazy danych
"Połącz się z MCP AWS i MCP PostgreSQL aby przeanalizować:
1. Aktualne użycie instancji RDS
2. Wzorce wzrostu magazynu
3. Zużycie zasobów zapytań"

-- Optymalizuj pod kątem kosztów bazy danych w chmurze
"Na podstawie danych MCP, optymalizuj pod kątem kosztów:

Todo:
- [ ] Zidentyfikuj drogie zapytania zużywające IOPS
- [ ] Rekomenduj instancje zarezerwowane na podstawie użycia
- [ ] Zasugeruj optymalizację warstw magazynu
- [ ] Implementuj polityki archiwizacji danych
- [ ] Ustaw alerty kosztów"

-- AI dostarcza strategie oszczędzania kosztów:
-- 1. Archiwizuj stare dane do tańszego magazynu
-- 2. Użyj replik odczytu do analityki
-- 3. Implementuj pooling połączeń
-- 4. Planuj ciężkie zapytania poza godzinami szczytu

Następne kroki

Zaawansowana optymalizacja SQL

Gotowy na dalszy rozwój? Eksploruj:

Równoległe wykonanie zapytań: Wykorzystaj przetwarzanie wielordzeniowe
Magazyn kolumnowy: Dla obciążeń analitycznych
Optymalizacja w pamięci: Dla ultra-szybkich zapytań
Auto-dostrajanie wspierane przez AI: Samo-optymalizujące się bazy danych
Rozproszony SQL: Skalowanie na wielu węzłach

Użyj asystentów AI do przewodnictwa w implementacji tych zaawansowanych technik zachowując poprawność zapytań i integralność danych.