【数据库基础|第7篇】索引到底是什么？为什么能提高查询速度？

前言

前面我们已经学习了 SQL 增删改查、条件查询、分组统计和多表查询。

这些内容已经能支撑我们写出很多业务 SQL 了。但是在真实项目中，光会写 SQL 还不够，还要考虑一个很重要的问题：查询速度。

比如员工表只有几十条数据时，怎么查都很快。

但是如果一张表有几十万、几百万条数据，下面这种查询就可能变慢：

select *
from emp
where username = 'zhangsan';

如果数据库每次都从第一行查到最后一行，数据越多，查询就越慢。

这时候就需要用到数据库中的一个重要概念：索引。

这一篇我们就来学习索引是什么、为什么索引能提高查询速度、怎么创建索引，以及使用索引时有哪些常见注意点。

一、索引是什么？

索引可以简单理解为数据库表的“目录”。

就像我们查字典时，不会从第一页开始一页一页翻，而是先看目录或拼音索引，快速定位到目标内容。

数据库索引也是类似的作用。

如果没有索引，查询数据时可能需要全表扫描：

第 1 行查一下
第 2 行查一下
第 3 行查一下
…
一直查到目标数据

如果有索引，数据库可以先通过索引快速定位数据位置，再去表中取数据。

所以一句话理解：

索引是帮助数据库快速查找数据的数据结构。

二、为什么主键查询通常很快？

我们经常写这样的 SQL：

select *
from emp
where id = 1;

这种根据主键 id 查询通常很快。

原因是主键默认会创建索引。

比如建表时：

create table emp (
id int unsigned primary key auto_increment comment '员工ID',
username varchar(20) not null,
name varchar(10) not null
);

这里的：

primary key

不仅表示 id 是主键，还会自动为 id 创建主键索引。

所以根据 id 查询时，数据库可以通过索引快速定位数据。

三、没有索引时查询会怎样？

假设员工表中有 100 万条数据，我们要根据用户名查询员工：

select *
from emp
where username = 'zhangsan';

如果 username 字段没有索引，数据库可能需要从头到尾扫描整张表。

这个过程叫：

全表扫描

也就是：

检查第 1 条数据 username 是不是 zhangsan
检查第 2 条数据 username 是不是 zhangsan
检查第 3 条数据 username 是不是 zhangsan
…

数据量越大，扫描成本越高。

如果给 username 加上索引，数据库就可以通过索引更快找到 zhangsan 对应的数据。

四、如何创建索引？

1. 创建普通索引

create index idx_emp_username
on emp(username);

这条 SQL 表示给 emp 表的 username 字段创建一个普通索引。

索引名是：

idx_emp_username

索引命名一般建议带上字段名，方便后期维护。

2. 创建唯一索引

如果某个字段不能重复，比如用户名，就可以创建唯一索引：

create unique index idx_emp_username
on emp(username);

唯一索引有两个作用：

比如 username 加了唯一索引后，数据库就不允许插入两个相同的用户名。

3. 建表时创建唯一约束

也可以在建表时直接写：

create table emp (
id int unsigned primary key auto_increment,
username varchar(20) not null unique,
name varchar(10) not null
);

这里的：

unique

会为 username 创建唯一约束，一般也会对应唯一索引。

五、查看索引

可以使用下面的 SQL 查看某张表有哪些索引：

show index from emp;

或者：

show indexes from emp;

通过这个命令可以看到索引名称、索引字段、是否唯一等信息。

如果想删除索引，可以使用：

drop index idx_emp_username on emp;

不过实际项目中不要随便删除索引，因为可能会影响查询性能。

六、索引为什么能提高查询速度？

MySQL 中常见索引底层结构是 B+Tree。

这里不展开很深的底层源码，先用通俗方式理解。

如果没有索引，查询可能像这样：

从第一条数据开始，一条一条找

如果有索引，数据库会维护一个有序的数据结构。

比如按照 username 建索引后，数据库会把 username 的值按一定规则组织起来。

查询时不需要扫描整张表，而是通过索引结构逐步缩小查找范围。

类似我们查字典：

先定位首字母
再定位页码范围
最后找到具体单词

所以索引能减少扫描数据的数量，从而提升查询速度。

七、常见索引类型

1. 主键索引

主键自动创建索引。

id int primary key auto_increment

特点：

• 一张表只能有一个主键
• 主键不能重复
• 主键不能为空
• 根据主键查询通常很快

2. 唯一索引

唯一索引用来保证字段值唯一。

create unique index idx_emp_username
on emp(username);

适合场景：

• 用户名
• 手机号
• 邮箱
• 身份证号
• 订单号

前提是这些字段在业务上确实不能重复。

3. 普通索引

普通索引只用于提高查询速度，不限制字段值是否重复。

create index idx_emp_name
on emp(name);

适合经常作为查询条件的字段。

4. 联合索引

联合索引是给多个字段一起创建索引。

create index idx_emp_dept_gender
on emp(dept_id, gender);

适合多个字段经常一起作为查询条件的场景。

比如：

select *
from emp
where dept_id = 1 and gender = 1;

这时候联合索引可能会发挥作用。

八、联合索引和最左前缀原则

联合索引有一个很重要的原则：最左前缀原则。

比如创建了联合索引：

create index idx_emp_dept_gender_salary
on emp(dept_id, gender, salary);

这个索引的字段顺序是：

dept_id -> gender -> salary

下面这些查询可能会用到索引：

where dept_id = 1

where dept_id = 1 and gender = 1

where dept_id = 1 and gender = 1 and salary > 8000

但是如果查询条件没有从最左边字段开始，比如：

where gender = 1

就不一定能充分使用这个联合索引。

所以创建联合索引时，字段顺序很重要。

简单记忆：

联合索引要从最左边的字段开始使用。

九、哪些字段适合加索引？

不是所有字段都适合加索引。

适合加索引的字段通常有这些特点：

比如员工表中：

username
dept_id
phone
create_time

这些字段都有可能适合加索引。

字段区分度是什么？

字段区分度可以理解为：这个字段的值是否足够分散。

比如 username 基本每个人都不一样，区分度高。

但是 gender 只有 1 和 2，区分度很低。

如果给 gender 单独加索引，效果可能不明显。

因为查询：

where gender = 1

可能会查出一半数据。

这种情况下，索引的意义就没有那么大。

十、索引不是越多越好

很多初学者容易有一个误区：

既然索引能提高查询速度，那是不是索引越多越好？

不是。

索引虽然能提高查询速度，但是也有代价。

1. 索引会占用磁盘空间

索引本身也是数据结构，需要存储。

索引越多，占用空间越多。

2. 索引会影响增删改性能

当我们插入、修改、删除数据时，数据库不仅要维护表数据，还要维护索引。

比如新增一名员工：

insert into emp(username, name, dept_id)
values('zhangsan', '张三', 1);

如果表上有多个索引，数据库还要更新这些索引结构。

所以索引越多，写入成本越高。

3. 索引需要维护

项目发展过程中，SQL 会变化。

以前有用的索引，后面可能不再使用。

所以索引也需要根据实际查询情况维护，而不是随便加一堆。

十一、索引失效的常见情况

加了索引，不代表一定会用上索引。

下面这些写法可能导致索引失效。

1. 对索引字段使用函数

假设 name 有索引：

create index idx_emp_name on emp(name);

如果这样写：

select *
from emp
where substring(name, 1, 1) = '张';

可能会导致索引不好使用。

因为数据库需要先对字段做函数计算，再比较结果。

2. 模糊查询以 % 开头

如果 name 有索引：

where name like '张%'

这种以固定内容开头的模糊查询，可能还能利用索引。

但是：

where name like '%张%'

因为前面是 %，数据库很难从索引开头定位，可能无法很好使用索引。

不过后台管理系统里姓名搜索经常需要包含查询，所以这里要根据业务权衡。

3. 对索引字段进行运算

假设 salary 有索引：

where salary + 1000 > 10000

这种对字段做计算的写法可能影响索引使用。

更推荐写成：

where salary > 9000

4. 类型不一致

比如字段是字符串：

phone varchar(11)

查询时写：

where phone = 13800000000

这就把手机号当数字用了。

更推荐写成：

where phone = '13800000000'

字段类型和查询值类型保持一致，更利于索引使用，也更规范。

十二、使用 explain 查看 SQL 执行情况

想知道 SQL 有没有使用索引，可以使用：

explain

比如：

explain select *
from emp
where username = 'zhangsan';

explain 会返回 SQL 的执行计划。

初学阶段可以重点看几个字段：

如果 key 显示使用了某个索引，说明这条 SQL 可能走了索引。

如果 rows 很大，说明扫描数据可能比较多。

explain 是后面做 SQL 优化时很常用的工具。

十三、索引在 Java 项目中的常见场景

在 Spring Boot + MyBatis 项目中，索引通常不是写在 Java 代码里，而是在数据库表结构中设计。

比如员工登录：

select *
from emp
where username = #{username} and password = #{password};

这个查询经常根据用户名查用户，所以 username 适合加唯一索引。

create unique index idx_emp_username
on emp(username);

再比如员工列表按部门查询：

select *
from emp
where dept_id = #{deptId}
order by update_time desc;

如果这个查询很频繁，可以考虑建立联合索引：

create index idx_emp_dept_update_time
on emp(dept_id, update_time);

这样索引设计就和具体业务 SQL 关联起来了。

十四、实际开发建议

在项目中使用索引时，可以参考下面几点：

十五、常见问题总结

1. 索引一定能提高查询速度吗？

不一定。

如果表数据量很小，索引提升可能不明显。

如果字段区分度很低，索引效果也可能不好。

2. 为什么加索引会影响新增、修改、删除？

因为增删改数据时，数据库不仅要改表数据，还要维护索引结构。

所以索引会提高查询效率，但会增加写入成本。

3. 性别字段适合单独加索引吗？

一般不太适合。

因为性别字段的值太少，区分度较低，单独加索引效果可能不明显。

4. 联合索引字段顺序重要吗？

重要。

联合索引遵循最左前缀原则，字段顺序会影响索引使用效果。

5. 怎么知道 SQL 有没有走索引？

可以使用：

explain select ...

查看执行计划中的 key 字段。

十六、总结

这一篇主要学习了数据库索引。

索引可以理解为数据库表的目录，它能帮助数据库更快地定位数据，从而提高查询速度。

常见索引包括主键索引、唯一索引、普通索引和联合索引。联合索引要注意最左前缀原则。

不过索引不是越多越好。索引会占用空间，也会增加增删改时的维护成本。所以索引设计一定要结合实际 SQL 场景。

对于 Java 后端项目来说，索引通常和登录查询、条件查询、多表关联、排序分页等场景密切相关。写项目时不仅要会写 SQL，也要慢慢学会判断哪些字段应该加索引。

下一篇我们继续学习数据库中另一个非常核心的知识点：事务。