[MySQL] Tabele po 1 mln rekordów a szybkość operacji

0

Witam

Borykam się z problemem dużych tabel. Przy 1 000 000 rekordów wszelkie operacje wybierania (po polu innym niż index) czy sortowania (to mniej ważne) trochę trwają. Jedynym zabiegiem, jaki robiłem, to rozbijanie dużej tabeli na kilkanaście małych, ale to bez sensu IMO. Przeciez są fora czy inne skrypty, które radzą sobie z tabelami po kilka milionów postów. Jaki trik trzeba zastosować, żeby zapytanie trwało krócej, działało szybciej?

Mam przykładową tabelę:
id (index) | nazwa (varchar(20)) | x (int)
Wybieram po polu x (do kilkuset rekordów). Zapytanie potrafi trwać od 2,5 do 5 sekund.

Z góry dziękuję za odpowiedź

0

Pomaga zakładanie dodatkowych indeksów, partycjonowanie (!), zmiana zapytań, czasem, gdy to konieczne, rezygnacja z postaci 'normalnej', na przykład powielanie danych, tabele pomocnicze, triggery do drobnych operacji itp.

0

a może podwójne zapytania?
Nie wiem czy to ma sens, ale pobierasz pierwszych 10k rekordów i z nich wybierasz co cie interesują, potem nastepne 10k itd.. aż do końca.

Ciekawe jakby to było z czasem... może tabele tymczasowe (temp)?

0

no a czego się spodziewasz wybierając jakieś dane z pola, na którym nie ma indeksu??? Przecież baza wtedy musi przejechać CAŁĄ tabelę, WSZYSTKIE rekordy i wybrać np. 5. Myśl trochę jak coś robisz a nie zadajesz głupie pytania. Sam napisałeś

wszelkie operacje wybierania (po polu innym niż index) czy sortowania (to mniej ważne) trochę trwają
no <ort>tosz </ort>chyba się wniosek sam nasuwa - czy to takie trudne??? No i na koniec może by tak <ort>najpier </ort>trochę teorii na temat tego co się robi najpierw wypadało przyswoić...

0

zamiast varchar(20) można użyć char(20) - spowoduje to że rekory będą miały stałą długość a zatem szybsze będzie przeskakiwanie po rekordach bo baza nie będzie musiała obliczać dla każdego rekordu offsetu o jaki się przesunąć dalej w pliku żeby dojść do indeksu kolejnego rekordu tylko zawsze przemieści się o stałą wartość - mniej obliczeń.

Do stałych długości rekordów powinno się dążyć w celu zwiększenia szybkości. Jednakże dane zawsze będą zajmować 20 bajtów, nawet jak wpiszesz 1 znak to zapisane zostanie 20 (1 twój i 19 pustych) - jak masz dużo miejsca i chcesz szykość zwiększyć to zrób na char(20), jak chcesz mniej miejsca ale trochę wolniej to varchar

0

ludzie ale co Wy tu ze herezje piszecie - choćbyście się (brzydko mówiąc) zesrali to bez indeksów nie przyśpieszycie. Nawet jakby tam było pole Char(1) to i tak za każdym razem baza MUSI przejechać całą tabelę!!!!

Nie mam MySQLa pod ręką ale na postgresie (który podobno jestwolniejszy od MySQLa) wygląda to tak:

Tabela

CREATE TABLE "public"."tab1" (
  "id" SERIAL, 
  "tekst" VATCHAR(20),
  "kwota" REAL, 
  CONSTRAINT "pk_id" PRIMARY KEY("id")
) WITH OIDS;
SELECT COUNT(*) FROM tab1 

Count=1000000
1 rows fetched (5,11 sec)

Zapytanie z filtrowaniem po polu z indeksem

SELECT Count(*) FROM tab1 WHERE id < 56367 and id > 54310

Count=2056
1 rows fetched (0,02 sec)

zapytanie po polu bez indeksu

SELECT Count(*) FROM tab1 WHERE kwota > 310 and kwota < 320

Count=19848
1 rows fetched (1,03 sec)

i po dodaniu indeksu na t pole
Count=19848
1 rows fetched (0,28 sec)

a teraz na polu tekstowym
bez indeksu

SELECT Count(*) FROM tab1 WHERE tekst > '310' and tekst < '320'

Count=11022
1 rows fetched (15,33 sec)

z indeksem
Count=11022
1 rows fetched (0,41 sec)

każde zapytanie wykonywałem kilka razy, a czasy to są czasy średnie

dane wyglądają mniej więcej tak
id tekst kwota
3000031 99749130802112200000 470,37
3000032 56609964210824100000 198,79
3000033 67387188350496400000 69,68
3000034 55277758629656300000 383,52
3000035 44398726357332800000 340,89
3000036 81734853741589400000 226,65
3000037 71300751609402100000 332,52
3000038 58786406954185300000 348,98
3000039 1260413462883050000 250,69
3000040 7402625404020130000 288,16

Kumacie już po co są indeksy?

0

Jeżeli chodzi o mnie to ja doskonale wiem po co są indeksy :P Ale OPRÓCZ indeksów należy używać takich typów żeby długość rekordu była stała (a nie ZAMIAST indeksów), także Twoja wypowiedź jest trochę oskarżycielska :P

Oczywiście należy indeksować pola w celu zwiększenia szybkości (te pola które w najbardziej jednoznaczny sposób definiują poszczególne wiersze w tabeli), lub te których używamy do filtracji danych. Ogólnie rzecz biorąc tworzymy indeksy na pola których używamy po słowach WHERE oraz GROUP BY, należy używać jak najmniejszych (zajmujących najmniejszą ilość pamięci) typów danych jak to tylko możliwe - zmniejszy to wielkość tabeli oraz zwiększy szybkość (bo plik będzie mniejszy).
To tyle ode mnie na ten temat.

0
prgtw napisał(a)

Jeżeli chodzi o mnie to ja doskonale wiem po co są indeksy :P Ale OPRÓCZ indeksów należy używać takich typów żeby długość rekordu była stała (a nie ZAMIAST indeksów), także Twoja wypowiedź jest trochę oskarżycielska :P

coś z tą długością pola to jakieś herezje mówisz :) Oczywiście trzeba wiedzieć, kiedy jaki typ zastosować (np. czy CHAR czy VARHAR) ale nie ma to praktycznie znaczenia jeśli chodzi o szybkość przeszukiwania bazy. A co do oskarżycielskiego tonu to niestety ale w Twojej poprzedniej wypowiedzi nie było słowa o indeksowaniu

Oczywiście należy indeksować pola w celu zwiększenia szybkości (te pola które w najbardziej jednoznaczny sposób definiują poszczególne wiersze w tabeli), lub te których używamy do filtracji danych. Ogólnie rzecz biorąc tworzymy indeksy na pola których używamy po słowach WHERE oraz GROUP BY, należy używać jak najmniejszych (zajmujących najmniejszą ilość pamięci) typów danych jak to tylko możliwe - zmniejszy to wielkość tabeli oraz zwiększy szybkość (bo plik będzie mniejszy).
To tyle ode mnie na ten temat.
no i tu się zgodzę :)

0

Nie pisałem o indeksach bo wcześniej sam o nich napisałeś więc po co miałem się powtarzać ... ale nie ważne. Krótko: indeksy, najmniejsze możliwe typy danych i do tego dające w miarę możliwości stałe długości rekordu.

0

Wszystko racja, ja dodam jeszcze, że same indeksy czasem mogą nie dać pożądanych rezultatów, jeśli dane w tabeli są fizycznie niepoukładane. Jest to szczególnie ważne w takich bazach jak PostgreSQL i MySQL, które nie potrafią jeszcze robić tzw. INDEX ONLY SCAN, czyli wyszukiwania danych jedynie na podstawie indeksu, bez zaglądania do tabeli. Jeśli wybierasz np. 100 rekordów z tabeli 1 mln rekordów po indeksie niesklastrowanym, do będziesz mieć typowo ~101...103 op. czytania bloków. Jeśli indeks jest sklastrowany, będzie ok. 2-3 op. czytania, bo wszystkie dane zmieszczą się pewnie w jednym bloku czyli jakieś 30-50 razy szybciej. W PostgreSQL do klastrowania tabeli wg indeksów służy CLUSTER, w MySQL nie wiem.
Jeśli nic takiego tam nie ma to na pewno można zastosować trick z

INSERT INTO sklastrowana_tabela SELECT * FROM niesklastrowana_tabela ORDER BY indeksowane_kolumny;

// BTW. Ten trick w Postgresie dziala dziala szybciej niz CLUSTER, choc CLUSTER jest dużo wygodniejszy, bo nie trzeba tab. tymczasowej robić.

1 użytkowników online, w tym zalogowanych: 0, gości: 1