Postgresql 服务器端编程 - 操作耗时如何从1小时减到

OB保种管理系统使用说明
如何使数据库访问速度提升167倍

OB保种管理系统，目前用户众多，操作数据量越来越大，查询速度优化事宜，提到日程上来了。使用Flask (Python) + Postgresql

image.png

需解决的问题：

• 原型开发，主要使用SQLAlchemy，开发速度是很快，但执行效率低。当数据量上来之后，查询都要10~15秒，用户体验变差
• 复杂运算逻辑，需要交叉查询大量数据来得到最终结果，Host服务器执行起来慢。保种系统每次计算员工工资，需要查询5张大表，然后做运算，导致计算所有员工工资需要0.5~1小时！
• 原先依赖于Redis，但受Host服务器限制，对于大量数据，Redis加速有限

优化表现：

• 普通查询：10~15秒 => 少于1秒
• 复杂运算逻辑：1小时 => 少于5秒

优化步骤：

1. 普通查询的优化

• ORM => SQL
  SQLAlchemy ORM语句转化成raw SQL。花了1、2天时间，读了postgres教程。再花2天，把所有耗时查询，全部改为raw SQL语句（其实并不难，就几大操作类型），然后用SQLAlchemy session.execute执行就行，对原架构不用作改动！

#例子
with app.app_context():
    ss = db.session
    ss.execute(""" SELECT id, size FROM ourbits o INNER JOIN xxx GROUP BY o.id; """)
    ss.commit()

2. 复杂运算逻辑：

• Postgresql 服务器端编程（Server Side Programming）
  • View：一些公用的查询条件，直接创建view，可以简化Web后端的代码，但不会提速
  • Materialized View：一些耗时长的公用的查询条件，可以创建为实例化View， 后端的代码可以直接访问，提速明显！Web后端定期触发刷新就行。也可以在Postgresql 用Trigger触发

• Function：对于大量交叉查询的操作，创建为Postgresql 服务器端的函数，可以显著节省查询时间。而且Postgresql 服务器端编程，提供Python2/3的接口，0门槛就能编写了！

  
  image.png