P

olecenie MySQL optimize table w niektórych warunkach może efektywnie “zdefragmentować” tabele bazy. Szczególnie komenda ta przydaje się w przypadku baz, które są często aktualizowane i/lub ich elementy usuwane. Dla przykładu posiadamy tabelę o nazwie “articles”, która posiada tysiące wierszy, które są często dokładane, aktualizowane oraz usuwane. Z poniższego opisu tabeli można zobaczyć, że zawiera ona typy danych kolumn różnej długości:

mysql> desc articles;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| content        | text         | NO   |     | NULL    |                |
| author_id      | int(11)      | YES  |     | NULL    |                |
| article_title  | varchar(120) | YES  |     | NULL    |                |
| article_hash   | int(11)      | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

Jeśli spojrzymy na zajętość tabeli na dysku – graniczy ona z rozmiarem 190 MB. Jeśli dokonamy zapytania na kolumnie tabeli “article_title”, która jest zaindeksowana, możemy zobaczyć średni czas odpowiedzi:

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.63 sec)

Teraz jeśli zoptymalizujemy tabelę następującym poleceniem:

mysql> optimize table articles;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| books.articles        | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (6.27 sec)

Odniesie to się do efektu defragmentacji tabeli oraz redukcji jej zajętości na dysku – do 105 MB. Ma to również bardzo duży wpływa na czas reakcji na to samo zapytanie (z 0.63 sekundy do 0.39):

mysql> select count(*) from articles where article_title like 'The%';
+----------+
| count(*) |
+----------+
|    15830 |
+----------+
1 row in set (0.39 sec)

Bonus:A jak wykonać taką optymalizację dla wszystkich naszych baz oraz tabel MySQL ? Poniżej znajduje się proste polecenie by automatycznie naprawić, sprawdzić oraz zoptymalizować wszystkie bazy danych znajdujące się na serwerze MySQL:

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

mysqlcheck używa instrukcji SQL: CHECK TABLE, REPAIR TABLE, ANALYZE TABLE oraz OPTIMIZE TABLE w szybki i prosty sposób. Ustala, które instrukcje mają zostać użyte w operacjach, które chcemy wykonać, a następnie wysyła je do wykonania na serwerze. Polecenie to należy wykonać na wszystkich chodzących bazach, najlepiej o takiej porze, w której jest obsługiwanych jak najmniej zapytań pochodzących np. ze stron WWW (spowoduje to, że proces ten będzie mógł zostać wykonany trochę szybciej).

Więcej informacji: DBTuna