【后端数据库】MySQL 索引生效/失效规则 + 核心原理

 SQL 优化的核心 —— 什么时候能“走索引”，什么时候会“失效”。整理一个索引生效/失效规则 + 核心原理的全景图，帮助彻底理解。

🔑 MySQL 索引使用的核心原理

MySQL 使用 B+Tree 索引（最常见），特点是：

• 数据在磁盘上是 有序存储 的。

• 查询时会从根节点到叶子节点 二分查找。

• 只有满足 有序性 的条件才能利用索引，否则会退化成全表扫描。

所以，判断 SQL 是否能走索引的关键就是：WHERE 条件是否能利用索引的有序性。

✅ 典型场景：会走索引

1. 精确匹配（==, IN）

SELECT * FROM user WHERE id = 100;   -- 主键索引
SELECT * FROM user WHERE email IN ('a@xx.com','b@xx.com'); -- 普通索引

👉 等值查询最友好，100%用到索引。

2. 范围查询（BETWEEN, >, <, >=, <=）

SELECT * FROM orders WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31';

👉 范围条件能利用索引的有序性。

3. 前缀匹配（LIKE 'abc%'）

SELECT * FROM article WHERE title LIKE '优化%';

👉 因为能从 abc 开头定位，所以可走索引。

4. 复合索引（最左前缀原则）

CREATE INDEX idx_user_name_age ON user(name, age);-- 能走索引：
SELECT * FROM user WHERE name = 'Tom';
SELECT * FROM user WHERE name = 'Tom' AND age = 20;

👉 使用复合索引时，必须从 最左字段开始连续使用。

5. 覆盖索引 (Covering Index)

-- 索引: (user_id, create_time)
SELECT user_id, create_time FROM orders WHERE user_id = 10;

👉 查询列全部在索引里，不需要回表，效率更高。

❌ 索引失效场景：不会走索引

1. LIKE '%abc' / %abc% （左模糊）

SELECT * FROM article WHERE title LIKE '%优化';
SELECT * FROM article WHERE title LIKE '%优化%';

👉 无法确定开头位置，B+Tree 的有序性失效。

2. 索引列上做函数 / 运算

-- 函数
SELECT * FROM user WHERE YEAR(create_time) = 2024;-- 运算
SELECT * FROM user WHERE id + 1 = 10;

👉 MySQL 不能用索引里的有序值，只能逐行计算 → 全表扫描。

3. 隐式类型转换

-- id 是 int，但传了字符串
SELECT * FROM user WHERE id = '100';

👉 会触发类型转换，导致索引失效。

4. 使用 OR（混合索引列 vs 非索引列）

-- status 有索引，age 没有
SELECT * FROM user WHERE status = 1 OR age = 20;

👉 部分条件没索引 → 可能导致索引失效。

（解决办法：把 OR 改成 UNION ALL）

5. 复合索引未遵循“最左前缀”

CREATE INDEX idx_name_age ON user(name, age);-- ❌ age 单独用不了索引
SELECT * FROM user WHERE age = 20;

6. 使用 !=、<>、NOT IN、NOT LIKE

SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE email NOT LIKE 'a%';

👉 索引失效，大概率全表扫描。

7. 在索引字段上用 IS NULL / IS NOT NULL

• IS NULL 可以走索引（但要看情况）。

• IS NOT NULL 基本走不了索引。

📊 总结表：索引能不能走

🌟 核心记忆法

👉 索引利用的关键：有序性

• 能从“开头”精准定位 → ✅ 走索引

• 破坏有序性（函数、运算、模糊、NOT） → ❌ 索引失效