MySQL入门知识（二）

7.事务

7.1 什么是事务

        事务是指将一组SQL语句放在同一批次进行处理，只有这些语句都正常运行才能运行，只要有一个SQL语句出错，则该批次内的所有SQL都会被取消执行。事务是一个原子操作，是一个最小执行单元，它可以甶一个或多个SQL语句组成。

7.2 事务的特性

        原子性：事务由原子的操作序列组成，所有操作要么全部成功，要么全部失败，不可能停滞在中间某个环节。事务在执行过程中如果出现错误，就会全部回滚，回到事务开始前的状态。

        一致性：事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。具体来说，就是保护性和不变性。例如：有五个账户，每个账户余额是100元，那么五个账户总额是500元，不管这五个账户之间发生多少次转账，五个账户的总金额一定是500元。此外，要么五个账户都处于事务执行前的状态，要么都处于执行后的状态。

        隔离性：多个用户并发访问数据库时，数据库为每个用户执行的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。即：为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

        持久性：一个事务一旦提交并执行成功，那么对数据库中数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

7.3 事务并发问题与隔离级别

        由于事务具有的隔离性，会产生一定的并发问题。根据不同的事务并发问题，我们用事务的隔离级别来解决它们。

7.3.1 事务并发问题

        事务并发问题包括：脏读、不可重复读和幻读。

        脏读：脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。例如，A给B转500元，B余额增加，但此时事务还没完成。如果此时有另外一个事务要求读取B的余额，读的就是转账后的余额，此时就发生了脏读，因为之前的事务还没完成，也就是说有可能发生回滚，那B的余额就不应该增加。

        不可重复读：不可重复读是指对于数据库中某个数据，一个事务范围内多次查询返回了不同的数据值，这是由于在多次查询之间，有其他事务修改了数据并进行了提交。

        幻读：幻读是指一个事务中执行两次完全相同的查询时，第二次查询所返回的结果集跟第一个查询不相同。与不可重复读的区别在于，不可重复读是对同一条记录，两次读取的值不同。而幻读是记录的增加或删除，导致两次相同条件获取的结果记录数不同。

7.3.2 隔离级别

        事务的隔离级别有四种，从上到下级别依次提高：读未提交、读已提交、可重复读和串行化。

        读未提交：可以读取到其他事务未提交的内容，这是最低的隔离级别，这个隔离级别下，前面提到的三种并发问题都有可能发生。

        读已提交：只能读取到其他事务已经提交的数据。这个隔离级别可以解决脏读问题。

        可重复读：可以保证整个事务过程中，对同数据的多次读取结果是相同的。这个级别可以解决脏读和不可重复读的问题。

        串行化：所有事务操作都依次顺序执行。这个级别会导致并发度下降，性能最差。不过这个级别可以解决前面提到的所有并发问题。

7.4 事务的分类

        事务可以分为五大类：扁平化事务、带有保存点的扁平化事务、链事务、嵌套事务和分布式事务。

        扁平化事务：在扁平事务中，所有的操作都在同一层次，这也是我们平时使用最多的一种事务。它的主要限制是不能提交或者回滚事务的某一部分，要么都成功，要么都回滚。

        带有保存点的扁平化事务：它允许事务在执行过程中回滚到较早的状态，而不是全部回滚。通过在事务中插入保存点，当操作失败后，可以选择回滚到最近的保存点处。

        链事务：它在事务提交时，会将必要的上下文隐式传递给下一个事务，当事务失败时就可以回滚到最近的事务。它有点类似于带有保存点的扁平化事务，不过，链事务只能回滚到最近的保存点，而带保存点的扁平化事务是可以回滚到任意的保存点。

        嵌套事务：由顶层事务和子事务构成，类似于树的结构。一般顶层事务负责逻辑管理，子事务负责具体的工作，子事务可以提交，但真正提交要等到父事务提交，如果上层事务回滚，那么所有的子事务都会回滚。

        分布式事务：是指分布式环境中的扁平化事务。

        savepoint关键字的使用：

        可以看到，我们插入了两次数据，分别是1和2，但由于savepoint设置了保存点，回滚到了保存点，所以最终test1表里只有第一次插入的数据。

7.5 MySQL的事务操作

       事务分为隐式事务和显式事务，是否开启隐式事务是由变量autocommit控制的。

        MySQL里，事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。

        显式事务是指需要手动开启、提交或回滚，由开发者自己控制的事务。

7.6 事务操作常见命令

        set autocommit：设置是否开启自动提交事务

        start transaction：开启事务

        start transaction read only：开启只读事务

        commit：提交事务

        rollback：回滚事务

        savepoint：设置保存点

        rollback to 保存点：回滚到某个保存点

8.索引

8.1 什么是索引        

        索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。

        当数据量庞大/某个字段很少出现DML操作/某字段经常出现在where语句中时，一般需要创建索引。

8.2 索引的优缺点

        优点：①提高数据查询速度；②提高数据检索的效率，降低数据库的IO成本；③通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗；④使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间；⑤加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义；⑥使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

        缺点：①会占据磁盘空间；②虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件；③维护索引需要消耗数据库资源；④如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果；⑤对于非常小的表，大部分情况下简单的全表扫描更高效。

8.3 索引的分类

8.3.1 逻辑分类

        主键索引：primary key，一张表只能有一个主键索引，不允许重复、不允许为 NULL；

        唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；

        普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

        全文索引：它查找的是文本中的关键词，主要用于全文检索。

8.3.2 物理分类

        分为聚簇索引和非聚簇索引。聚簇是为了提高某个属性(或属性组)的查询速度，把这个或这些属性(称为聚簇码)上具有相同值的元组集中存放在连续的物理块。

        聚簇索引不是单独的一种索引，而是一种数据储存方式，它依靠B+树实现，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。所以，聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据。

        非聚簇索引：数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

8.4 索引的基本操作

8.4.1 创建索引

8.4.2 删除索引

8.4.3 其他操作

8.5 其他（待更新）

9.视图

9.1 什么是视图

        视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据，所以它也称为虚拟表，本身不存储数据，而是按照某种指定的方式进行查询。

9.2 视图的相关操作

        首先创建两张表，一张为记录学生信息的表student，一张为记录学生分数排名的表student_score，并插入实例数据。

表student：

表student_score：

9.2.1 查看权限

9.2.2 创建视图

        此外，创建视图时，也可以用with check option来限定生成视图所需的记录。

9.2.3 查看视图信息

9.2.4 修改视图

9.2.5 更新视图

        当我更新视图2的分数信息（比如将纳西妲的分数改成480），student_score表里纳西妲的分数也发生了改变：

        需要注意的是，一般来说，尽量不要用视图来更新数据，因为它有很多的限制。使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能会造成数据更新失败。

9.2.6 删除视图

        DROP VIEW IF EXISTS ‘视图名’；

9.3 视图的作用

        视图是在原有表或者视图的基础上重新定义的虚拟表，这可以从原有的表上选取对用户有用的信息，忽略次要信息，其作用类似于筛选。其主要作用如下：

        ①使操作更简单。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

        ②增加数据安全性。通过视图，用户只能查询和修改指定的数据。指定数据之外的信息，用户根本接触不到。这样可以防止敏感信息被未授权的用户查看，增强机密信息的安全性。

        ③提高表的独立逻辑性。视图可以屏蔽原有表结构变化带来的影响。例如原有表增加列和删除未被引用的列，对视图不会造成影响。同样，如果修改表中的某些列，可以使用修改视图来解决这些列带来的影响。