mysql基础_索引

介绍

“索引”是为了能够更快地查询数据。比如一本书的目录，就是这本书的内容的索引，读者可以通过在目录中快速查找自己想要的内容，然后根据页码去找到具体的章节。

优缺点

优势：以快速检索，减少I/O次数，加快检索速度；根据索引分组和排序，可以加快分组和排序；

劣势：索引本身也是表，因此会占用存储空间。索引的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表

何时添加索引

我们在下面三种情况下会考虑给字段添加索引：

1.数据量庞大

毋庸置疑，数据量庞大时添加索引明显能够提升查询效率

2.字段作为查询条件

该字段老是出现在where语句后面，老是被扫描。

3.该字段很少进行插入，修改，删除操作

因为做这些操作，索引需要重新排序

注意：1.主键上，以及unique字段上都会自动添加索引的（所以查询的时候尽量通过有主键或者带有unique字段进行查询）

​ 2.建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能

索引语法

创建索引

create index student_name_index on student(name);

这条命令的意思是：在学生表student的name字段上创建索引，名字是student_name_index，使用的时候结合自己的表来替换对应的东西。

删除索引

drop index student_name_index on student;

这条命令的意思是：将学生表student上的student_name_index索引对象删除，使用的时候结合自己的表来替换对应的东西。

是否使用索引检索

举例：

对一张comment表的content字段进行查询，发现查询出来的行数是19行，而这张表的行数也是19行，没有使用索引检索，如下：

explain select * from comment where content = '很好';

给该字段创建索引，然后再进行查看。

有使用索引进行查询，查询出来的条数是两条（因为我这个表里content字段有两条记录是“很好”。）

索引的分类

单一索引：顾名思义，就是在一个字段上添加索引。

语法跟上面说的一样，这里不重复。

主键索引：这是在主键上添加索引，建表的时候通过 PRIMARY KEY(字段) 这种方式来设置主键，同时自动添加索引，注意，一张表只能有一个主键。

唯一索引：与单一索引类似，索引列的值必须唯一，允许有空值。

create unique index 索引的名字 on 表名(字段);

组合索引：两个或者更多的字段添加索引

alter table 表名 add index 索引的名字(字段，字段，字段);

例如：

索引失效的时候

%开头

字段采用模糊查询的时候是以“%”开头了，这种情况要尽量避免，如下图，查询还是全部查出来了。

or两边有一边没有索引

查询的时候，使用or。使用or那么要求or两边的条件字段都要有索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个字段上的索引也会失效，如下图。

索引参与运算

在查询条件中标记为索引的列参与运算，索引就失效。

索引使用函数

在查询条件中标记为索引的列使用函数，索引就失效了。

没有使用最左侧的列查询

使用复合索引的时候，查询时没有使用最左侧的列进行查询，索引失效。

面试题

1.索引的原理

MySQL 中的索引是通过 B+ 树实现的。B+ 树是⼀种多叉树，它可以将数据按照⼀定的顺序组织起来，从而提高查询效率。

B+ 树的所有数据都存储在叶子节点上，而非叶子节点只存储索引，这样可以提高数据查询效率。B+ 树的叶子节点之间使用指针相连，这样可以实现区间查找，也就是说，可以快速定位某个区间内的数据。

在 MySQL 中，B+ 树的实现主要是通过 InnoDB 存储引擎来实现的。InnoDB 存储引擎中的索 引主要有聚簇索引和辅助索引两种类型，聚簇索引是根据主键创建的索引，而辅助索引是根据非主键列创建的索引。

对于辅助索引，MySQL 中会同时创建⼀个对应的聚簇索引，这样可以提高查询效率。

2.覆盖索引和联合索引是什么？讲⼀下索引的最左前缀匹配原则。

覆盖索引和联合索引是数据库中常见的两种索引类型。 覆盖索引是指⼀个包含了所有查询需要的列的索引，查询时可以直接从索引中取到需要的数据，而不需要再回到表中查找，从而可以提高查询效率。 联合索引是指使用多个列组合起来作为⼀个索引，可以同时查询多个列，以提高查询效率。联合索引可以包含多个列，但是查询时只能使用前缀列进行查询，即只有在查询中使用了联合索引的前几个列，才能利用联合索引进行查询。如果查询中没有使用前缀列，那么联合索引就不能发挥作用，需要使用单独的索引或全表扫描。最左前缀匹配原则是指如果⼀个联合索引包含了多个列，那么在查询时只能使用前面的列进行匹配。 例如，⼀个联合索引包含了 A、B、C 三列，那么查询时只能使用A、AB 或 ABC 进行匹配， 而不能只使用 B 或 C 进行匹配。这是因为如果查询时使用的列不是最左前缀列，那MySQL 就⽆法使⽤索引进行查询，会导致全表扫描，从而降低查询效率。 在实际的应用中，覆盖索引和联合索引可以结合使用，以提高查询效率。同时，使用最左前缀匹配原则可以让我们更加合理地设计索引，从而提高查询性能。