绪论​

每日激励：“不设限和自我肯定的心态：I can do all things。 — Stephen Curry”

绪论​：
本章是MySQL中进阶的部分，通过了解索引知道MySQL底层到底是如何存储数据的，以及MySQL底层中的结构到底是啥，本章主要具体的讲到了索引、MySQL数据如何存储page、存储引擎的结构B+树任何管理page数据、索引的操作（本质其实是主键的操作），最终相信通过本章索引的理解你会对MySQL数据库存储有很大的提升，下期将更新MySQL另一大知识点事务，敬请期待~
————————
若有看不懂如何登录Linux中的MySQL来执行具体代码的或者不知道如何创建表等一些列MySQL基础的的请看—》MySQL专栏(文章平均94分)
——————————
早关注不迷路，话不多说安全带系好，发车啦（建议电脑观看）。

---

1. 什么是索引？没有索引可能会有什么问题？

索引：提高数据库的性能，索引是物美价廉的东西了。

不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度

这些操作全部是在mysql的内存中进行的，然后定期刷新到外设硬盘中mysql的服务器，本质是在内存中的，所有的数据库的CURD操作，全部是在内存中进行的！索引也是如此！

实验海量表中没有索引时有什么问题

先整一个海量表，在查询的时候，看看没有索引时有什么问题？
index.data.sql：

--构建一个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);

注意对于这些海量数据的表中，我们一定要使用limit限制。

查询海量数据中的empno=998877，并且因为数据非常海量所以查询数据也会偏久（花费5s），这在项目中是不太允许存在的：

---

那么就可以使用索引了（先了解下后面细讲）：

---

创建索引：
alter table 表 add index(字段名);

alter table EMP add index(empno);

当添加索引后再次查询（发现效率就能提高非常多！）：

这就是索引带来的好处！

2. 硬件理解（可略过，不过看了你会对IO有不一样的理解）

认识磁盘

（磁盘大致主结构如下）

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题！

扇区

数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。
题外话（回顾操作系统中的文件）：

从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大
那么，所有扇区都是默认512字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。
不过最新的磁盘技术，已经慢慢的让扇区大小不同了，不过我们现在暂时不考虑。
我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文件系统，如： proc ， sys 之类，我们不考虑)

那么其中在Linux上的mysql的表本质也是一个个在Linux上的文件，所以说他本质也会访问文件也就是访问底层磁盘扇区获取数据
（磁盘的纵向图，观察扇区的）

柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面

• 每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的

• 所以，我们只需要知道，磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这磁盘数据定位方式叫做 CHS 。不过实际系统软件使用的并不是 CHS （但是硬件是），而是LBA，一种线性地址，可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS，交给磁盘去进行数据读取。不过，我们现在不关心转化细节，知道这个东西，让我们逻辑自洽起来即可。

结论：
我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，就直接按照扇区(512字节，部分4096字节)，进行IO交互吗？
不是

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

故：系统读取磁盘，是以块为单位的，基本单位是 4KB 。

磁盘随机访问(Random Access)与连续访问(Sequential Access)

1. 随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。
2. 连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问。
   因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。

磁盘是通过机械运动进行寻址的，随机访问不需要过多的定位，故效率比较高。

---

通过了解了底层硬件，再来看上层软件

---

3. MySQL 与磁盘交互基本单位

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB（而非文件4KB的） (后面统一使用 InnoDB 存储引擎讲解)
（软硬件逻辑交互图：）

查看innodb存储引擎的数据块大小sql：

show global status like 'innodb_page_size'

也就是说，磁盘这个硬件设备的基本单位是 512 字节（或4096），而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即， MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）

4. MySQL底层数据存储

首先要了解MySQL底层本质也是通过OS和硬盘进行IO交互的，所以本质也就衍生出许多规则：

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

---

了解了怎么多MySQL与底层的关系，其实本质就是为了引出一点：
MySQL和文件一样在底层磁盘中的存储都是以page块为单位的

那么话不多说，我就开始真正索引的理解

---

5. 索引的理解

1. 创建表并插入数据：
2. 发现我们向一个具有 主键的表中，乱序插入数据，发现数据会自动排序，这是谁做的？为什么做？（这后面会细说原因）
3. 如何理解mysql中的page？
   1. 其中mysql的 buffer pool 128Mb，所以在mysql内部，一定需要并且会存在大量的page存在buffer pool中
   2. 也就决定了mysql 要将多个同时存在的page管理起来
   3. 要管理所有的MySQL内的page（需要先描述，在组织）
      

为何IO交互要是 Page

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

1. 如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
2. 但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时 候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5 等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。（也就是局部性性原理，相关信息一般在一起）

• 怎么保证，用户一定下次找的数据，就在这个Page里面？
• 不能严格保证，但是有很大概率，因为有局部性原理。
• 往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数

5.1理解 Page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织，我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表
因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序
从上面的Page内数据记录可以看出，数据是有序且彼此关联的

为什么数据库在插入数据时要对其进行排序呢？
按正常顺序插入数据不是也挺好的吗？
插入数据时排序的目的，就是优化查询的效率。页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。
正式因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的。

理解多个Page

• 通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了
  

5.2页目录

本质就是书籍中开始的目录，通过目录的形式快速的找到索要找的数据
单页情况：
针对上面的单页Page，引入目录（如下图）：
那么当前，在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。
为何通过键值 MySQL 会自动排序：

可以很方便引入目录

多页情况：
MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据：

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

温馨提示：上面的图，是理想结构，大家也知道，目前要保证整体有序，那么新插入的数据，不一定会在新Page上面，这里仅仅做演示==

这样，我们就可以通过多个Page遍历，Page内部通过目录来快速定位数据。
可是，貌似这样也有效率问题，在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了

那么如何解决呢？解决方案，其实就是我们之前的思路

5.3目录项（给Page也带上目录）

1. 用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。
2. 和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。
3. 其中，每个目录项的构成是：键值+指针（图中没有画全）
   
   存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据，有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

• 其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。
• 这样既能管理好页目录，也能再次避免数据量的增加
  
  总体如下图：
  然后在最后再用一个页目录管理目录项
  
  这货就是传说中的B+树（不同在于在非叶子结点处相互没了链接），至此，我们已经给我们的表user构建完了主键索引。
  并且常见的存储引擎都是B+树！

---

总结一下：

1. 常见的存储引擎底层都是通过B+数来对数据的page进行管理
2. 而在存储引擎中的B+树：它是由3层构成
3. 从下而上
4. 第三层：就是一个个page，他们呢内部也有目录项，通过目录项能够快速的找到数据
5. 第二层：是一个个目录项：每个目录项存储的是page的最小（起始）的主键
6. 第三层：再通过一个目录来管理目录项
7. 这样三层结构下来，就能快速的找到page及page中的数据

---

附：

1. 叶子结点保存数据，路上结点没有只保存 目录项
   1. 非叶子结点，不存数据，可以存储更多目录项，也就是可以管理更多Page
   2. 这颗树一定是一个矮胖的树
   3. 也就意味着途径路上结点减少！找到目标数据也就只需要更少的 Page，IO次数更少 提高了效率！
   4. 每一个结点都有目录项，可以大大提高搜索效率，总体提高搜索效率
2. 叶子结点全部用链表级联起来

而这颗B+树本质就是 MySQL：innode db 索引结构！我们也就是在这张表上进行增删查改
若没有主键同样也是这样的方法构建（mysql会自动生成隐藏主键），但没有索引！

复盘一下：

1. Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
2. 查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

为何选择B+树

1. 节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
2. 叶子节点相连，更便于进行范围查找

6. 聚簇索引 VS 非聚簇索引

MyISAM 存储引擎-主键索引：MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM表的主索引， Col1 为主键

其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。

相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的

创建一个表，使用innodb存储引擎：

在Linux系统中就创建了两个表：

当我们创建表test2并使用MyISAM存储引擎：

此时就会有三个表（本质也就是将表和数据分开了）：

• MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引
• 相反：聚簇索引就是：InnoDB这种将索引和数据放在一起的方案

6.1 辅助（普通）索引：

当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引（唯一键）。对于 MyISAM，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

• 同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图
  可以看到：
  1.InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值

2. 因为一份数据没必要主键索引存一份，其他辅助索引又存一份，浪费空间

所以通过辅助（普通）索引，找到目标记录，需要两遍索引：

1. 首先检索辅助索引获得主键
2. 然后用主键到主索引中检索获得记录
3. 这种过程，就叫做回表查询

3. 只要有主键就会有主键索引（而系统在底层即使没有生成主键索引默认生成一个隐藏的主键）

---

总结：

1. 索引的本质就是数据结构 B+ 树
2. 当有主健就会有索引
3. 而底层page中就会通过主键索引，连接起来这样也就会自动的进行排序
4. 所以说我们在有主键的前提下：乱序插入他变成有序的原因就是如此

---

7. 索引的操作

0.索引的特点（本质就是主键特点）

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

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));

2. 创建标后添加主键（本质也是添加索引）

create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);

2. 查看表的索引

1. show keys from 表名

2. show index from 表名
   

3. desc 表名
   

3. 唯一索引

1. 本质就是创建唯一键的方法：

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

2. 在表创建后添加唯一键

create table test1(id int primary key, name varchar(30)）；
alter table test1 add unique(name);

对于索引来说他本质是不需要我们自己设定的，当我们在设置约束：主键、唯一键时就会自动添加索引

4. 普通索引

1. 第一种方式：

create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);

2. 第二种方式

create table user9(id int primary key, name varchar(20), email
varchar(30));
	
alter table user9 add index(name); --创建完表以后指定某列为普通索引

3. 第三种方式

create table user10(id int primary key, name varchar(20), email
varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

5. 删除索引

1. 删除主键索引:

alter table 表名 drop primary key

2. 删除其他索引：

alter table 表名 drop index 索引名
索引名就是show keys
from 表名中的 Key_name 字段

因为他本质和普通索引是一样的

3. 第三种方法方法：

drop index 索引名 on 表名;

6.复合索引

可以同时在多列上打上索引，相当于多列看成主键一样：

查看索引结构：

show index from test1\G;

发现name和email都创建了索引结构，但这仅仅只是看起来，他们本质还是要联合进行查询的
验证方法当删除其中一个索引两个都将消失：

或者再使用能设置索引名的方法创建索引，发现两索引名称也是一致的

create index myindex on test1(name,email);

1. 复合索引
2. 索引最左匹配原则（当使用复合索引时，进行查询过程中需要匹配两个字段，它是从左往右匹配的）
3. 索引覆盖（当在查询过程中，当匹配了左边的数据就不用再去匹配右边了，这样右边的就相当于被覆盖了般，也代表：索引只要有左边一个数据，就能将该复合索引找到，但注意索引的最左匹配原则，所以只能右两种情况：只有左边的索引，左右都在的索引）

8. 什么情况下添加索引

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

9. 全文索引的创建

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

创建表：

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body) -- FULLTEXT创建全文索引
)engine=MyISAM;

插入一些数据：

现在需要再body内部进行搜索（表数据如下）：

先进行平常正常的查询（并使用explain来查看查询的方式：type类型中的ALL就代表的是全部遍历式的查询，并且key为null也代表没有使用索引）：

那如何使用全文索引呢？

explain SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database')\G

---

本章完。预知后事如何，暂听下回分解。

如果有任何问题欢迎讨论哈！

如果觉得这篇文章对你有所帮助的话点点赞吧！

持续更新大量MySQL细致内容，早关注不迷路。

​

转载自CSDN-专业IT技术社区

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

原文链接：https://blog.csdn.net/ZYK069/article/details/145656283