php - 需要性能良好的SQL查询才能选择不符合条件的数据

Question

我有一个数据库


company表
country表
company_country n：n表，用于定义在哪个国家/地区可以使用的公司
product表（每个产品属于一个特定的categoryId）
还有一个company_product_country n：n：n表，该表定义了哪个公司在哪个国家/地区提供哪种产品。


后者具有三个主键列companyId，productId，countryId和其他列val和limitedAvailability。 val是具有值yes|no|n/a的ENUM，而limitedAvailability是具有值0|1的ENUM。
类别1或2中的产品在所有国家/地区都可用，因此获得countryId = 0。但同时，只有这些产品可能会有limitedAvailability = 1。

可以在此处找到带有测试数据库的SQLFiddle：http://www.sqlfiddle.com/#!9/a065a/1/0
它包含五个国家，产品和公司。

我需要从数据库中选择的背景信息：

PHP脚本生成一个搜索表单，可以在其中选择国家和产品的任意列表。产品按类别分开（我没有在示例数据库中添加类别表，因为在这种情况下不需要该表）。对于第一类，我可以选择是否排除可用性有限的产品。

生成所需结果的效果很好：
它显示了所选国家/地区中可用的所有公司，并且至少具有可用的所选产品之一。结果提供了一个列，该列定义了公司可提供的所选产品数量。
如果用户定义一个或多个类别不应包含可用性有限的产品，则如果公司仅提供有限可用性的产品，则相应类别中的产品将不会计为匹配项。

我对查询的性能感到满意。我的原始数据库包含15个国家/地区，100个公司和150种产品。选择搜索表单中的所有内容将占用MySQL服务器大约两秒钟，这对我来说是可以接受的。

问题：

在生成与尽可能多的产品搜索条件匹配的公司的结果列表之后，我使用PHP遍历这些公司，然后运行另一个SQL查询，该查询将为我提供该公司不提供与搜索条件相对应的产品列表。以下是查询companyId 1的示例，以找出哪些产品在以下情况下不可用


所需产品的productId为2、4和5
产品的国家/地区可用性应至少为countryId 1、2或3中的一种
来自limitedAvailability的产品不应具有categoryId = 2：

SELECT DISTINCT p.name 
FROM `product` p
LEFT JOIN `company_product_country` cpc ON `p`.`productId` = `cpc`.`productId` AND `cpc`.`companyId` = 1 
WHERE NOT EXISTS(
        SELECT * 
        FROM company_product_country cpcTmp 
        WHERE `cpcTmp`.`companyId` = 1 
        AND cpcTmp.val = 'yes'
        AND (
                cpcTmp.limitedAvailability = 0 
                OR p.categoryId NOT IN(2) 
        ) 
        AND cpcTmp.productId = p.productId
)
AND p.`productId` IN (2,4,5) 
AND countryId IN(0,1,2,3);

可以在上面链接的SQLFiddle上找到数据库以及此查询。

该查询会生成正确的结果，但是其性能会随着产品数量的增加而急剧下降。在15个国家/地区搜索150种产品时，我的本地SQL Server每家公司大约需要4秒钟。当遍历100家公司时，这是无法实现的。有什么方法可以改善此查询，例如避免 IN(...)函数包含多达150种产品吗？还是应该将查询分成两个这样：


首先获取不具有国家/地区ID 0并且是 IN所需 countryId的不匹配产品
然后在 countryId = 0和适用的过滤器 limitedAvailability = 0中提取不匹配的产品


？

非常感谢您的帮助！

Answer 1

我建议写这样的查询：

SELECT p.name 
FROM product p
WHERE EXISTS (select 1
              from company_product_country cpc
              where p.productid = cpc.productid and
                    cpc.companyid = 1 and
                    cpc.countryid in (1, 2, 3)
             ) and
      NOT EXISTS (select 1
                  from company_product_country cpcTmp 
                  where cpcTmp.productId = p.productId and
                        cpcTmp.companyId = 1 and
                        cpcTmp.val = 'yes' and
                        cpcTmp.limitedAvailability = 0
                 ) AND
      NOT EXISTS (select 1
                  from company_product_country cpcTmp 
                  where cpcTmp.productId = p.productId and
                        cpcTmp.companyId = 1 and
                        cpcTmp.val = 'yes' and
                        p.categoryId NOT IN (2) 
                 )
      p.`productId` IN (2, 4, 5) ;

然后，您需要以下索引：


product(productid, categoryid, name)
company_product_country(productid, companyid, countryid)
company_product_country(productid, companyid, val, limitedavailability)
company_product_country(productid, companyid, val, category)


注意：这些索引完全“覆盖”了查询，这意味着查询中的所有列都来自索引。对于大多数目的，在 company_product_country上具有单个索引可能就足够了。这三个都可以。