diff options
| author | muqiuhan <[email protected]> | 2025-09-09 06:17:00 +0000 |
|---|---|---|
| committer | muqiuhan <[email protected]> | 2025-09-09 06:17:00 +0000 |
| commit | d5de65fdb1802cdf498d65d93397f290813c377c (patch) | |
| tree | 92fe61a76d20d1203203665d4b48e7f151f088bd /2025/04 | |
| parent | 48efa2dfde7c263f84ee5bb0872747034908d607 (diff) | |
| download | blog-d5de65fdb1802cdf498d65d93397f290813c377c.tar.gz | |
deploy: 9665097f0fa0dae9f92123fac54d26c0818758a5
Diffstat (limited to '2025/04')
| -rw-r--r-- | 2025/04/02/N-1-selects-problem-与-Prisma-ORM/index.html | 18 | ||||
| -rw-r--r-- | 2025/04/13/uuidv7-rdbms/index.html | 28 | ||||
| -rw-r--r-- | 2025/04/20/linux-amd-screen-boom/index.html | 1 |
3 files changed, 29 insertions, 18 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 index d8936873..819f6d02 100644 --- a/2025/04/02/N-1-selects-problem-与-Prisma-ORM/index.html +++ b/2025/04/02/N-1-selects-problem-与-Prisma-ORM/index.html @@ -198,10 +198,12 @@ <p>现在，需要获取前 10 个用户以及他们各自的所有帖子。</p> <p>一种有问题的 ORM 实现（或不当的使用方式）可能会这样执行：</p> <ol> -<li>第一次查询 (The “1”): 获取前 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>接下来的 N (=10) 次查询 (The “N”): 对于上一步获取到的每一个用户，单独执行一次查询来获取该用户的帖子。<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> +<li>第一次查询 (The “1”): 获取前 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>接下来的 N (=10) 次查询 (The “N”): 对于上一步获取到的每一个用户，单独执行一次查询来获取该用户的帖子。<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 = 11 次数据库查询。如果 N 的值很大（比如获取 1000 个用户），就会产生 1001 次查询，这对数据库造成巨大的、不必要的压力，并显著增加应用程序的响应时间。每一次数据库交互都有网络延迟和数据库处理的开销，N+1 次查询会将这些开销放大 N 倍。</p> +<p>在这个场景下，总共执行了 1 + 10 = 11 次数据库查询。如果 N 的值很大（比如获取 1000 个用户），就会产生 1001 次查询，这对数据库造成巨大的、不必要的压力，并显著增加应用程序的响应时间。每一次数据库交互都有网络延迟和数据库处理的开销，N+1 次查询会将这些开销放大 N 倍。</p> <p>N+1 问题通常源于 ORM 处理关联数据的方式，特别是与“懒加载”（Lazy Loading）相关的策略。懒加载是指只有在显式访问关联属性时，ORM 才会去数据库加载这些数据。虽然这在某些情况下可以避免加载不需要的数据，但如果在循环中访问关联属性，就很容易触发 N+1 问题。</p> <p>然而，问题的根源在于没有有效地预先加载（或批量加载）所需的关联数据。即使不使用严格意义上的懒加载，如果 ORM 在处理关联查询时不够智能，采用了逐个获取关联对象的策略，同样会产生 N+1 查询。</p> <p>在 Prisma 出现之前或在其他 ORM 中，解决 N+1 问题常见的方法包括：</p> @@ -212,14 +214,16 @@ <p>Prisma ORM 在设计上就考虑了 N+1 问题，并提供了一种既方便开发者又高效的解决方案。当使用 Prisma Client 查询数据并需要包含关联模型时，Prisma 会自动优化查询，避免产生 N+1 查询，主要通过关系查询（Relation Queries）中的 <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 不会 生成 N+1 个 SQL 查询。而是首先会分析请求，并将其转化为数量非常有限的高效 SQL 查询。对于上面这个一对多关系的 <code>include</code> 查询，Prisma 通常会执行以下两步（类似于批量加载策略）：</p> <ol> -<li>查询父模型: 获取所有 <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>查询关联的子模型: 使用上一步获取到的所有用户 <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> +<li>查询父模型: 获取所有 <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>查询关联的子模型: 使用上一步获取到的所有用户 <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> +<h2 id="Refs"><a class="header-anchor" href="#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> diff --git a/2025/04/13/uuidv7-rdbms/index.html b/2025/04/13/uuidv7-rdbms/index.html index 6872f514..48d66555 100644 --- a/2025/04/13/uuidv7-rdbms/index.html +++ b/2025/04/13/uuidv7-rdbms/index.html @@ -193,23 +193,31 @@ </div> <div class="post-content"> <p>UUID v7 之所以能显著提升关系型数据库（尤其是采用聚集索引的 MySQL InnoDB）在插入和查询速度，主要归功于它在 ID 中引入了“可排序的时间戳前缀”，从而大幅减少了 B-Tree 索引页分裂（page split）和数据碎片化。这里分几点来说：</p> -<p>一、 聚集索引（Clustered Index）的插入机制 </p> +<p>一、 聚集索引（Clustered Index）的插入机制</p> <p>在 InnoDB 中，聚集索引的叶子节点同时存储了行数据，且按照索引键（主键）顺序物理排序。</p> -<p>当新记录的主键完全随机（如 UUID v4）时，每次插入都会随机落在 B-Tree 的不同叶子页，导致频繁的页分裂和指针重排，而页分裂和随机 I/O 会带来大量的磁盘写放大和缓存抖动（cache churn），削弱吞吐并拉高延迟。</p> -<p>二、UUID v7 的时间排序特性 </p> -<p>UUID v7 在高位（前 48 位）嵌入了以毫秒级精度的 Unix 时间戳，剩下的位用于随机数或序列号，这样生成的 ID 保持全局唯一性的同时，随着时间自然递增（即“近似单调递增”），新插入的记录几乎总是追加到 B-Tree 的最右端叶子节点。 </p> -<p>参见 dbaplus.cn 的分析： </p> +<p>当新记录的主键完全随机（如 UUID v4）时，每次插入都会随机落在 B-Tree 的不同叶子页，导致频繁的页分裂和指针重排，而页分裂和随机 I/O 会带来大量的磁盘写放大和缓存抖动（cache churn），削弱吞吐并拉高延迟。</p> +<p>二、UUID v7 的时间排序特性</p> +<p>UUID v7 在高位（前 48 位）嵌入了以毫秒级精度的 Unix 时间戳，剩下的位用于随机数或序列号，这样生成的 ID 保持全局唯一性的同时，随着时间自然递增（即“近似单调递增”），新插入的记录几乎总是追加到 B-Tree 的最右端叶子节点。</p> +<p>参见 <a target="_blank" rel="noopener" href="http://dbaplus.cn">dbaplus.cn</a> 的分析：</p> <blockquote> <p>“UUID v7 的创新之处在于其时间排序特性，它在前 48 位中嵌入了以毫秒为单位的 Unix 时间戳……可能在插入和查询操作上提供更好的性能”【1】。</p> </blockquote> -<p>三、降低页分裂与碎片化 </p> +<p>三、降低页分裂与碎片化</p> <p>顺序或近似顺序的主键能使叶子节点连续增长，极少触发页分裂，并且更少的页分裂意味着更低的写放大（write amplification）和更稳定的插入延迟，同时，减少了空洞和链表重排，提高了磁盘和内存缓存的命中率。</p> -<p>四、提升查询局部性与缓存命中 </p> -<p>因为数据物理上是按时间顺序紧凑写入，时间范围查询（如 “最近 1 小时的日志”）可以快速定位连续的叶子页，I/O 更聚集，内存缓冲池（buffer pool）或操作系统页缓存能更有效地缓存最近热数据，进一步加速查询。</p> +<p>四、提升查询局部性与缓存命中</p> +<p>因为数据物理上是按时间顺序紧凑写入，时间范围查询（如 “最近 1 小时的日志”）可以快速定位连续的叶子页，I/O 更聚集，内存缓冲池（buffer pool）或操作系统页缓存能更有效地缓存最近热数据，进一步加速查询。</p> <hr> -<p>Rimon Tawadrous 在其 GitHub repo 中的测试，对比 100 万条逐条插入实验，UUID v7 相较 UUID v4 在单线程插入上速度快约 3.24%，多线程下更可观【1】。 </p> +<p>Rimon Tawadrous 在其 GitHub repo 中的测试，对比 100 万条逐条插入实验，UUID v7 相较 UUID v4 在单线程插入上速度快约 3.24%，多线程下更可观【1】。</p> <hr> -<p>参考链接<br>[1] “为什么 UUID 7 比 UUID 4 更适合作为 RDBMS 的聚集索引？” dbaplus.cn<br> <a target="_blank" rel="noopener" href="https://dbaplus.cn/news-160-6313-1.html">https://dbaplus.cn/news-160-6313-1.html</a><br>[2] “PostgreSQL and UUID as primary key” maciejwalkowiak<br> <a target="_blank" rel="noopener" href="https://maciejwalkowiak.com/blog/postgres-uuid-primary-key/">https://maciejwalkowiak.com/blog/postgres-uuid-primary-key/</a><br>[3] “Optimised UUIDs in mysql” stitcher<br> <a target="_blank" rel="noopener" href="https://stitcher.io/blog/optimised-uuids-in-mysql">https://stitcher.io/blog/optimised-uuids-in-mysql</a><br>[3] “Storing UUID Values in MySQL” percona<br> <a target="_blank" rel="noopener" href="https://www.percona.com/blog/store-uuid-optimized-way/">https://www.percona.com/blog/store-uuid-optimized-way/</a></p> +<p>参考链接<br> +[1] “为什么 UUID 7 比 UUID 4 更适合作为 RDBMS 的聚集索引？” <a target="_blank" rel="noopener" href="http://dbaplus.cn">dbaplus.cn</a><br> +<a target="_blank" rel="noopener" href="https://dbaplus.cn/news-160-6313-1.html">https://dbaplus.cn/news-160-6313-1.html</a><br> +[2] “PostgreSQL and UUID as primary key” maciejwalkowiak<br> +<a target="_blank" rel="noopener" href="https://maciejwalkowiak.com/blog/postgres-uuid-primary-key/">https://maciejwalkowiak.com/blog/postgres-uuid-primary-key/</a><br> +[3] “Optimised UUIDs in mysql” stitcher<br> +<a target="_blank" rel="noopener" href="https://stitcher.io/blog/optimised-uuids-in-mysql">https://stitcher.io/blog/optimised-uuids-in-mysql</a><br> +[3] “Storing UUID Values in MySQL” percona<br> +<a target="_blank" rel="noopener" href="https://www.percona.com/blog/store-uuid-optimized-way/">https://www.percona.com/blog/store-uuid-optimized-way/</a></p> </div> diff --git a/2025/04/20/linux-amd-screen-boom/index.html b/2025/04/20/linux-amd-screen-boom/index.html index 78a8c76f..41c36deb 100644 --- a/2025/04/20/linux-amd-screen-boom/index.html +++ b/2025/04/20/linux-amd-screen-boom/index.html @@ -195,7 +195,6 @@ <p>不知道我们所说的闪烁是不是一个东西，在我的笔记本上，闪烁是指偶发的屏幕中出现部分彩色雪花。</p> <p>我使用 Pop!_OS，此发行版使用 EFI 启动，故而可以在 <code>/boot/efi/loader/loader.conf</code> 中加上:</p> <figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">options quiet splash amdgpu.dcdebugmask=0x10 amdgpu.sg_display=0</span><br></pre></td></tr></table></figure> - <p>对于其他使用 EFI 或 GRUB 启动的发行版也可以添加类似的参数尝试尝试。</p> </div> |