PHP - MySQL 1.000.000记录查询优化

Question

感谢你花时间在我的问题上，如果已经有人问过类似的问题，我很抱歉。

我有以下数据库示意图：

​

​

我只有数据库的SELECT权限。在当前设置下，我的查询倾向于运行2-3秒到10秒。我被要求优化到当前配置的最佳结构，我的SQL如下所示：(我需要获得所有订单中每个产品的总收入，这些订单间隔为1个月)

SELECT items.`type` AS type, 
  DATE(orders.date) AS date,
   sum(items.price * orders.quantity * clients.discount_percent/100) AS total
  FROM orders
 INNER JOIN items ON orders.item = items.id
 INNER JOIN clients ON orders.`client` = clients.id
 GROUP BY DATE(orders.date), items.`type`

我正在考虑通过修改在PHP端处理数据的SQL来对结果进行分块-但不确定这样做是否有效。

如果这可以优化的话，我希望听到你的意见:)。

KR，迪皮

附注:解释结果：EXPLAIN

Answer 1

一般来说，您的性能问题是由对“order”的全表扫描引起的。

对于一个很小的数据集(~10MB)来说，这么大的运行时间是很奇怪的。在我的服务器上执行类似的查询大约需要1秒。

尝试优化数据类型以适应较小的数据大小。(对于index，您必须使用int)。

检查Orders.Data是否有索引。

检查Orders.Data是否为Date类型以提高索引性能。

在所有情况下，您都在使用一些聚合函数(group和sum)，这些类型的函数非常昂贵，因为服务器首先生成一个包含所有数据的大表，然后执行聚合函数。

如果您的性能问题是由于服务器配置(工作负载、ram或…)引起的您可以缓存结果，以避免每次计算所有结果时都要重新计算。

Answer 2

字段orders.item和orders.client是否已编入索引？如果您使用Phpmyadmin创建表，您必须指定字段作为索引以及将要具有的关系，因此当您使用JOIN进行类似查询时，查询针对搜索进行了最优化。

当然，您可以对表执行ALTER操作并像INDEX一样设置字段，但它们必须是同一类型，否则关系将无法指定！例如，您的PRIMARY KEY和FOREIGN KEY必须为INT UNSIGNED。

我希望你认为这个答案是有用的。