MySQL是有办法的。如何加快查询速度

Question

我做了一个查询，给出了我需要的结果，但是非常慢。

DB : Mysql

问题是:非常慢。

问题是:有什么方法可以加快查询速度吗？

提前感谢！

SELECT  Count(*) AS totalCount
    FROM  
      ( SELECT  Max(issue_cnt) AS max_count, deal_id, issue_date, penalty_point,
                sale_volume, penalty_classify_code
            FROM  
              ( SELECT  penalty.deal_id, Count(*) AS issue_cnt,
                        Max(penalty_point) AS max_point,
                        penalty.issue_date, penalty.penalty_point,
                        penalty.sale_volume,
                        penalty.penalty_classify_code
                    FROM  bizinfo.tb_pc_sale_penalty penalty, 
                      ( SELECT  Max(penalty_point) AS max_point,
                                penalty.deal_id, penalty.issue_date,
                                penalty.sale_volume,
                                penalty.penalty_classify_code
                            FROM  bizinfo.tb_pc_sale_penalty penalty,
                                  bizinfo.tb_deal_info deal
                            WHERE  penalty.deal_id = deal.deal_id
                              AND  penalty.issue_date >= '2016-01-01'
                              AND  penalty.issue_date < '2016-03-16'
                              AND  Date_format(penalty.registe_date, '%Y-%m-%d') <=
                                   Date_format(Now(), '%Y-%m-%d')
                            GROUP BY  deal_id , issue_date
                      ) terms
                    WHERE  penalty.deal_id = terms.deal_id
                      AND  penalty.issue_date = terms.issue_date
                      AND  penalty.sale_volume = terms.sale_volume
                      AND  penalty.penalty_point = terms.max_point
                      AND  penalty.issue_date >= '2016-01-01'
                      AND  penalty.issue_date < '2016-03-16'
                      AND  Date_format(penalty.registe_date, '%Y-%m-%d') <=
                           Date_format(Now(), '%Y-%m-%d' )
                    GROUP BY  deal_id , penalty.sale_volume , issue_date ,
                              penalty.penalty_classify_code
              ) choice
            GROUP BY  deal_id , issue_date
      ) selection
    left join  
      ( SELECT  1 AS cnt, penalty_classify_code, penalty_point,
                process_condition,
                deal_id, issue_date, registe_date, company_id
            FROM  bizinfo.tb_pc_penalty_point_finalize
            WHERE  issue_date >= '2016-01-01'
              AND  issue_date < '2016-03-16'
      ) finalize ON selection.deal_id = finalize.deal_id
      AND  selection.issue_date = finalize.issue_date
    left join  
      ( SELECT  Ifnull(SUM(penalty_point), 0) AS point, company_id
            FROM  bizinfo.tb_pc_penalty_point_finalize
            WHERE  process_condition = 1
              AND  penalty_point > 0
              AND  registe_date >= Date_sub(Curdate(), interval 89 day)
              AND  registe_date < Date_format(Now(), '%Y-%m-%d')
            GROUP BY  company_id
      ) cumulate ON finalize.company_id = cumulate.company_id
    left join  
      ( SELECT  Count(*) AS cnt, penalty.deal_id, penalty.issue_date
            FROM  bizinfo.tb_pc_sale_penalty penalty, bizinfo.tb_deal_info deal
            WHERE  deal.deal_id = penalty.deal_id
              AND  penalty.issue_date >= '2016-01-01'
              AND  penalty.issue_date < '2016-03-16'
              AND  penalty_point < 0
            GROUP BY  deal_id , issue_date
      ) suspension ON selection.deal_id = suspension.deal_id
      AND  selection.issue_date = suspension.issue_date
    left join  
      ( SELECT  penalty.deal_id, penalty.issue_date, Count(*) AS all_issue_count,
                Ifnull(exp.explain_cnt, 0) AS explain_count,
                SUM(penalty.penalty_point) AS penalty_cumulative_point,
                penalty_point AS penalty_allocate_point, deal.company_id,
                deal.md_id, deal.team_id, deal.main_name
            FROM  
              ( SELECT  deal_id,
                        CASE WHEN penalty_point > 0
                             THEN penalty_point
                             ELSE 0 END AS penalty_point,
                        issue_date
                    FROM  bizinfo.tb_pc_sale_penalty
              ) penalty
            left join  
              ( SELECT  1 AS explain_cnt, exp.deal_id, exp.issue_date
                    FROM  bizinfo.tb_pc_penalty_explain exp
                    GROUP BY  exp.deal_id , exp.issue_date
              ) exp ON exp.deal_id = penalty.deal_id
              AND  exp.issue_date = penalty.issue_date, bizinfo.tb_deal_info deal
            WHERE  penalty.deal_id = deal.deal_id
            GROUP BY  penalty.deal_id , penalty.issue_date , deal.company_id
      ) alltype ON selection.deal_id = alltype.deal_id
      AND  selection.issue_date = alltype.issue_date, bizinfo.tb_deal_info deal,
        bizinfo.tb_partner_member member, bizinfo.tb_partner_member member2,
        bizinfo.tb_pc_communication_master_code master,
        bizinfo.tb_company_info company
    WHERE  selection.deal_id = deal.deal_id
      AND  deal.md_id = member.user_id
      AND  deal.team_id = member2.user_seq
      AND  master.group_cd = 'P10080'
      AND  master.value_cd NOT IN ('p99')
      AND  master.value_cd = selection.penalty_classify_code
      AND  alltype.company_id = company.company_id;

Answer 1

使用FROM a JOIN b ON ...语法而不是FROM a,b WHERE ...。

FROM ( SELECT ... ) JOIN ( SELECT ... ) ON ... (或旧格式)的优化效果很差。尽量避免。

Date_format(Now(), '%Y-%m-%d') -> CURRENT_DATE().

这是更简单和更优化的；我怀疑它给出了同样的‘意图’：

Date_format(penalty.registe_date, '%Y-%m-%d') <=
Date_format(Now(), '%Y-%m-%d' )
-->
penalty.registe_date < NOW()

请提供SHOW CREATE TABLE，这样我们就可以判断索引的效用并理解数据类型。(例如: issue_date是DATE还是DATETIME?)请提供EXPLAIN SELECT，这样我们就可以看到查询是如何执行的。

考虑一下JOIN是否适合于LEFT JOIN而不是LEFT JOIN。

您运行的是什么版本的MySQL？

penalty可能会从INDEX(deal_id, issue_date, sale_volume, penalty_point, registe_date)中受益--特别是使用registe_date last。

在早期的一个子查询中，为什么包括deal？(... FROM bizinfo.tb_pc_sale_penalty penalty, bizinfo.tb_deal_info deal ...)没有deal，INDEX(deal_id, issue_date)将是一个非常好的索引。如果您包括deal，我不知道该推荐什么。

tb_pc_penalty_point_finalize需要INDEX(issue_date)。

left join ( SELECT Ifnull(SUM(penalty_point), 0) AS point ...可以完全删除！这是额外的工作，但您不使用它生成的point。而且，由于它是LEFT，没有人关心SELECT是否找到了任何东西。也许其他LEFT JOINs也是无用的？

master可能需要INDEX(group_cd)；selection可能需要INDEX(penalty_classify_code)。

Answer 2

是的，您可以将索引应用于中使用的列，其中子句例如在selection.deal_id、deal.deal_id、deal.md_id和member.user_id上添加索引。但是在一个表上有太多的索引是不好的。

作为一般规则，您应该对所有主键(在这方面您没有选择)、所有外键和其他通常用于获取行的字段都有索引。

Answer 3

在查询中涉及的列上创建索引。

FLUSH TABLES

RESET QUERY CACHE

上面会释放一些内存空间。祝好运