Detailed explanation of mysql operation buffer usage in php, mysqlbuffer_PHP tutorial

Detailed explanation of the usage of mysql operation buffer in php, mysqlbuffer

This article describes the usage of mysql operation buffer in php. Share it with everyone for your reference. The specific analysis is as follows:

There are three ways to connect php to mysql, mysql, mysqli, and pdo. No matter which method is used to connect, there is a difference between using a buffer and not using a buffer.

What does it mean to use buffer and not to use buffer?

The client performs query operations with the mysql server. If the amount of data obtained during the query operation is relatively large, where should the query results be placed?

There are two places to put it: the client's buffer and the server's buffer.

The buffer we are talking about here refers to the client's buffer. If the query results have been obtained from the server and placed in the client's buffer, we call it using the buffer. If it is still stored in the buffer on the server side, we say that the buffer (unbuffer) is not used.

What is the difference between using buffer and not using buffer?

Mainly in terms of memory, using buffer will increase the memory pressure of the client. When the returned data result is particularly large, it may occupy a relatively large process that calls the client (actually a PHP process). Not using buffer will naturally put more pressure on the server (here we are talking about the server that provides mysql service).

For details, please refer to: Memory usage analysis of PHP querying MySQL large amounts of data

How to set whether to use buffer in the three modes in php?

The default query of mysql uses buffer. If you do not use buffer, you need to use mysql_unbuffer_query

The default query of mysqli does not use buffer. To use buffer, you need to set MYSQLI_STORE_RESULT

The default query of pdo does not use buffer. To use buffer, you need to set MYSQL_ATTR_USE_BUFFERED_QUERY

The relevant codes are roughly as follows:

<&#63;php
$dbConfig = array(
  'host' => '10.128.11.101',
  'port' => '3306',
  'user' => 'test',
  'pass' => 'test',
  'db' => 'test',
);
$sql = 'select * from so_topic_app';
//---------mysql----------//
$db = mysql_connect($dbConfig['host'], $dbConfig['user'], $dbConfig['pass']);
mysql_select_db($dbConfig['db'], $db);
mysql_set_charset('utf8', $db);
// mysql使用buffer
$res = mysql_query($sql, $db);
$data = array();
while($row = mysql_fetch_row($res)) {
  $data[] = $row;
}
// mysql不使用buffer
$res = mysql_unbuffered_query($sql, $db);
$data = array();
while($row = mysql_fetch_row($res)) {
  $data[] = $row;
}
mysql_close($db);
//---------mysqli----------//
$db = mysqli_connect($dbConfig['host'], $dbConfig['user'], $dbConfig['pass'], $dbConfig['db']);
// mysqli不使用buffer
$result = mysqli_query($db, $sql);
$data = array();
while($row = $result->fetch_array()) {
  $data[] = $row;
}
// mysqli使用buffer
$result = mysqli_query($db, $sql, MYSQLI_STORE_RESULT);
$data = array();
while($row = $result->fetch_array()) {
  $data[] = $row;
}
mysqli_free_result($result);
mysqli_close($db);
//---------pdo----------//
$dsn = "mysql:dbname={$dbConfig['db']};host={$dbConfig['host']}";
$pdo = new PDO($dsn, $dbConfig['user'], $dbConfig['pass']);
// pdo不使用buffer
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = array();
$data = $stmt->fetchAll();
// pdo使用buffer
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$stmt = $pdo->prepare($sql);
$stmt->execute();
$data = array();
$data = $stmt->fetchAll();

Follow up

Of course, if the amount of data is very large, most people will still consider using batches to extract and process the data. So there are very few scenarios that actually require us to pay attention to and use mysql whether to use buffer or not.

I hope this article will be helpful to everyone’s PHP programming design.

http://www.bkjia.com/PHPjc/970854.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/970854.htmlTechArticleDetailed explanation of the usage of mysql operation buffer in php, mysqlbuffer This article describes the usage of mysql operation buffer in php. Share it with everyone for your reference. The specific analysis is as follows: There are three connections between php and mysql...