过滤后密钥集分页性能的改进

Question

我有一张这样的桌子：

Post:
    id int
    time int
    title varchar(100)

我想根据标题过滤表，然后分页结果。对于分页，我使用的是使用where time < last_post_time or (time = last_post_time and id > last_post_id)的键集分页。表上有两个索引，一个用于搜索，另一个用于分页：

• 索引on (Title)
• 索引on (time desc，id)

表的SQL：

create table test ( 
    id int, 
    time int, 
    title varchar(100), 
    primary key (id) , 
    index idx_title (title), 
    index idx_time_id (time desc, id) 
);

SQL查询：

explain select id, time, title 
from test
where title = 'some title'
and time < 10 or (time = 10 and id > 5)
order by time desc, id, title
limit 10

输出显示它正在使用idx_time_id，这是有意义的。我假设它首先根据时间和id进行过滤，然后对过滤后的标题数据进行一次全面扫描(因为索引不能用于标题，因为它已经过滤过了)

有没有办法让它避免对整个过滤过的数据进行扫描？因为时间和id条件可能导致返回整个表，这意味着它将搜索整个表以查找标题。

如何优化此查询以进行筛选，然后对其进行分页？

Answer 1

假设查询实际上更像以下内容：

select  id, time, title
    from  test
    where  title = 'some title'
      and  time < 10
      or  (time = 10
              and  id < 5    -- getting older
          )
    order by  time desc, id DESC  -- both DESC, no need for `title`
    limit  10 

按此顺序，该表需要INDEX(title, time, id)。你也可以放弃INDEX(title)。

这使您能够“记住您停止的位置”，并继续使用新的LIMIT 10而不带out OFFEST。(更多讨论： )