在最大n个组子查询中使用范围筛选器的性能非常差。

Question

我有一个相当简单的查询(在greatest-n-per-group情况下用作子查询)。state_id是主键-其他的东西都是非唯一的.

SELECT max(states.state_id) AS max_state_id
FROM states
WHERE states.created >= '2017-06-10 21:53:38.977455'
  AND states.created < '2017-06-26 07:00:00'
GROUP BY states.entity_id;

问题是，这个查询非常慢，而且我不相信任何顺序的多色索引都能解决它的编写方式。它总是以一个using where; using index; using temporary; using filesort结束

如果不清楚的话:我们在这里要做的是在两个时间戳之间为每个entity_id获取最新的entity_id。

相反，我们可以做一个max(states.created) (而不是max(states.state_id))，这可能更好，但是我没有外部查询要加入的state_id。

下面是完整的查询，包括外部部分，以提供完整的上下文：

SELECT states.state_id AS states_state_id, states.domain AS states_domain, states.entity_id AS states_entity_id, states.state AS states_state, states.attributes AS states_attributes, states.event_id AS states_event_id, states.last_changed AS states_last_changed, states.last_updated AS states_last_updated, states.created AS states_created
FROM states INNER JOIN (
    SELECT max(states.state_id) AS max_state_id
    FROM states
    WHERE states.created >= '2017-06-10 21:53:38.977455' AND states.created < '2017-06-26 07:00:00' GROUP BY states.entity_id
) AS anon_1 ON states.state_id = anon_1.max_state_id;

当然，必须有一种方法来重写这个查询，以便允许索引进行松散的索引扫描……

Answer 1

首先，对于这个查询：

SELECT max(s.state_id) AS max_state_id
FROM states s
WHERE s.created >= '2017-06-10 21:53:38.977455' AND
      s.created < '2017-06-26 07:00:00'
GROUP BY s.entity_id;

我想确定您在states(created, entity_d, state_id)上有一个索引。看看能不能帮上忙。

第二，考虑将查询重写为：

SELECT max(s.state_id) AS max_state_id
FROM states s
WHERE s.created >= '2017-06-10 21:53:38.977455' AND
      s.created < '2017-06-26 07:00:00' AND
      s.state_id = (SELECT MAX(s2.state_id)
                    FROM states s2
                    WHERE s2.entity_id = s.entity_id AND
                          s2.created >= '2017-06-10 21:53:38.977455' AND
                          s2.created < '2017-06-26 07:00:00'
                   );

对于此查询，需要在：states(entity_id, created, state_id)和states(created, entity_id, state_id)上建立索引(索引事项中列的顺序)。