文章目录
- 一、 MySQL 事务
- 1.1 数据库三大范式
- 1.2 数据库三大范式的特点
- 1.3 事务的概念
- 二、 事务的ACID 原则与执行语句
- 2.1 事务的ACID原则
- 2.2 事务控制语句
- 2.3 MYSQL 事务处理方法
- 三、 事务操作
- 验证rollback
- 直接用 SET 来改变 MySQL 的自动提交模式
- 一个存档点
- 多个存档点
- 四、 MySQL 存储引擎
- 4.1 MyISAM 存储引擎
- 4.2 InnoDB 存储引擎
- 4.3 两种引擎的区别
- 4.4 两种引擎的使用选择
- 4.5 修改默认的存储引擎
一、 MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如在我们安装某些软件的时候，如果我们安装到一半突然有事，取消了安装，有的软件就会将之前安装的所有信息全部清除掉，恢复到没有安装的状态，实现这种操作的就是事务。
1.1 数据库三大范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
-
第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
-
第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
-
第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.
注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性
1.2 数据库三大范式的特点
第一范式
1、每一列属性都是不可再分的属性值，确保每一列的原子性
2、两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据。
第二范式
每一行的数据只能与其中一列相关，即一行数据只做一件事。只要数据列中出现数据重复，就要把表拆分开来。
第三范式
数据不能存在传递关系，即没个属性都跟主键有直接关系而不是间接关系。像：a–>b–>c 属性之间含有这样的关系，是不符合第三范式的。
比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）这样一个表结构，就存在上述关系。 学号–> 所在院校 --> (院校地址，院校电话)这样的表结构，我们应该拆开来，如下。
（学号，姓名，年龄，性别，所在院校）–（所在院校，院校地址，院校电话）
1.3 事务的概念
-
是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
-
是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
-
适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
-
通过事务的整体性以保证数据的一致性
二、 事务的ACID 原则与执行语句
2.1 事务的ACID原则
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句。
一般来说，事务是必须满足 4 个条件（ACID）：原子性（Atomicity，或称不可分割性）、 一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。 - 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不 会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样；
- 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作；
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包 括读未提交（Readuncommitted）、读提交（readcommitted）、可重复读（repeatable read）和串行化（Serializable）；
- 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
2.2 事务控制语句
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务必须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SETAUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
但是要记住BEGIN与COMMIT搭配使用，SETAUTOCOMMIT=0与SETAUTOCOMMIT=1搭配使用。
- BEGIN 或 STARTTRANSACTION：显式地开启一个事务；
- COMMIT：也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改变为永久性的；
- ROLLBACK：又可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINTidentifier：SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以 有多个 SAVEPOINT；
- RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACKTO identifier：把事务回滚到标记点；
- SETTRANSACTION：用来设置事务的隔离级别。 InnoDB 存储引擎提供事务的隔离级 别 有 READ UNCOMMITTED 、 READ COMMITTED 、 REPEATABLE READ 和SERIALIZABLE。
2.3 MYSQL 事务处理方法
MYSQL 事务处理主要有两种方法：
（1）用 BEGIN, ROLLBACK, COMMIT 来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
（2）直接用 SET 来改变 MySQL 的自动提交模式 - SETAUTOCOMMIT=0 禁止自动提交
- SETAUTOCOMMIT=1 开启自动提交
三、 事务操作
查看当前数据库存在的部分库与表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| home |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> use company;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| info |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
+----+----------+---------+
4 rows in set (0.00 sec)
验证rollback
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values (5,'laoda','gz');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
+----+----------+---------+
5 rows in set (0.00 sec)
mysql> rollback; ##进行回滚操作，会直接回滚到begin之前
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
+----+----------+---------+
4 rows in set (0.00 sec)
然后验证rollback命令是不是会自动结束事务
mysql> insert into info values (5,'laoda','gz');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
+----+----------+---------+
5 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
+----+----------+---------+
5 rows in set (0.00 sec)
验证结果是rollback命令会自动结束事务
直接用 SET 来改变 MySQL 的自动提交模式
mysql> set autocommit=0
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values (6,'laoer','yn');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
+----+----------+---------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
+----+----------+---------+
5 rows in set (0.00 sec)
mysql> set autocommit=0
-> ;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values (6,'laoer','yn');
Query OK, 1 row affected (0.00 sec)
mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
+----+----------+---------+
6 rows in set (0.00 sec)
一个存档点
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values (7,'laosan','sc');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
+----+----------+---------+
7 rows in set (0.00 sec)
mysql> insert into info values (8,'laosi','cq');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
| 8 | laosi | cq |
+----+----------+---------+
8 rows in set (0.00 sec)
mysql> rollback to a; ##回滚到存档点a，并不会结束事务
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
+----+----------+---------+
7 rows in set (0.00 sec)
多个存档点
在多个存档点的情况下，如果回滚到第一个回滚点，就不能再往后回滚了。
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values (9,'laowu','cq');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info values (10,'laoliu','hd');
Query OK, 1 row affected (0.00 sec)
mysql> savepoint b;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
| 8 | laosi | cq |
| 9 | laowu | cq |
| 10 | laoliu | hd |
+----+----------+---------+
10 rows in set (0.00 sec)
mysql> insert into info values (11,'laoqi','hd');
Query OK, 1 row affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
| 8 | laosi | cq |
| 9 | laowu | cq |
| 10 | laoliu | hd |
| 11 | laoqi | hd |
+----+----------+---------+
11 rows in set (0.00 sec)
mysql> rollback to b;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
| 8 | laosi | cq |
| 9 | laowu | cq |
| 10 | laoliu | hd |
+----+----------+---------+
10 rows in set (0.00 sec)
mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
| 3 | wangwu | nj |
| 4 | zhaoliu | nj |
| 5 | laoda | gz |
| 6 | laoer | yn |
| 7 | laosan | sc |
| 8 | laosi | cq |
| 9 | laowu | cq |
+----+----------+---------+
9 rows in set (0.00 sec)
mysql> rollback to b;
ERROR 1305 (42000): SAVEPOINT b does not exist
四、 MySQL 存储引擎
MySQL 中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的、不同的功能和能力。通过选择不同的技术，能够获得额外的速度或者功能，从而改善应用的整体性能。
这些不同的技术以及配套的相关功能在 MySQL 中被称作存储引擎(也称作表类型)。 MySQL 默认配置了许多不同的存储引擎，可以预先设置或者在 MySQL 服务器中启用。选择适用于服务器、数据库和表格的存储引擎，可以在存储信息、检索数据时，提供最大的灵活性。
关系数据库表是用于存储和组织信息的数据结构，可以将表理解为由行和列组成的表 格，类似于 Excel 电子表格的形式。有的表简单、有的表复杂、有的表根本不用来存储任何长期数据、有的表读取时非常快，但是插入数据时却很差。在实际开发过程中，就可能需要各种各样的表，不同的表就意味着存储不同类型的数据，数据的处理上也会存在着差异。那 么，对于 MySQL 来说，它提供了很多种类型的存储引擎（或者说不同的表类型），根据对 数据处理的需求，可以选择不同的存储引擎，从而最大限度的利用 MySQL 强大的功能。
4.1 MyISAM 存储引擎
MyISAM 存储引擎不支持事务，也不支持外键，特点是访问速度快，对事务完整性没有 要求，以 SELECt、INSERT 为主的应用基本都可以使用这个引擎来创建表。
每个 MyISAM 表在磁盘上存储成 3 个文件，其中文件名和表名都相同，但是扩展名分别为：
- frm(存储表定义)
- MYD(MYData，存储数据)
- MYI(MYIndex，存储索引)
4.2 InnoDB 存储引擎
InnoDB 是一个健壮的事务型存储引擎，这种存储引擎已经被很多互联网公司使用，为 用户操作非常大的数据存储提供了一个强大的解决方案。MySQL 从 5.5.5 版本开始，默认 的存储引擎为 InnoDB。InnoDB 存储引擎还引入了行级锁定和外键约束，在以下场景中使 用 InnoDB 存储引擎是最理想的选择：
- 更新密集的表：InnoDB 存储引擎特别适合处理多重并发的更新请求。
- 事务：InnoDB 存储引擎是支持事务的标准 MySQL 存储引擎。
- 自动灾难恢复：与其它存储引擎不同，InnoDB 表能够自动从灾难中恢复。
- 外键约束：MySQL 支持外键的存储引擎只有 InnoDB。
- 支持自动增加列 AUTO_INCREMENT 属性。
4.3 两种引擎的区别
- InnoDB 支持事务，MyISAM 不支持，这一点是非常重要的。事务是一种高级的处理方 式，如对一些表中的列进行增删改的过程中只要哪个出错还可以回滚还原，而 MyISAM就不可以。
- MyISAM 适合查询、插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用。
- InnoDB 支持外键，MyISAM 不支持。
- 从 MySQL5.5.5 以后，InnoDB 是默认引擎。
- MySQL 从 5.6 版本开始 InnoDB 引擎才支持 FULLTEXT 类型的索引。
- InnoDB 中不保存表的行数，如 selectcount()fromtable 时，InnoDB 需要扫描一遍整 个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。需要注意的 是，当 count()语句包含 where 条件时 MyISAM 也需要扫描整个表。
- 对于自增长的字段， InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立组合索引。
- 清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表。
- InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’;）。
4.4 两种引擎的使用选择
（1）如果应用程序一定要使用事务，毫无疑问要选择 InnoDB 引擎。但要注意， InnoDB 的行级锁是有条件的。在 where 条件没有使用主键时，照样会锁全表。比如 DELETEFROM mytable 这样的删除语句。
（2）如果应用程序对查询性能要求较高，就要使用 MyISAM 了。MyISAM 索引和数据是分开的，而且其索引是压缩的， 可以更好地利用内存。所以它的查询性能明显优于InnoDB。压缩后的索引也能节约一些磁盘空间。
4.5 修改默认的存储引擎
四种方法如下：
（1）通过 alter table 修改。
MySQL>alter table user_infoengine=MyISAM;
（2）通过修改 my.cnf，指定默认存储引擎并重启服务。
[root@Mysql /]#vim my.cnf default-storage-engine=InnoDB
（3）通过 create table 创建表时指定存储引擎。
MySQL>create table engine home(id int)engine=MyISAM;
（4）通过 Mysql_convert_table_format 转化存储引擎。
[root@Mysql/]# yum-y install perl-DBI perl-DBD-MySQL [root@Mysql/]#/usr/local/mysql/bin/mysql_convert_table_format --user=root--password='abc123'--sock=/tmp/mysql.sockauth
