PHP PDO庞大数据库查询速度优化

Question

我正在建立一个网站 geekwiz.com。测试加载它，LONG。

我是 php 新手，刚刚从 mysql_query 更改为 PDO。我必须执行多次查询才能在第一页上显示产品，并且该表有超过 150 万条记录。

我怎样才能让它更快？

这是我创建的代码:

#connection
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

单独的文件

#fetch data
require_once('db_config.php');
$STH = $DBH->query("
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('notebook pc - other') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Desktop PC') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Hard Drives - External') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Camcorders - Analog/Digital') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Monitors - LCD Flat Panel') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Software - PC Games') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('third party accessories') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE advertisercategory LIKE ('LED TV') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Motherboards - %') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE advertisercategory LIKE ('LED TV') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Hard Drives - External') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('CPU Cooling') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('CPU Thermal Paste / Grease') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Keyboards') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Memory (USB Flash Drive)') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Memory (Desktop Memory)') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Memory (Notebook Memory)') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Mouse') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Printer - Inkjet Printers') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Projectors') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Processors - Desktops') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;

");

$STH->setFetchMode(PDO::FETCH_OBJ);

while ($row = $STH->fetch()) {
echo "<ul>";
echo "<li>" .$row->name ."</li>";
echo "<li>" .$row->buyurl ."</li>";
echo "<li>" .$row->imageurl ."</li>";
echo "<li>" .$row->price ."</li>";
echo "<li>" .$row->manufacturer ."</li>";
echo "<li>" .$row->manufacturerid ."</li>";
echo "</ul>";
}

Answer 1

有一些问题，您可能不会喜欢我所说的。

按 text/varchar 排序非常慢，并且更新将花费很长时间。您应该做的是将所有文本转换为整数。因此，advertisercategory 中不应包含实际文本。相反，它应该是一个固定长度的INT，链接到存储实际文本的表。 special 和 instock 也是如此。 standardshippingcost 看起来像一个 varchar，因为它用单引号引起来，如果它看起来像这样，那实际上不应该是这样。如果它是数字，它应该是数字。我不太确定您的架构，因此 standardshippingcost 可能需要位于通过 FK 链接到 catalog 的自己的表中。 lastupdated 应该是 Unix 时间的 INT。

您的数据应为 INT 的原因是因为 1) 它比 notebook pc - other 占用的空间更少(因此排序时占用的内存更少)，2) 更新将更快(我假设你使用的是 InnoDB)。

因此，当您排序时，SELECT 所有 id(应该是自动增量 PRIMARY)， advertercategory、instock、standardshippingcost、standardshippingcost 和 lastupdated 放入 PHP 数组中。找到最有效的方式来获得你想要的东西(你会找到捷径)。最后，通过PRIMARY键SELECT具体数据。

我会向后解释。这种方法之所以能产生最高的性能是因为，如果您在没有任何其他索引的 InnoDB 表上从 PRIMARY 上 SELECT ，那么无论您的表有多大，都没有关系也就是说，它将“立即”返回。正如您所经历的，使用巨大的、深奥的查询是很慢的。这就是您实际检索数据的方式。

PHP 在逻辑上比 MySQL 更快。这就是它的用途。堆栈上有很多 Q 表明了这一点。这就是为什么你想在 PHP 上进行排序。另外，如果您在数据中看到某种统计或其他类型的模式，您实际上可以编写更好的算法来使排序运行得更快。

除了 unix 日期之外，其他列不应太长。 advertisercategory 可能会有点大，但与长度已经为 19 的 notebook pc - other 相比，它不会有任何变化。我在您的 Q 中看到大约 30 个 advertisercategory，所以这只是一个 INT LENGTH 2 因为那是 100 个潜在值。这将在排序时节省空间和内存。

此外，更新时使用固定长度整数会更快，因为聚集表将始终保持自身紧凑并进行碎片整理。如果您将集群 InnoDB 中间的一行从'Memory (USB Flash Drive)'UPDATE到'Projectors'，您就会导致您的数据库对整个数据库进行碎片整理，以消除您刚刚造成的间隙。如果您在固定长度 int 列中从一个 1 更改为 10，则不会发生这样的事情，因为两者占用相同的空间量，不会产生间隙，无需碎片整理集群的。

当您这样做时，您会对性能的大幅提升感到震惊，但是，是的，这将涉及更多代码。那么您的优先级是什么，性能还是开发时间？

<小时/>

哎呀!忘记unix时间解释了。您想要使用它是因为您可以将其存储在 INT 中(秒)和 DECIMAL 中(微秒)。由于 php 是在 UNIX 中运行的，因此这样做可以在 PHP 中实现最简单的操作。