Не стоит пользоваться OFFSET и LIMIT в запросах с разбиением на страницы
Прошли те дни, когда не надо было беспокоиться об оптимизации производительности баз данных. Время не стоит на месте. Каждый новый бизнесмен из сферы высоких технологий хочет создать очередной Facebook, стремясь при этом собирать все данные, до которых может дотянуться. Эти данные нужны бизнесу для более качественного обучения моделей, которые помогают зарабатывать. В таких условиях программистам необходимо создавать такие API, которые позволяют быстро и надёжно работать с огромными объёмами информации.
Если вы уже некоторое время занимаетесь проектированием серверных частей приложений или баз данных, то вы, вероятно, писали код для выполнения запросов с разбиением на страницы. Например — такой:
SELECT * FROM table_name LIMIT 10 OFFSET 40 Но если разбиение на страницы вы выполняли именно так, я с сожалением могу отметить, что вы делали это далеко не самым эффективным образом.
Хотите мне возразить? Можете не тратить время. Slack, Shopify и Mixmax уже применяют приёмы, о которых я хочу сегодня рассказать.
Назовите хотя бы одного разработчика бэкендов, который никогда не пользовался OFFSET и LIMIT для выполнения запросов с разбиением на страницы. В MVP (Minimum Viable Product, минимальный жизнеспособный продукт) и в проектах, где используются небольшие объёмы данных, этот подход вполне применим. Он, так сказать, «просто работает».
Но если нужно с нуля создавать надёжные и эффективные системы, стоит заблаговременно позаботиться об эффективности выполнения запросов к базам данных, используемых в таких системах.
Сегодня мы поговорим о проблемах, сопутствующих широко используемым (жаль, что это так) реализациям механизмов выполнения запросов с разбиением на страницы, и о том, как добиться высокой производительности при выполнении подобных запросов.
Что не так с OFFSET и LIMIT?
Как уже было сказано, OFFSET и LIMIT отлично показывают себя в проектах, в которых не нужно работать с большими объёмами данных.
Проблема возникает в том случае, если база данных разрастается до таких размеров, что перестаёт помещаться в памяти сервера. Но при этом в ходе работы с этой базой данных нужно использовать запросы с разбиением на страницы.
Для того чтобы эта проблема себя проявила, нужно, чтобы возникла ситуация, в которой СУБД прибегает к неэффективной операции полного сканирования таблицы (Full Table Scan) при выполнении каждого запроса с разбиением на страницы (в то же время могут происходить операции по вставке и удалению данных, и устаревшие данные нам при этом не нужны!).
Что такое «полное сканирование таблицы» (или «последовательный просмотр таблицы», Sequential Scan)? Это — операция, в ходе которой СУБД последовательно считывает каждую строку таблицы, то есть — содержащиеся в ней данные, и проверяет их на соответствие заданному условию. Известно, что этот тип сканирования таблиц является самым медленным. Дело в том, что при его выполнении выполняется много операций ввода/вывода, задействующих дисковую подсистему сервера. Ситуацию ухудшают задержки, сопутствующие работе с данными, хранящимися на дисках, и то, что передача данных с диска в память — это ресурсоёмкая операция.
Например, у вас есть записи о 100000000 пользователях, и вы выполняете запрос с конструкцией OFFSET 50000000 . Это значит, что СУБД придётся загрузить все эти записи (а ведь они нам даже не нужны!), поместить их в память, а уже после этого взять, предположим, 20 результатов, о которых сообщено в LIMIT .
Скажем, это может выглядеть так: «выбрать строки от 50000 до 50020 из 100000». То есть, системе для выполнения запроса нужно будет сначала загрузить 50000 строк. Видите, как много ненужной работы ей придётся выполнить?
Если не верите — взгляните на пример, который я создал, пользуясь возможностями db-fiddle.com.
Пример на db-fiddle.com
Там, слева, в поле Schema SQL , имеется код, выполняющий вставку в базу данных 100000 строк, а справа, в поле Query SQL , показаны два запроса. Первый, медленный, выглядит так:
SELECT * FROM `docs` LIMIT 10 OFFSET 85000; А второй, который представляет собой эффективное решение той же задачи, так:
SELECT * FROM `docs` WHERE id > 85000 LIMIT 10; Для того чтобы выполнить эти запросы, достаточно нажать на кнопку Run в верхней части страницы. Сделав это, сравним сведения о времени выполнения запросов. Оказывается, что на выполнение неэффективного запроса уходит, как минимум, в 30 раз больше времени, чем на выполнение второго (от запуска к запуску это время различается, например, система может сообщить о том, что на выполнение первого запроса ушло 37 мс, а на выполнение второго — 1 мс).
А если данных будет больше, то всё будет выглядеть ещё хуже (для того чтобы в этом убедиться — взгляните на мой пример с 10 миллионами строк).
То, что мы только что обсудили, должно дать вам некоторое понимание того, как, на самом деле, обрабатываются запросы к базам данных.
Учитывайте, что чем больше значение OFFSET — тем дольше будет выполняться запрос.
Что стоит использовать вместо комбинации OFFSET и LIMIT?
Вместо комбинации OFFSET и LIMIT стоит использовать конструкцию, построенную по такой схеме:
SELECT * FROM table_name WHERE id > 10 LIMIT 20 Это — выполнение запроса с разбиением на страницы, основанное на курсоре (Cursor based pagination).
Вместо того, чтобы локально хранить текущие OFFSET и LIMIT и передавать их с каждым запросом, нужно хранить последний полученный первичный ключ (обычно — это ID ) и LIMIT , в результате и будут получаться запросы, напоминающие вышеприведённый.
Почему? Дело в том, что в явном виде указывая идентификатор последней прочитанной строки, вы сообщаете своей СУБД о том, где ей нужно начинать поиск нужных данных. Причём, поиск, благодаря использованию ключа, будет осуществляться эффективно, системе не придётся отвлекаться на строки, находящиеся за пределами указанного диапазона.
Давайте взглянем на следующее сравнение производительности различных запросов. Вот неэффективный запрос.
Медленный запрос
А вот — оптимизированная версия этого запроса.
Быстрый запрос
Оба запроса возвращают в точности один и тот же объём данных. Но на выполнение первого уходит 12,80 секунд, а на второй — 0,01 секунда. Чувствуете разницу?
Возможные проблемы
Для обеспечения эффективной работы предложенного метода выполнения запросов нужно, чтобы в таблице присутствовал бы столбец (или столбцы), содержащий уникальные, последовательно расположенные индексы, вроде целочисленного идентификатора. В некоторых специфических случаях это может определять успех применения подобных запросов ради повышения скорости работы с базой данных.
Естественно, конструируя запросы, нужно учитывать особенности архитектуры таблиц, и выбирать те механизмы, которые наилучшим образом покажут себя на имеющихся таблицах. Например, если нужно работать в запросах с большими объёмами связанных данных, вам может показаться интересной эта статья.
Если перед нами стоит проблема отсутствия первичного ключа, например, если имеется таблица с отношением «многие-ко-многим», то традиционный подход, предусматривающий применение OFFSET и LIMIT , нам гарантированно подойдёт. Но его применение может привести к выполнению потенциально медленных запросов. В подобных случаях я порекомендовал бы использовать первичный ключ с автоинкрементом, даже если он нужен только для организации выполнения запросов с разбиением на страницы.
Если вам интересна эта тема — вот, вот и вот — несколько полезных материалов.
Итоги
Главный вывод, который мы можем сделать, заключаются в том, что всегда, о каких бы размерах баз данных ни шла речь, нужно анализировать скорость выполнения запросов. В наше время крайне важна масштабируемость решений, и если с самого начала работы над некоей системой спроектировать всё правильно, это, в будущем, способно избавить разработчика от множества проблем.
Как вы анализируете и оптимизируете запросы к базам данных?
- Базы данных
- разработка
Оператор OFFSET
OFF SET можно перевести как “вне набора”. Применительно к языку ассемблера ещё более вольный перевод может звучать так: за пределами набора команд. Почему этот оператор назвали именно так, поймёте, прочитав эту статью. Ну а вообще слово OFFSET переводится как “смещение”. И это, конечно, настоящий перевод. Но я позволил себе немного пофилософствовать )))
Оператор OFFSET возвращает адрес (смещение) некоторой метки данных относительно начала сегмента. Под смещением здесь понимается то количество байтов, которое отделяет метку данных от начала сегмента.
В защищённом режиме работы процессора смещения всегда являются 32-разрядными числами без знака. В реальном и виртуальном режимах адресации смещения всегда 16-разрядные.
С помощью оператора OFFSET в ассемблере можно объявлять переменные, то есть связывать адрес в памяти с именем переменной. По этой ссылке вы найдёте пример объявления строки. Но переменные могут быть, разумеется, не только строковыми.
.model tiny .code ORG 100h start: MOV AX, wVar ; AX = 65535 MOV DX, OFFSET wVar ; DX = 107 RET wVar DD 65535 ; Объявляем переменную типа WORD END start
Здесь мы объявили переменную wVar и назначили сразу ей какой-то значение (в нашем случае 65535). В программе мы записали ЗНАЧЕНИЕ этой переменной в регистр АХ. А вот потом, с помощью оператора OFFSET, мы получаем адрес (смещение) переменной wVar , относительно начала сегмена (в нашем случае это 100h). И в нашем случае смещение будет равно 107 в шестнадцатеричной системе, потому что:
- Сегмент начинается с адреса 100h ( ORG 100h )
- Команда MOV AX, wVar занимает 3 байта с адресами: 100h, 101h, 102h
- Команда MOV DX, OFFSET wVar занимает следующие 3 байта с адресами: 103h, 104h, 105h
- Команда RET занимает 1 байт по адресу: 106h
Ну и получается, что наша переменная wVar находится по адресу 107h.
Таким вот нехитрым образом можно получить адрес первого байта любой объявленной переменной. Соответственно, чтобы получить адрес следующего байта, надо просто прибавить 1 к смещению. Это обычно используется при работе со строками, когда надо получить отдельный символ строки. Пример:
.model tiny .code ORG 100h start: MOV AH, 09h ;Номер функции 09h MOV DX, OFFSET stroka+7 ;Адрес строки записываем в DX INT 21h RET stroka DB 'Hello, World. $' ;Строка для вывода END start END start
Здесь мы выводим не всю строку, а начиная с 8-го символа, потому что к смещению адреса переменной stroka мы прибавили 7. Но прибавили мы 7, а не 8, потому что адресация начинается с нуля, а не с единицы. Таким образом на экран будет выведено:
то есть только нужная нам часть строки.
На этом пока всё. Подключайтесь к группе Основы программирования в Телеграм, или к другим каналам (ссылки ниже), чтобы ничего не пропустить.
Что такое offset в программировании
Оператор LIMIT позволяет извлечь определенное количество строк:
SELECT * FROM Products ORDER BY ProductName LIMIT 4;
Оператор OFFSET позволяет указать, с какой строки надо начинать выборку. Например, выберем 3 строки, начиная со 2-й:
SELECT * FROM Products ORDER BY ProductName LIMIT 3 OFFSET 2;
Если нам надо выбрать вообще все строки, начиная с какой-то определенной, то оператор LIMIT можно опустить:
SELECT * FROM Products ORDER BY ProductName OFFSET 2;
Либо после LIMIT указать ключевое слово ALL :
SELECT * FROM Products ORDER BY ProductName LIMIT ALL OFFSET 2;
Смещение (информатика) — Offset (computer science)
В информатике смещение смещение в массиве или другом объекте структуры данных представляет собой целое число, указывающее расстояние (перемещение) между началом th объект и данный элемент или точка, предположительно внутри одного объекта. Концепция расстояния действительна, только если все элементы объекта имеют одинаковый размер (обычно задаются в байтах или словах ).
Например, в A как массив символов, содержащий «abcdef» , четвертый элемент, содержащий символ ‘d ‘ имеет смещение на три от начала A .
В языке ассемблера
В компьютерной инженерии и низкоуровневом программировании (например, язык ассемблера ), смещение смещение обычно обозначает количество ячеек адреса, добавленных к базовому адресу, чтобы перейти к конкретному абсолютному адресу. В этом (исходном) значении смещения для указания размера смещения используется только базовая адресная единица, обычно 8-битный байт. В этом контексте смещение иногда называют относительным адресом .
В предыдущем примере описывается косвенное способ обращения к ячейке памяти в формате сегмент: смещение. Например, предположим, что мы хотим обратиться к ячейке памяти 0xF867. Один из способов сделать это — сначала определить сегмент с начальным адресом 0xF000, а затем определить смещение 0x0867. Кроме того, нам также разрешено сдвигать шестнадцатеричный сегмент, чтобы достичь окончательного абсолютного адреса памяти. Здесь следует отметить, что мы можем достичь нашего окончательного абсолютного адреса разными способами.