問題描述

我正在通過命令行 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 -- 不包括獲取調(diào)用所需的時間.

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 客戶端一樣快.繼續(xù)閱讀令人難以置信的細節(jié).

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 選擇為索引使用不同的字段，因為據(jù)我所知，這些東西不應該相關；但遺憾的是，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模板網(wǎng)！