Hello everyone, I'm programmer Fishskin. Recently, my oldest brother, Little Abba, was on summer vacation and wanted to find something to do, so he came to me and asked: Lao Chai, I want to do a practice project, is there any good advice?
I said: if you are practicing for a project, just do one that interests you, and add whatever features you want, it will be more comfortable to do it.
Little Abba: Emm, there are so many I'm interested in, any recommendations?
I said: Then think of a website or APP that you use often and pick one that is relatively familiar with your business processes.
Little Abba thought for a moment and tapped his head: yes, I use WeChat every day, so I'll make a WeChat! Maybe after that everyone is chatting with the software I made?
When I heard it, I couldn't help but secretly marvel, I didn't expect the young man to be young and ambitious!
I said: the idea is good, but want to do a WeChat such IM (Instant Messaging) project, but not so simple, do you have any realization of the idea? Do you have any ideas on how to realize it?
Little Abba: The core function of WeChat is to send and receive messages. I can save the messages sent by user A into the database, and when user B enters the chat interface, he can just query the messages sent to him from the database.
As soon as I heard this answer, I knew it was unlikely that Little Abba would want to make WeChat at his current level.
I ask: Emm, forget about user experience and performance for a moment, let's just implement the basic functionality, how would you allow users to view their history of messages?
Little Abba thought for a moment, then the corners of his mouth rose slightly, revealing a wry smile: did you think I would say that I would check out all the history messages at once? It's a pity ah Lao Chai, you think me too naive, the user may have hundreds or thousands of history messages, the full load will be very slow, so I will inevitably use thetab window (in a web browser etc) to inquire!
I said: okay, so how are you going to paginate it?
Little Abba: This is really not difficult to me, these years I practiced hard to add, delete, change and check, paging write very smooth! Present me with a pen and paper, and let's see me handwrite SQL for you:
select * from message
where user = 'Fish skin'
limit 0, 20;
I said: Emm, old brother, listen to my advice, let's not think of doing WeChat first, first realize a message management system.
Little Abba: How? Is my SQL not fine?
This is actually a classic scenario question: how to implement a drop-down paging load of history messages in an instant messaging project?
Fishskin explains it below.
How to implement dropdown paging loading?
business scenario
Typically in instant messaging projects (like chat rooms), we use drop-down paging to allow users to load a history of messages.
Unlike standard pagination, which only displays data from the current page at a time, dropdown pagination loads areincremental loading The pattern, each time the drop-down will request to load a small part of the new data, and put it into the list of loaded data, so as to form the effect of infinite scrolling, to ensure that the user experience is smooth.
For example, if the user has 10 message records, paged by 5, only the latest 5 messages will be loaded when the user first enters the room:
When you scroll down, the 6th - 10th message of the history will be loaded:
After understanding the business scenario, then look at the implementation options and why it is not recommended to use traditional paging to achieve dropdown loading.
Problems with traditional paging
In traditional paging, data is usuallyBased on page number or offset for loading. If data changes during the paging process, such as inserting new data and deleting old data, users may see inconsistencies in the paged data, causing them to miss or duplicate certain data.
As an example, for an instant messaging program, users may receive new messages continuously. If you follow the traditional paging based on offset loading, the first page is already loaded with rows 1 - 5, and the second page of data to be queried is rows 6 - 10 (corresponding to the SQL statement limit 5, 5), the database record is as follows:
As a result, before querying the second page, suddenly the user receives 5 new messages, and the database record becomes the following. The original first page becomes the current second page!
This results in the second page of data queried, which happens to be the first page of data that has been queried before, causing the message to be loaded repeatedly. So this method is not recommended.
Recommended Solution - Cursor Paging
To solve this problem, you can use cursor paging. Use a cursor to keep track of the paging location, rather than based on page numbers, and load the data for each request starting with the cursor from the previous request.
Generally we choose a unique identifier (primary key), timestamp, or field with sorting capability of a data record as a cursor. For example, each message in an instant messaging system usually has a unique self-incrementing id that can be used as a cursor. After each query of the current page, the id of the last message record can be passed to the front end (client) as the cursor value.
When loading the next page, the front-end carries the cursor value to initiate the query, and the back-end operates the database from the data whose id is smaller than the current cursor value, so that the query result will not be affected by the new data.
The corresponding SQL statement is:
SELECT * FROM messages
WHERE id < :cursorId
ORDER BY id DESC
LIMIT 5;
Expanding Knowledge
In fact, cursor paging is a classic program, which has many application scenarios, especially for incremental data loading, high-performance querying and processing of large data volumes. In addition to the IM system to obtain the history of message records, common scenarios are social media streaming, content recommendation system, data migration backup and so on.
Cursor paging and a lot of extended knowledge, space reasons will not be expanded here, interested students can be in our
ultimate
Little Abba listened and sighed long and hard: Alas, I didn't think that just such a small feature would make it difficult for me.
Little Abbott: Gotta, I'll go ahead and work on the message management system then! 🐶.
more
💻 Programming Learning Exchange: programming navigation:
📃 Quick resume builder: old fish resumes:
✏️ Interview Brush-Up: Interview Duck: