【MySQL】MySQL索引

绵阳的脑内SSD

已于 2025-05-08 22:27:52 修改

阅读量3.3k

点赞数 114

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

于 2024-11-30 21:18:16 首次发布

本文链接：https://blog.youkuaiyun.com/strive_mianyang/article/details/143994416

版权

MySQL课程专栏收录该内容

19 篇文章

订阅专栏

前言:本节内容主要讲解索引。注意：索引是很重要的知识点。务必学习！！本节将会主要讲述索引的B+树，以及为什么选择B+和操作索引。下面友友们开始学习吧！

ps：本节内容友友们只要想学习mysql都可以观看哦！

索引

磁盘基本读取单位 ---块

MySQL与磁盘交互基本单位 ---page

索引

索引是为了提高数据库的读取速度的。 mysql的CURD操作都是在内存中进行。可以理解为mysql在启动的时候会在内存中找一大块内存空间，即申请内存页，数据库操作执行在内存页里。执行完操作后，如果修改过，这块内存此时标记为‘脏页’，脏页会被在合适的时候将数据刷新到外设。

MySQL在内存中CURD，CURD的是什么？是内存页，内存页保存的是什么？是索引。——所以，索引也要加载到内存中，如果数据页在内存中，那么直接CURD。如果不在，先通过索引页找到数据页，然后把索引的数据页加载到内存，再进行CURD。

我们还要知道，提高算法效率的因素无非就是两个：

1、组织数据的方式。
2、算法本身。

所以，这个索引一定是某种数据结构。

其实对于我们数据库里每一个表，在系统下都对应的就是一个文件。文件都需要在磁盘上保存，未来的数据在盘片上的一个一个小格子，也就是扇区当中。

磁盘基本读取单位 ---块

认识数据库，我们就必须要认识一下磁盘。 数据库的数据其实本质上就是保存在磁盘上。和磁盘文件如出一辙，同样是保存在磁盘上的一个一个小格子上面，即扇区。并且，对于这一个一个的小格子，其实就是512字节，如今大都是4kb。具体的磁盘是什么样子的，博主已经做过讲解，下面是文章连接：linux基础IO——文件系统——学习硬件：磁盘_linux 逻辑盘文件系统-优快云博客

这里需要强调的是，我们之前学习文件系统，它的读取基本单位，不是扇区，而是数据块。所以，系统读取磁盘，是以块为单位的，基本单位是4kb。

MySQL与磁盘交互基本单位 ---page

MySQL之下并不直接是硬件，而是操作系统。

在mysql中，InnoDB中默认与磁盘是以16kb进行交互。但是mysql要经过OS， OS再与磁盘进行交互。而OS与磁盘交互数据的大小为4kb。

所以，在未来我们mysql要通过OS与磁盘交互， mysql一次就是操作16kb，转化到OS和磁盘就是进行四次4kb的交互。

所以，在mysql看来，他不管，他就是write，操作系统就是必须在内部的文件缓冲区找到4个块，然后四次交互将数据刷新到磁盘中。同理，未来mysql从磁盘中读数据，就是要让OS读取够四个块的数据，然后mysql读取数据read一次性读取16kb。而对应的OS与磁盘之间就使用fsync系统调用进行交互。

mysql的这个16kb是mysql进行数据交互的基本单位，这个基本单位就叫做page。

Buffer Pool

首先， mysql中的数据文件，是以page为单位保存在磁盘中的。

mysql的CURD操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的的数据。

而只要涉及到计算，就必须有cpu参与，而cpu参与，就一定要让数据先移动到内存当中，所以我们对应的磁盘当中的表当中的操作，需要CURD操作的，就必须要先将表搬到内存中，在内存里面用mysql自己的代码进行增删查改。而这个搬运过程里面搬运一次是4kb，即一次IO。

为了提高搬运的效率。mysql服务器在内存中运行的时候，在服务器内部，就申请了被称为Buffer Pool的大内存空间，来进行page的缓存。以后，逻辑IO就是MySQL对Buffer Pool的IO操作，是内存的IO操作。物理IO就是OS从磁盘读取数据到Buffer Pool的操作。这就以后MySQL就能在Buffer Pool中直接读取数据，而不需要等待OS四次磁盘IO后再读取数据。

Buffer Pool的压力大小，和MySQL的有效IO次数是直接挂钩的。如果MySQL查询一个数据所需要的逻辑IO次数越少，那么Buffer Pool的压力越小， OS和磁盘交互的就越少，Buffer Pool的空间也就可以开小一些。如果MySQL查询一个数据所需要的IO次数越多，那么Buffer Pool的压力就越大，OS和磁盘交互的就越多， Buffer Pool的空间也就要开大一些。 ——所以，就要选择一个合适的索引结构，降低MySQL的逻辑IO次数。

索引的理解

MySQL的底层索引方式是一颗b+树。

在最早期的数据库，其实保存数据的形式是追加日志信息。就是当一个新数据到来，那么就在日志文件的尾部添加一条这个数据的日志信息。然后以后查某个数据的时候就是从日志的尾部向前遍历，第一次遍历到的数据就是要查询的数据。这个过程中，写入数据的速度非常快， O(1)，但是查询的时间复杂度可以说是O(N)。所以后来人们设计出了各种结构，并在20世纪70年代b+树被广泛使用。

如何理解mysql中的b+树索引呢？要联系之前说的page。

首先，在mysql中， b+树的节点也是以page为单位的，即一个page就是一个节点。然后对于每一个非叶子节点，都是儿子节点的目录，即父目录可以找到子目录，然后子目录找到子目录的子目录。如下：

struct page
{
    struct page *_prev;
    struct page *_next;
    struct page *_son_page[NUM];
    Data buffer[NUM - 1];
};

然后当我们查询一个数据，就是从根目录开始，从根目录的第一个比较数据开始对比，如果小，就去对应下标处的子目录去寻找。就这样一层一层向下比较找，一直找到叶子节点。这样的非叶子节点的page，我们可以叫做索引页。

对于b+树的叶子节点，他们没有下一级，也就没有下一级page的指针。但是每一个叶子节点都有一个前驱和后继指针，将所有的叶子节点串联起来。所以，不要简单的将page认为是一个内存块。 page内部也必须写入对应的管理信息。这样的叶子结点我们可以叫做数据页。

struct page
{
    struct page* next;
    struct page* prev;
    struct page *_son_page[NUM];
    Data buffer[NUM - 1];
};

为什么会用b+树？因为我们说一开始是索引是向后追加写日志文件，查询效率是O(N)，会导致反复IO，为了优化查询效率，就要用到数据结构和算法。而MySQL中的B+树是一颗矮胖(page的设计推动了这个结果)的树，这样做就可以途径路上节点减少，直接减少逻辑IO次数（一次逻辑IO读取16kb数据，就能找到下一层子目录，所以逻辑IO次数 = B+树层数 - 1)；并且每一个节点都有对应的目录路径，就能大大提高查询的效率。同时又因为一些其他数据结构不如b+树适合，为什么马上会讲到，所以要用b+树。

ps: 就是我们说b+树的结构其实就是mysql InnoDB下的索引结构。但是如果没有主键怎么办？没有主键也是使用这种b+树的结构吗？ ——是的。mysql看到创建的表没有主键，那么他会默认生成一个隐藏主键列，同样会创建上面的主键索引。

为什么叶子节点全部用链表集连起来?

首先，这是B+树的特点。是因为mysql选择了B+结构。所以，不是叶子节点为什么用链表连接起来，而是mysql选择了B+树。而为什么选择B+，不选用B树？是因为我们习惯进行范围查找。

首先链表的效率是不行的，查询效率O(N)。然后就是搜索二叉树，搜索二叉树是有序的，二叉搜索树很明显不如AVL树，红黑。而红黑和AVL虽然便利速度很快，并且稳定。但是其实AVL和红黑树其实是一种瘦高的树，他的层越高，就说明我们路上的节点越多，就说明我们的IO交互就越多。哈希对于范围查找是搞不定的，哈希存储内部存储的二方式是乱序的。

为什么不选择b树的原因就是上面说的范围查找不如B+树。它一旦范围查找，就要重新查找，每横向扩展一次，就是一个层数次逻辑IO，而B+树只需要一次逻辑IO，所以B+更合适。

为什么要用16kb的page？

我们上面已经说了， mysql的索引数据结构为B+树，这棵树的节点为一个page。并且MySQL每次IO，不管是从Buffer Pool中读取，还是从磁盘IO都是一个page。为什么要用要用这个page？

首先，我们要知道， page是16kb并不是固定的，这个16kb只是一个经过大佬们进行平衡设计的结果。这个page的大小也可以是32kb，也可以是8kb。但是不能是10kb，因为我们操作系统从磁盘中一次IO为4kb，所以这个约束了mysql一次读取最好设置成4kb的倍数，也就间接导致了page的大小是一个4kb的倍数。 ---- 这是第一个原因， page应该设置成4kb的倍数。

当然，如果我们不考虑上面的块对齐的约束，将page的大小变小，比如变为1kb，变为几百字节甚至几十b好像也可以？因为我们有Buffer Pool来蓄水， kernel 从磁盘物理IO大小一次为4kb不变，那么磁盘IO的次数永远不变，及磁盘IO的开销不变。变的只是MySQL因为page变小，所以树高增加，比如以前B+树三层，现在变成四层甚至更高，导致从蓄水池逻辑IO的次数变多，然而这种内存的IO次数变多相对于磁盘IO来说微不足道。这么考虑，page好像不用必须设置成16kb这么大， page的设计就比较随意。

上面这种说法， 1、忽略了Buffer Pool本身对于逻辑页page的管理成本和B+树对逻辑页的管理成本。 2、树高的增加，即便很小也要减少不必要的开销。 3、核心的一点，如果想要读取4kb数据。 page为16kb时一次逻辑IO到内存。page为1kb时，四次逻辑IO到内存，但是可能进行了四次物理IO才成功取到完整的4kb数据。

聚簇索引和非聚簇索引

以上我们讲解的B+树的存储形式是我们的InnoDB的存储形式。 InnoDB是将数据和索引放到一起。

我们说MyISAM也是B+树，但是他的索引和数据没有放到一起。他的叶子节点存储的是数据的地址。我们将B+树和数据本身分离的方案称为非聚簇索引。我们把数据和B+树放到一起的称为聚簇索引。

现在来看一个例子：

我们创建一个数据库叫做index_db

然后创建出一个表，引擎为InnoDB：

然后我们能看到，只有一个文件ibd，索引和表是合起来的。

然后我们创建一个表，引擎是MyISAM：

然后我们能看到有三个文件。这两个文件里面myd就是数据， myi就是索引结构。两个是分开的。

重新理解索引问题

知道了上面的东西之后，我们就可以重新考虑一下问题了。就是mysql之中，mysql除了会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助索引。

但是对于MyISAM来说，建立辅助索引和主键索引没有区别，无非就是主键不能重复，而非主键可以重复。在MyISAM当中，是可以给给一张表建立多个索引的。

由上面的理解我们可以知道，索引的本质，就是数据结构！！！

对于InnoDB来说，我们除了主键索引里面包含所有的数据信息。辅助索引里面只有叶子节点保存的不再是一个数据的完整数据。而是保存该数据的主键和某一个列的字段。就比如保存的是主键和name列，或者主键和学科列等等。未来查询某一个name信息，然后筛选条件是age = 10。那么就要在age的非主键索引中查找age = 10的叶子节点，然后就能找到对应age和主键值。拿着主键值再回到主键索引中查找所有信息，这个就叫做回表查询。

未来我们创建一个表，这个表中没有创建主键，那么mysql就会默认创建一个隐藏主键，以及对应的主键索引。只不过我们的其他列没有对应的辅助索引，所以只能先行遍历整个数据库。如果我们要创建某一列的辅助索引，那么对于MyISAM就是将指针指向对应的数据。而对于innoDB就是要重新创建B+树，创建一个辅助索引。

创建主键索引

创建主键索引其实就是创建主键或者修改主键。问题是我们如何查看索引，下面来看例子：

create table test3(
id int primary key,
name varchar(20)
);

show index from test3;

上面，就是一个查看主键索引的例子。

然后我们可以删除主键索引：

alter table test3 primary key;

然后还可以添加索引：

添加唯一键索引

我们添加唯一键的时候，也会默认创建一个b+树，叫做唯一索引。除此之外就可以用alter add unique来添加唯一键索引：

alter table 表名 add unique(列名);

alter table test3 add unique(name);

然后我们就能看到两个索引。

删除主键索引，因为主键索引只有一个。所以删除主键索引就叫做:

alter table  表名 primary key;

删除其他索引就是：

alter table 表名 drop index 索引名

alter table test3 drop index 索引名;

创建普通索引

alter table test3 add index(name);

也可以给索引取名：

create index myindex on test3(name);

我们创建索引的时候也可以创建多列

创建复合索引

复合索引也就是多列索引。

先将上面的test1表新增一列：

alter table test1 add email varchar(20) not null after name;

然后创建多列索引：

alter table test1 add index(name, email);

然后我们能看到有三个索引。为什么不是2个呢？ ——虽然上面显示的是3个索引。但是其实第二个name和第三个email是共用一颗B+树。

创建索引的原则

比较频繁作为查询条件的字段应该创建索引。
唯一性太差不适合做索引。
更新太频繁不适合做索引。
永远不会出现在where子句中的。

全文索引

注意： mysql中myisam有全文索引。 innoodb没有全文索引。

全文索引和B+有些差别。之间都是以某一列或者某几列作为键值。建立索引。但是全文索引是对某一列里面的具体内容进行索引。就比如某一列是一个text文本，并且大小为varchar(200)。那么索引里面的内容就是全文索引。就是比如一段语句叫做“abcdefghijklmn", 想要索引其中的”def“, 就是使用全文索引。