Zaplanuj z nami swoją stronę internetową!
Pobierz dokument, który pomoże Ci zgromadzić podstawowe informacje oraz opracować strategię rozwoju
Twojego serwisu WWW.
[pobierz plik]
Astcon Rozwiązania Informatyczne
al. Zwycięstwa 96/98
81-451 Gdynia
tel: 58 352 46 79
tel: 22 350 64 30
fax: 58 690 79 85
e-mail: astcon@astcon.pl
Optymalizacja baz danych MySQL
Gdy programy działają zbyt wolno, gdy proste zapytania do bazy wykonywane są dłużej niż kilka sekund, to znak, że warto zoptymalizować naszą bazę danych.
Poniżej opisane sposoby mówią o optymalizacji samych baz danych, ale pamiętać trzeba, że to nie wszystko. Wiele problemów rozwiąże "postawienie" bazy i programu na dobrym i sprawnym serwerze. Niezbędny będzie szybki procesor, pojemny i szybki dysk twardy z dużą ilością pamięci cache oraz spora ilość pamięci RAM. Do tego oczywiście płyta główna, która będzie w stanie zapewnić odpowiednią szybkość przesyłu danych pomiędzy powyższymi elementami.
Dla baz danych umiejscowionych na serwerach
współdzielonych (hosting) trzeba pamiętać, że głównym problemem ociężałego działania może nie być sama baza, ale
inne programy (i bazy danych) działające na tym serwerze, które mogą "pożerać" nasze zasoby. Optymalnym
rozwiązaniem jest więc serwer dedykowany. Można zaproponować jeszcze takie rozwiązania sprzętowe jak:
- wdrożenie macierzy RAID 0 co powinno pomóc w przyspieszeniu odczytu danych z dysku (jeśli to jest problemem),
- wydzielenie bazy danych na oddzielny serwer dedykowany tylko i wyłącznie dla niej,
- podzielenie bazy danych na kilka serwerów.
Wydzielając bazy danych na oddzielne serwery, pamiętajmy jednak o tym, żeby połączenie LAN pomiędzy serwerami było wykonywane w możliwie największej prędkości.
Ten artykuł zasadniczo koncentruje się na silniku bazy danych MySQL, ale z powodzeniem większość zawartych w nim porad można zastosować również w innych bazach danych.
Przejdźmy więc do kilku sposobów optymalizacji samej bazy danych.
1. Tworzenie bazy danych - wybór rodzaju tabel
Zasadniczo wybór sprowadza się do dwóch rodzajów tabel - MyISAM lub InnoDB. Są to (mówiąc skrótowo) dwa różne
sposoby przechowywania danych w bazie na dysku serwera. Którą z nich wybrać? W zasadzie wybór jest prosty:
- jeżeli chcesz aby Twoja baza danych działała jak najszybciej, wybierz MyISAM,
- jeżeli potrzebujesz koniecznie obsługę transakcji (TRANSACTION) lub/i obsługę kluczy obcych (FOREIGN KEY) wybierz InnoDB.
Pamiętaj, że Twoja baza może łączyć tabele MyISAM oraz InnoDB, więc jeśli potrzebujesz obsługę transakcji być może wystarczy gdy będzie ona jedynie na kilku tabelach? Wówczas pozostałe możesz zapisać w MyISAM.
2. Rodzaje pól w bazie
Zawsze wybierz najmniejszy możliwy typ pola. Jeśli wiesz, że w danej tabeli będzie tylko kilkanaście elementów (np. lista województw), i nigdy się to nie zmieni, wybierz typ TINYINT dla pola ID. Dokładne informacje o tym jakie wartości może przechowywać dane pole liczbowe znajdziesz tutaj:
http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
3.Wartość UNSIGNED.
Pole TINYINT może przechowywać wartości od -127 do 128.
Pole UNSIGNED TINYINT może przechowywać wartości od 0 do 255.
Jeśli wiesz że w danej tabeli będzie ok. 200 elementów, ale żaden z nich nie będzie ujemny, wówczas zastosuj UNSIGNED. UNSIGNED powinno się KONIECZNIE stosować dla pól typu AUTO_INCREMENT, o których wiadomo że mogą przyjmować wartości jedynie od 0 w górę.
UNSIGNED oznacza, że pole nie może przyjmować wartości ujemnych. Oznacza to z kolei, że może przyjąć około dwa razy więcej wartości dodatnich (np. 255 zamiast 128 w polu TINYINT).
4.Pole tekstowe czy liczbowe?
Jeśli przechowujesz wartość która jest typową liczbą (tzn. taka, dla której ma sens operacja dodawania, mnożenia itd.) to przechowaj ją w polu liczbowym - np. TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, DECIMAL, NUMERIC itd. Taki rodzaj pola będzie odpowiedni np. dla ilości sztuk towaru w sklepie czy kwoty do zapłaty. Nie jest to odpowiedni typ pola np. dla numeru PESEL czy NIP - choć są to liczby to nie ma sensu dla nich operacja dodawania czy odejmowania - co komu da dodanie dwóch numerów PESEL?.
5. TEXT kontra VARCHAR kontra CHAR
Pole CHAR ma stałą szerokość, stosuj je wówczas, gdy wiesz że większość danych w tym polu będzie miało taką wartość podaną (tzn. nie będzie null) i wartość ta będzie miała określoną ilość znaków. Np. kod pocztowy - zawsze składa się z 6 znaków (5 cyfr i kreska).
Pole VARCHAR ma zmienną szerokość. Stosuj je wówczas, gdy nie wiesz jaką szerokość będzie miało pole. Przykładowo można je użyć do zapisania imienia i nazwiska. Pamiętaj, że to pole ma ograniczenie do 255 znaków.
TEXT stosuj tylko wówczas, gdy potrzebujesz dużej ilości tekstu do przechowania (np. artykuł w serwisie internetowym). W tym przypadku ograniczenie to 65000 znaków.
BLOB to pole podobne do TEXT, ale używane nie do przechowywania tekstu, lecz danych. Przykładowo możesz w nim przechować zdjęcie lub plik ZIP.
6. Kolejność w jakiej ustawiasz pola w tabeli również ma ogromne znaczenie
Na początku zawsze powinno znaleźć się pole AUTO_INCREMENT, jeśli takie będzie w tabeli. Następnie kolejno pola o stałej szerokości - np. liczbowe (TINYINT, INT) i inne (CHAR, ENUM, SET itd.). Po nich można dać pola, których wartości nie są przechowywane bezpośrednio w tabeli (typy TEXT i BLOB oraz ich odmiany - LARGETEXT, LARGEBLOB itd.). Na samym końcu dopiero powinny być umieszczone pola o zmiennej długości (VARCHAR, VARBINARY itd.).
Pola o stałej długości zajmują zawsze tyle samo bajtów pól w bazie danych. Pola o zmiennej długości mogą zajmować różną długość, zależnie od tego ile tekstu lub danych w nich umieścisz (VARCHAR, VARBINARY, SET).
Założmy że stworzyliśmy taką bazę:
----------------------- id INT UNSIGNED NOT NULL AUTO_INCREMENT, status TINYINT UNSIGNED NOT NULL, imie VARCHAR(50) NOT NULL, nazwisko VARCHAR(50) NOT NULL, PRIMARY KEY(id) -----------------------
i że mamy w niej 20 milionów rekordów.
Pobranie danych id i status będzie przebiegało szybko, ponieważ baza "wie" gdzie jest dany rekord (na podstawie id), wie że id jest pierwszym polem i zajmuje 4 bajty (czyli jest na pozycjach 0 do 3), zaś status jest drugim polem zajmującym 1 bajt (czyli na pozycji 4). Baza może więc szybko odnaleźć pole imię, bo wie, że zaczyna się ono na pozycji 5.
Niestety większy problem jest ze znalezieniem już końca pola imie, i początku pola nazwisko. Pola te mają zmienną długość, więc baza nie może na chybił trafił przeskoczyć do pola 55 (50 to długość pola imie + 5 czyli pozycja początku pola imię), lecz musi je odnaleźć. To zabiera bardzo dużo czasu, dlatego stosuj pola o zmiennej długości tylko gdy to konieczne.
WAŻNA UWAGA!!! Tabela zawierająca WYŁĄCZNIE pola o stałej długości będzie pracowała o wiele szybciej.
Ale nie można też przesadzić w drugą stronę. To prawda, że tabela z polem imie CHAR(50) będzie teoretycznie pracowała szybciej niż tabela z polem imie VARCHAR(50), ale pamiętaj, że będzie zajmowała znacznie więcej miejsca na dysku. W praktyce więc może pracować wolniej.
Jaki więc rodzaj pola wybrać i jak? Doświadczalnie. Wypełnij tabelę danymi i zobacz z jaką prędkością się wyszukują. Następnie zmień rodzaj pola na inny (ALTER TABLE) i spróbuj ponownie. Pamiętaj, żeby wypełnić pole dużą ilością rekordów, ponieważ dla małej ilości różnice będą niezauważalne. Dodatkowo możesz na tym samym komputerze gdzie znajduje się baza uruchomić jakiś program który mocno wykorzystuje dysk - np. scandisk. Pozwoli to sprawdzić jak baza danych radzi sobie w warunkach dużego obciążenia serwera.
7. NOT NULL
W powyższym przykładzie stosowaliśmy polecenie NOT NULL. Określa ono, czy dane pole może przyjmować wartość NULL (czyli w zasadzie brak wartości). Jeśli dane pole zawsze musi posiadać wartość (np. zawsze musi być podana kwota przelewu, nie może być przelewu bez kwoty), wówczas możemy dodać polecenie NOT NULL. Jeśli wartość jest NOT NULL, to oszczędzamy znów kilka bajtów miejsca dla każdego pola. Wydaje się, że nie jest to dużo, ale jeśli baza ma kilka milionów wpisów a każde pole oznaczymy NOT NULL to różnica stanie się zauważalna.
8. Stosuj INDEXy (klucze)
Klucze, nazywane też indeksami, znacznie przyspieszają wyszukiwanie informacji w bazie. Działają identycznie jak indeks w książce, pozwalający na odnalezienie w niej danych słów kluczowych. Pamiętaj, że klucze spowalniają dodawanie nowych elementów do bazy, ponieważ nie tylko dodać trzeba nowy wpis, ale trzeba również zapisać w odpowiednim miejscu klucz do niego. Klucze jednak znacznie przyspieszają wyszukiwanie dlatego w większości przypadków ich stosowanie jest uzasadnione.
Klucze zakładaj tylko i wyłącznie na polach wg. których będziesz wyszukiwał, np.:
KEY(numer_NIP)
Jeśli wiesz, że wyszukiwanie będzie zawsze przebiegało po kilku polach, możesz stworzyć z nich jeden klucz, np.:
KEY(wojewodztwo,miasto,cena)
Taki klucz jak powyżej ma zastosowanie jedynie gdy wyszukujemy wg. województwa i ew. dodatkowo miasta i ew. dodatkowo ceny. Jeśli wyszukamy tylko wg ceny, to ten indeks nie będzie mógł zostać wykorzystany przez bazę danych. Staraj się unikać klucza UNIQUE. Pozwala ono zachować spójność bazy danych, nie pozwalając na dodanie elementów które już istnieją w bazie (np. nie można dodać drugiej osoby z takim samym numerem PESEL jak inna osoba już istniejąca w bazie), jednak znacznie spowalniają one dodawanie, ponieważ za każdym razem baza musi zostać przeszukana pod kątem istnienia w niej już podobnego rekordu.
Klucz obcy (FOREIGN KEY) ma zastosowanie jedynie w tabelach InnoDB. W MyISAM jest ignorowany. Jeśli ważne jest zachowanie spójności danych wówczas możesz go zastosować, lecz pamiętaj, że on również będzie znacznie spowalniał działanie bazy.
9. Łączenie z bazą danych - kodowanie znaków
Łącząc z bazą danych, aby ustalić odpowiednie kodowanie dla znaków, stosuj polecenie
SET NAMES binary;
Spowoduje to, że znaki z bazy danych bądą przesyłane do Twojego programu (np. w PHP) identycznie tak, jak są tam zapisane. Jeśli zamiast binary podasz np. utf8, to każdy znak przed wysłaniem zostanie skonwertowany na utf8. Jeśli sposób kodowania znaków w Twojej bazie to ISO 8859-2, a zapytanie spowoduje pobranie dużej ilości danych, to znacznie obciąży to system. SET NAMES binary pozwala na uniknięcie tych niepotrzebnych konwersji, dlatego stosuj to gdzie tylko się da.
10. Optymalizacja zapytań
Pamiętaj że każdy dodatkowy element w zapytaniu to dodatkowa praca dla bazy. Nie stosuj porządkowania bazy (ORDER BY, HAVING) jeśli nie jest to konieczne. Część pracy wykonywanej przez bazę czasem można wykonać efektywniej w kodzie programu (np. w PHP) i przez to odciążyć bazę. Pobierając dane z bazy pobieraj w takiej kolejności w jakiej są tam zapisane. Jeśli pola w bazie to kolejno id,imie,nazwisko, wiek to wykonując zapytanie musisz napisać właśnie w takiej kolejności:
SELECT id,imie,nazwisko,wiek FROM uzytkownicy
Dzięki temu baza danych nie musi przesuwać pól w tabeli specjalnie dla potrzeb zapytania, co również wpływa na szybkość wykonywania wysyłanych do niej poleceń.
Zastanów się czy stosujesz prawidłowe złączenia (JOIN). LEFT i RIGHT JOIN działają najszybciej, następnie jest INNER JOIN i dopiero OUTER JOIN. Dla ogromnych tabel to ostatnie złączenie potrafi znacznie zwiększyć czas wykonywania zapytania. Podobnie staraj się unikać zagnieżdżonych zapytań (zapytanie w zapytaniu) oraz polecenia GROUP. Stosuj natomiast polecenie LIMIT, które przyspiesza wykonywania zapytania. Przydatne jest nie tylko w zapytaniach SELECT ale także w UPDATE, gdzie podając LIMIT 1 określamy, że tylko jedno pole chcemy zmienić. Pozwoli to uniknąć żmudnego przeszukiwania bazy danych w poszukiwaniu kolejnych rekordów, podczas gdy wiemy że ten jeden, który miał być zmieniony dawno już został zaktualizowany.
Jeśli mimo zastosowania powyższych porad zapytania wykonują się zdecydowanie zbyt długo, oznacza to, że powinno się zdecydowanie przemyśleć strukturę bazy danych oraz sposób pisania zapytań. Być może nie wszystkie informacje jakie trafiają do bazy danych musimy faktycznie przechowywać. Być może część z nich jest rzadko wykorzystywana i możemy przechować je gdzie indziej? Być może nie musimy wyszukiwać danych wg wszystkich pól? Być może nasze zapytania zawierają elementy całkowicie zbędne? Błędy związane z bazą danych i jej ociężałym działaniem mogą być spowodowane kiepską architekturą danych, stworzoną już na początku realizacji programu. Dlatego też polecamy korzystanie z profesjonalnych usług tworzenia baz danych i oprogramowania, dzięki czemu będziesz miał pewność, że większość problemów z wydajnością po prostu przestanie mieć znaczenie. A nawet jeśli się pojawią - będziesz mógł liczyć na fachową pomoc.
Nie masz czasu na wykonanie analizy biznesowej swojego sklepu bądź serwisu?
Zleć to nam!