mysqli_result 是否需要活动连接来查找结果? [英] Do mysqli_result need an alive connection to seek results?

问题描述

我们注意到，对我们的 API 端点的调用通常有多个重复的 MySQL 查询，用于 SELECT 语句(有时是数百个).所以我们决定创建一个哈希图来存储mysqli_result 并检查给定的查询是否已经完成并返回保存在我们地图中的结果.

We noticed that a call to our API endpoint usually have multiple duplicated MySQL queries for SELECT statements (sometimes hundreds). So we decided to create a hashmap to store the mysqli_result and check if a given query has already been done and return the result saved in our map.

我的问题是:mysqli_result 是否维护对我们连接的引用?这真的会帮助我们避免已经不必要地请求的查询使我们的数据库过载吗?如果mysqli_result太大，这会不会溢出我们fpm进程的内存?

My question is: do mysqli_result maintain a reference to our connection? Would this actually help us to avoid overloading our database with queries that have already been requested unnecessarily? If the mysqli_result is too big, could this overflow the memory for our fpm process?

我想知道的基本上是哪种方法更好:

What I want to know basically is what approach is better:

• 对于查询A，每次需要时重用mysqli_result对象；
• 每次需要时重做查询 A.

推荐答案

mysqli_result() 既不需要连接到 MySQL 也不需要 mysqli 对象，一旦结果从MySQL.

mysqli_result() requires neither a connection to MySQL nor a mysqli object once the results are fetched from MySQL.

警告: 以上仅适用于缓冲查询.如果您使用的是无缓冲结果集，则 mysqli_result 需要一个打开的 mysqli 连接来获取数据，否则您将收到一条错误消息:

Caveat: The above only applies to the buffered queries. If you are using unbuffered resultsets then mysqli_result requires an open mysqli connection to fetch the data otherwise you will receive an error saying:

PHP 警告:mysqli_result::fetch_all():读取...中的行时出错

PHP Warning: mysqli_result::fetch_all(): Error while reading a row in ...

但是，只有在使用无缓冲查询关闭连接时才会出现此错误消息.例如

However, this error message can only happen when you close the connection when using unbuffered queries. e.g.

$res = $mysqli->query('SELECT id FROM Users LIMIT 1', MYSQLI_USE_RESULT);
$mysqli->close();
$res->fetch_all();

即使 mysqli_result 在创建它的实例时需要一个有效的连接，它只需要连接来获取数据.mysqli_result::__construct() 中的第二个参数用于决定结果集是在 PHP 中缓冲还是存储在 MySQL 服务器上并逐行提取.当你创建一个mysqli_result的实例时，你需要传递一个mysqli的实例作为第一个参数.

Even though mysqli_result requires a valid connection when creating an instance of it, it only needs the connection to fetch the data. The second parameter in the mysqli_result::__construct() is used to decide whether the resultset should be buffered in PHP or stored on MySQL server and fetched row by row. When you create an instance of mysqli_result you need to pass an instance of mysqli as the first parameter.

// Execute query on MySQL server
$res = $mysqli->real_query('SELECT id FROM Users LIMIT 1');
// Create the result object. 
// The second argument can be MYSQLI_STORE_RESULT for buffered result or MYSQLI_USE_RESULT for unbuffered.
$res = new mysqli_result($mysqli, MYSQLI_STORE_RESULT);

// If MYSQLI_STORE_RESULT was used then you can close mysqli here.
unset($mysqli); // or $mysqli->close(); or both
$data = $res->fetch_all();

// If MYSQLI_USE_RESULT was used then you can't close mysqli yet.
// unset($mysqli);
$data = $res->fetch_all();

SQL 查询的缓冲是一项相当复杂的任务，最好避免重复调用而不是实现缓存.尝试重构您的代码，使其在脚本执行期间不会多次调用相同的 SELECT 查询.

Buffering of SQL queries is a rather complex task and it would be better to avoid duplicate calls rather than implementing caching. Try to refactor your code so that it does not call the same SELECT query multiple times during the execution of your script.

也有一个预制的解决方案，虽然不是很流行.Mysqlnd 查询结果缓存插件

There is also a pre-made solution for this, although not very popular. Mysqlnd query result cache plugin

永远记住:

过早优化是万恶之源

这篇关于mysqli_result 是否需要活动连接来查找结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！