MySQL数据库——索引

时间:2024-09-30 07:13:48

目录

什么是索引(Index)?

怎样加索引?

索引的特点

索引类型

主键索引(Primary Key)

辅助索引(二级索引)

聚集索引和非聚集索引

聚集索引

非聚集索引

单列索引和联合索引

单列索引

联合索引

创建索引

删除索引

创建索引的原则

一.合适创建索引的字段

1.不为 NULL 的字段

2.被频繁查询的字段

3.被作为条件查询的字段

4.被经常频繁用于连接的字段

二.不合适创建索引的字段

1.被频繁更新的字段应该慎重建立索引

2.不被经常查询的字段没有必要建立索引

3.尽可能的考虑建立联合索引而不是单列索引

4.注意避免冗余索引

5.考虑在字符串类型的字段上使用前缀索引代替普通索引

使用索引一定会提高性能吗?(面试题)


什么是索引(Index)?

索引就像词典的目录,我们想去查找某个字的时候,先去目录定位字的位置,然后根据位置去找到字。不用一页一页逐个翻找,大大加快了我们找到目标的效率。

MyISAM和Innodb都使用了B+树这种数据结构做为索引(数据库默认存储引擎Innodb)

怎样加索引?

CREATE INDEX 索引名称 ON 表名 (列名, 列名, ...);

索引的特点

        1、显著加快数据的检索效率;

        2、需要占用额外的物理空间存储索引;

        3、索引的创建和维护需要耗费时间。

索引类型

主键索引(Primary Key)

一张表的主键就是主键索引,每张表只能有一个主键且不能为null,主键是唯一的、不可重复。主键可以直接定位数据。

辅助索引(二级索引)

辅助索引的作用原理是:根据辅助索引找到主键,然后根据主键索引获得目标数据。(辅助索引并不直接存储数据的位置,而是存储主键的位置)

除了主键索引之外的其他的所有索引都是辅助索引。

回表:即 二次查询: 当查到索引对应的指针或主键后,可能还需要根据指针或主键再到数据文件或表中查询。

如果,要查询的字段name 已经建立了索引,索引的key就是name的值,这种情况下就可以直接把查到的name返回,不需要再“回表”查询,这种情况就是覆盖索引。

聚集索引和非聚集索引

聚集索引

聚集索引:索引结构和数据一起存放,聚集索引的排列顺序与表中数据的排列顺序是一致的(数据会按照索引的顺序在物理空间按顺序存储),所以它的查询效率非常高;但是由于物理连续,进行修改时所需要的操作就会很慢(例如在中间某位置插入新数据时,后边的数据都要往后移动)。

MySQL的默认存储引擎是InnoDB,所以创建的主键索引就是聚集索引。

非聚集索引

非聚集索引:索引结构和数据分开存放,数据在逻辑上是顺序存放的,物理上是散列存放的。

除了聚集索引之外的,都是非聚集索引。

聚集索引非聚集索引MySQL都是通过B+树实现的。

单列索引和联合索引

单列索引

即由一个列属性组成的索引

联合索引

即由多个列属性组成的索引

创建索引

1.对Departments表上的DepartmentName列建立唯一性索引。

2.在Employees表的Name列和Address列上建立联合索引。

删除索引
DROP INDEX idx_name_address ON Employees

创建索引的原则

一.合适创建索引的字段

1.不为 NULL 的字段

索引字段的数据应该尽量不为 NULL,因为对于数据为 NULL 的字段,数据库较难优化。如果字段频繁被查询,但又避免不了为 NULL,建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代。

2.被频繁查询的字段
3.被作为条件查询的字段

被作为 WHERE 条件查询的字段,应该被考虑建立索引。

4.被经常频繁用于连接的字段

经常用于连接的字段可能是一些外键列,对于外键列并不一定要建立外键,只是说该列涉及到表与表的关系。对于频繁被连接查询的字段,可以考虑建立索引,提高多表连接查询的效率。

二.不合适创建索引的字段

1.被频繁更新的字段应该慎重建立索引

虽然索引能带来查询上的效率,但是维护索引的成本也是不小的。 如果一个字段不被经常查询,反而被经常修改,那么就更不应该在这种字段上建立索引了。

2.不被经常查询的字段没有必要建立索引

简单理解为每个索引都对应着一颗 B+树,需要占用磁盘空间

3.尽可能的考虑建立联合索引而不是单列索引

如果一个表的字段过多,索引过多,那么当这个表的数据达到一个体量后,索引占用的空间也是很多的,且修改索引时,耗费的时间也是较多的。如果是联合索引,多个字段在一个索引上,那么将会节约很大磁盘空间,且修改数据的操作效率也会提升。

4.注意避免冗余索引

冗余索引指的是索引的功能相同,能够命中就肯定能命中。如(name,city )和(name )这两个索引就是冗余索引,能够命中后者的查询肯定是能够命中前者的。

5.考虑在字符串类型的字段上使用前缀索引代替普通索引

前缀索引仅限于字符串类型,较普通索引会占用更小的空间,所以可以考虑使用前缀索引带替普通索引。

使用索引一定会提高性能吗?(面试题)

大多数情况下会提高性能。首先谈谈你自己对索引的理解,然后指出:索引查询都是比全表扫描要快非常多的,但是维护索引也需要消耗一定的性能,如果数据库的数据量不大,那么使用索引也不一定能够带来很大提升。