mysql 索引原理

什么是回表

回表的概念，去根据索引搜索到数据，但是索引中没有要查找的数据，就要去定位到那个行，去把数据行中的数据搜索出来【这个就是回表】【经过两次查询】

回表触发的条件【什么时候会回表】
- 查询普通索引【辅助索引 B+ 树存的是主键，再去通过主键去查询】
- 没有使用覆盖索引的时候，就会触发回表
  - 数据如果能直接从索引中查询出来，就会走覆盖索引，不需要回表
  - 如果没法覆盖索引，就会走普通索引的回表

回表例子应用

有一个表，我给时间加了索引，然后我的查询是 where a.create_time > now_time, 结果经过 explain 查看，发现没有走索引，原因是什么？

首先给时间加的这个索引是一个非聚集索引，走非聚集索引会出现回表现象，会经过2次查询，sql执行器认为直接全表扫描只用一次查询的话，效率更高，因此使用了全表扫描，而不使用这个索引。

什么是覆盖索引

数据之间在索引里面，之间把数据从索引里面读取出来即可，这个就是覆盖索引。

学习视频

如果一个索引覆盖（包含）了所有需要查询的字段的值，这个索引就是覆盖索引。因为索引中已经包含了要查询的字段的值，因此查询的时候直接返回索引中的字段值就可以了，不需要再到表中查询，避免了对主键索引的二次查询【又叫回表】，也就提高了查询的效率。

B+ 树

**最左前缀匹配原则：**在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

　　要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

mysql 就是从左往右去检索数据的，这个叫做联合索引。

聚集索引

聚簇索引也叫聚集索引，它并不是一种单独的索引类型，在聚簇索引的叶子页中，保存了整张表的行数据信息，所以也将聚簇索引的叶子节点称为数据页。名词 “聚簇” 表示数据行和相邻的键值紧凑的存储在一起。因为不能同时把数据行存储在两个不同的地方，所以一个表只能有一个聚簇索引。

非聚集索引

辅助索引也叫非聚簇索引、非聚集索引、二级索引等。辅助索引跟聚簇索引的区别在于，聚簇索引叶子节点中保存了完整的行数据，而辅助索引叶子节点中保存的是聚簇索引中的索引键值。辅助索引的存在不影响数据在聚簇索引中的组织，因此每张表中可以有多个辅助索引。

对于面试的总结

局部性原理

磁盘预读：（预读的长度一般为页 (page) 的整数倍）

页是存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多操作系统中，页的大小通常为 4k) ,主存和磁盘以页为单位交换数据。

主存和磁盘是以页为单位的。磁盘预读的长度一般为页的整数倍。

Innodb是通过B+Tree结构对主键创建索引，然后叶子节点中存储记录，如果没有主键，选择唯一键，如果没有唯一键，选择6字节的row_id来进行存储
如果创建的索引是其它字段(不是主键)，那么在叶子节点中存储的是该记录的主键(不是数据) ，主键有个指针，指向数据行，然后再通过主键索引查找对应的记录

普通索引和主键索引的区别
1. 普通索引的是 B+ 树，叶子节点存的是主键，主键有个指针指向那个行，和主键索引的区别就是，会查询2次，这个叫做回表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10



show databases;
use blog_oj;
show tables;
show index from t_user; # 查看表的索引信息
 desc t_user; # 查看表结构
 
 show create table t_user; # 查看mysql这张表建表信息
 
 

1
2
3
4
5
6
7
8
9


# 建立索引

altar table t_user add index `user_name` (user_name);
alter table t_user add index index_username (`username`);

alter table t_user drop index index_username;

create index index_username on t_user (username);
alter table t_user drop index index_username;

索引和数据存储位置

对于InnoDB

索引和实际的数据都是存储在磁盘上的，只不过在进行数据读取的时候会优先把索引加载到内存中

存储引擎：不同的数据文件在磁盘中有不同的组织形式

MyIsam存储引擎

.frm 表结构

.MYD 数据文件

.MYI 索引

InnoDb存储引擎

.frm 表结构

.ibd 索引文件+数据文件

索引的分类

主键索引
- 唯一性索引，一个表只有一个主键
唯一索引
- 所有值只能出现一次
普通索引
- 可以为空，无唯一性现在
全文索引
- full text
组合索引
- 多个列值组成一个索引，用于组合搜索

6、操作系统基础知识

6.1、局部性原理

时间局部性

之前被访问过的数据很有可能再次被访问
空间局部性

数据和程序都有聚集成群的倾向

缓存的应用：网站访问数据的特点大多数呈现在"二八定律":80%的业务访问集中在20%的数据上。这时为了减轻数据的压力和提高网站的数据访问速度，则可以使用缓存机制来优化网站。

最左匹配原则

问怎么理解最左匹配原则；

我们最重要的一点就是答 B+ 树的结构：

B+树存储结构就是从左到右存储
查询的时候索引也是从左到右匹配这样子

mysql执行流程

解决这个面试题的方法：

连接器：负责与用户连接
从编译原理的角度看问题：
1. 先进行词法分析，然后语法分析
对sql语法进行优化，【有索引就走索引】
执行sql ，查询数据

其他

[[..\八股文专题【面试八股文】\mysql相关.md]]

mysql索引原理

文章目录