目录
上集回顾
exists关键字
--查询语文或英文的成绩信息
这是之前用多行子查询嵌套写法
用exists关键字一样能实现
第一种方法 in,执行过程就是两次查询,先根据内部子查询得到课程 id ,score.course_id,
然后拿着这个id去外部条件中 过滤
第二种方法先执行外部查询,依次取出外部查询的每一条语句去和内部查询匹配,匹配上就保留结果,否则丢弃。
解析:就是从score成绩表中一行行的比对,比如先抽第一行,拿进行跟里面条件比对,如果符合就保留,不然就丢弃。 上面第一个是先查询条件 是 4 或 6,而这里是每一行抽出来的成绩查询里面的classes_id 是否对应 条件 4 或者 6
多行子查询使用in或exists的区别~~
1.第一种写法in,执行过程只有两次查询,先执行内部查询,根据内部查询的结果筛选外部条件, 子查询的结果会缓存到内存中,小的情况,适用于子查询结果集比较小的情况,效率比较高,但是需要使用内存空间。
⒉第二种写法exists,比较耗时,每次都是从外部查询中取出记录和内部查询匹配,内部子查询不会产生临时表,不耗费内存空间,适用于子查询结果集比较大,且内存放不下的情况!
-- 获取“中文系2019级3班”的平均分,将其看作临时表
结合上面,查出最终结果
每个成绩都是 平均分 70 要高
合并查询
MySQL索引事务(重点)
MySQL中的索引和事务(比较复杂的场景和比较庞大的数据量),面试必考。
现阶段还没有合适场景去体会用法,背就完事了~~
概念
索引是一种特殊的文件,包含数据库中所有记录的引用,类似于数组的引用下标,通过这个下标就能拿到数组中的数据。
作用
和数组中下标一样,通过下标寻找数组数据很快的~~
通过索引去查找数据库中的数据也是很快的~~
MySQL数据库是插件式的设计,每种索引在不同的存储引擎中的实现都有可能不同~~
存储引擎:就是MySQL到底如何对数据进行增删差改的不同实现方案~~
举例:同一种牌子的汽车品牌可能有很多不同类型的车型
同一个牌子:Mysql
很多的车型:存储引擎
MySQL中最经典的两种存储引擎
这里讲述的索引和事务的实现都是基于InnoDB引擎
查看当前存储引擎
为何需要索引
举例:索引和原数据表的关系就相当于书的目录和书本的内容之间的关系
根据书本目录去查找特定内容 远比 你拿一本书胡翻快的多~~
为了提高查询数据的效率,但是会拖慢 增,删,改 的效率创建索引也需要时间和空间的开销
创建索引也需要时间和空间的开销
但是,如果涉及到书本内容的修改,现在还需要改目录
适用于场景: 查多改少的情况
注释:对应表来说,查找是高频操作,增删改查是低频。
查询某个人的具体信息,id = 123
上面这语句是怎么查找的?
方法:全表扫描,在800w的记录上查询id_number为123的信息
一行行扫描直到找到id = 123的为止
explain sql语句
MySQL的引擎分析这个sql语句可能会用到的索引等信息,包括扫描的行数
举例:在上面查找 id的前面加上 explain
经常查询的属性设置为索引
创建索引需要时间和空间开销
创建索引
有无添加索引的查询速度对比:
这次花了 3.58秒
根据id_number创建索引之后根据id查诗
之前查询一次就要花费3.58秒,现在查询一次只需要纳秒级别,大大优化了时间。
创建、删除、查看索引
上面是创建普通索引,还有不普通的索引。
索引背后的数据结构
我们之前学的高效查找数据的数据结构方法
BST:二分搜索树 =》RBTree,查找的时间复杂度为logN,当数据量非常大时,由于二叉树每个节点最多有两个子树,RBTree的高度很高,数据库的数据都在磁盘上存储,每当访问一个二叉树的节点都得读写一次磁盘,这样就非常慢。
举例:
1ms = 1000 us ; 1us = 1000 ns ......
为什么使用mysql使用B+树作为索引的实现结构
为什么不用RBTree(红黑树)和哈希表
解析:因为哈希表不能区间查找, 红黑树如果数据太高的话树的层高高度太深,太高,一次红黑树的读取就相当于范围一次磁盘了,读写的非常慢。
MySQL的索引实现基于B+树的设计(N叉搜索树)
首先了解B-树(这就是B树,写成 B-树)
注释: B树家族是严格平衡树,左右子树的高度差为0,左树和右树高度完全相等
B-树
属于N叉搜索树
比如选择查询 48
先在头结点里面根据,比50小,就往下找,然后发现比20,40,45都大,就往45下面找
引入N叉搜索树后可以大大降低树的高度,相较于BST来说
注释:其实B树中的每个值是一个复合值
比如节点 50 里面,可能还保存了对应学生的名称等等~
聚簇索引
聚簇索引:(主键索引,一个表只有一个聚簇索引):构建索引树上的每个节点,除了要保存索引列的信息,还需要保存这条记录的完整内容。
特点:查询速度快,一个表只有一个聚簇索引,保存的信息多,占用空间大
非聚簇索引也叫二级索引(普通索引,唯一索引,index创建的都是非聚簇索引,一张表可以有多个非聚簇索引):索引树上的每个节点,除了保存索引列的信息之外,还需要保存该记录的行号(对应的主键id)
特点:查询速度慢(只能找到主键信息,需要回表查询),一个表可以有多个非聚簇索引,保存的信息少,占用空间小
聚餐索引
非聚餐索引
两者的区别: 非聚簇索引相较于聚簇索引需要有更多的查表操作——更多的读写磁盘操作
因为非聚餐索引只是拿到了主键id,还需要根据这个id进行别的操作
B+树
特点
1. B+树中,子节点中存在的最大值(最小值)是在父节点中出现过的值 如:8,2,5 ..等等
这样做的目的就是最底层的叶子节点包含了整个数据的全集!!
2.B+树中,最底层的叶子节点使用链表连接
目的:更高效率的区间查找
比如:要查找[5...13]的数据
先找到中最底层叶子节点的 5 和 13 ,那么5 和 13之间的所有节点都符合要求,用链表的遍历即可
因为最底层的叶子节点是链表连接,5 ,6, 8, 13
3.对于B+树来说,所有的数据都存储在叶子节点,非叶子节点只需要保存索引列的辅助信息(索引列的值)非叶子节点占用的空间小,可以直接放在内存中。
为什么放在内存中?: 减少磁盘IO
注释:如果面试考你背就完事~~请给我一张纸和笔,我给您画—下B+的结构通过画图给人家讲清楚