Таблица 2gb с 10 миллионами строк, выбор поздней выборки страниц медленный

Ответ 1

Для кнопок Next и Prev вы можете использовать предложение WHERE вместо OFFSET.

Пример (используя LIMIT 10 - Пример данных, приведенных ниже): Вы находитесь на одной странице, которая показывает вам 10 строк с идентификаторами [2522,2520,2514,2513,2509,2508,2506,2504,2497,2496]. Это в моем случае создается с помощью

select *
from link l
order by l.id desc
limit 10
offset 999000

На следующей странице вы будете использовать

limit 10
offset 999010

получение строк с идентификаторами [2495,2494,2493,2492,2491,2487,2483,2481,2479,2475].

Для предыдущей страницы вы использовали бы

limit 10
offset 998990

получение строк с идентификаторами [2542,2541,2540,2538,2535,2533,2530,2527,2525,2524].

Все вышеперечисленные запросы выполняются через 500 мс. Используя "трюк", предложенный Sanj, он все равно занимает 250 мс.

Теперь с данной страницей с minId=2496 и maxId=2522 мы можем создавать запросы для кнопок Next и Last с помощью предложения WHERE.

Следующая кнопка:

select *
from link l
where l.id < :minId -- =2496
order by l.id desc
limit 10

Результирующие идентификаторы: [2495,2494,2493,2492,2491,2487,2483,2481,2479,2475].

Кнопка Prev:

select *
from link l
where l.id > :maxId -- =2522
order by l.id asc
limit 10

Результирующие идентификаторы: [2524,2525,2527,2530,2533,2535,2538,2540,2541,2542].

Чтобы отменить порядок, вы можете использовать запрос в подзапросе:

select *
from (
    select *
    from link l
    where l.id > 2522
    order by l.id asc
    limit 10
) sub
order by id desc

Результирующие идентификаторы: [2542,2541,2540,2538,2535,2533,2530,2527,2525,2524].

Эти запросы выполняются в режиме "нет времени" (менее 1 мс) и обеспечивают тот же результат.

Вы не можете использовать это решение для создания номеров страниц. Но я не думаю, что вы собираетесь выводить 200 тыс. Номеров страниц.

Данные теста:

Данные, используемые для примера и эталонных тестов, были созданы с помощью

CREATE TABLE `link` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `url` VARCHAR(255) NOT NULL,
    `website` VARCHAR(100) NULL DEFAULT NULL,
    `state` VARCHAR(10) NULL DEFAULT NULL,
    `type` VARCHAR(100) NULL DEFAULT NULL,
    `prio` VARCHAR(100) NULL DEFAULT NULL,
    `change` VARCHAR(100) NULL DEFAULT NULL,
    `last` VARCHAR(100) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `url` (`url`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB;

insert into link
    select i.id
        , concat(id, '-', rand()) url
        , rand() website
        , rand() state
        , rand() `type`
        , rand() prio
        , rand() `change`
        , rand() `last`
    from test._dummy_indexes_2p23 i
    where i.id <= 2000000
      and rand() < 0.5

где test._dummy_indexes_2p23 - таблица, содержащая 2 ^ 23 идентификаторов (около 8M). Таким образом, данные содержат около 1M строк, случайным образом пропускающих каждый второй id. Размер таблицы: 228 МБ

Ответ 2

Это, скорее всего, связано с "ранним поиском строки"

MySQL может быть вынужден выполнять "поиск в конце строки". Попробуйте выполнить запрос

SELECT  l.*
FROM    (
        SELECT  id
        FROM    link
        ORDER BY
                id
        LIMIT 999999 , 50
        ) q
JOIN    link l
ON      l.id = q.id

Проверьте эту статью

Предложение ограничения MySQL и низкий уровень поиска

Ответ 3

Я обновил свой SQL-запрос до этого, и это занимает меньше времени.

 SELECT * FROM link ORDER BY id LIMIT 999999 , 50  ;

Ответ 4

Из-за большого объема данных

Есть несколько советов по улучшению времени ответа на запрос:

• Измените механизм хранения Innodb на myisam.
• Создание разбиения таблиц (https://dev.mysql.com/doc/refman/5.7/en/partitioning-management.html)
• Кластер Mysql (http://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-overview.html)
• Увеличьте емкость оборудования.

Спасибо

Ответ 5

Прежде всего, работая на вашем столе без какого-либо заказа, не гарантирует, что ваш запрос вернет те же данные, если он будет работать дважды. Лучше добавить предложение ORDER BY. Принимая id как хороший кандидат, так как это ваш первичный ключ и кажется уникальным (как это значение auto_increment).

Вы можете использовать это как свою базу:

SELECT * FROM link ORDER BY id LIMIT 50;

Это даст вам первые 50 строк в вашей таблице.

Теперь для следующих 50 строк вместо OFFSET мы могли бы сохранить наше последнее местоположение в запросе.

Вы сохранили бы идентификатор из последней последней строки последнего запроса из предыдущего запроса и использовали его в следующем запросе:

SELECT * FROM link WHERE id > last_id ORDER BY id LIMIT 50;

Это даст вам следующие 50 строк после последнего id.

Причина, по которой ваш запрос выполняется медленно при высоких значениях OFFSET, заключается в том, что mysql должен запускаться во всех строках в заданном OFFSET и возвращать последнее число строк LIMIT. Это означает, что чем больше OFFSET, тем медленнее будет выполняться запрос.

Решение, показанное выше, не зависит от OFFSET, поэтому запрос будет работать с той же скоростью, независимо от текущей страницы.

См. также эту полезную статью, которая объясняет несколько других вариантов, которые вы можете выбрать: http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/