php - 使用占位符时 PDO 慢执行()阶段
问题描述
我正在制作一些返回计数和按结果分组的 JSON 端点。对于这个端点,我使用 PDO 和命名占位符。使用命名占位符时,PHP 响应在该execute
阶段最多需要 6 秒。当执行相同的查询并将值直接放入我的查询中时,响应几乎是瞬间的。
我正在更新仍然使用 mysql_query() 来使用 PDO 语句的旧代码。
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");
$conn = new PDO('mysql:host=localhost;dbname=nameOfDB', 'username', 'password');
$conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->exec("set names utf8");
$query_slow = "SELECT t.meta as meta, count(*) as cnt
FROM field, repo, t, uplink, link
WHERE (
repo.rp1 = field.id OR
repo.rp2 = field.id OR
repo.rp3 = field.id OR
repo.rp4 = field.id)
AND repo.combination = t.meta
AND t.doc_id = uplink.doc_id
AND uplink.written = 1
AND uplink.link_id = link.id
AND field.id = :field
AND t.earliest > :min
AND t.latest < :max
GROUP BY t.meta
ORDER BY cnt desc";
if($parameters){
$stmt = $conn->prepare($query_slow);
$stmt->execute($parameters);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($data, JSON_UNESCAPED_UNICODE );
}
如果我这样做,var_dump($parameters)
我会返回以下内容:
$parameters = array(3) {
[":field"]=>
int(5)
[":min"]=>
int(-1000)
[":max"]=>
int(600)
}
这是快速查询:
$query_fast = "SELECT t.meta as meta, count(*) as cnt
FROM field, repo, t, uplink, link
WHERE (
repo.rp1 = field.id OR
repo.rp2 = field.id OR
repo.rp3 = field.id OR
repo.rp4 = field.id)
AND repo.combination = t.meta
AND t.doc_id = uplink.doc_id
AND uplink.written = 1
AND uplink.link_id = link.id
AND field.id=5
and t.earliest > -1000
and t.latest <600
GROUP BY t.meta
ORDER BY cnt desc";
if(1==1){
// I ditched the $parameters, so my execute is empty.
$stmt = $conn->prepare($query_slow);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($data, JSON_UNESCAPED_UNICODE );
}
slow_query 最多可能需要 6 秒才能运行。如果我用填充的值运行相同的查询并且为空,execute()
我的响应几乎是瞬间出现的。我对 PDO 做错了什么?
当给execute()
我的页面一个数组时响应很慢。使用没有参数的查询时。响应是即时的。
@RiggsFolly 要求的我的计时码:
所以我在那里运行了计时代码:
if($parameters){
$time_prepare = microtime(true);
$stmt = $conn->prepare($querygraph);
$time_pre_exec = microtime(true);
$stmt->execute(($parameters));
//$stmt->execute();
$time_post_exec = microtime(true);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
$time_post_fetch = microtime(true);
echo json_encode($data, JSON_UNESCAPED_UNICODE );
}
这是使用参数 5、-800 和 800(之前使用的相同)。我的计时码显示:
start: 1563972660.9188 (First line of the script)
prepare: 1563972660.9197 ==$time_prepare
pre execution: 1563972660.9201 ==$time_pre_exec
post execution: 1563972669.0058 ==$time_post_exec ==> 9 seconds!
post fetch: 1563972669.0058
在没有占位符的查询上使用相同的参数时,我使用相同的查询和一个空的 execute()。我用我在准备好的语句中使用的相同值替换了占位符。时间看起来像:
prepare: 1563973120.2965
pre execution: 1563973120.2969 //before execution()
post execution: 1563973120.312 //after executeion()
post fetch: 1563973120.3121
解决方案
对于那些有类似问题的人;@YourCommonSense 的建议解决了它。
我必须明确使用 bindValue(); 现在整个执行 - 从开始到结束 - 大约需要 0.02 秒,这比我希望的要好得多。对于动态生成的查询,我将代码更改为:
$conditions = []; //values to be inserted by type on placeholder
$parameters = []; //kind of thing to execute in the query with the placeholder (substring of a query with a named placeholder)
$bindTypes=[]; //the type expected to be for a placeholder (the function takes this as third argument. It should be a long type (strings give errors, hence the if/else if block))
$placeholders=[]; //contains only the :placeholders. Use this to tell the bindvalue() function that you want to bind a value X to placeholder :X
if(isset($_GET["min"])){
$mindate = (int)$_GET["min"];
if($mindate != ""){
$conditions[] = 't.earliest>= :mindate';
$parameters[] = $mindate;
$bindTypes[] = 'int';
$placeholders[] = ':mindate';
}
}
if(isset($_GET["max"])){
$maxdate= (int)$_GET["max"];
if($maxdate!= ""){
$conditions[] = 't.latest>= :maxdate';
$parameters[] = $maxdate;
$bindTypes[] = 'int';
$placeholders[] = ':maxdate';
}
}
if($parameters){
$stmt = $conn->prepare($querygraph);
for ($i=0; $i<count($placeholders); $i++) {
if($bindTypes[$i]=='str'){// it's better to use integers as comparison
$stmt->bindValue($placeholders[$i], $parameters[$i], PDO::PARAM_STR);
} else if($bindTypes[$i]=='int'){
echo $placeholders[$i];
$stmt->bindValue($placeholders[$i], $parameters[$i], PDO::PARAM_INT);
}
}
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo json_encode($data, JSON_UNESCAPED_UNICODE );
}
推荐阅读
- python - Python中元组的返回类型注释
- git - 如果将父分支合并到主分支,子分支会发生什么?
- c++ - C++ 令牌粘贴运算符未在预处理器定义中连接 __LINE__
- loopback4 - Loopback 4 提取 POST 请求的原始正文
- flutter - Flutter - SliverFillRemaining 落后于 SliverPersistentHeader
- gradle - 使用 jOOQ 和 Gradle 生成代码时出现“不明确的类型名称”错误
- php - Laravel 与递归表的多对多关系
- reactjs - React,Typescript - 打印获取的数据
- asp.net - 如何将所有动态添加的文本框保存到 ASP MVC 数据库中的两列?
- node.js - Windows 中的多个终端命令