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