0


【MySQL系列】索引的学习及理解

「前言」文章内容大致是MySQL索引的学习。

「归属专栏」MySQL

「主页链接」个人主页

「笔者」枫叶先生(fy)

MySQL

目录

一、索引概念

  • 如果没有索引,那么在查询数据时是直接一条条遍历表中的数据,那么查询的时间复杂度将会是O(N)
  • 如果数据库表有索引,就能提高海量数据的检索速度,就能大大提高查找的效率
  • 索引概念:索引是指对数据库中的数据进行结构化的组织和管理,以提高数据的检索效率。
  • 索引的本质就是一种数据结构

MySQL是一款网络服务器,MySQL服务端是一直在内存中,MySQL客户端的

CURD

操作都会交给MySQL的服务端完成,即MySQL的所有

CURD

操作都是在内存中进行的,即索引也是在内存中的进行的。

比如,假设数据库组织数据的方式是线性的,查询的时间复杂度将会是

O(N)

;如果数据库组织数据的方式是以二叉树的,那么查询的时间复杂度将会大大降低。

所以,索引虽然提高了数据的查询速度,但在一定程度上也会降低数据增删改的效率,因为这时在对表中的数据进行增删改操作时,除了需要进行对应的增删改操作之外,可能还需要对底层建立的数据结构进行调整维护,维护结构是需要成本的。

即查询速度的提高是以插入、更新、删除的速度为代价的

常见的索引分为:

  • 主键索引(primary key)
  • 唯一索引(unique)
  • 普通索引(index)
  • 全文索引(fulltext)

先见一见索引

使用如下SQL创建一个海量数据表

dropdatabaseifexists`index_demon`;createdatabaseifnotexists`index_demon`defaultcharacterset utf8;use`index_demon`;-- 构建一个8000000条记录的数据-- 构建的海量表数据需要有差异性,所以使用存储过程来创建-- 产生随机字符串delimiter $$
createfunction rand_string(n INT)returnsvarchar(255)begindeclare chars_str varchar(100)default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255)default'';declare i intdefault0;while i < n doset return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));set i = i +1;endwhile;return return_str;end $$
delimiter;-- 产生随机数字delimiter $$
createfunction rand_num()returnsint(5)begindeclare i intdefault0;set i = floor(10+rand()*500);return i;end $$
delimiter;-- 创建存储过程,向雇员表添加海量数据delimiter $$
createprocedure insert_emp(instartint(10),in max_num int(10))begindeclare i intdefault0;set autocommit =0;repeatset i = i +1;insertinto EMP values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
endrepeat;commit;end $$
delimiter;-- 雇员表CREATETABLE`EMP`(`empno`int(6)unsigned zerofill NOTNULLCOMMENT'雇员编号',`ename`varchar(10)DEFAULTNULLCOMMENT'雇员姓名',`job`varchar(9)DEFAULTNULLCOMMENT'雇员职位',`mgr`int(4)unsigned zerofill DEFAULTNULLCOMMENT'雇员领导编号',`hiredate`datetimeDEFAULTNULLCOMMENT'雇佣时间',`sal`decimal(7,2)DEFAULTNULLCOMMENT'工资月薪',`comm`decimal(7,2)DEFAULTNULLCOMMENT'奖金',`deptno`int(2)unsigned zerofill DEFAULTNULLCOMMENT'部门编号');-- 执行存储过程,添加8000000条记录call insert_emp(100001,8000000);

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

将上述的SQL语句保存在一个文件中,然后MySQL中使用

source

命令执行文件中的SQL
在这里插入图片描述
等待SQL语句执行完成(时间有点久)
在这里插入图片描述
查看数据库就能看到一个名为index_demon的数据库
在这里插入图片描述
在查看EMP表中的数据时可以带上limit子句(数据量太大)
在这里插入图片描述
查询EMP表中指定工号的员工信息(该表没有索引),每次查询都要花费几秒进行查询(在实际项目中,如果放在公网中,假如同时有1000个人并发查询,那很可能就死机)
在这里插入图片描述
下面给该表创建索引
在这里插入图片描述
再查询EMP表中指定工号的员工信息,发现查询的效率大大提高了
在这里插入图片描述
根据实验结果,发现索引能大大提高查询数据的效率,这就是索引的价值所在。

二、从硬件角度理解

2.1 磁盘

  • MySQL给用户提供存储服务,存储的数据在磁盘这个外设当中
  • 磁盘是计算机中的一个机械设备,相比于计算机的其他电子元件,磁盘的效率是比较低的
  • 而如何提高效率是MySQL的一个重要话题,因此我们有必要了解一下磁盘的相关内容

磁盘在该文章有详细介绍,文章链接:点击传送,这里就简单说一下概念

磁盘的整体结构如下:(物理结构)

在这里插入图片描述

磁盘的存储结构

在这里插入图片描述

  • 磁道(Tracker):磁盘表面被分为许多同心圆,每个同心圆称为一个磁道,每个磁道都有自己的编号
  • 柱面(Cylinder):多个磁盘的同一个磁道重叠起来叫做磁柱
  • 磁面(Head):一个盘面,每个盘面都有自己的编号
  • 扇区(Sector):磁道的一个扇形区,每个扇区都有自己的编号

磁盘的基本读写单位是扇区,扇区大小一般是 512字节(512byte) ,每个盘片被分为若干个同心圆,每一个同心圆就是一个磁道,而每个磁道被划分为若干个扇区,每个扇区的大小都是 512字节

注意:近三十年来,扇区大小一直是512字节,但最近几年正在迁移到更大、更高效的4096字节扇区,通常称为

4K

扇区

我们在使用Linux,所看到的大部分目录或者文件,其实就是保存在硬盘当中的。(当然,有一些内存文件系统,如:

proc , sys

之类,我们不考虑)

比如:数据库文件,本质其实就是保存在磁盘的盘片当中,就是一个一个的文件

在该目录下存储着mysql的文件

ls /var/lib/mysql

在这里插入图片描述
新建一个数据库,本质上就是在该目录下创建一个目录(图中颜色没有高亮,普通用户查看)
在这里插入图片描述
所以,最基本的,找到一个文件的全部,本质,就是在磁盘找到所有保存文件的扇区

而我们能够定位任何一个扇区,那么便能找到所有扇区,因为查找方式是一样的

扇区的定位方式

定位扇区时采用

CHS

寻址方式

注:通过 柱面

Cylinder

—— 磁头

Head

—— 扇区

Sector

进行寻址,这种寻址方法为

CHS

寻址

说明一下:

  • CHS寻址方式是磁盘定位扇区的方式,但实际CHS寻址方式对磁盘以外的设备来说没什么作用,因此系统软件在定位磁盘上的数据时采用的是LBA(LogicalBlock Address,逻辑区块地址)
  • LBA是描述计算机存储设备上数据所在区块的通用机制,LBA和CHS之间可以通过计算公式进行相互转换,LBA存在的意义就是对底层逻辑器件进行虚拟化,让系统软件可以不用关心底层硬件具体的寻址方式,而实际底层硬件采用的还是CHS寻址方式

注:以上在磁盘篇章有详细介绍,这里就不再谈论。

2.2 结论

操作系统与磁盘交互的基本单位

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

  • 操作系统与磁盘进行IO交互时,如果直接以扇区的大小作为IO的基本单位,那么这时系统的IO代码和硬件就是强相关的,将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码
  • 此外,以扇区的大小作为IO的基本单位太小了,这就意味着读取同样的数据内容,需要进行更多次的磁盘访问,而磁盘的效率是比较低的,这样IO效率就降低了
  • 即以下两个点:
  • 不想让 OS 的代码和硬件强耦合,实现硬件和系统的解耦
  • 操作系统与磁盘以4KB作为IO交互的基本单位,是为了提高IO效率(局部性原理)

磁盘随机访问(

Random Access

)与连续访问(

Sequential Access

)

  • 随机访问:本次IO所给出的扇区地址和上次IO给出扇区地址不连续,这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
  • 连续访问:如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的,那磁头就能很快的开始这次IO操作,这样的多个IO操作称为连续访问

因此尽管相邻的两次IO操作在同一时刻发出,但如果它们的请求的扇区地址相差很大的话也只能称为随机访问,而非连续访问

连续访问中的连续指的是访问的扇区地址的连续,而不是访问时间的连续,由于连续访问不需要过多的定位,因此效率比较高

三、从软件角度理解

MySQL与磁盘交互的基本单位

MySQL作为一款应用软件,可以想象成是一种特殊的文件系统,它有着更高频的IO场景,因此为了提高基本的IO效率,MySQL与磁盘交互的基本单位是

16KB

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

mysql>showglobalstatuslike'innodb_page_size';

在这里插入图片描述
注:后面统一使用

InnoDB

存储引擎进行讲解

也就是说,磁盘这个硬件设备的基本单位是512字节,而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即, MySQL 和磁盘进行数据交互的基本单位是

16KB

。这个基本数据单元,在 MySQL 这里叫做

page

(注意和系统的page区分)

Buffer Pool

  • 实际上MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存,这块内存空间叫做Buffer Pool,后续磁盘中加载的数据就会保存在Buffer Pool中,刷新数据时也就是将Buffer Pool中的数据刷新到磁盘
  • 然而,OS是有内核缓冲区的。
  • 因此MySQL从磁盘读取数据时,需要先将数据从磁盘读取到内核缓冲区,再将数据从内核缓冲区读取到Buffer Pool,MySQL将数据刷新到磁盘时,同样需要先将数据从Buffer Pool刷新到内核缓冲区,再将数据从内核缓冲区刷新到磁盘。

在这里插入图片描述

四、共识

为了方面下面理解,我们需要有以下共识:

  • MySQL 中的数据文件,是以page为单位保存在磁盘当中的(MySQL的page)
  • MySQL 的 CURD 操作,都需要通过计算,找到对应的插入位置,或者找到对应要修改或者查询的数据。而只要涉及计算,就需要CPU参与,而为了便于CPU参与,一定要能够先将数据移动到内存当中
  • 所以在特定时间内,数据一定是磁盘中有,内存中也有。后续操作完内存数据之后,以特定的刷新策略,刷新到磁盘。
  • 而这时,就涉及到磁盘和内存的数据交互,也就是IO了,而此时IO的基本单位就是Page
  • 为了更好的进行上面的操作, MySQL 服务器在内存中运行的时候,在服务器内部,就申请了被称为 Buffer Pool 的的大内存空间,来进行各种缓存。其实就是很大的内存空间,来和磁盘数据进行IO交互。
  • 为了更高的效率,一定要尽可能的减少系统和磁盘IO的次数

五、索引的理解

5.1 一个现象和结论

建立一个测试表,表当中包含用户的id、年龄和姓名,并将用户的id设置成主键

createtableifnotexistsuser(
id intprimarykey,-- 一定要添加主键,只有这样才会默认生成主键索引
age intnotnull,
name varchar(16)notnull);

在这里插入图片描述
在这里插入图片描述
然后插入多条记录,并且插入数据时没有按照主键的大小顺序插入,即乱序插入

insertintouser(id, age, name)values(3,18,'张三');insertintouser(id, age, name)values(4,16,'李四');insertintouser(id, age, name)values(2,26,'王五');insertintouser(id, age, name)values(5,36,'赵六');insertintouser(id, age, name)values(1,56,'田七');

插入完成后,查看表中的数据时,却发现显示出来的数据是按照主键进行有序排列的
在这里插入图片描述
引入主键之后,插入的数据自动按照主键排序这个排序工作是谁做的,为什么要这么做?

  • 这个排序工作是MySQL自己做的,进行排序的目的是为了方便引入目录(下面谈)

重谈MySQL的page,MySQL与磁盘进行交互时为什么不是按需交互,而是以Page为基本单位进行交互(16KB)?

  • 比如上面的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效率低下的最主要矛盾不是IO单次数据量的大小,而是IO的次数导致IO效率低下

怎么保证,用户一定下次找的数据,就在这个Page里面?

  • 这个是无法保障的,但是用户要找的数据会有很大的概率会在这个page里面(局部性原理)
  • 局部性原理:当一个数据正在被访问时,那么下一次有很大可能会访问其周围的数据
  • 如果局部性原理没有起作用,那就再把对应的Page加载到内存当中即可

综上,MySQL与磁盘进行交互时以Page为基本单位,可以减少与磁盘IO交互的次数,进而提高IO的效率

5.2 对Page进行建模

  • MySQL中要管理很多数据文件,在运行期间一定有大量的Page需要被换入换出,因此MySQL一定需要将内存中大量的Page管理起来
  • 要管理MySQL内部的所有的Page,就需要对Page进行先描述,再组织
  • 所以,不能简单认为Page就是一个内存块,Page内部也必须写入对应的管理信息

对Page进行建模如下:

structpage{structpage*next;structpage*prev;char buffer[NUM];}// 一个Page大小16KB

不同的Page ,在 MySQL 中,都是16KB ,都是使用prev和next指针构成双向链表,从而用链表的形式对Page进行了管理

假设测试表中的记录都在同一个Page当中,那么该Page的结构大致如下:
在这里插入图片描述

  • 每个Page结构体内部的数据会按照主键进行排序,从上面的Page内数据记录可以看出,数据是有序且彼此关联的。目的是为了优化数据查询的效率
  • 只要设置了主键,即便向表中插入的数据是乱序的,MySQL底层也会自动按照主键对插入的数据进行排序(创建了主键便是使用了索引,后序谈)

单个Page内创建页内目录(引入页目录)

  • Page结构体内部存储的数据记录是以单链表的形式组织起来的。当页内部的数据量增多时,如果直接进行线性遍历,会导致效率低下。为了提高查询效率,可以在Page结构体内部引入页内目录
  • 页内目录将Page结构体内部存储的数据记录按照主键划分为若干区域,并存储了这些区域的最小键值。在查询数据时,可以先通过页内目录找到目标数据所在区域的起始记录,然后再从该记录开始向后遍历,找到目标记录。
  • 通过引入页内目录,可以减少不必要的线性遍历,提高查询效率

在这里插入图片描述

什么是页目录

比如我们在看《谭浩强C程序设计》这本书的时候,如果我们要看<指针章节>,找到该章节有两种做法:

  • 第一种:从头逐页的向后翻,直到找到目标内容
  • 第二种:通过书提供的目录,发现指针章节在234页(假设),那么我们便直接翻到234页。同时,查找目录的方案,可以顺序找,不过因为目录肯定少,所以可以快速提高定位
  • 本质上,书中的目录,是多花了纸张的,但是却提高了效率
  • 所以,目录,是一种“空间换时间的做法
  • 每个Page结构体内部的数据会按照主键进行排序,其实就是为了引入页内目录,因为只有数据按照主键排序后引入页内目录才有意义,就像书中每一页都是按照页码进行排序的一样,如果一本书的页码是乱序的,那么它的目录根本就没有意义

上面是理解单个Page,下面理解多个Page

多个Page

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

在这里插入图片描述

Page之上创建页目录

  • 随着Page的增多,遍历Page数量太大(本质也是线性遍历),也会导致查找效率降低
  • 这时可以给各个Page结构体也建立页目录
  • 页目录中的每个目录项都指向一个Page,而这个目录项存放的就是其指向的Page中存放的最小数据的键值
  • 其中,每个目录项的构成是:键值+指针,该目录不存储数据,只存放页目录
  • 着数据量不断增大,Page变得越来越多,这时一个页目录无法管理所有的Page,这时就需要更多个的页目录

在这里插入图片描述
注意:目录页的本质也是页,普通页中存的数据是用户数据,而目录页中存的数据是普通页的地址

页目录之上再创建页目录

  • 随着数据量不断增大,我们可以不断在页目录之上再创建页目录,最终就一定能够得到一个入口页目录
  • 这时在查询数据时就可以从入口页目录开始不断查询页目录,最终找到目标数据所在的Page,然后再在该Page内部找到目标数据

在这里插入图片描述

  • 这个矮胖的树,实际就是一棵B+树,这棵B+树就是InnoDB的索引结构(存储结构)
  • 查找的时候,自定向下找,只需要加载部分目录页到内存,即可完成算法的整个查找过程,大大减少了IO次数

B+树中的Page结点无需全部加载到Buffer Pool中:

  • 当对MySQL中的某张表进行增删查改操作时,不需要将其对应B+树的所有结点全量加入到Buffer Pool中,甚至在刚开始时只需要将B+树的根结点加入到Buffer Pool中
  • 当后续访问表中的数据时,再将该数据对应路径上的结点加入到Buffer Pool中即可,对于其他不需要的结点根本不用加入到Buffer Pool中,这一点和操作系统中的页表是很像

5.3 索引可以采用的数据结构

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

常见的存储引擎,与其所支持的索引类型:
在这里插入图片描述
注:这里的BTREE指的是B+树

B树 VS B+树

B+树是B树的一种变形结构

  • B树中的所有结点中都同时包括索引信息和数据信息,由于一个Page的大小是固定的,因此非叶子结点中如果包含了数据信息,那么这些结点中能够存储的索引信息一定会变少,这时这棵树形结构一定会变得更高更瘦,当查询数据时就可能需要与磁盘进行更多次的IO操作(IO效率降低)
  • B树中的各个叶子结点之间没有连接起来,这将不利于进行数据的范围查找

B树结构如下:
在这里插入图片描述

  • B+树的每个节点只包含键值,所有的数据记录都存储在叶子节点中,并且叶子节点之间通过指针连接成一个有序链表(范围查询非常高效)
  • 节点不存储data,这样一个节点就可以存储更多的key,可以使得树更矮,所以IO操作次数更少

B+树结构如下:

在这里插入图片描述

5.4 聚簇索引和非聚簇索引

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

  • InnoDB存储引擎采用B+树作为索引的基本数据结构
  • MyISAM存储引擎同样采用B+树作为索引的基本数据结构
  • 与InnoDB存储引擎的B+树不同的是,MyISAM存储引擎的B+树的叶子结点存放的不是数据记录,而是数据记录对应的地址
  • 非聚簇索引:像MyISAM存储引擎这种,将数据记录与索引结构分离的索引方案,叫做非聚簇索引

注:图中Col1列为主键
在这里插入图片描述

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

  • InnoDB是将索引和数据放在一起的
  • 聚簇索引:像InnoDB存储引擎这种,将数据记录与索引结构放在一起的索引方案,叫做聚簇索引

聚簇索引 VS 非聚簇索引(实验)

创建一个是

InnoDB

为索引的表

mysql>createtableifnotexists test1(-> id intunsignedauto_incrementprimarykey,-> name varchar(20)->)engine=InnoDB;

进入该路径,找到自己所在的数据库

ls /var/lib/mysql -l

在这里插入图片描述
进入自己所在的数据库

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

  • xxx.frm文件,该文件中存储的是表结构相关的信息
  • 采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件,该文件中存储的是索引和数据相关的信息,这就是所谓的聚簇索引,索引和数据是存储在同一个文件中的

在这里插入图片描述
再创建一个test2的表,使用

MyISAM

存储引擎创建表

mysql>createtableifnotexists test1(-> id intunsignedauto_incrementprimarykey,-> name varchar(20)->)engine= MyISAM;

在这里插入图片描述
当采用MyISAM存储引擎创建表时,在数据库对应的目录下会新增三个文件

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

在这里插入图片描述

普通索引

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

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

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

对于 MyISAM,建立辅助(普通)索引和主键索引没有差别,无非就是主键不能重复,而非主键可重复,如下图:
在这里插入图片描述

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

同样, InnoDB除了主键索引,用户也会建立辅助(普通)索引

  • InnoDB存储引擎的普通索引采用的也是B+树结构,但普通索引的B+树中的键值可以重复,并且B+树的叶子结点中存储的不是数据记录,而是对应数据记录的主键值。
  • 当根据普通索引查询数据时,会先查找普通索引对应的B+树找到目标记录的主键值,然后再查找主键索引对应的B+树找到目标记录,这个过程就叫做回表查询
  • InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录,是为了节省空间,因为同一张表可能会创建多个普通索引,每个普通索引的B+树中都保存一份数据会造成数据冗余,所以通过回表查询主键索引对应的B+来获取整个数据记录

以上表中的 Col3 建立对应的辅助索引如下图:
在这里插入图片描述
注意:采用InnoDB存储引擎建立的每张表都会有一个主键,就算用户没有设置,InnoDB也会自动帮你创建一个不可见的主键

六、索引操作

6.1 创建主键索引

创建主键索引方式一

在创建表的时候,直接在字段名后指定

primary key
mysql>createtableifnotexists user1(-> id intprimarykey,-> name varchar(20)->);

在这里插入图片描述

创建主键索引方式二

在创建表的最后,指定某列或某几列为主键索引

mysql>createtableifnotexists user2(-> id int,-> name varchar(20),->primarykey(id)->);

在这里插入图片描述

创建主键索引方式三

创建表以后再添加主键

altertable user3 addprimarykey(id);

在这里插入图片描述

主键索引的特点:

  • 一个表中,最多有一个主键索引,当然可以使符合主键
  • 主键索引的效率高(主键不可重复)
  • 创建主键索引的列,它的值不能为null,且不能重复
  • 主键索引的列基本是整型类型

6.2 唯一索引的创建

唯一索引的创建方式一

在表定义时,在某列后直接指定

unique

唯一属性

mysql>createtable user4(-> id intprimarykey,-> name varchar(20)unique->);

在这里插入图片描述

唯一索引的创建方式二

创建表时,在表的后面指定某列或某几列为

unique
mysql>createtable user5(-> id intprimarykey,-> name varchar(20),->unique(name)->);

在这里插入图片描述

唯一索引的创建方式三

创建表以后再添加唯一键

altertable user6 addunique(name);

在这里插入图片描述

唯一索引的特点

  • 一个表中,可以有多个唯一索引
  • 查询效率高
  • 如果在某一列建立唯一索引,必须保证这列不能有重复数据
  • 如果一个唯一索引上指定not null,等价于主键索引

6.3 普通索引的创建

普通索引的创建方式一

在表的定义最后,指定某列为索引

createtable user7(id intprimarykey,
name varchar(20),
email varchar(30),index(name));

在这里插入图片描述

普通索引的创建方式二

创建完表以后指定某列为普通索引

altertable user8 addindex(name);

在这里插入图片描述

普通索引的创建方式三

创建表后,使用

create

命令给指定字段创建普通索引,并指定索引名

-- 创建一个索引名为 idx_name 的索引createindex idx_name on user9(name);

在这里插入图片描述

普通索引的特点

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

6.4 查询索引

查询索引方式一

语法:

showkeysfrom 表名;

例如,查询user1表
在这里插入图片描述
部分说明:

  • Table: 表示创建索引的表的名称
  • Non_unique: 表示该索引是否是唯一索引,如果是则为0,如果不是则为1
  • Key_name: 表示索引的名称(索引也有名字)
  • Column_name: 表示定义索引的列字段
  • Index_type: 显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE

第二种方法

语法:

showindexfrom 表名;

比如查询user4的索引,有两行说明有两个索引
在这里插入图片描述

第三种方法

语法:(查出的信息比较简略)

desc 表名;

比如查询user7表的索引
在这里插入图片描述

6.5 删除索引

删除主键索引

语法:

altertable 表名 dropprimarykey;

例如,删除user1的主键索引
在这里插入图片描述

删除非主键索引

语法:

altertable 表名 dropindex 索引名;

注:索引名就是

show keys from 表名

中的

Key_name

字段

例如,删除user4表的唯一键索引
在这里插入图片描述

第三种方法

该语法可以删除指定的非主键索引:

dropindex 索引名 on 表名;

6.6 全文索引的创建

  • 当对文章字段或有大量文字的字段进行检索时,会使用到全文索引
  • MySQL提供全文索引机制,但是有要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文
  • 如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)

全文索引案例

创建一个文章表,表当中包含文章的id、文章名称、文章内容,并在创建表的最后通过fulltext给title和body列创建全文索引

CREATETABLE articles (
id INTUNSIGNEDAUTO_INCREMENTNOTNULLPRIMARYKEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)-- 全文索引)engine=MyISAM;

在这里插入图片描述
插入数据

INSERTINTO articles (title,body)VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');

在这里插入图片描述
如果要查询哪些文章中包含database关键字,我们可以通过模糊匹配进行查找(没有使用到全文索引)

select*from articles where body like'%database%';

在这里插入图片描述
可以用

explain

工具看一下,是否使用到索引

在SQL语句前面加上explain,可以看到key对应的值为NULL,表示这条SQL在执行过程中没有用到任何索引
在这里插入图片描述
如果要通过全文索引来查询,需要使用

match against

进行搜

SELECT*FROM articles WHEREMATCH(title,body) AGAINST ('database');

在这里插入图片描述
在这条SQL语句前面加上explain,可以看到key对应的值为title,表示这条SQL在执行过程中用到了索引名为

title

的索引
在这里插入图片描述
注:MyISAM存储引擎是支持全文索引的,而InnoDB存储引擎是在5.6以后才开始支持全文索引的

6.7 索引创建原则

索引创建的原则如下:

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

--------------------- END ----------------------

「 作者 」 枫叶先生
「 更新 」 2023.9.1
「 声明 」 余之才疏学浅,故所撰文疏漏难免,
          或有谬误或不准确之处,敬请读者批评指正。
标签: mysql 学习 adb

本文转载自: https://blog.csdn.net/m0_64280701/article/details/132516434
版权归原作者 枫叶先生 所有, 如有侵权,请联系我们删除。

“【MySQL系列】索引的学习及理解”的评论:

还没有评论