MySQL中事务、视图、索引、备份和恢复

事务

什么是事务

• 事务（transaction）是作为单个逻辑工作单元执行的一系列操作。
• 多个操作作为一个整体向系统提交，要么都执行、要么都不执行。
• 事务是一个不可分割的工作逻辑单元。
• 事务主要用于处理操作量大，复杂度高的数据。

事务的特性

事务必须具备以下四个属性，简称为 ACID 属性。

-原子性（Atomicity）
事务是一个完整的操作，事务的各步操作是不可分的（原子的），要么都执行，要么都不执行，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态 ，就像这个事务从来没有执行过一样。

• 一致性（Consistency）
  当事务完成时，数据必须处于一致状态。

• 隔离性（Isolation）
  数据库允许多个并发事务同时对数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。并发事务之间彼此隔离、独立，它不应该以任何方式依赖于或影响其它事务。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性（Durability）
  事务完成后，它对数据库的修改被永远保持，即使系统故障也不会丢失。

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

事务控制语句

• BEGIN 或 START TRANSACTION 显示地开启一个事务。
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的。
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT。
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。
• ROLLBACK TO identifier 把事务回滚到标记点。
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MySQL中支持事务的存储引擎有InnoDB和BDB
MySQL事务处理主要有两种方法

1.用BEGIN、ROLLBACK、COMMIT来实现

• BEGIN 开始一个事务
• ROLLBACK 事务回滚
• COMMIT 事务确认

2.直接用 SET 来改变MySQL 的自动提交模式

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

关闭自动提交后，从下一条SQL语句开始则开启新事务，需使用COMMIT或ROLLBACK语句结束该事务

视图

为什么需要视图

• 不同的人员关注不同的数据
• 保证信息的安全性

什么是视图

• 视图是一张 虚拟表
  1.表示一张表的部分数据或多张表的综合数据
  2.其结构和数据是建立在对表的查询基础上

• 视图中不存放数据
  1.数据存放在视图所引用的原始表中

• 一个原始表，根据不同用户的不同需求，可以创建不同的视图

视图的用途

• 筛选表中的行
• 防止未经许可的用户访问敏感数据
• 降低数据库的复杂程度
• 将多个物理数据库抽象为一个逻辑数据库

如何创建视图

使用SQL语句创建视图

CREATE VIEW view_name
AS
<SELECT 语句>;

使用SQL语句删除视图

DROP VIEW [IF EXISTS] view_name;

使用SQL语句查看视图

SELECT 字段1,字段2,......FROM view_name;

使用视图注意事项

• 视图中可以使用多个表
• 一个视图可以嵌套另一个视图
• 对视图数据进行添加、更新和删除操作直接影响所引用表中的数据
• 当视图数据来自多个表时，不允许添加和删除数据

查看所有视图

USE information_schema;
SELECT * FROM views\G;

使用视图修改数据会有许多限制，一般在实际开发中视图仅用作查询

索引

什么是索引

• 索引是一种有效组合数据的方式，为快速查找到指定记录

• 作用
  1.大大提高数据库的检索速度
  2.改善数据库性能

• MySQL索引按存储类型分类
  1.B-树索引：InnoDB、MyISAM均支持
  2.哈希索引

常用的索引类型

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
ON table_name(column_name[length]...);

删除索引

DROP INDEX index_name 
ON table_name

删除表时，该表的所有索引同时会被删除

创建索引的指导原则

• 按照下列标准选择建立索引的列
  1.频繁搜索的列
  2.经常用作查询选择的列
  3.经常排序、分组的l列
  4.经常用作连接的列（主键 / 外键）

• 请不要使用下面的列创建索引
  1.仅包含几个不同值的列
  2.表中仅包含几行

• 经验
  1.查询时减少使用 * 返回全部列，不要返回不需要的列
  2.索引应该尽量小，在字节数小的列上建立索引
  3.WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其它表达式之前
  4.避免在ORDER BY子句中使用表达式

查看索引

SHOW INDEX FROM table_name;

Table：创建索引的表
Non_unique：索引是否非唯一
Key_name：索引的名称
Column_name：定义索引的列字段
Seq_in_index：该列在索引中的位置
Null：该列是否能为空值
Index_type：索引类型

备份

使用mysqldump命令备份数据库

mysqldump命令——MySQL一个常用的备份工具

• 将CREATE和INSERT INTO语句保存到文本文件
• 属于DOS命令

mysqldump [options] -u userName -h host -p password
dbname[tbname1[,tbname2......]]>filename.sql

注意：mysqldump是DOS系统下的命令，在使用时无须进入mysql命令行，否则将无法执行

常用的参数选项

恢复数据库

1. 方法一：使用mysql命令恢复数据库

mysql -u username -p password [dbname] <filename.sql
注意：
	1.mysql为DOS命令
	2.在执行该语句之前，必须在MySQL服务器中创建新数据库，如果不存在
	，恢复数据库过程将会报错

2. 方法二：使用source命令恢复数据库

source filename;
注意：
	1.登录MySQL服务器后使用
	2.执行该命令前，先创建并选择恢复后的目标数据库

导入 / 导出

导出

SELECT * FROM tablename
[WHERE contion]
INTO OUTFILE 'filename'[OPTION]

导入

LOAD DATA INFILE filename 
INTO TABLE tablename[OPTION]
注意：
	导入数据前应确保目标表已存在！

归纳