代码之家 › 专栏 › 技术社区 › HerrimanCoder

如何优化相关子查询?

mysql

-1

HerrimanCoder · 技术社区 · 6 年前

我有一个针对MySQL的查询:

SELECT DISTINCT tp.parts_group as PartsGroup, tpf.code as FeatureCode, CONVERT(tpf.market_id, char) as MarketID
FROM jpt_product_feature tpf
INNER JOIN jpt_product tp
ON tpf.product_id = tp.id
INNER JOIN jpt_product_model tpm
ON tp.model_id = tpm.id
JOIN ModelImport mi
ON tpm.Code = mi.ModelCode
WHERE NOT EXISTS (
      SELECT 1 
      FROM FeatureSequence fs
      WHERE tp.parts_group = fs.PartsGroup
      AND tpf.code = fs.FeatureCode
      AND (tpf.market_id = fs.MarketID or tpf.market_id is null)
) 
ORDER BY PartsGroup, FeatureCode, MarketID

它在我的电脑上运行38秒,考虑到跨多个表的大量行,这很好。但在马力较小的虚拟机上运行时,这个查询会运行大约2个小时,然后出现一个错误 FATAL ERROR .

以下是我的索引:

CREATE INDEX idxFeatureSequencePartsGroup ON FeatureSequence (PartsGroup); 
CREATE INDEX idxToyProductPartsGroup ON jpt_product (parts_group); 
CREATE INDEX idxToyProductFeature ON jpt_product_feature (code);
CREATE INDEX idxFeatureSequenceFeatureCode ON FeatureSequence (FeatureCode); 
CREATE INDEX idxToyProductFeatureMarketID ON jpt_product_feature (market_id);
CREATE INDEX idxFeatureSequenceMarketID ON FeatureSequence (MarketID);

1 回复 | 直到 6 年前

Uueerdo 6 年前

相关的查询往往比不相关的查询效率要低得多(如果这样的查询是可能的)。在这种情况下,我会尝试以下方法:

SELECT DISTINCT tp.parts_group as PartsGroup, tpf.code as FeatureCode, CONVERT(tpf.market_id, char) as MarketID
FROM jpt_product_feature tpf
INNER JOIN jpt_product tp ON tpf.product_id = tp.id
INNER JOIN jpt_product_model tpm ON tp.model_id = tpm.id
INNER JOIN ModelImport mi ON tpm.Code = mi.ModelCode
LEFT JOIN (
      SELECT DISTINCT 1 AS matchCheck
         , fs.PartsGroup AS fsPartsGroup
         , fs.FeatureCode AS fsFeatureCode
         , fs.MarketID AS fsMarketID
      FROM FeatureSequence fs
) AS fs ON tp.parts_group = fs.fsPartsGroup
      AND tpf.code = fs.fsFeatureCode
      AND (tpf.market_id = fs.fsMarketID OR tpf.market_id is null)
WHERE fs.matchCheck IS NULL
ORDER BY PartsGroup, FeatureCode, MarketID
;

如果不知道数据分布的细节,就很难判断这是否会更快(在某些情况下,相关子查询是最佳选择);但这是我要做的第一件事。如果FeatureSequence与涉及的其他表相比相对较大,那么相关查询可能仍然更好(针对一个大表的许多小命中与单个大命中)。

推荐文章

Badal Solanki · 如何在MySQL中对字符串进行算术运算?

2 年前

Saleh Mehdiyev · 我想重构我的Laravel代码以遵守SOLID原则

2 年前

hello_programmers · Mysql从其他表输出一列

2 年前

Community wiki · 这个MySQL语句出了什么问题?

2 年前

Community wiki · 优化从同一表中提取的多列的查询

2 年前