mysql事务隔离级别和索引_MySQL 四种事务隔离级别以及索引优化

一、事务的基本要素(ACID)1、原子性(Atomicity)：事务开始后所有操作，要么全部完成，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态。也就是说事务是一个不可分割的整体。

2、一致性(Consistency)：事务开始前和结束后，数据库的完整性约束没有被破坏，符合所有现实世界中的约束。例如每一笔交易完成后，都需要保证整个系统的余额等于账户的收入减去账户的支出。

3、隔离性(Isolation)：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。

4、持久性(Durability)：事务完成后，事务对数据库的所有操作都被保存到数据库，不能回滚。

二、事务的并发问题1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据就是脏数据。

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取过程中，对数据做了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。

3、幻读：事务 A 读取所有数据，然后事务B插入了一条数据，并且事务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，提交事务前，多次统计的数据没有变化，故不存在幻读。

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 操作。

所以，为了避免间隙锁，需要遵循存在才删除原则，尽量避免删除不存在的记录。

可重复读(RR)如何解决幻读的？

1. 多版本并发控制(MVCC)(快照读)

多数数据库都实现了多版本并发控制，并且都是靠保存数据快照来实现的。

以 InnoDB 为例，每一行中都冗余了两个字断。

trx_id：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务 id 赋值给 trx_id 隐藏列。

roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo 日志中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

READ COMMITTED —— 每次读取数据前都生成一个ReadView(快照).

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView.

故 RR 解决了幻读，每次读取数据都是同一个 ReadView。

普通的 select 就是快照读。select * from T where number = 1;

原理：将历史数据存一份快照，所以其他事务增加与删除数据，对于当前事务来说是不可见的。

2. next-key 锁 (当前读)

next-key 锁包含两部分：

1. 记录锁(行锁)

2. 间隙锁

除了普通查询语句，其他的都是当前读(需要加锁)。select * from T where number = 1 for update;

select * from T where number = 1 lock in share mode;

insert

update

delete

原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。

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对于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码进行排序，索引分布亦是如此。

Innodb 普通索引:

B+Tree之Innodb 普通索引

普通索引：索引叶子节点存放是其主键的信息，然后根据主键数据到主键索引中去查询数据记录。为什么如此设计？

因为普通索引的叶子节点存放的是主键信息，而主键信息一般都不会变的，但是主键索引关联的数据是整条记录，只要记录发生变化(如删除)，索引就要重新排列。在 Myisam 中，所有的索引都需要维护其记录对应的磁盘地址，性能消耗较大，而 Innodb 中，只有主键索引才需要维护记录。

覆盖索引：如果查询的列可通过索引直接返回，那么该索引称之为覆盖索引。

覆盖索引是不进行回表查询的，可减少数据库IO，可提高查询性能。

所以，不建议查询直接使用 select * ,因为这样无法命中覆盖索引。

密集索引和稀疏索引

密集索引：在密集索引中，文件中的每个搜索码值都对应一个索引值。也就是说，密集索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示，索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。

密集索引图示

特性：每个存储块的每一个键对应的指针都指向每个数据块每一条记录，当要查找指定键K时，采用二分查找即可找到键K对应的记录，复杂度为log2n。

稀疏索引：在稀疏索引中，只为搜索码的某些值建立索引项。也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。如下图所示。

稀疏索引示意图

特性：每个存储块的每一个键对应的指针都指向每个数据块的第一条记录，当要查找指定建K时，先采用二分查找找到<=K的键S，如果S=K，则命中记录，如果S

a    稀疏索引占用的索引存储空间比较小，但是查找时间较长；

b    稠密索引查找时间较短，索引存储空间较大。

下面具体看看 Innodb 和 Myisam 索性查询流程：

索性查询流程

对于 Myisam 来说，由于它的索引是存放在.MYI文件，数据存放在.MYD文件，在两个不同的文件上，于是数据的查询必须经过两步，第一步到索引文件查索引对应数据的磁盘地址，第二步根据磁盘地址拿到数据，于是其索引都是稀疏索引。

对于 Innodb 来说，其索引和数据都在.ibd文件里，故当主键查询时，由于主键的叶子节点直接存放了数据，故只要找到了主键索引就找到了数据，故主键索引属于密集索引。但是对于普通索引来说，其叶子节点存储的是主键信息，其查询需要两步，先到普通索引 Tree 查到主键信息，然后根据主键信息到主键索引 Tree 去查询记录。于是 Innodb 必须要有且仅有一个密集索引，其分配规则如下：

Innodb 密集索引分配规则

索引优化：

(1)BTREE索引的使用场景：

场景1

场景2使用场景：

1、全值匹配的查询，如oder_sn='9876432119900'；

2、匹配最左前缀的查询，在联合索引中，只要最左的索引列用到了，该索引就会生效。但是如果是后面的索引列用到了，最左列未使用，是不会生效的；

3、匹配列前缀查询，即模糊查询最前面不要使用‘%’，如order_sn='9876%'；

4、匹配范围的查询，如oder_sn>'9876432119900'，order_sn

5、精确匹配左前列并范围匹配另外一列。

(2)BTREE索引的使用限制：

使用限制使用限制：

1、如果不是按照索引最左列开始查找的，则无法使用索引；

2、使用索引时不能跳过索引的列，假如3个列的联合索引，用到了1、3，未用的2，此时只有1是生效的，3是不生效的；

3、Not in 和 <> 操作无法使用索引；

4、如果查询中有某个列的范围查找，则其右边的所有列都无法使用索引。

5、排序列包含非同一个索引的列，也无法使用索引。

6、排序列使用了复杂的表达式，如 UPPER(name)，也无法使用索引。

7、如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出现的话(如 where age * 2 > 6)，是用不到索引的。

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 中的字段全部在关联表的第一张表中。

在mysql种这两种方式的体现就是加锁和不加锁

普通查询不加锁使用快照读

如果在事务中出了查询外还有其他增加修改操作那么久要手动加锁实现当前读

彩蛋：

mysql 客户端发起一条 SQL 到服务端返回数据，经过了哪些流程？

mysql 处理流程

1. 连接管理：验证用户名、密码是否正确。

2. 解析与优化：

(一)查询缓存：mysql 会把刚刚处理过的查询请求和结果缓存起来，下次如果有同样的请求过来时，会直接返回缓冲的结果。如果两个查询请求在任何字符上的不同(例如：空格、注释、大小写)，都会导致缓存不会命中。另外，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。系统函数如：NOW。

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从 MySQL 5.7.20 开始，不推荐使用查询缓存，并在 MySQL 8.0 中删除。

(二)语法解析：因为客户端程序发送过来的请求只是一段文本而已，所以 MySQL 服务器程序首先要对这段文本做分析，判断请求的语法是否正确。

(三)语法优化：因为我们写的 MySQL 语句执行起来效率可能并不是很高，MySQL 的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、查询字段顺序调整等。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。

3. 存储引擎：MySQL 服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。如 InnoDB、MyISAM等。

为什么不建议使用 uuid 作为主键？

1. uuid 占用的空间较大

2. uuid 是无序的，作为主引容易导致索引分裂，性能低下，因为叶子节点需要重新排序。如果是自增主键，则直接在叶子节点后面添加即可。

Explain SQL语句时，不同的 type 场景：

const：当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const。

ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。

ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是 ref_or_null。

range：如果使用索引获取某些范围区间的记录，那么就可能使用到 range 访问方法。

index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index。

all：全表扫描。

一般来说，这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了 all 这个访问方法外，其余的访问方法都能用到索引，并且只能用到一个索引。

附录：mysql 各文本字段类型的长度限制：