存储引擎、事务、视图、三范式
1、存储引擎
问题: 什么是存储引擎?
存储引擎这个名词只在mysql中存在(oracle中有对应的机制,但不叫存储引擎。就是表的存储方式)。
mysql支持很多存储引擎,每个存储引擎都对应了一种不同的存储方式。每个存储引擎都有各自的有缺点,需要在合适的时机选择合适的存储引擎。
InnoDB是mysql默认使用的存储引擎,我们安装默认设置的字符集建议使用utf-8。
如何查看mysql当前版本支持的存储引擎?
命令: SHOW ENGINES \G
常见的存储引擎有哪些?
*************************** 6. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
MyISAM存储引擎:
这种存储引擎不支持事务,是mysql最常用的存储引擎,但不是默认存储引擎。
MyISAM采用三个文件组织一张表:
xxx.frm(存储表结构的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)
优点:可以被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。
缺点:不支持事务。
*************************** 7. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
InnoDB存储引擎:
表结构存储在xxx.frm文件中。
数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读。
优点:支持事务、行级锁、外键等,数据安全得到保障。
InnoDB存储引擎在mysql数据库崩溃之后提供自动恢复机制。
InnoDB支持级联删除和级联更新。
*************************** 8. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
老版本名字:HEPA引擎
优点:查询速度最快。
缺点:不支持事务。所有数据和索引都存储在内存中,数据容易丢失。
****************************** ***************************
- 建表时指定存储引擎
完整的建表语句:
CREATE TABLE `t_x` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
注意:
在MySQL当中,凡是标识符使用飘号括起来的。最好别用,不通用。
建表的时候可以指定存储引擎,也可以指定字符集。
mysql默认使用的存储引擎是InnoDB方式
默认采用的字符集是UTF-8。
默认的存储引擎可在my.ini配置文件中使用default-storage-engine选项指定
- 查看现有表的存储引擎
SHOW TABLE STATUS LIKE 'emp' \G
SHOW CREATE TABLE emp\G
- 更改现有表的存储引擎
ALTER TABLE emp ENGINE = INNODB;
2、事务
事务是一个完整的业务单元,不可拆分。
在开发中为满足业务需求,有时需要对一组数据操作语句进事务绑定来保证数据的完整性、安全性。
和事务相关的语句只有DML,因为他们三个语句是对数据库中的数据进行操作的。
- 事务的特性
事务包括四个特性:ACID
原子性(Atomicity):事务是最小的工作单元,不可拆分。
一致性(Consistency):事务必须保证多余DML语句同时成功或者同时失败。
隔离性(Isolation):多个事务之间具有隔离。
持久性(Durability):持久性表示最终数据必须持久化到硬盘中,事务才算成功。
- 事务的四种隔离级别
事务包括四个隔离级别:
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在的问题:脏读(Dirty Read)、不可重复读,幻读。
第二级别:读已提交(read committed)
对方事务提交事务之后的数据我方可以读取。
读已提交解决的问题是:脏读。
读已提交存在的问题是:不可重复读,幻读。
第三级别:可重复读(repeatable read)
这种隔离级别解决了:脏读、不可重复读问题。
这种隔离级别存在的问题是:幻读。
第四级别:序列化读/串行化读( serializable)
解决所有问题。
效率低,需要事务排队。
Oracle数据库默认的隔离级别是:第二级别,读已提交。
mysql数据库默认的隔离级别是:第三级别,可重复读。
- 事务的七种传播行为
所谓事务传播行为是指,处于不同事务中的方法在相互调用时,执行期间事务的维护情况。如,A 事务中的方法 a() 调用 B 事务中的方法 b(),在调用执行期间事务的维护情况,就称为事务传播行为。事务传播行为是加在方法上的。
REQUIRED:指定的方法必须在事务内执行。若当前存在事务,就加入到当前事务中;若当前没有事务,则创建一个新事务。
这种传播行为是最常见的选择,也是 Spring 默认的事务传播行为。
SUPPORTS:指定的方法支持当前事务,但若当前没有事务,也可以以非事务方式执行。
MANDATORY:指定的方法必须在当前事务内执行,若当前没有事务,则直接抛出异常。
REQUIRES_NEW:总是新建一个事务,若当前存在事务,就将当前事务挂起,直到新事务执行完毕。
NOT_SUPPORTED:指定的方法不能在事务环境中执行,若当前存在事务,就将当前事务挂起。
NEVER:指定的方法不能在事务环境下执行,若当前存在事务,就直接抛出异常。
NESTED:指定的方法必须在事务内执行。若当前存在事务,则在嵌套事务内执行;若当前没有事务,则创建一个新事务。
- mysql中四种隔离级别的切换
mysql中的事务是支持自动提交的,只要执行一条DML语句,则提交一次。
start transaction : 关闭自动提交机制,开启事务。
savepoint 回滚点名称:设置回滚点
rollback : 回滚。
commit : 提交。
第1级别:读未提交
全局:对所有会话生效
set global transaction isolation level read uncommitted;
会话级:只对当前会话生效
set session transaction isolation level read uncommitted;
第二级别;读已提交
set global transaction isolation level read committed;
第三级别:可重复读
set global transaction isolation level repeatable read;
第四级别:序列化读
set global transaction isolation level serializable;
- 查看当前事务隔离级别:
全局:SELECT @@global.transaction_isolation;
会话:SELECT @@session.transaction_isolation;
3、视图
试图有时也被称为“虚拟表”,可以隐藏表的实现细节。保密级别较高的系统,数据库只对外提供相关的视图,java程序员只对视图对象进行CRUD。
对视图进行增删改查,会影响到原表数据。(通过视图影响原表数据,不是直接操作的原表)
mysql不支持子查询创建视图。
- 创建视图
示例:create view myview as select empno,ename from emp;
格式:create view 视图名 as 查询语句;
- 修改视图
示例:alter view myview as select empno as myempno,ename as myname,job as myjob from emp;
格式:alter view 视图名 as 查询语句;
- 删除视图
示例: drop view if exists myview;
格式:drop view 视图名;
4、数据库设计三范式
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础上,所有非主键字段完全依赖主键,不能产生部份依赖。
多对多?三张表,关系表两个外键。
第三范式:建立在第二范式的基础上,所有非主键字段直接依赖主键,不能产生传递依赖。
一对多?两张表,多的表加外键。
提醒:在实际的开发中,以满足客户需求为主,有的时候会拿冗余换执行速度。
一对一设计有两种方案:主键共享、外键唯一。