MySQL索引什么时候生效，什么时候失效

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花

MySQL索引的核心作用是提升查询效率，本质是通过B+树等结构快速定位数据，避免全表扫描。但索引并非创建后就一定生效，其有效性取决于索引设计、查询语句写法、数据分布等多种因素。以下分「生效场景」和「失效场景」，结合具体案例和原理详细说明，所有案例均贴合实际开发场景，便于理解和规避问题。

一、MySQL索引生效场景

索引生效的核心前提：查询语句能匹配索引的存储结构（如B+树的有序性），且优化器判断走索引比全表扫描更高效。常见生效场景如下：

1. 主键/唯一索引的等值查询（必生效）

主键（PRIMARY KEY）和唯一索引（UNIQUE）是MySQL中效率最高的索引，用于精确匹配单个值时，索引必生效，因为其存储结构天然支持快速定位，且无重复值，查询效率极高。

示例：

-- 主键索引（id为主键）
SELECT * FROM user WHERE id = 100;
-- 唯一索引（user_phone为唯一索引）
SELECT * FROM user WHERE user_phone = '138008000';

原理：主键和唯一索引的B+树叶子节点直接关联数据行（聚簇索引）或主键值（非聚簇索引），无需扫描多余数据，可瞬间定位目标记录。

2. 普通索引的等值查询（通常生效）

普通索引（INDEX）用于非唯一字段的等值查询时，若字段区分度较高（重复值少），索引会生效；若区分度过低（如性别字段），优化器可能选择全表扫描（后续失效场景会详细说明）。

示例：

-- 普通索引（name为普通索引）
SELECT * FROM user WHERE name = '小五'

3. 组合索引的最左前缀匹配（核心生效场景）

组合索引（多字段联合创建的索引，如INDEX idx_abc(a,b,c)）的生效遵循「最左前缀原则」：查询条件必须从索引的最左列开始匹配，顺序不能跳过，否则无法充分利用索引。只要匹配最左前缀，索引就会生效（匹配的列越多，效率越高）。

示例（组合索引 idx_abc(a,b,c)）：

-- 生效（匹配最左列a）
SELECT * FROM user WHERE a = 1;
-- 生效（匹配a和b，最左前缀连续）
SELECT * FROM user WHERE a = 1 AND b = 2;
-- 生效（匹配a、b、c，全匹配）
SELECT * FROM user WHERE a = 1 AND b = 2 AND c = 3;

原理：组合索引的B+树是按最左列优先的顺序构建的，只有从最左列开始匹配，才能沿着树的结构快速定位数据，跳过最左列会导致索引结构无法被利用。

4. 范围查询（部分生效）

对索引列进行范围查询（>、<、≥、≤、BETWEEN、IN）时，索引会生效，但需注意：组合索引中，范围查询后的列无法再利用索引（即范围查询会阻断后续索引的使用）。

示例：

-- 普通索引（age为普通索引），生效
SELECT * FROM user WHERE age BETWEEN 18 AND 30;
-- 组合索引 idx_abc(a,b,c)，a生效，b生效，c失效
SELECT * FROM user WHERE a = 1 AND b > 5 AND c = 10;

5. 覆盖索引查询（高效生效）

覆盖索引是指查询的所有列（SELECT后的列）都包含在索引中，无需回表（无需通过索引找到主键后再查询全量数据），此时索引必生效，且查询效率极高（避免了磁盘I/O开销）。

示例：

-- 组合索引 idx_name_age(name, age)，查询列name、age均在索引中
SELECT name, age FROM user WHERE name = '小五';

6. 索引列用于ORDER BY/GROUP BY（生效）

当ORDER BY、GROUP BY的字段与索引列一致，且符合最左前缀原则时，索引会生效，可避免MySQL进行额外的排序操作（filesort），提升查询效率。

示例：

-- 索引 idx_create_time(create_time)，生效
SELECT * FROM logs ORDER BY create_time DESC;
-- 组合索引 idx_abc(a,b)，生效（符合最左前缀）
SELECT * FROM user WHERE a = 1 GROUP BY b;

二、MySQL索引失效场景（高频坑点）

索引失效是开发中最常见的性能问题，核心原因：查询语句破坏了索引的存储结构，或优化器判断全表扫描比走索引更高效。以下是高频失效场景，结合案例和解决方案说明：

1. 对索引字段使用函数或运算（必失效）

在WHERE条件中，对索引字段进行函数处理（如YEAR()、LOWER()）或数学运算（如+、-、*、/），会破坏索引的有序性，导致MySQL无法利用索引，只能进行全表扫描。

示例（失效）：

-- 索引字段create_time，使用YEAR()函数，失效
SELECT * FROM user WHERE YEAR(create_time) = 2025;
-- 索引字段id，进行数学运算，失效
SELECT * FROM user WHERE id + 1 = 100;

解决方案：将函数/运算移到常量一侧，避免操作索引字段。

正确写法：

SELECT * FROM user WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';
SELECT * FROM user WHERE id = 99;

2. 模糊查询以通配符%开头（必失效）

LIKE模糊查询中，若通配符%放在开头（如'%abc'），MySQL无法利用索引的前缀匹配特性（B+树按前缀有序存储），只能全表扫描；若%放在结尾（如'abc%'），索引会生效。

示例：

-- 失效（%开头）
SELECT * FROM user WHERE name LIKE '%小五';
-- 生效（%结尾）
SELECT * FROM user WHERE name LIKE '小五%';

解决方案：若必须使用前置通配符，可考虑使用全文索引或Elasticsearch（ES）替代。

3. 索引列出现隐式类型转换（必失效）

当索引字段的类型与查询条件中的值类型不一致时，MySQL会进行隐式类型转换（如字符串转整型、编码转换），等价于对索引字段进行函数操作，导致索引失效。

示例（失效）：

-- 索引字段user_id是VARCHAR类型，查询条件用整型123，隐式转换，失效
SELECT * FROM user WHERE user_id = 123;
-- 索引字段name是utf8mb4编码，查询条件字符串是utf8编码，编码转换，失效
SELECT * FROM user WHERE name = '小五';

解决方案：保证查询条件的值类型、编码与索引字段完全一致。

正确写法：

SELECT * FROM user WHERE user_id = '123';
-- 统一编码后查询
SELECT * FROM user WHERE name = CONVERT('小五' USING utf8mb4);

4. 组合索引未遵循最左前缀原则（必失效）

组合索引必须从最左列开始匹配，跳过最左列或打乱顺序，都会导致索引失效（或部分失效）。

示例（组合索引 idx_abc(a,b,c)，失效场景）：

-- 跳过最左列a，失效
SELECT * FROM user WHERE b = 2;
-- 跳过中间列b，仅a生效，c失效
SELECT * FROM user WHERE a = 1 AND c = 3;
-- 顺序打乱，失效
SELECT * FROM user WHERE b = 2 AND a = 1;

注意：即使查询条件中包含索引的所有列，若顺序不匹配最左前缀，也无法充分利用索引。

5. OR条件中存在未建索引的字段（大概率失效）

当OR连接的多个条件中，有一个字段未建立索引时，MySQL优化器会判断全表扫描比走索引更高效，从而放弃所有索引，进行全表扫描。

示例（失效）：

-- name有索引，age无索引，OR连接后，索引失效
SELECT * FROM user WHERE name = '小五' OR age = 20;

解决方案：给OR连接的所有字段都建立索引，或拆分SQL，用UNION/UNION ALL合并查询结果。

6. NOT、!=、<>、NOT IN等否定条件（大概率失效）

对于!=、<>、NOT IN、NOT EXISTS等否定条件，MySQL无法通过索引快速定位符合条件的数据（索引擅长“找到存在的值”，不擅长“排除不存在的值”），通常会进行全表扫描。

示例（失效）：

SELECT * FROM user WHERE age != 20;
SELECT * FROM user WHERE name NOT IN ('小五', '小二');

解决方案：将否定条件改写为范围查询（如age < 20 OR age > 20），可利用索引提升效率。

7. IS NOT NULL（大概率失效）

IS NULL 通常可以使用索引（单列索引虽不存储NULL值，但聚簇索引可定位），但 IS NOT NULL 会导致索引失效，因为需要扫描大部分数据来排除NULL值。

示例：

-- 可能生效
SELECT * FROM user WHERE age IS NULL;
-- 大概率失效
SELECT * FROM user WHERE age IS NOT NULL;

8. 索引字段区分度过低（大概率失效）

索引的效果取决于字段的区分度（基数），区分度=唯一值数量/总数据量，区分度过低（如性别、状态字段，只有2-3个唯一值），索引意义不大，MySQL优化器会直接放弃索引，选择全表扫描。

示例（失效）：

-- gender是索引字段，只有男/女两个值，区分度过低，失效
SELECT * FROM user WHERE gender = '男';

解决方案：避免给区分度过低的字段单独建索引，可将其作为组合索引的后续列使用。

9. 数据量过小或查询结果占比过高（大概率失效）

当表中数据量极少（如几百行），或查询结果占表数据总量的30%以上时，MySQL优化器会认为全表扫描的成本更低（避免索引维护和回表的开销），从而放弃索引。

示例（失效）：

-- 表中仅100行数据，即使id是主键，也可能全表扫描
SELECT * FROM small_table WHERE id = 10;
-- 查询结果占表数据80%，索引失效
SELECT * FROM user WHERE age > 18;

10. 强制使用索引失败（失效）

即使使用FORCE INDEX强制指定索引，若MySQL优化器判断全表扫描更快（如数据量小、索引选择性差），也会放弃使用索引，导致索引失效。

示例（失效）：

-- 强制使用idx_name索引，但数据量小，优化器选择全表扫描，索引失效
SELECT * FROM user FORCE INDEX (idx_name) WHERE name = '小五';

三、验证索引是否生效的方法

最常用、最直观的方法是使用EXPLAIN分析查询执行计划，重点关注3个字段：

1. key：显示MySQL实际使用的索引，若为NULL，说明索引未生效；
2. type：显示查询类型，const、ref、range表示索引生效（效率依次降低），ALL表示全表扫描（索引失效）；
3. rows：显示MySQL预估扫描的行数，行数越少，查询效率越高。

示例：

EXPLAIN SELECT * FROM user WHERE name = '小五';

四、总结

1. 索引生效的核心：匹配索引结构（最左前缀、无函数/运算）、优化器判断走索引更高效；

2. 索引失效的高频坑：函数/运算、隐式转换、%开头模糊查询、组合索引不遵循最左前缀、OR条件含非索引字段；

3. 开发建议：创建索引时优先选择区分度高的字段，组合索引按“查询频率高→低”排序；写查询语句时，避免破坏索引结构，用EXPLAIN验证索引有效性，定期清理冗余索引。

ps：如果这篇帖子对于还在找工作和找实习的你有所帮助，可以关注我，给本贴点赞、评论、收藏并订阅专栏；同时不要吝啬您的花花