本文主要是介绍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 。
因此从上面认识中,我们可以有一下理解:
- mysql中的文件,是用page为单位来进行存储的。
- mysql的cord操作,对数据进行修改,首先找到数据page所在的位置,需要持久化时,根据page的位置进行覆盖更新即可。
- 只要是涉及计算的cpu就一定会参与其中,数据也必然存在于内存中,这时这个计算中就会出现两份数据,一份在内存中,一份在磁盘中;需要持久化时,操作系统会根据其刷新策略刷新到磁盘中,也就是一次I/O,单位为page。
- 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条件判断的不适合索引
结语
本小节就到这里了,感谢小伙伴的浏览,如果有什么建议,欢迎在评论区评论,如果给小伙伴带来一些收获,请动动你发财的小手点个免费的赞,你的点赞和关注永远是博主创作的动力源泉。
这篇关于MySQL基础索引知识【索引创建删除 | MyISAM InnoDB引擎原理认识】的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!