Mysql数据库原理--查询收尾+索引+事务

1.查询收尾

1.1自查询

子查询就是套娃的过程，下面的这个例子就可以帮助我们理解这个过程：

例如在一个学生表里面，我们想要查询这个张三同学的同班同学：

select classid from student where name=‘张三’;----首先需要找到张三同学的这个班级信息,假设结果是6

select name from student where class_id=6 and name!=‘张三’;—这个时候我们已经查询出来结果了；

但是子查询就是要套娃：

select name from student where class_id=（select classid from student where name=‘张三’） and name!=‘张三’;

这个就是子查询，其实就是把两个语句合并成为了一个，把一个的结果套进另外一个语句里面去；

下面看一下这个多行子查询：就是返回的结果是多条记录；

查询英语，或者语文成绩信息：这个我们可以使用联合查询（相关联的这个表连接起来），也可以使用子查询；

select score.student_id,course.name,score.score from course,score where course.id=student.course_id and(course.name=‘语文’ or course.name=‘英语’); 这个是联合查询；

下面的这个是子查询的方法：

select id from course where name=‘语文’ or name=‘英语’; ------------打印结果4,6

==select score.student_id,score.course_id,score.score from score where score.course_id in (4,6); == 这个是根据课程进行分数的查询

子查询就是合并上面的两个语句：

==select score.student_id,score.course_id,score.score from score where score.course_id in (select id from course where name=‘语文’ or name=‘英语’); ==

1.2合并查询

合并查询就是把多个sql的查询结果，合并到一起去，这个就是合并查询，我们使用的这个union关键字进行合并查询的操作；

查询id<3或者是课程名称是英语的课程：

select * from course where course_id<3 or name=‘英语’;

select * from course where course_id<3 union select * from course where name=‘英语’;

上面的两个可以实现相同的查询效果，但是我们的这个合并查询的这个union可以在不同的表里面进行查询，但是我们的这个or只可以是对于一个表进行处理；

2.索引事务

我们的这个下面的演示还是基于学生表来进行的，简单的介绍一下这个学生表，这个学生表里面的这个id是属于int类型的数据，name是属于varchar类型的，这个就是常规操作，之前的演示基本上也是这样搞得；

2.1约束自动生成索引

我们的主键primary，unique约束，以及这个外键约束都是会向这个表里面自动添加索引的，我们可以通过具体的sql语句尝试一下；

下面的这个是为我们的学生表添加主键约束primary key之后，我们使用这个show index的语句进行查询，这个时候就可以看到这个主键添加之后，这个表里面就会自动生成索引；

create table student(id int primary key,name varchar(20));

show index from student;

下面的这个就是添加的这个unique的约束，我们查看这个表的相关的索引之后，发现这个对应的约束也是成为了索引；

create table student(id int unique,name varchar(20));

show index from student;

下面的这个是添加的外键约束，使用的就是我们的这个foreign ky以及已经设置的这个主键约束，让我们的这个学生表里面的这个id和我们的班级表里面的这个classid进行相互的检查，防止出现问题；

create table student(id int primary key,name varchar(20),classid int,foreign key(classid) references class(id));

show index from student;

2.2create手动添加索引

create index stu_index_name on student(name);

show index from student;

2.3.删除手动创建的索引

我们的删除索引只可以删除我们自己手动创建的索引，就是上面的这个我们使用的create index这个语句创建的索引才可以被我们删除，如果是类似于这个主键，unique以及这个外键约束之类的这个索引，我们自己是无法进行删除的；

drop index stu_index_name on student;

而且一般的建议都是，如果我们的这个数据量很大，我们不建议中途向这个表里面添加索引，因为这个时候大概率会出现问题，我们的索引最开始的时候就确定好，后期尽量不要进行修改；

2.4索引背后的数据结构

我们的这个索引是在数据库里面进行使用的，这个就需要和我们的这个数据库具体的业务相符合，例如这个数据的查询，以及这个精准匹配和模糊匹配；

例如这个id=10就是精准匹配，例如这个age<20就是模糊匹配，我们选择的这个数据结构需要可以处理类似这样的这个具体的情况，最后发现背后的数据结构是B+树–这个是一个改进的树状结构；也称为N叉搜索树，使我们之前学习的这个二叉搜索树的一个升级版本；

要想了解这个B+树，我们首先要知道这个B树结构以及特点：

下面的这个就是我自己手动绘制的一个简单的B树结构，在这个树里面，我们可以简单的看一下，再去了解这个数的结构特点：

首先，这个树的叉变多了，而且每一个的叉的这个点的数据多少是可以不一样的，我们的这个最上面的这一层的数据是30 40 50 60这是把我们的数轴划分为了5个区间（这个应该不难理解吧）；

我们的第一个分叉》就是位于这个小于30的区间里面的数据，所以我写了这个15 20 25之类的；

第二个分叉就是属于是位于30—40之间的数据，我也写了三个数据，以此类推，后面的这个数据也是遵循的这个规律；

同理，第三层的这个数据，我们也是把这个15 20 25作为基准，把这个数轴划分为4个部分，每一个部分都是有自己的这个节点数据的，下面的这个数据也是分别按照这个 小于15 15–20 20—25 大于25 的这个顺序情况进行罗列的，这个就是对于B树的一个简单初步的认识；

和二叉树相比：二叉树只可以有两个分支，数据量很大的时候，这个树的高度就会比较高，但是我们的这个B树的分支比较多，因此这个树的高度就不会太高；

2.5B+树的结构特点和优点–经典面试题

B+树的特点：
1）N叉搜索树，有N个节点，把这个划分为了N+1个区间；

2）每一个节点的N个key里面的最后一个，就是当前子树的最大值；

3）父节点的每一个key都会在子节点区间里面以最大值的形式出现；

4）B+树使用链表的结构，把子节点连接起来，方便我们进行遍历等操作；

通过下面的这个图，简单的结合者说一下这个B+树的情况，就是我们的这个8，一定是这个第二层的左子树的最大值（2 5 8里面的这个8就是最大值），我们的这个15在下面的一层的一个节点里面，也一定是最大值（11 13 15里面的这个15确实是最大值）；

我们的这个2 5 8 这个节点里面，2 5 8 分别是他的这个子节点对应区间里面的最大值，而且必须要出现，这个可以很显然的看到，同理，这个11 13 15在这个对应的子节点里面也分别是作为最大值出现的；

这个时候，我们的这个最下层的这个6个节点里面的12个数据实际上就已经囊括了我们的整个B+树的所有的数据，因为我们的第一层数据在第二层里面会以最大值的形式存在，第二层的数据在第三层里面会以最大值的形式存在，这样算下来我们的这个上面的两层的数据，在第三层里面一定是存在的，因此这个第三层就包含了我们的所有的数据，到这个地方，我们就可以理解这个上面的介绍的B+树的前面的三个特点；

第四个链表结构，就是我们的最下面的这个叶子节点之间是类似于链表的解构链接起来的，方便我们取出来这个里面的对应区间的查询结果，例如我们的这个查询结果是id>6 and id<14,这个时候我们可以通过上面的非叶子节点快速定位这个6具体在什么位置，14在什么位置，不断地向下找，最后确定是这个7 8 9 11 12 13这个区间，这个时候正好是链表连接的叶子结点，我们就可以很快的确定结果；

如果其他的树状结构，没有使用这个链表链接，这个时候每次需要进行回溯，这个时候会降低我们的查询效率，因此这个B+树的第四个特点可以提高我们的这个查询的速度，这个B+树就是为了我们的mysql查询量身定做的一个数据结构~~

B+树和其他的数据结构相比之下的优点：

1）N叉搜索树，这个树的高度是有限的，降低了IO的次数（磁盘读取和写入的速度）；

2）非常擅长范围查询（例如我们要查找这个具体的数据，直接根据父节点的这个key和最下层的子节点的具体的数值，很快就可以找到）；

3）所有的查询最终都是要落在这个子树上面，因此这个查询和查询之后的这个时间的开销是很稳定的（不会说这次特别快，下次特别慢的情况）；

4）叶子节点是全集，全部的节点在我们的叶子结点这个全集上面都是存在的，这个时候我们的这个非叶子结点只会用来存储这个key(查询的参照物）；

3.事务–经典面试题

3.1基本理解

开发时候的一些场景需要我们一气呵成完成操作，转账~~张三的账户向李四转账，张三的这个账户减少500元，李四的账户多了500，如果这个时候张三账户金额少了之后，因为其他的状况，李四的账户没有没有到账，这样的不上不下的情况，就是非常明显的bug；

事务（transaction）的引入就是为了解决上面的这个问题：把sql打包成为一个整体，要么全部执行，要么“一个都不执行”（这个也是执行之后才可以看出来，出现了故障，好像是一个也没有执行）；

多个sql打包到一起进行执行，这样的特性叫做原子性；

我们需要先开启事务，然后再执行sql,这个时候才会有原子性；

commit-----事务结束了；

roll back—主动回滚（回滚就是我们的这个sql执行出错之后，就会返回初始状态，因为事务需要一起执行，这个时候为了原子性，就会回滚，上面的是出错导致回滚，这个roll back是我们主动地让这个回滚）；

我们通过日志（打印一些执行的节点内容到这个文件里面，相当于记录之类的东西，方便我们查看这个执行的过程），记录这个事务里面的关键操作，我们的操作就是通过日志进行的，这个日志就是我们回滚的依据；

3.2事务的特性

1）原子性：就是要么全部正确执行，要么全部进行恢复到最初的状态，就是假设我们的这个程序在执行，如果打包的这个事务没有遇到问题，这个时候就皆大欢喜，如果执行的过程中遇到问题，这个时候肯定是有一部分是执行的，有一部分还没有执行，这个时候因为这个事务是一个sql语句的打包，这个时候我们就会通过回滚的方式让已经执行的这个内容回归到最初始的状态；

2）一致性：数据执行之前和执行之后，这个数据不可以太离谱，例如还是我们上面的这个转账的操作，我们小张像小李转了500元，这个执行结束之后变成了100元，这个数据就对不上，可能这个例子不是很合适，但是这个一致性就是这个意思，可以类比着去解释；

3）持久性：事务做出的修改，都是在这个硬盘上面持久保存的，即使我们的这个服务器重启，这个数据依然是在这个硬盘上面存在的，我们之前的这个修改还是生效的；

4）隔离性：数据库并发执行多个事务的时候涉及到的问题，我们的服务器向多个客户端提供服务，如果一个客户端的执行失败了，另外一个客户端才开始执行，这个时候数据库服务器需要同时处理这两个情况，服务好这两个客户端，这个就是并发执行；

隔离性就是用来衡量当我们的并发执行的时候，不同的程序之间的这个影响程度，如果一个程序的情况对于另外其他的影响很大，这个隔离性就是弱，如果程序之间几乎没有依赖性和影响性，我们就成这个并发执行的过程隔离性很强；

我们想要这个执行效率高，就需要提高这个服务器的并发程度，但是如果这个兵法的程度过高的时候，就会出现一些问题，这个隔离性就是在这个执行效率和数据的准确性的权衡；

并发执行事务的时候，可能出现的问题：

1）脏读问题：举例说明，我们的老师在写代码，我们的学生在老师写的过程中看代码，老师写完了，他也看完了，但是最后发现老师讲课的时候的代码和他之前看的完全不一样，这个就是因为他看到的是老师当时写的，后来老师进行了修改，他只读取了第一次的数据，没有读取这个修改之后的代码数据，这个时候他读取的这个数据就是无效的数据，因为学生读取的数据是我们最开始版本的数据，这个无效的数据就叫做脏数据；

解决这个脏读的手段就是在写的时候加锁：老师写的时候，我们不可以读，直到老师完全确定这个内容，我们才可以进行读取；

2）不可重复读问题：并发执行事务的时候，事务A在一次读取内容的时候，出现了不同的情况，例如我们上面的这个老师写的时候我们不可以看了，老师写完之后把这个代码上传到码云上面，这个时候我们正在看，但是这个老师又进行了修改，再次重新提交，这个时候我们正在读取，发现这个时候的数据突然变化了，这个现象就是不可重复读；

有的同学可能会说上面不是加锁了吗，为什么还会有问题，我们上面加的这个锁，只是老师写的时候我们不可以读，并没有说我们读的时候老师不可以再写，因此这个时候解决手段就是给读加锁–我们读取的时候，老师不可以写；

3）幻读问题：还是接着上面的这个现象，上面我们已经给读加锁，给写加锁，但是我们读取的时候读取的是A文件，这个时候老师无法写，是无法写A文件，但是老师可以写这个B文件啊，因为我们的加锁是给我们读取的文件加锁，这个时候我们读取的时候发现这个码云上面突然多出来一个B文件，这个现象就叫做幻读；

官方解释：事务A执行的过程中，两次的读取操作，数据的内容虽然没改变（读取的A文件内容没有改变），但是我们的数据集发生了改变（数据集里面多出来了B文件），这个现象就是幻读；

解决幻读现象的手段就是串行化的方式，保证绝对的串行执行事务，此时就完全没有并发了，这个时候效率是最低的，但是这个隔离性是最高的；

3.3隔离级别

这个隔离的层级和上面的这个现象就是相互对应的，这个需要根据实际情况选择：例如我们的银行转账，这个最求的就是准确性，这个时候效率就不是特别重要（和我们的转账的金额的准确性相比），例如我们的这个B栈上面的视频，点赞，收藏量的参数，这个效率就更重要，因为这个要不断地实时更新（点赞量100000和100006的差别显得没有那么重要这个时候，我们显示10w就可以了），因此这个需要我们根据实际情况确定下面的这个隔离的级别使用，选择合适的即可；

1）read uncommitted（读未提交）:并发性最高，隔离程度最低，执行的速度最快；

2）raed committed（读已提交）:写完之后才可以进行读取，这个时候并发的程度降低，隔离性增强；

3）repeatable read（可重复读）:读的时候不可以写，写的时候也不可以读取，这个隔离性进一步增强，并发程度进一步降低；

4）serializable（串行化）:这个时候就是一个一个的执行事务，并发性最低，隔离性最高；