首页 > 数据库 >MySQL基础索引知识【索引创建删除 | MyISAM & InnoDB引擎原理认识】

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

时间:2024-05-31 21:29:42浏览次数:29  
标签:page 索引 InnoDB MyISAM mysql 磁盘 数据 主键

  博客主页:花果山~程序猿-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条件判断的不适合索引

结语

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

标签:page,索引,InnoDB,MyISAM,mysql,磁盘,数据,主键
From: https://blog.csdn.net/qq_72112924/article/details/139361502

相关文章

  • 中型研发组织管理(索引页)
     前言在此,总结下本人在数年研发管理过程中的一些经验,本次希望能结构化,作为工具书,供需查询参考。自我管理管理者必须具备的基本素质沟通向上沟通向下沟通跨部门沟通团队管理组织架构设置leader管理选择人了解人要求人激励人培养人(待补充)评估人团队优化(待补充)绩效管......
  • Mysql视图和索引
    什么是视图(view)   MySQL中的视图(View)是一个虚拟的表,其内容由查询定义。视图并不在数据库中以存储的数据值形式存在。行和列数据来自由定义视图的查询所引用的表,并且在引用视图时动态生成。视图的优势简化复杂性:通过视图,用户可以只看到他们感兴趣的数据或数据的简化版......
  • MySQL全文索引实现简单版搜索引擎
    1前言只有Innodb和myisam存储引擎能用全文索引(innodb支持全文索引是从mysql5.6开始的)char、varchar、text类型字段能创建全文索引(fulltextindextype)全文索引的基于关键词的,如何区分不同的关键词了,就要用到分词(stopword)英文单词用空格,逗号进行分词;中文分词不方便(一个句......
  • mysql高级篇学习(数据表的设计方法,索引优化)
    使用docker安装mysql安装docker#yum包更新到最新yumupdate#卸载旧的dockeryumremovedocker\docker-client\docker-client-latest\docker-common\docker-latest\......
  • MongoDb索引
    MongoDb索引数据库索引类似于图书索引。有了索引便不需要浏览整本书,而是可以采取一种快捷方式,只查看一个有内容引用的有序列表。这使得MongoDB的查找速度提高了好几个数量级。不使用索引的查询称为集合扫描,这意味着服务器端必须“浏览整本书”才能得到查询的结果。MongoDB如......
  • 深入探索 MongoDB:高级索引解析与优化策略
    MongoDB是一种非常流行的NoSQL数据库,它支持丰富的索引类型和功能,以提高数据查询的效率和性能。本文将详细介绍MongoDB的高级索引,包括基本语法、常用命令、示例、应用场景、注意事项和总结。基本语法在MongoDB中,可以使用createIndex()方法创建索引,语法如下:db.col......
  • 7款优秀的AI搜索引擎工具推荐
    AI搜索引擎不仅能够理解复杂的查询语句,还能够通过学习用户的搜索习惯和偏好,提供更加个性化的搜索结果。本篇文章将介绍7款在这一领域表现出色的AI搜索引擎工具,它们各有特色,但都致力于为用户提供更加智能、高效和精准的搜索体验。传统的搜索引擎在处理模糊或多义性强的查询时往往......
  • 数据库聚簇索引——not null条件对唯一键索引成为聚簇索引的影响
    数据库聚簇索引的规则如下:如果有主键,则主键是聚簇索引(当然主键也不一定是单个列的);如果没有主键,但是有notnull修饰的唯一键索引,则这个索引是聚簇索引;都没有,创建一个叫db_row_id的6字节隐藏列为聚簇索引,这个索引程序员不可见(这个列存在一定冲突和性能问题,详见大佬:https://www.c......
  • InnoDB锁
    为什么要加锁?锁的存在是为了在多个并发请求存取数据的情况下,维护数据一致性锁分类共享/排他锁对行记录加锁——行锁共享锁:S锁,在事务要读取一条记录时,需要先获取该记录的S锁。排他锁:X锁,在事务要改动一条记录时,需要先获取该记录的X锁。X锁与任何锁都不兼容对表加锁—......
  • 搜索引擎ElasticSearch18_Spring Data ElasticSearch 使用6
    一、SpringDataElasticSearch简介1、什么是SpringDataSpringData是一个用于简化数据库访问,并支持云服务的开源框架。其主要目标是使得对数据的访问变得方便快捷,并支持map-reduce框架和云计算数据服务。SpringData可以极大的简化JPA的写法,可以在几乎不用写实现的情况......