Dzielenie tabel w MySQL. Dobra praktyka?

Rozpocząłem pracę nad istniejącym projektem, a poprzedni programista podzielił tabelę na 10 oddzielnych tabel o identycznych schematach, ale z różnymi danymi.

Tabele wyglądają następująco:

[tableName_0]
[tableName_1]
[tableName_2]
[tableName_3]
[tableName_4]
[tableName_5]
[tableName_6]
[tableName_7]
[tableName_8]
[tableName_9]

Klucz podstawowy jest idpolem całkowitym . Aplikacja korzysta z algorytmu skrótu ( idmod 10), aby wiedzieć, do której tabeli można uzyskać dostęp podczas wyszukiwania. Na przykład id= 10 spowoduje [tableName_0].

W połączeniu tabele mają prawdopodobnie 100 000 wierszy, a tempo wzrostu jest stosunkowo niskie.

Moje pytanie brzmi więc, czy jest to realne rozwiązanie, czy nawet jest dobrą praktyką w każdej sytuacji. Moją teorią jest dążenie do ich połączenia, ponieważ ułatwi to wszystko, aż do UNIONs, itp. Główną wadą jest zmiana całego kodu aplikacji i to, czy w ogóle warto na dłuższą metę.

Myślę, że wszyscy to nadmiernie komplikują. Kluczową kwestią jest tutaj:

W połączeniu tabele mają prawdopodobnie 100 000 wierszy, a tempo wzrostu jest stosunkowo niskie.

Jest to bułka z masłem dla każdego RDBMS do obsługi. Idź z jedną tabelą, indeksuj ją poprawnie i uważaj za rozwiązany problem.

Nie musisz brać pod uwagę partycjonowania, czy to „domowego”, czy w inny sposób, dopóki nie zaczniesz obsługiwać bardzo dużych ilości danych - pomyśl miliardy wierszy i więcej.

Możesz używać tabel scalania, jednak są one bardziej przestarzałe od wersji 4.x. Biorąc pod uwagę, że twoja aplikacja jest ręcznie podzielona na partycje, ponieważ albo a) używasz naprawdę starej wersji, albo b) pierwotny programista nie wiedział o partycjach tabel.

Krótko mówiąc, jeśli korzystasz z wersji 5.1+, możesz pozwolić mysql wykonać tę partycjonowanie za ciebie. Zobacz http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Jeśli używasz wersji 5.5, powinieneś sprawdzić te konkretne dokumenty, ponieważ zauważysz pewne różnice.

Partycjonowanie ma wiele zalet. Jednak tak naprawdę zależy to od zestawu danych, wzorców dostępu i sposobu jego indeksowania. Pamiętaj też, że moje poniższe komentarze są w kontekście partycjonowania mysql 5+, a NIE starszych tabel mysql Merge; chociaż czasem są one omawiane w kategoriach partycji.

Kilka przykładów:

• Prosty segment (lub skrót) oparty na często dostępnym kluczu wyszukiwania. Jeśli prawie zawsze patrzysz na klucz podstawowy lub inny unikalny klucz, mysql może zmniejszyć przestrzeń wyszukiwania o współczynnik, ile razy masz partycje. Należy jednak pamiętać, że może to być szkodliwe, jeśli partycjonujesz według jednego klucza, a następnie często wyszukujesz według innego klucza. Jeśli wyszukujesz według klucza, dane nie są podzielone na partycje, to musi WIĘCEJ wyszukiwań podczas wyszukiwania (jedno dla każdej partycji, b / c szczerze mówiąc, nie wie, gdzie są dane)
• Rozważmy sytuacje, w których masz tymczasowy zestaw rekordów rosnących według daty i okresowo przycinasz poprzedni miesiąc. Jeśli partycjonujesz według daty, możesz po prostu usunąć partycję, która jest tak samo szybka jak upuszczenie tabeli, bez względu na to, jak duża. Jeśli przycinasz taką tabelę według dat, musisz wydać jedno lub więcej zapytań DELETE, w których usuwany jest każdy wiersz. Wadą tego jest to, że mysql nie tworzy automatycznie nowych partycji po osiągnięciu maksymalnej daty uwzględnionej w tym scenariuszu; potrzebujesz dodatkowych skryptów konserwacyjnych zbudowanych z twojej strony, aby dodawać partycje według potrzeb.
• Jeśli korzystasz z usługi myisam, kontrole i odzyskiwanie są znacznie szybsze. Zastanów się nad stołem myisam 100G. Jeśli chcesz odzyskać zepsuty stół, potrzebujesz co najmniej 100 G wolnego miejsca na dysku. Jeśli został on podzielony na 10 różnych części o równej wielkości, potrzebujesz tylko 10 G miejsca (i mniej pamięci buforowej key_sort_buffer do szybkiego odzyskiwania); ale musiałby wykonać iterację dla każdej partycji.

Podsumowując, ogólne podejście do partycjonowania tabel może przynieść wiele korzyści. Jednak nie jest to magiczna kula stosowana na ślepo, bez uwzględnienia wzorów dostępu i tego , jak dokładnie dzielisz.

Mogę sobie wyobrazić sytuacje, w których pożądane partycjonowanie jest bardzo specyficzne dla aplikacji i lepiej by było, gdyby logika ta znajdowała się w warstwie aplikacji. Jednak biorąc pod uwagę opis modułu prostego 10, nie wygląda to na taki przypadek.

EDYTOWAĆ

Pisząc mój opis, zapomniałem, że podałeś, że twoja tabela ma 100 tysięcy wierszy. Bez pełnego schematu tabeli i średniej długości wiersza trudno jest z całą pewnością stwierdzić, ale ogólnie rzecz biorąc, to brzmi średniej wielkości, nawet w przypadku skromnego sprzętu. Jednocześnie, jeśli nie powoduje problemów tak, jak jest teraz lub w dającej się przewidzieć przyszłości, nie marnuj czasu i nie narażaj się na ryzyko, zmieniając je.

To, co zrobił dla ciebie poprzedni programista, to zbudowanie własnej implementacji partycjonowania według skrótów. MySQL dosłownie obsługuje to natywnie z MySQL 5.1:

http://dev.mysql.com/doc/refman/5.1/en/partitioning-hash.html

Nie mogę wymyślić żadnego dobrego powodu, więc zaimplementuj własny partycjonowanie według mieszania zamiast polegać na natywnej wersji [1]. Wprowadzanie zmian w schemacie będzie koszmarem.

Rzadko zalecam także partycjonowanie według skrótu (implementacja natywna). Myślę, że byłoby użyteczne, gdybyś mógł użyć go do równoległego przeszukiwania każdej partycji naraz (czego nie zrobi MySQL). Jeśli musisz przeszukiwać wiele partycji, opisany schemat często będzie znacznie wolniejszy.

[1] Jednak w przypadku niektórych innych typów partycjonowania sensowne może być utworzenie własnego partycjonowania. MySQL zmusza cię do włączenia klucza partycji do klucza podstawowego i wszystkich unikalnych indeksów.

W odpowiedzi na pytanie:

jest to, czy jest to realne rozwiązanie

IMHO, To wydaje się niepotrzebnym narzutem. Możesz po prostu poprawnie zindeksować i podzielić jedną tabelę, chyba że w opisie ujawniono inne informacje.

W odpowiedzi na pytanie:

... jeśli jest to dobra praktyka w każdej sytuacji

IMHO, dzielenie pionowe może mieć sens w zależności od kontekstu. Kiedy to widzę, zwykle jest to jakiś dziennik. Udawajmy, że używamy tego do dzienników serwera WWW i chcemy podzielić na partycje według miesięcy. Zamiast zmieniać istniejącą tabelę na miejscu, możemy codziennie tworzyć nową tabelę i rejestrować wiersze w tej tabeli.

np. Udawaj, że tabela dziennika sieci może przyjąć postać:

datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
uri VARCHAR(1024),
host VARCHAR(255),
user_agent VARCHAR(255),
etc...

Twoje rozwiązanie tworzy tabele w razie potrzeby w bazie danych blogów:

weblogs.20120301
weblogs.20120302
weblogs.20120303

itp.

W ten sposób dane są możliwe do utrzymania i wyszukiwania. Ekstrakcja staje się normalnym procesem okresowym. Ciągłe operacje nie są blokowane przez operacje na starszych danych.

W przedstawionym scenariuszu i tak jesteś zamknięty w strukturze, więc dlaczego nie użyć pojedynczej tabeli zoptymalizowanej do tego celu? Przechowywanie wierszy na podstawie algorytmów wydaje się szkicowe i podatne na błędy.

Jeśli zapytanie skierowane jest do ogromnej ilości danych, podział danych według warunków zapytania znacznie poprawi wydajność. Ale taki podział, jak widzieliście, powoduje pewne problemy programistyczne.

Pytanie brzmi: czy ten podział jest wart wydajności, czy może szkodzi wydajności?

Jeśli masz transakcję, która musi zablokować wiele wierszy w kilku tabelach i występują w niej problemy (na przykład zakleszczenie lub przekroczenie limitu czasu transakcji), możesz połączyć je w jedną tabelę i przepisać kod SQL, aby naprawić problemy.

Kiedy zastanawiałem się, czy podzielić tabelę, zastanawiałem się nad kompromisem między wzrostem wydajności a złożonością programowania.

W twojej sytuacji modyfikacja istniejącego kodu może być rozwiązaniem długoterminowym, aby ułatwić utrzymanie kodu. Proponuję spróbować metaprogramowania. Na przykład przy użyciu StringTemplate do dynamicznego generowania SQL. Lubię generować SQL z silnika metaprogramowania, jeśli modyfikacja istniejącego kodu jest zbyt trudna.

Gdy musisz przechowywać pliki w tabeli, użycie tej metodologii pomaga eksportować, naprawiać i przywracać.

Mam tabele z> 30 Gb podzielonymi na 10 tabel. Te tabele mają tylko ID-BLOB i dla mnie łatwo je zachować. I używam MyISAM do zapisywania bufora INNODB.