关于SQLite批量删除的性能优化

在开发Flame时,后台需要一个邀请码删除功能,首先介绍下项目的技术背景:

  • 后端:Python & Flask
  • 前端:Layui
  • 数据库:Sqlite + Flask-SQLAlchemy

100 Parameters or 100 Requests

最开始开发的时候,我想的还很简单,直接在后端写了一个能够删除邀请码的路由,只要把邀请码传进来,就能在数据库中删掉这行数据,大致长这样:

1
http://192.168.1.1/delete/invite_code/b90390db1e4f121854078cbbd0c0ff03

在请求后即在数据库中查找并删除b90390db1e4f121854078cbbd0c0ff03这行数据

而前端在选中数据表中的选项后,自然而然的就写了一个循环用于迭代提交ajax请求,往后端一直请求数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// 上文省略
deleteTicked: function() {
var checkStatus = table.checkStatus('invitation_manager'),
data = checkStatus.data;
for (d in data) {
var icode = data[d].invite_code;
console.log(icode);
$.ajax({
type: 'GET',
url: '/api/delete/invite_code/' + icode,
success: function(value) {
if (value === '1') {
{#删除成功不显示数据#
}
} else {
layer.msg('删除失败');
}
}
});
}
layer.msg('删除成功');
table.reload('invitation_manager', {
url: '/api/all_invite_codes',
where: {} //设定异步数据接口的额外参数
//,height: 300
});

很快我就发现了问题,当删除100条邀请码的时候,前端直接卡死等待响应,后端则一直收到GET请求……然后用不存在的大脑思考了一下发现智障了,性能瓶颈在HTTP请求上

遂第二天重构,改为直接发一个列表过去,让后端去迭代删除

100 Commits or 1 Commit

对Flask和SQLAlchemy熟悉的同学一定知道里面有一个非常方便的功能,在开启SQLALCHEMY_COMMIT_ON_TEARDOWNTrue时,可以在每次与数据库连接完成后,自动进行commit。例如在添加完数据db.session.add(user)之后,它会自动进行db.session.commit()的操作

而我的后端是这么写的

1
2
3
4
5
6
7
8
9
10
11
12
13
@api.route('/delete/invite_code', methods=['POST'])
@api_login_required
@admin_required
def delete_invite_code():
to_delete = json.loads(request.values.get("to_delete", []))
for td in to_delete:
icode = str(td['invite_code'])
ainvite_code = Invitation.query.filter_by(invitation_code=icode).first()
if ainvite_code is None:
pass
else:
db.session.delete(ainvite_code)
return make_response('1')

自然而然地在每次delete操作之后,会自动进行一次commit。而sqlite本身对并发支持并不佳,在写入操作时会锁定数据库,不允许读操作,而这时再访问就会报出sqlalchemy.exc.OperationalError错误

那么问题就在于如何避开自动提交了:首先一个思路自然而然就是关掉SQLALCHEMY_COMMIT_ON_TEARDOWN,但这样需要我们把之前所有的数据库操作后再重新加上db.session.commit(),感觉改动量稍大,而且我们也并不想放弃这个语法糖

于是决定使用execute()函数直接执行sqlite语句

1
db.session.execute("delete from invitations where invitation_code = '" + icode + "'"])

但是显而易见,这种拼接方式会造成SQL注入,只需闭合单引号即可执行任意SQL语句。于是又进行了参数绑定

1
db.session.execute("delete from invitations where invitation_code = (?)",[icode])

这里需要注意的一个地方是,execute函数传入的第二个值应该len为1,如果直接传入字符串的话len为字符串的长度,所以应该用列表封装一下。但是封装后又发现遇到另一个问题

1
2
3
File "/opt/local/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLAlchemy-0.8.0b2-py2.7-macosx-10.8-x86_64.egg/sqlalchemy/engine/base.py", line 740, in _execute_clauseelement
keys = distilled_params[0].keys()
AttributeError: 'list' object has no attribute 'keys'

关于这个问题,我只在Google论坛找到了类似的分析,问题还是出在传入SQLAlchemy时需要一个.keys()的操作,由于传入的列表没有key,这里才会报错

深入挖掘SQLAlchemy需要继续深入到源码,而且资料不多,于是换了个思路,直接调了数据库原始连接,对sqlite进行操作,相关问题可以参考这里

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 删除邀请码
@api.route('/delete/invite_code', methods=['POST'])
@api_login_required
@admin_required
def delete_invite_code():
to_delete = json.loads(request.values.get("to_delete", []))
connection = db.engine.raw_connection()
cursor = connection.cursor()
for td in to_delete:
icode = str(td['invite_code'])
cursor.execute("delete from invitations where invitation_code= ? ", [icode])
cursor.close()
connection.commit()
connection.close()
return make_response('1')

最终性能得到了极大提升,且不会出现锁库现象