【MySQL】数据库开发技术：内外连接与表的索引穿透深度解析

**前言:**本节内容主要讲解表的内连和外连以及索引的一部分。注意：索引是很重要的知识点。务必学习！！本节将会主要谈一谈什么是索引，如何理解索引。以及怎么理解MySQL与磁盘的关系。下面友友们开始学习吧！

ps：本节内容友友们只要想学习mysql都可以观看哦！！

表的内连和外连接

内连接

外连接

左连接

右连接

索引

认识磁盘

MySQL与磁盘交互基本单位

建立共识

索引的理解

表的内连和外连接

内连接

内连接实际就是利用where子句对两种表形成的结果进行筛选。连接是在from中。以前学习的笛卡尔积实际上就是今天的内连接。笛卡尔积和where判断，就叫做内连接。

下面来看一个内连接的案例：

找出SMITH的部门名称和部门编号

select ename, dname from emp inner join dept on emp.deptno = dept.deptno where ename = 'SMITH';

外连接

左连接

除了内连接，就是外连接，我们想让左侧的表，完全显示。右侧的表如果配不上，就去掉。左外连接的语法和内连接的语法一摸一样，只不过是把left替换了inner。

现在做一下示例：

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来：

create table stu(id int, name varchar(30));
insert into stu values(1, 'jack'), (2, 'kity'), (4, 'nono');
create table exam(id int, grade int);
insert into exam values(1, 56), (2, 76), (11, 8);

现在我们进行左连接查询。我们上面的表中学生有三个。同时成绩也有三个。但是三个成绩的最后一个成绩的学号并不是上面三个学生里面的任何一个人。那么，我们左连接查询：

select * from stu left join exam on stu.id = exam.id;

我们会发现，即便我们的nono没有对应的成绩，在第二张表中没有他，他也被打印出来了。所以，我们就能发现，左连接就是，这个stu.id = exam.id的条件，更偏向于stu表。

右连接

其实左连接和右连接是一样的。所以，我们就可以做这个案例：

对stu表和exam表进行联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来。

select * from stu right join exam on stu.id = exam.id;

其实和左连接差不多的。

以上就是内外链接的所有内容。其实对于内连接来说，就是判断条件时，两张表是公平的。对于左连接来说，判断条件时，偏向左表；对于右连接来说，判断条件时，偏向右表。

索引

索引是为了提高数据库的读取速度的。我们所有的mysql的CURD操作都是在mysql的内存中进行， mysql在启动的时候会在内存中开辟一大块空间，然后将数据库操作体现在内存里。最后在合适的时候将数据刷新到外设。

我们首先要知道，MySQL的服务器，本质是在内存中的，所有的数据库的CURD操作，全部都是在内存中进行的！——所以，索引也是如此。

我们还要知道，提高算法效率的因素无非就是两个：

1、组织数据的方式。
2、算法本身。

所以，这个索引一定是某种数据结构。

其实对于我们数据库里每一个表，在系统下都对应的就是一个文件。文件都需要在磁盘上保存，未来的数据在盘片上的一个一个小格子，也就是扇区当中。

认识磁盘

认识数据库，我们就必须要认识一下磁盘。 数据库的数据其实本质上就是保存在磁盘上。和磁盘文件如出一辙，同样是保存在磁盘上的一个一个小格子上面，即扇区。并且，对于这一个一个的小格子，其实就是512字节，如今大都是4kb。具体的磁盘是什么样子的，博主已经做过讲解，下面是文章连接：linux基础IO——文件系统——学习硬件：磁盘_linux 逻辑盘文件系统-CSDN博客

这里需要强调的是，我们之前学习文件系统，它的读取基本单位，不是扇区，而是数据块。所以，系统读取磁盘，是以块为单位的，基本单位是4kb。

MySQL与磁盘交互基本单位

MySQL之下并不直接是硬件，而是操作系统。

站在mysql的角度，他认为与磁盘是以16kb进行交互，但是mysql要经过OS， OS再与磁盘进行交互。而OS与磁盘交互数据的大小为4kb。

所以，在未来我们mysql要通过OS与磁盘交互， mysql一次就是操作16kb，转化到OS和磁盘就是进行四次4kb的交互。所以，在mysql看来，他不管，他就是write，操作系统就是必须在内部的文件缓冲区找到4个块，然后四次交互将数据刷新到磁盘中。同理，未来mysql从磁盘中读数据，就是要让OS读取四个快的数据，然后mysql读取数据read一次性读取16kb。而对应的OS与磁盘之间就使用fsync系统调用进行交互。

mysql的这个16kb是mysql进行数据交互的基本单位，这个基本单位就叫做page。

建立共识

首先， mysql中的数据文件，是以page为单位保存在磁盘中的。

mysql的CURD操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的的数据。

而只要涉及到计算，就必须有cpu参与，而cpu参与，就一定要让数据先移动到内存当中，所以我们对应的磁盘当中的表当中的操作，需要CURD操作的，就必须要先将表搬到OS内存中，再从内存搬到mysql自己的缓冲区里面。在自己的缓冲区里面用mysql自己的代码进行增删查改。而这个搬运过程里面搬运一次是4kb。

为了更好的搬运，更好的提高搬运的速度。mysql服务器在内存中运行的时候，在服务器内部，就申请了被称为Buffer Pool的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。本质上就是减少系统和磁盘IO的消耗。

索引的理解

我们向一个具有主键的表中，乱序插入数据。为什么会发现这些数据会自动排序？这些是系统默认做的排序。如果一个表中以整数为主键，那么他就会按照主键的类型进行排序。

如何理解mysql中page的概念呢？mysql内部，一定需要并且会存在大量的page，也就决定了mysql必须将多个同时存在的page管理起来！要管理所有的mysql内的page，需要先描述，再组织。

所以，不要简单的将page认为是一个内存块。 page内部也必须写入对应的管理信息！！！

struct page
{struct page* next;struct page* prev;char buffer[NUM];
};

16kb， new page，将所有的page用’链表的形式管理起来 — 在buffer pool 内部，对mysql中的page进行了一个建模。

现在回到为什么要自动排序？

根据我们理解就是用来优化查询效率。 page的数据结构是链表，链表的特点也就是删除快，查询修改慢。所以优化查询的效率是必须的。在查询某条数据记录的过程中，我们一定是将整个页也就是16kb加载到内存当中。所以就能IO次数。 page和page之间是链式， page之间也是链式。但是链表查询太慢了，为了提高效率——就从两个角度进行考虑：1、page之间和page之内。

所以就有了页目录。我们知道，对于一本书来说，目录就是用来加速我们搜索书中信息的。而且，目录也是占用纸张的。所以，目录就是一种空间换取时间的做法。所以，为什么我们musqld要对数据进行排序呢，就是因为方便我们引入页目录，因为只有数据的编号有序的，我们的目录才能更快捷的查找找到对应的数据。不仅仅是page内部的数据可以使用页目录，如果page之间不适用页目录。那么我们的page之间进行搜索的时间，其实也是消耗大量时间的。所以，给我们的page之间也引入了页目录。

整个页目录如何创建，就是再创建一批page，这个page什么数据都不存，只保存它管理的page指针。这个指针指向他所管理的page的第一数据的起始位置。

可是如果我们的插入的数据非常多。所以上级的页page也会非常多。那么对于这些页目录page就也是要进行线序遍历。所以，如果发生这种情况，就要再从上层继续添加page。

所以，往后我们查询数据就自顶向下查询数据。而这，就是我们所说的B+树。所以， mysql内的数据索引，统一使用的是b+树索引的。未来我们的b+树只有叶子节点用next与prev指针将他们连接起来。其他的层并没有使用叶子节点进行连接起来。

这里有一些细节：就是叶子节点保存数据，路上节点没有。即非叶子节点不要数据，只要目录。为什么非叶子节点不存数据，可以存储更多的目录项。目录页可以更多的管理叶子page。也就意味着这棵树一定是一个矮胖的树！——即途径路上节点减少，找到目标数据只需要更少的page。也就是IO的次数更少，就在IO的层面提高了效率。而路上节点减少就意味大大提高所以了搜索效率。所以就从两个层面上整体的提高了搜索的效率。