deploy: a0dad147ccf1d831c5f48d8d0e868160a23cc92c

1 files changed, 308 insertions, 0 deletions

diff --git a/2025/04/13/uuidv7-rdbms/index.html b/2025/04/13/uuidv7-rdbms/index.html
new file mode 100644
index 00000000..1a8b513a
--- /dev/null
+++ b/2025/04/13/uuidv7-rdbms/index.html
@@ -0,0 +1,308 @@
+<!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>
+                                        
+                                            UUID v7 是如何加快 RDBMS 索引速度的 | 
+                                                    暮秋小屋
+                                    </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">
+            
+            
+                UUID v7 是如何加快 RDBMS 索引速度的
+            
+            
+        </div>
+        <span class="post-date">
+            Apr 13, 2025
+        </span>
+    </div>
+    <div class="post-img">
+        
+            <div class="h-line-primary"></div>
+              
+    </div>
+</div>
+    <div class="post-content">
+    <p>UUID v7 之所以能显著提升关系型数据库（尤其是采用聚集索引的 MySQL InnoDB）在插入和查询速度，主要归功于它在 ID 中引入了“可排序的时间戳前缀”，从而大幅减少了 B-Tree 索引页分裂（page split）和数据碎片化。这里分几点来说：</p>
+<p>一、 聚集索引（Clustered Index）的插入机制  </p>
+<p>在 InnoDB 中，聚集索引的叶子节点同时存储了行数据，且按照索引键（主键）顺序物理排序。</p>
+<p>当新记录的主键完全随机（如 UUID v4）时，每次插入都会随机落在 B-Tree 的不同叶子页，导致频繁的页分裂和指针重排，而页分裂和随机 I&#x2F;O 会带来大量的磁盘写放大和缓存抖动（cache churn），削弱吞吐并拉高延迟。</p>
+<p>二、UUID v7 的时间排序特性  </p>
+<p>UUID v7 在高位（前 48 位）嵌入了以毫秒级精度的 Unix 时间戳，剩下的位用于随机数或序列号，这样生成的 ID 保持全局唯一性的同时，随着时间自然递增（即“近似单调递增”），新插入的记录几乎总是追加到 B-Tree 的最右端叶子节点。  </p>
+<p>参见 dbaplus.cn 的分析：  </p>
+<blockquote>
+<p>“UUID v7 的创新之处在于其时间排序特性，它在前 48 位中嵌入了以毫秒为单位的 Unix 时间戳……可能在插入和查询操作上提供更好的性能”【1】。</p>
+</blockquote>
+<p>三、降低页分裂与碎片化  </p>
+<p>顺序或近似顺序的主键能使叶子节点连续增长，极少触发页分裂，并且更少的页分裂意味着更低的写放大（write amplification）和更稳定的插入延迟，同时，减少了空洞和链表重排，提高了磁盘和内存缓存的命中率。</p>
+<p>四、提升查询局部性与缓存命中  </p>
+<p>因为数据物理上是按时间顺序紧凑写入，时间范围查询（如 “最近 1 小时的日志”）可以快速定位连续的叶子页，I&#x2F;O 更聚集，内存缓冲池（buffer pool）或操作系统页缓存能更有效地缓存最近热数据，进一步加速查询。</p>
+<hr>
+<p>Rimon Tawadrous 在其 GitHub repo 中的测试，对比 100 万条逐条插入实验，UUID v7 相较 UUID v4 在单线程插入上速度快约 3.24%，多线程下更可观【1】。  </p>
+<hr>
+<p><strong>参考链接</strong><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>
+
+</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/20/linux-amd-screen-boom/">Prev</a>
+                </div>
+            
+        </div>
+        <div class="next-item">
+            
+                <div class="icon arrow-right"></div>
+                <div class="post-link">
+                  <a href="/2025/04/02/N-1-selects-problem-%E4%B8%8E-Prisma-ORM/">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>