Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
При умеренных объёмах базы данных в использовании offset нет ничего плохого, но со временем база данных растёт и запросы начинают «тормозить». Становится актуальным ускорение запросов.
Очевидно, если причина в росте объёмов базы данных, то используя главный принцип дзюдо «падающего - толкни, нападающего - тяни», следует ещё увеличить объём, в данном случае путём добавления нового поля в таблицы для последующей сортировки по нему.
Вообще говоря, этот приём не нов, например, https://medium.com/swlh/sql-pagination-you-are-probably-doing-it-wrong-d0f2719cc166. Но, на мой, используется незаслуженно редко.
Собственно, цель этой статьи привлечь внимание к этому приёму и показать на реальных базах данных его несложную реализацию и эффективность сравнительно со стандартным использованием offset.
Зачастую при запросах к БД используют offset и пока таблицы не более 100 000 строк запросы работают быстро, но как только объёмы данных увеличиваются и пользователю необходимо перейти на страницу 1001, возникают проблемы со скоростью ответа.
Замечу, что большинство поисковых систем не показывают количество найденных страниц, а просто предлагают перейти на следующую страницу.
Хотя ранее данная информация присутствовала, но видимо разработчики произвели оптимизацию:
Предложение состоит в том чтобы не использовать offset в запросах и не выводить общее количество записей, а внедрить сортируемые данные в таблицы.
Произведём замеры при получение одних и тех же данных в сортируемой и не сортируемой таблице (выполняю локально):
Запрос к не сортируемой таблице:
sql: 'select table_name.* from table_name group by ID limit ? offset ?'
Limit offset | Duration | Bytes | Deceleration |
limit 10 offset 0 | 179ms | 1320 | |
limit 10 offset 1000 | 191ms | 1320 | 1.06 |
limit 10 offset 100 000 | 606ms | 1320 | 3.38 |
limit 10 offset 200 000 | 790ms | 1320 | 4.41 |
limit 10 offset 300 000 | 864ms | 1320 | 4.82 |
limit 10 offset 400 000 | 1115ms | 1320 | 6.22 |
По результатам шести запросов с offset от 0 до 400 000, мы видим увеличение скорости выполнения запроса более чем в 6 раз. Это связанно с тем, что оператор offset является не эффективным и запрос выгрузил все 400 000 записей, а уже потом проверил их на соответствие условиям. Так же отдельного внимания стоит уделить запросу на получение значения общего числа найденных сущностей, так как данный запрос используется для указания числа страниц. Длительность выполнения данного запроса составляет около 3000ms на таблице с 400 000 записей. Данное время можно смело прибавлять к общему значению Duration, так как единожды данный запрос необходимо будет выполнить.
Пример запроса: 'select count(*) from table_name'.
Запрос к сортируемой таблице:
sql: 'select table_name.* from table_name where (table_name.sortID > ?) group by sortID order by sortID asc limit ?'
Оператор offset в данном запросе не используется. В качестве переменной для сравнения с sortID используем sortID 0 или значение sortID последней сущности из предыдущего запроса.
SortID limit | Duration | Bytes | Deceleration |
sortID 0 limit 10 | 183ms | 1320 | |
sortID 1000 limit 10 | 189ms | 1320 | 1,032 |
sortID 100000 limit 10 | 190ms | 1320 | 1,038 |
sortID 200000 limit 10 | 192ms | 1320 | 1,049 |
sortID 300000 limit 10 | 179ms | 1320 | 0,978 |
sortID 400000 limit 10 | 180ms | 1320 | 0,983 |
По результатам шести запросов с sortID от 0 до 400 000, мы видим увеличение времени выполнения запроса близко к погрешности. Это связано с тем что БД загружает только 10 записей.
Результаты эксперимента на графике:
Ось X - Значение offset/sortID.
Ось Y - Время выполнения запроса в миллисекундах.
На графике наглядно видно, что в не сортируемых таблицах при значении offset/sortID > 1000 резко возрастает время выполнения запроса. Очевидно при увеличении количества записей время выполнения запроса будет расти.
Как оптимизировать существующие таблицы и запросы к БД.
Добавляем в таблицу сортируемое поле. Создаём миграцию:
ALTER TABLE table_nameADD COLUMN sortID bigint(15) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUEFIRST;Переписываем запросы:
Было: '
select table_name.* from table_name group by ID limit ? offset ?' (limit =10, offset=100 000)
Стало: 'select table_name.* from table_name where (table_name.sortID > ?) group by sortID order by sortID asc limit ?' (sortID =100 000, limit =10).
Итог: Данные действия с таблицами позволят уменьшить время выполнения запросов в разы при объёмных таблицах и время выполнения запросов будет стабильным даже если таблицы будут большими. Но необходимо понимать, что за данную оптимизацию мы платим тем, что мы испытываем сложности с дополнительной сортировкой (например, по алфавиту), но на мой взгляд, эта плата не значительна. По сути простой и быстрый метод ускорения запросов, но во многих проектах до сих пор используется классический offset.
