在大数据集展示的业务场景中，直接查询MySQL全量数据返回给前端会导致接口响应延迟、服务端内存占用过高，甚至引发服务不可用的问题。通过Sublime开发合理的MySQL数据接口分页功能，是优化大数据集展示性能的核心方案。
分页方案对比与选型
常见的MySQL分页方案有两种，不同方案在大数据集下的性能表现差异极大，具体对比如下：
| 分页方案 | 实现方式 | 大数据集性能 | 适用场景 |
|---|---|---|---|
| LIMIT偏移分页 | 使用LIMIT offset, pageSize语法 | 偏移量越大性能越差，百万级数据偏移50万时查询耗时明显升高 | 小数据集、偏移量较小的场景 |
| 游标分页（基于自增ID） | 使用WHERE id > lastId LIMIT pageSize语法 | 性能稳定，不受数据量大小影响，查询耗时恒定 | 大数据集、流式加载场景 |
Sublime开发分页接口的实现步骤
1. 环境准备
在Sublime中新建Python文件，首先安装MySQL连接依赖，这里使用pymysql库，通过终端执行安装命令：
pip install pymysql
2. 数据库连接封装
先封装MySQL连接工具类，方便后续复用，代码如下：
import pymysql
class MySQLClient:
def __init__(self, host, user, password, db, port=3306):
self.conn = pymysql.connect(
host=host,
user=user,
password=password,
db=db,
port=port,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor # 返回字典格式结果
)
def query(self, sql, params=None):
with self.conn.cursor() as cursor:
cursor.execute(sql, params)
return cursor.fetchall()
def close(self):
self.conn.close()
3. 游标分页接口实现
采用性能更优的游标分页方案，实现获取用户列表的分页接口，代码如下：
from flask import Flask, request, jsonify
from mysql_client import MySQLClient # 导入上面的连接类
app = Flask(__name__)
# 初始化MySQL客户端，实际使用时替换为你的数据库配置
mysql_client = MySQLClient(
host='127.0.0.1',
user='root',
password='123456',
db='test_db'
)
@app.route('/api/users/page', methods=['GET'])
def get_user_page():
# 获取请求参数，last_id是上一页最后一条数据的id，默认从0开始
last_id = request.args.get('last_id', type=int, default=0)
page_size = request.args.get('page_size', type=int, default=20)
# 限制最大每页条数，避免恶意请求导致性能问题
if page_size > 100:
page_size = 100
# 构建分页查询SQL，使用游标分页避免大偏移性能问题
sql = 'SELECT id, username, email FROM users WHERE id > %s ORDER BY id ASC LIMIT %s'
params = (last_id, page_size)
user_list = mysql_client.query(sql, params)
# 构造返回结果，包含下一页的起始游标
result = {
'code': 0,
'data': user_list,
'has_more': len(user_list) == page_size,
'next_last_id': user_list[-1]['id'] if user_list else last_id
}
return jsonify(result)
if __name__ == '__main__':
app.run(debug=True)
性能优化额外建议
- 给分页查询的排序字段添加索引，比如上面的
id是自增主键默认有索引，如果是其他排序字段需要手动创建索引 - 限制单次请求的最大分页条数，避免用户传入过大的
page_size导致查询压力升高 - 对于非实时性要求高的场景，可以给分页接口添加缓存，缓存时间根据业务数据更新频率调整
- 如果业务需要支持跳页功能，可结合偏移分页和游标分页，小偏移用偏移分页，大偏移引导用户使用游标分页
注意事项
使用LIMIT offset, pageSize偏移分页时，MySQL会先扫描offset+pageSize条记录，再丢弃前offset条，因此offset越大查询越慢。如果业务必须使用偏移分页，可优化为子查询方式：
-- 优化前的大偏移分页 SELECT id, username FROM users LIMIT 500000, 20; -- 优化后的子查询分页，先查到偏移位置的id再关联查询 SELECT id, username FROM users WHERE id >= (SELECT id FROM users ORDER BY id LIMIT 500000, 1) LIMIT 20;
这种优化方式可以减少扫描的数据量，提升大偏移场景下的查询性能，但整体仍不如游标分页稳定。