对线面试官系列：搞懂MySQL 回表机制，看这一篇就够了！
<p>@</p><div class="toc"><div class="toc-container-header">目录</div><ul><li>前言</li><li>背景</li><li>基础知识</li><li>回表定义</li><li>经典示例:</li><li>回表的影响</li><li>如何避免回表？<ul><li>1. 使用覆盖索引</li><li>2. 使用聚簇索引</li><li>3. 优化查询条件</li><li>4. 减少SELECT *的使用</li><li>5. 使用联合索引</li><li>6. 使用子查询或临时表</li><li>7. 定期优化和重建索引</li><li>8. EXPLAIN分析查询计划</li><li>9.调整表结构</li><li>10. 使用缓存</li></ul></li><li>回表总结</li></ul></div><p></p><hr>
<h1 id="前言">前言</h1>
<p><font color="#999AAA">请各大网友尊重本人原创知识分享，谨记本人博客：南国以南i、</font>微信公众号：<strong><code>白码梦想家</code></strong></p>
<p><code>提示：以下是本篇文章正文内容，下面案例可供参考</code></p>
<h1 id="背景">背景</h1>
<blockquote>
<p>Hello 朋友们，接下来为大家开启，<strong>面试题相关系列☞《对线面试官》</strong> <code>自信出击，让 offer 手到擒来！！！</code></p>
</blockquote>
<h1 id="基础知识">基础知识</h1>
<blockquote>
<ul>
<li><strong>聚簇索引：</strong> 聚簇索引的<code>叶子节点存储的是整行数据</code>，且数据的物理存储顺序与聚簇索引的顺序一致。</li>
<li><strong>非聚簇索引：</strong> 非聚簇索引的<code>叶子节点存储的是索引列的值和对应行的主键值</code>，而不是整行数据。</li>
</ul>
</blockquote>
<p><strong>聚族索引与非聚族索引区别：</strong></p>
<blockquote>
<p>叶节点是否存放一整行记录，<code>一个表中除了主键id字段是聚族索引，其它字段都是非聚族索引</code></p>
</blockquote>
<h1 id="回表定义">回表定义</h1>
<p>回表（Look-up）指的是在MySQL中， <strong><code>在使用非聚集索引进行查询时，才会涉及回表问题 </code></strong> 。MySQL首先通过非聚簇索引找到对应的主键值，后利用这个主键值在聚簇索引中定位到具体的行，并获取所需的其他列数据。<br>
简单来说，回表是一种二次查找的操作，用于获取非聚集索引无法提供的其他列的值。</p>
<h1 id="经典示例">经典示例:</h1>
<p>假设有一个名为users的表，其中包含三列：id（主键，聚簇索引）、name（非聚簇索引）和age。如果执行查询<code>SELECT * FROM users WHERE name = 'Alice';</code>，则MySQL会首先通过name列的非聚簇索引找到所有name为'Alice'的记录的主键值，然后利用这些主键值在id列的聚簇索引中查找并返回整行数据。<br>
<img src="https://i-blog.csdnimg.cn/direct/eaf82fda4a5045a2bb42ebf5a5fc7e17.png" alt="索引" loading="lazy"></p>
<blockquote>
<p>由图可知流程，首先从非聚簇索引开始寻找聚簇索引，找到非聚簇索引上的聚簇索引后，就会到聚簇索引的B+树上进行查询，通过聚簇索引B+树找到完整的数据。该过程比较专业的叫法也被称为 <strong><code>“回表”</code></strong>。</p>
</blockquote>
<h1 id="回表的影响">回表的影响</h1>
<p>回表操作会增加查询的成本，因为它需要额外的I/O操作来访问聚簇索引并获取整行数据。特别是在数据量较大的情况下，<strong>回表操作可能会对查询性能产生显著影响</strong>。</p>
<h1 id="如何避免回表">如何避免回表？</h1>
<p>如果无法避免回表操作，可以通过以下方法进行优化：</p>
<h2 id="1-使用覆盖索引">1. 使用覆盖索引</h2>
<p><strong>定义：</strong><code>覆盖索引（Covering Index）的方式。覆盖索引是指创建一个包含了查询所需的所有列的索引</code>，这样就可以直接从索引中获取所需的数据，而无需回到表中查找。使用覆盖索引可以减少I/O操作和提高查询性能。</p>
<p><strong>做法：</strong> 在创建索引时，确保索引包含了查询中需要的所有列。这样，MySQL可以直接从索引中获取所需的数据，而无需回表。</p>
<p><strong>注意：</strong> <code>覆盖索引（Covering Index）和索引覆盖（Index Covering）实际上是同一概念的不同表述方式，它们指的是同一个优化技术。因此，从本质上来说，覆盖索引和索引覆盖没有区别。</code></p>
<p><strong>应用场景：</strong></p>
<ol>
<li>当查询的列是索引的一部分时，如果这些列足以满足查询需求，就可以使用覆盖索引。</li>
<li>在进行分页查询时，如果查询条件已经包含在索引中，并且只需要获取索引中的部分列数据，就可以使用覆盖索引来提高查询效率。</li>
<li>在进行统计查询时，如果统计信息可以通过索引直接计算得出，也可以使用覆盖索引来减少数据访问量。</li>
</ol>
<p><strong>示例：</strong></p>
<pre><code class="language-sql">CREATE INDEX idx_name_age ON table_name(name, age);
SELECT name, age FROM table_name WHERE name = 'Alice';
</code></pre>
<h2 id="2-使用聚簇索引">2. 使用聚簇索引</h2>
<p><strong>定义：</strong> 聚簇索引是一种特殊的索引，它将数据和索引存储在一起。在InnoDB存储引擎中，主键索引就是聚簇索引。</p>
<p><strong>做法：</strong> 确保查询中频繁使用的列是主键或包含在聚簇索引中。这样，当通过这些列进行查询时，可以直接从聚簇索引中获取数据，无需回表。</p>
<p><strong>注意：</strong> <code>每个表只能有一个聚簇索引，因为数据只能有一种物理存储顺序。</code></p>
<h2 id="3-优化查询条件">3. 优化查询条件</h2>
<p>做法：尽量避免在查询条件中使用不在索引列中的列，因为这会导致回表查询。优化查询条件，使之尽可能使用索引列。</p>
<p>示例：</p>
<pre><code class="language-sql">-- 优化前（假设没有针对customer_name的索引）
SELECT * FROM customers WHERE customer_name = 'John Doe';
-- 优化后（添加索引）
ALTER TABLE customers ADD INDEX (customer_name);
SELECT * FROM customers WHERE customer_name = 'John Doe';
</code></pre>
<h2 id="4-减少select-的使用">4. 减少SELECT *的使用</h2>
<p><strong>做法：</strong> 尽量避免使用SELECT *，只查询需要的列。这样可以减少数据传输量，提高查询效率，并在某些情况下避免回表。</p>
<p><strong>示例：</strong></p>
<pre><code class="language-sql">-- 优化前
SELECT * FROM table_name WHERE id = 1;
-- 优化后
SELECT id, name FROM table_name WHERE id = 1;
</code></pre>
<h2 id="5-使用联合索引">5. 使用联合索引</h2>
<p><strong>定义：</strong> 联合索引是将多个列组合成一个索引。</p>
<p><strong>做法：</strong> 将查询中经常一起出现的列组合成联合索引。这样，当这些列一起出现在查询条件中时，可以减少回表次数。</p>
<p>示例：</p>
<pre><code class="language-sql">CREATE INDEX idx_name_age ON table_name(name, age);
SELECT * FROM table_name WHERE name = 'Alice' AND age = 20;
</code></pre>
<h2 id="6-使用子查询或临时表">6. 使用子查询或临时表</h2>
<p><strong>做法：</strong> 将需要查询的数据先存储在临时表或子查询中，然后再进行关联查询。这样可以减少回表次数，特别是当关联查询涉及多个表时。</p>
<h2 id="7-定期优化和重建索引">7. 定期优化和重建索引</h2>
<p><strong>做法：</strong> 随着数据的更新和增长，索引可能会变得不再紧凑，影响查询性能。定期优化和重建索引可以保持索引性能。</p>
<h2 id="8-explain分析查询计划">8. EXPLAIN分析查询计划</h2>
<p><strong>做法：</strong> 通过EXPLAIN语句分析查询计划，了解查询是如何执行的，从而找到优化的方法，减少回表查询的次数。</p>
<h2 id="9调整表结构">9.调整表结构</h2>
<p><strong>做法：</strong>如果回表操作非常频繁，可以考虑调整表结构，将需要查询的列放在索引中，或者使用聚簇索引来减少回表操作。</p>
<h2 id="10-使用缓存">10. 使用缓存</h2>
<p><strong>做法：</strong> 如果查询的数据具有一定的重复性，可以考虑使用缓存来减少回表操作。</p>
<p>通过以上方法，可以有效地避免MySQL中的回表操作，提高查询性能和数据库的整体性能。</p>
<h1 id="回表总结">回表总结</h1>
<p>回表是MySQL数据库中一种常见的操作，用于获取非聚集索引无法提供的其他列的值。<code>回表操作会增加额外的I/O操作和访问时间，影响查询的性能</code>。为了避免回表操作，可以使用<strong>覆盖索引</strong>的方式。如果无法避免回表操作，可以通过<strong>优化查询语句</strong>、<strong>调整表结构</strong>和<strong>使用缓存</strong>等方式来优化回表操作。在实际应用中，需要根据具体的场景和需求来选择合适的优化策略。</p>
<hr style="border: solid; width: 100px; height: 1px" color="#000000" size="1"">
<p>我是南国以南i记录点滴每天成长一点点，学习是永无止境的！转载请附原文链接！！！</p>
<p>参考链接、参考链接、</p><br><br>
来源：https://www.cnblogs.com/bgyb/p/19420823
頁:
[1]