首页 › MySQL论坛 › Mysql索引失效场景

林中林 發表於 2025-9-16 16:14:00

Mysql索引失效场景

<p class="ds-markdown-paragraph">以下是导致索引失效的常见情况，分类并举例说明：</p>
<h3>1. 对索引列进行运算或函数操作</h3>
<p class="ds-markdown-paragraph">当在索引列上使用函数、表达式、计算或类型转换时，MySQL无法直接使用索引来定位数据。</p>
<p class="ds-markdown-paragraph">失效示例：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token comment">-- 使用函数
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM users <span class="token keyword">WHERE <span class="token keyword">YEAR<span class="token punctuation">(create_time<span class="token punctuation">) <span class="token operator">= <span class="token number">2023<span class="token punctuation">;
<span class="token comment">-- 使用表达式
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM products <span class="token keyword">WHERE price <span class="token operator">* <span class="token number">2 <span class="token operator">&gt; <span class="token number">100<span class="token punctuation">;
<span class="token comment">-- 进行运算
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM employees <span class="token keyword">WHERE salary <span class="token operator">+ <span class="token number">1000 <span class="token operator">&gt; <span class="token number">5000<span class="token punctuation">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">优化方案：<br>将运算或函数操作转移到常量一侧。</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>

</div>

</div>

</div>
<pre><span class="token comment">-- 优化后：在create_time上建立索引有效
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM users <span class="token keyword">WHERE create_time <span class="token operator">&gt;= <span class="token string">'2023-01-01' <span class="token operator">AND create_time <span class="token operator">&lt; <span class="token string">'2024-01-01'<span class="token punctuation">;
<span class="token comment">-- 优化后
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM products <span class="token keyword">WHERE price <span class="token operator">&gt; <span class="token number">50<span class="token punctuation">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<h3>2. 使用&nbsp;<code>NOT LIKE</code>,&nbsp;<code>&lt;&gt;</code>,&nbsp;<code>NOT IN</code></h3>
<p class="ds-markdown-paragraph">这些否定操作符通常无法有效利用索引的结构进行快速查找。</p>
<ul>
<li>
<p class="ds-markdown-paragraph"><code>&lt;&gt;</code>&nbsp;或&nbsp;<code>!=</code>：需要检查所有不等于该值的记录，本质上接近全表扫描。</p>
</li>
<li>
<p class="ds-markdown-paragraph"><code>NOT LIKE</code>：类似。</p>
</li>
<li>
<p class="ds-markdown-paragraph"><code>NOT IN</code>：需要检查所有不在列表中的值，效率低下。</p>
</li>
</ul>
<p class="ds-markdown-paragraph">失效示例：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM customers <span class="token keyword">WHERE name <span class="token operator">NOT <span class="token operator">LIKE <span class="token string">'A%'<span class="token punctuation">;
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM orders <span class="token keyword">WHERE <span class="token keyword">status <span class="token operator">&lt;&gt; <span class="token string">'shipped'<span class="token punctuation">;
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM products <span class="token keyword">WHERE category_id <span class="token operator">NOT <span class="token operator">IN <span class="token punctuation">(<span class="token number">1<span class="token punctuation">, <span class="token number">2<span class="token punctuation">, <span class="token number">3<span class="token punctuation">)<span class="token punctuation">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">优化方案：<br>考虑改写查询逻辑，或者有时使用&nbsp;<code>LEFT JOIN ... IS NULL</code>&nbsp;来替代&nbsp;<code>NOT IN</code>。</p>
<h3>3. 使用&nbsp;<code>OR</code>&nbsp;连接条件（并非所有情况）</h3>
<p class="ds-markdown-paragraph">如果&nbsp;<code>OR</code>&nbsp;连接的多个条件中，并非所有列都有索引，MySQL通常会放弃使用索引而进行全表扫描。</p>
<p class="ds-markdown-paragraph">失效示例：<br>假设表有&nbsp;<code>a</code>（有索引）和&nbsp;<code>b</code>（无索引）两个字段。</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>

</div>

</div>

</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM <span class="token keyword">table <span class="token keyword">WHERE a <span class="token operator">= <span class="token number">1 <span class="token operator">OR b <span class="token operator">= <span class="token number">2<span class="token punctuation">;</span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">因为&nbsp;<code>b</code>&nbsp;列没有索引，MySQL必须读取所有行来检查&nbsp;<code>b=2</code>&nbsp;的条件，所以它也会放弃使用&nbsp;<code>a</code>&nbsp;列的索引。</p>
<p class="ds-markdown-paragraph">优化方案：</p>
<ul>
<li>
<p class="ds-markdown-paragraph">为&nbsp;<code>b</code>&nbsp;列也添加索引。</p>
</li>
<li>
<p class="ds-markdown-paragraph">使用&nbsp;<code>UNION</code>&nbsp;或&nbsp;<code>UNION ALL</code>&nbsp;将查询拆分，确保每个部分都能利用索引。</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM <span class="token keyword">table <span class="token keyword">WHERE a <span class="token operator">= <span class="token number">1
<span class="token keyword">UNION <span class="token keyword">ALL
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM <span class="token keyword">table <span class="token keyword">WHERE b <span class="token operator">= <span class="token number">2<span class="token punctuation">;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">（注意：此方案的前提是&nbsp;<code>b=2</code>&nbsp;的结果集很小，或者最终也必须为&nbsp;<code>b</code>&nbsp;列创建索引）</p>
</li>
</ul>
<h3>4. 隐式类型转换</h3>
<p class="ds-markdown-paragraph">如果查询条件的数据类型与索引列的定义类型不一致，MySQL会进行隐式类型转换，这相当于在索引列上使用了函数，导致索引失效。</p>
<p class="ds-markdown-paragraph">失效示例：<br>假设&nbsp;<code>user_id</code>&nbsp;字段是字符串类型（VARCHAR），但查询时使用了数字。</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>

</div>

</div>

</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM users <span class="token keyword">WHERE user_id <span class="token operator">= <span class="token number">123456<span class="token punctuation">; <span class="token comment">-- 失效</span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">MySQL需要将表中每行的&nbsp;<code>user_id</code>&nbsp;字符串转换成数字才能与&nbsp;<code>123456</code>&nbsp;比较。</p>
<p class="ds-markdown-paragraph">优化方案：<br>确保类型匹配。</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>

</div>

</div>

</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM users <span class="token keyword">WHERE user_id <span class="token operator">= <span class="token string">'123456'<span class="token punctuation">; <span class="token comment">-- 有效</span></span></span></span></span></span></span></span></pre>
</div>
<h3>5. 违反最左前缀原则</h3>
<p class="ds-markdown-paragraph">这是针对联合索引（复合索引）&nbsp;的经典陷阱。联合索引的顺序非常重要，它是从最左列开始有序的。</p>
<p class="ds-markdown-paragraph">假设有一个联合索引&nbsp;<code>idx_first_last (first_name, last_name)</code>。</p>
<p class="ds-markdown-paragraph">有效示例（遵循最左前缀）：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM employees <span class="token keyword">WHERE first_name <span class="token operator">= <span class="token string">'Zhang'<span class="token punctuation">; <span class="token comment">-- 使用索引
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM employees <span class="token keyword">WHERE first_name <span class="token operator">= <span class="token string">'Zhang' <span class="token operator">AND last_name <span class="token operator">= <span class="token string">'San'<span class="token punctuation">; <span class="token comment">-- 使用索引</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">失效示例（违反最左前缀）：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM employees <span class="token keyword">WHERE last_name <span class="token operator">= <span class="token string">'San'<span class="token punctuation">; <span class="token comment">-- 失效！跳过了 first_name</span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">这就像打电话簿，电话簿是按（姓，名）联合排序的。如果你只知道名而不知道姓，是无法快速查找的。</p>
<h3>6. 使用&nbsp;<code>LIKE</code>&nbsp;以通配符&nbsp;<code>%</code>&nbsp;开头</h3>
<p class="ds-markdown-paragraph">使用&nbsp;<code>LIKE</code>&nbsp;进行模糊查询时，如果通配符&nbsp;<code>%</code>&nbsp;出现在开头，索引会失效。</p>
<p class="ds-markdown-paragraph">失效示例：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM products <span class="token keyword">WHERE name <span class="token operator">LIKE <span class="token string">'%apple%'<span class="token punctuation">; <span class="token comment">-- 可能全表扫描
<span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM products <span class="token keyword">WHERE name <span class="token operator">LIKE <span class="token string">'%apple'<span class="token punctuation">; <span class="token comment">-- 失效</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">有效示例：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM products <span class="token keyword">WHERE name <span class="token operator">LIKE <span class="token string">'apple%'<span class="token punctuation">; <span class="token comment">-- 有效，使用索引范围扫描</span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">因为&nbsp;<code>‘apple%’</code>&nbsp;是前缀，索引的有序性可以发挥作用。而&nbsp;<code>‘%apple’</code>&nbsp;不知道开头是什么，无法利用索引排序。</p>
<h3>7. 索引列使用&nbsp;<code>IS NULL</code>&nbsp;或&nbsp;<code>IS NOT NULL</code></h3>
<p class="ds-markdown-paragraph">在某些情况下（特别是当表中允许为NULL的值非常多或非常少时），优化器可能选择全表扫描而不是索引扫描。</p>
<p class="ds-markdown-paragraph">示例：</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>
</div>
</div>
</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM users <span class="token keyword">WHERE phone_number <span class="token operator">IS <span class="token boolean">NULL<span class="token punctuation">; <span class="token comment">-- 可能失效</span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">优化器会评估使用索引的成本，如果<code>NULL</code>值很多，它可能觉得全表扫描更快。</p>
<h3>8. 数据分布不均匀（优化器放弃索引）</h3>
<p class="ds-markdown-paragraph">当MySQL的优化器通过统计信息发现，使用索引查询需要回表的数据量非常大（例如超过表记录的20%-30%），它可能会认为全表扫描的成本比使用索引更低，从而放弃使用索引。</p>
<p class="ds-markdown-paragraph">示例：<br>假设有一个&nbsp;<code>gender</code>&nbsp;字段，上面有索引，但值只有 ‘M‘ 和 ’F‘，且分布大致为50%/50%。</p>
<div class="md-code-block md-code-block-light">
<div class="md-code-block-banner-wrap">
<div class="md-code-block-banner md-code-block-banner-lite">
<div class="_121d384">
<div class="d2a24f03"><span class="d813de27">sql</span></div>

</div>

</div>

</div>
<pre><span class="token keyword">SELECT <span class="token operator">* <span class="token keyword">FROM students <span class="token keyword">WHERE gender <span class="token operator">= <span class="token string">'M'<span class="token punctuation">; <span class="token comment">-- 优化器可能选择全表扫描</span></span></span></span></span></span></span></span></pre>
</div>
<p class="ds-markdown-paragraph">因为需要回表获取一半的数据，不如直接顺序读整个表。</p><br><br>
来源：https://www.cnblogs.com/ljc1212/p/19095027

查看完整版本: Mysql索引失效场景