peewee update和save性能分析
背景
python项目中使用了peewee这款orm框架，在对数据库更新时有两种语法，分别是save和update方法。有同事说从peewee的日志来看，update比save更快，于是做了一个简单的比较实验，看看真实情况如何。
基础环境：
python: 3.8.10
peewee: 3.16.2
数据库：sqlite
准备
插入1w条数据
import datetime
from peewee import AutoField, DateTimeField, Model, SqliteDatabase, TextField, IntegerField
class BaseModel(Model):
"""A base model that will use our Sqlite database."""
id = AutoField()
update_time = DateTimeField(default=datetime.datetime.now)
class Meta:
database = db
class User(BaseModel):
name = TextField()
age = IntegerField()
class Meta:
table_name = "user"
if __name__ == "__main__":
User.truncate_table()
db.connect()
db.create_tables([User])
data = []
for i in range(10000):
data.append({"name": f"person_P{i}", "age": i})
print(i)
User.insert_many(data).execute()
update 更新
if __name__ == "__main__":
import logging
import time
logger = logging.getLogger('peewee')
logger.propagate = False
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
start = time.time()
for user in users:
User.update(age=6012).where(User.id==user.id).execute()
print(user.id, user.name, user.age)
end = time.time()
print(f"total: {end-start}")
peewee：日志
('UPDATE "user" SET "update_time" = ?, "name" = ?, "age" = ? WHERE ("user"."id" = ?)', [datetime.datetime(2023, 8, 29, 17, 30, 36, 719081), 'person_P9996', 12341, 10024])
('UPDATE "user" SET "update_time" = ?, "name" = ?, "age" = ? WHERE ("user"."id" = ?)', [datetime.datetime(2023, 8, 29, 17, 30, 36, 719088), 'person_P9997', 12341, 10025])
('UPDATE "user" SET "update_time" = ?, "name" = ?, "age" = ? WHERE ("user"."id" = ?)', [datetime.datetime(2023, 8, 29, 17, 30, 36, 719096), 'person_P9998', 12341, 10026])
('UPDATE "user" SET "update_time" = ?, "name" = ?, "age" = ? WHERE ("user"."id" = ?)', [datetime.datetime(2023, 8, 29, 17, 30, 36, 719103), 'person_P9999', 12341, 10027])
结果：67.96582674980164 s
save更新
if __name__ == "__main__":
import logging
import time
logger = logging.getLogger('peewee')
logger.propagate = False
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
start = time.time()
for user in users:
user.age = 12341
user.save()
print(user.id, user.name, user.age)
end = time.time()
print(f"total: {end-start}")
peewee日志：
('UPDATE "user" SET "age" = ? WHERE ("user"."id" = ?)', [2000, 10025])
10025 person_P9997 3000
('UPDATE "user" SET "age" = ? WHERE ("user"."id" = ?)', [2000, 10026])
10026 person_P9998 3000
('UPDATE "user" SET "age" = ? WHERE ("user"."id" = ?)', [2000, 10027])
结果：67.52418804168701 s
结果分析
从三个方面来分析：
- 从打印的日志来看，
save会更新记录全部字段，update只会更新指定的字段。 - 从结果来看，1w行以内的更新操作两者没有性能的差别。
- 从方法实现来看，
update方法是底层方法，save方法调用了update方法或insert方法实现更新操作。
所以理论上来说update 比 save 更底层，效率略高。实际使用中save写法较为方便，个人更喜欢save方法。
update 方法
def __sql__(self, ctx):
super(Update, self).__sql__(ctx)
with ctx.scope_values(subquery=True):
ctx.literal('UPDATE ')
expressions = []
for k, v in sorted(self._update.items(), key=ctx.column_sort_key):
if not isinstance(v, Node):
if isinstance(k, Field):
v = k.to_value(v)
else:
v = Value(v, unpack=False)
elif isinstance(v, Model) and isinstance(k, ForeignKeyField):
# NB: we want to ensure that when passed a model instance
# in the context of a foreign-key, we apply the fk-specific
# adaptation of the model.
v = k.to_value(v)
if not isinstance(v, Value):
v = qualify_names(v)
expressions.append(NodeList((k, SQL('='), v)))
(ctx
.sql(self.table)
.literal(' SET ')
.sql(CommaNodeList(expressions)))
if self._from:
with ctx.scope_source(parentheses=False):
ctx.literal(' FROM ').sql(CommaNodeList(self._from))
if self._where:
with ctx.scope_normal():
ctx.literal(' WHERE ').sql(self._where)
self._apply_ordering(ctx)
return self.apply_returning(ctx)
update方法可以看到是拼接出一个sql语句，update xx set xx=xx where
save 方法
def save(self, force_insert=False, only=None):
field_dict = self.__data__.copy()
if self._meta.primary_key is not False:
pk_field = self._meta.primary_key
pk_value = self._pk
else:
pk_field = pk_value = None
if only is not None:
field_dict = self._prune_fields(field_dict, only)
elif self._meta.only_save_dirty and not force_insert:
field_dict = self._prune_fields(field_dict, self.dirty_fields)
if not field_dict:
self._dirty.clear()
return False
self._populate_unsaved_relations(field_dict)
rows = 1
if self._meta.auto_increment and pk_value is None:
field_dict.pop(pk_field.name, None)
if pk_value is not None and not force_insert:
if self._meta.composite_key:
for pk_part_name in pk_field.field_names:
field_dict.pop(pk_part_name, None)
else:
field_dict.pop(pk_field.name, None)
if not field_dict:
raise ValueError('no data to save!')
rows = self.update(**field_dict).where(self._pk_expr()).execute()
elif pk_field is not None:
pk = self.insert(**field_dict).execute()
if pk is not None and (self._meta.auto_increment or
pk_value is None):
self._pk = pk
# Although we set the primary-key, do not mark it as dirty.
self._dirty.discard(pk_field.name)
else:
self.insert(**field_dict).execute()
self._dirty -= set(field_dict) # Remove any fields we saved.
return rows
save方法是调用update方法或insert方法间接实现更新。
