Najważniejszym komponentem systemu zarządzania bazami danych jest wewnętrzny mechanizm składowania danych (ang. storage engine). Jest to moduł pracujący na niskim poziomie abstrakcji, którego rolą jest po prostu składowanie danych. Zawiera szczegółowe implementacje tego jak dane są otwierane, zamykane, odczytywane, wstawiane, aktualizowane i usuwane. MySQL poza gotowymi trybami składowania danych oferuje tworzenie własnych ale nie trzeba się w to za bardzo zagłębiać.
W tym artykule porównamy gotowe typy tabel jakie można wykorzystywać w bazach danych MySQL. Dzięki tej wiedzy będziemy mogli z większą świadomością wybierać tryby przechowywania danych w tabelach przy tworzeniu i optymalizacji baz danych MySQL co jak wiadomo będzie miało same korzyści z punktu widzenia wydajności i stosunku efektywności do kosztów utrzymywania naszych baz danych.
Za pomocą komendy mysql> SHOW STORAGE ENGINES; wyświetlimy wszystkie tryby jakie oferuje nasza wersja serwera baz danych.
MyISAM
Typ MyISAM to domyślny i oryginalny mechanizm składowania danych w MySQL. Każda tabela tego typu jest zapisywana na trzech plikach.
- .frm – zawiera definicję tabeli
- .myd – zawiera dane tabeli
- .myi – zawiera indeksy
Mechanizm MyISAM oferuje bardzo wysoką wydajność zarówno dla operacji zapisu jak i odczytu. Bierze się to stąd, że ilość operacji wejścia-wyjścia jest mniejsza z powodu prostszej metody działania tego systemu. Niestety, nie umożliwia wykonywania tych operacji jednocześnie.
Przy każdym odczytywaniu danych z tabeli typu MyISAM, system składowania danych nakłada na całą tabelę blokadę READ LOCK, umożliwiającą wiele odczytów ale blokującą wszystkie operacje zapisu (tak naprawdę aktualizacji innych niż wstawianie czyli aktualizacji UPDATE i usuwania DELETE).
Przy zapisywaniu, MyISAM nakłada blokadę typu WRITE, która jest stosowana za każdym razem kiedy wstawiamy nowe rekordy do tabeli, aktualizujemy je lub usuwamy. Blokada ta blokuje wszystkie inne operacje aktualizacji i odczytu w całej tabeli. Wyjątkiem jest jednoczesne wstawianie rekordów – tutaj nie dochodzi do wzajemnej blokady.
Można z tego wywnioskować, że mimo wysokiej wydajności tego trybu składowania danych, poziom współbieżności bazy złożonej z tabel tego typu jest niski. MyISAM nie obsługuje transakcji. Dlatego trudniej utrzymać integralność w przypadku baz danych przechowujących zapis operacji złożonych z kilku powiązanych ze sobą zdarzeń (np. operacja przelewu bankowego wymaga zmniejszenia dostępnych środków u jednego klienta banku i zwiększenia ilości środków u drugiego). Kłania się zasada ACID.
Do niedawna wielką przewagą tabel typu MyISAM był indeks typu FULLTEXT które umożliwiał bardzo szybkie wyszukiwanie słów w tabel przechowujących spore bloki tekstu.
InnoDB
Mechanizm InnoDB, zawiera w sobie rozwiązania, których brakowało w MyISAM. Jak można się domyślić, niestety jest to okupione nieco mniejszą wydajnością „surowych” zapisów i odczytów z bazy danych.
Mechanizm InnoDB umożliwia utrzymywanie danych w zgodzie z koncepcją ACID (Atomicity, Consisteny, Isolation, Durability). Jest to możliwe za pomocą prowadzenia dziennika transakcji, z którego zawartość można wykorzystać do przywrócenia integralności w przypadku nieoczekiwanych awarii i niespójności. Jak można się domyślić, umożliwia przez to przetwarzanie transakcyjne.
Dostęp do tabel InnoDB jest blokowany za pomocą mechanizmy MVCC (Multi-Versioned Concurrency Control), który w przeciwieństwie do MyISAM nie blokuje całych tabel ale jest w stanie blokować tylko pojedyncze rekordy. Jest to możliwe dzięki utrzymywaniu kopii rekordów aktualizowanych w ramach transakcji. Dzięki temu, operacje zapisu nie muszą blokować operacji odczytu co pozytywnie wpływa na współbieżność baz danych tworzonych z wykorzystaniem tego typu tabel. Poza tym, InnoDB umożliwia stosowanie kluczy obcych.
Jednym z minusów (poza wcześniej wspomnianą mniejszą wydajnością) jest około trzykrotnie większa przestrzeń zajmowana przez tabele tego typu. Co może utrudniać utrzymywanie naprawdę dużych zbiorów danych. W starszych wersjach MySQL nie można było korzystać z indeksowania typu FULLTEXT. Na szczęście od wersji 5.6.4 wprowadzono to indeksowanie, które podobno działa jeszcze lepiej niż w MyISAM.
Memory
Memory (inna nazwa to Heap) – tabele, których zawartość nie jest w ogóle zapisywana na dysku twardym tylko w pamięci RAM serwera MySQL. Po restarcie serwera tabele są puste. Na dysku zapisuje się jedynie schemat tabeli. Mimo że stosuje podobne blokady jak w przypadku MyISAM (blokuje całe tabele) działanie takiej bazy danych jest błyskawiczne i gwarantuje dobrą współbieżność, ponieważ pamięć RAM działa dużo szybciej niż jakikolwiek dysk twardy.
Wadą tego mechanizmu jest oczywiście konieczność przechowywania stosunkowo małych tabel – takich które całe są w stanie się zmieścić w pamięci operacyjnej. Tabele typu Memory stosujemy wtedy kiedy potrzebujemy szybkiego dostępu do danych, które są wstanie zapisywać się w czasie rzeczywistym i nie jest potrzebne ich trwałe utrwalenie.
O bazach typu Memory można myśleć jakby były uniwersalną pamięcią operacyjną dla całej aplikacji – można ją wykorzystać np. w roli prostej pamięci podręcznej – w której możemy wykorzystać system do zarządzania bazą danych i semantykę zapytań z języka SQL.
Archive
Na koniec został nam jeszcze Archive. Jak sama nazwa wskazuje, Archive stosuje się do specjalnych zastosowań gdzie nie wykorzystujemy indeksów a chcemy mieć możliwość archiwizowania danych. Z danych zawartych w tabelach typu Archive korzysta się jak z pamięci masowej.
Ze cech charakterystycznych dla tego mechanizmu składowania jest fakt, że dane nie mogą być usuwane tylko dodawane. Ze względu na brak indeksów, każde zapytanie SELECT wymaga skanowania całego pliku co powoduje, że działa ona bardzo wolno. Jedynym ratunkiem jest wewnętrzny bufor MySQL, który przyspieszy zwracanie wyników dla często wykonywanych zapytań.
W tym silniku też nie mamy możliwości skorzystania z transakcji.
Nie stosuje blokad na poziomie tabeli dlatego można współbieżnie dodawać poszczególne rekordy jednak w zamian nie gwarantuje kolejności wstawiania danych do tabeli.
Porównanie wybranych cech silników składowania danych w MySQL
Wykorzystanie trybów składowania w bazach większej skali
Dywersyfikację trybów składowania wykorzystuje się w replikacji baz MySQL. Zaawansowana replikacja pozwala na wyodrębnienie z danych aplikacji baz albo pod-węzłów wyspecjalizowanych. Jeżeli stosują one częste odczyty można zastosować wówczas tryb MyISAM jeżeli zaś muszą obsługiwać transakcje wykorzystuje się InnoDB.
Drugim zastosowaniem trybów składowania w aplikacjach internetowych dużej skali jest wykorzystanie trybu MEMORY w bazach, które służą do przechowywania sesji w bazie MySQL. Współbieżność w takim wypadku jest dużo ważniejsza, transakcje niepotrzebne i nie będzie tragedią jeżeli taki serwer się zrestartuje, ponieważ sesje z natury są tymczasowe.
Źródła
https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html
Schwartz B., Zaitsev P., Tkachenko V., High Performance MySQL. Optimization, Backups, Replication, and More, O’Reilly 2012
Henderson C. Building Scalable Web Sites. Building, Scaling, and Optimizing the Next Generation of Web Applications, Helion 2006.
Odpowiedz lub skomentuj