Pagination upgrade of token in mysql

When querying the database paging, a token used for paging must be returned. This token can be directly used to locate the first piece of data on the next page. The offset of limit offset cannot be simply used as the page token, and the query time complexity cannot be reduced when the data volume is large.

Introduction

The core of this view is to improve the efficiency of paging query in large data sets. When the data volume is very large, useLIMITandOFFSETStatement paging may cause performance problems because the database needs to be skippedOFFSETSpecify a number of rows to retrieve data, which meansOFFSETWith the increase in  , the query efficiency will gradually decrease.

To solve this problem, a pagination method based on cursors or "Seek Method" can be used. This method does not search the next page by specifying the number of records to be skipped, but by recording a unique key (usually a primary key or a unique index) of the last record on the previous page, and then querying all sorted next sets of records.

Here is a simple example, suppose we have auserstable, which contains a self-incrementedidFields are used as primary keys.

First, we need an API, which does not accept a page number, but accepts alast_seen_id(ID of the last user on the previous page) as a parameter to get the data of the next page.

Code

Here is a sample code that uses the Flask framework to create a pagination API. For simplicity, this example does not include details of database connections:

from flask import Flask, request, jsonify

app = Flask(__name__)

# Suppose we have a database query functiondef query_db(query, args=(), one=False):
    # Here should be the code to execute database queries, such as using pymysql or sqlalchemy    # This is for example only, and no specific implementation is provided    pass

# Pagination query API@('/users', methods=['GET'])
def get_users():
    # Set the default number and maximum number per page    default_page_size = 10
    max_page_size = 100
    
    # Get last_seen_id and number of page parameters from the request    last_seen_id = ('last_seen_id', None)
    page_size = int(('page_size', default_page_size))
    
    # Restrict page_size cannot exceed the maximum limit    if page_size > max_page_size:
        page_size = max_page_size
    
    # Create a query and use last_seen_id to locate the next page of data    if last_seen_id:
        query = "SELECT * FROM users WHERE id > %s ORDER BY id ASC LIMIT %s"
        args = (last_seen_id, page_size)
    else:
        # If last_seen_id is not provided, return the first page of data        query = "SELECT * FROM users ORDER BY id ASC LIMIT %s"
        args = (page_size,)
    
    # Execute query    users = query_db(query, args)
    
    # Convert query results to dictionary list    # Assume that each user is a tuple (id, name)    users_list = [{'id': user[0], 'name': user[1]} for user in users]
    last_seen_id = users[-1][0] if users else None
    
    # Return JSON response    return jsonify({
        'users': users_list,
        'last_seen_id': last_seen_id
    })

# Start Flask appif __name__ == '__main__':
    (debug=True)

Using this API, the client can specifylast_seen_idParameters to get the data on the next page, for example/users?last_seen_id=100&page_size=20The next page with ID greater than 100 will be obtained.

The advantage of this method is that no matter whether you want to obtain the first page or the first million page, the query efficiency is the same, because the database is always from the last one on the previous page.idStart looking for the next set of records without skipping any rows. This can greatly improve the efficiency of querying, especially when dealing with large data sets.

This is the end of this article about the role of token in mysql. For more related content of mysql token, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!