我发现"SELECT"SQL查询中的"LIKE"运算符在pdo_sqlite(PHP 5.3或PHP 5.4)时非常慢。
在 sqlite3 二进制文件中输入的相同查询要快得多。
示例代码:
<?php
$bdd = new PDO('sqlite:./chaines_centre.db');
$reponse = $bdd->prepare("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = ? and NomChaine like 'DCLC257__' order by DateMonteeAuPlan DESC limit 20;");
$reponse->execute($_GET['job']);
while ($donnees = $reponse->fetch())
{
// whatever...
}
$reponse->closeCursor();
?>
这是我用以下方法制作的快速"基准":
- 用于pdo_sqlite度量的 XDebug 跟踪
- 带有".timer on"的SQLite二进制文件
NomChaine like 'DCLC257__'
:
● pdo_sqlite : 1.4521秒 ✘
● SQLeT3 二进制 : 0.084s ✔
NomChaine like 'DCLC257%'
:
● pdo_sqlite : 1.4881秒 ✘
● SQLeT3二进制:0.086秒 ✔
NomChaine = 'DCLC25736'
:
● pdo_sqlite:0.002秒✔(我认为有点长,但很快)
● SQLeT3 二进制 : 0.054s ✔
我怎样才能改善这种情况?
编辑:也许我太关注"喜欢"运算符了。
<?php
$bdd = new PDO('sqlite:./chaines_centre.db');
$time_start = microtime(true);
$reponse = $bdd->query("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' order by DateMonteeAuPlan DESC limit 20;");
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Situation 1 : $time second(s)<br><br>";
// Output : 1.3900790214539 second(s)
$time_start = microtime(true);
$reponse = $bdd->query("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine like 'DCLC257%' limit 20;");
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Situation 2 : $time second(s)<br><br>";
// Output : 0.0030009746551514 seconde(s)
$time_start = microtime(true);
$reponse = $bdd->query("select DateMonteeAuPlan, Debut, Fin, Statut from ReportJobs where NomJob = 'NSAVBASE' and NomChaine = 'DCLC25736' order by DateMonteeAuPlan DESC limit 20;");
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Situation 3 : $time second(s)<br><br>";
// Output : 0 seconde(s)
?>
通过删除LIKE
运算符或order by DateMonteeAuPlan
,查询将在预期时间内执行...
太奇怪了。o_O
您是否偶然在同一脚本中运行了PDO与二进制文件(一个接一个)?如果您这样做了,那么使用二进制获得更好的结果是正常的,因为 PDO 在缓存为空时运行(因此它会击中光盘),而二进制从 RAM 获取数据。
对于第二个脚本,情况肯定如此:第一个查询获得 1.3+ 秒,因为它也读取数据,而其余查询从 RAM 获取数据。
有关详细信息,请参阅 http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html#pragma-cache_size。