MySQL学习之路Day-four

存储引擎

MySQL支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式。

MyISAM

MyISAM存储引擎是MySQL最常用的引擎

特征

它使用三个文件表示每个表:
1.格式文件：存储表结构的定义（.frm）
2.数据文件：存储表行的内容（.MYD）
3.索引文件：存储表上索引（.MYI）
有灵活的AUTO_INCREMENT字段处理
可被转换为压缩，只读表来节省空间

优缺点

优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率
缺点：不支持事务

InnoDB存储引擎

InnoDB存储引擎是MySQL的缺省引擎

特征

每个InnoDB表在数据库目录里以.frm格式文件表示
innoDB表空间tablespace被用于存储表的内容
提供一组用来记录事务性活动的日记文件
用COMMIT、SAVEPOINT、ROKKBACK支持事务处理
提供全ACID兼容
在MySQL服务器崩溃后提供自行恢复
多版本和行级锁定
支持外键及引用的完整性

优缺点

优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。 提供自动恢复机制
缺点：无法被压缩，无法转换为只读

MEMORY存储引擎

以前被称为HEAP引擎。使用MEMORY存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得MEMORY存储引擎非常快。

特征

在数据库目录内，每个表均以.frm格式的文件表示
表数据及索引被存储在内存中
表级锁机制
不能包含TEXT或BLOB字段

优缺点

缺点：不支持事务，数据容易丢失，因为所有的数据和索引都是存储在内存当中的
优点：查询速度最快

事务（Transaction）

一个事务是一个完整的业务逻辑单元，不可再分，要么同时成功，要么同时失败。
和事务相关的是DML语句（insert，delete，update），通常需要多条DML语句共同联合完成
事务的存在是为了保证数据的完整性，安全性

机制

开启事务机制后,先执行语句，但把执行记录到数据库的历史操作当中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据。最后提交事务或回滚事务，然后清空历史记录

四大特性

A（原子性）：事务是最小的工作单元，不可再分
C（一致性）：事务必须保证多条DML语句同时成功或失败
I （隔离性）：事务A与事务B直接具有隔离
D（持久性）：最终数据必须持久化的保存到硬盘中

隔离级别

理论上包括四个级别，MySQL默认是第三级别

第一级别

读未提交（read uncommitted）
对方事务还未提交，我们当前事务可以读取到对方未提交的数据
读未提交存在脏读（Dirty Read）现象：表示读到了脏的现象

第二级别

读已提交（read committed）
对方事务提交后的数据我们当前事务可以读取到
这种隔离级别解决了脏读现象
但存在问题是不可重复读

第三级别

可重复读（repeatable read）
解决了不可重复读现象
存在问题是读到的数据是幻象

第四级别

序列化读（serialiazble）
解决了所以问题
效率低，需要事务排队

MySQL默认自动提交，关闭自动提交：start transaction
设置事务隔离级别：set global transaction isolation level ____
查看事务隔离级别：select @@global.tx_isolation

索引

什么是索引

索引相当于一本书的目录，通过目录可以快速的找到对应的资源。
在数据库方面，查询一张表的时候有两种检索方式，即全表扫描和根据索引扫描

根据索引扫描缩小了扫描的范围

索引虽然可以提高检索效率，但不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改这样就不适合添加索引，因为数据一旦更改，索引就需要重新排序，进行维护。

添加索引是给某一个字段或者说某些字段添加索引

创建索引

create index 索引名称 on 表名(字段名);

删除索引

drop index 索引名称 on 表名;

其中主键和具有unique约束的字段自动会添加索引

根据主键查询的效率较高

索引底层数据结构：Btree

索引原理

通过BTree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的物理地址，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的。
一个表通过create语句建立一个索引，会在某个位置产生一个索引对象，且已经自动排完序，并分成各种不同的区，采用Btree存储起来，假设在字段A上创建了索引，当查询A='abc’的时候，首先查询A字段有没有索引，查询到有索引，通过索引检索。A=‘abc’，先定位a区，然后定位b区…缩短了扫描的数量，定位到abc后，索引后会有abc的物理地址，此时之前查询A字段的语句的条件就变成了物理地址=…的样子。
当模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的。

视图

什么是视图？

站在不同的角度去看到数据

创建视图

create view 视图名 as select 字段1,字段2 from 表;

删除视图

drop view 视图名;

对视图进行增删改查，会影响到原表数据

作用

视图可以隐藏表的实现细节。保密级别较高的系统，数据库只对外提供相关的视图，Java程序员只对视图对象进行CRUD

三大范式

第一范式

第一范式有主键，每个字段原子性不可再分

第二范式

所有非主键字段完全依赖主键，不能产生部分依赖
多对多，三张表，关系表两外键

第三范式

非主键字段直接依赖于主键字段，不能产生传递依赖
一张多，两张表，多的表加外键