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 zapisuje do tło, ale nadal wpływa na wydajność w momencie pisania. Duży plik dziennika oznacza lepszą wydajność ze względu na mniej nowych i mniejszych tworzonych punktów kontrolnych, ale dłuższy czas przywracania w przypadku awarii (trzeba zapisać więcej danych w bazie 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:
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 domyślna instalacja WordPressa 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 # Całkowity procent atrybutów 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
Z opcje dodatkowe 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ń.
- Wysłane przez Nikołaja Korotkowa
- Data: 8 grudnia 2012 o 14:04
Po co to wszystko? Na co to wpływa? Jak przełożyć na rzeczywistość? Na wszystkie te pytania postaram się odpowiedzieć w tym poście!
A teraz trochę tła. Ogólnie otrzymałem ostatnio list do mojego adres e-mail, o następującej treści:
W ciągu ostatnich 3 dni średnie obciążenie wygenerowane przez Twoje konto ******* , wymyślony 119% od akceptowalnego poziomu twojego plan taryfowy. Zalecamy przejście na taryfy VPS. Informujemy, że w przypadku regularnego przekraczania limitów zastrzegamy sobie prawo do zablokowania Twojego konta zgodnie z klauzulą Umowy...
Oboje płynęli - pomyślałem w tym momencie! Zgadzam się, otrzymywanie takich listów nie jest zbyt przyjemne. A skoro po raz pierwszy spotkałem się z takim problemem, czy możesz sobie wyobrazić, jak bardzo byłem zakłopotany? Moje oburzenie nie miało granic! Jaki problem z VPS? Mogę tylko powiedzieć, że ustaliłem na jednej taryfie, ale tutaj proponują mi przejście na hosting współdzielony, który jest trzykrotnie droższy. Nie, chłopaki, myślę, że jest jeszcze wcześnie.
Piszę list zwrotny do mojego hostera, prosząc go o wyjaśnienie, dlaczego mój ładunek przechodzi przez dach? W końcu mój blog ma niewiele ponad dwa miesiące. A frekwencja nie jest świetna. Generalnie piszę, że kategorycznie jestem przeciw przejściu na VPS, uważam, że nie jest to wskazane na tak wczesnym etapie rozwoju zasobów i proszę o wskazanie moich błędów, co z nimi zrobić i jak je kontrolować w przyszłości!
W odpowiedzi otrzymuję:
Drogi subskrybencie, nie odłączymy Cię teraz, to banalne ostrzeżenie, ale mówią, że trzeba coś z tym zrobić. Problem przekroczenia obciążenia nie zależy bezpośrednio od frekwencji, ale w większym stopniu zależy od nieprawidłowej optymalizacji Twojego zasobu. Aby śledzić ładunek, w panelu sterowania wyświetlaliśmy licznik, który jest aktualizowany co 10 minut:
No cóż, dzięki za wyjaśnienie, myślę sobie. Zamierzam zbadać problem. Po wpisaniu w Internecie zapytania „jak zmniejszyć obciążenie hostingu” zdałem sobie sprawę, że nie jestem jedyny, ale w rzeczywistości problem jest dość istotny. I prędzej czy później wpłynie to na wielu. Po dokładniejszym przeanalizowaniu problemu zdałem sobie sprawę, że mam dwa wyjścia z tej sytuacji:
- Zwróć się do profesjonalistów (freelancerów) o pomoc, płacąc im pewna ilość pieniądze, które zawsze są na czas.
- Spróbuj sam rozwiązać problem.
Wybrałem więc drugą opcję i powiem szczerze, do tej pory nie żałowałem ani grama. Udało mi się zmniejszyć obciążenie hostingu dwa lub trzy razy. Tutaj przekonaj się sam:
Różnica jest na miejscu! Teraz pokażę ci i powiem, co zrobiłem w tym celu:
— zoptymalizowano bazę danych mysql, co znacząco wpłynęło na obciążenie hostingu i przyspieszenie wordpressa;
- pozbyłem się około 8 zbędnych wtyczek.
- przyspieszyłem wordpress, edytując kilka plików tematycznych mojego bloga.
Ponieważ materiał jest dość obszerny, postanowiłem podzielić go na trzy części. W tym artykule dowiesz się, jak zmniejszyć obciążenie hostingu poprzez optymalizację bazy danych. W następnym artykule ci opowiem. I ostatni artykuł będzie na ten temat. Kiedy zrobiłem to wszystko z moim zasobem, byłem zszokowany tym, jak mój blog zaczął się ładować! W porównaniu z tym, co to było, zaczął latać.
Ogólnie rzecz biorąc, materiał, który zbierzesz z tych trzech postów, będzie po prostu niesamowity. Nie przegap, !
Optymalizacja bazy danych
Zanim zaczniesz wykonywać różne czynności z bazą danych, koniecznie zrób utworzyć kopię zapasową . Aby w razie problemów można było wszystko szybko przywrócić. Baza zawiera całą historię Twojego zasobu, przechowuje wszystkie wpisy, które są obecne na Twoim blogu! Generalnie radzę, aby z zasady codziennie zapisywać bazę danych! Zajmie Ci to dosłownie 1 minutę, ale zawsze będziesz spać spokojnie. Wiesz, wszystko może się zdarzyć.
1. Utwórz kopię zapasową bazy danych
Dla wygody łączenia się z serwerem i przetwarzania danych korzystam z . Bardzo fajna sprawa, kiedyś napiszę osobny post o tym kliencie, . Ogólnie rzecz biorąc, musisz przejść do swojego serwera i poszukać zakładki „Bazy danych” lub „Bazy danych MySQL”, coś w tym rodzaju. Na każdym serwerze znajduje się baza danych, serwer może poprosić o hasło podczas przejścia. Musisz to mieć. Przy zakupie hostingu podawane jest hasło.
W rezultacie powinieneś być na tej stronie, phpMyAdmin:
Wejdź do bazy danych klikając na jej nazwę. Tabela bazy danych otworzy się przed tobą (kliknij, aby powiększyć):
Kliknij „Eksportuj” i „OK”. Oszczędzaj na swoim komputerze. Wszystko, baza jest zapisana, teraz możemy zacząć ją optymalizować. Pamiętaj, że jeśli Twój hosting ma pole „Zapisz jako plik”, nie zapomnij zaznaczyć pola obok niego! A także pamiętaj, ile to waży ten moment swoją bazę danych, a następnie zobacz, ile będzie ważyć po optymalizacji.
Przed optymalizacją ważył dla mnie 26 Mb - to STRASZNE, ale co teraz? A teraz waży tylko 2 Mb! Czy możesz sobie wyobrazić, ile niepotrzebnych śmieci zawierała w sobie? Czy możesz sobie wyobrazić, jakie obciążenie stworzyło na serwerze? Po zoptymalizowaniu bazy danych mój blog zaczął latać jak odrzutowiec! Ogólnie rzecz biorąc, po wykonaniu wszystkich kroków opisanych poniżej, poczujesz znaczną różnicę!
2. Wyłącz wersje publikowania i ustaw minimalny okres przechowywania usuniętych plików w koszu
Co to jest weryfikacja posta? Kiedy piszesz post na blogu, wordpress automatycznie zapisuje kopię zapasową każdego posta w bazie danych po określonym czasie, generalnie robi to automatycznie. A teraz wyobraź sobie, kiedy piszesz 50 postów na blogu? Ile kopii postów zachowasz? To jest dobre! Podczas pisania posta masz już co najmniej 10 automatycznych zapisów!
Dodatkowo, jeśli usuniesz pliki, gromadzą się one w koszu, który również ładuje bazę danych. Oczywiście dobrze, jeśli od razu usuniesz plik z kosza, ale często zdarza się, że wiele osób o tym zapomina, a niektórzy po prostu się zapychają! A to, jak nie dobrze… Baza danych rośnie, obciążenie serwera rośnie i rośnie, blog ładuje się coraz wolniej… Zastanawialiście się kiedyś, jakie to może mieć konsekwencje?
Oto główna część konsekwencji, ale nie wszystkie: redukcja, częste awarie, pogorszenie, obniżenie pozycji w wynikach wyszukiwania… A potem autor rozpacza z powodu nieuzasadnionych oczekiwań. Chęć blogowania z czasem znika i to wszystko! Rozbić się!
Co ja o tym wszystkim mówię? Baza danych musi być stale monitorowana i utrzymywana w dobrym stanie. Zrozum, że baza danych jest jak serce bloga. Przy ciągłym obciążeniu serca niepotrzebnymi śmieciami, z czasem nie wytrzyma i ZATRZYMA! Myślę, że mnie rozumiesz? Dość horrorów i przejdźmy do optymalizacji baz danych.
Otwórz więc plik wp-config.php, który znajduje się w katalogu głównym twojego bloga, tj. Twój hosting/httpdocs lub public_html (w zależności od hostingu)/wp-config.php. I dodaj do tego dwie linijki:
| 1 2 | define("WP_POST_REVISIONS" , fałsz) ; zdefiniuj ("EMPTY_TRASH_DAYS" , 1 ) ; |
Linia nr 1 wyłącza weryfikację postów, linia nr 2 oznacza, ile dni usunięte pliki będą przechowywane w koszu. Jak widać, wstawiłem „1”, oczywiście możesz wpisać „0”, ale jeśli nagle, przez zaniedbanie, zadrży Ci ręka i klikniesz w link „usuń”, wszystko jest KAPETS!
A po siedzeniu przy komputerze przez 5-8 godzin uwierz mi, to możliwe! Więc wolę zostawić cyfrę „1”. Oczywiście po usunięciu pliku lepiej od razu ręcznie opróżnić kosz, ale nawet jeśli o tym zapomnisz, plik zostanie automatycznie usunięty z kosza już po dniu! Oto jak to dla mnie wygląda:
3. Usuń wersje postu
Jeśli w poprzednim akapicie wyłączyliśmy rewizję postów, to w tym akapicie musimy usunąć wszystkie rewizje postów, które narosły przez cały czas blogowania. Jeśli nigdy tego nie robiłeś, zachowałeś ich niewiarygodnie dużą liczbę! Zróbmy to. Skopiuj ten wiersz tutaj:
Wracamy do bazy danych MySQL, jak opisano w pierwszym akapicie. Przejdź do zakładki SQL, wklej skopiowaną linię w pole i kliknij "OK":
Baza danych zapyta:
Odpowiadamy „OK” i sprawdzamy, ile niepotrzebnych rewizji postów zawierała Twoja baza danych i ile czasu zajęło przetworzenie wniosku. A każdy kawałek czasu daje swój ładunek:
Zrobiłem czyszczenie 3 dni temu, więc nie otrzymał jeszcze dla mnie poprawek. Kiedy po raz pierwszy wyczyściłem bazę danych, usunąłem już 1800 nieparzystych linii! Czy możesz sobie wyobrazić, ile kopii niepotrzebnych postów było w nim przechowywanych? Pójść dalej.
4. Optymalizuj posty w wp-post
Folder wp-post zawiera wszystkie posty na blogu. Tak jak w poprzednim akapicie, skopiuj linię:
| TABELA OPTYMALIZACJI wp_posts; |
I wklej go w polu zapytania SQL. Kliknij "OK", spójrz:
Wszystkie wnioski zakończone!
5. Czysty wp-postmeta
Co dokładnie sprzątamy? Folder wp-postmeta zawiera:
- czas ostatniej edycji któregokolwiek z postów. Nie ma znaczenia, ale obciążenie serwera, które jest żadne, ale daje;
- treść poprzedniego (zrozumiały dla człowieka adres URL). Jeśli kiedykolwiek zmieniłeś permalink w jakimkolwiek poście. Następnie, gdy go zmienisz, nie jest usuwany, ale osadza się w folderze wp-postmeta i ładuje twoją bazę danych.
Robimy to samo, skopiuj ten kod:
Wklej go w pole zapytania SQL i kliknij OK. Spójrzmy na wynik:
6. Usuń komentarze spamowe
Odbywa się to w ten sam sposób, skopiuj kod:
Wstaw w polu zapytania SQL, kliknij "OK", spójrz na wynik:
Jak widać „0”. Po wypełnieniu tego żądania zapomnisz o komentarzach spamowych!
7. Usuń pingbacki
Pingbacki to powiadomienia, że ktoś łączy się z Twoim postem lub stroną. Nie potrzebujemy tego, dodatkowe obciążenie! Usunąć!
8. Wyłącz pingbacki
Z ostatniego akapitu dowiedzieliśmy się, że pingbacki nie przynoszą żadnych korzyści naszemu zasobowi, a jedynie go zatykają. Więc po prostu je wyłączmy. Skopiuj ten kod:
| AKTUALIZUJ wp_posts p USTAW s. ping_status="zamknięty" |
Jak ci się podoba to sprzątanie? Podobało ci się? Teraz spójrz, ile Twoja baza danych zaczęła ważyć po jej optymalizacji? Znacznie zmniejszony rozmiar? I powiedziałem ci! Zobacz, jak ładuje się Twój blog! Musi latać! Ale to nie wszystko na dziś. Teraz rozważymy ostatni punkt, który również znacznie poprawi optymalizację.
9. Zainstaluj wtyczkę Optymalizuj DB
Już pokrótce wspomniałem o tej wtyczce. Cóż, przyjrzyjmy się bliżej, jak z niego korzystać. Zgadłeś, że ta wtyczka pomaga zoptymalizować bazę danych! Pobierz archiwum z wtyczką na swój komputer i aktywuj je:
To wszystko, Twoja baza danych jest dodatkowo zoptymalizowana za pomocą wtyczki:
Po optymalizacji wyłącz wtyczkę, aby nie obciążała dodatkowego zasobu. I ogólnie radzę wykonywać wszystkie wyżej opisane czynności z częstotliwością raz w miesiącu, nawet częściej. A wtedy Twój blog załaduje się błyskawicznie, a obciążenie serwera będzie minimalne.
A w dalszej części wpisu pokażę jak zastąpić niektóre niepotrzebne wtyczki kodami. Pamiętaj, aby niczego nie przegapić. To będzie potężny post, po którym Twój serwer będzie lekki jak piórko!
I z tym pożegnam się z tobą. To wszystko na dziś, życzę wszystkim sukcesów i pamiętaj, że jest to ogromne zmniejszenie obciążenia twojego zasobu. Pożegnaj wszystkich i do zobaczenia wkrótce.
I na koniec porcja żartów:
Jak ci się podoba ten artykuł? Jestem pewien, że będziesz zadowolony po przeczytaniu go i zarekomendowaniu swoim zasobem! Czekam na Wasze komentarze!
Podobał Ci się artykuł? Podziel się z przyjaciółmi!
Każdy komentator dostaje w prezencie książkę!
Książka zawiera szczegółowy opis bardzo skuteczne metody promocja Twojego zasobu!
60 komentarzy
- Aleksander 8 grudnia 2012 15:18
I wiem, dlaczego twoje obciążenie pracą tak bardzo wzrosło. Po prostu przyzwyczaiłem się tu z tobą i ciągle czegoś się uczę. A co jeśli infa tutaj jest fajna. Ale poważnie, polecam, aby wszystkie powyższe wskazówki zostały wykonane przez wszystkich blogerów w pierwszej kolejności. Zrobiłem to dawno temu, więc śpię spokojnie. A jednak wtyczka Optimize DB jest generalnie obowiązkowym atrybutem każdego bloga. Dzięki Kolya, jak zawsze, wszystko jest przydatne i istotne. I nie mogę się doczekać kolejnego wpisu. Więc idź napisz
- 9 grudnia 2012 16:19
Boję się grzebać w bazie, ale po zainstalowaniu i wyczyszczeniu za pomocą wtyczki WP-Cleanup zmniejszyła się z prawie 50 do 7Mb. Blog naprawdę zaczął się ładować znacznie szybciej.
- 9 grudnia 2012 20:39
Ściśle mówiąc, to nie sama baza danych pyta podczas operacji na bazie danych (ogólnie DBMS, wszystkie akcje są takie same, o nic nie pyta), ale klient, phpMySql.
Jeśli chodzi o pingbacki, „Od ostatniego punktu dowiedzieliśmy się, że pingbacki nie przynoszą żadnych korzyści naszym zasobom, a jedynie je zatykają”. Ściśle mówiąc, nic nie zostało wykryte.
Właśnie powiedziałeś, nie argumentując, że nie są potrzebne, to wszystko. W rzeczywistości mogą być przydatne, wystarczy użyć tego narzędzia zgodnie z jego przeznaczeniem. Na przykład, czy słowo kluczowe „sieć semantyczna” coś dla Ciebie znaczy?
- 10 grudnia 2012 08:36
- Jurij 16 grudnia 2012 23:49
Witaj mój przyjacielu!
Twój post jest naprawdę niesamowity. W Internecie napisano tyle bzdur, że informacje trzeba przeszukiwać krok po kroku. I oto poszedłem, a na tobie wszystko jest zrozumiałe i zrozumiałe. Właśnie zacząłem mieć problem z obciążeniem serwera. Polecam również zainstalowanie wtyczki WP Super Cache. Wystarczy go odpowiednio skonfigurować. Fajna wtyczka! Może w innych Twoich postach coś o nim jest powiedziane, ale jeszcze tego nie czytałem. Spieszę przejść do drugiej części optymalizacji. Powodzenia dla Ciebie i Twojego bloga
- 25 grudnia 2012 11:40 rano
- 28 stycznia 2013 11:24
Dzień dobry! Bardzo interesujące, ale co z moim blogiem na Bloggerze? Wszystkie wtyczki do WP nie są odpowiednie dla Blogspota, musisz samodzielnie poszukać metod optymalizacji w Internecie.
Z poważaniem Vadim.
- Anton 2 kwietnia 2013 20:34
Dziękuję za naprawdę dobry post. Nawiasem mówiąc, po wykonaniu punktu 3, mam - „4145 wierszy zostało usuniętych. (Żądanie zajęło 7,0269 sekund)."
- 14 lipca 2013 19:04
Ciekawe, ale jakoś da się wyczyścić bazę danych ze starych wtyczek? Na pewno były też po nich jakieś ślady?
- 14 lipca 2013 19:06
Po: a także bardzo podobny do twojego tekstu tutaj dayafternight.ru/wordpress/baza-dannih-mysql-optimizacia
- 12 września 2013 12:57
Dzięki Nikolay, właściwa rzecz.
Wszystko jest dostępne i jasno napisane.
Czy jest już artykuł o kodach?
- 12 września 2013 13:05
Nikolay zapomniał zapytać, powiedz mi proszę. Kiedy przeprowadziłem optymalizację, znalazłem nową bazę danych information_schema w moim PhpMyadmin
Skąd mogła pochodzić?
Ostatnio wstawiono tylko kod metryczny Yandex.
Natalia Geger
Zignoruj to... Większość nowoczesnych serwerów to ma! Wynika to z wydania MySQL w wersji 5.0 i nowszych...
INFORMATION_SCHEMA to wirtualna baza danych, która powstaje podczas uruchamiania serwera i zawiera metadane wszystkich baz, tj. informacje o strukturze bazy danych. Jest dostępny tylko do czytania.
- 27 października 2013 01:06
oj wyczyściłem bazę wg Twojej metody + od siebie pisakami, wynik jest oczywisty. Wcześniej baza ważyła 20mb, teraz 5mb
- 29 października 2013 23:34
Bardzo dziękuję za artykuł. Dziś też otrzymałem wtyczkę od hosta. W wyniku działań baza z 25Mb stała się 5,2. Są 2 pytania, wszystkie te manipulacje powinny być wykonywane okresowo? I drugie pytanie, zainstalowałem wtyczkę, klikam, aby zoptymalizować, w wyniku czego naprzeciwko każdej linii jest napisane,
uwaga: Tabela nie obsługuje optymalizacji, zamiast tego wykonujemy odtwarzanie + analizę
Czyż nie wszystko wygląda dobrze?!
Zapraszamy! Tak, robię te wszystkie manipulacje mniej więcej raz w miesiącu. Ale nie mogę jeszcze nic powiedzieć o wtyczce, najwyraźniej zrobiłeś coś złego. Spróbuj wyszukać w Internecie informacje na ten temat. Ale są też dobre rzeczy. Zostawiłeś 2100. komentarz na moim blogu i za to masz prawo do nagrody w wysokości 100 rubli:
Wyślij swój numer wmr-portmonetki, a ja przeleję pieniądze do Ciebie.
- 30 października 2013 13:27
Dziękuję, nagroda została odebrana. Jak trafiłem na twoją stronę?! Wczoraj po raz kolejny strona przestała działać, a na ekranie pojawił się napis „Błąd połączenia z bazą danych”. Napisałem do hostera, potwierdzili, że jest duże obciążenie MySQL i coś z tym zrób, ale na razie przeszli na wyższą taryfę. Od razu zacząłem szukać, co robić i znalazłem Twój artykuł, który zmniejszył bazę o 5 razy. Wtyczka, która początkowo nie chciała działać, nadal działała, ale główny problem, polegający na usuwaniu zbędnych żądań, nie został rozwiązany. Mam już zainstalowaną wtyczkę WP Super Cache, ale buforuje strony, a nie zapytania do bazy danych. I tak do czwartej rano szukałem wtyczki, która mogłaby mi pomóc w prośbach i ją znalazłem. Pamięć podręczna plików WP buforuje żądania, liczba żądań i MB pamięci jest znacznie zmniejszona. Na stronach, na których wcześniej było 40 żądań i 35 MB, teraz są żądania 9 i 12 MB. Jedyną rzeczą jest to, że prędkość pobierania wydaje się nieco wzrosnąć, ale tylko nieznacznie, biorąc pod uwagę, że moja prędkość ładowania strony wynosi średnio 0,15-0,5 sekundy. Może ktoś ta informacja będzie ciekawie.
- 7 grudnia 2013 15:41
Czy powyższe działania mogą wpłynąć na działanie wtyczki nrelate-flyout?
→ 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żyj dysku twardego 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 w żaden sposób nie tak:
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.
W codziennej pracy podczas pisania zapytań masz do czynienia z dość podobnymi błędami.
W tym artykule chciałbym podać przykłady, jak NIE pisać zapytań.
- Zaznaczanie wszystkich pól
WYBIERZ * Z tabeliPisząc zapytania nie zaznaczaj wszystkich pól - "*". Wymień tylko te pola, których naprawdę potrzebujesz. Zmniejszy to ilość pobieranych i wysyłanych danych. Nie zapomnij też o pokrywaniu indeksów. Nawet jeśli naprawdę potrzebujesz wszystkich pól w tabeli, najlepiej je wymienić. Po pierwsze poprawia czytelność kodu. Korzystając z gwiazdki, nie można dowiedzieć się, jakie pola znajdują się w tabeli, bez zaglądania do niej. Po drugie, z biegiem czasu liczba kolumn w Twojej tabeli może się zmieniać, a jeśli dziś jest to pięć kolumn INT, to za miesiąc mogą zostać dodane pola TEXT i BLOB, co spowolni selekcję.
- Żądania w cyklu.
Musisz jasno zrozumieć, że SQL jest językiem operującym na zestawach. Czasami programistom przyzwyczajonym do myślenia w kategoriach języków proceduralnych trudno jest przebudować swoje myślenie w języku zestawów. Można to zrobić w bardzo prosty sposób, stosując prostą zasadę – „nigdy nie wykonuj zapytań w pętli”. Przykłady, jak można to zrobić:1. Próbki
$news_ids = get_list("SELECT news_id FROM Today_news");
while($news_id = get_next($news_ids))
$news = get_row("SELECT tytuł, treść FROM news WHERE news_id = ". $news_id);Zasada jest bardzo prosta – im mniej żądań, tym lepiej (choć są od tego wyjątki, jak od każdej reguły). Nie zapomnij o konstrukcji IN(). Powyższy kod można zapisać w jednym zapytaniu:
SELECT tytuł, treść FROM Today_news INNER JOIN news USING(news_id)2. Wkładki
$log = parse_log();
while($rekord = następny($log))
query("INSERT INTO logs SET value = "(!LANG:. $log["value"]);!}Dużo wydajniej jest skleić i wykonać pojedyncze zapytanie:
INSERT INTO logs (value) VALUES (...), (...)3. Aktualizacje
Czasami trzeba zaktualizować wiele wierszy w tej samej tabeli. Jeśli zaktualizowana wartość jest taka sama, wszystko jest proste:
UPDATE news SET title = "(! JĘZYK: test" WHERE id IN (1, 2, 3).!}Jeśli wartość do zmiany dla każdego wpisu jest inna, można to zrobić za pomocą następującego zapytania:
AKTUALIZUJ aktualności SET
tytuł = PRZYPADEK
KIEDY news_id = 1 THEN "aa"
WHEN news_id = 2 THEN "bb" END
GDZIE news_id IN (1, 2)Nasze testy pokazują, że takie zapytanie jest 2-3 razy szybsze niż kilka oddzielnych zapytań.
- Wykonywanie operacji na polach indeksowanych
SELECT user_id FROM users WHERE blogs_count * 2 = $wartośćTo zapytanie nie użyje indeksu, nawet jeśli zaindeksowana jest kolumna blogs_count. Aby indeks mógł zostać użyty, żadne przekształcenia nie muszą być wykonywane na indeksowanym polu w zapytaniu. W przypadku takich żądań przenieś funkcje konwersji do innej części:
SELECT user_id FROM users WHERE blogs_count = $value / 2;Podobny przykład:
SELECT user_id FROM użytkowników WHERE TO_DAYS (CURRENT_DATE) - TO_DAYS (zarejestrowanych)<= 10;Nie użyje indeksu w zarejestrowanym polu, podczas gdy
SELECT user_id FROM użytkowników WHERE zarejestrowani >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY);
Wola. - Pobieranie rzędów tylko po to, aby policzyć ich liczbę
$result = mysql_query("SELECT * FROM table", $link);
$liczba_wierszy = mysql_num_rows($wynik);
Jeśli musisz wybrać liczbę wierszy spełniających określony warunek, użyj zapytania tabeli SELECT COUNT(*) FROM zamiast zaznaczania wszystkich wierszy tylko w celu ich zliczenia. - Pobieranie dodatkowych wierszy
$result = mysql_query("SELECT * FROM table1", $link);
while($row = mysql_fetch_assoc($wynik) && $i< 20) {
…
}
Jeśli potrzebujesz tylko n przykładowych wierszy, użyj LIMIT zamiast odrzucać dodatkowe wiersze w aplikacji. - Używając ORDER BY RAND()
SELECT * Z tabeli ORDER BY RAND() LIMIT 1;Jeśli tabela ma więcej niż 4-5 tysięcy wierszy, to ORDER BY RAND () będzie działać bardzo wolno. Dużo wydajniejsze będzie uruchomienie dwóch zapytań:
Jeśli tabela ma klucz podstawowy „auto_increment” i nie ma przerw:
$rnd = rand(1, query("SELECT MAX(id) FROM table"));
$row = query("SELECT * FROM table WHERE id = ".$rnd);Lub:
$cnt = query("WYBIERZ LICZNIK(*) Z tabeli");
$row = query("SELECT * FROM table LIMIT ".$cnt.", 1");
co jednak może być również powolne przy bardzo dużej liczbie wierszy w tabeli. - Stosowanie duża liczba DOŁĄCZ
WYBIERZ
v.video_id
imię,
g.gatunek
OD
filmy AS v
LEWE ŁĄCZENIE
link_actors_videos AS la ON la.video_id = v.video_id
LEWE ŁĄCZENIE
aktorzy jako ON a.actor_id = la.actor_id
LEWE ŁĄCZENIE
link_genre_video AS lg ON lg.video_id = v.video_id
LEWE ŁĄCZENIE
gatunki AS g ON g.genre_id = lg.genre_idNależy pamiętać, że gdy tabele są połączone jeden-do-wielu, ilość wierszy w selekcji będzie rosła z każdym kolejnym JOIN "e. W takich przypadkach szybciej jest rozbić takie zapytanie na kilka prostych.
- Używając LIMIT
SELECT... FROM table LIMIT $start, $per_pageWiele osób myśli, że takie zapytanie zwróci rekordy $per_page (zwykle 10-20) i dlatego działa szybko. Będzie działać szybko przez kilka pierwszych stron. Ale jeśli liczba rekordów jest duża i trzeba wykonać zapytanie SELECT ... FROM table LIMIT 1000000, 1000020, to aby wykonać takie zapytanie, MySQL najpierw wybierze 1000020 rekordów, odrzuci pierwszy milion i zwróci 20. To może wcale nie być szybki. Nie ma trywialnych sposobów rozwiązania problemu. Wielu po prostu ogranicza liczbę dostępnych stron do rozsądnej liczby. Możliwe jest również przyspieszenie takich zapytań poprzez wykorzystanie indeksów pokrywających lub rozwiązań firm trzecich (np. sphinx).
- Nie używam AKTUALIZACJI ZDUPLIKOWANEGO KLUCZA
$row = zapytanie("SELECT * FROM tabela WHERE id=1");Jeśli($wiersz)
query("UPDATE tabela SET kolumna = kolumna + 1 WHERE id=1")
w przeciwnym razie
query("INSERT INTO table SET kolumna = 1, id=1");Podobną konstrukcję można zastąpić pojedynczym żądaniem, pod warunkiem, że w polu id znajduje się klucz podstawowy lub unikalny:
INSERT INTO tabela SET kolumna = 1, id=1 ON DUPLICATE KEY UPDATE kolumna = kolumna + 1
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.
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 przechowywania długoterminowego $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.
Aby skutecznie zoptymalizować, musimy zastosować trzy podejścia do Twojej aplikacji:
- Analiza (rejestrowanie powolnych zapytań, eksploracja systemu, analiza zapytań i projektowanie baz danych)
- Wymagania dotyczące wydajności (ilu użytkowników)
- Ograniczenia technologiczne (szybkość sprzętu, niewłaściwe użycie MySQL)
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ą, dlatego mając kilka opcji, nie zawsze dokonuje właściwego wyboru 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 w Twojej bazie danych MySQL. 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 tymczasowej tabeli zapytań MySQL. To wymaga dużo zasobów.
Ignorowanie indeksu PEŁNEGO TEKSTU
Używanie LIKE to najlepszy sposób na spowolnienie wyszukiwania pełnotekstowego.
Nieuzasadniony wybór dużej liczby rzędów
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 w porządku? 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 na tej samej tabeli jest wiele operacji pobierania. Aby kontynuować zgłębianie tematu, radzę spojrzeć w stronę projektu Percona.
