sqlalchemy使用count()函数遇到的问题

在使用flask-sqlalchemy对一个千万级别表进行count操作时，出现了耗时严重、内存飙升的问题。

原代码：

# 统计当日登陆次数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_1FROM    (        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

进行了一次子查询，会生成临时表，效率低。

 

优化代码：

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_1FROM    loginlogWHERE    loginlog.username = % (username_1) sAND loginlog. STATUS = % (status_1) sAND CAST(loginlog.time AS DATE) = CAST(%(param_1) s AS DATE)

无子查询，效率高。