相同的查询，两种不同的方式，性能截然不同
我有一个包含超过 800 万行的 Postgres 表。鉴于以下两种通过 DBD::Pg 执行相同查询的方法，我得到了截然不同的结果。
$q .= '%';
## query 1
my $sql = qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE '$q'
};
my $sth1 = $dbh->prepare($sql);
$sth1->execute();
## query 2
my $sth2 = $dbh->prepare(qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE ?
});
$sth2->execute($q);
查询 2 至少比查询 1 慢一个数量级...看起来它没有使用索引，而查询 1 正在使用索引。
很想听听为什么。
I have a Postgres table with more than 8 million rows. Given the following two ways of doing the same query via DBD::Pg, I get wildly different results.
$q .= '%';
## query 1
my $sql = qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE '$q'
};
my $sth1 = $dbh->prepare($sql);
$sth1->execute();
## query 2
my $sth2 = $dbh->prepare(qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE ?
});
$sth2->execute($q);
query 2 is at least an order of magnitude slower than query 1... seems like it is not using the indexes, while query 1 is using the index.
Would love hear why.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
对于
LIKE表达式，只有当搜索模式是左锚定的，即以%终止时，才能使用b 树索引。 更多详细信息请参阅手册。感谢@evil otto 提供的链接。此链接指向当前版本。
您的第一个查询在准备时提供了此基本信息，因此查询规划器可以使用匹配的索引。
您的第二个查询在准备时不提供有关模式的任何信息，因此查询规划器无法使用任何索引。
With
LIKEexpressions, b-tree indexes can only be used if the search pattern is left-anchored, i.e. terminated with%. More details in the manual.Thanks to @evil otto for the link. This link to the current version.
Your first query provides this essential information at prepare time, so the query planner can use a matching index.
Your second query does not provide any information about the pattern at prepare time, so the query planner cannot use any indexes.
我怀疑在第一种情况下，查询编译器/优化器检测到该子句是一个常量，并且可以构建最佳查询计划。在第二个中，它必须编译一个更通用的查询，因为绑定变量在运行时可以是任何内容。
I suspect that in the first case the query compiler/optimizer detects that the clause is a constant, and can build an optimal query plan. In the second it has to compile a more generic query because the bound variable can be anything at run-time.
您是否使用相同的 $dbh 对象从同一文件运行两个测试用例？
我认为在第二种情况下提高速度的原因是您使用已经解析的准备好的语句（但也许我错了:)）。
Are you running both test cases from same file using same $dbh object?
I think reason of increasing speed in second case is that you using prepared statement which is already parsed(but maybe I wrong:)).
啊，我明白了——我会在这个评论后退出，因为我不懂 Perl。但我相信编辑器将 $q 突出显示为常量是正确的。我猜您需要将值连接到字符串中，而不是直接引用变量。所以，我的猜测是，如果 + 用于 perl 中的字符串连接，则使用类似以下内容：
my $sql = qq{
选择a、b、c
从 t
哪里下(a) LIKE '
} + $q + qq{'};
（注：除非该语言与数据库紧密集成，例如Oracle/PLSQL，否则通常必须在提交到数据库之前创建一个完全有效的SQL字符串，而不是期望编译器“插值”/“替换”该值）
我再次建议您获取语句的 COUNT()，以确保您正在比较苹果与苹果。
Ahh, I see - I will drop out after this comment since I don't know Perl. But I would trust that the editor is correct in highlighting the $q as a constant. I'm guessing that you need to concatenate the value into the string, rather than just directly referencing the variable. So, my guess is that if + is used for string concatenation in perl, then use something like:
my $sql = qq{
SELECT a, b, c
FROM t
WHERE Lower( a ) LIKE '
} + $q + qq{'};
(Note: unless the language is tightly integrated with the database, such as Oracle/PLSQL, you usually have to create a completely valid SQL string before submitting to the database, instead of expecting the compiler to 'interpolate'/'Substitute' the value of the variable.)
I would again suggest that you get the COUNT() of the statements, to make sure that you are comparing apple to apples.
我根本不知道 Postgres，但我认为在第 7 行（WHERE Lower( a ) LIKE '$q'
), $q 实际上是一个常数。看起来你的编辑也这么认为，因为它以红色突出显示。您可能仍然需要使用 ?对于变量。
要进行测试，请执行 COUNT(*)，并确保它们匹配 - 我可能会大错特错。
I don't know Postgres at all, but I think in Line 7 (WHERE Lower( a ) LIKE '$q'
), $q is actually a constant. It looks like your editor thinks so too, since it is highlighted in red. You probably still need to use the ? for the variable.
To test, do a COUNT(*), and make sure they match - I could be way offbase.