MySQL基础部分总结3

基础部分总结3"/>

MySQL基础部分总结3

一. 事务原理

一个事务是由一条或者多条对数据库操作的SQL语句所组成的一个不可分割的单元，只有当事务中的所有操作都正常执行完了，整个事务才会被提交给数据库；如果有部分事务处理失败，那么事务就要回退到最初的状态，因此，事务要么全部执行成功，要么全部失败。
事务的实现原理：
ACID四个特性的实现原理：（A）原子性原理+©一致性原理+(I)隔离性原理+(D)持久性原理
学习这些原理之前我们要先知道MySQL的Buffer Pool机制。
InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证，所以我们需要使用mysql中的日志来保证数据持久性。
持久性实现原理：redo log 日志
当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
原子性实现原理：undo log 日志 rollback; commit;
undo log中会记录一条和你执行的sql语句意义相反的sql语句，当你rollback时就执行undo log中的sql语句。
实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。
Mysql隔离性的实现原理：锁机制 + MVCC机制：
Record Locks（记录锁）：在索引记录上加锁。
Gap Locks（间隙锁）：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。相当于Record Locks与Gap Locks的一个结合。
共享读锁和排他写锁：
 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的。
行锁和表锁：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
MVCC机制：
所谓的mvcc机制就是尽量的不去使用这些锁，一旦这些锁加上之后事务与事务之间就变成了完全的串行执行，在隔离级别中串行化其实就是这样做的。这里又要涉及到我们之前所学的乐观锁和悲观锁。
InnoDB的一致性的非锁定读就是通过在MVCC实现的，Mysql的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都同时实现了多版本并发控制（MVCC）。MVCC的实现，是通过保存数据在某一个时间点的快照来实现的，，每一个事务只操作自己版本下的数据。因此每一个事务无论执行多长时间看到的数据，都是一样的。所以MVCC实现可重复读。
把这些机制了解清楚之后，我们来说一下隔离性实现的原理。首先隔离性一共分了4种隔离级别。分别是：未提交读，已提交读，可重复读，和序列化。
未提交读（Read uncommitted）： 这种级别，数据库一般都不会用，这里就不讨论了。
已提交读（Read committed）： 在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的（Record Locks）。那么已提交读时如何解决脏读问题的？这个就利用了mvcc机制。只要没有commit那么修改就是自己版本下的数据。
可重复读（Repeatable read）： 数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁（Record Locks）的+Mvcc机制，即使commit操作的也是自己版本数据。
序列化（Serializable）：所有锁都加了；没有mvcc机制；效率最低。这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，（加的锁就是Next-Key Locks）实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。由于InnoDB引擎加的是行锁，并且是加在索引记录上，所以为了不产生幻读还会加间隙锁。
一致性实现原理：持久性+隔离性+原子性共同实现了数据的一致性。

二.事务操作

前提：存在表和数据，存储引擎为InnoDB
开启事务 ：相当于是自己控制提交的时间。
由于Mysql在敲下sql语句按下回车之后会自动提交所以第一步开启事务实际上就是关闭自动提交机制。
1.可通过SELECT @@AUTOCOMMIT; 查看MySQL是否已经关闭自动提交。
mysql> select @@autocommit;
±-------------+
| @@autocommit |
±-------------+
| 1 | # 0表示手动提交事务 1表示自动提交事务
±-------------+
1 row in set (0.01 sec)
2.如果未关闭，可使用SET AUTOCOMMIT = 0操作;设置事务提交方式为手动提交事务
3.BEGIN 或 START TRANSACTION 显式地开启一个事务；
4.COMMIT;提交事务，结束事务的一种方式；
5.ROLLBACK;回滚到初始的位置，结束事务的另一种方式；
6.SAVEPOINT point1;设置一个名为point1的保存点，可回滚到此处；
7. set session transaction isolation level READ COMMITTED; 设置事务的隔离级别。
如下4种分别对应，我们之前讲过的4种隔离级别：Read uncommitted 、Read committed 、Repeatable read 、 Serializable 序列化。
8.查询事务的隔离级别
mysql> select @@tx_isolation;
±----------------+
| @@tx_isolation |
±----------------+
| REPEATABLE-READ | // MySQL默认工作在“可重复读”的隔离级别 重启恢复默认
±----------------+
1 row in set (0.00 sec)
不同隔离级别的原理：
隔离级别 脏读 不可重复读 幻读
未提交读 可以 可以 可以 （不讨论）
已提交读 不可以 可以 可以 原理 （完成）
可重复读 不可以 不可以 可以 原理 （完成）
串行化 不可以 不可以 不可以 原理 （完成）

三.JDBC操作

JDBC（Java DataBase Connectivity）是Java和数据库之间的一个桥梁，是一个规范而不是一个实现。各种不同类型的数据库都依这种规范有相应的实现，都是由java类和接口组成。
这里说的是通过jdbc连接MySQL数据库。
使用JDBC的步骤：jdbc 只是一个规范
(1) 导入jdbc的Mysql实现依赖
(2）参数配置
jdbcUrl ：唯一确定数据库; 以及编码形式中文
jdbc:mysql://127.0.0.1:3306/数据库名?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
jdbc:mysql ：和mysql建立连接时使用的协议
127.0.0.1:3306 ：mysql服务器所在的ip地址和端口号
ip地址：唯一的确定mysql服务器运行的主机 127.0.0.1 当前主机
端口号：能够确定mysql服务器是这台主机的哪一个进程 3306 mysql默认端口
IP 和 端口号帮助我们确定mysql服务器的位置。
(3）加载驱动 驱动加载好之后就可以使用jdbc了
(4）建立连接
(5）创建操作数据库对象 statement /PreparedStatement
(6) 使用statement /PreparedStatement对象，执行sql语句
当使用insert/delete/update 使用executeUpdate方法执行;当使用select 使用executeQuery方法执行;结构完全相同，仅仅只是参数值不同;
PreparedStatement 当执行多条同结构的sql语句时，PreparedStatement对象会先提交一个无参数的sql语句进进行编译。insert into test values(？,？);
同结构的sql语句执行多次，PreparedStatement对象的执行效率要比statment对象高的多。
原因：PreparedStatement对象对于同结构的sql只编译一次。而statment对象有几条sql
就需要执行几次。
一个结构的sql语句只执行一次，statment对象更好。原因是：statment对象执行sql时
编译和执行一次就完成。而PreparedStatement对象先编译无参数的sql，再提交参数
然后再执行。
综上：只有同结构的sql语句执行多次PreparedStatement对象的执行效率比statment对象高
如果执行一次statment对象更好。在实际操作中，往往使用preparedStatement，因为statement对象容易产生sql注入异常；
（7）关闭连接 避免资源的浪费

四.通过JDBC操作事务

（1）开启事务 connection.setAutoCommit(false);
（2)设置事务的隔离级别connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE); //如设置成序列化
建立连接并完成开启事务和设置隔离级别：

public void createConnection(){try {Class.forName(jdbcDriver)