Pytanie Jak mogę przyspieszyć zapytanie MySQL o duże przesunięcie w klauzuli LIMIT?


Mam problemy z wydajnością, kiedy LIMITmysql SELECT z dużym przesunięciem:

SELECT * FROM table LIMIT m, n;

Jeśli przesunięcie m jest na przykład większa niż 1 000 000, operacja jest bardzo powolna.

Muszę użyć limit m, n; Nie mogę użyć czegoś takiego id > 1,000,000 limit n.

Jak zoptymalizować to stwierdzenie, aby uzyskać lepszą wydajność?


23
2017-08-07 10:03


pochodzenie




Odpowiedzi:


Być może można utworzyć tabelę indeksowania, która zapewnia klucz sekwencyjny odnoszący się do klucza w tabeli docelowej. Następnie możesz dołączyć do tabeli indeksowania do tabeli docelowej i użyć klauzuli where, aby efektywniej uzyskać pożądane wiersze.

#create table to store sequences
CREATE TABLE seq (
   seq_no int not null auto_increment,
   id int not null,
   primary key(seq_no),
   unique(id)
);

#create the sequence
TRUNCATE seq;
INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;

#now get 1000 rows from offset 1000000
SELECT mytable.* 
FROM mytable 
INNER JOIN seq USING(id)
WHERE seq.seq_no BETWEEN 1000000 AND 1000999;

13
2017-08-07 10:09



to podejście działa tylko w instrukcjach wyboru, które nie zawierają warunku. moim zdaniem nie jest to dobre rozwiązanie. - ray pixar
Jak zaktualizować tabelę indeksu? W moim przypadku muszę zamówić przez kolumnę datetime i użyć dużych przesunięć powodujących powolne zapytania. Jeśli utworzę tę tabelę suportów, będę musiał ponownie wstawić za każdym razem, gdy mam nową datę, ponieważ nie jest ona w porządku. Już widzę to rozwiązanie, ale z tabelami tymczasowymi. - Keyne Viana
Jeśli czytam to poprawnie, po prostu duplikujesz kolumnę id z mytable w innej tabeli (i będziesz musiał zaktualizować obie tabele). Czy nie możesz po prostu dołączyć do siebie, gdzie tylko wybierasz identyfikator? Oto, co najbardziej obejść, widziałem. - Gremio


Gdzieś w Internecie jest blog na temat tego, jak najlepiej zrobić wybór rzędów pokazanie powinno być tak zwarte, jak to tylko możliwe, a więc: tylko identyfikatory; i generowanie kompletnych wyników powinno z kolei pobrać wszystkie potrzebne dane tylko dla wybranych wierszy.

W związku z tym SQL może być coś w rodzaju (untested, nie jestem pewien, czy rzeczywiście coś dobrego):

select A.* from table A 
  inner join (select id from table order by whatever limit m, n) B
  on A.id = B.id
order by A.whatever

Jeśli twój silnik SQL jest zbyt prymitywny, aby umożliwić tego rodzaju instrukcje SQL, lub niczego nie poprawia, wbrew nadziei, warto podzielić tę pojedynczą instrukcję na wiele instrukcji i przechwycić identyfikatory w strukturę danych.

Aktualizacja: Znalazłem wpis na blogu, o którym mówiłem: to był Jeff Atwood "Wszystkie abstrakcje są nieudanymi abstrakcjami" o przerażeniu kodowania.


9
2017-08-07 10:35



Przetestowałem Twój sugerowany SQL. ale nie robi żadnej poprawy. - ray pixar
Co jeśli masz klauzulę where na podstawie tabeli A? To nie zadziała, od pierwszego ograniczenia, a następnie zastosuj klauzulę where. Jeśli użyjesz join wewnątrz podzapytania, stracisz wydajność, prawda? - Keyne Viana
To zadziałało dla mnie, SELECT id FROM ... zapytanie zostało wykonane około 50 razy szybciej na zestawie prawie miliona wierszy w porównaniu do SELECT bunch,of,fields FROM .... - mr.b
Dziękuję za wskazówkę do artykułu Atwooda; to ciekawa lektura. Ale nie zaleca zawsze robienia tego, co mówisz; raczej używa tej techniki jako przykładu czegoś, co działa tutaj. Twierdzę, że cała przesłanka tego artykułu jest taka, że ​​bazy danych są złożonymi bestiami i żadne rozwiązanie nie może pasować do wszystkich przypadków (stąd abstrakcje są nieuchronnie "nieszczelne"). - alexis


Jeśli rekordy są duże, spowolnienie może pochodzić z ładowania danych. Jeśli kolumna id jest indeksowana, to po prostu zaznaczenie jej będzie znacznie szybsze. Następnie można wykonać drugie zapytanie z klauzulą ​​IN dla odpowiednich identyfikatorów (lub sformułować klauzulę WHERE, używając identyfikatorów min i max z pierwszego zapytania).

powolny:

SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000

szybki:

SELECT id FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000

SELECT * FROM table WHERE id IN (1,2,3...10)

4
2018-05-27 22:12





Odpowiedź Paula Dixona jest rzeczywiście rozwiązaniem tego problemu, ale musisz zachować tabelę sekwencji i upewnić się, że nie ma luki między wierszami.

Jeśli jest to wykonalne, lepszym rozwiązaniem byłoby po prostu upewnienie się, że oryginalna tabela nie ma luk między wierszami i zaczyna się od id 1. Następnie złap wiersze, używając id dla stronicowania.

SELECT * FROM table A WHERE id> = 1 AND id <= 1000;
SELECT * FROM table A WHERE id> = 1001 AND id <= 2000;

i tak dalej...


2
2017-12-15 23:24



SELECT * FROM table WHERE id> 1000 LIMIT 1000 - Keyne Viana
Ponownie, nie zadziała, jeśli zastosują się inne filtry. - devXen


Nie sądzę, że istnieje potrzeba stworzenia osobnego indeksu, jeśli Twój stół już go posiada. Jeśli tak, to możesz zamówić za pomocą tego klucza podstawowego, a następnie użyć wartości klucza, aby przejść przez:

SELECT * FROM myBigTable WHERE id > :OFFSET ORDER BY id ASC;

Inną optymalizacją byłoby nie użycie SELECT *, ale tylko identyfikatora, aby mógł po prostu odczytać indeks i nie musi wtedy lokalizować wszystkich danych (zmniejszyć narzut IO). Jeśli potrzebujesz innych kolumn, być może możesz dodać je do indeksu, tak aby były odczytywane kluczem podstawowym (który najprawdopodobniej będzie przechowywany w pamięci i dlatego nie wymaga sprawdzania dysku) - chociaż nie będzie to odpowiednie dla wszystkich przypadków, więc będziesz musiał grać.

Napisałem artykuł z większą ilością szczegółów:

http://www.4pmp.com/2010/02/scalable-mysql-avoid-offset-for-large-tables/


2
2018-05-31 07:59



Czy po prostu mysql lub większość dbs działa w ten dziwny sposób? Do tej pory najlepszym rozwiązaniem jest podzapytanie (gdy nie masz uporządkowanego indeksu). Najpierw zapytaj i zamów wszystko, a następnie wstaw przesunięcie. - Keyne Viana
Pomysł użycia tylko identyfikatora może być bardzo dobrym rozwiązaniem, zależy to od silnika pamięci, który przypuszczam! - twicejr


Niedawno natknąłem się na ten problem. Problem polegał na dwóch częściach do naprawienia. Najpierw musiałem użyć wewnętrznego wyboru w mojej klauzuli FROM, która spowodowała moje ograniczenie i kompensację tylko dla klucza podstawowego:

$subQuery = DB::raw("( SELECT id FROM titles WHERE id BETWEEN {$startId} AND {$endId}  ORDER BY title ) as t");  

Wtedy mógłbym użyć tego jako części mojej zapytania:

'titles.id',
                            'title_eisbns_concat.eisbns_concat', 
                            'titles.pub_symbol', 
                            'titles.title', 
                            'titles.subtitle', 
                            'titles.contributor1', 
                            'titles.publisher', 
                            'titles.epub_date', 
                            'titles.ebook_price', 
                            'publisher_licenses.id as pub_license_id', 
                            'license_types.shortname',
                            $coversQuery
                        )
                        ->from($subQuery)
                        ->leftJoin('titles',  't.id',  '=', 'titles.id')
                        ->leftJoin('organizations', 'organizations.symbol', '=', 'titles.pub_symbol') 
                        ->leftJoin('title_eisbns_concat', 'titles.id', '=', 'title_eisbns_concat.title_id') 
                        ->leftJoin('publisher_licenses', 'publisher_licenses.org_id', '=', 'organizations.id') 
                        ->leftJoin('license_types', 'license_types.id', '=', 'publisher_licenses.license_type_id')

Przy pierwszym utworzeniu tego zapytania użyłem funkcji PRZESUNIĘCIE i LIMIT w MySql. To działało dobrze, dopóki nie przeszłam na stronie 100, wtedy przesunięcie zaczęło być nieznośnie powolne. Zmiana tego na BETWEEN w moim wewnętrznym zapytaniu przyspieszyła to dla dowolnej strony. Nie jestem pewien, dlaczego MySql nie przyspieszył OFFSET, ale pomiędzy wydaje się ponownie go przywrócić.


0
2018-03-04 21:50



Jest to bardzo podobne do wielu innych rozwiązań, w których wiesz przedtem, jaki identyfikator chcesz rozpocząć, aby go ograniczyć (prawdopodobnie istnieją bardziej eleganckie sposoby, aby to zrobić). Główny problem polega na tym, że musisz wyświetlać strony w środku i nie masz pojęcia, od którego identyfikatora należy zacząć (zależnie od klauzuli where). Domyślam się, że kolejność według tytułu w twoim zapytaniu nie jest indeksowana. Możesz spróbować użyć wyjaśnienia, aby dowiedzieć się, co się dzieje i tworzyć nowe indeksy. Próba indeksowania tekstu może być problematyczna. dba.stackexchange.com/questions/35821/... - Gremio