Mysql面试框架图
2023-01-12 14:26:55 0 举报AI智能生成
应该是大部分面试问题都包含在内
作者其他创作
大纲/内容
MySQL底层的存储结构-页
事务
满足ACIDt特性的一组操作叫做事务
commit 提交
rollback 回滚
ACID
A：原子性
原子性表示事务中的所有操作要么一起成功，要么一起失败
举例：A转给B100块，A账户减100，B账户加100这俩操作，这俩操作要么都成功，要么都失败。
依赖于Undo log
C：一致性
前后状态一致
举例：A转给B100块，无论成功与否，A和B账户的总额是一样的
I：隔离性
一个事务在最终提交前，对别的事物不可见
d：持久性
事物一旦提交将会永久保存，即使数据库奔溃，也不会丢失数据
依赖于Redo log
事务的隔离级别
未提交读
事物中的修改，即使没有提交也可以被其他事务看到。
可能会产生脏读，幻读，不可重复的读等问题
提交读
一个事务只能读取已经提交的事物所作的修改
解决了脏读问题，但是会发生不可重复读和幻读问题
可重复读
保证一个事务多次读取到的数据是一致的
解决了脏读和不可重复读的问题
串行化
强制事务串行执行，多个事务互不干扰
不会出现并发一致性的问题
MySQL默认的是可重复读，解决了脏读和幻读问题
可重复是通过MVCC解决的（后面会具体描述）
幻读是通过间隙锁解决的
幻读问题就是在我们事务执行过程中，突然有别的事务进行添加了一条数据且已经提交，导致我们再次查询的时候发现多了一条数据。
间隙锁的原理：假设现在数据主键Id分别为1，5，10。那么在查询大于5的数据时，我们会将5前后的空间给锁死，这样子别的事务就没有办法进行添加数据了。
MySQL事务日志
redo log
重做日志，记录的物理级别的日志，即页的偏移量以及修改的值，用于保证数据的可靠性和事务的持久性。
作用
为了消除内存和磁盘间的巨大鸿沟，将所有的修改操作记录到日志中，符合一定的条件在写到磁盘
保证持久性
好处
降低了刷盘次数
占用空间比较小
特点
是顺序存储的
每执行一条语句，就会写入若干条日志。不同于binlog，binlog是最后提交的时候才会一次性写入
刷盘策略
0：纯摆子，等着后台线程进行刷盘，最多丢失1s的数据，但效率高。
1：每次提交事务都会进行一次主动的刷盘操作。虽然不存在数据的丢失，但是效率太慢。
2：每次提交事务都会将redo log buffer中的内容存放入操作系统的文件缓存中。只要操作系统不挂，就能保证可靠性
两阶段提交
概念
涉及到Redo log和bin log
当要提交的时候，先让redolog进入一个准备阶段，之后写入binlog中，然后再提交redolog
好处
解决了数据不一致的问题。
如果先提交redolog，这个时候MySQL崩溃了，那么那么binlog中就没有相关的修改，就会造成日志不一致的问题。
如果先提交binlog，这个时候MYSQL崩溃了，那么就会导致redolog缺失内容，从而造成了数据不一致的问题。那么肯定有人会问我可以利用binlog进行恢复啊，其实是不行的，因为binlog的内容是用户的操作语句，起不到数据恢复的作用
问题
肯定有人会问如果redolog到准备阶段，且binlog写入，那么系统奔溃恢复时
如果redolog是完整日志，即有commit标识，就直接提交
如果redolog是在准备阶段，则需要判断binlog是否存在完整的日志，如果是就提交，不是就回滚
如何判断binlog是否完整
statement的格式是COMMIT
row格式最后有一个XID event
如何能然Redo log和binlog对应
他俩有一个公共字段XID，如果发现redolog处于准备阶段，那么就拿着该字段和binlog进行比较
数据最终写入磁盘是来自buffer pool呢还是redolog中更新的呢
答案是buffer pool
因为redolog中只是记录了修改的位置，而不是具体的数据。哪怕是恢复数据的时候也是将数据不一致的页拿出来存入内存后进行修改
为什么MySQL执行的时候会抖一下
场景发生
一个正常SQL语句，有时候执行的快，有时候执行的慢，而且场景极难复刻，且随机发生
执行修改语句的逻辑
当我们要更新一条数据的时候，先去内存中将其进行更新
然后写入redolog中
最后在适当的时候进行刷盘
如果最后发现内存中的数据页和磁盘不一致，那磁盘数据页被叫做脏页，后期如果内存中的页被淘汰时需要进行刷盘
会抖一下的原因
刷盘时涉及到了各种原因，且因为IO操作，所以会短暂阻塞
这也就是为什么我们很难复刻的原因
可能产生刷盘的原因
redolog满了，这个时候别的操作进行时，就需要进行刷盘后才能让写指针往前推
系统内存不足的时候，当我们需要新的页的时候，这个时候内存满了，就需要淘汰旧的页，这些页需要进行数据同步。
MySQL认为系统空闲的时候会进行刷盘操作
当我们关闭服务器时，就需要把内存中的脏页刷到磁盘上，MySQL启动的时候可以直接去磁盘读数据
解决办法
更具服务器的刷盘能力确定MySQL的刷盘速度
控制脏页的比例
undo log
回滚日志，记录的是一条条的逻辑语句，即如果我们添加操作，那么回滚日志记录的就是删除语句。回滚行记录到某个特定的版本，用来保证原子性，一致性。
作用
数据回滚
这里的数据回滚只是说是从逻辑层面进行了复原，但是在内存或者页上可能会有所不同
MVCC
多版本并发控制器，用于解决不可重复读的问题
存储结构
undolog的存储结构是段式存储
一个事务对应一个回滚段，但是一个回滚段可能对应多个事务
回滚段中的数据类别
未提交的回滚字段
已提交但是为过期的字段
已提交且已过期的字段
类别
insert undo log
update undo log
bin log
通过二进制形式的进行存储的日志，记录了数据库所有执行过的DML和DDL语句
作用
数据恢复/备份
数据复制
中继日志
用于主从复制，即主服务器将自己的bin log日志传给从服务器，从服务器将会读取该bin log日志，然后写入本地，这个文件叫做中继日志，之后从服务器读取中继日志进行赋值数据
error log
记录了一些MySQL服务器发生的错误
general query log
记录了用户的所有的操作，包括连接数据库
开启通用查询日志，还原操作时的具体场景，帮助我们找到错误
默认是关闭的
多版本并发控制
MVCC在InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
当前读和快照读
快照读读取的是之前版本的信息，利用不同版本间的信息从而解决并发读的问题
当前读读取的是最新的数据
MVCC的实现原理
隐藏字段
DB_TRX_ID：记录创建这条记录/最后一次修改该记录的事务ID
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本（存储在rollback segment里）
DB_ROW_ID：隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引
Undo log
ReadView
creator_trx_id：创建这个ReadView的事务ID
trx_ids：表示在ReadView时，当前系统中活跃的事务ID
up_limit_id：活跃的事务中最小的事务ID
low_limit_id：表示生产ReadView时系统中下一个事务应该分配事务ID
分配规则
解决幻读问题
当我们开启一个事务的时候，进行第一次查询操作时就会生成一个ReadView试图，即当前快照
之后的所有的数据都是按照快照中存储的数据来的
这里需要注意除非规定在开启事务是进行事务生成，不然正常说话应该是在第一次查询时生成
常见的问题
为啥有时候会查询的很慢（这里只谈因为MVCC影响的）
有两个客户端a和b
这个时候有一个线程给1加了10000次1
a查询的是当前读，耗时极低
但是b查询的是快照读，就需要往前翻10000次，速度就很慢
MVCC可以解决幻读问题吗？
首先要知道MVCC是无法解决幻读问题的
只有配合间隙锁才能解决
主从复制
作用
读写分离：提高了并发性
数据备份
高可用性：当主机崩溃时从机可以及时顶上去
原理
三个线程
主库线程：二进制日志转储线程，将二进制日志发给从库
从库线程：IO线程，从库向主库发送请求更新binlog日志后，从库可以通过IO线程读取到主库的二进制日志转储现场发送的binlog更新部分，并拷贝到本地的中继日志
从库线程：SQL线程，从中继日志中读取并更新数据，将从库的数据于主库保持同步
同步数据一致性问题
要求
主从数据一致
写数据必须去写库
读数据则不一定要到读库
主从延时问题的根源
备库接受binlog和执行完这个事务之间的时间差
最常见的表现：从库消费中继日志(relay log)的速度，比主库生产binog的速度要慢
减少延迟的方法
降低多线程大事务并发的概率，优化业务逻辑
优化SQL，减少慢查询和减少批量操作
提高从库的机器配置
尽量采用短的链路，即减少从库和主库的距离，提升端口宽带，降低binlog传输的网络延迟
实时性要求的业务读强制走出库，从库只做备份。
解决一致性问题（从弱到强）
异步复制
当客户端提交完commit后，不需要主库进行返回结果，而是直接将结果返回给客户端
问题就是可能会导致数据一致性弱的情况
半同步复制
当客户端提交commit后，至少需要一个从机进行应答后才能返回给客户端
数据一致性会比之前好一点点，但是降低了主库键的读写效率
组复制
将多个节点共同组成一个复制组，在执行读写事务的时候需要通过一致性协议层的同意，也就是说对于一个存在写的事务如果想提交，必须要经过组内大多数人的统一（N/2+1），而不是发送放自己说的。
换句话将，就是组内的每个节点都是一台可以独立完成事务的服务器，只是对于RW事务在提交的时候，需要使用广播的形式向周围的组进行通知。告诉别的节点执行了那些变化，然后进行数据的同步。这样子就保证了数据的一致性。但是如果发生了并发情况，那么由组进行调整，按照先后顺序为主。
如何保证主备一致
我们都知道主机和备机之间是通过binlog日志进行通信的，即主机完成操作后，将对应的binlog日志传递给备机，之后备机读取到本地变成了中继日志，然后操作中继日志来更新操作
那么主备一致性就是在研究binlog日志到底存的啥，以及为啥有三种格式
statement
这种格式就是将我们完整的sql语句以及一些其他的信息保存下来
缺点对于多条件且有索引的范围查询第一条语句，可能会因为选择索引不同而导致数据不一致问题
row
这种格式是将我们修改的数据进行保存，即数据的主键是啥，以及修改的内容是啥
解决了statement的遗留问题，但是对于批量的操作，我们会生成n条数据，恢复和修改不方便，且文件比较大
mixed
他就是将上面两种格式进行了一个结合，在可能产生歧义的地方就使用row格式，其他地方用statement
但是现在越来越多的情况是将binlog设置为row格式，原因是row格式容易恢复数据
对于一些SQL语句中的函数操作，例如now函数，mixed会使用statement格式，只是说使用set timestamp命令锁死数据
在我们日常的使用中，其实并不是说使用的一主一从的这种单向关系，而是互为主备关系，这样子方便于切换，不用修改相关的信息，但是也会产生问题
循环复制问题
假设A主机和B主机，当我在A上有一个操作，生成对应的binlog，B拿到后也会进行更新操作，也有binlog，此时A作为B的备机又收到了B的binlog，继续操作，以此类推（建议将log_salve-updates=on，备机在进行操作时也会生成日志）
所以现在就是有一个属性叫做server_id，他记录的是客户端连接进行操作的主机id，且互为主备的机器的id不能一致
假设在A上操作，在有binlog日志的时候，他的server_id=1，然后传递给B之后，他的server_id是不允许变得，还是1，当Bbinlog传递到A的时候发现和A的id一致，就放弃操作
但是还有可能产生循环复制问题
A和B单向的，B和C是双向的，就会造成循环复制
修改了server_id值，就会产生B的binlog传回后发现不一致继续操作
常见的查询语句
格式
执行顺序
Mysql执行引擎的区别
MyISAM
不支持事物
不支持外键
只有表锁，并发性差
索引和数据是分离的
关注点是性能，节省资源，消耗少，简单业务
没有奔溃恢复机制
InnoDB
支持事务
支持外键
有表锁，行锁，页锁
对于聚簇索引而言，索引即数据
关注的并发写，事物，大资源
有奔溃恢复机制
Mysql的逻辑架构
索引
索引的数据结构
B+树
B树和B+树的区别
双向链表：范围查询
聚簇索引叶子节点即数据，以及什么时候回表
hash索引：虽然快，但是只能进行精确查询，可以在特殊情况下使用
索引的类型
聚簇索引
主键索引
非聚簇索引
普通索引
联合索引(最左前缀，索引覆盖)
索引是越多越好吗
答案是不一定的，因为索引有一定的空间浪费
对于查询语句而言，索引多了可能会导致优化器选择错索引，从而导致速度下降
对于修改语句而言，由于需要进行值的修改，那么就可能会存在着分裂，从而降低修改效率
那么对于大量数据的批量修改或者删除，我们一般建议删除无用索引来保证效率。
索引创建的条件
索引失效的条件
关于慢查询问题
1，打开慢查询日志：set global slow_query_log='ON'
2，查看慢查询日志：show varibales like 'slow_query_log%'
3，利用MySQL自带的慢查询分析工具进行查找慢查询的SQL：mysqldumpslow -s r -t 10 /var/lib/mysql/慢查询日志名 表示返回记录集最多的10个SQL
4，使用explain 慢查询语句分析走了哪个索引，或者有没有走索引，为什么。
如何给字符串添加索引
添加完整索引
字符串一般情况下内存 较大，查询效率低，如果要使用最好索引覆盖
前缀索引
进行截取前面的几个值，但是需要注意区分度的问题
倒序存储
其实就是将正常的字符串进行倒序然后截取
例如身份证就可以这样子搞
使用hash字段
给一个值进行哈希计算，然后进行查找这个哈希值。
hash和倒序的区别
都不能进行范围查询
倒序没有利用多余的空间，而hash则是需要创建一个多余的列
hash的查询更加稳定一点
索引选错的原因
MySQL的索引选择是由优化器去进行结果预测，选择一个最好的索引，而扫描行数的是影响决定因素之一。但是由于他的特殊采样技术，一般索引统计值和真实情况是大差不差的。
优化器还需要关注回表的代价，所以这里就是他出问题的一个原因。
举个例子，事物A生成了一个read view，然后事物B要进行删除数据时，只能逻辑删除，因为事物A还需要使用，这样子导致了计算表的数据和我们想的不是同一个，从而进行索引的选择错误，我们可以使用analyze进行修正。
解决方案
自己写SQL的时候主动引导优化器进行选择索引
如果是统计错误的话，可以使用analyze来进行
也可以通过强制使用索引解决 forcel index来完成
为啥一样的查询逻辑，而结果却相差甚远
其本质就是没有使用到索引
有以下一些情况
对于一些简单的操作，我们最好用自己写条件代替使用函数
数据的隐式转化，如比较的数据不是同一个类型
创建表时隐式字符编码转化
收藏
收藏
0 条评论
下一页
