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

一、事务

• 事务（TRANSACTION）是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库操作命令要么都执行，要么都不执行。
• 事务是一个不可分割的工作逻辑单元 。

二、事务的特性

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

原子性（Atomicity）

• 事务的各步操作是不可分的(原子的)，要么都执行，要么都不执行

一致性（Consistency）

• 当事务完成时，数据必须处于一致状态

隔离性（Isolation）

• 并发事务之间彼此隔离、独立，它不应以任何方式依赖于或影响其他事务

持久性（Durability）

• 事务完成后,它对数据库的修改被永久保持

三、MySQL事务处理

MySQL中支持事务的存储引擎有InnoDB和BDB。
方法1：用 BEGIN, COMMIT,ROLLBACK来实现。

• BEGIN ; #开始事务，显式地标记一个事务的起始点
• COMMIT ; #提交事务，标志一个事务成功提交。
• ROLLBACK; #回滚事务，清除自事务起始点至该语句所做的所有数据更新，将数据状态回滚到事务开始前，并释放由事务控制的资源。
  方法2：直接用 SET 来改变 MySQL 的自动提交模式。
• 默认情况下，每条单独的SQL语句视为一个事务
• 关闭默认提交状态后，可手动开启、关闭事务
• SET autocommit = 0|1;
• 值为0：关闭自动提交
• 值为1：开启自动提交

四、视图

视图是一张虚拟表，表示一张表的部分数据或多张表的综合数据。

• 其结构和数据是建立在对表的查询基础上
• 视图中不存放数据，数据存放在视图所引用的原始表中
  一个原始表，根据不同用户的不同需求，可以创建不同的视图
  视图的用途
• 筛选表中的行
• 防止未经许可的用户访问敏感数据
• 将多个物理数据库表抽象为一个逻辑数据库表

五、管理视图

1、使用SQL语句创建视图

CREATE VIEW view_name  
AS
<SELECT 语句>;

2、使用SQL语句查看视图

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

3、使用SQL语句删除视图

DROP VIEW [IF EXISTS] view_name;

六、使用视图的注意事项

视图中可以使用多个表

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

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

七、索引

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

• 大大提高数据库的检索速度
• 改善数据库性能
  MySQL索引按存储类型分类
• B-树索引：InnoDB、MyISAM均支持
• 哈希索引

八、常见索引类型

普通索引

• 基本索引类型
• 允许在定义索引的列中插入重复值和空值

唯一索引

• 索引列数据不重复
• 允许有空值，创建唯一约束时自动创建唯一索引

主键索引

• 主键列中的每个值是非空、唯一的
• 一个主键将自动创建主键索引

复合索引

• 将多个列组合作为索引

全文索引

• 支持值的全文查找
• 允许重复值和空值

空间索引

• 对空间数据类型的列建立的索引

九、管理索引

使用SQL语句创建索引

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

使用SQL语句删除视图

DROP  INDEX index_name ON table_name;

十、创建索引的指导原则

按照下列标准选择建立索引的列

• 频繁搜索的列
• 经常用作查询选择的列
• 经常排序、分组的列
• 经常用作连接的列（主键/外键）

请不要使用下面的列创建索引

• 仅包含几个不同值的列
• 表中仅包含几行

十一、优化SQL语句的意识

查询时减少使用*返回全部列，不要返回不需要的列；
索引应该尽量小，在字节数小的列上建立索引；
WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前；
避免在ORDER BY子句中使用表达式。
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理。

十二、MySQL的备份

数据库备份必要性

• 保证重要数据不丢失
• 数据转移

MySQL数据库备份方法

• 数据库管理工具，如SQLyog
• mysqldump备份工具
• 直接拷贝数据库文件和相关配置文件

mysql –u root –p  dbname >  <地址>;

十三、MySQL的恢复

MySQL数据库恢复方法

• 数据库管理工具，如SQLyog
• mysql命令

mysql –u root –p  dbname  <  <地址>;