Question

I m having a strange time dealing with selecting from a table with about 30,000 rows.

It seems my script is using an outrageous amount of memory for what is a simple, forward only walk over a query result.

Please note that this example is a somewhat contrived, absolute bare minimum example which bears very little resemblance to the real code and it cannot be replaced with a simple database aggregation. It is intended to illustrate the point that each row does not need to be retained on each iteration.

<?php
$pdo = new PDO( mysql:host=127.0.0.1 ,  foo ,  bar , array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$stmt = $pdo->prepare( SELECT * FROM round );
$stmt->execute();

function do_stuff($row) {}

$c = 0;
while ($row = $stmt->fetch()) {
    // do something with the object that doesn t involve keeping 
    // it around and can t be done in SQL
    do_stuff($row);
    $row = null;
    ++$c;
}

var_dump($c);
var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

产出:

int(39508)
int(43005064)
int(43018120)

我不理解为什么在几乎不需要随时保存任何数据时,会使用40只记忆。我已经做了大量工作,用大约6个因素来减少记忆,将“选举后”改为“选举后回家”,但我甚至认为,这种使用率很低,而且表格只会增加。

Is there a setting I m missing, or is there some limitation in PDO that I should be aware of? I m happy to get rid of PDO in favour of mysqli if it can not support this, so if that s my only option, how would I perform this using mysqli instead?

Answer 1

After creating the connection, you need to set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false:

<?php
$pdo = new PDO( mysql:host=127.0.0.1 ,  foo ,  bar , array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// snip

var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

产出:

int(39508)
int(653920)
int(668136)

无论结果大小,记忆的使用仍然相当静态。

Answer 2

另一种选择是:

$i = $c = 0;
$query =  SELECT home, away FROM round LIMIT 2048 OFFSET %u; ;

while ($c += count($rows = codeThatFetches(sprintf($query, $i++ * 2048))) > 0)
{
    foreach ($rows as $row)
    {
        do_stuff($row);
    }
}

Answer 3

整个成果集(所有30 000分)在你开始研究之前,被缓冲为记忆。

你们应该让数据库做到汇总,只要求数据库提供所需要的两个数字。

SELECT SUM(home) AS home, SUM(away) AS away, COUNT(*) AS c FROM round

Answer 4

现实是,如果你 all一遍,并期望能够把他们全部放到公共卫生和社会福利部,就会一劳永逸地存在。

如果你真的不考虑使用有动力的表述和聚合物,那么你可以考虑限制/放弃你的数据处理。不要一劳永逸地夺走所有各行:

1)  Fetch 5,000 rows
2)  Aggregate/Calculate intermediary results
3)  unset variables to free memory
4)  Back to step 1 (fetch next set of rows)

Just an idea...

Answer 5

I haven t done this before in PHP, but you may consider fetching the rows using a scrollable cursor - see the fetch documentation for an example.

不要把你的询问的所有结果一回到你的购买力平价,而是把结果放在服务器上,而你则利用一个 cur子通过一个时日子来加以修复。

Whilst I have not tested this, it is bound to have other drawbacks such as utilising more server resources and most likely reduced performance due to additional communication with the server.

Altering the fetch style may also have an impact as by default the documentation indicates it will store both an associative array and well as a numerical indexed array which is bound to increase memory usage.

As others have suggested, reducing the number of results in the first place is most likely a better option if possible.

友情链接