Некоторое время вся необходимая оптимизация сводилась к простому добавлению индексов там, где про них забыли. Первым по-настоящему сложным моментом стала лента комментариев — скорость их добавления достигла 2000 в день, а их общее число — более 120 000. К этому моменту общая лента комментариев на сайте уже безбожно тормозила — 3 секунды для первой страницы, и 7 секунд для последней.
Здесь я хочу описать некоторые приёмы, позволившие ускорить генерацию более чем в 30 раз (теперь 0.1 сек).
Первым делом был изучен вопрос постранички путём поиска готовых рецептов на Хабре. Была найдена такая статья: habrahabr.ru/blogs/mysql/44608/
К сожалению, методики описанные в ней для моего случая не подходили. Передавать на следующую страницу параметр last_page_id не подходил из-за стандартного вида разбивки «1… 4 5 6 7 8… 100» вместо предложеных автором «Вперёд — Назад». Грубая оценка количества записей вместо select count(*) также не подходила из-за вида пагинатора — в нём всегда присутствует ссылка на последнюю страницу.
Пришлось ускорять выборку своими силами. И вот что было сделано:
- Пре-выборка идентификаторов
Выборка разбивается на два запроса. Первый получает только id подходящих строк. Второй запрос получает полностью строки с заданными id.
Опытным путем было установлено, что скорость выполнения
«select * from»
и
«select id from» + «select * from where id in (...)»
при offset=100000
различается в 10 раз, а при сортировке не по полю id — почти в 30 раз. В пользу второго варианта. - Денормализация
В запросе для выборки идентификаторов не должно быть никаких join-ов. Если цеплять лишнюю таблицу для проверки в ней какого-то поля, то сервер не сможет использовать один лишь индекс для выборки нужных строк. Ему придется проделать все джойны, а при limit 100000,10 — их будет больше 100000.
В моём случае — такой джойн проверял область видимости поста или фотки, к которым сделан коммент. Если документ только для друзей — в общую ленту он не попадает. Решение заключалось в добавлении двух триггеров — на добавление коммента и на изменение видимости документа. И добавлении флага for_all в таблице комментариев. - Кэширование count
Запрос select count(*) с наложенными условиями where практически не поддается оптимизации — перебирать индекс в таком случае все равно придётся. Результат может быть закеширован самой базой, но иногда добавление комментов может происходить слишком часто. Тогда возможным решением является кэширование числа строк на определенный промежуток времени, например пять минут. Это может привести к периодическому «выпаданию» из списка последних результатов, но техника из следующего пункта лишает метод этого недостатка. - Инверсия сортировки
Даже после всех проведенных оптимизации, все равно на выборке limit 100000,10 сервер вынужден просматривать индекс, по которому идёт сортировка. И пропустить сто тысяч лишних строк в нем. Пустая работа. Чтобы не заставлять его делать её, можно для второй половины страниц разбивки менять порядок сортировки на обратный. Например, страница номер 10000 вместо
«order by rating desc limit 100000,10»
станет выбираться
«order by rating asc limit О,10»
Ну, и при инверсии надо не забыть перевернуть результат еще раз — я делаю это при помощи array_reverse в PHP
Описанные приёмы относятся скорее не только к постраничной разбивке, а в общем — к задаче произвольной выборки отсортированных данных из большой таблицы. Но на практике это обычно одно и то же.
