MySQL索引（四）：深入剖析索引失效的原因与优化方案
<blockquote><p>MySQL系列文章</p>
<p>本文是 MySQL索引系列的第四篇。在前三篇文章中，我们系统介绍了<strong>索引的数据结构</strong>、<strong>覆盖索引</strong>、<strong>最左前缀原则</strong>、<strong>索引下推</strong>等核心优化技术，以及<strong>字符串索引的优化方法</strong>。本文将深入分析索引失效的多种场景及其背后的原理，帮助你全面理解索引为何有时会“失效”，以及如何有效避免和优化这类问题。</p>
</blockquote>
<h2 id="一核心原理b树索引的有序性特性">一、核心原理：B+树索引的有序性特性</h2>
<p>要理解索引失效的原因，我们首先需要回顾<strong>B+树索引的核心特性——有序性</strong>。InnoDB存储引擎使用的B+树索引结构保持同一层兄弟节点的有序性，这是索引能够快速定位数据的根本原因。</p>
<p><strong>实际上，B+树提供的快速定位能力，正是来源于同一层兄弟节点的有序性</strong>。当我们执行等值查询或范围查询时，优化器可以借助这种有序性快速跳过不符合条件的数据块，极大减少需要扫描的数据量。</p>
<p>然而，当我们对索引字段进行函数操作时（下文都默认字段上有索引），问题就出现了：</p>
<pre><code class="language-sql">-- 示例：按月份查询订单数据
SELECT * FROM orders WHERE MONTH(create_time) = 7;
</code></pre>
<p>这条SQL语句的问题在于：<strong>B+树索引是按照create_time的原始值排序的，而不是按照MONTH(create_time)的计算结果排序的</strong>。如果计算month()函数，你会看到传入7的时候，在树的第一层就不知道该怎么办了，因为所有月份的日期值都被转换为1-12的数字，完全破坏了原有的有序性。</p>
<p><strong>也就是说，对索引字段做函数操作，可能会破坏索引值的有序性</strong>，因此优化器就决定<strong>放弃走树搜索功能</strong>，转而使用全索引扫描或全表扫描。</p>
<h2 id="二函数操作导致索引失效的详细分析">二、函数操作导致索引失效的详细分析</h2>
<h3 id="21-显式函数操作">2.1 显式函数操作</h3>
<p>最常见的索引失效场景就是在索引列上直接使用函数：</p>
<table>
<thead>
<tr>
<th>函数类型</th>
<th>失效示例</th>
<th>优化方案</th>
</tr>
</thead>
<tbody>
<tr>
<td>日期函数</td>
<td><code>WHERE YEAR(create_time) = 2023</code></td>
<td><code>WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'</code></td>
</tr>
<tr>
<td>字符串函数</td>
<td><code>WHERE SUBSTRING(name, 1, 4) = 'Johnoh'</code></td>
<td><code>WHERE name LIKE 'John%'</code></td>
</tr>
<tr>
<td>数学函数</td>
<td><code>WHERE ABS(salary) > 5000</code></td>
<td>避免存储负值，或使用<code>salary > 5000 OR salary < -5000</code></td>
</tr>
</tbody>
</table>
<blockquote>
<p><code>WHERE SUBSTRING(name, 1, 4) = 'Johnoh'</code>，在 MySQL 中表示：<br>
筛选出 <code>name</code> 列中「从第 1 个字符开始，连续截取 4 个字符，结果等于 'John'」的所有用户记录。</p>
</blockquote>
<h3 id="22-隐式类型转换">2.2 隐式类型转换</h3>
<p>MySQL的隐式类型转换也会在底层转换为函数操作，导致索引失效：</p>
<pre><code class="language-sql">-- order_no是VARCHAR类型，但用数字查询
SELECT * FROM orders WHERE order_no = 1001;
-- MySQL实际执行的是：
SELECT * FROM orders WHERE CAST(order_no AS SIGNED) = 1001;
</code></pre>
<p>MySQL字符转换默认规则：<strong>在MySQL中，字符串和数字做比较的话，是将字符串转换成数字</strong>。这个规则可以通过简单查询验证：</p>
<pre><code class="language-sql">SELECT '10' > 9;-- 返回1（true），说明字符串'10'被转换为数字10
</code></pre>
<p>如果MySQL将数字转换为字符串，按字符串比较'10'和'9'，应该返回0（false），因为'10'的第一个字符'1'比'9'小。但实际返回1，证实了MySQL的字符串到数字的转换规则。</p>
<h3 id="23-关键区别索引列-vs-查询值">2.3 关键区别：索引列 vs 查询值</h3>
<p><strong>重要区别</strong>：只有在索引列上做函数操作才会导致索引失效，在查询值上做函数操作不会影响索引使用：</p>
<pre><code class="language-sql">-- 不会导致索引失效（在查询值上做操作）
SELECT * FROM users WHERE id = 1000 + 1;
SELECT * FROM users WHERE age = '30';-- 字符串转数字
SELECT * FROM orders WHERE create_time = DATE_ADD('2023-01-01', INTERVAL 7 DAY);
-- 会导致索引失效（在索引列上做操作）
SELECT * FROM users WHERE id + 1 = 1001;
SELECT * FROM users WHERE CAST(age AS CHAR) = '30';
SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2023-06';
</code></pre>
<h2 id="三隐式字符编码转换的多表关联问题">三、隐式字符编码转换的多表关联问题</h2>
<p>在多表关联查询中，如果关联字段的字符集不同，也会导致隐式转换和索引失效：</p>
<pre><code class="language-sql">-- 订单表使用utf8mb4字符集
CREATE TABLE orders (
id INT PRIMARY KEY,
order_no VARCHAR(20) CHARACTER SET utf8mb4,
KEY idx_order_no (order_no)
);
-- 订单详情表使用utf8字符集
CREATE TABLE order_details (
id INT PRIMARY KEY,
order_no VARCHAR(20) CHARACTER SET utf8,
product_name VARCHAR(100),
KEY idx_order_no (order_no)
);
-- 关联查询
SELECT o.*, od.*
FROM orders o
JOIN order_details od ON o.order_no = od.order_no;
</code></pre>
<p>MySQL实际执行的是：</p>
<pre><code class="language-sql">SELECT o.*, od.*
FROM orders o
JOIN order_details od ON CONVERT(od.order_no USING utf8mb4) = o.order_no;
</code></pre>
<p>由于在order_details表的索引字段order_no上进行了CONVERT函数操作，导致该表的索引无法使用。</p>
<p><strong>到这里，你终于明确了，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因</strong>。</p>
<h2 id="四mysql优化器的保守行为">四、MySQL优化器的"保守"行为</h2>
<p><strong>MySQL的优化器确实有"偷懒"的嫌疑，即使简单地把where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写</strong>。</p>
<p>这意味着开发者需要主动优化查询语句，而不是依赖优化器自动优化：</p>
<pre><code class="language-sql">-- 优化器不会重写这个查询（导致全表扫描）
SELECT * FROM users WHERE id + 1 = 1001;
-- 需要手动重写为（可以使用索引）
SELECT * FROM users WHERE id = 1001 - 1;
</code></pre>
<p>这种"保守"行为提醒我们，作为开发者需要具备主动优化意识，不能完全依赖数据库优化器。</p>
<blockquote>
<p>前面4种情况其实说的都是同一个事情：<strong>对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，导致索引失效。</strong></p>
</blockquote>
<h2 id="五其他常见索引失效场景">五、其他常见索引失效场景</h2>
<p>除了函数操作，还有多种情况会导致索引无法有效使用：</p>
<h3 id="51-违反最左前缀原则">5.1 违反最左前缀原则</h3>
<p>对于复合索引 <code>(col1, col2, col3)</code>，以下查询无法充分利用索引：</p>
<table>
<thead>
<tr>
<th>查询条件</th>
<th>索引使用情况</th>
<th>优化建议</th>
</tr>
</thead>
<tbody>
<tr>
<td><code>WHERE col2 = 'a' AND col3 = 'b'</code></td>
<td>无法使用索引</td>
<td>调整查询条件或创建新索引</td>
</tr>
<tr>
<td><code>WHERE col1 = 'a' AND col3 = 'b'</code></td>
<td>仅使用col1部分</td>
<td>如果可以请加上col2部分</td>
</tr>
<tr>
<td><code>WHERE col1 = 'a' AND col2 LIKE '%b' AND col3 = 'c'</code></td>
<td>使用col1部分</td>
<td>避免在中间列使用通配符</td>
</tr>
</tbody>
</table>
<blockquote>
<p>最左前缀原则要求查询必须从复合索引的最左边列开始，并且不能跳过中间的列。这是因为B+树索引是按照索引定义的列顺序构建的，如果跳过前面的列，就无法利用索引的有序性。</p>
</blockquote>
<h3 id="52-like查询以通配符开头">5.2 LIKE查询以通配符开头</h3>
<pre><code class="language-sql">-- 无法使用索引
SELECT * FROM products WHERE name LIKE '%apple%';
SELECT * FROM products WHERE name LIKE '%apple';
-- 可以使用索引
SELECT * FROM products WHERE name LIKE 'apple%';
</code></pre>
<blockquote>
<p>当LIKE模式以通配符开头时，优化器无法利用索引的有序性进行快速定位，因为无法确定匹配值的前缀。这种情况下，优化器只能进行全表扫描，逐行比较是否匹配模式。</p>
<p>对于%%全模糊匹配，可以考虑使用搜索引擎如Elasticsearch。如果必须使用前导通配符%apple，可以考虑使用<strong>反转字符串并建立反转索引</strong>的技巧。</p>
</blockquote>
<h3 id="53-or条件使用不当">5.3 OR条件使用不当</h3>
<p>当OR条件中包含未索引列时，整个查询可能无法使用索引：</p>
<pre><code class="language-sql">-- 假设age字段没有索引
SELECT * FROM users WHERE name = 'john' OR age > 30;
-- 优化方案：使用UNION或确保所有OR条件都有索引（但是需要注意union可能会使用临时表）
SELECT * FROM users WHERE name = 'john'
UNION
SELECT * FROM users WHERE age > 30;
</code></pre>
<blockquote>
<p>MySQL处理OR条件时，如果OR的各个条件都使用独立的索引，可以使用<strong>index_merge</strong>优化。但如果其中一个条件没有索引，优化器就无法使用任何索引，只能选择全表扫描。</p>
</blockquote>
<h3 id="54-in和not-in滥用">5.4 IN和NOT IN滥用</h3>
<p>当IN列表中的值过多时，优化器可能选择全表扫描：</p>
<pre><code class="language-sql">-- 当value_list包含大量值时，可能导致全表扫描
SELECT * FROM products WHERE category_id IN (1, 2, 3, ..., 1000);
-- 最简单的方案就是，分批次查询（拆成5批）
SELECT * FROM products WHERE category_id IN (1, 2, ..., 200);
</code></pre>
<blockquote>
<p>当IN列表包含大量值时，优化器需要评估<strong>回表查询的代价</strong>。如果IN列表过大，优化器可能判断全表扫描更高效。</p>
<p>一般来说，当IN列表包含的值超过表中<strong>总行数的30%时</strong>，优化器倾向于选择全表扫描。</p>
</blockquote>
<h3 id="55-select--的性能影响">5.5 SELECT * 的性能影响</h3>
<p>虽然<code>SELECT *</code>不会直接导致索引失效，但会带来其他性能问题：</p>
<ol>
<li><strong>无法使用覆盖索引</strong>：除非索引字段全覆盖（正常都不会）</li>
<li><strong>网络传输浪费</strong>：返回不必要的数据增加了网络传输开销</li>
<li><strong>内存占用增加</strong>：需要缓存更大的结果集，可能挤占其他查询的内存资源，影响内存命中率</li>
<li><strong>增加了排序和临时表的使用</strong>：当需要排序或分组时，更大的行尺寸会增加临时表的使用</li>
</ol>
<pre><code class="language-sql">-- 不推荐
SELECT * FROM users WHERE age > 30;
-- 推荐：只选择需要的字段
SELECT id, name, email FROM users WHERE age > 30;
-- 使用覆盖索引优化
CREATE INDEX idx_users_age_covering ON users(age) INCLUDE (id, name, email);
SELECT id, name, email FROM users WHERE age > 30;
</code></pre>
<h2 id="六诊断与优化使用explain深入分析查询">六、诊断与优化：使用EXPLAIN深入分析查询</h2>
<p>要深入诊断索引是否被正确使用，EXPLAIN命令是最重要的工具。EXPLAIN执行计划包含6个关键字段，每个字段都承载着优化器决策的关键信息：</p>
<table>
<thead>
<tr>
<th>字段</th>
<th>说明</th>
<th>优化意义</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong>type</strong></td>
<td>访问类型，性能排序：system > const > eq_ref > ref > range > index > ALL</td>
<td>SQL优化的核心指标，决定数据检索效率</td>
</tr>
<tr>
<td><strong>key</strong></td>
<td>实际使用的索引</td>
<td>验证优化器最终选择的索引</td>
</tr>
<tr>
<td><strong>key_len</strong></td>
<td>索引使用的字节数</td>
<td>计算复合索引中使用到的字段长度，验证索引利用率</td>
</tr>
<tr>
<td><strong>rows</strong></td>
<td>预估扫描行数</td>
<td>数值越小性能越好，大数值需优化</td>
</tr>
<tr>
<td><strong>filtered</strong></td>
<td>存储引擎层过滤后的剩余比例</td>
<td>查询效率核心指标，100%表示完美过滤</td>
</tr>
<tr>
<td><strong>Extra</strong></td>
<td>额外执行信息</td>
<td>揭示潜在性能问题（如Using temporary, Using filesort等）</td>
</tr>
</tbody>
</table>
<pre><code class="language-sql">-- 分析查询执行计划
EXPLAIN SELECT * FROM orders WHERE MONTH(create_time) = 6;
</code></pre>
<p>对于这条查询，EXPLAIN结果可能显示：</p>
<ul>
<li><code>type: ALL</code>：表示全表扫描</li>
<li><code>key: NULL</code>：表示没有使用索引</li>
<li><code>rows: 1000000</code>：表示需要扫描100万行</li>
<li><code>Extra: Using where</code>：表示需要逐行判断条件</li>
</ul>
<p>这表明索引没有被使用，需要进行优化。</p>
<blockquote>
<p>如果想深入学习EXPLAIN的详细用法和所有字段含义，推荐阅读我的另一篇文章：《MySQL EXPLAIN执行计划：SQL性能翻倍的秘密武器》</p>
</blockquote>
<h2 id="七总结与最佳实践">七、总结与最佳实践</h2>
<p>通过本文的分析，我们可以看到，大多数索引失效场景都源于同一个根本原因：<strong>对索引字段进行了某种形式的操作，破坏了索引值的有序性，导致优化器无法使用索引的快速定位能力</strong>。以下是详细的总结和优化建议：</p>
<h3 id="71-索引失效场景及解决方案总结表">7.1 索引失效场景及解决方案总结表</h3>
<table>
<thead>
<tr>
<th>失效场景</th>
<th>根本原因</th>
<th>示例</th>
<th>解决方案</th>
</tr>
</thead>
<tbody>
<tr>
<td>索引列函数操作</td>
<td>破坏索引有序性</td>
<td><code>WHERE MONTH(create_time)=6</code></td>
<td>重写为范围查询：<code>WHERE create_time BETWEEN...</code></td>
</tr>
<tr>
<td>隐式类型转换</td>
<td>MySQL自动转换类型</td>
<td><code>WHERE varchar_col=123</code></td>
<td>确保类型匹配：<code>WHERE varchar_col='123'</code></td>
</tr>
<tr>
<td>字符集不一致</td>
<td>关联查询隐式转换</td>
<td>多表关联字符集不同</td>
<td>统一字符集或显式转换</td>
</tr>
<tr>
<td>违反最左前缀</td>
<td>复合索引使用不当</td>
<td>索引(a,b,c)但查询只用b,c</td>
<td>调整查询条件或创建新索引</td>
</tr>
<tr>
<td>LIKE前导通配符</td>
<td>无法利用索引有序性</td>
<td><code>WHERE name LIKE '%abc'</code></td>
<td>避免前导通配符或使用全文索引</td>
</tr>
<tr>
<td>OR条件无索引</td>
<td>其中一个条件无索引</td>
<td><code>WHERE a=1 OR b=2</code>(b无索引)</td>
<td>使用UNION或为b字段添加索引</td>
</tr>
<tr>
<td>IN列表过大</td>
<td>优化器判断全表更快</td>
<td><code>WHERE id IN(1,2,...,1000)</code></td>
<td>分拆查询</td>
</tr>
<tr>
<td>SELECT * 滥用</td>
<td>无法使用覆盖索引</td>
<td><code>SELECT * FROM large_table</code></td>
<td>明确指定所需字段</td>
</tr>
<tr>
<td>数据分布倾斜</td>
<td>优化器误判扫描成本</td>
<td>某值占比过高</td>
<td>使用FORCE INDEX或优化统计信息</td>
</tr>
<tr>
<td>统计信息过期</td>
<td>优化器做出错误决策</td>
<td>数据变化后未分析表</td>
<td>定期执行ANALYZE TABLE</td>
</tr>
</tbody>
</table>
<blockquote>
<p>数据分布倾斜、统计信息过期出现概率较小，因此全文未具体介绍。</p>
<p>核心原因在于：<strong>MySQL使用采样统计的方法导致索引统计信息不准确</strong>及<strong>优化器存在误判的情况</strong>。</p>
</blockquote>
<h3 id="72-核心优化原则">7.2 核心优化原则</h3>
<ol>
<li><strong>保持索引原始性</strong>：避免在索引列上进行任何函数计算、类型转换或表达式运算</li>
<li><strong>注意隐式转换</strong>：MySQL的隐式类型转换和字符集转换可能导致意外的函数操作</li>
<li><strong>统一设计规范</strong>：保持表结构设计的一致性，避免字符集和排序规则的不匹配</li>
<li><strong>主动优化意识</strong>：MySQL优化器不会自动重写所有低效查询，需要开发者主动优化</li>
<li><strong>使用EXPLAIN验证</strong>：对关键查询使用EXPLAIN分析执行计划，确保索引被正确使用</li>
</ol>
<h3 id="73-结语">7.3 结语</h3>
<p>索引优化是数据库性能调优的核心技能，也是一个需要持续学习和实践的过程。通过本文的系统分析，希望你已经理解了各种索引失效场景背后的原理，并掌握了相应的优化方法。</p>
<p>在实际工作中，建议养成以下良好习惯：</p>
<ul>
<li>在编写SQL时就要考虑索引使用情况</li>
<li>定期使用EXPLAIN分析关键查询的执行计划</li>
<li>监控慢查询日志，及时发现性能问题</li>
<li>建立数据库设计规范，避免常见的设计陷阱</li>
</ul>
<p>数据库优化之路永无止境，但每一步的探索都会带来实实在在的性能提升和更好的用户体验。希望本文能成为你索引优化路上的有力助手，帮助你在工作中解决更多的性能挑战。</p>
<blockquote>
<p>文章的最后，想和你多聊两句。</p>
<p>技术之路，常常是热闹与孤独并存。那些深夜的调试、灵光一闪的方案、还有踩坑爬起后的顿悟，如果能有人一起聊聊，该多好。</p>
<p>为此，我建了一个小花园——我的微信公众号「<strong>[努力的小郑]</strong>」。</p>
<p>这里没有高深莫测的理论堆砌，只有我对后端开发、系统设计和工程实践的持续思考与沉淀。它更像我的<strong>数字笔记本</strong>，记录着那些值得被记住的解决方案和思维火花。</p>
<p>如果你觉得今天的文章还有一点启发，或者单纯想找一个同行者偶尔聊聊技术、谈谈思考，那么，欢迎你来坐坐。<br>
<img src="https://img2024.cnblogs.com/blog/3703499/202601/3703499-20260105210259813-964799315.jpg"></p>
<p>愿你前行路上，总有代码可写，有梦可追，也有灯火可亲。</p>
</blockquote><br><br>
来源：https://www.cnblogs.com/xzqcsj/p/19203188
頁:
[1]