[MySQL] MySQL中的索引

2023-12-13 20:42:05

文章目录

一、初识索引

1、1 索引的概念

1、2 索引案例

二、认识磁盘

2、1 磁盘结构

2、2 操作系统与磁盘的数据交互

2、3?磁盘随机访问与连续访问

2、4?MySQL与磁盘的数据交互

三、索引的理解

3、1 建立测试表

3、2?为何MySQL与磁盘IO交互是 Page

3、3 理解Page

3、3、1 页目录

3、3、2 多个Page

3、4 索引所能够采用的数据结构?

3、5 B树与B+树

3、6 聚簇索引与非聚簇索引

四、索引操作

4、1 索引的创建

4、1、1 主键索引的创建

4、1、2?唯一索引的创建

4、1、3?创建普通索引

4、1、4?全文索引的创建

4、2 查询索引

4、3 删除索引

4、4 索引创建原则


🙋?♂??作者:@Ggggggtm?🙋?♂?

👀?专栏:MySQL?👀

💥?标题:MySQL中的索引💥

????寄语:与其忙着诉苦,不如低头赶路,奋路前行,终将遇到一番好风景???

一、初识索引

1、1 索引的概念

? 在MySQL中,索引是一种数据结构用于提高查询效率和加速数据检索。创建索引可以加快数据库中表的查询操作,减少查询所需的时间和资源消耗。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删 除的速度为代价的,这些写操作,增加了大量的IO。所以它的价值,在于提高一个海量数据的检索速度。

? 常见的索引类型有如下几种:

  • 主键索引(Primary Key Index):用于唯一标识表中的每一行。一个表只能有一个主键索引。
  • 唯一索引(Unique Index):确保被索引的列中的值是唯一的。
  • 普通索引(Normal Index):最基本的索引类型,没有唯一性约束。
  • 全文索引(Full-Text Index):用于全文搜索,适用于大段文本的搜索。

? 到这里你可能依然对索引的概念是一种模糊的状态,后文我们会详细解释。看会此篇文章再来回头看索引的概念,相信你就会理解了。?

1、2 索引案例

? 怎么来证明索引可以很好的提高查询效率呢?我们个人在平常使用数据库时,并没有感觉到查询的速度有很慢的情况。但是在实际应用中,往往一张表中会包含了大量的数据此时查询其中一条数据时,速度就会较慢。接下来我们先弄出来了一张数据量较大的表,该表包含八百万条数据。具体的构建过程如下:

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;


--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解
--产生随机字符串

delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

--产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;

--创建存储过程,向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;

-- 执行存储过程,添加8000000条记录
call insert_emp(100001, 8000000);

? 注意:由于创建表的数据量过大,所以创建的过程中需要一定的时间。当创建完成时,不要查看表中所有的数据,因为数据量过大,且IO效率低。我们来看创建好后的表:

? 现在我们在此表中以员工工号来查询一个员工的数据,我们看看所用时间:

? 如上图所示,每条查询语句所用到的时间大概为4.5秒左右,且在查询的过程中明显能够感受到停顿。试想在实际应用中,查询一个用户的信息用了4秒时间,可以接受吗?当然不可以!下面我们创建一个索引,再来查询看看是否又效率提升!构建索引具体如下:

? 上述我们也提到了,索引本质上就是一种数据结构,当我们在给员工表中的工号建立索引时,实际上数据库底层就会以工号来构建特定的数据结构。由于表中的数据量较大,建立索引时也需要花费较长时间。这时我们再来以工号查询员工信息,具体如下:

? 这时候再查询时,发现速率大大的提升了。通过上图也能看到,耗费的时间小于0.01秒。那么问题来了:我们一直在说索引本质上是一种数据结构,那到底是什么数据结构呢在不添加索引的情况下,效率慢在哪里呢

二、认识磁盘

? 为了数据持久化,最终mysql中的数据是要存储到磁盘上的。我们前面也了解到了,在创建数据库和表时,就是在对应的目录下创建了文件夹和特定文件。所以我们很有必要来了解一下磁盘。

? 磁盘是计算机中的一个机械设备,相比于计算机其他电子元件,磁盘效率是比较低的,在加上IO本身的特征,可以知道,如何提交效率,是 MySQL 的一个重要话题。

2、1 磁盘结构

? 磁盘的整体结构如下图:

? 重要组成硬件解释:

  1. 盘片:磁盘中最重要的组成部分之一。通常,磁盘中会有多个盘片叠放在一起,每个盘片都是由坚固且非磁性的材料制成,如铝合金或玻璃。盘片上覆盖着一个可磁化的薄膜,用来存储数据。

  2. 磁头:位于磁盘中的磁头负责读写数据。每个盘片都有两个磁头,一个位于盘片的上表面,另一个位于下表面。这些磁头能够飞行在盘片上方,通过电磁感应读取和写入数据。

  3. 主轴:主轴是磁盘驱动器中的一个旋转轴,它用来支撑盘片并使其高速旋转。主轴由电机驱动,通常转速在5400到15000转/分钟之间。

  4. 永磁铁:?机械硬盘的存储方式与磁带比较类似,磁体具有记忆的功能,永磁铁是为了保证磁性的稳定。
  5. 音圈马达:?硬盘读取数据的关键部位,主要作用是将存储在磁盘上的信息转换为电信号向外传输。
  6. 空气滤波片:?过滤空气硬盘透气孔中进入的空气,保证硬盘内部清洁,同时还可以防止硬盘内部的零件氧化,确保硬盘安全使用。
  7. 串行接口:?用来连接电脑与硬盘的接口,起到传输的作用。

? 在上图中我们看到盘片只有一片,实际上是有多片叠放在一起的。且每个盘片的两面都是可以存储数据的。具体如下图:

? 每个盘片又被划分为多个区域,且每个区域所能够存储容量大小是相同的,我们称该区域为扇区。具体如下图:

??从上图可以看出来,在半径方向上,距离圆心越近,扇区越小,距离圆心越远,扇区越大那么,所有扇区都是默认512字节吗?目前是的,我们也这样认为。因为保证一个扇区多大,是由比特位密度决定的。不过最新的磁盘技术,已经慢慢的让扇区大小不同了,不过我们现在暂时不考虑。

? 从上图中也能看出,盘片表面划分为多个同心圆轨道,每个轨道称为一个磁道。实际上是每个磁道又被细分为扇区,用于存储数据。

? 数据库文件就是保存在磁盘中的一个个扇区中的,因此找到一个文件本质就是,在磁盘上找到保存该文件的所有扇区。而我们能够定位任何一个扇区,那么便能找到所有扇区,因为查找方式是一样的。那么怎么可以很快的定位到一个扇区呢

? ?我们先看如下图:

??柱面是多盘磁盘,每盘都是双面,大小完全相等。那么同半径的磁道,整体上便构成了一个柱面 。每个盘面都有一个磁头,那么磁头和盘面的对应关系便是1对1的。

? 那么现在寻址的思路就很明确了:先确定柱面,在确定磁头。这下就确定在那个盘片上了。然后在找对应的扇区,就可以很好的找到对应的存储位置了。我们也称之为CHS寻址(柱面(Cylinder)- 磁头(Heads)- 扇区(Sector)寻址)。?在CHS寻址中,通过指定柱面号、磁头号和扇区号来确定数据的物理位置。通过这三个参数,计算机可以准确地访问磁盘上的数据。

??不过实际系统软件使用的并不是 CHS (但是硬件是),而是 LBA ,一种线性地址,可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS (这个转换过程由磁盘控制器负责执行,它根据磁盘的物理结构和磁盘参数表来进行适当的转换),交给磁盘去进行数据读取。

2、2 操作系统与磁盘的数据交互

? 我们在对mysql内部的数据等做操作(CURD)的时候,本质其实就是操作文件内容,那么文件必须先被打开。对文件内容做任何操作,都不是直接在磁盘设备上做操作的!通过冯诺依曼结构我们知道,CPU只与内存进行数据交互。这就意味着任何磁盘数据,在进程中要进行操作,本质都必须在内存中进行!那么在对磁盘数据进行操作时,一定会先将磁盘的数据加载到内存当中,由操作系统进行管理

??操作系统与磁盘进行IO交互的基本单位是4KB,而不是扇区的大小512字节,原因如下:

  1. 物理内存实际是被划分成一个个4KB大小的页框的,磁盘上的数据也会被划分成一个个4KB大小的页帧,因此操作系统与磁盘以4KB为单位进行IO交互,就能提高数据加载和保存的效率。
  2. 操作系统与磁盘进行IO交互时,如果直接以扇区的大小作为IO的基本单位,那么这时系统的IO代码和硬件就是强相关的,将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。主要实现了OS与磁盘的解耦。
  3. 此外,以扇区的大小作为IO的基本单位太小了,这就意味着读取同样的数据内容,需要进行更多次的磁盘访问,而磁盘的效率是比较低的,这样IO效率就降低了。以4KB为基本单位可以减小IO次数。

2、3?磁盘随机访问与连续访问

? 随机访问: 本次IO所给出的扇区地址与上次IO给出的扇区地址不连续,磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO。
连续访问: 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的,磁头很快就能找到目标扇区进行IO。
? 尽管两次IO是在同一时刻发出的,但如果它们请求的扇区地址相差很大,那也只能称为随机访问,因为连续访问中的连续指的是访问的扇区地址的连续,而不是访问时间的连续,由于连续访问不需要过多的定位,因此效率比较高

2、4?MySQL与磁盘的数据交互

??而 MySQL 作为一款应用软件,可以想象成一种特殊的文件系统。它有着更高的IO场景,所以,为了提高基本的IO效率, MySQL 进行IO的基本单位是 16KB。我们通常称这个基本单元为 Page。这里的 Page 与系统的 Page 并不相同,需要区分一下的。? 在Linux下,MySQL分为客服端(mysql)与服务端(mysqld)。

  • 上文中也提到了,在MySQL中进行的各种CRUD操作时,都需要先通过计算找到对应的操作位置,只要涉及计算就需要CPU参与,而冯诺依曼体系结构决定了CPU只能和内存打交道,因此为了便于CPU参与,就需要先将数据加载到内存当中。
  • 那么在特定的时间内,MySQL中的数据一定是同时存在于磁盘和内存中的,当操作完内存数据后,再以特定的刷新策略将内存中的数据刷新到磁盘当中,这时MySQL和磁盘进行数据交互的基本单位就是Page。
  • 为了更好的支持上述操作,MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存,这块内存空间叫做Buffer Pool,后续磁盘中加载的数据就会保存在Buffer Pool中,刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。
  • 由于内核中是有内核缓冲区的,因此MySQL从磁盘读取数据时,需要先将数据从磁盘读取到内核缓冲区,再将数据从内核缓冲区读取到Buffer Pool,MySQL将数据刷新到磁盘时,同样需要先将数据从Buffer Pool刷新到内核缓冲区,再将数据从内核缓冲区刷新到磁盘。

? 实际上,mysqld并不直接与磁盘打交道,而是利用了中间的内核缓冲区。所谓MySQL 进行IO的基本单位是 16KB,我们可以理解为一次从Buffer Pool向内核缓冲区 读取/写入 的大小为16KB,然后再让内核缓冲区 读取/刷新 到磁盘上。

三、索引的理解

3、1 建立测试表

? 建表代码如下:

create table if not exists user (
    id int primary key, --一定要添加主键哦,只有这样才会默认生成主键索引
    age int not null,
    name varchar(16) not null
);

? 具体如下图:

? 我们在向表中插入一些数据,具体如下图:

? 发现插入数据时id值是乱序进行插入的,但是我们查询表中的信息发现是有序的!排序的工作是谁做的呢为什么要进行排序呢

3、2?为何MySQL与磁盘IO交互是 Page

??为何MySQL和磁盘进行IO交互的时候,要采用Page的方案进行交互呢?用多少,加载多少不香吗?

? 如上面的5条记录,如果MySQL要查找id=2的记录,第一次加载id=1,第二次加载id=2,一次一条记录,那么就需要2次IO。 如果要找id=5,那么就需要5次IO。 但如果这5条(或者更多)都被保存在一个Page中(16KB,能保存很多记录),那么第一次IO查找id=2的时候,整个Page会被加载到MySQL的Buffer Pool中,这里完成了一次IO。但是往后如果在查id=1,3,4,5等,完全不需要进行IO了,而是直接在内存中进行了。所以,就在单Page里面,大大减少了IO的次数。我们知道IO的效率是很慢的。
? 你怎么保证,用户一定下次找的数据,就在这个Page里面?我们不能严格保证,但是有很大概率,因为有局部性原理。什么是局部性原理?计算机的局部性原理(Locality Principle)是指在程序执行过程中,访问内存的趋势倾向于集中在某些特定的内存区域或数据块上。当一个数据被访问后,其附近的数据也很可能会被访问。比如,当程序访问某个内存地址时,它很可能会在接下来的指令中继续访问相邻的内存地址。这种局部性原理可以通过预取(Prefetching)和预读(Prefetching)等技术来优化内存访问效率。
? 往往IO效率低下的最主要矛盾不是IO单次数据量的大小,而是IO的次数

3、3 理解Page

? 在MySQL中,不仅仅是只有一张表。大概率会同时管理着多张表数据。那么而要管理好这些表,就需要 先描述,在组织?。我们目前可以简单理解成一个个独立表是有一个或者多个Page构成的。那么MySQL将内存中的每一个Page都用一个结构体描述起来,然后再将各个结构体以双链表的形式组织起来,因此一个Page结构体内部既包含数据字段,也包含属性字段。我们用上述的测试数据来理解,如下图:

? 我们也知道,再插入数据时是无序的,为什么查询的时候就有序了呢?也就是为什么要进行排序。我们接着往下看。

3、3、1 页目录

? 我们知道,当一张表中的数据很多时,查询的速度就会变的很慢。本质上还是因为进行的遍历查询。那么有没有什么办法,能够加快我们的查询速度呢?我们在看《谭浩强C程序设计》这本书的时候,如果我们要看<指针章节>,找到该章节有两种做法:

  1. 从头逐页的向后翻,直到找到目标内容(遍历,较慢)。
  2. 通过书提供的目录,发现指针章节在234(假设),那么我们便直接翻到234页。同时,查找目录的方案,可以顺序找,不过因为目录肯定少,所以可以快速提高定位。
  3. 本质上,书中的目录,是多花了纸张的,但是却提高了效率。
  4. 所以,目录,是一种空间换时间的做法

? ?通过上述例子,我们就想到:一个Page中能不能也引入目录呢?答案是可以的。MySQL也正是这么做的。具体如下图:

? 当数据量很大的时候,目录就起到的很好的过滤无用数据的作用。那么当前,在一个Page内部,我们引入了目录。比如,我们要查找id=4记录,之前必须线性遍历4次,才能拿到结

果。现在直接通过目录 2[3],直接进行定位新的起始位置,提高了效率。当我们在添加页内目录时,Page内部能够保存的数据记录变少了,所以 在Page内部引入页内目录本质是一种空间换时间的做法,就像给书添加目录需要花费更多的纸张一样
? 但现在或许你就理解了为什么要对插入的数据进行排序了。就是为了能够更好的引入目录,提高查询的速度。因为我们在创建表的时候,设置了id为主键,所以MySQL会以主键为索引进行排序的。

? 但是,随着数据量不断增大,单个Page中无法存下所有数据,这时就需要用多个Page来存储数据。这时在查询数据时就需要,先遍历Page双链表确定目标数据在哪一个Page,然后再在该Page内部找到目标数据。具体如下图:

? 那么当我们线性遍历在那个Page当中时,注意访问Page时,就需要将Page从磁盘加载到内存当中,这就意味着需要多次IO,效率也是比较低的。怎么解决多个Page效率低的问题呢?

3、3、2 多个Page

? 能不能在多个Page之上再次引入目录呢?答案是可以的。解决方案,其实就是我们之前的思路,给Page也带上目录。

  1. 使用一个目录项来指向某一页,而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
  2. 和页内目录不同的地方在于,这种目录管理的级别是页,而页内目录管理的级别是行。
  3. 其中,每个目录项的构成是:键值+指针。

? 具体如下图:

??随着数据量不断增大,Page变得越来越多,这时一个页目录无法管理所有的Page,这时就需要更多个的页目录。这些页目录也是一个个的Page结构体,只不过这些Page结构体中存放的不是数据记录,而是各个Page的目录信息。

? 那么好了,当数据量更多的时候,存在着很多的用Page存储目录时,我们只需要在该Page之上再次创建Page目录进行管理即可。具体如下图:

??我们上图中,最终构建出来的就是一棵B+树。到这里,我们已经给我们的表user构建完了主键索引。我们接下来再来详细的分析和总结一些问题

  1. 索引本质上就是一种数据结构,那么这个数据结构就是指的B+树。
  2. 当我们创建一个表时,如果该表中有主键,那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来,保存在Buffer Pool当中,当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
  3. 那要是没有主键呢?MySQL会自动形成隐藏主键,同样会构建成B+树将数据存储起来。只不过我们在查询时,并不是按照主键进行查询的,所以本质上还是进行的线性遍历。
  4. 我们上述讲述的B+树存储结构中,只有在叶子节点中存储数据。
  5. 一个Page的大小为16KB,可存储大量数据。当一个Page中只存储目录项时,可想而知能够管理多大的数据。所以B+树整体是一颗矮胖的多叉树。查找的时候,自定向下找,只需要加载部分目录页到内存(并不需要加载整颗B+树,最开始只需要根节点即可),即可完成算法的整个查找过程,大大减少了IO次数。

? 相信到这里,你就会对索引有一个很好的了解。那么索引能不能采用其他的数据结构呢?

3、4 索引所能够采用的数据结构?

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

  • 链表:查找时是线性遍历,效率太低。
  • 普通二叉搜索树:可能退化成线性结构,这时查找还是线性遍历。
  • AVL树和红黑树:虽然保证了二叉树是绝对或近似平衡的,不会退化成线性结构,但AVL树和红黑树都是二叉树结构,这就意味着树的层高会比较高,而查询数据时都是从根结点开始向下进行查找的,这也就意味着在查询过程中需要遍历更多结点,如果这些结点还没有被加载到Buffer Pool中,这时就需要进行更多次的IO操作,所以最终没有选择其作为索引结构。
  • 哈希表:官方的索引实现方式中MySQL是支持HASH的,只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是 O ( 1 )? 的,但哈希表也有一个缺点就是不利于进行数据的范围查找。

3、5 B树与B+树

? MySQL的索引采用了B+树,为什么不采用B树呢?

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

? 下图是B树的结构,可以结合的理解一下:

3、6 聚簇索引与非聚簇索引

??聚簇索引(Clustered Index): 聚簇索引是将表的数据按照索引键的顺序物理地存储在磁盘上。每张表只能有一个聚簇索引,因为它决定了表中数据的物理存储方式。当创建聚簇索引时,表的数据会根据索引键的顺序进行重新排序,并且数据行的物理位置与其在索引键中的顺序一致。

? 我们上述讲解索引的数据结构B+树时,所解释索引的类型就是聚簇索引。最明显的特点就是数据存储在B+树的叶子节点当中。在前面文章我们也讲到过,再创建一张表时默认的存储引擎是InnoDB。具体如下图:

? 那我们大概也就知道了,InnoDB存储引擎的索引结构采用的是B+树,同时索引类型为聚簇索引。?

? 非聚簇索引(Non-clustered Index): 非聚簇索引是在单独的数据结构中存储索引键和对应的行指针,而不是存储实际的数据行。一个表可以有多个非聚簇索引。非聚簇索引通过维护一个索引树,加速数据查询的过程。查询操作首先定位到索引树上的特定值,然后使用该值来获取对应的行指针,最后再通过行指针访问实际的数据行。

? ?MyISAM存储引擎的索引类型就是采用的非聚簇索引。其索引的结构也是B+树。最大的特点就是B+树中不再存储有效数据,而是存储的指针,该指针指向的就是数据所在的地址。下图为MyISAM存储引擎的主键索引结构,其中Col1为主键。如下:

? 当然, MySQL 除了默认会建立主键索引外,我们用户也有可能建立按照其他列信息建立的索引,一般这种索引可以叫做辅助(普通)索引。?

? MyISAM存储引擎的普通索引采用的也是B+树结构,与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。下图为MyISAM存储引擎的普通索引结构,其中Col2为索引列,和主键索引没有差:

? 有一个问题:我们知道创建索引时,本质上就是以我们所选中的列创建对用的数据结构,比如主键索引就会以主键为节点的数据,来创建B+树。但是我们也能够创建普通索引,这也就意味着一张表会存在多个索引和多个B+树。有多个B+树的同时,那么会不会每个B+树中都存储了数据信息呢?也就是会不会造成空间的浪费呢?首先,MyISAM存储引擎采用的是非聚簇索引,B+树中存储的是指针,所以并不会造成空间浪费。那InnoDB呢?我们以上表中的 Col3 建立对应的辅助索引如下图:

??可以看到,InnoDB 的非主键索引中叶子节点并没有数据,而只有对应记录的key值

所以通过辅助(普通)索引,找到目标记录,需要两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。这种过程,就叫做回表查询
??InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录,是为了节省空间,因为同一张表可能会创建多个普通索引,每个普通索引的B+树中都保存一份数据会造成数据冗余,所以通过回表查询主键索引对应的B+来获取整个数据记录,该做法本质一种以时间换取空间的做法。当根据普通索引查询数据时,其实也不一定需要进行回表查询,因为有可能我们要查询的就是这条记录对应的主键值,因此查询完普通索引对应B+树后即可完成查询。

? 我们接下来观察一下InnoDB存储引擎和MyISAM存储引擎两者所形成的文件的区别。?

? 我们现在创建一个表时,指定其存储引擎为InnoDB,我们观察一下其形成的文件。建表如下:

? 我们在 \var\lib\mysql目录下查看其形成的文件。具体如下:

? 我们再来创建一个表,该表的存储引擎为MyISAM,具体建表如下图:

? 在 \var\lib\mysql目录下形成对应的文件。具体如下:

? 通过上述的对比我们来总结一下:

  1. ?采用 InnoDB 和 MyISAM 存储引擎创建表时都会生成xxx.frm文件,该文件中存储的是表结构相关的信息。
  2. 当创建一个存储引擎为InnoDB表时,会生成一个与表名相同的.ibd文件,其中包含了数据页和索引页。这就是聚簇索引,索引和数据是存储在同一个文件中的。
  3. InnoDB是MySQL的默认存储引擎,它支持事务处理和行级锁定。InnoDB的聚簇索引是通过B+树数据结构实现的,将主键索引与实际数据存储在一起。
  4. InnoDB的聚簇索引是按照主键顺序进行存储的,这样可以提高查询效率。
  5. 当创建一个MyISAM表时,会生成与表名相同的.MYI和.MYD文件,分别存储索引和数据。这就是所谓的非聚簇索引,索引和数据是分开存储的。
  6. MyISAM是MySQL的另一个存储引擎,不支持事务处理和行级锁定。MyISAM的非聚簇索引也是通过B+树数据结构实现的,但是将索引和实际数据分开存储。

四、索引操作

4、1 索引的创建

4、1、1 主键索引的创建

? 第一种方式:在创建表的时候,直接在字段名后指定 primary key。具体如下:

? 第二种方式:在创建表的最后,指定某列或某几列为主键索引。具体如下图:

? 第三种方式:创建表以后再添加主。具体如下图:

??主键索引的特点:

  1. 一个表中,最多有一个主键索引,当然可以使复合主键。
  2. 主键索引的效率高(主键不可重复)。
  3. 创建主键索引的列,它的值不能为null,且不能重复。
  4. 主键索引的列基本上是int。

4、1、2?唯一索引的创建

??第一种方式:在表定义时,在某列后直接指定unique唯一属性。具体如下:

??第二种方式:创建表时,在表的后面指定某列或某几列为unique 。如下:

??第三种方式:创建表以后再添加唯一键。具体如下:

? 唯一索引的特点:
  1. 一个表中,可以有多个唯一索引。
  2. 查询效率高。
  3. 如果在某一列建立唯一索引,必须保证这列不能有重复数据,可以为NULL。
  4. 如果一个唯一索引上指定not null,等价于主键索引。

4、1、3?创建普通索引

? 第一种方式:在表的定义最后,指定某列为索引。具体如下图:

??

? 第二种方式:创建完表以后指定某列为普通索引。如下:

? 第三种方式:创建一个索引名为 idx_name 的索引,相当于我们可以给索引指定名称(下文会解释)。具体如下:

??普通索引的特点:

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

4、1、4?全文索引的创建

??当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求:你需要确保你的表使用的是支持全文索引的存储引擎,所以可选存储引擎是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。全文索引的创建如下:

? 上图是通过fulltext给title和body列创建全文索引。接下来我们再插入一下数据,如下图:

? 如果要查询哪些文章中包含database关键字,当然我们可以通过模糊匹配进行查找。如下:

? 但是一旦数据量过大时,最终是需要进行线性遍历的,查询速度会很慢。我们可以使用可以用explain工具看一下,是否使用到索引。如下图:

? 上图中的 key 为NULL。标示并没有用到任何索引。那么如何使用全文索引呢?使用全文索引进行查询: 一旦创建了全文索引,你可以使用MATCH AGAINST语句来执行全文搜索查询。这个语句将会返回与搜索条件匹配的记录,并根据相关度进行排序。使用模板如下:

SELECT * FROM articles WHERE MATCH(title, content) AGAINST('keyword');

? 具体使用例子如下:

? 我们再次使用explain工具看一下,具体如下:

? 综上我们来总结一下:

  • MyISAM存储引擎是支持全文索引的,而InnoDB存储引擎是在5.6以后才开始支持全文索引的。
  • 同时使用title和body建立全文索引时,相当于建立了一个复合索引,默认会选择fulltext中的第一个列名作为这个复合索引的索引名,所以这里explain中key对应的索引名为title。
  • 由于是title和body共同建立的全文索引,所以如果文章当中没有出现关键字,但文章名称中出现了关键字则也会被筛选出来。

4、2 查询索引

? 第一种方法:show keys from 表名\G。上述在创建普通索引时,我们提到可以给索引命名。那么我们看一下user10表中的索引。user10的表结构如下:

? 查询结果具体如下:

??下面是对查询结果中的属性进行解释:

  1. Table: 表示索引所属的表名。

  2. Non_unique: 表示索引是否是唯一索引。如果值为0,表示索引是唯一索引;如果值为1,表示索引不是唯一索引。注意,这里的唯一索引指的是在索引列上的值是否允许重复。

  3. Key_name: 表示索引的名称。

  4. Seq_in_index: 表示索引中列的顺序。如果一个索引由多个列构成,在该属性中会显示每个列的顺序号。

  5. Column_name: 表示索引中的列名。

  6. Collation: 表示列排序规则。例如,utf8_general_ci表示不区分大小写的Unicode字符集。

  7. Cardinality: 表示索引中唯一值的数量估计。该值用于优化查询语句,较大的值通常意味着更好的性能。

  8. Sub_part: 表示索引列的部分长度。如果整个列被索引,该值为NULL。

  9. Packed: 表示如何存储索引。如果为NULL,表示没有使用特殊的存储方式。

  10. Null: 表示索引列是否允许NULL值。如果值为YES,表示列允许为NULL;如果值为NO,则表示列不允许为NULL。

  11. Index_type: 表示索引的类型。常见的索引类型包括BTREE(B+树索引)和HASH(哈希索引)等。

  12. Comment: 提供关于索引的额外注释信息。

? 第二种方法:show index from 表名; 具体如下图:

?

? 第三种方式:desc 表名;SQL查询(信息比较简略),比如查询articles表中的索引信息。如下:

4、3 删除索引

? 第一种方法-删除主键索引: alter table 表名 drop primary key具体如下图:

? 由于主键只有一个,所以我们在删除的时候并不需要指定其列名。

??第二种方法-其他索引的删除: alter table 表名 drop index 索引名。注意:索引名就是show keys from 表名中的Key_name 字段。具体例子如下:

??第三种方法方法: drop index 索引名 on 。具体如下图:

4、4 索引创建原则

? 索引创建的原则如下:

  • 比较频繁作为查询条件的字段应该创建索引。
  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件。
  • 更新非常频繁的字段不适合创建索引。
  • 不会出现在where子句中的字段不应该创建索引。

文章来源:https://blog.csdn.net/weixin_67596609/article/details/134950866
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。