在mysql中使用mysqli准备的语句，使用缓冲数据和未缓冲数据，性能提高或降低了多少%。，
1.缓冲数据例如
$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";
if ($stmt = mysqli_prepare($link, $query)) {
/* execute statement */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $row['Name'], $row['CountryCode']);
/* store result */
mysqli_stmt_store_result($stmt);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
echo $row['Name'].'-'. $row['CountryCode'];
}
/* free result */
mysqli_stmt_free_result($stmt);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
2.未缓冲的数据例如
$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 150,5";
if ($stmt = mysqli_prepare($link, $query)) {
/* execute statement */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $row['Name'], $row['CountryCode']);
/* fetch values */
while (mysqli_stmt_fetch($stmt)) {
echo $row['Name'].'-'. $row['CountryCode']);
}
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
第一个例子使用
mysqli_stmt_store_result($stmt);
它将结果数据存储在缓冲区中，以便以后提取
而另一个示例在不存储在缓冲器中的情况下直接向客户端获取数据。
必须为以下每个查询调用mysqli_stmt_store_result（）成功生成结果集（SELECT、SHOW、DESCRIBE、EXPLAIN），如果并且仅当您希望通过客户端，以便随后的mysqli_stmt_fetch（）调用返回缓冲数据。
参考：http://php.net/manual/en/mysqli-stmt.store-result.php
所以我的问题是，如果使用mysqli_stmt_store_result，性能会提高多少？
虽然我从来没有需要存储结果，但我仍然觉得这个问题很有趣，我也想知道同样的事情。我创建了一个包含垃圾数据的数据库，并对其进行了一些测试，以使用未缓冲和缓冲的结果来查看时间差异。我已经包括了我的测试和发现，以防对你（或其他任何人）有帮助。
数据库设置
数据库由一个带有四个不同字段的表组成。这是模式：
ID | int(5) | primary_key, auto_increment
Name | tinytext | not_null
CountryCode | int(3) | not_null
Description | tinytext | not_null
该表有超过10000行，所有行都包含垃圾数据。ID用它的自增量值填充，CountryCode是一个介于11和999之间的随机数，最后两个字段Name和Description包含数值数据的md5散列。
测试
总共进行了六次不同的测试。前5个测试是对未缓冲和缓冲结果之间的处理时间的比较。测试的总体目标是从表的开头和在中间看到不同大小结果集的基准。最终测试只是随机访问缓冲结果与顺序访问缓冲结果的基准。未缓冲的结果不能任意通过mysqli_stmt_data_seek()，因此比较很难进行，并且被认为是公平的。
使用microtime()计算基准时间。Tick在MySQLi语句准备好之前就开始了，在语句关闭后就结束了。
以下是6项测试的明细：
- 测试1:比较从表的开始中选择的100行结果集的未缓冲/缓冲处理时间
- 测试2:比较从表的中间中选择的100行结果集的未缓冲/缓冲处理时间
- 测试3:比较从表的开始中选择的1000行结果集的未缓冲/缓冲处理时间
- 测试4:比较从表的中间中选择的1000行结果集的未缓冲/缓冲处理时间
- 测试5:比较从表的开始中选择并重复三次的5000行结果集的未缓冲/缓冲处理时间
- 测试6:10000行结果集的处理时间基准，随机访问，重复三次
结果
PHP为上述测试生成了以下输出。
Test 1
Took 0.002000093460083 seconds to process unbuffered result of 100 rows from the beginning of the table
Took 0.0019998550415039 seconds to process buffered result of 100 rows from the beginning of the table
Test 2
Took 0.012001037597656 seconds to process unbuffered result of 100 rows from the middle of the table
Took 0.011001110076904 seconds to process buffered result of 100 rows from the middle of the table
Test 3
Took 0.013001918792725 seconds to process unbuffered result of 1000 rows from the beginning of the table
Took 0.012001037597656 seconds to process buffered result of 1000 rows from the beginning of the table
Test 4
Took 0.023001909255981 seconds to process unbuffered result of 1000 rows from the middle of the table
Took 0.020002126693726 seconds to process buffered result of 1000 rows from the middle of the table
Test 5
Took 0.19601988792419 seconds to process unbuffered result of 5000 rows sequentially, three times
Took 0.085008144378662 seconds to process buffered result of 5000 rows sequentially, three times
Test 6
Took 4.2634270191193 seconds to process buffered result of 10000 rows randomly, three times
结论
测试1-4表明，性能增益在大多数情况下可以忽略不计。即使在处理大量行或从表中的各种偏移量获取记录时，缓冲结果的收益也是最小的。偏移位置增加了一些开销（大约前进5000行的百分之一秒）。
也就是说，缓冲仍然有它的位置。在测试五中，具有数千行的结果集被多次迭代，使用缓冲结果有明显的好处。该测试的缓冲和非缓冲版本都有效地处理了15000行。但是，因为缓冲版本不必再次检索结果，所以它能够在不到非缓冲版本一半的时间内完成工作。
正如其他人在这个问题中已经指出的那样，当必须任意/随机访问行时，缓冲非常有用。测试六简单地显示了一组缓冲的10000行可以随机访问多长时间，然后再重复两次。测试六有效地获取了30000行完全无序的数据。
当然，守则
这是我用来创建这个测试的代码。它都是过程性的，所以看起来不是最漂亮的，但如果我发现自己用它创建了一个类，或者修改了代码来清理它，我一定会在这里更新它！
<?php
//tell PHP not to mind how long it is running
set_time_limit(0);
//control output for test results
ob_start();
//array to hold time values from the tests
$times = array();
//Connect to the database
$connection = mysqli_connect("localhost", "root", "", "blah");
/***********************************************************************
* TEST 1: Small result set of 100 rows from the beginning of the table
**********************************************************************/
$times['Test 1'] = array();
//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 1'][] = "Took $time seconds to process unbuffered result of 100 rows from the beginning of the table";
//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 1'][] = "Took $time seconds to process buffered result of 100 rows from the beginning of the table";
echo "'n ** END TEST 1** 'n'n";
/*******************************************************************
* TEST 2: Small result set of 100 rows from the middle of the table
******************************************************************/
$times['Test 2'] = array();
//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 2'][] = "Took $time seconds to process unbuffered result of 100 rows from the middle of the table";
//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 100");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 2'][] = "Took $time seconds to process buffered result of 100 rows from the middle of the table";
echo "'n ** END TEST 2** 'n'n";
/***********************************************************************
* TEST 3: Large result set of 1000 rows from the beginning of the table
**********************************************************************/
$times['Test 3'] = array();
//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 3'][] = "Took $time seconds to process unbuffered result of 1000 rows from the beginning of the table";
//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 3'][] = "Took $time seconds to process buffered result of 1000 rows from the beginning of the table";
echo "'n ** END TEST 3** 'n'n";
/********************************************************************
* TEST 4: Large result set of 1000 rows from the middle of the table
*******************************************************************/
$times['Test 4'] = array();
//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 4'][] = "Took $time seconds to process unbuffered result of 1000 rows from the middle of the table";
//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000, 1000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 4'][] = "Took $time seconds to process buffered result of 1000 rows from the middle of the table";
echo "'n ** END TEST 4** 'n'n";
/******************************************************************************
* TEST 5: Work with larger result set, 5000 rows, multiple times, sequentially
*****************************************************************************/
$times['Test 5'] = array();
//UNBUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000");
for ($i = 0; $i < 3; $i++)
{
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
}
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 5'][] = "Took $time seconds to process unbuffered result of 5000 rows sequentially, three times";
//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 5000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);
for ($i = 0; $i < 3; $i++)
{
mysqli_stmt_data_seek($stmt, 0);
while (mysqli_stmt_fetch($stmt))
{
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
}
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 5'][] = "Took $time seconds to process buffered result of 5000 rows sequentially, three times";
echo "'n ** END TEST 5** 'n'n";
/***************************************************************************
* TEST 6: Work with larger result set, 10000 rows, multiple times, randomly
**************************************************************************/
$times['Test 6'] = array();
//UNBUFFERED VERSION
//Can't test unbuffered result sets randomly as mysqli_stmt_data_seek
//only works on buffered results.
//BUFFERED VERSION
$benchmarkStart = microtime(true);
$stmt = mysqli_prepare($connection, "SELECT * FROM City LIMIT 10000");
mysqli_stmt_execute($stmt);
mysqli_stmt_bind_result($stmt, $id, $name, $code, $desc);
mysqli_stmt_store_result($stmt);
for ($i = 0; $i < 3; $i++)
{
$rows = range(0, (mysqli_stmt_num_rows($stmt) - 1));
shuffle($rows);
for ($j = 0; $j < 10000; $j++)
{
mysqli_stmt_fetch($stmt);
printf("%d, %s, %d, %s 'n", $id, $name, $code, $desc);
$row = $rows[0];
mysqli_stmt_data_seek($stmt, $row);
array_shift($rows);
}
}
mysqli_stmt_free_result($stmt);
mysqli_stmt_close($stmt);
$benchmarkEnd = microtime(true);
$time = $benchmarkEnd - $benchmarkStart;
$times['Test 6'][] = "Took $time seconds to process buffered result of 10000 rows randomly, three times";
echo "'n ** END TEST 6** 'n'n";
/*******************
* Print the results
******************/
$output = ob_get_clean();
foreach ($times as $tests => $results)
{
echo $tests . "'n";
foreach ($results as $result)
{
echo $result . "'n";
}
echo "'n'n";
}
//Dumps all of those rows that have been getting printed out to the browser.
//This kicked out a little north of 64,000 lines in my browser.
echo $output;
?>
我认为90%的时间你不会看到任何差异，因为：
1-如果你有少量的数据，一切都会很快，不会有太大的不同。
2-如果你有大量的数据，这些数据将被放在客户端的缓冲区中，只有当你多次使用相同的结果集时，这才会节省你的时间。
不幸的是，我无法给你一个明确的答案，因为只有你知道查询将产生的数据量，以及这些数据是否需要多次。