Linux下学【MySQL】底层存储的秘密---索引（存储引擎到底是什么？聚簇索引和非聚簇索引又是啥？）联合软硬件理解，通俗易懂，图文并茂

绪论​ 每日激励：“不设限和自我肯定的心态：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)

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

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

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交互的，所以本质也就衍生出许多规则：

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

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

5. 索引的理解

要管理所有的MySQL内的page（需要先描述，在组织）

为何IO交互要是 Page

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

• 怎么保证，用户一定下次找的数据，就在这个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也带上目录）

其中，每个目录项的构成是：键值+指针（图中没有画全） 存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据，有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

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

总结一下：

附：

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

复盘一下：

为何选择B+树

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值

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

总结：

7. 索引的操作

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

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

2. 查看表的索引

3. 唯一索引

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

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

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

4. 普通索引

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

create table user9(id int primary key, name varchar(20), email
varchar(30));

alter table user9 add index(name); –创建完表以后指定某列为普通索引

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

5. 删除索引

alter table 表名 drop primary key

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

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

drop index 索引名 on 表名;

6.复合索引

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

show index from test1\\G;

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

create index myindex on test1(name,email);

8. 什么情况下添加索引

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细致内容，早关注不迷路。

​