MySQL-复习

七、数据库篇

• 锁机制介绍：行锁、表锁、排他锁、共享锁；
• 乐观锁的业务场景及实现方式；
• 事务介绍，分布式事物的理解，常见的解决方案有哪些，什么事两阶段提交、三阶段提交；
• MySQL记录binlog的方式主要包括三种模式？每种模式的优缺点是什么？
• MySQL锁，悲观锁、乐观锁、排它锁、共享锁、表级锁、行级锁；
• 分布式事务的原理2阶段提交，同步\异步\阻塞\非阻塞；
• 数据库事务隔离级别，MySQL默认的隔离级别、Spring如何实现事务、JDBC如何实现事务、嵌套事务实现、分布式事务实现；
• SQL的整个解析、执行过程原理、SQL行转列；

基础概念

ACID

A : 原子性 实现原理：undo log --> 逻辑日志

C : 一致性 实现原理：redo log -->

I : 隔离性

D : 持久性

事务，acid是怎么实现的

https://www.cnblogs.com/kismetv/p/10331633.html

redo log与binlog

我们知道，在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。

（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。

（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。

（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：

• 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。
• 除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

MVCC原理

https://zhuanlan.zhihu.com/p/147372839

MySQL的可重复读怎么解决的幻读

快照读情况写，用的mvcc；当前读用的是间隙锁

MySQL语句的执行流程

1.tcp连接数据库 2.查询缓存（8.0之后没有这个功能了）3.分析器：词法分析把句子变成单词，语法分析满足是否满足语法 4.优化器 5.执行器：先检查有没有权限，然后调用存储引擎的接口查询每一行。https://www.cnblogs.com/wyq178/p/11576065.html

SQL逻辑查询语句执行顺序

执行FROM语句、执行ON过滤、 添加外部行、执行WHERE过滤、执行GROUP BY分组、执行HAVING过滤、 SELECT列表、执行DISTINCT子句 执行ORDER BY子句

B+树和B树的区别

https://www.jianshu.com/p/ace3cd6526c4

log相关

redolog和binlog

redolog记录的是数据变成了什么，可以用于异常故障宕机恢复数据，binlog记录的是逻辑日志，记录对应的sql语句，可以用于主从复制。https://www.cnblogs.com/wupeixuan/p/11734501.html

Redolog为什么可以crashsafe（数据恢复），但是binlog不行

redolog循环写，保留的都是未刷入磁盘的数据，binlog全量写，分不清哪个在磁盘，哪个不在，https://cloud.tencent.com/developer/article/1757612

binlog的三种格式

row会记录每一行的修改，如果update id>1000那么会记录几百万行的修改。statement则是记录原始的sql语句。mixed由mysql决定使用哪一种。statement减少了日志的数量，但是对于存储过程，触发器，函数一类的不友好。

https://blog.csdn.net/qq_34556414/article/details/107425717

数据库引擎

innodb和myisam区别

https://blog.csdn.net/qq_41706670/article/details/92836395

分表策略

垂直拆分，按照列拆分。水平拆分，根据id取模（方法：雪花算法），时间，地点分表。
此时联合查询怎么办：多次查询，在业务层做关联；做数据冗余

explain语句查看select执行情况

https://blog.csdn.net/qq_15764477/article/details/109602129

间隙锁是怎么锁的

没有索引，锁住整张表。有索引，对于范围查询，锁住整个范围，左闭右闭。等值查询，寻找闭当前值小的一个和大的一个锁住，id=5，封锁(5的上一个，5]和(5,5的下一个],并且不让修改为被锁定的值。对于id>5,锁住的是(5,100]。

悲观锁与乐观锁

https://www.cnblogs.com/kismetv/p/10787228.html

select和update执行流程

https://blog.csdn.net/zwx900102/article/details/106741454

SQL进阶

sql优化索引

MySQL索引优化，9条：

1.前导模糊查询不能用索引

2.数据出现隐式转换，比如1和’1’，不会用索引 3.复合索引只能用最左边的

4.被or分割的条件，or前面有索引，后面没索引，也会全表扫描

5.不等于，not in,not like都不会走索引，可以优化为in

6.数据库执行计算不会用到索引，where age+1>24 7.尽量查询覆盖索引，避免回表

8.更新频繁不能建立索引

9.区分度不大不能建立索引
三层的B+树最多存储两千万数据

索引覆盖

https://www.cnblogs.com/myseries/p/11265849.html 只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

mysql读写分离：有延迟导致数据不一致怎么办

https://time.geekbang.org/column/article/77636
第一种方案强制读主，某些强一致性的业务直接走主库读取，如果一致性要求不高，可以采用，但是如果所有业务都要求强一致性，那么肯定不行。
第二种，判断主备是否有延迟。show slave status语句可以查出从库的情况，有一项是延迟，所以可以通过延迟判断，每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。show slave status还有其他结果，比如位点，可以通过位点判断是否一致。主库有两组值，表示的是读到的主库的最新位点，从库也有两组，表示的是备库执行的最新位点。如果这两组完全一致，那就说明日志已经同步完成。还有gtid（全局事务id），上面语句结果还有gtid集合，一个是备库收到的所有日志的 GTID 集合，另一个是备库所有已经执行完成的 GTID 集合，如果两个集合一样，就说明同步完成。或者可以同时启用semi-sync，当收到从库ack之后才返回给客户端，但一主多从的情况下，可能只收到了一个从节点，此时还可能不一致。还有一个问题是高并发情况下，之前用gtid判断总是不相等。
第三种，mysql提供了一个语句查询当前库是否有对应的gtid，有返回0，超时返回1.等 GTID 的执行流程就变成了：trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；选定一个从库执行查询语句；在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；如果返回值是 0，则在这个从库执行查询语句；否则，到主库执行查询语句。这样所有超时的也会到主库访问，这个业务代码需要我们自己来权衡了。

mysql为什么要用自增id作为主键

https://www.cnblogs.com/kancy/p/13458991.html

自增id的生成策略(分布式)

https://juejin.cn/post/6844904016141369352