MySQL vs PostgreSQL vs SQLite


29 sierpnia, 2010
BMW competition

Na rynku darmowych, SQL-owych  systemów bazodanowych, mamy trzech głównych graczy (firebird-a pomijamy): MySQL, PostgreSQL oraz SQLite. Co je charakteryzuje i czym się różnią? Który z nich wybrać? W sieci można znaleźć dużo testów porównujących wydajność, które swoją drogą, są już nieaktualne lub mierzą jeden konkretny przypadek mający się nijak do środowiska pracy rzeczywistej.

Nie będziemy skupiać się na przykładach i testach syntetycznych prowadzących do wniosków, że baza X jest lepsza, bo zapytanie Y działa tam najszybciej, gdyż takie podejście jest mylne. Naszą intencją jest przedstawienie systemów bazodanowych w szerszej perspektywie.

MySQL

Pierwsza wersja tego systemu została wydana 23 maja 1995 roku przez zespół fińskich i szwedzkich programistów.  Została powołana firma MYSQL AB, gdzie rozwijano projekt. Przedsiębiorstwo było jednym z największych przedsięwzięć w sektorze rozwiązań typu open-source na skalę światową. W lutym 2008 roku firma zostaje kupiona przez Sun Microsystems, a w styczniu 2010 roku firma Oracle wykupiła Sun. Przyszłość MySQL wydaje się być niejasna, jednak warto mieć na uwadze projekty poboczne: Drizzle oraz MariaDB.

MySQL obsługuje indeksy typu przestrzennego, widoki, triggery, procedury, tabele w pamięci. Ma replikację na poziomie zapytań i wierszy. Posiada interfejs plugin-ów umożliwiający podłączanie dowolnych funkcjonalności.

Istotną cechą wyróżniającą ten system, jest bogaty wybór tzw. storage engine nawet na poziomie konkretnej tabeli, co pozwala na szczegółowe dostosowanie systemu do konkretnych pożądanych cech środowiska bazodanowego. Poniżej krótka charakterystyka:

Memory:

  • Brak transakcji
  • Indeksy: Hash, BTree
  • Przechowywanie w pamięci RAM
  • Buforowanie: nie dotyczy
  • Blokady na poziomie tabeli
  • Brak kluczy obcych
  • Uwagi: zawartość jest ulotna.

MyISAM:

  • Brak transakcji
  • Indeksy: BTree, RTree, FullText (możliwość spakowania poszczególnych indeksów w celu zmniejszenia zajmowanego miejsca, często prowadzi do przyspieszenia odczytu, ale spowalnia zapis)
  • Przechowywanie w plikach (osobno dane, osobno indeksy dla każdej tabeli)
  • Buforowanie: cache dla indeksów, dane poprzez cache systemu plików, istnieje możliwość wykorzystania indeksów pokrywających.
  • Blokady na poziomie tabeli, ale umożliwia współbieżne dopisywanie nowych rekordów (concurent Insert).
  • Brak kluczy obcych
  • Uwagi: możliwość spakowania całej tabeli w celu zmniejszenia zajmowanego miejsca (tabela tylko do odczytu).

InnoDB:

  • Obsługa transakcji (wiele poziomów izolacji), MVCC, ACID
  • Indeksy: BTree + Adaptive Hash Index
  • Przechowywanie, dane razem z indeksami, w plikach osobno dla każdej tabeli, wiele tabel w pliku (tzw. tablespace), bezpośrednio na urządzeniu blokowym (także jako table space).
  • Buforowanie: ujednolicony bufor stron dla danych, indeksów, metadanych oraz różnych wersji rekordów (MVCC).
  • Blokady na poziomie rekordu
  • Obsługa kluczy obcych
  • Uwagi: Dane i indeksy przechowywane są w postaci B-Drzewa, przez co zawsze musi istnieć klucz główny, jeśli nie jest stworzony jawnie InnoDB automatycznie utworzy ukryty. Pozostałe indeksy nie wskazują bezpośrednio na rekordy danych, ale na indeks główny.

Archive:

  • Brak transakcji
  • Brak indeksów (Zoptymalizowana pod kątem pełnego skanowania tabeli)
  • Przechowywanie: w postaci skompresowanego pliku
  • Buforowanie: cache systemu plików
  • Blokady na poziomie rekordu
  • Brak kluczy obcych
  • Uwagi: Umożliwia tylko wykonywanie operacji Insert i Select. Świetnie nadaje się do przechowywania danych statystycznych tam gdzie zwykle/zawsze operuje się na pełnym zbiorze.

CSV:

  • Brak transakcji
  • Brak indeksów
  • Przechowywanie: w postaci pliku CSV
  • Buforowanie: cache systemu plików
  • Brak blokad
  • Brak kluczy obcych
  • Uwagi: Umożliwia użycie pliku z danymi w formacie CSV jako tabeli tylko do odczytu (select only) i wykonywanie na danych zapytań w języku SQL.

Merge:

  • Uwagi: Mechanizm umożliwiający połączenie kilku tabel MyISAM i traktowanie ich jako jednej. Ma wszystkie cechy MyISAM poza obsługą indeksów pełnotekstowych.

Federated:

  • Brak transakcji
  • Przechowywanie: zdalny serwer MySQL
  • Uwagi: umożliwia użycie tabel przechowywanych na zdalnych serwerach MySQL. Pozostałe cechy zależą od mechanizmu przechowywania tabel na zdalnym serwerze.

NDB (cluster):

  • Obsługa transakcji
  • Indeksy: Hash, BTree
  • Przechowywanie: zdalne i lokalne węzły klastra przechowują dane w RAM i okresowo zrzucają je na dysk
  • Buforowanie: pamięć podręczna kluczy i danych
  • Blokada na poziomie wiersza
  • Brak kluczy obcych
  • Uwagi: To bardzo zaawansowane rozwiązanie do specyficznych zastosowań. Obsługuje wewnętrzną synchroniczną replikację z dwufazowym zatwierdzaniem oraz partycjonowanie danych na bazie klucza podstawowego.

Blackhole:

  • Uwagi: Ten mechanizm nie przechowuje żadnych danych, jest użyteczny w niektórych scenariuszach replikacji.

Istnieje również stworzony przez firmę Percona ulepszony engine na bazie InnoDB o nazwie XtraDB. Nie różni się on jednak podstawowymi funkcjonalnościami od pierwowzoru. Od wersji 5.1.33 dostępny jest także mechanizm IBMDB2I firmy IBM.

Do obsługi wielu połączeń, wykorzystywany jest mechanizm wątków.

MySQL’a można używać korzystając jednej z licencji: GPL lub komercyjnej.

PostgreSQL

Produkt ten wywodzi się z bazy Ingres ’82, poprzez Postgres (nazwa pochodzi od Post-Ingres) ’89, dalej Postgres95 posiadający język SQL zamiast QUEL, aby ostatecznie nazwać się PostgreSQL dla zaakcentowania zastosowania języka SQL ’96.

System określany jest jako jeden z najbardziej zaawansowanych systemów bazodanowych i ciężko się z tym stwierdzeniem nie zgodzić. Oczywiście oprócz pełnej kompatybilności z ACID, oferuje szereg ogromnie rozbudowanych funkcjonalności:

  • Typy danych: oprócz popularnych, możemy operować na danych przestrzennych (spatial), a nawet definiować własne typy danych.
  • Indeksy: możliwe są wszelkie kombinacje indeksów częściowych, funkcyjnych, wielokolumnowych czy pełnotekstowych.
  • Transakcje: są integralną częścią systemu i pozwalają na zdefiniowanie różnych poziomów izolacji.
  • Język proceduralny: mocno rozbudowany PL/pgSQL pozwala na oprogramowanie praktycznie każdej złożonej operacji na danych. Ponadto, możliwe jest tworzenie kodu w innym języku proceduralnym, jak np: PL/Perl czy PL/Python
  • Inne: wielce funkcjonalne widoki (views), wyzwalacze (triggers), sekwencje

Jedynie brak spójnego i zintegrowanego systemu wysokiej dostępności, rozłożenia obciążenia i replikacji nie pozwala zaliczyć tego systemu do rozwiązania klasy enterprise. Należy zaznaczyć, iż takie funkcjonalności istnieją, jednak za pośrednictwem rozwiązań zewnętrznych jak np. Slony-II, a natywna replikacja planowana jest na następne wydanie systemu.

Do obsługi wielu połączeń, wykorzystywany jest mechanizm rozwidlania procesów.

PostgreSQL używamy na podstawie własnej licencji tożsamej z licencją BSD czy MIT.

SQLite

System powstał w 2000 roku za sprawą  pracownika General Dynamics na zlecenie US Navy jako projekt mający zastąpić ówczesny system wykorzystywany na niszczycielach rakietowych. Nowy system był projektowany z myślą o braku konieczności skomplikowanej instalacji i zarządzania nim. Ponadto, zrezygnowano z architektury klient-serwer na rzecz biblioteki dołączanej do aplikacji, która dzięki swojemu API operuje na bazie na zasadzie dostępu do lokalnego pliku (podobnie jak np. DBM). W tym miejscu należy zaznaczyć, że baza spełnia warunki określane jako ACID.

Funkcjonalności bazy, jak na rozmiar jej biblioteki, jest bardzo duża: bogaty dialekt SQL, indeksy, transakcje.  Z racji prostej architektury i braku narzutu zdalnej komunikacji, jest również bardzo szybka. Nie nadaje się jednak jako główny system bazodanowy w środowiskach kilku serwerów. Doskonale się natomiast sprawdza w systemach typu embedded (np. Android), czy jako rozwiązania wspomagające (np portage). Niewiele osób ma świadomość,  że  SQLite wykorzystywany jest przez Firefox-a i Thunderbird-a.

Do obsługi wielu połączeń, wykorzystywany jest mechanizm blokady dostępu do lokalnego pliku – bazy danych.

z SQLite korzystamy na podstawie licencji Public Domain

Podsumowanie

Wybór SQLite nad pozostałymi systemami jest dość prosty, gdy mamy bardzo konkretne zastosowanie. Z pozostałymi systemami wybór nie jest już tak oczywisty. Należy wziąć pod uwagę kwestie licencji, jakich funkcjonalności będziemy potrzebowali i z jakim systemem czujemy się najbardziej komfortowo pod względem obsługi. Nie należy natomiast ślepo kierować się opiniami o lepszej wydajności, gdyż te opinie prawie zawsze odnoszą się do konkretnych przypadków – niekoniecznie naszych. Miejmy również na uwadze fakt, iż wymienione projekty są aktywnie rozwijane, zatem lista funkcjonalności dla każdego z nich stale się powiększa.


Copyright © InnerVision Sp. z o.o.. All rights reserved.