在大页面或最后一页上的PHP分页缓慢 [英] php pagination slow on large or last pages

问题描述

使用分页，我获得了6000多个结果，总共超过235页.当我单击第一页时，它加载速度非常快，大约300毫秒，直到第40页左右为止.之后，它实际上会下山，大约需要30到40+秒的页面加载时间.我正在使用索引数据库.我试图给我们mysql catch查询，但是不喜欢它.有人可以帮我吗?

I have over 6000 results with come to about more than 235 pages using pagination. When I click first page, it loads really fast ~ 300ms up until around 40th page. after that it really goes down hill with about 30 ~ 40+ seconds of page load time. I am using indexed database. I tried to us mysql catch query, but did not like it. Can someone help me out.

php:

$sql = mysql_query("SELECT * FROM data WHERE (car = '$cars') AND (color = '$color' AND price BETWEEN '".$min."' AND '".$max."'  
ORDER BY price LIMIT {$startpoint} , {$limit}");

索引:

data    0   PRIMARY     1   id  A   106199  NULL    NULL        BTREE       
data    1   car_index   1   car     A   1799    NULL    NULL        BTREE       
data    1   car_index   2   color   A   2870    NULL    NULL        BTREE       
data    1   car_index   3   price   A   6247    NULL    NULL        BTREE       
data    1   car_index   4   location    A   106199  NULL    NULL        BTREE       

推荐答案

这是MySQL(和其他数据库系统)的常见问题.首先使用LIMIT + OFFSET(这就是您在LIMIT x，y中隐式使用的功能)效果很好，但是随着获取的行数的增加，速度会成倍地降低. 添加索引绝对是一个不错的第一步，因为您应始终基于索引查询数据，以避免全表扫描.

This is a common issue with MySQL (and other database systems). Using LIMIT + OFFSET (which is what you are using implicitely with LIMIT x, y) works great at first but slows down exponentially as the number of fetched rows grows. Adding an index is definitely a good first step, as you should always query data based on an index, to avoid full table scans.

仅具有价格指数是不够的，因为您具有其他WHERE属性.基本上，这就是MySQL所做的: 假设$limit = 25和$startPoint = 0，MySQL将从头开始读取表，并在找到25个匹配的行并返回之后停止.假设它在第一次迭代中读取了500行.下一次迭代且由于它在car + color + price上没有索引，因此它不知道如何直接跳到第25个匹配行(表中的第500行)，因此它将再次从头开始读取，跳过第一个25个匹配的行，并返回25个下一个匹配的行.假设此迭代还需要读取500个额外的行.

Only having an index on price won't be enough as you have other WHERE attributes. Basically, this is what MySQL is doing: Assuming that $limit = 25 and $startPoint = 0, MySQL will start reading the table from the beginning and stop after it finds 25 matching rows and will return them. Let's assume that it read 500 rows for this first iteration. Next iteration and because it does not have an index on car + color + price, it does not know how to jump directly to the 25th matching row (the 500th row in the table), so it will start reading from the beginning again, skip the first 25 matching rows and return the 25 next matching rows. Let's assume that this iteration also required 500 extra rows to be read.

现在，您可以了解出了什么问题.对于每次迭代，MySQL必须从头开始读取所有行，从而成倍增加返回行所需的时间.

Now you see what's going wrong. For every iteration, MySQL will have to read the all the rows from the beginning, exponentially increasing the time it takes to return row.

在我的示例中，要获取100(25 * 4个迭代)行，MySQL必须读取500 + 1000 + 1500 + 2000 = 5000行，而您可能希望它仅读取500 * 4 = 2,000行.要获取1000(25 * 40次迭代)行，MySQL必须读取500 + 1000 + 1500 + ... 20000 = 410,000行！这远远超出了您预期的500 * 40 = 20,000行.

In my example, to fetch 100 (25 * 4 iterations) rows, MySQL will have to read 500 + 1000 + 1500 + 2000 = 5000 rows while you could expect it to only read 500 * 4 = 2,000 rows. To fetch 1000 (25 * 40 iterations) rows, MySQL will have to read 500 + 1000 + 1500 + ... 20000 = 410,000 rows!! That's way more than the 500 * 40 = 20,000 rows you could expect.

要优化查询，请首先仅选择所需的数据(否SELECT *).然后，诀窍是记住上一次获取的ID.

To optimize your query, first only select the data you need (no SELECT *). Then the trick is to remember the last fetched id.

$lastFetchedId = 0;
do {
    $sql = mysql_query("SELECT * FROM data WHERE id > $lastFetchedId AND (car = '$cars' AND color = '$color' AND price BETWEEN '".$min."' AND '".$max."')
ORDER BY price LIMIT {$limit}");

    $hasFoundRows = false;
    while ($row = mysql_fetch_assoc($sql)) {
        $hasFoundRows = true;
        $lastFetchedId = $row['id'];
        // do something with the row
    }
} while ($hasFoundRows === false);

只有当您在WHERE子句中使用的所有列上都有索引时，MySQL才能很好地执行排序.这样考虑:如果数据没有排序，MySQL将如何知道哪些行将匹配以及匹配的行在哪里.为了能够对结果进行排序并仅返回一个子集，MySQL需要构建一个实际匹配的所有行的排序列表.这意味着要遍历整个表，首先获取所有匹配的行，然后对它们进行排序，最后只返回其中一些.

Having MySQL taking care of the ordering works well only if you have an index on all the columns you are using in the WHERE clause. Think about it this way: if the data is not sorted, how would MySQL know which rows will match and where the matching rows are. To be able to sort the results and only return a subset, MySQL needs to build a sorted list of ALL the rows that actually match. This means going through the entire table to first get all the matching rows, then sort them and finally return only a few of them.

希望可以帮助您更好地了解自己在这里可以做得更好的事情:)

Hope that helps you understand better what you can do better here :)

这篇关于在大页面或最后一页上的PHP分页缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！