PDO语句比MySQL CLI长400倍 [英] PDO Statement Taking 400x Longer Than MySQL CLI

问题描述

我正在运行PDO准备好的语句，以从大约6k行的表中进行选择.由于WHERE语句具有约5k个pId，因此该特定查询最终返回所有行.该表在pId列上也有一个索引.

I am running a PDO prepared statement to select from a table of around ~6k rows. This particular query ends up returning all of the rows due to the WHERE statement which has ~5k pIds. The table has an index on the pId column as well.

SELECT * FROM table_a WHERE pId in (?, ? ,? ....)

此查询在PHP中运行需要4.5秒，而在MySQL CLI中运行则需要0.01秒. PHP和MySQL的EXPLAIN语句相同，都使用pId上的索引不.我认为这是由于MySQL意识到它正在返回整个表而无需使用索引这一事实.

This query takes 4.5 seconds to run in php and when run in the MySQL CLI it takes .01 seconds. The EXPLAIN statement for PHP and MySQL are the same, both are NOT using the index on pId. I think this is due to the fact that MySQL is aware that it is returning the entire table and does not need to use the index.

我知道准备好的语句会有一些开销，但是我在其他地方运行了一个非常相似的查询(不同的表名)，而且花费的时间不长(〜.9秒).有什么想法吗?

I know there's some overhead with prepared statements but I am running a very similar query (different table name) elsewhere and it's not taking close to as long (~.9 seconds). Any ideas?

PHP版本:5.5

PHP Version: 5.5

MySql版本:5.6

MySql Version: 5.6

推荐答案

我怀疑速度较慢是在获取行，返回的行数而不是语句中的5000+个绑定占位符上. pId IN ( ? , ? , ... , ? )

I suspect the slowness is in fetching the rows, the number of rows being returned, rather than the 5000+ bind placeholders in the statement. pId IN ( ? , ? , ... , ? )

我的建议是测试仅返回单行，提供一个已知存在的值/返回一行，然后提供4999+个不存在的值/不返回行.

My suggestion would be to test returning only a single row, supply one value that is known to exist/return a row, and then 4999+ values that are known not to exist/not to return a row.

例如，如果我们知道表中的最高pId值，请使用高于该值的值，为这样的语句提供绑定值

For example, if we know the highest pId value in the table, use values higher than that, supply bind values for a statement like this

 ... pId IN ( ? , ? , ? , ... , ? )

所以结果等同于运行

 ... pId IN ( 99999999 , 99999998 , 99999997 , ... , 42 )

与我们将要运行的结果相同

which would be the same result we would get running

 ... pId IN ( 42 )

我们的期望是只返回一行(pId = 42).

Our expectation would be to return just one row ( pId = 42 ).

然后将其时间(5000多个绑定值返回1行)与两个绑定值返回单行的时间进行比较

Then compare the timing of that ( 5000+ bind values returning 1 row ) to two bind values returning a single row

 ... pId IN ( 99999999 , 42 )

看看性能是否有显着差异.

And see if there is a significant difference in performance.

(要处理5000多个绑定值，还有很多工作要做，但是我不希望有很大的差异，但是应该对此进行测试.

(There's more work to do with 5000+ bind values, but I wouldn't expect a huge difference, but it should be tested.

稍微想一想，使用所有现有的绑定值设置测试可能会更容易，只需在查询的末尾添加LIMIT 2即可. (我不确定MySQL是否对LIMIT 2进行了一些性能增强.

Thinking on it a bit, it might be easier to setup a test using all the existing bind values, and just adding LIMIT 2 to the end of the query. (I'm not sure if MySQL has some performance enhancements for LIMIT 2.

添加像AND pId * 10 = 420

目标是提供一整套绑定值，但只返回一两行.

The goal is to supply a whole slew of bind values but only return one or two rows.

另一个测试是返回整个行，但仅使用几个绑定值.也许是返回5000+行的范围条件.

Another test would be to return a whole slew of rows, but using only a couple of bind values. Maybe a range condition that returns 5000+ rows.

查询可能是:

 ... pId >= ? AND pId <= ? 

在提供的值之间有足够大的范围，我们可以在5000行附近获得

with a large enough range between the provided values that we get in the neighborhood of 5000 rows.

比较性能.

我的预测(猜测?)是，性能将更多地与返回的行数而不是绑定值的数目相关.

My prediction (guess?) is that performance will be correlated more with the number of rows returned, rather than the number of bind values.

我不确定这是否是您的问题的答案，但这是我要回答这个问题的方法……是什么导致它变慢，绑定值的数量或返回行?"

I'm not sure if this is an answer to your question, but it's the approach I would take to answer the question ... "what is causing this to be slow, the number of bind values, or the number of rows returned?"

这篇关于PDO语句比MySQL CLI长400倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！