深入理解MySQL联合索引最左匹配原则
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">什么是联合索引？</a></li><li><a href="#_label1">什么是最左匹配原则？</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_0">核心要点：</a></li></ul><li><a href="#_label2">举例说明</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_1">✅场景一：完全匹配最左列</a></li><li><a href="#_lab2_2_2">✅场景二：匹配所有列</a></li><li><a href="#_lab2_2_3">✅场景三：匹配最左连续列</a></li><li><a href="#_lab2_2_4">❌场景四：跳过最左列</a></li><li><a href="#_lab2_2_5">⚠️场景五：包含最左列，但中间有断档</a></li><li><a href="#_lab2_2_6">⚠️场景六：最左列是范围查询</a></li></ul><li><a href="#_label3">总结与最佳实践</a></li><ul class="second_class_ul"></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>什么是联合索引？</h2><p>首先，要理解最左匹配原则，得先知道什么是联合索引。</p>
<ul><li><strong>单列索引</strong>：只针对一个表列创建的索引。例如，为 <code>users</code> 表的 <code>name</code> 字段创建一个索引。</li><li><strong>联合索引</strong>：也叫复合索引，是针对<strong>多个表列</strong>创建的索引。例如，为 <code>users</code> 表的 <code>(last_name, first_name)</code> 两个字段创建一个联合索引。</li></ul>
<p>这个索引的结构可以想象成类似于电话簿或字典。电话簿是先按<strong>姓氏</strong>排序，在姓氏相同的情况下，再按<strong>名字</strong>排序。你无法直接跳过姓氏，快速找到一个特定的名字。</p>
<p class="maodian"><a name="_label1"></a></p><h2>什么是最左匹配原则？</h2>
<p><strong>最左匹配原则</strong>指的是：在使用联合索引进行查询时，<strong>MySQL/SQL数据库从索引的最左前列开始，并且不能跳过中间的列，一直向右匹配，直到遇到范围查询（</strong><code>></code>、<code><</code>、<code>BETWEEN</code>、<code>LIKE</code><strong>）就会停止匹配。</strong></p>
<p>这个原则决定了你的 SQL 查询语句<strong>是否能够使用</strong>以及<strong>如何高效地使用</strong>这个联合索引。</p>
<p class="maodian"><a name="_lab2_1_0"></a></p><h3>核心要点：</h3>
<ol><li><strong>从左到右</strong>：索引的使用必须从最左边的列开始。</li><li><strong>不能跳过</strong>：不能跳过联合索引中的某个列去使用后面的列。</li><li><strong>范围查询右停止</strong>：如果某一列使用了范围查询，那么它右边的列将无法使用索引进行进一步筛选。</li></ol>
<p class="maodian"><a name="_label2"></a></p><h2>举例说明</h2>
<p>假设我们有一个 <code>users</code> 表，并创建了一个联合索引 <code>idx_name_age</code>，包含 <code>(last_name, age)</code> 两个字段。</p>
<table id="e42LT"><tbody><tr><td><p>id</p></td><td><p>last_name</p></td><td><p>first_name</p></td><td><p>age</p></td><td><p>city</p></td></tr><tr><td><p>1</p></td><td><p>Wang</p></td><td><p>Lei</p></td><td><p>20</p></td><td><p>Beijing</p></td></tr><tr><td><p>2</p></td><td><p>Zhang</p></td><td><p>Wei</p></td><td><p>25</p></td><td><p>Shanghai</p></td></tr><tr><td><p>3</p></td><td><p>Wang</p></td><td><p>Fang</p></td><td><p>22</p></td><td><p>Guangzhou</p></td></tr><tr><td><p>4</p></td><td><p>Li</p></td><td><p>Na</p></td><td><p>30</p></td><td><p>Shenzhen</p></td></tr><tr><td><p>5</p></td><td><p>Zhang</p></td><td><p>San</p></td><td><p>28</p></td><td><p>Beijing</p></td></tr></tbody></table>
<p>索引 <code>idx_name_age</code> 在磁盘上大致是这样排序的（先按 <code>last_name</code> 排序，<code>last_name</code> 相同再按 <code>age</code> 排序）：</p>
<blockquote><p>(Li, 30)<br />(Wang, 20)<br />(Wang, 22)<br />(Zhang, 25)<br />(Zhang, 28)</p></blockquote>
<p>现在，我们来看不同的查询场景：</p>
<p class="maodian"><a name="_lab2_2_1"></a></p><h3>✅场景一：完全匹配最左列</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM users WHERE last_name = 'Wang';</pre></div>
<ul><li><strong>分析</strong>：查询条件包含了索引的最左列 <code>last_name</code>。</li><li><strong>索引使用情况</strong>：✅ <strong>可以使用索引</strong>。数据库可以快速在索引树中找到所有 <code>last_name = 'Wang'</code> 的记录（<code>(Wang, 20)</code> 和 <code>(Wang, 22)</code>）。</li></ul>
<p class="maodian"><a name="_lab2_2_2"></a></p><h3>✅场景二：匹配所有列</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM users WHERE last_name = 'Wang' AND age = 22;</pre></div>
<ul><li><strong>分析</strong>：查询条件包含了索引的所有列，并且顺序与索引定义一致。</li><li><strong>索引使用情况</strong>：✅ <strong>可以高效使用索引</strong>。数据库先定位到 <code>last_name = 'Wang'</code>，然后在这些结果中快速找到 <code>age = 22</code> 的记录。</li></ul>
<p class="maodian"><a name="_lab2_2_3"></a></p><h3>✅场景三：匹配最左连续列</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM users WHERE last_name = 'Zhang';</pre></div>
<ul><li><strong>分析</strong>：虽然只用了 <code>last_name</code>，但它是索引的最左列。</li><li><strong>索引使用情况</strong>：✅ <strong>可以使用索引</strong>。和场景一类似。</li></ul>
<p class="maodian"><a name="_lab2_2_4"></a></p><h3>❌场景四：跳过最左列</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM users WHERE age = 25;</pre></div>
<ul><li><strong>分析</strong>：查询条件<strong>没有</strong>包含索引的最左列 <code>last_name</code>。</li><li><strong>索引使用情况</strong>：❌ <strong>无法使用索引</strong>。这就像让你在电话簿里直接找所有叫“伟”的人，你必须翻遍整个电话簿，也就是<strong>全表扫描</strong>。</li></ul>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>⚠️场景五：包含最左列，但中间有断档</h3>
<div class="jb51code"><pre class="brush:sql;">-- 假设我们有一个三个字段的索引 (col1, col2, col3)
-- 查询条件为 WHERE col1 = 'a' AND col3 = 'c';</pre></div>
<ul><li><strong>分析</strong>：虽然包含了最左列 <code>col1</code>，但跳过了 <code>col2</code> 直接查询 <code>col3</code>。</li><li><strong>索引使用情况</strong>：✅ <strong>部分使用索引</strong>。数据库只能使用 <code>col1</code> 来缩小范围，找到所有 <code>col1 = 'a'</code> 的记录。对于 <code>col3</code> 的过滤，它无法利用索引，需要在第一步的结果集中进行逐行筛选。</li></ul>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>⚠️场景六：最左列是范围查询</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT * FROM users WHERE last_name > 'Li' AND age = 25;</pre></div>
<ul><li>分析：最左列 last_name 使用了范围查询 >。</li><li>索引使用情况：✅ 部分使用索引。数据库可以使用索引找到所有 last_name > 'Li' 的记录（即从 Wang 开始往后的所有记录）。但是，对于 age = 25 这个条件，由于 last_name 已经是范围匹配，age 列在索引中是无序的，因此数据库无法再利用索引对 age 进行快速筛选，只能在 last_name > 'Li' 的结果集中逐行检查 age。</li></ul>
<p class="maodian"><a name="_label3"></a></p><h2>总结与最佳实践</h2>
<p><strong>最左匹配原则的本质</strong>是由索引的<strong>数据结构（B+Tree）</strong> 决定的。索引按照定义的字段顺序构建，所以必须从最左边开始才能利用其有序性。</p>
<p><strong>如何设计好的联合索引？</strong></p>
<ol><li><strong>高频查询优先</strong>：将最常用于 <code>WHERE</code> 子句的列放在最左边。</li><li><strong>等值查询优先</strong>：将经常进行等值查询（<code>=</code>）的列放在范围查询（<code>></code>， <code><</code>， <code>LIKE</code>）的列左边。</li><li><strong>覆盖索引</strong>：如果查询的所有字段都包含在索引中（即覆盖索引），即使不符合最左前缀，数据库也可能直接扫描索引来避免回表，但这通常发生在二级索引扫描中，效率依然不如最左匹配。</li></ol>
頁:
[1]