Przejdź do treści

MySQL – optymalizacja i wydajność

O pracy MySQL DBA – przemyślenia administratora

Archiwa

Archiwa z daty Lipiec, 2010

SELECT DISTINCT, tabele tymczasowe i JOIN kilku tabel

Lip 30
MySQL

W poprzednim poście analizowałem zapytanie typu SELECT DISTINCT i pokazywałem jak można uniknąć tworzenia tablicy tymczasowej. Jak sytuacja wygląda, gdy wykonywany jest SELECT DISTINCT na JOINie dwóch tabel? Niestety, tu już sytuacja jest gorsza. Tak jak w przypadku zapytania na pojedynczej tabeli, tworzona jest tabela tymczasowa.

mysql> EXPLAIN SELECT SQL_NO_CACHE DISTINCT text1 FROM tab1 LEFT OUTER JOIN tab2 USING(id)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 3
Extra: Using index; Using temporary
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.tab1.id
rows: 1
Extra:
2 rows in set (0.00 sec)

Tym razem, niestety, zastosowanie indeksów nie pomaga. Co można zrobić?

czytaj dalej…

Komentarze wył. , , , , Czytaj dalej

SELECT DISTINCT i tabele tymczasowe

Lip 24
MySQL

DISTINCT to modyfikator agregujący dane w wyniku zapytania – zwraca tylko unikalne wartości, bez powtórzeń. Najlepiej zobaczyć to na przykładzie:

mysql> SELECT text1 FROM tab2;
+-------+
| text1 |
+-------+
| w     |
| e     |
| e     |
| e     |
| f     |
+-------+
5rows in set (0.00 sec)

mysql> SELECT DISTINCT text1 FROM tab2;
+-------+
| text1 |
+-------+
| w     |
| e     |
| f     |
+-------+
3 rows in set (0.00 sec)

Jak widać, w pierwszym zestawie wyników trzykrotnie występuje wartość ‚e’. Po zastosowaniu DISTINCT otrzymaliśmy zagregowane wyniki, powtórzenia zostały usunięte. Generalnie stosowanie agregacji wiąże się z koniecznością utworzenia tabeli tymczasowej.

mysql> EXPLAIN SELECT DISTINCT text1 FROM tab2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tab2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using temporary
1 row in set (0.00 sec)

Administratorzy MySQL nie przepadają za tabelami tymczasowymi, bo stanowią one skazę na idealnym pięknie, jakim jest szybko i wydajnie działająca baza danych. Konkretnie mówiąc, potrafią one zmniejszyć tempo działania bazy z galopu, do czołgania się. Dzieje się tak szczególnie wtedy, gdy w zestawie wyników pojawi się jakaś kolumna typu BLOB czy TEXT i w efekcie tabela tworzona jest na dysku. Jak więc pozbyć się tabel tymczasowych z SELECT DISTINCT?
czytaj dalej…

Komentarze wył. , , , , , Czytaj dalej

Optymalna konfiguracja MySQL

Lip 19
MySQL

„Mam MySQL uruchomiony na serwerze fizycznym/VPS o następujących parametrach: <ciach parametry>.
Co zrobić, aby działał on z maksymalną wydajnością?

<ciach przeklejoną zawartość my.cnf>”

Znacie to, drodzy Czytelnicy? Tego typu pytania pojawiają się co pewien czas na różnorakich forach dotyczących webdeveloperki czy hostingu. Co pewien czas, coraz to nowe osoby szukają Graala, jakim jest „optymalna konfiguracja MySQL”. Uczynni, a lepiej orientujący się w temacie członkowie forum, sugerują poszukującym różne rozwiązania. Czy te poszukiwania uda się kiedykolwiek zakończyć?
czytaj dalej…

1 komentarz , , , Czytaj dalej

STRAIGHT_JOIN – gdy optimizer zawodzi

Lip 14
MySQL

Optimizer MySQL to kawałek kodu, który przy pomocy wbudowanych w niego algorytmów i na podstawie posiadanych przez serwer MySQL informacji o tabelach i ich zawartości, przepisuje zapytania do postaci najbardziej optymalnej dla serwera. Tyle teoria. W praktyce nie jest tak różowo. Podstawowym problemem jest to, że zawartość tabel się zmienia. Zmienia się wybiórczość danych, zmienia się ich lokalizacja – część danych została pobrana do pamięci, część jest na dysku. Serwer MySQL zakłada zawsze, że dane znajdują się na dysku. W praktyce, w przypadku baz danych mniejszych niż dostępna pamięć na serwerze fizycznym, może być tak, że cały zestaw używanych danych jest w pamięci. Jak wiadomo, czas dostępu do pamięci i do dysku trochę się różni. Administrator, wiedząc że czas dostępu będzie krótszy, może na przykład preferować zapytanie, które dokona skanu tabeli, ale za to nie będzie generowało tabeli tymczasowej. Optimizer zawsze wybierze tą opcję, która zagwarantuje konieczność odczytania mniejszej ilości danych. Statystyki tabeli także nie są uaktualniane na bieżąco (dlatego też trzeba co pewien czas wykonywać operacje ANALYZE TABLE), przez co optimizer może podejmować decyzje na podstawie błędnych danych. Na szczęście, w niektórych przypadkach można trochę poprawić efekty jego pracy.
czytaj dalej…

Komentarze wył. , , , Czytaj dalej

Sortowanie ORDER BY z wykorzystaniem indeksów

Lip 9
MySQL

Indeksy typu B-tree, które są stosowane w MySQL a o których pisałem niedawno, mają bardzo przydatną cechę – chodzi o to, że dane w indeksie przechowywane są w sposób posortowany. Z punktu widzenia administratora MySQL jest to cecha niezwykle pożyteczna, gdyż można ją wykorzystać do przyspieszenia działania zapytań, w których wyniki są sortowane. Wspominałem o tym przy okazji postu dotyczącego algorytmu filesort, tu temat trochę rozwiniemy.

czytaj dalej…

Komentarze wył. , , , Czytaj dalej

Komentarze

Lip 7
MySQL

Być może zostało to już przez niektórych czytelników zauważone, ale jeśli nie to chciałbym poinformować, że od dziś możliwe jest komentowanie wpisów na blogu. Zapraszam wszystkich do dzielenia się swoimi uwagami, sugestiami i pomysłami.

1 komentarz Czytaj dalej

Indeksy w MySQL

Lip 3
MySQL

To, że indeksy są niezbędne do poprawnego funkcjonowania bazy danych, powinni wiedzieć nie tylko administratorzy MySQL, ale także cała reszta użytkowników, którzy projektują strukturę bazy danych.

W silnikach MyISAM i InnoDB stosowane są indeksy typu B-tree. W pewnym uproszczeniu. W praktyce, pomiędzy indeksem w MyISAM a indeksem w InnoDB jest kilka różnic, nie są one jednak istotne dla dalszej części tego postu. Indeksy mogą zostać nałożone na jedną, lub kilka kolumn. Jest to stosunkowo istotna ich cecha, o której twórcy aplikacji bazodanowych (szczególnie ci początkujący) nie zawsze pamiętają. Przykładowy ALTER TABLE dodający indeks na kolumny `kategoria`, `podkategoria` i `cena` w tabeli `produkt` mógłby wyglądać następująco:

ALTER TABLE produkt ADD INDEX idx_kat_podkat_cena (kategoria, podkategoria, cena);

czytaj dalej…

Komentarze wył. , Czytaj dalej
MySQL – optymalizacja i wydajność
Copyright © 2018 MySQL – optymalizacja i wydajność Design by SRS Solutions do góry