MySQL 索引的作用？什么情况下索引会失效？一、MySQL 索引为什么快本质上，索引的作用就是：减少数据查找范围，避

一、MySQL 索引为什么快

本质上，索引的作用就是：减少数据查找范围，避免全表扫描。

1. 如果没有索引会怎样

假设有一张用户表，有 1000 万条数据：

select * from user where id = 1001;

如果 id 没有索引，MySQL 只能从第一行开始，一行一行比对：

第 1 行不是
第 2 行不是
第 3 行不是
…
直到找到目标

这就是 全表扫描，时间复杂度接近 O(n) 。

数据量越大，查询越慢。

2. 有索引为什么会快

如果 id 上有索引，MySQL 不需要一行一行找，而是先走索引结构快速定位，再去取数据。

MySQL 的 InnoDB 索引底层通常是 B+ 树。

B+ 树有几个关键特点：

天然有序
多路平衡查找
树高度低
适合磁盘 IO

比如查找一个值时，不需要扫描全部数据，只需要从根节点往下找几层，就能定位到目标记录，时间复杂度大约是 O(log n) 。

所以索引快的核心原因是：

把无序查找变成有序查找
大幅减少扫描行数
减少磁盘 IO 次数
利用索引的有序性支持范围查询、排序、分组

3. 索引为什么特别适合数据库

数据库数据大多在磁盘里，不是全在内存里。
磁盘随机 IO 很慢，所以数据库优化的核心之一就是：尽量少做 IO。

B+ 树比普通二叉树更适合数据库，原因是：

（1）层数更少

B+ 树一个节点可以存很多 key，相当于“一个节点分很多叉”，树会更矮。

例如：

二叉树每层最多 2 个分支
B+ 树每层可能几百上千个分支

这样查一次数据，只要访问很少几层节点，磁盘 IO 次数就少很多。

（2）叶子节点有序

B+ 树叶子节点之间通常通过指针相连，范围查询效率很高：

select * from user where age between 20 and 30;

定位到 20 后，可以顺着叶子节点一直扫到 30。

（3）非叶子节点只存索引，不存整行数据

这样一个页里能放更多索引项，进一步降低树高度。

4. 索引加快查询的几种典型场景

等值查询

select * from user where id = 10;

范围查询

select * from user where create_time > '2025-01-01';

排序

select * from user order by create_time;

如果排序字段上有合适索引，可能直接按索引顺序读取，避免额外排序。

分组

select dept_id, count(*) from emp group by dept_id;

覆盖索引

select id, name from user where name = 'Tom';

如果查询字段刚好都在索引里，就不需要回表，效率更高。

二、什么情况下索引会失效

所谓索引失效，不是索引“没了”，而是 SQL 执行时优化器没有使用索引，或者没有按预期高效使用索引。

1. 对索引列做了函数、运算、表达式

例如：

select * from user where year(create_time) = 2025;

或者：

select * from user where age + 1 = 30;

原因是索引里存的是原始值，不是函数计算后的值。
对列做运算后，MySQL 很难直接利用原来的有序结构。

优化写法

select * from user where create_time >= '2025-01-01'
  and create_time < '2026-01-01';

2. 使用了 `like '%xx'` 或 `like '%xx%'`

例如：

select * from user where name like '%abc';
select * from user where name like '%abc%';

B+ 树索引是按照前缀有序排列的，前面是模糊的，无法确定从哪里开始找。

可以走索引的情况

select * from user where name like 'abc%';

这是前缀匹配，可以利用索引。

3. 联合索引不满足最左前缀原则

假设有联合索引：

index(a, b, c)

那么能有效使用索引的常见情况有：

where a = 1
where a = 1 and b = 2
where a = 1 and b = 2 and c = 3
where a = 1 and c = 3

但下面这种通常不行：

where b = 2
where c = 3
where b = 2 and c = 3

因为联合索引是先按 a 排序，再按 b，再按 c，跳过最左边字段通常无法利用完整索引结构。

4. 联合索引遇到范围查询，范围后面的列可能失效

还是联合索引：

index(a, b, c)

SQL：

where a = 1 and b > 10 and c = 3

这里通常 a、b 能利用索引，但 b 是范围条件后，c 往往不能继续充分利用索引过滤。

这也是面试高频点。

5. 隐式类型转换

例如：

select * from user where phone = 13800138000;

如果 phone 字段是字符串类型 varchar，但你传的是数字，可能触发隐式转换，导致索引使用异常或失效。

更稳妥写法

select * from user where phone = '13800138000';

字段类型和条件类型尽量一致。

6. 使用 `!=`、`<>`、`not in`、`not like`

例如：

select * from user where status != 1;

这类条件筛选范围往往太大，优化器可能认为走索引不划算，转而全表扫描。

注意，不是绝对失效，而是很多情况下不会选索引。

7. 数据区分度太低

比如 gender 只有：

select * from user where gender = '男';

即使建了索引，也可能不用。
因为匹配结果太多，走索引后还要回表，成本可能比直接全表扫更高。

这种叫 选择性差，索引价值低。

8. `or` 两边条件有一边没索引

例如：

select * from user where name = 'Tom' or age = 20;

如果 name 有索引，age 没索引，优化器可能直接放弃索引，走全表扫描。

9. 查询结果集太大

即使条件字段有索引，但如果查出来的数据占表中很大比例，优化器也可能认为：

走索引要先查索引
再大量回表
成本反而更高

于是直接全表扫描。

这不是索引失效，而是 优化器选择了更低成本方案。

10. 没有遵循覆盖索引，回表成本高

例如：

select * from user where name = 'Tom';

如果 name 上有索引，但你查的是 *，而索引里没有全部字段，就要回表。

如果匹配行很多，回表代价很大，优化器可能放弃索引。

三、怎么判断索引有没有生效

最常用的是：

explain select * from user where name = 'Tom';

重点看这些字段：

1. `type`

常见从好到差大致是：

const
ref
range
index
all

如果看到 all，通常说明走了全表扫描。

2. `key`

表示实际使用了哪个索引。

3. `rows`

表示预计扫描的行数，越少通常越好。

4. `extra`

常见关注：

Using index：覆盖索引，通常不错
Using where：需要过滤
Using filesort：额外排序
Using temporary：用了临时表

四、背诵版

1. 为什么索引能加快查询

因为索引可以帮助 MySQL 快速缩小数据查找范围，避免全表扫描。
InnoDB 索引底层通常是 B+ 树，查找时间复杂度从全表扫描的 O(n) 降低到 O(log n)。同时 B+ 树层级低、叶子节点有序，能够减少磁盘 IO，并且支持等值查询、范围查询、排序和分组。

2. 索引失效的常见场景

常见有这些：

对索引列使用函数、计算、表达式
like 以 % 开头
联合索引不满足最左前缀原则
联合索引中范围查询后面的列可能失效
隐式类型转换
使用 !=、<>、not in、not like
字段区分度太低，优化器可能不走索引
or 条件中部分字段没索引
查询结果集过大，优化器认为全表扫描更便宜

五、本质原因

索引是否生效，本质上不是看“有没有建索引”，而是看优化器是否认为走索引成本更低。
所以索引失效很多时候并不是真的失效，而是因为 SQL 写法破坏了索引结构，或者扫描数据太多，导致优化器放弃索引。

MySQL 索引的作用？什么情况下索引会失效？