SQL分类:
数据查询语言DQL:select
数据操作语言DML:insert,delete,update
数据定义语言DDL:create,drop,alter
事务控制语言TCL:commit,rollback
数据控制语言DCL:grant,revoke
数据库设计三范式:
第一范式:数据库中不能出现重复记录(主键),每个字段是原子性的不能再分(不能包含两种信息)。
第二范式:所有非主键字段完全依赖主键,不能产生部分依赖(一个表中出现两种类型数据,比如老师和学生,信息有冗余,需要三张表记录,学生表,老师表和中间表)。
第三范式:非主键字段不能传递依赖于主键字段(表中有传递性依赖关系,须将冗余字段单独建表)。
视图:
根据查询定义的数据库对象,用于获取局部数据,被称为“虚拟表”,被用来从常规表或其他视图中查询数据。
视图作用:提高检索效率,隐藏表内容具体实现,向不同用户显示不同内容。
视图创建:create view t_base as select * from t_user where id = 1;
视图修改:alter view t_base as --
视图删除:drop view if exist t_base --
注意:mysql不支持子查询创建视图。
Mysql架构图:
Server层:连接器,查询缓存,分析器,优化器,执行器+通用binlog日志模块等。
存储引擎:主要负责数据的存储和读取。支持InnoDB,MyISAM,Memory等
执行:权限校验-查询缓存-分析器-优化器
MyISAM存储引擎:
不支持事务,也不支持行级锁和外键,表锁,当插入或更新会锁定整个表,效率低。但读取快,而且不占用大量内存,顺序存储,是一种静态索引结构。
InnoDB存储引擎:
底层存储结构B+树,B树的每个节点对应innodb的一个page,page大小是固定的,一般为16K,其中非叶子节点只有键值,叶子节点包含完成数据。支持(COMMIT,SAVEPOINT,ROLLBACK)事务,支持多版本(MVCC)和行级锁,提供ACID兼容,在MySQL崩溃后自动恢复,支持外键及引用的完整性,包括级联删除和更新。适用于常更新多并发,支持事务,可容灾,外键支持场景。
MEMORY存储引擎:
也叫HEAP堆内存,数据在内存且长度固定,所以速度非常快,支持表级锁,不包含TEXT或BLOB字段。
InnoDB和MyISAM对比:
1.count运算上,MyISAM缓存有表meta-data记录行数,InnoDB没有
2.事务支持和崩溃恢复:MyISAM强调性能,查询具有原子性,其执行速度比InnoDB快,但是不支持事务,InnoDB支持事务,提供外键
3.外键支持:MyISAM不支持,InnoDB支持
4.行级锁支持:MyISAM只有表级锁,InnoDB支持行级锁和表级锁,默认行级锁。
5.日志:MySQL自带binlog归档日志,InnoDB有redo log重做日志
面试题:给定业务场景,说明设计数据库思路?
1.分析场景,选定存储引擎,
2.数据库设计的原则,遵循三大范式
3.字段的设计,选型
redo log:
mysql先把磁盘数据刷到内存,对数据进行修改,在刷回磁盘。为避免宕机,内存数据丢失,事务提交前把数据写入磁盘,但是直接写入会出现问题--只修改一个页面的一个二字节,就要将整个页面加载到磁盘,很浪费资源,毕竟一个页面16kb大小。一个事务里的sql可能牵扯到多个数据页的修改,而这些数据页可能不是相邻的,也就是随机IO,速度慢,于是决定采用redo log解决上面问题,当做数据修改的时候,不仅在内存中操作,还会在redo log中记录这次操作,当事务提交的时候,会将redo log日志进行刷盘(redo log一部分在内存中,一部分在磁盘上)。当数据库宕机重启的时候,会将redo log中的内容恢复到数据库中,再根据undo log和binlog内容决定回滚数据还是提交数据。
redo log的好处:
1.将redo log进行刷盘币对数据页刷盘效率高,redo log体积小,只记录那些有修改。
2.redo log是一直往末尾追加,属于顺序IO,效率比随机IO高。
使用redo log怎么保证crashsafe(数据库异常重启提交记录不丢失):
Mysql自带MyISAM引擎只有binlog日志,InnoDB才有redo log,因此只有InnoDB才有crash-safe能力。InnoDB引擎就是通过redo log两阶段提交的方式+MySQL事务处理机制来支事物的。
通过redo log预提交-》bin log入库-》redo log提交来保证crashsafe。但是redo log处于预提交状态,binlog也已经写完,这个时候发生异常就要处理:
1.判断redo log是否完整,完整就提交
2.redo log只是预提交但不是commit状态,就判断binlog是否完整,完整就提交redo log,不完整就回滚事务undo log,这样就解决数据一致性问题。
事务:
TRANSACTION表示一个完整不可分割的业务,DML语句同时成功或失败,事物具有ACID特性。注意:事务只对DML有效,rollback或commit后事务就结束了。
ACID:
原子性:整个事务操作必须作为一个单元全部完成,ubdo log是实现原子性的关键。
一致性:事物开始之前和结束之后,数据库保持一致状态。
隔离性:一个事务不会影响其他事务运行,隔离性是利用锁和多版本并发控制MVCC。
持久性:在事务完成后,该事务对数据库所作的操作将永久保存并不会回滚,利用redo log实现。
事务隔离级别详解:
InnoDB实现了四个隔离级别,用以控制事务所作的修改,并将修改告知其它并发的事务:
读未提交Read UnCommitted:允许一个事务看到其它事务未提交的修改。(脏读,不可重复读,幻读)
读已提交Read Committed:允许一个事务看到其他事务已提交修改。(不可重复读,幻读)
可重复读Repeate Read:确保如果一个事务中执行两次相同的SELECT语句,都得到相同的结果,不管其他事务是否提交修改。(MyISAM有幻读)
串行化Serializable:该级别为InnoDB缺省设置,完全隔离。(没有)
注意:幻读和不可重复读有相似,但是不可重复读重点是修改,幻读重点是新增或删除。
分布式事务:
微服务都是独立的进程,数据库也是独立的,但是事务要横跨这些微服务,这是一个整体的事务,这种就是分布式事务。
CAP定理和Base理论:
它指出WEB服务无法同时满足一下三个属性:
1.一致性:客户端知道一系列的操作都会同时发生
2.可用性:每个操作都必须可以预取的响应结束
3.分区容错性:即使出现单个组件无法可用,操作依然可以完成。
Base理论:
Basically Availiable(基本可用):分布式系统在出现故障时,允许损失部分可用功能,保证核心可用。
Soft state(软状态):允许系统中存在中间状态,这个状态不影响系统可用性,这里指的是CAP中的不一致。
Eventually consistent(最终一致性):指经过一段时间后,所有节点数据都将会达到一致
BASE理论是对CAP中的一致性和可用性进行一个权衡的结果,理论的核心是:我么无法做到强一致,但每个应用都可以根据自身的业务特点,采用适当的方式来使系统达到最终一致性。
2PC二阶段分布提交
其核心思想就是将分布式事务拆分成本地事务进行处理:各个服务在处理数据中,会进行预备提交操作,预操作可以反应出各个节点是否都能够提交,预操作成功的话,事务协调器会执行每个数据库的commit操作,如果在预提交中有一个节点不能够提交,事务协调器会执行每个数据库的roll back操作。
实现思路:通过MQ实现同步消息。
TCC方式:
分为try(尝试),Confirm(确认),Cancel(取消)阶段