索引及其背后的数据结构(顺带介绍了一下子查询和合并查询)

索引及其背后的数据结构(顺带介绍了一下子查询和合并查询)

子查询🌛

说白了两个甚至多个sql,非要反人类式的写成一个sql

比如说:我想查找张三的的同班同学有哪些

正常想法就是:先查出张三的班级是哪个班级

select class_id from classes where name='张三';
//假设返回1

然后根据班级号,把该班级的所有学生进行打印即可

select name from student where class_id =1;

但是子查询的操作就是:

select name from student where class_id =(select class_id from classes where name = '张三');

可以看出,括号内的打印结果必须要能和括号外的所选字段能够匹配的上!

多行子查询💤

比如我想查成绩单中英语和语文的成绩,但是呢,选课表和成绩表可能不是同一张表,那正常的操作:

先找到两门课的课程id,然后拿着id去成绩表中查成绩(成绩表中只有学生id和课程id,没有英语和语文这样的name字段)

select id from course where name ='语文' or name ='英语';
//假设返回的是4   6

再拿着id去查成绩:

select * from score where course_id in(4,6);

而多行子查询,一步搞定:

select * from score where course_id in (select id from course where name='语文' or name='英语');

---

合并查询🎃

使用关键字union,将两个甚至多个表竖向的结合在一起,注意区别于笛卡尔积,笛卡尔积是将两张甚至多张表的所有记录再横向进行排列组合.

正因为要竖向结合,所以,这若干个表的列的属性要能匹配的上,名字和字段属性.

如 在课程表中查找名字是英语的课程,和课程id小于3的课程(这里是针对同一张表,即课程表)

select * from course where name='英语' union select * from course where id<3;
//这里是对同一张表的上下拼接,并且还是全列查询,那各列当然能匹配的上咯

当然上述union的做法是可以用or来进行平替的:

select * from course where name='英语' or id<3;

但之所以有了or还出现union,是因为union可以针对不同的表进行上下拼接,而or只能针对一张表进行操作咯.

合并查询当然也有另外一个关键字:union all

当上述sql中的union替换成union all时,上下拼接的若干记录,一模一样的行会被降重成一份.

---

MySQL的索引事务📦

生活案例:一张学生表,我想拿着在里头找指定的一个学生,那没有索引的情况下,我们可能需要遍历整张表,这样效率是相当低效的,那具体的效率问题,很大程度上取决于这个数据是怎么组织的,这个数据的结构将会决定我们找指定的记录会更快还是更慢.那索引的目的就是来加快查找效率的.又比如在一颗普通的二叉树中查找一个指定的关键字key,那我们只能通过前(中,后)序遍历式查找,节点数是n,那查找的时间复杂度就是O(n),那如果我们把这个二叉树改造成二叉搜索树的结构去存储数据时:那最差的情况下,能找到的话,就是在二叉搜索树的最下面一层找到关键字,假设节点数是n,那最差的查找情况下,时间复杂度就是:O(logn),也可见数据的组织方式将会很大程度上决定的我们干一件事的效率.

---

建立索引之后会有什么缺点?🗡

1. 你给一本书加一个目录,你总得在书的最前面多加几页纸吧,是不是浪费纸张了,那建立索引也类似呀,即,建立数据库的索引,需要消耗一定的额外空间,数据量越大,索引消耗的空间也会越大
2. 还是一本书,如果目录已经弄好了,我突然要对书的内容上进行一些修改,那我目录也必须做一些变动,即要重新调整目录

---

和索引相关的操作🈂️

1. 查看一张表的索引

   show index from 表名;
   

   注意:建表的时候,如果对某些字段设置了:primary key(主键),unique(唯一),foreign key(外键),那这些字段就默认被添加了索引,后续依据这些字段进行查找时,效率就会高很多.

2. 创建索引

   create index 索引名 on 表名(字段名);
   

   注意:创建索引是一件非常低效的事情.尤其是当前表里数据量比较大的时候,正是右键建立索引非常的低效,所以生产环境不要贸然的创建索引,否则容易把生产环境的数据库搞挂.

3. 删除索引

   drop index 索引名 on 表名;
   

   注意:删除索引也很低效,生产环境也不能随便弄,容易搞挂生产环境.

---

索引背后的数据结构🐰

B+树

要了解B+树,必须先了解B-树,这里叫B树,不叫B减树!!!

要了解B树,先了解什么是树,为什么要用树?

1. 组织数据的结构我们所熟知的比如顺序表和链表,我们把数据放在里面可以是可以,但是一旦进行查找,那就要死了,因为要遍历查找,数据量一大,那效率是极低的!
2. 那就用树呗,树,比如二叉树,二叉树就是一个节点最多生两个儿子,如果只是使用最普通的二叉树,那我查找一个关键字,还是要进行遍历整棵树呀!不管你是用前中后序遍历还是层序遍历,总之要遍历.
3. 那树除了最普通的二叉树,还有搜索树呀,先说搜索树:依据搜索树的特点,我们找key的时候,一层相当于只找一次,找到key位置,这样我们就能省去很多没意义的比较!此时搜索树的查找效率就相当于取决于搜索树的高度了呀(万一key在辈分最小的那一层呢,是吧!?),量化就是O(logn)
4. 那我们可能就会想啦,你搜索树搞的不好,它也是可以变成一棵单支树的,单支的搜索树和普通二叉树一样,要遍历整棵树咯.
5. 所以说搜索树不一定百分之百解决问题,那还有什么树,AVL树,AVL树只不过把树的高度给弄低了,该遍历还是要遍历,所以AVL树不行
6. 那红黑树呢?红黑树是要求更宽松一点的AVL树,高度不够小,不好
7. 那哈希表呢?也不行,哈希表查相等查的快,对于查不相等的数据,就不好查咯!

---

综述:大结构还是要用树,只不过不是上面提到过的树,而是基于B树进化出来的B+树:

B树:

1. B树通过建立成多叉树,达到了有限的节点,所使用的数据结构高度更矮的目的
2. 观察发现:B树的每个节点中,N个节点可以最多生N+1个孩子
3. 在B树中查找与搜索树进行查找时类似,也是从根出发,找key所在的区间,一路往孩子方向去找,直至null,那就是没找到

问题是:B树确实比搜索树更矮了,但是你B树一个节点存那么多关键字,我在一个节点里都要比较好多次,那一前一后,是不是B树相比较于搜索树没啥优势呢?

不是!因为我们在操作数据库,数据在硬盘里!因为查找时,是以节点为单位去磁盘读数据!,读到内存中之后,在内存中的一个节点之内的比较可就快了!因为我们知道内存的访问速度可是硬盘的3-4个数量级!,所以即使B树一个节点内比较的次数比搜索树来的更多,但是查找的效率还是高非常多!

---

B+树

相比较于B树,B+树做出的改进就是,把每一个父节点的关键字操作到了叶子里,如图:

B+树比B树好在哪里?

1. B+树是真正的用于索引的数据结构
2. 每个节点的N个关键字可以最多生N个孩子
3. 由于父节点的值会在孩子节点中体现,所以B+树的最下面一层节点肯定是会包含所有的节点的关键字的
4. 最下面一层的节点通过一个双向链表连接
5. B+树也是多叉树,也可以磁盘IO少
6. 查询都在叶子里找,查找次数稳定
7. 由于是双向链表串起了所有的叶子,所以从根出发到叶子,我们也可以只在局部范围内进行查找
8. 由于非叶子只节点只负责关键字,所以非叶子没有载荷,非叶子节点甚至可以缓存到内存中!

---