性能文章>【译】为什么我的数据库很慢,10 个查询反而比 1 个查询更快?>

【译】为什么我的数据库很慢,10 个查询反而比 1 个查询更快?转载

2年前
471237
GET /tweets 
GET /tweets/id
POST /tweets
GET /users
GET /users/id
GET /tweets?orderBy=createdAt,DESC&limit=10 
[
{
"id": 1,
"contents": "这是我第一篇文章!",
"authorId": 4
},
{
"id": 2,
"contents":
“hello world!”,
“ID”:7
}
...
]
GET /users/4
{
"id": 4,
"firstName": "Mahatma",
"lastName": "Gandhi"
}
GET /users/7
{
"id": 7,
"firstName": "Florence",
"lastName": "Nightingale"
}
GET /tweets?orderBy=createdAt,DESC&limit=10 
[
{
"id": 1,
"contents": "这是我的第一篇文章!",
"author": {
"id": 4,
"firstName": "Mahatma" ,
"lastName": "Gandhi"
}
},
{
"id": 2,
"contents": "Hello world!",
"author": {
"id": 7,
"firstName": "Florence",
"lastName" :“夜莺”
}
}
...
]
SELECT * FROM Tweet
JOIN User ON User.id = Tweet.authorId
ORDER BY Tweet.createdAt DESC
LIMIT 10;
 
CallList 有很多 ScheduledCalls,其中有很多 Calls,有很多症状
 
SELECT * FROM CallList 
LEFT JOIN ScheduledCall ON ScheduledCall.callListId = CallList.id LEFT JOIN Call ON Call.scheduledCallId = ScheduledCall.id 
LEFT JOIN Symptom ON Symptom.callId = Call.id 
WHERE CallList.id = 1;
callList.scheduledCalls;
左加入 ScheduledCall ON ScheduledCall.id = CallList.id
async function getCallListAndStatusRelations(id: string): Promise<CallList> {
  const callList = await entityManager.findOne(CallList, id);
  const scheduledCalls = await entityManager.find(ScheduledCall, { 
    where: { 
      callListId: callList.id
    } 
  });
  const calls = await entityManager.find(Call, {
    where: { 
      scheduledCallId: In(scheduledCalls.map(sc => sc.id) 
    }
  });
  const symptoms = await entityManager.find(Symptom, {
    where: {
      callId: In(calls.map(c => c.id)
    }
  });
  // ...
}
 
SELECT * FROM CallList WHERE id = '1';
SELECT * FROM ScheduledCall WHERE callListId IN ('1'); 
SELECT * FROM Call WHERE scheduledCallId IN ('some', 'ids');
SELECT * FROM Symptom WHERE callId IN ('lots', 'more', 'identifiers');
 
 
async function getCallListAndStatusRelations(id: string): Promise<CallList> {
  // ...
  calls.forEach(c => 
    c.symptoms = symptoms.filter(s => s.callId === c.id)
  );
  scheduledCalls.forEach(sc => 
    sc.calls = calls.filter(c => c.scheduledCallId === sc.id)
  );
  callList.scheduledCalls = scheduledCalls;
  return callList;
}
  • 真实世界的数据模型比待办事项应用程序或 Twitter 克隆更丰富、更复杂
  • JOIN 只能在一定程度上提高性能(从那时起,情况会变得更糟)
  • 软件运行时和数据库引擎擅长不同的事情,你应该利用它来发挥自己的优势
async function getCallListAndStatusRelations(id: string): Promise<CallList> {
  const callList = await entityManager.findOne(CallList, id);
  const scheduledCalls = await entityManager.find(ScheduledCall, { 
    where: { 
      callListId: callList.id
    } 
  });
  const calls = await entityManager.find(Call, {
    where: { 
      scheduledCallId: In(scheduledCalls.map(sc => sc.id) 
    }
  });
  const symptoms = await entityManager.find(Symptom, {
    where: {
      callId: In(calls.map(c => c.id)
    }
  });
  calls.forEach(c => 
    c.symptoms = symptoms.filter(s => s.callId === c.id)
  );
  scheduledCalls.forEach(sc => 
    sc.calls = calls.filter(c => c.scheduledCallId === sc.id)
  );
  callList.scheduledCalls = scheduledCalls;
  return callList;
}
 
点赞收藏
金色梦想

终身学习。

请先登录,查看3条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

日常Bug排查-偶发性读数据不一致

日常Bug排查-偶发性读数据不一致

7
3