四、事务、范式
1、存储引擎(了解)
-
完整的建表语句:
create table tx( id int default null )engine = InnoDB default charset=utf-8; -- 建表的时候,可以指定存储引擎,也可以指定字符集
-
什么是存储引擎?
- 只有mysql中存在这个名称,就是表的存储方式,mysql支持很多存储引擎(存储方式),每个存储引擎都有各自的优缺点,要在合适的时候选择合适的存储引擎
- 查看mysql支持的存储引擎:show engines \G
-
常见的存储引擎
-
MyISAM
不支持事务,mysql最常用的引擎,但不是默认的,采用三个文件存储一张表
xxx.frm(存储格式的文件)、xxx.MYD(存储表中数据的文件)、xxx.MYI(鵆表中索引的文件)
优点:可被压缩、节省存储空间,可以转换为只读表,提高检索效率
缺点:不支持事务
-
InnoDB
支持事务,mysql默认引擎,数据存储在tablespace这样的表空间中,无法被压缩,无法转换为只读
优点:支持事务、行级锁、外键等,安全性高,提供mysql数据库崩溃后提供自动恢复机制
-
MEMORY
缺点:不支持事务,数据容易丢失(数据和索引都是存储在内存中的)
优点:查询速度快
-
2、事务
-
概念:一个事务是一个完整的业务逻辑单元,不可再分
-
和事务相关的语句只有:DML语句(insert delete update)
因为只有他们这三个语句都是和数据库表中的"数据"相关的,事务的存在是为了保证数据的完整性,安全性
-
事务的特性ACID
- 原子性:事务是最小的工作单元,不可再分
- 一致性:事务必须保证多条DML语句同时成功或同时失败
- 隔离性:事务A与事务B之间具有隔离
- 持久性:最终数据必须持久化到硬盘文件中,事务才算成功的结束
-
事务的隔离级别:
- 读未提交(read uncommitted):对方事务还没有提交,我们当前事务可以读取到对象未提交的数据
- 对方事务还没有提交,我们当前事务可以读取到对象未提交的数据,这种现象被称为赃读(Dirty Read):表示读到了脏的数据
- 读已提交(read committed):对方事务提交之后的数据我方可以读取到,解决了脏读,但是会出现不可重复读:由于对方事务的更改,我方读到的数据不一致
- 可重复读(repeatable read):解决了不可重复读的问题,但是出现了幻读:读到的数据可能不是真实的
- 序列化读(serializable):解决了所有问题
- 读未提交(read uncommitted):对方事务还没有提交,我们当前事务可以读取到对象未提交的数据
-
Mysql默认是自动提交事务的,需要关闭自动提交事务:start transaction;
-
设置当前隔离级别:set global transaction isolation level 隔离级别;
-
查看当前的隔离级别:select @@global.tx_isolation;
-
Mysql默认隔离级别是可重复读(repeatable),oracle默认隔离级别是读已提交(read commintted)
3、索引
-
索引的概念:相当于一本书的目录,通过目录可以找到对应的资源
-
数据库查询一张表数据检索方式:
- 全表索引
- 根据索引检索(效率高)
-
什么时候考虑给字段添加索引(满足什么条件?)
- 数据量庞大
- 该字段很少进行DML操作。(因为字段进行修改操作,索引也需要维护)
- 该字段经常出现在where子句中(经常根据那个字段查询)
-
创建索引对象、删除索引对象
- 创建索引对象:create index 索引名称 on 表名(字段名);
- 删除索引对象:drop index 索引名称 on 表名;
-
注意点:
- 主键和具有unique约束的字段会自动添加索引
- 根据主键查询效率较高,尽量根据主键检索
-
索引的分类:
- 单一索引:给单个字段添加索引
- 复合索引:给多个字段联合起来添加1个索引
- 主键索引:主键上会自动添加索引
- 唯一索引:有unique约束的字段会自动添加索引
-
索引什么时候会失效
- select ename from emp where ename like ‘%A%’;
- 模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的
-
查询sql语句的执行计划:explain
explain select ename,sal from emp where sal = 5000;
-
索引的执行原理:底层使用 B + tree
4、视图
- 什么是视图
- 视图可以隐藏表的实现细节,保留级别较高的系统,数据库只对外提供相关的视图
- 创建视图/删除视图
- create view myview as select empno,ename from emp;
- drop view myview;
5、DBA命令
- 将数据库导出:在windows的dos命名窗口
- 导出整个库:mysqldump mnyuansql > D:\mnyuan.sql -uroot -p1234
- 导出指定数据库中的表:mysqldump mnyuansql emp > D:\mnyuan.sql -uroot -p1234
- 导入数据:source D:\mnyuan.sql
6、数据库设计三范式
- 设计范式:设计表的依据,按照三范式设计的表能很大程度上减少数据冗余
- 三范式是那些:
- 第一范式:任何一张表都应该有主键、并且每一个字段原子性不可再分
- 第二范式:建立自第一范式的基础上,所有非主键完全依赖主键,不能产生部分依赖(多对多?三张表,关系两个外键)
- 第三范式:建立在第二范式的基础上,所有非主键字段直接依赖主键,不能产生传递依赖(一对多?两张表,多的表加外键)
- 一对一怎么设计:
- 主键共享
- 外键唯一