测试需求平台14-DBUtils优化数据连接与SQL Limit实现分页

2023-10-21 19:21:49 浏览数 (1)

✍ 此系列为整理分享已完结入门搭建《TPM提测平台》系列的迭代版，拥抱Vue3.0将前端框架替换成字节最新开源的arco.design，其中约60%重构和20%新增内容，定位为从 0-1手把手实现简单的测试平台开发教程，内容将囊括基础、扩展和实战，由浅入深带你实现测试开发岗位中平台工具技术能力入门和提升。

本篇将对数据库进行优化，包括链接池优化和limit使用技巧。

1.数据库连接优化

在项目中链接数据是直接通过pymysql去做的链接请求关闭，每次操作都要独立重复请求，其实是比较浪费资源，在并发不大的小项目虽然无感知，但如果有频繁请求的项目中，就会有性能问题，那么可以通过使用连接池技术，管理来进行优化

1.1 Python DBUntils

DBUnitls是一套Python的数据库连接池包，对链接对象进行自动链接和释放，提高高频高并发下数据访问性能，大概的原理如：

DBUntils 按照配置初始化多个数据库连接存储在连接池中

  • 在程序创建连接的时候，从空闲的连接池中获取，不需要重新初始化连接，提升链接速度；
  • 在程序使用完毕后，把连接放回连接池，并不真正地关闭，等待其他请求使用，减少频繁数据的打开和关闭操作。

DBUntils 提供两种方式，并都能自动管理

  • PersistentDB (persistent_db) 提供线程专用的连接
  • PooledDB (pooled_db) 提供线程间可共享的连接

官方 [注解-1] 给出的示意图如下，仅够参考下，不进行扩展

使用此功能需要安装依赖 pip install DBUtils ，一般比较常用就是共享的方式，以 pooled_db为例子，使用方法很简单

代码语言：javascript复制
import pymysql
from dbutils.pooled_db import PooledDB
# 初始化数据库连接，使用pymysql连接，
pool = PooledDB(pymysql,3,host='',port='',user='',passwd='',database='')

# 一般连接
db = pool.connection()
cur = db.cursor()
cur.execute(...)
res = cur.fetchone()
cur.close() # 关闭使用游标
db.close() # 关闭使用连接

# 或者通过with方式
with pool.connection() as db:
    with db.cursor as cur:
        cur.execute(...)
       res = cur.fetchone()

一些参数说明，上边说明中第一参数为 creator 指定那种连接模式，第二参数为mincached

  • mincached ：启动时开启的空的连接数量
  • maxcached ：连接池最大可用连接数量
  • maxshared : 连接池最大可用共享连接数量
  • maxconnections : 最大允许连接数量
  • maxusage ：单个丽娜姐最大复用次数
  • blocking ：达到最大数量时是否阻塞

这里只是对连接的管理，DBUntils的基本知识点讲完了，关于性能对比可以网上搜索相关内容，另外其他的数据库操作还按照自己使用方法进行操作即可。

1.2 实例改造

以已经实现的product.py查询列表接口做一个实际改改造，具体的应用会在后边的需求实现中体现。

代码语言：javascript复制
from dbutils.pooled_db import PooledDB

  pool = PooledDB(pymysql, mincached=2, maxcached=5,host=config.MYSQL_HOST, port=config.MYSQL_PORT,
                  user=config.MYSQL_USER, passwd= config.MYSQL_PASSWORD, database=config.MYSQL_DATABASE,
                  cursorclass=pymysql.cursors.DictCursor)

@app_product.route("/api/product/list", methods=['GET'])
def product_list():
-    # 初始化数据库链接
-    connection = connectDB()
     # 使用连接池链接数据库
    connection = pool.connection()
    # 使用python的with..as控制流语句（相当于简化的try except finally）
    with connection.cursor() as cursor:
        # 查询产品信息表-按更新时间新旧排序
        sql = "SELECT * FROM `products` WHERE `status`=0 ORDER BY `update` DESC"
        cursor.execute(sql)
        data = cursor.fetchall()

    # 按返回模版格式进行json结果返回
    resp_data = {
        "code": 20000,
        "data": data
    }

2. SQL中limit

2.1 基础知识

项目中对于较多的数据显示，需要实现分页数据查询，这可以利用SQL语法关键词 limit ，它可以限制查询结果返回的数量，也可以指定起始索引，来完成分页查询效果，语法形式与参数解释：

  • i：指定。一个返回记录行的偏移量（可省略即表示从0开始）
  • n：第二个参数指定返回记录行的最大数目
代码语言：javascript复制
# 基本用法
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

# 简化形式
select * from tableName limit i,n

两个示例如：

代码语言：javascript复制
... limit 10 # 查询前10条数据, 也可以表示为limit 0,10
... limit 10,15 # 从11条开始返回后15条数据 即11-25

这里有个 注意事项，网上好多关于limit最后一个参数用 -1 可以表示，指定偏移量到最后，这个目前在新的MYSQL版本已经无效，会报语法错误，办法是用个较大的数代替。

如何实现分页查询，通过观察我们可以这样，如果前端传递的page页数（初始为1），每页的数量为page_size，那么第一个参数计算偏移量可设置为 (page-1) * page_size，第二参数表可设置为page_size， 例如 前端请求第一页20个，则计算后后两个参数为（ 0, 20），再如请求第2页20个，则计算后两个参数为 ( 20, 20 )，这样就可以实现分页数据查询效果。

2.2 实践案例

具体案例这里截图一张后边实现提测需求查询，通过参数size，page计算limit参数实现分页的数据查询逻辑。

这个知识点最后，扩展一个小优化，如果是分页数据特别，第一个参数偏移量太大的时候会带来性能的问题，优化的方式是使用子查询优化（前提条件是有自增ID的表），即先通过查询偏移量ID，然后条件 “>=ID limit 数量" 进行查询。

代码语言：javascript复制
SELECT * FROM apps where id >=(SELECT id FROM apps LIMIT 100000,1) LIMIT 20

上篇回顾：测试需求平台13-Table组件应用产品列表优化

下篇预告：前端js好用的时间库Moment.js

测试 分页 连接 数据 优化

0 人点赞