mysql - 查询优化/修复

Question

前一周我遇到了类似的问题，查询需要永远运行。在编写此查询时，我尝试应用从其他查询中学到的一些知识，但执行起来需要很长时间。

运行查询的两个单独部分时，每个部分需要 2 分钟才能完成，这是可以接受的，但越快越好。

这是我的查询:

SELECT
    x.entityCode    'Entity Code'
,   x.nnd_volume    'Latest Brand Volume'
,   maxDate         'Last Action Date'
,   x.Brand         'Brand Description'
,   (SELECT 
            ROUND(((SELECT (MAX(A.line_value * B.pack_volume)) 
                         FROM master_data.brand_master B 
                         WHERE A.brand_code = B.brand_code
                           AND A.brand_code = B.brand_code) 
                  / --THIS IS NOT A BUG, JUST TRYING TO CLEARLY INDICATE THE TWO SELECTS BEING DIVIDED AND CALCULATED
                   (SELECT C.case_volume 
                         FROM master_data.brand_master C 
                         WHERE A.brand_code = C.brand_code
                           AND A.brand_code = C.brand_code)),2) 'brand_volume' 
       FROM am_lines.brand_module A
       WHERE A.action_date BETWEEN DATE(DATE_SUB(CURDATE(), INTERVAL 28 DAY))
         AND DATE(DATE_SUB(CURDATE(), INTERVAL 0 DAY))
         AND A.entity_code = x.entityCode
         AND A.brand_code = x.brandCode
       GROUP BY A.entity_code, A.brand
    ) 'Max Brand (28 Days)'
FROM
    (SELECT
        BBM.entity_code AS entityCode    
    ,   MAX(action_date) AS maxDate
    ,   brand AS Brand
    ,   brand_code AS brandCode
    ,   ROUND(((SELECT (BBM.line_value * B.pack_volume) 
        FROM master_data.brand_master B 
        WHERE BBM.brand_code = B.brand_code AND BBM.brand_code = B.brand_code) 
    / --THIS IS NOT A BUG, JUST TRYING TO CLEARLY INDICATE THE TWO SELECTS BEING DIVIDED AND CALCULATED
    (SELECT C.box_volume 
        FROM master_data.brand_master C 
        WHERE BBM.brand_code = C.brand_code AND BBM.brand_code = C.brand_code)),2) 'brand_volume' 
    FROM 
        am_lines.brand_module BBM
    WHERE 
        line_field_id IN (3154, 3655)
    AND action_date BETWEEN DATE(DATE_SUB(CURDATE(), INTERVAL 28 DAY)) AND DATE(DATE_SUB(CURDATE(), INTERVAL 0 DAY))    
    GROUP BY
    --  action_date
        entity_code
    ,   brand_code) AS X
GROUP BY
    x.entityCode
,   x.brand;    

样本数据(包装体积和盒子体积只是一个引用表，指示包装/盒子中的总单位并用于计算，因此我没有将其包含在样本数据中):

    entity_code | action_date| brand    | line_value
    ------------+------------+----------+----------- 
    108792      |2016-12-07  |brand 001 | 25
    108793      |2016-12-08  |brand 002 | 36
    108795      |2016-12-06  |brand 003 | 10
    108796      |2016-12-05  |brand 004 | 55
    108795      |2016-12-13  |brand 001 |  5
    108792      |2016-12-14  |brand 003 |  2 
    108793      |2016-12-14  |brand 005 | 15 
    108796      |2016-12-16  |brand 006 | 25

当前有效的成果:

• 每个选择查询都会在几分钟内运行(最多 2 分钟)并给出期望的结果。
• action_date 和 line_field_id 已编入索引。
• 由于某种未知原因，向entity_code 添加索引会降低表的速度。

当前遇到的问题/期望的结果:

• 整个查询运行一次需要永远运行，超过 2小时。
• 每个实体都会销售/持有多个品牌的库存。我需要确定 28 天周期以及一个月内的最大股票持有量(所需集合中的最大持有 28 天列)。
• 我还需要显示最近一周的股票持有量(所需集合中的品牌持有栏)。

期望的结果集 View :

    entity_code | Last Date  | brand holding    | Max Holding (28 Days) |Brand
    ------------+------------+------------------+-----------------------+----------+
    108792      |2016-12-27  |10                | 25                    |Brand 001
    108792      |2016-12-27  |36                | 36                    |Brand 002
    108792      |2016-12-27  |5                 | 10                    |Brand 003
    108792      |2016-12-27  |25                | 55                    |Brand 004
    108792      |2016-12-27  |4                 |  5                    |Brand 005
    108783      |2016-12-15  |80                | 80                    |Brand 001
    108783      |2016-12-15  |36                | 41                    |Brand 002
    108783      |2016-12-15  |12                | 12                    |Brand 003
    108783      |2016-12-15  |8                 | 11                    |Brand 004
    108783      |2016-12-15  |20                | 90                    |Brand 005        

查询计划(store_code 与实体代码相同，我只是为了帖子的目的将其重命名):

我已将索引调整为 action_date 和 line_field_id。

显示创建表图像:

Answer 1

让我给你一些提示。如果还不够，让我们重新开始。

• 您拥有的许多子查询都是不需要的；使用JOIN会更好。
• CURDATE没有时间，所以不需要转换为DATE:DATE(DATE_SUB(CURDATE(), INTERVAL 28 DAY)) --> (CURDATE() - 间隔 28 天)
• 请使用某种缩进来帮助区分子查询。
• 什么保证 (SELECT ROUND... 仅返回一行？也许 GROUP BY 是多余的？
• 有时您使用brand，有时使用brand_code；这是一个“错误”吗？
• 添加以下索引；我不知道哪个最好。

索引:

INDEX(action_date, entity_code, brand_code)
INDEX(line_field_id, action_date)
INDEX(entity_code, brand, action_date)

附录

• 请勿将 VARCHAR 用于需要计算的值，例如 MAX(A.line_value * B.pack_volume)。
• 不要盲目地在 VARCHAR 上使用 (255)，使用“合理”的最大长度。
• 在任何 InnoDB 表上都有一个主键。
• 不匹配的类型，例如 line_field_id IN (3154, 3655) 与 VARCHAR(900) 会妨碍合理的优化，并可能产生“错误”结果。

(至少在数据类型被“修复”之前我不会进一步考虑这个问题。)