第二十七篇（事务、试图、索引、备份和恢复）

事务、试图、索引、备份和恢复

1.掌握MySQL的事务处理

1.1 业务

（例如：请假、报销、办卡、入学、登录、注册...）

（一套固定的顺序流程）

银行转账：“ A ” 转账给 “B ” 500元

		1.将 A 的余额扣除500

		2.将 B 的余额增加500

1.将A的余额扣除500
UPDATE
	bank
SET money = money - 500
WHERE
	`name` = 'A';

2.将B的余额增加500
UPDATE
	bank
SET money = money + 500
WHERE
	`name` = 'B';

1.2 事务的概述

事务：一系列操作要么都执行成功！要么都失败！

RDB安全、事务支持好
NoSQL事务一般并不是太强（Redis是假事务）

MySQL：存储引擎 INNODB（默认、高频采用）、 BDB才支持事务

MyISAM也是常有存储引擎，但是它在事务等方面不支持，它的查询性能比较强，也支持全文索引。

其实在使用了支持事务的引擎之后，事务就已经在使用了！因为MySQL中默认在佛那个提交事务！

1.3 事务的使用

# 1.自行开启事务BEGIN  START TRANSACTION(tx)
BEGIN;

1.将A的余额扣除500
UPDATE
	bank
SET money = money - 500
WHERE
	`name` = 'A';

2.将B的余额增加500
UPDATE
	bank
SET money = money + 500
WHERE
	`name` = 'B';
	
# 如果没有出现异常，则应该提交事务
COMMIT;

# 如果出现异常，事务应进行回滚
ROLLBACK;

# 2.通过关闭自动提交来使用事务， SET autocommit = 0(关闭) | 1(开启);
SET autocommit = 0;
SET autocommit = 1;

2.理解MySQL的ACID原则（事务的特点）

四大特性：原子性、一致性、隔离性、持久性。

1. 原子性：（在化学上讲究原子为最小单位，不可再分）事务是不可分割的！要么都成功，要么都失败。
2. 一致性：事务执行前和事务执行后的数据总量不变！例如转账
3. 持久性：事务在执行完毕之后必须能够将数据持久保存到数据库中！
4. 隔离性：多个事务之间应该彼此独立，不受影响。

事务并发引发的问题可能有：脏读、不可重复读、幻读/虚读

• 脏读：一个事务读取到了另外一个事务未提交的数据！（基本不允许，因为事务有可能回滚。）
• 不可重复读：一个事务在执行期间读取了多次内容，结果不一样。主要针对另一个事务对同样的内容进行了修改操作（UPDATE）
• 幻读/虚读：一个事务在执行全表性操作时，另一个事务进行了插入操作，那么第一个事务就会发现还有未执行完的内容。（INSERT）

（MySQL）出现了四大隔离级别：读未提交、读已提交、可重复读、串行化

• 读未提交：任何事务并发问题都无法解决！（基本不采用）
• 读已提交：能解决脏读问题！（Oracle数据库默认）
• 可重复读：可以解决脏读和不可重复读问题！（MySQL默认）
• 串行化：最严格的隔离级别！可以解决所有的并发问题！（基本不采用）

3.了解视图的使用

视图：它是一种虚拟表，可以用来降低查询复杂度！也可以适用于不同群体对数据的不同要求。

# 视图创建

CREATE VIEW stu_result_list AS
			SELECT
				stu.stuName,sub.subjectName,r.result,r.examDate
			FROM 
				result r,student stu,`subject` sub
			WHERE
				r.stuId = stu.stuId AND r.subjectId = sub.subjectId
			ORDER BY 
				r.result DESC , r.examDate DESC;

# 视图的使用【视图是一张虚拟表，你对他的改动会最终影响到真实数据物理表！】

SELECT * FROM stu_result_list;

# 视图删除

DROP VIEW stu_result_list;

视图注意事项：

1. 视图仅仅作为查询使用即可！不要进行修改等操作！
2. 视图也可以进行嵌套使用！
3. 视图的数据是来自于对应的物理表！

4.掌握数据库索引的创建

索引的概述：将一个列或者一组列进行相关组合排序使用！

索引：提升查询性能！

原理：类似于字典前的目录页。

索引的分类：

• 普通索引：基本索引类型，允许在定义索引的列中插入重复值得空值。
• 唯一索引：索引列数据不重复，允许有空值。
• 主键索引：主键列中的每个值是非空、唯一的，一个主键将自动创建主键索引。
• 复合索引：将多个列组合作为索引。
• 全文索引：支持值的全文查找，允许重复值和空值。
• 空间索引：对空间数据类型的列建立的索引。

常用的简短概述：

普通索引：提升查询性能。

唯一索引：除了提升查询性能，还可以保证列/字段值唯一。

主键索引：除了提升查询性能，还可以保证主键的特点，非空且唯一。

符合索引：可以将多个字段列组合提升查询性能。

全文索引：它只能在MyISAM存储引擎中使用，提升大数据量情况下对文本查找的性能。

# 创建一个普通索引
CREATE INDEX index_stu_name ON student(stuName);

# 创建外键和主键和唯一键都会产生对应的索引

# 删除索引
DROP INDEX index_stu_name ON student;

# 查看索引列表
SHOW INDEX FROM student;

索引并不是一味的创建即可提升性能！因为索引也会占用存储空间！而且索引列会在操作时进行索引验证…

需要谨慎的设计和使用！

设计：

【建议】

1. 频繁搜索的列！

2. 频繁用作于查询选择的列！

3. 经常排序分组的列！

4. 经常用于连接的列！

【不建议】

5. 仅包含几个不同值的列！例如：性别、学历…

6. 在表中仅包含几行的列！数据不多！

使用：

1. 尽量不要使用 * 来查找数据！
2. 索引列应该尽量小，在字节数小的列上建立！
3. 如果 where 中有多个条件表达式，那么索引列的条件表达式要放在前面。
4. 避免对索引进行计算表达式！

5.了解MySQL数据库的备份和恢复

# 备份数据库：

mysqldump [参数选项] -u用户名 -p密码 -h主机地址 要备份的数据库名[要备份的数据表列表] > 输出的地址 
参数选项：
	-add-drop-table  表示在创建表语句前添加drop table语句，可以通过-skip-add..取消
	--add-locks 表示在插入数据前后加上锁表和解锁语句，可以防止插入出错！
	-t(-table 减去table) 导出数据时不添加数据结构创建的语句 CREATE TABLE
	-c(complete 完整的) 可以帮助我们在导出数据时， 添加语句可以加上字段列表。
	-d (-data 减去data) 导出时，只要表结构不要数据。

# 恢复数据库：
mysql -u用户名 -p密码 -h主机地址 要恢复到的数据库名 < 数据脚本地址
在登录mysql服务器之后