Konserwacja indeksów MySQL

Przeprowadziłem wiele badań dotyczących utrzymania indeksów w MySQL, aby zapobiec fragmentacji i jakoś zoptymalizować wykonywanie niektórych zapytań.

Znam tę formułę, która oblicza stosunek maksymalnej przestrzeni dostępnej dla tabeli do przestrzeni używanej przez dane i indeksy.

Jednak na moje główne pytania wciąż nie ma odpowiedzi. Być może wynika to z faktu, że znam obsługę indeksu w SQL Server i wydaje mi się, że w MySQL powinno być jakoś podobnie.

Na serwerze SQL możesz mieć kilka indeksów, a każdy z nich może mieć różne poziomy fragmentacji. Następnie możesz wybrać jeden i wykonać operację „REORGANIZACJA” lub „ODBUDOWA” w tym konkretnym indeksie, bez wpływu na resztę.

Zgodnie z moją najlepszą wiedzą nie ma „fragmentacji tabeli” jako takiej, a SQL Server nie zapewnia żadnego narzędzia do naprawy „fragmentacji tabeli”. Zapewnia narzędzia do sprawdzania fragmentacji indeksu (rozumianej jako stosunek liczby stron używanych przez indeks VS do pełnej strony i ciągłości), a także fragmentację wewnętrzną i zewnętrzną.

Wszystko to jest dość łatwe do zrozumienia, przynajmniej dla mnie.

Teraz, gdy nadchodzi kolej na utrzymanie indeksów w MySQL, istnieje tylko koncepcja fragmentacji tabeli, jak wspomniano powyżej.

Tabela w MySQL może mieć kilka indeksów, ale kiedy sprawdzam „współczynnik fragmentacji” za pomocą tej znanej formuły, nie widzę fragmentacji każdego indeksu, ale tabelę jako całość.

Kiedy chcę zoptymalizować indeksy w MySQL, nie wybieram konkretnego indeksu do działania (jak w SQL Server). Zamiast tego wykonuję operację „OPTYMALIZUJ” w całej tabeli, która prawdopodobnie wpływa na wszystkie indeksy.

Gdy tabela jest zoptymalizowana w MySQL, zmniejsza się stosunek między przestrzenią używaną przez dane + indeksy VS a całkowitą przestrzenią, co sugeruje jakąś fizyczną reorganizację na dysku twardym, co przekłada się na zmniejszenie fizycznej przestrzeni. Jednak fragmentacja indeksu dotyczy nie tylko fizycznej przestrzeni, ale także struktury drzewa, która ulegała zmianom z powodu wstawek i aktualizacji.

Wreszcie mam tabelę w InnoDB / MySQL. Ta tabela ma 3 miliony rekordów, 105 kolumn i 55 indeksów. Ma 1,5 GB z wyłączeniem indeksów, które wynoszą 2,1 GB.

Tę tabelę uderza się tysiące razy dziennie w celu aktualizacji, wstawienia (tak naprawdę nie usuwamy rekordów).

Ta tabela została stworzona wiele lat temu i wiem na pewno, że nikt nie utrzymuje indeksów.

Spodziewałem się tam znaleźć dużą fragmentację, ale kiedy wykonam obliczenia fragmentacji zgodnie z zaleceniami

free_space / (data_length + index_length)

okazuje się, że mam tylko 0,2% fragmentacji. IMHO to dość nierealne.

Główne pytania to:

1. Jak sprawdzić fragmentację określonego indeksu w MySQL, a nie tabelę jako całość
2. Czy OPTIMIZE TABLE naprawia fragmentację wewnętrzną / zewnętrzną indeksu jak w SQL Server?
3. Kiedy optymalizuję tabelę w MySQL, czy faktycznie odbudowuje ona wszystkie indeksy w tabeli?
4. Czy realistyczne jest myślenie, że zmniejszenie fizycznej przestrzeni indeksu (bez przebudowywania samego drzewa) faktycznie przekłada się na lepszą wydajność?

Fragmentacja indeksu jest znacznie przereklamowana. Nie martw się o to.

Dwa sąsiednie, nieco puste bloki są scalane przez InnoDB jako naturalne przetwarzanie.

Losowe działania na BTree powodują, że naturalnie przyciąga się średnio do 69%. Oczywiście nie jest to 100%, ale narzut związany z „naprawą” nie jest tego wart.

SHOW TABLE STATUS daje pewne dane, ale są wadliwe - „Data_free” obejmuje pewne „wolne” miejsce, ale nie inne „wolne” miejsce.

W każdym bloku jest niewykorzystane miejsce; darmowe bloki 16 KB; bezpłatne „zakresy” (fragmenty nMB); Wiersze MVCC czekają na zbiór; węzły inne niż liście mają własne rozdrobnienie; itp.

Percona i Oracle mają różne sposoby patrzenia na to, jak duży (liczba bloków) jest indeks. Uważam, że żadne z nich nie jest przydatne ze względu na ograniczoną definicję „darmowy”. Wygląda na to, że bloki (16 KB każdy) są przydzielane we fragmentach (kilka MB), co prowadzi do przekonania, że ​​istnieje wiele rodzajów fragmentacji. W rzeczywistości jest to zwykle tylko jeden z tych fragmentów o wielkości wielu MB. I OPTIMIZE TABLEniekoniecznie odzyskuje przestrzeń.

Jeśli SQL Server używa BTrees, kłamstwem jest stwierdzenie, że „nie ma fragmentacji”. Pomyśl o tym, co dzieje się podczas „podziału bloku”. Albo pomyśl o narzutach związanych z ciągłą defragmentacją. Tak czy inaczej przegrasz.

Ponadto zauważ, że tabela i indeks są zasadniczo identycznymi strukturami:

• Drzewo B +, na podstawie jakiegoś indeksu
• „Dane” oparte są na kluczu podstawowym; każdy indeks wtórny jest drzewem B + na podstawie jego indeksu.
• Węzeł liścia „danych” zawiera wszystkie kolumny tabeli.
• Węzeł liścia indeksu dodatkowego zawiera kolumny tego indeksu dodatkowego oraz kolumny KLUCZA PODSTAWOWEGO.

Jeśli tak innodb_file_per_table = ON, możesz wyraźnie zobaczyć skurcz (jeśli występuje) po OPTYMALIZACJI TABELI, patrząc na .ibdrozmiar pliku. Dla OFF, informacja jest pochowany ibdata1, ale SHOW TABLE STATUSmoże być dość dokładne, ponieważ wszystkie „wolne” miejsce należy do każdego stołu. Cóż, z wyjątkiem wstępnie przydzielonych fragmentów.

Możesz zauważyć, że świeżo zoptymalizowana tabela plików na tabelę ma dokładnie 4M, 5M, 6M lub 7M Data_free. Ponownie, jest to wstępna alokacja i brak podania drobiazgowych szczegółów.

Współpracuję z InnoDB od ponad dekady; Pracowałem z tysiącami różnych stolików, dużych i małych. Mówię, że tylko jeden stół na tysiąc naprawdę potrzebuje OPTIMIZE TABLE. Używanie go na innych stołach jest marnotrawstwem.

105 kolumn to dużo, ale może nie za dużo.

Czy masz 55 indeksów na jednym stole? To jest złe. To 55 aktualizacji na INSERT. Omówmy to dalej. Pamiętaj, że INDEX(a)jest to bezużyteczne, jeśli masz również INDEX(a,b). I INDEX(flag)jest bezużyteczny z powodu niskiej liczności. (Ale INDEX(flag, foo)może być przydatne.)

P1: Nie ma dobrego sposobu na sprawdzenie wszystkich form fragmentacji w danych lub indeksach wtórnych.

Q2, Q3: OPTIMIZE TABLEodbudowuje tabelę o CREATEingnową tabelę i INSERTingwszystkie wiersze, a następnie RENAMEingi DROPping. Ponowne wstawienie danych w kolejności PK zapewnia, że dane są dobrze defragmentowane. Indeksy to inna sprawa.

Q4: Ty mógł DROP i reCREATEkażdy indeks go oczyścić. Ale to bardzo powolny proces. 5.6 ma pewne przyspieszenia, ale nie wiem, czy pomagają w defragmentacji.

Jest również możliwe ALTER TABLE ... DISABLE KEYS, wówczas ENABLEim. Ta maja do bardziej wydajny odbudowanie wszystkich indeksów średnich naraz.

Jak sprawdzić fragmentację określonego indeksu w MySQL, a nie tabelę jako całość

Przechodzić.

Czy OPTIMIZE TABLE naprawia fragmentację wewnętrzną / zewnętrzną indeksu jak w SQL Server?

Całkowicie odbudowuje tabelę i jej indeksy.

Kiedy optymalizuję tabelę w MySQL, czy faktycznie odbudowuje ona wszystkie indeksy w tabeli?

To to samo pytanie z tą samą odpowiedzią.

Czy realistyczne jest myślenie, że zmniejszenie fizycznej przestrzeni indeksu (bez przebudowywania samego drzewa) faktycznie przekłada się na lepszą wydajność?

Myślenie, że możesz zmniejszyć przestrzeń bez odbudowywania drzewa, nie jest realistyczne . Idą razem.