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 条评论
回复 删除
下一页
