MySQL 事务、视图、索引
一、事务（Transaction）
1.1 什么是事务？
SQL中，事务是指将一系列数据操作捆绑成为一个整体进行统一管理。
如果一个事务执行成功，该事务中进行的所有数据均会提交，称为数据库中的永久组成部分；
如果事务执行时遇到错误切必须取消or回滚，则数据将全部恢复到操作前的状态，所有更改会被清除。
在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。
1.2 事务的ACID属性
原子性（Atomicity）：事务中的所有元素必须作为一个整体提交或回滚。
一致性（Consistency）：事务完成时，数据必须处于一致状态。在事务进行中，可能会不一致。如转账操作中的金额总量。
隔离性（Isolation）：对数据进行修改的所有并发事务是彼此隔离的，即事务是独立的。
持久性（Durability）：指不管系统是否发生了故障，事务处理的结果都是永久的。就是说一旦事务被提交，事务的效果会被永久地保留在数据库中。
1.3 如何执行事务
MySQL的默认存储引擎InnoDB支持事务，InnoDB主要通过UNDO和REDO日志实现
UNDO日志：复制事务执行前的数据，用于在事务发生异常时回滚数据；
REDO日志：记录在事务执行中，每条对数据进行更新的操作，当事务提交时，内容会一起被刷新到磁盘。
1.4 事务的执行过程
MySQL的默认设置下，每句SQL语句就是一个事务，即执行SQL语句后自动提交。
（1）开始时，开启事务 or 关闭默认的自动提交：
begin;
/*或者*/
start transaction;
这两个语句显示地标记一个事务的起始点.
（2）提交事务
commit;
这个语句标志一个事务会成功提交，数据保存到数据库的数据文件中，并释放连接时占用的资源。
（3）回滚事务
rollback；
取消自事务起始点到该语句的所有数据更新操作，将数据状态回滚到事务开始前，并释放事务控制的资源。
/*银行转账实例*/
use mybank;
set names gbk;
begin;
update bank set currentMoney=currentMoney-500
where customerName='张三';
/*如果在这里发现张三的账户中余额不足500，就需要rollback*/
update bank set currentMoney=currentMoney+500
where customerName='李四';
commit;
1.5 MySQL中事务的隔离级别
MySQL中事务的默认隔离级别是repeatable-read（可重读）级别，意味着会话B中如果未关闭自动提交，在会话A中执行事务未结束时（没有commit或者rollback）,会话B只能读取到未提交的数据。
1.6 通过关闭自动提交执行事务
/*关闭自动提交*/
use mybank;
set names gbk;
set autocommit=0;
/*一个事务开始，假设操作没有问题*/
update bank set currentMoney=currentMoney-500
where customerName='张三';
update bank set currentMoney=currentMoney+500
where customerName='李四';
commit;
/*一个事务已经结束，这是下一个事务的起点*/
/*假设新的事务中，出现了问题，张三余额不足啦！*/
update bank set currentMoney=currentMoney-500
where customerName='张三';
rollback;
set autocommit=1;
1.7 编写事务的原则
- 事务尽可能简短，较大的事务占用的资源很多；
- 事务中访问的数据量尽量少，这样事务之间对资源的争夺就少；
- 查询数据不要使用事务；
- 事务处理的过程中尽量不要出现等待用户输入的操作。
二、视图
2.1 为什么使用视图？
安全——不希望用户看到整个数据库的结构，所以对不同权限的用户使用不同的视图隐藏必要数据；
方便——让用户更容易获得、理解数据；
2.2 什么是视图？
视图是一种查看数据库中一个或多个表中数据的方法，是保存在数据库中的select查询。
视图是一种虚拟表，它的行和列来自查询中引用的表，在执行时，它直接显示来自表中的数据。
2.3 视图常用来进行下面三种操作
- 筛选表中的行；
- 防止未经许可的用户访问敏感数据；
- 将多个物理数据表，抽象为一个逻辑数据表。
2.4 创建、查看视图
/*创建视图*/
create view view_Name as <select子句>;
/*如果数据库中已经存在同名视图，先删除*/
drop view if exists view_student;
create view view_student as
select studentNo,studentName,phone from student;
/*查看视图*/
select * from view_student;
2.5 使用视图的注意事项
- 每个视图中可以使用多个表
- 与查询相似，一个视图可以嵌套另一个视图，但最好不要超过三层；
- 对视图数据进行添加、更新和删除操作直接影响表中的数据；
- 当数据来自多个表时，不允许添加和删除数据。
三、索引——大大提高数据库的检索速度
3.1 什么是索引
索引提供指针以指向存储在表中指定列的数据值，再根据指定的排序次序排列这些指针。
数据库使用指针的方式与使用书的目录很相似，通过搜索索引找到特定的值，再跟随指针到达包含该值的行。
索引是创建在数据库表对象上的，由表中的一个字段 or 多个字段生成的键组成。
这些键存储在数据结构（B-树或哈希表）中，因此根据索引的存储类型，B树索引和哈希索引。
InnoDB和MyISAM存储引擎支持B-树索引。
3.2 索引分类
（1）普通索引
允许在定义索引的列中插入重复值和空值，唯一任务是加快对数据的访问速度。
因此，应该只为那些最常出现在查询条件或排序条件中的数据列创建索引。
（2）唯一索引
唯一索引不允许两行具有相同的索引值。
若数据中存在重复的键值，一般情况下，所属数据库是不允许创建唯一索引；
若已经创建了唯一索引，当新数据使得表中的键值重复时，数据库也拒绝接受此数据。
创建了唯一索引的列允许有空值。
（3）主键索引
在数据库关系图中为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。
主键索引要求主键中的每个值是非空、唯一的，当在查询中使用主键索引时，它还允许快速访问数据。
（4）复合索引
创建索引时，可以将多个列组合作为索引。
需要注意的是，只有在查询中使用了组合索引最左边的字段时，索引才会被使用。
（5）全文索引
全文索引的作用是在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。
全文索引可以在char、varchar或text类型的列上创建，主要用于在大量文本文字中搜索字符串，此时使用全文索引的效率将大大高于使用SQL的like关键字的效率。
（6）空间索引
对空间数据类型的列建立的索引，如geometry、point等。
创建空间索引的列，必须为not null，且只能在存储引擎为MyISAM的表中创建。
3.3 创建、删除索引
在已存在的表上添加索引的基本语法：
/*创建索引*/
/*length为索引长度，只有字符串类型才能指定索引长度*/
create [unique|fulltext|spatial|] index index_name
on tableName(colum_Name[length]...);
/*删除索引*/
drop index tableName.index_name;
删除表，该表的所有索引将同时被删除；
删除表中的列，如果要删除的列为索引的组成部分，则该列也会从索引中删除；
如果组成索引的列都被删除，则整个索引都将被删除。
3.4 选择建立索引的列
- 频繁搜索的列；
- 经常用作查询的列；
- 经常排序、分组的列；
- 经常用作连接的列（主键/外键）
不要使用下面的列创建索引：
- 仅包含几个不同值的列；
- 表中仅包含几行，因为为小型表创建索引并不实用；
3.5 一点经验
查询时减少使用*返回全部列，不要返回不需要的列；
索引应该尽量小，在字节数小的列上建立索引；
where子句中有多个条件表达式时，包含索引的表达式应置于其他表达式之前；
避免在order by子句中使用表达式；
根据业务数据发生频率，定期重新生成或重新组织索引，进行碎片整理。
3.6 查看索引
/*查看已创建的索引*/
show index from tableName;
/*查看学生表中的索引*/
show index from `student`\G;
在查询语句后面加“\G”，表示结果将按列排列。当表中列较多，需要看表中各列中的值时非常有用。
在存储类型为InnoDB的表中，经常使用唯一索引、普通索引、组合索引来提高查询效率。
