一、Mysql索引底层数据结构与算法

(一)索引的数据结构

索引是帮助MySQL高效获取数据的排好序的数据结构。索引与要查找的数据是以key-value的形式存放，key为索引字段的值，value为数据或数据的磁盘地址，存放在节点上

数据库数据存放在磁盘上，写数据是一个磁道一个磁道的写,数据随机分布,同一张表的数据不一定是连续的。从磁盘上读取一条数据就是一次磁盘的IO，IO速度不快。查询数据时，如果没有索引查询数据的时候会一行一行的IO查找比对，如果减少查找次数速度就会有很大提升，建立索引的目的就是减少IO的次数。

1.二叉树与平衡二叉树

二叉树特点为：右边元素大于父元素，左边子元素小于父元素。存在的问题：当索引为单边增长插入(自增插入)的时候，数据结构就变成没有节点的链表的形式，这样的数据结构对查询速度基本没有提升

平衡二叉树特点: 单边增长插入(自增插入)的时候，当一遍树的高度比另外一边树高很多时会自动平衡（通过旋转使其恢复平衡），将树的高度变矮一点。存在的问题：虽然对普通二叉树有所优化，但是当数据量很大的时候，树的高度将很高，查询一次依旧很慢

2.B-Tree

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。B-Tree的一个节点是在磁盘上画一块空间，使能够存放更多索引，即一个节点存放多个索引。

B-Tree节点存储的数据：

每个节点存有n个数据索引（索引+数据）和n+1个可以用来指向子节点指针，其中叶节点的指针为空。数据索引与指针相互间隔，节点两端是指针，所以节点中指针比指针多一个。

B-Tree特点：

节点中的数据索引从左到右递增排列，且任意指针指向的字节点的最小索引值不小于指针左方的索引值，最大索引值不大于右边该指针右边的索引值。且索引的索引元素不重复

3.B+Tree

B-Tree变种,一个节点就是一个磁盘页，```SHOW GLOBAL STATUS like 'Innodb_page_size'```默认是16k,同B-Tree一样，每个节点能放很多索引。

节点存储的数据：

非叶子节点不存储data只存储索引(索引关键字冗余子节点的第一个元素索引)和下一个磁盘页的第一个元素地址。叶子节点只存储索引和数据，包含所有索引字段并且，非叶子节点指向子节点指针与索引关键字个数相同，同样相互间隔，但第一元素存放的是索引关键字。

特点

叶子节点没有用于指向子节点的指针，但是叶子节点之间用指针连接，它们相互指向，提高区间访问的性能（一块小空间，存放节点在磁盘上的位置）和B-TREE一样从左到右递增排放

为什么mysql页文件默认16K

假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14=1170个(主键+指针)那么一颗高度为2的B+树能存储的数据为：1170*16=18720条，一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级条

4.Hash

对索引的key进行一次hash计算就可以定位出数据存储的位置,若存在hash碰撞就根据索引值找到对应的磁盘位置

很多时候Hash索引要比B+ 树索引更高效。当然其存在的问题包括仅能满足 “=”、“IN”，不支持范围查询；存在hash冲突问题。

(二)存储引擎

1.MyISAM与InnoDB存储引擎索引实现

1.1常用存储引擎

Mysql中，常用的存储引擎为InnoDB和MyISAM，它们的文件存储如下：

InnoDB：表在磁盘上存储有两个个文件:frm(数据表结构相关)、idb（数据+索引）

MyISAM：表在磁盘上存储有三个文件:frm(数据表结构相关)、MYD（数据）、MYI(索引)

1.2非聚集索引和聚集索引

1.非聚集索引，也叫非聚簇索引，将数据与索引分开存储，索引结构的叶子节点指向了数据的对应行(value存储的是磁盘地址或主键key值)，然后通过指向找到对应的数据。MyISAM索引文件和数据文件是分离的，它的主键索引和非主键索引的都是非聚集索引。

2.聚集索引，也叫聚簇索引，将数据存储与索引放到了一块，找到索引也就找到了数据，InnoDB主键索引就是使用聚集索引,其表数据文件本身就是按B+Tree组织的一个索引结构文件。

1.3MyISAM存储引擎索引实现

MyISAM索引文件和数据文件是分离的，也就是说MyISAM的索据和索引分开存储，属于非聚集索引。如下图：

1.4InnoDB存储引擎索引实现

InnoDB表数据文件本身就是按B+Tree组织的一个索引结构文件，其主键索引叫一级索引，其他索引就是二级索引，也叫普通索引。InnoDB主键索引使用的就是聚集索引，并且一个表中只能有一个聚集索引。二级索引使用的就是非聚集索引，它叶子节点下的data就是该表主键值，一个表中可以有多个非聚集索引。另外还有一点，由多个字段组成的索引被称为联合索引。

一级索引

二级索引

联合索引

2.二级索引如何查找数据

使用二级索引查找，先找到主键值。

如果不需要主键值，只通过索引的值就能完全覆盖select字段，就是叫```覆盖索引```。

如果主键和索引的值能完全覆盖select字段，就直接从索引中取得查询结果，这就是```索引覆盖```。

如果主键和索引的值不能完全覆盖select字段，需要通过主键值到一级索引中查找其他字段，这就是```回表查询```。

3.为什么建议InnoDB必须创建一个主键，并且推荐使用整型的自增主键？

必须建主键：基于InnoDB的设计，idb文件必须要用一个B+TREE的结构来组织数据，如果没有设置主键，那么数据库会先找一个没有重复数据的列来组织数据，若还是没找到就会```自己创建一个隐藏列```来组织数据，这样相对于自己创建来讲，更耗费数据库性能

推荐使用整型：查找索引时是要比较大小，UUID是String，比较大小的时候要将一个一个字符转ascii进行比较，相对会比较慢，而且使用整型比uuid更节省空间

推荐自增：

如果表使用自增主键:那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，主键的顺序按照数据记录的插入顺序排列，```自动有序```。当一页写满，就会自动开辟一个新的页

如果使用非自增主键:由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而```移动数据```，甚至目标页面可能已经被回写到磁盘上而```从缓存中清掉```，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了```大量的碎片```，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

4.为什么非主键索引结构叶子节点存储的是主键值？

一致性和节省存储空间，先查普通索引然后根据rowid查找数据可以保证一致性和减少复杂度，

在Secondary key中(二级索引)，若不存放rowid 存的是数据，那么当索引多的时候就很有很多份数据，很浪费空间。

(三)联合索引及最左前缀原则

1.联合索引

```联合索引```又叫```复合索引```，两个或更多个列上的构成的索引被称作复合索引。联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2.联合主键索引```排序方式```，安照```索引键的先后顺序维护```，即会先根据第一个键排序，然后再根据第二个键排序，以此类推。

底层数据结构为:

2.最左前缀原则

索引组合索引 有“最左前缀”原则，遇到范围查询(>、<、between、like)就会停止匹配,若前面的索引键没有用到，那么后面的索引将无效，因为基于排好序的原则，若前面的键无法排序时，后面的键将是混乱无序的。

二、MySql Explain使用及索引优化

(一)Explain使用

1、Explain工具介绍

EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈。在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL，如：

explain select * from actor;

它将展示如下列

explain有两个两个变种，分别为explain extended和explain partitions。

explain extended（可以认为显示filtered）：

explain extended会在 explain 的基础上额外提供一些查询优化的信息。sql语句在执行的过程中可能被Mysql内部优化,紧随其后通过 show warnings 命令可以得到优化后的查询语句（并不一定能执行，只是展示做的大致优化），从而看出优化器优化了什么，。

explain partitions（可以任为显示partitions）：

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

2、explain中的列说明

参考官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

(二)索引优化

1.索引下推

对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，如SQL语句SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。

MySQL5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

总的来讲，索引下推会减少回表次数，要注意的是：对于innodb引擎的表，索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

为什么范围查找Mysql没有用索引下推优化？

估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以这里Mysql选择给 like KK% 用了索引下推优化，当然这也不是绝对的，有时like KK% 也不一定就会走索引下推。

2.Order by与Group by优化

Order by排序有 filesort 和index两种方式，在EXPLAIN的Extra列中会有Using index和Using filesort这么两个值，Using index是指MySQL扫描索引本身完成排序，Using filesort将用外部排序而不是索引排序，其中index效率高，而filesort效率低。

order by满足两种情况会使用Using index: order by语句使用索引最左前列或者使用where子句与order by子句条件列组合满足索引最左前列。所以尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则

索引的排序方式不同（where order的顺序是按照最左前缀，只是排序有升序有降序），会产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

Group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序

2.1Using filesort文件排序原理详解

filesort文件排序方式分为单路排序和双路排序

单路排序：

单路排序是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields>，其排序过程为：

1.从索引(name)找到第一个满足 (name = ‘zhuge’) 条件的主键 id

2.根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中

3.然后找到第二个满足条件的主键id，取出整行放入到 sort_buffer。以此类推，直到将所有满足条件的都放入到sort_buffer。

4.对 sort_buffer 中的数据按照字段排序，返回结果给客户端

双路排序：

双路排序（又叫回表排序模式）:是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段。用trace工具可以看到sort_mode信息里显示< sort_key, rowid >，其排序过程为：

1.从索引(name)找到第一个满足 (name = ‘zhuge’) 条件的主键 id

2.根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中

3.然后找到第二个满足条件的主键id，取出整行,把排序字段 position 和主键 id放入到 sort_buffer。以此类推，直到将所有满足条件的都放入到sort_buffer。

4.对 sort_buffer 中的字段 position 和主键 id 按照字段 (position) 进行排序

5.遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

排序方式的选择

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。小于max_length_for_sort_data，用单路排序模式，大于max_length_for_sort_data ，那么使用 双路排序模式。

MySQL 排序内存 sort_buffer

sort_buffer默认1M,sort_buffer内存排序一般情况下效率会高于磁盘文件排序,但不能因为这个就随便增大sort_buffer,mysql很多参数设置都是做过优化的，不要轻易调整。

sort_buffer 配置的比较小并且没有条件继续增加了,把 max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法,可以在sort_buffer 中一次排序更多的行.

sort_buffer 有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。

2.2 样例分析

在了解Order by与Group by优化之前先看看几个样例分析

示例表

CREATE TABLE `employees` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',

 `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

 `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',

 `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',

 PRIMARY KEY (`id`),

 KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());

INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());

INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

-- 插入一些示例数据

drop procedure if exists insert_emp;

delimiter ;;

create procedure insert_emp()        

begin

 declare i int;                    

 set i=1;                          

 while(i<=100000)do                

   insert into employees(name,age,position) values(CONCAT('zhuge',i),i,'dev');  

   set i=i+1;                      

 end while;

end;;

delimiter ;

call insert_emp();

样例1：

分析：

利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

样例2：

分析：

从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，未遵守最左前缀原则，出现了Using filesort。

样例3：

分析：

查找只用到索引name，age和position用于排序，无Using filesort。

样例4：

分析：

和样例3中explain的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

样例5：

分析：

与样例4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，所以索引未颠倒，不会出现Using filesort。

样例6：

分析：

虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

样例7：

分析：

对于排序来说，多个相等条件也是范围查询

样例8：

可以用覆盖索引优化，如

3.Join关联查询优化

3.1驱动表与被驱动表

Join关联查询中，先执行的就是驱动表，后执行的是被驱动表。inner join(INNER JOIN 与 JOIN 是相同的)时，优化器一般会优先选择小表做驱动表，排在前面的表并不一定就是驱动表；left join时，左表是驱动表，右表是被驱动表；right join时，右表时驱动表，左表是被驱动表

3.2表关联常见有两种算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

从一张表里拿出一条记录再嵌套循环到另外一张表里拿记录叫做嵌套循环。一次一行循环地从驱动表中读取行(如果驱动表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据)，在这行数据中取到关联字段，根据关联字段在驱动表里取出满足条件的行，然后取出两张表的结果合集.

2、基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比，返回满足 join 条件的数据。

join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

关联查询中，Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，mysql会选择Block Nested-Loop Join算法,有索引一般选择 NLJ 算法。原因是：

使用jion关联时，若关联字段不是索引,那么NLJ就是全表扫描，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

3、straight_join

straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序，要注意的是：

1.straight_join只适用于inner join

2.使用straight_join一定要慎重，尽可能让优化器去判断，大部分情况下mysql优化器是比人要聪明的。

3.3对于关联sql的优化

1.关联字段加索引，尽量选择NLJ算法，避免全表扫描

2.小表驱动大表，知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

4.in和exsits关联查询优化

原则：小表驱动大表，即小的数据集驱动大的数据集

4.1in关联查询

select * from A where id in (select id from B)

等价于

for(select id from B){

  select * from A where A.id = B.id

}

当B表的数据集小于A表的数据集时，in优于exists

4.2 exists关联查询

select * from A where exists (select 1 from B where B.id = A.id)

等价于:

for(select * from A){

select * from B where B.id = A.id

}

当A表的数据集小于B表的数据集时，exists优于in

EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换，官方说法是实际执行时会忽略SELECT清单,因此没有区别

EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比

EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析

5.count(*)查询优化

count(*)、count(1)、count(字段)、count(主键 id) 只有count(字段)不会统计字段为null值的数据行。四个sql的执行计划一样，说明这四个sql执行效率应该差不多，效率区别为:

字段有索引

count(*)≈count(1)>count(字段)>count(主键 id)

count(字段)统计走二级索引，二级索引存储数据比主键索引少，检索性能应该更高，所以count(字段)>count(主键 id)

字段无索引

count(*)≈count(1)>count(主键 id)>count(字段)

count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。

常见优化方法

1、查询mysql自己维护的总行数

对于myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算

对于innodb存储引擎的表mysql不会存储表的总记录行数，查询count需要实时计算

2、show table status，表总行数的估计值

3、将总数维护到Redis里

4、增加数据库计数表

(三)索引设计原则

1、代码先行，索引后上。主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

2、联合索引尽量覆盖条件（SQL语句）。尽量根据sql语句建联合索引，让每一个联合索引都尽量包含在sql语句里的where、order by、group by中，且这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。sql语句中用到多个单值索引查询，msql可能只用到一个

3、不要在小基数字段上建立索引。不要在不同值少的字段上建立索引

4、长字符串我们可以采用前缀索引。尽量对字段类型较小的列设计索引，字段类型较小的话，占用磁盘空间也会比较小，搜索性能也会比较好一点。如varchar(255)的大字段，可以针对这个字段的前20个字符建立索引。但要注意的是order by、group by没法用上索引的

5、where与order by冲突时优先where

6、基于慢sql查询做优化

核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询

(四)优化总结

索引失效，转全表扫描

1.在索引列上做操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

2.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描

3.is null,is not null 一般情况下也无法使用索引

4.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作

5.字符串不加单引号索引失效

6.联合索引，索引的使用顺序与定义顺序相同且不能跳过，索引跳过，索引会失效。索引使用范围查询，索引右侧的列索引失效。

可能会使用索引

1.< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

2.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引.in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描，

3.like KK% 一般情况都会走索引

其他建议

1.最左前缀法则--如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

2.范围查询-可以将大的范围拆分成多个小范围。：mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。避免由于单次数据量查询过大导致优化器最终选择不走索引

3.尽量使用覆盖索引，减少 select * 语句，避免回表

三、Mysql事务隔离级别与锁机制

多个事务并发操作同一批数据，可能会导致脏写、脏读、不可重复读、幻读。为了解决这些问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制。

(一)事务

1.1事务及ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有四个属性，简称ACID属性。

原子性(Atomicity)：

事务是一个原子操作，其对数据的修改操作，要么都执行，要么都不执行。

一致性(Consistent)：

在事务开始和完成时,数据都必须保持一致状态（要么都修改成功，要么都修改不成功），才能保持数据的完整性。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。也就是说事务中，所有数据的操作状态都必须一致，要么都修改成功，要么都没有修改成功，它和原子性有些类似，不同的是原子性针对的是操作，一致性针对的是数据。

举个例子，一个订单事务中，先下单然后减库存，不允许下单成功减库存失败

隔离性(Isolation)：

数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。说白了就是我在自己事务里的操作，不被其它事务影响，也无法影响到其它事务。

比方说在我的事务中第一次查询库存是10，未做修改，再次查询还是还是10，其它事务就算是对这个库存做了修改，依旧未影响到我。

持久性(Durable)：

持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。

1.2并发事务带来问题

更新丢失或脏写：

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题。最后的更新覆盖了由其他事务所做的更新

脏读

一句话：事务A读取到了事务B已经修改但尚未提交的数据(脏数据)，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

举个例子：库存为10，事务B对库存进行减5，库存变为5，但是没有提交。事务A获取到B事务未库存5，然后对库存减1后库存变为4后提交事务。B事务回滚，库存变为10,事务A读取的数据5是脏数据

不可重复读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变或某些记录已经被删除了！这种现象就叫做“不可重复读”。

一句话：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性

幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。

一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

如：事务A根据id<5查询到两条记录，事务B新增了一条id=3记录并提交了事务，事务A再次查询id<5的记录时变成了3条记录

1.3事务的相关操作

事务自动提交

MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。SET AUTOCOMMIT=0 设置禁止自动提交，SET AUTOCOMMIT=1 开启自动提交。

事务手动操作

开始事务:begin或start transaction

事务中创建回滚点（保存点，可有多个）：savepoint <savepoint_name>

释放回滚点:release savepoint <savepoint_name>

回滚至回滚点：rollback to <savepoint_name>

回滚事务：rollback或rollback work

提交事务：commit或commit work提交事务

注意：

commit与commit work区别是它们的区别在于，commit work 可以控制事务接收后的行为(即事务提交完成下一步干啥)，可以用 select @@completion_type 查看值，默认是0。

completion_type = 0，两者等价。

completion_type = 1，commit work 等价于 commit and chain，表示马上开启一个相同隔离级别的事务。

completion_type = 2，commit work 等价于 commit and release，表示事务提交后自动断开与服务器的连接。

事务隔离级别

查看事务隔离级别： show variables like 'tx_isolation';

设置事务隔离级别：set tx_isolation='REPEATABLE-READ';

Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔离级别，如果Spring设置了就用已经设置的隔离级别

1.4事务隔离级别

脏读、不可重复读和幻读,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。

事务的隔离级别

读未提交：事务中能够读取到没有被提交的数据，脏读、不可重复读、幻读都无法解决。

读已提交：事务中可以读取到其他事务提交的数据，不能读取到未提交的事务，可以解决脏读问题。

可重读：可以重复读，就是每次读取的结果集都相同，不管其他事务有没有提交，读和写两个操作默认是不会加锁。解决了不可重复读问题及脏读问题，不能解决幻读问题。

解决脏读用到是MVCC机制（即MVCC机制使得其它事务修改提交后，我读到数据和最开始读的一致，但是我对该数据的修改还是基于其它事务提交的数据）。

幻读：幻读是虽然我看不到，但可以感知到，体现在：其它事务新增了记录，我虽然可重复读（暂时看不到新增数据），但是对新增的数据进行操作后（修改），我就可以看到新增的数据

串行化：

加锁的方式实现串行化。select 会对涉及到的查询结果加锁，其它事务修改该数据时阻塞来解决不可重复读；

注意：

1.数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。

2.不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读"和“幻读”并不敏感,可能更关心数据并发访问的能力。

(二)锁机制

2.1锁分类

从性能上分为乐观锁和悲观锁

乐观锁：用版本对比来实现，如CAS，认为别人不会修改，修改时会校验是否发生了变化，数据提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回给用户错误的信息，让用户决定如何去做。

悲观锁：在整个数据处理过程中，将数据处于锁定状态，事务没有提交之前其它事务一直等待

从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分，分为表锁和行锁

2.2表锁和行锁

2.2.1表锁

每次操作锁住整张表，特点有：

开销小，加锁快--直接找到表进行加锁

不会出现死锁--共享资源就同一张表

锁定粒度大，发生锁冲突的概率最高，并发度最低

用的不多，一般用在整表数据迁移的场景，给表加一个读锁，将无法对表进行数据更新

基本操作

手动增加表锁 lock table 表名称 read\write,表名称2 read(write);

查看表上加过的锁 show open tables;

删除表锁 unlock tables;

案例分析-加读锁

当前session和其他session都可以读该表

当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待

案例分析-加写锁

当前session对该表的增删改查都没有问题，其他session对该表的所有操作被阻塞

2.2.2行锁

每次操作锁住一行数据，特点有：

开销大，加锁慢--行锁要一条一条的找到那条记录去加锁，无索引，数据量大时，就很慢

会出现死锁--两个线程A B分别加锁1 2列数据，却都想先操作对方加锁的数据

锁定粒度最小，发生锁冲突的概率最低，并发度最高。

加锁：

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；

显示的给SELECT加锁：

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

2.2.3InnoDB与MYISAM对比

1.InnoDB与MYISAM的最大不同有两点：InnoDB支持事务（TRANSACTION)和支持行级锁

2.MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。

3.InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

4.简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

2.3.间隙锁(Gap Lock)与临键锁

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，可以利用间隙锁在某些情况下解决幻读问题。

间隙，指的是没有数据的区间，如上图数据库表有数据的id为1、2、3、10、20，那么间隙则包括(3.10),(10,20),(20,正无穷)。当我们在一个事务中对一个范围内数据进行修改操作时，这个范围所包含的所有行记录以及和这个范围有交集的间隙会被加锁，其它事务session在这个范围内都无法修改数据。行锁与间隙锁的组合就是临键锁。例如

执行update account set name = 'zhuge' where id > 8 and id<18，被加锁的间隙包括(3.10)、(10,20)，(3,20).行锁与间隙锁的组合就是临键锁

注意：

锁主要是加在索引上，如果对非索引字段更新，可重复读隔离级别下，行锁可能会变表锁。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

因为加锁是对索引加锁，索引不存在，无法加锁，那它实现原理可能是:先将这个间隙区间暂存或加上间隙锁标识，然后插入数据时判断id是否在加锁间隙区间

2.4行锁分析及优化建议

2.4.1lock状态变量

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量

Innodb_row_lock_time: 从系统启动到现在锁定总时间长度

Innodb_row_lock_time_avg: 每次等待所花平均时间

Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间

Innodb_row_lock_waits: 系统启动后到现在总共等待的次数

对于这5个状态变量，比较重要的主要是：

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

这几个值比较大就要进行分析了,尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

查看INFORMATION_SCHEMA系统库锁相关数据表

• - 查看事务,可以查找到Mysql对应的线程ID(trx_mysql_thread_id)

select * from INFORMATION_SCHEMA.INNODB_TRX;

• - 查看锁

select * from INFORMATION_SCHEMA.INNODB_LOCKS;

• - 查看锁等待

select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

• - 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到

kill trx_mysql_thread_id

• - 查看锁等待详细信息（输出一系列信息，可以找到锁等待的sql）

show engine innodb status\G;  

2.4.2死锁

set tx_isolation='repeatable-read';

for update是一种行级锁

Session_1执行：select * from account where id=1 for update;

Session_2执行：select * from account where id=2 for update;

Session_1执行：select * from account where id=2 for update;

Session_2执行：select * from account where id=1 for update;

查看近期死锁日志信息：show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

2.4.3锁优化建议

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽可能减少检索条件范围，避免间隙锁

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离

四、MVCC多版本并发控制机制及BufferPool缓存机制

(一)MVCC多版本并发控制机制

Mysql在可重复读隔离级别下是靠MVCC-多版本并发控制机制来保证事务较高的隔离性。串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的，而MVCC对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性,它是由undo日志版本链与read view比对机制实现。

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

1.undo log版本链

每行数据(除了自身字段外)其实都有两个隐藏字段，trx_id和roll_pointer。trx_id指的是生成这个数据事务id；数据被修改后，mysql会保留修改前的数据（即原始数据），roll_pointer指向的就是前的数据，而这个原始数据就是undo log。

表中的一行记录被多个事务修改或一个事务修改多久就会生成多个undo log，undo log通过roll_pointer指向形成了undo log版本链，这个版本链只有一份。undo log版本链再加上最新数据，形成了记录版本链。如下图：

2.一致性视图read-view

可重复读隔离级别下，事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前都不会变化，除非有在当前事务做了修改操作。读已提交隔离级别在每次执行查询sql时都会重新生成read-view。

read-view视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id组成（称为max_id）。如下图：

3.undo日志版本链与read view比对

事务里的任何sql查询结果需要从对记录应版本链里的最新数据开始逐条跟read-view做比对（逐条指的是若这条记录没有比对上就比对下一条记录，直到找到判定未可见的记录为止）从而得到最终的快照结果。比对规则为：

1. 根据read-view划分出三个区间，小于min_id的区间为绿色部分，表示的是已提交的事务，[min_id,max_id]为黄色部分，包含则已提交和未提交的事务，大于max_id为红色部分表示未开始事务。如下图：

2.然后比对，看row 的 trx_id落在三个区间区间的哪个部分及事务trx_id是否落在视图的未执行事务数组中：

落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的数据，是可见的；

落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的；

落在黄色部分 (min_id <trx_id<= max_id) 那就到read-view未提交事务数组对比。 若在未提交事务数组中，不可见；· 不在说明是已经提交了的事务生成的， 可见。

对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数据。

注意：

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，事务才真正启动，才会向mysql申请事务id，

mysql内部是严格按照事务的启动顺序来分配事务id的。

(二)BufferPool缓存机制

1.InnoDB下，一条update语句执行机制

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB。一条update语句（将id=1d的name修改为666）在InnoDB的过程为：

1.SQL语句经过分析器确定要做什么，优化器确定执行方案，执行器判断了权限后调用引擎接口处理数据。

2.数据处理

2.1在InnoDB先将要修改的记录,从磁盘文件(idb)中，加载到Buffer Pool缓存池中，注意，数据是一页一页存放，记录所在的整页数据都会被加载

2.2将旧的值写入到undo日志文件中，用于事务提交失败数据回滚，将undo日志文件中数据恢复到buffer pool

2.3（执行器）更新内存数据，将缓存数据修改为指定的值

2.4（执行其）更新redo日志，redo日志记录的是缓存中修改的记录。redo日志使用的也是缓存机制，先把日志信息存放到缓存中。

2.5准备提交事务，redo日志Buffer中的记录批量写入到redo日志文件中。

2.6准备提交事务，（执行器）将binlog日志写入磁盘，记录在binlog文件

2.7binlog文件写完，写入commit标记到redo日志文件里 ，然后真正提交事务。写入commit标记为了保证事务提交后redo与binlog数据一致

binlog数据用于恢复磁盘数据，redo日志用于恢复buffer pool数据,避免buffer pool中数据h哎未写入磁盘，系统宕机了buffer pool的数据恢复问题。

2.8通过IO线程，将Buffer Pool修改后的数据，以page为单位随机写入到磁盘文件(ibd)

2.为什么Mysql不能直接更新磁盘上的数据而且设置这么一套复杂的机制来执行SQL了？

mysql对数据的磁盘文件是随机读写的，性能是非常差，直接更新磁盘文件是不能让数据库抗住很高并发的。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件（顺序IO与随机IO速度差3个数量级，几乎和内存相媲美）,而BufferPool和日志文件分别用了更新内存和顺序写。Mysql这套机制看起来复杂,但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干的读写请求。

3.为什写磁盘是随机IO而日志是顺序IO?

因为redo日志文件只追加，不删除文件，同kafka文件一样可顺序读写

mysql数据文件中page页，会有删除，导致page不连续，无法顺序读写