Jak obsłużyć dużą tabelę w MySQL?


Mam bazę danych używaną do przechowywania przedmiotów i właściwości tych przedmiotów. Liczba właściwości zwiększa się, więc istnieje scalona tabela zawierająca każdą właściwość skojarzoną z wartością elementu.
CREATE TABLE `item_property` ( `property_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`value` double NOT NULL,
PRIMARY KEY (`property_id`,`item_id`),
KEY `item_id` (`item_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Ta baza danych ma dwa cele: przechowywanie (które ma pierwszy priorytet i powinno być bardzo szybkie, chciałbym zrobić wiele wstawień (setki) w kilka sekund), pobieranie danych (wybiera za pomocą item_id i property_id) (to jest drugi priorytet , może działać wolniej, ale nie za bardzo, ponieważ zrujnuje to korzystanie z bazy danych).
W tej tabeli znajduje się obecnie 1,6 miliarda rekordów, a proste zliczanie może zająć do 2 minut ... Wkładka nie jest wystarczająco szybka, aby można ją było użyć.
Używam Zend_Db do uzyskiwania dostępu do moich danych i byłbym bardzo zadowolony, gdybyś ty

nie

zasugeruj mi rozwinięcie jakiegoś elementu pobocznego PHP.
Zaproszony:
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Jeśli z jakiegoś powodu nie możesz znaleźć rozwiązań korzystających z różnych systemów zarządzania bazami danych lub partycjonowania według klastra, możesz zrobić jeszcze trzy główne rzeczy, aby

radykalnie

poprawiają ich wydajność (i oczywiście działają w połączeniu z klastrami):
  • Konfigurowanie mechanizmu pamięci masowej MyISAM
  • Użyj polecenia „LOAD DATA INFILE nazwa pliku INTO TABLE nazwa tabeli”
  • Podziel swoje dane na wiele tabel

To wszystko. Przeczytaj resztę tylko jeśli interesują Cię szczegóły :)
Wciąż czytasz? OK, oto rzecz: MyISAM jest kamieniem węgielnym, ponieważ jest to najszybszy dostępny obecnie silnik. Zamiast wstawiać wiersze danych za pomocą zwykłych instrukcji SQL, należy spakować je do pliku i

wstaw ten plik
http://dev.mysql.com/doc/refma ... .html
w regularnych odstępach czasu (tak często, jak potrzebujesz, ale tak rzadko, jak pozwala na to aplikacja). W ten sposób możesz wstawiać w kolejności miliona wierszy na minutę.
Następną rzeczą, która Cię ogranicza, są Twoje klucze/indeksy.Gdy nie mieszczą się w Twojej pamięci (ponieważ są po prostu za duże), doświadczysz ogromnego spowolnienia zarówno w przypadku wstawiania, jak i zapytań. Dlatego dzielisz swoje dane na wiele tabel, wszystkie z tym samym schematem. Każda tabela powinna być tak duża, jak to tylko możliwe, bez zapełniania pamięci, gdy jest ładowana pojedynczo. Dokładny rozmiar zależy oczywiście od komputera i indeksów, ale powinien wynosić od 5 do 50 milionów wierszy/tabeli. znajdziesz to, jeśli po prostu zmierzysz czas potrzebny do wstawienia ogromnego zestawu wierszy po drugim, szukając momentu, w którym znacznie zwolni. Kiedy znasz limit, utwórz nowy stół w locie za każdym razem, gdy Twój ostatni stół zbliża się do tego limitu.
Konsekwencją rozwiązania multitable jest to, że musisz przeszukiwać wszystkie tabele, a nie tylko jedną, gdy potrzebujesz danych, co nieco spowolni twoje zapytania (ale nie za bardzo, jeśli masz miliard wierszy). Oczywiście jest tu również kilka optymalizacji. Jeśli istnieje coś fundamentalnego, którego możesz użyć do podzielenia danych (na przykład data, klient lub cokolwiek innego), możesz podzielić je na różne tabele przy użyciu pewnego rodzaju strukturalnego wzorca, który pozwala wiedzieć, gdzie są określone typy danych, nawet bez wykonywania zapytań w tabelach. Wykorzystaj tę wiedzę tylko do tworzenia zapytań w tabelach, które mogą zawierać żądane dane itp.
Jeśli potrzebujesz jeszcze większej personalizacji, przejdź do

podział
http://dev.mysql.com/doc/refma ... .html
zgodnie z sugestią Eineki i Oedo.
Ponadto, aby poinformować, że to nie są dzikie spekulacje, obecnie przeprowadzam podobne testy skalowalności na naszych własnych danych i takie podejście działa dla nas cuda. Każdego dnia udaje nam się wstawiać dziesiątki milionów wierszy, a zapytania zajmują ~ 100 MS.
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Przede wszystkim nie używaj InnoDb, ponieważ wydaje się, że nie potrzebujesz jego podstawowych funkcji w porównaniu z MyISAM (blokowanie, transakcje itp.).
Więc użyj MyISAM, to już zrobi różnicę.
Następnie, jeśli to nadal nie jest wystarczająco szybkie, przejdź do indeksowania, ale już powinieneś zauważyć radykalną różnicę.
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Wow, to całkiem duży stół :)
Jeśli potrzebujesz szybkiego przechowywania, możesz grupować swoje wkładki i wstawiać je za pomocą jednej wielokrotnej instrukcji INSERT. jednak na pewno będzie to wymagało dodatkowego kodu klienta (php), przepraszam!
INSERT INTO `table` (`col1`, `col2`) VALUES (1, 2), (3, 4), (5, 6)...

wyłącz również wszystkie indeksy, które nie są POTRZEBNE, ponieważ indeksy spowalniają wykonywanie poleceń wstawiania.
alternatywnie możesz przyjrzeć się partycjonowaniu swojej tabeli:

linky
http://dev.mysql.com/doc/refma ... .html
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Spójrz na memcache, aby zobaczyć, gdzie można go zastosować. Zwróć także uwagę na partycjonowanie poziome, aby zmniejszyć rozmiary tabel/indeksów.
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Po pierwsze: pojedyncza tabela z 1,6 miliardami rekordów wydaje się zbyt duża. Pracuję nad kilkoma dość dużymi systemami, w których nawet tabele dziennika, które śledzą całą aktywność, nie stają się tak duże przez lata. Dlatego, jeśli to możliwe, zastanów się, czy możesz znaleźć lepszą metodę przechowywania. Nie mogę udzielić więcej porad, ponieważ nie znam struktury bazy danych, ale jestem pewien, że będzie wystarczająco dużo miejsca na optymalizację. 1,6 miliarda rekordów to za dużo.
Kilka rzeczy o wydajności:
Jeśli nie potrzebujesz kontroli integralności referencyjnej, co jest mało prawdopodobne, możesz przełączyć się na silnik pamięci MyISAM. Jest nieco szybszy, ale brakuje mu integralności ckecków i transakcji.
Coś innego będzie wymagało więcej informacji.
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Czy rozważałeś taką możliwość

rozdzielać
http://dev.mysql.com/doc/refma ... .html
tabele na sekcje?
Anonimowy użytkownik

Anonimowy użytkownik

Potwierdzenie od:

Należy pamiętać, że domyślna instalacja MySQL nie jest skonfigurowana do tak ciężkiej pracy. Upewnij się, że ty

ustawić to
http://www.ibm.com/developerwo ... .html
dla Twojego obciążenia pracą.

Aby odpowiedzieć na pytania, Zaloguj się lub Zarejestruj się