mysql
2023-11-16 19:59:55 0 举报
AI智能生成
为你推荐
查看更多
包含初级程序员对于mysql面试的知识体系和结构内容
作者其他创作
大纲/内容
MySql
insert
单表查询
连表查询
group by / having
left join 左连接 右连接 内连接
count() sum() avg() min() max()
聚合函数
select
delete
update
内连接的一个重要性质:内连接查询结果与表的顺序无关 (当然顺序可能会发生变化,但是对应关系绝对不会错乱)
外连接不同于内连接的一个性质:外连接查询与表的顺序有关
内连接和外连接的区别
对于内连接查询,on语句和where语句是等效的
对于外连接,on语句在连接之前执行,where语句(在逻辑上)是在连接之后执行的,但是从explain计划来看,where也是在连接之前执行,效率更高。
on配合join连进行联表查询
on和where
where在select,update这些语句中都可以使用,而having只能在select语句中使用。
where判断在分组之前就会执行,having判断在分组之后执行。如果两个一起使用,where会先执行
where子句中可以用一些聚会函数,但是where子句中不可以
where 和 having
on 条件 where条件 和having条件对结果有什么影响
注意联表字段添加索引
查询字段使用索引 不要select * ,不然内存装不下
一般情况下,将较小的表放在前面会提高查询的效率。这是因为数据库在执行关联查询时,会先读取第一个表的数据,然后再根据关联条件去第二个表中查找匹配的数据。如果第一个表的数据量较小,那么数据库会更快地找到匹配的数据,从而提高查询效率。
a表放在前
比如a表有100条记录,b表有10000条记录,两张表做关联查询时,是将a表放前面效率高,还是b表放前面效率高?
查询的时候,首先将user表的数据加载到MySQL内存,然后去匹配Order表数据。假设user表的数据匹配量是a,Order表的数据匹配量是b,一般来说,on的条件必须是大表的索引,不然,查询速度贼慢,当然小表最好也是索引。还有就是where中的条件,最好也是和on条件的联合索引或者其他主键之类的,这样查询起来会轻松许多。
刚开始加载到内存还有个操作,就是读取User表的操作,也就是a次操作。小表驱动大表的操作次数就是 a+a*b,大表驱动小表的操作次数为b+b*a
从操作次数上来说
先说内存方面,将数据加载的内存,然后进行对比,获取到最终的结果,如果大表驱动小表,需要将大表数据加载到内存,无疑,这必然占据大量的系统内存,要知道内存的每一分空间都是珍贵的,内存占用过大,必然会挤压其他线程可以使用的空间。而且如果表数据过大,内存放不下,还必须建立临时表,然后一部分一部分的取数据进行对比,那不用说,更慢!
从内存上来说
数据IO,将表数据加载到系统内存,肯定要消耗系统IO的。如果是小表驱动大表,也就是需要加载a条数据,然后去匹配过滤数据。反过来,就需要加载b条数据,虽然对比数据的时候都是 a*b次,但是明显加载小表更省IO。
从数据IO来说
a+a*b和b+a*b的区别
原理
小表驱动大表
联表查询中有哪些优化操作注意事项
join联表语法
在mqsql8.0以上的版本已经取消了server层的缓存的功能了。
一条SQL语句是如何执行的
基础语句
有序的二叉树,在添加索引的时候会按照默认的顺序存储
叶子结点之间通过双向链表之间链接,范围查找更方便
层级更低,IO次数更少。
矮
数据都在叶子结点,通过双向链表连接,范围查询和顺序查询更加方便
胖
特点优势:有序,矮胖
Innodb存储引擎默认给一个结点分配16KB的大小。假如存储int类型的主键,一个int类型的主键大小为8B,Innodb存储引擎还会分配6B的空间用来存储下一个结点的内存地址,这样一个叶子结点就可以存储一千一百多个主键索引,最低层的结点存储索引和数据,结点之间通过双向链表连接,范围查询更加方便。其他层结点存贮冗余的索引,这样千万级数据的查找也只需要通过三四次的磁盘IO就可以查询到,这个查询效率是非常高的。
总结
B+树
通过Hash表来实现的,通过Hash运算来计算索引的位置,查询效率是很高的,但是一般不用,因为不支持范围查询
Hash索引
Hash表的查询效率非常快,但是它的hash值是无序的,不支持范围查找。
平衡二叉树当数据量特别多的时候,它的树的层级会非常高,查询也会变慢。最致命的是,当进行范围查找时,他先找到范围的边界值,然后还需要回旋(回到上面的层级)再去查找,导致效率会很低。
b树它是一个节点存贮多个索引,树的层级很低,查找的效率比较高。但是它没有解决范围查找时需要进行回旋查找的问题。
为什么要用B+数来做索引的数据结构,不用Hash表,平衡二叉树,b数。
索引结构
说白了就是主键索引
通过主键索引(没有主键ID索引也会有隐藏默认的row_id)维护索引数,叶子结点上存储的是数据行
Innodb存储引擎的B+数就是聚簇索引。而myisam存储引擎中索引和数据是分离的,叶子结点上存贮的是数据的内存地址。MyISAM 存储引擎的表数据以文件形式存储在磁盘上,每个表对应三个文件:.frm(表结构定义)、.MYD(数据文件)、.MYI(索引文件)。
聚簇索引
mysql中的基本索引类型,没有什么限制,允许在定义索引的列中插入空值和NULL值,纯粹就是为了查找的快一些。
普通索引
用多个字段来组合成联合索引,但是需要符合最左匹配原则。
联合索引
索引中的值必须是唯一的,但是允许为空值。
唯一索引
索引的分类
在sql中尽量避免select * 这种查询
用到的索引都符合查询需求,不需要再回表查询,这就是覆盖索引。
索引覆盖
在使用普通索引查询时,如果需要查询索引之外的数据,就要依靠普通索引查到的ID回到大表中再进行一次查询。
回表
索引就是一组排好序的数据结构,来帮助我们更快地查询数据,就好比字典中的目录一样
什么是索引
就是在使用联合索引的时候,查询条件需要按照联合索引的顺序进行匹配。只有在匹配到最左边的列的时候,才能继续匹配后面的列。
不符和最左匹配原则的话,联合索引就会失效
为什么要有最左匹配原则
最左匹配原则
查询的时候从数据结构上根本走不了索引
mysql优化器认为走索引的效率不如全表扫描,就不走索引了,这样对外的表现形式上来看就是索引失效。但是这只是mysql认为的,不一定哪一种效率更高,需要人工explain来具体分析。
从底层上来说
使用like模糊匹配,联合索引不符合最左匹配原则,使用了or ,或者select * ,在索引列上使用聚和函数等操作。
具体场景
索引失效
在mysql5.6版本中推出。在存储引擎中,通过索引a查到满足条件的整行数据,并不会直接返回给server层,而是继续使用索引b进行索引条件判断(这一个过程称为下推)。同理,如果有更多的索引则会继续下推。如果该数据满足了所有的索引判断的条件,就再读取该行数据,做其他的非索引的where判断。
说白了就是能使用索引的时候先试用索引来过滤数据,尽量减少回表操作,减少IO次数。
索引下推
mysql只是提供了这种机制,但是不意味着每次查询都会触发,mysql优化器会根据行数据大小,数据总量,索引数情况等综合考虑。
索引跳跃
索引的原理
索引
explain
索引的设计和取舍
看是sql走不了索引,还是mysql自动优化的
mysql中物理上是以数据页的形式来存储数据的,大量的insert和update可能会导致页分裂,频繁的页分裂就会导致页变得稀疏,并且可能会被不规则的填充,产生空间碎片。
在delete的时候,mysql并不是立刻去删除这些数据,而是将这些数据标记为“已删除”,Innodb会开启一个线程异步的删除这些没用的数据。
mysql碎片整理
数据库层面
严禁for循环
尽量避免连表查询,都走单表查询性能会更高
连接池参数优化,默认8个(最多同时有8个数据库连接可供应用程序使用)
代码层面
单库有瓶颈,分库分表
主从 读写分离
架构层面
sql优化
性能调优
字段数值会以字符集编码的形式存储在磁盘里
row_id:这一行记录的唯一标识, 如果没有创建主键索引,mysql会使用这个隐藏的索引id来帮我们维护数据库
事务id:记录哪一个事务对这行数据进行了操作
回滚指针:记录上一个事务的指针,用来进行事务回滚
隐藏字段
数据行在数据页中以单向链表的方式存储
一行数据在磁盘中是如何存储的
1.一个数据页16KB
子主题
数据页
free双向链表: 记录了空闲的缓冲页的指针
flush链表: 记录缓存页中的被更改过的脏页
记录缓冲池中缓冲页是使用顺序,加载数据页的时候会把下一个数据页加载进来
当缓冲池中缓冲页不够的时候,会使用淘汰策略来对缓冲数据进行淘汰
在lru链表中依照数据的使用频率将数据分为了热数据和冷数据,比例为5:3
数据淘汰的时候直接淘汰冷数据的尾部,刷入磁盘
lru链表
buffer poll 就是为了在高并发场景下保护磁盘做的写缓存
脏页合并:平时很快的更新操作,都是写在内存和日志上的,这就导致有时内存数据页和磁盘数据页的数据不一致
mysql的表中的数据其实是随机分配在磁盘的各个不同的角落的,在高并发场景下,会有大量的磁盘随机写命令需要执行,这样对于磁盘和机器的压力是巨大的,所以mysql做出的设计就是buffer poll, 把写数据的命令先缓存到buffer poll,然后由后台的异步线程去执行脏页合并,起到异步流量削峰,保护磁盘的作用。
buffer pool
缓存页
Redis中间件,就是为了高并发场景下为了保护mysql做的读缓存
写缓存的经典设计场景就是 mysql的底层的buffer poll缓存页。
读缓存和写缓存
性能:因为表数据在磁盘中是随机分布的,而日志文件是可以顺序写的,效率更高。
安全:如果异步写磁盘失败了,还可以根据日志文件把丢失的数据修补回来
顾名思义,在写将数据写到磁盘之前先写到日志中。为什么?
WAL(write ahead log)机制
万一buffer poll中的内存丢失了怎么办
预提交
先写redolog
再写binlog
给之前写入的数据打上commit标志,事务两阶段提交完成。
再写redolog
二阶段提交就是为了保证原子性,两个副本一个存在就都存在,要不就都不存在。出现不一致的情形肯定是redolog写了但是binlog没有写,所以每次重启之后,自动查看redolog的状态,如果是prepare状态,就会去查看有没有对应的binlog,有binlog就表示事务已经提交,直接更改redolog为commit状态,没有binlog直接把这条redolog删除即可。
具体流程
核心逻辑就是把少量的数据多次IO变成大数据少量的IO,最后达到提交事务提升效率的目的。
mysql的两阶段提交
数据存储
innodb
myisam
innodb支持事务
innodb支持外键
聚集索引的数据文件指针存放在主键索引的叶子结点上
非聚集索引的数据文件是分离的,索引的叶子结点存放的是数据的指针
innodb是聚集索引,myisam是非聚集索引
myisam和innodb的区别
存储引擎
架构
所谓事务,它是⼀个操作序列,这些操作要么都执⾏,要么都不执⾏,它是⼀个不可分割的⼯作单位。(执⾏单个逻辑功能的⼀组指令或操作称为事务)
通过undo log实现
指事务是一个不可分割的工作单元,事务中的操作要么全部完成,要么都不发生。(比如A向B转账)
原子性
通过其他三个特性最终来保证数据库的一致性。是事务追求的最终目标。
不论这个事务是否完成,数据的一致性不会改变。(A向B转账,不论是否成功,两者的存款总额是不变的,这是业务层面上的一致性)。一致性指事务前后数据库的完整约束性没有被破坏,数据字段的类型和大小都没有没改变。
一致性
写-写操作师通过锁来实现的
读写操作是通过MVCC来实现的
多个事务并发的时候,事务之间是隔离的,。一个事务不会影响另外一个事务的操作
隔离性
通过redo log实现
当事务完成之后,这个事务对数据的修改就保存在了数据库中,不会被回滚。
持久性
事务的特性
事务可以读取到未提交的数据,可能会读到脏数据,会遇到脏读,幻读,不可重复读等问题。
读未提交(RU)
事务只能读取到已经提交的数据,解决了脏读问题,但是会遇到不可重复读,幻读等问题。
读以提交(RC)
保证事务多次读取到同一个数据的结果是一致的,避免了不可重复读的问题,但是会遇到幻读
可重复读(RR)(默认的事务隔离级别)
最高的隔离级别,所有事务串行执行,避免了所有的并发问题,但是效率比较低。
序列化(S)
读到了其他事务没有提交的数据
脏读
同一个事务前后读取同一行数据的结果不一致
不可重复读
select xx for update语句对这行数据加了锁,其他事务不能插入数据
这个时候不管怎么查询都不会出现幻读的现象,因为其他事务根本插不进去数据
已解决
T1时刻,事务1select id>100的数据,查询到了三条数据
T2时刻,事务2 insert 了一条 id>100的数据。
T3时刻,事务1又进行了一次 id>100的数据,但是查到了四条数据,发生了幻读现象。
无法解决
当前读下的幻读场景本质上就是先发生了快照读,都没又发生了当前读,针对这种mysql确实没有办法,只能我们在查询的时候,尽量在事务开启后马上进行select for update,因为他会对查询的数据加锁,其他事务不能插入数据。
从另一个方面来讲,幻读并不能称为问题,它只是一种现象,根据业务场景具体去处理。
当前读下的幻读场景
RR隔离级别只是解决了快照读下的幻读,解决了部分当前读的幻读。
同一个事务两次进行查询返回的结果集不一样
幻读
两个事务同时对一行数据进行修改导致最终结果发生偏差
脏写
问题
是基于MVCC机制实现的,读的是undolog版本链的可见版本。
普通的select * 这样的语句就是快照读,它有空能读到的不是最新的数据。主要是为了实现可重复读的事务隔离级别。
快照读
当前读读的是undolog版本链的最新的版本,并且会给读到的这行数据加锁,保证其他事务不会再并发的修改这条数据。
比如insert,update,delete,select for update语句这种就是当前读
当前读
在mysql中,只有RC和RR才会使用快照读。RC在每次select时都会生成read view视图,而RR只在第一次select生成read view,之后的查询中都是复用这个read view读视图。
当前读和快照读
事务的隔离级别
并发控制机制,它通过为每个读操作创建⼀个视图来实现读写分离,保证了多个事务同时读写同⼀个数据时的⼀致性和并发性。
就是利用undo log版本链和read view读视图构成的一个数据过滤的逻辑。
多版本并发控制
事务ID
回滚指针,用来指向上一个事务
mysql的隐藏字段
顾名思义就是记录的版本的链条,当事务并发执行修改一行数据时,不同的事务对这条数据的修改产生了多个版本,在修改之前保存上一次修改的数据,在隐藏字段事务ID上记录本次修改的事务,然后将隐藏字段的回滚指针指向上一个版本。这样就会行成一个链条。
undolog日志版本链
最小事务ID
最大事务ID(当前事务ID+1)
当前事务
活跃事务列表
有四个重要的属性
read view读视图
具体实现依赖于
RC 级别时,在⼀个事务中,每执⾏⼀次查询都会⽣成⼀次读视图
RR 级别时,在⼀个事务中,只有第⼀次查询会⽣成⼀个读视图,后⾯的查询都是复⽤这个读视图,保证了可重复读
RC 和 RR 的区别
当并发事务执⾏的时候,执⾏查询操作的时候,会根据这个事务的 ID 和 readView 中的事务ID 进⾏⼀些⽐较来确定读取哪个版本的快照记录,
MVCC执⾏的流程
MVCC
如何实现隔离级别
事务
行锁是Innodb特有的,基于索引的锁
行锁
myisam,锁住整张表
表锁
间隙锁
一个事务锁住了表A,然后又访问表B;另一个事务锁住了表B,然后企图访问表A;这时就会互相等待对方释放锁,就导致了死锁
死锁
锁
binlog是MySQL用于记录数据库中的所有DDL语句和DML语句的一种二进制日志。它记录了所有对数据库结构和数据的修改操作,如INSERT、UPDATE和DELETE等。binlog主要用来对数据库进行数据备份、灾难恢复和数据复制等操作。
1.主从复制
2.数据回放
最开始binlog的作用
binlog
Redo Log是MySQL用于实现崩溃恢复和数据持久性的一种机制。在事务进行过程中,MySQL会将事务做了什么改动到Redo Log中。当系统崩溃或者发生异常情况时,MySQL会利用Redo Log中的记录信息来进行恢复操作,将事务所做的修改持久化到磁盘中。
Crash safe是指MySQL在系统崩溃或断电等意外情况下,能够保证数据的完整性和一致性。
最开始只有binlog ,为了满足crash safe的需求,产生了redolog,mysql做了两阶段提交保证数据一致性。
undolog
Undo Log则用于在事务回滚或系统崩溃时撤销(回滚)事务所做的修改。当一个事务执行完成后,MySQL会将事务修改前的数据记录到Undo Log中。如果事务需要回滚,则会从Undo Log中找到相应的记录来撤销事务所做的修改。另外,Undo Log还支持MVCC(多版本并发控制)机制,用于在并发事务执行时提供一定的隔离性。
undolog专门用来做事务版本链,回滚日志。
redolog
undo就是回退的意思,就跟在文本编辑器里面有一个undo按钮一样,你编辑的东西,按一下这个undo按钮就回退到上一个版本了。
redo是“re”+\"do\",\"re\"就是重来一次的意思,“do”就是做的意思。所以连在一起,就是重新再做一遍,也就是重新再执行一次sql。那么什么时候需要重新再执行一次sql呢?执行的数据丢了嘛,自然就需要重新执行一次。
bin就是“binary”的缩写,\"binary\"就是二进制的意思,可以引申为“原始”的意思,所以bin log就是最全最原始的东西,里面包含了一切,所以可以用来做备份,有了它,就有了一切。
帮助记忆
目的:Redo log的目的是为了保证事务的持久性,主要用于崩溃恢复,而Undo log的目的是为了保证事务的原子性和一致性,主要用于事务回滚。
记录内容:Redo log记录的是数据事务执行后的内容,而Undo log记录的是事务执行前的内容
记录时间:Redo log是在事务提交时记录,Undo log是在事务执行时记录。
undolog 和 redolog区别
日志
0 条评论
回复 删除
下一页