八股文之mysql

1、MySQL性能优化解决思路

SQL优化

表结构优化

程序配置优化

架构设计优化

硬件和操作系统优化

SQL语句优化

索引失效情况

最左前缀匹配原则

索引下推

表结构优化

将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

单表的字段应该少而精，那多少合适呢？一般单表字段上限控制在20到50个。

大表：当一个表的数据超过千万行的时候，就会对数据库造成影响

大事务：运行的时间比较长，操作的数据比较多的事务

风险：

锁定太多的数据，造成大量的阻塞和锁超时

回滚所需要的时间比较长

执行时间长，容易造成主从延迟

如何处理大事务？避免一次处理太多的数据

移出不必要在事务中的SELECT操作

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

比如权限管理（五个表）

增加冗余字段

简单介绍

冗余字段，我们有一个表是记录图片的，另一个表是记录商品的。

我们可以在图片你放商品图片里的 url，同时商品里放图片 id 和图片 URL，这两个字段是重复的，这就是数据冗余，设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

三大范式

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式，如下表所示。

2．第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。

比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。

3.第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。

第一范式

1NF，强调的是列的原子性，也就是列不能再被分割。如电话列可进行拆分—家庭电话、公司电话

第二范式

2NF，首先是 1NF，另外包含两部分内容，一是表必须有主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。例如:订单表(订单编号、产品编号、定购日期、价格、……)，"订单编号"为主键，"产品编号"和主键列没有直接的关系，即"产品编号"列不依赖于主键列，应删除该列。

第三范式

3NF，首先是 2NF，目标是确保每列都和主键列直接相关,而不是间接相关(另外非主键列必须直接依赖于主键，不能存在传递依赖).

为了理解第三范式，需要根据Armstrong公里之一定义传递依赖。假设A、B和C是关系R的三个属性，如果A-〉B且B-〉C，则从这些函数依赖中，可以得出A-〉C，如上所述，
依赖A-〉C是传递依赖。
例如:订单表(订单编号，定购日期，顾客编号，顾客姓名，……)，初看该表没有问题，满足第二范式，每列都和主键列"订单编号"相关，再细看你会发现"顾客姓名"和"顾客
编号"相关，"顾客编号"和"订单编号"又相关，最后经过传递依赖，"顾客姓名"也和"订单编号"相关。为了满足第三范式，应去掉"顾客姓名"列，放入客户表中。

程序配置优化

MySQL 是一个经过互联网大厂验证过的生产级别的成熟数据库，对于 Mysql 数 据库本身的优化，一般是通过 Mysql 中的配置文件 my.cnf 来完成的，比如。 Mysql5.7 版本默认的最大连接数是 151 个，这个值可以在 my.cnf 中修改。 binlog 日志，默认是不开启 缓存池 bufferpoll 的默认大小配置等。 由于这些配置一般都和用户安装的硬件环境以及使用场景有关系，因此这些配置 官方只会提供一个默认值，具体情况还得由使用者来修改。 关于配置项的修改，需要关注两个方面。 l 配置的作用域，分为会话级别和全局 l 是否支持热加载 因此，针对这两个点，我们需要注意的是： l 全局参数的设定对于已经存在的会话无法生效 l 会话参数的设定随着会话的销毁而失效 l 全局类的统一配置建议配置在默认配置文件中，否则重启服务会导致配置失效

架构设计优化

MySQL 是一个磁盘 IO 访问量非常频繁的关系型数据库

在高并发和高性能的场景中.MySQL 数据库必然会承受巨大的并发压力，而此时， 我们的优化方式可以分为几个部分。

1. 搭建 Mysql 主从集群，单个 Mysql 服务容易单点故障，一旦服务器宕机，将 会导致依赖 Mysql 数据库的应用全部无法响应。 主从集群或者主主集群可以保 证服务的高可用性。
2. 读写分离设计，在读多写少的场景中，通过读写分离的方案，可以避免读写 冲突导致的性能影
3. 引入分库分表机制，通过分库可以降低单个服务器节点的 IO 压力，通过分表 的方式可以降低单表数据量，从而提升 sql 查询的效率。
4. 针对热点数据，可以引入更为高效的分布式数据库，比如 Redis、MongoDB 等，他们可以很好的缓解 Mysql 的访问压力，同时还能提升数据检索性能。

硬件以及操作系统优化

影响 Mysql 性能的因素有，CPU、可用内存大小、磁盘读写 速度、网络带宽 从操作系层面来说，应用文件句柄数、操作系统网络的配置都会影响到 Mysql 性能。 这部分的优化一般由 DBA 或者运维工程师去完成。 在硬件基础资源的优化中，我们重点应该关注服务本身承载的体量，然后提出合 理的指标要求，避免出现资源浪费！

2、如何处理线上MySQL慢查询

1、通过相关指令开启慢查询日志

-- 查看是否开启了慢查询日志
show variables like 'slow_query_log';
-- 默认是OFF，不开启，可以手动开启
-- 方式一 set global slow_query_log=1;
--  修改配置文件my.cnf，加入下面一行命令 slow_query_log = ON

2、慢查询日志找到对应的SQL，分析SQL

-- 查询慢查询日志文件路径
show variables like '%slow_query_log_file%';
-- MySQL提供了分析慢查询日志的工具mysqldumpslow
mysqldumpslow -s t -t 10 /usr/local/mysql/data/localhost_slow.log

-- 例如 ，休眠20s
SELECT sleep(20); 

常用参数有 -s: 表示按何种方式排序：　　c: 访问次数　　l: 锁定时间　　r: 返回记录　　t: 查询时间　　al: 平均锁定时间　　ar: 平均返回记录数　　at: 平均查询时间-t: 返回前面多少条的数据

3、where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高

看一下accurate_result = 1的记录数：

select count(*),accurate_result from stage_poi  group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
|     1023 |              -1 |
|  2114655 |               0 |
|   972815 |               1 |
+----------+-----------------+

我们看到accurate_result这个字段的区分度非常低，整个表只有-1,0,1三个值，加上索引也无法锁定特别少量的数据。

4、explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询 ）

索引是否应用，联合索引是否完全应用，扫描行数rows

explain select * from a;

explain select * from b;

5、了解业务应用场景，实时数据，历史数据，定期删除等因素,辅助我们更好的分析和优化查询语句

6、加索引时参照建索引的几大原则

最左匹配原则

区分度高的列作为索引，count(distinct name)/count(*),表示字段不重复的比例，比例越大扫描记录越少

索引失效情况

尽量扩展索引，不要新建索引

7、mysqldumpslow

使用帮助

-s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default # 默认是at 平均查询时间
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time  # 查询时间排序
-r           reverse the sort order (largest last instead of first) # 反转排序顺序 
-t n  just show the top n queries # 仅仅显示前n行

实践

mysqldumpslow -s t -t 10 localhost-slow.log

总结

1 根据命令mysqldumpslow找到慢查询时间耗时比较长的SQL

2 explain查看执行计划，需要重点关注 type索引是否应用，联合索引是否完全应用，扫描行数rows

3 加索引时参照建索引的几大原则

3.1 最左匹配原则

3.2 区分度高的列作为索引，count(distinct name)/count(*),表示字段不重复的比例，比例越大扫描记录越少

3.3 索引失效情况

3.4 尽量扩展索引，不要新建索引

4 了解业务应用场景，实时数据，历史数据，定期删除等因素,辅助我们更好的分析和优化查询语句

3、MySQL执行计划

https://cloud.tencent.com/developer/article/1672295

id , type , key , rows , extra

id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
1、id 相同：执行顺序由上至下

2、id 不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行
3、id 相同又不同（两种情况同时存在）：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

type

访问类型，sql 查询优化中一个很重要的指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的 sql 查询至少达到 range 级别，最好能达到 ref

1、system：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，可以忽略不计

2、const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只需匹配一行数据，所有很快。如果将主键置于 where 列表中，mysql 就能将该查询转换为一个 const

3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

5、range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了那个索引。一般就是在 where 语句中出现了 bettween、<、>、in 等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

6、index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常为 ALL 块，应为索引文件通常比数据文件小。（Index 与 ALL 虽然都是读全表，但 index 是从索引中读取，而 ALL 是从硬盘读取）

7、ALL：Full Table Scan，遍历全表以找到匹配的行

key

实际使用的索引，如果为 NULL，则没有使用索引。

查询中如果使用了覆盖索引，则该索引仅出现在 key 列表中

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

extra

不适合在其他字段中显示，但是十分重要的额外信息

1、Using filesort ：
mysql 对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说 mysql 无法利用索引完成的排序操作成为“文件排序”

2、Using temporary：
使用临时表保存中间结果，也就是说 mysql 在对查询结果排序时使用了临时表，常见于 order by 和 group by

3、Using index：
表示相应的 select 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现 Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没用同时出现 Using where，表明索引用来读取数据而非执行查找动作

4、Using where ：
使用了 where 过滤

5、Using join buffer ：
使用了链接缓存

6、Impossible WHERE：
where 子句的值总是 false，不能用来获取任何元祖

7、select tables optimized away：
在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

8、distinct：
优化 distinct 操作，在找到第一个匹配的元祖后即停止找同样值得动作

执行顺序
1（id = 4）、【select id, name from t2】：select_type 为 union，说明 id=4 的 select 是 union 里面的第二个 select。

2（id = 3）、【select id, name from t1 where address = ‘11’】：因为是在 from 语句中包含的子查询所以被标记为 DERIVED（衍生），where address = ‘11’ 通过复合索引 idx_name_email_address 就能检索到，所以 type 为 index。

3（id = 2）、【select id from t3】：因为是在 select 中包含的子查询所以被标记为 SUBQUERY。

4（id = 1）、【select d1.name, … d2 from … d1】：select_type 为 PRIMARY 表示该查询为最外层查询，table 列被标记为 “derived3”表示查询结果来自于一个衍生表（id = 3 的 select 结果）。

5（id = NULL）、【 … union … 】：代表从 union 的临时表中读取行的阶段，table 列的 “union 1, 4”表示用 id=1 和 id=4 的 select 结果进行 union 操作。

4、请说一下MySQL索引的优点和缺点

理解

索引，是一种能够帮助 Mysql 高效从磁盘上检索数据的一种数据结构，索引类似于字典里面的目录

在 Mysql 中的 InnoDB 引擎中，采用了 B+树的结构来实现索引和数据的存储

优点

在我看来，Mysql 里面的索引的优点有很多 ，首先通过 B+树的结构来存储数据，可以大大减少数据检索时的磁盘 IO 次数，从而提

升数据查询的性能 ，另外B+树索引在进行范围查找的时候，只需要找到起始节点，然后基于叶子节点的 链表结构往下读取即可，查询效率较高。

通过唯一索引约束，可以保证数据表中每一行数据的唯一性 ；

缺点

当然，索引的不合理使用，也会有带来很多的缺点。 数据的增加、修改、删除，需要涉及到索引的维护，当数据量较大的情况下，索

引的维护会带来较大的性能开销

在我看来，任何技术方案都会有两面性，大部分情况下，技术方案的选择更多的是看中它的优势和当前问题的匹配度

5、MySql索引是什么数据结构？

mysql为什么使用B+树而不是B树作为索引？为什么不能使用红黑树，哈希表

首先，常规的数据库存储引擎，一般都是采用 B 树或者 B+树来实现索引的存储。 因为 B 树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度 会相比二叉树来说，会矮很多。 而对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘 IO 的效率实

际上是很低的，特别是在随机磁盘 IO 的情况下效率更低。 所以树的高度能够决定磁盘 IO 的次数，磁盘 IO 次数越少，对于性能的提升就越大，这也是为什么采用 B 树作为索引存储结构的原因但是在 Mysql 的 InnoDB 存储引擎里面，它用了一种增强的 B 树结构，也就是 B+树来作为索引和数据的存储结构。 相比较于 B 树结构，B+树做了几个方面的优化。 B+树的所有数据都存储在叶子节点，非叶子节点只存储索引。

叶子节点中的数据使用双向链表的方式进行关联使用 B+树来实现索引的原因，我认为有几个方面。

B+树非叶子节点不存储数据，所以每一层能够存储的索引数量会增加，意味着 B+树在层高相同的情况下存储的数据量要比 B 树要多，使得磁盘 IO 次数更少。

在 Mysql 里面，范围查询是一个比较常用的操作，而 B+树的所有存储在叶子节点的数据使用了双向链表来关联，所以在查询的时候只需查两个节点进行遍历就

行，而 B 树需要获取所有节点，所以 B+树在范围查询上效率更高。 在数据检索方面，由于所有的数据都存储在叶子节点，所以 B+树的 IO 次数会更

加稳定一些。 因为叶子节点存储所有数据，所以 B+树的全局扫描能力更强一些，因为它只需

要扫描叶子节点。但是 B 树需要遍历整个树。

另外，基于 B+树这样一种结构，如果采用自增的整型数据作为主键，还能更好 的避免增加数据的时候，带来叶子节点分裂导致的大量运算的问题。

总的来说，我认为技术方案的选型，更多的是去解决当前场景下的特定问题，并不一定是说 B+树就是最好的选择，就像 MongoDB 里面采用 B 树结构，本质上来说，其实是关系型数据库和非关系型数据库的差异

如果使用红黑树，会使树的高度更高，增加IO消耗（数据量）

哈希表对于范围查找和排序效率低，但对于单个数据的查询效率很高；hash表的索引格式是链式，hash存储是将所有文件添加内存，耗费内存空间

二叉树：两个枝杈，并且根节点的值大于左子树，小于右子树，如遇到极端境况会退化成一条链表，效率不高

AVL树（平衡二叉查找）：最高子树和最低子树差值为一，进行旋转保持平衡，查找效率高，插入效率低

总结

MySQL采用B+树作为索引存储结构。

B+树是B树增强版， B 树是一种多路平衡树，用这种存储结构来存储大量数据，它的整个高度会相比二叉树来说，会矮很多。 而对于数据库来说，所有的数据必然都是存储在磁盘上的，而磁盘 IO 的效率实际上是很低的，特别是在随机磁盘 IO 的情况下效率更低。 所以树的高度能够决定磁盘 IO 的次数，磁盘 IO 次数越少，对于性能的提升就越大，这也是为什么采用 B 树作为索引存储结构的原因。

Mysql 的 InnoDB 存储引擎里面，它用了一种增强的 B 树结构，也就是 B+树来作为索引和数据的存储结构。 相比较于 B 树结构，B+树做了几个方面的优化， B+树的所有数据都存储在叶子节点，非叶子节点只存储索引，叶子节点数据使用双向链表的方式进行关联。

6、B树和B+树之间的区别

B树

• 叶子节点和非叶子节点都存储数据
• 范围查找的话，则需要在叶子节点和内部节点不停的往返移动
• 对于在内部节点的数据，可直接得到，不必根据叶子节点来定位

B+树

• 非叶子节点不存储data,只存储索引，可以放更多的索引
• 叶子节点包含主键key和具体数据
• 叶子节点用指针连接，范围扫描非常简单

总结

1、关于叶子节点和非叶子节点说明

2、关于范围查询优劣

7、为什么要遵守最左前缀原则才能利用索引

案例说明

-- 创建数据库表
CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

-- 插入数据
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

-- 建立索引
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

全索引顺序

EXPLAIN SELECT 	* FROM 	staffs WHERE name = 'z3' AND age = 22 AND pos = 'manager';
EXPLAIN SELECT 	* FROM 	staffs WHERE age = 22 AND name = 'z3' AND pos = 'manager';
EXPLAIN SELECT 	* FROM 	staffs WHERE pos = 'manager' AND name = 'z3' AND age = 22;

最主要是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划

部分索引顺序

-- 只有第一个索引生效
explain select * from staffs where name='z3';
-- 跳过中间位置索引
explain select * from staffs where name='z3' and pos='manager';
-- 只使用最后一个索引
explain select * from staffs where pos='manager';

模糊索引顺序

-- 最左前缀索引，类型为index或者range
explain select *from staffs where name like '3%'; 
-- 类型为all，全表查询
explain select *from staffs where name like '%3%'; 

范围索引顺序

如果查询多个字段的时候，出现了中间是范围的话，建议删除该索引，剔除中间索引即可

EXPLAIN SELECT id,author_id FROM article WHERE category_id =1 AND comments >1 ORDER BY views DESC LIMIT 1;

-- 但是其思路要避开中间那个范围的索引进去
create index idx_article_cv on article(category_id, views);

原理分析

复合索引数据结构是这样的，比如现在创建name,age,sex的组合索引，b+树是按照从左往右的顺序建立搜索树，name=张三，age=10,sex=男

这样数据检索时候，b+树优先比较name来确定一下不的搜索方向，如果name相同再一次比较age和sex,最后得到检索的数据；

但是当检索age=20,sex=男这样的没有name数据时候，b+树布置到第一步该查询哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须先根据name来搜索才能知道下一步去哪里查询；

比如name=张三，sex=男这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 sex=男 的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

结论

如果索引关联了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。 如果跳跃某一列，索引将部分失效（后面的字段索引失效）

反例

1、数据库表中所有字段都输索引，不符合最左匹配原则

2、返回数据有覆盖索引，也会失效

8、几种索引失效原理

不遵守最左前缀匹配原则导致失效

select * from user where age>12

在name确定的情况下。现在你的name都飞了，那age肯定是不能确定顺序的，在一个无序的B+树上是无法用二分查找来定位到age字段的。

所以这个时候，是用不上索引的

范围查询右边导致失效

select * from staffs where age >12 and name = 'z3'

首先age字段在B+树上是有序的，所以可以使用二分查找定位年龄12，然后找到年龄大于12的数据，age可以使用索引；

name有序的前提是age是确定的数值，现在age获取的是>12，这个范围可能有10个，也可能有100个，其中大于12的age那一部分在b+树中

name字段是无序的，所以name不能再无序的b+树中使用二分查找，故而name使用不到索引

like模糊查询导致失效

-- 查询a开头的数据 前缀
where name like 'a%'
-- 查询name中包含a的数据 中缀
where name like '%a%'
-- 查询name中以a结束的数据 后缀
where name like '%a'

对于前缀：索引的排列顺序是根据比较字符串的首字母排序的，如果首字母相同，就根据比较第二个字母进行排序；我们在进行模糊查询的时候，如果把 % 放在了前面，最左的 n 个字母便是模糊不定的，无法根据索引的有序性准确的定位到某一个索引，只能进行全表扫描，找出符合条件的数据。

order by排序导致失效

order by使用索引列排序时会失效吗?
在使用order by对某列进行排序时, 其sql一定进行了多表联查的操作. 说白了肯定做了表关联. 既然有表关联, 那order by排序就可以对任意的表进行排序. 那么应该对那个表进行排序好点呢?

先说结论, order by后面跟的索引列只是对 驱动表才起作用, 也就是说如果是对驱动表进行排序, 那么order by后面的索引是起作用的, 但是如果order by后面跟的排序字段不是驱动表的, 那么这时索引是不起作用的.

问题来了 , 什么是驱动表?
在进行多表联查时, sql执行时会先查出一个表中符合查询条件的数据,然后再用这些条件去匹配另一个表中的符合条件的数据 , 合并后的数据就是我们要的结果集. 那么先查出来结果集的这个表就是驱动表.

问题又来了, 谁有资格做驱动表?
答: 谁查出来符合条件的数据量少, 谁就最有资格做驱动表. 这里为什么只说最有资格做? 而不是一定做?
因为当用户使用 left join , right join 指定了驱动表时, 以用户指定的表作为驱动表. (关键字左边的表时驱动表) , 当用户使用join 或者 , 没有指定驱动表时, 由数据库自己挑出一个结果集最少的表作为驱动表. 如果用户使用left join指定了一个查询出来数据量很大的表作为驱动表时, 那就很糟糕了.

order by索引排序的失效与不失效?
如果order by 后的排序字段用的是驱动表的排序字段, 那么这个字段的索引是不失效的, 效率高.
如果order by 后的排序字段用的不是驱动表的字段 , 那么索引不会起作用, 数据库会将驱动表查出来的数据和其他表进行关联, 关联后的结果集创建一个临时表进行存储, 然后对这个临时表按非驱动表的列进行排序 ,这样sql效率会变慢.

字段上进行运算导致失效

9、覆盖索引底层原理

如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”

select name,age,pos from staffs where name = 'z3' and age = 18 and pos ='manager';

分析

基本信息 user, InnoDB存储引擎

表结构：id -> 主键 name,phone_num -> 联合索引 user_num -> 唯一索引

下面哪一个SQL执行效率高

• select * from user where user_num = ?;
• select name,user_num from user where user_num = ?;
• select name, user_num from user where name = ? ;
• select id,phone_num from user where name = ?;

第一条SQL：user_num唯一索引，采用非主键索引形式，叶子结点存放主键信息和user_nam信息；但是*需要返回name,phone_num,故而需要回表操作，根据主键查询主键索引树返回 查询两棵树

第二条SQL：user_num唯一索引，采用非主键索引形式，叶子结点存放id和user_num信息；但是需要返回 name,user_num，故而需要回表操作，根据主键查询主键索引树返回name 查询两棵树

第三条SQL：name联合索引，采用非主键索引形式，叶子节点存放name,phone_num，id信息，但是需要返回user_num,故而需要回表操作，根据主键查询主键索引树返回user_num 查询两棵树

第四条SQL：name联合索引，采用非主键索引形式，叶子节点存放name,phone_num,id信息，返回数据只需要id,phone_num故而不需要回表操作，查询一棵树，这就是覆盖索引

10、MySQL数据类型转换注意点

在varchar类型中，不是数字的字符如果转换为数字的话会统统转成0，比如'123' = 123 , 'abc' = 0

SELECT CAST('abc' AS decimal); -- 0
SELECT CAST('123' AS decimal); -- 123

隐式类型转换：当条件判断字段两边的操作数类型不一致时，MySQL会发生类型转换以使操作数兼容，这些转换是隐式发生的

如果参数之一是TIMESTAMP或DATETIME列，而另一个参数是常量，则在执行比较之前，该常量将转换为时间戳。

select cast(now() as decimal); -- 9999999999

条件列是字符串时，如果传入的条件参数是整数，会先转换成浮点数，再全表扫描，导致索引失效；

条件参数要尽可能与列的类型相同，避免隐式转换，或者在传入的参数上执行转换函数，转换成与索引列相同的类型。

所以，在 MySQL 里 “1”、 " 1"、“1a” 、"01"这样的字符串转成数字后都是 1 。

11、简单描述MySQL各种索引

B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。

哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)，如果是范围查询就是一个个遍历hash桶，效率太低

底层就是 hash 表。进行查找时，根据 key 调用hash 函数获得对应的 hashcode，根据 hashcode 找到对应的数据行地址，根据地址拿到对应的数据。

总结

如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。

如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

12、索引的设计基本原则

• 最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

• '='和’in’可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

• 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

• 索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

• 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

• 索引下推

在存储引擎中提前判断对应的搜索条件是否满足，满足了再去回表，通过减少回表次数进而提高查询效率。

13、索引存储在文件中还是内存中

通常，索引本身很大，需要持久化操作，不能完全存储在内存中，因此索引通常作为索引文件存储在磁盘上

14、索引字段用int还是varchar

尽量用int来存储索引,它只占4个字节,而varchar会占用比较多的字节.

15、索引叶子节点存放是什么

叶子节点存放了整张表的所有行数据，非叶子节点并不存储行数据,是为了能存储更多索引键,从而降低B+树的高度,进而减少IO次数

16、什么是回表查询

InnoDB 中，对于主键索引，只需要走一遍主键索引的查询就能在叶子节点拿到数据。

而对于普通索引，叶子节点不存储行记录，无法直接定位行记录，需要扫描两次索引树，先定位主键值，通过主键索引找到行记录，再定位行记录。

17、MySQL中B树高度怎么计算的

InnoDB存储引擎最小储存单元是页，一页大小就是16k。

B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据；

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数*单个叶子节点记录行数。

如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
非叶子节点内存放多少指针呢？我们假设主键ID为bigint类型，长度为8字节(面试官问你int类型，一个int就是32位，4字节)，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16*1024B/14B = 1170
因此，一棵高度为2的B+树，能存放1170 * 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 *1170 *16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。

18、为什么 InnoDB 只在主键索引树的叶子节点存储了具体数据

，但是其他索引树却不存具体数据呢，而要多此一举先找到主键，再在主键索引树找到对应的数据呢?

其实很简单，因为 InnoDB 需要节省存储空间。一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

19、MySQL 的 InnoDB 存储引擎是怎么设计的？

对于 MySQL，要记住、或者要放在你随时可以找到的地方的两张图，一张是 MySQL 架构图，另一张则是 InnoDB 架构图：

遇到问题，或者学习到新知识点时，就往里套，想一想，这是对应这两张图的哪个模块、是属于具体哪个成员的能力。

这其中，第一张图的最底下的存储引擎层（Storage Engines），它决定了 MySQL 会怎样存储数据，怎样读取和写入数据，也在很大程度上决定了 MySQL 的读写性能和数据可靠性。

对于这么重要的一层能力，MySQL 提供了极强的扩展性，你可以定义自己要使用什么样的存储引擎：InnoDB、MyISAM、MEMORY、CSV，甚至可以自己开发一个存储引擎然后使用它。

我一直觉得 MySQL 的设计，是教科书式的，高内聚松耦合，边界明确，职责清晰。学习 MySQL，学的不只是如何更好的使用 MySQL，更是学习如何更好的进行系统设计。

通常我们说 Mysql 高性能高可靠，都是指基于 InnoDB 存储引擎的 Mysql，所以，这一讲，先让我们来看看，除了 redo log，InnoDB 里还有哪些成员，他们都有什么能力，承担了什么样的角色，他们之间又是怎么配合的？

InnoDB 内存架构

从上面第二张图可以看到，InnoDB 主要分为两大块：

• InnoDB In-Memory Structures
• InnoDB On-Disk Structures

内存和磁盘，让我们先从内存开始。

1、Buffer Pool

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed.

正如之前提到的，MySQL 不会直接去修改磁盘的数据，因为这样做太慢了，MySQL 会先改内存，然后记录 redo log，等有空了再刷磁盘，如果内存里没有数据，就去磁盘 load。

而这些数据存放的地方，就是 Buffer Pool。

我们平时开发时，会用 redis 来做缓存，缓解数据库压力，其实 MySQL 自己也做了一层类似缓存的东西。

MySQL 是以「页」（page）为单位从磁盘读取数据的，Buffer Pool 里的数据也是如此，实际上，Buffer Pool 是a linked list of pages，一个以页为元素的链表。

为什么是链表？因为和缓存一样，它也需要一套淘汰算法来管理数据。

Buffer Pool 采用基于 LRU（least recently used） 的算法来管理内存：

关于 Buffer Pool 的更多知识，诸如如何配置大小、如何监控等等：Buffer Pool

2、Change Buffer

上面提到过，如果内存里没有对应「页」的数据，MySQL 就会去把数据从磁盘里 load 出来，如果每次需要的「页」都不同，或者不是相邻的「页」，那么每次 MySQL 都要去 load，这样就很慢了。

于是如果 MySQL 发现你要修改的页，不在内存里，就把你要对页的修改，先记到一个叫 Change Buffer 的地方，同时记录 redo log，然后再慢慢把数据 load 到内存，load 过来后，再把 Change Buffer 里记录的修改，应用到内存（Buffer Pool）中，这个动作叫做 merge；而把内存数据刷到磁盘的动作，叫 purge：

• merge：Change Buffer -> Buffer Pool
• purge：Buffer Pool -> Disk

The change buffer is a special data structure that caches changes to secondary index pages when those pages are not in the buffer pool. The buffered changes, which may result from INSERT, UPDATE, or DELETE operations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.

上面是 MySQL 官网对 Change Buffer 的定义，仔细看的话，你会发现里面提到：Change Buffer 只在操作「二级索引」（secondary index）时才使用，原因是「聚簇索引」（clustered indexes）必须是「唯一」的，也就意味着每次插入、更新，都需要检查是否已经有相同的字段存在，也就没有必要使用 Change Buffer 了；另外，「聚簇索引」操作的随机性比较小，通常在相邻的「页」进行操作，比如使用了自增主键的「聚簇索引」，那么 insert 时就是递增、有序的，不像「二级索引」，访问非常随机。

如果想深入理解 Change Buffer 的原理，除了 MySQL 官网的介绍：Change Buffer，还可以阅读下《MySQL技术内幕》的「2.6.1 - 插入缓冲」章节，里面会从 Change Buffer 的前身 —— Insert Buffer 开始讲起，很透彻。

3、Adaptive Hash Index

MySQL 索引，不管是在磁盘里，还是被 load 到内存后，都是 B+ 树，B+ 树的查找次数取决于树的深度。你看，数据都已经放到内存了，还不能“一下子”就找到它，还要“几下子”，这空间牺牲的是不是不太值得？

尤其是那些频繁被访问的数据，每次过来都要走 B+ 树来查询，这时就会想到，我用一个指针把数据的位置记录下来不就好了？

这就是「自适应哈希索引」（Adaptive Hash Index）。自适应，顾名思义，MySQL 会自动评估使用自适应索引是否值得，如果观察到建立哈希索引可以提升速度，则建立。

4、Log Buffer

The log buffer is the memory area that holds data to be written to the log files on disk.

从上面架构图可以看到，Log Buffer 里的 redo log，会被刷到磁盘里：

Operating System Cache

在内存和磁盘之间，你看到 MySQL 画了一层叫做 Operating System Cache 的东西，其实这个不属于 InnoDB 的能力，而是操作系统为了提升性能，在磁盘前面加的一层高速缓存，这里不展开细讲，感兴趣的同学可以参考下维基百科：Page Cache

InnoDB 磁盘架构

磁盘里有什么呢？除了表结构定义和索引，还有一些为了高性能和高可靠而设计的角色，比如 redo log、undo log、Change Buffer，以及 Doublewrite Buffer 等等.

有同学会问，那表的数据呢？其实只要理解了 InnoDB 里的所有表数据，都以索引（聚簇索引+二级索引）的形式存储起来，就知道索引已经包含了表数据。

1、表空间（Tablespaces）

从架构图可以看到，Tablespaces 分为五种：

• The System Tablespace
• File-Per-Table Tablespaces
• General Tablespace
• Undo Tablespaces
• Temporary Tablespaces

其中，我们平时创建的表的数据，可以存放到 The System Tablespace 、File-Per-Table Tablespaces、General Tablespace 三者中的任意一个地方，具体取决于你的配置和创建表时的 sql 语句。

这里同样不展开，如何选择不同的表空间存储数据？不同表空间各自的优势劣势等等，传送门：Tablespaces

2、Doublewrite Buffer

如果说 Change Buffer 是提升性能，那么 Doublewrite Buffer 就是保证数据页的可靠性。

怎么理解呢？

前面提到过，MySQL 以「页」为读取和写入单位，一个「页」里面有多行数据，写入数据时，MySQL 会先写内存中的页，然后再刷新到磁盘中的页。

这时问题来了，假设在某一次从内存刷新到磁盘的过程中，一个「页」刷了一半，突然操作系统或者 MySQL 进程奔溃了，这时候，内存里的页数据被清除了，而磁盘里的页数据，刷了一半，处于一个中间状态，不尴不尬，可以说是一个「不完整」，甚至是「坏掉的」的页。

有同学说，不是有 Redo Log 么？其实这个时候 Redo Log 也已经无力回天，Redo Log 是要在磁盘中的页数据是正常的、没有损坏的情况下，才能把磁盘里页数据 load 到内存，然后应用 Redo Log。而如果磁盘中的页数据已经损坏，是无法应用 Redo Log 的。

所以，MySQL 在刷数据到磁盘之前，要先把数据写到另外一个地方，也就是 Doublewrite Buffer，写完后，再开始写磁盘。Doublewrite Buffer 可以理解为是一个备份（recovery），万一真的发生 crash，就可以利用 Doublewrite Buffer 来修复磁盘里的数据。

留个问题，有了 Doublewrite Buffer 后，不就意味着 MySQL 要写两次磁盘？性能岂不是很差？

让我们再来回顾一下这张图：

这篇文章，顺着这张图，给大家介绍了 InnoDB 里的每一个成员、成员各自扮演的角色、提供的能力。

当然，这张图里能表达的信息是有限的，我习惯称这种图为「架构图」，或者「模块图」。

用 DDD 的话来讲，这张图可以告诉你，MySQL 里有哪些「域」（子域、核心域、通用域、支撑域），配合文字介绍，可以知道这些「域」之间都有什么样的能力、行为，知道「域」之间一些简单的交互。

然而，这张图并没有告诉你具体某个业务中，这些成员之间要如何配合，来提供一个服务，或者说，如果你的技术方案里只有这张图，那你进入开发阶段后，最多最多，只能新建几个微服务应用，新建几个类和对象，而写不出这些个微服务、class 之间如何协作起来提供一个服务的代码。

20、什么是 Buffer Pool？

Buffer Pool 是 InnoDB 维护的一个缓存区域，用来缓存数据和索引在内存中，主要用来加速数据的读写，如果 Buffer Pool 越大，那么 MySQL 就越像一个内存数据库，默认大小为 128M。

InnoDB 会将那些热点数据和一些 InnoDB 认为即将访问到的数据存在 Buffer Pool 中，以提升数据的读取性能。

InnoDB 在修改数据时，如果数据的页在 Buffer Pool 中，则会直接修改 Buffer Pool，此时我们称这个页为脏页，InnoDB 会以一定的频率将脏页刷新到磁盘，这样可以尽量减少磁盘I/O，提升性能。

21、聚簇索引和非聚簇索引？

聚簇索引：innodb数据文件和索引文件在一起，并且是按照一定的顺序组织的，找到索引就找到了数据。数据的物理存放顺序与索引顺序是一致的

非聚簇索引：myism是分开的myd和myi文件分开存储的，叶子节点不存放数据、存储的数数据地址。也就是说根据索引查找到数据行的位置再取磁盘查找数据。

相关问题

1、一个表中聚簇索引一定是主键索引？

不一定,在 InnoDB 引擎中，每张表都会有一个特殊的索引“聚簇索引”，也被称之为聚集索引，它是用来存储行数据的。一般情况下，聚簇索引等同于主键索引，但这里有一个前提条件，那就是这张表需要有主键，只有有了主键，它才能有主键索引，有主键索引才能等于聚簇索引

2、一个表中聚簇索引可以有多个吗？

一个表只能有一个聚集索引，可以有多个非聚集索引

聚集索引基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。

3、主键索引一定是聚簇索引吗？

如果创建表时候没有主键，没有唯一键，添加数据之后，再设置主键

那么 InnoDB 会生成一个名为 GEN_CLUST_INDEX 的隐藏聚簇索引，这个隐藏的索引为 6 字节的长整数类型。

4、innodb普通列索引是聚簇还是非聚簇

如果创建索引的键是其他字段，那么叶子节点存放就是该记录的主键，然后通过主键索引查询

5、聚簇索引诞生过程

当你为一张表创建主键时，也就是定义 PRIMARY KEY 时，此时这张表的聚簇索引就是主键索引。通常情况下，我们应该为一张表设置一个主键，如果没有合适的列作为主键列，我们可以定义一个自动递增的唯一列为主键，并且在插入数据时是自动填充此列。

然而，如果一张表中没有设置主键，那么 InnoDB 会使用第一个唯一索引（unique），且此唯一索引设置了非空约束（not null），我们就使用它作为聚簇索引。

如果一张表既没有主键索引，又没有符合条件的唯一索引，那么 InnoDB 会生成一个名为 GEN_CLUST_INDEX 的隐藏聚簇索引，这个隐藏的索引为 6 字节的长整数类型。

22、为什么建议innodb表必须建主键，并且使用整形的自增主键？

InnoDB中采用的是聚簇索引，表数据文件本身就是按照B+Tree组织的一个索引结构文件，主键索引默认就是B+Tree，由此主键索引可以维护整张表。如果在实际建表过程中不建立主键，MySQL会自动在表中找一列数据（该列数据没有重复值）来建立唯一索引，在B+tree中维护整张表的数据。

整型比大小更快，整型对于UUID来说占用存储空间小。

用自增方便每次插入到叶子节点链的后面，对于B+树的分裂来说更加方便。如果不用自增的话，有可能插入到叶子节点的中间位置，对于B+树的分裂来说不太方便。主要影响数据写入表的性能。

23、innodb如何解决幻读

事务隔离级别

Mysql 有四种事务隔离级别，这四种隔离级别代表当存在多个事务并发冲突时，

可能出现的脏读、不可重复读、幻读的问题。

其中 InnoDB 在 RR 的隔离级别下，解决了幻读的问题

幻读

那么，什么是幻读呢？

幻读是指在同一个事务中，前后两次查询相同的范围时，得到的结果不一致

第一个事务里面我们执行了一个范围查询，这个时候满足条件的数据只有一条

第二个事务里面，它插入了一行数据，并且提交了

接着第一个事务再去查询的时候，得到的结果比第一查询的结果多出来了一条数

据。

如何解决

InnoDB 引入了间隙锁和 next-key Lock 机制来解决幻读问题，为了更清晰的说

明这两种锁，我举一个例子： 假设现在存在这样这样一个 B+Tree 的索引结构，这个结构中有四个索引元素分 别是：1、4、7、10。

当我们通过主键索引查询一条记录，并且对这条记录通过 for update 加锁

这个时候，会产生一个记录锁，也就是行锁，锁定 id=1 这个索引

被锁定的记录在锁释放之前，其他事务无法对这条记录做任何操作。

前面我说过对幻读的定义：幻读是指在同一个事务中，前后两次查询相同的范围

时，得到的结果不一致！ 注意，这里强调的是范围查询， 也就是说，InnoDB 引擎要解决幻读问题，必须要保证一个点，就是如果一个事务通过这样一条语句进行锁定时

另外一个事务再执行这样一条 insert 语句，需要被阻塞，直到前面获得锁的事务释放

所以，在 InnoDB 中设计了一种间隙锁，它的主要功能是锁定一段范围内的索引

记录当对查询范围 id>4 and id<7 加锁的时候，会针对 B+树中（4，7）这个开区间范

围的索引加间隙锁。 意味着在这种情况下，其他事务对这个区间的数据进行插入、更新、删除都会被

锁住。

但是，还有另外一种情况，比如像这样这条查询语句是针对 id>4 这个条件加锁，那么它需要锁定多个索引区间，所以

在这种情况下 InnoDB 引入了 next-key Lock 机制。

next-key Lock 相当于间隙锁和记录锁的合集，记录锁锁定存在的记录行，间隙

锁锁住记录行之间的间隙，而 next-key Lock 锁住的是两者之和每个数据行上的非唯一索引列上都会存在一把 next-key lock，当某个事务持有该数

据行的 next-key lock 时，会锁住一段左开右闭区间的数据。

因此，当通过 id>4 这样一种范围查询加锁时，会加 next-key Lock，锁定的区间

范围是：(4,7],(7,10],(10,+∞]，间隙锁和 next-key Lock 的区别在于加锁的范围，间隙锁只锁定两个索引之间的引用间隙，而 next-key Lock 会锁定多个索引区间，它包含记录锁和间隙锁。

总结

虽然 InnoDB 中通过间隙锁的方式解决了幻读问题，但是加锁之后一定会影响到

并发性能，因此，如果对性能要求较高的业务场景中，可以把隔离级别设置成

RC，这个级别中不存在间隙锁

22、MySQL有哪些存储引擎

用过innodb，myisam

23、myisam与Innodb区别

理解：不同数据文件在磁盘的不同组织形式

InnoDB支持事务，MyISAM不支持；我们业务一般都是需要可靠性要求的，基本上用的都是innodb,而myisam存储引擎适用于读多写少场景，类似于博客系统，

索引存储的叶子节点数据不同：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构） MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

innodb有外键，myisam没有；

延伸

为什么只读场景myisam比innodb快

查询的时候，由于innodb支持事务，所以会有mvcc的一个比较。这个过程会损耗性能。
查询的时候，如果走了索引，而索引又不是主键索引，此时，由于innodb是聚簇索引，会有一个回表的过程，即：先去非聚簇索引树（非主键索引树）中查询数据，找到数据对应的key之后，再通过key回表到聚簇索引树，最后找到需要的数据。而myisam是非聚集索引，而且叶子节点存储的是磁盘地址，所以，查询的时候查到的最后结果不是聚簇索引树的key，而是会直接去查询磁盘。
锁的一个损耗，innodb锁支持行锁，在检查锁的时候不仅检查表锁，还要看行锁。

24、InnoDB 四大特性知道吗？

插入缓冲（insert buffer）：

索引是存储在磁盘上的，所以对于索引的操作需要涉及磁盘操作。如果我们使用自增主键，那么在插入主键索引（聚簇索引）时，只需不断追加即可，不需要磁盘的随机 I/O。但是如果我们使用的是普通索引，大概率是无序的，此时就涉及到磁盘的随机 I/O，而随机I/O的性能是比较差的（Kafka 官方数据：磁盘顺序I/O的性能是磁盘随机I/O的4000~5000倍）。

因此，InnoDB 存储引擎设计了 Insert Buffer ，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池（Buffer pool）中，若在，则直接插入；若不在，则先放入到一个 Insert Buffer 对象中，然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对于非聚集索引插入的性能。

插入缓冲的使用需要满足以下两个条件：1）索引是辅助索引；2）索引不是唯一的。

因为在插入缓冲时，数据库不会去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有随机读取的情况发生，从而导致 Insert Buffer 失去了意义。

二次写（double write）：

脏页刷盘风险：InnoDB 的 page size一般是16KB，操作系统写文件是以4KB作为单位，那么每写一个 InnoDB 的 page 到磁盘上，操作系统需要写4个块。于是可能出现16K的数据，写入4K 时，发生了系统断电或系统崩溃，只有一部分写是成功的，这就是 partial page write（部分页写入）问题。这时会出现数据不完整的问题。

这时是无法通过 redo log 恢复的，因为 redo log 记录的是对页的物理修改，如果页本身已经损坏，重做日志也无能为力。

doublewrite 就是用来解决该问题的。doublewrite 由两部分组成，一部分为内存中的 doublewrite buffer，其大小为2MB，另一部分是磁盘上共享表空间中连续的128个页，即2个区(extent)，大小也是2M。

为了解决 partial page write 问题，当 MySQL 将脏数据刷新到磁盘的时候，会进行以下操作：

1）先将脏数据复制到内存中的 doublewrite buffer

2）之后通过 doublewrite buffer 再分2次，每次1MB写入到共享表空间的磁盘上（顺序写，性能很高）

3）完成第二步之后，马上调用 fsync 函数，将doublewrite buffer中的脏页数据写入实际的各个表空间文件（离散写）。

如果操作系统在将页写入磁盘的过程中发生崩溃，InnoDB 再次启动后，发现了一个 page 数据已经损坏，InnoDB 存储引擎可以从共享表空间的 doublewrite 中找到该页的一个最近的副本，用于进行数据恢复了。

自适应哈希索引(adaptive hash index）：

哈希（hash）是一种非常快的查找方法，一般情况下查找的时间复杂度为 O(1)。但是由于不支持范围查询等条件的限制，InnoDB 并没有采用 hash 索引，但是如果能在一些特殊场景下使用 hash 索引，则可能是一个不错的补充，而 InnoDB 正是这么做的。

具体的，InnoDB 会监控对表上索引的查找，如果观察到某些索引被频繁访问，索引成为热数据，建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应（adaptive）的。自适应哈希索引通过缓冲池的 B+ 树构造而来，因此建立的速度很快。而且不需要将整个表都建哈希索引，InnoDB 会自动根据访问的频率和模式来为某些页建立哈希索引。

预读（read ahead）：

InnoDB 在 I/O 的优化上有个比较重要的特性为预读，当 InnoDB 预计某些 page 可能很快就会需要用到时，它会异步地将这些 page 提前读取到缓冲池（buffer pool）中，这其实有点像空间局部性的概念。

空间局部性（spatial locality）：如果一个数据项被访问，那么与他地址相邻的数据项也可能很快被访问。

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）。

其中，线性预读以 extent（块，1个 extent 等于64个 page）为单位，而随机预读放到以 extent 中的 page 为单位。线性预读着眼于将下一个extent 提前读取到 buffer pool 中，而随机预读着眼于将当前 extent 中的剩余的 page 提前读取到 buffer pool 中。

线性预读（Linear read-ahead）：线性预读方式有一个很重要的变量 innodb_read_ahead_threshold，可以控制 Innodb 执行预读操作的触发阈值。如果一个 extent 中的被顺序读取的 page 超过或者等于该参数变量时，Innodb将会异步的将下一个 extent 读取到 buffer pool中，innodb_read_ahead_threshold 可以设置为0-64（一个 extend 上限就是64页）的任何值，默认值为56，值越高，访问模式检查越严格。

随机预读（Random read-ahead）: 随机预读方式则是表示当同一个 extent 中的一些 page 在 buffer pool 中发现时，Innodb 会将该 extent 中的剩余 page 一并读到 buffer pool中，由于随机预读方式给 Innodb code 带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃。要启用此功能，请将配置变量设置 innodb_random_read_ahead 为ON。

25、MySQL事务的实现原理

Mysql 里面的事务，满足 ACID 特性，所以在我看来，Mysql 的事务实现原理， 就是 InnoDB 是如何保证 ACID 特性的。

首先，A 表示 Atomic 原子性，也就是需要保证多个 DML 操作是原子的，要么都成功，要么都失败。

那么，失败就意味着要对原本执行成功的数据进行回滚，所以 InnoDB 设计了一 个 UNDO_LOG 表，在事务执行的过程中，把修改之前的数据快照保存到

UNDO_LOG 里面，一旦出现错误，就直接从 UNDO_LOG 里面读取数据执行反 向操作就行了。

其次，C 表示一致性，表示数据的完整性约束没有被破坏，这个更多是依赖于业 务层面的保证，数据库本身也提供了一些，比如主键的唯一余数，字段长度和类

型的保证等等。

接着，I 表示事务的隔离性，也就是多个并行事务对同一个数据进行操作的时候，如何避免多个事务的干扰导致数据混乱的问题。

而 InnoDB 提供了四种隔离级别的实现

RU（未提交读）

RC（已提交读）

RR（可重复读）

Serializable（串行化）

InnoDB 默认的隔离级别是 RR（可重复读），然后使用了 MVCC 机制解决了脏读和不可重复读的问题，然后使用了行锁/表锁的方式解决了幻读的问题。

最后一个是 D，表示持久性，也就是只要事务提交成功，那对于这个数据的结果的影响一定是永久性的。

理论上来说，事务提交之后直接把数据持久化到磁盘就行了，但是因为随机磁盘 IO 的效率确实很低，所以 InnoDB 设计了 Buffer Pool 缓冲区来优化，也就是数据发生变更的时候先更新内存缓冲区，然后在合适的时机再持久化到磁盘。 那在持久化这个过程中，如果数据库宕机，就会导致数据丢失，也就无法满足持久性了,所以 InnoDB 引入了 Redo_LOG 文件，这个文件存储了数据被修改之后的值， 当我们通过事务对数据进行变更操作的时候，除了修改内存缓冲区里面的数据以外，还会把本次修改的值追加到 REDO_LOG 里面。

当提交事务的时候，直接把 REDO_LOG 日志刷到磁盘上持久化，一旦数据库出 现宕机，在 Mysql 重启在以后可以直接用 REDO_LOG 里面保存的重写日志读 取出来，再执行一遍从而保证持久性。 因此，在我看来，事务的实现原理的核心本质就是如何满足 ACID 的，在 InnoDB里面用到了 MVCC、行锁表锁、UNDO_LOG、REDO_LOG 等机制来保证

26、简单说一下事务的隔离级别

假设有两个事务 T1/T2 同时在执行，T1 事务有可能会读取到 T2 事务未提交的数据，但是未提交的事务 T2 可能会回滚，也就导致了 T1 事务读取到最终不一

定存在的数据产生脏读的现象

假设有两个事务 T1/T2 同时执行，事务 T1 在不同的时刻读取同一行数据的时候结果可能不一样，从而导致不可重复读的问题

假设有两个事务 T1/T2 同时执行，事务 T1 执行范围查询或者范围修改的过程中，事务 T2 插入了一条属于事务 T1 范围内的数据并且提交了，这时候在事

务 T1 查询发现多出来了一条数据，或者在 T1 事务发现这条数据没有被修改， 看起来像是产生了幻觉，这种现象称为幻读

读未提交，在这种隔离级别下，可能会产生脏读、不可重复读、幻读。

读已提交（RC)，在这种隔离级别下，可能会产生不可重复读和幻读。

可重复读（RR），在这种隔离级别下，可能会产生幻读

串行化，在这种隔离级别下，多个并行事务串行化执行，不会产生安全性问题。

这四种隔离级别里面，只有串行化解决了全部的问题，但也意味着这种隔离级别的性能是最低的。

在 Mysql 里面，InnoDB 引擎默认的隔离级别是 RR（可重复读），因为它需要保证事务 ACID 特性中的隔离性特征。

27、acid靠什么保证的

1、原子性是由undolog日志来保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功是sql。

2、一致性是由其他三大特征保证，程序代码要保证业务上的一致性。

3、隔离性是由MVCC来保证

4、持久性由redolog来保证，MySQL修改数据的时候会在redolog中记录一份日志数据，就算数据没有保存成功，只要日志保存成功了，数据仍然不会丢失。

28、事务的特性和隔离级别

在 Mysql 中事务的四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔离性（Isalotion）、持久性(Durable)，简称为 ACID。
原子性是指事务的原子性操作，对数据的修改要么全部执行成功，要么全部失败，实现事务的原子性，是基于日志的 Redo/Undo 机制。
持久性则是指在一个事务提交后，这个事务的状态会被持久化到数据库中，也就是事务提交，对数据的新增、更新将会持久化到数据库中。
在我的理解中，原子性、隔离性、持久性都是为了保障一致性而存在的，一致性也是最终的目的。

原子性是基于日志的 Redo/Undo 机制，你能说一说 Redo/Undo 机制吗？
Redo/Undo 机制比较简单，它们将所有对数据的更新操作都写到日志中。
Redo log 用来记录某数据块被修改后的值，可以用来恢复未写入 data file 的已成功事务更新的数据；Undo log 是用来记录数据更新前的值，保证数据更新失败能够回滚。
假如数据库在执行的过程中，不小心崩了，可以通过该日志的方式，回滚之前已经执行成功的操作，实现事务的一致性。

可以举一个场景，说一下具体的实现流程吗？

可以的，假如某个时刻数据库崩溃，在崩溃之前有事务 A 和事务 B 在执行，事务 A 已经提交，而事务 B 还未提交。当数据库重启进行 crash-recovery 时，就会通过 Redo log 将已经提交事务的更改写到数据文件，而还没有提交的就通过 Undo log 进行 roll back。

• READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
• READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
• REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ 隔离级别 Oracle 默认采用的 READ_COMMITTED 隔离级别

29、什么是脏读、幻读、不可重复读

脏读：指一个线程中的事务读取到了另外一个线程中未提交的数据。

脏读是读到了别的事务回滚前的脏数据。比如事务B执行过程中修改了数据X，在未提交前，事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

不可重复读：指一个线程中的事务读取到了另外一个线程中提交的 update 的数据。

事务A首先读取了一条数据，然后执行逻辑的时候，事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。

幻读：指一个线程中的事务读取到了另外一个线程中提交的 insert 的数据。

事务A首先根据条件索引得到N条数据，然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据，导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

30、MySQL可重复读怎么实现的

使用 MVCC 实现的，即 Mutil-Version Concurrency Control，多版本并发控制。关于 MVCC，比较常见的说法如下，包括《高性能 MySQL》也是这么介绍的。

InnoDB 在每行记录后面保存两个隐藏的列，分别保存了数据行的创建版本号和删除版本号。每开始一个新的事务，系统版本号都会递增。事务开始时刻的版本号会作为事务的版本号，用来和查询到的每行记录的版本号对比。在可重复读级别下，MVCC是如何操作的：

SELECT：必须同时满足以下两个条件，才能查询到。1）只查版本号早于当前版本的数据行；2）行的删除版本要么未定义，要么大于当前事务版本号。

INSERT：为插入的每一行保存当前系统版本号作为创建版本号。

DELETE：为删除的每一行保存当前系统版本号作为删除版本号。

UPDATE：插入一条新数据，保存当前系统版本号作为创建版本号。同时保存当前系统版本号作为原来的数据行删除版本号。

MVCC 只作用于 RC（Read Committed）和 RR（Repeatable Read）级别，因为 RU（Read Uncommitted）总是读取最新的数据版本，而不是符合当前事务版本的数据行。而 Serializable 则会对所有读取的行都加锁。这两种级别都不需要 MVCC 的帮助。

最初我也是坚信这个说法的，但是后面发现在某些场景下这个说法其实有点问题。

举个简单的例子来说：如果线程1和线程2先后开启了事务，事务版本号为1和2，如果在线程2开启事务的时候，线程1还未提交事务，则此时线程2的事务是不应该看到线程1的事务修改的内容的。

但是如果按上面的这种说法，由于线程1的事务版本早于线程2的事务版本，所以线程2的事务是可以看到线程1的事务修改内容的。

31、好像是有这个问题，那究竟是怎么实现的？

实际上，InnoDB 会在每行记录后面增加三个隐藏字段：

DB_ROW_ID：行ID，随着插入新行而单调递增，如果有主键，则不会包含该列。

DB_TRX_ID：记录插入或更新该行的事务的事务ID。

DB_ROLL_PTR：回滚指针，指向 undo log 记录。每次对某条记录进行改动时，该列会存一个指针，可以通过这个指针找到该记录修改前的信息 。当某条记录被多次修改时，该行记录会存在多个版本，通过DB_ROLL_PTR 链接形成一个类似版本链的概念。

接下来进入正题，以 RR 级别为例：每开启一个事务时，系统会给该事务会分配一个事务 Id，在该事务执行第一个 select 语句的时候，会生成一个当前时间点的事务快照 ReadView，主要包含以下几个属性：

• trx_ids：生成 ReadView 时当前系统中活跃的事务 Id 列表，就是还未执行事务提交的。
• up_limit_id：低水位，取 trx_ids 中最小的那个，trx_id 小于该值都能看到。
• low_limit_id：高水位，生成 ReadView 时系统将要分配给下一个事务的id值，trx_id 大于等于该值都不能看到。
• creator_trx_id：生成该 ReadView 的事务的事务 Id。

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：

1）如果被访问版本的trx_id与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。

2）如果被访问版本的trx_id小于ReadView中的up_limit_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。

3）如果被访问版本的trx_id大于ReadView中的low_limit_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。

4）如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_ids列表中。如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

在进行判断时，首先会拿记录的最新版本来比较，如果该版本无法被当前事务看到，则通过记录的 DB_ROLL_PTR 找到上一个版本，重新进行比较，直到找到一个能被当前事务看到的版本。

而对于删除，其实就是一种特殊的更新，InnoDB 用一个额外的标记位 delete_bit 标识是否删除。当我们在进行判断时，会检查下 delete_bit 是否被标记，如果是，则跳过该版本，通过 DB_ROLL_PTR 拿到下一个版本进行判断。

以上内容是对于 RR 级别来说，而对于 RC 级别，其实整个过程几乎一样，唯一不同的是生成 ReadView 的时机，RR 级别只在事务开始时生成一次，之后一直使用该 ReadView。而 RC 级别则在每次 select 时，都会生成一个 ReadView。

32、mvcc 可以解决幻读吗

幻读：在一个事务中使用相同的 SQL 两次读取，第二次读取到了其他事务新插入的行，则称为发生了幻读。

例如：

1）事务1第一次查询：select * from user where id < 10 时查到了 id = 1 的数据

2）事务2插入了 id = 2 的数据

3）事务1使用同样的语句第二次查询时，查到了 id = 1、id = 2 的数据，出现了幻读。

谈到幻读，首先我们要引入“当前读”和“快照读”的概念，聪明的你一定通过名字猜出来了：

快照读：生成一个事务快照（ReadView），之后都从这个快照获取数据。普通 select 语句就是快照读。

当前读：读取数据的最新版本。常见的 update/insert/delete、还有 select … for update、select … lock in share mode 都是当前读。

对于快照读，MVCC 因为因为从 ReadView 读取，所以必然不会看到新插入的行，所以天然就解决了幻读的问题。

而对于当前读的幻读，MVCC 是无法解决的。需要使用 Gap Lock 或 Next-Key Lock（Gap Lock + Record Lock）来解决。

其实原理也很简单，用上面的例子稍微修改下以触发当前读：select * from user where id < 10 for update，当使用了 Gap Lock 时，Gap 锁会锁住 id < 10 的整个范围，因此其他事务无法插入 id < 10 的数据，从而防止了幻读。

33、那经常有人说 Repeatable Read 解决了幻读是什么情况？

SQL 标准中规定的 RR 并不能消除幻读，但是 MySQL 的 RR 可以，靠的就是 Gap 锁。在 RR 级别下，Gap 锁是默认开启的，而在 RC 级别下，Gap 锁是关闭的。

34、乐观锁与悲观锁了解吗，如何使用

乐观锁

顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量

如何实现乐观锁

第一种方案
通过 数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的 version 值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据

第二种方案
在需要乐观锁控制的 table 中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则 OK，否则就是版本冲突。

应用场景

比如A、B操作员同时读取一余额为1000元的账户，A操作员为该账户增加100元，B操作员同时为该账户扣除50元，A先提交，B后提交。最后实际账户余额为1000-50=950元，但本该为1000+100-50=1050。这就是典型的并发问题。

悲观锁

对于并发间操作产生的线程安全问题持悲观状态，悲观锁认为竞争总是会发生，因此每次对某资源进行操作时，都会持有一个独占的锁，然后再操作资源。

悲观锁采用的是「先获取锁再访问」的策略，来保障数据的安全。但是加锁策略，依赖数据库实现，会增加数据库的负担，且会增加死锁的发生几率。此外，对于不会发生变化的只读数据，加锁只会增加额外不必要的负担。在实际的实践中，对于并发很高的场景并不会使用悲观锁，因为当一个事务锁住了数据，那么其他事务都会发生阻塞，会导致大量的事务发生积压拖垮整个系统。

场景

在商品购买场景中，当有多个用户对某个库存有限的商品同时进行下单操作。若采用先查询库存，后减库存的方式进行库存数量的变更，将会导致超卖的产生。

若使用悲观锁，当B用户获取到某个商品的库存数据时，用户A则会阻塞，直到B用户完成减库存的整个事务时，A用户才可以获取到商品的库存数据。则可以避免商品被超卖。

初始化表结构和数据

CREATE TABLE `tbl_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `status` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
);


INSERT INTO `tbl_user` (`id`, `status`, `name`)
VALUES
    (1,1,X'7469616E'),
    (2,1,X'63697479');

事务操作

窗口1

// 关闭mysql数据库的自动提交属性
set autocommit=0;

// 开启事务
BEGIN;

SELECT * FROM tbl_user where id=1 for update;

窗口2

SELECT * FROM tbl_user where id=1 for update;

如何使用

如何使用悲观锁

用法：SELECT … FOR UPDATE;

如下操作

select * from tbl_user where id=1 for update;

获取锁的前提：结果集中的数据没有使用排他锁或共享锁时，才能获取锁，否则将会阻塞。

需要注意的是， FOR UPDATE 生效需要同时满足两个条件时才生效：

• 数据库的引擎为 innoDB
• 操作位于事务块中（BEGIN/COMMIT）

35、什么是共享锁、排它锁或者说从锁的类型划分

排它锁：写锁，如果事务1对数据对象a加上锁，事务1可以读a也可以修改a，其他事物不能对a任何操作，直到事务1释放在a上的锁，这也就保证了其他事物在事务1在释放a的锁不能再读取和修改a

共享锁：读锁，如果事务1对数据对象a加锁，则事务1可以读a但是不能修改a,其他事务只能对a共享锁，不能加排它锁，直到事务1释放a上的排它锁，这就保证其他事务可以读a,但是在事务1释放a上的共享锁之前不能对a进行修改。

ReentrantLock就是一种排它锁。CountDownLatch是一种共享锁

ReentrantLock是一个排他锁，这种锁在同一时刻只允许一个线程进行访问。在实际生产中，多个线程同时读一个资源是不会产生并发问题的

CountDownLatch 是一个同步工具类，它允许一个线程或多个线程一直等待，知道其他线程的操作执行完毕再执行，倒计时操作

36、讲一下MySQL死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

如果业务处理不好可以用分布式事务锁或者使用乐观锁

如何查看死锁

使用命令show engine innodb status查看最近一次出现的死锁。
还可以使用Innodb Lock Monitor 打开监控，每15s输出一次日志。 使用完后建议关闭，会影响性能。

如何处理死锁

1. 通过Innodbblockwait_timeout 来设置超时时间，一直等待直到超时。
2. 发起死锁检测，发现死锁后，主动回滚到死锁中的某一事务，让其他事务继续执行。

37、innodb行锁三剑客

行锁在 InnoDB 中是基于索引实现的，所以一旦某个加锁操作没有使用索引，那么该锁就会退化为表锁。

行锁三剑客指的是：InnoDB引擎下的记录锁（Record Locks），间隙锁（Gap Locks），临键锁（Next-Key Locks）。

记录锁

记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录

SELECT * FROM table WHERE id = 1 FOR UPDATE; 

id 为 1 的记录行会被锁住。
需要注意的是：

id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁。
同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

其他实现
在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

间隙锁

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。
注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

当我们使用索引进行范围查询，命中了记录的情况下，就是使用了临键锁，他相当于记录锁+间隙锁。

两种退化的情况：

唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。

没有匹配到任何记录的时候，退化成间隙锁。

左开右闭区间，目的是为了解决幻读的问题。

select * from xx where id > 5 and id < 9;

38、说说数据库的行锁和表锁？

行锁：操作时只锁某一（些）行，不对其它行有影响。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。

表锁：即使操作一条记录也会锁住整个表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突概率高，并发度最低。

页锁：操作时锁住一页数据（16kb）。开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

InnoDB 有行锁和表锁，MyIsam 只有表锁。

39、InnoDB 的行锁是怎么实现的？

InnoDB 行锁是通过索引上的索引项来实现的。意味者：只有通过索引条件检索数据，InnoDB 才会使用行级锁，否则，InnoDB将使用表锁！

对于主键索引：直接锁住锁住主键索引即可。

对于普通索引：先锁住普通索引，接着锁住主键索引，这是因为一张表的索引可能存在多个，通过主键索引才能确保锁是唯一的，不然如果同时有2个事务对同1条数据的不同索引分别加锁，那就可能存在2个事务同时操作一条数据了。

40、InnoDB 锁的算法有哪几种？

Record lock：记录锁，单条索引记录上加锁，锁住的永远是索引，而非记录本身。

Gap lock：间隙锁，在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

Next-key lock：Record lock 和 Gap lock 的结合，即除了锁住记录本身，也锁住索引之间的间隙。

41、数据库的锁，到底锁的是什么？

在MySQL数据库中，为了解决并发问题，引入了很多的锁机制，很多时候，数据库的锁是在有数据库操作的过程中自动添加的。

所以，这就导致很多程序员经常会忽略数据库的锁机制的真正的原理。比如，我经常在面试中会问候选人，你知道MySQL Innodb的锁，到底锁的是什么吗？

关于这个问题的回答，我听到过很多种，但是很少有人可以把他回答的很完美。因为想要回答好这个问题，需要对数据库的隔离级别、索引等都有一定的了解才行。

MySQL Innodb的锁的相关介绍，在MySQL的官方文档(https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-insert-intention-locks )中有一定的介绍，本文的介绍也是基于这篇官方文档的。

*Record Lock*

**Record Lock，翻译成记录锁，是加在索引记录上的锁。**例如，SELECT c1 FROM t WHERE c1 = 10 For UPDATE;会对c1=10这条记录加锁，为了防止任何其他事务插入、更新或删除c1值为10的行。

需要特别注意的是，记录锁锁定的是索引记录。即使表没有定义索引，InnoDB也会创建一个隐藏的聚集索引，并使用这个索引来锁定记录。

Gap Lock

Gap Lock，翻译成间隙锁，他指的是在索引记录之间的间隙上的锁，或者在第一个索引记录之前或最后一个索引记录之后的间隙上的锁。

那么，这里所谓的Gap（间隙）又怎么理解呢？

Gap指的是InnoDB的索引数据结构中可以插入新值的位置。

当你用语句SELECT…FOR UPDATE锁定一组行时。InnoDB可以创建锁，应用于索引中的实际值以及他们之间的间隙。例如，如果选择所有大于10的值进行更新，间隙锁将阻止另一个事务插入大于10的新值。

既然是锁，那么就可能会影响到数据库的并发性，所以，间隙锁只有在Repeatable Reads这种隔离级别中才会起作用。

在Repeatable Reads这种隔离下，对于锁定的读操作（select … for update 、 lock in share mode)、update操作、delete操作时，会进行如下的加锁：

• 对于具有唯一搜索条件的唯一索引，InnoDB只锁定找到的索引记录，而不会锁定间隙。
• 对于其他搜索条件，InnoDB锁定扫描的索引范围，使用gap lock或next-key lock来阻塞其他事务插入范围覆盖的间隙。

也就是说，对于SELECT FOR UPDATE、LOCK IN SHARE MODE、UPDATE和DELETE等语句处理时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁，即锁住其扫描的范围。

Next-Key Lock

Next-Key锁是索引记录上的记录锁和索引记录之前间隙上的间隙锁的组合。

假设一个索引包含值10、11、13和20。此索引可能的next-key锁包括以下区间:

(-∞, 10]

(10, 11]

(11, 13]

(13, 20]

(20, ∞ ]

对于最后一个间隙，∞不是一个真正的索引记录，因此，实际上，这个next-key锁只锁定最大索引值之后的间隙。

所以，Next-Key 的锁的范围都是左开右闭的。

Next-Key Lock和Gap Lock一样，只有在InnoDB的RR隔离级别中才会生效。

Repeatable Reads能解决幻读

很多人看过网上的关于数据库事务级别的介绍，会认为MySQL中Repeatable Reads能解决不可重复读的问题，但是不能解决幻读，只有Serializable才能解决。但其实，这种想法是不对的。

因为MySQL跟标准RR不一样，标准的Repeatable Reads确实存在幻读问题，但InnoDB中的Repeatable Reads是通过next-key lock解决了RR的幻读问题的。

因为我们知道，因为有了next-key lock，所以在需要加行锁的时候，会同时在索引的间隙中加锁，这就使得其他事务无法在这些间隙中插入记录，这就解决了幻读的问题。

关于这个问题，引起过广泛的讨论，可以参考：https://github.com/Yhzhtk/note/issues/42 ，这里有很多大神发表过自己的看法。

MySQL的加锁原则

前面介绍过了Record Lock、Gap Lock和Next-Key Lock，但是并没有说明加锁规则。关于加锁规则，我是看了丁奇大佬的《MySQL实战45讲》中的文章之后理解的，他总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”：

原则 1：加锁的基本单位是 next-key lock。是一个前开后闭区间。原则 2：查找过程中访问到的对象才会加锁。优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

假如，数据库表中当前有以下记录：

当我们执行update t set d=d+1 where id = 7的时候，由于表 t 中没有 id=7 的记录，所以：

• 根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
• 根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

当我们执行select * from t where id>=10 and id<11 for update的时候：

• 根据原则 1，加锁单位是 next-key lock，会给 (5,10]加上 next-key lock，范围查找就往后继续找，找到 id=15 这一行停下来
• 根据优化 1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
• 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。因此最终加的是行锁 id=10 和 next-key lock(10,15]。

当我们执行select * from t where id>10 and id<=15 for update的时候：* 根据原则 1，加锁单位是 next-key lock，会给 (10,15]加上 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。* 但是，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

假如，数据库表中当前有以下记录：

当我们执行select id from t where c=5 lock in share mode的时候：

• 根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。
• 根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock。
• 根据优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
• 根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁。

当我们执行select * from t where c>=10 and c<11 for update的时候：

• 根据原则 1，加锁单位是 next-key lock，会给 (5,10]加上 next-key lock，范围查找就往后继续找，找到 id=15 这一行停下来
• 根据原则 2，访问到的都要加锁，因此需要加 next-key lock(10,15]。
• 由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

总结

以上，我们介绍了InnoDB中的锁机制，一共有三种锁，分别是Record Lock、Gap Lock和Next-Key Lock。

Record Lock表示记录锁，锁的是索引记录。Gap Lock是间隙锁，说的是索引记录之间的间隙。Next-Key Lock是Record Lock和Gap Lock的组合，同时锁索引记录和间隙。他的范围是左开右闭的。

InnoDB的RR级别中，加锁的基本单位是 next-key lock，只要扫描到的数据都会加锁。唯一索引上的范围查询会访问到不满足条件的第一个值为止。

同时，为了提升性能和并发度，也有两个优化点：

• 索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
• 索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

关于锁的介绍，就是这么多了，但是其实，RR的隔离级别引入的这些锁，虽然一定程度上可解决很多如幻读这样的问题，但是也会带来一些副作用，比如并发度降低、容易导致死锁等。

42、谈一下对MySQL日志理解

binlog用于记录数据库执行的写入性操作，以二进制的形式保存在磁盘中。binlog 是 mysql的逻辑日志（可以理解为是sql语句的二进制存储），并且由 Server 层进行记录，使用任何存储引擎的 mysql 数据库都会记录 binlog 日志。

在实际应用中， binlog 的主要使用场景有两个，分别是 主从复制 和 数据恢复

redo log 包括两部分：一个是内存中的日志缓冲( redo log buffer )，另一个是磁盘上的日志文件( redo log file )。mysql 每执行一条 DML 语句，先将记录写入 redo log buffer，后续某个时间点再一次性将多个操作记录写到 redo log file 。这种 先写日志，再写磁盘 的技术就是 MySQL里经常说到的 WAL(Write-Ahead Loggin``g) 技术。

数据库事务四大特性中有一个是 原子性 ，具体来说就是 原子性是指对数据库的一系列操作，要么全部成功，要么全部失败，不可能出现部分成功的情况。实际上， 原子性 底层就是通过 undo log 实现的。undo log 主要记录了数据的逻辑变化，比如一条 INSERT 语句，对应一条 DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态

43、说说 MySQL 的主从复制？

MySQL主从复制涉及到三个线程，一个运行在主节点（Log Dump Thread），其余两个（I/O Thread，SQL Thread）运行在从节点，如下图所示

主从复制默认是异步的模式，具体过程如下。

1）从节点上的I/O 线程连接主节点，并请求从指定日志文件（bin log file）的指定位置（bin log position，或者从最开始的日志）之后的日志内容；

2）主节点接收到来自从节点的 I/O请求后，读取指定文件的指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的 bin-log file 以及 bin-log position；从节点的 I/O 进程接收到内容后，将接收到的日志内容更新到 relay log 中，并将读取到的 bin log file（文件名）和position（位置）保存到 master-info 文件中，以便在下一次读取的时候能够清楚的告诉 Master “我需要从某个bin-log 的哪个位置开始往后的日志内容”；

3）从节点的 SQL 线程检测到 relay-log 中新增加了内容后，会解析 relay-log 的内容，并在本数据库中执行。

44、异步复制，主库宕机后，数据可能丢失？

可以使用半同步复制或全同步复制。

半同步复制：

修改语句写入bin log后，不会立即给客户端返回结果。而是首先通过log dump 线程将 binlog 发送给从节点，从节点的 I/O 线程收到 binlog 后，写入到 relay log，然后返回 ACK 给主节点，主节点 收到 ACK 后，再返回给客户端成功。

半同步复制的特点：

• 确保事务提交后 binlog 至少传输到一个从库
• 不保证从库应用完这个事务的 binlog
• 性能有一定的降低，响应时间会更长
• 网络异常或从库宕机，卡主主库，直到超时或从库恢复

全同步复制：主节点和所有从节点全部执行了该事务并确认才会向客户端返回成功。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

45、主库写压力大，从库复制很可能出现延迟？

可以使用并行复制（并行是指从库多个SQL线程并行执行 relay log），解决从库复制延迟的问题。

MySQL 5.7 中引入基于组提交的并行复制，其核心思想：一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的 prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）。

判断事务是否处于一个组是通过 last_committed 变量，last_committed 表示事务提交的时候，上次事务提交的编号，如果事务具有相同的 last_committed，则表示这些事务都在一组内，可以进行并行的回放。

46、MySQL查询流程

网络连接：mysql 客户端通过协议与 mysql 服务器建连接

查询缓存：连接器得工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句；如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。如果查询的语句命中查询缓存，那么就会直接返回 value 给客户端。如果查询的语句没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中

解析器：在正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析，这个工作交由由解析器来完成，解析器会做如下两件事情，词法分析（MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方面后面模块获取 SQL 类型、表名、字段名、 where 条件），语法分析（判断你输入的这个 SQL 语句是否满足 MySQL 语法）

优化器： 经过解析器后，接着就要执行 SQL 查询语句了，但是在真正执行之前，会检查用户是否有访问该数据库表的权限，如果没有就直接报错了。如果有权限，就进入 SQL 查询语句的执行阶段，而 SQL 查询语句真正执行之前需要先制定一个执行计划，这个工作交由「优化器」来完成的。

执行器：执行器根据执行计划，调用存储引擎的api接口，之后存储引擎层是一个可插拔的设计，不同存储引擎会去实现不同API接口，可以自由的更换我们的存储引擎，上层是无感知的。

47、mysql缓存

查询缓存8.0取消，数据更新时候，数据容易失效，缓存命中率比较低；

5.7版本，有意义，对应表是字典表，不经常变更；

48、为什么需要redolog,不用行不行

其实不用redolog不用也行，但是我们需要保证事务持久性，所以这就要求我们事务运行过程中，修改所有数据数据都得刷到磁盘，

unredolog一些数据页，索引页还有changebuffer 这些都需要刷到磁盘，它们在磁盘是不同的位置，这就导致大量随机io，性能会

很慢；那么我们既想要保证事务持久性，又保证效率，我们就需要redolog ,它记录其实这些文件的具体修改值，有了redolog ,它是

顺序追加写的方式写进磁盘，避免了很多随机io，之后的所有其他文件只需要修改缓存，redolog 只要在事务提交时候，把redolog刷进磁盘

就可以了，避免一些崩溃数据丢失问题

49、redolog、undolog、binlog是什么时候进行刷盘的？？

bufferpool中数据什么时候进行刷盘？？

undolog是不会进行落盘的

Undo logs that reside in the global temporary tablespace are used for transactions that modify data in user-defined temporary tables. These undo logs are not redo-logged, as they are not required for crash recovery. They are used only for rollback while the server is running. This type of undo log benefits performance by avoiding redo logging I/O.
翻译：全局空间中的undolog只服务于服务器运行时的回滚，并不是宕机恢复的必要条件。undolog的存在避免了redolog的IO操作，从而提高性能。

redolog是顺序写入磁盘的，IO操作后返回；

redolog在第一阶段先是写入log buffer中，默认在每次提交进行刷盘；

binlog先写入binlog cache, 每次提交IO到磁盘

data是在bufferpool中修改，专门IO线程根据刷盘机制刷入磁盘；一般默认是自适应算法adapted flushing

缓存到磁盘固化

刷binlog的重要参数就是sync_binlog：

sync_binlog=0：表示关闭写binlog；

sync_binlog=1：表示每次提交便写binlog到磁盘（默认）;

sync_binlog=N（N>=2): 表示binlog组提交;

logbuffer的重要参数是innodb_flush_log_at_trx_commit:

innodb_flush_log_at_trx_commit=0: 表示每秒写入磁盘，可能会丢失事务；

innodb_flush_log_at_trx_commit=1：表示每次提交就写入磁盘（默认）；

innodb_flush_log_at_trx_commit=2：表示提交或每秒写入磁盘，也不会丢失事务。

刷脏页一般发生在commit之后，redo和binlog提交之后。

50、mvcc工作原理

简单来说，MVCC就是存储了同一条数据的不同历史版本链，不同事务可以访问不同的数据版本。

相关的概念

1、事务版本号

事务每次开启时，都会从数据库获得一个自增长的事务ID，可以从事务ID判断事务的执行先后顺序。这就是事务版本号。

也就是每当begin的时候,首选要做的就是从数据库获得一个自增长的事务ID,它也就是当前事务的事务ID。

2、隐藏字段

对于InnoDB存储引擎，每一行记录都有两个隐藏列trx_id、roll_pointer,如果数据表中存在主键或者非NULL的UNIQUE键时不会创建row_id,否则InnoDB会自动生成单调递增的隐藏主键row_id。

列名	是否必须	描述
row_id	否	单调递增的行ID，不是必需的，占用6个字节。 这个跟MVCC关系不大
trx_id	是	记录操作该行数据事务的事务ID
roll_pointer	是	回滚指针，指向当前记录行的undo log信息

这里的记录操作，指的是insert|update|delete。对于delete操作而已，InnoDB认为是一个update操作，不过会更新一个另外的删除位，将行表示为deleted,并非真正删除。

3、undo log

undo log可以理解成回滚日志,它存储的是老版本数据。在表记录修改之前，会先把原始数据拷贝到undo log里，如果事务回滚，即可以通过undo log来还原数据。或者如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本。

在insert/update/delete(本质也是做更新，只是更新一个特殊的删除位字段)操作时，都会产生undo log。

在InnoDB里，undo log分为如下两类：

1）insert undo log : 事务对insert新记录时产生的undo log, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃。

2）update undo log : 事务对记录进行delete和update操作时产生的undo log，不仅在事务回滚时需要，快照读也需要，只有当数据库所使用的快照中不涉及该日志记录，对应的回滚日志才会被删除。

undo log有什么用途呢？

1、事务回滚时，保证原子性和一致性。
2、如果当前记录行不可见，可以顺着undo log链找到满足其可见性条件的记录行版本(用于MVCC快照读)。

4、版本链

多个事务并行操作某一行数据时，不同事务对该行数据的修改会产生多个版本，然后通过回滚指针（roll_pointer），连成一个链表，这个链表就称为版本链。如下：

5、快照读和当前读

快照读： 读取的是记录数据的可见版本（有旧的版本）。不加锁,普通的select语句都是快照读,如：

select * from user where id = 1;

当前读：读取的是记录数据的最新版本，显式加锁的都是当前读

select * from user where id = 1 for update;
select * from user where id = 1 lock in share mode;

6、ReadView

ReadView是事务在进行快照读的时候生成的记录快照, 可以帮助我们解决可见性问题的

如果一个事务要查询行记录，需要读取哪个版本的行记录呢？ ReadView 就是来解决这个问题的。 ReadView 保存了当前事务开启时所有活跃的事务列表。换个角度，可以理解为: ReadView 保存了不应该让这个事务看到的其他事务 ID 列表。

ReadView是如何保证可见性判断的呢？我们先看看 ReadView 的几个重要属性

• trx_ids: 当前系统中那些活跃(未提交)的读写事务ID, 它数据结构为一个List。(重点注意:这里的trx_ids中的活跃事务，不包括当前事务自己和已提交的事务，这点非常重要)
• low_limit_id: 目前出现过的最大的事务ID+1，即下一个将被分配的事务ID。
• up_limit_id: 活跃事务列表trx_ids中最小的事务ID，如果trx_ids为空，则up_limit_id 为 low_limit_id。
• creator_trx_id: 表示生成该 ReadView 的事务的 事务id

访问某条记录的时候如何判断该记录是否可见,具体规则如下：

• 如果被访问版本的 事务ID = creator_trx_id，那么表示当前事务访问的是自己修改过的记录，那么该版本对当前事务可见；
• 如果被访问版本的 事务ID < up_limit_id，那么表示生成该版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的 事务ID > low_limit_id 值，那么表示生成该版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本的 事务ID在 up_limit_id和m_low_limit_id 之间，那就需要判断一下版本的事务ID是不是在 trx_ids 列表中，如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；
  如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。

画张图来理解下

这里需要思考的一个问题就是 何时创建ReadView？

上面说过,ReadView是来解决一个事务需要读取哪个版本的行记录的问题的。那么说明什么？只有在select的时候才会创建ReadView。但在不同的隔离级别是有区别的:

在RC隔离级别下，是每个select都会创建最新的ReadView；而在RR隔离级别下，则是当事务中的第一个select请求才创建ReadView(下面会详细举例说明)。

那insert/update/delete操作呢？

这样操作不会创建ReadView。但是这些操作在事务开启(begin)且其未提交的时候，那么它的事务ID,会存在在其它存在查询事务的ReadView记录中，也就是trx_ids中。

查询流程

1. 获取事务自己事务ID,即trx_id。(这个也不是select的时候获取的，而是这个事务开启的时候获取的 也就是begin的时候)
2. 获取ReadView(这个才是select的时候才会生成的)
3. 数据库表中如果查询到数据，那就到ReadView中的事务版本号进行比较。
4. 如果不符合ReadView的可见性规则， 即就需要Undo log中历史快照,直到返回符合规则的数据;

InnoDB 实现MVCC，是通过ReadView+ Undo Log 实现的，Undo Log 保存了历史快照，ReadView可见性规则帮助判断当前版本的数据是否可见。

51、系统突然宕机了，mysql很多脏页没有刷到磁盘，这些数据如何恢复

其实就是redolog恢复的，redolog有一个lsn版本号，我们redolog文件不断写入这lsn版本号不断增加，然后有三个比较重要的lsn指针，第一个指针就是更新指针redolog buffer就是redolog缓存写出写入的一个最新值的一个位置，第二个指针redolog刷盘指针,也就是redolog哪些缓存中值已经刷到磁盘里面，然后记录的是这样一个指针；第三个指针记录脏页的刷盘指针，它们已经刷进磁盘里面；崩溃瞬间就是把第三个指针之后所有数据给查询出来，然后这些redolog对应那些脏页加载缓存里面，之后通过redolog重放之前的修改，之后之前缓存页变回脏页变到崩溃前的一个状态，之后我们根据事务状态来决定它到底是提交还是回滚，之后就可以恢复之前所有的情况。

52、为什么需要二阶段提交

如果没有两阶段提交，那么 binlog 和 redolog 的提交，无非就是两种形式：

先写 binlog 再写 redolog。
先写 redolog 再写 binlog。
这两种情况我们分别来看。

假设我们要向表中插入一条记录 R，如果是先写 binlog 再写 redolog，那么假设 binlog 写完后崩溃了，此时 redolog 还没写。那么重启恢复的时候就会出问题：binlog 中已经有 R 的记录了，当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就会同步到 R 这条记录；但是 redolog 中没有关于 R 的记录，所以崩溃恢复之后，插入 R 记录的这个事务是无效的，即数据库中没有该行记录，这就造成了数据不一致。

相反，假设我们要向表中插入一条记录 R，如果是先写 redolog 再写 binlog，那么假设 redolog 写完后崩溃了，此时 binlog 还没写。那么重启恢复的时候也会出问题：redolog 中已经有 R 的记录了，所以崩溃恢复之后，插入 R 记录的这个事务是有效的，通过该记录将数据恢复到数据库中；但是 binlog 中还没有关于 R 的记录，所以当从机从主机同步数据的时候或者我们使用 binlog 恢复数据的时候，就不会同步到 R 这条记录，这就造成了数据不一致。

那么按照前面说的两阶段提交就能解决问题吗？

我们来看如下三种情况：

**情况一：**一阶段提交之后崩溃了，即写入 redo log，处于 prepare 状态 的时候崩溃了，此时：

由于 binlog 还没写，redo log 处于 prepare 状态还没提交，所以崩溃恢复的时候，这个事务会回滚，此时 binlog 还没写，所以也不会传到备库。

**情况二：**假设写完 binlog 之后崩溃了，此时：

redolog 中的日志是不完整的，处于 prepare 状态，还没有提交，那么恢复的时候，首先检查 binlog 中的事务是否存在并且完整，如果存在且完整，则直接提交事务，如果不存在或者不完整，则回滚事务。

**情况三：**假设 redolog 处于 commit 状态的时候崩溃了，那么重启后的处理方案同情况二。

由此可见，两阶段提交能够确保数据的一致性。

美团三面：一直追问我， MySQL 幻读被彻底解决了吗？

MySQL 的 Buffer Pool （缓冲池）是如何工作的？

索引下推