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

🔑 MySQL 索引使用的核心原理

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

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

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

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

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

✅ 典型场景：会走索引

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

sql

复制代码

SELECT * FROM user WHERE id = 100; -- 主键索引

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

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

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

sql

复制代码

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

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

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

sql

复制代码

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

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

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

sql

复制代码

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)

sql

复制代码

-- 索引: (user_id, create_time)

SELECT user_id, create_time FROM orders WHERE user_id = 10;

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

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

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

sql

复制代码

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

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

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

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

sql

复制代码

-- 函数

SELECT * FROM user WHERE YEAR(create_time) = 2024;

-- 运算

SELECT * FROM user WHERE id + 1 = 10;

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

3. 隐式类型转换

sql

复制代码

-- id 是 int，但传了字符串

SELECT * FROM user WHERE id = '100';

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

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

sql

复制代码

-- status 有索引，age 没有

SELECT * FROM user WHERE status = 1 OR age = 20;

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

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

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

sql

复制代码

CREATE INDEX idx_name_age ON user(name, age);

-- ❌ age 单独用不了索引

SELECT * FROM user WHERE age = 20;

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

sql

复制代码

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 基本走不了索引。

📊 总结表：索引能不能走

SQL 场景

是否走索引

说明

= / IN 精确匹配

✅

最高效

范围查询（BETWEEN, <, >）

✅

能利用索引有序性

LIKE 'abc%' 前缀匹配

✅

从开头开始匹配

LIKE '%abc' / %abc%

❌

破坏有序性

复合索引（遵循最左前缀）

✅

必须从最左列开始

索引列上函数 / 运算

❌

索引失效

隐式类型转换

❌

字符串 vs 数字要注意

!=、<>、NOT IN

❌

基本全表扫描

IS NULL

✅(可能)

能用，效果视情况

IS NOT NULL

❌

通常不走索引

覆盖索引

✅

查询字段全在索引里

🌟 核心记忆法

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

能从"开头"精准定位 → ✅ 走索引

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