PHP / MySQL数据库查询如何工作？ [英] How do PHP/MySQL database queries work exactly?

问题描述

我已经使用MySQL了很多，但我总是想知道它是如何工作 - 当我得到一个积极的结果，数据存储在哪里？例如，我写这样：

I have used MySQL a lot, but I always wondered exactly how does it work - when I get a positive result, where is the data stored exactly? For example, I write like this:

$sql = "SELECT * FROM TABLE"; 
$result = mysql_query($sql);
while ($row = mysql_fetch_object($result)) {
    echo $row->column_name;
}

返回结果时，我假设它保存所有数据结果，它返回一个片段，只返回它要求的地方，如$ row-> column_name？

When a result is returned, I am assuming it's holding all the data results or does it return in a fragment and only returns where it is asked for, like $row->column_name?

或者它真的返回每一行数据，即使你只想在$ result中的一列吗？

Or does it really return every single row of data even if you only wanted one column in $result?

此外，如果使用LIMIT分页，即使更新数据库，它是否保存原始（旧）结果？

Also, if I paginate using LIMIT, does it hold THAT original (old) result even if the database is updated?

推荐答案

细节依赖于实现，但通常说，结果被缓冲。对数据库执行查询将返回一些结果集。如果它足够小，所有的结果可以返回与初始调用或一些可能是，并且更多的结果返回结果对象。

The details are implementation dependent but generally speaking, results are buffered. Executing a query against a database will return some result set. If it's sufficiently small all the results may be returned with the initial call or some might be and more results are returned as you iterate over the result object.

这样：

1. 打开数据库连接;


2. （1）;


3. 认证和连接步骤（至少）一次往返服务器（忽略持久连接）;
/ li>

4. 您在客户端上执行查询;


5. 该查询已发送到服务器;


6. 必须确定如何执行查询;


7. 如果服务器先前已执行查询，执行计划可能仍在查询缓存中。如果不是必须创建新计划;


8. 服务器执行给定的查询并向客户端返回结果;


9. 该结果将包含某些缓冲区的行是实现依赖。它可以是100行或更多或更少。


10. 当您最终获取更多行时，客户端会向服务器请求更多行。这可能是客户端用完时或可能抢先完成。这也是实现依赖的。

1. You open a connection to the database;
2. There is possibly a second call to select a database or it might be done as part of (1);
3. That authentication and connection step is (at least) one round trip to the server (ignoring persistent connections);
4. You execute a query on the client;
5. That query is sent to the server;
6. The server has to determine how to execute the query;
7. If the server has previously executed the query the execution plan may still be in the query cache. If not a new plan must be created;
8. The server executes the query as given and returns a result to the client;
9. That result will contain some buffer of rows that is implementation dependent. It might be 100 rows or more or less. All columns are returned for each row;
10. As you fetch more rows eventually the client will ask the server for more rows. This may be when the client runs out or it may be done preemptively. Again this is implementation dependent.

这一切的想法是最小化到服务器的往返，而不发回太多不必要的数据，这就是为什么如果你请求一百万行，你不会一次全部回来。

The idea of all this is to minimize roundtrips to the server without sending back too much unnecessary data, which is why if you ask for a million rows you won't get them all back at once.

LIMIT子句或任何子句

LIMIT clauses--or any clause in fact--will modify the result set.

最后，（7）很重要，因为 SELECT * FROM table WHERE a ='foo'和 SELECT * FROM table WHERE a ='bar'是两个不同的查询，就数据库优化器而言，必须为每个分别。但是具有不同参数的参数化查询（ SELECT * FROM table WHERE a =：param ）是一个查询，只需要计划一次（至少直到它落在查询缓存）。

Lastly, (7) is important because SELECT * FROM table WHERE a = 'foo' and SELECT * FROM table WHERE a = 'bar' are two different queries as far as the database optimizer is concerned so an execution plan must be determined for each separately. But a parameterized query (SELECT * FROM table WHERE a = :param) with different parameters is one query and only needs to be planned once (at least until it falls out of the query cache).

这篇关于PHP / MySQL数据库查询如何工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！