MySQL索引失效场景

今年夏天 發表於 2025-6-30 23:59:00

MySQL 索引优化是提升查询性能的关键手段之一，但有时使用不当会导致索引失效。今天我们一起来看看哪些情况下索引会失效。
1、联合索引没有使用最左前缀
<ul>
<li>失效示例：联合索引 <code>(a,b,c)</code> 
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM table WHERE b=1 AND c=2;-- ❌ 索引失效</code></pre>
</li>
<li>正确写法：  
<pre class="language-sql highlighter-hljs"><code>WHERE a = ?-- ✅
WHERE a = ? AND b = ?-- ✅
WHERE a = ? AND b = ? AND c = ?-- ✅
-- ps：MySQL 对 = 条件的列，优化器会按索引顺序重组 WHERE 条件，比如：
WHERE b = ? AND a = ? AND c = ?-- ✅ 也是会走索引的</code></pre>
</li>
</ul>
2、在索引列上使用函数或运算
<ul>
<li>失效示例： 
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM orders WHERE YEAR(create_time) = 2025;--❌ 索引失效</code></pre>
</li>
<li>正确写法：  
<pre class="language-sql highlighter-hljs"><code>-- 改为范围查询
SELECT * FROM orders
WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31';-- ✅</code></pre>
</li>
</ul>
3、隐式类型转换
<ul>
<li>失效示例：字段类型与查询值类型不一致 
<pre class="language-sql highlighter-hljs"><code>-- user_id 是 VARCHAR 类型
SELECT * FROM users WHERE user_id = 1001;-- ❌ 索引失效（数字转字符串，MySQL 需将列值转为数字再比较，无法走索引）</code></pre>
</li>
<li>正确写法：  
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM users WHERE user_id = '1001';-- ✅ 保持类型一致</code></pre>
</li>
</ul>
4、<code>LIKE</code> 查询左边加了通配符 <code>%</code>
<ul>
<li>失效示例： 
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM users WHERE name LIKE '%王';-- ❌ 索引失效</code></pre>
</li>
<li>正确写法： 
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM users WHERE name LIKE '王%';-- ✅ 可以使用索引</code></pre>
</li>
</ul>
5、OR 连接非索引列
<ul>
<li>失效示例： 
<pre class="language-sql highlighter-hljs"><code>-- age 有索引，address 无索引
SELECT * FROM users WHERE age > 25 OR address = '北京';-- ❌ 索引失效</code></pre>
</li>
</ul>
<ul>
<li>正确写法： 
<pre class="language-sql highlighter-hljs"><code>-- 拆分成 UNION
SELECT * FROM users WHERE age > 25
UNION
SELECT * FROM users WHERE address = '北京';-- ✅</code></pre>
<img src="https://img2024.cnblogs.com/blog/1171560/202506/1171560-20250630235111024-1926065718.gif" class="cke_reset cke_widget_drag_handler" height="15" width="15" data-cke-widget-drag-handler="1"></li>
</ul>
6、使用 IS NULL / IS NOT NULL
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM users WHERE name IS NULL;-- ✅ 通常能用索引
SELECT * FROM users WHERE name IS NOT NULL;-- ❌ 索引不一定用，通常不能用</code></pre>
7、NOT IN / NOT EXISTS
<ul>
<li>失效示例： 
<pre class="language-sql highlighter-hljs"><code>SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist); -- ❌ 索引失效</code></pre>
</li>
<li>正确写法： 
<pre class="language-sql highlighter-hljs"><code>-- 改用 LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;-- ✅</code></pre>
</li>
</ul>
有时我们会发现，明明是正确的使用方法，但是看执行计划还是没走索引。有可能是数据量比较少时，MySQL自带的优化器认为全表扫描更快。当然，索引失效的情况，我只是列举了几种常见的。还有 重复索引、索引统计信息过期、范围查询中断联合索引等等，也会导致索引失效。我们可以根据具体情况进行分析，对于执行计划的解释的，大家可以参考另一篇博文 MySQL EXPLAIN 关键字详解。
资本低潮终究会过去。-- 烟沙九洲
<div>
<div style="position: fixed; right: 0; top: 0; width: 18px; height: 100%; z-index: 999999; pointer-events: none; background-image: url("data:,"); background-size: 100% 100%; background-repeat: no-repeat"> </div>
<button class="move-button" style="display: none; position: fixed; right: 18px; left: auto; top: 50%; z-index: 999999">◀</button></div>
<div>
<div style="position: fixed; right: 0; top: 0; width: 18px; height: 100%; z-index: 999999; pointer-events: none; background-image: url("data:,"); background-size: 100% 100%; background-repeat: no-repeat"> </div>
<button class="move-button" style="display: none; position: fixed; right: 18px; left: auto; top: 50%; z-index: 999999">◀</button></div>
<div>
<div style="position: fixed; right: 0; top: 0; width: 18px; height: 100%; z-index: 999999; pointer-events: none; background-image: url("data:,"); background-size: 100% 100%; background-repeat: no-repeat"> </div>
<button class="move-button" style="display: none; position: fixed; right: 18px; left: auto; top: 50%; z-index: 999999">◀</button></div> 
来源：https://www.cnblogs.com/yanshajiuzhou/p/18958834

頁: [1]

琼殿技术论坛's Archiver

MySQL索引失效场景