Bardzo duża tablica

Nie znam się na bazach prawie wcale, raczej nie lubię ich, ale z przymusu muszę pewien problem rozwiązać.

Mam taką bazę do zrobienia, nie będę całkiem jej opisywał ale weźmy na przykład dwie tabele, które budową przypominają mój problem.

Niech to będzie na ten przykład często eksploatowany przykład wypożyczalni.

Pierwsza tabela to niech będzie Klient
i niech ma między innymi pole klient_id.

Druga tabela to Książka
i oczywiście ma pole ksiazka_id

Trzecie tabela to Wypozyczenia
i oczywiście ma między innymi pola klient_id i ksiazka_id referujące z tabelami Książka i Klient.

Teraz dajmy na to jeżeli książka jest wypożyczana przez klienta to to informacja o tym razem z numerami id, datami itd wrzucana jest do tabeli Wypozyczenia. Chcę trzymać naprawdę sporą historię.

Teraz główny problem. Mam wątpliwość czy coś takiego będzie hulać z tego względu że tabela Wypożyczenia zgodnie z przewidywaniami po pewnym czasie osiągnie np 48 milionów rekordów. I cały właśnie mój problem to pytanie czy takie coś będzie w miarę wydajne? Wydajne w sensie czy selecty później nie będą trwać przez 30 sekund np. Jaka jest granica tych rozmiarów?

jaka baza, jaki serwer, struktura tabeli, INDEKSY. Można tak spierdolić strukturę, że z tabeli 1k rekordów zapytanie będzie wybierało dane 10min a można zrobić tak, że z tabeli 1kk rekordów dane będą wybierane w sekundę

mySQL, serwer nie wiadomo jeszcze, średni.

Struktura powiedzmy taka jak mówię, Książka ma pola: id i oprócz tego z 10 innych pól
Klient to samo,
Wypożyczenie ma obydwa id tamtych tabel i jeszcze z 5 pól.

Jeszcze jedno pytanie, powiedzmy że jakoś zniosę czas selecta na poziomie sekund ale czy wielkość tabeli wpływa na czas inserta do niej?

flakon napisał(a)

mySQL, serwer nie wiadomo jeszcze, średni.

Struktura powiedzmy taka jak mówię, Książka ma pola: id i oprócz tego z 10 innych pól
Klient to samo,
Wypożyczenie ma obydwa id tamtych tabel i jeszcze z 5 pól.

Jeszcze jedno pytanie, powiedzmy że jakoś zniosę czas selecta na poziomie sekund ale czy wielkość tabeli wpływa na czas inserta do niej?

Wydaje mi się że na czas inserta wpływa tylko liczba aktywnych indeksów dla tej tabeli i przy dzisiejszych prędkościach komputerów można osiągnąć setki i tysiące insertów na sekundę. Select będzie błyskawiczne jeśli tylko zrobisz indeksy na wszystkich polach, które występują w części "where" zapytania SQL.

Jak mówiłem, nie jestem w temacie baz więc jeżeli moje pytanie ma bardzo niski poziom to możecie śmiało odesłać mnie do diab... wróć. do googli.

Więc rozumiem że dobry select to taki który posiada jakieś id w sobie czyli taki select:

select * from Wypozyczenia where id_ksiazki == x and id_klienta == y and data_wypozyczenia == z

Czy może chodzi o selecta który ma w sobie id - klucz tabeli z której wybiera czyli musiałbym nałożyć jakiś klucz na tabelę Wypozyczenia i po nim wybierac, ale to mi nie pasuje oczywiście.

Nie, nikt tu nie mówi o żadnych kluczach. Chodzi o to zeby założyć indeksy dla pól po których będziesz szukał. Jeśli uważasz ze najczęściej będziesz puszczał select po dacie lub po id_klienta to na te pola zakładasz indeksy i już.

aha już wszystko jasne. Pomyliłem klucze z indeksami. Dzięki za help

to wszystko to nie do końca tak. O ile baza z rekordami idąca w dziesiątki tysięcy, (WAŻNE) dobrze zaprojektowana i przemyślana, nie potrzebuje zazwyczaj (albo po prostu system działa na tyle wydajnie) żeby cokolwiek w niej zmieniać, to baza, gdzie są tabele po milion rekordów, które są dość ważne dla systemu, potrzebuje już osoby, która się nią zajmie. Tutaj już trzeba mocno optymalizować zapytania i indeksy (nie mówię, że baza ze 100 rekordami w tabeli nie potrzebuje - tutaj jest to po prostu już wymagane aby system działał sprawnie). Tu nie wystarczy dołożyć kolejny procesor do serwera lub dodać ramu. Jak chcesz mieć wydajny system to trzeba o niego dbać, opiekować się nim jak dzieckiem. Dobrzy DBA mają stawki nie biorące się z kosmosu - dobry DBA potrafi przyśpieszyć wykonywanie jakiegoś zestawienia parę(naście) razy po uprzednim prześledzeniu statystyk i analizie planu wykonania. Zauważ, że jeśli przy 100 rekordach optymalny był full scan na tabeli, to już przy 100000 bardziej optymalny będzie indeks. Baza żyje, zmieniają się dane na których pracuje program i samą strukturę (głównie indeksy) oraz same zapytania trzeba dostosować do istniejących warunków. Rzadko kiedy jest tak, że choćby ten kto projektuje bazę był geniuszem, że projekt początkowy, tworzony wg jakichś założeń i zakładający docelowo konkretny rząd wielkości był po np. dwóch latach nadal tak samo dobry jak na początku.
BTW zawsze dziwiło mnie jak to jest, że zamiast wziąć kogoś, kto ma pojęcie o bazach, ludzie wolą zlecić projekt komuś, kto o temacie nie ma pojęcia a dopiero potem (jak parę k rekordów już jest) jest lament i płacz, że proste zestawienie wykonuje się 30min ...

@Misiekd bo sie ludziom wydaje że tak będzie taniej. A że potem płacą grubą kasę innej firmie za naprawienie tej sytuacji to już inna sprawa ;]

No dobrze, na pewno firma po czasie zleci obsługę bazy odpowiedzialnej osobie ale na start musi wystarczyć to co robię ja.

Żeby była jasność to ta tabela zawierająca 1mln rekordów ma zawierać bardzo mało danych, ot dwie daty, dwa numery id, i jeden int.

Ok już mam jakiś pogląd na to wszystko, powiedzcie mi jeszcze jedną rzecz, którą Mariusz J próbował wyjaśnić ale nie jest pewien :) mianowicie, select działający długo będzie wkurzał i zmobilizuje ludzi do zadbania o bazę, natomiast długo działający insert spowoduje w tym przypadku wyje*** się systemu bo inserty mają nadchodzić masowo. Więc powiedzcie mi czy wielkość tabeli ma wpływ na szybkość insertów czy mniej? I czy można jakiś priorytet ustawić żeby nigdy chodzący 5 minut select nie zablokował mi inserta?

Nie wydaje mi się żeby na tak prostej bazie można było wykonać jakieś skomplikowane zapytania które się długo wykonują. Zawsze ograniczaj na sztywno maksymalną liczbę wyświetlanych rekordów żeby select nie zwrócił na przykład dziesięć tysięcy rekordów. W razie bardziej skomplikowanych zapytań, są książki do SQL i przykłady przyspieszania niektórych zapytań jak "zastępowanie podzapytań złączeniami" itp. Select nigdy nie zablokuje inserta, bo select tylko odczytuje dane. Konflikt transakcji następuje, gdy jest robiony update na tej samej kolumnie tego samego rekordu jednocześnie przez więcej niż jednego użytkownika. W takiej sytuacji transakcja powodująca wyjątek jest "wycofywana" i czeka się kilka milisekund (losowy odstęp czasu) i powtarza transakcję aż zastąpimy transakcje równoległe (zachodzące na siebie) transakcjami szeregowymi (jedna po drugiej).

Ostatnie pytanie, szkoda nowy temat zakładać bo to pytanie na pewno banalne i śmieszne :P

Jeżeli potrzebuję wyciągnąć 10k rekordów to lepiej zrobić selecta który wypluje 10k rekordów czy może lepiej zrobić 10k selectów które wyplują po jednym rekordzie?

selecty nie lockuja tabel, stron, kluczy
insert polega na wstawieniu danych oraz dodaniu informacji do indeksu, czasem jego reorganizacji
insert robi blokady, ktore moga zablokowac selecty
wiec jak juz pisano duza liczba indeksow bedzie powodowala zwolnienie insert
z kolei brak indeksow, spowolni selecty
duza liczba rekordow + selecty wybierajace odlegle od siebie rekordy (ulozone na roznych stronach) moga zdecydowanie zwolnic
lub selecty nie uzywajace indeksow
ale jesli selecty beda wybieraly male porcje danych blisko siebie polozonych oraz beda robity to za pomoca indeksow, powinno byc ok
nalezy sobie tez zadac pytanie ile danych historycznych bedzie potrzebnych w codziennej pracy, moze wystarczy czesc starych rekordow co jakis okres czasu (co tydz., co dzien) przenosic do innej archiwalnej tabeli i jezeli bedzie to konieczne siegac do niej
najlepiej zrob jakies wstepne testy struktury bazy jaka ci potrzebna i przekonaj sie czy ilosc danych bardzo spowolni operacje

zdecydowanie jeden select wyciagajacy 10k rekordow
samo polaczenie z baza danych najczescie jest najkosztowniejsza czescia wykonywania zapytania z kodu aplikacji
ale generalnie wybieranie 10k rek. wydaje sie niepotrzebne, moze lepiej pobierac mniej i stronicowac dane

massther napisał(a)

selecty nie lockuja tabel, stron, kluczy

Jesteś tego absolutnie pewien?

massther napisał(a)

zdecydowanie jeden select wyciagajacy 10k rekordow
samo polaczenie z baza danych najczescie jest najkosztowniejsza czescia wykonywania zapytania z kodu aplikacji
ale generalnie wybieranie 10k rek. wydaje sie niepotrzebne, moze lepiej pobierac mniej i stronicowac dane

Będę potrzebował wybierać spore paczki historycznych danych, Ta tabela którą nazwałem Wypozyczenia w realu będzie zawierać opis działań konkretnych ludzi, tzn każde jedno możliwe działanie pracownika ląduje do bazy, nie pytajcie po co, jest to potrzebne i tyle ;)
Czyli chcę taki efekt mieć:
Pracownik numer 1, od 11-07-2010 9:24 do 9:29, siedział na tyłku.
Pracownik numer 2 od 11-07-2010 9:01 do 9:12, spał

Więc jak widać w zależności od częstotliwości zmian "stanów" i ilości pracowników może być bardzo wielka tabela. No i właściwie codziennie chcę wybierać z tej tabeli dla każdego usera, np ostatnie 6 tygodni pracy. Więc chcąc nie chcąc będzie tego dużo.

W takim wypadku pamietaj, ze indeks jest wiecej wart jesli jest selektywny. Innymi slowy - jesli tabela ma 1m wierszy i kolumne z 2 wartosciami to indeks cudow nie zdziala. Silnik i tak musi wybrac np. polowe wierszy, bo tyle pasuje do wyszukiwanej wartosci. Idealnym jest indeks unikalny, wtedy szukasz jednej konkretnej wartosci a z nia wiersza. Oczywiscie nie zawsze sie da. W przypadku indeksu wielokolumnowego definiuje sie go od najbardziej selektywnej kolumny do najmniej. Efekt jest taki, ze np. w przypadku 3 warunkow w where najpierw szukasz w indeksie wartosci nr 1, otrzymujesz np. 5 wierszy i dla tych 5 wierszy sprawdzasz warunek drugi, ktory dalej ogranicza ilosc wierszy i w koncu 3. Na odwrot byloby np. warunek3 -> wynik : 10k wierszy, warunek 2 -> 1k wierszy, warunek 1 -> 3 wiersze.

Jesli w tej tabeli, ktora podajesz masz np. historie z ostatnich 10 lat i szukasz wartosci dla danego dnia, to wyszukanie po zaindeksowanej kolumnie z data mocno ogranicza ilosc wierszy w wyniku, czyli indeks jest mocno selektywny. Pytanie wtedy czy bardziej selektywny jest indeks po id pracownika czy dacie. Tabela pewnie bedzie zawierac dane dla np. 100 pracownikow i dane dotyczace dnia. Prawdopodobnie indeks po dacie najpierw bedzie bardziej efektywny, bo dla danego dnia otrzymasz nilosc_pracownikow wierszy, gdzie n to ilosc danych na dzien na pracownika. Szukajac po pracowniku najpierw otrzymasz nilosc_dni_zarejestrowanych, ktora to wielkosc bedzie a) wieksza niz poprzednia b) bedzie przyrastac. To drugie oznacza, ze z czasem indeks bedzie mniej selektywny.

Rozumiem, że te 10000 rekordów nie będzie do wyświetlania dla użytkownika, tylko do przeglądania sekwencyjnego przez program i dokonywania na nich odpowiednich automatycznych zmian. Być może lepiej byłoby zrobić pętlę zapytań z których każde dotyczy tylko jednego dnia. W testach powinno wyjść czy tak jest szybciej i mniej zasobów pamięciowych zajęte.

somekind napisał(a)

massther napisał(a)

selecty nie lockuja tabel, stron, kluczy

Jesteś tego absolutnie pewien?

pisze w kontekscie MS SQL Server
selecty zakladaja locki tylu S i IS
IS na tabele, S na strony, chyba tez na klucz
inserty zakladaja na na tabele i strone IX, a na klucz X
(daruje rozwazania na temat isolation level)
wiec w najgorszym razie insert na ulamek sekundy zostanie zablokowany kiedy select zablokuje strone do ktorej ma byc dostawiony rekord

massther napisał(a)

(daruje rozwazania na temat isolation level)

[rotfl]

Ale dlaczego?
W zależności od poziomu izolacji transakcji operacje selekcji mogą blokować inserty. I trzeba brać to pod uwagę.

bo malo kto z poczatkujacych wie co to jest i jak uzyc i zazwyczaj uzywa domyslnego, ktory zdaje sie ze jest read committed, w ktorym to co napisalem jest prawda

somekind wybacz moje niechlujstwo, nastepnym razem bedzie pieknie wstep, rozwiniecie, zakonczenie i obrazki beda :D

Masz rację z tymi początkującymi, ale autor pyta o "duża tablicę", co już takie początkujące nie jest. Warto zwrócić na to uwagę, bo a nuż widelec okaże się, że w bazie jest ustawiony niedomyślny serializable i wydajność bazy będzie bliska zeru. ;)

Wybacz moje czepialstwo, ale miałem o to pytanie na obronie inż., więc ten temat jest mi wyjątkowo bliski. ;p

somekind napisał(a)

massther napisał(a)

(daruje rozwazania na temat isolation level)

[rotfl]

Ale dlaczego?
W zależności od poziomu izolacji transakcji operacje selekcji mogą blokować inserty. I trzeba brać to pod uwagę.

Chyba najbezpieczniej jest w takim razie ustawić poziom izolowania transakcji snapshot (maksymalny).