Szukaj


drukuj pdf
Optymalizacja bazy SQL

Dlaczego warto optymalizować bazę?
Należy utrzymywać bazę w dobrym stanie, dzięki temu będziesz pewny, że działa prawidłowo. Chodzi o to, aby informacje zawarte w bazie były jak najszybciej wysyłane do danego skryptu. Aby tak było, baza musi mieć dobrą strukturę i musi być zoptymalizowana. W tym przewodniku, pokażemy Ci, w jaki sposób najlepiej zoptymalizować swoją bazę.

Optymalizacja w bazie

  • Prawidłowe ustawienie indeksów
Aby zwiększyć szybkość poszukiwań podczas połączenia, należy wstawić index do pól, które są używane w klauzulach WHERE. Przykład: Poszukujesz osoby pod względem miasta. Trzeba indeksować pole 'miasto' w następujący sposób :

ALTER TABLE `test` ADD INDEX ( `miasto` );


  • Porządki w bazie
Niektóre Twoje dane nie są już konsultowane. Dlaczego ich nie zarchiwizujesz? Tabele nie będą tak wypełnione, poszukiwania będą przeprowadzane zdecydowanie szybciej.

Optymalizacja w skryptach

  • Ograniczenia wyświetleń
Ogranicz wyświetlanie zapisów do określonej liczby (na przykład 10 na stronę) z częścią połączenia LIMIT.

  • Przegrupowanie zapytań
Przegrupuj zapytania na początku skryptu :



połącz_się_z_bazą
pytanie1
pytanie2
...
rozłącz_się_z_bazą

Wyświetlanie ...
Przetwarzanie danych ...
Operacja trwa ...
Wyświetlanie ...
...



  • Używanie cache
Jeśli masz informacje, które pochodzą z bazy danych i które nie zmieniają się zbyt często, umieść je w cache. Na przykład utwórz stronę html. Kiedy modyfikujesz informacje, takie działanie zmniejsza Twoje dostępy. Na przykład strona news. Jeśli plik (cache) html istnieje, używasz go, jeśli nie, tworzysz ten plik. W momencie gdy umieszczasz nowy news, możesz wpisać w skrypt, aby usuwał on plik html : w ten sposób będzie on odtworzony następnym razem, gdy osoba odwiedzająca tego zażąda. Możesz także tworzyć cache sesji. Umieścić rezultat żądania w zmiennej sesji.

  • Pobieraj tylko niezbędne dane
Sprawdź, czy wybierasz tylko to, co jest Ci potrzebne w zapytaniach SQL. I przede wszystkim, czy nie zapomniełeś połączenia między tabelami (where table1.champs = table2.champs2), ponieważ zajmuje to bardzo dużo czasu.

  • Unikaj opcji zbyt wymagających
Unikaj używania HAVING i GROUP BY wtedy, gdy nie są konieczne.

KILKA DODATKOWYCH WSKAZÓWEK

Aby poprawić szybkość i wydajność MYSQL, należy:

  • przede wszystkim pamiętać o optymalizacji bazy ( patrz wyżej)

Jakość bazy można pośrednio sprawdzić przez:

mysql> show status like "%handler_read_rnd%";


Jeżeli te wartości są duże, znaczy to, że system bazy danych musi często skanować całą baze w celu poszukiwania danych. To jest oznaka, że coś jest nie tak z indeksowaniem, lub budową zapytań.

  • bufor kluczy

Zapotrzebowanie na bufor można sprawdzić komendą:



Najlepiej, żeby wartość 'reads' była mniejsza od 0.01 wartości 'requests'. Jeżeli jest to więcej niż 0.5-0.1, to zdecydowanie powinniśmy zwiększyć wielkość buforu (key_buffer=XXXM).

  • pamięć podręczna zapytań

Tu sprawa jest bardziej indywidualna i zależna od rodzaju baz danych. Idea jest taka: należy zrobić tak, żeby ilość trafień była jak największa:

mysql> show status like "qcache%";


Opcję można regulować przez następujące wpisy:

query_cache_limit = XM
query_cache_size = XXXM
query_cache_type = X


  • otwarte tabele:



ustawiane przez 'table_cache'. Dobrze by była, żeby liczba otwartych tabel była mniejsza od wartości maksymalnej, jednakże nie zawsze jest to możliwe (np. przy dużej liczbie baz danych). Przy dużej liczbie pamięci RAM warto też zastanowić się nad zwiększeniem wielkości tablic tymczasowych budowanych w pamięci, a nie na dysku.

Jeżeli system pokazuje, że często buduje tabele tymczasowe na dysku:



To można zwiększyć wielkość buforu w RAM na te tabele wpisem tmp_table_size.

  • pamięć podręczna wątków





Jeżeli stworzonych wątków jest dużo można zwiększyć wielkość 'thread_cache_size'. Ilość trafień to jest connections/threads_created.

  • Bardziej indywidualne opcje to bufory poszczególnych operacji:
join_buffer
record_buffer
sort_buffer