MySQL教程阅读笔记学习mysql相关技术有感，记录下的笔记。http://www.itsoku.com/course

事务详解

9.1 事务的ACID特性

事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性

原子性(Atomicity)

事务的整个过程如原子操作一样，最终要么全部成功，或者全部失败，这个原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。

一致性(Consistency)

一个事务必须使数据库从一个一致性状态变换到另一个一致性状态。

首先回顾一下一致性的定义。所谓一致性，指的是数据处于一种有意义的状态，这种状态是语义上的而不是语法上的。最常见的例子是转帐。例如从帐户A转一笔钱到帐户B上，如果帐户A上的钱减少了，而帐户B上的钱却没有增加，那么我们认为此时数据处于不一致的状态。

从这段话的理解来看，所谓一致性，即，从实际的业务逻辑上来说，最终结果是对的、是跟程序员的所期望的结果完全符合的

也就是库存数量+订单数量永远是等于最初的库存总数的，比如原本的总库存是500个，此时库存剩余200个，那也就代表着必须要有300条订单数据才行

但来思考一个问题

如果事务执行过程中，刚减完库存后，MySQL所在的服务器断电了咋整？似乎无法利用事务回滚机制去确保数据一致性了撒？对于这点大可不必担心，因为MySQL宕机重启后，会通过分析日志的方式恢复数据，确保一致性

隔离性(Isolation)

一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

隔离性在底层是如何实现的呢？基于MySQL的锁机制和MVCC机制做到的

持久性(Durability)

一个事务一旦提交，他对数据库中数据的改变就应该是永久性的。当事务提交之后，数据会持久化到硬盘，修改是永久性的。

就算MySQL宕机也不会影响数据改变，因为宕机后也可以通过日志恢复数据

9.2 Mysql中事务操作

mysql中事务默认是隐式事务，执行insert、update、delete操作的时候，数据库自动开启事务、提交或回滚事务。是否开启隐式事务是由变量autocommit控制的。

在MySQL默认情况下，一条SQL会被视为一个单独的事务，同时也无需咱们手动提交，因为默认是开启事务自动提交机制的，如若你想要将多条SQL组成一个事务执行，那需要显式的通过一些事务指令来实现。

所以事务分为隐式事务和显式事务。

事务是基于当前数据库连接的，每个连接之间的事务是具备隔离性的

当你在Navicat、SQLyog这类可视化工具中，新建一个查询时，本质上它就是给你建立了一个数据库连接，每一个新查询都是一个新的连接

隐式事务

事务自动开启、提交或回滚，比如insert、update、delete语句，事务的开启、提交或回滚由mysql内部自动控制的。

查看变量autocommit是否开启了自动提交：show variables like 'autocommit'; autocommit为ON表示开启了自动提交。

显式事务

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

2种方式手动控制事务：

set autocommit=0; #设置不自动提交事务
#执行事务操作
commit|rollback;

或者

start transaction; #开启事务也可以使用begin
#执行事务操作
commit|rollback;

事务是基于当前数据库连接的，每个连接之间的事务是具备隔离性的

当你在Navicat、SQLyog这类可视化工具中，新建一个查询时，本质上它就是给你建立了一个数据库连接，每一个新查询都是一个新的连接

savepoint关键字事务回滚点

start transaction;

insert into test1 values (1);

savepoint part1; # 设置一个保存点

insert into test1 values (2);

rollback to part1; # 将savepint = part1的语句到当前语句之间所有的操作回滚

commit; # 提交事务

select * from test1;

从上面可以看出，执行了2次插入操作，最后只插入了1条数据。

savepoint需要结合rollback to part1一起使用，可以将保存点part1到rollback to之间的操作回滚掉。

应用场景：在某些SQL执行成功后，但后续的操作有可能成功也有可能失败，但不管成功亦或失败，你都想让前面已经成功的操作生效时，此时就可在当前成功的位置设置一个回滚点。当后续操作执行失败时，就会回滚到该位置，而不是回滚整个事务中的所有操作，这个机制则称之为事务回滚点。

应用场景2：spring事务方法A中调用事务方法B，如果想B抛出异常回滚的时候不影响A事务的提交。可以在调用B方法之前设置回滚点X，在B抛出异常后回滚到X这个事务点，然后A执行完成后提交事务即可

只读事务

表示在事务中执行的是一些只读操作，如查询，但是不会做insert、update、delete操作，数据库内部对只读事务可能会有一些性能上的优化。用法如下：

start transaction read only;

示例：

START TRANSACTION READ only;
update asset set name = 'xxx' where id = 1 ;
commit;

执行失败，MySQL报错：[Err] 1792 - Cannot execute statement in a READ ONLY transaction.

并发事务处理带来的问题

更新丢失(Lost Update)或脏写

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

脏读（Dirty Reads）

　　一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。

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

不可重读（Non-Repeatable Reads）

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

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

（在一个事务A中多次操作一个数据，在这两次或多次访问这个数据的中间，事务B也操作此数据，并使其值发生了改变，这就导致同一个事务A在两次操作这个数据的时候值不一样，这就是不可重复读）

幻读（Phantom Reads）

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

　　一句话：事务A读取到了事务B提交的新增数据，不符合隔离性。（是指事务不独立执行产生的一种现象。事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。这样就会导致当A本来执行的结果包含B执行的结果，这两个本来是不相关的，对于A来说就相当于产生了“幻觉”）

一句话概括MySQL数据库并发事务导致的问题:

脏读（事务A读取到了事务B已经修改但尚未提交的数据，然后B事务回滚了）
不可重复读（事务A内部的相同查询语句在不同时刻读出的结果不一致）
幻读（另外一个事务在第一个事务要处理的目标数据范围之内新增了数据，然后先于第一个事务提交造成的问题）
脏写（多个事务一起操作同一条数据）

mysql的不可重复读和幻读有什么区别？

这两者有些相似。但不可重复读重点在于update，而幻读的重点在于insert和delete。以下是四位网友的观点：

网友一回复：

不可重复读只的是查同一条数据，结果不同。比如按身份证号查人，一会叫张三一会叫李四。幻读就是查到了别的数据，比如查叫张三的人，一会是3个一会是4个。参考：juejin.cn/post/687660…

网友二回复：

不可重复读 where name = 豆豆，第一次查出来 name 豆豆 age 18 ，第二次 name 豆豆 age 180幻读 where name = 豆豆，第一次 total 1 （name 豆豆 age 18）第二次 total 10000 （namn 豆豆 age ，1 .。。。。。。）

网友三回复：

不可重复读：对于一个数据的读取，前后两次读取数据不一致。幻读：对于范围查询，前一次查询了2条，下一次查询出来3条，就像产生了幻觉，开始看见2个人，后面看见了3个人

网友四回复：

不可重复读侧重于修改，幻读侧重于新增删除.

9.3 事务隔离级别

当多个事务同时进行的时候，如何确保当前事务中数据的正确性，比如A、B两个事物同时进行的时候，A是否可以看到B已提交的数据或者B未提交的数据，这个需要依靠事务的隔离级别来保证，不同的隔离级别中所产生的效果是不一样的。

事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。

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

但如若想要真正理解事务隔离级别，得先明白因为并发事务操作造成的问题：脏读、幻读、不可重复读、脏写问题

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

各种隔离级别中会出现的问题

隔离级别	脏读	不可重复读	幻读
读未提交：READ-UNCOMMITTED	有	有	无
读已提交：READ-COMMITTED	无	有	无
可重复读：REPEATABLE-READ	无	无	有
串行化：SERIALIZABLE	无	无	无

MySQL默认为第三级别：Repeatable read可重复读

表格中和网上有些不一样，主要是幻读这块，幻读只会在可重复读级别中才会出现，其他级别下不存在

上面4种隔离级别越来越强，会导致数据库的并发性也越来越低

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

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

设置隔离级别

1. 修改系统中事务隔离级别

修改mysql中的my.init文件，我们将隔离级别设置为：READ-UNCOMMITTED，如下： transaction-isolation=READ-UNCOMMITTED，然后重启MySQL

2. 设置当前会话中的事务隔离级别

set transaction_isolation='READ-COMMITTED'; # mysql 8 以前 是tx_isolation

打开另一个会话，执行如下sql发现两个会话的事务隔离级别不一样了

show variables like '%isolation%';

关于生产环境中隔离级别的选择

需要对各种隔离级别产生的现象非常了解，然后选择的时候才能游刃有余
隔离级别越高，并发性也低，比如最高级别SERIALIZABLE会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。
具体选择哪种需要结合具体的业务来选择。
读已提交（READ-COMMITTED）通常用的比较多。

10. 视图

视图是在mysql5之后出现的，是一种虚拟表，行和列的数据来自于定义视图时使用的一些表中，视图的数据是在使用视图的时候动态生成的，视图只保存了sql的逻辑，不保存查询的结果。

使用场景

多个地方使用到同样的查询结果，并且该查询结果比较复杂的时候，我们可以使用视图来隐藏复杂的实现细节。

视图和表的区别

	语法	实际中是否占用物理空间	使用
视图	create view	只是保存了sql的逻辑	增删改查，实际上我们只使用查询
表	create table	保存了数据	增删改查

视图的好处

简化复杂的sql操作，不用知道他的实现细节
隔离了原始表，可以不让使用视图的人接触原始的表，从而保护原始数据，提高了安全性

11. 变量

变量主要分两类：

系统变量：系统变量由系统定义的，不是用户定义的，属于mysql服务器层面的。它也分两类：全局变量、会话变量
自定义变量：变量由用户自定义的，而不是系统提供的。它也分两类：用户变量、局部变量

11.1 系统变量

全局变量

作用域：mysql服务器每次启动都会为所有的系统变量设置初始值。我们为系统变量赋值，针对所有会话（连接）有效，可以跨连接，但不能跨重启，重启之后，mysql服务器会再次为所有系统变量赋初始值。

查看所有的全局变量：show global variables;

全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认为session级别

查询某全局变量，有2种写法：

select @@global.autocommit;
show global variables like '%autocommit%';

给全局变量设置值（一般需要超管权限，否则提示权限不足），有2种写法：

set global autocommit=0;
set @@global.autocommit=1;

会话变量

作用域：针对当前会话（连接）有效，不能跨连接。会话变量是在连接创建时由mysql自动给当前会话设置的变量。

查看所有的会话变量：show session variables; 或者 show variables;

全局变量需要添加global关键字，会话变量需要添加session关键字，如果不写，默认为session级别

查询某会话变量，有4种写法：

select @@autocommit;
select @@session.autocommit;
show variables like '%autocommit%';
show session variables like '%autocommit%';

给会话变量设置值，有4种写法：

set @@autocommit=0;
set @@session.autocommit=0;
set session autocommit = 0;
set autocommit = 1;

11.2 自定义变量

用户变量

作用域：针对当前会话（连接）有效，作用域同会话变量。用户变量可以在任何地方使用也就是既可以在begin end里面使用，也可以在他外面使用。

声明并初始化(要求声明时必须初始化)，有4种写法：

set @id1=1;
set @id2:=2;
select @id3:=3;
select 'abc' into @id4;
# 查询这4个变量的值
select @id1,@id2,@id3,@id4;

更新变量的值，有5种写法：

set @id1=4;
set @id2:=5;
select @id3:=6;
select 'abcd' into @id4;
select id into @id5 from acl_class limit 1;
# 查询这5个变量的值
select @id1,@id2,@id3,@id4,@id5;

局部变量

作用域：declare用于定义局部变量变量，在存储过程和函数中通过declare定义变量在begin…end中，且在语句之前。并且可以通过重复定义多个变量。declare变量的作用范围同编程里面类似，在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了，不能使用了。这个同编程一样。

11.3 用户变量和局部变量对比

	作用域	定义位置	语法
用户变量	当前会话	会话的任何地方	加`@`符号，不用指定类型
局部变量	定义他的begin end之间	begin end中的第一句话	不加`@`符号，要指定类型

11.4 总结

系统变量可以设置系统的一些配置信息，数据库重启之后会被还原
会话变量可以设置当前会话的一些配置信息，对当前会话起效
declare创建的局部变量常用于存储过程和函数的创建中
作用域：全局变量对整个系统有效、会话变量作用于当前会话、用户变量作用于当前会话、局部变量作用于begin end之间
注意全局变量中用到了@@，用户变量变量用到了@，而局部变量没有这个符号
delimiter关键字用来声明脚本的结束符

12. 索引

12.1 索引是什么？

索引是依靠某些数据结构和算法来组织数据，最终引导用户快速检索出所需要的数据。

索引有2个特点：

通过数据结构和算法来对原始的数据进行一些有效的组织
通过这些有效的组织，可以引导使用者对原始数据进行快速检索

mysql为了快速检索数据，也用到了一些好的数据结构和算法，来组织表中的数据，加快检索效率。

索引的本质：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

12.2 磁盘物理结构和运行原理

磁盘的物理结构如下图所示：

一个磁盘有多个盘面组成，一个盘面的上下两个面都是可以进行数据读写的，每个面都有一个读写磁头（Head），每个盘面是由很多半径不同的同心圆组成的，每个同心圆是一个磁道（Track），不同盘面的同一磁道组成一个柱面（Cylinder），每个磁道又可以划分为多个扇区（Sector），每个扇区能存储512字节数据，因此，磁盘容量 = C * H * S * 512 byte。

磁盘的最小单位是扇区，计算磁盘的容量等价于计算磁盘中扇区的数量。

12.3 磁盘的运行原理

由于每个盘面上都有磁头，当磁盘高速旋转的时候，会与盘面保持一个很小的距离，磁头就可以进行数据读取，这就是磁盘运行的基本原理。

给定任意一个扇区，如果想要读取该扇区的数据，需要三个步骤：

寻道：通过移动磁头，找到数据所在的磁道（柱面），这个过程叫寻道延迟，大概会消耗3～15ms；
旋转：通过旋转定位到磁头上具体的扇区，这个过程叫旋转延迟，大概会消耗2～4ms；
读写数据：这个过程速度很快，可以忽略不计。

因此，读写一个扇区的数据需要大约9ms左右。

从上述过程中，我们可以分析出两个结论：1、做数据读写的时候，寻道和旋转是最耗时的，因为他们都是机械运动，而数据读取的速度很快，为了充分利用这次寻找扇区所花费的时间，我们可以多读取一些数据，比如4KB，这样并不会增加一次数据读写的时间，反而提升了数据读写的效率；2、既然寻道和循转最耗时，在数据读写过程中，如果能减少这两种操作，就可以提升数据读写的效率。

12.4 磁盘中数据的存取

以机械硬盘来说，先了解几个概念。

扇区：磁盘存储的最小单位，扇区一般大小为512Byte。

磁盘块：文件系统与磁盘交互的的最小单位（计算机系统读写磁盘的最小单位），一个磁盘块由连续几个（ $2^n$ ）扇区组成，块一般大小一般为4KB。

磁盘读取数据：磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

12.5 mysql中的页

mysql中和磁盘交互的最小单位称为页，页是mysql内部定义的一种数据结构，默认为16kb，相当于4个磁盘块，也就是说mysql每次从磁盘中读取一次数据是16KB，要么不读取，要读取就是16KB，此值可以修改的。

这是什么意思呢？其实就是指MySQL一次磁盘IO不仅仅只会读取一条表数据，而是会读取多条数据，那到底读多少条数据呢？在InnoDB引擎中，一次默认会读取16KB数据到内存。

12.6 数据检索过程

我们对数据存储方式不做任何优化，直接将数据库中表的记录存储在磁盘中，假如某个表只有一个字段，为int类型，int占用4个byte，每个磁盘块可以存储1000条记录，100万的记录需要1000个磁盘块，如果我们需要从这100万记录中检索所需要的记录，需要读取1000个磁盘块的数据（需要1000次io），每次io需要9ms，那么1000次需要9000ms=9s，100条数据随便一个查询就是9秒，这种情况我们是无法接受的，显然是不行的。

12.7 我们迫切的需求是什么？

我们迫切需要这样的数据结构和算法：

需要一种数据存储结构：当从磁盘中检索数据的时候能，够减少磁盘的io次数，最好能够降低到一个稳定的常量值
需要一种检索算法：当从磁盘中读取磁盘块的数据之后，这些块中可能包含多条记录，这些记录被加载到内存中，那么需要一种算法能够快速从内存多条记录中快速检索出目标数据

我们来找找，看是否能够找到这样的算法和数据结构。

12.8 常见的检索算法和数据结构

循环遍历查找

支持数组无序，查找时间复杂度O(n)

二分法查找（折半查找）

前提是：目标数组是有序的，查找时间复杂度是:O(logN)，定位数据非常快（100万数据查找最多只需要20次（2^20=1048576））

有序数组

如果我们将mysql中表的数据以有序数组的方式存储在磁盘中，那么我们定位数据步骤是：

取出目标表的所有数据，存放在一个有序数组中
如果目标表的数据量非常大，从磁盘中加载到内存中需要的内存也非常大

步骤取出所有数据耗费的io次数太多，步骤2耗费的内存空间太大，还有新增数据的时候，为了保证数组有序，插入数据会涉及到数组内部数据的移动，也是比较耗时的，显然用这种方式存储数据是不可取的。

单向链表、双向链表

优点：

可以快速定位到上一个或者下一个节点
可以快速删除数据，只需改变指针的指向即可，这点比数组好

缺点：

无法向数组那样，通过下标随机访问数据
查找数据需从第一个节点开始遍历，不利于数据的查找，查找时间和无需数据类似，需要全遍历，最差时间是O(N)

二叉查找树

二叉树是每个结点最多有两个子树的树结构，通常子树被称作“左子树”（left subtree）和“右子树”（right subtree）。二叉树常被用于实现二叉查找树和二叉堆。二叉树有如下特性：

1、每个结点都包含一个元素以及n个子树，这里0≤n≤2。
2、左子树和右子树是有顺序的，次序不能任意颠倒，左子树的值要小于父结点，右子树的值要大于父结点。

二叉树的优缺点：

查询数据的效率不稳定，若树左右比较平衡的时，最差情况为O(logN)，如果插入数据是有序的，退化为了链表，查询时间变成了O(N)
数据量大的情况下，会导致树的高度变高，如果每个节点对应磁盘的一个块来存储一条数据，需io次数大幅增加，显然用此结构来存储数据是不可取的

平衡二叉树（AVL树）

平衡二叉树是一种特殊的二叉树，所以他也满足前面说到的二叉查找树的两个特性，同时还有一个特性：

它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

平衡二叉树相对于二叉树来说，树的左右比较平衡，不会出现二叉树那样退化成链表的情况，不管怎么插入数据，最终通过一些调整，都能够保证树左右高度相差不大于1。

这样可以让查询速度比较稳定，查询中遍历节点控制在O(logN)范围内

如果数据都存储在内存中，采用AVL树来存储，还是可以的，查询效率非常高。不过我们的数据是存在磁盘中，用过采用这种结构，每个节点对应一个磁盘块，数据量大的时候，也会和二叉树一样，会导致树的高度变高，增加了io次数，显然用这种结构存储数据也是不可取的。

B-树

B杠树，千万不要读作B减树了，B-树在是平衡二叉树上进化来的，前面介绍的几种树，每个节点上面只有一个元素，而B-树节点中可以放多个元素，主要是为了降低树的高度。

一棵m阶的B-Tree有如下特性：

每个节点最多有m个孩子，m称为b树的阶

除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子

若根节点不是叶子节点，则至少有2个孩子

所有叶子节点都在同一层，且不包含其它关键字信息

每个非终端节点包含n个关键字（健值）信息

关键字的个数n满足：ceil(m/2)-1 <= n <= m-1

ki(i=1,…n)为关键字，且关键字升序排序

Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值（行中的主键），data为一行记录中除主键外的数据（行数据）。对于不同的记录，key值互不相同。

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个键将数据划分成的三个范围域，对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29

分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作，由于内存中的关键字是一个有序表结构，可以利用二分法快速定位到目标数据，而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。

B-树相对于avl树，通过在节点中增加节点内部数据的个数来减少磁盘的io操作。

上面我们说过mysql是采用页方式来读写数据，每页是16KB，我们用B-树来存储mysql的记录，每个节点对应mysql中的一页（16KB），假如每行记录加上树节点中的1个指针占160Byte，那么每个节点可以存储1000（16KB/160byte）条数据，树的高度为3的节点大概可以存储（第一层1000+第二层 $1000^{2}$ +第三层 $1000^{3}$ ）10亿条记录，是不是非常惊讶，一个高度为3个B-树大概可以存储10亿条记录，我们从10亿记录中查找数据只需要3次io操作可以定位到目标数据所在的页，而页内部的数据又是有序的，然后将其加载到内存中用二分法查找，是非常快的。

可以看出使用B-树定位某个值还是很快的(10亿数据中3次io操作+内存中二分法)，但是也是有缺点的：B-不利于范围查找，比如上图中我们需要查找[15,36]区间的数据，需要访问7个磁盘块（1/2/7/3/8/4/9），io次数又上去了，范围查找也是我们经常用到的，所以b-树也不太适合在磁盘中存储需要检索的数据。

B+树

先看个B+树结构图：

B+树的特征如下：

每个结点至多有m个子女

除根结点外,每个结点至少有[m/2]个子女，根结点至少有两个子女

有k个子女的结点必有k个关键字

父节点中持有访问子节点的指针

父节点的关键字在子节点中都存在（如上面的1/20/35在每层都存在），要么是最小值，要么是最大值，如果节点中关键字是升序的方式，父节点的关键字是子节点的最小值

最底层的节点是叶子节点

除叶子节点之外，其他节点不保存数据，只保存关键字和指针

叶子节点包含了所有数据的关键字以及data，叶子节点之间用链表连接起来，可以非常方便的支持范围查找

由于B-树不适合于大范围查询操作，因此B+树中多了个指针，当需要做范围查询时，只需要定位第一个节点，然后就可以直接根据各节点之间的指针，获取到对应范围之内的所有节点，也就是只需要发生一次IO，就能够确定所查范围之内的所有数据位置。

B+树与B-树的不同

B+树中一个节点如果有k个关键字，最多可以包含k个子节点（k个关键字对应k个指针）；而B-树对应k+1个子节点（多了一个指向子节点的指针）
B+树除叶子节点之外其他节点值存储关键字和指向子节点的指针，而B-树还存储了数据，这样同样大小情况下，B+树可以存储更多的关键字
B+树叶子节点中存储了所有关键字及data，并且多个节点用链表连接，从上图中看子节点中数据从左向右是有序的，这样快速可以支撑范围查找（先定位范围的最大值和最小值，然后子节点中依靠链表遍历范围数据）

B-Tree和B+Tree该如何选择？

B-Tree因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而B+Tree所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更高。
由于B+Tree所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，而B-Tree还需要遍历该关键字结点的根结点去搜索。
由于B-Tree的每个结点（这里的结点可以理解为一个数据页）都存储主键+实际数据，而B+Tree非叶子结点只存储关键字信息，而每个页的大小有限是有限的，所以同一页能存储的B-Tree的数据会比B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更大，增大查询时的磁盘I/O次数，进而影响查询效率。

千万级别的表B+Tree会有多高？

MySQL中一张千万级别的数据表，如果基于自增ID的主键字段建立B+树索引，那此时树会有多高呢？想要计算出树高，首先得有三个值：

计算索引字段值的大小：如果表的自增ID为int无符号类型，占4个字节，那基于ID建立主键索引时，B+Tree每个节点的索引键大小就为4个字节

计算B+树单个节点的大小：在MySQL中默认使用innodb引擎的一页大小作为单节点的容量，我们可以通过这条命令SHOW GLOBAL STATUS LIKE "Innodb_page_size";查询。默认情况下InnoDB引擎的一页大小是16KB，即B+Tree的每个节点容量为16KB。

MySQL中的指针大小：一般来说，操作系统的指针为了方便寻址，一般都与当前的操作系统位数对应，64位的操作系统指针则为64bit/8Bytes，但由于64bit的指针寻址范围太大，目前的计算机根本用不上这么大的寻址范围，因此在InnoDB引擎的源码中，单个指针被缩小到6Bytes大小。

千万级别的索引树高计算：一个完整的索引信息是由主键字段值+指针组成的，也就是4+6=10B，那此时先来计算一下单个节点中可存储多少个索引信息呢？

16KB / (4B + 6B) ≈ 1638个

那此时来计算一下，对于一颗高度为2的B+树，根节点可存储1638个叶子节点指针，也就代表着B+Tree的第二层有1638个叶子节点，因为叶子节点要存储实际的行数据，假设表中每行数据为1KB，这也就是代表着一个叶子节点中可存储16条行数据，那么一颗高度为2的B+树可存储的索引信息为：1638 * 16 = 26208条数据。

再来算算树高为3的B+树可以存多少呢？因为最下面一排才是叶子节点，此时树高为3，也就代表着中间一排是叶节点，只存储指针并不存储数据，而每个节点可容纳1638个索引键+指针信息，因此计算过程是：1638 * 1638 * 16 = 42928704条。

是不是很令你惊讶？树高为3的B+Tree，竟然可以存储四千多万条数据，也就代表着千万级别的表，走索引查询的情况下，大致只需要发生三次磁盘IO即可获取数据。

当然，上述的这个数据是基于主键为int类型、表的一行数据为1KB来计算的，实际情况中会不一样，因为主键有可能是bigint类型或其他类型，而一行数据也可能不仅仅只有1KB。因此对于一张实际的千万级别表，它的主键索引实际树高有多少，你结合主键的数据类型以及一行数据的大小，也可以计算出来，它同时不会太高。
对实际的千万表索引树高感兴趣的，我提供一个计算公式：索引键大小=索引字段类型所占的空间、一行表数据大小=所有表字段的类型+隐藏字段（20Bytes）所占大小总和，得到这两个值之后，再套入前面的例子中既可得知。

不过B+Tree中的每个索引页中，还会存储页头（页号、指针、伪记录等）、页目录、页尾等信息，大概一共占用128Bytes左右，因此想要真正的计算出来接近实际情况的索引树高，还需要把这点考虑在内~

12.9 为什么不建议使用uuid作为主键索引

主键索引是聚簇索引，表数据和索引数据在一块、索引结构是有序的，为何不使用UUID呢？因为UUID是无序的，如果使用UUID作为主键，那么每当插入一条新数据，都有可能破坏原本的树结构。但使用自增ID就不会有这个问题，所有新插入的数据都会放到最后。

还有一个原因就是UUID比整数自增ID长，UUID至少占位32字节，但int类型只占4字节，存储一个UUID的空间，可以存8个自增整数ID。也就代表着单个节点中，能存储的自增ID会比UUID多很多

12.10 索引覆盖

由于表中只能存在一个聚簇索引，一般都为主键索引，而建立的其他索引都为辅助索引，包括联合索引也例外，最终索引节点上存储的都是指向主键索引的值

什么是索引覆盖：就是要查询的列，在使用的索引中已经包含，被所使用的索引覆盖，减少回表（使用主键再去主键索引在查一遍），这种情况称之为索引覆盖

但如果查询时用*，因为联合索引中不具备完整的一行数据，只能再次转向聚簇索引中获取完整的行数据，因此到这里大家应该也明白了为什么查询数据时，不能用*的原因，这是因为会导致索引覆盖失效，造成回表问题。

12.11 索引下推

也可以参考：juejin.cn/post/714907…

索引下推是MySQL5.6版本以后引入的一种优化机制，默认开启的，用户可以通过set optimizer_switch='index_condition_pushdown=off|on';命令来手动启用或关闭。

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

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

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

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

12.12 Index Skip Scan 索引跳跃式扫描

参考：juejin.cn/post/714907…

提到过最左前缀匹配原则，也就是SQL的查询条件中必须要包含联合索引的第一个字段，这样才能命中联合索引查询，但实际上这条规则也并不是100%遵循的。因为在MySQL8.x版本中加入了一个新的优化机制，也就是索引跳跃式扫描，这种机制使得咱们即使查询条件中，没有使用联合索引的第一个字段，也依旧可以使用联合索引，看起来就像跳过了联合索引中的第一个字段一样，这也是跳跃扫描的名称由来。

但是跳跃扫描机制也有很多限制，比如多表联查时无法触发、SQL条件中有分组操作也无法触发、SQL中用了DISTINCT去重也无法触发.....，总之有很多限制条件

通过通过set @@optimizer_switch = 'skip_scan=off|on';命令来选择开启或关闭跳跃式扫描机制，仅限MySQL8.0以上的版本

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

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

12.13 有没有什么办法可以尽量避免回表或让回表的开销变小呢？

业务手段：先走索引查询出目标记录的id，然后再通过id查询聚簇索引
Multi Range Read 多范围读取（和索引下推一同在MySQL5.6版本中引入的性能优化措施）
覆盖索引：对要查询的字段全部都建立索引，这样避免回表（不实际，需要评估二级索引存在列太多的维护成本）

回表

为什么会发生回表？

因为使用的索引并没有整条记录的所有信息，因此使用索引后不满足查询列表需要的列，就要回表查询聚簇索引

回表查询聚簇索引时，由于主键值是乱序的这样就会导致随机IO

什么是随机IO呢？

MySQL查询时，需要将磁盘的数据加载到缓冲池中，与磁盘交互的单位是页，页中存在多条记录

由于获取的是聚簇索引的页，那么该页中的主键值是有序的，但在二级索引上的记录主键值可能并不是有序的

比如图中第一条记录主键值为24记录在页A中，第二条记录主键值为82546记录在页C中

当遍历到第一条记录时需要去加载页A，当遍历下一条记录时需要去加载页C

当这种随机IO过多时，可能每查一条记录相当于要去加载一个页，成本非常大

不要小瞧回表的开销，当查询数据量大，使用二级索引都要回表的话，性能还不如全表扫描（扫描聚簇索引），这通常也是索引失效的一大场景（后续文章再来聊聊这块）

Multi Range Read 多范围读取

那有没有什么办法降低成本呢？

回表成本大的原因主要是产生随机IO，那能不能先在索引上查出多条记录，要回表时对主键值进行排序，让随机IO变成顺序IO呢

对主键值排序后每个加载的页，页中可能存在多条需要回表查询的记录就减少回表随机IO的开销

MySQL中另一个优化回表的手段是：Multi Range Read 多范围读取 MRR

MRR使用缓冲区对需要回表的记录根据主键值进行排序，将随机IO优化为顺序IO

使用MRR优化后图中第二条记录id为25回表时就可以直接在缓冲池的页A中获取完整记录

查看MRR缓冲池大小show variables like '%read_rnd_buffer_size%';

可以使用查看相关优化器的参数SHOW VARIABLES LIKE 'optimizer_switch';

有关MRR的优化器开关参数：mrr，mrr_cost_based

mrr 表示是否开启MRR

MRR还需要在缓冲池中排序的开销，因此并不是所有场景都用MRR，默认情况下启动mrr_cost_based基于成本判断是否要使用MRR

SET optimizer_switch='mrr=on,mrr_cost_based=off';关闭根据成本判断是否用MRR

附加信息携带Using MRR说明使用MRR

除了将随机IO优化为顺序IO，还有没有什么方式可以降低回表的开销呢？

我们从另一个角度分析，如果减少查询的数据量，是不是也可以减少回表次数，降低回表开销

那如何减少数据量呢？实际上上篇文章说过的ICP就可以减低回表次数

Covering Index 覆盖索引

回表无论如何优化都会存在一定的开销，那有没有可能避免回表呢？

要避免回表问题，那就要知道为什么会回表？

由于使用的二级索引不包含查询需要的字段，因此需要回表查询聚簇索引获取需要的字段

那如果使用的二级索引包含需要的查询字段是不是就避免回表的呢！

因此可以通过修改查询需要的字段select xx1,xx2或增加二级索引包含的列（变成联合索引）来避免出现回表

注意：如果你想通过增加二级索引的列来避免回表时，需要评估二级索引存在列太多的维护成本

MySQL中的覆盖索引指的是使用二级索引时不需要回表，在执行计划中的附加信息显示Using index

将查询列表从 * 改为 age,student_name ,使用二级索引时不需要回表

总结

当使用的二级索引不满足查询需要的列时，会进行回表查询聚簇索引获取完整记录
回表不仅需要再查一次聚簇索引，而且在二级索引中主键值可能是乱序的，因此查询聚簇索引会出现随机IO
查询随机IO时可能每条记录都在不同的页中，这会导致每查询一条记录就需要将磁盘中的页加载到缓冲池，随机IO开销很大
优化回表有两种思路：一种是降低回表的开销，另一种是避免回表
Index Condition Push 索引条件下推（上篇文章说的）可以减少回表次数，降低回表的开销
Multi Range Read 多范围读取在某些场景下使用缓冲池排序主机，将读取的随机IO转换为顺序IO，降低回表开销
修改查询需要的字段或者给二级索引上增加列，使用覆盖索引的方式来避免回表

使用MyISAM引擎的表

test_myisam_index这张表是使用MyISAM引擎的表，对应磁盘中3个文件：

test_myisam_index.frm：存储表的结构信息
test_myisam_index.MYD：存储表的行数据
test_myisam_index.MYI：存储表的索引数据

MyISAM引擎的表数据和索引数据，是分别放在两个不同的磁盘文件中存储的，这也意味着MyISAM引擎并不支持聚簇索引，因为聚簇索引要求表数据和索引数据一起存储在同一块空间，而MyISAM的.MYI索引文件中，存储的是表数据所在的地址指针。

使用InnoDB引擎的表

test_innodb_index这张表是使用InnoDB引擎的表，对应磁盘中2个文件：

test_innodb_index.frm：存储表的结构信息。
test_innodb_index.ibd：存储表的行数据和索引数据。

因为InnoDB引擎中，表数据和索引数据都一起放在.ibd文件中，也就代表着索引数据和表数据是处于同一块空间存储的，这符合聚簇索引的定义，因此InnoDB支持聚簇索引。

同时也正由于这个原因，所以如果使用InnoDB引擎的表未主动创建聚簇索引，它会自动选择表中的主键字段，作为聚簇索引的字段。但如果表中未声明主键字段，那则会选择一个非空唯一索引来作为聚簇索引。但如果表中依旧没有非空的唯一索引，InnoDB则会隐式定义一个主键来作为聚簇索引（这个列在上层是不可见的，是一个按序自增的值）。

聚簇索引和非聚簇索引的结构区别

在InnoDB非聚簇索引中，每个索引信息中存储聚簇索引的ID值。
MyISAM非聚簇索引中，每个索引信息中则直接存储行数据的指针。

聚簇索引和非聚簇索引的根本区别：

聚簇索引中，表数据和索引数据是按照相同顺序存储的，非聚簇索引则不是。
聚簇索引在一张表中是唯一的，只能有一个，非聚簇索引则可以存在多个。
聚簇索引在逻辑+物理上都是连续的，非聚簇索引则仅是逻辑上的连续。
聚簇索引中找到了索引键就找到了行数据，但非聚簇索引还需要做一次回表查询。

InnoDB-非聚簇索引与MyISAM-非聚簇索引的区别：

InnoDB中的非聚簇索引是以聚簇索引的索引键，与具体的行数据建立关联关系的。
MyISAM中的非聚簇索引是以行数据的地址指针，与具体的行数据建立关联关系的。

一般来说，由于MyISAM引擎中的索引可以根据指针直接获取数据，不需要做二次回表查询，因此从整体查询效率来看，会比InnoDB要快上不少。

Innodb引擎的innodb_lock_wait_timeout参数说明

innodb_lock_wait_timeout参数在MySQL数据库中定义了一个事务等待行锁的最大时间，默认50s。当一个事务尝试获取已被另一个事务持有的行锁时，如果等待时间超过了innodb_lock_wait_timeout设定的值，MySQL将自动回滚这个等待中的事务，并抛出错误 Lock wait timeout exceeded; try restarting transaction。这个设置主要是用来防止由于长时间的锁等待导致的系统僵死或性能问题。

默认50s，该参数的作用：

避免死锁影响：尽管InnoDB能够自动检测并解决行锁导致的死锁，但这个参数可以作为一种补充机制，处理那些未达到死锁但依然导致长时间阻塞的情况。
控制响应时间：通过限制事务等待锁的时间，可以保证数据库操作的响应速度，避免单个事务长时间占用资源，影响其他事务的执行。
故障排查：当遇到锁等待超时错误时，可以提示开发者或管理员去检查潜在的并发控制问题或考虑优化事务设计。

验证： navicat新建查询1

# 默认等待行锁最多50s，可以临时设置为10s
set session innodb_lock_wait_timeout=10;
begin;
update config_info set data_id = 'nacos.cfg.dataId' where id=42;
# 不提交事务

执行上面的sql后，navicat新建查询2执行下面的sql

# 默认等待行锁最多50s，可以临时设置为10s
set session innodb_lock_wait_timeout=10;
begin;
update config_info set data_id = 'nacos.cfg.dataId' where id=42;
# 不提交事务

结果10s后报错如下

[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

MySQL教程阅读笔记