🢂Mysql ‑ Cheatsheet (ściąga)

Ściąga z GITa która ma na celu szybkie przekazanie najważniejszych, kluczowych punktów, które pomagają zrozumieć daną kwestię bez zagłębiania się w szczegółowe i rozbudowane wyjaśnienia.

Optymalizacja silnika bazy danych MySQL jest kluczowa dla zapewnienia wydajności i skalowalności aplikacji.

Dostępne silniki pamięci masowej MySQL:

InnoDB: domyślny silnik od MySQL 5.5, oferuje wysoką wydajność, skalowalność i dostępność. Obsługuje transakcje ACID i zapewnia spójność danych.
MyISAM: starszy silnik, prostszy w konfiguracji i zarządzaniu. Nie obsługuje transakcji ACID, ale może być szybszy od InnoDB dla niektórych typów zapytań.- - Memory: przechowuje dane w pamięci RAM, co zapewnia bardzo wysoką wydajność, ale jest ulotne i nie nadaje się do przechowywania dużych ilości danych.
CSV: przechowuje dane w plikach CSV, co ułatwia przenoszenie danych, ale może być wolniejsze od innych silników.
NDB Cluster: rozproszony silnik pamięci masowej, który zapewnia wysoką dostępność i skalowalność.
Archive: służy do archiwizacji danych, nie nadaje się do bieżącej pracy z bazą danych.
Blackhole: odrzuca wszystkie dane, może być używany do testowania lub do celów edukacyjnych.
Federated: umożliwia dostęp do danych z innych serwerów MySQL.

Definicje, zastosowania, zalety i wady:

InnoDB:

Definicja: Silnik transakcyjny oparty na drzewie B+.
Zastosowanie: Odpowiedni do większości zastosowań, gdzie wymagana jest wysoka wydajność, skalowalność i spójność danych.
Zalety: Wysoka wydajność, skalowalność, dostępność, obsługa transakcji ACID, spójność danych.
Wady: Bardziej złożony w konfiguracji i zarządzaniu niż MyISAM, może być wolniejszy dla niektórych typów zapytań.

MyISAM:

Definicja: Silnik oparty na tabelach ISAM.
Zastosowanie: Odpowiedni do prostych aplikacji, gdzie nie są wymagane transakcje ACID.
Zalety: Prosty w konfiguracji i zarządzaniu, może być szybszy od InnoDB dla niektórych typów zapytań.
Wady: Nie obsługuje transakcji ACID, nie zapewnia spójności danych, nie jest tak skalowalny jak InnoDB.

Memory:

Definicja: Silnik przechowujący dane w pamięci RAM.
Zastosowanie: Odpowiednie do aplikacji, gdzie wymagana jest bardzo wysoka wydajność i dostępność danych w pamięci.
Zalety: Bardzo wysoka wydajność, dostępność danych w pamięci.
Wady: Ulotne, nie nadaje się do przechowywania dużych ilości danych.

CSV:

Definicja: Silnik przechowujący dane w plikach CSV.
Zastosowanie: Odpowiednie do przenoszenia danych lub do prostych aplikacji, gdzie nie jest wymagana wysoka wydajność.
Zalety: Łatwe przenoszenie danych, proste w konfiguracji i zarządzaniu.
Wady: Może być wolniejsze od innych silników, nie nadaje się do dużych ilości danych.

NDB Cluster:

Definicja: Rozproszony silnik pamięci masowej.
Zastosowanie: Odpowiednie do aplikacji o wysokich wymaganiach dotyczących dostępności i skalowalności.
Zalety: Wysoka dostępność, skalowalność, odporność na awarie.
Wady: Bardziej złożony w konfiguracji i zarządzaniu niż inne silniki.

Uwaga:

To tylko krótkie podsumowanie. Więcej informacji na temat silników pamięci masowej MySQL można znaleźć w dokumentacji MySQL: [usunięto nieprawidłowy URL].

Wybór odpowiedniego silnika

Wybór odpowiedniego silnika pamięci masowej zależy od specyfiki danej aplikacji. Należy wziąć pod uwagę takie czynniki jak:

Wymagana wydajność
Skalowalność
Dostępność
Spójność danych
Łatwość konfiguracji i zarządzania

Po zapoznaniu się z zaletami i wadami różnych silników można wybrać ten, który najlepiej odpowiada potrzebom danej aplikacji.

Dodatkowe informacje:

MySQL Documentation: https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

Optymalizacja silnika bazy danych MySQL

Analiza wydajności:

SHOW STATUS: Wyświetla statystyki serwera MySQL, w tym liczbę zapytań, połączeń i czasu odpowiedzi.
EXPLAIN: Pokazuje plan wykonania dla danego zapytania, co pozwala zidentyfikować potencjalne problemy z wydajnością.
mysqldumpslow: Rejestruje wolne zapytania do pliku, który można później przeanalizować.
pt-query-digest: Narzędzie do analizy logów zapytań MySQL, które pomaga zidentyfikować zapytania o słabej wydajności.

Optymalizacja zapytań:

Używaj indeksów: Indeksy pomagają MySQLowi szybko znaleźć dane.
Używaj właściwych typów danych: Używanie odpowiednich typów danych dla kolumn może poprawić wydajność.
Optymalizuj składnię zapytań: Unikaj zbędnych podzapytań i joinów.
Zwiększ rozmiar bufora zapytań: Większy bufor zapytań może zmniejszyć liczbę odczytów z dysku.

Optymalizacja serwera:

Dostosuj parametry serwera: Dostosuj parametry serwera MySQL, takie jak innodb_buffer_pool_size i max_connections, aby uzyskać optymalną wydajność.
Użyj pamięci podręcznej zapytań: Pamięć podręczna zapytań może przechowywać wyniki zapytań w pamięci, co może przyspieszyć ich działanie.
Użyj partycjonowania tabel: Partycjonowanie tabel może rozłożyć dane na wiele dysków, co może poprawić wydajność odczytu i zapisu.
Defragmentuj tabelę: Defragmentacja tabeli może poprawić wydajność odczytu i zapisu.

Narzędzia:

MySQL Workbench: Graficzne narzędzie do zarządzania i administrowania bazami danych MySQL.
phpMyAdmin: Narzędzie webowe do zarządzania bazami danych MySQL.
MySQLTuner: Narzędzie do automatycznego wykrywania i sugerowania optymalizacji serwera MySQL.

Ściąga z optymalizacji silnika bazy danych MySQL, wraz z przykładowymi poleceniami:

Wybór silnika przechowywania: Domyślnie, użyj InnoDB dla lepszej wydajności i wsparcia transakcji.

   ALTER TABLE nazwa_tabeli ENGINE=InnoDB;

Indeksowanie: Utwórz indeksy dla kolumn używanych w zapytaniach WHERE, JOIN.

    CREATE INDEX idx_nazwa_kolumny ON nazwa_tabeli(nazwa_kolumny);

Optymalizacja zapytań: Użyj EXPLAIN do analizy i optymalizacji zapytań.

    EXPLAIN SELECT * FROM nazwa_tabeli WHERE nazwa_kolumny = 'wartość';

Konfiguracja MySQL:
- Zwiększ innodb_buffer_pool_size (zależnie od dostępnej pamięci RAM). W pliku my.cnf lub my.ini:

    [mysqld]
    innodb_buffer_pool_size = 1G

- Ustaw **`max_connections`** zgodnie z oczekiwaną liczbą połączeń.

    [mysqld]
    max_connections = 200

Partycjonowanie: Rozważ partycjonowanie dla dużych tabel.

    CREATE TABLE nazwa_tabeli (
      id INT NOT NULL,
      dane VARCHAR(100),
      ...
    )
    PARTITION BY RANGE (id) (
      PARTITION p0 VALUES LESS THAN (1000),
      PARTITION p1 VALUES LESS THAN (2000),
      ...
    );

Monitorowanie i analiza: Użyj SHOW PROCESSLIST do monitorowania aktywnych zapytań.

    SHOW PROCESSLIST;

Optymalizacja sprzętu: Brak bezpośredniego polecenia, ale upewnij się, że serwer ma odpowiednią konfigurację sprzętową, szczególnie pamięć RAM i szybkie dyski.
Regularna konserwacja:

Optymalizacja tabel:

    OPTIMIZE TABLE nazwa_tabeli;

Sprawdzanie spójności danych:

    CHECK TABLE nazwa_tabeli;

Stosując się do tych zaleceń, będziesz mógł skutecznie zoptymalizować wydajność swojej bazy danych MySQL, co przełoży się na lepszą responsywność aplikacji oraz stabilność systemu.

Dodatkowe wskazówki:

Monitoruj wydajność bazy danych: Regularnie monitoruj wydajność bazy danych, aby zidentyfikować potencjalne problemy.
Utwórz plan konserwacji: Utwórz plan konserwacji bazy danych, aby zapewnić jej optymalną wydajność.
Aktualizuj oprogramowanie MySQL: Regularnie aktualizuj oprogramowanie MySQL do najnowszej wersji.

Uwaga:

Pamiętaj, aby przed zastosowaniem jakichkolwiek zmian w konfiguracji serwera MySQL wykonać kopię zapasową bazy danych.
Niektóre z tych poleceń mogą wymagać uprawnień administratora.

Źródła:

Dokumentacja MySQL: https://dev.mysql.com/doc/
MySQL Performance Blog: https://mysqlperformanceblog.com/
Percona: https://www.percona.com/

Dodatkowe narzędzia:

pt-table-checksum: Narzędzie do sprawdzania integralności danych w tabelach MySQL.
innodb_file_per_table: Opcja konfiguracji, która pozwala na przechowywanie danych i indeksów każdej tabeli w osobnych plikach.
MySQL Enterprise Monitor: Narzędzie do monitorowania i zarządzania wydajnością baz danych MySQL.