→ Optymalizacja zapytań MySQL

MySQL posiada duży zestaw funkcji dla różnego rodzaju ( ZAMÓW PRZEZ), zgrupowania ( GRUPUJ WEDŁUG), związki ( LEWE ŁĄCZENIE lub WŁAŚCIWE DOŁĄCZENIE) itp. Wszystkie są z pewnością wygodne, ale w warunkach jednorazowych próśb. Na przykład, jeśli osobiście potrzebujesz wykopać coś w bazie danych za pomocą kilku tabel i linków, to oprócz powyższych funkcji możesz, a nawet musisz użyć operatorów warunkowych JEŚLI. Głównym błędem początkujących programistów jest chęć zastosowania takich żądań w działającym kodzie witryny. W tym przypadku złożone zapytanie jest z pewnością piękne, ale szkodliwe. Chodzi o to, że żadne operatory sortowania, grupowania, łączenia lub podzapytania nie mogą być wykonywane w pamięć o dostępie swobodnym, I użyć dysk twardy do tworzenia tabel tymczasowych. A trudno, jak wiesz, jest wąskim gardłem serwera.

Zasady optymalizacji zapytań mysql

1. Unikaj zagnieżdżonych zapytań

To najpoważniejszy błąd. Proces nadrzędny zawsze będzie czekał na zakończenie przez dziecko iw tym czasie utrzyma połączenie z bazą danych, użyje dysku i załaduje iowait. Dwa równoległe zapytania do bazy danych i wykonanie niezbędnego filtrowania w interpreterze serwera ( Perl, PHP itp.) wykona o rząd wielkości szybciej niż zagnieżdżony.

perl przykłady Czego nie robić:

Moje $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth->wykonaj(); while (my @row = $sth->fetchrow_array()) ( my $groupNAME = $dbh->selectrow_array("SELECT groupNAME FROM groups WHERE groupID = $row"); ### Powiedzmy, że musimy zebrać nazwy grup # ## i dodaj je na końcu tablicy danych push @row => $groupNAME; ### Zrób coś innego... )

lub nie w ten sposób:

Moje $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID FROM tbl WHERE groupID IN(SELECT groupID FROM groups WHERE groupNAME = "First" OR groupNAME = "Drugi" OR groupNAME = "Siódmy")");

Jeśli zajdzie taka potrzeba, we wszystkich przypadkach lepiej jest użyć skrótu, tablicy lub innego sposobu filtrowania.

Przykład w perlu, jak to zwykle robię:

Moje %grupy; my $sth = $dbh->prepare("SELECT ID_grupy,NAZWA_grupy FROM grup WHERE ID_grupy IN(2,3,7)"); $sth->wykonaj(); while (my @row = $sth->fetchrow_array()) ( $groups($row) = $row; ) ### A teraz wykonaj główne pobieranie bez podzapytania my $sth2 = $dbh->prepare("SELECT elementID ,elementNAME,groupID FROM tbl WHERE groupID IN(2,3,7)"); $sth2->wykonaj(); while (my @row = $sth2->fetchrow_array()) ( push @row => $groups($row); ### Zrób coś innego... )

2. Nie sortuj, nie grupuj ani nie filtruj w bazie danych

Jeśli to możliwe, nie używaj w zapytaniach operatorów ORDER BY, GROUP BY, JOIN. Wszystkie używają tabel tymczasowych. Jeśli sortowanie lub grupowanie jest potrzebne tylko do wyświetlenia elementów, na przykład alfabetycznie, lepiej wykonać te czynności w zmiennych interpretera.

Perlowe przykłady, jak nie sortować:

Moje $sth = $dbh->prepare("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7) ORDER BY elementNAME"); $sth->wykonaj(); while (my @row = $sth->fetchrow_array()) ( print qq($row => $row); )

Przykład w perlu, jak zwykle sortuję:

Moja lista $ = $dbh->selectall_arrayref("SELECT elementID,elementNAME FROM tbl WHERE groupID IN(2,3,7)"); foreach (sort ( $a-> cmp $b-> ) @$list)( print qq($_-> => $_->); )

O wiele szybciej. Różnica jest szczególnie widoczna, jeśli danych jest dużo. Jeśli chcesz posortować według perl na wielu polach można zastosować sortowanie Schwartz. Jeśli wymagane jest losowe sortowanie ORDER BY RAND() - użyj losowego sortowania perla.

3. Użyj indeksów

Jeśli sortowanie w bazie danych może zostać w niektórych przypadkach porzucone, to GDZIE prawdopodobnie się nie powiedzie. Dlatego do porównywania pól konieczne jest ustawienie indeksów. Są proste.

Z taką prośbą:

ZMIEŃ TABELĘ `any_db`.`any_tbl` DODAJ INDEKS `text_index`(`text_fld`(255));

Gdzie 255 to długość klucza. Nie jest to wymagane w przypadku niektórych typów danych. Szczegóły znajdziesz w dokumentacji MySQL.

Poryskav w wolnym czasie na tyrnecie, zdziwiłem się, że nie ma specjalnych artykułów-poradników na temat optymalizacji zapytań SQL. przeglądając różne informacje i książek, postaram się udzielić wskazówek, które pomogą Ci nauczyć się pisać właściwe zapytania.

1. Optymalizacja stołu. Jest to konieczne, gdy w tabeli dokonano wielu zmian: albo większość danych została usunięta, albo dużo zmian z ciągami o zmiennej długości - text, varchar, blob. Faktem jest, że usunięte rekordy są nadal przechowywane w pliku indeksu, a późniejsze wstawianie nowych rekordów wykorzystuje pozycje starych rekordów. Aby zdefragmentować plik danych, użyj polecenia OPTYMALIZUJ.

   OPTYMALIZUJ TABELĘ `tabela1`, `tabela2`…

   Nie zapominaj, że podczas optymalizacji dostęp do stołu jest zablokowany.

Odbudowa danych w tabeli. Po częstych zmianach w tabeli to polecenie może poprawić wydajność pracy z danymi. Przestawia je w tabeli i sortuje według określonego pola.

ALTER TABLE `table1` ORDER BY `id`

Typ danych. Najlepiej nie indeksować pól typu string, zwłaszcza pól typu TEKST. W przypadku tabel, których dane często się zmieniają, zaleca się unikanie używania pól typu VARCHAR i BLOB, ponieważ ten typ tworzy dynamiczną długość wiersza, zwiększając tym samym czas dostępu do danych. Jednocześnie zaleca się używanie pola VARCHAR zamiast TEKST, ponieważ działa z nim szybciej.

NOT NULL i pole domyślne. Najlepiej oznaczyć pola jako NIE NULL, ponieważ oszczędzają one trochę miejsca i eliminują niepotrzebne sprawdzenia. W takim przypadku warto ustawić domyślną wartość pól i wstawić nowe dane tylko wtedy, gdy się od niej różnią. Przyspieszy to dodawanie danych i skróci czas analizowania tabel. A warto pamiętać, że typy pól BLOB i TEXT nie mogą zawierać wartości domyślnych.

Stałe połączenie z serwerem bazy danych. Pozwala uniknąć marnowania czasu na ponowne połączenie. Warto jednak pamiętać, że serwer może mieć limit liczby połączeń, a jeśli ruch na stronie jest bardzo duży, to stałe połączenie może być okrutnym żartem.

Separacja danych. Zaleca się oddzielenie długich pól niebędących kluczami w osobnej tabeli, jeśli tabela źródłowa jest stale pobierana i często się zmienia. Ta metoda zmniejszy rozmiar zmiennej części tabeli, co doprowadzi do ograniczenia wyszukiwania informacji.
Dotyczy to zwłaszcza przypadków, gdy część informacji w tabeli jest przeznaczona tylko do odczytu, a druga część nie tylko do odczytu, ale także do modyfikacji (nie zapominaj, że podczas zapisywania informacji cała tabela jest zablokowana). Uderzającym przykładem jest licznik trafień.
Jest tabela (najpierw nazwa) z polami id, content, show. Pierwszy klucz z auto_increment, drugi - tekst, a trzeci numeryczny - zlicza liczbę wyświetleń. Za każdym razem, gdy strona jest ładowana, +1 jest dodawane do ostatniego pola. Oddziel ostatnie pole do drugiej tabeli. Tak więc pierwsza tabela (pierwsza) będzie miała pola id, content, a druga (druga) będzie miała pola show i first_id. Pierwsze pole jest jasne, myślę, że drugie jest również odniesieniem do pola identyfikatora klucza z pierwszej tabeli.
Teraz w drugiej tabeli pojawią się ciągłe aktualizacje. Jednocześnie lepiej zmienić liczbę odwiedzin nie programowo, ale poprzez żądanie:

A wybór nastąpi przy skomplikowanym zapytaniu, ale jedno lub dwa nie są potrzebne:

SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN pierwszy ON (first.id = second.first_id)

Warto pamiętać, że to wszystko nie dotyczy witryn o małym ruchu i niewielkiej ilości informacji.

Nazwy pól, dzięki której np. dwie tabele są połączone, pożądane jest, aby miały one tę samą nazwę. Wtedy jednoczesne odbieranie informacji z różnych tabel za pomocą jednego zapytania będzie szybsze. Na przykład z poprzedniego akapitu pożądane jest, aby w drugiej tabeli pole miało nazwę nie first_id, ale po prostu id, podobnie jak w pierwszej tabeli. Jednak przy tej samej nazwie na zewnątrz staje się niejasne, co, gdzie i jak. Dlatego porady amatorskie.

Wymagaj mniej danych. Jeśli to możliwe, unikaj zapytań takich jak:

WYBIERZ * Z `tabela1`

Zapytanie nie jest wydajne, ponieważ najprawdopodobniej zwraca więcej danych, niż jest to konieczne do pracy. Lepszym projektem byłoby:

SELECT id, nazwa FROM table1 ORDER BY id LIMIT 25

Zaraz zrobię dodatek o celowości korzystania z LIMITu. To polecenie ogranicza liczbę wierszy zwracanych przez zapytanie. Oznacza to, że zapytanie staje się „lżejsze” i bardziej produktywne.
Jeśli jest LIMIT równy 10, to po otrzymaniu dziesięciu wierszy żądanie jest przerywane.
Jeżeli zapytanie korzysta z sortowania ORDER BY, to nie dotyczy całej tabeli, a jedynie selekcji.
Jeśli użyjesz LIMIT w połączeniu z DISTINCT, zapytanie zostanie przerwane po znalezieniu określonej liczby unikalnych wierszy.
Jeśli użyjesz LIMIT 0, to zostanie zwrócona pusta wartość (czasami trzeba określić typ pola lub po prostu sprawdzić zapytanie).

Ogranicz użycie DISTINCT. To polecenie wyklucza zduplikowane wiersze z wyniku. Polecenie wymaga dłuższego czasu przetwarzania. Najlepiej łączyć z LIMIT.
Jest mała sztuczka. Jeśli konieczne jest przejrzenie dwóch tabel na temat meczu, powyższe polecenie zostanie zatrzymane, gdy tylko zostanie znaleziony pierwszy mecz.

Ogranicz użycie SELECT na stale zmieniających się stołach.

2. Nie zapomnij o tymczasowych tablicach HEAP. Pomimo tego, że tabela ma ograniczenia, wygodnie jest przechowywać w niej dane pośrednie, zwłaszcza gdy trzeba dokonać kolejnego wyboru z tabeli bez ponownego pobierania. Faktem jest, że ta tabela jest przechowywana w pamięci i dlatego dostęp do niej jest bardzo szybki.
3. Wyszukiwanie wzorców. Zależy od rozmiaru pola, a jeśli zmniejszysz rozmiar z 400 bajtów do 300, czas wyszukiwania zostanie skrócony o 25%.

Jak zoptymalizować zapytania MySQL?

W przypadku zwykłej, mało odwiedzanej witryny nie ma dużej różnicy, czy zapytania do bazy danych MySQL są zoptymalizowane, czy nie. Jednak w przypadku mocno obciążonych serwerów produkcyjnych różnica między poprawnym a nieprawidłowym SQL jest ogromna, a w czasie wykonywania mogą one znacząco wpłynąć na zachowanie i niezawodność usług. W tym artykule przyjrzę się, jak pisać szybkie zapytania i czynnikom, które powodują ich spowolnienie.

Dlaczego MySQL?

Dziś dużo się mówi o Dig Data i innych nowych technologiach. NoSQL i rozwiązania chmurowe są świetne, ale wiele popularnych programów (takich jak WordPress, phpBB, Drupal) nadal działa na MySQL. Migracja do najnowszych rozwiązań może skutkować czymś więcej niż tylko zmianą konfiguracji na serwerach. Ponadto wydajność MySQL jest nadal na poziomie, zwłaszcza wersji Percony.

Nie popełniaj powszechnego błędu wyrzucania coraz większej ilości sprzętu, aby rozwiązać problem powolnych zapytań i dużego obciążenia serwera - lepiej udać się do źródła problemu. Zwiększenie mocy procesorów i dyski twarde a dodanie pamięci RAM jest również pewnym rodzajem optymalizacji, jednak nie o tym będziemy rozmawiać w tym artykule. Ponadto, optymalizując witrynę i rozwiązując problem z żelazem, obciążenie będzie rosło tylko wykładniczo. Więc to jest tylko krótkoterminowe rozwiązanie.

Dobra znajomość języka SQL jest dla programisty WWW niezbędnym narzędziem do efektywnej optymalizacji i korzystania z relacyjnych baz danych. W tym artykule skupimy się na popularnej bazie danych open source, często używanej w połączeniu z PHP, czyli MySQL.

Dla kogo jest ten artykuł?

Dla twórców stron internetowych, architektów i programistów baz danych oraz administratorzy systemu dobrze zaznajomiony z MySQL. Jeśli nie korzystałeś wcześniej z MySQL, ten artykuł może nie być dla Ciebie zbyt przydatny, ale nadal postaram się być tak pouczający i pomocny, jak to tylko możliwe, nawet dla tych, którzy dopiero zaczynają korzystać z MySQL.

Najpierw kopia zapasowa

Zalecam wykonanie następujących kroków na bazie MySQL, z którą pracujesz, ale pamiętaj, aby wykonać kopię zapasową. Jeśli nie masz bazy danych do pracy, podam przykłady tworzenia własnej bazy danych tam, gdzie to konieczne.

Tworzenie kopii zapasowych MySQL jest łatwe dzięki narzędziu mysqldump:

$ mysqldump myTab > myTab-backup.sql Możesz przeczytać więcej o mysqldump .

Co powoduje spowolnienie żądania?

Oto ogólna lista czynników, które wpływają na szybkość wykonywania zapytań i obciążenie serwera:

• indeksy tabel;
• klauzula WHERE (oraz użycie wewnętrznych funkcji MySQL, takich jak IF lub DATE);
• sortowanie według ORDER BY;
• częste powtarzanie tych samych próśb;
• rodzaj mechanizmu przechowywania danych (InnoDB, MyISAM, Memory, Blackhole);
• niekorzystanie z wersji Percona;
• konfiguracje serwerów (my.cnf / my.ini);
• duże wyjścia danych (ponad 1000 wierszy);
• niestabilne połączenie;
• konfiguracja rozproszona lub klastrowa;
• słaba konstrukcja stołu.

W dalszej części zajmiemy się wszystkimi tymi kwestiami. Ponadto zainstaluj Perconę, jeśli jeszcze nie używasz tego wbudowanego zamiennika dla domyślnego MySQL - zapewni to ogromny wzrost mocy bazy danych.

Czym są indeksy?

Indeksy są używane w MySQL do wyszukiwania wierszy z określonymi wartościami kolumn, na przykład za pomocą polecenia WHERE. Bez indeksów MySQL musi czytać całą tabelę, zaczynając od pierwszego wiersza, szukając odpowiednich wartości. Im większy stół, tym większy koszt.

Jeśli tabela zawiera indeksy kolumn, które zostaną użyte w zapytaniu, MySQL szybko znajdzie lokalizacje wymaganych informacji bez przechodzenia przez całą tabelę. Jest to znacznie szybsze niż przeszukiwanie każdego wiersza sekwencyjnie.

Słabe połączenie?

Gdy Twoja aplikacja łączy się z bazą danych i ustanawiane jest trwałe połączenie, będzie ono używane za każdym razem bez konieczności otwierania nowego połączenia za każdym razem. To optymalne rozwiązanie dla środowiska pracy.

Ograniczamy częste powtarzanie tych samych próśb

Najszybszy i skuteczna metoda Ten, który znalazłem, to utworzenie repozytorium żądań i ich wyników za pomocą Memcached lub Redis. Dzięki Memcache możesz łatwo buforować wyniki swojego żądania, na przykład w następujący sposób:

connect("localhost",11211); $cacheResult = $cache->get("nazwa klucza"); if($cacheResult)( //nie potrzebujemy zapytania $result = $cacheResult; ) else ( //uruchom zapytanie $mysqli = mysqli("p:localhost","username","password","table "); //add p: do długoterminowego przechowywania $sql = "WYBIERZ * Z postów LEWY DOŁĄCZ userInfo using (UID) WHERE posts.post_type = "post" || posts.post_type = "artykuł" ORDER BY kolumna LIMIT 50" ; $result = $mysqli->query($sql); $memc->set("nazwa-klucza", $result->fetch_array(), MEMCACHE_COMPRESSED,86400); ) //Hasło $cacheResult do szablonu $template- >assign(" posty", $cacheResult); ?> Teraz ciężkie zapytanie używające LEFT JOIN będzie wykonywane tylko raz na 86 400 sekund (czyli raz dziennie), co znacznie zmniejszy obciążenie serwera MySQL, pozostawiając zasoby dla innych połączeń.

Uwaga: Dodaj p: na początku argumentu hosta MySQLi, aby utworzyć trwałe połączenie.

Konfiguracja rozproszona lub klastrowa

Kiedy danych staje się coraz więcej, a prędkość Twojej usługi spada, może Cię ogarnąć panika. Szybkim rozwiązaniem może być alokacja zasobów (sharding). Jednak nie polecam tego robić, chyba że masz duże doświadczenie, ponieważ dystrybucja z natury sprawia, że ​​struktury danych są bardzo złożone.

Słaby projekt stołu

Tworzenie schematów baz danych nie jest trudnym zadaniem, jeśli przestrzegasz złotych zasad pracy z ograniczeniami i wiesz, co działa najlepiej. Na przykład przechowywanie obrazów w komórkach BLOB jest bardzo mylące - lepiej przechowuj ścieżkę do pliku w komórce VARCHAR, jest to znacznie lepsze rozwiązanie.

Zapewnienie odpowiedniego projektu dla przeznaczenie ma kluczowe znaczenie w tworzeniu aplikacji. Przechowuj różne dane w różnych tabelach (np. kategorie i artykuły) i upewnij się, że relacje przyjaciel (wiele do jednego) i jeden do wielu (jeden do wielu) można łatwo powiązać z identyfikatorami (identyfikatorami). Użycie FOREIGN KEY w MySQL jest idealne do przechowywania danych kaskadowych w tabelach.

Podczas tworzenia tabeli pamiętaj o następujących kwestiach:

• Twórz efektywne tabele, aby rozwiązać swoje problemy i nie wypełniaj tabel niepotrzebnymi danymi i relacjami.
• Nie oczekuj, że MySQL wykona Twoją logikę biznesową lub programowanie — dane muszą być gotowe do wstawienia wiersza przez Twój język skryptowy. Na przykład, jeśli chcesz posortować listę losowo, zrób to w tablicy PHP bez użycia ORDER BY z arsenału MySQL.
• Użyj typów indeksów UNIQUE dla unikalnych zestawów danych i użyj opcji ON DUPLICATE KEY UPDATE, aby aktualizować datę, na przykład, aby wiedzieć, kiedy wiersz został ostatnio zmodyfikowany.
• Użyj typu danych INT do przechowywania liczb całkowitych. Jeśli nie określisz rozmiaru typu danych, MySQL zrobi to za Ciebie.

Podstawy optymalizacji

Aby skutecznie zoptymalizować, musimy zastosować trzy podejścia do Twojej aplikacji:

1. Analiza (rejestrowanie powolnych zapytań, eksploracja systemu, analiza zapytań i projektowanie baz danych)
2. Wymagania dotyczące wydajności (ilu użytkowników)
3. Ograniczenia technologiczne (szybkość sprzętu, niewłaściwe użycie MySQL)

Analizę można przeprowadzić na kilka sposobów. Najpierw przyjrzymy się najbardziej oczywistym sposobom, jak zajrzeć pod maskę twojego MySQL, gdzie wykonywane są zapytania. Pierwszym narzędziem optymalizacyjnym w Twoim arsenale jest EXPLAIN. Jeśli dodasz tę instrukcję przed zapytaniem SELECT, wynikiem zapytania będzie:

Głośniki, widzisz, trzymaj ważna informacja o wniosku. Kolumny, na które należy zwrócić największą uwagę to possible_keys i Extra.

Kolumna possible_keys pokaże indeksy, do których MySQL miał dostęp w celu wykonania zapytania. Czasami trzeba przypisać indeksy, aby zapytanie działało szybciej. Kolumna Extra pokaże, czy użyto dodatkowego WHERE lub ORDER BY. Najważniejszą rzeczą, którą należy zauważyć, jest to, czy w danych wyjściowych znajduje się Using Filesort.

Co robi Using Filesort jest opisane w pomocy MySQL:

MySQL musi wykonać dodatkowe przejście, aby dowiedzieć się, jak zwrócić posortowane wiersze. To sortowanie iteruje po wszystkich wierszach zgodnie z typem sprzężenia i przechowuje klucz sortowania i wskaźnik wiersza dla wszystkich wierszy, które pasują do klauzuli WHERE. Klucze są sortowane, a wiersze zwracane we właściwej kolejności.

Dodatkowa przepustka spowolni twoją aplikację i należy jej unikać za wszelką cenę. Innym krytycznym skutkiem Extra, którego powinniśmy unikać, jest używanie tymczasowego. Mówi, że MySQL musiał stworzyć tymczasową tabelę, aby wykonać zapytanie. Oczywiście jest to straszne użycie MySQL. W takim przypadku wynik zapytania powinien być przechowywany w Redis lub Memcache i nie być ponownie wykonywany przez użytkowników.

Aby uniknąć problemu z Using Filesort, musimy upewnić się, że MySQL używa INDEX. Istnieje teraz wiele kluczy w possible_keys, z których można wybierać, ale MySQL może wybrać tylko jeden indeks dla końcowego zapytania. Ponadto indeksy mogą składać się z wielu kolumn, a ponadto można wprowadzać wskazówki (wskazówki) dla optymalizatora MySQL, wskazujące na utworzone indeksy.

Podpowiedź indeksu

Optymalizator MySQL użyje statystyk opartych na zapytaniach tabel do wyboru najlepszy indeks aby wypełnić żądanie. Działa po prostu w oparciu o wbudowaną logikę statystyczną, więc posiadanie kilku opcji nie zawsze działa. właściwy wybór bez pomocy podpowiedzi. Aby upewnić się, że użyto prawidłowego (lub nieprawidłowego) klucza, użyj w zapytaniu słów kluczowych FORCE INDEX, USE INDEX i IGNORE INDEX. Więcej informacji na temat podpowiedzi do indeksu można znaleźć w pomocy MySQL.

Aby wyświetlić klucze tabeli, użyj polecenia POKAŻ INDEX. Można określić wiele wskazówek, które będą używane przez optymalizator.

Oprócz EXPLAIN istnieje słowo kluczowe DESCRIBE. Dzięki DESCRIBE możesz przeglądać informacje z tabeli w następujący sposób:

Dodawanie indeksu

Aby dodać indeksy do MySQL, użyj składni CREATE INDEX. Istnieje kilka rodzajów indeksów. FULLTEXT Używany do wyszukiwania pełnotekstowego, podczas gdy UNIQUE służy do przechowywania unikalnych danych.

Aby dodać indeks do swojej tabeli, użyj następującej składni:

Mysql> CREATE INDEX idx_bookname ON `books` (bookname(10)); Spowoduje to utworzenie indeksu w tabeli książek, który będzie używał pierwszych 10 liter z kolumny varchar zawierającej tytuły książek. W takim przypadku każde wyszukiwanie WHERE w tytule książki, które pasuje do maksymalnie 10 znaków, da taki sam wynik, jak przeszukanie całej tabeli od góry do dołu.

Indeksy złożone

Indeksy mają duży wpływ na szybkość wykonywania zapytań. Samo przypisanie głównego unikalnego klucza nie wystarczy - klucze złożone są prawdziwym przypadkiem użycia w konfiguracji MySQL, która czasami wymaga sprawdzenia A/B za pomocą EXPLAIN.

Na przykład, jeśli musimy odwołać się do dwóch kolumn w klauzuli WHERE, klucz złożony byłby idealny.

mysql> CREATE INDEX idx_composite NA użytkownikach (nazwa użytkownika, aktywny); Raz utworzyliśmy klucz na podstawie kolumny username, w której przechowywana jest nazwa użytkownika oraz aktywnej kolumny ENUM, która określa, czy jego konto jest aktywne. Wszystko jest teraz zoptymalizowane pod kątem zapytania, które użyje WHERE, aby znaleźć prawidłową nazwę użytkownika z aktywnym kontem (active = 1).

Jak szybki jest Twój MySQL?

Włączmy profilowanie, aby przyjrzeć się bliżej zapytaniom MySQL. Można to zrobić, wydając polecenie set profiling=1, po czym należy uruchomić show profile, aby zobaczyć wynik.

Jeśli używasz PDO, uruchom następujący kod:

$db->query("ustaw profilowanie=1"); $db->query("wybierz nagłówek, treść, tagi z postów"); $rs = $db->query("pokaż profile"); $db->query("ustaw profilowanie=0"); // wyłącz profilowanie po wykonaniu zapytania $records = $rs->fetchAll(PDO::FETCH_ASSOC); // uzyskaj wyniki profilowania $errmsg = $rs->errorInfo(); //Złap tutaj kilka błędów To samo można zrobić z mysqli:

$db = nowy mysqli($host,$nazwa użytkownika,$hasło,$nazwadb); $db->query("ustaw profilowanie=1"); $db->query("wybierz nagłówek, treść, tagi z postów"); if ($result = $db->query("POKAŻ profile", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close(); ) if ($result = $db->query("pokaż profil dla zapytania 1", MYSQLI_USE_RESULT)) ( while ($row = $result->fetch_row()) ( var_dump($row); ) $result->close( ); ) $db->query("ustaw profilowanie=0"); Zwróci to profilowane dane zawierające czas wykonania zapytania w drugim elemencie tablicy asocjacyjnej.

Array(3) ( => string(1) "1" => string(10) "0.00024300" => string(17) "wybierz nagłówek, treść, tagi z postów" ) Wykonanie tego zapytania zajęło 0.00024300 sekund. Jest dość szybki, więc nie martw się. Ale kiedy liczby stają się duże, musimy spojrzeć głębiej. Przejdź do swojej aplikacji, aby przećwiczyć na roboczym przykładzie. Sprawdź stałą DEBUG w konfiguracji bazy danych, a następnie rozpocznij eksplorację systemu, włączając dane wyjściowe profilowania za pomocą funkcji var_dump lub print_r. W ten sposób możesz przechodzić od strony do strony w swojej aplikacji, uzyskując wygodne profilowanie systemu.

Pełny audyt bazy danych Twojej witryny

Aby przeprowadzić pełny audyt swoich żądań, włącz logowanie. Niektórzy twórcy witryn obawiają się, że rejestrowanie ma duży wpływ na wykonywanie i dodatkowo spowalnia wykonywanie zapytań. Praktyka pokazuje jednak, że różnica jest nieznaczna.

Aby włączyć logowanie w MySQL 5.1.6, użyj zmiennej globalnej log_slow_queries lub możesz oznaczyć plik do logowania za pomocą zmiennej slow_query_log_file. Można to zrobić, uruchamiając następujące zapytanie:

Ustaw globalne log_slow_queries = 1; ustaw globalny plik_logu_slow_query_log = /dev/slow_query.log; Można go również określić w plikach konfiguracyjnych /etc/my.cnf lub my.ini na serwerze.

Po dokonaniu zmian nie zapomnij zrestartować serwera MySQL za pomocą niezbędnej komendy, na przykład service mysql restart jeśli używasz Linuksa.

W wersjach MySQL po 5.6.1 zmienna log_slow_queries jest przestarzała i zamiast niej jest używana slow_query_log. Możesz również włączyć dane wyjściowe tabeli w celu łatwiejszego debugowania, ustawiając zmienną log_output na TABLE, jednak ta funkcja jest dostępna tylko w MySQL 5.6.1.

Log_output = TABELA; log_queries_not_using_indexes = 1; long_query_time = 1; Zmienna long_query_time określa liczbę sekund, po których zapytanie jest uważane za wolne. Wartość to 10, a minimalna to 0. Możesz także określić milisekundy, używając ułamka; teraz wskazałem jedną sekundę. A teraz każde żądanie, które będzie działać dłużej niż 1 sekundę, jest rejestrowane w tabeli.

Logowanie zostanie wykonane w tabelach mysql.slow_log i mysql.general_log Twojej Bazy danych MySQL dane. Aby wyłączyć rejestrowanie, zmień log_output na NONE.

Logowanie na serwerze produkcyjnym

Na serwerze produkcyjnym obsługującym klientów lepiej jest używać logowania tylko przez krótki czas i monitorować obciążenie, aby nie tworzyć niepotrzebnego obciążenia. Jeśli Twoja usługa jest przeciążona i wymagane jest pilne działanie, spróbuj wyizolować problem za pomocą polecenia SHOW PROCESSLIST lub uzyskaj dostęp do tabeli information_schema.PROCESSLIST za pomocą SELECT * FROM information_schema.PROCESSLIST;.

Rejestrowanie wszystkich żądań na serwerze produkcyjnym może dostarczyć wiele informacji i jest dobrym narzędziem do celów badawczych podczas sprawdzania projektu, ale logi z dłuższych okresów nie dają wielu przydatnych informacji w porównaniu z logi do 48 godzin (spróbuj śledzić szczytowe obciążenia, aby mieć szansę na lepsze zbadanie wykonania zapytania).

Uwaga: jeśli masz witrynę, w której występują fale ruchu i czasami prawie nie ma ruchu, na przykład witrynę sportową poza sezonem, skorzystaj z tych informacji, aby utworzyć i przestudiować rejestrowanie.

Rejestrowanie wielu żądań

Ważne jest, aby być świadomym nie tylko zapytań, które działają dłużej niż sekundę, ale także zapytań, które są uruchamiane setki razy. Nawet jeśli żądania są wykonywane szybko, w załadowanym systemie mogą ściągnąć wszystkie zasoby do siebie.

Dlatego zawsze powinieneś mieć się na baczności po wprowadzeniu zmian do działającego projektu — jest to najbardziej krytyczny czas dla działania dowolnej bazy danych.

Gorąca i zimna pamięć podręczna

Liczba żądań i obciążenie serwera ma duży wpływ na wykonanie, a także może wpływać na czas wykonania żądań. Podczas programowania powinieneś przyjąć zasadę, że każde żądanie nie powinno trwać dłużej niż ułamek milisekundy (0.0xx lub szybciej) na wolnym serwerze.

Korzystanie z Memcache ma silny wpływ na obciążenie serwerów, uwalniając zasoby, które obsługują żądania. Upewnij się, że efektywnie korzystasz z Memcached i przetestuj swoją aplikację za pomocą gorącej pamięci podręcznej (pobrane dane) i zimnej pamięci podręcznej.

Aby uniknąć uruchamiania na serwerze produkcyjnym z pustą pamięcią podręczną, dobrym pomysłem jest posiadanie skryptu, który zbiera całą niezbędną pamięć podręczną przed uruchomieniem serwera, aby duży napływ klientów nie spowalniał uruchamiania systemu.

Naprawianie powolnych zapytań

Teraz, gdy rejestrowanie jest skonfigurowane, możesz napotkać powolne żądania w swojej witrynie. Naprawmy je! Na przykład pokażę kilka typowych problemów, możesz zobaczyć logikę ich naprawiania.

Jeśli nie znalazłeś jeszcze wolnego zapytania, sprawdź ustawienia long_query_time, jeśli używasz tej metody rejestrowania. W przeciwnym razie, po sprawdzeniu wszystkich żądań profilowania (ustaw profilowanie=1), zrób listę żądań, które zajmują więcej niż ułamek milisekundy (0,000x sekundy) i zacznij od tego miejsca.

Częste problemy

Oto sześć najczęstszych problemów, które napotkałem podczas optymalizacji zapytań MySQL:

ORDER BY i sortowanie plików

Zapobieganie sortowaniu plików jest czasami niemożliwe ze względu na klauzulę ORDER BY. Aby zoptymalizować, przechowaj wynik w Memcache lub posortuj w logice aplikacji.

Używając ORDER BY z WHERE i LEFT JOIN

ORDER BY bardzo spowalnia wykonywanie zapytań. Jeśli to możliwe, staraj się nie używać ORDER BY. Jeśli potrzebujesz sortowania, użyj sortowania według indeksów.

Stosowanie ORDER BY na tymczasowych kolumnach

Po prostu tego nie rób. Jeśli chcesz połączyć wyniki, zrób to w logice aplikacji; nie używaj filtrowania ani sortowania w tabeli tymczasowej Zapytanie MySQL. To wymaga dużo zasobów.

Ignorowanie indeksu PEŁNEGO TEKSTU

Korzystanie z LIKE jest najbardziej Najlepszym sposobem spowalnia wyszukiwanie pełnotekstowe.

Bezpodstawny wybór duża liczba linie

Zapominanie o LIMIT w zapytaniu może znacznie wydłużyć czas pobierania z bazy danych, w zależności od rozmiaru tabel.

Nadmierne użycie JOIN zamiast tworzenia złożonych tabel lub widoków

Jeśli używasz więcej niż trzech lub czterech instrukcji LEFT JOIN w jednym zapytaniu, zadaj sobie pytanie: czy wszystko tutaj jest poprawne? Kontynuuj, jeśli masz ku temu dobry powód, na przykład jeśli zapytanie jest rzadko używane do wyświetlania w panelu administracyjnym lub wynik może zostać zapisany w pamięci podręcznej. Jeśli musisz uruchomić zapytanie z dużą liczbą sprzężeń tabel, lepiej pomyśleć o tworzeniu tabel złożonych z wymaganych kolumn lub przy użyciu widoków.

więc

Omówiliśmy podstawy optymalizacji i narzędzia potrzebne do wykonania zadania. Zbadaliśmy system za pomocą profilowania i instrukcji EXPLAIN, aby zobaczyć, co dzieje się z bazą danych i zobaczyć, jak można ulepszyć strukturę.

Przyjrzeliśmy się również kilku przykładom i klasycznym pułapkom, na które można wpaść przy użyciu MySQL. Korzystając z podpowiedzi indeksów, możemy zapewnić, że MySQL wybierze odpowiednie indeksy, zwłaszcza gdy istnieje wiele operacji pobierania na tej samej tabeli. Aby kontynuować zgłębianie tematu, radzę spojrzeć w stronę projektu Percona.

MySQL jest nadal najpopularniejszą relacyjną bazą danych na świecie, ale także najbardziej niedostatecznie zoptymalizowaną. Wiele osób pozostaje przy domyślnych ustawieniach bez „kopania” głębiej. W tym artykule przyjrzymy się kilku poradom optymalizacyjnym MySQL, w połączeniu z niektórymi z ostatnich wydań.

Optymalizacja konfiguracji

Pierwszą rzeczą, jaką każdy użytkownik MySQL powinien zrobić, aby poprawić wydajność, jest dostosowanie konfiguracji. Jednak większość ludzi pomija ten krok. W wersji 5.7 (aktualnej) ustawienia domyślne są znacznie lepsze niż w poprzednich wersjach, ale nadal można je poprawić i łatwo je ulepszyć.

Mamy nadzieję, że używasz Linuksa lub czegoś takiego jak Vagrant -box (jak nasz Homestead Improved), więc twój plik konfiguracyjny będzie zlokalizowany w /etc/mysql/my.cnf . Możliwe, że Twoja instalacja faktycznie załaduje do tego dodatkowy plik konfiguracyjny. Więc spójrz, jeśli plik my.cnf zawiera jakieś, to zajrzyj do /etc/mysql/mysql.conf.d/mysqld.cnf .

Strojenie ręczne

Poniższe ustawienia należy wykonać po wyjęciu z pudełka. Zgodnie z tymi wskazówkami dodaj do pliku konfiguracyjnego w sekcji:

Innodb_buffer_pool_size = 1G # (zmień tutaj około 50%-70% całkowitej pamięci RAM) innodb_log_file_size = 256M innodb_flush_log_at_trx_commit = 1 # można zmienić na 2 lub 0 innodb_flush_method = O_DIRECT

• innodb_buffer_pool_size . Pula buforów jest rodzajem „magazynu” do buforowania danych i indeksów w pamięci. Służy do przechowywania często używanych danych w pamięci. A kiedy korzystasz z serwera dedykowanego lub wirtualnego, na którym baza danych jest często wąskim gardłem, warto poświęcić mu większość pamięci RAM. Dlatego dajemy mu 50-70% całej pamięci RAM. Dokumentacja MySQL zawiera przewodnik po konfiguracji tej puli.
• innodb_log_file_size . Ustawienie rozmiaru pliku dziennika jest dobrze opisane, ale w skrócie jest to ilość danych przechowywanych w dziennikach przed ich wyczyszczeniem. Zwróć uwagę, że w tym przypadku dziennik nie zawiera rekordów błędów, ale pewną migawkę delta zmian, które nie zostały jeszcze opróżnione na dysk w głównych plikach innodb. MySQL pisze w tle, ale nadal wpływa to na wydajność w momencie zapisu. Duży plik dziennika oznacza więcej wysoka wydajność ze względu na małą liczbę nowych i małych utworzonych punktów kontrolnych, ale z dłuższym czasem odzyskiwania w przypadku awarii (więcej danych należy przepisać do bazy danych).
• innodb_flush_log_at_trx_commit jest opisany i pokazuje, co dzieje się z plikiem dziennika. Najbezpieczniejsza jest wartość 1, ponieważ dziennik jest opróżniany na dysk po każdej transakcji. Przy wartościach 0 i 2 - mniej gwarantowanego KWASU, ale większa wydajność. Różnica nie jest wystarczająco duża, aby przewyższyć korzyści ze stabilności wynikające z 1.
• innodb_flush_method . Na domiar złego to ustawienie powinno być ustawione na O_DIRECT, aby uniknąć podwójnego buforowania. Radzę to robić zawsze, o ile system I/O pozostaje bardzo powolny. Chociaż większość hostów, takich jak DigitalOcean, będzie mieć dyski SSD, więc system I/O będzie bardziej produktywny.

Istnieje narzędzie firmy Percona, które pomoże nam automatycznie znaleźć pozostałe problemy. Zauważ, że gdybyśmy mieli go uruchomić bez tego ustawienie ręczne, wówczas zdefiniowane zostanie tylko 1 z 4 ustawień, ponieważ pozostałe 3 zależą od preferencji użytkownika i środowiska aplikacji.

Inspektor zmiennych

Instalowanie inspektora zmiennych na Ubuntu:

Wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all. deb sudo apt-get aktualizacja sudo apt-get install percona-toolkit

W przypadku innych systemów postępuj zgodnie z tymi instrukcjami.

Następnie uruchom zestaw narzędzi:

pt-variable-advisor h=localhost,u=homestead,p=sekret

Zobaczysz ten wynik:

# WARN delay_key_write: Bloki indeksu MyISAM nigdy nie są opróżniane, dopóki nie jest to konieczne. # UWAGA max_binlog_size: max_binlog_size jest mniejszy niż domyślna wartość 1 GB. # UWAGA sort_buffer_size-1: Zmienna sort_buffer_size powinna generalnie pozostawać domyślna, chyba że ekspert uzna, że ​​należy ją zmienić. # UWAGA innodb_data_file_path: Automatyczne rozszerzanie plików InnoDB może zajmować dużo miejsca na dysku, które jest bardzo trudne do późniejszego odzyskania. # WARN log_bin: Logowanie binarne jest wyłączone, więc odzyskiwanie do określonego momentu i replikacja nie są możliwe.

Notatka. tłumacz:
Na moim lokalnym komputerze, oprócz tego, wydałem również następujące ostrzeżenie:

# UWAGA innodb_flush_method: Większość produkcyjnych serwerów baz danych korzystających z InnoDB powinna ustawić innodb_flush_method na O_DIRECT, aby uniknąć podwójnego buforowania, chyba że system we/wy ma bardzo niską wydajność.

Fakt, że parametr innodb_flush_method musi być ustawiony na O_DIRECT i dlaczego został omówiony powyżej. A jeśli postępowałeś zgodnie z sekwencją strojenia, jak w artykule, nie zobaczysz tego ostrzeżenia.

Żaden z tych ( ok. przeł.: wskazany przez autora) ostrzeżenia nie są krytyczne, nie trzeba ich poprawiać. Jedyną rzeczą, którą można poprawić, jest skonfigurowanie dziennika binarnego dla replikacji i migawek.

Uwaga: w nowych wersjach domyślny rozmiar binlogu to 1G i to ostrzeżenie nie będzie dostępne.

max_binlog_size = 1G log_bin = /var/log/mysql/mysql-bin.log server-id=master-01 binlog-format = "ROW"

• max_binlog_size . Określa, jak duże będą logi binarne. Rejestrują twoje transakcje i prośby oraz tworzą punkty kontrolne. Jeśli transakcja przekroczy maksimum, dziennik może przekroczyć swój rozmiar podczas zapisywania na dysku; w przeciwnym razie MySQL będzie obsługiwał go w tym limicie.
• log_bin . Ta opcja ogólnie umożliwia logowanie binarne. Bez niej migawki lub replikacje są niemożliwe. Pamiętaj, że może to mieć ogromny wpływ na ilość miejsca na dysku. server-id jest opcją wymaganą, gdy włączone jest logowanie binarne, więc dzienniki "wiedzą", z którego serwera pochodzą (w celu replikacji), a format binlog jest po prostu taki, jak są napisane.

Jak widać, nowy MySQL ma domyślne wartości, które są już prawie gotowe do produkcji. Oczywiście każda aplikacja jest inna i ma dodatkowe sztuczki i poprawki.

Tuner MySQL

Narzędzia pomocnicze: Percona Toolkit do identyfikacji duplikatów indeksów

Zainstalowany wcześniej zestaw Percona Toolkit zawiera również narzędzie do wykrywania duplikatów indeksów, które może się przydać podczas korzystania z CMS innych firm lub po prostu do sprawdzenia, czy przypadkowo dodałeś więcej indeksów, niż potrzebujesz. Na przykład, Instalacja WordPress domyślnie ma zduplikowane indeksy w tabeli wp_posts:

Kontroler duplikatów-kluczy Pt h=localhost,u=homestead,p=sekret # ################################ # ######################################## # homestead.wp_posts # #### # ################################################## ################## # Klucz type_status_date kończy się prefiksem indeksu klastrowego # Definicje kluczy: # KLUCZ `type_status_date` (`post_type`,`post_status`,`post_date` ,`ID`), # PRIMARY KEY (`ID`), # Typy kolumn: # `post_type` varchar(20) zestawiaj utf8mb4_unicode_520_ci nie null domyślne "post" # `post_status` varchar(20) zestawiaj utf8mb4_unicode_520_ci nie null domyślne "publikuj" " # `post_date` datetime not null default "0000-00-00 00:00:00" # `id` bigint(20) unsigned not null auto_increment # Aby skrócić ten zduplikowany indeks klastrowy, wykonaj: ALTER TABLE `homestead`.` wp_posts` DROP INDEX `type_status_date`, DODAJ INDEKS `type_status_date` (`post_type`,`post_status`,`post_date`);

Jak widać z ostatniego wiersza, to narzędzie daje również wskazówki, jak pozbyć się zduplikowanych indeksów.

Narzędzia pomocnicze: Percona Toolkit dla nieużywanych indeksów

Percona Toolkit może również wykrywać nieużywane indeksy. Jeśli rejestrujesz powolne zapytania (zobacz sekcję „Wąskie gardła” poniżej), możesz uruchomić narzędzie, które sprawdzi, czy te zapytania używają indeksów w tabelach iw jaki sposób.

pt-index-usage /var/log/mysql/mysql-slow.log

Aby uzyskać szczegółowe informacje na temat korzystania z tego narzędzia, zobacz .

Wąskie miejsca

W tej sekcji opisano, jak wykrywać i monitorować wąskie gardła bazy danych.

Najpierw włączmy wolne rejestrowanie zapytań:

Slow_query_log = /var/log/mysql/mysql-slow.log long_query_time = 1 log-queries-not-using-indexes = 1

Powyższe wiersze należy dodać do konfiguracji mysql. Baza danych będzie śledzić zapytania, które trwają dłużej niż 1 sekundę oraz te, które nie używają indeksów.

Gdy w tym dzienniku pojawią się jakieś dane, możesz je przeanalizować pod kątem użycia indeksu za pomocą powyższego narzędzia pt-index-usage lub za pomocą pt-query-digest , które wygeneruje coś takiego:

Pt-query-digest /var/log/mysql/mysql-slow.log # 360ms czasu użytkownika, 20ms czasu systemowego, 24,66 mln rss, 92,02 mln vsz # Aktualna data: Czw 13 lutego 22:39:29 2014 # Nazwa hosta: * # Pliki: mysql-slow.log # Łącznie: 8 łącznie, 6 unikalnych, 1,14 QPS, 0,00x współbieżność ________________ # Zakres czasu: 2014-02-13 22:23:52 do 22:23:59 # Atrybut łącznie min max śr 95% mediana odchylenia standardowego # ============ ======= ======= ======= ======= ===== == ======= ======= # Czas wykonania 3ms 267us 406us 343us 403us 39us 348us # Czas blokady 827us 88us 125us 103us 119us 12us 98us # Wysłane wiersze 36 1 15 4,50 14,52 4,18 3,897 # Sprawdzanie wierszy 4 30 10,88 28,75 7,37 7,70 # Rozmiar zapytania 2,15 tys 153 296 245,11 284,79 48,90 258.32 # ==== ============================ = == ===== ====== ===== ================ # Profil # Ranga Identyfikator zapytania Czas odpowiedzi Połączenia R/Call V/M Element # ==== ================================= == =============== # 1 0x728E539F7617C14D 0,0011 41,0% 3 0,0004 0,00 WYBIERZ artykuł_blog # 2 0x1290EEE0B201F3FF 0,0003 12,8% 1 0,0003 0,00 S ELECT portfolio_item # 3 0x31DE4535BDBFA465 0,0003 12,6% 1 0,0003 0,00 SELECT portfolio_item # 4 0xF14E15D0F47A5742 0,0003 12,1% 1 0,0003 0,00 SELECT portfolio_category # 5 0x8F848005A09C9588 0,0003 11,8% SELECT blog 0,0003_kategoria 10.002= =========================================================== ============= # Zapytanie 1: 0 QPS, 0x współbieżność, ID 0x728E539F7617C14D w bajcie 736 ______ # Wyniki: V/M = 0,00 # Zakres czasowy: wszystkie zdarzenia wystąpiły 13.02.2014 22:23:52 # Udział procentowy atrybutów łącznie min. maks. śr. 95% mediana odchylenia standardowego # =========== === ======= ======= ==== = == ======= ======= ======= ======= # Liczba 37 3 # Czas wykonania 40 1ms 352us 406us 375us 403us 22us 366us # Czas blokady 42 351us 103us 125US 117US 119US 9US 119US # Wiersze wysłane 25 9 1 4 3 3,89 1,37 3,89 # Wiersze Zbadaj 24 21 5 8 7 7,70 1,29 7,70 # Rozmiar zapytania 47 1,02k 261 262 261,25 258,32 0 258.32 # Ciąg: # Hosty Localhost # użytkownicy * # Dystrybucja Query_time # 1us # 10us # 10 0 nas ################################################ # ############## # 1ms # 10ms # 100ms # 1s # 10s+ # Tabele # POKAŻ STATUS TABELI JAK "blog_article"\G # SHOW CREATE TABLE `blog_article`\G # WYJAŚNIJ /* ! 50100 PARTITIONS*/ SELECT b0_.id AS id0, b0_.slug AS slug1, b0_.title AS title2, b0_.excerpt AS excerpt3, b0_.external_link AS external_link4, b0_.description AS description5, b0_.created AS created6, b0_. updated Zaktualizowano7 OD blog_artykuł b0_ ZAMÓW PRZEZ b0_.utworzono LIMIT DESC 10

Jeśli wolisz analizować te dzienniki ręcznie, możesz zrobić to samo, ale najpierw musisz wyeksportować dziennik do bardziej analizowalnego formatu. Można to zrobić w ten sposób:

mysqldumpslow /var/log/mysql/mysql-slow.log

OD Dodatkowe opcje możesz filtrować dane, aby wyeksportować tylko to, czego potrzebujesz. Na przykład 10 najpopularniejszych zapytań posortowanych według średniego czasu wykonania:

mysqldumpslow -t 10 -s w /var/log/mysql/localhost-slow.log

Wniosek

W tym obszernym poście dotyczącym optymalizacji MySQL omówiliśmy różne metody i technik, dzięki którym możemy sprawić, że nasz MySQL będzie latał.

Opracowaliśmy optymalizację konfiguracji, przekopaliśmy się przez indeksy i pozbyliśmy się niektórych wąskich gardeł. Wszystko to było w większości teorią, jednak wszystko to odnosi się do rzeczywistych zastosowań.

Stopień obciążenia serwera, a co za tym idzie szybkość ładowania strony, w dużej mierze zależy od tego, jak dobrze zoptymalizowane są zapytania do bazy mySQL. Optymalizacja zapytań mySQL pomoże odciążyć serwer i skrócić czas ładowania Twojej witryny.

Po co optymalizować zapytania do bazy danych

Właściciele witryn zarządzanych przez samodzielnie napisane systemy administracyjne muszą po prostu dobrze zrozumieć, które zapytania do bazy danych są wykonywane szybko i łatwo, a które znacznie zwiększają obciążenie serwera i znacznie spowalniają ładowanie witryny.

Nie zaszkodzi to tym webmasterom, którzy korzystają ze znanych systemów administracyjnych i lubią podłączać wszelkiego rodzaju wtyczki firm trzecich, a także dostosowywać motywy dla siebie, na przykład w najpopularniejszym darmowym CMS - WordPressie, żeby trafić do sedna sprawy.

Niektóre czynności można wykonać różne sposoby, na przykład możesz policzyć liczbę rekordów znalezionych w tabeli za pomocą funkcji mysql_num_rows (ale nie jest to zalecane) lub możesz użyć konstrukcji SELECT COUNT (). Osobiście przeprowadziliśmy badanie, w którym stworzyliśmy ogromną tabelę danych zawierającą kilkaset tysięcy rekordów i ważącą więcej niż jeden gigabajt, a następnie próbowaliśmy policzyć liczbę wierszy we wskazany sposób.

Wynik był widoczny gołym okiem, ponieważ w przypadku użycia mysql_num_rows strona zawieszała się na 5 sekund, po czym wyświetlał się wynik. W drugim przypadku wynik w postaci liczby rekordów w tabeli otrzymaliśmy niemal natychmiast. Nie musieliśmy nawet mierzyć czasu ładowania skryptu za pomocą mikrotimera, ponieważ wynik był więcej niż oczywisty.

To samo dotyczy innych projektów. Niektóre operacje na bazach danych można wykonać na dwa, trzy, cztery lub więcej sposobów, a każda z nich będzie się dokładnie różnić szybkością, a wynik we wszystkich przypadkach będzie jednakowo prawdziwy.

Jak zoptymalizować zapytania do bazy danych

Aby dokładnie zrozumieć, jak optymalizować zapytania i które konstrukcje są szybsze, a które wolniejsze, ponownie przeprowadzimy mały eksperyment i zrobimy to już teraz.

Po pomoc będziemy musieli zwrócić się do interfejsu popularnego i bardzo wygodnego phpmyadmina. Aby rozpocząć, musimy wybrać jedną z dostępnych baz danych i stworzyć w niej tabelę testową. Jego nazwa w naszym przypadku będzie raczej banalna - test.

CREATE TABLE `test` (`ID` INT NOT NULL AUTO_INCREMENT , `TITLE` VARCHAR(100) ZESTAW ZNAKÓW utf8 UKŁADANIE utf8_unicode_ci NOT NULL , `OGŁOSZENIE` ZESTAW ZNAKÓW TEKSTOWYCH utf8 UKŁADANIE utf8_unicode_ciT CHARAKTER NOT NULL NULL , KLUCZ PODSTAWOWY (`ID`)) SILNIK = MYISAM ;

Teraz to stół testowy już mamy, musimy wypełnić go abstrakcyjnymi danymi. Jak widać ze struktury właśnie utworzonej tabeli, do wypełnienia potrzebujemy następujących danych:

• nagłówek
• Zapowiedź
• Pełny tekst

Po teksty abstrakcyjne, z przyzwyczajenia, przejdziemy do stworzonego właśnie w tym celu serwisu Yandex.Abstracts. Udało nam się natknąć na temat „Torsion Photon w XXI wieku” i weźmy go.

Jako tytuł wskazaliśmy losowo wybrany temat, jako zapowiedź wzięliśmy jeden przeciętny akapit tekstu, a jako pełny tekst artykułu będziemy mieli tekst o długości 4000 znaków ze spacjami. Do policzenia ilości znaków w tekście skorzystaliśmy z własnej usługi i zalecamy w niej liczyć, ponieważ. istnieje możliwość włączenia spacji lub nie.

Nie skopiujemy tutaj wynikowego żądania, ponieważ będzie to ponad 4000 znaków nieunikatowego tekstu zaczerpniętego z samego Yandex, co jest dość pogrubione, a Ty też go nie potrzebujesz. Zamiast tego napiszemy prostą pętlę PHP, która szybko doda do bazy danych tyle rekordów, ile tylko zechcemy. Na początek będzie to 100 000 artykułów.

Im mniej zapytań do bazy danych, tym lepiej

Już na tym etapie pokażemy Ci częsty błąd, który my sami teraz popełnimy.

Dla($i=1;$i<100000;$i++) { mysql_query("INSERT INTO `test` (`ID`, `TITLE`, `ANNOUNCEMENT`, `TEXT`) VALUES (NULL, "Заголовок", "Анонс", "Полный текст")"); }

Na żądanie wkleiliśmy kod skopiowany z phpmyadmina, który wyświetlał się na ekranie po jednorazowym ręcznym dodaniu pierwszego artykułu. Od razu zaznaczam, że nie należy w ten sposób budować zapytań do bazy danych. Zrobiliśmy to tylko dlatego, że po prostu musieliśmy szybko wypełnić tabelę losowymi danymi, a to zapytanie jest pisane szybciej niż to, które jest bardziej optymalne. W tej pętli otrzymaliśmy 99999 oddzielnych wywołań bazy danych (pierwsze wykonaliśmy ręcznie z phpmyadmina), co jest bardzo złą formą.

O wiele bardziej poprawnym rozwiązaniem byłoby wykonanie tej samej operacji przy użyciu tylko jednego wywołania bazy danych, wymieniając wszystkie wiersze oddzielone przecinkami.

WSTAWIĆ WARTOŚCI „test” („ID”, „TYTUŁ”, „OGŁOSZENIE”, „TEKST”) (NULL, „Tytuł”, „Ogłoszenie”, „Pełny tekst”), (NULL, „Tytuł”, „Ogłoszenie” , „Pełny tekst”), (NULL, „Tytuł”, „Ogłoszenie”, „Pełny tekst”), …

Jeśli wrócimy do naszej pierwszej metody, wyglądałoby to tak:

INSERT INTO „test” („ID”, „TYTUŁ”, „OGŁOSZENIE”, „TEKST”) WARTOŚCI (NULL, „Tytuł”, „Ogłoszenie”, „Pełny tekst”) INSERT INTO „test” (`ID`, ` TYTUŁ”, „OGŁOSZENIE”, „TEKST”) WARTOŚCI (NULL, „Tytuł”, „Ogłoszenie”, „Pełny tekst”) WSTAWIĆ W „TEST” („ID”, „TYTUŁ”, „OGŁOSZENIE”, „TEKST”) WARTOŚCI (NULL, "Tytuł", "Ogłoszenie", "Pełny tekst") ...

Poczuj różnicę? Optymalna jest opcja, która korzysta tylko z jednego dostępu do bazy danych. Szybkość tych dwóch metod, prowadząca do tego samego wyniku, znacznie się różni i jest widoczna gołym okiem bez żadnych pomiarów, uwierz mi, to prawda.

Wybierz tylko pola wymagane przez skrypt

Tutaj wszystko jest bardzo proste - ta lub inna funkcja potrzebuje określonych danych z tabeli docelowej. Bardzo często okazuje się, że w ogóle trzeba wyciągnąć wszystkie pola, zwłaszcza jeśli stół jest dość duży i jest ich więcej niż 10.

WYBIERZ * Z `test`

W tym zapytaniu gwiazdka oznacza, że ​​dane zostaną pobrane ze wszystkich pól tabeli testowej. Ale co, jeśli w tabeli jest 20-30 lub więcej tych pól? Skrypt najprawdopodobniej potrzebuje tylko niektórych z nich, a cała reszta, która nie zostanie w żaden sposób wykorzystana, zostanie wybrana na próżno. Taka operacja będzie wolniejsza, niż gdybyś określił tylko te pola, których w danej chwili naprawdę potrzebujesz, oddzielone przecinkami.

WYBIERZ „ID”, „TYTUŁ” Z „TEST”

W tym przykładzie w ogóle nie dotkniemy ogłoszenia i pełnego tekstu artykułu, co znacznie przyspieszy działanie skryptu. Wnioskujemy zatem, że optymalizacja zapytań bazodanowych oznacza również konkretne wskazanie wymaganych pól w zapytaniach oraz odrzucenie uniwersalności w postaci gwiazdki.

Łączenie wielu zapytań w jedno

Mówiliśmy już z Państwem, że dobra optymalizacja pracy z mySQL polega na wykorzystaniu minimalnej możliwej liczby zapytań i podaliśmy przykład z dodawaniem danych do tabeli.

Oprócz dodawania, ta technika może i powinna być używana podczas próbkowania danych. Weźmy teraz najprostszy przykład. Wyobraź sobie, że w swojej bazie danych masz dwie tabele - pierwsza tabela zawiera informacje o zarejestrowanych użytkownikach, a druga zawiera artykuły napisane przez tych użytkowników.

Powiedzmy, że musisz wyświetlić na ekranie jakiś losowy artykuł i podpisać go nazwiskiem autora na dole. Połączenie między tabelami w tym przypadku jest oczywiste i następuje poprzez ID użytkownika, tj. ID użytkownika w tabeli users musi zgadzać się z polem USER_ID w tabeli posts. To połączenie jest standardowe i powinno być jasne dla wszystkich bez wyjątku.

Tak więc, aby wybrać losowy artykuł, piszesz zapytanie w ten sposób:

$rs_post = mysql_query("SELECT `ID`, `USER_ID`, `TITLE`, `TEXT` FROM `posts` ORDER by RAND() LIMIT 1");

Jeden artykuł zostanie losowo wybrany z tabeli postów. Następnie nasze działania będą wyglądać mniej więcej tak:

$row_post = mysql_fetch_assoc($rs_post); $ID_użytkownika = $row_post["ID_UŻYTKOWNIKA"];

Teraz zmienna $userID zawiera identyfikator użytkownika, który jest autorem tego artykułu i aby uzyskać jego dane, na przykład IMIĘ (imię) i NAZWISKO (nazwisko), uzyskasz dostęp do tabeli users i zapytanie wyglądać mniej więcej tak:

$rs_user = mysql_query("SELECT `NAME`, `NAZWISKO` FROM `users` WHERE `ID` = "".$row_post["USER_ID"]."" LIMIT 1");

Nawiasem mówiąc, nie zapomnij o ramkach zmiennych w żądaniach pojedynczymi cudzysłowami, zwłaszcza gdy dane pochodzą z zewnątrz, za pomocą GET lub POST. Stworzy to dodatkową barierę dla atakujących i jest jednym ze środków mających na celu ochronę przed wstrzyknięciem SQL. Wróćmy więc do naszego przykładu. Po złożeniu zapytania do bazy wszystko jest proste - pobieramy imię i nazwisko i wyświetlamy je jako podpis do artykułu. Misja zakończona.

Ale te dwa zapytania można zoptymalizować, zamieniając je w jedno. W tym celu użyjemy konstrukcji LEFT JOIN:

SELECT `posty`.`ID`, `posts`.`ID_USER_ID`, `posts`.`TITLE`, `posts`.`TEXT`, `users`.`NAME`, `users`.`NAZWISKO` FROM ` posty` LEWY DOŁĄCZ `users` ON ​​`posts`.`USER_ID` = `users`.`ID` ORDER by RAND() LIMIT 1

Jak widać, w powyższej konstrukcji nie ma nic skomplikowanego i wszystko jest intuicyjne. Jedyne, na co chciałbym zwrócić uwagę, to wyraźne wskazanie tabel sparowanych z polami, ponieważ istnieje możliwość wyboru z kilku tabel jednocześnie. Jeśli nazwy niektórych pól są takie same, należy użyć tzw. aliasów mySQL, aby później nie pomylić się przy wyświetlaniu wyniku.

Wniosek

Jak widać, zapytania do bazy danych mogą i powinny być zoptymalizowane. Jeśli uważasz, że skoro wszystko działa tak szybko dla Ciebie, to nie ma sensu nic zmieniać, poczekaj, aż baza danych Twojej witryny powiększy się kilkakrotnie, a wraz z nią wzrośnie ruch. Większy ruch oznacza częstszy jednoczesny dostęp do bazy danych, której wielkość wpływa również na szybkość operacji.

Słaba optymalizacja zapytań może pojawić się nieco później, gdy witryna wystarczająco się rozrośnie, az czasem wprowadzanie zmian będzie coraz trudniejsze, ponieważ rozmiary plików i liczba funkcji tylko rosną. Do serwisu dodawane są nowe funkcje mające na celu wygodę użytkowników. Innymi słowy, jeśli wszystko osiągnie pewien punkt wrzenia, może nie być końca, a optymalizacja wszystkich wywołań baz danych rozproszonych w setkach plików zajmie kilka dni, a może tygodni. Dlatego lepiej od razu spróbować dobrze sobie radzić, aby w przyszłości nie stwarzać sobie niepotrzebnych problemów.