MySQL
Podnoszenie wydajności

Składnia

• Nie używaj DISTINCT i GROUP BY w tym samym WYBORZE.

• Nie dziel stron na OFFSET, „pamiętaj, gdzie skończyłeś”.

• GDZIE (a, b) = (22,33) wcale się nie optymalizuje.

• Wyraźnie powiedz ALL lub DISTINCT po UNION - przypomina ci, że wybierasz pomiędzy szybszą ALL a wolniejszą DISTINCT.

• Nie używaj opcji WYBIERZ *, zwłaszcza jeśli masz niepotrzebne kolumny TEKST lub BLOB. W tabelach tmp i transmisji występuje narzut.

• Jest to szybsze, gdy GROUP BY i ORDER BY mogą mieć dokładnie tę samą listę.

• Nie używaj FORCE INDEX; to może pomóc dziś, ale jutro prawdopodobnie będzie bolało.

Uwagi

Dodaj poprawny indeks

To ogromny temat, ale także najważniejszy problem z wydajnością.

Główną lekcją dla początkującego jest poznanie indeksów „kompozytowych”. Oto szybki przykład:

INDEX(last_name, first_name)

jest doskonały dla tych:

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- (order in WHERE does not matter)

ale nie dla

WHERE first_name = '...'   -- order in INDEX _does_ matter
WHERE last_name = '...' OR first_name = '...'   -- "OR" is a killer

Ustaw pamięć podręczną poprawnie

innodb_buffer_pool_size powinien stanowić około 70% dostępnej pamięci RAM.

Unikaj nieefektywnych konstrukcji

x IN ( SELECT ... )

zamień się w JOIN

Jeśli to możliwe, unikaj OR .

Nie „ukrywaj” indeksowanej kolumny w funkcji, takiej jak WHERE DATE(x) = ... ; sformatuj ponownie jako WHERE x = ...

Zasadniczo można uniknąć WHERE LCASE(name1) = LCASE(name2) , mając odpowiednie zestawienie.

Nie używaj opcji OFFSET do „paginacji”, zamiast tego „pamiętaj, gdzie przerwałeś”.

Unikaj SELECT * ... (chyba że debugowanie).

Uwaga do Marii Delevy, Barranki, Batsu: To jest miejsce; usuń te elementy, tworząc pełne przykłady. Po wykonaniu tych, które możesz, przeprowadzę się, aby rozwinąć resztę i / lub podrzucić.

Negatywne

Oto kilka rzeczy, które prawdopodobnie nie poprawią wydajności. Wynikają z nieaktualnych informacji i / lub naiwności.

• InnoDB poprawił się do tego stopnia, że MyISAM raczej nie będzie lepszy.
• PARTITIONing rzadko zapewnia korzyści w zakresie wydajności; może nawet zaszkodzić wydajności.
• Ustawianie query_cache_size większe niż 100M zazwyczaj boli wydajność.
• Zwiększenie wielu wartości w my.cnf może prowadzić do „zamiany”, co jest poważnym problemem z wydajnością.
• „Indeksy prefiksów” (takie jak INDEX(foo(20)) ) są na ogół bezużyteczne.
• OPTIMIZE TABLE jest prawie zawsze bezużyteczna. (I polega na zablokowaniu stołu.)

Mieć INDEKS

Najważniejszą rzeczą dla przyspieszenia zapytania w dowolnej nie-malutkiej tabeli jest odpowiedni indeks.

WHERE a = 12  --> INDEX(a)
WHERE a > 12  --> INDEX(a)

WHERE a = 12 AND b > 78  --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78  --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x  --> INDEX(x)
ORDER BY x, y  --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC  --> No index helps - because of mixing ASC and DESC

Nie chowaj się w funkcji

Częstym błędem jest ukrywanie indeksowanej kolumny w wywołaniu funkcji. Na przykład indeks nie może temu pomóc:

WHERE DATE(dt) = '2000-01-01'

Zamiast tego, biorąc pod uwagę INDEX(dt) mogą oni użyć indeksu:

WHERE dt = '2000-01-01'  -- if `dt` is datatype `DATE`

Działa to dla DATE , DATETIME , TIMESTAMP , a nawet DATETIME(6) (mikrosekund):

WHERE dt >= '2000-01-01'
  AND dt  < '2000-01-01' + INTERVAL 1 DAY

LUB

Ogólnie OR optymalizuje zabójstwa.

WHERE a = 12 OR b = 78

nie może używać INDEX(a,b) i może, ale nie INDEX(a), INDEX(b) używać INDEX(a), INDEX(b) poprzez „scalanie indeksu”. Scalanie indeksów jest lepsze niż nic, ale tylko ledwo.

WHERE x = 3 OR x = 5

zamienia się w

WHERE x IN (3, 5)

który może użyć indeksu x w nim.

Podzapytania

Podkwerendy występują w kilku odmianach i mają różne możliwości optymalizacji. Po pierwsze, zauważ, że podzapytania mogą być „skorelowane” lub „nieskorelowane”. Skorelowane oznacza, że zależą one od pewnej wartości spoza podzapytania. Ogólnie oznacza to, że podzapytanie musi zostać ponownie ocenione dla każdej wartości zewnętrznej.

Ten skorelowany podzapytanie jest często całkiem niezły. Uwaga: Musi zwrócić maksymalnie 1 wartość. Często jest przydatny jako alternatywa dla LEFT JOIN , choć niekoniecznie szybszy niż.

SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
    FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
    FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
    FROM u ...

Jest to zwykle nieskorelowane:

SELECT ...
    FROM ( SELECT ... ) AS a
    JOIN b ON ...

Uwagi na temat FROM-SELECT :

• Jeśli zwróci 1 wiersz, świetnie.
• Dobrym paradygmatem (ponownie „1 wiersz”) jest podzapytanie ( SELECT @n := 0 ) , inicjując w ten sposób ( SELECT @n := 0 ) do użycia w pozostałej części lub zapytaniu.
• Jeśli zwraca wiele wierszy, a JOIN ma również ( SELECT ... ) z wieloma wierszami, wówczas wydajność może być straszna. W wersjach wcześniejszych niż 5.6 nie było indeksu, więc stał się CROSS JOIN ; 5.6+ obejmuje dedukcję najlepszego indeksu w tabelach tymczasowych, a następnie wygenerowanie go, tylko po to, aby go wyrzucić po zakończeniu z SELECT .

DOŁĄCZ + GROUP BY

Częstym problemem, który prowadzi do nieefektywnego zapytania, jest coś takiego:

SELECT ...
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY a.id

Po pierwsze, JOIN rozszerza liczbę wierszy; następnie GROUP BY zmniejsza go z powrotem o liczbę wierszy w a .

Rozwiązanie tego problemu z wybuchem-implode może nie być dobrym wyborem. Jedną z możliwych opcji jest przekształcenie JOIN w skorelowane podzapytanie w SELECT . Eliminuje to również GROUP BY .