MySQL基础学习（二）

MySQL基础学习（二）

一、存储引擎

存储引擎是MySQL中特有的一个术语，其他数据库中没有
实际上存储引擎是一个表存储/组织数据的方式
不同的存储引擎，表存储数据的方式不同。
sql show create table 表名;//查看表的组织结构

怎么给表添加/指定"存储引擎"？

在建表的时可以在最后的小括号的右边使用：
ENGINE来指定存储引擎
CHARSET来指定这张表的字符编码方式
MySQL默认的存储引擎是：InnoDB
MySQL默认的字符编码方式是：utf8
sql create table t_product( id int primary key, name varchar(255) )engine = InnoDB default charset = gbk;

MylSAM存储引擎

MyISAM 存储引擎是 MySQL 最常用的引擎。
• 它管理的表具有以下特征：
– 使用三个文件表示每个表：
• 格式文件 — 存储表结构的定义（mytable.frm）
• 数据文件 — 存储表行的内容（mytable.MYD）
• 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率
– 灵活的 AUTO_INCREMENT 字段处理
优点：可被转换为压缩、只读表来节省空间

InnoDB存储引擎

MySQL默认的存储引擎，同时也是一个重量级的存储引擎。
它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容
– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新
支持事物、数据库自动崩溃后自动恢复机制。
InnoDB最主要的特点：非常安全

MEMORY存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非
常快。
• MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。
• MEMORY 存储引擎以前被称为 HEAP 引擎。
优点：查询效率最高
缺点：不安全，断电后数据即消失

二、事务（重要）

事务：一个完整的业务逻辑。
事务的本质：批量的DML语句同时成功或同时失败。

完整的业务逻辑：假设转账，从A转1000到B账户中，将A账户减去1000（update），给B账户加上1000（update）
以上操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分
只有DML语句（insert、update、delete）才与事物有关，其它语句和事物无关！

对事务的执行过程的理解

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中，我们可以提交事务，也可以回滚事务。

提交事务

清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
提交事务标志着，事务的结束，并且是一种全部成功的结束。
sql commit;

回滚事务

将之前所有DML操作全部撤销，并清空事务性活动的日志文件。
回滚事务标志着，事务的结束。并且是一种全部失败的结束。
回滚永远只能回滚到上一次的提交点！
sql rollback;

MySQL中默认的事务行为

MySQL默认情况下是支持自动提交事务的，每执行一次DML语句，则提交一次！
自动提交事务机制不符合开发习惯
如何关闭自动提交事务的机制呢？
sql start transaction;

事务的四个特性

A：原子性：事务是最小的工作单元，不可再分
C：一致性：所有事务中的操作，必须同时成功，或者同时失败
I： 隔离性：A事务和B事务之间具有一定的隔离
D：持久性：事务最终结束的一个保障，事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上

隔离性之事务的隔离级别

1.读未提交：read uncommited（最低的隔离级别）：没有提交就读到了
事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是：脏读现象（Dirty Read）

2.读已提交：read committed：提交之后才能读到
事务A只能读到事务B提交之后的数据，解决了脏读现象
Oracle数据库默认的隔离级别是读已提交
这种隔离级别存在的问题：不可重复读取数据

什么是“不可重复读取数据”？
在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读取到4条数据，3≠4，称为不可重复读取

3.可重复读：repeatable read：提交之后也读不到，永远读的都是开启时的数据

什么是可重复读取？
事务A开启之后，不管是多久，每一次在事务A中读到的数据都是一致的。即使事务B已经将数据修改且持久化，事务A读到的数据还是没有发生改变。

虽然解决了不可重复读取数据的问题，但是可重复读每次读到的数据都是幻想，不够真实，存在幻读。

4.序列化/串行化：serializable（最高的隔离级别）
解决了所有问题，效率最低，这种隔离级别表示事务排队，不能并发！

三、索引

索引：在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。

一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。
类比查字典，先通过目录（索引）去获得一个大概的位置，然后直接定位到这个位置，做局域性的扫描，缩小扫描的范围，快速查找，效率较高。

MySQL在查询方面主要就是两种方式：
1.全表扫描
2.根据索引检索

实现原理

MySQL中索引是一个BTree数据结构，遵循左小右大原则存放，采用中序遍历方式遍历取数据
在任何数据库中，主键上都会自动添加索引对象，另外，如果一个字段上有unique约束的话，也会自动创建索引对象。
在任何数据库中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI的文件中。InnoDB中，索引存储在一个逻辑名称叫做tablespace的当中。MEMORY中，索引被存储在内存当中。

添加索引的条件

1.数据量庞大
2.该字段经常作为条件来进行查询
3.该字段很少发生DML操作（因为DML后，索引需要被重新排序）

索引的创建与删除

sql create index 索引名 on 表名(字段名); drop index 索引名 from 表名;
怎么查看一条DML语句是否使用了索引？
sql explain DML语句;

索引失效的情况

1.模糊查询中以"%"开始
2.使用or时两边任意一边字段未使用索引（少用or，建议union）
3.使用复合索引的时候，没有使用左侧的列查找
4.在where当中索引列参加了运算
sql explain select * from vip where age + 1 = 21;
5.在where列当中索引列使用了函数
sql explain select * from vip where lower(name) = 'alan';

索引的分类

1.单一索引：一个字段上添加索引
2.复合索引：两个字段或更多的字段上添加索引
3.主键索引：主键上添加索引
4.唯一性索引：具有unique约束的字段上添加索引
注意：唯一性较弱的字段上添加索引的意义不大

四、视图（View）

视图：站在不同的角度去看待同一份数据。

创建与删除视图

sql create view 视图名 as select * from 表名; drop view 视图名;
注意：只有DQL语句才能以view的形式创建

视图的用途

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作！
视图可以用来简化SQL语句
假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。可以把这条复杂的SQL语句以视图对象，可以大大简化开发，且利于后期的维护。

五、DBA常用命令

新建用户

``sql
create user 用户名 identified by 密码;

### 数据的导入和导出（数据的备份）
``sql
mysqldump 数据库名 > 目录\文件名.sql -u用户名 -p密码	//导出
source 目录\文件名.sql //导入

六、数据库设计三范式

数据库设计范式：数据库表的设计依据

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
第二范式：要求所有非主键字段完全依赖全部主键，不要产生部分依赖
第三范式：要求所有非主键字段直接依赖主键，不要产生传递依赖
第二范式建立在第一范式的基础上，第三范式建立在第二范式的基础上。
按照以上范式进行设计，可以避免表中的数据冗余，空间的浪费

第一范式

最核心，最主要的范式，所有表的设计必须满足

不满足第一范式，没有主键。怎么解决？教师和学生编号联合做主键

第二范式

不满足，学生依赖学生编号，教师依赖教师编号，这是部分依赖。会产生数据冗余
解决方案：使用三张表表示多对多的关系（学生表、教师表、学生教师关系表）

多对多怎么设计？

多对多，三张表，关系表两个外键

第三范式

一对多怎么设计？

一对多，两张表，多的表，加外键

一对一的拆分设计

实际开发中，可能存在一张表字段太多，太庞大，此时需要拆表。

口诀：一对一，外键唯一

总结

数据库设计三范式是理论上的，实践和理论有时候存在偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
有的时候可能存在冗余，但是为了减少表连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度降低。