Apple Facebook Google Microsoft badania bezpieczeństwo patronat DI prawa autorskie serwisy społecznościowe smartfony

Porada: MySQL, a poprawa wydajności aplikacji

Porada Kei.pl 27-01-2010, 01:04

MySQL - popularny system bazodanowy, wraz z systemem Linux, serwerem Apache i językiem PHP tworzy jedną z najbardziej popularnych platform do tworzenia aplikacji sieciowych (ów słynny LAMP). Platformę tą wykorzystują niezliczone produkty - CMS, fora dyskusyjne, adservery itp. Nic więc dziwnego w tym, że poprawne działanie baz danych, jako kluczowe dla zapewnienia poprawnego działania serwisów, jest jedną z głównych trosk administratorów.

Mechanizmy analizy

MySQL udostępnia różne mechanizmy służące do analizy wydajności i optymalizacji zapytań. Podstawą są tak zwane slowlogi. Jest to log serwera MySQL w którym zapisywane są wszystkie zapytania na wykonanie których serwer potrzebował więcej niż zdefiniowany przedział czasu (np. jedna sekunda).

Przykładowy wpis w slowlogu może wyglądać następująco:

# Time: 070909 5:08:02 # User@Host: xxx[xxx] @ domena.pl [127.0.0.1]
# Query_time: 0.038309 Lock_time: 0.000042 Rows_sent: 0 Rows_examined: 31483
use baza;
select * from tabela where uri='jakis_ciag' AND id_serwisu='1';

Interesują nas głównie dwie rzeczy - Query_time - czas wykonania zapytania w sekundach (im więcej, tym gorzej) i Rows_examined - ilość sprawdzonych rekordów (także im więcej, tym większą pracę musi wykonać serwer MySQL)

Mając podejrzane zapytanie, możemy sprawdzić jak MySQL chce je realizować. Służy do tego polecenie DESCRIBE wydane z linii poleceń klienta MySQL, czy też poprzez phpMyAdmina.

mysql> describe(select * from tabela where uri='jakis_ciag' AND id_serwisu='1');



select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
SIMPLE | tabela | ALL | NULL | NULL | NULL | NULL | 31485 | Using where



W efekcie widzimy, że wykonywany jest pełny skan tabeli, sprawdzane są wszystkie istniejące rekordy. Nie wykorzystujemy żadnych mechanizmów optymalizacji MySQL.
Sprawdźmy jeszcze strukturę tabeli:

describe tabela;

| Field   | Type   | Null   | Key   | Default   | Extra   |  
| id   | int(11)   | NO   | PRI   | NULL   | auto_increment   |  
| id_serwisu   | int(11)   | NO   |   | 0   |   |
| uri   | text   | NO   |   |   |   |  
| odslon   | int(11)   | NO   |   | 0   |   |  


Jak widać, na polu id założony jest główny klucz, nie mamy natomiast zdefiniowanych innych indeksów. Tabela wymaga więc poprawy.

Indeksy - co to jest i dlaczego jest takie ważne?

Indeksy wykorzystywane są przez MySQL aby szybko zlokalizować rekordy zawierające pola o konkretnej wartości. Bez indeksu MySQL zmuszony jest przeglądać całą tabelę, co przy większej ilości rekordów jest wybitnie nieopłacalne.
MySQL wykorzystuje indeksy między innymi podczas:
- sprawdzania warunku WHERE
- łączenia tabeli poprzez JOIN
- sortowania tabeli
- wywoływania funkcji MIN() i MAX()

Oczywiście, nie ma róży bez kolców i indeksy posiadają także pewne wady:
- zmniejszają szybkość operacji INSERT, UPDATE i DELETE
- zwiększają zapotrzebowanie na przestrzeń dyskową

Kiedy powinniśmy stosować indeksy? Jak najczęściej. Zalety szybkiego dostępu do danych są ogromne i znacznie przeważają nad wadami. Należy jednak pamiętać żeby nie zakładać indeksów które nie będą potem wykorzystywane.

Wracając do naszego przykładowego zapytania, w warunku WHERE mamy dwa ograniczenia a żadne z nich nie wykorzystuje indeksu. Załóżmy więc indeks na pole id_serwisu.

mysql> alter table tabela add index (`id_serwisu`);
Query OK, 31486 rows affected (0.83 sec)
Rekordów: 31486 Duplikatów: 0 Ostrzeżeń: 0

i sprawdźmy co udało się nam osiągnąć:

mysql> describe(select * from tabela where uri='jakis_ciag' AND id_serwisu='1');

select_type   | table   | type   | possible_keys   | key   | key_len   | ref   | rows   | Extra  
SIMPLE   | tabela   | ALL   | id_serwisu   | id_serwisu   | NULL   |NULL   |23615   | Using where


Jak widać, ilość rekordów koniecznych do przeglądnięcia zmniejszyła się o ok. 8 tysięcy.

Struktura tabeli

Kolejną kwestią o której trzeba pamiętać, jest dobranie odpowiedniej struktury tabeli do danych, jakie będziemy w niej przechowywać. Zasadą podstawową powinno być minimalizowanie rozmiaru bazy - stosujmy SMALLINT zamiast INT, VARCHAR(20) zamiast VARCHAR(100).
Drugą zasadą jest dobór typu danych pola do jego zawartości. Nie przechowujmy danych typu INTEGER w polach VARCHAR czy TEXT. Jeśli to możliwe, stosujmy CHAR bądź VARCHAR a nie TEXT. Ważne jest to np. wtedy gdy w zapytaniu mamy warunek:

WHERE tabela1.id=tabela2.id

Jeśli pole tabela1.id jest typu INT a pole tabela2.id typu np. CHAR, może nie być możliwości użycia indeksów. MySQL będzie musiał przeglądnąć całość obydwu tabeli, co jest procesem kosztownym zarówno jeśli chodzi o czas procesora jak i o operacje dyskowe. Podobnie, dane typu BLOB czy TEXT nie są przez MySQL indeksowane i ich zastosowanie uniemożliwia skorzystanie z indeksów.

Wracamy do naszego przykładu. Jak pokazało nam polecenie DESCRIBE, pole uri, drugi element warunku WHERE, jest typu TEXT. Wiemy już że w takim wypadku nie ma możliwości zastosowania indeksów. Zamieńmy typ tego pola na typ VARCHAR, załóżmy na nim indeks (VARCHAR może być indeksowane) i zobaczmy co się stanie:

describe(select * from tabela where uri='jakis_ciag' AND id_serwisu='1');

select_type   | table   | type   | possible_keys   | key   | key_len   | ref   | rows   | Extra   |
SIMPLE   |tabela   | ref   | id_serwisu,uri   | uri   | 272   | const   | 1   | Using where   |



Zamiast sprawdzania 31,5 tysiąca (w wersji podstawowej) czy też 23,5 tysiąca (po dodaniu indeksu na pole id_serwisu) rekordów sprawdzany jest tylko i wyłącznie jeden.

Podsumowanie

Nie zawsze jesteśmy w stanie zmniejszyć obciążenie generowane na bazie danych praktycznie do zera. Często odwiedzana strona, wykonująca sporą ilość zapytań do bazy będzie w pewnym stopniu generować obciążenie. Ważnym jest to, aby starać się jak najbardziej do tego ideału zbliżyć. W końcu wszyscy chcemy, aby strona działała sprawnie, a użytkownik z niej korzystający nie czekał długich sekund na wygenerowanie kolejnej podstrony.

 

kei.plPoradę dla Czytelników Dziennika Internautów przygotowała firma Kei.pl, dostawca usług hostingowych. Zapraszamy do zadawania pytań i wysyłania próśb o kolejne porady na adres redakcji.


Aktualności | Porady | Gościnnie | Katalog
Bukmacherzy | Sprawdź auto | Praca


Artykuł może w treści zawierać linki partnerów biznesowych
i afiliacyjne, dzięki którym serwis dostarcza darmowe treści.

              *              



Ostatnie artykuły:


fot. Samsung



fot. HONOR