一、事务的基本要素（ACID）
1、原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态。也就是说事务是一个不可分割的整体。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。比如 A 向 B 转账，不可能 A 扣了钱，B 却没有收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
4、持久性（Durability）：事务完成后，事务对数据库的所有操作都被保存到数据库，不能回滚。
二、事务的并发问题
1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据就是脏数据。
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取过程中，对数据做了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读侧重于修改，幻读侧重于新增或删除。解决幻读需要锁表。
三、MySQL 事务隔离级别
MySQL 事务隔离级别
mysql 默认的事务隔离级别为 repeatable-read
默认事务隔离级别
四、演示说明各个隔离级别的情况
1、读未提交（read-uncommitted）:
（1）打开客户端 A，并设置当前事务模式为 read uncommitted，查询表 goods 的初始值；
（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新 goods;
（3）这时，虽然客户端 B 的事务还没提交，但是客户端 A 就可以查询到 B 已经更新的数据；
（4）一旦客户端 B 的事务因为某种原因回滚，所有的操作都会将被撤销，那客户端 A 查询到的数据其实就是脏数据；
（5）在客户端 A 执行更新语句 update goods set goods_stock = goods_stock - 10 where id =1，iphonex 的 goods_stock没有变成30，居然是40，出现了脏读。也就是说，在应用程序中，我们会用40-10=30，并不知道其他会话的回滚，要想解决这个问题，可以采用读已提交的隔离级别。
2、读已提交（read committed）
（1）打开一个客户端 A ，并设置当前事务级别为 read committed，查询表 goods的初始值；
（2）在客户端 A 的事务提交之前，打开另一个客户端 B，更新 goods ；
（3）这时，客户端 B 的事务还没提交，客户端 A 不能查询到 B 已经更新的数据，解决了脏读的问题；
（4）客户端 B 的事务提交；
（5）客户端 A 执行上一步相同的查询，结果与上一步不一致，即产生了不可重复读的问题；
3、可重复读（repeatable read）
一切目的是：保证可重复读；A 事务开启时，若A 的读操作先于 事务B 的写操作，则为了可重复读，在 A 事务提交前，A事务 不会将 B 的最新值读过来；若A 的读操作晚于 B 的写操作，即事务的首次读，会读取数据的最新值。写操作为了保证数据的一致性，都是会基于最新数据操作的。
（1）打开一个客户端 A，并设置当前事务级别为 repeatable read，查询表 goods；
（2）在客户端 A 提交事务之前，打开另一个客户端 B，更新表 goods 并提交；
（3）客户端 A 再次执行查询操作，两次查询结果一致，没有出现不可重复读的问题；
（4）客户端 A 接着执行 update goods set goods_stock = goods_stock - 10 where id =1，iphonex 的 goods_stock没有变成50 - 10 = 40，iphonex 的 goods_stock的值用的是步骤（2）中提交的40来算的，所以结果是30，数据一致性没有被破坏。
（5）客户端 A 提交事务，查询表 goods；
（6）在客户端 B 开启事务，新增一条数据，其中 goods_stock字段值为40，并提交；
（7） 在客户端 A 计算 goods_stock 之和，值为30+50=80，没有把客户端 B 的值算进去，客户端 A 提交后再计算 goods_stock 之和，居然变成了120，这是因为把客户端 B 的40算进去了；
对于客户端 A，提交事务前后，统计的数据不一致，导致了幻读。站在开发者角度，数据的一致性并没有被破坏。但是在应用程序中，我们的代码可能会把80提交给用户，但是这个概率很小。想避免这种情况，除非锁住表，不让其新增或者删除，这就要讲到最后一个事务隔离级别-"串行化"。
4、串行化（serializable）
（1）打开客户端 A，设置当前事务级别为 serializable，查询表 goods；
（2）打开一个客户端B，并设置当前事务模式为 serializable，插入一条记录报错，表被锁了插入失败。mysql中事务隔离级别为serializable时会锁表，因此不会出现幻读的情况，这种隔离级别并发性极低，开发中很少会用到。
补充：
1、mysql中默认的事务隔离级别是 repeatable read，并不会锁住读取到的行；
2、事务隔离级别为 read committed 时，写数据只会锁住相应的行。即写操作锁住了相应的行，导致读操作读不到，故只有写操作提交了，其他事务才能读到最新数据；
3、事务隔离级别为 repeatable read 时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁、行锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表（但是可读）。
4、隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以优先考虑隔离级别设为 Read Committed，它能够避免脏读，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或者乐观锁来控制。
间隙锁（Gap Lock）：
定义：锁加在不存在的空闲空间，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引记录之后的空间。
间隙锁的出现主要集中在同一个事务中先 delete 后 insert 的情况下，当我们通过一个索引参数去删除一条记录的时候，如果参数在数据库中存在，那么这个时候产生的是普通行锁，锁住这条记录，然后删除，然后释放锁；如果这条记录不存在，问题就来了，数据库会扫描索引，发现这个记录不存在，这个时候的 delete 语句获取到的就是一个间隙锁，然后数据库会扫描到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，然后以此为界，构建一个区别，锁住整个区间的数据，一个特别容易出现死锁的间隙诞生了。
（1）在默认的 repeatable read 隔离级别下，并且mysql 存储引擎是 InnoDB（支持事务） 时，客户端 A 开启事务，执行删除语句 delete from goods where id = 7；
（2）客户端 B 执行 insert into goods (id,goods_name,goods_stock) values (9,"aaaaaa", 80)，发现产生了间隙锁。当执行删除语句是，由于没有 id = 7 的记录，于是会在id(4 - 10)区间内生成间隙锁，不允许此区间内的 insert 操作。
所以，为了避免间隙锁，需要遵循存在才删除原则，尽量避免删除不存在的记录。
MySQL 存储引擎和索引优化：
数据库存储引擎的选择：
MyISAM：不支持事务，表级锁（读操作是共享锁）。适用场景：无事务场景，只读场景。
InnoDB：事务级存储引擎，完美支持行级锁、事务ACID特性。MySQL 5.7以上的默认选择。
大事务：在一个事务中，处理了太多的数据或者太多的SQL，导致处理时间很长而产生锁表。
如何避免大事务？
1、避免一次处理太多的数据，可以采用分批处理。
2、移出不必要在事务中的select操作。
索引的选择：
1、BTREE索引
BTREE索引，底层是B+树的实现方式。
为什么不使用平衡二叉树做Mysql索引？
1、树的高度决定了它的IO操作次数，而平衡二叉树由于只有两个子节点，导致树的高度很高；
2、每次磁盘IO操作，可以最多加载一页的数据（即4kb），但是它每次只给一个关键字数据，没有利用好磁盘IO的数据交互特性。mysql中每次磁盘IO最多加载16kb。
1.1 B-Tree
B-Tree
B-Tree是一个多路的绝对平衡的查找树，其关键字（索引）数量，一定是路数-1。每个节点（磁盘块）都保存了关键字对应的数据，一般是记录对应的磁盘地址。
1.2 B+Tree
B+Tree
B+Tree是B-Tree的Plus版本，它继承了B-Tree的绝对平衡性。并且，B+Tree的非叶子节点不保存数据相关信息，只保存关键字和子节点的引用。关键字对应的数据保存在叶子节点上，其叶子节点是顺序排列的，相邻节点具有排序引用的关系，便于排序和范围查找。
B+Tree对于B-Tree的优势：
1、B+树的扫库、扫表能力更强。因为它的非叶子节点（稀疏索引）是不保存数据的，即能够保存更多的关键字和子节点的引用，故扫描更快。
2、B+树的排序能力更强。因为它的数据全部放在叶子节点上（稠密索引），并且是顺序排列的（类似链表）。
3、B+树的查询效率更加稳定。注意：这是并不是说效率更加快，因为B+树的扫描必须扫描到叶子节点才能拿到数据（记录对应的磁盘地址），但是B-树是不需要扫描到叶子节点就能够拿到数据的。对于相同数据量来说，B+树的高度是比B-树低的，所以最坏情况下，B-树查询效率比B+树低。故B+的查询效率更加稳定。
稠密索引和稀疏索引下面会讲。
Mysql中的B+Tree索引体现形式——Myisam:
B+Tree之Myisam
当Mysql存储引擎为Myisam时，其数据目录下会有3个文件：
1、xxx_myisam.frm，保存数据结构；
2、xxx_myisam.MYD，保存数据（包括每条记录对应的磁盘地址）；
3、xxx_myisam.MYI，保存索引。
使用索引查询时，会先到.MYI文件查找索引所在的数据（磁盘地址），然后根据磁盘地址到.MYD文件查找对应的数据。
Mysql中的B+Tree索引体现形式——Innodb:
Innodb 主键索引：
B+Tree之Innodb 主键索引
.当Mysql存储引擎为Innodb时，其数据目录下会有2个文件：
1、xxx_innodb.frm，保存数据结构；
2、xxx_innodb.ibd，保存数据和索引。
上图是Innodb的主键索引：数据库记录都放在索引叶子节点上，并且是有顺序的。只有是主键索引才是聚集索引：数据库表中数据的物理顺序与索引的顺序相同。
此时，你会有一个疑问，当主键为uuid怎么办？
其实，我们在建库的时候，就选择了数据库的排序规则为：utf8_general_ci，以ASCII码，不区分大小写的规则排序。所以，我们主键采用字符串uuid时，其会采用ASCII码进行排序，索引分布亦是如此。
聚集索引又分为稠密索引和稀疏索引
稠密索引：在稠密索引中，文件中的每个搜索码值都对应一个索引值。也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示，索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。
稠密索引图示
特性：每个存储块的每一个键对应的指针都指向每个数据块每一条记录，当要查找指定键K时，采用二分查找即可找到键K对应的记录，复杂度为log2n。
稀疏索引：在稀疏索引中，只为搜索码的某些值建立索引项。也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。如下图所示。
稀疏索引示意图
特性：每个存储块的每一个键对应的指针都指向每个数据块的第一条记录，当要查找指定建K时，先采用二分查找找到<=K的键S，如果S=K，则命中记录，如果S<K，则顺序查找=K的键，复杂度大于log2n，小于n。
比较：
a 稀疏索引占用的索引存储空间比较小，但是查找时间较长；
b 稠密索引查找时间较短，索引存储空间较大。
Innodb 普通索引:
B+Tree之Innodb 普通索引
普通索引：索引叶子节点存放是其主键的信息，然后根据主键数据到主键索引中去查询数据记录。
为什么如此设计？
因为普通索引的叶子节点存放的是主键信息，而主键信息一般都不会变的，但是主键索引关联的数据是整条记录，只要记录发生变化（如删除），索引就要重新排列。在Myisam中，所有的索引都需要维护其记录对应的磁盘地址，性能消耗较大，而Innodb中，只有主键索引才需要维护记录。
覆盖索引：如果查询的列可通过索引直接返回，那么该索引称之为覆盖索引。
覆盖索引可减少数据库IO，可提高查询性能。
所以，不建议查询直接使用 select * ,因为这样无法命中覆盖索引。
（1）BTREE索引的使用场景：
场景1
场景2
使用场景：
1、全值匹配的查询，如oder_sn='9876432119900'；
2、匹配最左前缀的查询，在联合索引中，只要最左的索引列用到了，该索引就会生效。但是如果是后面的索引列用到了，最左列未使用，是不会生效的；
3、匹配列前缀查询，即模糊查询最前面不要使用‘%’，如order_sn='9876%'；
4、匹配范围的查询，如oder_sn>'9876432119900'，order_sn<'9876432119999'；
5、精确匹配左前列并范围匹配另外一列。
（2）BTREE索引的使用限制：
使用限制
使用限制：
1、如果不是按照索引最左列开始查找的，则无法使用索引；
2、使用索引时不能跳过索引的列，假如3个列的联合索引，用到了1、3，未用的2，此时只有1是生效的，3是不生效的；
3、Not in 和 <> 操作无法使用索引；
4、如果查询中有某个列的范围查找，则其右边的所有列都无法使用索引。
2、Hash索引
Hash索引，底层是Hash表。具体Hash表的结构，可以参考我的另一篇文章 Java7/8 中的HashMap 和 ConcurrentHashMap。
（1）Hash索引的特点：
Hash 索引的特点
只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引。
Hash索引的限制
Hash索引的限制：
1、Hash索引必须进行二次查找，因为Hash索引存储的是Hash码，并不是直接存储数据的指针地址，所以需要二次查询，但是由于数据库都是有缓存的，这样的性能损耗可以忽略；
2、Hash索引无法用于排序，因为它不像BTREE那样是顺序存储元素的；
3、Hash索引不支持部分索引查找也不支持范围查找，只支持精确查找；
4、Hash索引中的Hash码的计算可能存在Hash冲突，从而影响性能。
索引优化：
联合索引优化
创建联合索引时，如何选择索引列的顺序？
1、经常会被使用到的列优先；
2、选择性高的列优先，即值的可能性比较多的列；
3、宽度小的列优选。
order by 优化
使用索引扫描来优化排序：
1、索引的列排序和Order by 子句的顺序完全一致；
2、索引中的所有列的方向（升序，降序）和Order by 子句完全一致；
3、Order by 中的字段全部在关联表的第一张表中。
网友评论