MySQL udostępnia ciekawe, aczkolwiek nie tak popularne narzędzie, które pomaga administratorowi zoptymalizować strukturę bazy danych. W dzisiejszym poście będzie kilka informacji na temat tego co to jest, jak tego używać i dlaczego stosowanie tego narzędzia nie jest takim głupim pomysłem.

Polecenie to, doklejane do końca jakiegoś SELECTa, na podstawie wyników zapytania zbiera informację o tym, jaka powinna być optymalna definicja kolumn. W praktyce wygląda to na przykład tak:

mysql> SELECT * FROM actorchar PROCEDURE ANALYSE(4, 256)\G
*************************** 1. row ***************************
Field_name: sakila.actorchar.actor_id
Min_value: 1
Max_value: 200
Min_length: 1
Max_length: 3
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 100.5000
Std: 57.7343
Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 2. row ***************************
Field_name: sakila.actorchar.first_name
Min_value: ADAM
Max_value: ZERO
Min_length: 2
Max_length: 11
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 5.3050
Std: NULL
Optimal_fieldtype: VARCHAR(11) NOT NULL
*************************** 3. row ***************************
Field_name: sakila.actorchar.last_name
Min_value: AKROYD
Max_value: ZELLWEGER
Min_length: 3
Max_length: 12
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 6.2300
Std: NULL
Optimal_fieldtype: VARCHAR(12) NOT NULL
*************************** 4. row ***************************
Field_name: sakila.actorchar.last_update
Min_value: 2006-02-15 04:34:33
Max_value: 2006-02-15 04:34:33
Min_length: 19
Max_length: 19
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 19.0000
Std: NULL
Optimal_fieldtype: ENUM('2006-02-15 04:34:33') NOT NULL
4 rows in set (0,00 sec)

Jak widać, przyjmuje ona dwie wartości jako parametr. Pierwsza z nich to w skrócie maksymalna ilość elementów jakie może przyjąć typ ENUM. Przykładowo, jeśli zdefiniujemy ją jako 3 a w kolumnie, w praktyce, występują cztery rodzaje wartości, to typ ENUM nie będzie nam proponowany. Drugi parametr to ilość pamięci, jaką MySQL może użyć w trakcie wyszukiwania wszystkich unikalnych wartości.

Co mamy w wyniku? Lecimy po kolei. Nazwa kolunmy, minimalna i maksymalna wartość występująca w kolumnie, maksymalna i minimalna długość wartości umieszczonej w tej kolumnie. Ilość wartości pustych lub 0. Ilość wartości NULL. Średnia wartość (dla kolumn typu numerycznego) lub średnia długość (dla kolumn typu znakowego). Odchylenie standardowe. Wreszcie ostatnia informacja: proponowany optymalny typ dla danej kolumny.

Wynik można łatwo porównać z tym, co faktycznie znajduje się w bazie:

mysql>  EXPLAIN actorchar\G
*************************** 1. row ***************************
Field: actor_id
Type: smallint(5) unsigned
Null: NO
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: first_name
Type: char(45)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: last_name
Type: char(45)
Null: YES
Key: MUL
Default: NULL
Extra:
*************************** 4. row ***************************
Field: last_update
Type: timestamp
Null: NO
Key:
Default: CURRENT_TIMESTAMP
Extra: on update CURRENT_TIMESTAMP
4 rows in set (0,00 sec)

Jak widać, pewne zmiany można wprowadzić. Kolumna actor_id można zdefiniować jako NOT NULL – nie zawiera ona wartości pustych a narzucenie takiego ograniczenia na poziomie kolumny ułatwia pracę MySQL podczas porównywania, sortowania itp. wartości w tej kolumnie. Po prostu, baza nie musi uważać na wartości NULL bo wie że takie wartości nie występują. Kolumny first_name i last_name można przyciąć – maksymalna długość danych w nich to odpowiednio 12 i 11 znaków. Do limitu 45 trochę brakuje – można go zmniejszyć. Można także zmienić ich typ z CHAR na VARCHAR, tak aby alokowane było tylko tyle miejsca ile faktycznie jest na dane potrzebne.

Ok, po co to w ogóle robić? Wbrew pozorom, tego typu optymalizacje mają sens. Mniejsze kolumny to mniej miejsca na dysku zajętego przez bazę, większa ilość rekordów, które można zmieścić w takiej samej ilości pamięci (czyli bufory i cache będą bardziej wydajne). Załóżmy że uda się zyskać średnio 50 bajtów na rekord w tabeli. Załóżmy że takich tabel mamy 10. Załóżmy że średnio mamy milion rekordów w każdej z tabeli. Wynik? 50 * 10 * 1000 000 – pięćset milionów bajtów, prawie 500MB danych mniej.

Oczywiście, tego typu zmniejszanie pojemności kolumn może skutkować tym, że w pewnym momencie trzeba będzie wykonać ALTER TABLE aby zwiększyć długość danych, które można w danej kolumnie przechować. PROCEDURE ANALYSE() analizuje dane obecnie znajdujące się w tabeli, nie jest w stanie przewidzieć jak wyglądać będzie przyszłe zapotrzebowanie. Nie zmienia to faktu, że gdy na prawdę potrzebna jest duża wydajność i optymalizacja serwera musi być prawie perfekcyjna, dobrze pamiętać o tym narzędziu, a jeśli nie o nim, to o tym, że struktura tabeli także wymaga dokładnej analizy i wyszukania tego, co można poprawić.