MySQL 索引优化实战指南

tianyuan Tue May 26 07:36:42 CST 2026 42 次浏览后端

<p>从原理到实践，掌握 MySQL 索引优化的核心技巧，告别慢查询，提升数据库性能。</p>
<h2>一、MySQL 常见索引类型</h2>
<p>日常开发中，我们主要用到 3 种索引，搞清楚它们的适用场景，索引就学会一半了。</p>
<ul>
<li><p><strong>B+Tree 索引</strong><br>最常用、默认索引，支持<strong>范围查询、排序、分组</strong>，几乎所有业务场景都用它。</p>
</li>
<li><p><strong>哈希索引</strong><br>只支持<strong>精准等值查询</strong>（=、in），不支持范围和排序，Memory 引擎常用。</p>
</li>
<li><p><strong>全文索引</strong><br>专门用于<strong>长文本搜索</strong>，<code>like &quot;%关键词%&quot;</code> 这类场景 B+Tree 索引效率极低，优先使用全文索引。</p>
<blockquote>
<p>补充：<strong>MySQL 5.7+ 版本 InnoDB 引擎才正式支持全文索引</strong>，低版本仅 MyISAM 引擎可用。</p>
</blockquote>
</li>
</ul>
<h2>二、最左前缀原则（联合索引核心）</h2>
<p>联合索引 <code>(a, b, c)</code> 必须遵循<strong>最左前缀匹配</strong>：</p>
<ul>
<li><p>能命中索引：</p>
<ul>
<li><code>where a = ?</code></li>
<li><code>where a = ? and b = ?</code></li>
<li><code>where a = ? and b = ? and c = ?</code></li>
</ul>
</li>
<li><p>不能命中索引：</p>
<ul>
<li><code>where b = ?</code></li>
<li><code>where c = ?</code></li>
<li><code>where a = ? and c = ?</code>（仅能命中 a 列索引）</li>
</ul>
</li>
</ul>
<p>一句话：<strong>联合索引必须从最左边开始连续匹配，中间断列则后续列无法走索引。</strong></p>
<h2>三、EXPLAIN 执行计划分析（慢查询必备）</h2>
<p>通过 <code>EXPLAIN</code> 能一眼看出 SQL 有没有用到索引、执行效率如何。</p>
<pre><code class="language-sql">EXPLAIN SELECT * FROM users WHERE name = &quot;test&quot;;
</code></pre>
<h3>重点查看 4 个字段</h3>
<ul>
<li><p><strong>type</strong>：访问类型，优先级最高，完整排序：<br><code>system &gt; const &gt; eq_ref &gt; ref &gt; ref_or_null &gt; range &gt; index &gt; ALL</code></p>
<ul>
<li>常用最优区间：<code>system &gt; const &gt; eq_ref &gt; ref</code>（正常命中索引）</li>
<li><code>ref_or_null</code>：特殊场景（索引列同时匹配普通值与 <code>NULL</code>），日常较少见</li>
<li>较差区间：<code>range &gt; index &gt; ALL</code>，<strong>ALL 代表全表扫描，必须优先优化</strong></li>
</ul>
</li>
<li><p><strong>key</strong>：SQL 实际使用的索引名称，值为 <code>NULL</code> 表示未使用任何索引。</p>
</li>
<li><p><strong>rows</strong>：存储引擎预估需要扫描的数据行数，数值越小性能越好。</p>
</li>
<li><p><strong>Extra</strong>：额外执行信息</p>
<ul>
<li><code>Using index</code>：命中<strong>覆盖索引</strong>，无需回表，性能极佳</li>
<li><code>Using where</code>：使用 <code>where</code> 条件过滤数据，不代表一定使用索引</li>
<li><code>Using filesort / Using temporary</code>：出现该标记说明存在排序/临时表问题，需要立即优化</li>
</ul>
</li>
</ul>
<h2>四、哪些情况会导致索引失效？（高频踩坑）</h2>
<p>以下是开发中极易踩坑的场景，也是索引失效的主要原因，可作为优化高危信号参考：</p>
<ol>
<li><strong>对索引列做运算、函数、隐式类型转换</strong></li>
</ol>
<pre><code class="language-sql">WHERE age + 1 = 20   -- 索引失效
WHERE DATE(create_time) = &#39;2025-01-01&#39;  -- 索引失效
</code></pre>
<ol start="2">
<li><strong>模糊查询以 % 开头</strong></li>
</ol>
<pre><code class="language-sql">WHERE name LIKE &#39;%test&#39;  -- 索引失效
WHERE name LIKE &#39;test%&#39;  -- 正常走索引
</code></pre>
<ol start="3">
<li><strong>使用 !=、&lt;&gt;、is not null、not in</strong><br>  该说法并非绝对：</li>
</ol>
<ul>
<li><code>is not null</code>：若字段建立索引且本身允许为 <code>NULL</code>，<strong>部分场景仍可走索引</strong>，但效率不稳定；</li>
<li><code>not in</code>：当查询集合中存在 <code>NULL</code> 值时，会直接导致查询异常、索引失效；</li>
<li>整体建议：这类语法容易让索引退化为范围查询或全表扫描，日常开发尽量规避。</li>
</ul>
<ol start="4">
<li><p><strong>联合索引不满足最左前缀规则</strong><br>  跳过首列、中间断列，后续字段均无法使用索引。</p>
</li>
<li><p><strong>MySQL 优化器主动放弃索引</strong><br>  表数据量极小、索引区分度极低时，优化器会判定全表扫描效率更高，自动放弃索引。</p>
</li>
</ol>
<h2>五、索引最佳实践（企业级通用规范）</h2>
<h3>1. 适合建立索引的场景</h3>
<ul>
<li><code>where</code> 子句中高频作为查询条件的字段</li>
<li><code>group by</code> / <code>order by</code> / <code>distinct</code> 排序分组字段</li>
<li>多表联查 <code>join</code> 的关联字段</li>
<li>区分度高的字段（唯一ID、手机号、用户名等）；性别、状态这类低区分度字段不建议建索引</li>
</ul>
<h3>2. 不建议建立索引的场景</h3>
<ul>
<li>频繁执行 <code>insert/update/delete</code> 的字段（索引会增加数据更新开销）</li>
<li>区分度极低的字段（如性别、状态、固定分类）</li>
<li>数据量小于千行的小型表</li>
<li>业务中几乎不会作为查询条件的字段</li>
</ul>
<h3>3. 联合索引设计技巧</h3>
<ul>
<li>等值查询字段放前面，范围查询字段放后面</li>
<li>区分度高的字段靠前排列</li>
<li>单条联合索引字段数量<strong>不建议超过 3 个</strong></li>
</ul>
<pre><code class="language-sql">-- 合理设计
INDEX idx_name_age (name, age)

-- 不合理设计（范围字段前置，截断后续索引）
INDEX idx_age_name (age, name)
</code></pre>
<h2>六、覆盖索引（性能提升神器）</h2>
<p><strong>查询所需字段全部包含在索引中</strong>，数据库无需回表查询原始数据，大幅提升性能。</p>
<p>示例：</p>
<pre><code class="language-sql">-- 表已建立联合索引：(name, age)
SELECT name, age FROM user WHERE name = &quot;张三&quot;;
</code></pre>
<p>执行计划 <code>Extra</code> 字段会显示 <code>Using index</code>，代表成功命中覆盖索引。</p>
<h2>七、避免索引过量</h2>
<ul>
<li>单张数据表索引总数<strong>不建议超过 5 个</strong></li>
<li>联合索引字段数控制在 3~4 个以内</li>
<li>索引可以加速查询，但会降低 <code>insert/update/delete</code> 写入性能，按需创建即可</li>
</ul>
<h2>八、慢查询优化通用步骤（实战流程）</h2>
<ol>
<li>使用 <code>EXPLAIN</code> 分析执行计划，判断是否正常走索引</li>
<li>检查 <code>type</code> 字段，若为 <code>ALL</code>（全表扫描）优先优化</li>
<li>校验联合索引是否遵循最左前缀原则</li>
<li>排查索引列是否存在运算、函数、类型转换等问题</li>
<li>评估并创建<strong>覆盖索引</strong>，减少回表操作</li>
<li>调整 SQL 写法，规避索引失效场景</li>
<li>终极优化：单表数据量过大时，考虑<strong>分表、读写分离</strong>架构方案</li>
</ol>
<h2>九、总结（一句话速记）</h2>
<ul>
<li>B+Tree 索引是业务主力</li>
<li>联合索引严格遵守最左前缀</li>
<li>调优优先看 <code>EXPLAIN</code> 的 type、key、rows、Extra</li>
<li>禁止在索引列上做运算和函数操作</li>
<li>覆盖索引是查询性能优化利器</li>
<li>索引并非越多越好，平衡查询与写入性能</li>
</ul>