带有限制和大偏移量的MySQL查询将永远占用 [英] MySQL query with limit and large offset taking forever
问题描述
我有一个使用下面的PHP脚本构建的目录，它使用分页来获得每页1002个结果。问题在于，页面越远，载入的时间就越长。例如，第1页的加载速度明显快于第10,000页。
我猜测我在查询时做了一些错误，而不是仅仅选择它应该是的1002结果有限的，它也是通过之前所有的循环。如果有人可以发布需要修复的代码，那就太好了！
感谢您的时间和帮助！
<？php include（websites / header.html）; ？>
< center>
<？php
/ *
在此放置代码以连接到您的数据库。
* /
include（'websites / database.php'）; //包含您的代码以连接到数据库。
$ tbl_name =list; //你的表名
//每边应显示多少个相邻页面？
$ adjacents = 5;
/ *
首先获取数据表中的总行数。
如果您的查询中有WHERE子句，请确保在此处将其镜像。
* /
$ query =SELECT COUNT（*）as num FROM $ tbl_name;
$ total_pages = mysql_fetch_array（mysql_query（$ query））;
$ total_pages = $ total_pages [num];
/ *设置查询的变量。 * /
$ targetpage =websites.php; //你的文件名（这个文件的名字）
$ limit = 1002; //每页显示多少项目
$ page = $ _GET ['page'];
if（$ page）
$ start =（$ page - 1）* $ limit; //在此页面上显示的第一项
else
$ start = 0; //如果没有给定页面var，则设置开始为0
/ *获取数据。 * /
$ sql =SELECT website from $ tbl_name LIMIT $ start，$ limit;
$ result = mysql_query（$ sql）;
/ *设置页面变量以供显示。 * /
if（$ page == 0）$ page = 1; //如果没有页面var，默认为1.
$ prev = $ page - 1; //前一页是页面 - 1
$ next = $ page + 1; //下一页是页面+ 1
$ lastpage = ceil（$ total_pages / $ limit）; // lastpage =每页总页数/项目，向上取整。
$ lpm1 = $ lastpage - 1; //最后一页减1
/ *
现在我们应用我们的规则并绘制分页对象。
我们实际上将代码保存到一个变量中，以防我们想多次绘制它。
* /
$ pagination =;
if（$ lastpage> 1）
{
$ pagination。=< div class = \pagination2 \>;
//上一个按钮
if（$ page> 1）
$ pagination。=< a href = \$ targetpage？page = $ prev \>& amp ; lt; previous< / a>;
else
$ pagination。=< span class = \disabled \>& lt; previous< / span>;
$ b $ //页面
if（$ lastpage< 7 +（$ adjacents * 2））//没有足够的页面打扰分解
{
for （$ counter = 1; $ counter <= $ lastpage; $ counter ++）
{
if（$ counter == $ page）
$ pagination。=< span class = \\ current\ > $计数器< /跨度>中;
else
$ pagination。=< a href = \$ targetpage？page = $ counter \> $ counter< / a>;
}
}
elseif（$ lastpage> 5 +（$ adjacents * 2））//足够的页面隐藏一些
{
//接近开始;如果（$ page< 1 +（$ adjacents * 2））
{
for（$ counter = 1; $ counter< 4 +（$ adjacents * 2） ）; $ counter $）
{
if（$ counter == $ page）
$ pagination。=< span class = \current \> $ counter< /跨度>中;
else
$ pagination。=< a href = \$ targetpage？page = $ counter \> $ counter< / a>;
}
$ pagination。=...;
$ pagination。=< a href = \$ targetpage？page = $ lpm1 \> $ lpm1< / a>;
$ pagination。=< a href = \$ targetpage？page = $ lastpage \> $ lastpage< / a>;
}
//中间;隐藏一些正面和一些背面
elseif（$ lastpage - （$ adjacents * 2）> $ page&& $ page>（$ adjacents * 2））
{
$ pagination。=< a href = \$ targetpage？page = 1 \> 1< / a>;
$ pagination。=< a href = \$ targetpage？page = 2 \> 2< / a>;
$ pagination。=...;
for（$ counter = $ page - $ adjacents; $ counter <= $ page + $ adjacents; $ counter ++）
{
if（$ counter == $ page）
$ pagination。=< span class = \current \> $ counter< / span>;
else
$ pagination。=< a href = \$ targetpage？page = $ counter \> $ counter< / a>;
}
$ pagination。=...;
$ pagination。=< a href = \$ targetpage？page = $ lpm1 \> $ lpm1< / a>;
$ pagination。=< a href = \$ targetpage？page = $ lastpage \> $ lastpage< / a>;
}
//接近结束;只隐藏早期页面
else
{
$ pagination。=< a href = \$ targetpage？page = 1 \> 1< / a>;
$ pagination。=< a href = \$ targetpage？page = 2 \> 2< / a>;
$ pagination。=...;
for（$ counter = $ lastpage - （2 +（$ adjacents * 2））; $ counter <= $ lastpage; $ counter ++）
{
if（$ counter == $ page）
$ pagination。=< span class = \current \> $ counter< / span>;
else
$ pagination。=< a href = \$ targetpage？page = $ counter \> $ counter< / a>;
$ b //下一个按钮
if（$ page< $ counter - 1）
$ pagination。= < a href = \$ targetpage？page = $ next \> next& gt;< / a>;
else
$ pagination。=< span class = \disabled \> next& gt;< / span>;
$ pagination。=< / div> \\\
;
}
？>
<？php
$ i = 0;
echo'< table style =table-layout：fixed; width：1050px;>< tr>';
while（$ row = mysql_fetch_array（$ result））
{
$ i ++;
if（$ i <= 3）
{
echo'< td style =word-wrap：break-word;>
< div>< a href =http://www.mywebsite.com/check.php?site='.strtolower($row[website]）。'>'。strtolower（$行[网站]）'< / A>< / DIV>
< / td>';
}
else
{
echo'< / tr>< tr>';
echo'< td style =word-wrap：break-word;>< div>< a href =http://www.mywebsite.com/check.php?site=' .strtolower（$行[网站]） '>'。用strtolower（$行[网站]）。。 '< / A>< / DIV>< / TD>';
$ i = 0;
$ i ++;
}
}
echo'< / tr>< / table>';
？>
<？= $ pagination？>>
< / center>
<？php include（websites / footer.html）; ？>
我发现一些 文档对MySQL的这种效果，我试图找到一篇我刚才阅读的一篇非常好的文章，为SQLite解释了这一点）。原因是它通常是这样实现的：
- 执行所有正常的查询计划，就好像
LIMIT子句不存在 - 浏览结果，直到找到您想要的索引
- 开始返回结果
这意味着如果你做了 LIMIT 10000,10 ，它将被解释为：
- 获取前10,000个结果并忽略它们
- 为您提供接下来的10个结果
li>
有一个简单的优化，您至少可以使用索引来计算前10,000个结果，因为您不关心它们的值，但即使在这种情况下，数据库在向您提供10个结果之前，仍需要遍历10,000个索引值。有可能会有进一步的优化，可以改善这一点，但在一般情况下你不希望使用 LIMIT 与大值偏移量 。
处理分页的最有效方法是我记住最后一个索引，所以如果第一页以结尾， id = 5 ，然后让下一个链接有 WHERE id> 5 （当然是 LIMIT x ）。
编辑：找到<一个href =https://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor =nofollow> SQLite的文章。我强烈建议你阅读这篇文章，因为它解释了The Right Way™在SQL中做事。由于SQLite人员很聪明，而其他数据库也有这个问题，所以我假设MySQL以类似的方式实现了这一点。
另一个频繁出现的错误是程序员试图用LIMIT和OFFSET实现滚动窗口。这里的想法是，你首先要记住显示屏顶部条目的索引并运行一个这样的查询：
SELECT title FROM tracks
WHERE singer ='Madonna'
ORDER BY title
LIMIT 5 OFFSET：index
索引初始化为0.向下滚动只需将索引增加5并重新运行查询。要向上滚动，将索引减5并重新运行。
以上实际上可行。 问题是，索引变大时它会变慢。 OFFSET在SQLite中的工作方式是它使sqlite3_step（）函数忽略它所看到的第一个：索引断点。因此，例如，如果：索引为1000，那么您确实在读取1005个条目并忽略除最后5个之外的所有条目。最终效果是滚动开始变慢，因为您在列表中越来越低。
I have a directory that I've built with the PHP script below and it uses pagination to get 1002 results per page. The problem is that the farther you get in the pages, the longer they take to load. For example, page 1 loads significantly faster than page 10,000.
I'm guessing I did something wrong with the query and instead of just selecting the 1002 results that it should be limited, it's also cycling through all the ones from before it as well. If someone could post the code that needs to be fixed, that would be great!
Thanks for your time and help!
<?php include("websites/header.html"); ?>
<center>
<?php
/*
Place code to connect to your DB here.
*/
include('websites/database.php'); // include your code to connect to DB.
$tbl_name="list"; //your table name
// How many adjacent pages should be shown on each side?
$adjacents = 5;
/*
First get total number of rows in data table.
If you have a WHERE clause in your query, make sure you mirror it here.
*/
$query = "SELECT COUNT(*) as num FROM $tbl_name";
$total_pages = mysql_fetch_array(mysql_query($query));
$total_pages = $total_pages[num];
/* Setup vars for query. */
$targetpage = "websites.php"; //your file name (the name of this file)
$limit = 1002; //how many items to show per page
$page = $_GET['page'];
if($page)
$start = ($page - 1) * $limit; //first item to display on this page
else
$start = 0; //if no page var is given, set start to 0
/* Get data. */
$sql = "SELECT website FROM $tbl_name LIMIT $start, $limit";
$result = mysql_query($sql);
/* Setup page vars for display. */
if ($page == 0) $page = 1; //if no page var is given, default to 1.
$prev = $page - 1; //previous page is page - 1
$next = $page + 1; //next page is page + 1
$lastpage = ceil($total_pages/$limit); //lastpage is = total pages / items per page, rounded up.
$lpm1 = $lastpage - 1; //last page minus 1
/*
Now we apply our rules and draw the pagination object.
We're actually saving the code to a variable in case we want to draw it more than once.
*/
$pagination = "";
if($lastpage > 1)
{
$pagination .= "<div class=\"pagination2\">";
//previous button
if ($page > 1)
$pagination.= "<a href=\"$targetpage?page=$prev\">< previous</a>";
else
$pagination.= "<span class=\"disabled\">< previous</span>";
//pages
if ($lastpage < 7 + ($adjacents * 2)) //not enough pages to bother breaking it up
{
for ($counter = 1; $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
}
elseif($lastpage > 5 + ($adjacents * 2)) //enough pages to hide some
{
//close to beginning; only hide later pages
if($page < 1 + ($adjacents * 2))
{
for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//in middle; hide some front and some back
elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2))
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
$pagination.= "...";
$pagination.= "<a href=\"$targetpage?page=$lpm1\">$lpm1</a>";
$pagination.= "<a href=\"$targetpage?page=$lastpage\">$lastpage</a>";
}
//close to end; only hide early pages
else
{
$pagination.= "<a href=\"$targetpage?page=1\">1</a>";
$pagination.= "<a href=\"$targetpage?page=2\">2</a>";
$pagination.= "...";
for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++)
{
if ($counter == $page)
$pagination.= "<span class=\"current\">$counter</span>";
else
$pagination.= "<a href=\"$targetpage?page=$counter\">$counter</a>";
}
}
}
//next button
if ($page < $counter - 1)
$pagination.= "<a href=\"$targetpage?page=$next\">next ></a>";
else
$pagination.= "<span class=\"disabled\">next ></span>";
$pagination.= "</div>\n";
}
?>
<?php
$i = 0;
echo '<table style="table-layout:fixed; width:1050px;"><tr>';
while($row = mysql_fetch_array($result))
{
$i ++;
if ($i<=3)
{
echo '<td style="word-wrap: break-word;">
<div><a href="http://www.mywebsite.com/check.php?site='.strtolower($row[website]).'">'.strtolower($row[website]).'</a></div>
</td>';
}
else
{
echo '</tr><tr>';
echo '<td style="word-wrap: break-word;"><div><a href="http://www.mywebsite.com/check.php?site='.strtolower($row[website]).'">'.strtolower($row[website]).'</a></div></td>';
$i = 0;
$i++;
}
}
echo '</tr></table>';
?>
<?=$pagination?>
</center>
<?php include("websites/footer.html"); ?>
LIMIT with an offset is extremely slow in most databases (I've found some documentation to this effect for MySQL and I'm trying to find a really good article I read a while ago explaining this for SQLite). The reason is that it's generally implemented something like this:
- Do all normal query planning as if the
LIMITclause wasn't there - Walk through results until we get to the index you want
- Start returning results
What this means if that if you do LIMIT 10000, 10, it will be interpreted as:
- Fetch the first 10,000 results and ignore them
- Give you the next 10 results
There's a trivial optimization where you can at least use the index for the first 10,000 results since you don't care about their values, but even in that case, the database still needs to walk through 10,000 index values before giving you your 10 results. There may be further optimizations that can improve this, but in the general case you don't want to do use LIMIT with an offset for large values.
The most efficient way to handle pagination that I'm aware of is to keep track of the last index, so if page one ends on id = 5, then make your next link have WHERE id > 5 (with a LIMIT x of course).
EDIT: Found the article for SQLite. I highly recommend you read this since it explains The Right Way™ to do things in SQL. Since the SQLite people are really smart and other databases have this same problem, I assume MySQL implements this in a similar way.
Another error that crops up frequently is programmers trying to implement a scrolling window using LIMIT and OFFSET. The idea here is that you first just remember the index of the top entry in the display and run a query like this:
SELECT title FROM tracks WHERE singer='Madonna' ORDER BY title LIMIT 5 OFFSET :indexThe index is initialized to 0. To scroll down just increment the index by 5 and rerun the query. To scroll up, decrement the index by 5 and rerun.
The above will work actually. The problem is that it gets slow when the index gets large. The way OFFSET works in SQLite is that it causes the sqlite3_step() function to ignore the first :index breakpoints that it sees. So, for example, if :index is 1000, you are really reading in 1005 entries and ignoring all but the last 5. The net effect is that scrolling starts to become sluggish as you get lower and lower in the list.
这篇关于带有限制和大偏移量的MySQL查询将永远占用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
