如何使用GroupBy MySQL查询来优化计数?[英] How can I optimise COUNT with GROUPBY mysql query?

问题描述

i在 eus 表中具有5m 记录, 121 记录 es 表中的记录.我正在做一个左连接,但是 count 查询使我的查询非常慢.我该如何优化?

public static function getAllActiveEvaluationSymptomsWithNameForDataTable(){
    $queryBuilder = new Builder();

    $queryBuilder
        ->from(array('es' =>  static::class))
        ->leftJoin('EvaluationUserSymptom',  'es.id = eus.eb_evaluation_symptom_id','eus')
        ->columns('es.id, es.title, COUNT(eus.eb_evaluation_symptom_id) AS counts')
        ->groupBy('eus.eb_evaluation_symptom_id')
        ->where('es.is_active = 1');

    return  $queryBuilder;
}

RAW查询与解释:

EXPLAIN 
SELECT  es.id AS id, es.title AS title,
        COUNT(eus.eb_evaluation_symptom_id) AS counts,
        eus.date_created AS date_created
    FROM  eb_evaluation_symptom AS es
    LEFT JOIN  eb_evaluation_user_symptom AS eus
           ON es.id = eus.eb_evaluation_symptom_id
    WHERE  es.is_active = 1
    GROUP BY  eus.eb_evaluation_symptom_id;

解释的输出:

在此处输入图像说明

解释视觉视图:

在此处输入图像说明

这个全表扫描计数正在使问题.

注意:所有连接和必要的列字段都有适当的索引.

推荐答案

相关的子查询可以是一种快速方法:

SELECT es.id, es.title,
      (select count(*)
       from eb_evaluation_user_symptom eus
       where es.id = eus.eb_evaluation_symptom_id
      ) as cnt
FROM eb_evaluation_symptom es  
WHERE es.is_active = 1 ;

对于性能,您需要在eb_evaluation_user_symptom(eb_evaluation_symptom_id)上进行索引.

eb_evaluation_symptom上的索引不会有太大帮助,因为该桌子很小.

其他推荐答案

在加入之前尝试聚集:

SELECT es.id AS id, es.title AS title
  , coalesce(eus.counts, 0) as counts
  , eus.date_created
FROM eb_evaluation_symptom AS es 
LEFT JOIN 
 ( select eb_evaluation_symptom_id  
     , COUNT(*) AS counts
     , min(date_created) AS date_created -- or MAX?
   from eb_evaluation_user_symptom 
   GROUP BY eb_evaluation_symptom_id
 ) AS eus 
ON es.id = eus.eb_evaluation_symptom_id 
WHERE es.is_active = 1 ;

其他推荐答案

您是否有这些覆盖索引,在这些订单中?

INDEX(eb_evaluation_symptom_id, date_created)
INDEX(is_active, id, title)

"覆盖"索引有时 速度加倍.

本文地址:https://www.itbaoku.cn/post/1739798.html