Java找工作必备知识——day03【MySQL索引原理】

MySQL索引

1.索引的基本概述

什么是索引？

官方定义：一种帮助MySQL提高查询效率的数据结构

🐐 索引的优点：

• 大大加快了数据查询的速度

🐖索引的缺点:

• 维护索引需要耗费数据资源
• 索引须要占用磁盘空间
• 当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响。

索引的分类⭐️

InnoDB支持：

• 主键索引：默认数据库会自动为主键简历索引，innodb为聚簇索引

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引；（单列索引，普通索引）

• 唯一索引：索引列的值必须唯一，但允许有空值

• 复合索引：一个索引中包含多个列，索引的创建由多个列组合到一起，基于多个列查的时候。

• 全文索引：5.7之前只有MyISAM支持。【应用不多】

索引的创建

show index for 表名； 查看表中的索引

• 主键索引：

  创建表的时候会自动创建

• 普通索引的创建（key）

  • 建表的时候创建

    create table t_user(id varchar(20) primary key, name varchar(20) ,key(name));
    

    无法自定义索引名，默认与列名一致。

  • 建表之后创建：

    create index 索引名字 on 表(列)；
    

    

• 唯一索引（unique）

  • 建表的时候创建

    create table t_stu(id varchar(20) primary key,name varchar(20),unique(name));
    

    无法自定义索引名，默认与列名一致；
    

  • 建表之后创建

    create unique index 索引名 on  表名(列名)；
    

• 复合索引

  • 建表时创建

    create  table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
    

  • 建表后创建

    create index 索引名 on 表名(列名1，列名2)；
    

  经典面试题：

  最左前缀原则：只有从左开始的可以利用索引例如：name ;name age; name age bir;

  mysql引擎在查询的时候为了更好的利用索引，在查询过程中会动态调整字段顺序

  经典问题：创建的时候顺序为name，age，bir；

  下列查询的时候能否利用索引？

  • name,bir,age能否利用索引? 【可以，会自动调整顺序】

  • name age bir 能都利用索引？【肯定可以】

  • age bir 能否利用索引 【不可以，不包含左前缀】

  • bir age name 能否利用索引？【可以】

  • age bir 能否利用索引？【不可以，不包含左前缀】

MySQL索引的底层原理

😇 总结：我们在放入数据的时候，会基于数据进行排序，然后以链表的形式把数据连接起来。mysql为了底层进一步优化，基于b+树的形式存储，以一页一页的存储。默认一页的大小是16kb，一个三层的b+树就可以存大概10亿条数据了。

B+树，只有叶子节点存储数据，非叶子存储（key值信息）页的首个索引和指向页的指针。B树非叶子节点也存储数据。

顶层页常驻内存，查找某一键值的记录最多只需要1-3次磁盘I/O操作

优化手段：主键用int（可以排序）

聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

一般主键索引一定是聚簇索引

一个表中只能有一个聚簇索引

在innodb中，在聚簇索引之上简历啊的索引称为辅助索引，总是需要二次查找

为什么不记录地址呢？而是记录id？？

增删改会修改树上的地址！

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

MyISAM使用的是非聚簇索引

---

什么时候无法利用索引呢？？