一文详解MySQL——索引篇

清疚 2024-03-23 12:18 14阅读 0赞

### 1. MySQL的索引是什么？ ###

MySQL的索引是一种帮助MySQL **高效地查询和检索数据** 的数据结构，可以看作是 **数据的目录**。

索引的 **好处** 在于：

*  可以大大加快数据的检索速度（减少检索的数据量），降低数据库的IO成本。
 *  加快分组和排序的速度。
 *  保证行的唯一性，增强数据完整性。

当然并不是创建的索引越多越好，索引也存在 **缺点** ：

*  创建和维护索引需要耗费一定时间，对数据进行插入、删除、修改时增加了时间开销，降低SQL执行效率。
 *  索引需要使用物理空间存储即需要占用一定的磁盘空间。

所以MySQL索引是以 **空间换取时间** 的设计思想。

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

### 2. 索引的分类 ###

MySQL的索引按照不同的类型和特点可以从三个角度进行分类：

*  按 **数据结构** 分类可分为：B+树索引、Hash索引、全文索引等
 *  按 **物理存储** 分类可分为：聚簇索引（主键索引）、非聚簇索引（二级索引）等
 *  按 **逻辑特性** 分类可分为：主键索引、普通索引、唯一索引、前缀索引、联合索引（多个字段）等

#### 2.1 为什么使用B+树作为索引？ ####

MySQL索引从数据结构的角度可以分为B+树索引、Hash索引、全文索引等。

在这里只详细说一下B+树索引，因为InnoDB、MyISAM等常见的MySQL存储引擎都支持B+树索引，并且MySQL5.5后将InnoDB设为默认存储引擎，B+树索引也成为了MySQL存储引擎使用最多的所有类型。

> **在疑问为什么使用B+树这种数据结构之前，先想想什么样的数据结构才最适合MySQL？**

MySQL的数据是 **持久化** 的，也就是说数据是存储在磁盘中的（即索引和记录是保存在磁盘中的）。

那么在需要使用索引进行高效检索数据时，就必须访问磁盘取得索引读入内存，再根据索引再次访问磁盘检索数据后读入内存，所以每次检索数据都需要进行多次的 I/O操作，I/O操作耗费大量时间。

因此应该选择一种数据结构 **使得 I/O操作 次数尽可能的少** 。

此外还需要注意的是MySQL是支持范围查询的，也就意味着挑选的数据结构必须能够 **支持高效地范围查询**。

> **了解了MySQL对数据结构的需求后，来看看为什么B+树符合？**

MySQL中的B+树结构图：

![format_png][]

阐述一下B+树的优点：

*  B+树是一种基于磁盘的 **平衡多路查找树**，它的高度通常很低（3~4层），这意味着访问效率很高，从千万或上亿数据里查询一条数据，**只用 3到4 次 I/O操作**。
 *  B+树的 **非叶子节点不存储实际的数据，只存储索引**，相比于非叶子节点又存储索引又存储数据的B树而言，B+树的非叶子节点可以存放更多的索引，所以查询底层节点的磁盘I/O次数会更少。
 *  B+树的**所有叶子节点都存储了数据，并且叶子节点之间用双向链表连接**，所以数据是有序的，范围查询通过链表可以快速实现，这样可以方便地进行范围查询和排序操作。
 *  B+树每个节点都有大量的数据或索引（有大量冗余的节点），这些冗余的数据或索引可以保证B+树在插入和删除时效率更高（因为不会因为某个数据导致需要进行复杂的树变化，即B+树的树层结构很稳定不会经常变化）。

相比于其他数据结构：

*  相对于 **数组** 来说，虽然使用数组＋二分查找的方式实现线性排序更为简单，但是相比于B+树，B+树具备更高效的插入或删除元素的能力。
 *  相对于 **二叉查找树或平衡二叉树** 来说，虽然平衡二叉树具备平衡树高和高效地维护元素地能力，但是相比于B+树，由于二叉树始终只能二分，随着元素地增加，树高会越来越大，B+树具备更矮的树高，即对I/O操作的次数更少，所以具备更高效地查找效率。
 *  相对于 **B树** 来说，因为B树需要使用中序遍历的方式进行范围查询，增加了I/O次数。而由于B+树的叶子节点使用双向链表所以可以更高效地进行范围查询，具备给更高的范围查询效率。

所以B+树更适合作为MySQL索引的数据结构。

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

#### 2.2 聚簇索引和非聚簇索引的区别？ ####

聚簇索引和非聚簇索引的区别：

*  聚簇索引是指将 **数据存储与索引放到了一块**，叶子节点存储索引和索引对应的数据；聚簇索引的顺序就是数据的物理存储顺序，聚簇索引可以把相关数据保存在一起，提高查询效率；但也会增加插入和更新的开销，以及占用更多的空间。
 *  非聚簇索引是指将 **数据与索引分开存储**，索引结构的叶子节点指向了数据对应的位置；非聚簇索引的顺序与数据物理排列顺序无关

MySQL的InnoDB存储引擎中**每张表只能有一个聚簇索引，通常是主键索引**；而**非聚簇索引都是二级索引，可以有多个**。

> **主键索引**

主键索引是建立在主键字段上的索引，主键索引的B+树结构中的叶子节点存放的是实际数据（所有完整的记录）。

关于InnoDB引擎确定 **聚簇索引（主键索引）** 的方式按照如下次序：

1.  如果表中有主键，默认使用 **主键** 作为聚簇索引（主键索引）的索引键
2.  如果表中没有主键，则选择 **第一个不包含NULL值的唯一列** 作为聚簇索引（主键索引）的索引键
3.  如果2的情况也不存在，则存储引擎会 **自动生成一个隐式自增id** 列作为聚簇索引（主键索引）的索引键

> **二级索引**

二级索引又称为辅助索引，二级索引的B+树结构中的叶子节点 **存放的数据是主键**，索引分类中的唯一索引、普通索引、前缀索引等都属于二级索引。

*  唯一索引（UNIQUE）：唯一索引的属性列不能出现重复的数据，但是允许数据为NULL，一张表可以允许创建多个唯一索引。
 *  普通索引（INDEX）：建立在普通字段上的索引，一张表可以允许多个普通索引，并允许数据重复和数据为NULL。
 *  前缀索引：前缀索引只能建立在字符串类型（`char`、`varchar`、`binary`、`varbinary`）等类型上。创建方式

[format_png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/1950713ba0d844b1bb4b21d024e6a3f3.png