【mysql】事务、视图、索引

事务

事务（transaction）是作为单个逻辑工作单位执行的一系列操作，多个操作作为一个整体向系统提交，要么都执行、要么都不执行。

事务的ACID属性

原子性：(Atomicity)；

事务是一个完整的操作，事务的各步操作时不可分的（原子的），要么都执行、要么都不执行。

一致性(Consistency)：

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

隔离性（Isolation）:

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

持久性（Durability）：

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

创建事务

开始时，开启事务 or 关闭默认的自动提交：

begin; 
/*或者*/
start transaction;

提交事务：

这个语句标志一个事务会成功提交，数据保存到数据库的数据文件中，并释放连接时占用的资源。

commit

回滚事务:

取消自事务起始点到该语句的所有数据更新操作，将数据状态回滚到事务开始前，并释放事务控制的资源。

rollback

示例：

begin;
# sql语句
commit;

begin;
# sql语句
rollback;

脏读、幻读、不可重复读

脏读

一个事务读取到了另外一个事务没有提交的数据：

当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

• a事务对一个数据进行修改(没有提交事务)
• 此时b读取了这行数据拿到的是修改后的数据
• 而a在b读取数据后将事务进行了回滚，b得到的数据就是脏数据

不可重复读

在同一事务中，两次读取同一数据，得到内容不同：

在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的

• a事务读取了一条数据值为10，且操作为完成
• b事务中将这条数据修改为20
• a事务再之后操作再次读取数据发现数据变成了20

在修改事务完全提交之后才可以读取数据，则可以避免该问题。

幻读

同一事务中，用同样的操作读取两次，得到的记录数不相同：

例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

• a事务读取了数据总量10
• b事务中添加了一条数据
• a事务再之后操作再次读取数据发现数据总量变为了11

在操作事务完成数据处理之前，其他事务不可以添加新数据，则可避免该问题

事务的隔离级别

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读取未提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

隔离级别	脏读	不可重复读	幻读
Read Uncommitted	√	√	√
Read Committed	×	√	√
Repeatable Read	×	×	√
Serializable	×	×	×

视图

视图是一种虚拟表，它的行和列来自查询中引用的表，在执行时，它直接显示来自表中的数据，自己本身并不存储数据。它是保存在数据库中的select查询。

作用：

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

使用视图：

创建视图：

  create view view_Name as <select子句>;

删除视图：

drop view if exists view_Name;

查看视图

select * from view_Name;

注意事项

对视图数据进行添加、更新和删除操作直接影响所引用表中的数据

当视图数据来自多个表时，不允许添加和删除数据

一个视图可以嵌套另一个视图

视图中可以使用多个表

索引

索引是一种有效的组合数据的方式，为快速查找到指定记录，对复杂的查询语句进行优化，加速查询

索引的分类

普通索引：

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

唯一索引：

索引列中的值必须是唯一的，但是允许为空值。

主键索引：

是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：

组合索引：

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

全文索引：

全文索引可以在char、varchar或text类型的列上创建，主要用于在大量文本文字中搜索字符串，此时使用全文索引的效率将大大高于使用SQL的like关键字的效率。

使用索引

创建索引：

create [unique|fulltext|spatial] index index_name on tableName(colum_Name[length]);

在哪些列建立索引：

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

删除索引：

drop index tableName.index_name;

删除表，该表的所有索引将同时被删除
删除表中的列，如果要删除的列为索引的组成部分，则该列也会从索引中删除,#如果组成索引的列都被删除，则整个索引都将被删除。

查看索引：

show index from tableName；

索引未命中的情况

• 当使用了模糊查询并且以%开头时
• 如果列的数据类型是字符串，在查询时查询参数需要使用引号
• 没有查询条件时或者查询条件没有创建索引
• 查询条件中有or时，or条件的列不全有索引时
• 使用了not in、not exist时