PHP运行查询所花的时间比MySQL客户端长90倍 [英] PHP takes 90x longer to run query than MySQL client

问题描述

我正在通过命令行PHP脚本运行MySQL查询(使用mysqlnd驱动程序上的PDO进行预查询).这是一个简单的查询，只有一个左联接，返回每行100行，每行7小列.

I'm running a MySQL query via a command-line PHP script (prepared query using PDO on the mysqlnd driver). It's a simple query with a single left-join, returning 100 rows and 7 small columns per row.

当我在MySQL CLI中(在运行有问题的PHP脚本的同一台机器上)运行此查询时，它花费了0.10秒-即使抛出了SQL_NO_CACHE标志.

When I run this query in the MySQL CLI (on the same machine running the PHP script in question), it takes 0.10 seconds -- even with the SQL_NO_CACHE flag thrown in.

当我运行此查询时，通过PDO进行准备需要9秒钟以上.这是execute()仅 －不包括提取调用所需的时间.

When I run this query, prepared, through PDO, it takes over 9 seconds. This is execute() only -- not including the time it takes for the fetch call.

我的查询示例:

SELECT HEX(al.uuid) hexUUID, al.created_on,
    IFNULL(al.state, 'ON') actionType, pp.publishers_id publisher_id,
    pp.products_id product_id, al.action_id, al.last_updated
FROM ActionAPI.actionLists al
LEFT JOIN ActionAPI.publishers_products pp
    ON al.publisher_product_id = pp.id
WHERE (al.test IS NULL OR al.test = 0)
    AND (al.created_on >= :since OR al.last_updated >= :since)
ORDER BY created_on ASC
LIMIT :skip, 100;

考虑到我尝试过的每个本机MySQL客户端都几乎立即运行了该查询，我不认为该查询有问题，但这是关于踢的解释:

I don't believe the query is at fault, considering every native MySQL client I've tried has run it near-instantly, but here's the EXPLAIN for kicks:

+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys           | key        | key_len | ref                               | rows | Extra       |
+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
|  1 | SIMPLE      | al    | index  | created_on,last_updated | created_on | 8       | NULL                              |  100 | Using where |
|  1 | SIMPLE      | pp    | eq_ref | PRIMARY                 | PRIMARY    | 4       | ActionAPI.al.publisher_product_id |    1 |             |
+----+-------------+-------+--------+-------------------------+------------+---------+-----------------------------------+------+-------------+
2 rows in set (0.00 sec)

PDO到底在做什么，耗时8.9秒?

What in the world is PDO doing that is taking 8.9 seconds?

如评论中所述，我也为此编写了mysql_query版本，并且它的性能同样很差.但是，删除WHERE子句的一部分，使其运行速度与MySQL客户端一样快.继续阅读以获取令人难以置信的详细信息.

As stated in the comments, I've written a mysql_query version of this as well, and it has the same poor performance. Removing part of the WHERE clause, however, makes it run as fast as the MySQL client. Read on for mind-boggling details.

推荐答案

对此问题进行了很晚的更新:

Giving a very belated update on this question:

我没有找到原因，但是事实证明，PHP和CLI的解释是不同的.我不确定连接的任何方面是否会导致MySQL选择对索引使用不同的字段，因为据我所知，这些东西不应该相关.但是可惜，PHP的EXPLAIN显示未使用正确的索引，而CLI却在使用.

I've not found the cause, but it turns out the EXPLAIN was different in PHP versus on the CLI. I'm not sure if any aspect of the connection would cause MySQL to choose to use a different field for the index, because as far as I know those things shouldn't be related; but alas, PHP's EXPLAIN showed that the proper index was not being used, while the CLI's did.

这种(令人困惑的)案例的解决方案是使用 index提示.从我的示例中看到此修改后的查询中的"FROM"行:

The solution in this (baffling) case is to use index hinting. See the 'FROM' line in this modified query from my example:

SELECT HEX(al.uuid) hexUUID, al.created_on,
    IFNULL(al.state, 'ON') actionType, pp.publishers_id publisher_id,
    pp.products_id product_id, al.action_id, al.last_updated
FROM ActionAPI.actionLists al USE INDEX (created_on)
LEFT JOIN ActionAPI.publishers_products pp
    ON al.publisher_product_id = pp.id
WHERE (al.test IS NULL OR al.test = 0)
    AND (al.created_on >= :since OR al.last_updated >= :since)
ORDER BY created_on ASC
LIMIT :skip, 100;

希望这对某人有帮助！

这篇关于PHP运行查询所花的时间比MySQL客户端长90倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！