Jakie są zalety i wady MySQL oraz kiedy jest dobrym wyborem?

MySQL to relacyjny system zarządzania bazą danych, który może być używany w typowych usługach internetowych i przetwarzaniu transakcji online (OLTP), zwłaszcza przy użyciu silnika składowania InnoDB oraz jego funkcji, takich jak transakcje, blokowanie na poziomie wierszy i spójne odczyty. Z drugiej strony replikacja odczytu jest domyślnie asynchroniczna, a konfiguracje wysokiej dostępności, złożone zapytania i partycjonowanie wiążą się z ograniczeniami projektowymi i operacyjnymi. Zalety stają się rzeczywistymi zaletami tylko wtedy, gdy odpowiadają obciążeniu i możliwościom operacyjnym zespołu. dev.mysql.com↗ dev.mysql.com↗

Podczas oceny MySQL trafniejsze jest rozważenie sposobu odczytu i zapisu danych, gwarancji wymaganych w razie awarii oraz stopnia złożoności schematu niż proste pytanie, czy jest to „szybka baza danych”. Poniższe omówienie opiera się na zakresie oficjalnej dokumentacji MySQL 8.4. Rzeczywiste zachowanie może różnić się zależnie od wersji, silnika składowania, konfiguracji i topologii replikacji. dev.mysql.com↗

Jakim rodzajem bazy danych jest MySQL?

Relacyjna baza danych przechowuje dane w tabelach złożonych z wierszy i kolumn oraz używa języka zapytań SQL do zarządzania relacjami między tabelami. Na przykład sklep internetowy może korzystać z tabel takich jak customers, orders i order_items, aby zarządzać relacjami między klientami, zamówieniami i zamówionymi produktami. W wielu przypadkach kilka zmian trzeba zgrupować w jedną operację, na przykład utworzenie zamówienia, zmniejszenie stanu magazynowego i zapisanie statusu płatności.

Silniki składowania są w MySQL ważne. Silnik składowania to komponent odpowiedzialny za sposób przechowywania, blokowania i odzyskiwania tabel. W szczególności InnoDB jest domyślnym silnikiem składowania MySQL i zapewnia transakcje ACID, zatwierdzanie i wycofywanie transakcji, odzyskiwanie po awarii, blokowanie na poziomie wierszy, wielowersyjną kontrolę współbieżności (MVCC) oraz klucze obce. Dlatego niezawodność transakcyjna powszechnie kojarzona z MySQL często dotyczy MySQL używającego poprawnie skonfigurowanych tabel InnoDB. dev.mysql.com↗

ACID to zbiorcza nazwa oczekiwanych właściwości transakcji. Atomowość oznacza, że cała operacja albo się powiedzie, albo zostanie anulowana. Spójność oznacza zachowanie zdefiniowanych reguł dotyczących danych. Izolacja kontroluje wpływ równocześnie wykonywanych operacji na siebie nawzajem, a trwałość oznacza, że zatwierdzone wyniki muszą przetrwać awarie. Na właściwości ACID w MySQL wpływają także silnik, konfiguracja, sprzęt i procedury operacyjne, dlatego sama nazwa nie powinna być rozumiana jako automatyczne rozwiązanie każdego scenariusza awarii. dev.mysql.com↗

Dlaczego transakcje InnoDB i współbieżność są zaletą?

OLTP oznacza obciążenia z częstymi, względnie krótkimi żądaniami, takimi jak przyjmowanie zamówień, aktualizowanie danych członkowskich czy zmiana statusu płatności. W takim środowisku wielu użytkowników może jednocześnie modyfikować te same rodzaje danych, dlatego ważne jest bezpieczne grupowanie zmian danych i maksymalne ograniczanie zakresu konfliktów.

Ponieważ InnoDB zapewnia zatwierdzanie i wycofywanie transakcji oraz odzyskiwanie po awarii, aplikację można skonfigurować tak, aby wycofywała transakcję, jeśli jeden krok zawiedzie, na przykład podczas tworzenia zamówienia i zmniejszania zapasu. Blokowanie na poziomie wierszy blokuje konkretne wiersze w miarę potrzeby i może być korzystniejsze dla umożliwienia pracy współbieżnej niż szerokie blokowanie całej tabeli. Oczekiwania i konflikty nie znikają jednak, gdy wiele operacji często konkuruje o te same wiersze lub sąsiadujące zakresy danych. dev.mysql.com↗

MVCC zapewnia spójne odczyty poprzez użycie wielu wersji danych. Nie oznacza po prostu, że odczyty i zapisy nigdy sobie nie przeszkadzają. Obserwowane wyniki i zachowanie blokad mogą się różnić w zależności od poziomu izolacji transakcji, wykonywanych instrukcji SQL oraz użycia odczytów blokujących. Dlatego podczas rozwiązywania problemów ze współbieżnością nie należy sprawdzać wyłącznie nazwy silnika. Najpierw określ w regułach biznesowych, które odczyty muszą widzieć najnowszą wartość, a które aktualizacje muszą wzajemnie się wykluczać.

Klucz obcy to ograniczenie pomagające zapewnić, że wartość w jednej tabeli odwołuje się do istniejącego wiersza w innej tabeli. Na przykład może wymuszać, aby identyfikator klienta w zamówieniu wskazywał na rzeczywistego klienta. Może to pomóc ograniczyć nieprawidłowe odwołania, ale oznacza też, że zasady usuwania i aktualizacji oraz struktura tabel muszą być wcześniej starannie zaprojektowane. Jeśli planujesz późniejsze wprowadzenie partycjonowania, musisz również sprawdzić ograniczenia zgodności dotyczące kluczy obcych. dev.mysql.com↗ dev.mysql.com↗

Jakie zalety MySQL oferuje środowiskom programistycznym i kontroli dostępu?

MySQL udostępnia wiele protokołów klienckich i API dla C/C++, Java, PHP, Python, Ruby oraz innych języków. Aplikacje już korzystające z tych języków i narzędzi mają więc możliwości budowy warstwy połączeń, a utworzenie podstawowej ścieżki między aplikacją internetową a bazą danych może być stosunkowo łatwe. Jednak obecność API dla określonego języka sama w sobie nie gwarantuje poprawnej konfiguracji puli połączeń, ponawiania prób po błędach, zestawów znaków i obsługi stref czasowych. Podejście aplikacji do dostępu do danych musi zostać zweryfikowane osobno. dev.mysql.com↗

System uprawnień również stanowi część projektu operacyjnego. MySQL zapewnia uprawnienia na poziomie globalnym, bazy danych i obiektu, a także uprawnienia dynamiczne. Można go użyć do rozdzielenia ról: na przykład przyznać kontu aplikacji tylko uprawnienia odczytu i zapisu potrzebne dla określonych tabel, a do zadań tworzenia kopii zapasowych i administracyjnych używać oddzielnych kont. Zasada najmniejszych uprawnień jest użyteczną zasadą projektową ograniczającą skutki przejęcia konta lub błędu w programie. dev.mysql.com↗

Jednak większa szczegółowość uprawnień sama w sobie nie zapewnia pełnego bezpieczeństwa. W praktyce należy zarządzać tym, które konta mają jakie uprawnienia, czy konta administratorów i aplikacji są rozdzielone oraz jaki proces reguluje zmiany uprawnień. Innymi słowy, funkcje uprawnień MySQL zapewniają mechanizmy kontroli, ale odpowiedzialność za ich przydzielenie zgodnie z rolami biznesowymi pozostaje po stronie operacji.

Jakie problemy rozwiązują indeksy i partycjonowanie?

Indeks to struktura danych zaprojektowana tak, aby ograniczyć konieczność skanowania całej tabeli w celu znalezienia żądanych wierszy. Na przykład jeśli często występują żądania znalezienia pojedynczego zamówienia według numeru zamówienia, indeks na tej kolumnie może pomóc. Indeksy wielokolumnowe mogą być przydatne dla zapytań wykorzystujących kilka kolumn łącznie jako warunki, ale znaczenie mają kolejność kolumn i faktyczne predykaty zapytania. InnoDB obsługuje do 64 indeksów wtórnych na tabelę i do 16 kolumn w indeksie wielokolumnowym. dev.mysql.com↗

Indeksy nie są jednak automatycznie lepsze tylko dlatego, że utworzono ich więcej. Zajmują przestrzeń dyskową i muszą być również utrzymywane podczas wstawiania, aktualizowania lub usuwania wierszy. Limit liczby obsługiwanych indeksów jest limitem technicznym, a nie celem projektowym. To, czy indeks skracający ścieżkę wyszukiwania jest rzeczywiście potrzebny oraz jakie obciążenie dodaje do ścieżek zapisu, należy oceniać na podstawie reprezentatywnych zapytań i rozkładu danych.

Indeksy łańcuchów znaków mają również ograniczenia fizyczne. Limit prefiksu klucza indeksu InnoDB wynosi zazwyczaj 3072 bajty, choć zależnie od formatu wiersza może zostać zmniejszony do 767 bajtów. Próba indeksowania długich łańcuchów za pomocą zestawu znaków o dużym rozmiarze przechowywania na znak, takiego jak utf8mb4, może sprawić, że limit ten wpłynie na projekt schematu. Szczególnie ważne jest rozróżnienie, że jest to limit oparty na bajtach, a nie na liczbie znaków. dev.mysql.com↗

Partycjonowanie to funkcja, która przechowuje jedną tabelę w wielu partycjach zgodnie ze zdefiniowanymi regułami. Jeśli warunek odpowiada regułom partycjonowania, odcinanie partycji może wykluczyć partycje, których MySQL nie musi przeszukiwać. Na przykład w przypadku dużej tabeli historii przeszukiwanej według zakresu dat, jeśli tabela jest partycjonowana według daty, można rozważyć projekt zmniejszający zakres docelowy dla wyszukiwań w konkretnym okresie. dev.mysql.com↗

Nie oznacza to, że każda duża tabela powinna być partycjonowana. Jeśli często używane warunki nie odpowiadają kluczowi partycjonowania, oczekiwane ograniczenie danych docelowych może nie nastąpić. Partycjonowanie wprowadza też dodatkowe reguły operacyjne, dotyczące projektowania kluczy i ograniczeń, dlatego lepiej najpierw porównać, czy problem można rozwiązać prostszymi indeksami i ulepszeniami zapytań.

Jakie ograniczenia dotyczą partycjonowania i wyszukiwania pełnotekstowego?

W MySQL 8.4 partycjonowanie jest obsługiwane przez silniki składowania InnoDB i NDB. Partycjonowana tabela InnoDB nie może mieć kluczy obcych ani być celem odwołań kluczy obcych z innej tabeli. Ponadto każda kolumna użyta w kluczu partycjonowania musi należeć do każdego klucza unikalnego, w tym klucza głównego. Ten warunek może znacząco zmienić model podczas próby partycjonowania kluczowej tabeli o gęstych odwołaniach, takiej jak tabela zamówień. dev.mysql.com↗

Wyszukiwanie pełnotekstowe to funkcja wyszukiwania tekstu według słów. MySQL obsługuje wyszukiwanie pełnotekstowe w InnoDB i MyISAM, ale nie jest ono obsługiwane w tabelach partycjonowanych. Dlatego jeśli w tej samej tabeli oczekujesz zarówno funkcji wyszukiwania w długich dokumentach, jak i partycjonowania dużych danych historycznych, należy wcześnie sprawdzić, czy takie połączenie jest możliwe. Dodanie funkcji później może wymagać podziału tabel lub zmiany architektury wyszukiwania. dev.mysql.com↗

Ograniczenia te pokazują nie tylko, że MySQL nie ma pewnych funkcji, lecz że funkcje mogą nie być ze sobą niezależnie łączalne. Możesz sprawdzić oddzielnie potrzebę kluczy obcych, kluczy unikalnych, kluczy partycjonowania i wyszukiwania pełnotekstowego. Bezpieczniej jest unikać podejmowania decyzji o schemacie na podstawie korzyści tylko jednej funkcji.

Jak można wykorzystać replikację do skalowania odczytu i tworzenia kopii zapasowych?

Replikacja jest architekturą przesyłającą zmiany z jednego serwera do drugiego. Zazwyczaj serwer źródłowy rejestruje zmiany, a serwery replik je stosują. Rozłożenie części żądań odczytu na wiele replik może zmniejszyć obciążenie odczytem serwera źródłowego, a zadania tworzenia kopii zapasowych lub analityczne można również przenieść na repliki. dev.mysql.com↗

GTID to metoda obsługi pozycji replikacji przez przypisanie identyfikatora każdej transakcji. Replikacja oparta na GTID może pomóc ograniczyć konieczność ręcznego uzgadniania nazw plików dziennika binarnego i pozycji. Jednak ustanowienie topologii replikacji różni się od monitorowania opóźnienia replikacji i obsługi procedur odzyskiwania. Należy określić, który serwer obsługuje zapisy, które serwery mogą obsługiwać odczyty oraz co robić, gdy wystąpi opóźnienie. dev.mysql.com↗

Domyślna replikacja jest asynchroniczna. Oznacza to, że w chwili zakończenia zatwierdzania na serwerze źródłowym nie ma gwarancji, iż każda replika zastosowała tę samą zmianę. Na przykład zapytanie skierowane do repliki bezpośrednio po zmianie adresu przez użytkownika może wyświetlić poprzedni adres. Można to postrzegać jako problem spójności odczytu po zapisie. Żądania, które muszą uzyskać najnowsze dane, wymagają polityki kierującej je do serwera źródłowego albo uwzględniającej status zastosowania zmian przez replikę. dev.mysql.com↗

Replikacja półsynchroniczna wykorzystuje podejście, w którym serwer źródłowy otrzymuje potwierdzenie, że replika odebrała i zapisała zdarzenie transakcji w dzienniku. Jest to alternatywa dla domyślnej replikacji asynchronicznej, ale nie oznacza, że każde wymaganie staje się w pełni synchroniczne. Przy omawianiu silnych wymagań synchronicznych należy jasno zdefiniować wymagany poziom spójności, akceptowalny zakres opóźnień i zachowanie w razie awarii, a następnie rozważyć również odrębne opcje, takie jak NDB Cluster. dev.mysql.com↗

Czy Group Replication automatycznie rozwiązuje problem wysokiej dostępności?

Wysoka dostępność to cel polegający na skonfigurowaniu systemu tak, aby usługa mogła działać dalej, gdy zawiedzie część serwera lub sieci. Group Replication zarządza członkostwem w grupie, automatycznie wybiera serwer podstawowy w trybie single-primary albo obsługuje konfiguracje multi-primary. Możliwość utworzenia topologii wysokiej dostępności w połączeniu z InnoDB Cluster i MySQL Router jest ważną opcją MySQL. dev.mysql.com↗

Jednak konsensus między serwerami bazy danych i przełączanie awaryjne połączeń aplikacji to nie ten sam problem. Group Replication nie zawiera funkcji przełączania klientów dotkniętych awarią na sprawnych członków. Aplikacje potrzebują MySQL Router, load balancera, konektora lub własnego middleware do określania miejsca połączenia, a ta warstwa również musi być obsługiwana z uwzględnieniem awarii, ponawiania prób i aktualizacji stanu. dev.mysql.com↗

Dlatego gdy słyszysz „automatyczne przełączanie awaryjne”, zadaj co najmniej trzy oddzielne pytania. Po pierwsze: czy można wybrać serwer podstawowy? Po drugie: czy nowe połączenia aplikacji trafiają na sprawny serwer? Po trzecie: jakie wyniki zobaczą żądania w toku i żądania ponowione przez użytkownika? Istnienie funkcji odpowiadającej na pierwsze pytanie nie gwarantuje automatycznie pozostałych dwóch.

Konfiguracji multi-primary również nie należy rozumieć po prostu jako przełącznika zwiększającego wydajność zapisu. Gdy zapisy są dozwolone z wielu lokalizacji, trzeba także zaprojektować, jak będą unikane lub obsługiwane na poziomie biznesowym współbieżne modyfikacje tych samych danych oraz jakich zasad musi przestrzegać ścieżka zapisu aplikacji. Wysoka dostępność to kwestia operacyjna obejmująca nie tylko wybór funkcji, ale również ćwiczenia awaryjne, obserwowalność i procedury odzyskiwania.

Dlaczego złożone zapytania mogą zwiększać nakład strojenia?

Optymalizator to komponent wybierający plan wykonania o szacunkowo najniższym koszcie spośród kilku sposobów uruchomienia instrukcji SQL. Określa on na przykład, którego indeksu użyć najpierw i w jakiej kolejności łączyć tabele. Oparty na kosztach optymalizator MySQL może polegać na oszacowaniach, gdy statystyki są niewystarczające, dlatego może wybrać plan inny niż oczekiwany przez człowieka. dev.mysql.com↗

Wraz ze wzrostem liczby łączonych tabel liczba kandydatów na plan wykonania może rosnąć wykładniczo. W takim przypadku wąskim gardłem może stać się nie tylko samo pobieranie danych, lecz także czas optymalizacji potrzebny do znalezienia odpowiednich planów. Dlatego w systemach często wykonujących złożone zapytania analityczne lub wiele złączeń trudno określić przydatność wyłącznie na podstawie tego, czy SQL wykonuje się syntaktycznie. Testy powinny używać rzeczywistych rozkładów danych i reprezentatywnych warunków. dev.mysql.com↗

EXPLAIN to narzędzie do sprawdzania planu wykonania wybranego dla zapytania. Gdy wyniki są wolne, najpierw sprawdź predykaty, warunki złączeń, używane indeksy i szacowane liczby wierszy. W razie potrzeby możesz odświeżyć statystyki lub dostosować indeksy i strukturę zapytania. Dostępne są również wskazówki indeksów oraz funkcje kontroli optymalizatora, ale podejścia wymuszające konkretny plan muszą być stale weryfikowane, aby zachować ważność po zmianach danych. dev.mysql.com↗ dev.mysql.com↗

Nie oznacza to, że w MySQL nie można wykonywać złożonej analityki. Jeśli jednak głównym obciążeniem są wielkoskalowe złączenia wielu tabel i zapytania analityczne, realistycznie jest z wyprzedzeniem porównać, ile czasu możesz przeznaczyć na strojenie, czy analitykę należy przenieść na repliki oraz czy warto dodać dedykowany system analityczny. Natomiast w usłudze składającej się przede wszystkim z krótkich, przewidywalnych transakcji obciążenie to może być względnie mniejsze.

Kiedy należy zachować ostrożność w przypadku procedur składowanych?

Procedury składowane to procedury lub funkcje przechowywane i wykonywane na serwerze bazy danych. Mogą utrzymywać część reguł przetwarzania danych blisko bazy danych, lecz funkcje składowane, których można używać w instrukcjach SQL, podlegają ograniczeniom. Na przykład funkcja składowana nie może używać instrukcji zwracającej zestaw wyników. Funkcja obliczająca pojedynczą wartość zwrotną i operacja zapytania zwracająca wiele wierszy mają różne cele i wzorce użycia. dev.mysql.com↗ dev.mysql.com↗

Deterministyczność procedur składowanych ma także znaczenie w środowiskach replikacji. Deterministyczność oznacza uzyskanie tego samego wyniku dla tych samych danych wejściowych. Niedeterministyczne lub zależne od czasu procedury, które różnią się w zależności od czasu albo stanu środowiska, mogą tworzyć problemy z odtwarzalnością zależnie od metody replikacji, co wymaga szczególnej ostrożności przy replikacji opartej na instrukcjach. Przy umieszczaniu logiki biznesowej w bazie danych należy także sprawdzić, czy logika ta może dać taki sam wynik podczas replikacji i odzyskiwania po awarii. dev.mysql.com↗

Decyzja o użyciu procedur składowanych zależy mniej od istnienia funkcji niż od tego, gdzie powinna znajdować się odpowiedzialność za zmiany, testowanie i wdrażanie. Gdy reguły są podzielone między kod aplikacji i procedury bazy danych, śledzenie i testowanie może stać się bardziej złożone. Z drugiej strony mogą być użyteczne dla prostych reguł bliskich integralności danych. Kluczowe pytanie brzmi, czy zespół może zrozumieć i zarządzać miejscem wykonywania tych reguł oraz ich wpływem na replikację.

Kiedy MySQL jest dobrym wyborem, a kiedy należy zachować ostrożność?

Poniższa tabela nie jest rankingiem produktów. Przedstawia perspektywę sprawdzania dopasowania między wymaganiami a funkcjami.

Pierwsze trzy wiersze tabeli opierają się na oficjalnych funkcjach InnoDB, replikacji i Group Replication. Ostatnie dwa wiersze odzwierciedlają także działanie i ograniczenia partycjonowania oraz optymalizatora. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗

W szczególności jeśli silna spójność wieloregionowa lub nieprzerwane przełączanie awaryjne są podstawowymi wymaganiami, nie należy podejmować decyzji wyłącznie na podstawie domyślnej replikacji asynchronicznej. Określ wymagania dotyczące aktualności danych, dopuszczalne opóźnienie, możliwość kontynuowania zapisów podczas awarii oraz ścieżkę przełączania awaryjnego aplikacji, a następnie porównaj Group Replication, NDB Cluster lub inne opcje rozproszone. Natomiast jeśli chcesz niezawodnie przetwarzać typowe transakcje odczytu i zapisu w obrębie jednego obszaru usługi oraz w razie potrzeby rozdzielać odczyty na repliki, połączenie funkcji MySQL może być praktycznym punktem wyjścia. dev.mysql.com↗ dev.mysql.com↗

Co należy sprawdzić przed wdrożeniem?

Po pierwsze, sprawdź, czy podstawowe tabele korzystają z InnoDB i czy granice transakcji odpowiadają jednostkom biznesowym. Zmiany, które muszą zakończyć się sukcesem lub niepowodzeniem razem, takie jak utworzenie zamówienia, należy zdefiniować jako jedną transakcję, unikając przy tym niepotrzebnie długich transakcji zwiększających czas blokad. Po drugie, wypisz najczęstsze zapytania odczytu i zapisu oraz sprawdź, czy wymagane indeksy odpowiadają faktycznym predykatom i metodzie sortowania. dev.mysql.com↗ dev.mysql.com↗

Po trzecie, jeśli używasz replikacji, zdecyduj, „które odczyty są dozwolone z replik”. Jednym z podejść jest rozróżnienie żądań wymagających aktualności, takich jak sprawdzenie statusu bezpośrednio po płatności, od zapytań listowych i statystycznych, które mogą tolerować pewne opóźnienie. Po czwarte, jeśli wymagana jest konfiguracja wysokiej dostępności, przetestuj scenariusze awarii nie tylko dla wyboru członków bazy danych, ale również pod kątem rzeczywistego miejsca przenoszenia połączeń aplikacji. dev.mysql.com↗ dev.mysql.com↗

Po piąte, załóż wzrost danych i sprawdź, czy partycjonowanie jest rzeczywiście potrzebne oraz czy możesz zaakceptować ograniczenia kluczy obcych i unikalnych. Jeśli jednocześnie potrzebujesz wyszukiwania w długich łańcuchach, wyszukiwania pełnotekstowego i partycjonowania, najpierw przeanalizuj ograniczenia między tymi funkcjami. Na koniec, jeśli złożone złączenia są kluczowe, sprawdź EXPLAIN w warunkach zbliżonych do danych produkcyjnych i oceń, czy masz możliwości ciągłego zarządzania statystykami oraz zmianami indeksów. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗

Wniosek: jak oceniać zalety i wady MySQL?

Do mocnych stron MySQL należą kontrola transakcji i współbieżności oparta na InnoDB, integracja z szerokim zakresem środowisk programistycznych, rozdzielanie odczytów przez replikację oraz oficjalne funkcje budowania konfiguracji wysokiej dostępności. Mogą one stanowić wartościową podstawę dla ogólnych usług internetowych i typowych obciążeń OLTP. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗

Jednocześnie potencjalne opóźnienie domyślnej replikacji, dodatkowy projekt przełączania awaryjnego połączeń w wysokiej dostępności, walidacja planów wykonania dla złożonych zapytań oraz ograniczenia dotyczące indeksów, partycjonowania i wyszukiwania pełnotekstowego muszą być uwzględnione jako rzeczywiste koszty. Ostatecznie MySQL nie jest wyborem o uniwersalnie pozytywnych właściwościach. Jest to baza danych, której przydatność można ocenić po sprecyzowaniu wymagań dotyczących spójności danych, proporcji odczytów do zapisów, ograniczeń schematu, poziomu reakcji na awarie oraz możliwości strojenia i obsługi operacyjnej. dev.mysql.com↗ dev.mysql.com↗ dev.mysql.com↗