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

Question

[Дмитрий Масленников]

Есть большая выборка данных (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;

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

Comments

[PrAw]

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

[PrAw]

дальше, есть конструкция 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

[kazmiruk]

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

[Дмитрий Масленников]

В итоге пришел к решению на уровне логики приложения. В основной базе постоянно содержится 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

[DrunkMaster]

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

Comments

[Дмитрий Масленников]

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

[DrunkMaster]

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

[DrunkMaster]

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

[Дмитрий Масленников]

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

Answer 3

[ThunderCat]

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

Comments

[Дмитрий Масленников]

ThunderCat на этом запросе MySQL вообще завис))