для чего используется limit select from orders limit 10
Использование MySQL LIMIT
В Mysql для ограничения количества результатов используется инструкция LIMIT:
SELECT * FROM users ORDER BY id DESC LIMIT 10
последние 10 записей из таблицы users
Смещение
Чтобы вернуть результаты с 6 по 15, нужно использовать такой синтаксис:
SELECT * FROM users ORDER BY id DESC LIMIT 5, 10
последние 10 записей из таблицы users, но без первых 5ти
Или то же самое:
SELECT * FROM users ORDER BY id DESC LIMIT 10 OFFSET 5
Скорость
С точки зрения производительности, LIMIT работает очень быстро.
Однако использование больших величин смещения может привести к деградации производительности.
Например, запрос:
SELECT * FROM users ORDER BY id DESC LIMIT 10
будет работать значительно быстрее, чем запрос:
SELECT * FROM users ORDER BY id DESC LIMIT 9999, 10
Во втором случае MySQL пропустит 10 тыс. строк, чтобы показать всего 10.
Этот текст был написан несколько лет назад. С тех пор упомянутые здесь инструменты и софт могли получить обновления. Пожалуйста, проверяйте их актуальность.
Что такое индексы в Mysql и как их использовать для оптимизации запросов
Как исправить ошибку доступа к базе 1045 Access denied for user
Основные понятия о шардинге и репликации
Настройка Master-Master репликации на MySQL за 6 шагов
Примеры ad-hoc запросов и технологии для их исполнения
Как создать и использовать составной индекс в Mysql
Анализ медленных запросов (профилирование) в MySQL с помощью Percona Toolkit
Уменьшение размера картинок при сохранении качества
Типы и способы применения репликации на примере MySQL
Настройка Master-Slave репликации на MySQL за 6 простых шагов
Правильная настройка Mysql под нагрузки и не только. Обновлено.
Check-unused-keys для определения неиспользуемых индексов в базе данных
Запрос для определения версии Mysql: SELECT version()
И как правильно работать с длительными соединениями в MySQL
3 примера установки индексов в JOIN запросах
Анализ медленных запросов с помощью EXPLAIN
Быстрый подсчет уникальных значений за разные периоды времени
Описание, рекомендации и значение параметра query_cache_size
Что значит и как это починить
Использование партиций для ускорения сложных удалений
Правила выбора типов данных для максимальной производительности в Mysql
Улучшение производительности Web сервера на Ubuntu
Включение и использование логов ошибок, запросов и медленных запросов, бинарного лога для проверки работы MySQL
SQL Limit: руководство для начинающих
Оператор SQL LIMIT ограничивает количество строк, возвращаемых запросом. Оператор LIMIT появляется в конце запроса после любых операторов ORDER BY. Вы можете запустить оператор LIMIT в определённой строке, используя аргумент смещения.
Когда вы работаете с SQL, вы можете захотеть получить только определённое количество строк из запроса.
Например, вы можете выбрать трёх лучших сотрудников на основе количества полученных ими наград «Сотрудник месяца». Это легче читать, чем список всех сотрудников и количество заработанных наград «Сотрудник месяца».
Есть встроенная функция SQL, которая позволяет вам выполнять это действие: SQL LIMIT. Лимит позволяет ограничить количество записей в запросе определённым количеством.
В этом руководстве мы обсудим, как использовать команду SQL LIMIT. Мы также обсудим, как использовать TOP, эквивалент LIMIT для экземпляров SQL Server.
Обновление запроса
Чтобы получить информацию из базы данных, вам нужно написать SQL-запросы. Запросы почти всегда начинаются с оператора SELECT. Мы используем этот оператор, чтобы сообщить базе данных, какие столбцы мы хотим получить. Запросы часто включают термин FROM, который используется для указания, из какой таблицы оператор получит данные.
Вот синтаксис SQL-запроса:
SELECT column_name FROM table_name WHERE conditions_apply;
Вот пример SQL-запроса, который вернёт список имён всех сотрудников в таблице сотрудников :
Наш запрос возвращает следующее:
Что такое SQL LIMIT?
Предложение SQL LIMIT ограничивает количество строк, возвращаемых из запроса. Синтаксис предложения LIMIT: SELECT * FROM table LIMIT X;. X представляет, сколько записей вы хотите получить.
Например, вы можете использовать предложение LIMIT для получения пяти лучших игроков в таблице лидеров. Или вы можете использовать этот пункт для пяти крупнейших клиентов, заказавших наибольшее количество товаров.
Вот синтаксис запроса, в котором используется предложение LIMIT:
SELECT column_name FROM table_name LIMIT number_to_limit;
Ваш LIMIT-номер должен быть положительным. Допустим, вы хотите получить записи из нижней части списка. Вы должны использовать оператор SQL ORDER BY, чтобы упорядочить их в порядке убывания. Затем вы должны использовать оператор LIMIT:
SELECT * FROM table ORDER BY column DESC LIMIT X;
Это синтаксис для упорядочивания таблицы в порядке убывания и ограничения вывода запроса. Мы использовали предложение ORDER BY для получения только первых X записей.
Пример SQL LIMIT
Давайте воспользуемся примером, чтобы проиллюстрировать предложение SQL LIMIT в действии. Предположим, мы хотим получить список имён трёх наиболее длительных сотрудников нашей организации.
Мы также хотим получить даты, когда они были наняты. Мы могли бы использовать следующий запрос для получения этой информации:
SELECT name, hired_date FROM employees ORDER BY hired_date DESC LIMIT 3;
Наш запрос возвращает следующее:
Наш запрос вернул трёх самых продолжительных сотрудников по дате приёма на работу.
Смещение предложения SQL LIMIT
Если вы хотите сместить предложение LIMIT — изменить начальную точку — вы можете указать второй параметр. Вот синтаксис запроса SQL LIMIT, который смещает запрос:
SELECT column_name FROM table_name LIMIT starting_point, rows_to_return;
Начальная точка — это смещение запроса (где будут начинаться ваши результаты). «Rows_to_return» — это количество строк, которое вы хотите получить по запросу.
SELECT name FROM employees ORDER BY salary DESC LIMIT 4, 1;
Мы указываем, что хотим получить элемент, который занимает пятое место в нашем списке — помните, это первая строка. Мы хотим получить только одну запись. Вот результат нашего запроса:
SELECT name FROM employees ORDER BY salary DESC OFFSET 4 LIMIT 1;
SQL TOP
В SQL Server и MS Access предложение SQL TOP используется для ограничения количества строк, возвращаемых запросом. Вот синтаксис SQL-запроса, в котором используется TOP:
SELECT TOP number column_names FROM table_name;
Вот пример запроса, который вернёт двух самых высокооплачиваемых сотрудников из таблицы сотрудников:
SELECT TOP 2 * FROM employees ORDER BY salary DESC;
Наш запрос возвращает следующее:
За предложением TOP может следовать ключевое слово PERCENT, которое извлекает процент строк вместо количества фиксированных строк. Итак, если вы хотите получить 25% лучших сотрудников на основе их заработной платы, вы можете использовать этот запрос:
SELECT TOP 25 PERCENT * FROM employees
ORDER BY salary DESC;
Наш запрос к базе данных с восемью записями возвращает следующее:
Вывод
Вы можете использовать оператор SQL LIMIT (или TOP в SQL Server и MS Access), чтобы ограничить количество строк, возвращаемых запросом. Это полезно, если вам нужно видеть только верхние или нижние записи запроса.
Урок 5. Ограничение выборки
Кроме фильтров и сотрировок, в SELECT запросах можно также ограничивать выборку. Например, когда вам нужные не все данные, а скажем только первые 10 строк. Для этого существует конструкия LIMIT и давайте попробуем вывести 5 самых дорогих товаров.
Разберем запрос. Сперва мы получаем все товары, отсортированные по цене в обратном порядке ORDER BY price DESC. То есть дорогие товары будут сверху. А затем мы указыаем LIMIT 5, что означает, что нам нужны только 5 первых записей. Так мы получаем таблицу самых дорогих товаров.
LIMIT можно использовать совместно с конструкций WHERE. Например если мы хотим получить самые дорогие товары, которые есть на складе, то нужно после products добавить WHERE count > 0:
Обратите внимание на порядок следования блоков запроса:
1. Сперва идет SELECT FROM — выбрать ИЗ;
2. Затем блок условие WHERE;
3. После сортировка ORDER BY;
4. И завершает ограничение LIMIT.
Первый блок SELECT FROM обязательный, так как он говорит что мы хотим сделать.
Остальные блоки опциональны. То есть мы можем убрать любой из них, главное чтобы сохранялся порядок: (1) WHERE (2) ORDER BY (3) LIMIT.
Но вернемся снова к LIMIT, ограничение выборки — это не единственная возможность этого оператора.
С помощью LIMIT можно делать смещение (OFFSET). Например искать не первые 5 самых дорогих товаров, а следующую пятерку.
Для этого нужно сперва указать количество элементов для пропуска, а уже после количество элементов, которое нужно получить. Напишем:
Запустим, получили 3 товара. Почему только три. Если мы удалим блок LIMIT и выполним запрос. То увидим 8 товаров у которых count > 0.
Соответственно когда мы добавляем LIMIT 5, 5, то сперва пропускается первая пятерка дорогих товаров, а затем 5 выводится. Но так как после пропуска остается всего 3, то их мы и видим.
Если мы вместо первой пятерки мы укажем 3, то пропустим первые 3 записи получим оставшиеся 5. Еще раз повторюсь, первое число — это сколько нужно пропустить, а второе, сколько получить.
Благодаря этой особенности, с помощью LIMIT можно организовывать постраничный просмотр информации. Когда значение пропуска расчитывается в программе или на сайте динамически в зависимости от страницы, на которой находится пользователь.
На этом мы закончим знакомство c SQL, а далее вас ждут два урока по понимаю принципов работы баз данных, а также по установке и настройке MySQL.
А после регистрации вы сможете потренироваться написании SQL-запросов в специальном тренажере.
Следующий урок
Урок 6. Принципы работы баз данных
В этом уроке вы узнате как работают реляционные базы данных, а также что такое СУБД и клиент-серверное взаимодействие.
Команда LIMIT
Синтаксис
Таблицы для примеров
| id айди | name имя | age возраст | salary зарплата |
|---|---|---|---|
| 1 | user1 | 23 | 400 |
| 2 | user2 | 25 | 500 |
| 3 | user3 | 23 | 500 |
| 4 | user4 | 30 | 900 |
| 5 | user5 | 27 | 500 |
| 6 | user6 | 28 | 900 |
Пример
Давайте выберем первые 3 записи из таблицы:
Результат выполнения кода:
| id айди | name имя | age возраст | salary зарплата |
|---|---|---|---|
| 1 | user1 | 23 | 400 |
| 2 | user2 | 25 | 500 |
| 3 | user3 | 23 | 500 |
Пример
Результат выполнения кода:
| id айди | name имя | age возраст | salary зарплата |
|---|---|---|---|
| 2 | user2 | 25 | 500 |
| 3 | user3 | 23 | 500 |
| 4 | user4 | 30 | 900 |
Пример
Давайте выберем последние 3 записи из таблицы. Для этого отсортируем их с помощью ORDER BY по убыванию id и возьмем 3 записи с помощью LIMIT.
Они и будут искомыми последними записями, так как LIMIT сработает после сортировки и будет браться по уже отсортированной таблице.
Результат выполнения кода:
| id айди | name имя | age возраст | salary зарплата |
|---|---|---|---|
| 6 | user6 | 28 | 900 |
| 5 | user5 | 27 | 500 |
| 4 | user4 | 30 | 900 |
Пример
Давайте выберем последнюю запись в таблице.
Для этого отсортируем их с помощью ORDER BY по убыванию id и возьмем первую запись с помощью LIMIT:
Не стоит пользоваться OFFSET и LIMIT в запросах с разбиением на страницы
Прошли те дни, когда не надо было беспокоиться об оптимизации производительности баз данных. Время не стоит на месте. Каждый новый бизнесмен из сферы высоких технологий хочет создать очередной Facebook, стремясь при этом собирать все данные, до которых может дотянуться. Эти данные нужны бизнесу для более качественного обучения моделей, которые помогают зарабатывать. В таких условиях программистам необходимо создавать такие API, которые позволяют быстро и надёжно работать с огромными объёмами информации.
Если вы уже некоторое время занимаетесь проектированием серверных частей приложений или баз данных, то вы, вероятно, писали код для выполнения запросов с разбиением на страницы. Например — такой:
Но если разбиение на страницы вы выполняли именно так, я с сожалением могу отметить, что вы делали это далеко не самым эффективным образом.
Хотите мне возразить? Можете не тратить время. Slack, Shopify и Mixmax уже применяют приёмы, о которых я хочу сегодня рассказать.
Назовите хотя бы одного разработчика бэкендов, который никогда не пользовался OFFSET и LIMIT для выполнения запросов с разбиением на страницы. В MVP (Minimum Viable Product, минимальный жизнеспособный продукт) и в проектах, где используются небольшие объёмы данных, этот подход вполне применим. Он, так сказать, «просто работает».
Но если нужно с нуля создавать надёжные и эффективные системы, стоит заблаговременно позаботиться об эффективности выполнения запросов к базам данных, используемых в таких системах.
Сегодня мы поговорим о проблемах, сопутствующих широко используемым (жаль, что это так) реализациям механизмов выполнения запросов с разбиением на страницы, и о том, как добиться высокой производительности при выполнении подобных запросов.
Что не так с OFFSET и LIMIT?
Как уже было сказано, OFFSET и LIMIT отлично показывают себя в проектах, в которых не нужно работать с большими объёмами данных.
Проблема возникает в том случае, если база данных разрастается до таких размеров, что перестаёт помещаться в памяти сервера. Но при этом в ходе работы с этой базой данных нужно использовать запросы с разбиением на страницы.
Для того чтобы эта проблема себя проявила, нужно, чтобы возникла ситуация, в которой СУБД прибегает к неэффективной операции полного сканирования таблицы (Full Table Scan) при выполнении каждого запроса с разбиением на страницы (в то же время могут происходить операции по вставке и удалению данных, и устаревшие данные нам при этом не нужны!).
Что такое «полное сканирование таблицы» (или «последовательный просмотр таблицы», Sequential Scan)? Это — операция, в ходе которой СУБД последовательно считывает каждую строку таблицы, то есть — содержащиеся в ней данные, и проверяет их на соответствие заданному условию. Известно, что этот тип сканирования таблиц является самым медленным. Дело в том, что при его выполнении выполняется много операций ввода/вывода, задействующих дисковую подсистему сервера. Ситуацию ухудшают задержки, сопутствующие работе с данными, хранящимися на дисках, и то, что передача данных с диска в память — это ресурсоёмкая операция.
Скажем, это может выглядеть так: «выбрать строки от 50000 до 50020 из 100000». То есть, системе для выполнения запроса нужно будет сначала загрузить 50000 строк. Видите, как много ненужной работы ей придётся выполнить?
Если не верите — взгляните на пример, который я создал, пользуясь возможностями db-fiddle.com.
Пример на db-fiddle.com
А второй, который представляет собой эффективное решение той же задачи, так:
Для того чтобы выполнить эти запросы, достаточно нажать на кнопку Run в верхней части страницы. Сделав это, сравним сведения о времени выполнения запросов. Оказывается, что на выполнение неэффективного запроса уходит, как минимум, в 30 раз больше времени, чем на выполнение второго (от запуска к запуску это время различается, например, система может сообщить о том, что на выполнение первого запроса ушло 37 мс, а на выполнение второго — 1 мс).
А если данных будет больше, то всё будет выглядеть ещё хуже (для того чтобы в этом убедиться — взгляните на мой пример с 10 миллионами строк).
То, что мы только что обсудили, должно дать вам некоторое понимание того, как, на самом деле, обрабатываются запросы к базам данных.
Учитывайте, что чем больше значение OFFSET — тем дольше будет выполняться запрос.
Что стоит использовать вместо комбинации OFFSET и LIMIT?
Вместо комбинации OFFSET и LIMIT стоит использовать конструкцию, построенную по такой схеме:
Это — выполнение запроса с разбиением на страницы, основанное на курсоре (Cursor based pagination).
Почему? Дело в том, что в явном виде указывая идентификатор последней прочитанной строки, вы сообщаете своей СУБД о том, где ей нужно начинать поиск нужных данных. Причём, поиск, благодаря использованию ключа, будет осуществляться эффективно, системе не придётся отвлекаться на строки, находящиеся за пределами указанного диапазона.
Давайте взглянем на следующее сравнение производительности различных запросов. Вот неэффективный запрос.
А вот — оптимизированная версия этого запроса.
Оба запроса возвращают в точности один и тот же объём данных. Но на выполнение первого уходит 12,80 секунд, а на второй — 0,01 секунда. Чувствуете разницу?
Возможные проблемы
Для обеспечения эффективной работы предложенного метода выполнения запросов нужно, чтобы в таблице присутствовал бы столбец (или столбцы), содержащий уникальные, последовательно расположенные индексы, вроде целочисленного идентификатора. В некоторых специфических случаях это может определять успех применения подобных запросов ради повышения скорости работы с базой данных.
Естественно, конструируя запросы, нужно учитывать особенности архитектуры таблиц, и выбирать те механизмы, которые наилучшим образом покажут себя на имеющихся таблицах. Например, если нужно работать в запросах с большими объёмами связанных данных, вам может показаться интересной эта статья.
Если вам интересна эта тема — вот, вот и вот — несколько полезных материалов.
Итоги
Главный вывод, который мы можем сделать, заключаются в том, что всегда, о каких бы размерах баз данных ни шла речь, нужно анализировать скорость выполнения запросов. В наше время крайне важна масштабируемость решений, и если с самого начала работы над некоей системой спроектировать всё правильно, это, в будущем, способно избавить разработчика от множества проблем.
Как вы анализируете и оптимизируете запросы к базам данных?