常见问题
质量好的
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。
两阶段提交
- 执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
- 执行器拿到引擎给的行数据,把这个值加上 1,比如原来是 N,现在就是 N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
- 引擎将这行新数据更新到内存中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。
- 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。
- 执行器调用引擎的提交事务接口,引擎把刚刚写入的 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。
范式
第一范式:
第二范式:
第三范式:
其他问题
- mysql 如何查看sql语句执行时间和效率:https://zhuanlan.zhihu.com/p/86905006