MySQL的索引详解

baixian110

已于 2023-08-09 22:16:46 修改

阅读量984

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 B树 B+树索引

于 2023-06-09 00:31:52 首次发布

本文链接：https://blog.csdn.net/baixian110/article/details/131106970

版权

MySQL 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

文章介绍了MySQL索引的概念，强调其在提升查询效率中的作用，特别是在大数据量和条件查询场景下。讨论了何时创建和不创建索引的策略，并展示了如何查看和创建索引。同时，文章深入讲解了B+树这种支持索引的数据结构，阐述了其优势和在数据库索引中的应用。

摘要由CSDN通过智能技术生成

1.什么是MySQL的索引

1.1索引的概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。
所以索引归根结底只是在做一件事，添加了索引的数据，查询起来会更快。我们可以把索引理解成书籍的目录。

1.2索引的使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

2.索引的使用

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

2.1查看索引

语法：

show index from 表名;

示例：

mysql> show index from student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| student |          0 | sn         |            1 | sn          | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | classes_id |            1 | classes_id  | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> desc student;-- 实现表结构 --
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | YES  |     | unkown  |                |
| qq_mail    | varchar(20) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

2.2创建索引

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

语法：

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

注：索引名的命名规则一般是：index_表名_列名

示例：

mysql> create index index_student_name on student(name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

在实际开发中，如果项目的数据量非常大，创建索引的成本是非常高的，所以在实际开发时，在建表的时候索引就要规划好，如果表里有很多数据了，建议不要再额外添加索引了。

2.3 删除索引

语法：

drop index 索引名 on 表名

示例：

mysql> drop index index_student_name on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

3.索引背后的数据结构

对于索引的操作是非常简单的，但是关键在于我们要去学习支持索引的数据结构。
在数据结构那门课程里，我们可以使用二叉搜索树来加快查询，但是元素个数一多，对于输的高度就就会很高，而树高就代表着比较次数多，在实际项目中代表中I/O的访问次数多，因为数据库中的数据是存在硬盘里的。或者我们还学过使用哈希表，他查询的时间复杂度为O(1)但是哈希表不支持范围查找，不支持模糊匹配。
实际上索引的背后使用的是B+树。
在了解B+树之前，先要了解B树，如果有考过408同学应该是对这个数据结构是非常熟悉的

3.1 B+树

B树本质上就是一个N叉的搜索树（查找树）。

在这里插入图片描述
B+树是在B树的基础上进行改进

在这里插入图片描述
B+的特点

一个结点，可以存储N个key,N个Key划分出N个区间（B树是N+1个）；
每个结点的Key值，都会在子节点中存在（并且是子节点的最大值）；
B+树的叶子结点首位相连，类似链表；
整个树的所有数据都包含在叶子结点中。所以非叶子结点的Key最终都会出现在叶子结点中。
B+树还有一个显著特点，他的每一个叶子结点都关联这一个记录，这个记录就是我们实际数据库里每一个表里的每一行记录。

B+树的优势

当前一个结点保存更多的key,最终树的高度是相对更矮的（B树也有这个优点），查询的时候可以减少IO的访问次数。
所有的查询最终都会落在叶子结点上（查询任何一个数据，经过的IO访问次数，是一样的。）稳定是很重要的，稳定可以让程序员对程序的运行效率有更准确的评估。
B+树的所有叶子结点都用链表进行了链接(并且是一个双向链表)，这样就支持更直接的范围查询了。同时代码也更好写了。
由于数据都在叶子结点上，非叶子结点只存了key，所以我们就可以将叶子结点的一部分进行缓存（B树非叶子结点是存记录的），这样可以进一步减少IO次数。

注意：以学生表为例

mysql> desc student;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| sn         | int(11)     | YES  | UNI | NULL    |                |
| name       | varchar(20) | YES  |     | unkown  |                |
| qq_mail    | varchar(20) | YES  |     | NULL    |                |
| classes_id | int(11)     | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

表的数据还是按照id为主键，构建出B+树，通过叶子结点组织所有的数据行。其次针对name如果我又创建了一个索引，那么此时在底层是又构建了一个B+树，这个B+树的叶子结点不再存储这一行的完整数据，而是存主键id。
此时，如果根据name来查询，查到叶子结点得到的只是主键Id,还需要再次通过主键id主键的B+树里再查一次。
上述过程称之为回表，是mysql自己完成的，用户感知不到。