mysql索引的本质



目录

1.索引的介绍

1.1.无索引情况下海量数据的查询

1.2.有索引情况下海量数据的查询

2.Buffer Pool

2.2.Buffer Pool

2.2.数据页

2.3.那么怎么识别数据在哪个缓存页中

2.4.buffer pool的初始化与配置

2.5.buffer pool的配置

2.6.Buffer Pool Size 设置和生效过程

3.索引的理解

3.1.主键索引现象

3.2.主键索引的构建

3.2.1.理解单个Page

3.2.2 页内目录

3.2.3 多页情况

3.2.4 给Page结构体创建页目录

 3.3.索引数据结构的选择

3.4.聚簇索引 与 非聚簇索引

4.索引操作

4.1.创建索引

4.1.1.主键索引

4.1.2.唯一索引

4.1.3.普通索引

4.2.查询索引

4.3.删除索引

4.4.建立索引的几大原则

---

说明： 本篇博客中没有做特殊说明的地方，都是以InnoDB存储引擎为例进行讲解的。 

在数据库中，索引是一种用于加速数据检索操作的数据结构。理解索引的工作原理和类型对于优化数据库性能至关重要。以下是关于索引的详细解释：

索引的作用

1. 提高查询速度：索引可以显著减少查询数据的时间复杂度，使得查询操作更加高效。例如，没有索引的情况下，查找某个记录的时间复杂度通常是 O(N)，其中 N 是表中的记录数。而有了索引后，查询时间复杂度可以降低到 O(log N) 或者更低，具体取决于索引的类型和数据结构。
2. 快速定位数据：索引通过构建一种数据结构（如 B-树、哈希表等），使得数据能够快速定位。例如，主键索引和唯一索引通过键值对的方式快速找到目标记录。

索引的代价

1. 增加存储开销：索引本身需要占用存储空间，特别是复合索引和全文索引，其占用的空间可能较大。
2. 降低增删改效率：每次进行数据的增删改操作时，除了对表本身进行操作外，还需要对索引进行维护，这会增加额外的开销。因此，在高频率的写操作场景中，需要谨慎使用索引。

常见的索引类型

1. 主键索引（Primary Key）：
   • 特点：唯一且非空。每个表只能有一个主键索引。
   • 用途：主要用于唯一标识表中的记录。
   • 数据结构：通常使用 B-树或哈希表实现。
2. 唯一索引（Unique）：
   • 特点：索引列的值必须唯一，但允许有空值。
   • 用途：确保某列或某几列的组合在表中是唯一的。
   • 数据结构：同样使用 B-树或哈希表实现。
3. 普通索引（Index）：
   • 特点：没有任何限制，允许有重复值和空值。
   • 用途：加速数据检索速度，但不保证数据的唯一性。
   • 数据结构：通常使用 B-树实现。
4. 全文索引（Fulltext）：
   • 特点：用于对文本数据进行全文搜索。
   • 用途：在文本字段（如文章、评论等）中进行全文检索。
   • 数据结构：使用倒排索引（Inverted Index）实现，支持复杂的文本查询。

---

先建立一个海量数据的员工表，看看在查询的时候没有索引时有什么问题？

我们可以在我们的系统创建一个test1.sql文件，然后把下面这些内容复制进去

创建完成之后，我们记录一下这个文件所在路径，我的是/root/MYSQL/test1.sql

接着我们打开mysql，然后使用source命令把这个文件搞进数据库

等个10-20分钟后，就会是下面这个样子！！

 上面的文件中构建一个条记录的数据，因为构建的海量表数据需要有差异性，所以使用存储过程来创建。

还有因为数据量很大，所以我们在创建该数据库时，会很慢，大概要分钟。

上述SQL中创建了一个名为index_demon的数据库，在该数据库中创建了一个名为EMP的员工表，并向表当中插入了八百万条记录。

我们可以进去看看

我们可以看看有多少个数据在里面

 

我们一执行就会像上面这样子，要等待！大约10秒左右就会出现下面这个

可以看到这个EMP表里有800万个数据

由于数据量很大，因此在查看EMP表中的数据时可以使用limit子句。如下：

通过desc命令可以看到，目前EMP员工表中没有建立任何索引。

 

查询EMP表中指定工号的员工信息，可以看到每次查询过程都需要花费10秒左右。如下：

 

 

 

 

我们发现，我们每次查询同样的东西，居然每次查询都是10秒。要这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

为了解决这个问题我们可以给这张表加上索引：

 

这里等待30s左右，才会出现下面这个.

给数据建立索引其实就是在底层去构建特定的数据结构，当然对海量数据构建特定数据结构也要花费一定的时间。

这时再查询EMP表中指定工号的员工信息，可以看到几乎检测不到查询时耗费的时间。

 

我们知道数据都是存储在磁盘上面的，所以我们得好好了解磁盘。不过，我这里就不讲了。如果想要了解更多的可以去：【Linux】基础I/O——理解ext2文件系统_linux ext2-CSDN博客

2.2.1.什么是Buffer Pool

　咱们在使用mysql的时候，比如很简单的select * from table;这条语句，具体查询数据其实是在存储引擎中实现的，大家都知道mysql数据其实是放在磁盘里面的，如果每次查询都直接从磁盘里面查询，这样势必会很影响性能，所以一定是先把数据从磁盘中取出，然后放在内存中，下次查询直接从内存中来取。但是一台机器中往往不是只有mysql一个进程在运行的，很多个进程都需要使用内存，所以mysql中会有一个专门的区域来处理这些数据，这个专门为mysql准备的区域，就叫buffer pool。

        buffer pool是mysql一个非常关键的核心组件。数据库中的数据实际上最终都是要存放在磁盘文件上的，如下图所示

        但是我们在对数据库执行增删改操作的时候，不可能直接更新磁盘上的数据的，因为如果你对磁盘进行随机读写操作，那速度是相当的慢，随便一个大磁盘文件的随机读写操作，可能都要几百毫秒。如果要是那么搞的话，可能你的数据库每秒也就只能处理几百个请求了！ 在对数据库执行增删改操作的时候，实际上主要都是针对内存里的Buffer Pool中的数据进行的，也就是实际上主要是对数据库的内存里的数据结构进行了增删改，如下图所示

 其实每个人都担心一个事，就是你在数据库的内存里执行了一堆增删改的操作，内存数据是更新了，但是这个时候如果数据库突然崩溃了，那么内存里更新好的数据不是都没了吗？ MySQL就怕这个问题，所以引入了一个redo log机制，你在对内存里的数据进行增删改的时候，他同时会把增删改对应的日志写入redo log中，如下图

万一你的数据库突然崩溃了，没关系，只要从redo log日志文件里读取出来你之前做过哪些增删改操作，瞬间就可以重新把这些增删改操作在你的内存里执行一遍，这就可以恢复出来你之前做过哪些增删改操作了。 当然对于数据更新的过程，他是有一套严密的步骤的，还涉及到undo log、binlog、提交事务、buffer pool脏数据刷回磁盘，等等。

　　Buffer Pool就是数据库的一个内存组件，里面缓存了磁盘上的真实数据，然后我们的系统对数据库执行的增删改操作，其实主要就是对这个内存数据结构中的缓存数据执行的。通过这种方式，保证每个更新请求，尽量就是只更新内存，然后往磁盘顺序写日志文件。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是比较高的，因为顺序写磁盘文件，他的性能要远高于随机读写磁盘文件。

总结：

Buffer Pool的工作原理

1. 数据加载：当MySQL需要访问某个数据页时，会首先检查该页是否已存在于Buffer Pool中。如果存在，则直接读取Buffer Pool中的数据；如果不存在，则从磁盘中读取该页，并将其缓存到Buffer Pool中。
2. 数据修改：当MySQL需要修改某个数据页时，它会在Buffer Pool中修改该页的副本，并将其标记为“脏页”。之后，后台线程会负责将脏页中的数据刷新到磁盘上。
3. 缓存替换：由于Buffer Pool的大小是有限的，因此当新的数据页需要被缓存时，可能会涉及到缓存替换的问题。MySQL通常会使用LRU（Least Recently Used）等算法来决定哪些数据页可以被替换掉。

Buffer Pool与磁盘交互

• 交互单位：MySQL与磁盘交互的基本单位是Page，而在MySQL中，这个Page的大小通常是16KB。然而，需要注意的是，MySQL的Buffer Pool与磁盘之间并不是直接交互的，它们之间还有一个内核缓冲区的存在。因此，在描述MySQL与磁盘交互时，我们更关注的是MySQL的Buffer Pool与内核缓冲区之间的交互，这个交互单位就是16KB。
• 交互过程：当MySQL从磁盘读取数据时，它首先会将数据从磁盘读取到内核缓冲区中，然后再将数据从内核缓冲区读取到Buffer Pool中。同样地，当MySQL将数据刷新到磁盘时，它也会先将数据从Buffer Pool刷新到内核缓冲区中，然后再将数据从内核缓冲区刷新到磁盘上。

因为操作系统和磁盘交互的基本单位是4KB，所以内核缓冲区与磁盘之间是以4KB为单位进行交互的。而MySQL的Buffer Pool和磁盘实际并不是直接交互的，因此通常情况下我们说：MySQL与磁盘交互的基本单位是16KB，指的是MySQL的Buffer Pool与内核缓冲区之间是以16KB为单位进行交互的。只不过在说的时候更关注的是MySQL和磁盘之间的关系，所以直接说的是MySQL与磁盘交互的基本单位是16KB，相当于忽略了中间的内核缓冲区。

我们先了解一下数据页这个概念。

它是 MySQL 抽象出来的数据单位，磁盘文件中就是存放了很多数据页，每个数据页里存放了很多行数据。

默认情况下，数据页的大小是 16kb。

所以对应的，在 Buffer Pool 中，也是以数据页为数据单位，存放着很多数据。但是我们通常叫做缓存页，因为 Buffer Pool 毕竟是一个缓冲池，并且里面的数据都是从磁盘文件中缓存到内存中。

所以，默认情况下缓存页的大小也是 16kb，因为它和磁盘文件中数据页是一一对应的。

所以，缓冲池和磁盘之间的数据交换的单位是数据页，Buffer Pool中存放的是一个一个的数据页。

假设我们要更新一行数据，此时数据库会找到这行数据所在的数据页，然后从磁盘文件里把这行数据所在的数据页直接给加载到Buffer Pool里去。如下图。

通过show命令查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是16KB。如下：

---

大家思考一个问题

MySQL与磁盘进行交互时为什么不是按需交互，而是以Page为基本单位进行交互的？

1. 当我们查询表中的某一条记录时，如果MySQL只从磁盘中将这一条记录加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL就一定需要再次与磁盘进行IO交互。
2. 而如果当我们查询表中的某一条记录时，MySQL直接将这条记录所在的整个Page都加载到内存当中，那么当我们继续查询表中的其他记录时，MySQL很可能就不再需要与磁盘进行IO交互了，因为这条记录很可能也在被加载进来的Page当中，这时直接在内存中进行查询即可，大大减少了IO的次数。
3. 当然，我们不能保证用户下一次要访问的数据一定就在本次加载进来的Page当中，但是根据统计学原理，当一个数据正在被访问时，那么下一次有很大可能会访问其周边的数据（局部性原理），因此我们有较大概率保证用户下一次要访问的数据和本次访问的数据在同一个Page当中，如果局部性原理没有起作用，那就再把对应的Page加载到内存当中即可。

也就是说，MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率。

        每个缓存页都会对应着一个描述数据块，里面包含数据页所属的表空间、数据页的编号，缓存页在 Buffer Pool 中的地址等等。

        描述数据块本身也是一块数据，它的大小大概是缓存页大小的5%左右，大概800个字节左右的大小。然后假设你设置的buffer pool大小是128MB，实际上Buffer Pool真正的最终大小会超出一些，可能有个130多MB的样子，因为他里面还要存放每个缓存页的描述数据。

        在Buffer Pool中，每个缓存页的描述数据放在最前面，然后各个缓存页放在后面。所以此时我们看下面的图，Buffer Pool实际看起来大概长这个样子 。

MySQL 启动时，是如何初始化 Buffer Pool 的呢？

• 1、MySQL 启动时，会根据参数 innodb_buffer_pool_size 的值来为 Buffer Pool 分配内存区域。
• 2、然后会按照缓存页的默认大小 16k 以及对应的描述数据块的 800个字节 左右大小，在 Buffer Pool 中划分中一个个的缓存页和一个个的描述数据库块。
• 3、注意，此时的缓存页和描述数据块都是空的，毕竟才刚启动 MySQL 呢。

　　buffer pool通常由数个内存块加上一组控制结构体对象组成。内存块的个数取决于buffer pool instance的个数，不过在5.7版本中开始默认以128M(可配置)的chunk单位分配内存块，这样做的目的是为了支持buffer pool的在线动态调整大小。

　　Buffer Pool默认情况下是128MB，还是有一点偏小了，我们实际生产环境下完全可以对Buffer Pool进行调整。 比如我们的数据库如果是16核32G的机器，那么你就可以给Buffer Pool分配个2GB的内存。

Buffer Pool配置参数

innodb_buffer_pool_size

1. 作用：设置InnoDB Buffer Pool的总大小。
2. 默认值：在MySQL 5.7及更高版本中，默认值可能因版本而异，但通常较小（如128MB），建议根据服务器内存大小进行调整。
3. 调整建议：在生产环境中，应根据服务器的内存大小和数据库的工作负载进行适当调整。例如，对于16核32G的机器，可以给Buffer Pool分配2GB或更大的内存。

innodb_buffer_pool_chunk_size

1. 作用：定义Buffer Pool增加或减少大小时的操作块（chunk）大小。
2. 默认值：128MB。
3. 调整建议：在大多数情况下，保持默认值即可。当调整innodb_buffer_pool_size时，操作会以这个块大小为单位进行。

innodb_buffer_pool_instances

1. 作用：设置InnoDB Buffer Pool实例的个数。
2. 默认值：根据innodb_buffer_pool_size和innodb_buffer_pool_chunk_size自动计算得出（在某些版本中可能是固定的）。
3. 调整建议：当Buffer Pool比较大时（超过1GB），增加实例个数可以提高读写操作的并发性，减少竞争。通常建议将实例个数设置为CPU核心数或核心数的一半（具体取决于工作负载）。

innodb_old_blocks_pct

1. 作用：定义InnoDB Buffer Pool中“老”数据块所占的百分比，用于LRU（Least Recently Used）算法中的页面替换策略。
2. 默认值：37%。
3. 调整建议：这个参数通常不需要调整，除非你对数据库的访问模式有非常深入的了解，并且确信调整这个参数能够带来性能上的提升。

注意事项

1. 大小关系：Buffer Pool的大小必须是innodb_buffer_pool_chunk_size乘以innodb_buffer_pool_instances的整数倍。如果配置的innodb_buffer_pool_size不是这个乘积的倍数，MySQL会自动将其调整为最近的乘积值，但不少于指定的值。
2. 动态调整：从MySQL 5.7版本开始，支持在线动态调整Buffer Pool的大小（通过调整innodb_buffer_pool_size），而无需重启数据库服务。
3. 性能监控：在调整Buffer Pool配置后，应密切关注数据库的性能指标（如查询响应时间、I/O等待时间等），以确保调整带来了预期的性能提升。

        在理想情况下，Buffer Pool Size应尽可能大，但前提是必须为服务器上的其他进程保留足够的内存空间。当Buffer Pool足够大时，可以显著提高数据库性能，因为更多的数据可以被缓存在内存中，从而减少了对磁盘的访问。

在线调整Buffer Pool Size

注意事项：

1. 修改后并不会立即生效，而是需要等待所有当前事务执行成功后才会生效。
2. 在Buffer Pool Size调整生效前，新的连接和事务可能会处于等待状态。
3. 调整过程中，Buffer Pool会进行碎片整理、移除缓存页等操作。

Buffer Pool Size的调整单位

• Buffer Pool Size的调整是以innodb_buffer_pool_chunk_size为单位进行的。
• innodb_buffer_pool_chunk_size定义了每次增加或减少Buffer Pool大小时的操作块大小，默认值为128MB。

建议：

1. Buffer Pool Size最好设置为innodb_buffer_pool_chunk_size与innodb_buffer_pool_instances的整数倍。
2. 如果配置的Buffer Pool Size不是整数倍，MySQL会自动调整为其最近的整数倍值，但可能会略小于你配置的大小。

示例

假设机器配置为8GB内存，并希望将Buffer Pool Size设置为8GB，同时设置16个Buffer Pool实例：

在这种情况下，innodb_buffer_pool_size是innodb_buffer_pool_chunk_size与innodb_buffer_pool_instances的整数倍（假设innodb_buffer_pool_chunk_size保持默认值128MB）。

查看Buffer Pool状态

要查看Buffer Pool的当前状态，可以使用以下SQL语句：

这将显示Buffer Pool大小调整的状态信息。

Buffer Pool大小调整过程

增加过程：

1. 增加执行块（chunk）。
2. 指定新地址。
3. 将新加入的执行块加入到free list（管理执行块的列表）。

减少过程：

1. 重新整理Buffer Pool和空闲页。
2. 将数据从块中移除。
3. 指定新地址（可能涉及重新分配内存）。

---

创建一个用户表，表当中包含用户的id、年龄和姓名，并将用户的id设置成主键。如下：

创建表完毕后向表中插入一些数据，并且插入数据时没有按照主键的大小顺序插入。如下：

然后我们查看表中的数据：

 

这时为什么呢，根本原因就是：因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL底层也会自动按照主键对插入的数据进行排序。

3.2.1.理解单个Page

• MySQL启动后，其BufferPool中一定有许多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来。
• 怎么进行管理呢？只能是通过构建数据结构来管理的！！！
• MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。
• 此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

假设上述测试表中的记录都在同一个Page当中，那么该Page的结构大致如下：

 

因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。 

3.2.2 页内目录

• Page结构体内部存储的数据记录是以单链表的形式组织起来的，当页内部的数据量增多时，本质在页内部进行的还是线性遍历，效率比较低下。
• 为了解决这个问题，这时可以在Page结构体内部引入页内目录，将Page结构体内部存储的数据记录按照主键划分为若干区域，页内目录中就存储着这若干区域的最小键值。

页内目录的作用

        页内目录的主要作用是加速页内数据的查找过程。通过将页内的数据记录按照主键划分为若干区域，并在页内目录中存储这些区域的最小键值，可以快速地定位到目标数据可能所在的区域。一旦定位到区域，就可以在该区域内进行更小的线性遍历，从而找到目标记录。

页内目录的实现

1. 区域划分：
   • 页内的数据记录按照主键值进行排序。
   • 将排序后的记录划分为若干个连续的区域，每个区域包含一定数量的记录。
2. 目录存储：
   • 页内目录存储每个区域的最小键值。
   • 目录项通常包括指向区域起始记录的指针（或偏移量）和该区域的最小键值。
3. 查找过程：
   • 当需要查找某个主键值时，首先在页内目录中查找。
   • 通过比较目标主键值与目录项中的最小键值，确定目标记录可能所在的区域。
   • 在确定的区域内进行线性遍历，找到目标记录。

优点

• 加速查找：通过减少线性遍历的范围，页内目录可以显著提高查找效率。
• 灵活性：页内目录的大小和区域的数量可以根据页内数据量的变化进行调整。

注意事项

• 空间开销：页内目录会占用一定的存储空间，因此需要在空间开销和查找效率之间做出权衡。
• 维护成本：在插入、删除或更新记录时，需要维护页内目录的一致性，这可能会增加一些额外的开销。

比如在之前的Page内部引入页内目录后的结构大致如下：

 在每个Page结构体内部引入页内目录，目的是为了加速在单个Page内部数据查询的效率。由于这个页内目录也是保存在Page内部的，而单个Page的大小是固定为16KB，因此添加页内目录后Page内部能够保存的数据记录变少了，所以在Page内部引入页内目录本质是一种空间换时间的做法，就像给书添加目录需要花费更多的纸张一样。

---

3.2.3 多页情况

多个Page的示意图如下：

随着数据量不断增大，单个Page中无法存下所有数据，这时就需要用多个Page来存储数据。

• 这时在查询数据时就需要，先遍历由Page构成的双链表确定目标数据在哪一个Page中，然后再在该Page内部找到目标数据。
• 虽然在单个Page内部能够通过页内目录来快速定位数据，但在遍历Page双链表寻找目标Page时本质进行的还是线性遍历。
• 遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

---

3.2.4 给Page结构体创建页目录

那么如何解决呢？解决方案，其实就是我们之前的思路，给Page也带上目录！

• 使用一个目录项来指向某一页，其中每个目录项的构成是：键值+指针。（图中没有画全）
• 页目录中的每个目录项都指向一个Page，而这个目录项存放的就是其指向的Page中存放的最小数据的键值。
• 在给各个Page结构体建立页目录后，在查询数据时就可以先通过遍历页目录找到目标数据所在的Page，然后再在该Page内部找到目标数据。

给各个Page建立页目录后的示意图如下：

说明：

• 最终构建出来的实际就是一棵B+树，这棵B+树就是InnoDB的索引结构，其中每一层Page的作用就是加速它的下一层的查找效率。
• 如果我们创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
• MySQL中可能同时有大量的表正在被处理，因此Buffer Pool中可能会存在多个索引结构，也就是同时存在多个B+树结构，当我们查询表时访问的就是这张表对应的B+树结构。

思考：B+树中的Page结点是否需要全量加入到Buffer Pool中？

• 当对MySQL中的某张表进行增删查改操作时，不需要将其对应B+树的所有结点全量加入到Buffer Pool中，甚至在刚开始时只需要将B+树的根结点加入到Buffer Pool中。
• 当后续访问表中的数据时，再将该数据对应路径上的结点加入到Buffer Pool中即可，对于其他不需要的结点根本不用加入到Buffer Pool中，这一点和操作系统中的页表是很像的。
• 此外，在刷新数据时也不需要将B+树中所有的结点都进行刷新，在Page结构体中有一个标记位用来标记当前Page是否被修改过，如果被修改过则说明这是一个脏数据，在刷新数据时只有脏数据才需要被刷新到磁盘上。
• 由于B+树中的结点都是16KB大小的Page，因此无论是刷新数据到磁盘函数从磁盘加载数据到Buffer Pool，都是以Page为单位进行的，这也就是所谓的MySQL与磁盘交互的基本单位是Page。

如果把这棵B+树逆时针旋转90度，就会发现这其实就是操作系统中的页表结构，本质操作系统中的页表也是B+树结构。如下：

 

以32位平台为例，页表将一个虚拟地址转换成物理地址的过程如下：

1. 选择虚拟地址的前10个比特位在页目录当中进行查找，找到对应的页表。
2. 再继续选择虚拟地址后续的10个比特位在对应的页表当中进行查找，找到物理内存中对应页框的起始地址。
3. 最后选择虚拟地址中剩下的12个比特位作为偏移量，从对应页框的起始地址处向后进行偏移，最终得到的就是转换后的物理地址。

说明：

1. 12个比特位有种取值，字节对应就是4KB，所以物理内存中一个页框的大小就是4KB，这也就是为什么操作系统与磁盘交互的基本单位是4KB的原因。
2. 此外，页表中的各个B+树结点也不需要全量加入到内存中，而只需要加入访问到的结点即可，所以页表占用的内存大小实际是可控的，这也就是为什么二级页表可行的原因。

除了InnoDB存储引擎所采用的B+树结构，索引结构还可以采用哪些数据结构呢？

1. 链表：查找时是线性遍历，效率太低。
2. 普通二叉搜索树：可能退化成线性结构，这时查找还是线性遍历。
3.  AVL树和红黑树：虽然保证了二叉树是绝对或近似平衡的，不会退化成线性结构，但AVL树和红黑树都是二叉树结构，这就意味着树的层高会比较高，而查询数据时都是从根结点开始向下进行查找的，这也就意味着在查询过程中需要遍历更多结点，如果这些结点还没有被加载到Buffer Pool中，这时就需要进行更多次的IO操作，影响效率。

上面三种都不行！只有下面这种可以！！

        哈希表：官方的索引实现方式中MySQL是支持HASH的，只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是的，但哈希表也有一个缺点就是不利于进行数据的范围查找，每次查找都要经过哈希运算。

下面是几个常见的存储引擎，与其所支持的索引类型：

下面是几个常见的存储引擎，与其所支持的索引类型：

那有人就想问了B+树可以，那B树可不可以 ？

B树 VS B+树

 B+树是B树的一种变形结构，那为什么我们没有采用普通的B树作为索引结构呢？

1. 首先，普通B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变高变瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作。
2. 其次，普通B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找，而B+树的各个叶子结点之间是连接起来的，当我们进行范围查找时，直接先找到第一个数据然后继续向后遍历找到之后的数据即可，因此将各个叶子结点连接起来更有利于进行数据的范围查找。

B树：

B+树：

之前推导的主键索引结构是InnoDB存储引擎的主键索引结构，而MyISAM存储引擎同样采用B+树作为索引的基本数据结构。

• 与InnoDB存储引擎的B+树不同的是，MyISAM存储引擎的B+树的叶子结点存放的不是数据记录，而是数据记录对应的地址。

MyISAM存储引擎 - 主键索引结构

比如下图为MyISAM存储引擎的主键索引结构，其中Col1为主键。如下:

 

MyISAM存储引擎 - 普通索引结构

• MyISAM存储引擎的普通索引采用的也是B+树结构，与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。
• 因此一张表可能会同时存在多个B+树结构，但由于MyISAM存储引擎的B+树叶子结点中，存储的是对应的数据记录的地址，因此有效数据只会存储一份。

InnoDB存储引擎 - 普通索引结构

• InnoDB存储引擎的普通索引采用的也是B+树结构，为了减少数据的重复存储，普通索引的B+树的叶子结点中存储的不是数据记录，而是对应数据记录的主键值。
• 当根据普通索引查询数据时，会先查找普通索引对应的B+树，找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录，这个过程就叫做回表查询。

比如下图为InnoDB存储引擎的普通索引结构，其中Col3为索引列。如下：

说明：

• InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录，该做法本质一种以时间换取空间的做法。
• 当根据普通索引查询数据时，其实也不一定需要进行回表查询，因为有可能我们想要查询的就是这条记录对应的主键的值，因此查询完普通索引对应B+树后即可完成查询。
• 采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键，因为完整数据记录只会存储在主键索引对应的B+树中的，因此采用InnoDB存储引擎建立的表必须有主键。

---

讲到这里我们终于可以回答这个问题了：聚簇索引 VS 非聚簇索引

• 聚簇索引： 像InnoDB存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引。
• 非聚簇索引： 像MyISAM存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引。

当采用InnoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件。如下：

当采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件。如下：

说明：

• 采用InnoDB和MyISAM存储引擎创建表时都会生成xxx.frm文件，该文件中存储的是表结构相关的信息。
• 采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件，该文件中存储的是索引和数据相关的信息，这就是所谓的聚簇索引，索引和数据是存储在同一个文件中的。
• 采用MyISAM存储引擎创建表时会生成一个xxx.MYD文件和一个xxx.MYI文件，其中xxx.MYD文件中存储的是数据相关的信息，而xxx.MYI文件中存储的是索引相关的信息，这就是所谓的非聚簇索引，索引和数据是分开存储的。

4.1.1.主键索引

方法一：在创建表的时候，直接在字段名后指定 primary key

---

 方式二：在创建表的最后，指定某列或某几列为主键索引

---

方式三：创建表后，使用alter命令给指定字段添加主键索引。如下:

有没有发现，这个主键索引的创建和我们主键的创建是一模一样啊！！！！

主键索引的特点：

唯一性

1. 单一主键：在一张表中，主键索引是独一无二的，这意味着一个表只能有一个主键索引。但需要明确的是，这个主键可以由一个或多个列（字段）共同组成，这种情况被称为复合主键。
2. 值的唯一性：主键索引的列（或列组合）中的值必须是唯一的，不能存在重复。这一特性确保了表中每一行数据的唯一标识。

非空性

1. 非空约束：被设定为主键索引的列，其值不能为NULL。这是由主键的唯一性和标识性所决定的，因为NULL值无法唯一地标识一行数据。

数据类型

1. 整型偏好：尽管主键索引的列并不严格限定为整型（如INT），但在实践中，整型字段常被用作主键，因为整型数据在存储和比较时通常更为高效。特别是自增整型（如AUTO_INCREMENT INT），它们能够自动产生唯一的值，非常适合作为主键。
2. 字符串主键的考量：如果主键是字符串类型，尤其是长字符串，那么在进行索引查找、连接和排序等操作时，性能可能会受到影响，因为字符串的比较通常比整型比较更为复杂和耗时。

高效性

1. 查询优化：主键索引是数据库查询优化的关键。由于主键的唯一性和非空性，数据库系统能够迅速定位到所需的数据行，从而大幅提高查询效率。
2. 聚簇索引：在InnoDB等存储引擎中，主键索引还扮演着聚簇索引的角色。这意味着数据行会按照主键的顺序进行物理存储，进一步提升了范围查询和排序操作的性能。

复合主键的特殊性

1. 复合主键的构成：当单个列无法唯一标识一行数据时，可以使用多个列的组合来构成复合主键。复合主键同样需要满足唯一性和非空性的要求。
2. 复合主键的使用场景：复合主键常用于那些需要多个字段共同确定唯一性的场景，如具有多个属性的实体或需要避免数据重复的情况。

---

4.1.2.唯一索引

方式一：在创建表时，直接在对应的字段名后指定unique。如下：

---

方式二：创建表时，在表的后面指定某列或某几列为unique 

---

方式三：创建表后，使用alter命令给指定字段添加唯一索引。

---

 唯一索引的特点：

1. 多个唯一索引的存在性

• 在一个表中，可以创建多个唯一索引。这一点与主键索引不同，因为主键索引在一张表中是唯一的，只能有一个。

2. 查询效率的提升

• 唯一索引通过优化数据检索过程，可以显著提高查询效率。当数据库系统执行查询操作时，会利用唯一索引快速定位到所需的数据行，从而减少了全表扫描的次数和时间。

3. 数据的唯一性约束

• 在某一列或某几列上建立唯一索引后，必须保证这些列中的数据是唯一的，不能存在重复值。这一特性确保了数据的完整性和一致性，防止了数据重复插入的问题。

4. 与主键索引的等价性

• 如果在唯一索引上指定了NOT NULL约束，那么该唯一索引在功能上与主键索引是等价的。这是因为主键索引本身也具有唯一性和非空性的特性。然而，需要注意的是，尽管它们在功能上相似，但在实际应用中，主键索引通常用于标识表中的唯一行，而唯一索引则更多地用于确保特定列或列组合的唯一性。

5. 额外的注意事项

• 唯一索引会占用额外的存储空间，因为需要存储索引数据和表数据之间的映射关系。
• 当对表进行插入、删除或更新操作时，数据库系统需要更新唯一索引，这可能会增加数据操作的开销和时间。
• 在创建唯一索引之前，应确保该列或列组合中的数据是唯一的，否则创建索引时会失败。如果表中已经存在重复值，可以先进行数据清洗或删除重复值，然后再创建唯一索引。

---

4.1.3.普通索引

方式一：在表的定义最后，指定某列为索引index

---

方式二：创建表后，使用alter命令给指定字段添加普通索引。如下： 

---

方式三：创建表后，使用create命令给指定字段创建普通索引，并指定索引名。

如下： 

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

方法一（信息比较简略）： 使用desc表名

---

方法二：show keys from 表名 

• able： 表示创建索引的表的名称。
• Non_unique： 表示该索引是否是唯一索引，如果是则为0，如果不是则为1。
• Key_name： 表示索引的名称。
• Seq_in_index： 表示该列在索引中的位置，如果索引是单列的，则该列的值为1，如果索引是复合索引，则该列的值为每列在索引定义中的顺序。
• Column_name： 表示定义索引的列字段。
• Collation： 表示列以何种顺序存储在索引中，“A”表示升序，NULL表示无分类。
• Cardinality： 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
• Sub_part： 表示列中被编入索引的字符的数量，若列只是部分被编入索引，则该列的值为被编入索引的字符的数目，若整列被编入索引，则该列的值为NULL。
• Packed： 指示关键字如何被压缩。若没有被压缩，则值为NULL。
• Null： 用于显示索引列中是否包含NULL，若包含则为YES，若不包含则为NO。
• Index_type： 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
• Comment： 显示评注。

---

方法三：show index from 表名

删除主键索引

 下面是user1的结构：

删除主键索引：

 

---

• 其他索引的删除

或者：

索引名就是show keys from 表名中的 Key_name 字段。

删除user4的索引：

删除user8的索引：

 

说明：

• 一个表只有一个主键索引，所以在删除主键索引的时候不用指明索引名，而一个表中可能有多个非主键索引，所以在删除非主键索引时需要指明索引名。

---

1.最左前缀匹配原则

非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

4.索引列不能参与计算

保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

5.尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

6．选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

7．为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

8．为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

9．限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

10．尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

11．尽量使用前缀来索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

12．删除不再使用或者很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

版权声明：

本文来源网络，所有图片文章版权属于原作者，如有侵权，联系删除。

本文网址：https://www.mushiming.com/mjsbk/14046.html