MySQL总结

1、初始数据库

1.1 什么是数据库

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

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

1.2 数据库分类

关系型数据库（SQL）

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

非关系型数据库（NoSQL）

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

1.3 数据库管理系统（DBMS）

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

DDL 数据库定义语言

CREATE	创建
ALTER	修改
DROP	删除
TRUNCATE 删除
1
2
3
4

DML 数据库操作语言

INSERT  插入
UPDATE	更新
DELETE	删除
1
2
3

delete 和 truncate 区别

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

DQL 数据库查询语言

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

DCL 数据库控制语言

GRANT 授权
ROLLBACK 回滚
COMMIT 提交
1
2
3

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; 
1
2
3

隐式提交

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

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

自动提交

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

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

在 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存储引擎层面的重做日志是物