mysql知识点汇总

常见问题

质量好的

https://www.sohu.com/a/331887831_120043609

一般的

https://zhuanlan.zhihu.com/p/164519371
https://zhuanlan.zhihu.com/p/336828076
https://zhuanlan.zhihu.com/p/112857507
https://zhuanlan.zhihu.com/p/222958908

https://blog.youkuaiyun.com/u014209205/article/details/83051001
https://blog.youkuaiyun.com/weixin_42509518/article/details/113296570
https://www.cnblogs.com/frankielf0921/p/5930743.html
https://zhuanlan.zhihu.com/p/59697340
https://www.cnblogs.com/hsmwlyl/p/10719152.html
https://zhuanlan.zhihu.com/p/44627083

汇总

存储引擎对比

差异	MyIsm	Innodb
外键	不支持	支持
索引&数据存放方式	索引和数据文件分开存放	索引和数据可以一起存放
锁	支持到表锁	支持到行锁
事务	不支持	支持
全文索引	支持	不支持
表的行数保存	保存	未保存

索引

主键索引

聚簇索引

覆盖索引

多个键索引

隔离级别

读未提交-|脏读|-读提交-|不可重复读|-可重复读-|幻读|-串行化

MVCC：多版本并发控制，通过undo log版本链和read-view实现事务隔离

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。
在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；
而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

脏读

一个事务的操作还没有提交，另一个事务就读取了数据。
读取到的是脏数据

例如：
张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。
与此同时，事务B正在读取张三的工资，读取到张三的工资为8000。
随后，事务A发生异常，而回滚了事务。
张三的工资又回滚为5000。
最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。

(大部分数据库缺省的事物隔离级别都不会出现这种状况)

不可重复读

例如：在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。与此同时，事务B把张三的工资改为8000，并提交了事务。随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。

(大部分数据库缺省的事物隔离级别都不会出现这种状况)

幻读

例如:目前工资为5000的员工有10人，事务A读取所有工资为5000的人数为10人。此时，事务B插入一条工资也为5000的记录。这是，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。

(大部分数据库缺省的事物隔离级别都会出现这种状况，此种事物隔离级别将带来表级锁)

事务

ACID

原子性、一致性、隔离性、持久性

乐观锁/悲观锁/MVCC

https://zhuanlan.zhihu.com/p/266850866
https://zhuanlan.zhihu.com/p/40211594
https://zhuanlan.zhihu.com/p/139757717

日志

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

区别点	归档日志-binlog	重做日志-redolog
作用	归档备份	提高写磁盘的效率，WAL-Write-Ahead Logging
写入方式	追加写（空间无限）	环形缓冲区（空间有限）
种类	逻辑日志-sql语句的原始逻辑	物理日志-在某个数据页上做了什么修改
所在位置	server层，所有mysql都有	存储引擎层，innodb特有

binlog-归档日志

redolog-重做日志

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

两阶段提交

1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
2. 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

1 prepare阶段 2 写binlog 3 commit

当在2之前崩溃时重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。一致当在3之前崩溃重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

查询

连接查询

https://zhuanlan.zhihu.com/p/46037197
https://blog.youkuaiyun.com/zjt980452483/article/details/82945663

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AgHaSn97-1626612309682)(en-resource://database/1095:1)]

左连接

SELECT boy.gid, boy.name, girl.name FROM boy LEFT JOIN girl ON boy.gid = girl.gid;

右连接

SELECT boy.gid, boy.name, girl.name FROM boy RIGHT JOIN girl ON boy.gid = girl.gid;

内连接

SELECT boy.gid, boy.name, girl.name FROM boy INNER JOIN girl ON boy.gid = girl.gid;

全连接

 (select id,name from A ) union all (select id,name from B ) order by id; 

锁

https://www.jianshu.com/p/1efa81feb93f
https://www.jianshu.com/p/d2ac26ca6525

存储过程

https://blog.youkuaiyun.com/Elsa15/article/details/104302954

主从复制

https://www.cnblogs.com/rickiyang/p/13856388.html
https://zhuanlan.zhihu.com/p/50597960

• 主 log dump 线程
• 从 I/O 线程
• 从 中继日志
• 从 sql线程
• 从 binlog

同步方式： 异步、半同步（一个从节点复制好了之后，再给客户端返回信息）、全同步
考虑操作延时和容错性

多线程恢复中继日志，因为事务在执行的时候也是并发执行的。

GTID：降低了主节点异常后，定位binlog的复杂度，能够快读定位binlog。

范式

第一范式：

第二范式：

第三范式：

其他问题

1. mysql 如何查看sql语句执行时间和效率：https://zhuanlan.zhihu.com/p/86905006