MySQL总结

1、初始数据库

1.1 什么是数据库

概念：数据仓库，安装在操作系统之上的软件。可以存储大量的数据。

作用：存储数据，管理数据。

1.2 数据库分类

关系型数据库（SQL）

• 通过表和表之间，行和列之间的关系进行数据的存储。
• MySQL、oracle、SqlServer

非关系型数据库（NoSQL）

• 对象存储，通过对象的自身属性来决定。
• Redis、mongdb

1.3 数据库管理系统（DBMS）

• 数据库管理软件，科学有效的管理、维护和获取数据
• MySQL是数据库管理系统

DDL 数据库定义语言

CREATE	创建
ALTER	修改
DROP	删除
TRUNCATE 删除

DML 数据库操作语言

INSERT  插入
UPDATE	更新
DELETE	删除

delete 和 truncate 区别

• 相同点：都能删除数据，都不会删除表结构
• 不同：
  • truncate 重新设置自增列，计数器会归1
  • truncate 不会影响事务

DQL 数据库查询语言

SELECT <字段名表>
FROM <表或视图名>
WHERE <查询条件>

DCL 数据库控制语言

GRANT 授权
ROLLBACK 回滚
COMMIT 提交

2、事务

这是一个超链接

事务：程序中一系列严密的逻辑操作，所有操作必须全部成功完成，否则在每个操作中所作的所有更改都会被撤消。（要么都成功，要么都失败）

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

2.1 事务的产生

数据库中的数据是共享资源，因此数据库系统通常要支持多个用户或多个应用程序的访问，并且各个访问进程都是独立执行的，这样就有可能出现并发存取数据的现象，如果不采取一定的措施则会出现数据异常的情况。为了避免数据库的不一致性，这种处理机制称之为“并发控制”，其中事务就是为了保证数据的一致性而产生的一种概念和手段（事务不是唯一手段）。

2.2 事务四大属性

事务原则（ACID原则）：原子性、一致性、隔离性、持久性

• 原子性（Atomicity）

  要么都成功，要么都失败。

• 一致性（Consistency）

  事务前后的数据完整性保持一致。

  举例：A有10元，B有20元，二者总和30元，A转给B5元，二者总和还是30元

• 隔离性（Isolation）

  多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

• 持久性（Durability）

  事务一旦提交不可逆，被持久化到数据库中。

2.3 隔离所导致的一些问题

脏读

1、在事务A执行过程中，事务A对数据资源进行了修改，事务B读取了事务A修改后的数据。

2、由于某些原因，事务A并没有完成提交，发生了RollBack操作，则事务B读取的数据就是脏数据。

这种读取到另一个事务未提交的数据的现象就是脏读(Dirty Read)。

不可重复读

事务B读取了两次数据资源，在这两次读取的过程中事务A修改了数据，导致事务B在这两次读取出来的数据不一致。

这种在同一个事务中，前后两次读取的数据不一致的现象就是不可重复读(Nonrepeatable Read)。

虚读（幻读）

事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。

幻读和不可重复读有些类似，但是幻读强调的是集合的增减，而不是单条数据的更新。

第一类更新丢失

事务A和事务B都对数据进行更新，但是事务A由于某种原因回滚了，把已经提交的事务B的更新数据给覆盖了。这种现象就是第一类更新丢失。

第二类更新丢失

其实跟第一类更新丢失有点类似，也是两个事务同时对数据进行更新，但是事务A的更新把已提交的事务B的更新数据给覆盖了。这种现象就是第二类更新丢失。

2.4 事务隔离级别

为了解决以上的问题，主流的关系型数据库都会提供四种事务的隔离级别。事务隔离级别从低到高分别是：读未提交，读已提交，可重复读，串行化。事务隔离级别越高，越能保证数据的一致性和完整性，但是执行效率也越低，所以在设置数据库的事务隔离级别时需要做一下权衡，mysql默认是可重复读

读未提交

读未提交(Read Uncommitted)，是最低的隔离级别，**事务A可以读取到事务B修改过但未提交的数据。**只能防止第一类更新丢失，不能解决脏读，可重复读，幻读，所以很少应用于实际项目。

读已提交

读已提交(Read Committed)，在该隔离级别下，**事务A只能在事务B修改过并且已提交后才能读取到事务B修改的数据。**可以防止脏读和第一类更新丢失，但是不能解决不可重复读和幻读的问题。

可重复读（重要）

可重复读(Repeatable Read)，mysql默认的隔离级别。在该隔离级别下，一个事务多次读同一个数据，在这个事务还没有结束前，其他事务不能访问该数据（包括了读写），这样就可以在同一个事务内两次读到的数据是一样的。可以防止脏读、不可重复读、第一类更新丢失，第二类更新丢失的问题，不过还是会出现幻读。

InnoDB为什么选用可重复读？

在InnoDB存储引擎中，使用可重复读可以解决脏读、不可重复读，而幻读也有可能发生，但是是可以避免的，通过加Next-Key Lock锁可以解决幻读问题。并且并非隔离级别越高越好，隔离级别越高的话，并发性能越低，所以在实际的开发中，需要根据业务场景进行选择事务的隔离级别。

串行化

串行化(Serializable)，这是最高的隔离级别。它要求事务序列化执行，事务只能一个接着一个的执行，不能并发执行。在这个级别，可以解决上面提到的所有并发问题，但是可能导致大量的超时现象和锁竞争，通常不会用这个隔离级别。

总结

隔离级别	脏读	不可重复读	幻读	第一类更新丢失	第二类更新丢失
读未提交	允许	允许	允许	不允许	允许
读已提交	不允许	允许	允许	不允许	允许
可重复读	不允许	不允许	允许	不允许	不允许
串行化	不允许	不允许	不允许	不允许	不允许

2.5 事务提交方式

显示提交

用COMMIT命令直接完成的提交为显式提交。

BEGIN;
INSERT ...
COMMIT; 

隐式提交

命令操作的事务是不可以回滚（ROLLBACK）的。这些命令主要是一些DDL（数据定义语言）和DCL（数据控制语言）：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT, QUIT，REVOKE，RENAME等

自动提交

插入、修改、删除等DML（数据管理语言）语句执行后，系统将自动进行提交。

insert,update,delete
SET AUTOCOMMIT=0; -- 禁止自动提交
SET AUTOCOMMIT=1; -- 开启自动提交

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

2.6 三种日志

redo log（重做日志）：恢复提交事务修改的页操作；通常是物理日志，记录的是页的物理修改操作。

undo log（回滚日志）：回滚记录到某个特定版本；通常是逻辑日志，根据每行记录进行记录。

bin log（二进制日志）：用来进行Point-In-Time(PIT)的恢复及主从复制环境的建立。

四种特性如何保证？

redo log用来保证事务的原子性和持久性。undo log用来保证事务的一致性。而隔离性是通过锁实现的。

bin log和redo log的区别？

1. 重做日志是在InnoDB存储引擎层产生的，而二进制日志是在MySQL数据库上层产生的，二进制日志不仅仅针对InnoDB存储引擎，任何存储引擎都会产生二进制日志。

2. 两种日志的记录内容形式不同。二进制日志是一种逻辑日志，记录的是SQL语句；而InnoDB存储引擎层面的重做日志是物