Baza danych MySQL jest lekkim, niezawodnym, ale jednocześnie w pełni funkcjonalnym poważnym systemem DBMS. Prostota MySQL zapewnia niski poziom wkładu programisty, ale później nie są oni szczególnie skłonni do zmiany strategii pracy z DBMS. To jest dla programistów z doświadczeniem w korzystaniu z MySQL w praktyce, że nasz artykuł jest zorientowany.
Większość materiału to tłumaczenie artykułu 32 Wskazówki dotyczące przyspieszenia zapytań MySQL.
- Użyj trwałego połączenia z bazą danych, aby uniknąć narzutu systemowego.
- Sprawdź, czy PRIMARY KEY jest na kolumnach z dużą liczbą unikalnych elementów. Na przykład kolumna „płeć” ma tylko 2 opcje (męskie i żeńskie). Natomiast unikalny identyfikator użytkownika zawiera dużą liczbę wartości i nadaje się do tego, aby stać się kluczem podstawowym.
- Pożądane jest, aby wszystkie łącza między tabelami były indeksowane (co oznacza, że muszą mieć te same typy danych, więc zapytania będą szybsze). Sprawdź również, czy pola, w których chcesz przeprowadzić wyszukiwanie (często pojawiają się w wyrażeniach WHERE, ORDER BY lub GROUP BY) mają indeksy. Ale nie dodawaj zbyt wielu indeksów: najgorsze, co możesz zrobić, to dodać indeks do każdej kolumny w tabeli (nie widziałem więcej niż 5 indeksów, nawet w tabeli z 20-30 kolumnami). Jeśli nigdy nie porównasz kolumny z innymi danymi i nie przeszukasz jej, nie ma potrzeby umieszczania na niej indeksu.
- Użyj prostych przywilejów, jak to możliwe, gdy wykonujesz polecenie GRAND, aby zmniejszyć koszty z powodu sprawdzania uprawnień podczas łączenia się z bazą danych.
- Używaj mniej pamięci RAM na linię, dokładnie określając wymaganą długość kolumny. (Na przykład, aby zapisać hasło w md5, musisz przydzielić dokładnie 32 znaki, to już nie ma sensu. Po prostu, ale wielu ludzi o tym zapomina).
- W MySQL możesz zdefiniować indeks na kilku kolumnach jednocześnie. W takim przypadku można użyć lewej kolumny jako oddzielnego indeksu, zmniejszając w ten sposób liczbę pojedynczych indeksów.
- Jeśli indeks składa się z kilku kolumn, dlaczego nie utworzyć kolumny mieszania z indeksem, który będzie krótki i raczej unikalny? Wtedy twoje zapytanie może wyglądać następująco: SELECT * FROM tabela WHERE hash_column = MD5 (CONCAT (col1, col2)) AND col1 = 'aaa' AND col2 = 'bbb';
- Rozważ uruchomienie ANALYZE TABLE (lub myisamchk --analyze z wiersza poleceń) w tabeli po wypełnieniu go danymi, aby pomóc MySQL w optymalizacji zapytań.
- Jeśli to możliwe, użyj typu CHAR (zamiast VARCHAR, BLOB lub TEXT) - gdy wartości kolumn mają stałą długość: skrót MD5, kod lotniska i podobne dane. Dane w kolumnach CHAR można znaleźć szybciej niż w kolumnach z innymi typami danych.
- Nie jest konieczne dzielenie tabeli tylko dlatego, że jest w niej zbyt wiele kolumn. Przy dostępie do łańcucha nie ma to znaczenia
- Kolumna powinna być zadeklarowana jako NIE NULL, jeśli w rzeczywistości nie ma w niej pustych komórek - w ten sposób przyspieszysz przejście przez tabele.
- Jeśli zazwyczaj otrzymujesz wiersze w tej samej kolejności, na przykład wyrażenie1, wyrażenie2 ..., zapytanie: ALTER TABLE ... ORDER BY wyrażenie1, wyrażenie2 ... w celu optymalizacji tabeli.
- Nie używaj pętli w PHP, tworząc wiele zapytań. Zamiast tego spróbuj tego zapytania: SELECT * FROM `table` WHERE` id` IN (1.7, 13,42);
- Użyj domyślnej wartości kolumny i wstaw tylko te wartości, które różnią się od zwykłych. Skraca to czas analizowania żądania.
- Użyj INSERT DELAYED lub INSERT LOW_PRIORITY dla MyISAM, gdy do logowania używany jest MySQL. Ponadto, jeśli pracujesz z MyISAM, możesz dodać opcję DELAY_KEY_WRITE = 1 - pozwoli to na szybszą aktualizację indeksów, ponieważ nie zostaną one zapisane na dysku, dopóki plik nie zostanie zamknięty.
- Dane sesji użytkownika (lub dowolne inne niezbyt ważne dane) w tabeli typu MEMORY znacząco skracają czas dostępu do bazy danych.
- Jeśli chcesz zapisać dużą ilość danych tekstowych, zwróć uwagę na typ BLOB, który jest odpowiedni do przechowywania skompresowanych danych (COMPRESS () w MySQL wygląda wolno, możesz spojrzeć na gzipping w PHP). Oczywiście najpierw musisz sprawdzić działanie tego rozwiązania.
- Obrazy, filmy i inne pliki na stronie są zwykle przechowywane w systemie plików. Dlatego nie powinieneś przechowywać ich w bazie danych (w polach BLOB), wystarczy podać link do niej w tabeli.
- Jeśli często musisz obliczyć funkcję LICZBA lub SUMA na podstawie dużej liczby wierszy (szacunki artykułów, liczba głosów w ankiecie, liczba zarejestrowanych użytkowników itp.), Sensowne jest utworzenie osobnej tabeli i zaktualizowanie licznika w czasie rzeczywistym, co będzie znacznie szybsze. . Jeśli chcesz zebrać statystyki z ogromnych tabel rejestracyjnych, użyj tabeli przestawnej zamiast przeglądania całej tabeli za każdym razem.
- Nie używaj polecenia REPLACE (które jest w rzeczywistości DELETE + INSERT i używa identyfikatorów): użyj INSERT ... zamiast DUPLICATE KEY UPDATE (czyli INSERT + UPDATE, jeśli wystąpił konflikt). Ta sama technika może być użyta, gdy po raz pierwszy musisz wybrać SELECT, aby dowiedzieć się, czy w bazie danych są już dane, a następnie wybierz INSERT lub UPDATE. Dlaczego sam decydujesz - polegaj na bazie danych!
- Skonfiguruj buforowanie MySQL: przydziel wystarczającą ilość pamięci dla bufora (na przykład SET GLOBAL query_cache_size = 1000000) i zdefiniuj query_cache_min_res_unit w zależności od średniego rozmiaru zwracanych danych w zapytaniu.
- Podziel złożone pytania na nieco prostsze - szybciej będą odpowiednio buforowane - szybciej.
- Grupuj kilka podobnych WSTAW w jednym długim z listą WARTOŚCI, aby wstawić kilka wierszy na raz: zapytanie będzie działać szybciej, ponieważ czas połączenia, wysyłanie i analizowanie zapytania jest około 5-7 razy dłuższe niż rzeczywiste wstawienie danych ( w zależności od długości sznurka). Jeśli nie jest to możliwe, użyj START TRANSACTION i COMMIT, pod warunkiem, że pracujesz z InnoDB. W przeciwnym razie użyj LOCK TABLES - skraca to czas, w którym bufor indeksu jest opróżniany na dysk tylko raz, po wykonaniu wszystkich instrukcji INSERT. Nie zapomnij odblokować tabel po około 1000 wstawionych wierszach, aby dać innym wątkom dostęp do tabeli.
- Podczas ładowania tabeli z pliku tekstowego użyj LOAD DATA INFILE, która jest 20-100 razy szybsza.
- Znajdź wąskie gardła w aplikacji i zbadaj je. Można więc znaleźć zapytania o wysokim czasie wykonywania, nie używając indeksów, a także powolnych wyrażeń, takich jak TABELA OPTYMALIZACJI i TABELA ANALIZ.
- Skonfiguruj ustawienia serwera bazy danych. Na przykład zwiększenie rozmiaru bufora.
- Jeśli aplikacja ma wiele DELETE lub aktualizacji dynamicznych formatów wierszy (jeśli w wierszu znajduje się kolumna VARCHAR, BLOB lub TEXT, wiersz ma format dynamiczny), uruchamiaj TABELĘ OPTYMALIZACJI co tydzień. Defragmentacja zwiększa szybkość zapytań. Jeśli nie używasz replikacji, dodaj słowo kluczowe LOCAL, aby defragmentacja zajęła mniej czasu.
- Nie używaj ORDER BY RAND (), aby uzyskać kilka losowych linii. Uzyskaj 10-20 rekordów (najnowsze dodatki lub identyfikatory) i wykonaj array_random () po stronie PHP. Istnieją inne rozwiązania.
- Staraj się unikać wyrażania się, gdy jest to możliwe.
- W większości przypadków wyrażenie DISTINCT można uznać za specjalny przypadek GROUP BY; zatem optymalizacja mająca zastosowanie do zapytań GROUP BY może być również stosowana do zapytań z wyrażeniem DISTINCT. Ponadto, jeśli używasz DISTINCT, spróbuj użyć LIMIT (MySQL zatrzymuje się, gdy tylko znajdzie row_count unikalnych wierszy), i unikaj ORDER BY (w wielu przypadkach wymaga tymczasowej tabeli).
- Kiedy czytam Budowanie skalowalnych stron internetowych, zdałem sobie sprawę, że czasami konieczne jest de-znormalizowanie niektórych tabel (nawiasem mówiąc, Flickr to robi), to jest, aby powielić niektóre dane w kilku tabelach, aby uniknąć JOIN, co może być kosztowne.
- Jeśli chcesz przetestować jakąś funkcję lub wyrażenie w MySQL, użyj do tego BENCHMARK.
Zaczynam od tłumaczenia artykułu. 32 wskazówki przyspieszające zapytania MySQL .
Jeśli indeks składa się z kilku kolumn, dlaczego nie utworzyć kolumny mieszania z indeksem, który będzie krótki i raczej unikalny?