性能较差的SQL语句，尽管使用了索引

Question

我的应用程序中有一条简单的SQL语句：

  SELECT SQL_NO_CACHE key_event_id, MAX(report_ts) AS max_ts
  FROM `key_event_reports`
  WHERE report_model_id = 2 
  GROUP BY key_event_id;

key_event_reports表是中等大小(约17M行)，这是表定义：

CREATE TABLE IF NOT EXISTS `key_event_reports` (
  `key_event_report_id` int(20) NOT NULL AUTO_INCREMENT,
  `report_model_id` int(5) NOT NULL,
  `key_event_id` int(5) NOT NULL,
  `title_id` int(15) NOT NULL,
  `report_ts` datetime NOT NULL,
  `report_time` time NOT NULL,
  `total` int(7) NOT NULL DEFAULT '0',
  `pos` int(7) NOT NULL DEFAULT '0',
  `neg` int(7) NOT NULL DEFAULT '0',
  `smooth_total` float NOT NULL DEFAULT '0',
  `smooth_pos` float NOT NULL DEFAULT '0',
  `smooth_neg` float NOT NULL DEFAULT '0',
  `buzz` float NOT NULL DEFAULT '0',
  `sentiment` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`key_event_report_id`),
  UNIQUE KEY `key_event_id_4` (`key_event_id`,`report_model_id`,`title_id`,`report_ts`),
  KEY `report_model_id` (`key_event_id`,`report_time`),
  KEY `report_model_id_2` (`report_model_id`,`key_event_id`,`report_ts`),
  KEY `key_event_id` (`key_event_id`,`report_model_id`,`report_time`,`title_id`,`smooth_total`),
  KEY `key_event_id_3` (`key_event_id`,`report_model_id`,`report_time`,`title_id`,`smooth_pos`),
  KEY `key_event_id_2` (`key_event_id`,`report_model_id`,`report_time`,`title_id`,`smooth_neg`),
  KEY `get_latest_report` (`report_model_id`,`report_ts`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16967636 ;

report_model_id始终是2(数据库中还没有其他型号，但这种情况很快就会改变)，并且每10分钟报告10种不同的key_events。

这个查询在没有缓存的情况下需要很长的时间(大约20秒)。当上面的查询在更大的语句中用作子查询时，问题会变得更糟：

SET @report_model_id = 2;
SET @message_id = ?;
SET @title_id = ?
SET @min_score = 5;

SET @min_message_id = ( 
    SELECT MIN(message_id)  
    FROM `messages`  
    WHERE msg_time > DATE_SUB(NOW(), INTERVAL 20 MINUTE) 
); 

SELECT 
    ke.key_event_id AS key_event_id, 
    COALESCE(kermmid.message_id, MIN(mhke.message_id)) AS max_message_id, 
    ker_max.max_ts AS last_report_ts 
FROM `key_events` ke
LEFT JOIN (
    SELECT key_event_id, MAX(report_ts) AS max_ts
    FROM `key_event_reports`
    WHERE report_model_id = 2 
    GROUP BY key_event_id
) ker_max
    ON ( ker_max.key_event_id = ke.key_event_id )
    LEFT JOIN `key_event_reports` ker 
        ON (
            ker.key_event_id = ke.key_event_id 
            AND ker.report_model_id = @report_model_id 
            AND ker.title_id = @title_id 
            AND ker.report_ts = @actcurrent 
        ) 
    LEFT JOIN `key_event_report_max_message_ids` kermmid 
        ON (
            kermmid.key_event_id = ker.key_event_id 
            AND kermmid.report_model_id = ker.report_model_id 
            AND kermmid.report_ts = ker.report_ts 
        ) 
    LEFT JOIN `messages_has_key_events` mhke 
        ON ( 
            mhke.key_event_id = ke.key_event_id 
            AND mhke.title_id = @title_id 
            AND mhke.message_id > @min_message_id 
            AND mhke.message_id < @message_id 
            AND mhke.score > @min_score 
        ) 
    GROUP BY 
        ke.key_event_id;

如果我在这里使用子查询，执行时间也从~50ms增加到>20s。

出现这种情况的原因是什么?我如何优化我的语句或DB结构？

Answer 1

尝试在(report_model_id,key_event_id,report_ts)上添加索引，并将report_model_id添加到group by。这应该允许它使用group by optimization。

SELECT key_event_id, MAX(report_ts) AS max_ts
FROM `key_event_reports`
WHERE report_model_id = 2 
GROUP BY report_model_id, key_event_id

我仍然在努力想出一种方法来处理剩下的查询...inner需要是左连接还是内部连接就可以？

编辑:我忽略了一个事实，那就是你已经有了索引，所以你只需要添加要分组的字段。

Answer 2

对于“为什么”，我猜测是MySQL查询缓存。

MySQL会在某些情况下缓存查询结果，以加快重复查询的速度。如果数据发生变化，它将不得不重新运行查询。我不知道它是如何处理子查询的。

Answer 3

您的查询似乎已经在使用此索引。

`report_model_id_2` (`report_model_id`,`key_event_id`,`report_ts`)

它包含查询所需的所有信息，因此MySQL能够通过对此索引而不是整个表执行范围扫描来满足查询。好消息是，您已经很好地优化了查询。这也是个坏消息。

创建一个汇总表，并在MySQL数据库中设置一个事件，以便偶尔根据详细数据更新汇总表，这样做有意义吗？只有当这个查询的结果运行稍微慢一点，对您的应用程序来说不是灾难性的时，这才是正确的。

如果必须使此信息与明细表完全同步，则还可以装配一个触发器来更新汇总表。