MySQL索引底层

MySQL索引底层

彼方 800 2022-07-14

索引

1.索引是什么

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

优点：加快数据的查询速度

缺点：占用空间，需要额外维护

2.索引的类型

  • 主键索引：设定为主键后会自动创建主键索引，innoDB为聚簇索引

  • 普通索引：同一个索引只能包含单个列，一个表可以有多个普通索引

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

  • 组合索引：同一个索引包含多个列

  • 全文索引（Mysql5.7版本之前，只能用于MyISAM引擎）

3.创建索引的方式

日常工作中创建索引一般会创建：普通索引、组合索引

查看索引：

show index from 表名;

删除索引：

drop index 索引名 on 表名；

1.主键索引：

  • 建表时创建索引
create table t_address(id varchar(20) primary key,name varchar(20));
  • 查看索引
show index from t_address;

2.普通索引：

  • 建表时创建索引
create table t_address(id varchar(20) primary key,name varchar(20),key(name));
  • 建表后创建索引
create index nameindex on t_user(name);

3.唯一索引：

  • 创建表时创建索引：
create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
  • 建表后创建索引
create unique index nameindex on t_user(name);

4.组合索引

  • 创建表时创建索引
create table t_user(id varhcar(20) primary key,name varchar(20),age int,key(name,age));
  • 建表后创建索引
create index nameageindex on t_user(name,age);

4.最左匹配原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。

以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。

注意：MySQL引擎在查询为了更好的利用索引，会在查询过程中动态的调整字段顺序以便利用索引

例如某表现有索引(a,b,c)，现在你有如下语句：

select * from t where a=1 and b=1 and c =1;     #这样可以利用到定义的索引（a,b,c）,用上a,b,c

select * from t where a=1 and b=1;     #这样可以利用到定义的索引（a,b,c）,用上a,b

select * from t where c=1 and a=1;     #这样可以利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器）

select * from t where a=1;     #这样也可以利用到定义的索引（a,b,c）,用上a

select * from t where b=1 and c=1;     #这样不可以利用到定义的索引（a,b,c）

select * from t where a=1 and c=1;     #这样可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到

5.索引的底层结构

image-20220714205326483

image-20220714204638605

image-20220714204435245

MySQL中的索引底层使用的是B+Tree的结构，以上分别为B-Tree与B+Tree的简图，可以看到B+树所有数据记录节点都时按照键值大小顺序存放在同一层的叶子节点中，而非叶子节点只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+树与B树的不同：

  • 非叶子节点只存储键值信息
  • 所有叶子节点之间都通过链指针连接
  • 数据记录都存放在叶子节点中

面试题：说一下索引的底层原理

mysql数据库在插入数据的时候，会对数据进行一个排序，排序之后会将数据以链指针链接起来，同时mysql底层是以B+树的结构，将数据以一页一页的格式进行存储，默认每一页的大小为16kb，以数据层级的角度来看，一个三层的B+树的结构可以存储8-10亿的数据，而我们项目中一般两层就足够了，如果按照两层的结构来进行进行主键查询的话，最多动用一次磁盘IO就可以了，因为B+树的顶层是常驻内存的。

6.聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放在一起，索引结构的节点节点保存了行数据（主键索引）

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

  • InnoDB：

    • innoDB使用的使用聚簇索引，将主键组织到一棵B+Tree中，行数据就存储在叶子节点中，如果使用where id = 1这种条件查询数据，则按照B+Tree的检索算法，可直接查找到对应的叶子节点，从而获取行数据
    • 如果是对非主键列进行条件搜索，则需要两步骤：第一步是在辅助索引B+Tree（注意：这是第二棵树）中检索非主键字段到达其叶子节点获取主键值；第二步再根据查询到的主键到主索引B+Tree中执行一次检索操作，最终到达叶子节点获取行数据。（通过其他键需要建立辅助索引，非聚簇索引都是主键索引）
    • 聚簇索引默认是主键，如果表中没有定义主键，则会选择一个唯一且非空的索引代替，如果没有这样的索引，innoDB会隐式定义一个主键来作为聚簇索引。

image-20220714210528160

  • MyISAM

    • MyISAM使用的是非聚簇索引，非聚聚簇索引也有两棵B+Tree，节点的结构一致只是存储的内容与聚簇节点不同，非聚簇索引的的主键索引B+Tree的节点存储了主键，辅助键索引B+树存储了辅助键。但这两棵树的叶子节点都使用一个地址指向了真正的表数据，因此这两棵索引树是独立的，通过辅助键索引无需访问主键的索引树即可查到到行数据。

image-20220714212240050

使用聚簇索引的优势：

- 问题：既然聚簇索引每次使用辅助索引都要经历两次B+Tree查找，而非聚簇索引一次就可以找到数据，为什么还要使用聚簇索引？

- 答：
	1.由于行数据和聚簇索引的叶子节点存储在一起，所以同一页会有多条数据，而访问同一页数据不同记录时，由于上一次查询已经把页加载了缓存器中，所以再次查询的时候，会直接从缓存中读取，避免了重复的IO访问。
	2.聚簇索引的辅助索引的叶子节点存储的是主键值而不是数据存放地址，这样在数据发生变化时，避免了辅助索引的维护工作，只需要维护聚簇索引树即可。

7.为什么选用B+Tree

  • hash结构很快，但每次IO只能取一个数，且不能进行范围查找同时不支持最左匹配原则
  • AVL树（自平衡二叉树）和 红黑树，在大量数据的情况下，IO操作还是太多
  • B树每个节点内都存储的是数据，因此每个节点存储的分支太少
  • B+节点存储的是索引+指针(引用指向下一个节点)，可以存储大量索引，同时最终数据存储在叶子节点，可以在2-3次的IO操作内完成千万级别的表操作。

8.为什么主键不推荐用UUID（聚簇索引）

因为UUID的值太过离散，不适合排序且又可以出现新增记录的UUID会插入到索引树的中间位置，导致索引树调整复杂度大，消耗更多的时间和资源。

9. 什么情况下索引会失效

  • 关联查询字段不一致
    • 关联join查询时，关联字段的类型不一致会导致索引失效
  • 查询语句中使用了LIKE关键字
    • 使用LIKE关键字查询时，如果匹配的第一个字符为 %，则一定不会使用索引，如果 % 不是在第一个位置，则会使用索引
  • 查询语句中使用了组合索引
    • 组合索引只有在使用组合字段中的第一个字段时才会走索引（最左匹配原则）
  • 查询语句中使用了OR关键字
    • 查询语句只有OR关键字时，如果OR前后的两个条件列都添加了索引，则查询会使用索引，如果OR前后的两个条件列有一个没有添加索引，则查询不会使用索引
MySQL