ostatnio zmodyfikowany 10 lipca 2020
W tym rozdziale omówimy silniki magazynowe MySQL.
Silnik magazynowy to moduł oprogramowania, którego system zarządzania bazą danych używa do tworzenia, odczytu, aktualizacji danych z bazy danych. Istnieją dwa rodzaje silników magazynowania w MySQL: transakcyjny i nietransakcyjny.
Dla MySQL 5.5 i nowszych, domyślnym silnikiem magazynowania jest InnoDB.Domyślnym silnikiem magazynowania dla MySQL przed wersją5.5 był MyISAM. Wybór odpowiedniego silnika pamięci masowej jest ważną decyzją strategiczną, która będzie miała wpływ na przyszły rozwój. W tym tutorialu będziemy używać silników MyISAM, InnoDB, Memory orazCSV. Jeśli jesteś początkującym użytkownikiem MySQL i studiujesz system zarządzania bazą danych MySQL, to nie jest to zbyt istotne. Jeśli planujesz stworzyć produkcyjną bazę danych, wtedy sprawy stają się bardziej skomplikowane.
Lista mechanizmów magazynowania
MySQL obsługuje mechanizmy magazynowania:
- InnoDB
- MyISAM
- Memory
- CSV
- Merge
- Archive
- Federated
- Blackhole
- Example
InnoDB jest najczęściej używanym silnikiem pamięci masowej z obsługą transakcji. Jest to silnik pamięci masowej zgodny z ACID. Obsługuje blokowanie na poziomie rzędów, odzyskiwanie po awarii i wielowersyjną kontrolę współbieżności. Jest to jedyny silnik, który zapewnia integralność referencji klucza obcego. Oracle zaleca używanie InnoDB dla tabel z wyjątkiem wyspecjalizowanych przypadków użycia.
MyISAM jest oryginalnym silnikiem pamięci masowej. Jest to szybki silnik pamięci masowej, który nie obsługuje transakcji. MyISAM zapewnia table-levellocking. Jest używany głównie w sieci Web i hurtowniach danych.
Memory storage engine tworzy tabele w pamięci. Jest to najszybszy silnik. Zapewnia blokowanie na poziomie tabeli. Nie obsługuje transakcji.Silnik pamięci masowej jest idealny do tworzenia tabel tymczasowych lub szybkiego wyszukiwania.Dane są tracone po ponownym uruchomieniu bazy danych.
CSV Przechowuje dane w plikach CSV. Zapewnia to dużą elastyczność, ponieważ dane w tym formacie można łatwo zintegrować z innymi aplikacjami.
Merge operuje na bazowych tabelach MyISAM. Tabele scalające pomagają w łatwiejszym zarządzaniu dużymi ilościami danych. Logicznie grupuje serię identycznych tabel MyISAM i odwołuje się do nich jako do jednego obiektu.Dobre dla środowisk hurtowni danych.
Mechanizm przechowywania archiwów jest zoptymalizowany do szybkiego wstawiania danych. Kompresuje dane w trakcie ich wstawiania. Nie obsługuje transakcji. Idealnie nadaje się do przechowywania i pobierania dużych ilości rzadko przywoływanych danych historycznych, archiwalnych.
Silnik magazynowy Blackhole przyjmuje, ale nie przechowuje danych. Pobieranie danych zawsze zwraca pusty zbiór. Funkcjonalność ta może być wykorzystana w projektowaniu rozproszonych baz danych, gdzie dane są automatycznie replikowane, ale nie są przechowywane lokalnie. Ten silnik przechowywania może być używany do przeprowadzania testów wydajności lub innych testów.
Federowany silnik przechowywania oferuje możliwość oddzielenia serwerów MySQL w celu utworzenia jednej logicznej bazy danych z wielu fizycznych serwerów.Zapytania na lokalnym serwerze są automatycznie wykonywane na zdalnych (sfederowanych) tabelach. Żadne dane nie są przechowywane na tabelach lokalnych.Jest to dobre rozwiązanie dla środowisk rozproszonych.
mysql> SHOW ENGINES\G*************************** 1. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 2. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO...
Komenda SHOW ENGINES pokazuje wszystkie dostępne silniki obsługiwane przez serwer.
Wybór właściwego silnika
Żaden silnik pamięci masowej nie jest idealny we wszystkich okolicznościach. Niektóre sprawdzają się najlepiej w określonych warunkach, a w innych wypadają gorzej. Istnieją kompromisy, które należy rozważyć. Bardziej bezpieczne rozwiązanie wymaga więcej zasobów; może być wolniejsze, zajmować więcej czasu procesora i miejsca na dysku. MySQL jest bardzo elastyczny, ponieważ udostępnia kilka różnych silników przechowywania danych. Niektóre z nich, jak np. silnik archiwum, zostały stworzone do wykorzystania w specyficznych sytuacjach.
W niektórych przypadkach odpowiedź jest oczywista. Zawsze, gdy mamy do czynienia z systemami płatności, jesteśmy zobligowani do zastosowania najbezpieczniejszego rozwiązania. Nie możemy sobie pozwolić na utratę tak wrażliwych danych. InnoDB jest właściwym rozwiązaniem. Jeżeli zależy nam na full-textsearch, to możemy wybrać albo MyISAM albo InnoDB. Tylko InnoDB obsługuje ograniczenie integralności referencyjnej klucza obcego i jeśli planujemy używać tego ograniczenia, to wybór jest jasny.
Określanie i zmienianie silników pamięci masowej
Silnik pamięci masowej jest określany w czasie tworzenia tabeli.
mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), -> Cost INTEGER) ENGINE='MyISAM';
Słowo kluczowe ENGINE określa silnik pamięci masowej używany dla tej konkretnej tabeli.
Jeżeli nie określimy wyraźnie mechanizmu przechowywania danych, wówczas zostanie użyty domyślny mechanizm przechowywania danych. Przed wersją MySQL 5.5 domyślnym mechanizmem przechowywania danych był MyISAM. Dla wersji 5.5 i nowszych domyślnym mechanizmem jest InnoDB.
Możliwa jest migracja do innego mechanizmu magazynowania. Należy pamiętać, że migracja dużych tabel może zająć dużo czasu. Możemy również napotkać pewne problemy podczas migracji tabel. Niektóre funkcje mogą nie być obsługiwane w obu tabelach.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| InnoDB |+--------+1 row in set (0,05 sec)
To wyrażenie SQL określa mechanizm przechowywania danych używany dla tabeli Cars w mydb bazie danych. Moglibyśmy również użyć instrukcji SELECT CREATE TABLE Cars SQL. information_schema jest tabelą, która przechowuje informacje techniczne o naszych tabelach.
mysql> ALTER TABLE Cars ENGINE='MyISAM';
Ta instrukcja SQL zmienia mechanizm przechowywania tabeli Cars na MyISAM.
mysql> SELECT ENGINE FROM information_schema.TABLES -> WHERE TABLE_SCHEMA='mydb' -> AND TABLE_NAME='Cars';+--------+| ENGINE |+--------+| MyISAM |+--------+1 row in set (0,00 sec)
Teraz mechanizmem przechowywania tabeli jest MyISAM.
W tej części samouczka MySQL omówiliśmy silniki magazynowania.