SqlAlchemy 分页查询计算总数的优化

框架: Flask
查询代码:

model = Album
query = model.query
# 获取分页信息
page, page_size = pag_args['page'], pag_args['page_size']
# 先获取分页总数
total = query.count()
# 再获取对应页面的数据
offset = (page - 1) * page_size
result_list = query.limit(page_size).offset(offset)

打印出计算total 的sql, 如下:

SELECT count(*) AS count_1 
FROM (SELECT ...
FROM album 
WHERE album.app_id = 1 ORDER BY album.id DESC) AS anon_1

可以看出,在Flask的sqlalchemy中,
query.count() 查询生成的语法结构如下:

SELECT count(*) AS count_1 FROM (
     SELECT <rest of query follows...> ) AS anon_1

从500万条数据中查询4984684数据执行时间 共 1 行, 查询花费 47.8617 秒

思索能不能优化成 select count(col) from model
修改sqlalchemy查询代码:

model = Album
query = model.query
# 获取分页信息
page, page_size = pag_args['page'], pag_args['page_size']
# 优化获取分页总数
total = query.with_entities(func.count(model.id)).scalar()
# 再获取对应页面的数据
offset = (page - 1) * page_size
result_list = query.limit(page_size).offset(offset)

查看total执行语句:

SELECT count(album.id) AS count_1 FROM album WHERE album.app_id = 1 ORDER BY album.id DESC

查看同样数据执行时间 共 1 行, 查询花费 1.2155 秒。