事务

1. 概述

一个事务(Transaction)实际上就是一个完整的业务逻辑。

比如说：从A账户向B账户转账10000，要顺次做两个操作

1. 将A账户的钱减去10000。
2. 将B账户的钱增加10000。

这就两个操作构成一个最小的工作单元，要么同时成功，要么同时失败，这就是一个完整的业务逻辑。

只有DML语句(insert，delete，update)才有事务，其他语句与事务无关，因为只有DML才能真正操作表中的数据。一旦表中的数据要发生变动，那么就一定要考虑安全问题。

如果所有的业务都只要一条DML就能完成，那么就没有存在事务的意义。存在事务的根本原因就是：有一些特定的业务是需要多条DML协同完成的。

事务本质上来说就是：多条DML语句同时成功，或者同时失败。

2. 实现

前文说到，MySQL默认引擎InnoDB支持事务，本质上是因为InnoDB提供了一组用来记录事务性活动的日志文件。

在事务的执行过程中，每一条DML语句的操作都会被记录到事务性活动的日志文件中。

在事务的执行过程中，我们可以提交事务，也可以回滚事务。

2.1 提交事务

提交事务就是清空事务性活动的日志文件，将数据全部彻底持久化到数据表中。

提交事务标志着事务的结束，并且是全部成功的结束。

提交事务的方式：

commit;
复制代码

2.2 回滚事务

回滚事务也会清空事务性活动的日志文件，但是会将之前所有的DML操作全部撤销。

回滚事务标志着事务的结束，并且是全部失败的结束。

回滚事务的方式：

rollback;
复制代码

注意：回滚永远只能回滚到上一次的事务提交点。

2.3 MySQL自动提交机制

MySQL默认情况下支持事务自动提交，就是默认将每一条DML都看作一个事务，每一条DML执行完毕后就自动提交事务。

实际上自动提交机制是不符合我们开发习惯的，因为一个业务通常是需要多条DML语句共同执行才能完成的。为了保证数据的安全，必须要求多条DML同时成功才能提交，所以不能执行一条DML就提交一条。

因此，我们就需要关闭MySQL自动提交机制。

start transaction;
复制代码

该命令需要在use了数据库之后使用。

3. 特性

事务包括四个特性：ACID

• 原子性（A—Atomicity）：事务是最小的工作单元，不可再分。
• 一致性（C—Consistency）：在同一个事务中，所有操作必须同时成功，或者同时失败，以保证数据一致性。
• 隔离性（I—Isolation）：事务A和事务B之间具有一定的隔离。事务A在操作一张表的时候，事务B也操作这张表会怎样（下文研究）。
• 持久性（D—Durability）：事务最终结束的一个保障。事务提交，就相当于将没有持久化的数据持久化到硬盘上。

4. 事务隔离级别

事务的隔离性指的是：事务A和事务B之间具有一定的隔离。

可以将隔离其想成一道墙，这道墙可能会很厚，也可能会很薄，墙的厚度就可以想象成事务的隔离级别。

事务和事务之间有四种隔离级别：

• 读未提交：read uncommitted（最低的隔离级别）
• 读已提交：read committed
• 可重复读：repeatable read
• 序列化 / 串行化：serializable（最高的隔离级别）

4.1 读未提交

读未提交指的是：事务A可以读到事务B未提交的数据。

这种隔离级别会造成脏读现象（Dirty Read），也可以说事务A读到了脏数据。

其他线程控制的事务没有提交的数据被称为脏数据。

这种隔离级别等于没有隔离性，一般不会将数据库设置到该隔离级别。

4.2 读已提交

读已提交指的是：事务A可以读到事务B已提交的数据。

这种隔离级别解决了脏读现象，但是会造成不可重复读取数据的问题。

不可重复度取数据的意思是：在事务A未提交之前，事务A能够读到事务B提交的数据，如果事务B增删改事务A操作的表中的数据之后并提交成功，那么会导致事务A在事务B提交前后两次从表中读取的数据不一样。

该种隔离级别虽然会造成不可重复读的问题，但是可以保证每一次读取的数据都绝对真实。

Oracle默认的数据库隔离级别是读已提交。

4.3 可重复读

可重读读指的是：事务A一旦开启之后，无论事务B有没有对事务A操作的表进行操作、有没有提交成功，事务A每一次读到的数据都是一致的，都是事务A刚开启时的数据状态。

可重复读的具体的实现方式是：在事务A开启后，DBMS就对事务A要操作的数据状态做了备份，事务A在提交之前所访问的所有数据都是备份数据。

这种隔离级别解决了不可重复读的问题，但是会造成幻读，也就是说不能保证读取到的数据是绝对真实的。

MySQL默认的数据库隔离级别就是可重复读。

4.4 序列化/串行化

序列化指的是：多个事务操作有重叠的表必须排队（串行），不能并发操作。

序列化是最高的隔离级别，效率也是最低的。

序列化能够解决上述的脏读、不可重复读和幻读所有的问题。

序列化能保证每一次读到的数据都一致，且数据是绝对真实的。

4.5 MySQL设置隔离级别

查看当前事务隔离级别：

select @@tx_isolation;
复制代码

设置读未提交：

set global transaction isolation level read uncommited;
复制代码

设置读已提交：

set global transaction isolation level read commited;
复制代码

设置可重复读：

set global transaction isolation level repeatable read;
复制代码

设置序列化：

set global transaction isolation level serializable;
复制代码

索引

1. 概述

索引（index）是帮助MySQL高效获取数据的一个数据结构，它相当于一本书的目录，添加在表的字段上，通过缩小扫描范围来提高查询效率。

MySQL有两种查询方式：

• 全字典扫描：从表的第一行开始，逐行进行扫描匹配，效率相对较慢。
• 区域性扫描：先通过查看表的索引确定一个大概搜索范围，然后从该范围的第一行开始，逐行进行扫描匹配，效率相对较高。

我们先不谈索引的具体实现B+树，我们使用一棵平衡搜索二叉树来代替B+树阐述一下索引的实现思想：

索引之所以能够缩小查询范围实际上只是让MySQL去某一个区间查询罢了。

在MySQL中，索引是需要排序的。比如说牛津词典的目录是按照首字母是a、b、c......进行分类排序的，为什么要分类排序？因为只有进行分类排序后，才能划分区间。

2. 存储位置

在MySQL当中，索引是一个单独的对象，在不同的存储引擎中以不同的形式存在：

• InnoDB：索引存储在一个逻辑名称为tablespace的空间中。
• MyISAM：索引存储在一个 .MYI 文件中。
• MEMORY：索引存储在内存中。

3. 优劣势

优势：

• 类似于书籍目录，提高数据检索效率，降低数据库IO成本。
• 通过索引对数据进行排序，降低数据排序成本，降低CPU消耗。

劣势：

• 实际上索引也是一张表，该表中保存了主键、索引字段和指向表中哪一行的记录，因此索引是要占一定空间的。
• 索引降低了更新表的速度。因为在对表进行insert、delete和update时，MySQL不仅要更新数据，还要更新索引信息。

4. 实现原理

在InnoDB中，索引的存储、排序等相关操作底层都是通过B+树这个平衡多叉树来组织和实现的。MySQL之所以能够去某一个区间实现区域查找靠的就是B+树自身的特性。

在任何一个DBMS去组织数据时，对应任何一张表的任何一行，硬盘上都存储一个对应的物理存储编号，比如于0x1234，0x2345。该物理存储编号会作为表的一个隐藏字段存储。

当一个索引对象的内部被B+树组织起来之后，该树的每一个节点都存储两个数据：

• 被添加索引的字段的每一行的内容。
• 每一行数据对应的物理存储编号。

被添加索引的字段的每一行的内容作为Key，也就是B+树组织和查找的参考关键字。

在查找时，通过B+树的查找机制能够以极低的代价找到匹配条件的所有节点，通过节点上存储的物理存储编号，直接生成如下SQL

select * from xxx where 物理编号 = xxx;
复制代码

从而能够直接对应到硬盘中表上的数据实现数据的查找。

如下例子：

假设InnoDB中使用的是5阶B+树组织数据，那么每个节点最少两个元素，最多四个元素

5. 添加条件

在MySQL中，有主键约束的，和有unique约束的字段都会被自动添加上索引。因为字段唯一性越高，添加索引的效果越好。

在上面条件下会考虑给字段添加索引？

• 数据量庞大，由于硬件环境不同，需要进行测试。
• 该字段的数据经常被扫描，也就是说该字段经常出现where或者having的后面作为查询的条件。
• 该字段很少会被DML操作（insert，delete，update），因为如果该字段被DML操作后，索引会重新组织排序。

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统性能。

建议通过主键查询或者通过添加了unique约束的字段查询，这样查询效率是比较高的。

6. 创建和删除

一张表的一个字段可以添加一个索引，多个字段联合起来也可以添加一个索引（复合索引）。

每一个索引对象都是独立的，即使不同索引对象在同一张表上，索引和索引之间也没有必然联系。只有SQL中使用到了被添加索引的字段作为条件进行查询，索引才会发挥作用。

索引的命名方式通常为：表名_字段名_index

复合索引的命名方式通常为：表名_字段名1_字段名2_index

创建索引对象：

create index 表名_字段名_index on 表名(字段名); 
复制代码

创建复合索引对象:

create index 表名_字段名1_字段名2_index on 表名(字段名1, 字段名2);
复制代码

删除索引对象：

drop index 表名_字段名_index on 表名;
复制代码

7. 失效

7.1 检测失效

并不是只要添加了索引的字段作为查询条件在执行时索引就一定会生效，索引也有失效的时候。

MySQL中如何查看一条DQL是否使用索引进行检索：

explain select语句
复制代码

该命令执行成功后会返回一个表格，该表格的type字段如果是ALL，则是全字典扫描；如果是ref，则是使用了索引进行的区域性扫描。

7.2 失效情况一

例如执行如下SQL，索引就会失效，该SQL的执行仍然是全字典扫描。

select * from emp where ename like '%T';
复制代码

因为模糊查询的条件中使用了 '%' 作为字符串匹配的开头，如果要使用某字段的索引进行局域性扫描，那么该字段在查询条件中必须要有一个明确的开头。

如果我们对需要对添加了索引的字段进行模糊查询，那么就尽量避免使用 '%' 开头作为模糊查询的条件，这是一种优化的手段。

7.3 失效情况二

如果查询条件中使用了or，那么需要注意or两边的字段是否都有索引。只有所有字段都有索引，整个DQL才会使用到索引进行局部扫描。只要其中有一个字段没有索引，那么即使有的字段上有索引，也会失效。

在写DQL时尽量少使用or，建议使用union，这也是一种优化的手段。

7.4 失效情况三

给若干个字段添加了一个复合索引，当引用这几个字段作为查询条件时，如果最左边的字段没有被引用，那么复合索引就会失效；如果最左的字段被引用，则复合索引就不会失效。（最左原则）

比如说给emp表中的job和sal字段添加了一个复合索引，当where后面只有sal时，索引失效；当where后面有job和sal时，索引不会失效；当where后面只有job时，索引也不会失效。

7.5 失效情况四

如果添加了索引的字段在作为查询条件时参与了数学运算，则索引失效。

比如说给emo表中的sal字段添加了一个索引，如果执行如下DQL，该索引就会失效

select * from emp where sal + 1 = 800;
复制代码

7.6 失效情况五

如果添加了索引的字段在作为查询条件时作为函数的参数，则索引失效。

比如说给emo表中的name字段添加了一个索引，如果执行如下DQL，该索引就会失效

select * from emp where lower(name) = 'smith';