MySql数据库知识点总结01

数据库架构

数据库从大的方面可以分为两大部分，分别为底层的存储系统也就是文件系统，和上层的程序实例组成，程序实例有存储管理、缓存管理、日志管理、权限管理、容灾管理、SQL解析、索引、锁等

程序实：存储管理、缓存管理、日志管理、权限管理、容灾管理、SQL解析、索引、锁等
存储：文件系统

MySQL体系结构

1. MySQL是插件式的存储引擎，其中存储引擎分很多种。只要实现符合mysql存储引擎的接口，可以开发自己的存储引擎!
2. 所有跨存储引擎的功能都是在服务层实现的。
3. MySQL的存储引擎是针对表的，不是针对库的。也就是说在一个数据库中可以使用不同的存储引擎。但是不建议这样做。

乐观锁和悲观锁

悲观锁：对数据持一种悲观的态度，认为在事务执行期间总是有别的事务修改数据，所以每一次操作都加锁
乐观锁：对数据持一种乐观的态度，所以不加锁，乐观锁中每一项数据都有一个版本号，在事务提交的时候，通过版本号来看是否是否在事务A期间，事务B修改了数据，比如有两个事务，事务A先查询数据，此时数据的版本号为0，事务B查询版本也为0，当A修改了数据并且版本变为1，在提交是版本号1>0提交成功，此时事务也修改了数据把版本加一变为1，在提交时版本号1不大于1，所以提交被驳回，数据过期

三范式

第一范式：字段不可分，只要是数据库中存在的表都符合1NF
第二范式：消除了非主属性对码的部分依赖
第三范式：消除了非主属性对码的传递依赖

数据库ACID的特性

A:原子性----一次事务是数据库的最小执行单位，要么事务内的所有操作做完，要么全不做，事务期间只要有一个操作不成功，就回滚，回到事务开始之前
C:一致性----事务从一个一致性状态转移到另一个一致性转态，比如：账户A有100，账户B也有100，加起来是200，当A给B转了50后，A为50，B为150，加起来还是200，感觉有一点能量守恒的意思
I:隔离线----一个事务的操作在提交之前是对其他不可见的
D:持久性----一旦事务提交数据会被保存到磁盘永久有效

leftjoin和rightjoin、innerjoin的区别

leftjoin(左联接) ：包含左表的全部数据和右表中联结字段相等的记录
rightjoin(右联接)： 返回包括右表中的所有记录和左表中联结字段相等的记录
innerjoin(等值连接)： 只返回两个表中联结字段相等的行

DROP，DELETE与TRUNCATE的区别

DROP会把一个表删掉
DELETE删除一个表的数据，一般配合where使用
TRUNCATE清空表中的数据，下一次在插入数据时自增长Id从1开始

UNION/ALL、EXCEPT/ALL和INTERSECT/ALL

union 合并两个查询结果，把两个结果做并集并且删除重复的行，union all不会删除重复的行
expect 把两个结果做查，包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行，expect all不消除重复的行
insert 把两个结果集做交集 只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复
行而派生出一个结果表 expect all不消除重复的行

InnoDB和MyISAM

InnoDB	MyISAM
MySQL默认的引擎，支持行级锁和表级锁 ，支持事务	只支持表级锁，不支持事务
适用大量的INSERT或UPDATE操作	适用大量的SELECT查询

为什么MyISAM会比InnoDB的查询速度快？

1. InnoDB要缓存数据块，而MyISAM只需要缓存索引快
2. InnoDB寻址要先映射到数据块在到数据行，而MyISAM记录的直接是数据的OFESET，定位比InooDB块
3. InnoDB还需要维护MVCC一致

隔离级别

脏读	读取了一个事务还未提交的数据
不可重复读	在同一个事务中两次读取的结果不一样，和幻读相比更侧重数据的修改
幻读	在同一个事务中两次读取的记录数量不一样，更偏向于插入操作带来的不一样

事务隔离级别	更新丢失	脏读	不可重复读	幻读
读未提交 （Read uncommitted）	×	√	√	√
读已提交 （Read committed）	×	×	√	√
可重复读（Repeatable read）	×	×	×	√
序列化（Serializable）	×	×	×	×

MySql在RR隔离级别下是如何避免幻读的

• 表象：快照读也叫非阻塞读 --伪MVVC
• 内在： next-key锁（行锁+gap锁）

快照读和当前读？
当前读：加了锁的增删改查语句

快照读：不加锁的非阻塞读,快照读顾名思义会生成快照，在RC（Read Committed）隔离级别下每一次的select语句都会产生一个快照，所以会产生幻读，在RR级别下，select语句执行的时间不同，生成的快照也会不同，如果有个事务的select语句执行的时候另一个事务还没提交那么这个事务就看不到另一个事务的修改，反之如果一个事务在另一个事务提交后执行了select语句那这个事务就可以看到另一个事务的修改也就是查询可以看到自己之前已提的所有事务所做的更改，看不到在查询开始之后的事务提交的更改

快照读是如何实现的？

• DB_TRX_ID、DB_ROLL_PTR、DB_ROW_ID
• undo日志
• read view

我们在建立表的时候，除了我们自己指定的一些字段，数据库会给我们默认加上一些字段，其中DB_TRX_ID就是事务的ID，DB_ROLL_PTR就是回滚的指针指向undo log，DB_ROW_ID当我们建表时没有指定主键，数据库就会指定一个ID

下面是快照读的一个例子
事务A修改了FIeld2，把12改为32，当修改的时候会把数据复制一份到undo log中，然后锁定当前行，DB_TRX_ID变为1，DB_ROLL_PTR指向undo log日志

另一个事务B也修改了数据把Filed3从13改为45，此时也会把原来的数据拷贝一份到undo log日志中，DB_TRX_ID变为2
read view可见性算法，决定了当前事务可以看见那个版本的数据，read view会把当前的DB_TRX_ID和系统其它的DB_TRX_ID对比，如果比其他大就可以看到

next-key锁？

MySql在RR隔离级别下实际上是通过next-key锁避免幻读的，next-key锁就是行锁+gap锁，gap会把一个范围锁起来，比如一个表的索引是1,3,5,当执行select * from table where id > 3;就会把>3的范围锁起来，当在另一个事务中插入一条id为4的数据时，就会被阻塞，只有当事务提交以后，另一个事务才能继续执行插入语句