Ta strona używa ciasteczek (cookies), dzięki którym nasz serwis może działać lepiej. Dowiedz się więcej OK, rozumiem
WebHelp.pl Warsztat Artykuły MySQL: typy tabel

Warsztat / Artykuły i tutoriale

MySQL: typy tabel

Bartosz Romanowski 11 października 2010 komentarze ()

Na forum serwisu WebHelp.pl coraz częściej pojawiają się pytania dotyczące obsługiwanych przez MySQLa typów tabel (czy raczej "silniki magazynowania danych" - bo tak chyba należałoby przetłumaczyć wyrażenie "storage engines"). Postanowiłem więc wyjaśnić w kilku słowach różnice pomiędzy nimi oraz kryteria, jakimi należy się kierować w wyborze tego odpowiedniego.

MySQL posiada obsługę dziesięciu typów tabel:

O typach (silnikach) EXAMPLE, BLACKHOLE, BDB i ISAM nie będę się w ogóle rozpisywał. Wspomnę tylko, że EXAMPLE nie robi kompletnie nic (może służyć deweloperom jako pomoc w tworzeniu np. nowego silnika od podstaw), BLACKHOLE nie zapisuje (poza logiem) żadnych danych (co nie znaczy, że nie nadaje się do niczego - ma swoje dość wąskie zastosowania), BDB nie jest w pełni obsługiwany (stąd nie polecam jego używania), a ISAM jest pierwowzorem MyISAM (w wersji 4.1 został oznaczony jako "niezalecany", a w 5.0 jego obsługa została całkowicie usunięta).

Zacznę może trochę od końca, a mianowicie od omówienia bardziej "egzotycznych" typów tabel.

MERGE to dość ciekawy wynalazek. Tabela tego typu jest logicznym połączeniem kilku tabel MyISAM o identycznej strukturze - czyli czymś na kształt mocno ograniczonego widoku (wprowadzonego w wersji 5.0.1). Praktycznym zastosowaniem tego typu jest np. tworzenie archiwum danych, które jest przechowywane w jakiejś ograniczonej (znanej) ilości tabel (np. podzielone na miesiące czy lata). Na połączonych w ten sposób tabelach można wykonywać zapytania SELECT, DELETE, UPDATE i INSERT (od wersji 4.0). Od wersji 4.1.1 tabela typu MERGE może łączyć także tabele znajdujące się w różnych bazach.

Zaletą stosowania tego typu jest głównie łatwiejsze operowanie na dużej ilości danych. Wielkość tabeli typu MyISAM jest ograniczona (wynika to z ograniczeń systemu plików, na którym działa silnik bazy) - połączenie wielu tabel w jedną (wirtualną) pozwala obejść te limity. Na dodatek naprawianie tabeli (REPAIR) działa szybciej kilku mniejszych tabelach, niż na jednej dużej. Typ ten ma jednak kilka wad. Przede wszystkim wyszukiwanie z wykorzystaniem indeksów jest wolniejsze, niż w przypadku pojedyńczej tabeli. Wynika to z faktu, iż tabela typu MERGE nie posiada (nie tworzy) własnych indeksów, a korzysta z już istniejących w poszczególnych tabelach - tak więc musi przeszukać x indeksów z wszystkich połączonych tabel. Mniej istotnymi wadami są: wymóg identycznej struktury łączonych tabel, ograniczenie ich typu do MyISAM oraz niemożność korzystania z indeksów pełnotekstowych (FULLTEXT).

Typ MEMORY (zwany także HEAP - w celu zachowania wstecznej kompatybilności) charakteryzuje się tym, że dane przechowywane są w pamięci operacyjnej, a nie na dysku. Jak nietrudno się domyślić, dostęp do danych oraz ich zapis są o wiele szybsze, niż w przypadku "tradycyjnych" tabel. Minusem jest (co oczywiste), że w momencie wyłączenia maszyny zapisane dane są bezpowrotnie tracone (struktura tabel nie ulega usunięciu, gdyż jest zapisana na dysku). Korzystanie z tych tabel nie wyróżnia się niczym szczególnym, chociaż różnice istnieją (np. brak możliwości tworzenia pól typu BLOB i TEXT).

Typ ARCHIVE służy do przechowywania dużych ilości danych bez indeksów. Dane są skompresowane przy użyciu algorytmu zlib. Na tabelach typu ARCHIVE można wykonywać jedynie operacje SELECT i INSERT. Zapytanie SELECT zawsze wykonuje pełne skanowanie tabeli oraz dekompresję znalezionych rekordów, tak więc jest dość wolne. Zapytanie INSERT wykonuje z kolei kompresję dopisywanego rekordu i umieszcza go w buforze, który jest "opróżniany" tylko w przypadku jego całkowitego zapełnienia lub wykonania zapytania SELECT. Z tego typu należy korzystać tylko i wyłącznie w przypadku, gdy do przechowywanych danych nie trzeba sięgać w ogóle lub robi się to sporadycznie - a już na pewno nie należy udostępniać publicznie żadnych interfejsów korzystających z takich tabel.

CSV to dość mało użyteczny typ tabel, który do przechowywania danych używa plików CSV (kolumny oddzielone przecinkami). Ten typ nie posiada możliwości indeksowania danych, co praktycznie wyklucza go z jakichkolwiek zastosowań.

Tym sposobem doszliśmy do sedna niniejszego tekstu, czyli porównania dwóch najpopularniejszych silników - MyISAM i InnoDB. Słyszy się sporo opinii (pochodzących głównie od niedoświadczonych teoretyków), że "należy używać typu InnoDB bo jest lepszy i nowocześniejszy". Nic bardziej mylnego. Oczywiście - InnoDB posiada funkcjonalności, których nie znajdziemy w MyISAM, ale nie oznacza to, że jest on jedynie słusznym wyborem. Przyjrzyjmy się więc zaletom i wadom obu silników.

InnoDB posiada kilka elementów, którymi warto się zainteresować. Jednym z nich są transakcje - coś, czego przez lata brakowało w MySQLu, a co konkurencyjny PostgreSQL miał chyba od zawsze. W dużym skrócie (dla niezorientowanych) transakcja to odseparowany ciąg operacji wykonywanych na danych znajdujących się w bazie. Jego odseparowanie polega na tym, że dopóki transakcja się nie zakończy, modyfikacje nie są widoczne dla innych transakcji czy procesów.

Z technicznego punktu widzenia wygląda to mniej więcej tak:

Kod: Zaznacz cały
START TRANSACTION;
UPDATE salda SET saldo = saldo - 190.55 WHERE nazwisko = 'Nowak';
UPDATE salda SET saldo = saldo + 190.55 WHERE nazwisko = 'Kowalski';
COMMIT;

W powyższym przykładzie widzimy realizację (uproszczoną) przelewu. Najpierw "zabieramy" Nowakowi pewną kwotę z konta, a następnie dodajemy ją do salda Kowalskiego. Proste. Problem zacząłby się w momencie, kiedy po pierwszej operacji druga nie doszłaby do skutku. Jednak nasze dwa zapytania zostały objęte transakcją. Operacja START TRANSACTION rozpoczyna transakcję, którą kończy operacja COMMIT. Jeśli pomiędzy tymi dwoma operacjami coś się wydarzy, to wszelkie zmiany nie zostaną fizycznie zapisane do bazy (fizyczny zapis odbywa się dopiero w momencie wykonania COMMITa). Na dodatek na obu aktualizowanych rekordach zostanie założona blokada (LOCK), która nie pozwoli innej transakcji na ich aktualizację aż do momentu zakończenia naszej transakcji. Oczywiście możemy wymusić na silniku wycofanie zmian dokonanych w bieżącej transakcji - służy do tego instrukcja ROLLBACK.

Jako że PHP jest chyba najpopularniejszym językiem server-side, posłużę się prostym przykładem w tym właśnie języku:

Kod: Zaznacz cały
$result = mysql_query("START TRANSACTION") or die(mysql_error());
$sql = "UPDATE salda SET saldo = saldo - 190.55 WHERE nazwisko = 'Nowak'";
if(!$result = mysql_query($sql))
{
    echo("Błąd aktualizacji salda wysyłającego!");
    die(mysql_error());
    // tutaj ROLLBACK nie jest potrzebny - nic wcześniej nie zostało zmodyfikowane
}
$sql = "UPDATE salda SET saldo = saldo + 190.55 WHERE nazwisko = 'Kowalski'";
if(!$result = mysql_query($sql))
{
    echo("Błąd aktualizacji salda odbierającego! ".mysql_error());
    $result = mysql_query("ROLLBACK") or die(mysql_error());    // tutaj wycofujemy zmiany z pierwszego zapytania
}
// to takie paranoiczne zabezpieczenie - na wypadek gdyby COMMIT się nie udał
if(!$result = mysql_query("COMMIT"))
{
    $result = mysql_query("ROLLBACK") or die(mysql_error());
    die();
}

W przypadku tabel InnoDB każde działanie jest wykonywane w ramach jakiejś transakcji. Istnieje jednak coś takiego, jak AUTOCOMMIT. Jest on domyślnie włączony i powoduje, że każde zapytanie SQL stanowi jedną, osobną transakcję. W praktyce oznacza to, że po wykonaniu zapytania jest wykonywany automatycznie COMMIT (czyli wszystko działa tak, jakby transakcje w ogóle nie były używane).

Mała uwaga dla programistów PHP i innych języków server-side, używanych do tworzenia dynamicznych serwisów internetowych. Po wykonaniu skryptu połączenie z bazą zostanie zamknięte, a co za tym idzie, wykonana zostanie operacja COMMIT (czyli zostaną fizycznie zapisane wszystkie modyfikacje). Nie należy (nie da się) używać transakcji np. do blokowania edytowanego właśnie w formularzu rekordu, podobnie jak nie da się tego zrobić za pomocą LOCK TABLES.

Kolejną zaletą tabel typu InnoDB są tzw. poziomy izolacji, czyli rodzaje blokad nakładanych przez silnik na tabele. Nie będę zagłębiał się w szczegóły, bo temat jest dość szeroki, ale pozwolę sobie na porównanie z typem MyISAM. Mamy tabelę z PRIMARY KEY na jakimś unikalnym identyfikatorze (ID) i aktualizujemy pole 'ilosc' dla rekordu o ID = 5. W przypadku tabeli typu MyISAM zostanie zablokowana cała tabela, a w przypadku tabeli InnoDB - jedynie aktualizowany rekord. Jakie to ma znaczenie? W przypadku MyISAM, każda operacja UPDATE blokując całkowicie tabelę spowoduje, że wszystkie zapytania (włącznie z zapytaniami SELECT) będą "czekać" na zakończenie UPDATE. Jeśli z bazy korzysta jednocześnie wielu użytkowników, to taka "kolejka" może urosnąć do naprawdę sporych rozmiarów. Natomiast w przypadku tabeli InnoDB będą czekać tylko zapytania odwołujące się do aktualizowanego właśnie rekordu - a i to nie w każdym przypadku (istnieją poziomy izolacji, w których zapytania SELECT odczytają aktualizowany rekord, jednakże nie ma pewności co do tego, czy rekord ten jest już po modyfikacji czy jeszcze przed). Jak widać korzyść z zastosowania InnoDB jest oczywista. Czy aby na pewno w każdym przypadku? Trzeba pamiętać, że blokady na poziomie rekordu (row-level locks) powodują konieczność każdorazowego sprawdzenia tych blokad przy zapytaniach SELECT. Daje to w efekcie spadek wydajności tych zapytań. Spadek ten nie jest jednak szczególnie wielki - co można sprawdzić przeglądając dostępne wyniki testów wydajności.

Jeśli jednak poruszyłem już temat wydajności, to trzeba wspomnieć o istotnej rzeczy. W tabelach typu InnoDB rekordy są przechowywane (fizycznie, na dysku) w kolejności zgodnej z PRIMARY KEY, natomiast w tabelach typu MyISAM - w kolejności ich dodania. Daje to w efekcie w tabelach InnoDB przyśpieszenie zapytań SELECT, które korzystają z PRIMARY KEY, ale przy zapytaniach INSERT powoduje konieczność przesortowania pliku z danymi (jeśli trzeba dany rekord zapisać pomiędzy już istniejące). Według wyników niektórych testów, dopisywanie danych do tabel InnoDB może być od 2 do nawet 15 razy wolniejsze, niż do tabel MyISAM.

Dość istotną funkcją tabel typu InnoDB jest możliwość odtworzenia zawartości na podstawie logu. Jest to użyteczne w przypadku jakiejś awarii i uszkodzenia plików z danymi - szczególnie dlatego, że jest sporo szybsze niż (co ma miejsce w przypadku MyISAM) wykonanie pełnego skanowania tabeli, przebudowa lub naprawa indeksów i odzyskiwanie danych, które nie zostały w pełni zapisane na dysku. Na dodatek czas potrzebny na naprawę tabel InnoDB jest w miarę stały, a w przypadku tabel MyISAM rośnie on wraz z ilością przechowywanych danych.

Pozostałe różnice są niewielkie i (w większości przypadków) mało istotne w praktyce. Można do nich zaliczyć m. in.:

InnoDB:

  • brak indeksów pełnotekstowych (FULLTEXT),
  • brak możliwości umieszczenia kolumny z atrybutem AUTO_INCREMENT w indeksie zawierającym wiele kolumn (trzeba dla niej stworzyć osobny indeks),
  • ilość rekordów w tabeli nie jest w żaden sposób przechowywana; z tego względu zapytanie "SELECT COUNT(*) FROM tabela" powoduje wykonanie pełnego skanowania tabeli,
  • utworzenie nowej tabeli powoduje dokonanie przez silnik odpowiedniego wpisu w tzw. tablespace; z tego względu nie można przenieść/usunąć tabeli przez proste skopiowanie/usunięcie jej pliku .frm.

MyISAM:

  • ponieważ indeks jest skompresowany, zajmuje mniej miejsca na dysku, ale podczas aktualizacji potrzebuje więcej mocy procesora.

Zdaję sobie sprawę, że w tym krótkim tekście nie wyczerpałem tematu ani nie udzieliłem odpowiedzi na wszystkie pytania dotyczące typów tabel. Możliwe, że pominąłem jakieś istotne kwestie - jeśli tak rzeczywiście się stało, to z pewnością postaram się to nadrobić (proszę o pytania i komentarze). Sądzę jednak, że mimo wszystko przekazane informacje staną się pomocne w dokonaniu wyboru typu odpowiedniego do konkretnego zastosowania. Należy pamiętać, że nic nie stoi na przeszkodzie aby w jednej bazie czy jednej aplikacji wykorzystywać kilka typów tabel - stąd konieczność dokonania świadomego wyboru.

Na koniec dodam jeszcze jedno - we wszystkim należy zachować zdrowy rozsądek. Jeśli ktoś poświęca tydzień czasu na sprawdzanie wydajności różnego rodzaju kombinacji typów tabel, indeksów, systemów cache itd., itp., a tworzy prosty serwis dla swojej klasy w liceum, to jest to po prostu strata czasu. Zgadzam się, że należy stosować dobre praktyki zawsze i wszędzie, ale czasami skórka nie jest warta wyprawki. ;)

Masz pytania lub wątpliwości? Odwiedź nasze forum dyskusyjne.

Bartosz Romanowski

Programista, gadżeciarz, krytyczny miłośnik produktów Apple, fan ciężkich brzmień i niepoprawny pesymista.


Komentarze


HTML CSS JavaScript PHP bazy danych MySQL Flash grafika framework hosting domeny pozycjonowanie wordpress Facebook