@创建于:2020.03.28
@修改于:2020.03.28
1. 背景
在python flask中,用sql语句进行 查询,如何做到前端请求数据后快速返回?
如:在使用flask-sqlalchemy对一个千万级别表进行count操作时,出现了耗时严重、内存飙升的问题。
2. 解决办法对比
2.1 原代码
说明:进行了一次子查询,会生成临时表,效率低。
# 统计当日登陆次数
count = LoginLog.query.filter(LoginLog.username == username, LoginLog.status == 0, db.cast(LoginLog.time, db.DATE) == db.cast(datetime.utcnow(), db.DATE)).count()
# sql打印
SELECT
count(*) AS count_1
FROM
(
SELECT
loginlog.id AS loginlog_id,
loginlog.username AS loginlog_username,
loginlog.time AS loginlog_time,
loginlog. STATUS AS loginlog_status
FROM
loginlog
WHERE
loginlog.username = % (username_1) s
AND loginlog. STATUS = % (status_1) s
AND CAST(loginlog.time AS DATE) = CAST(%(param_1) s AS DATE)
) AS anon_1
2.2 优化后
说明:无子查询,效率高。
count = db.session.query(func.count(LoginLog.id)).filter(LoginLog.username == username, LoginLog.status == 0, db.cast(LoginLog.time, db.DATE) == db.cast(datetime.utcnow(), db.DATE)).scalar()
# sql打印
SELECT
count(loginlog.id) AS count_1
FROM
loginlog
WHERE
loginlog.username = % (username_1) s
AND loginlog. STATUS = % (status_1) s
AND CAST(loginlog.time AS DATE) = CAST(%(param_1) s AS DATE)