Wykonanie dużego zapytania SQL, bez zawieszania serwera JAVA

Witam
Od jakiegoś czasu borykam się z problemem optymalizacji mojego systemu do serwera gry (L2jServer).
System ten to prosta Aukcja pozwalająca np: wyszukiwać przedmioty.

Problem polega na tym iż przy wywołaniu zapytania na tabeli rzędu 1 000 000 rekordów, serwer laguje na kilka sekund.
Całość oparta jest o obiektowy model java. Zapytania aktualizujące pojedyncze rekordy wydają sie nie powodować rzadnych lagów.

Problem pojawia się przy wyszukiwarce aukcji, dla podanych preferencji.

Zakładając że wyszukiwarka moze zostać użyta przez conajmniej 1000 użytkowników w jednej chwili, oraz
dodatkowo każdy przedmiot ma conajmniej 5 własciwości po których można wyszukiwać lub sortować.

Całość jest na 1 komputerze wiec zasoby są dzielone przez bazę danych, i server java.

Pomysł:

1. Przy podaniu preferencji wyszukiwania, wyślij zapytanie do bazy, i zwróć listę wybranych przedmiotów.
   (rozwiązanie może być skrajnie nie optymalne ze wzgledu na dużą liczbę rekordów (1 000 000) i duża liczbę użytkowników (1 000)).

2. Korzystanie z modelu obiektowego (model utworzony, lecz na razie brak pomysłu na posortowanie list).
   *Utworzenie list przechowujących wszystkie przedmioty (obiekty).
   *Każda lista musi być w jakiś sposób posortowana, ze względu na możliwość wyświetlania przedmiotów wg nazwy, ceny itp. (rosnąco lub malejąco).
   *można np: utworzyć jakąś listę lub mapę FastMap przechowujacą id aukcji w porzadku nie malejącym, dodawanie nowej aukcji dodawało by ją w odpowiednie miejsce (INSERT(pozycja) <- czy taki mechanizm istenie w JAVA? np FastMap nie ma metody insert).
   *Przy wielokrotnym wywołaniu wyszukiwania dla podanych preferencji, nie będzie to powodować lagów (np: wywołania asynchroniczne).

Podsumowując:

1. Pierwszy pomysł raczej odpada, więc czy 2 pomysł jest dobry?
   Jeżeli tak to czy istnieją listy pozwalające na operację wstawiania do listy nowego elementu na danej pozycji (uwględnić tu należy ze lista jest cały czas przeszukiwana! tzn. 1 gracz dodaje/usuwa aukcję, co ma nie powodować przerwania dla innych graczy szukających w tym samym czasie, po tej samej liście).
   Można zrobić wywołania asynchroniczne, co spowoduje rozłożenie obciążenia równomiernie dla 1000 graczy.
   Ale czy to nie spowoduje destabilizacji działania pozostałych fukcji serwera?

2. Czy należało by zmienić coś w bazie danych:

   • obecna struktura to 1 tabela zawierająca listę aukcji, oraz tabele pomocnicze jak np: dane graczy, dane przedmoitów.
   • edytować mogę TYLKO tabelę dotyczącą aukcji!
   • dla pomysłu 2 struktura tabeli raczej nie ma dużego znaczenia, bo wszystkie dane są przechowywane w modelu obiektowym.

3. Inne pomysły? na razie brak, w serwisach jak allegro używany jest model wielo-warstwowy. Ja posiadam 1 komputer i interesuje mnie jakich metod użyć w języku JAVA, SQL by zrealizować tą funkcjonalność?

Za podpowiedzi z góry dziekuję ;)

Ewentualnie proszę o podanie jakiejś literatury odnoszącej sie do tematu (może być po angielsku).

Pytanie czy są założone indeksy na tabeli.

Można by spróbować użyć mem cache > https://developers.google.com/appengine/docs/java/memcache/ . Można by przerzucić te dane do jakiegoś nosqla np. mongo gdzie całość powinna działać trochę lepiej.

Rozumiem że chodzi tu od indeksy tabeli w bazie, tak posiada ona klucz główny. Kazda aukcja posiada swoj id.

Sam klucz główny nie wystarczy. Na kolumnach po których jest wyszukiwanie powinny zostać nałożone dodatkowe, nieunikalne indeksy.

Z jakiego powodu?

wkleje strukture tabeli:

Indeksy przyspieszają wyszukiwanie. Zamiast skanować całą tabelę silnik bazy danych najpierw skanuje indeks po czym mając już "mniej więcej" lokalizację danych przeszukuje tylko interesujący fragment.

czyli rozumiem ze to rozwiazanie jest dla pomysłu 1.
bo tak jak wcześniej pisałem w obiektowym modelu java nie ma dla mnie wiekszej różnicy szybkość ladowania zapytań bo:

Działanie aukcji na serwerze:

• Start serwera.
• Wczytanie danych aukcji do list przechowujacych ich dane (obiekty).
• Odblokowanie logowania do gry.
• Teraz jest mój problem: czyli wydajne przeszukiwanie tych że ** list** z obiektami lub wskaznikami na obiekty (obiekt = przedmiot wystawiony na aukcji).

1. Korzystając z opcji sortowania w bazie wg danego kryterium przez danego gracza i zwracania mu listy wyników moze byc zasobożerne.
2. Sortowanie list przechowujacych indeksy aukcji w java np: podczas dodawania przedmiotu do aukcji. Może spełniać moje wymagania ale mam 1 problem taki iż nie wiem jeszcze jak dodawać nowy element do listy na danej pozycji, uwzgledniajac to ze inni gracze caly czas z tej listy korzystaja.
   Zakladam tu ze np jak mam sortowanie po nazwie , po cenie, ilosci itd, to tworze oddzielne posortowane listy dla kazdej z preferencji. i przeszukujac np po nazwie szukam tylko po liscie zawierajaca posortowane przedmioty po nazwie. jedynym problem tu jest dodanie nowego elementu na danej pozycji byz zachowac posortowany ciag ;)

Sortowanie na bazie danych prawie zawsze jest lepsze. Narzut nie jest aż tak gigantyczny jak się wydaje. Sortowanie po stronie aplikacji sprawdza się tylko w przypadku gdy chcesz zastosować w tym procesie jakąś dodatkową logikę np. uwzględniać fazę trzeciego księżyca Saturna. Osobiście raz stosowałem tego typu zabieg, ponieważ do sortowania trzeba było wprowadzić dodatkowo scoring uwzględniający pewne wykonane operacje biznesowe (sortowanie z wagami). Proste sortowanie rób na bazie.
Kolejny patent to tzw. widoki materializowane. Są to specjalne widoki, które nie tylko są aliasem do zapytania, ale przede wszystkim kopiują i zapisują wyniki w specjalnej tymczasowej tabeli. Ich wada jest konieczność synchronizowania widoku raz na pewien czas ponieważ jeżeli w tabeli nastąpi zmiana to widok nie zawiera zmienionych informacji. Sama synchronizacja może przebiegać na kilka sposobów może być to synchronizacja całkowita (jeszcze raz wykonujemy zapytanie) albo z uwzględnieniem plików logów operacji (powtarzane są kolejne operacje na głównej tabeli). Oczywiście najlepiej sprawdza się to w przypadku tabel rzadko modyfikowanych.

A co sądzisz o takim pomyśle:
Utworzenie mapy przechowującej dane uporzadkowane. I np dodanie elementu do takiej listy dodawalo by go na odpowiedniej pozycji. Operacja taaka wymagala by tylko wyszukania odpowiedniej pozycji i dodaniu naniej elementu. A tablica byla by zawsze posortowana. Tylko teraz czytalem o TreeMap'ie ale z tego co kiedys czytalem nie jest ona nakwydajniejsza. Idelanie pasowala by mi jakas hashMapa ktora pozwala dodawac elementy nie zaburzajac uporzadkowania elelemtow. czy istnieje taka klasa, ktora to zapewnia i jest w miare wydajna? najlepiej jak operuje na listach dwukierunkowych.

Jeżeli już to bym użył jakiegoś mem cache, a nie samemu to pisał. Zresztą sprawdź czy założenie indeksów na bazie pomogło. Później dopiero kombinuj z dodatkową warstwą cache. No i pamiętaj, że RAM jest skończony.

ok pokombinuje dzieki ;) jak cos wymysle dam znac:D

przede wszystkim jak zapytanie jest wolne to pierwsze co się robi to analizuje PLAN WYKONANIA ZAPYTANIA. Nie pyta się wróżki, nie lata po forum, nie wymyśla trójkątnego (bo nawet kwadratowym je ciężko nazwać) koła. Po prostu bierzesz, odpalasz narzędzie, które wyświetla plan zapytania, szukasz słabych punktów, zakładasz odpowiednie indeksy (ALE Z GŁOWĄ!! Indeksy na każde pole na pewno nie pomogą!) i sprawdzasz ponownie. Jak baza nigdzie nie leci po całej tabeli (ew. jeśli tabela ma 10 rekordów) a czas zapytania nadal nie spełnia naszych oczekiwań to albo zabierasz się za optymalizacje samej bazy (zmieniasz przydzieloną pamięć, grzebiesz w ustawieniach) lub stawiasz lepszy serwer albo rzucasz stare zapytanie i piszesz je od nowa dokładnie analizując co musi w nim być a co nie.
Po Twoich postach widać, że temat optymalizacji jest Ci obcy. A mogę się założyć, że odpowiedni indeks (indeksy) zmniejszą czas wykonywania zapytania o jakieś 90% jak nie więcej.