MySQL基础学习（七）————索引

最新推荐文章于 2024-04-22 11:29:54 发布

愿美梦成真

最新推荐文章于 2024-04-22 11:29:54 发布

阅读量391

点赞数 1

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

本文链接：https://blog.youkuaiyun.com/weixin_43510391/article/details/117378123

版权

MySQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了数据库索引的概念，解释了不同数据结构如二叉树、Hash表、B树和B+树在索引中的应用，并详细讨论了B+树的优势。同时，对比了MyISAM和InnoDB两种存储引擎的索引实现差异，包括事务支持、索引类型等。最后，讲解了创建和管理索引的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、什么是索引？

MySQL官方对索引的定义是：索引是可以帮助MySQL高效获取数据的数据结构。即索引是数据结构。数据库在执行查询的时候，如何没有索引存在的情况下，会采用全表扫描的方式进行查找。如果存在索引，则会先去索引列表中定位到特定的行或者直接定位到数据，从而可以极大地减少查询的行数，增加查询速度。
索引可以类比为一部字典开头的目录。

2、索引的数据结构

常见的数据结构：

链表
数组
Map
二叉树
红黑树
Hash表
B树

以上都是一些数据结构，那么究竟是哪种数据结构更加适合在数据库的索引中使用呢？

2.1 二叉树(红黑树)

在这里插入图片描述
我们可以看到，因为二叉树或者是红黑树只有两个叉，所以如果数据库的数据量比较大，那么就会导致树的高度较高，如果树的高度较高的话那么磁盘IO的次数就会增多，这个时候查询效率就会比较低（但是还是高于全表扫描），所以二叉树或者是红黑树不适合当索引。

2.2 Hash表

Hash表也叫散列表，根据相关的Key而直接访问的数据结构。做法很简单，把Key通过一个固定的运算转换成一个数字，然后将这个数字对数组的长度取余，最终的结果就当做数组的下标。对应的数据就放在该下标处。
在这里插入图片描述
如果采用Hash表作为索引，其查询效率也是很高的。但是Hash表会普遍用于MySQL的索引上来吗？
使用Hash作为索引会存在以下问题：

Hash索引仅能够查找=，in等情况的查找，无法进行范围查找
Hash索引无法进行排序（经过Hash运算后的数字大小和原本的数字大小没有关系）
Hash表这种数据结构可能会存在Hash冲突，即可能会存在不同的数据经过Hash运算后得到
相同的hash值，因此即便找到了对应的Hash值所在的下标，仍有可能需要再次扫描表的数据
如果存在大量Hash值相等的情况，那么此时Hash索引的查询性能不一定优秀

2.3 B树

B树也叫 B-树。是一种多路平衡查找树。B 树的定义如下（参考书籍—《数据结构》）

对于一颗m阶的B树而言：
1）树中的每个结点最多含有m个孩子；
2）除了根结点和叶子结点，其他结点至少有[ceil(m / 2)（代表是取上限的函数）]个孩子；
3）若根结点不是叶子结点时，则至少有两个孩子（除了没有孩子的根结点）
4）所有的叶子结点都出现在同一层中，叶子结点不包含任何关键字信息；
在这里插入图片描述
问题一：B树相对于平衡二叉树或者红黑树，最大的优势在什么地方？
对于B树而言，如果节点内存储的索引数量越多，那么即便B树的高度只有三层或者四层，也可以存储千万条以上的数据。

一般情况下，如果B树的高度是3，那么就需要进行三次查询，也就是需要经过三次磁盘IO，查询的限速步骤主要在于磁盘IO。

问题二：如果将千万条数据都放在同一个节点内，不是只需要一次磁盘IO就可以找到对应的数据了吗？
为什么不采用这样的方式呢？
在这里插入图片描述
当从磁盘中读取数据时，利用读写头找到对应的磁道，这个步骤称之为寻道。找到磁道后，盘片开始转动，磁道上的每个位都可以被磁头感知到，然后读取到其内容，对磁盘的访问整个过程可以分为寻道时间和访问时间。一般情况下，寻道时间较慢。

由于磁盘存取的速度比内存慢很多，所以磁盘读取时，通常情况下并不是按需读取，而是会预读一部分数据。预读的长度通常情况下为一个页的整数倍。一个页一般情况下大小为 4k。也就是说一次磁盘 IO通常只会读取 4k 的几倍。因此，把全部数据写入到一个节点中，也并没有太大用处，因为一般只会读取4K 或者 4K 的几倍。

数据库的实现者也利用磁盘预读的这一点，将一个节点的大小设为一个页的大小或者一个页大小的几倍。

2.4 B+树

数据库底层采用的其实是 B+树来作为索引。它可以看成是 B 树的变种。具有以下特点：

非叶子节点不存储data，只存储key
所有的叶子节点存储完整的一份key信息以及key 对应的 data
每一个父节点都出现在子节点中，是子节点的最大或者最小的元素
每个叶子节点都有一个指针，指向下一个数据，形成一个链表

特点：B+树由于非叶子节点不存储数据，仅在叶子节点才存储数据，所以，单个非叶子节点可以存储更多的索引字段。

3 索引的实现

介绍索引具体实现之前，先介绍一下数据库的组成结构。为什么？
因为索引的具体实现和不同的引擎有关。

3.1 数据库的组成结构

在这里插入图片描述
解析器：首先MySQL需要知道你想做什么。因此需要对输入的SQL进行解析。首先进行词法分析，需要识别出里面的字符串代表什么意思。比如 SELECT 代表查询，T 代表某张表，ID 代表某张表的列字段叫id；之后进行语法分析，根据语法规则，判断输入的 sql 语句是否符合MySQL语法。
优化器：经过解析之后，MySQL就知道你需要做什么事情了。但是在真正执行之前还需要经过优化器处理。比如当表中存在多个索引的时候，选择哪个索引来使用。或者多表关联的时候，选择各个表的连接先后顺序。
执行器：开始执行之前首先确认对该表有无执行查询的权限。如果没有，则返回错误的信息提示。如果有权限，则开始执行。首先根据该表的引擎类型，使用这个引擎提供的接口。比如查询某表，然后利用某字段查找，如果没有添加索引，则调用引擎的接口取出第一行数据，判断结果是不是，如果不是，依次再调用引擎的下一行数据，直至取出这个表中所有的数据。

如果该字段有索引，执行过程也大致相似，

所以具体的数据是保存在引擎中的。在MySQL中，常见的数据库引擎有MyISAM和InnoDB。

3.2 MyISAM索引实现

MyISAM的索引是非聚集索引。什么叫非聚集？MyISAM的索引文件和数据文件是分离的。
在这里插入图片描述

非主键索引：key是这一行数据对应的指定索引列的值，data是这一行数据存放的地址值。

3.3 InnoDB索引实现

InnoDB的文件只有一个表结构文件和数据文件。数据文件本身就是索引文件。InnoDB的索引是聚集索引形式。索引和文件数据是存放在一起的。

主键索引： Key就是主键，data是主键对应的这一行数据。
非主键索引： Key就是对应的索引列的值，data是这一行数据对应的主键。

使用InnoDB存储引擎存储的数据有2个文件：

3.4 MyISAM和InnoDB的差别

InnoDB 支持事务，MyISAM不支持事务，对于 InnoDB 中的每条SQL语句都自动封装成事务，自动提交，影响速度
InnoDB 支持外键，MyISAM不支持外键
InnoDB 是聚集索引，数据文件和索引绑在一起。MyISAM是非聚集索引，索引和数据文件是分开的
InnoDB 不保存表的行数，查询某张表的行数会全表扫描。MyISAM会保存整个表的行数，执行速度很快
InnoDB 支持表锁和行锁（默认），而 MyISAM支持表锁。
InnoDB 表必须要有一个主键（如果用户不设置，那么引擎会自行设定一列当做主键），MyISAM则可以没有
InnoDB 的存储文件是 frm 和 ibd，而 MyISAM是 frm、myd、myi 三个文件。
InnoDB会出现回表，MyISAM不会发生。

在这里插入图片描述

如何选择？
是否需要事务？如果不需要，则可以使用MyISAM
绝大多数操作是否是查询？如果是，可以选择MyISAM，有读也有写，则选择 InnoDB

4、索引语句

#一般在建表的时候创建，一般会设为int而且是auto_increment
#创建主键索引
CREATE TABLE `user` (
`id` int NOT NULL,
`name` varchar(255) NULL,
PRIMARY KEY (`id`),
INDEX `name_index`(`name`) USING BTREE
);
#在创建表以后添加索引
alter table tableName add [unique] index indexName(columnName);
create index indexName on tableName(columnName);
#删除索引
alter table tableName drop index indexName;
drop index indexName on tableName;
#查看索引
show index from tableName;