Co jest bardziej wydajne: wiele tabel MySQL czy jedna duża tabela?


103

Przechowuję różne dane użytkownika w mojej bazie danych MySQL. Pierwotnie był ustawiony w różnych tabelach, co oznaczało, że dane są połączone z identyfikatorami użytkowników i wyprowadzane za pomocą czasami skomplikowanych wywołań w celu wyświetlenia i manipulowania danymi zgodnie z wymaganiami. Konfigurując nowy system, prawie sensowne jest połączenie wszystkich tych tabel w jeden duży zbiór powiązanych treści.

  • Czy to będzie pomoc czy przeszkoda?
  • Szybsze rozważania przy dzwonieniu, aktualizowaniu lub wyszukiwaniu / manipulowaniu?

Oto przykład niektórych struktur moich tabel:

  • użytkownicy - identyfikator użytkownika, nazwa użytkownika, adres e-mail, zaszyfrowane hasło, data rejestracji, ip
  • user_details - dane z plików cookie, imię i nazwisko, adres, dane kontaktowe, przynależność, dane demograficzne
  • user_activity - wkład, ostatnio online, ostatnie przeglądanie
  • user_settings - ustawienia wyświetlania profilu
  • user_interests - zmienne reklamowe, na które można kierować reklamy
  • user_levels - prawa dostępu
  • user_stats - trafienia, podliczenia

Edycja: Głosowałem za wszystkimi odpowiedziami do tej pory, wszystkie zawierają elementy, które zasadniczo odpowiadają na moje pytanie.

Większość tabel ma relację 1: 1, co było głównym powodem ich denormalizacji.

Czy wystąpią problemy, jeśli tabela obejmuje ponad 100 kolumn, podczas gdy duża część tych komórek prawdopodobnie pozostanie pusta?


To inne pytanie też może być pomocne
Mosty Mostacho

Odpowiedzi:


65

Wiele tabel pomaga w następujący sposób / przypadkach:

(a) jeśli różne osoby będą tworzyć aplikacje wykorzystujące różne tabele, warto je podzielić.

(b) Jeśli chcesz przyznać różne uprawnienia różnym osobom dla różnych części zbierania danych, wygodniej będzie je podzielić. (Oczywiście można przyjrzeć się definiowaniu widoków i odpowiednim nadawaniu im uprawnień).

(c) W przypadku przenoszenia danych w różne miejsca, szczególnie podczas programowania, może być sensowne użycie tabel, które skutkują mniejszymi rozmiarami plików.

(d) Mniejszy ślad może zapewnić wygodę podczas tworzenia aplikacji na określonym zbiorze danych pojedynczej jednostki.

(e) Jest taka możliwość: to, co myśleliście, jako dane o pojedynczej wartości, może w przyszłości okazać się naprawdę wieloma wartościami. np. limit kredytu jest od teraz pojedynczym polem wartości. Ale jutro możesz zdecydować o zmianie wartości na (data od, data do, wartość kredytu). Dzielone tabele mogą się teraz przydać.

Mój głos byłby na wiele tabel - z odpowiednio podzielonymi danymi.

Powodzenia.


3
@RohitKhatri: O ile wiem, posiadanie wielu tabel w większości przypadków zwiększa wydajność.
Hari Harker

1
@HariHarker Dzięki za odpowiedź, ale zorientowałem się, że to zależy od Twojego wzorca dostępu.
Rohit Khatri,

Do niedawna zawsze przechowywałem wszystkie dane w jednej tabeli, ale jeśli się nad tym zastanowić, dzielenie danych ma wiele zalet pod względem wydajności (w zależności od przypadku użycia kursu), semantyki (niektóre dane lepiej pogrupować w inna tabela) i rozwój. Na przykład opracowuję teraz niestandardowy system ERP na starszym systemie. Musiałem rozszerzyć stare tabele bazy danych o dodatkowe kolumny. Postanowiłem zrobić nowe tabele dla nowych danych. Niektóre nowe funkcje przydają się w starszym systemie i teraz mogę je łatwo zintegrować bez konieczności przepisywania zbyt wielu starych zapytań
Ogier Schelvis

35

Łączenie tabel nazywa się denormalizowaniem.

Może (ale nie musi) pomóc wykonanie niektórych zapytań (które robią dużo JOIN), aby działały szybciej kosztem tworzenia piekła konserwacyjnego.

MySQLjest w stanie używać tylko JOINmetody, a mianowicie NESTED LOOPS.

Oznacza to, że dla każdego rekordu w tabeli sterującej MySQLlokalizuje pasujący rekord w tabeli sterowanej w pętli.

Lokalizowanie płyty jest dość kosztowną operacją, która może zająć dziesiątki razy dłużej niż zwykłe skanowanie płyt.

Przeniesienie wszystkich rekordów do jednej tabeli pomoże ci pozbyć się tej operacji, ale sama tabela się powiększy, a skanowanie tabeli potrwa dłużej.

Jeśli masz dużo rekordów w innych tabelach, zwiększenie skanowania tabeli może przeważyć korzyści wynikające z sekwencyjnego skanowania rekordów.

Z drugiej strony piekło konserwacji jest gwarantowane.


1
Jeśli masz 10000 użytkowników i wykonujesz łączenie z bazą danych skonfigurowaną poprawnie z kluczami obcymi, powinieneś potrzebować tylko intensywnego wyszukiwania, wykonując coś takiego jak select * from users, gdzie name = "bob". Kiedy już masz boba, używasz indeksu, aby znaleźć połączone tabele do boba, co jest znacznie szybsze, ponieważ używasz identyfikatora boba. Dzieje się tak niezależnie od tego, czy wykonujesz łączenie w zapytaniu, czy odpytujesz bob, a następnie osobno odpytujesz tabelę. Oczywiście miejmy nadzieję, że twoje drugie zapytanie opiera się na identyfikatorze boba, a nie na czymś innym.
Rudy Garcia,

17

Czy wszystkie są związkami 1: 1? Chodzi mi o to, że jeśli użytkownik mógłby należeć, powiedzmy, do różnych poziomów użytkownika, lub jeśli interesy użytkowników są reprezentowane jako kilka rekordów w tabeli zainteresowań użytkownika, to natychmiastowe scalenie tych tabel byłoby wykluczone.

Odnosząc się do poprzednich odpowiedzi na temat normalizacji, należy powiedzieć, że reguły normalizacji bazy danych całkowicie zignorowały wydajność i dotyczą tylko schludnego projektu bazy danych. To często jest to, co chcesz osiągnąć, ale są chwile, kiedy warto aktywnie denormalizować się w pogoni za wydajnością.

Podsumowując, powiedziałbym, że pytanie sprowadza się do tego, ile pól znajduje się w tabelach i jak często są one dostępne. Jeśli aktywność użytkownika często nie jest zbyt interesująca, może być po prostu uciążliwe, aby zawsze mieć ją w tym samym rejestrze, ze względu na wydajność i konserwację. Jeśli niektóre dane, na przykład ustawienia, są używane bardzo często, ale po prostu zawierają zbyt wiele pól, scalanie tabel może nie być wygodne. Jeśli interesuje Cię tylko wzrost wydajności, możesz rozważyć inne podejścia, takie jak oddzielne przechowywanie ustawień, ale zapisywanie ich we własnej zmiennej sesji, aby nie trzeba było często wysyłać zapytań do bazy danych.


Zupełnie nie zgadzam się z twoim komentarzem, że normalizacja skupia się tylko na zgrabności i całkowicie pomija wydajność. W obu scenariuszach występuje kompromis, a denormalizacja w rzeczywistości zagraża integralności danych. Powiedziałbym, że normalizacja bazy danych faktycznie poprawia ogólną wydajność bazy danych zamiast szybkiego pomijalnego wzrostu wydajności ze zdenormalizowanej tabeli.
Rudy Garcia,

Biorąc pod uwagę, że dyskusja dotyczy w szczególności relacji 1: 1, dzielenie tabel nie jest zadaniem normalizacji , prawda? Jeśli nie ma zduplikowanych informacji, jest to normalne, nawet jeśli jest to pojedyncza tabela. (Cóż, może nie spełniać 3NFnormalizacji, więc skorzystaj z drugiej tabeli, aby rozwiązać ten problem, ale wydaje się, że nie jest to tym, co OP odnosi się do innych tabel.)
ToolmakerSteve

14

Czy wszystkie te tabele są 1-to-1powiązane? Na przykład, czy każdy wiersz użytkownika będzie miał tylko jeden odpowiadający wiersz w user_statslub user_levels? Jeśli tak, warto połączyć je w jedną tabelę. Jeśli jednak związek nie jest 1 to 1 , prawdopodobnie nie miałoby sensu ich łączenie (denormalizacja).

Jednak umieszczenie ich w osobnych tabelach w porównaniu z jedną tabelą prawdopodobnie będzie miało niewielki wpływ na wydajność, chyba że masz setki tysięcy lub miliony rekordów użytkowników. Jedyną prawdziwą korzyścią, jaką uzyskasz, jest uproszczenie zapytań poprzez ich połączenie.

ETA:

Jeśli obawiasz się zbyt wielu kolumn , zastanów się, jakich rzeczy zwykle używasz razem i połącz je , pozostawiając resztę w osobnej tabeli (lub w kilku oddzielnych tabelach, jeśli to konieczne).

Jeśli spojrzysz na sposób, w jaki korzystasz z danych, domyślam się, że około 80% zapytań wykorzystuje 20% tych danych, a pozostałe 80% danych jest wykorzystywanych sporadycznie. Połącz te często używane 20% w jedną tabelę i pozostaw 80%, których rzadko używasz w osobnych tabelach, a prawdopodobnie uzyskasz dobry kompromis.


Tak, każda tabela ma tylko 1 wiersz dla każdego użytkownika, aby zaoszczędzić sobie bólu głowy związanego z zarządzaniem wieloma zduplikowanymi danymi. Dlatego myślę, że jeden stół pasuje. Gdyby dane użytkownika obejmowały wiele wierszy, spodziewałbym się, że te tabele będą oddzielone od głównej tabeli użytkownika.
Peter Craig,

1
Jeśli każda tabela ma relację 1 do 1, łatwiej byłoby użyć jednej tabeli. W takim przypadku nie ma potrzeby dzielenia tabeli. Dzielenie tabeli sugeruje, że jest więcej niż 1 wiersz, co może prowadzić do przypadku, gdy inny programista potraktowałby je w ten sposób.
Richard L

Bardzo ciekawa myśl dotycząca zastosowania 80/20 do projektowania tabel bazy danych. Zastanawiałem się również nad projektem klas OOP (jestem przede wszystkim programistą Java) i zastanawiałem się, czy to samo może być tam skuteczne (umieść podstawowe 80% funkcjonalności aplikacji w jednej klasie, a resztę w innych klasach).
Zack Macomber

1
@ZackMacomber - Nie, podział klas powinien być oparty na lokalizacji odniesienia . Zaletą podziału na wiele klas jest narysowanie granicy wokół mniejszej jednostki funkcjonalności, tak aby łatwiej było zrozumieć / przetestować / zmienić i jasno określić, gdzie ta jednostka współdziała z innymi jednostkami funkcjonalności. Celem jest utrzymanie większości połączeń (referencji, połączeń) wewnątrz jednej jednostki, z kilkoma połączeniami między jednostkami . Zdefiniowanie kilku interfejsów implementowanych przez klasę, z różnymi interfejsami w każdym przypadku użycia, może być przydatnym pierwszym krokiem w kierunku podziału.
ToolmakerSteve

@ToolmakerSteve Dobre myśli +1
Zack Macomber

9

Tworzenie jednej ogromnej tabeli jest sprzeczne z podmiotami zarządzającymi relacyjnej bazy danych. Nie połączyłbym ich wszystkich w jeden stół. Otrzymasz wiele wystąpień powtarzających się danych. Jeśli na przykład twój użytkownik ma trzy zainteresowania, będziesz mieć 3 wiersze z tymi samymi danymi użytkownika tylko po to, aby zapisać trzy różne zainteresowania. Zdecydowanie wybierz metodę wielokrotnej „znormalizowanej” tabeli. Zobacz stronę Wiki w celu normalizacji bazy danych.

Edycja: zaktualizowałem moją odpowiedź, ponieważ zaktualizowałeś swoje pytanie ... Zgadzam się z moją początkową odpowiedzią jeszcze bardziej, odkąd ...

duża część tych komórek prawdopodobnie pozostanie pusta

Jeśli na przykład użytkownik nie miał żadnych zainteresowań, jeśli znormalizujesz, po prostu nie będziesz mieć wiersza w tabeli odsetek dla tego użytkownika. Jeśli masz wszystko w jednej ogromnej tabeli, będziesz mieć kolumny (i najwyraźniej wiele z nich), które zawierają tylko wartości NULL.

Pracowałem dla firmy telekomunikacyjnej, w której było mnóstwo tabel, a uzyskanie danych może wymagać wielu połączeń. Gdy wydajność odczytu z tych tabel była krytyczna, wtedy powstawały procedury, które mogły generować tabelę płaską (tj. Tabelę zdenormalizowaną), która nie wymagałaby łączenia, obliczeń itp., Na które mogłyby wskazywać raporty. Te były następnie używane w połączeniu z agentem serwera SQL do uruchamiania zadania w określonych odstępach czasu (tj. Tygodniowy widok niektórych statystyk był uruchamiany raz w tygodniu itd.).


Podoba mi się to podejście, ponieważ zdenormalizowane dane istnieją tylko tymczasowo, jako migawka z chwili. Brak problemów z wstawianiem / modyfikowaniem / usuwaniem - po prostu wyrzuć je po zakończeniu.
ToolmakerSteve

7

Dlaczego nie zastosować tego samego podejścia, co Wordpress, mając tabelę użytkowników z podstawowymi informacjami o użytkowniku, które każdy ma, a następnie dodać tabelę „user_meta”, która może być w zasadzie dowolną parą klucza i wartości powiązaną z identyfikatorem użytkownika. Więc jeśli chcesz znaleźć wszystkie metadane użytkownika, możesz po prostu dodać je do swojego zapytania. Nie zawsze będziesz musiał dodawać dodatkowe zapytanie, jeśli nie jest to potrzebne do takich rzeczy, jak logowanie. Korzyści płynące z tego podejścia pozostawiają również możliwość dodawania nowych funkcji dla użytkowników, takich jak przechowywanie ich uchwytów na Twitterze lub poszczególnych zainteresowań. Nie będziesz też musiał zajmować się labiryntem powiązanych identyfikatorów, ponieważ masz jedną tabelę, która rządzi wszystkimi metadanymi i ograniczysz ją do tylko jednego powiązania zamiast 50.

Wordpress robi to specjalnie po to, aby umożliwić dodawanie funkcji za pośrednictwem wtyczek, dzięki czemu projekt jest bardziej skalowalny i nie będzie wymagał całkowitego przeglądu bazy danych, jeśli musisz dodać nową funkcję.


wp_usermetaStół Wordpress rośnie geometrycznie. Każdy użytkownik dodaje X wierszy do wp_usermetatabeli, po jednym wierszu na każdą metainformację, którą chcemy zachować dla tego użytkownika. Jeśli zachowasz 8 niestandardowych pól dla każdego użytkownika, oznacza to, że wp_usermeta będzie miało users * 8długość wierszy. Wydaje się, że powoduje to problemy z wydajnością, ale nie jestem pewien, czy to jest problem, czy nie…
trzecia osoba

1
Widziałem, jak może to powodować problemy z wydajnością, jeśli masz dziesiątki tysięcy użytkowników. Zasadniczo baza danych musiałaby przeszukać 10000 * 8 wpisów w tabeli meta użytkownika, aby znaleźć te, których szukasz. Jeśli jednak odpytujesz dane Meta tylko wtedy, gdy są potrzebne, myślę, że twoja wydajność byłaby lepsza. Jeśli zawsze pytasz o metadane, nawet jeśli ich nie potrzebujesz, możesz mieć problemy. Jeśli zawsze potrzebujesz metadanych, być może dzielenie tabel nie jest najlepszym podejściem.
Rudy Garcia

1
Jeszcze wczoraj mieliśmy do czynienia z motywem WP, który ładował wszystkich użytkowników (używając get_users()) tylko do obliczenia paginacji. Gdy poprawiliśmy kod, aby SELECT COUNT(…)zamiast tego używał zapytania do paginacji, czas ładowania strony wzrósł z 28 sekund do około 400 ms. Nadal zastanawiam się, jak wypada porównanie wydajności z połączonymi tabelami lub pojedynczym płaskim stołem… Mam problem ze znalezieniem jakichkolwiek metryk wydajności w Internecie.
trzecia osoba

Mając na uwadze mój poprzedni komentarz, mogłoby się wydawać, że dzielenie tabeli jest nadal efektywne, chyba że z jakiegoś powodu, takiego jak powyższy przykład paginacji, należałoby zaznaczyć wszystkich użytkowników. Chociaż jeśli pobierasz wszystkie metainformacje, nadal będziesz mieć 80k wpisów w tabeli usermeta. To dużo do przeszukania. Być może ktoś mógłby przetestować lepsze podejście, uruchamiając skrypt na obu implementacjach i uruchamiając go 100 razy, aby uzyskać średnią, może po prostu to zrobię.
Rudy Garcia

1
Przeczytałem to jeszcze raz dzisiaj i zdałem sobie sprawę, że mój komentarz dotyczący 10000 * 8 wpisów jest prawdziwy, jednak sposób działania bazy danych powinien w większości sprawić, że nie będzie to problem. Jeśli z jakiegoś powodu złapałeś wszystkich 10000 użytkowników ORAZ także ich metainformacje, byłoby to śmieszne. Nie przychodzi mi do głowy żaden scenariusz, w którym byś tego chciał. Baza danych z łatwością pobierze meta dla pojedynczego użytkownika z prędkością błyskawicy dzięki kluczom obcym i indeksowaniu. Zakładając, że twój model db jest poprawnie skonfigurowany.
Rudy Garcia

5

Myślę, że to jedna z tych sytuacji „to zależy”. Posiadanie wielu tabel jest czystsze i prawdopodobnie teoretycznie lepsze. Ale kiedy musisz dołączyć do 6-7 tabel, aby uzyskać informacje o pojedynczym użytkowniku, możesz zacząć przemyśleć to podejście.


1

Powiedziałbym, że zależy to od tego, co naprawdę oznaczają inne tabele. Czy user_details zawiera więcej niż 1 użytkownika więcej / users i tak dalej. To, jaki poziom normalizacji najlepiej odpowiada Twoim potrzebom, zależy od Twoich wymagań.

Jeśli masz jedną tabelę z dobrym indeksem, prawdopodobnie będzie to szybsze. Ale z drugiej strony prawdopodobnie trudniejsze w utrzymaniu.

Dla mnie wygląda na to, że możesz pominąć User_Details, ponieważ prawdopodobnie jest to relacja 1 do 1 z użytkownikami. Ale reszta to prawdopodobnie dużo wierszy na użytkownika?

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.