MySQL基础索引知识【索引创建删除 | MyISAM InnoDB引擎原理认识】

  博客主页：花果山~程序猿-CSDN博客

文章分栏：MySQL之旅_花果山~程序猿的博客-CSDN博客

关注我一起学习，一起进步，一起探索编程的无限可能吧！让我们一起努力，一起成长！

目录

 一，索引用处

二，磁盘

三，mysql 与磁盘的基本交互单位

四，管理page的数据结构（InnoDB引擎下）

单个page

多个page

B+树  VS B树 

聚簇索引 VS 非聚簇索引

辅助索引（普通索引）

MyISAM 引擎

InnoDB引擎

五，索引的操作

 主键索引

1.创建

2.删除

普通索引

1.创建

2. 删除

表索引查询

使用索引的原则

结语

---

嗨！收到一张超美的图，愿你每天都能顺心！

 一，索引用处

        首先我们需要知道一个结论，合适索引可以大大的提高对数据检索的效率，具体优化比如减少磁盘I/O操作等更细节的优化，待我们在更深层次的了解再谈吧，现在我们只需要知道，索引可以大大提升数据检索的效率，即可。

首先熟悉一下新名词，常见的索引种类有：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)--解决中子文索引问题

二，磁盘

        我们知道mysql是对数据进行管理，存储的上层应用，一旦涉及存储就一定会涉及磁盘这个外设（企业服务器用的基本是机械硬盘，谁叫他便宜），而磁盘属于一种机械设备，效率不能与电子元件相比，加上I/O的特性，那如何提升mysql的检索效率？——这得从磁盘讲起

了解磁盘

关于磁盘的运行和读取，感兴趣的同学可以去网上了解，这里就不赘述了。

我们知道磁盘扇区大小有512字节，也有4k的，那操作系统进行I/O操作也是以512，4k进行交互的吗？ 答：不是

 解释：

•   如果操作系统是依据磁盘提供的扇区大小，进行交互的，那扇区大小变化，则操作系统也得变化 。
•   如果一次I/O才512字节，那么I/O次数多，效率自然不高
•   我们学习的文件操作，数据单位是数据块，并不是扇区。

结论：系统与磁盘交互的单位是数据块，具体大小是4K。(局部性原理，预加载后面的数据)

这里补充一下，随机访问与连续访问：

随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。

连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。

结论：随机访问，I/O次数多，效率低；连续访问，I/O次数少，效率高。

三，mysql 与磁盘的基本交互单位

        我们清楚mysql是不能直接操作硬件的，这是一种忽略操作系统的逻辑理解。

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎讲解)

下面就用一张图进行理解吧。

在 Linux 系统中,文件缓冲区大小默认为4KB。但是可以通过修改内核参数来调整这个值,比如将 /proc/sys/vm/page-size 设置为 16384 来将缓冲区大小设置为 16KB(来源AI)

结论：从上面我们可以知道，硬盘与操作系统之间的交互是4k，mysql在InnoDB引擎下与磁盘之间进行16KB的IO交互，而这个交互单位在mysql中被称作 page 。

因此从上面认识中，我们可以有一下理解：

1. mysql中的文件，是用page为单位来进行存储的。
2. mysql的cord操作，对数据进行修改，首先找到数据page所在的位置，需要持久化时，根据page的位置进行覆盖更新即可。
3. 只要是涉及计算的cpu就一定会参与其中，数据也必然存在于内存中，这时这个计算中就会出现两份数据，一份在内存中，一份在磁盘中；需要持久化时，操作系统会根据其刷新策略刷新到磁盘中，也就是一次I/O，单位为page。
4. mysql服务端启动时会申请一个内存空间用来进行数据操作，这块空间叫Buffer Tool  大小为128MB，可见为这个缓存条很大。

四，管理page的数据结构（InnoDB引擎下）

        上面我们我们提到Buffer Tool是一个很大的内存空间，里面存放着从磁盘获取的数据，也有曾经使用过的污染数据，也有等待持久化的数据。

问题来了？这真的只是一个简单的内存块吗？

——不会是，根据先组织，后管理思想，里面必然有数据结构进行管理——这也是为什么当我们插入没有主键的表时，结果会为我们排序。

为什么mysql交换单位为page，而且是16k? 为什么不是要多少给多少？

答：这种策略叫做局部性原理，计算机读取一部分资源，大概率会读取周边数据，因此直接一次性将那一块数据缓存了。之所以I/O交互慢，I/O次数占大头，大量的随机访问会造成效率低下。

单个page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表。

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

问：为什么插入时，进行排序？

因为，进行排序是为了提高查询效率。

我们知道链表插入与删除效率高（不用移动其他数据，只用修改prev,next指针）但是查询，修改效率低，插入时排序可以让每次查询都是有效数据（比如说查询10，你本可以避免对13的查询）

多个page

在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，本质上是对page逐条查找。当多个page通过链表相连时，线性查找效率低下。

因此page的结构加入了目录，如下：

这是一种空间换时间的做法，减少单page存放的数据量，添加目录的数量，这样当查询此页时，只需要查看目录，就能以较少的次数就能查找到数据。

但也有一个问题，上面的方法仍然需要大量的I/O，page依然会被一个一个地加载到内存中！本质上效率提升不了多少，那怎么减少I/O的次数？

将带有数据page的目录给管理起来，如图所示：

（载有数据的page，通过链表相连；page的目录信息，将被会被外部的页目录管理） 

没错这是一颗B+树，数据量越大，减少的I/O次数越明显。 

（B+每个节点可以管理大量的目录page，和大量的数据page，每增加一个高度的节点管理的数据将成指数级增长，因此B+树是一个矮胖型的树，矮胖意味着途径的节点少，每次进入一个节点就是一次I/O，因此I/O次数少，而二叉树，avl数/红黑树这类受瘦高结构的树不合适，哈希类结构不支持范围查找）

关于B+树的实现，可以关注我未来的的高阶数据结构，我们来手把手实现。

复盘一下

B+树将Page分为目录页和数据页。目录页只放各个下级Page的最小键值。

查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，从而大大减少了I/O次数。

下面是各引擎，底层支持的数据结构：

B+树  VS B树 

问：为什么使用B+树，而不是使用B树？

首先我们来看看B树

特点：1.每个节点会包含数据；2. 数据之间没有进行连接。

光B树的特点我们就知道，其不适合磁盘I/O：

• 原因1 ：相同的目录页，能通过目录管理的数量会变少，变少意味一次着淘汰的数据量少，需要多次淘汰，因此B树的高度会更高，I/O次数更多。
• 原因2：B树数据之间缺少连接，不利于范围查找，变相提高I/O次数。

聚簇索引 VS 非聚簇索引

InnoDB存储索引，本事就是聚簇索引，那什么是非聚簇索引？下面我们了解一下mysql另一个索引——MyISAM

MyISAM 存储引擎-主键索引

MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引， Col1 为主键。

因此，我们可以简单的区分聚簇索引与非聚簇索引，叶子节点的data域存放真实数据的是聚簇，data域存放数据指针的叫做非聚簇。

两种引擎创建文件时的不同：

辅助索引（普通索引）

当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息（例如唯一键）建立的索引，一般这种索引可以叫做辅助（普通）索引

对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

MyISAM 引擎

下图就是基于 MyISAM 的 Col2（非主键） 建立的索引，和主键索引没有差别：

InnoDB引擎

下图是基于InnoDB的Col3建立的索引，但与主键索引有区别，如下：

值得我们注意的是InnoDB引擎下，辅助索引结果不存放完整数据，而是只存放主键(key).

所以InnoDB普通索引需要 2次索引：首先，普通索引查找记录找到主键，然后通过主键来找到数据记录。这个过程叫做——回表查询 

那为什么InnoDB要这么设计2次索引呢？？

答：太浪费空间了。

1.MyISAM的主键索引与普通索引基本上没差别的原因是结果都是数据地址（非聚簇索引），占用的空间比较小。

2.一张表是可以有多张索引，都会储存到文件中，而没有必要多存一份数据，而如果结果都是数据（聚簇索引）将会有大量的储存开销。

五，索引的操作

 主键索引

1.创建

第一种：创建表时，添加主键约束，mysql会自动帮我们创建索引

-- 在创建表的时候，直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));-- 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

第二种：在创建表后，添加主键（当然前提是没有主键）

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

2.删除

alter  table  表名  drop  primary  key;

普通索引

1.创建

我们需要知道的是 unique（唯一键）也是普通索引的一员，他的创建也是普通索引的创建，mysql也会为其创建索引结构

第一种：表创建时 

create table user8(id int primary key,
name varchar(20)  unique, --唯一键
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);

第二中：表创建后 + 索引重命名

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引alter index my_index_name user9(name); --对索引重命名

2. 删除

我们知道unique是普通索引中的一员，我们删除unique索引时，也是使用下面普通索引统一的删除法： 

alter  table  表名  drop  index 索引名; 

表索引查询

展示这个表中所有的索引信息

show  keys  from 表名 \G;

show  index  from 表名 \G;

使用索引的原则

• 经常被频繁调用的，适合作为索引——效率提的高
• 唯一性太差的不适合作为索引
• 更新比较频繁的不适合作为索引——这需要频繁的重新创建索引
• 基本不会作为where条件判断的不适合索引

结语

   本小节就到这里了，感谢小伙伴的浏览，如果有什么建议，欢迎在评论区评论，如果给小伙伴带来一些收获，请动动你发财的小手点个免费的赞，你的点赞和关注永远是博主创作的动力源泉。