我不太喜欢数据库,我对此查询(在MySql数据库上定义)的可能优化有以下疑问:
SELECT MCPS.id AS series_id,
MD_CD.market_details_id AS market_id,
MD_CD.commodity_details_id AS commodity_id,
MD.market_name AS market_name,
MCPS.price_date AS price_date,
MCPS.avg_price AS avg_price,
CU.ISO_4217_cod AS currency,
MU.unit_name AS measure_unit,
CD.commodity_name_en,
CN.commodity_name
FROM Market_Commodity_Price_Series AS MCPS
INNER JOIN MeasureUnit AS MU ON MCPS.measure_unit_id = MU.id
INNER JOIN Currency AS CU ON MCPS.currency_id = CU.id
INNER JOIN MarketDetails_CommodityDetails AS MD_CD ON MCPS.market_commodity_details_id = MD_CD.id
INNER JOIN MarketDetails AS MD ON MD_CD.market_details_id = MD.id
INNER JOIN CommodityDetails AS CD ON MD_CD.commodity_details_id = CD.id
INNER JOIN CommodityName AS CN ON CD.id = CN.commodity_details_id
INNER JOIN Languages AS LN ON CN.language_id = LN.id
WHERE MD.id = 4
AND CD.id = 4
AND LN.id=1
ORDER BY price_date DESC LIMIT 1
它包含大量联接只是因为表中的信息非常规范化。
此查询返回与特定市场中商品的最后价格 (AND CD.id = 4) 相关的信息(**WHERE MD.id = 4)。
因此,我首先检索特定市场中特定商品的所有价格列表(这可能是很多记录),然后我这样做:
ORDER BY price_date DESC LIMIT 1
我认为这种方式的计算成本可能很高,因为查询的第一部分(除了最后一个 ORDER BY 语句之外的整个查询)将检索大量必须排序的记录。
有问题吗?最终,优化此查询的明智策略是什么?(我不知道...
> 健全性检查...MD_CD.commodity_details_id
和CN.commodity_details_id
有什么不同吗? 还是错别字?
摆脱
INNER JOIN Languages AS LN ON CN.language_id = LN.id
...
AND LN.id=1
相反,只需AND CN.language_id = 1
每当您尝试配方时,请运行EXPLAIN SELECT ...
。
似乎大多数事情都以commodity_details_id = 4
为中心。 所以说AND MD_CD.commodity_details_id = 4
,而不是MD.id = 4
和CD.id = 4
.
现在,让我们尝试重新制定它以提高一点效率。 让我们从主过滤器开始 - AND MD_CD.commodity_details_id = 4
,所以让我们说FROM MD_CD JOIN ...
。 然后添加JOINs
,直到我们可以通过price_date DESC LIMIT 1'获得MCPS
.price_date so we can work on
订单:
SELECT very-few-columns
FROM MD_CD
JOIN ... (the minimal set of tables)
JOIN MCPS ON ...
WHERE MD_CD.commodity_details_id = 4
ORDER BY MCPS.price_date DESC LIMIT 1
在继续之前,请调试上面的内容以确保它提供正确的一行。
若要完成查询,有两种方法:
计划 A:使用该"派生表",并在以下位置添加更多 JOIN:
SELECT lots-of-stuff
FROM ( the above query ) AS x
JOIN the rest of the tables
计划 B:对缺少的列使用 SELECT 中的子查询:
SELECT some stuff,
( SELECT commodity_name_en FROM CN
WHERE commodity_details_id = CD.id ) AS commodity_name_en,
etc
FROM ... (as above)
(并注意我对 http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table 中多对多模式的建议)