如何优化查询以返回大量记录，然后按日期字段排序并仅返回最新记录？ [英] How can I optimize a query that returns a lot of records, then order by a date field and return only the latest one?

问题描述

我不是很喜欢数据库，并且对此查询的可能优化（在 MySql 数据库中定义）有以下疑问：

I am not so into database and I have the following doubt about the possible optimization of this query (defined on a MySql database):

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

它包含大量联接，仅是因为表中的信息已非常规范化。

It contains a lot of join only because the information in the tables are very normalized.

此查询返回与最后价格有关的信息商品（ AND CD.id = 4 ）在特定市场（** WHERE MD.id = 4）中。

This query returns the information related the last price of a commodity (AND CD.id = 4) in a specific market (**WHERE MD.id = 4).

我是首先检索特定市场中特定商品的所有价格清单（可能有很多记录），然后执行以下操作：

So I am first retrieving the list of all prices of a specific commodity in a specific market (that can be a lot of records) and then I do:

ORDER BY price_date DESC LIMIT 1

我认为这种方法在计算上可能会很昂贵，因为查询的第一部分（除最后一个 ORDER BY 语句外的整个查询将检索很多记录，然后

I think that maybe this way can be computationally expensive because the first part of the query (the entire query except the last ORDER BY statment will retrieve a lot of records that then have to be ordered.

有问题吗？最终，什么是优化此查询的明智策略？ （我不知道...）

Is it a problem? Eventually what could be a smart strategy to optimize this query? (I have no idea...)

推荐答案

健全性检查...是 MD_CD.commodity_details_id 和 CN.commodity_details_id 不同？还是错字？

Sanity check... are MD_CD.commodity_details_id and CN.commodity_details_id different? Or is it a typo?

摆脱

INNER JOIN Languages AS LN ON CN.language_id  = LN.id
 ...
AND LN.id=1

相反，只需使用和CN.language_id = 1

运行解释选择... 每次尝试制定公式。

Run EXPLAIN SELECT ... whenever you try a formulation.

似乎大多数事情都集中在 commodity_details_id周围= 4 。因此，说 AND MD_CD.commodity_details_id = 4 ，而不是 MD.id = 4 和 CD。 id = 4 。

It seems that most things are centered around commodity_details_id = 4. So say AND MD_CD.commodity_details_id = 4, not MD.id = 4 and CD.id = 4.

现在，让我们尝试将其重新构造以提高效率。让我们从主过滤器开始- AND MD_CD.commodity_details_id = 4 ，所以说 FROM MD_CD JOIN ... 。然后添加 JOINs ，直到获得 MCPS .price_date 以便我们继续进行工作 ORDER BY price_date DESC LIMIT 1`：

Now, let's try to reformulate it for a little bit of efficiency. Let's start with the main filter -- AND MD_CD.commodity_details_id = 4, so let's say FROM MD_CD JOIN .... Then add JOINs until we can get to MCPS.price_dateso we can work onORDER BY price_date DESC LIMIT 1`:

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

在进一步操作之前，请调试上面的内容以确保提供正确的一行。

Before going further, debug that above to make sure it is providing the correct one row.

要完成查询，有两种方法：

To finish out the query, there are two approaches:

方案A：使用派生表并在其上添加更多JOIN ：

Plan A: Use that is a 'derived table' and add more JOINs on:

SELECT lots-of-stuff
    FROM ( the above query ) AS x
    JOIN the rest of the tables

方案B：在SELECT子查询中使用缺少的列：

Plan B: Use subqueries in the SELECT for the missing columns:

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 ）

这篇关于如何优化查询以返回大量记录，然后按日期字段排序并仅返回最新记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！