MySQL索引无效和索引有效的详细介绍

概述

• MySQL 索引的有效性和无效性取决于查询条件、索引设计和数据特性。合理使用索引可以显著提升查询性能，但不当的设计或使用方式会导致索引失效，影响查询效率。以下是详细的分类说明：

一、索引有效的情况

1. 等值查询（=）

• 场景：当使用 = 匹配索引列时，索引通常有效。

SELECT * FROM users WHERE id = 100;

• 原因：B+树索引天然支持精确查找。

2. 主键或唯一索引

• 场景：主键（PRIMARY KEY）或唯一索引（UNIQUE）的查询几乎总是有效。

SELECT * FROM orders WHERE order_id = 'ABC123';

3. 范围查询（>, <, BETWEEN）

• 场景：对索引列进行范围查询时，索引可能有效。

SELECT * FROM products WHERE price > 100;

• 注意：如果范围查询后需要回表（访问主键索引），性能可能下降。

4. 覆盖索引（Covering Index）

• 场景：查询的列全部包含在索引中，无需回表。

-- 假设索引是 (name, age)
SELECT name, age FROM users WHERE name = 'Alice';

• 优势：减少磁盘I/O，性能最佳。

5. 最左前缀匹配（Leftmost Prefix）

• 场景：联合索引（复合索引）的最左列被使用时，索引有效。

-- 联合索引 (a, b, c)
SELECT * FROM table WHERE a = 1 AND b = 2;  -- 有效
SELECT * FROM table WHERE a = 1;             -- 有效

6. 排序（ORDER BY）和分组（GROUP BY）

• 场景：索引列用于 ORDER BY 或 GROUP BY 时，可能避免排序操作。

-- 索引 (created_at)
SELECT * FROM logs ORDER BY created_at DESC;

二、索引失效的情况

1. 对索引列使用函数或表达式

• 场景：对索引列进行运算或函数操作。

SELECT * FROM users WHERE YEAR(created_at) = 2023;  -- 失效
SELECT * FROM products WHERE price * 0.8 > 100;     -- 失效

• 解决：将运算移到右侧：

SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

2. 隐式类型转换

• 场景：索引列与查询值的类型不匹配。

-- 假设 phone 是 VARCHAR 类型
SELECT * FROM users WHERE phone = 123456789;  -- 失效（数值转字符串）

3. 联合索引未遵循最左前缀

• 场景：未使用联合索引的最左列。

-- 联合索引 (a, b, c)
SELECT * FROM table WHERE b = 2;  -- 失效
SELECT * FROM table WHERE a = 1 AND c = 3;  -- 部分有效（仅用 a）

4. 使用 OR 条件

• 场景：OR 连接的列中有非索引列。

-- 假设 age 无索引
SELECT * FROM users WHERE name = 'Alice' OR age > 20;  -- 失效

• 解决：改用 UNION ALL 或为所有列建索引。

5. 模糊查询以通配符开头

• 场景：LIKE 以 % 开头。

SELECT * FROM products WHERE name LIKE '%apple';  -- 失效

• 有效场景：

SELECT * FROM products WHERE name LIKE 'apple%';  -- 可能有效（索引扫描）

6. 数据量过少

• 场景：表中数据量过小时，优化器可能选择全表扫描而非索引。

-- 假设表中只有 100 行数据
SELECT * FROM small_table WHERE id = 10;  -- 可能全表扫描

7. 索引选择性过低

• 场景：索引列的重复值过多（如性别、状态字段）。

-- 性别字段只有 'M'/'F'，索引可能被忽略
SELECT * FROM users WHERE gender = 'M';

• 解决：组合索引或使用其他高选择性列。

三、如何验证索引是否有效？

使用 EXPLAIN 分析执行计划：

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

• 关键字段：

• type：const、ref、range 表示索引有效。

• possible_keys：可能使用的索引。

• key：实际使用的索引。

• rows：扫描的行数（越小越好）。

四、优化建议

1. 优先使用高选择性字段：区分度高的列更适合建索引。
2. 合理设计联合索引：按查询频率和列顺序优化。
3. 避免冗余索引：定期清理无用索引（如重复索引）。
4. 监控索引使用情况：通过 SHOW INDEX 和慢查询日志分析。

通过理解索引的有效性规则，可以显著提升查询性能并避免潜在的性能瓶颈。