Partycjonowanie danych w MySQL i PostgreSQL: Jak zwiększyć wydajność i zarządzać dużymi zbiorami w Eloquent oraz Doctrine

W miarę rozwoju aplikacji i gromadzenia coraz większej ilości danych, wydajność operacji na bazie może znacznie spadać. Pełne skanowanie dużych tabel, problemy z indeksami i długi czas zapytań to tylko niektóre z wyzwań, które mogą spowolnić system.

Jednym z rozwiązań poprawiających wydajność i skalowalność bazy danych jest partycjonowanie – technika polegająca na podziale jednej dużej tabeli na mniejsze, bardziej zarządzalne części.

W tym artykule omówimy:

  • Jak działa partycjonowanie w MySQL i PostgreSQL
  • Jak można je wykorzystać w ORM-ach: Eloquent (Laravel) i Doctrine (Symfony)
  • Porównanie obu podejść oraz najlepsze praktyki

Czym jest partycjonowanie?

Partycjonowanie to technika podziału danych w bazie na mniejsze segmenty (partycje), które mogą być traktowane jako oddzielne jednostki. Dzięki temu:

  • Zapytania są szybsze – nie trzeba przeszukiwać całej tabeli, tylko odpowiednią partycję.
  • Łatwiejsze zarządzanie danymi – np. szybkie usuwanie starych danych poprzez usunięcie partycji.
  • Lepsza równowaga obciążenia – partycje mogą być umieszczone na różnych dyskach (w niektórych bazach).

Partycjonowanie może być realizowane na poziomie aplikacji (np. poprzez osobne tabele dla każdego roku) lub na poziomie bazy danych (automatyczny podział na partycje).

Partycjonowanie w MySQL

Typy partycjonowania w MySQL

MySQL obsługuje kilka typów partycjonowania:

  1. RANGE – podział na zakresy wartości, np. według lat.
  2. LIST – podział na podstawie predefiniowanych wartości, np. krajów.
  3. HASH – automatyczny podział danych na podstawie funkcji skrótu.
  4. KEY – podobne do HASH, ale używające klucza głównego.

Przykład partycjonowania w MySQL

Partycjonowanie RANGE według lat

Załóżmy, że mamy tabelę orders, w której zamówienia są przechowywane na podstawie daty:

				
					CREATE TABLE orders (
    id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

Zapytanie wykorzystujące partycję

MySQL automatycznie wybiera odpowiednią partycję:

				
					SELECT * FROM orders WHERE order_date = '2023-05-10';

Aby ręcznie wskazać partycję:

				
					SELECT * FROM orders PARTITION (p2023) WHERE order_date >= '2023-01-01';

Ograniczenia MySQL

❌ Brak możliwości dynamicznego dodawania partycji – trzeba dodawać je ręcznie.
❌ Indeksy działają tylko w obrębie partycji – nie można mieć jednego globalnego indeksu.
❌ Nie można używać FOREIGN KEY – ograniczenia kluczy obcych nie działają w partycjonowanych tabelach.

Partycjonowanie w PostgreSQL

PostgreSQL od wersji 10+ wprowadził deklaratywne partycjonowanie, które działa lepiej niż wcześniejsze metody.

Deklaratywne partycjonowanie (PostgreSQL 10+)

Podobnie jak w MySQL, PostgreSQL obsługuje różne typy partycjonowania:

  • RANGE (zakresy wartości)
  • LIST (predefiniowane wartości)
  • HASH (podział według hasha)

Przykład partycjonowania w PostgreSQL

Tworzenie tabeli orders podzielonej na lata:

				
					CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

Tworzenie partycji:

				
					CREATE TABLE orders_2023 PARTITION OF orders 
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Zalety PostgreSQL

  • Obsługa kluczy obcych w partycjonowanych tabelach
  • Automatyczne przekierowanie do właściwej partycji
  • Możliwość dynamicznego dodawania partycji

Partycjonowanie w ORM – Eloquent (Laravel)

Strategie integracji

Laravel nie wspiera partycjonowania natywnie, ale można:

  • Tworzyć partycjonowane tabele ręcznie
  • Używać dynamicznych tabel (np. orders_2023, orders_2024)
  • Wymuszać partycję w surowych zapytaniach (DB::raw())

Przykład:

				
					$orders = DB::select(DB::raw("SELECT * FROM orders PARTITION (p2023) WHERE order_date >= '2023-01-01'"));

Partycjonowanie w ORM – Doctrine (Symfony)

Doctrine nie obsługuje partycjonowania natywnie, ale można je dodać ręcznie w MySQL/PostgreSQL.

Przykład konfiguracji w Doctrine

Encja Order:

				
					/**
 * @ORM\Entity
 * @ORM\Table(name="orders")
 */
class Order
{
    /** @ORM\Id @ORM\Column(type="integer") @ORM\GeneratedValue */
    private $id;

    /** @ORM\Column(type="date") */
    private $order_date;
}

Ręczne wymuszenie partycji:

				
					$query = $entityManager->createNativeQuery(
    'SELECT * FROM orders PARTITION (p2023) WHERE order_date >= :date',
    new ResultSetMapping()
);
$query->setParameter('date', '2023-01-01');

$orders = $query->getResult();

Porównanie i najlepsze praktyki

FeatureMySQLPostgreSQL
Typy partycjiRANGE, LIST, HASHRANGE, LIST, HASH
FOREIGN KEYBrakObsługiwane
Dynamiczne partycjeNieTak
Obsługa w ORMBrak wsparcia natywnegoBrak wsparcia natywnego

Kiedy unikać partycjonowania?

  • Gdy tabela ma mało rekordów (poniżej kilku milionów).
  • Gdy zapytania wymagają częstego łączenia partycji.

Zakończenie

Partycjonowanie to potężne narzędzie do zarządzania dużymi zbiorami danych w MySQL i PostgreSQL. Choć ORM-y (Eloquent, Doctrine) nie obsługują partycjonowania natywnie, można je stosować ręcznie dla poprawy wydajności.

  • MySQL – dobra opcja, ale ograniczona w dynamicznym zarządzaniu partycjami.
  • PostgreSQL – bardziej elastyczne rozwiązanie z pełnym wsparciem dla partycjonowania.

Jeśli Twoja aplikacja przetwarza ogromne ilości danych, partycjonowanie może znacząco zwiększyć jej wydajność!

Kategorie