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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
|
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<meta name="author" content="韩暮秋">
<meta name="subtitle" content="暮秋小屋">
<meta name="description" content="这里是暮秋小屋，思念和灵感的寄存处">
<meta name="keywords" content="韩暮秋,MuqiuHan,'Muqiu Han', 'muqiu han', muqiuhan">
<title>
N+1 selects problem 与 Prisma ORM |
暮秋小屋
</title>
<link rel="icon" href="/favicon.ico">
<style>
@font-face {
font-family: CarroisSong;
src: url('/fonts/CarroisSong.ttf');
}
</style>
<!-- stylesheets list from _config.yml -->
<link rel="stylesheet" href="/css/style.css">
<!-- scripts list from _config.yml -->
<script
src="/js/menu.js"></script>
<script
src="https://polyfill.alicdn.com/polyfill.js?features=es6"></script>
<script
id="MathJax-script"
async
src="https://lf6-cdn-tos.bytecdntp.com/cdn/expire-1-M/mathjax/3.2.0/es5/tex-mml-chtml.js"></script>
<meta name="generator" content="Hexo 6.3.0"></head>
<body>
<div class="mask-border">
</div>
<div class="wrapper">
<div class="header">
<div class="flex-container">
<div class="header-inner">
<div class="site-brand-container">
<a href="/">
暮秋小屋
</a>
</div>
<div id="menu-btn" class="menu-btn" onclick="toggleMenu()">
Menu
</div>
<nav class="site-nav">
<ul class="menu-list">
<li class="menu-item">
<a href="/">
主页
</a>
</li>
<li class="menu-item">
<a href="/categories/gallery/">
日记本
</a>
</li>
<li class="menu-item">
<a href="/tags/Medicine/">
医学
</a>
</li>
<li class="menu-item">
<a href="/tags/Technique/">
计算机/互联网
</a>
</li>
<li class="menu-item">
<a href="/tags/Life/">
生活
</a>
</li>
<li class="menu-item">
<a href="/archives">
全部
</a>
</li>
<li class="menu-item">
<a href="/about">
关于
</a>
</li>
<li class="menu-item search-btn">
<a href="#">Search</a>
</li>
</ul>
</nav>
</div>
</div>
</div>
<div class="main">
<div class="flex-container">
<article id="post">
<div class="post-head">
<div class="post-info">
<div class="tag-list">
<span class="post-tag">
<a href="/tags/Technique/">
Technique
</a>
</span>
</div>
<div class="post-title">
N+1 selects problem 与 Prisma ORM
</div>
<span class="post-date">
Apr 2, 2025
</span>
</div>
<div class="post-img">
<div class="h-line-primary"></div>
</div>
</div>
<div class="post-content">
<p>N+1 查询问题是指在通过 ORM 查询数据时，执行了<strong>一次</strong>初始查询来获取父对象列表（这 <strong>1</strong> 次查询），然后为列表中的<strong>每一个</strong>父对象都单独执行了一次额外的查询来获取其关联的子对象（这 <strong>N</strong> 次查询）。最终导致总共执行了 <strong>1 + N</strong> 次数据库查询，其中 N 是初始查询返回的父对象的数量。</p>
<p><strong>举个例子：</strong></p>
<p>假设有两个数据库模型：<code>User</code>（用户）和 <code>Post</code>（帖子），一个用户可以有多篇帖子（一对多关系）。</p>
<p>现在，需要获取前 10 个用户以及他们各自的所有帖子。</p>
<p>一种<strong>有问题</strong>的 ORM 实现（或不当的使用方式）可能会这样执行：</p>
<ol>
<li><strong>第一次查询 (The “1”)</strong>: 获取前 10 个用户。<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> <span class="keyword">User</span> LIMIT <span class="number">10</span>;</span><br></pre></td></tr></table></figure></li>
<li><strong>接下来的 N (=10) 次查询 (The “N”)</strong>: 对于上一步获取到的每一个用户，单独执行一次查询来获取该用户的帖子。<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 用户 1</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Post <span class="keyword">WHERE</span> authorId <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line"><span class="comment">-- 用户 2</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Post <span class="keyword">WHERE</span> authorId <span class="operator">=</span> <span class="number">2</span>;</span><br><span class="line"><span class="comment">-- 用户 3</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Post <span class="keyword">WHERE</span> authorId <span class="operator">=</span> <span class="number">3</span>;</span><br><span class="line"><span class="comment">-- ... 直到 用户 10</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> Post <span class="keyword">WHERE</span> authorId <span class="operator">=</span> <span class="number">10</span>;</span><br></pre></td></tr></table></figure></li>
</ol>
<p>在这个场景下，总共执行了 1 + 10 = <strong>11</strong> 次数据库查询。如果 N 的值很大（比如获取 1000 个用户），就会产生 1001 次查询，这对数据库造成巨大的、不必要的压力，并显著增加应用程序的响应时间。每一次数据库交互都有网络延迟和数据库处理的开销，N+1 次查询会将这些开销放大 N 倍。</p>
<p>N+1 问题通常源于 ORM 处理关联数据的方式，特别是与“懒加载”（Lazy Loading）相关的策略。懒加载是指只有在显式访问关联属性时，ORM 才会去数据库加载这些数据。虽然这在某些情况下可以避免加载不需要的数据，但如果在循环中访问关联属性，就很容易触发 N+1 问题。</p>
<p>然而，问题的根源在于<strong>没有有效地预先加载（或批量加载）所需的关联数据</strong>。即使不使用严格意义上的懒加载，如果 ORM 在处理关联查询时不够智能，采用了逐个获取关联对象的策略，同样会产生 N+1 查询。</p>
<p>在 Prisma 出现之前或在其他 ORM 中，解决 N+1 问题常见的方法包括：</p>
<ol>
<li><strong>预先加载（Eager Loading）</strong>: 在执行初始查询时，就明确指示 ORM 同时将关联数据也查询出来。这通常通过 SQL 的 <code>JOIN</code> 操作实现。例如，一次性查询出用户和他们的帖子。虽然这减少了查询次数，但复杂的 <code>JOIN</code> 可能会导致查询本身变得庞大和低效，并可能返回冗余数据。</li>
<li><strong>批量加载（Batch Loading）</strong>: 先执行初始查询获取父对象列表，然后收集所有父对象的 ID，在第二次查询中使用 <code>WHERE IN (...)</code> 子句一次性加载所有相关的子对象。这种方式通常需要两次查询，但避免了 N 次单独的查询。</li>
</ol>
<p>Prisma ORM 在设计上就考虑了 N+1 问题，并提供了一种既方便开发者又高效的解决方案。当使用 Prisma Client 查询数据并需要包含关联模型时，Prisma 会自动优化查询，<strong>避免产生 N+1 查询</strong>，主要通过<strong>关系查询（Relation Queries）</strong>中的 <code>include</code> 选项或嵌套读取（nested reads）来实现这一点：</p>
<p>假设想获取所有用户及其发布的帖子，使用 Prisma Client，可以这样写：</p>
<figure class="highlight typescript"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">import</span> { <span class="title class_">PrismaClient</span> } <span class="keyword">from</span> <span class="string">'@prisma/client'</span></span><br><span class="line"></span><br><span class="line"><span class="keyword">const</span> prisma = <span class="keyword">new</span> <span class="title class_">PrismaClient</span>()</span><br><span class="line"></span><br><span class="line"><span class="keyword">async</span> <span class="keyword">function</span> <span class="title function_">getUsersWithPosts</span>(<span class="params"></span>) {</span><br><span class="line"> <span class="keyword">const</span> usersWithPosts = <span class="keyword">await</span> prisma.<span class="property">user</span>.<span class="title function_">findMany</span>({</span><br><span class="line"> <span class="attr">include</span>: {</span><br><span class="line"> <span class="attr">posts</span>: <span class="literal">true</span>, <span class="comment">// 指示 Prisma 加载关联的 posts</span></span><br><span class="line"> },</span><br><span class="line"> })</span><br><span class="line"> <span class="comment">// usersWithPosts 包含了用户列表，每个用户对象中都有一个 posts 数组</span></span><br><span class="line"> <span class="variable language_">console</span>.<span class="title function_">log</span>(usersWithPosts)</span><br><span class="line">}</span><br><span class="line"></span><br><span class="line"><span class="title function_">getUsersWithPosts</span>()</span><br><span class="line"> .<span class="title function_">catch</span>(<span class="function">(<span class="params">e</span>) =></span> {</span><br><span class="line"> <span class="keyword">throw</span> e</span><br><span class="line"> })</span><br><span class="line"> .<span class="title function_">finally</span>(<span class="title function_">async</span> () => {</span><br><span class="line"> <span class="keyword">await</span> prisma.$disconnect()</span><br><span class="line"> })</span><br></pre></td></tr></table></figure>
<p>当执行上述查询时，Prisma <strong>不会</strong> 生成 N+1 个 SQL 查询。而是首先会分析请求，并将其转化为数量非常有限的高效 SQL 查询。对于上面这个一对多关系的 <code>include</code> 查询，Prisma 通常会执行以下<strong>两步</strong>（类似于批量加载策略）：</p>
<ol>
<li><strong>查询父模型</strong>: 获取所有 <code>User</code> 记录。<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> "public"."User"."id", "public"."User"."name", <span class="comment">/* ... other user fields */</span> <span class="keyword">FROM</span> "public"."User" <span class="keyword">WHERE</span> <span class="number">1</span><span class="operator">=</span><span class="number">1</span></span><br></pre></td></tr></table></figure></li>
<li><strong>查询关联的子模型</strong>: 使用上一步获取到的所有用户 <code>id</code>，通过 <code>WHERE IN (...)</code> 子句一次性查询所有相关的 <code>Post</code> 记录。<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> "public"."Post"."id", "public"."Post"."title", "public"."Post"."authorId", <span class="comment">/* ... other post fields */</span> <span class="keyword">FROM</span> "public"."Post" <span class="keyword">WHERE</span> "public"."Post"."authorId" <span class="keyword">IN</span> ($<span class="number">1</span>, $<span class="number">2</span>, $<span class="number">3</span>, ...) <span class="comment">/* 这里的 $1, $2, ... 是第一步查到的用户 ID 列表 */</span></span><br></pre></td></tr></table></figure></li>
</ol>
<p>Prisma Client 在内存中将这两次查询的结果高效地组合起来，最终返回嵌套的、符合 TypeScript 类型的数据。</p>
<h2 id="Refs"><a href="#Refs" class="headerlink" title="Refs."></a>Refs.</h2><ul>
<li><a target="_blank" rel="noopener" href="https://stackoverflow.com/questions/97197/what-is-the-n1-selects-problem-in-orm-object-relational-mapping">Stack Overflow: What is the N+1 selects problem in ORM?</a></li>
<li><a target="_blank" rel="noopener" href="https://www.prisma.io/docs/orm/prisma-client/queries/query-optimization-performance#solving-the-n1-problem">Prisma Docs: Solving the N+1 problem</a></li>
</ul>
</div>
<script>
window.onload = detectors();
</script>
<div class="post-footer">
<div class="h-line-primary"></div>
<nav class="post-nav">
<div class="prev-item">
<div class="icon arrow-left"></div>
<div class="post-link">
<a href="/2025/04/03/%E4%BA%8C%E3%80%87%E4%BA%8C%E4%BA%94%E5%B9%B4%E4%BA%94%E6%9C%88%E4%B8%89%E6%97%A5/">Prev</a>
</div>
</div>
<div class="next-item">
<div class="icon arrow-right"></div>
<div class="post-link">
<a href="/2025/03/31/%E5%9C%A8F-%E4%B8%AD%E5%A4%84%E7%90%86%E5%A4%8D%E6%9D%82%E4%BE%9D%E8%B5%96%E6%B3%A8%E5%85%A5%E7%9A%84%E5%AE%9E%E8%B7%B5%E6%8C%87%E5%8D%97/">Next</a>
</div>
</div>
</nav>
</div>
<div class="post-comment">
</div>
</article>
</div>
</div>
<div class="footer">
<div class="flex-container">
<div class="footer-text">
韩暮秋 |
希望路过的人可以添点柴火让这里暖和点
</div>
</div>
</div>
</div>
<div class="search-popup">
<div class="search-popup-overlay">
</div>
<div class="search-popup-window" >
<div class="search-header">
<div class="search-input-container">
<input autocomplete="off" autocapitalize="off" maxlength="80"
placeholder="Search Anything" spellcheck="false"
type="search" class="search-input">
</div>
<div class="search-close-btn">
<div class="icon close-btn"></div>
</div>
</div>
<div class="search-result-container">
</div>
</div>
</div>
<script>
const searchConfig = {
path : "/search.xml",
top_n_per_article: "1",
unescape : "false",
trigger: "auto",
preload: "false"
}
</script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/search.js"></script>
<script src="/js/search.js"></script>
</body>
</html>
|
