deploy: df2dc4b691a4a9b67ae92319ef5a38f1b73eb4a2

1 files changed, 291 insertions, 0 deletions

diff --git a/2025/04/02/N-1-selects-problem-与-Prisma-ORM/index.html b/2025/04/02/N-1-selects-problem-与-Prisma-ORM/index.html
new file mode 100644
index 00000000..18320f16
--- /dev/null
+++ b/2025/04/02/N-1-selects-problem-与-Prisma-ORM/index.html
@@ -0,0 +1,291 @@
+<!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>
+    @import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&family=Roboto+Mono&display=swap');
+</style>
+
+
+
+    <!-- stylesheets list from _config.yml -->
+    
+    <link rel="stylesheet" href="/css/style.css">
+    
+
+
+
+
+    <!-- scripts list from _config.yml -->
+    
+    <script src="/js/frame.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="/about">关于</a>
+              </li> 
+                   
+          
+          
+            <li class="menu-item search-btn">
+              <a href="#">Search</a>
+            </li>
+          
+        </ul>
+      </nav>
+    </div>
+  </div>
+</div>
+<script src="/js/menu.js"></script>
+
+
+      <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 (&#x3D;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 &#x3D; <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> &#123; <span class="title class_">PrismaClient</span> &#125; <span class="keyword">from</span> <span class="string">&#x27;@prisma/client&#x27;</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>) &#123;</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>(&#123;</span><br><span class="line">    <span class="attr">include</span>: &#123;</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">    &#125;,</span><br><span class="line">  &#125;)</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">&#125;</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>) =&gt;</span> &#123;</span><br><span class="line">    <span class="keyword">throw</span> e</span><br><span class="line">  &#125;)</span><br><span class="line">  .<span class="title function_">finally</span>(<span class="keyword">async</span> () =&gt; &#123;</span><br><span class="line">    <span class="keyword">await</span> prisma.$disconnect()</span><br><span class="line">  &#125;)</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> &quot;public&quot;.&quot;User&quot;.&quot;id&quot;, &quot;public&quot;.&quot;User&quot;.&quot;name&quot;, <span class="comment">/* ... other user fields */</span> <span class="keyword">FROM</span> &quot;public&quot;.&quot;User&quot; <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> &quot;public&quot;.&quot;Post&quot;.&quot;id&quot;, &quot;public&quot;.&quot;Post&quot;.&quot;title&quot;, &quot;public&quot;.&quot;Post&quot;.&quot;authorId&quot;, <span class="comment">/* ... other post fields */</span> <span class="keyword">FROM</span> &quot;public&quot;.&quot;Post&quot; <span class="keyword">WHERE</span> &quot;public&quot;.&quot;Post&quot;.&quot;authorId&quot; <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>
+        <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>