【MySQL】索引

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: 【MySQL】索引

思维导图

学习目标

      MySQL的服务器本质是在内存中的,所有数据库的CRUD操作,全部是在内存中——索引也是这样。

      提高算法效率的因素:1.组织数据的方式;2.算法本身。所以,索引是通过组织数据的方式来进行减少海量数据的检索速度,利用树的数据结构来进行优化效率。

一、没有索引,可能会有什么问题

1.1 先看一下索引是什么

我们先要创建一个海量的表,在创建完表之后,我们来进行一些操作:

  • 查询员工编号为998877的员工
select * from emp where empno = 998877;

      在未添加索引之前,我们可以看到查询耗时5.54秒,这还只是本机一个人在查询操作,在实际项目中, 假如同时有1000个人并发查询,那就有可能会死机,这是不能容忍的。

  • 创建索引
alter table emp add index(empno);

      创建索引也是需要时间的, 但是在创建完索引之后,查询所需要的时间就会大大减少。

  • 换一个员工编号,测试看看查询时间
select * from emp where empno = 123456;

1.2 索引的概念

      索引是用来提高数据库的性能,是一个物美价廉的东西。不需要添加内存,不需要修改程序,不需要调用sql,只用执行正确的create index,查询速度就可以提高成百上千倍。

      但是,没有事物是完美的:查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作(插入、更新、删除)增加了大量的IO。所以其价值在于提高一个海量数据的检索速度。

1.3 索引的分类

常见的索引分为:

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

二、认识磁盘

2.1 MySQL与存储

      MySQL给用户提供存储服务,而存储的都是数据,数据在磁盘这个外设中。

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

2.2 研究一下磁盘的构造

2.2.1 磁盘的介绍

  • 先来看一下磁盘的总体形状:

  • 再来看一下磁盘中的一个盘片:

      磁盘表面被分为许多个同心圆,每一个同心圆称为一个磁道。每一个磁道都有一个编号嘴最外面的是0磁道。

      每个磁道被划分为若干段(段又叫做扇区),每一个扇区的存储容量为512字节,每个扇区都有一个编号。

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

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

2.2.2 扇区

      存储在数据库中的数据,其实在Linux操作系统中,是以文件的形式进行存储的。数据库文件本质其实就是保存在磁盘的盘片中,也就是上图中的一个个小格子中(又称为扇区)。明显地,一个数据库文件很大,很多时,一定也需要占据多个扇区。

      我们在使用Linux,所看到的大部分目录和文件都是在硬盘中进行保存的(一些内存文件系统,如:proc,sys之类的,我们不进行考虑)。

      所以,最基本的,找到一个文件的全部,本质上就是在磁盘中找到所有保存文件的扇区。而我们能够定位到任何一个扇区,那么便可以找到所有扇区,因为查找方式是一样的。

2.2.3 定位扇区

我们想要定位扇区,就需要先定位盘面,再定位磁道,最后定位扇区。我们需要了解一下:

  • 柱面(磁道):多盘磁道,每一个盘都是双面,大小完全相等。所以同半径的磁道在整体上便构成了一个柱面。
  • 每一个盘面都有一个磁头,因此磁头和盘面的对应关系是一一对应的。

      因此,我们只需要知道:磁头(Heads)、柱面(Cylinder)、扇区(Sector)对应的编号,就可以在磁盘中定位所要访问的扇区,这种数据定位方式为CHS。但是,在实际系统软件使用的并不是CHS(但是硬件是),而是LBA(一种线性结构,可以想象为虚拟地址与物理地址)系统将LBA地址最后转化为CHS,交给磁盘去进行数据读取。

2.2.4 总结

      我们现在已经能够在硬件层面上定位任何一个基本数据块(扇区),那么在操作系统中,直接是按照扇区(512字节,部分4096字节)进行IO交互吗??答案:肯定不是的。

  • 如果操作系统直接使用硬件提供的数据大小进行交互,那么系统的IO代码就和硬件强相关; 换言之,如果系统发生变化,软件也需要发生变化。
  • 从目前来看,单次IO 512字节,还是太小了。IO单位小,意味着读取同样的数据内容,需要进行多次磁盘访问,会带来效率的降低。
  • 之前学习文件系统,就是在磁盘的基本结构下建立的,文件系统读取基本单位,就不是扇区,而是数据块。

因此:系统读取磁盘,是以块为单位进行的,基本单位是4KB。

2.3 磁盘的随机访问与连续访问

      随机访问(Random Access):本次IO所给出的扇区地址和上次IO给出的扇区地址不连续,这样的话,磁头在两次IO操作之间需要进行比较大的移动才可以重新读/写数据。

      连续访问(Sequential Access):如果当前IO操作给出的扇区地址与上次IO给出的扇区地址是连续的,那么磁头很快就可以开始本次IO,这样的多个IO操作称为连续访问。

三、MySQL与磁盘交互的基本单位

      MySQL作为一款应用软件,可以想象成一种特殊的文件系统,它有着更高的IO场景。所以,为了提高基本的IO效率,MySQL进行IO的基本单位是16KB(后面统一使用InnoDB存储引擎)。也就是说,磁盘这个硬件设备的基本单位是512字节,而InnoDB存储引擎使用16KB进行IO交互,MySQL 和磁盘进行数据交互的基本单位是 16KB。这个数据基本单位在MySQL中称为page。

MySQL、操作系统与硬盘之间的关系:

四、建立共识

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

五、索引的理解

5.1 建立测试表

// 创建一个数据表
create table if not exists user (
    id int primary key, --一定要添加主键哦,只有这样才会默认生成主键索引
    age int not null,
    name varchar(16) not null
);
 
// 插入数据
// 插入多条数据,注意,我们并不是按照主键的大小顺序进行插入的
insert into user (id, age, name) values(3, 18, '杨过');
insert into user (id, age, name) values(4, 16, '小龙女');
insert into user (id, age, name) values(2, 26, '黄蓉');
insert into user (id, age, name) values(5, 36, '郭靖');
insert into user (id, age, name) values(1, 56, '欧阳锋');

5.1.1 我们向一个具有主键的表中,乱序插入数据,我们会发现数据会自动进行排序。为什么呢???

      mysqld内部进行排序操作,只有创建的表中有主键,mysqld才会对其进行排序,使之后的查找效率更高。

      插入数据时排序的目的是:优化查询的效率。

      内部存放数据的模块,实质上也是一个链表的结构,链表的特点也就是增删快,查询修改慢,所以优化效率是必须的。

      正是因为有序,在查找的时候,从头到尾都是有效查找,没有任何一个查找是浪费的,而且如果运气好,是可以提前结束查找过程的。

5.1.2 如何理解mysql中的page的概念??

      mysql内部,一定需要并且会大量存在page,也就决定了,mysql必须要将多个同时存在的page管理起来!要管理所有的mysql内的page,我们需要进行先描述,再组织。所以,不要简单地认为page就是一个内存块,page内部也必须写入对应的管理信息!

struct page{
    struct page* next;
    string page* prev;
    char buffer[NUM];
};
// 将所有的page用一种特殊的数据结果管理起来,在 buffer pool 内部,对mysql中的page进行建模

5.2 为何IO交互要是page

      为什么MySQL和磁盘进行交互时,要采用 page 的方案进行交互呢??我们可以用多少,加载多少。

      举个例子:比如上面的 5 条记录,如果 MySQL 要查找 id = 2 的记录,第一次加载 id = 1,第二次加载 id = 2,一次加载一条记录,那么就需要加载 2 次 IO,如果要查找的是 id = 5,那么就需要 5 次 IO。但是,如果这5条记录被保存在一个 page 中(16KB,可以保存很多记录),那么第一次IO 查找 id = 2 的时候,整个 page 都会被加载到 Buffer Pool 中,这里完成了一次 IO。但是如果往后在寻找 id = 3, 4, 5等,完全不需要进行 IO,而是直接在内存中进行了。所以,在单 page中,大大减少了 IO 的次数。

      因为计算机有局部性原理,有很大可能的概率用户下次找的数据在 page 中。往往 IO 效率低下的最主要矛盾不是 IO 单次数据量的大小,而是 IO 的次数。

5.3 理解单个 Page

      在MySQL中一定有很多数据库表文件,因此,在MySQL中要管理很多数据库表文件,而要管理好这些数据库表文件,就要先描述,在组织。我们目前可以简单理解为一个个文件是有一个或者多个page构成的。

      不同的page在MySQL中的大小都是16KB,使用prev和next构成双向链表。

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

5.4 理解多个page

      通过上面的分析,我们知道,在上面的页模式中,只有一个功能:在查询某条数据的时候直接将一整页的数据加载到内存中,以减少硬盘IO的次数,从而提升性能。但是,我们也可以看到,现在的页模式内部,实际上是采用了链表的结构,前一条数据指向后一条数据,本质上还是通过数据的逐条比较来取出特定的数据。

      如果有一千万条数据,一定需要多个page来保存一千万条数据,多个page彼此使用双链表连接起来,而且每个page内部的数据也是基于链表的。那么,查找特定的一条记录,也一定是线式查找,其效率也是很低。

5.4.1 页目录

我们在生活中,想要看一本书的特定章节,我们有两种方式:

  • 从头到尾逐页向后翻,直到找到目标内容
  • 通过书本提供的目录,发现想要找的那个章节在235页,那么我们便可以直接翻到235页。同时,查找目录的方案,可以顺序找,不过因为目录数量少,所以可以快速提高定位。

      在生活中,会发现基本上每一个书都有其目录,我们读者可以通过该目录很快的找到了我们想要看的那一页,不需要我们进行一页一页的寻找。而上述链表结构就是需要我们一一遍历寻找。所以,在mysql中的数据也有其对应的目录,为了提高搜索效率。

  • 本质上,书中的目录是多花了纸张,但是却提高了效率
  • 因此,目录是一种“空间换时间的做法”。

5.4.2 单页情况

      针对单页的page,我们是否也可以引入目录呢?当然可以:

      如上图所示,在一个page内部,我们引入了目录。比如,我们要查找的 id = 4 记录,按照之前的线性查找,需要进行线性遍历4次,才可以拿到结果。现在通过目录2可以直接定位到所要查找的记录,提高了效率。

5.4.3 多页情况

      MySQL中每一页的大小只有16KB,单个page大小固定,所以随着数据量的不断增大,16KB不可能存下所有的数据,必定会有多个也来存储数据。

      在单表数据不断被插入的情况下,MySQL会在容量不足的时候,自动开辟新的page来保存新的数据,然后通过指针的方式,将所有的page组织起来。

      这样,我们可以通过多个page的遍历,page内部通过目录来快速定位数据。可是,这样也会有效率问题,在page之间,也是需要mysql遍历的,遍历意味着依旧需要进行大量的IO操作,将下一个page加载到内存,进行线性检测,这样显得我们之前的page内部的目录有点无用。

我们可以通过一些额外的空间建立目录来优化查找速度:

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

      其中,目录页的本质是用户数据,而目录中的数据存放的就是指向的那一页的最小的数据。有数据,就可以通过该访问,进而通过指针找到下一个page。

      其实目录页的本质也是页,普通页中存的数据是用户数据,而目录页中存的数据是普通页的地址。

5.4.4 复盘一下

  • page分为目录页和数据页,目录页只放各个下级page的最小键值
  • 查找的时候,自定向下找,只要加载部分电量到内存,即可完成算法的整个查询过程,大大减少; IO次数。

5.4.5 Inno_DB 在建立索引结果来管理数据的时候,我们可以使用其他数据结构吗??

  • 链表——线性遍历
  • 二叉搜索树——这个结构可能会发生退化,退化为线性结构
  • AVL && 红黑树——虽然是平衡或者是近似平衡,但是毕竟是二叉结构,相比较多阶B+树,意味着树的整体过高,大家都是自顶向下找,层高越低,意味着系统与硬盘之间的IO交互的次数会更少
  • Hash——官方的索引实现方式中,MySQL中是支持Hash,但是在InnoDB和MyIASM中并不支持Hash,Hash根据其算法特性,决定了虽然有时候速度是很快的(O(1)),不过,在面对范围查找的时候就明显不行,另外还有其他差别。

5.4.5.1 B树和B+树的对比

目前这两个树的区别是:

  • B树的节点上既有数据,又有page指针,而B+树中,只有叶子节点有数据,其他目录页中,只有键值和page指针
  • B+树中的叶子节点是全部相连的,而B树中的叶子节点是不相连的

为什么MySQL中的索引结构选择B+树??

  • B+树的节点不存放数据,不会占用一些空间,这样一个节点是可以存放更多的键值的,可以使得树更矮,IO次数就会变少
  • B+树的叶子节点是相连的,更便于进行范围查找(重要的)
5.4.5.2 聚簇索引和非聚簇索引

      MyIASM引擎同样使用B+树作为索引结果,叶结点的data域存放的是数据记录的地址,简单的说就是将数据和索引是分来进行存储的。而InnoDB引擎中的叶节点是将数据和索引一样存储的。其中,MyIASM最大的特点是:将索引page和数据page进行分离,也就是叶子结点没有数据,只有对应数据的地址(如下图所示)。

      在引擎中,我们除了主键索引,我们还可以建立其他索引。

      在InnoDB中,如果建立普通索引,在非主键索引的叶子节点中并没有数据,而只有对应记录的Key值。所以通过辅助(普通)索引,找到目标记录,需要两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录,这种过程就叫做回表查询。

      为什么InnoDB针对这种辅助(普通)索引的场景,不给叶子结点附上数据呢?原因就是太浪费空间。

六、索引操作

6.1 创建索引

6.1.1 创建主键索引

  • 第一种方式
// 在创建表的时候,直接在字段名后指定: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);

主键索引的特点:

  • 一个表中,最多有一个主键索引,当然也可以是复合主键
  • 主键索引的效率高(主键不可重复)
  • 创建主键索引的列,它的值不能为NULL,且不能重复
  • 主键索引的列基本上是int

6.1.2 创建唯一键索引

  • 第一种方式
// 在表定义时,在某列后直接指定unique唯一属性
create table user4(
    id int primary key,
    name varchar(30) unique
);
  • 第二种方式
// 创建表时,在表的后面指定某列或者某几列为unique
create table user5(
    id int primary key,
    name varchar(30),
    unique(name)
);
  • 第三种方式
create table user6(
    id int primary key,
    name varchar(30)
);
 
alter table user6 add unique(name);

唯一索引的特点:

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

6.1.3 创建普通索引

  • 第一种方式
create table user8(
    id int primary key,
    name varchar(30),
    index(name)  // 在表的定义最后,指定某列为索引
);
  • 第二种方式
create table user10(
    id int primary key,
    name varchar(30)
);
alter table user10 add index(name); 
  • 第三种方式
create table user11(
    id int primary key,
    name varchar(30)
);
create index idx_name on user11(name);

普通索引的特点:

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

6.1.4 创建全文索引(不重要)

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

  • 查询有没有database数据

如果使用如下查询方式,虽然查询出数据,但是没有使用到全文索引:

  • 如何使用全文索引?
select * from articals
>> where match (title, body) against ('database');

我们可以通过explain来分析这个sql语句,来看一下是否使用到索引:

                           

6.2 查询索引

  • 第一种方式
show keys from 表名\G
  • 第二种方式
show index from 表名
  • 第三种方式
desc 表名

6.3 删除索引

  • 第一种方式——删除主键索引
alter table 表名 drop primary key;
  • 第二种方式——删除其他索引
alter table 表名 drop index 索引名;  索引名就是show index from 表名中的key_name字段
  • 第三种方式
drop index 索引名 on 表名;

6.4 索引的创建原则

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

6.5 其他概念

  • 复合索引
  • 索引最左匹配原则
  • 索引覆盖
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
缓存 关系型数据库 MySQL
MySQL索引策略与查询性能调优实战
在实际应用中,需要根据具体的业务需求和查询模式,综合运用索引策略和查询性能调优方法,不断地测试和优化,以提高MySQL数据库的查询性能。
266 66
|
1月前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
238 9
|
3月前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
26天前
|
SQL 存储 关系型数据库
MySQL秘籍之索引与查询优化实战指南
最左前缀原则。不冗余原则。最大选择性原则。所谓前缀索引,说白了就是对文本的前几个字符建立索引(具体是几个字符在建立索引时去指定),比如以产品名称的前 10 位来建索引,这样建立起来的索引更小,查询效率更快!
93 22
 MySQL秘籍之索引与查询优化实战指南
|
6天前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
|
9天前
|
存储 关系型数据库 MySQL
浅入浅出——MySQL索引
本文介绍了数据库索引的概念和各种索引结构,如哈希表、B+树、InnoDB引擎的索引运作原理等。还分享了覆盖索引、联合索引、最左前缀原则等优化技巧,以及如何避免索引误用,提高数据库性能。
|
27天前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
93 10
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
86 18
|
1月前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
65 8
|
1月前
|
缓存 关系型数据库 MySQL
MySQL 索引优化以及慢查询优化
通过本文的介绍,希望您能够深入理解MySQL索引优化和慢查询优化的方法,并在实际应用中灵活运用这些技术,提升数据库的整体性能。
88 7

热门文章

最新文章