天元小站
实用工具箱
干货教程
关于我们
ICP备案号:
苏ICP备2026032856号-1
返回教程列表
MySQL 索引优化实战指南
tianyuan
Mon May 25 23:36:42 UTC 2026
10
次浏览
后端
<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 "%关键词%"</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 = "test"; </code></pre> <h3>重点查看 4 个字段</h3> <ul> <li><p><strong>type</strong>:访问类型,优先级最高,完整排序:<br><code>system > const > eq_ref > ref > ref_or_null > range > index > ALL</code></p> <ul> <li>常用最优区间:<code>system > const > eq_ref > ref</code>(正常命中索引)</li> <li><code>ref_or_null</code>:特殊场景(索引列同时匹配普通值与 <code>NULL</code>),日常较少见</li> <li>较差区间:<code>range > index > 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) = '2025-01-01' -- 索引失效 </code></pre> <ol start="2"> <li><strong>模糊查询以 % 开头</strong></li> </ol> <pre><code class="language-sql">WHERE name LIKE '%test' -- 索引失效 WHERE name LIKE 'test%' -- 正常走索引 </code></pre> <ol start="3"> <li><strong>使用 !=、<>、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 = "张三"; </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>