mysql的各类索引+在B+树的体现

冷不防 2024-03-17 17:51 45阅读 0赞

**目录**

一.四个索引类型

1、单列索引：一个索引只包含单个列，除了主键索引,一个表中可以有多个单列索引。

2.多列索引:一个多列索引包含多个列

1.联合主键

1)什么是联合主键

2)为什么要使用联合主键

2.组合索引

1)什么是组合索引

2)为什么要使用组合索引

3.最左原则

3.Full Text 全文索引

4.SPATIAL 空间索引

二.两者索引方法,btree索引和hash索引的区别

三. 索引在B+树的工作原理

1.我先建了一个表

2.主键索引(聚簇索引)

3.普通索引(二级索引,非聚簇索引)

4.组合索引

五:补充

补充1:覆盖索引

--------------------

MySQL的索引分四类：

单列索引（Normal普通索引、Unique唯一索引、主键索引）、

多列索引 (联合主键,组合索引)、

Full Text 全文索引

SPATIAL 空间索引

MySQL的索引方法分两类：

btree索引和hash索引

### 一.四个索引类型 ###

#### 1、**单列索引：**一个索引只包含单个列，除了主键索引,一个表中可以有多个单列索引。 ####

（1）Normal 普通索引(二级索引,非聚簇索引)：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹是为了查询数据更快一点。

（2）Unique 唯一索引(二级索引,非聚簇索引)：索引列中的值必须是唯一的，但是允许为空值。

（3）主键索引**(**聚簇索引**)**：是一种特殊的唯一索引，非空且唯一。（主键约束，就是一个主键索引）。

> 主键索引与唯一索引的区别：
> 
> （1）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。
> 
> （2）主键创建后一定包含一个唯一性索引，唯一性索引并不一定是主键。
> 
> （3）唯一性索引列允许空值，而主键列不允许为空值。
> 
> （4）主键索引在创建时，已经默认为非空值+唯一索引了。
> 
> （5）一个表最多只能创建一个主键索引，但可以创建多个唯一索引。
> 
> （6）主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
> 
> （7）主键可以被其他表引用为外键，而唯一索引不能。

#### 2.**多列索引:一个多列索引包含多个列** ####

##### **1.联合主键** #####

###### **1)什么是联合主键** ######

(也叫复合主键、`组合主键`),一个表中有多个主键列组合在一起,叫联合主键,一个表中只能有一个联合主键.

> create table test  
> (  
> name varchar(19),  
> id number,  
> value varchar(10),  
> primary key (name,id)  
> )

###### **2)为什么要使用联合主键** ######

比如一个部门人员表,你不想单独建一个id列作为主键的话,就可以用 部门列 和 员工name 列都设为主键,作为表的联合主键,这样也可以保证表中每行数据都不重复.

##### **2.组合索引** #####

###### **1)什么是组合索引** ######

**(**也叫复合索引、`联合索引`**)**,组合索引是不含主键索引的,是多个普通索引组合在一起的.一个表中可以有多个组合索引.

> ALTER TABLE mytable ADD INDEX name\_city\_age (name,age,city);

把一个表的name,age,city三个非主键列设为一个组合索引,数据库实际上自动为我们设立了3个索引,分别是(name) (name,age) (name,age,city)

###### **2)为什么要使用组合索引** ######

*  减少开销。  
    建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。比分别建立索引(col1),(col2),(col3)少了两个索引. 每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
 *  覆盖索引。  
    对联合索引(col1,col2,col3)，如果有如下的sql: `select col1,col2,col3 from test where col1=1 and col2=2`。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

##### **3.最左原则** #####

联合主键和组合索引都要遵守最左原则.

怎么查询才能保证全部走索引呢,那么就是要保证最左原则.

1.where条件后面必须含有第一个列name,

2.顺序不能跳,就是不能是name和city,而没有age.

3.where条件后面的name和age在写在前后都无所谓.

走全部索引的例子:

> SELECT \* FROM mytable WHREE name="admin"
> 
>   
> SELECT \* FROM mytable WHREE name="admin" AND age= 20
> 
> SELECT \* FROM mytable WHREE age= 20 AND name="admin"
> 
> SELECT \* FROM mytable WHREE name="admin" AND age= 20 AND city ="北京"
> 
> SELECT \* FROM mytable WHREE city ="北京" AND name="admin" AND age= 20

走部分索引的例子:

> SELECT \* FROM mytable WHREE name="admin" AND city ="北京"

完全没走索引的例子:

> SELECT \* FROM mytable WHREE age= 20  
> SELECT \* FROM mytable WHREE age= 20 AND city ="北京"

#### 3.Full Text **全文索引** ####

在大量数据中，通过其中的某个关键字，就能找到该字段所属的记录行。全文索引在开发中很少用，因为其占用很大的物理空间和降低了记录修改性。

#### 4.SPATIAL 空间索引 ####

空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

### 二.两者索引方法,btree索引和hash索引的区别 ###

1、BTREE（B树（可以是多叉树）） \{主流使用\}  
2、HASH（key,value） 这种方式对范围查询支持得不是很好

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。  
可 能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足”=”,”IN”和”<=>”查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。  
————————————————  
原文链接：https://blog.csdn.net/guo\_qiangqiang/article/details/88794971

### **三.** 索引在B+树的工作原理 ###

#### **1.我先建了一个表** ####

>     CREATE TABLE `user_innodb` (
>       `id` int NOT NULL AUTO_INCREMENT,
>       `name` varchar(255) DEFAULT NULL,
>       `gender` tinyint(1) DEFAULT NULL,
>       `phone` varchar(11) DEFAULT NULL,
>       PRIMARY KEY (`id`)
>     ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我创建了一个存储引擎为InnoDB的表`user_innodb`，其中包含主键id、姓名字段（`name`）、性别字段（`gender`，用0，1表示不同性别）、手机号字段（`phone`），并批量初始化了500W+条数据。

#### **2.主键索引(**聚簇索引**)** ####

**注意:一张表只能有一个主键,也只能有一个聚簇索引, 也就只能有一个主键树**

**不管这个主键是只有一个字段还是几个字段组成的联合主键, 这张表的主键树只有一个,**

**如果主键是一个字段, 那么这个主键树就只记录这个一个字段,**

**如果主键是两个字段,那么这个主键树就是组合索引B+树的样式, 记录的是两个主键字段在一个B+树的形式.**

![e759e673b6d1017691a25a8504ef14de.jpeg][]

上图就是mysql的innodb引擎根据我们设的id自动为我们生成的一个主键B+树索引结构,我们给它取了个名字,叫主键索引.又叫做聚簇索引.

主键索引有两个特点：

1.  按照主键的大小对用户记录和数据页进行排序，记录用单向链表连接，叶子节点使用双向链表连接；
2.  B+树的叶子节点保存了用户的完整记录。

#### **3.普通索引(**二级索引,非聚簇索引**)** ####

主键索引是在搜索条件为主键的时候才会发挥作用，但是我要以`name='蝉沐风'`为搜索条件怎么办？

innodb的解决办法就是再创建一个B+树（我们称为name索引）,这就是普通索引.

![98f76afd0378a5ef853ec3ca2afb67be.jpeg][]

这棵普通索引的B+树和聚簇索引的B+树有点区别：

1.  叶子节点存放的不再是完整的用户记录，而是只记录`name`列和主键值；
2.  数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照`name`列排序；
3.  目录项记录除了存储索引列（`name`）和页号之外，同时还存储了主键值；（大家可以想一想，为什么要存储主键值,因为name列可能不是唯一的,如果两个name相同,那么谁排前面谁排后面呢,这时候就要按主键值来排序了.）

有了这棵B+树，你就可以通过`name`列快速找到主键值了，查找的方式和根据主键值查找用户记录的方式完全一样，只不过前者查到的是主键值，后者查找到的是一条完整的用户记录罢了。

> 你可能对字符串进行二分法感到有点奇怪，甚至没有接触过的相关知识的读者连对字符串进行排序都会觉得很诧异。其实在创建表的时候我们可以对字符串字段指定字符集和比较规则，如果你不指定，MySQL会默认给你设置，总之，MySQL总会找到一个方式对字符串进行排序。

现在得到主键的id了，然后根据主键id到主键索引中查找到完整的用户记录，这个过程叫做回表。如果没有为`name`列设置唯一性约束，那就可能找到多个符合条件的主键id，多回几次表就可以了。

对`name`这种单个列添加的索引叫做**普通索引**，也叫**二级索引**。

#### **4.组合索引 (其实就是多列的主键索引和多列的普通索引)** ####

**(**也叫复合索引、`联合索引`**)**

假设我们为`name`列和`phone`列建立联合索引,name和phone列可以是两个主键组成的联合主键

也可以是两个普通列组成的组合索引.

（注意我描述的顺序,name列在前,phone列在后,后面的图的顺序也就是按这个顺序排的），自然也是创建一棵B+树，这棵B+树和之前又稍微有点不同：

1.  叶子节点存放的是`name`列、`phone`列和主键值；
2.  目录项记录除了存储索引列（`name`、`phone`）和页号之外，同时还存储了主键值；（大家可以想一想，为什么要存储主键值）
3.  数据页中存放的用户记录和目录项记录由原本的按照主键排序变为按照`name`列排序，如果`name`列相同，那就按照`phone`列排序；（如果`phone`列再一样呢？你现在明白为什么要存储主键值了吗？）

再画个图吧（有点偷懒了哈，数据页号没换）：

![2a235a449c5d295a39405f6bd027e998.jpeg][]

还是和二级索引一样，利用B+树快速定位到数据页，然后页内快速定位到记录，找到记录中的主键id，再回表，如果找到多条符合条件的记录，就多回几次表。

### 四:补充 ###

#### 补充1:覆盖索引 ####

覆盖索引（covering index ，或称为索引覆盖）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能。

例子1:id是主键,name是普通索引

![format_png][]

selete id from stu where name = 小王, 这个查询就不用回表,查询快,是覆盖索引.

例子2:在例子1基础上,现在出现的业务需求中要求根据名称获取学生的年龄，并且该搜索场景非常频繁，那么先在我们删除掉之前以字段name建立的普通索引，以name和age两个字段建立联合索引，sql命令与建立后的索引树结构如下:

> ALTER TABLE student DROP INDEX I\_name;
> 
> ALTER TABLE student ADD INDEX I\_name\_age(name, age);

![format_png 1][]

那在我们再次执行如下sql后

> SELECT age FROM stu WHERE name = '小李'；

流程为：

> 1.  在name,age联合索引树上找到名称为小李的节点
> 2.  此时节点索引里包含信息age 直接返回 12

这没有回表,查询速度快,也是覆盖索引.

如何确定这个查询是覆盖索引

当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

![format_png 2][]

这里我们很清楚的看到Extra中Using index表明我们成功使用了覆盖索引

**来源一:**

[MySQL 覆盖索引详解 - 掘金 (juejin.cn)][MySQL _ - _ _juejin.cn]

[彻底搞懂MySQL的主键索引、普通索引和联合索引 - 知乎 (zhihu.com)][MySQL_ - _ _zhihu.com]

[e759e673b6d1017691a25a8504ef14de.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/83dba95550264576a62c704f1939094d.png
[98f76afd0378a5ef853ec3ca2afb67be.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/553a3d7c7c8c4f78ac1003f2fd4d18d5.png
[2a235a449c5d295a39405f6bd027e998.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6f425221007c4e5b9be37a323b41f9c3.png
[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b7b07488ae6d404caacd4100c73aa82c.png
[format_png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/88c8e6e08a4e4b9daa6aebbbaf5c27f8.png
[format_png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/d2cb3ac9710444268500ca03fe88179a.png
[MySQL _ - _ _juejin.cn]: https://juejin.cn/post/6844903967365791752
[MySQL_ - _ _zhihu.com]: https://zhuanlan.zhihu.com/p/479164948