MySQL索引总结

最新推荐文章于 2024-08-04 00:11:06 发布

西瓜游侠

最新推荐文章于 2024-08-04 00:11:06 发布

阅读量170

点赞数

分类专栏：数据库

原文链接：https://juejin.cn/post/6850037271233331208#heading-8

版权

数据库专栏收录该内容

20 篇文章 1 订阅

订阅专栏

本文深入探讨了MySQL中的索引原理，包括B+Tree索引的优势与劣势，以及如何创建、删除和查看索引。详细解释了B+Tree与B树的区别，强调了B+Tree在数据库索引中的应用。同时，提到了Hash索引适用于等值查询但不支持范围查询的特点。全文索引和空间索引也在文中进行了介绍。创建索引的策略包括频繁查询的字段、外键和排序字段等，而避免在数据重复且分布均匀、频繁更新的字段上创建索引。

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

转载：MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列） (juejin.cn)

1 MySQL索引简介

索引的本质是 数据结构，索引的目的在于 提高查询效率，可以类比字典、火车站的车次表、图书的目录等。

可以简单的理解为“排好序的快速查找数据结构”，数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图是一种可能的索引方式示例。

在这里插入图片描述

索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上。
平常说的索引，没有特别指明的话，就是B+树（多路搜索树，不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。此外还有哈希索引等。

索引的优势和劣势：

优势：

提高数据检索效率，降低数据库IO成本；
降低数据排序的成本，降低CPU的消耗；

劣势：

索引也是一张表，保存了主键和索引字段，并指向实体表的记录，所以也需要占用内存；
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

2 创建、删除、查看索引的命令

创建

新建索引：

  CREATE [UNIQUE] INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构时候添加索引：

  ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName);

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name\G

–可以通过添加 \G 来格式化输出信息。

使用ALERT命令

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

这条语句创建唯一索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list);

添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

该语句指定了索引为 FULLTEXT ，用于全文索引。

3 索引分类

（1）从数据结构角度分类：

B+Tree 索引（重要）
Hash 索引
Full-Text 全文索引
R-Tree 索引

（2）从物理存储角度分类：

聚集索引（clustered index）
非聚集索引（non-clustered index），也叫辅助索引（secondary index）

聚集索引和非聚集索引都是B+树结构。

（3）从逻辑角度分类：

主键索引：主键索引是一种特殊的唯一索引，不允许有空值。
唯一索引或者非唯一索引：值是不是唯一的。
单列索引（普通索引）：每个索引只包含单个列，一个表可以有多个单列索引。
多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合。
空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。 MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建。

4 索引数据结构

索引（index）是在存储引擎（storage engine）层面实现的，而不是server层面。不是所有的存储引擎都支持所有的索引类型。即使多个存储引擎支持某一索引类型，它们的实现和行为也可能有所差别。

4.1 B+Tree 索引（重点）

MyISAM 和 InnoDB 存储引擎，都使用 B+Tree的数据结构，它相对与 B-Tree结构，所有的数据都存放在叶子节点上，且把叶子节点通过指针连接到一起，形成了一条数据链表，以加快相邻数据的检索效率。

详细讲解见：MySQL 三万字精华总结 + 面试100 问，和面试官扯皮绰绰有余（收藏系列） (juejin.cn)

B-Tree（读作：B树）、B+Tree（读作：B+树）

B-Tree结构：

在这里插入图片描述

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。

B-Tree结构的缺陷：如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

B+Tree 是在 B-Tree 基础上的一种优化。

B+Tree相对于B-Tree有几点不同：

非叶子节点只存储键值信息；
所有叶子节点之间都有一个链指针；
数据记录都存放在叶子节点中。

在这里插入图片描述

MyISAM主键索引与辅助索引的结构

MyISAM引擎的索引文件和数据文件是分离的。MyISAM引擎索引结构的叶子节点的数据域（data），存放的并不是实际的数据记录，而是数据记录的地址。索引文件与数据文件分离，这样的索引称为"非聚簇索引"。MyISAM的主索引与辅助索引区别并不大，只是主键索引不能有重复的关键字。

在这里插入图片描述

InnoDB主键索引与辅助索引的结构

InnoDB引擎索引结构的叶子节点的数据域（data），存放的就是实际的数据记录（对于主键索引，此处会存放表中所有的数据记录；对于辅助索引此处会引用主键，检索的时候通过主键到主键索引中找到对应数据行），或者说，InnoDB的数据文件本身就是主键索引文件，这样的索引被称为"“聚簇索引”，一个表只能有一个聚簇索引。

主键索引

它的索引结构是在同一个树节点中同时存放索引和数据。

在这里插入图片描述

在Innodb中，索引分叶子节点和非叶子节点，非叶子节点就像新华字典的目录，单独存放在索引段中，叶子节点则是顺序排列的，在数据段中。

辅助索引

它的索引结构跟主键索引的结构有很大差别，在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。

这就意味着，对列进行条件搜索，需要两个步骤：

① 在辅助索引上检索目标的值，到达其叶子节点获取对应的主键；

② 使用主键在主索引上再进行对应的检索操作。

这也就是所谓的 “回表查询”。

在这里插入图片描述

几个问题

问题:1：为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。其中在MySQL底层对B+树进行进一步优化：在叶子节点中是双向链表，且在链表的头结点和尾节点也是循环指向的。