懒咪学编程


sqlalchemy使用count时遇到的坑

时间:2019-06-08 10:34:41  来源:  作者:ralap  所属栏目:Python

在用flask-sqlalchemy对一个千万级别表进行count操作时,出现了耗时严重、内存飙升的情况。
要统计出一天内车辆访问次数,原代码如下:
car_visit_counts = CarVisit.query.filte

在用flask-sqlalchemy对一个千万级别表进行count操作时,出现了耗时严重、内存飙升的情况。
要统计出一天内车辆访问次数,原代码如下:

car_visit_counts = CarVisit.query.filter(
    CarVisit.park == car_visit.park,
    CarVisit.plate_number == car_visit.plate_number,
    CarVisit.visited_at >= today_start_time(),
).count()

发现代码运行特别慢,所以把生成的sql打印出来看一下:

SELECT
          COUNT(*) AS count_1
FROM
          (
                    SELECT
                              car_visits.id                            AS car_visits_id
                            , car_visits.park_id                       AS car_visits_park_id
                            , car_visits.store_id                      AS car_visits_store_id
                            , car_visits.car_id                        AS car_visits_car_id
                            , car_visits.brand_id                      AS car_visits_brand_id
                            , 
                            ...
                    FROM
                              car_visits
                    WHERE
                              %(param_1)s                 = car_visits.park_id
                              AND car_visits.plate_number = %(plate_number_1)s
                              AND car_visits.visited_at  >= %(visited_at_1)s
          )
          AS anon_1

可以发现进行了一次子查询,这样的话会生成临时表,效率低下,将原语句改变一下:

car_visit_counts = db.session.query(func.count(CarVisit.id)).filter(
    CarVisit.park == car_visit.park,
    CarVisit.plate_number == car_visit.plate_number,
    CarVisit.visited_at >= today_start_time(),
).scalar()

此时在看一下打印的sql语句:

SELECT
          COUNT(car_visits.id) AS count_1
FROM
          car_visits
WHERE
          %(param_1)s                 = car_visits.park_id
          AND car_visits.plate_number = %(plate_number_1)s
          AND car_visits.visited_at  >= %(visited_at_1)s

子查询消失了,速度也快了好多。

原文地址:https://segmentfault.com/a/1190000019414190,作者:ralap

以上就是对sqlalchemy使用count时遇到的坑的相关介绍,希望对您学习python,flask,flask-sqlalchemy有所帮助,感谢您关注懒咪IT学习网!

本文地址:https://c.lanmit.com/bianchengkaifa/Python/5.html

 

推荐Python排行......