GoldenLine
Zaloguj się
Marcin D.

Marcin D. frontend & backend
developer

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Tabela 20 mln rekordów,
wyszukanie poprzez IN (...) w którym zawarte jest do 5000 pozycji,
po indeksie oczywiście,
zajmuje kilka sekund.
Czy można to zadanie przyspieszyć inną metodą?
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Pokaż zapytanie, plan zapytania, rodzaj bazy danych z wersją i strukturę tabeli.
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

jaka baza danych bo od tego wiele zależy
Link do wypowiedziLink
Marcin D.

Marcin D. frontend & backend
developer

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Baza to MySQL. Pola z INDEX-em typu INT.

Z pierwszego zapytania:
SELECT lat_la_id FROM `tof_link_la_typ` WHERE `lat_typ_id` = 13671
otrzymuję ok. 5000 i więcej różnych wartości: 8468662,8468663,8468664,8468665,8468666,8468667,.....
z którymi poprzez IN() tworzone jest zapytanie do tabeli z 13 979 432 rekordów (250MB+250MB index) poprzez:
SELECT la_art_id FROM `tof_link_art` WHERE `la_id` IN (8468662,8468663,8468664,8468665,8468666,...,8468900)
Zapytanie działa szybko, jednak gdy w IN () jest więcej wartości:
100 - 0.2s; 1000 - 1.2s; 2000 - 1.8s; 3000 - 2.5s; 5000 - 3.5s; 1000 - 8s; 5000 - 24s.

Serwer ma 1vcore z 1GB RAM, i pewnie poprawiłoby osiągi zwiększenie parametrów, jednak nie miałem wcześniej problemu z wydajnością przy znacznie większych tabelach (po 80 mln rekordów i 2GB rozmiaru).
Pytanie, czy można zastąpić w jakiś inny sposób metodę IN ()?
Link do wypowiedziLink
Sebastian O.

Sebastian O.

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

zawsze możesz użyć 'or' ale nie jestem pewien czy to przyśpieszy.
Możesz jeszcze przetestować tak:


SELECT la_art_id FROM `tof_link_art` WHERE `la_id` IN (SELECT lat_la_id FROM `tof_link_la_typ` WHERE `lat_typ_id` = 13671)


lub złączyć obie tabele joinem.Ten post został edytowany przez Autora dnia 18.08.14 o godzinie 16:48
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Wydaje mi się, że:

OR nic nie powinien zmienić

SELECT ... WHERE ... IN (SELECT ...) nie powinien zmienić bardzo wiele, tylko ledwie poprawi wydajność, bo nie będzie trzeba pchać i parsować dużego zapytania.

Natomiast join powinien pomóc.

Inna sprawa, że możliwe jeszcze, że opóźnienie jest po stronie przesyłania danych, albo też program ma do kitu algorytm obrabiający te dane.

Pytanie: jakie są plany zapytania dla tej innej ilości danych i jaki jest dla joina?
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów



drop temporary table if exists tTmp;

create temporary table tTmp as

SELECT lat_la_id FROM `tof_link_la_typ` WHERE `lat_typ_id` = 13671;



ALTER TABLE tTmp

  ADD INDEX idx_tmp ( lat_la_id);



SELECT la_art_id 

FROM `tof_link_art` as a join tTmp  as b on a.tof_link_art = b.lat_la_id


powinno działać w miarę szybko :)
a jak chcesz większego speeda to rób temporary table w Memory Engine za pomocą standardowego crate table i insert into
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Sprawdź wartość zmiennej konfiguracyjnej "key_buffer_size", przy 1GB RAM, ustaw na co najmniej 128M, jeżeli ma mniejszą wartość.
Link do wypowiedziLink
Marcin Wojcieszczyk

Marcin Wojcieszczyk projektant
programista, INNSOFT
Sp. z o.o.

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Przesyłanie dużej ilości wartości w IN nie jest dobrym pomysłem. Baza Oracle ma nawet ograniczenie, że w klauzuli IN można przesłać maksymalnie 1000 wartości.

W tym przypadku załatwić sprawę powinien zwyczajny join czyli zamiast dwóch zapytań piszesz jedno które pobiera dane od razu z dwóch tabel. Oczywiście tabele muszą być odpowiednio poindeksowane.

Pomysł z tabelą tymczasową też jest dobry, ale więcej komplikacji w porównaniu z joinem. Tabelę tymczasową należy zastosować wtedy gdy nie ma możliwości zrobienia joina.

20 milionów rekordów to nie jest mało, ale też nie jest to ekstremalnie dużo. Znam przypadki tabel z większą ilością rekordów...
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Marcin W.:
Pomysł z tabelą tymczasową też jest dobry, ale więcej komplikacji w porównaniu z joinem. Tabelę tymczasową należy zastosować wtedy gdy nie ma możliwości zrobienia joina.

20 milionów rekordów to nie jest mało, ale też nie jest to ekstremalnie dużo. Znam przypadki tabel z większą ilością rekordów...

MySQL to nie Oracle, oczywiście zamiast tabeli tymczasowej można dać subquery, ale optymalizator apytań już nie daje rady, z doświadczenia wiem że operowanie tabelami tymczasowymi daje lepsze rezultaty niż subquery
Link do wypowiedziLink
Tomasz Zadora

Tomasz Zadora programuję

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Planer MySQL-a czasami dziwnie się zachowuje i trzeba wtedy używać polecenia HINT aby wymusić indeksy z jakich będzie korzystać przy wykonywaniu zapytania.

Można w ten sposób znacznie przyspieszyć zapytania, ale to raczej dotyczy bardziej skomplikowanych przypadków niż z tego wątku.

Tutaj albo pomoże JOIN albo możliwe że bufor na indeks jest bardzo mały i to spowalnia - jak powiększyć napisałem wcześniej.
Link do wypowiedziLink
Marcin Wojcieszczyk

Marcin Wojcieszczyk projektant
programista, INNSOFT
Sp. z o.o.

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Przemysław R.:
MySQL to nie Oracle, oczywiście zamiast tabeli tymczasowej można dać subquery, ale optymalizator apytań już nie daje rady, z doświadczenia wiem że operowanie tabelami tymczasowymi daje lepsze rezultaty niż subquery

Tak, w tym przypadku podzapytanie nie będzie wydajnym rozwiązaniem, to oczywiste. Ale ja piszę o złączeniu (join).

W tym przypadku nie ma potrzeby stosować tabeli tymczasowej. Po co tworzyć tabelę tymczasową na podstawie tabeli z danymi aby ją złączyć z inną tabelą, skoro można od razu złączyć w jedno zapytanie obydwie tabele z danymi?
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Marcin W.:
Przemysław R.:
MySQL to nie Oracle, oczywiście zamiast tabeli tymczasowej można dać subquery, ale optymalizator apytań już nie daje rady, z doświadczenia wiem że operowanie tabelami tymczasowymi daje lepsze rezultaty niż subquery

Tak, w tym przypadku podzapytanie nie będzie wydajnym rozwiązaniem, to oczywiste. Ale ja piszę o złączeniu (join).

W tym przypadku nie ma potrzeby stosować tabeli tymczasowej. Po co tworzyć tabelę tymczasową na podstawie tabeli z danymi aby ją złączyć z inną tabelą, skoro można od razu złączyć w jedno zapytanie obydwie tabele z danymi?

to wszystko zależy czy elementy sprawdzane w IN są z tabeli czy z klejenia SQL-a np. z jakiegoś formularza. W momencie gdy masz 5000 wartości z palca to cóż....., jak jest tabelka to fakt jest to zbędne, można odrazu

tabela tymczasowa służy tylko i wyłącznie za kontener na elementy po których stosujemy JOIN później, to czy będą z odfiltrowanego zbioru tak jak zrobiłem czy też wydziergane ręcznie za pomocą create table / insert into to już kwestia wtórna, bo zauważ że w kwestii zasadniczej jesteśmy zgodni stosujemy JOIN do uzyskania zbioru wynikowego
Link do wypowiedziLink

konto usunięte

Temat: IN (1,2,3,4,5,6,...,5000) w tabeli z 20 mln rekordów

Szymon G.:
Wydaje mi się, że:

OR nic nie powinien zmienić

SELECT ... WHERE ... IN (SELECT ...) nie powinien zmienić bardzo wiele, tylko ledwie poprawi wydajność, bo nie będzie trzeba pchać i parsować dużego zapytania.

Natomiast join powinien pomóc.

Inna sprawa, że możliwe jeszcze, że opóźnienie jest po stronie przesyłania danych, albo też program ma do kitu algorytm obrabiający te dane.

Pytanie: jakie są plany zapytania dla tej innej ilości danych i jaki jest dla joina?

co do OR i MySQL to lepiej zrobić UNION ALL - i to nie jest żart
http://stackoverflow.com/questions/705311/serious-mysq...

osobiście widziałem jak zapytanie wykonuje się kilkaset razy szybciej jak tak zrobimy
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Optymalizacja bazy danych m...




Wyślij zaproszenie do
Anuluj