Spadek rozmiaru bazy danych po optymalizacji nie musi oznaczać utraty danych. Aby ocenić, co się zmieniło, rozdziel logiczną ilość danych od fizycznego miejsca zajmowanego przez tabele, indeksy, logi i pliki tymczasowe. Dopiero porównanie tych elementów pozwala sprawdzić, czy operacja przebiegła bezpiecznie.
Spis treści
Co oznacza mniejszy rozmiar bazy?
Rozmiar logiczny opisuje dane widoczne dla aplikacji, na przykład liczbę rekordów i ich zawartość. Rozmiar fizyczny to miejsce zajmowane przez pliki bazy na dysku. Obejmuje ono nie tylko dane, ale także indeksy, wolne strony wewnątrz plików oraz struktury pomocnicze.
Osobno należy analizować logi, pliki tymczasowe i backupy. Ich rozmiar może się zmienić bez zmiany liczby rekordów w tabelach. Plik eksportu SQL także nie musi mieć takiego samego rozmiaru jak baza na serwerze. Wynika to między innymi z różnicy między strukturą pliku eksportu a strukturą fizyczną bazy danych. Więcej o różnicy rozmiaru bazy i eksportu SQL.
Najczęstsze przyczyny zmniejszenia rozmiaru
| Przyczyna | Co może się zmniejszyć | Dane logiczne | Możliwy wpływ | Typowe ryzyko |
|---|---|---|---|---|
| Usunięcie rekordów | Tabele lub wolne miejsce wewnątrz plików | Zmniejszają się | Mniej danych do odczytu, ale nie zawsze szybsze zapytania | Usunięcie danych potrzebnych aplikacji |
| Archiwizacja | Tabele produkcyjne i powiązane indeksy | Zmniejszają się w bazie produkcyjnej | Może ułatwić pracę na bieżących danych | Brak dostępu do danych historycznych |
| Przebudowa tabel lub indeksów | Fragmentacja i wolne strony | Zwykle pozostają bez zmian | Może poprawić organizację struktur | Blokady, obciążenie CPU i I/O |
| Usunięcie nieużywanych indeksów | Indeksy oraz miejsce potrzebne do ich utrzymania | Pozostają bez zmian | Może zmniejszyć koszt operacji zapisu | Gorszy czas zapytań korzystających z usuniętego indeksu |
| Kompresja | Dane lub indeksy | Zwykle pozostają bez zmian | Może zmniejszyć zajętość dysku | Wpływ na CPU i czas operacji |
| Usunięcie plików pomocniczych | Logi, pliki tymczasowe lub nadmiarowe backupy | Bez zmian | Zmniejsza zajętość dysku poza tabelami | Utrata pliku potrzebnego do odtworzenia lub analizy |
| Zmiana typu lub formatu danych | Kolumny, tabele lub indeksy | Mogą pozostać podobne, ale zmienia się sposób zapisu | Zależy od zastosowanej zmiany | Niezgodność aplikacji lub utrata części informacji |
Najważniejsze pojęcia
- Fragmentacja to nieuporządkowanie zapisanych struktur, które może powodować wykorzystanie większej liczby stron lub bloków.
- Bloat oznacza nadmiar zajętego miejsca związany z nieefektywnym wykorzystaniem struktur danych. Usunięcie bloatu może zmniejszyć rozmiar fizyczny bez zmiany danych logicznych.
- Wolne miejsce wewnątrz pliku to obszar zwolniony przez bazę, który może zostać wykorzystany ponownie, lecz nadal należy do pliku widocznego na dysku.
- Przebudowa indeksu tworzy indeks ponownie w uporządkowanej strukturze. Może usunąć fragmentację, ale wymaga zasobów i może powodować blokady.
- Kompresja zmienia sposób zapisu danych lub indeksów tak, aby zajmowały mniej miejsca.
- Shrink, czyli zmniejszanie pliku, oddaje część wolnego miejsca z końca pliku systemowi operacyjnemu. Nie jest tym samym co samo usunięcie rekordów.
Dlaczego usunięcie rekordów nie zawsze zmniejsza plik?
Po usunięciu rekordów baza może oznaczyć zajmowane przez nie strony jako wolne. Takie miejsce może zostać użyte przy kolejnych zapisach, ale rozmiar pliku na dysku pozostanie bez zmian. Aby fizycznie zmniejszyć plik, potrzebna jest osobna operacja zwracająca wolne miejsce systemowi operacyjnemu, na przykład shrink lub operacja o podobnym działaniu. Nazwy i szczegóły zależą od używanego systemu zarządzania bazą danych.
Z tego powodu warto porównać dwa parametry: wolne miejsce wewnątrz plików oraz miejsce faktycznie zwrócone systemowi operacyjnemu. Różnica między nimi wyjaśnia, dlaczego baza może mieć mniej danych, ale nadal zajmować tyle samo miejsca na dysku.
Jak sprawdzić, co zmieniło się po optymalizacji?
Przed analizą przygotuj kopię zapasową i zapisz nazwę silnika bazy oraz jego wersję. Nie używaj jednej uniwersalnej komendy optymalizacyjnej, ponieważ dostępne operacje różnią się między systemami, takimi jak MySQL, MariaDB, PostgreSQL, Microsoft SQL Server i Oracle.
- Zanotuj rozmiar całej bazy przed operacją i po jej zakończeniu.
- Porównaj osobno rozmiar tabel, indeksów, logów oraz plików tymczasowych.
- Sprawdź liczbę rekordów w kluczowych tabelach. Jeśli liczba zmieniła się bez planowanego usunięcia lub archiwizacji, zatrzymaj dalsze prace i wyjaśnij przyczynę.
- Ustal, czy wykonano kompresję, przebudowę, reorganizację, vacuum, shrink lub inną operację porządkowania.
- Porównaj wolne miejsce wewnątrz plików z miejscem zwróconym systemowi operacyjnemu.
- Sprawdź logi operacji, czas wykonania, komunikaty o błędach i występujące blokady.
- Zweryfikuj integralność danych i wykonaj test najważniejszych zapytań aplikacji.
W przypadku bazy MySQL optymalizację lub naprawę tabel można rozpocząć w phpMyAdmin, wybierając bazę danych, a następnie jej tabele. Opis pracy z tabelami MySQL w phpMyAdmin. Konkretne opcje i ich skutki zależą jednak od silnika oraz wersji.
Kiedy mniejsza baza jest dobrym objawem?
Zmniejszenie rozmiaru jest zwykle zgodne z planem, gdy liczba rekordów pozostała bez zmian, zmalał rozmiar indeksów lub odzyskano wcześniej niewykorzystane miejsce, a dane przechodzą kontrolę integralności. Korzystny sygnał stanowi także poprawny wynik testów najważniejszych zapytań, bez nowych błędów i blokad.
Sam spadek rozmiaru nie dowodzi poprawy wydajności. Zapytania mogą działać podobnie, szybciej albo wolniej, zależnie od zmian w indeksach, strukturze tabel i sposobie kompresji.
Jakie ryzyka uwzględnić przed i po operacji?
Przebudowa, kompresja lub zmniejszanie plików może czasowo zwiększyć zajętość dysku. Operacja może też obciążyć CPU i I/O, powodować blokady oraz wpływać na działanie aplikacji produkcyjnej. Przed zmianą wykonaj kopię zapasową, zaplanuj możliwość cofnięcia operacji i sprawdź wynik na środowisku testowym, jeśli jest dostępne.
- potwierdź liczbę rekordów przed i po operacji,
- zapisz rozmiar tabel, indeksów, logów i backupów,
- zachowaj informacje o wykonanych poleceniach lub użytym narzędziu,
- sprawdź integralność danych i działanie aplikacji,
- monitoruj rozmiar bazy po zmianie,
- porównaj wydajność najważniejszych zapytań.
Co zrobić, jeśli rozmiar spadł bez planowanej zmiany?
Najpierw porównaj liczbę rekordów w kluczowych tabelach oraz rozmiar tabel i indeksów. Następnie sprawdź logi, pliki tymczasowe, backupy i historię wykonanych operacji. Jeśli zmniejszyły się dane logiczne, a nie planowano usuwania ani archiwizacji, wstrzymaj kolejne operacje i zabezpiecz kopię zapasową. Gdy dane są kompletne, ale zmieniły się tylko struktury fizyczne, wynik może być oczekiwanym skutkiem przebudowy, kompresji lub odzyskania wolnego miejsca.
Podsumowanie
Spadek rozmiaru bazy może wynikać z usunięcia danych, uporządkowania tabel i indeksów, kompresji albo usunięcia plików pomocniczych. Bezpieczna ocena wymaga porównania danych logicznych, struktur fizycznych, logów i plików tymczasowych. Najpierw wykonaj kopię zapasową, potem sprawdź zakres operacji, integralność danych i wydajność kluczowych zapytań.