GoldenLine
Zaloguj się
Rafał Plewa

Rafał Plewa Manager IT,
Tourmedica.pl Sp. z
o.o.

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

Witam,

Mam od dawna problem z optymalizacją zapytań do pewnej tabeli MySQL. Szukam jakiegoś sposobu lub chociaż punktu zaczepienia, jak mógłbym zwiększyć wydajność moich zapytań.

Tabela `terminy` (InnoDB) zawiera ok. 3 mln rekordów (700mb).
Potrzebuję odpytać tą tabelę, aby wylistować w PHP rekordy w postaci stronicowanej (np. co 10 rekordów) listy terminów.

Poniższe zapytanie działa świetnie:
SELECT SQL_NO_CACHE id, cena FROM terminy LIMIT 10 OFFSET 0
(czas 0.00001 sekundy)

Ale wraz z zagłębianiem się w głąb tabeli (duży offset) jest już tragicznie:
SELECT SQL_NO_CACHE id, cena FROM terminy LIMIT 10 OFFSET 2000000
(czas 11 sekund !!!)

Gdy wstawiam warunki WHERE jest jeszcze gorzej, nie mówiąć o łączeniu tej tabeli z innymi. Oczywiście indeksy są pozakładane, na różne pola, co przyspiesza, ale nie jakoś szczególnie drastycznie.

Moja prośba do Was jest taka: czy macie jakiś pomysł na technikę którą mógłbym tutaj zastosować? Czasy jakie chciałbym osiągnąć przy wertowaniu końcowych rekordów to ok 1 sekundy. 3 miliony rekordów to nie jakoś szczególnie dużo, dlatego jestem pewien, że czasy do 1 sekundy są realne.
Link do wypowiedziLink
Michał Jarosz

Michał Jarosz Frontend Developer &
Team Leader

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

A co mówi EXPLAIN?
Link do wypowiedziLink
Radosław Bułat

Radosław Bułat Ruby on Rails
developer,
Adtaily.com

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

Dla zapytania:


SELECT SQL_NO_CACHE id, cena FROM terminy LIMIT 10 OFFSET 0


mógłbyś spróbować dodać index na (id, cena) (jeśli tabelka to innodb to wystarczy na samą kolumnę 'cena'). Wtedy zapytanie będzie korzystać tylko z indeksu, zakładając że cały indeks mieści się w pamięci powinno być szybko.

Zakładam jednak, że zapytanie oryginalne nie jest aż tak proste, prawda? Wklej je tutaj proszę wraz z tym co zwraca EXPLAIN dla niego.

Ze stronnicowaniem zawsze jest problem, prędzej czy później. Niestety to zupełnie normalne, że wraz z większym offsetem zapytanie jest coraz wolniejsze. Po prostu baza i tak musi znaleźć OFFSET + LIMIT rekordów a następnie przeskoczyć OFFSET rekordów i zwrócić LIMIT.

Możesz jeszcze przemyśleć czy faktycznie użytkownik potrzebuje tak wielkich offsetów (przeważnie można uznać pewne dane wstecz za "archiwalne") i być może mógłbyś pozwolić tylko na przeglądanie do X stron wstecz.
Link do wypowiedziLink
Rafał Plewa

Rafał Plewa Manager IT,
Tourmedica.pl Sp. z
o.o.

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

EXPLAIN pokazuje to:

mysql> explain SELECT SQL_NO_CACHE id, price FROM term order by price LIMIT 2000000,10;
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
| 1 | SIMPLE | term | ALL | NULL | NULL | NULL | NULL | 2161192 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+----------------+
1 row in set (0.00 sec)

Radku, rzeczywiście zapytania do tej tabeli nie kończą się jedynie na powyższym - przykładowym, które jednak jest najbardziej czasochłonnym zapytaniem, gdyż zwraca najwięcej wyników. Pozostałe zapytania do tej tabeli będą kombinacją różnych filtrów, zatem zwracanych wyników będzie mniej.

Ciekawa sytuacja, ponieważ po zwiększeniu zmiennej serwera mysql inno_db_buffer_pool_size z 16M na 512M nagle z 10 sekund na 2.8. Możliwe zatem, że problem tkwi w kiepskiej konfiguracji serwera. Czy macie jakieś sugestie odnośnie innych zmiennych konfiguracyjnych?
Link do wypowiedziLink
Radosław Bułat

Radosław Bułat Ruby on Rails
developer,
Adtaily.com

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

Niestety musiałbym googlać, bo z pamięci nie jestem w stanie podać. Generalnie znaczenie mają _zwykle_ 3-4 parametry. Chodzi zarówno o zwykłe bufory jak i bufory dla indeksów. Możesz zerknąć np. tutaj: http://www.mysqlperformanceblog.com/2006/09/29/what-to...

Googluj za: mysql tune configuration.

Nie podałeś oryginalnego zapytania, ale duże znaczenie ma także fakt, czy korzystasz z indeksy pokrywającego (zapytanie, które odwołuje się tylko do kolumn w indeksie) i czy ten indeks mieści się cały w pamięci.
Link do wypowiedziLink
Michał Jarosz

Michał Jarosz Frontend Developer &
Team Leader

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

A tak nawiasem mówiąc, rozumiem że ten SQL_NO_CACHE to na potrzeby pomiaru czasu, a na produkcji jednak z cache korzystasz?
Link do wypowiedziLink
Rafał Plewa

Rafał Plewa Manager IT,
Tourmedica.pl Sp. z
o.o.

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

Michał Jarosz:
A tak nawiasem mówiąc, rozumiem że ten SQL_NO_CACHE to na potrzeby pomiaru czasu, a na produkcji jednak z cache korzystasz?

Hehe, jasne :)

Radku -> Dzięki za pomoc, będę optymalizował, testował, optymalizował, testował itd...
Link do wypowiedziLink
Michał Jarosz

Michał Jarosz Frontend Developer &
Team Leader

Temat: Lista wyników z końca dużej tabeli, offset i długie czasy

Rafał Plewa:

Hehe, jasne :)

Nigdy nie szkodzi sprawdzić. Ludzie różne dziwne rzeczy robią czasami ;)
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj