Mysql

一、Mysql索引结构

1）数据结构——B+树

但是红黑树的问题是：如果数据量大了之后，树的高度就会很大。


B+数叶子节点是双向链表，父节点存放子节点的最大值。
B树与B+树的区别：

1. 由于B+树中间节点不存指针，同样大小的磁盘页可以容纳更多的节点元素，树的高度就小。（数据量相同的情况下，B+树比B树更加“矮胖”），查找起来就更快。
2. B+树每次查找都必须到叶子节点才能获取数据，而B树不一定，B树可以在非叶子节点上获取数据。因此B+树查找的时间更稳定。
3. B+树的每一个叶子节点都有指向下一个叶子节点的指针，方便范围查询和全表查询：只需要从第一个叶子节点开始顺着指针一直扫描下去即可，而B树则要对树做中序遍历。

注意：一次IO就能将其中一个节点完全载入其中

2）存储引擎

InnoDB是默认的mysql存储引擎。

• .frm文件是存放表结构数据，.ibd存放的数据+索引。
• 粒度为行锁，支持高并发。
• B+树：只会在叶子节点上面挂载数据，非叶子节点不会存放数据，数据只会存在叶子节点上面，非叶子节点只存放索引列的数据。
• 叶子节点大小为1KB左右，如果树层高为3，最多三次IO就能找到，而通常根节点已经存于内存中，故2次IO。
• 支持事务.
• InnoDB一个表有多个索引，每个索引都使用B+树来存储。
• 注意：MylSAM也是B+树，与此InnoDB不同的是，MylSAM的节点是存储地址，InnoDB节点存数据。
• 一定有主键：因为按照主键来建立主键索引树，如果没有主键，会自创一个long整型作为主键。
  
• 推荐自增的整型作为主键：因为加入的时候不会插在中间，重新调整结构比较耗时。
• 不建议主键（聚集索引）字段太长，由于整体是一颗树，那尽量非叶子结点不要占用大量空间。
• 辅助索引（普通索引）：InnoDB辅助索引的data字段存的也是主键，如图的5910等，也就是从辅助索引->主键->再搜索。

索引类型	搜索几次？	怎么搜索？
聚集索引（主键）	一次搜索即可（故我们可以通过建立索引来提高速度）	以主键形成的B+树的叶子节点包含了行的全部数据
普通索引（辅助索引）（二级索引）	查询是两次查询 （回表查询，效率极低，比查一次的要低很多）	以普通索引形成的B+树的叶子节点仅存当前索引值和主键值，回表查询主键
为什么二级索引叶子节点仅存主键值，因为减少当表出现改动时避免改动所有的B+树。

• 索引覆盖：是一种优化查询的技术，将被查询的字段，建立到联合索引里去。

执行计划（EXPLAIN关键词）：
1.Using where：使用了where过滤；
2.Using index：表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表；
3.Using index condition ：有索引参加且需要回表

注意：mysql的优化器会进行优化，当需要读取的数据超过一个临界值时，优化器会放弃从索引中读取而改为进行全表扫描（ALL），这是为了避免过多的 random disk IO

3）案例

初始的SQL：

select s.* from t_student s where s.sid in (select sid from t_score sc where sc.cid = 1 and sc.score = 87);

太慢，效果不好，，主要是用了in这个关键字。

改进SQL：
做成联合索引


explain是开启执行计划。

再改进：
原本采用的是索引优化，其实可以从语句上优化，用join

二、事务的四大特性ACID

1）原子性（Atomicity）

事务包含的所有操作要么全部成功，要么全部失败回滚

2）一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。

拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

3）隔离性（Isolation）

当多个用户并发访问数据库，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

并发vs并行：
并发：只是CPU把一个时间段划分成几个时间片，然后在这几个时间片之间来回切换，由于CPU处理的速度非常快，只要时间间隔处理得当，即可让用户感觉是多个应用程序同时在进行。
并行：当系统有一个以上CPU时，当一个CPU执行一个进程时，另一个CPU可以执行另一个进程，两个进程互不抢占CPU资源，可以同时进行

4）持久性（Durability）

持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的。

5）深度理解事务的四大特性

• redo log重做日志：实现了事务的持久性D，记录修改后的记录，其包括两方面：

  1. 内存中的redo log buffer重做日志缓冲——容易丢失
  2. redo log file重做日志文件——持久
  3. 当事务提交时，必须先将该事务的所有日志写入到redo log file进行持久化，待事务的提交操作完成之后才算完成。
     
  4. 默认的重做策略是，先写到buffer中，每次commit时就会执行fsync()，全写到磁盘上。

• undo log：为了数据回滚，保证了原子性A

  1. 如果一旦宕机了，通过redo log file可以轻易恢复数据变更，但是回滚不能做。
  2. 注意：undo log是存了数据修改前的数据的值，所以只是逻辑取消之前的操作，即执行相反的sql语句。
  3. MVCC多版本控制：当用户读取一行记录时，若该记录已经被其他事务占用时，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。
  4. 当事务递交后，就将undo log放入列表，方面其他事务通过undo log来得到之前版本的行记录。
     

• purge：
  对于delete操作，并不是执行真正的删除，例如对于delete * from t where a = 1而是在讲delete flag设置成1，该记录仍然在B+树中。对于update语句，不是直接对记录进行更新，而是标识旧记录为删除，然后产生一条新记录。那么此时产生的旧版本的记录什么时候删除呢，怎么删除？就是通过purge操作。

6）三大日志

binlog逻辑日志：mysql语句

1. 记录数据库的写入性信息，以二进制保存在磁盘。
2. 使用场景：
   • 主从复制：Master端生成binlog，再将binlog发给多个slave端，slave端执行binlog从而主从数据一致。
   • 数据恢复：通过mysqlbinlog恢复数据
3. 刷盘时机：对于InnoDB存储引擎，事务提交时会记录binlog ，可以通过设置参数控制多少个事务commit了就写入磁盘中，可以通过设置此参数牺牲一定的一致性换性能。

redolog（仅Innodb引擎）物理日志：某页上发生了什么

1. 持久性：
   问：每次事务提交就刷新到磁盘中，有什么问题？
   答：①InnoDB是以页为单位与磁盘交互，而一个事务如果只修改一个数据页几个字节，把完整的数据页刷到磁盘，太费资源。②如果涉及修改多个数据页，性能也差。
2. redolog：具体记录了事务对数据页做了哪些修改。
3. 两部分：内存中的redologbuffer和磁盘上的redologfile
   mysql 每执行一条 DML 语句，先将记录写入 redologbuffer，后续某个时间点再一次性将多个操作记录写到 redologfile。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Logging) 技术。.
4. Remark: 操作系统中用户空间无法直接写进磁盘，必须经过操作系统缓冲区OS Buffer，再通过系统调用fsync()将其刷写到redologfile中
   
   可以通过设置参数，来设置刷写时机
   

5. 注意此redolog大小是固定的，循环写入，当写道结尾就回到开头覆盖写

binlog：事务提交时进行一次写入，redolog：随时写入。

由 binlog 和 redolog 的区别可知：binlog 日志只用于归档，只依靠 binlog 是没有 crash-safe 能力的。

但只有 redo log 也不行，因为 redo log 是 InnoDB特有的，且日志上的记录落盘后会被覆盖掉。因此需要 binlog和 redo log二者同时记录，才能保证当数据库发生宕机重启时，数据不会丢失。

undolog：旧版本日志

undolog记录着此行的旧版本。

任何一个增删改操作的旧版本，都会记录在undolog中，各个版本之间用回滚指针字段连接。

relaylog（从节点专属）：中继日志

将dump线程发送的binlog复制到relaylog。

三、事务的隔离级别

​​
从上之下依次加重锁的意思，即串行化是一个事务一个事务执行。

1）脏读

脏读：A读取了B的未提交的修改，这时B出现了回滚（撤销了），则A读取的全都是垃圾信息。（需要“读已提交”隔离级别才能解决）

2）不可重复读

不可重复读：A读取一下得到数据，B修改了，再读取一下，发现改变了，这里与上面不同的是B的修改是已经提交的。（需要“可重复读”隔离级别）

3）幻读

幻读：A首先读取到了数据，然后B向里面插入或删除了几条数据，所以A再查询的时候多了或少了几条。（需要“串行化”隔离级别）

​
不可重复读和幻读类似，但是幻读主要针对的是insert操作。

MVCC多版本并发控制 —— InnoDB引擎

1. 三个隐藏字段

innoDB引擎中每行存在三个字段：DB_TRX_ID （事务ID）、DB_ROLL_PTR （回滚指针）、DB_ROW_ID （自增字段，不重要）。

事务ID严格递增。

2. MVCC作用——解决读写冲突

• 为了为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争——版本号的乐观锁
• 最大好处：读不加锁，读写不冲突

3. undolog做了什么？

trx_id是事务id，事务1、2、3依次执行，undolog记录着此行的旧版本。

4. read-view(一致性视图)

第一次出现读操作时，生成一致性视图。
其包括：未提交事务ID数组(数组里最小的ID为min_id)和已创建的最大事务id(max_id)

5. MVCC过程举例

事务5进行查询，此时生成一致性视图：min= 2，未提交事务数组[2,3]，max=5.

此时的undolog是这样：

依次比较，第一个事务id = 4，发现其处于最大和最小号之间，并且不在未提交事务数组中，因此，直接返回此行。

6. 可重复读、读已提交隔离级别的实现

回顾此表格。

• 读已提交隔离级别解决了脏读，读已提交会在每次select创建一个ReadView
• 可重复读隔离级别解决了不可重复读，可重复读是第一次select之后创建ReadView，之后再select都会复用。

​

四、数据库中的锁

1）行级别的锁

2）表级别的锁

意向锁。

3）以上两种锁的解释

背景：当一个事务想要操作一个表的时候，需要首先判断“整个表是否被锁住”再判断“表中的某一行是否被锁住了”，这样扫描每一行就太复杂了。

为了解决这个问题，引出了意向锁IS，也就是如果A事务获得了行锁S，数据库会自动帮助我们表级IS锁，让这个判断再第一个判断就返回，效率高。

​

4）一致性非锁定读（普通读）

• 当我们使用时，如果当前事务正在修改某一个数据，另一个事务也是能正常读取到这个数据的，此时读取的就是一份快照数据，这种技术称为多版本控制MVCC。
• MVCC是基于undo实现的，此时读取的数据就是undo log的快照数据。
• mysql在读已提交和可重复读的隔离级别下使用的都是非锁定的一致性读

​

5）一致性锁定读（当前读）

显式的加锁。

select ... for update（加X行锁）

select ... lock in share mode（加S行锁）
​​

两个不同的Session执行lock in share mode时。SessionA和SessionB的读取没有问题，但是当SessionA再想去更新这一条记录时发现当前数据被SessionB锁住了，因此它必须等到SessionB释放掉锁之后才能正常更新。所以对于这种存在并发场景时还是需要用 for update。

​如果想要唯一并锁定数据而且还要更新的话，最好是采用select ..for update的方式。这种方式其实也是解决多线程更新问题的一种方式，还有一种就是在程序内通过Java层面的锁去解决，一个道理。

​

​

6）死锁

死锁的四个条件	解释	解决方法
互斥条件	资源是互斥的	资源共享
不剥夺条件	拿到的资源不会被抢	申请不到别的资源，自己的也放弃；申请资源被占用时
请求和保持条件	在拿到某些资源后还请求别的
循环等待条件	存在进程的循环等待链	给资源编号，必须按照顺序请求资源。

InnoDB检测死锁：等待图（wait for graph）

比如：

首先X和S都是行锁，且仅S与S能兼容，可以在Lock List中看到T2先占了row1，T1等待；在T1拿到row2后，T2等待，出现了死锁。

五、Mysql容灾

1. 容灾的几种方式

冷备+热备
同城+异地
因此：同城冷备，异地冷备，同城热备，异地热备。

比如：两地三中心

2. MySQL如果挂了怎么办呢？

主从模式。部分读请求分配到从服务器。
主挂了，从升级为主；

3. 那主从模式按部署方式又分为哪几种？

1.一主一从模式：一个大佬带一个小弟，大佬挂了小弟上位。
2.一主多从模式：一个大佬带一群小弟，只要不全挂，就还能翻盘。
3.级联主从模式：一个大佬培养了一个亲信骨干，其它小弟都由亲信骨干培养。

4. 主节点和从节点怎么保证一致呢？

两种方式：

1. 写入时写进两个数据库。成本太高
2. 主从复制。应该选用。

5. 主从复制原理

主节点Master：

1. 在主节点进行写操作时，会按时间顺序写进了binlog文件
2. 当每个Slave节点接入Master时，主节点就创建一个dump线程
3. 当Master节点的binlog发生改变时，dump线程就发送binlog到Slave节点

从节点Slave：
4. 开启：IO线程+SQL线程
5. IO线程：接收binlog并写入本地的relaylog
6. SQL线程：读取relaylog，并操作本地数据库

6. 主从复制的模式

异步模式、半同步模式、全同步模式。

1. 异步模式：主节点不管从节点的同步情况，直接返回success

2. 半同步模式：主节点会等到其中一个从节点返回ACK，再返回给用户success，否则一直等待直至超时回滚。
   这种模式比异步模式性能低，但是可靠性高。
   

3. 全同步模式：等待全部从节点返回信息。
   

7. 全同步模式性能差，怎么优化？MAR（异步多线程强同步复制）

采用MAR（异步多线程强同步复制）。

MAR机制： 速度快，又能保证节点数据的一致性。
原本我们的Master线程，在全同步和半同步模式下，都是同步的（Master写进binlog–>dump线程将此文件发给Slave的IO线程relaylog–>SQL线程拉取relaylog并写入数据库）

因此MAR采取了多线程异步模式。

主服务器User线程：Master写进binlog就算结束，继续处理下一个请求。即避免了让线程阻塞等待应答
主服务器dump线程：将binlog立即发送出去
从服务器IO线程：收binlog并写为relaylog，此步完成之后就发送一个应答给Master
从服务器SQL线程：读取relaylog并操作数据库
主服务器User ACK线程：单独开启此线程负责接收应答

Mysql面试题

1. 存储过程

• 关系型数据库MySql，SQL需要先编译再执行，而存储过程是一组为了完成特定功能的SQL语句集，经编译后存于数据库中，用户可以通过指定的存储过程名字与参数来执行它。

• 类似自己编写函数，方法

• 好处：1.能被多次调用，灵活

• 2. 是预编译的，不同的语句每次都要编译和优化
• 3. 能进行权限限制，协助限制非授权用户对数据的访问

2. 索引

• 是一种存储数据结构。用于对数据库表的一列或者多列进行排序。加快检索
• 能单独用CREATE INDEX添加普通索引，主键不行
• 设置索引的原则：较频繁的作为查询的字段；唯一性太差的不适合；更新频繁的不行
• 索引的弊端：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。
• 索引的好处：加快查询速度。

3. 事务的分类

1. 扁平事务：常见的
2. 带保存点的扁平事务：回滚到任意保存点
3. 链事务：回滚时仅能恢复到最近一个保存点
4. 嵌套事务
5. 分布式事务

4. 视图

视图是一种虚拟子表，对此做增删改查不影响原来的表

5. MylSAM和InnoDB差异

MyISAM：只支持表级锁，不支持事务，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

---

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。（看where里的是不是主键才行）。自动生成6字节的主键。支持外键（此表内的某键是另一个表的主键）。

MyISAM	InnoDB
行级锁	表级锁
不支持事务	事务ACID
frm表结构，myi索引文件，myd数据文件	frm表结构，idb索引+数据
可以无主键	必须有主键

6. SQL的几种连接

1. 内连接inner join

select * from book as a inner join stu as b on a.sutid = b.stuid

2. 外连接

select * from book as a left join stu as b on a.sutid = b.stuid

select * from book as a right join stu as b on a.sutid = b.stuid

select * from book as a full outer join stu as b on a.sutid = b.stuid

3. 交叉连接

select * from book as a cross join stu as b order by a.id

返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。

7. 索引底层如何实现的？什么时候失效？

B+树，

---

索引失效：使用索引进行查询是比较快的，索引失效就会慢速。

索引失效的情况：模 型 数 空 运 最 快

模：模糊查询like以%开头

SELECT * FROM `user` WHERE `name` LIKE '%老猿';

型：数据类型错误

SELECT * FROM `user` WHERE height= 180;

height为varchar类型导致索引失效。

数：使用了函数

SELECT * FROM `user` WHERE DATE(create_time) = '2020-09-03';

空：索引默认不存null，所以is null就会认为不是索引

SELECT * FROM `user` WHERE address IS NULL   不走索引

运：对索引列进行运算会使其失效

SELECT * FROM `user` WHERE age - 1 = 20;

最：符合索引的索引列顺序重要，如果不是按照索引的最左列开始查找，则无法使用索引。
新版本的mysql会自动优化。

快：如果mysql预计全表扫描更快，就会不使用索引

8. 数据库三范式

第一范式：
列的原子性：即列不能再分成其他几列

第二范式：
在一范式基础上加上如下，一个表必须有一个主键；不是主键的列必须完全依赖于主键

第三范式：
在第二范式基础上，不是主键的列不能互相之间存在依赖。

9. 为什么B+树作为索引？

• 降低了树的高度，可以减少IO次数
• 非叶子节点不存指针，同样大小的磁盘页可以容纳更多的节点元素
• 因为叶子节点被双向链表连接，可以很快地遍历、排序等，而B树必须做中序遍历才行
• 所有节点高度一致，查询效率稳定

10. 数据库为啥不用跳表？而redis用跳表？

跳表：
在原始链表上再加几层链表，加快查询。
查询复杂度logn（想象二分法）

红黑树查询复杂度logn；
红黑树高度logn+1

数据库采取B+树而不是跳表？

1. 跳表需要额外的空间，是一种空间换时间的操作，数据库数据数量大，跳表额外空间大。
2. 数据库的处于磁盘中，跳表越高层间距越大，会产生巨大的随机IO，影响性能。
3. 查询不如B+树稳定。
4. 因为数据库是存在着聚集索引和普通索引，这样就需要很多个跳表。

redis的zset采用跳表而不是B+树？

• redis基于内存的，redis需求高查询速度，且数据比数据库少。
• 注意zset就是一个需要排序的hashmap，每个插入进去的对象都有个score，按照score进行排序。也就是只需一个跳表即可。
• 因为是内存，无IO损耗，相比而言，跳表的插入等操作比较简单。

hashmap采用红黑树而不是跳表？

• 注意采用跳表就是必须比点啥，hashmap是存储的，不比啥。
• 红黑树不占用多余的空间