MySQL中索引失效的8大陷阱及排查指南
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">索引失效的8大常见场景</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1. 索引列上做计算（最常见）</a></li><li><a href="#_lab2_0_1">2. 索引列使用函数</a></li><li><a href="#_lab2_0_2">3. 模糊查询开头用通配符</a></li><li><a href="#_lab2_0_3">4. OR 条件使用不当</a></li><li><a href="#_lab2_0_4">5. 类型不匹配（隐式转换）</a></li><li><a href="#_lab2_0_5">6. 联合索引没用最左列</a></li><li><a href="#_lab2_0_6">7. 数据量太少，不如全表扫描</a></li><li><a href="#_lab2_0_7">8. 索引列选择性太低</a></li></ul><li><a href="#_label1">如何排查索引失效</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_8">第1步：用 EXPLAIN 快速诊断</a></li><li><a href="#_lab2_1_9">第2步：看懂 EXPLAIN 结果</a></li><li><a href="#_lab2_1_10">第3步：使用性能分析工具</a></li><li><a href="#_lab2_1_11">第4步：慢查询日志分析</a></li></ul><li><a href="#_label2">实战排查案例</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_12">案例1：为什么这个查询慢</a></li><li><a href="#_lab2_2_13">案例2：联合索引问题</a></li></ul><li><a href="#_label3">索引效果评估表</a></li><ul class="second_class_ul"></ul><li><a href="#_label4">最佳实践清单</a></li><ul class="second_class_ul"><li><a href="#_lab2_4_14">创建索引前问自己</a></li><li><a href="#_lab2_4_15">创建索引后要检查</a></li></ul><li><a href="#_label5">简单记忆口诀</a></li><ul class="second_class_ul"></ul></ul></div><p><strong>索引不是万能的！用不对反而更慢！</strong></p><p class="maodian"><a name="_label0"></a></p><h2>索引失效的8大常见场景</h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1. 索引列上做计算（最常见）</h3>
<div class="jb51code"><pre class="brush:sql;">-- ❌ 错误：在索引列上计算
SELECT * FROM users WHERE YEAR(created_time) = 2024;
SELECT * FROM products WHERE price * 2 > 100;
-- ✅ 正确：把计算移到右边
SELECT * FROM users WHERE created_time >= '2024-01-01' AND created_time < '2025-01-01';
SELECT * FROM products WHERE price > 50;
</pre></div>
<p><strong>原因</strong>：数据库必须对<strong>每一行</strong>都计算才能比较，无法用索引快速定位。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2. 索引列使用函数</h3>
<div class="jb51code"><pre class="brush:sql;">-- ❌ 错误：使用函数包裹索引列
SELECT * FROM users WHERE UPPER(name) = 'JOHN';
SELECT * FROM logs WHERE DATE(create_time) = '2024-01-01';
-- ✅ 正确：使用范围查询
SELECT * FROM users WHERE name = 'john';-- 存入时统一小写
SELECT * FROM logs WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';
</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>3. 模糊查询开头用通配符</h3>
<div class="jb51code"><pre class="brush:sql;">-- ❌ 错误：以 % 开头
SELECT * FROM users WHERE name LIKE '%张%';-- 全表扫描
SELECT * FROM users WHERE name LIKE '%明';
-- ✅ 正确：以确定字符开头
SELECT * FROM users WHERE name LIKE '张%'; -- 可以使用索引
SELECT * FROM users WHERE name LIKE '张_明'; -- _匹配一个字符
</pre></div>
<p><strong>生活类比</strong>：</p>
<ul><li><code>张%</code> → 知道姓张，直接翻到张姓区域 ✅</li><li><code>%张</code> → 不知道姓什么，只能一页页翻 ❌</li></ul>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>4. OR 条件使用不当</h3>
<div class="jb51code"><pre class="brush:sql;">-- ❌ 错误：OR 条件中有的列没索引
SELECT * FROM users WHERE age = 25 OR salary > 10000;
-- 如果 salary 没索引，整个查询都无法用索引
-- ✅ 正确：确保 OR 两边都有索引
-- 或者分开查询
SELECT * FROM users WHERE age = 25
UNION
SELECT * FROM users WHERE salary > 10000;
</pre></div>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>5. 类型不匹配（隐式转换）</h3>
<div class="jb51code"><pre class="brush:sql;">-- ❌ 错误：字符串列用数字查询
CREATE TABLE users (
id VARCHAR(20) PRIMARY KEY,-- 字符串类型
name VARCHAR(50)
);
SELECT * FROM users WHERE id = 123;-- 字符串和数字比较
-- ✅ 正确：类型一致
SELECT * FROM users WHERE id = '123';
</pre></div>
<p><strong>测试隐式转换</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 查看执行计划
EXPLAIN SELECT * FROM users WHERE id = 123;
-- 注意看 key 列是否为 NULL（没走索引）
</pre></div>
<p class="maodian"><a name="_lab2_0_5"></a></p><h3>6. 联合索引没用最左列</h3>
<div class="jb51code"><pre class="brush:sql;">-- 索引：idx_abc (a, b, c)
-- ✅ 有效：用了最左列
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;
-- ❌ 无效：没从最左开始
SELECT * FROM table WHERE b = 2; -- 跳过 a
SELECT * FROM table WHERE c = 3; -- 跳过 a,b
SELECT * FROM table WHERE b = 2 AND c = 3;-- 跳过 a
</pre></div>
<p><strong>记忆技巧</strong>：就像查字典，必须知道<strong>第一个字母</strong>才能快速查找。</p>
<p class="maodian"><a name="_lab2_0_6"></a></p><h3>7. 数据量太少，不如全表扫描</h3>
<div class="jb51code"><pre class="brush:sql;">-- 假设表只有 100 行数据
SELECT * FROM small_table WHERE id = 50;
-- 数据库会想：走索引要查索引树 + 回表，不如直接全表扫描更快
</pre></div>
<p class="maodian"><a name="_lab2_0_7"></a></p><h3>8. 索引列选择性太低</h3>
<div class="jb51code"><pre class="brush:sql;">-- 比如"性别"列，只有"男/女"两种值
CREATE INDEX idx_gender ON users(gender);
SELECT * FROM users WHERE gender = '男';
-- 可能失效，因为要返回接近一半的数据，不如全表扫描
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>如何排查索引失效</h2>
<p class="maodian"><a name="_lab2_1_8"></a></p><h3>第1步：用 EXPLAIN 快速诊断</h3>
<div class="jb51code"><pre class="brush:sql;">-- 在查询前加 EXPLAIN
EXPLAIN SELECT * FROM users WHERE name LIKE '%张%';
-- 关键看这几列：
-- 1. type: ALL → 全表扫描（最差）
-- 2. key: NULL → 没走索引
-- 3. rows: 数值很大 → 要扫描很多行
-- 4. Extra: Using filesort, Using temporary → 性能警告
</pre></div>
<p class="maodian"><a name="_lab2_1_9"></a></p><h3>第2步：看懂 EXPLAIN 结果</h3>
<div class="jb51code"><pre class="brush:sql;">EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';
-- 理想结果：
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
| id | select_type | table| type | possible_keys | key | key_len | rows | Extra|
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
|1 | SIMPLE | orders | ref| idx_user | idx_user| 8 | 10 | NULL |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+
-- type 从好到差：
-- system > const > eq_ref > ref > range > index > ALL
-- 至少要到 range 级别才合格
</pre></div>
<p class="maodian"><a name="_lab2_1_10"></a></p><h3>第3步：使用性能分析工具</h3>
<div class="jb51code"><pre class="brush:sql;">-- 1. 开启 profiling（查看详细耗时）
SET profiling = 1;
SELECT * FROM users WHERE ...;
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
-- 2. 查看索引使用统计
SELECT * FROM sys.schema_index_statistics
WHERE table_name = 'users';
-- 3. 找出从未使用的索引（该删除了！）
SELECT * FROM sys.schema_unused_indexes;
</pre></div>
<p class="maodian"><a name="_lab2_1_11"></a></p><h3>第4步：慢查询日志分析</h3>
<div class="jb51code"><pre class="brush:sql;">-- 1. 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;-- 超过2秒记录
-- 2. 查看慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
-- 3. 使用工具分析（推荐）
-- mysqldumpslow 或 pt-query-digest
</pre></div>
<p class="maodian"><a name="_label2"></a></p><h2>实战排查案例</h2>
<p class="maodian"><a name="_lab2_2_12"></a></p><h3>案例1：为什么这个查询慢</h3>
<div class="jb51code"><pre class="brush:sql;">-- 原始查询（很慢）
SELECT * FROM orders
WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01'
ORDER BY amount DESC;
-- 步骤1：EXPLAIN分析
EXPLAIN SELECT * FROM orders WHERE DATE_FORMAT(create_time, '%Y-%m') = '2024-01';
-- 发现：type=ALL, key=NULL（没走索引）
-- 原因：对 create_time 使用了函数
-- 优化方案：
SELECT * FROM orders
WHERE create_time >= '2024-01-01'
AND create_time < '2024-02-01'
ORDER BY amount DESC;
-- 再 EXPLAIN：type=range, key=idx_create_time ✅
</pre></div>
<p class="maodian"><a name="_lab2_2_13"></a></p><h3>案例2：联合索引问题</h3>
<div class="jb51code"><pre class="brush:sql;">-- 查询：经常按城市和年龄搜索用户
SELECT * FROM users WHERE city = '北京' AND age > 25;
-- 现有索引：idx_age_city (age, city)
-- 问题：最左列是 age，但查询先过滤 city
-- 解决方案：
-- 1. 调整查询顺序（如果业务允许）
SELECT * FROM users WHERE age > 25 AND city = '北京';
-- 2. 或创建新索引
CREATE INDEX idx_city_age ON users(city, age);
</pre></div>
<p class="maodian"><a name="_label3"></a></p><h2>索引效果评估表</h2>
<table><thead><tr><th>检查项</th><th>合格标准</th><th>如何检查</th></tr></thead><tbody><tr><td>索引使用率</td><td>> 90%</td><td>SHOW STATUS LIKE 'Handler_read%'</td></tr><tr><td>查询响应时间</td><td>< 100ms</td><td>慢查询日志</td></tr><tr><td>扫描行数</td><td>rows < 1000</td><td>EXPLAIN 的 rows 列</td></tr><tr><td>临时表使用</td><td>尽量避免</td><td>EXPLAIN 的 Extra 列</td></tr><tr><td>文件排序</td><td>尽量避免</td><td>EXPLAIN 的 Extra 列</td></tr></tbody></table>
<p class="maodian"><a name="_label4"></a></p><h2>最佳实践清单</h2>
<p class="maodian"><a name="_lab2_4_14"></a></p><h3>创建索引前问自己</h3>
<ul><li>这个查询真的需要索引吗？（数据量小不需要）</li><li>索引列的选择性高吗？（唯一值多吗？）</li><li>会频繁更新这个列吗？（更新频繁的列不适合建索引）</li><li>已经有类似的索引了吗？（避免重复索引）</li></ul>
<p class="maodian"><a name="_lab2_4_15"></a></p><h3>创建索引后要检查</h3>
<div class="jb51code"><pre class="brush:sql;">-- 1. 验证索引被使用
EXPLAIN SELECT ...;
-- 2. 监控索引大小
SELECT
table_name,
index_name,
ROUND(SUM(index_length)/1024/1024, 2) AS '索引大小(MB)'
FROM information_schema.TABLES
WHERE table_schema = '你的数据库'
GROUP BY table_name, index_name;
-- 3. 定期清理无用索引
-- 查看使用频率低的索引
SELECT * FROM sys.schema_unused_indexes;
</pre></div>
<p class="maodian"><a name="_label5"></a></p><h2>简单记忆口诀</h2>
<p><strong>索引失效八大坑：</strong></p>
<ul><li>计算函数不用想</li><li>模糊查询百分左</li><li>类型转换隐式藏</li><li>OR 条件要全防</li><li>最左前缀不能忘</li><li>数据量少全表爽</li><li>选择性低是白忙</li><li>不等号<>也够呛</li></ul>
<p><strong>排查四步曲：</strong></p>
<ul><li><strong>EXPLAIN</strong> 看计划</li><li><strong>慢日志</strong> 抓真凶</li><li><strong>PROFILE</strong> 查明细</li><li><strong>统计表</strong> 清无用</li></ul>
<p>记住：<strong>索引就像书的目录，但翻目录本身也需要时间。如果书只有10页，不如直接翻；如果目录编排不合理，还不如不用！</strong></p>
<p>以上就是MySQL中索引失效的8大陷阱及排查指南的详细内容，更多关于MySQL索引失效解决的资料请关注琼殿技术社区其它相关文章！</p>
頁:
[1]