Как оптимизировать постраничный вывод из большого объема данных?

Question

Дмитрий Масленников @aprenoir

программный архитектор

MySQL

Как оптимизировать постраничный вывод из большого объема данных?

Есть большая выборка данных (50 млн.), надо вывести из нее первые 10 записей по наиболее свежей дате (после чего подзагружать еще по 10 записей). Сложность в том, что данные сгруппированы в 10 разделов (в каждом от 2-3 до 10 млн. записей) и выводить надо только данные из этого раздела.

SELECT * FROM posts WHERE type=1 ORDER BY updated DESC LIMIT 10;

По этому запросу EXPLAIN показывает, что MySQL сначала выбирает несколько млн. строк, соответствующих нужному типу, после чего их сортирует по дате.

В таблице стоит составной индекс (type, updated), но он не сильно помогает. MySQL использует только первую его часть.

Тоже самое при выводе последующих страниц

SELECT * FROM posts WHERE type=1 AND updated<offset
ORDER BY updated DESC LIMIT 10;

Как оптимизировать этот запрос?

Вопрос задан более трёх лет назад
395 просмотров

7 комментариев

Подписаться 2 Оценить 7 комментариев

PrAw @remzalp

я покажусь капитаном Очевидность, но ИНДЕКСЫ ТОЧНО ЕСТЬ?
приведите пожалуйста выдачу "show create table posts"

Написано более трёх лет назад
PrAw @remzalp

дальше, есть конструкция USE INDEX
SELECT * FROM posts USE INDEX(type, updated,offset)
WHERE type=1 AND updated

Написано более трёх лет назад
Дмитрий Масленников @aprenoir Автор вопроса

PrAw: вот выдача:

CREATE TABLE `posts` (
...
`type` int(11) unsigned NOT NULL DEFAULT '0',
...
`updated` int(11) unsigned NOT NULL DEFAULT '0',
...
KEY `flag_sort` (`updated`),
KEY `type_main` (`type`)
KEY `composite` (`type`,`updated`),
) ENGINE=InnoDB AUTO_INCREMENT=53998754 DEFAULT CHARSET=utf8

Написано более трёх лет назад
kazmiruk @kazmiruk

KEY `type_main` (`type`) лишний у вас. К вопросу не относится, но не порядок )

Написано более трёх лет назад
Дмитрий Масленников @aprenoir Автор вопроса

PrAw: по этому запросу не совсем понятно:

SELECT * FROM posts USE INDEX(type, updated,offset)
WHERE type=1 AND updated

как обеспечивается сортировка? offset - это те же данные из updated, полученные из предыдущей выборки.

делал такой запрос:

SELECT * FROM posts USE INDEX(type_main, flag_sort)
WHERE type=1 AND updated

EXPLAIN выдает такое же количество строк, как и при использовании первоначального запроса.

Написано более трёх лет назад
Дмитрий Масленников @aprenoir Автор вопроса

kazmiruk: KEY `type_main` (`type`) - этот индекс создавался специально для экспериментов.

Написано более трёх лет назад
Дмитрий Масленников @aprenoir Автор вопроса

kazmiruk: полагаю там вся проблема не в индексе, он работает, но выборка по нему идет на несколько миллионов, после чего эти несколько миллионов сортируются по колонке updated.

полагаю, что надо использовать подход, где сначала идет сортировка по updated, после чего из нее выбираются уже 10 нужных записей (в соответствии с подходящим типом, т.к. ORDER BY нужен перед WHERE), но вопрос как это реализовать?

сейчас смотрю курсоры mysql, как советовал DrunkMaster. думаю это подходящий вариант, но пока еще не понял, как составить правильный запрос.

Написано более трёх лет назад

Решения вопроса 1

Комментировать

Пригласить эксперта

Ответы на вопрос 2

4 комментария

Дмитрий Масленников @aprenoir Автор вопроса

да, это может быть вариантом, но как именно составить запрос в моем случае? надо сразу ограничить выборку. полагаю в начале следует отталкиваться от индекса по колонке updated, после чего из данных выбирать 10 требуемых значений по type. не совсем понятно как это реализовать на уровне логики.

Написано более трёх лет назад
DrunkMaster @DrunkMaster

Дмитрий Масленников: Сейчас запроса под рукой с курсором нет, без него вам нужно уменьшить количество выбираемых записей через добавление второго параметра:
SELECT * FROM posts WHERE type=1 AND ...
А в AND уже может быть что угодно. Например вам надо выбрать 10 последних по дате и вы знаете что записи добавляются по 1 в день, тогда ограничив AND скажем последним месяцем вы не упустите ничего важного и сократите первичную выборку с 50 млн. до 0,5 допустим. Так же можно с id играться. Базу делите пополам по id и запрашиваете только большую часть.

Написано более трёх лет назад
DrunkMaster @DrunkMaster

Дмитрий Масленников: И вообще, у вас подпись "программный архитектор"!!! и я вам что-то советую )

Написано более трёх лет назад
Дмитрий Масленников @aprenoir Автор вопроса

DrunkMaster: да, в итоге пришел к решению на уровне программной логики приложения

Написано более трёх лет назад

1 комментарий

Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации

Похожие вопросы

MySQL

+1 ещё

Простой
Как извлечь топ 15 очков из таблицы чтобы игроки не дублировались?
- 1 подписчик
- 14 часов назад
- 84 просмотра
1

ответ
MySQL

Простой
Как сохранить mysql базу?
- 1 подписчик
- вчера
- 81 просмотр
1

ответ
PHP

+1 ещё

Простой
Как исправить ошибку Uncaught Error: Call to undefined function mysql_real_escape_string?
- 1 подписчик
- вчера
- 97 просмотров
3

ответа
PHP

+2 ещё

Средний
Ошибка при регистрации PHP, в Unity, что делать?
- 1 подписчик
- 20 апр.
- 84 просмотра
0

ответов
MySQL

Простой
Объясните CASE WHEN THEN?
- 1 подписчик
- 20 апр.
- 101 просмотр
2

ответа
MySQL

Простой
Как обновить определенные значения в базе, в зависимости от значения переменной?
- 1 подписчик
- 20 апр.
- 41 просмотр
1

ответ
PHP

+2 ещё

Простой
Как в Drupal 10 массово проставить noindex для >1000 страниц?
- 1 подписчик
- 18 апр.
- 49 просмотров
1

ответ
MySQL

+1 ещё

Средний
Как восстановить базу данных mysql, если служба mysql не запускается на windows?
- 1 подписчик
- 18 апр.
- 101 просмотр
1

ответ
WordPress

+1 ещё

Простой
Как импортировать большую базу данных в Wordpress?
- 1 подписчик
- 18 апр.
- 64 просмотра
3

ответа
MySQL

+1 ещё

Средний
MySQL ошибка InnoDB: Attempted to open a previously opened tablespace. Куда копать?
- 1 подписчик
- 15 апр.
- 58 просмотров
1

ответ
Показать ещё Загружается…

Программист C++ Builder / базы данных MySQL

RU Electronics • Москва

от 180 000 до 200 000 ₽

Инженер технической поддержки с английским языком и знанием PHP/MySQL

IT-Aces

от 100 000 до 150 000 ₽

PHP FullStack Developer (Middle+)

ГК «Талант» • Сочи

от 100 000 до 200 000 ₽

Переделать скрипт на питоне (добавить прокси с авторизацией)

23 апр. 2024, в 09:39

500 руб./за проект

Довести разработку flutter приложения до логичного конца

23 апр. 2024, в 09:24

15000 руб./за проект

SMM для HR

23 апр. 2024, в 08:58

15000 руб./за проект

я покажусь капитаном Очевидность, но ИНДЕКСЫ ТОЧНО ЕСТЬ?
приведите пожалуйста выдачу "show create table posts"
дальше, есть конструкция USE INDEX
SELECT * FROM posts USE INDEX(type, updated,offset)
WHERE type=1 AND updated
PrAw: вот выдача:

CREATE TABLE `posts` (
...
`type` int(11) unsigned NOT NULL DEFAULT '0',
...
`updated` int(11) unsigned NOT NULL DEFAULT '0',
...
KEY `flag_sort` (`updated`),
KEY `type_main` (`type`)
KEY `composite` (`type`,`updated`),
) ENGINE=InnoDB AUTO_INCREMENT=53998754 DEFAULT CHARSET=utf8
KEY `type_main` (`type`) лишний у вас. К вопросу не относится, но не порядок )
PrAw: по этому запросу не совсем понятно:

SELECT * FROM posts USE INDEX(type, updated,offset)
WHERE type=1 AND updated

как обеспечивается сортировка? offset - это те же данные из updated, полученные из предыдущей выборки.

делал такой запрос:

SELECT * FROM posts USE INDEX(type_main, flag_sort)
WHERE type=1 AND updated

EXPLAIN выдает такое же количество строк, как и при использовании первоначального запроса.
kazmiruk: KEY `type_main` (`type`) - этот индекс создавался специально для экспериментов.
kazmiruk: полагаю там вся проблема не в индексе, он работает, но выборка по нему идет на несколько миллионов, после чего эти несколько миллионов сортируются по колонке updated.

полагаю, что надо использовать подход, где сначала идет сортировка по updated, после чего из нее выбираются уже 10 нужных записей (в соответствии с подходящим типом, т.к. ORDER BY нужен перед WHERE), но вопрос как это реализовать?

сейчас смотрю курсоры mysql, как советовал DrunkMaster. думаю это подходящий вариант, но пока еще не понял, как составить правильный запрос.

Answer 1 · 2017-01-19 17:55:23

В итоге пришел к решению на уровне логики приложения. В основной базе постоянно содержится 0.5-1 млн наиболее свежих записей, а остальные выносятся в архивную. Скрипт-архиватор раз в сутки переносит старые записи в архив. Первоначально пользователям выдаются только наиболее свежие данные из основной базы (99% запросов), а если их недостаточно, то производится поиск в архивной.

P.S. В дополнение, если пользователь начал листать ленту - сделал вывод не 10, а 50 записей. При скроллинге клиент 1 раз выдает данные из запроса, а остальные 4 пакета помещает в массив и выдает по мере необходимости. Когда данные клиента заканчиваются, делается новый запрос. Сразу 50 не вывожу, чтобы не тормозить браузер (там много графики). Что-то подобное встречается в vk и ряде других крупных сайтов.

P.P.S. Итоговое решение на уровне MySQL получилось следующим. Провел вручную тестирование с выборками на основе жестко прописанного (use index) простого или составного индекса. Наилучшим по производительности оказалось использование составного индекса (например, (type, update)), использование которого жестко прописано в коде приложения в зависимости от конкретного типа выборки (по-умолчанию, в ряде случает MySQL выбирает не самый производительный индекс). Удивило расхождение данных, выдаваемых EXPLAIN с реальными показателями производительности. Так, простой индекс (EXPLAIN показывает rows 10) работал в сотни раз медленнее составного с rows в несколько млн. записей.

P.P.P.S. В общем, проблема была в неправильном выборе движком MySQL индекса по которому проводился поиск (в некоторых случаях использовался только простой индекс, когда лучше было использовать составной, а в некоторых поиск проводился сразу по 2-м индексам с объединением результатов). При прописывании USE INDEX вручную (на каждый конкретный случай) производительность многократно возросла.

Answer 2 · 2017-01-19 12:03:26

DrunkMaster @DrunkMaster

Гуглите "Курсоры MySQL"

Ответ написан более трёх лет назад

4 комментария

Answer 3 · 2017-01-19 15:33:46

SELECT * FROM (
  SELECT * 
  FROM posts 
  WHERE updated < offset
  ORDER BY updated DESC
 ) AS `table` 
WHERE type = 1
LIMIT 10

или так, кажется, пусть меня поправит кто нть если косяк увидит, пишу по памяти

SELECT * FROM post AS p1
INNER JOIN
     (SELECT *
     FROM post
     WHERE updated < offset
     ORDER BY updated DESC
     ) as p2
ON p1.id = p2.id
WHERE p2.type = 1

Как оптимизировать постраничный вывод из большого объема данных?

Войдите, чтобы написать ответ

Минуточку внимания

Войдите на сайт