优化聚合查询

Question

下面是我的聚合查询，耗时1.1秒。此查询有多个联接。用于优化查询的列的索引。

         EXPLAIN SELECT straight_join
          aggsm.tdm_id AS topid,              
          sum(aggsm.m_count) AS mencnt ,
          sum(aggsm.ps_count) AS pscnt,
          sum(aggsm.ns_count) AS ngscnt,
          topdm.topic_name AS topname
         FROM AGG_MENTION AS aggsm 
         JOIN TOPICDM AS topdm  ON aggsm.topicdm_id = topdm.topicdm_id
         JOIN LOCATIONDM AS locdm ON aggsm.locationdm_id = locdm.locationdm_id
         JOIN CITY AS citydm ON locdm.city_id = citydm.city_id
         JOIN STATE AS statedm ON citydm.state_id = statedm.state_id
         WHERE aggsm.cdm_id = 11
         AND aggsm.ei_type IN (1,2,3,4)
         AND aggsm.datedm_id BETWEEN 20130101 AND 20130522   
         AND statedm.country_id IN (1,2,3,4) 
         AND topdm.topic_group_id IN (1,2,3,4,5,6,7)    
         GROUP BY aggsm.topicdm_id
         -- ORDER BY aggsm.topicdm_id DESC,sum(aggsm.m_count) DESC
         LIMIT 0,200000

以下是explain输出：

   1    SIMPLE  aggsm   ref PRIMARY,datedm_id_UNIQUE,agg_sm_locdm_fk_idx,agg_sm_comdm_fk_idx,agg_sm_topdm_fk_idx,agg_sm_datedm_fk_idx,agg_em_indtype_fk_idx,comp_top_dt,l_idx   comp_top_dt 8   const   202129  Using where; Using index
   1    SIMPLE  topdm   eq_ref  PRIMARY,topicdm_id_UNIQUE,topdm_grp_id_idx,id_idx   PRIMARY 8   opinionleaders.aggsm.topicdm_id 1   Using where
   1    SIMPLE  locdm   eq_ref  PRIMARY,city_id_UNIQUE,locationdm_id_UNIQUE,loc_city_fk_idx,id_idx  PRIMARY 8   opinionleaders.aggsm.locationdm_id  1   
   1    SIMPLE  citydm  eq_ref  PRIMARY,city_id_UNIQUE,city_state_fk_idx,id_idx PRIMARY 8   opinionleaders.locdm.city_id    1   
   1    SIMPLE  statedm eq_ref  PRIMARY,state_id_UNIQUE,state_country_fk_idx,id_idx PRIMARY 8   opinionleaders.citydm.state_id  1   Using where

取消对order by子句的注释将导致aggsm表使用'using temporary，using filesort‘

我们如何优化查询或定义索引

Answer 1

1)限制0,200000绝对不是一个好的解决方案，我几乎看不到需要如此大量数据的应用程序，将其拆分成块

2)在ORDER BY子句中，您指定了and sum(aggsm.m_count) -这是一个聚合函数，请尝试将其编址为mencnt (因此ORDER BY aggsm.topicdm_id DESC，mencnt DESC )，但不确定SQL server是否能像您所希望的那样理解它

Answer 2

稍微重新格式化的查询，以提高可读性，并查看关系和关联的列以考虑索引。由于您的Aggsm表是按topicdm_id进行分组的，因此我至少会尝试获取一个包含此表和列的覆盖索引，以便在获得合格的记录之前不需要访问原始页面数据。此外，还应包括where子句的组件。我建议在表格上建立一个索引

table        index
agg_mention  ( cdm_id, ei_type, datedm_id, topicdm_id, locationdm_id )
topicdm      ( topicdm_id, topic_group_id )
state        ( state_id, country_id )
locationdm   ( locationdm_id )
city         ( city_id )

SELECT straight_join
      aggsm.tdm_id AS topid,
      sum(aggsm.m_count) AS mencnt,
      sum(aggsm.ps_count) AS pscnt,
      sum(aggsm.ns_count) AS ngscnt,
      topdm.topic_name AS topname
   FROM 
      AGG_MENTION AS aggsm 
         JOIN TOPICDM AS topdm  
            ON aggsm.topicdm_id = topdm.topicdm_id
            AND topdm.topic_group_id IN (1,2,3,4,5,6,7)    
         JOIN LOCATIONDM AS locdm 
            ON aggsm.locationdm_id = locdm.locationdm_id
            JOIN CITY AS citydm 
               ON locdm.city_id = citydm.city_id
               JOIN STATE AS statedm 
                  ON citydm.state_id = statedm.state_id
                  AND statedm.country_id IN (1,2,3,4) 
   WHERE 
          aggsm.cdm_id = 11
      AND aggsm.ei_type IN (1,2,3,4)
      AND aggsm.datedm_id BETWEEN 20130101 AND 20130522   
   GROUP BY 
      aggsm.topicdm_id
   ORDER BY 
      aggsm.topicdm_id DESC,
      sum(aggsm.m_count) DESC
   LIMIT 
      0,200000