php - 如何提高具有多个Where条件和Order By的查询速度？

Question

我已经存储了根据颜色、重量和质量创建产品所需的所有属性的所有排列和组合，并将其存储在属性表中。

在程序中，我循环处理该表中的数据，并创建 Select sql 来查询另一个包含基于属性的产品价格的表，并将其存储在第三个表中。

我假设属性表中有 7000 条记录，价格表中有 20 万条记录。所以程序循环到7000条记录，每个select SQL查询20万条记录。

选择包含多个Where子句和一个Order by on Price以获得最低价格的SQL。

我的问题是如何减少查询执行时间。

示例:-

属性表

sno  color      Quality  Weight
1      blue     Good       3kg
2      red      Fair        1kg   
3      Yellow   Excellent   1.5Kg

价格表

sno     color     Quality    Weight(in kgs)    Market Price  Our Price 
1      sky blue    Good       4                 $400           $360
2      orange red  Excellent  2                 $500           $450

我店的红色价格表 - 深红、橙红等

但是我有红色的属性，因为红色是原色，在市场上你买不到完美的红色。因此，我给出的产品接近红色，并且在该红色组中更便宜。

select * from tbl_price 
where color IN {Array [tbl data Entry i.e. RED ]    gives-> ( "Crimson Red", "Orange Red","Carrot Red" )}
AND Quality IN {Array [tbl data Entry i.e. Good ] gives-> ("Above Average","Medium","Not Bad" )}
AND Weight >= {tbl Entry of Weight}
Order by OurPrice ASC
LIMIT 1,1;

Answer 1

quote: “程序循环到7000条记录，每个select SQL查询20万条记录”

这听起来像是您正在处理 RBAR(逐行处理)并运行 7,000 个查询。

(在 SQL 中)将行作为一个集合进行处理并运行单个查询来返回所需的结果集通常会更高效。

与发出 SQL 语句相关的开销(客户端发送 SQL 文本，服务器解析文本，执行语法检查，执行语义检查，选择执行计划，然后执行计划，准备结果集，将结果返回到客户。)

对于数千次查询执行，语句处理开销可能占总运行时间的很大一部分，无论您运行实际查询的效率如何。

(我完全有可能误解了你所说的话。)

奇怪的是，您的示例查询显示了一个 LIMIT 1,1 子句。这是“跳过”结果集中的第一行。这似乎有点奇怪，因为您说您想要获得“最低”价格，而不是第二低的价格。

对于MySQL，可以使用EXPLAIN来查看执行计划；这可能有助于洞察性能问题。有时，添加适当的索引可能会显着提高性能。但是，在这种情况下，如果您尝试改进的查询正在搜索包含两行的表，则任何索引都不太可能对性能产生任何影响。

底线是，为了提高性能，如果您要执行 7,000 个查询，请重新设计，以便将所有 7,000 行作为一个集合进行处理。返回结果的单个查询可能会更有效。