mysql - 无限滚动的myBatis偏移位置设置问题

Question

我正在构建一个类似 Quora 的应用程序。后端使用spring boot，mybatis连接一个mysql数据库。当用户打开网站时，后端返回前 10 个问题。如果用户单击“获取更多”按钮，后端应返回接下来的 10 个问题。

mybatis代码为

<mapper namespace="com.quora.dao.QuestionDAO">
    <sql id="table">question</sql>
    <sql id="selectFields">id, title, content, comment_count,created_date,user_id
    </sql>
    <select id="selectLatestQuestions" resultType="com.quora.model.Question">
        SELECT
        <include refid="selectFields"/>
        FROM
        <include refid="table"/>

        <if test="userId != 0">
            WHERE user_id = #{userId}
        </if>
        ORDER BY id DESC
        LIMIT #{offset},#{limit}
    </select>
</mapper>

目前我的逻辑是第一次#{offset}为0，第二次#{offset}为10。但是我发现当表更新频繁时，这个逻辑就不对了。如果表中插入了新行，用户可能会得到重复的数据。如何根据前端显示的最后一个问题 ID 设置#{offset}？比如前端最后一个问题id是10，那么#{offset}应该是问题id 10的行号。

谁能给我一些建议？

谢谢，彼得

Answer 1

一般的想法是根本不使用 OFFSET 而是进行过滤。如果您可以定义消息的顺序，以便在插入新消息时它不会更改(例如，您增量生成 ID 并按 id ASC 对消息进行排序)那么很容易:

SELECT id, some_other_field, yet_another_field
FROM question
<if test="last_seen_question_id != null">
    WHERE id > #{last_seen_question_id}
</if>
ORDER BY id ASC
LIMIT #{limit}

然后客户端应该使用最后看到的问题 id 并在它想要获取下一页时传递它。

根据您的查询 (ORDER BY id DESC)，您似乎希望在顶部看到最新的问题。这有点问题，因为新插入的问题往往会排在最前面。

如果您可以先在下一页上回答新问题，然后再回答旧问题，您可以这样做:

<!-- This condition is needed to avoid duplication when the first page is fetched
     and we haven't seen any question yet.
     In this case we just get up to limit of the last questions.
-->
<if test="newest_seen_question_id != null">
SELECT * FROM (
  -- select all questions that were created _after_
  -- the last seen max question id
  -- client should maintain this value as in get the 
  -- largest question id for every page and compare it
  -- with the current known max id. And client should
  -- do it for the whole duration of the paging
  -- and pass it to the follow up queries
  SELECT id, some_other_field, yet_another_field
  FROM question
  WHERE id > #{newest_seen_question_id}
  -- note that here we do sorting in the reverse order
  -- basically expanding the set of records that was returned
  -- in the direction of the future
  ORDER BY id ASC
  LIMIT #{limit}
  UNION ALL
</if>
  -- select all questions that were created _before_
  -- the last seen min question id.
  -- client should maintain this value for the whole
  -- duration of the paging
  -- and pass it to the follow up queries      
  SELECT id, some_other_field, yet_another_field
  FROM question
  <if test="oldest_seen_question_id != null">
    WHERE id < #{oldest_seen_question_id}
  </if>
  ORDER BY id DESC
  LIMIT #{limit}
<if test="newest_seen_question_id != null">
) AS q
ORDER BY id DESC
LIMIT #{limit}
</if>

另一个好处是这种不使用OFFSET 的分页方法是much better从性能的角度来看。