Optymalizacja częstotliwości próbkowania danych

6.1 Wprowadzenie

Przejdźmy teraz od teorii do praktyki.

Żyjemy w prawdziwym świecie, a wszystkie produkty oprogramowania są ostatecznie tworzone dla żyjących ludzi. Ci żyjący ludzie są bardzo zirytowani stronami, które ładują się wolno i programami, które zwalniają.

A jeśli zapytanie do bazy danych zajmuje więcej niż sekundę, jest to niedopuszczalne . Użytkownicy po prostu nie będą używać produktu, którego strony/funkcje działają tak wolno.

Często jednak, aby wyświetlić jedną stronę, trzeba wykonać kilkadziesiąt zapytań do bazy danych. A jeśli są wykonywane sekwencyjnie, to nie masz już drugiego limitu, ale powiedzmy 100 ms na żądanie.

Oto 5 najlepszych sposobów, w jakie programiści przyspieszają zapytania do baz danych:

1. Dodawanie indeksów do tabel w bazie danych.
2. Przepisywanie i optymalizacja zapytań.
3. Włącz (i skonfiguruj) buforowanie po stronie bazy danych.
4. Włącz buforowanie po stronie klienta.
5. Wykonywanie denormalizacji bazy danych.

Wszystkie te rzeczy są już w większości zaznajomione, więc poniższe będą tylko praktyczną radą.

6.2 Indeksy

Nie jest tajemnicą, że praca z bazą danych zajmuje większość pracy prawie każdej witryny. I to właśnie praca z bazą danych jest najczęściej wąskim gardłem aplikacji webowych.

W tym artykule chciałbym udzielić praktycznych porad dotyczących korzystania z MySQL.

Od razu powiem:

• ten artykuł jest napisany o MySQL, chociaż ogólne rzeczy prawdopodobnie będą prawdziwe dla każdego DBMS.
• wszystko napisane w artykule jest moim osobistym punktem widzenia i nie jest ostateczną prawdą.
• porady nie pretendują do miana nowości i są wynikiem uogólnienia przeczytanej literatury i osobistych doświadczeń.
• w ramach tego artykułu nie będę poruszał kwestii konfiguracji MySQL.

Problemy podczas korzystania z MySQL można podzielić na trzy następujące grupy (w kolejności ważności):

1. Nieużywanie lub niewłaściwe użycie indeksów.
2. Niewłaściwa struktura bazy danych.
3. Niepoprawne \ nieoptymalne zapytania SQL.

Przyjrzyjmy się bliżej każdej z tych grup.

Korzystanie z indeksów

Nieużywanie lub niewłaściwe używanie indeksów jest tym, co najczęściej spowalnia zapytania. Osobom, które nie są zaznajomione z mechanizmem działania indeksów lub jeszcze nie zapoznały się z nim w instrukcji, gorąco zachęcam do jej przeczytania.

Wskazówki dotyczące korzystania z indeksów:

• Nie musisz wszystkiego indeksować . Dość często, nie rozumiejąc znaczenia, ludzie po prostu indeksują wszystkie pola tabeli. Indeksy przyspieszają pobieranie, ale spowalniają wstawianie i aktualizowanie wierszy, więc wybór każdego indeksu musi być sensowny.
• Jednym z głównych parametrów charakteryzujących indeks jest selektywność, czyli liczba różnych elementów w indeksie. Indeksowanie pola, które ma dwie lub trzy możliwe wartości, nie ma sensu. Taki indeks przyniesie niewielkie korzyści.
• Wybór indeksów należy rozpocząć od analizy wszystkich zapytań pod kątem danej tabeli. Bardzo często po takiej analizie zamiast trzech czy czterech wskaźników można wykonać jeden złożony.
• Podczas korzystania z indeksów złożonych kolejność pól w indeksie ma kluczowe znaczenie.
• Nie zapomnij o zakryciu indeksów. Jeśli wszystkie dane w zapytaniu można pobrać z indeksu, MySQL nie uzyska bezpośredniego dostępu do tabeli. Takie prośby będą realizowane bardzo szybko. Np. dla zapytania SELECT name FROM user WHERE login='test'z indeksem (login, nazwa) dostęp do tabeli nie jest wymagany. Czasami sensowne jest dodanie dodatkowego pola do indeksu złożonego, co sprawi, że indeks zakryje i przyspieszy zapytania.
• W przypadku indeksów wierszy często wystarczy zaindeksować tylko część wiersza. Może to znacznie zmniejszyć rozmiar indeksu.
• Jeśli %jest na początku, LIKE(SELECT * FROM table WHERE field LIKE '%test')indeksy nie będą używane.
• Indeks PEŁNOTEKSTOWY jest używany tylko ze składnią PODAJ.POZYCJĘ ... PRZECIWKO .

6.3 Struktura bazy danych

Dobrze zaprojektowana baza danych to klucz do szybkiej i sprawnej pracy z bazą danych. Z drugiej strony źle zaprojektowana baza danych zawsze przysparza programistom bólu głowy.

Wskazówki dotyczące projektowania baz danych:

1. Używaj najmniejszych możliwych typów danych. Im większy typ danych, tym większa tabela, tym więcej dostępów do dysku jest potrzebnych do pobrania danych. Użyj bardzo wygodnej procedury: SELECT * FROM table_name PROCEDURE ANALYSE();określ minimalne możliwe typy danych.
2. Obserwuj normalne formy podczas fazy projektowania. Często programiści uciekają się do denormalizacji już na tym etapie. Jednak w większości przypadków na początku projektu nie jest oczywiste, w jaki sposób może to nastąpić. Denormalizacja tabeli jest znacznie łatwiejsza niż cierpienie z powodu nieoptymalnie zdenormalizowanej tabeli. A JOINczasami działa to szybciej niż nieprawidłowo zdenormalizowane tabele.
3. Nie używaj NULLkolumn, chyba że świadomie ich potrzebujesz.

6.4 Zapytania SQL.

Równie często pojawia się chęć przepisania wszystkich zapytań w natywnym SQL, aby zapytanie było jak najszybsze. Jeśli zdecydujesz się to zrobić, oto kilka wskazówek:

1. Unikaj żądań w pętli. SQL jest językiem zbiorów, a do pisania zapytań należy podchodzić nie w języku funkcji, ale w języku zbiorów.
2. Unikaj *(gwiazdek) w zapytaniach. Możesz podać dokładnie wybrane przez siebie pola. Zmniejszy to ilość pobieranych i wysyłanych danych. Nie zapomnij również o zakryciu indeksów. Nawet jeśli wybierzesz wszystkie pola w tabeli, lepiej je wymienić. Po pierwsze poprawia czytelność kodu. Używając gwiazdek, nie można dowiedzieć się, jakie pola znajdują się w tabeli, bez zaglądania do niej. Po drugie , dzisiaj twoja tabela ma pięć kolumn INT , a miesiąc później dodano jeszcze jedną TEXT i BLOB , a gwiazdka pozostała bez zmian.
3. Podczas dzielenia na strony, aby uzyskać całkowitą liczbę rekordów, użyj funkcji SQL_CALC_FOUND_ROWSi SELECT FOUND_ROWS();When used SQL_CALC_FOUND_ROWS MySQL, buforuje wybraną liczbę wierszy (przed zastosowaniem LIMIT), a gdy jest używany, SELECT FOUND_ROWS()zwraca tylko tę wartość z pamięci podręcznej bez konieczności ponownego wykonywania zapytania.
4. Nie zapominaj, że istnieje INSERTskładnia dla wielu wstawek. Jedno zapytanie uruchomi się o rząd wielkości szybciej niż wiele zapytań w pętli.
5. Używaj LIMITtam, gdzie nie potrzebujesz wszystkich danych.
6. Użyj INSERT… ON DUPLICATE KEY UPDATE…zamiast i INSERTlub UPDATEpo zaznaczeniu, a często zamiast REPLACE.
7. Nie zapomnij o tej niesamowitej funkcji GROUP_CONCAT. Może pomóc w przypadku złożonych zapytań.