limit offset mysql php
Не стоит пользоваться 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 секунда. Чувствуете разницу?
Возможные проблемы
Для обеспечения эффективной работы предложенного метода выполнения запросов нужно, чтобы в таблице присутствовал бы столбец (или столбцы), содержащий уникальные, последовательно расположенные индексы, вроде целочисленного идентификатора. В некоторых специфических случаях это может определять успех применения подобных запросов ради повышения скорости работы с базой данных.
Естественно, конструируя запросы, нужно учитывать особенности архитектуры таблиц, и выбирать те механизмы, которые наилучшим образом покажут себя на имеющихся таблицах. Например, если нужно работать в запросах с большими объёмами связанных данных, вам может показаться интересной эта статья.
Если вам интересна эта тема — вот, вот и вот — несколько полезных материалов.
Итоги
Главный вывод, который мы можем сделать, заключаются в том, что всегда, о каких бы размерах баз данных ни шла речь, нужно анализировать скорость выполнения запросов. В наше время крайне важна масштабируемость решений, и если с самого начала работы над некоей системой спроектировать всё правильно, это, в будущем, способно избавить разработчика от множества проблем.
Как вы анализируете и оптимизируете запросы к базам данных?
Pagination using MySQL LIMIT, OFFSET
I have some code that LIMITs data to display only 4 items per page. The column I’m using has about 20-30 items, so I need to make those spread out across the pages.
On the first page, I have:
You’ll notice towards the bottom of the page my anchor tag within lists the second page, «itempage2.php». In item page 2, I have the same code, except my select statement lists the offset of 4.
This works, this way when there is a pre-determined number of items within my database. But it’s not that good. I need to create a new page only if there are more items, not hard-coded into it like it is now.
How can I create multiple pages without having to hard-code each new page, and offset?
4 Answers 4
First off, don’t have a separate server script for each page, that is just madness. Most applications implement pagination via use of a pagination parameter in the URL. Something like:
This makes your SQL formulation really easy:
So that is the basic problem of pagination. Now, you have the added requirement that you want to understand the total number of pages (so that you can determine if «NEXT PAGE» should be shown or if you wanted to allow direct access to page X via a link).
In order to do this, you must understand the number of rows in the table.
You can simply do this with a DB call before trying to return your actual limited record set (I say BEFORE since you obviously want to validate that the requested page exists).
Why does MYSQL higher LIMIT offset slow the query down?
Scenario in short: A table with more than 16 million records [2GB in size]. The higher LIMIT offset with SELECT, the slower the query becomes, when using ORDER BY *primary_key*
takes far less than
That only orders 30 records and same eitherway. So it’s not the overhead from ORDER BY.
Now when fetching the latest 30 rows it takes around 180 seconds. How can I optimize that simple query?
6 Answers 6
I had the exact same problem myself. Given the fact that you want to collect a large amount of this data and not a specific set of 30 you’ll be probably running a loop and incrementing the offset by 30.
So what you can do instead is:
So you can always have a ZERO offset. You will be amazed by the performance improvement.
It’s normal that higher offsets slow the query down, since the query needs to count off the first OFFSET + LIMIT records (and take only LIMIT of them). The higher is this value, the longer the query runs.
The query cannot go right to OFFSET because, first, the records can be of different length, and, second, there can be gaps from deleted records. It needs to check and count each record on its way.
Assuming that id is the primary key of a MyISAM table, or a unique non-primary key field on an InnoDB table, you can speed it up by using this trick:
MySQL cannot go directly to the 10000th record (or the 80000th byte as your suggesting) because it cannot assume that it’s packed/ordered like that (or that it has continuous values in 1 to 10000). Although it might be that way in actuality, MySQL cannot assume that there are no holes/gaps/deleted ids.
So, as bobs noted, MySQL will have to fetch 10000 rows (or traverse through 10000th entries of the index on id ) before finding the 30 to return.
EDIT : To illustrate my point
Note that although
would be fast(er), and would return the same results provided that there are no missing id s (i.e. gaps).
I found an interesting example to optimize SELECT queries ORDER BY id LIMIT X,Y. I have 35million of rows so it took like 2 minutes to find a range of rows.
Just put the WHERE with the last id you got increase a lot the performance. For me it was from 2minutes to 1 second 🙂
It works too with strings
The time-consuming part of the two queries is retrieving the rows from the table. Logically speaking, in the LIMIT 0, 30 version, only 30 rows need to be retrieved. In the LIMIT 10000, 30 version, 10000 rows are evaluated and 30 rows are returned. There can be some optimization can be done my the data-reading process, but consider the following:
What if you had a WHERE clause in the queries? The engine must return all rows that qualify, and then sort the data, and finally get the 30 rows.
Also consider the case where rows are not processed in the ORDER BY sequence. All qualifying rows must be sorted to determine which rows to return.
For those who are interested in a comparison and figures 🙂
Experiment 1: The dataset contains about 100 million rows. Each row contains several BIGINT, TINYINT, as well as two TEXT fields (deliberately) containing about 1k chars.
Experiment 2: Similar thing, except that one row only has 3 BIGINTs.
Correct Use of MySQL LIMIT OFFSET Clauses: Limiting and Paginating
Web Development Course:
When you start working with a database management system such as MySQL, you inevitably reach the point when the amount of data grows significantly. It becomes hard to handle and use for both the developer and website or application.
MySQL has inbuilt functionalities for making the management easier. With MySQL LIMIT OFFSET clauses, you can control the number of records that are returned. In other words, when writing a specific query, you can use special clauses to limit MySQL data results to a specified number.
This set limitation helps you paginate the results, making it easier for the server to handle information. Using limit is highly recommended for handling large tables.
Contents
MySQL LIMIT OFFSET: Main Tips
Usage of LIMIT
Imagine you are building an admin interface and you want to display a list of users. However, it seems endless. You’d like to paginate the query results to make it easier to navigate through.
To limit MySQL query results to just twenty on the first page, you can write a query using the SELECT statement:
$sql = «SELECT * FROM users LIMIT 20»;
OFFSET Clause Explained
Say you’d like the second page to display the following twenty results. To do this, we have to use the the MySQL OFFSET clause. It will make the selection start from a specified entry. In our case, we will skip straight to the entry 21. Let’s look at the statement below:
$sql = «SELECT * FROM users LIMIT 20 OFFSET 20»;
There is also a way to make the statement shorter and achieve the same result. Note that in this case, we write the MySQL SELECT value first and the LIMIT value second:
MySql limit and offset giving wrong result
I’m getting only 99 records for limit 100 after 9900 offset. Even though i have 2,00,000 records in db with left join in both the tables with distinct. What’s wrong with my query or loop
Even i tried the query in phpmyadmin there also it was giving same result 99 records.
Query
Laravel Query:
Loop
1 Answer 1
This may not be right answer to address why it was giving the 99 records. But i when i play with that query i found these solutions
Issue:
MySQL MyISAM tables make no guarantees on result with out order by on limit, offsets
Solutions:
Using order by in query Giving 100 results but not tested with entire loop
select distinct(table1.id), table2.name, table2.uuid from table1 left join table2 on table1.id = table2.id order by table1.id limit 9900, 100
Strangely if i use table2.* instead of some fields in same query with out order by gives 100 records
select distinct(table1.id), table2.* from table1 left join table2 on table1.id = table2.id order by table1.id limit 9900, 100
So, Finally it’s only my assumption for the problem is there might chance of null values in left join tables give wrong result.