Zadanie 5: Analiza domyślnych, systemowych baz danych

Cel zadania

Głównym celem tego zadania jest przeniesienie studenta z roli użytkownika bazy danych do roli świadomego administratora, który rozumie wewnętrzną architekturę i mechanizmy działania serwera MySQL/MariaDB. Zadanie koncentruje się na dogłębnej eksploracji i zrozumieniu przeznaczenia trzech kluczowych, systemowych baz danych: `information_schema`, `mysql` oraz `performance_schema`. Student nauczy się, w jaki sposób serwer przechowuje i zarządza metadanymi (czyli "danymi o danych"), jak zorganizowane są informacje o kontach użytkowników i ich uprawnieniach, a także gdzie szukać informacji diagnostycznych dotyczących wydajności. Zrozumienie tych wewnętrznych struktur jest absolutnie kluczowe dla zaawansowanej administracji, optymalizacji zapytań, automatyzacji zadań oraz skutecznego rozwiązywania problemów. To zadanie otwiera drzwi do postrzegania serwera nie jako "czarnej skrzynki", ale jako transparentnego i możliwego do analizy systemu.

Scenariusz

Student, po opanowaniu podstawowych operacji, zaczyna zadawać sobie bardziej zaawansowane pytania: "Skąd serwer wie, jakie tabele istnieją w mojej bazie?", "Gdzie fizycznie zapisane są informacje o uprawnieniach, które nadawałem?", "Jak mogę sprawdzić, które zapytania najbardziej obciążają serwer?". Aby znaleźć odpowiedzi, postanawia przeprowadzić szczegółową analizę wewnętrznych mechanizmów serwera. Loguje się do konsoli MySQL na konto `root`, które daje mu pełny wgląd w system. Jego pierwszym krokiem jest wykonanie polecenia `SHOW DATABASES;`, które tym razem analizuje nie pod kątem swoich baz, ale właśnie pod kątem tych systemowych: `information_schema`, `mysql`, `performance_schema` i `sys`. Jego podróż w głąb serwera rozpoczyna się od `information_schema`. Po przełączeniu się na tę bazę (`USE information_schema;`), wykonuje `SHOW TABLES;` i jest zdumiony liczbą dostępnych widoków. Postanawia zbadać kilka z nich. Najpierw odpytuje widok `TABLES`, filtrując wyniki tak, aby zobaczyć tylko informacje o tabelach w jego bazie `test_hotel`. Odkrywa tam takie informacje jak silnik przechowujący dane (np. InnoDB) czy przybliżoną liczbę wierszy. Następnie, z ciekawością analizuje widok `COLUMNS`, aby zobaczyć, jak serwer przechowuje szczegółowe informacje o każdej kolumnie w jego tabelach – jej typie danych, wartości domyślnej czy dopuszczalności wartości `NULL`. To doświadczenie daje mu olśnienie: każda operacja `CREATE TABLE` czy `ALTER TABLE` to w rzeczywistości modyfikacja metadanych zapisanych właśnie w `information_schema`. Kolejnym krokiem jest zbadanie bazy `mysql`. Student wie, że to tutaj znajduje się serce systemu bezpieczeństwa. Zamiast ogólnego zapytania, tym razem pisze bardziej szczegółowe, aby zobaczyć nie tylko nazwy użytkowników i hosty, ale także kolumny z uprawnieniami, takie jak `Select_priv` czy `Insert_priv`. Obserwując wiersz dla stworzonego wcześniej użytkownika `hotel_app`, widzi, że wartości w tych kolumnach to 'Y' (Yes), co jest bezpośrednim odzwierciedleniem wykonanego przez niego polecenia `GRANT`. Na koniec, zagląda do `performance_schema`. Chociaż wie, że pełna analiza tej bazy wymaga zaawansowanej wiedzy, wykonuje proste zapytanie do tabeli `events_statements_summary_by_digest`, aby zobaczyć zagregowane statystyki wykonanych zapytań. Odkrywa tam, że serwer śledzi każde unikalne zapytanie, liczy, ile razy zostało wykonane i mierzy średni czas jego wykonania. To daje mu przedsmak potężnych możliwości, jakie drzemią w narzędziach do optymalizacji wydajności.

Wykonanie krok po kroku

1. Identyfikacja baz systemowych

   Zaloguj się na konto `root` i wyświetl listę wszystkich baz danych. Zidentyfikuj te, które nie zostały przez Ciebie utworzone – to są właśnie bazy systemowe.

   -- To polecenie jest punktem wyjścia do naszej eksploracji.
   SHOW DATABASES;

   Wyjaśnienie: Na liście zobaczysz swoje bazy (np. `test_hotel`) oraz bazy systemowe. Najważniejsze z nich to: `information_schema` (metadane), `mysql` (uprawnienia) i `performance_schema` (wydajność). W nowszych wersjach MariaDB/MySQL może pojawić się również baza `sys`, która jest zbiorem czytelnych widoków na dane z `performance_schema`.

2. Eksploracja `information_schema` – słownika danych

   `information_schema` to zbiór widoków (wirtualnych tabel) tylko do odczytu, które opisują wszystkie inne obiekty na serwerze. Jest to standard ANSI SQL, więc wiedza o nim jest przenośna między różnymi systemami baz danych.

   -- Przełączamy się na bazę metadanych.
   USE information_schema;
   
   -- Wyświetlamy informacje o wszystkich tabelach w naszej bazie 'test_hotel'.
   -- Możemy zobaczyć m.in. silnik przechowujący dane i kiedy tabela była ostatnio aktualizowana.
   SELECT TABLE_NAME, ENGINE, UPDATE_TIME FROM TABLES WHERE TABLE_SCHEMA = 'test_hotel';
   
   -- Teraz zajrzyjmy głębiej - analiza struktury kolumn w tabeli 'pokoje'.
   -- To zapytanie pokaże nam typ danych każdej kolumny, jej pozycję w tabeli i wartość domyślną.
   SELECT COLUMN_NAME, ORDINAL_POSITION, COLUMN_TYPE, COLUMN_DEFAULT
   FROM COLUMNS WHERE TABLE_SCHEMA = 'test_hotel' AND TABLE_NAME = 'pokoje';

   Wyjaśnienie: Odpytywanie `information_schema` jest niezwykle użyteczne do automatyzacji. Można np. napisać skrypt, który generuje dokumentację bazy danych, sprawdzając wszystkie tabele i kolumny właśnie poprzez zapytania do tego schematu.

3. Analiza bazy `mysql` – centrum uprawnień

   Baza `mysql` zawiera tabele, które przechowują wszystkie informacje o użytkownikach, hasłach i uprawnieniach. Jest to krytyczny element serwera, a jej bezpośrednia modyfikacja jest ryzykowna i niezalecana (należy używać poleceń `GRANT`, `CREATE USER` itp.).

   -- Przełączamy się na bazę zarządzającą serwerem.
   USE mysql;
   
   -- Wyświetlamy szczegółowe informacje o uprawnieniach dla użytkownika 'hotel_app'.
   -- Kolumny *_priv (privileges) przechowują informację 'Y' (Yes) lub 'N' (No).
   SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv, Alter_priv
   FROM user WHERE User = 'hotel_app';

   Wyjaśnienie: Analiza tabeli `user` pozwala zrozumieć, jak polecenia takie jak `GRANT` działają "pod maską" – po prostu zmieniają wartości w odpowiednich kolumnach tej tabeli. Oprócz tabeli `user`, w bazie `mysql` znajdują się też inne ważne tabele, np. `db` (uprawnienia na poziomie baz danych) czy `tables_priv` (uprawnienia na poziomie tabel).

4. Wprowadzenie do `performance_schema` – monitorowanie wydajności

   `performance_schema` to zaawansowany silnik monitorujący, który zbiera dane o niskopoziomowych zdarzeniach na serwerze w czasie rzeczywistym. Jego analiza pozwala na precyzyjną diagnostykę "wąskich gardeł" wydajności.

   -- Przełączamy się na bazę danych o wydajności.
   USE performance_schema;
   
   -- Wyświetlamy podsumowanie wykonanych zapytań, posortowane od najdłużej trwających.
   -- To zapytanie może pomóc zidentyfikować zapytania, które wymagają optymalizacji.
   SELECT
       DIGEST_TEXT AS query,
       COUNT_STAR AS exec_count,
       (SUM_TIMER_WAIT / 1000000000) AS sum_latency_ms,
       (AVG_TIMER_WAIT / 1000000000) AS avg_latency_ms
   FROM events_statements_summary_by_digest
   ORDER BY sum_latency_ms DESC
   LIMIT 10;

   Wyjaśnienie: To zapytanie jest przykładem potężnych możliwości analitycznych. `DIGEST_TEXT` to unikalny wzorzec zapytania (bez konkretnych wartości). `COUNT_STAR` to liczba wykonań. `SUM_TIMER_WAIT` i `AVG_TIMER_WAIT` to zagregowany i średni czas oczekiwania (w pikosekundach, stąd dzielenie), który jest miarą czasu wykonania zapytania.

Podsumowanie i wnioski

Eksploracja systemowych baz danych jest jak zajrzenie do maszynowni statku – pozwala zrozumieć, co napędza cały system i jak diagnozować jego problemy. To zadanie pokazało, że serwer MySQL/MariaDB jest transparentnym systemem, który sam siebie opisuje i dostarcza potężnych narzędzi do autodiagnostyki. Zrozumienie roli `information_schema` zmienia perspektywę z ręcznego zarządzania na możliwość pełnej automatyzacji i generowania dynamicznych raportów o strukturze bazy. Analiza bazy `mysql` demistyfikuje zarządzanie uprawnieniami, pokazując, że polecenia takie jak `GRANT` są w rzeczywistości bezpiecznym interfejsem do modyfikacji konkretnych tabel systemowych. Wreszcie, nawet pobieżne zapoznanie się z `performance_schema` otwiera oczy na świat optymalizacji wydajności, pokazując, że serwer sam zbiera dane niezbędne do znalezienia i naprawienia wolno działających zapytań. Kluczowym wnioskiem jest to, że świadomy administrator i deweloper nie traktuje serwera jako czarnej skrzynki. Potrafi on, w razie potrzeby, zajrzeć "pod maskę", aby zrozumieć, dlaczego system zachowuje się w określony sposób, co jest niezbędne do budowania i utrzymywania wydajnych i niezawodnych aplikacji.