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

Ответ 1

Возможно, вы могли бы создать таблицу индексирования, которая предоставляет последовательный ключ, относящийся к ключу в вашей целевой таблице. Затем вы можете присоединиться к этой таблице индексирования в свою целевую таблицу и использовать предложение 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;

Ответ 2

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

Таким образом, 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-запросы или он ничего не улучшает, с надеждой, может быть целесообразно разбить этот единственный оператор на несколько операторов и зафиксировать идентификаторы в структуре данных.

Обновление: я нашел сообщение в блоге, о котором я говорил: это был Jeff Atwood "Все абстракции не соответствуют абстракциям" О ужасе кодирования.

Ответ 3

Если записи велики, медленность может возникать при загрузке данных. Если индексный столбец индексируется, то его выбор будет намного быстрее. Затем вы можете выполнить второй запрос с предложением 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)

Ответ 4

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

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

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

и т.д.

Ответ 5

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

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

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

Я написал статью с подробностями:

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

Ответ 6

Недавно я столкнулся с этой проблемой. Проблема состояла в том, чтобы исправить две части. Сначала мне пришлось использовать внутренний выбор в моем предложении 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, тогда смещение начало становиться невыносимо медленным. Изменение этого параметра МЕЖДУ в моем внутреннем запросе ускорило его для любой страницы. Я не уверен, почему MySql не ускорил OFFSET, но между ними, кажется, снова намотано.