Как я могу ускорить запрос MySQL с большим смещением в предложении LIMIT?

У меня проблемы с производительностью, когда LIMITв мыслях SELECT с большим смещением:

SELECT * FROM table LIMIT m, n;

Если смещение m скажем, больше чем 1 000 000, операция очень медленная.

Я должен использовать limit m, n; Я не могу использовать что-то вроде id > 1,000,000 limit n,

Как я могу оптимизировать это утверждение для лучшей производительности?

Источник

6 ответов

Решение

Возможно, вы могли бы создать таблицу индексации, которая предоставляет последовательный ключ, относящийся к ключу в вашей целевой таблице. Затем вы можете присоединить эту таблицу индексации к вашей целевой таблице и использовать предложение where для более эффективного получения нужных вам строк.

#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;
Источник

Если записи велики, медлительность может быть связана с загрузкой данных. Если столбец id проиндексирован, то просто выбрать его будет гораздо быстрее. Затем вы можете выполнить второй запрос с предложением IN для соответствующих идентификаторов (или сформулировать предложение WHERE, используя минимальные и максимальные идентификаторы из первого запроса).

медленный:

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

быстро:

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

SELECT * FROM table WHERE id IN (1,2,3...10)
Источник

Где-то в Интернете есть пост в блоге о том, как лучше всего сделать выбор отображаемых строк как можно более компактным, таким образом: только идентификаторы; и получение полных результатов должно в свою очередь извлекать все данные, которые вы хотите, только для выбранных вами строк.

Таким образом, SQL может быть примерно таким (непроверенный, я не уверен, что он действительно пойдет на пользу):

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

Если ваш движок SQL слишком примитивен, чтобы допускать такого рода операторы SQL, или он ничего не улучшает, вопреки надежде, возможно, стоит разбить этот единственный оператор на несколько операторов и записать идентификаторы в структуру данных.

Обновление: я нашел сообщение в блоге, о котором я говорил: это был "Все абстракции - неудачные абстракции" Джеффа Этвуда на тему "Кодирующий ужас".

Источник

Я не думаю, что нужно создавать отдельный индекс, если он уже есть в вашей таблице. Если это так, то вы можете упорядочить по этому первичному ключу, а затем использовать значения ключа для перехода:

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

Другой оптимизацией было бы не использование SELECT *, а только ID, чтобы он мог просто прочитать индекс и не должен затем находить все данные (уменьшать накладные расходы ввода-вывода). Если вам нужны некоторые из других столбцов, то, возможно, вы могли бы добавить их в индекс, чтобы они читались с помощью первичного ключа (который, скорее всего, будет храниться в памяти и, следовательно, не требует поиска диска) - хотя это не подходит для всех случаев, так что вам придется играть.

Я написал статью с более подробной информацией:

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

Источник

Ответ Пола Диксона - действительно решение проблемы, но вам нужно будет поддерживать таблицу последовательности и следить за тем, чтобы в ней не было пропусков строк.

Если это возможно, лучшим решением было бы просто убедиться, что в исходной таблице нет пропусков строк, и начинается с идентификатора 1. Затем захватите строки, используя идентификатор для разбивки на страницы.

SELECT * FROM table A WHERE ID>= 1 И ID <= 1000;
SELECT * FROM table A, где id >= 1001 и id <= 2000;

и так далее...

Источник

Я столкнулся с этой проблемой недавно. Проблема состояла из двух частей, которые нужно было исправить. Сначала я должен был использовать внутреннее выделение в предложении FROM, которое делало мои ограничения и смещения только для первичного ключа:

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

Тогда я мог бы использовать это как часть моего запроса:

'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')

Когда я впервые создал этот запрос, я использовал OFFSET и LIMIT в MySql. Это работало нормально, пока я не прошел страницу 100, затем смещение стало невыносимо медленным. Изменение этого значения между BETWEEN в моем внутреннем запросе ускорило его для любой страницы. Я не уверен, почему MySql не ускорил OFFSET, но между ними, кажется, снова его катит.

Источник
Другие вопросы по тегам