PHP 运行查询所需的时间是 MySQL 客户端的 90 倍

问题描述

我正在通过命令行 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() only -- 不包括获取调用所需的时间.

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 中的 EXPLAIN 与 CLI 中的 EXPLAIN 不同.我不确定连接的任何方面是否会导致 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;

希望这对某人有所帮助！

Hope this helps someone!

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