PHP：查询MySQL的最快方法是什么？因为PDO非常缓慢

I need to perform a simply query.

我需要执行一个简单的查询。

Literally, all I need to perform is:

从字面上看,我需要做的就是:

SELECT price, sqft, zipcode FROM homes WHERE home_id = X

When I use PHP PDO, which I've read is the recommended way to connect to a MySQL database, simply creating the connection takes a measured 610ms.

当我使用PHP PDO时,我已经阅读了推荐的连接MySQL数据库的方法,只需创建连接就需要610毫秒。

My code is below:

我的代码如下:

try {
    $conn_str = DB . ':host=' . DB_HOST . ';dbname=' . DB_NAME;
    $dbh = new PDO($conn_str, DB_USERNAME, DB_PASSWORD);
    $params = array();    
    $sql = 'SELECT price, sqft, zipcode FROM homes WHERE home_id = :home_id';
    $params[':home_id'] = X;
    $stmt = $dbh->prepare($sql);    
    $stmt->execute($params);
    $result_set = $stmt->fetchAll(PDO::FETCH_ASSOC);
    // json output  
    ob_start("ob_gzhandler");
    header('Content-type: text/javascript; charset=utf-8');
    print "{'homes' : ";
    print json_encode( $result_set );
    print '}';
    ob_end_flush();
    $dbh = null;
} catch (PDOException $e) {
    die('Unable to connect');
}

Question: What's the fastest way for me to connect to my MySQL database to perform the query above?

问题:连接到MySQL数据库以执行上述查询的最快方法是什么?

4 个解决方案

#1

Fastest possible :

最快的可能:

mysqli_connect("servername", "user", "pass") or die("can't connect");
mysqli_select_db("dbname") or die("can't select database");

list($price, $sqft, $zipcode) = mysqli_fetch_array(mysqli_query("SELECT price, sqft, zipcode FROM homes WHERE home_id = ".mysqli_real_escape_string($home_id)));

[EDIT]: Now using mysqli instead of mysql.

[编辑]:现在使用mysqli而不是mysql。

#2

If the slowness is due to having to reach over the network for each connection, and mysql having to do a reverse DNS lookup to check through its GRANTs table, then that overhead could very well account for a large chunk of the latency. Switching to persistent connections would make it a one-time cost for the life of the connection.

如果缓慢是由于必须通过网络到达每个连接,并且mysql必须执行反向DNS查找以检查其GRANTs表,那么该开销可能很好地解决了大部分延迟。切换到持久连接将使其成为连接生命周期的一次性成本。

However, this does lead to othe problems. Since transactions are rolled back and locks released when the connection holding them is closed, going persitent means they'll stay active. Without taking great care in your code to not leave the connection in an inconsistent state, you could very well create a deadlock or at least lock out all other connections until you go in manually and clean up.

然而,这确实导致了其他问题。由于事务被回滚并且当持有它们的连接关闭时释放锁定,因此持续存在意味着它们将保持活动状态。如果不在代码中小心谨慎,不要让连接处于不一致状态,那么你很可能会创建一个死锁或至少锁定所有其他连接,直到你手动进行清理。

#3

Guess PDO is as fast as MYSQLI. I think your problem is how you connect with PDO. Propably your connectionstring looks like:

猜猜PDO和MYSQLI一样快。我认为你的问题是你如何与PDO联系。你的connectionstring看起来像是这样的:

:host=localhost;:dbname=foo

And there is the problem... PDO tries to connect to localhost but PDO uses the DNS to turn localhost into 127.0.0.1 and this is what costs time. If you use 127.0.0.1 directly you wont have these problems anymore :) So the connectionstring must look like

并且存在问题...... PDO尝试连接到localhost,但是PDO使用DNS将localhost转换为127.0.0.1,这就是花费时间。如果你直接使用127.0.0.1,你将不再遇到这些问题:)所以connectionstring必须看起来像

:host=127.0.0.1;:dbname=bar

#4

as of version php 5.3.0 the fastest and most lightweight way of calling into the db from php is as follows:

从php 5.3.0版开始,从php调用db的最快,最轻量级的方法如下:

This example uses the mysql/ext (not mysqli) and calls stored procedures

此示例使用mysql / ext(而不是mysqli)并调用存储过程

$conn = mysql_connect("localhost", "user", "pass");
mysql_select_db("db");

$sql = sprintf("call get_user(%d)", 1);

$result = mysql_query($sql);

mysql_free_result($result);
mysql_close($conn);

The stored procedure:

存储过程:

delimiter #
create procedure get_user
(
in p_user_id int unsigned
)
begin
    select 
     u.user_id, u.username, u.status_id, s.name as status_name, ...
    from 
        users u
    inner join user_status s on u.status_id = s.status_id
    ...
    where 
      u.user_id = p_user_id;
end #

delimiter ;

#1