SequelizeJS中的慢关联 [英] Slow associations in SequelizeJS
问题描述
我正在尝试使用SequlizeJS作为ORM的Express应用程序诊断速度下降的原因.我有一个具有2倍hasMany&的模型hasOne与其他2个模型的关系:
I am trying to diagnose the cause of some slow downs in my Express app which is using SequlizeJS as the ORM. I have a model that has a 2x hasMany & a hasOne relation with 2 other models:
更新:我已经使用classMethods#associate函数在define调用中建立了关联.
Update: I've made the associations within the define call using the classMethods#associate function.
// Model1
classMethods: {
associate: function(models) {
Model1.hasMany(models.Model2);
Model1.hasMany(models.Model3);
Model1.hasOne(models.Model2, {as: 'next', foreignKey: 'model2_next'});
}
}
// Model2
classMethods: {
associate: function(models) {
Model2.belongsTo(models.Model1, {foreignKey: 'model2_next'});
}
}
如果我以以下方式查询它们:
if I query them in the following manner:
db.Model1.find({
where: { /* Simple where statement */ },
include: [
db.Model2,
db.Model3,
{ model: db.Model2, as: 'next' },
]
}).complete(function(err, data) {
res.json(data);
});
可能需要8到12秒的时间才能做出响应.但是，如果我分别查询Model2并使用异步&手动合并它们， lodash库:
It can take between 8-12seconds to respond. However, if I query Model2 separately and merge them manually using the async & lodash libraries:
async.parallel({
model2: function(callback) {
db.Model2.findAll({
where: { /* Simple where statement */ }
}).complete(callback)
},
model1: function(callback) {
db.Model1.find({
where: { /* Simple where statement */ },
include: [
db.Model3,
{ model: db.Model2, as: 'next' },
]
}).complete(callback);
}
}, function(err, data) {
var response = data.model1.values;
response.Model2 = data.model2.map(function(Model2){ return Model2.values });
res.json(response);
})
大约需要60-100毫秒.
it takes between 60-100ms.
我尝试过从MySQL切换到PostgreSQL，虽然PostgreSQL的速度要快一些，但是大约是2-3％！
I've tried switching from MySQL to PostgreSQL and whilst PostgreSQL was fractionally faster, it was a matter of 2-3%!
是什么原因导致Sequelize花费比拆分查询更长的时间，并且有什么方法可以加快此过程?
What is causing Sequelize to take so much longer than the split query and is there a way I can speed this up?
推荐答案
将:M关系添加到包含中时，Sequelize的速度变慢. :M关系会导致sql结果中出现重复的行，因此我们必须花费时间对重复数据进行重复数据删除并将其解析为模型.
Sequelize slows down when you add :M relations to your include. :M relations result in duplicate rows in your sql result, so we have to spend time deduplicating that and parsing it into models.
为了获得最佳性能，您可以将:1关系保留在include中，但在单独的查询中保留:M.
For optimal performance you could leave your :1 relations in your include but do the :M in seperate queries.
当然查询本身也可能很慢，但很可能是Sequelize开销的结果-尝试直接在数据库上运行查询.
Of course the query itself could also be slow, but most likely it's the result of Sequelize overhead - Try running the query directly on the database.
(免责声明:Sequelize核心开发人员)
(Disclaimer: Sequelize core developer)
您正在运行什么版本?您报告的初始数字听起来很高，但是在进行一些优化之前我们已经听说过这些数字，请尝试对最新的git master进行测试.
What version are you running on? The initial number you reported sounds high, but we have heard of those numbers before we did some optimizations, try testing against the latest git master.
我们一直在为这些方案优化代码，但是将20.000行重复数据删除为5.000行将始终需要一些cpu周期.
We're always working on optimizing the code for these scenarios, but deduplicating 20.000 rows to 5.000 rows will always require some cpu cycles.
这篇关于SequelizeJS中的慢关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
