Почему смещение MYSQL выше LIMIT замедляет запрос вниз?

Сценарий вкратце: таблица с более чем 16 миллионами записей [размером 2 ГБ]. Чем выше LIMIT offset с SELECT, тем медленнее будет запрос при использовании ORDER BY * primary_key *

So

SELECT * FROM large ORDER BY `id`  LIMIT 0, 30

занимает гораздо меньше, чем

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30

Это только заказывает 30 записей и то же самое в любом случае. Так что это не накладные расходы от ORDER BY.
Теперь, получая последние 30 строк, требуется около 180 секунд. Как я могу оптимизировать этот простой запрос?

Ответы

Ответ 1

Нормально, что более высокие смещения замедляют запрос вниз, так как запрос должен отсчитывать первые записи OFFSET + LIMIT (и брать только LIMIT из них). Чем выше это значение, тем дольше выполняется запрос.

Запрос не может перейти прямо к OFFSET, потому что, во-первых, записи могут иметь разную длину и, во-вторых, могут быть пробелы из удаленных записей. Он должен проверять и считать каждую запись на своем пути.

Предполагая, что id является PRIMARY KEY таблицы MyISAM, вы можете ускорить его, используя этот трюк:

SELECT  t.*
FROM    (
        SELECT  id
        FROM    mytable
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    mytable t
ON      t.id = q.id

См. статью:

Ответ 2

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

Итак, что вы можете сделать, это:

  • Удерживать последний идентификатор набора данных (30) (например, lastId = 530)
  • Добавьте условие WHERE id > lastId limit 0,30

Таким образом, вы всегда можете иметь смещение ZERO. Вы будете поражены улучшением производительности.

Ответ 3

MySQL не может перейти непосредственно к 10000-й записи (или 80000-й байт, как вы предлагаете), потому что он не может предположить, что он упакован/упорядочен как этот (или что он имеет непрерывные значения от 1 до 10000). Хотя в действительности это может быть так, MySQL не может предположить, что нет дыр/пробелов/удаленных идентификаторов.

Итак, как отмечали бобы, MySQL должен будет получить 10000 строк (или пройти через 10000-й записи индекса на id), прежде чем найти 30 для возврата.

РЕДАКТИРОВАТЬ. Чтобы проиллюстрировать мою точку.

Обратите внимание, что хотя

SELECT * FROM large ORDER BY id LIMIT 10000, 30

будет медленным (er),

SELECT * FROM large WHERE id >  10000 ORDER BY id LIMIT 30

будет быстрым (er) и вернет те же результаты, если нет отсутствующих id (т.е. пробелов).

Ответ 4

Отнимающая много времени часть двух запросов извлекает строки из таблицы. Логически говоря, в версии LIMIT 0, 30 необходимо восстановить только 30 строк. В версии LIMIT 10000, 30 оценивается 10000 строк и возвращается 30 строк. Может быть какая-то оптимизация может быть выполнена моим процессом чтения данных, но рассмотрим следующее:

Что делать, если в запросах есть предложение WHERE? Механизм должен возвращать все строки, которые квалифицируются, а затем сортировать данные и, наконец, получить 30 строк.

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

Ответ 5

Я нашел интересный пример для оптимизации запросов SELECT ORDER BY id LIMIT X, Y. У меня 35 миллионов строк, поэтому для поиска ряда строк потребовалось 2 минуты.

Вот трюк:

select id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 1000;

Просто поставьте ГДЕ с последним идентификатором, который вы сильно увеличили производительность. Для меня это было от 2 минут до 1 секунды:)

Другие интересные трюки здесь: http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

Он также работает со строками