MySQL索引

MySQL

1.什么是索引

定义:索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。

索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

索引的优点:

一句话就是:提高数据的检索速度

建立索引的缺点:

1 需要占用磁盘,消耗数据库资源

2 在对数据进行增删改的时候,因为有索引的存在,会去改变索引,结果就是会影响效率

一般需要建立索引的字段

1、经常用在where语句之后的字段

2、主键或者外键

3、字段具有唯一性的时候建立唯一性索引

4、在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的

索引主要分为四种:普通索引、主键、唯一索引、复合索引

普通索引:一个索引只包含单个列,一个表可以有多个单列索引

​ 建表时创建

只需要在建表时加入key(需要建索引的字段名),但这种不能指定索引名,会默认使用和字段一样的名
例如:create table xxxx(id varchar(10) primary key,name varchar(2),key(name));

​ 建表后创建

create index 索引名字 on 表名(需要建索引的字段名)

主键:数据库自动建立索引

唯一索引:索引列的值必须唯一,但允许空值(只能有一个null)

建表时创建

只需要在建表时加入unique(需要建索引的字段名),但这种不能指定索引名,会默认使用和字段一样的名
例如:create table xxxx(id varchar(10) primary key,name varchar(2),unique(name));

建表后创建

create unique index 索引名字 on 表名(需要建索引的字段名)

复合索引:一个索引包含多个列(用索引时要遵循:最左前缀原则)

MySQL在查询中会调整查询字段顺序,这样可能更好利用索引

建表时创建

只需要在建表时加入key(需要建索引的字段名,需要建索引的字段名),但这种不能指定索引名,会默认使用和字段一样的名
例如:create table xxxx(id varchar(10) primary key,name varchar(2),key(name,...,...));

建表后创建

create index 索引名字 on 表名(需要建索引的字段名,需要建索引的字段名,...)

底层结构

索引原理:

在我们无序插入主键为id的多条数据时,查询结果却是有序的?

这是为什么?肯定是因为主键索引啊

类似于链表,在插入时就会把id为1的放在前面,整个数据结构由三部分组成,第一部分放索引值(id的值),第二部分放值,第三部分是一个指针(指向下一个索引),这就使整个数据全部链接起来了(但万一要查的那个值的索引特别大呢,所以就又优化了)

分页

页目录的大小和分页的大小都是默认16kb,然后再做一个页目录,这里面存储着每页里面第一个存储值得索引值(id的值)和指针;指针是指向的哪一页了。一般来说这种树有三层就很大了

聚簇索引:

数据存储与索引放在一起

非聚簇索引:

除了聚簇索引,其他的都是非聚簇索引,比如name,age 等在查找时会经历两次io,第一次是找到存储的id,然后拿到id再去聚簇索引上去查看:为什么需要存储id,不直接存储对应值的路径呢?因为数据的增删改操作,然后存储路径就需要经常改,增大了系统开销,然而我如果存储的是id的话,id不会变,这里是经过优化了的

在innodb中,在聚簇索引之上建立的索引称之为辅助索引,非聚簇索引都是辅助索引,比如复合索引,前缀索引,唯一索引;辅助索引叶子节点存储不再是行的物理位置,而是主键值,辅助索引访问数据需要二次查找

使用聚簇索引的优势:

由于行数据和叶子节点存储在一起,同一页中会有多条行数据,访问同一数据页不同行记录时,已经把页加载到了Buffer中,再次访问的时候,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起被载入内存的,找到叶子节点就可以立刻将行数据返回了,如果按照主键Id来组织数据,获得数据更快。

聚簇索引适合用在排序的场合,非聚簇索引不适合

取出一定范围数据的时候,使用用聚簇索引速度快

可以把相关数据保存在一起。

聚簇索引需要注意点:

当使用主键为聚簇索引时,主键最好不要使用uuid,uuid太过离散。

为什么主键通常建议使用自增id

减少物理位置的调整,因为自增的话,索引都是相邻的,所以对应的数据在磁盘上存储位置也是相邻的。

什么情况下无法使用索引

查询语句中使用LIKE关键字

查询语句中使用多列索引

查询语句中使用OR关键字

posted @ 2021-05-25 22:12  努力加油天天向上  阅读(36)  评论(0编辑  收藏  举报