MySql教程

mysql基础_索引

本文主要是介绍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 = '很好';

image-20230509000900955

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

image-20230509000837646

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

索引的分类

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

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

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

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

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

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

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

例如:

image-20230509124926486

索引失效的时候

%开头

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

image-20230509131653140

or两边有一边没有索引

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

image-20230518233743577

索引参与运算

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

image-20230518234409884

索引使用函数

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

image-20230518234751351

没有使用最左侧的列查询

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

image-20230519000710761

面试题

1.索引的原理

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

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

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

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

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

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

这篇关于mysql基础_索引的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!