对数据库索引的理解以及索引在MySQL中的数据结构

Dear 丶 2024-03-30 08:52 28阅读 0赞

**引言**

索引在本质上相当于书的目录，通过目录就可以快速的找到某个章节对应的位置。索引的效果，就是加快了查找的速度。日常进行数据库的操作，一般地都是进行增删查改，而在很多场景中，进行查找的概率要比增删改大很多。

虽然索引可以增加查找的概率，但是也增加了增删改的开销，因为一旦进行增删改操作，那么就需要调整已经创建好的索引了。同时，索引也会增加空间的开销，因为创建索引，也需要额外的硬盘空间来保存。

**使用场景**

1.数据量较大，且经常对这些列进行条件查询。

2.该数据库表的插入操作，及对这些列的修改操作频率较低。

3.索引会占用额外的磁盘空间。

满足以上条件时，就可以考虑对表中的这些字段创建索引，以提高查询效率。反之，不考虑创建索引。

**使用**

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

**1.查看索引**

show index from 表名;

示例：查看学生表中已有的索引

show index from student;

**2.创建索引**

对于非主键、非唯一约束、非外键的字段，可以创建普通索引。

create index 索引名 on 表名(字段名);

示例：创建班级表中，name字段的索引

create index idx\_classes\_name on classes(name);

**3.删除索引**

drop index 索引名 on 表名;

drop index idx\_classes\_name on classes;

关于创建索引，最好的就是在建表之初就创建好。对于一个已经存在非常多数据的表再来创建索引，是有很大风险的。因为数据库里面的数据量非常庞大，那么在创建索引的时候，一下子就会吃掉大量的磁盘IO，可能会持续一段时间，具体时间要根据数据量的大小来定。那么在磁盘IO被吃满的这段时间里，是无法进行任何操作的，可能会带来一定的经济损失。

关于删除索引，也是和创建索引一样，有磁盘IO被吃满的风险，所以删除索引的时候要慎重！

在索引被创建好之后，是不需要我们手动的去使用的，查询的时候会自动的用索引进行查询。SQL是通过数据库的执行引擎来进行执行的，这里面可能会涉及一些优化操作，执行引擎会自动评估哪一种方案是成本最低查询速度最快的。

**索引在MySQL中的数据结构**

抓住重点，数据库需要的是范围查找！

1.关于哈希表，虽然哈希表查找元素的时间度是O(1)，但是哈希表只能比较相等，无法进行大于小于这样的范围查找。

2.关于二叉搜索树，二叉搜索树查找元素的时间复杂度一般情况下为O(logN),但是在最坏情况下是单枝树，相当于链表了，所以此时为O(N)。虽然二叉搜索树可以查找范围，因为树里面的元素时是有序的，根据起点和终点，可以确定范围。但是，当树中的元素非常多的时候，那么树的高度就会比较高，然后树的高度就决定了在进行查询的时候，元素的比较次数，因为数据库在进行比较的时候都是要读硬盘的，因此二叉树搜索树并不合适。

3.针对二叉搜索树的缺点，就引入了N叉搜索树，这样就解决了树的高度问题。其中一种典型的实现就是B树，每个节点上有多个值，同时有多个分叉：

![9894e14b2f9d4ea18f3538752b0db019.png][]

关于B树，因为一个节点上存在多个值，每个节点都是在硬盘上，所以虽然比较次数没有减少，但是读写硬盘的次数明显减少了。虽然B树比较适合做数据库的索引了，但是并不是，又在此基础上做了一些改进，引入了B+树。

4.关于B+树，就是为了索引这个场景而量身定做的数据结构，它也属于N叉搜索树，但是具有一些新的特点。

![d43a7882b61944a6812d9db5c40b5218.png][]

**结构特点：**

1.B+树也是N叉搜索树，每个节点上可能包含N个key，N个key划分出N个区间，最后一个key就是最大值。

2.父元素的key会在子元素中重复出现，并且在子元素中是最大值。

3.会把叶子节点用类似于链表的方式进行首尾相连。

**优势：**

1.首先它也属于N叉搜索树，树的高度降了下来，在进行比较的时候，磁盘IO次数就比较少了

2.更适合进行范围查询

3.所有的查询都是要落在叶子节点上的，无论查询那个元素，中间进行比较的次数是差不多的，查询操作比较均衡。在这一点上，对于B树来说，可能有些值查询比较快，有些查询比较慢，但是对于B+树来说，查询的速度是稳定的。

4.由于所有的key都会在叶子节点中体现，只需要把所有的数据行给放到叶子节点中。

![87aaf22e8f054fc38ee3a0f131f89e9a.png][]

由于非叶子节点只存了简单的id，没有存一整行，这就意味着非叶子节点占用的空间是比较小的，也有可能非叶子节点可以放进内存中去缓存，更进一步的降低了磁盘IO次数，提高查询的速度，本质上就是在减少磁盘IO的次数。

最后，当前B+树结构，只是针对MySQL的InnoDB（最主流的一种存储引擎）这个数据引擎里面使用的数据结构。不同的数据库不同的存储引擎，里面的存储结构可能存在差异。

[9894e14b2f9d4ea18f3538752b0db019.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8f1d1f83947142b99c6b2741f8b37778.png
[d43a7882b61944a6812d9db5c40b5218.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/8ac07b232ee24c359a0828bdbf52a8c3.png
[87aaf22e8f054fc38ee3a0f131f89e9a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/6da3c106a224408f813723e849280f56.png