Pytanie Bardzo wolne zapytanie MYSQL dla 2,5 miliona tabeli wierszy


Naprawdę staram się uzyskać czas zapytania w dół, jego obecnie ma do zapytania 2,5 miliona wierszy i trwa to ponad 20 sekund

tutaj jest zapytanie

SELECT play_date AS date, COUNT(DISTINCT(email)) AS count
FROM log
WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01'
AND type = 'play'
GROUP BY play_date
ORDER BY play_date desc;

 `id` int(11) NOT NULL auto_increment,
  `instance` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `type` enum('play','claim','friend','email') NOT NULL,
  `result` enum('win','win-small','lose','none') NOT NULL,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `play_date` date NOT NULL,
  `email_refer` varchar(255) NOT NULL,
  `remote_addr` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `email` (`email`),
  KEY `result` (`result`),
  KEY `timestamp` (`timestamp`),
  KEY `email_refer` (`email_refer`),
  KEY `type_2` (`type`,`timestamp`),
  KEY `type_4` (`type`,`play_date`),
  KEY `type_result` (`type`,`play_date`,`result`)

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  log ref type_2,type_4,type_result   type_4  1   const   270404  Using where

Kwerenda używa indeksu type_4.

Czy ktoś wie, jak mogę przyspieszyć tę kwerendę?

Dzięki Tomek


21
2017-11-24 15:59


pochodzenie


+1 za podanie wszystkich istotnych informacji i jasne wyjaśnienie. - Ben S
Czy możesz uruchomić jeden bez pola COUNT, aby zobaczyć, jak bardzo zmienia to prędkość zapytania? Nie chodzi o to, że nie potrzebujesz pola, tak jak linia podstawowa, aby zobaczyć, jaki wpływ ma to działanie. - Derek H
bez liczenia upragnionego czasu około 8 sekund - Tom
Spróbuj utworzyć indeks złożony dla ("play_date", "e-mail") w tej kolejności, jeśli jest używany, powinien naprawdę przyspieszyć zapytanie. - Pop Catalin
Wygląda na to, że dodanie e-maila do indeksu type_4 naprawiło to - Tom


Odpowiedzi:


To już jest stosunkowo dobre. Zanotowanie wydajności polega na tym, że zapytanie musi porównywać v2 270404 dla równości dla COUNT(DISTINCT(email)), co oznacza, że ​​należy przeczytać 270404 wierszy.

Możesz szybciej policzyć, tworząc indeks pokrywający. Oznacza to, że rzeczywiste wiersze nie muszą być odczytywane, ponieważ wszystkie wymagane informacje są obecne w samym indeksie.

Aby to zrobić, zmień indeks w następujący sposób:

KEY `type_4` (`type`,`play_date`, `email`)

Byłbym zaskoczony, gdyby to nie przyspieszyło sprawy.

(Podziękowania dla MarkR za odpowiedni termin.)


15
2017-11-24 16:20



Po prostu wypróbowałem to i otrzymałem ogromne ulepszenie, czas zapytania wynosi teraz mniej niż 3 sekundy, z którymi możemy żyć. Dziękuję Ci bardzo! - Tom
Myślę, że dla tego konkretnego zapytania pomocne będzie również usunięcie typu. - Pop Catalin
Nazywa się to "indeksem kryjącym"; dzieje się tak, gdy wszystkie kolumny użyte w zapytaniu znajdują się w indeksie. W takim przypadku są one również we właściwej kolejności, aby GROUP BY można było zoptymalizować w prosty skan zakresu (bez konieczności sortowania) indeksu pokrywającego, który jest naprawdę dobrym rodzajem planu zapytania, nawet jeśli są one dość dużo rzędów; będzie dobra lokalizacja danych. - MarkR


Twoje indeksowanie jest prawdopodobnie tak dobre, jak tylko możesz je zdobyć. Masz indeks złożony w 2 kolumnach w swoim where i explain wysłane, oznacza, że ​​jest używane. Niestety, istnieje 2570404 wierszy, które pasują do twoich kryteriów where i wszystkie one muszą być wzięte pod uwagę. Poza tym nie zwrócisz niepotrzebnych wierszy select lista.

Moja rada polegałaby na agregowaniu danych codziennie (lub co godzinę) i buforowaniu wyników. W ten sposób możesz natychmiast uzyskać dostęp do nieznacznie zużytych danych. Mam nadzieję, że jest to do przyjęcia dla waszych celów.


5
2017-11-24 16:05



Niestety statystyki muszą być aktywne - Tom


Wypróbuj indeks na play_date, wpisz (to samo co type_4, po prostu odwróć pola) i sprawdź, czy to pomaga

Istnieją 4 możliwe typy i zakładam 100 możliwych dat. Jeśli zapytanie korzysta z indeksu typu play_date, w zasadzie (nie w 100% dokładne, ale ogólny pomysł) mówi.

(A) Find all the Play records (about 25% of the file)
(B) Now within that subset, find all of the requested dates

Poprzez odwrócenie indeksu podejście jest

> (A) Find all the dates within range
> (Maybe 1-2% of file) (B) Now find all
> PLAY types within that smaller portion
> of the file

Mam nadzieję że to pomoże


4
2017-11-24 16:03



Ta odpowiedź pozwoli zaoszczędzić potrzebę użycia innego indeksu, ale migth wpływa na inne zapytania zależne od typu, a nie od play_date. To jest kompromis. - borjab
Cześć Sparky, stworzyłem nowy indeks, ale MySQL go nie użyje, więc użyłem FORCE INDEX, ale gdzie wydaje się zwracać wszystkie wiersze? id select_type typ tabeli possible_keys key key_len ref rows Extra 1 PROSTE logarytm play_date_type play_date_type 4 NULL 2519022 Używanie gdzie - Tom


Wydobywanie wiadomości e-mail do oddzielnej tabeli powinno być dobrym wzrostem wydajności, ponieważ zliczanie odrębnych pól varchar powinno zająć trochę czasu. Poza tym - używany jest poprawny indeks, a samo zapytanie jest zoptymalizowane tak, jak mogłoby być (z wyjątkiem oczywiście wiadomości e-mail).


3
2017-11-24 16:05





The COUNT(DISTINCT(email)) część to ta część, która cię zabija. Jeśli naprawdę potrzebujesz pierwszych 2000 wyników o wartości 270,404, być może pomogłoby to w liczeniu e-maili tylko dla wyników, a nie dla całego zestawu.

SELECT date, COUNT(DISTINCT(email)) AS count
FROM log,
(
    SELECT play_date AS date
      FROM log
     WHERE play_date BETWEEN '2009-02-23' AND '2020-01-01'
       AND type = 'play'
     ORDER BY play_date desc
     LIMIT 2000
) AS shortlist
WHERE shortlist.id = log.id
GROUP BY date

1
2017-11-24 16:23



... poza tym, że mówi, że element count () zabija go tylko przez 8 sekund z 20. - Ewan Todd


Spróbuj utworzyć indeks tylko na play_date.


0
2017-11-24 16:04





Na dłuższą metę polecam tworzenie tabeli podsumowań z kluczem podstawowym play_date i liczbą wyraźnych e-maili.

W zależności od tego, jak aktualny będzie to konieczne - zezwól na aktualizowanie go codziennie (przez play_date) lub na żywo za pomocą wyzwalacza w tabeli dziennika.


0
2017-11-24 16:09





Istnieje duża szansa, że ​​skanowanie tabeli będzie szybsze niż losowy dostęp do ponad 200 000 wierszy:

SELECT ... FROM log IGNORE INDEX (type_2,type_4,type_result) ...

Ponadto dla dużych zgrupowanych zapytań możesz zobaczyć lepszą wydajność, wymuszając sortowanie plików zamiast grupy opartej na hashtable (ponieważ jeśli okaże się, że potrzebujesz więcej niż tmp_table_size lub max_heap_table_size wydajność się załamuje):

SELECT SQL_BIG_RESULT ...

0
2017-11-24 16:17