MySQL索引

陈炆

已于 2023-10-03 19:03:17 修改

阅读量62

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库

于 2023-09-26 21:34:18 首次发布

本文链接：https://blog.csdn.net/Chenwen666/article/details/133324219

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

3.2、二叉查找树（Binary Search Tree）

3.3、平衡二叉树（AVL）

3.4、B树

3.5、B+树

四、聚簇索引和普通索引（InnoDB存储引擎下）

一、什么是索引

索引（Index）是帮助MySQL高效获取数据的数据结构。列如一本书的目录，可以根据目录快速查找到对应的内容。其本质其实是一种数据结构。InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引。B+树索引其实是最为关键的。

二、索引的优缺点

优点

第一个就是能够快速定位到需要查找的数据，加快我们的查询效率。
因为索引是有序的，如果我们需要order by，那么可以降低我们排序的一个成本。

缺点

首先就是众所周知，InnoDB存储引擎中我们一个索引对应一个B+树。那么就会需要我们更多的存储空间，所以一般一个表中索引数量最好不要超过5个。
会降低更新的效率。比如增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

三、MySQL为什么使用B+树索引

3.1、哈希索引

哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。

哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。如图：

那为什么HashMap不适合做数据库索引？

1、hash表只能匹配是否相等，不能实现范围查找；

2、当需要按照索引进行order by时，hash值没办法支持排序；

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了a和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引；

4、当数据量很大时，hash冲突的概率也会非常大。

3.2、二叉查找树（Binary Search Tree）

二叉查找树的定义：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

二叉查找树可以减少我们IO遍历次数。但是使用二叉查找树会带来一定的问题。如果数据都是有序的话，则会退化成链表的形式。如图：

这样的话，同样会导致我们的查询某个值需要的时间复杂度为O(n)。为了解决这个问题，我们引入平衡二叉树。

3.3、平衡二叉树（AVL）

平衡二叉树的定义：平衡二叉查找树除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。插入与删除的时候通过左旋与右旋来保证左右子树高度差最多为1。使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(log2n)。如下图：

时间复杂度与树高相关。树有多高我们就需要检索多少次。每个节点的读取都对应着一次磁盘的IO操作。非常影响性能。
平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

那么有没有一种数据结构是一个节点可以放多个数据的呢？有的，那就是我们的B树。接下来引入B树这种数据结构。

3.4、B树

MySQL的数据都是存储在磁盘中的。一般查询数据都是需要从磁盘中读取到内存，每次IO都是很影响性能的。每访问一个节点就要进行一次IO读取，那么我们就需要减少IO次数，简单的说就是减少树的高度。

假如每个节点上key值需要8个字节，每个节点上有两个指针，每一个需要4个字节。一个节点占用的空间16个字节（8+4*2=16）。但是MySQL每次IO从磁盘读取到内存都是按页大小去读取的（一般是大小16K），然而我们真正需要的有效数据只占用16k字节，空间的利用率很低。

那么我们有个想法，就是一个节点上能不能存储更多的数据，每个节点可以存储1000个索引(16k/16=1000），这样就将二叉树改造成了多叉树而且树的高度也变矮了。从而最大可能的利用到IO的每次读取。

定义：

B树的节点中存储着多个元素，每个内节点有多个分叉。
节点中的元素包含键值和数据，节点中的键值从大到小排列。
父节点当中的元素不会出现在子节点中。
所有的叶子结点都位于同一层，叶子节点具有相同的深度，叶子节点之间没有指针连接。

B树结构如图：

假如我们现在需要查询值等于10的执行流程

第一次IO，读取磁盘块1，加载到内存中。10<15，在左边，通过指针P1找到磁盘块2。
第二次IO，读取磁盘块2，加载到内存中。10>7，在右边，通过指针P2找到磁盘块6.
第三次IO，读取磁盘块6，加载到内存中。10=10，结束读取。
相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘IO次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计

B树结构解决了IO读取数据利用率问题，同时我们来思考一下B树还有改造的空间吗？肯定还是有的。

第一点就是，如果每个节点都存储行数据的话，如果数据很大，那么一个节点能够存储的数据就会很少。所以我们非叶子节点最好是指存储索引值，行数据放在叶子节点。这么做可以减少我们的树的高度，从而减少IO次数。
B树不支持范围查找，如果要进行范围查找，又需要从根节点重新进行遍历了。

为了解决上述两个不足，我们再次对B树进行改造，从而引入B+树的数据结构。

3.5、B+树

B+树与B树的改进点是：

将data行数据储存在叶子节点，非叶子节点值存着索引值和指针数据。
叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

这两个点的改进，为我们解决了。

我们非叶子节点可以放更多是索引值，从而我们的树可以变得矮胖这种，读取一个数据，IO次数变得更少。
叶子节点使用双向指针连接，是我们进行范围查询不需要再从根节点进行遍历查询，加快效率。

四、聚簇索引和普通索引（InnoDB存储引擎下）

我们现在有如下创建表T语句，我们在k值上创建了普通索引。

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)。

4.1、聚簇索引

定义：简单说，聚簇索引的叶子节点存放的是主键值和Row数据。InnoDB下每个表都有一个聚簇索引，如果没有指定主键索引。那么InnoDB会自动创建一个ROWID字段来构建聚簇索引。基于表T的聚簇索引如下：

底部R1、R2为行数据，这么说就是叶子节点存储的是主键值和行数据。

4.2、普通索引

定义：简单说，普通索引的叶子节点存放的是索引值和主键值。如图所示：

如图叶子节点100,200等值为主键值。所以普通索引叶子节点存储的索引值和主键值。

五、回表

创建表T，并初始化插入对呀数据，执行SQL如下：

create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;

insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

现在我们需要查询k值在3和5之间的数据，SQL如下：

select * from T where k between 3 and 5

我们看看这个语句的执行流程是怎么样的？

通过K索引树，找到主键ID=300；
通过主键ID=300，获取到R3；
在 k 索引树取下一个值 k=5，取得 ID=500；
通过主键ID=500，获取到R5；
在 k 索引树取下一个值 k=7，不满足条件，流程结束。

在这个过程中，回到主键索引树搜索的过程，我们称为回表。比如步骤2和步骤4就是回表的操作。因为K索引树叶子节点只存储了k数据和主键值，我们需要的s值存放在主键索引的叶子节点。那么回表如何优化呢？下面我们来看看什么事索引覆盖。

六、索引覆盖

定义：简单的说，就是我们需要的数据能够通过一次索引遍历就能获取到所需的数据，不需要进行回表操作。那么这个就是所谓的索引覆盖。

比如我们现在要执行如下SQL语句：

select ID from T where k between 3 and 5

同样我们可以走K索引树，K索引树的叶子节点是k值和ID主键值，已经可以获取到ID值。不需要再次从主键索引树获取，这样我们就称作索引覆盖。

七、最左匹配原则

我们现在将name和age创建为联合索引（name,age）如下：

满足最左匹配原则SQL：

select * from like name '张%'

select * from like name '张%' and age = 20

不满足最左匹配原则的SQL：

-- %在左边模糊匹配
select * from like name '%张'
-- where后没有使用name
select * from like age = 20

八、索引下推

定义：MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

我们还是以name和age创建为联合索引（name,age）为前提下，现在需要找到name是张开头，年龄是10的数据。

select * from tuser where name like '张%' and age=10 and ismale=1;

1.首先看不走索引下推的流程：

通过联合 (name,age) 索引，我们能快速定位到4条数据，然后更加ID值去主键索引树查询数据。因此需要回表4次。

2.走索引下推的流程：

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。所以只需要遍历ID4和ID5的数据，因此需要回表2次。所以索引下推可以减少回表的次数，加快查询数据效率。

引用：

https://blog.csdn.net/qq_35190492/article/details/109257302
https://time.geekbang.org/column/article/69636

陈炆

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引

索引（Index）是帮助MySQL高效获取数据的数据结构。列如一本书的目录，可以根据目录快速查找到对应的内容。其本质其实是一种数据结构。InnoDB存储引擎支持以下几种常见的索引：B+树索引、全文索引、哈希索引。B+树索引其实是最为关键的。
复制链接

扫一扫