MySQL面试

谈一下你对于索引的理解（为什么MySQL要选择B+树来存储索引）

mysql的索引选择B+树作为数据结构来进行存储，使用B+树的本质原因在于可以减少IO次数，提高查询的效率，简单点来说就是可以保证在树的高度不变的情况下可以存储更多的数据：

1. 在MYSQL的数据库中，表的真实数据和索引数据都是存储在磁盘中，我们在进行数据读写的时候必然涉及到IO的问题，IO本质上来说是硬件方面的问题，但是我们在做索引设计的时候肯定要尽可能的考虑如何提高IO的效率，一般来说，提高IO效率主要有两个维度的考虑，减少IO次数和减少IO量，所以要遵循这两个原则

2. 我们在进行数据存储的时候量是没办法预估的，当表的数据量非常大的时候，我们是没有办法一次性将所有的数据都读取到内存中，因此这个时候就要采用分治的思想，将数据进行分块读取，一旦分块读取的话，我们就要考虑设计合理的块大小

3. 数据在磁盘存储的时候有时间局部性和空间局部性的特性，内存跟磁盘在进行数据交互的时候也不是需要啥就读取啥，而是会把相关的数据全部都加载到内存中，在进行加载的时候有一个最基本的逻辑单位称之为页，页的大小一般是4KB或者8KB，跟操作系统相关，我们在数据读取的时候一般会选择页的整数倍读取，比如innodb存储引擎每次读取16KB的大小。这个特性刚刚好跟我们上述说的分块读取的设计观念吻合起来，因此块的大小会选择页的整数倍，在MYSQL中一般都是16KB的大小，当然也可以通过参数来进行调整，比如innodb中的innodb_page_size这个参数，当然一般情况下我们不会调整这个参数的大小

4. 当块的大小确定了之后我们就要考虑数据格式了，我们在使用索引的时候基本是根据某一个或者多个索引列的值来进行整行数据或者部分字段的读取，比如select * from table where id = 10这个语句就是根据id的值去检索整行记录，因此整体的数据格式可以设计为K-V格式的数据，K值就是索引列的值，V值的设计就需要进一步思考了。

5. 正常情况下，当需要从磁盘中读取某一行记录的时候，需要知道一些信息才能够定位到数据，比如：文件名称，偏移量，数据长度，当知道这些信息的时候就可以定位到任何一行记录，所以我们可以将V的值设计为刚刚的几个字段，但是要考虑一件事，如果将刚刚的那些信息作为索引信息的话，那么在进行数据读取的时候，首先要打开一个文件，读取到刚刚的那几个字段信息，然后再根据那些信息找到对应的数据文件读取具体的行数据，如果打开一次文件就是一次IO的话，至少需要2次IO操作才可以读取，这个跟我们上面所说的减少IO次数有点相违背，所以最好的方式是在V中直接将行记录进行存储，那么在读取数据的时候就可以直接根据K值读取到行记录，只不过此时需要将数据跟索引绑定存储，在MYSQL中，innodb存储引擎就是这样存储的，数据文件和索引文件全部位于后缀名为ibd的文件中
   
   

6. 当数据格式确定了之后我们就需要思考使用什么数据结构存储了。支持K-V格式的数据结构有很多，比如哈希表，二叉树，BST，AVL，红黑树，但是MYSQL最终选择了B+树，下面我们要对比下各个数据结构之间的区别：

   1. 使用哈希表可以进行数据存储，但是哈希表本质是无序散列表，因此在进行范围查询的时候就必须要挨个进行数据的对比，此时的效率是比较低的，此外，哈希表会存在哈希碰撞或者哈希冲突的问题，需要设计性能优良的哈希算法，因此哈希表并不适用，但是在MYSQL中，MEMORY存储引擎支持哈希索引，innodb存储引擎支持自适应哈希，

   2. 二叉树、BST、AVL、红黑树这几种树也可以支持K-V格式的数据存储，但是它们有一个共同的特点就是至多只有两个分支，那么在进行数据存储的时候，一个三层的树至多可以存储7个数据结果值，这个数据太少了，如果想要存储更多的数据，只能把树的高度变高，而树的高度变高之后又会导致IO的次数变多，影响查询效率，那么我们就要思考如何在保证树的高度不变的情况下存储更多的数据，上述的这些树存储数据少的原因在于分支至多只有两个，那么我们就要思考改变分支的结构了，因此有了B-树。

   3. 使用B-树之后，存储如下图所示：在每一个数据块中包含三种类型的数据，分别是key值，行记录和指针，当需要进行数据读取的时候只要一层一层向下检索即可：
      
      在上图中，如果要读取28这条记录的话，那么只需要读取磁盘块1,3,8就可以把数据取出来，如果一个磁盘块大小是16KB的话，那么读取48KB的数据就可以获取到要查询的记录，此时我们就要思考，这样3层的B-树存满的情况下可以存储多少条记录了，假设一条记录是1KB的大小，那么第一层至多存储15条记录，第二层至多存储16（第二层的子节点个数）*15（每个节点可以存储的行记录树）=240条记录，第三层至多存储16*16*15=3840条记录，那么三层的树存满的情况下最多存储15+240+3840=4095条记录，此时存储的数据量依然不是很大，如果想要存储更多的数据的话就只能将树变高，变成4层或者5层，那么此时又会增加IO的次数，会影响查询效率，那么此时就要思考为何只能存储这么少的数据，经过分析之后发现，data占用了大量的空间，因此考虑使用B+树进行数据的存储

7. 使用B+树之后，存储如下图所示：将所有的data全部放到了叶子节点中，非叶子节点中只存储key值和指针的值，在进行检索的时候可以从根节点向下检索，也可以在叶子节点中从前向后或者从后向前检索：
   
   在上图中，所有的data都在叶子节点中，也就说第一层和第二层省掉了大量的data的存储空间，那么可以存储更多的数据，假设一个data还是1KB的大小，一个key加上一个指针的大小为10个字节，那么我们可以来计算下可以存储多少数据，第一层一个数据块，第二层16*1024/10=1638个数据块，第三层1638*16*1024\10=2683044个数据块，第三层的每个数据块可以存储16条记录，那么最后的总记录数为42928704条记录，可以发现跟B-树的存储不是一个量级，在相同树高的情况下，B+树可以存储更多的数据

因此MYSQL最终选择了B+树做为数据结构来存储，在刚刚上述的计算公式中，我们做了一个假设，key+指针一共占了10个字节，如果占用100个字节的话，那么整体的数据会缩小2个量级，因此在回答索引的树的高度的时候不要说3层或者4层，给一个标准说法：一般情况下，3-4层的B+树足以支撑千万级别的数据量存储。

索引有哪些分类

索引的分类要按照不同的角度去进行分类：

1. 从数据结构的角度可以分为B+树索引、哈希索引、FULLTEXT索引、R-Tree索引（用于对GIS数据创建SPATIAL索引）

2. 从物理存储角度可以分为聚簇索引和非聚簇索引

3. 从逻辑角度可以分为主键索引、普通索引、唯一索引、组合索引

聚簇索引与非聚簇索引

​ 在MYSQL的innodb存储引擎中，数据在进行插入的时候必须要跟某一个索引列绑定在一起进行存储，如果有主键，那么选择主键，如果没有主键，那么选择唯一键，如果没有唯一键，那么系统会生成一个6字节的rowid进行存储，因此：

​ 跟数据绑定存储的索引称之为聚簇索引

​ 没有跟数据绑定存储的索引称之为非聚簇索引

​ 一张表中只有一个聚簇索引，其他非聚簇索引的叶子节点中存储的值为聚簇索引的列值

回表、索引覆盖、最左匹配原则、索引下推

回表

​ 回表表示使用非聚簇索引时，数据库引擎会先根据普通索引找到匹配的行，然后根据叶子节点中存储的聚簇索引的值去聚簇索引的索引树中查找整行记录的过程。例如：

​ 有一张表有如下字段：id，name，age，gender，address，其中id是主键，name是普通索引

​ 那么要进行如下SQL语句的查询：

select * from table where name = 'zhangsan';

​ 上述SQL语句的查找过程是：先根据name的值去name的索引树上进行检索，找到匹配的记录之后取出id的值，然后再根据id的值去id的B+树上检索整行的记录，在这个过程中，查找了两棵树，多进行了棵树的IO，因此效率比较低，在生产环境中应该尽量避免回表

索引覆盖

​ 索引覆盖是指一个索引包含了查询所需要的所有数据，从而在查询中无需回表从原表中获取数据

​ 假设有一张表，表中有以下字段：id，name，age，gender，address，其中id是主键，name是普通索引

​ 那么要进行如下SQL语句的查询：

select id,name from table where name = 'zhangsan';

​ 查找过程如下：在name的索引树上包含了要查询的所有字段，所以直接通过name字段去name的B+树上检索对应的记录即可，不需要找到id之后再去id的B+树上检索数据

​ 索引覆盖可以提高查询的性能，所以在生产环境做SQL优化的时候，可以考虑索引覆盖

最左匹配原则

最左匹配原则主要适用于组合索引，指的是多个列值进行匹配的时候要严格遵循从左到右的顺序，否则会导致索引失效

假设有一张表，表中有以下字段：id，name，age，gender，address
id是主键，(name,age)是组合索引

1、Select * from table where name = 'zhangsan' and age = 10;
2、Select * from table where name = 'zhangsan';
3、Select * from table where age = 10;
4、Select * from table where age = 10 and name = 'zhangsan';
组合索引 (name, age) 可以理解为一本排序的电话簿：
第一层（主排序键）：按 name 排序。
第二层（次排序键）：在相同 name 的记录中，按 age 进一步排序。
主要分为
完全匹配 (name, age)，如 name = 'zhangsan' AND age = 10。
部分匹配 name，如 name = 'zhangsan'。
不能单独使用 age 进行匹配，除非 name 也被用于查询条件。

上述的四条语句中，1,2,4都可以用到组合索引，3用不到，但是很多同学会有疑问，为什么第四条会用到，明明不符合最左
匹配原则的顺序，这里需要注意，如果把第四条SQL语句的条件换一下顺序，会影响最终的查询结果吗？答案是不会的，所以
MySQL中的优化器会进行优化，调整条件的顺序

索引下推

​ ICP是针对mysql使用索引从表中检索行的情况进行优化，如果没有ICP，那么存储引擎会根据索引来定位到记录，然后将结果返回给mysql的server，然后在server上对where条件进行筛选。在启用ICP之后，如果where条件的一部分可以通过使用索引中的列来求值，那么MySQL会把这部分的where条件筛选下推到存储引擎中。

​ 使用索引下推的时候会有以下的条件：

​ 1、当需要访问完整的行记录时，ICP用于range、ref、eq_ref和ref_or_null访问方法

​ 2、ICP可以用于innodb和myisam表，包括分区的innodb表和myisam表

​ 3、对于innodb表，ICP仅用于二级索引。ICP的目标是减少整行读取的次数，从而减少IO操作

​ 4、在虚拟列上创建的二级索引不支持ICP

​ 5、引用子查询的条件不能下推

​ 6、引用存储函数的条件不能下推

​ 7、触发器条件不能下推

​ 8、不能将条件下推到包含对系统变量引用的派生表中

​ 假设有一张表，表中有以下字段：id，name，age，gender，address，其中id是主键，(name,age)是组合索引

​select * from table where name = 'zhangsan' and age = 10;

​ 没有索引下推：mysql执行这条SQL语句的时候，会首先根据name的值去存储引擎中拉取数据，然后将数据返回到mysql server，然后在server层对age进行条件过滤，把符合条件的结果返回给客户端
​ 有索引下推：mysql执行这条SQL语句的时候，会直接根据name和age的值去存储引擎中拉取数据，而无需在server层对数据进行条件过滤

​ 所谓的下推指的是将条件的筛选从server层下推到存储引擎层

​ 可以通过optizizer_switch中的index_condition_pushdown条件来是否开启，默认是开启的

SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

如何设计性能优良的索引

1. 索引列占用的空间越小越好

2. 选择索引列的时候尽量选择离散度高的列作为索引列，离散度的计算公式count(distinct(column_name)) / count(*)，这个值越大，那么越适合做索引

3. 在where后的order by字段上添加索引

4. 在join on的条件字段上添加索引

5. 索引的个数不要过多，会增加索引的维护成本

6. 频繁更新的字段，不要创建索引，会增加索引的维护成本

7. 随机无序的值，不建议作为主键索引，如身份证号，UUID

8. 索引列在设计的时候最好不为NULL

9. 可以使用列前缀作为索引列
   ​有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

   一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB,TEXT,VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

   	--创建数据表
   	create table citydemo(city varchar(50) not null);
   	insert into citydemo(city) select city from city;
   	
   	--重复执行5次下面的sql语句
   	insert into citydemo(city) select city from citydemo;
   	
   	--更新城市表的名称
   	update citydemo set city=(select city from city order by rand() limit 1);
   	
   	--查找最常见的城市列表，发现每个值都出现45-65次，
   	select count(*) as cnt,city from citydemo group by city order by cnt desc limit 10;
   	
   	--查找最频繁出现的城市前缀，先从3个前缀字母开始，发现比原来出现的次数更多，可以分别截取多个字符查看城市出现的次数
   	select count(*) as cnt,left(city,3) as pref from citydemo group by pref order by cnt desc limit 10;
   	select count(*) as cnt,left(city,7) as pref from citydemo group by pref order by cnt desc limit 10;
   	--此时前缀的选择性接近于完整列的选择性
   	
   	--还可以通过另外一种方式来计算完整列的选择性，可以看到当前缀长度到达7之后，再增加前缀长度，选择性提升的幅度已经很小了
   	select count(distinct left(city,3))/count(*) as sel3,
   	count(distinct left(city,4))/count(*) as sel4,
   	count(distinct left(city,5))/count(*) as sel5,
   	count(distinct left(city,6))/count(*) as sel6,
   	count(distinct left(city,7))/count(*) as sel7,
   	count(distinct left(city,8))/count(*) as sel8 
   	from citydemo;
   	
   	--计算完成之后可以创建前缀索引
   	alter table citydemo add key(city(7));
   	
   	--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。  
   

什么情况下会造成索引失效？

1. 索引列上使用函数（replace\SUBSTR\CONCAT\sum count avg）、表达式

2. 数据类型不匹配，当查询条件的数据类型和索引字段的类型不匹配

3. like 条件中前面带%

4. 在组合索引中，不满足最左匹配原则

5. 使用is not null

6. mysql的优化器在进行分析的时候发现全表扫描比使用索引快的时候

7. 使用or关键字会导致索引失效

主键为什么建议选择自增主键？

​ 如果选择自增主键的话，每次新增数据时，都是以追加的形式进行存储，在本页索引写满之后，只需申请一个新页继续写入即可，不会产生页分裂问题

​ 如果说采用业务字段作为主键的话，新增数据不一定是顺序的，需要挪动数据，页快满时还要去分裂页，保持索引的有序性，造成写数据成本较高

如何查看SQL语句是否使用索引

​ 通过执行计划可以判断查询中是否用到了索引，以便进行SQL优化。

​ explain语句提供了mysql如何执行语句的信息，explain可以跟select、delete、insert、replace、update语句一起工作

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

id号分为三种情况：

​ 1、如果id相同，那么执行顺序从上到下

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

​ 2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

explain select * from emp where ename not in (select ename from emp where ename like '%S%') ;

​ 3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

explain Select dept.*,person_num,avg_sal from dept,(select count(*) person_num,avg(sal) avg_sal,deptno from emp group by deptno) t where dept.deptno = t.deptno ;

select_type

主要用来分辨查询的类型，是普通查询还是联合查询还是子查询

select_type Value	JSON Name	Meaning
SIMPLE	None	Simple SELECT (not using UNION or subqueries)
PRIMARY	None	Outermost SELECT
UNION	None	Second or later SELECT statement in a UNION
DEPENDENT UNION	dependent (true)	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	union_result	Result of a UNION.
SUBQUERY	None	First SELECT in subquery
DEPENDENT SUBQUERY	dependent (true)	First SELECT in subquery, dependent on outer query
DERIVED	None	Derived table
DEPENDENT DERIVED	dependent (true)	Derived table dependent on another table
MATERIALIZED	materialized_from_subquery	Materialized subquery
UNCACHEABLE SUBQUERY	cacheable (false)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	cacheable (false)	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

--simple:简单的查询，不包含子查询和union
explain select * from emp;

--primary:查询中最外层的查询，如果查询中有子查询，则最外层的查询被标记为primary
explain select * from emp where ename not in (select ename from emp where ename like '%S%') ;

--union:若第二个select出现在union之后，则被标记为union
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

--union result:表示一个union的结果集作为一个单独的表返回，这通常发生在union操作之后，并且可能跟其他表进行join操作
explain select * from emp where deptno = 10 union select * from emp where sal >2000;

--subquery:在查询中作为另一个查询的子查询的查询，例如，在 `SELECT ... WHERE column IN (SELECT ...)` 结构中的子查询。
explain select * from emp where sal > (select avg(sal) from emp) ;

--dependent subquery:与subquery类似，但是这个查询依赖于外部查询的某些部分。
explain select e.empno,e.ename,e.sal from emp e where e.sal < (select e2.sal from emp e2 where e2.empno = e.mgr)

--DERIVED: 出现在from子句中的子查询，MySQL会为这个子查询生成一个临时表。这个值表示该查询是为派生表生成的。
explain select t.job from (select min(sal) min_sal,job from emp group by job) t where t.min_sal > 2500 ;
--dependent derived：与derived类似，但是这个查询依赖于外部查询的某些部分：未找到案例

--materialized：表示该子查询的结果被物化（即存储在临时表中），以供稍后的join使用，这种类型的子查询在执行时比常规子查询要慢，
EXPLAIN 
select * from emp where deptno in (select deptno from (select min(sal) min_sal,deptno from emp group by deptno) a where min_sal < '2000') ;

--UNCACHEABLE SUBQUERY：一个子查询的结果不能被缓存，因此每次都会重新计算：未找到案例
--uncacheable union:一个union的结果不能被缓存，因此每次都会重新计算：未找到案例

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集
1、如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名

​ 2、表名是derivedN的形式，表示使用了id为N的查询产生的衍生表

​ 3、当有union result的时候，表名是union n1,n2等的形式，n1,n2表示参与union的id

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

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

一般情况下，得保证查询至少达到range级别，最好能达到ref

--all:全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。
explain select * from emp;

--index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序
explain  select empno from emp;

--range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符： =, <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 
explain select * from emp where empno between 7000 and 7500;

--index_subquery：跟unique_subquery类型，使用的是辅助索引
SET optimizer_switch='materialization=off';
EXPLAIN select * from emp where ename not in (select dname from dept where dname like '%SALES' );
SET optimizer_switch='materialization=on';


--unique_subquery:子查询的结果由聚簇索引或者唯一索引覆盖
--dept表的deptno字段有主键
SET optimizer_switch='materialization=off';
EXPLAIN select * from emp where deptno not in (select deptno from dept where deptno >20 );
SET optimizer_switch='materialization=on';
 
--index_merge：索引合并，在where条件中使用不同的索引字段
--ename，deptno都创建索引
explain select * from emp where ename='SMITH' or deptno = 10;

--ref_or_null：跟ref类似，在ref的查询基础上，加一个null值的条件查询
explain select * from emp  where ename = 'SMITH' or ename is null;

--ref：使用了非聚集索引进行数据的查找
alter table emp add index idx_name(ename);
explain select * from emp  where ename = 'SMITH';

--eq_ref ：使用唯一性索引进行数据查找
explain select * from emp e,emp e2 where e.empno = e2.empno;

--const：这个表至多有一个匹配行，
explain select * from emp where empno = 7369;
 
--system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现

possible_keys

​ 显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

explain select * from emp where ename = 'SIMTH' and deptno = 10;

key

​ 实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp where ename = 'SIMTH' and deptno = 10;

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

explain select * from emp where ename = 'SIMTH' and deptno = 10;

ref

显示了那些列或常量被用于查找索引列，这对于非唯一索引查找有效

explain select * from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出找出所需记录需要读取的行数，此参数很重要，直接反应的sql找了多少数据，在完成目的的情况下越少越好

explain select * from emp;

filtered

表示返回行的预估百分比，它显示了哪些行被过滤掉了，最大的值为100，这意味这没有对行进行筛选，从100开始递减的值表示过滤量在增加，rows表示预估的行数，rows*filtered表示与下表连接的行数

extra

提供查询的额外信息

--using filesort:说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
explain select * from emp order by sal;

--using temporary:建立临时表来保存中间结果，查询完成之后把临时表删除
explain select ename,count(*) from emp where deptno = 10 group by ename;

--using index:这个表示当前的查询时覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找
explain select deptno,count(*) from emp group by deptno limit 10;

--using where:通常是进行全表或者全索引扫描后再用where子句完成结果过滤，需要添加索引
explain select * from emp where job='SMITH';

--using join buffer:使用连接缓存
explain select * from t3 join t2 on t3.c1 = t2.c1;

--impossible where：where语句的结果总是false
explain select * from emp where 1=0

什么是事物

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事物的四大特性（ACID）

原子性（Atomicity）

也就是我们刚才说的不可再分，也就意味着我们对数据库的一系列的操作，要么都是成功，要么都是失败，不可能出现部分成功或者部分失败的情况，以刚才提到的转账的场景为例，一个账户的余额减少，对应一个账户的增加，这两个一定是同时成功或者同时失败的。全部成功比较简单，问题是如果前面一个操作已经成功了，后面的操作失败了，怎么让它全部失败呢？这个时候我们必须要回滚。

原子性，在 InnoDB 里面是通过 undo log 来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用 undo log 来实现回滚操作。

一致性（consistent）

指的是数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。比如主键必须是唯一的，字段长度符合要求。

​除了数据库自身的完整性约束，还有一个是用户自定义的完整性。

举例：

1. 比如说转账的这个场景，A 账户余额减少 1000，B 账户余额只增加了 500，这个时候因为两个操作都成功了，按照我们对原子性的定义，它是满足原子性的， 但是它没有满足一致性，因为它导致了会计科目的不平衡。

2. 还有一种情况，A 账户余额为 0，如果这个时候转账成功了，A 账户的余额会变成-1000，虽然它满足了原子性的，但是我们知道，借记卡的余额是不能够小于 0 的，所以也违反了一致性。用户自定义的完整性通常要在代码中控制。

隔离性（isolation）

有了事务的定义以后，在数据库里面会有很多的事务同时去操作我们的同一张表或者同一行数据，必然会产生一些并发或者干扰的操作，对隔离性就是这些很多个的事务，对表或者 行的并发操作，应该是透明的，互相不干扰的。通过这种方式，我们最终也是保证业务数据的一致性。

​隔离性是通过MVCC以及锁来进行实现的，后续会对MVCC做详细的讲解

持久性（Durable）

我们对数据库的任意的操作，增删改，只要事务提交成功，那么结果就是永久性的，不可能因为我们重启了数据库的服务器，它又恢复到原来的状态了。

持久性怎么实现呢？数据库崩溃恢复（crash-safe）是通过什么实现的？持久性是通过 redo log 来实现的，我们操作数据的时候，会先写到内存的 buffer pool 里面，同时记录 redo log，如果在刷盘之前出现异常，在重启后就可以读取 redo log的内容，写入到磁盘，保证数据的持久性。

​总结：原子性，隔离性，持久性，最后都是为了实现一致性

扩展

顺序读写和随机读写

1. 机械硬盘
   在顺序读写场景下有相当出色的性能表现，但一遇到随机读写性能则直线下降。
   顺序读是随机读性能的400倍以上。顺序读能达到84MB/S
   顺序写是随机读性能的100倍以上。顺序写性能能达到79M/S
2. 固态硬盘
   顺序读：220.7MB/s。随机读：24.654MB/s。
   顺序写：77.2MB/s。随机写：68.910MB/s。

总结：对于固态硬盘，顺序读的速度仍然能达到随机读的10倍左右。但是随机写还是顺序写，差别不大。

MySQL的日志

中继日志

假设做主从复制，master 每执行一条SQL记录在binlog当中，从节点如果同步master的数据，直接从binlog读取的话，master写到binlog是顺序读写的，但是你slave直接执行binlog可是随机读写的，顺序读写比随机读写效率快很多，会存在效率变慢，效率慢就有可能存在SQL堆积执行不完的情况。所以需要relaylog记录binlog中新增的SQL，这个过程很快是顺序执行的。
在这个过程中存在组提交的概念，就是为了优化效率，没必要一条条写到binlog，可以进行分组提交，通过对事务进行分组，优化减少了生成二进制日志所需的操作数，而slave读取relaylog也同样，执行并不是单线程执行，可以进行MTS（enhanced multi-threaded slave）操作

前滚日志

假设做内存同步到磁盘操作，内存通过flush刷写到磁盘效率是随机读写，效率很慢的，假设这个过程中服务挂了，不就数据丢失了么，这时做了个一个操作，就是在写之前记录到log buffer日志里，然后再把log buffer顺序写到 磁盘里的redolog日志里，这个过程是会很快的，让磁盘读取redolog日志尽量保证数据不丢失

undolog和binlog哪个先执行呢

此时发现两种方法都不行啊，该怎么办呢，引入两阶段提交概念

MVCC

​ MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

快照读（提高数据库的并发查询能力）

像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

当前读、快照读、MVCC关系

MVCC多版本并发控制指的是维持一个数据的多个版本，使得读写操作没有冲突，快照读是MySQL为实现MVCC的一个非阻塞读功能。MVCC模块在MySQL中的具体实现是由三个隐式字段，undo日志、read view三个组件来实现的。

解决的问题

数据库并发场景有三种，分别为：

1. 读读：不存在任何问题，也不需要并发控制
2. 读写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读、幻读、不可重复读
3. 写写：有线程安全问题，可能存在更新丢失问题

MVCC是一种用来解决读写冲突的无锁并发控制，也就是为事务分配单项增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照，所以MVCC可以为数据库解决一下问题：

1. 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能

2. 解决脏读、幻读、不可重复读等事务隔离问题，但是不能解决更新丢失问题

实现原理

MVCC的实现原理主要依赖于记录中的三个隐藏字段，undolog，read view来实现的。

1. 隐藏字段
   每行记录除了我们自定义的字段外，还有数据库隐式定义的DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID等字段

   • DB_TRX_ID
     6字节，最近修改事务id，记录创建这条记录或者最后一次修改该记录的事务id
   • DB_ROLL_PTR
     7字节，回滚指针，指向这条记录的上一个版本,用于配合undolog，指向上一个旧版本
   • DB_ROW_JD
     6字节，隐藏的主键，如果数据表没有主键，那么innodb会自动生成一个6字节的row_id

   记录如图所示：
   
   在上图中，DB_ROW_ID是数据库默认为该行记录生成的唯一隐式主键，DB_TRX_ID是当前操作该记录的事务ID，DB_ROLL_PTR是一个回滚指针，用于配合undo日志，指向上一个旧版本

2. undo log
   undolog被称之为回滚日志，表示在进行insert，delete，update操作的时候产生的方便回滚的日志

   当进行insert操作的时候，产生的undolog只在事务回滚的时候需要，并且在事务提交之后可以被立刻丢弃

   当进行update和delete操作的时候，产生的undolog不仅仅在事务回滚的时候需要，在快照读的时候也需要，所以不能随便删除，只有在快照读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除（当数据发生更新和删除操作的时候都只是设置一下老记录的deleted_bit，并不是真正的将过时的记录删除，因为为了节省磁盘空间，innodb有专门的purge线程来清除deleted_bit为true的记录，如果某个记录的deleted_id为true，并且DB_TRX_ID相对于purge线程的read view 可见，那么这条记录一定时可以被清除的）

   下面我们来看一下undolog生成的记录链
   1、假设有一个事务编号为1的事务向表中插入一条记录，那么此时行数据的状态为：
   
   2、假设有第二个事务编号为2对该记录的name做出修改，改为lisi
   在事务2修改该行记录数据时，数据库会对该行加排他锁
   然后把该行数据拷贝到undolog中，作为 旧记录，即在undolog中有当前行的拷贝副本
   拷贝完毕后，修改该行name为lisi，并且修改隐藏字段的事务id为当前事务2的id，回滚指针指向拷贝到undolog的副本记录中
   事务提交后，释放锁

   
   3、假设有第三个事务编号为3对该记录的age做了修改，改为32
   在事务3修改该行数据的时，数据库会对该行加排他锁

   然后把该行数据拷贝到undolog中，作为旧纪录，发现该行记录已经有undolog了，那么最新的旧数据作为链表的表头，插在该行记录的undolog最前面

   修改该行age为32岁，并且修改隐藏字段的事务id为当前事务3的id，回滚指针指向刚刚拷贝的undolog的副本记录

   事务提交，释放锁

   

   从上述的一系列图中，大家可以发现，不同事务或者相同事务的对同一记录的修改，会导致该记录的undolog生成一条记录版本线性表，即链表，undolog的链首就是最新的旧记录，链尾就是最早的旧记录。

   undolog中的数据不会无限增加，后台会有专门的线程用来进行undolog的清除工作，叫做purge

   当进行insert操作的时候，事务提交之后对应的undolog就会被清除掉

   当进行update或者delete操作的时候，事务提交不一定会清除，因为update，delete操作进行过程中可能会有其他的并发事务存在，有可能需要进行历史数据版本的读取，所以之后在事务提交或者不需要读取历史记录的时候才会删除

3. Read View
   上面的流程如果看明白了，那么大家需要再深入理解下read view的概念了。

   Read View是事务进行快照读操作的时候生产的读视图，在该事务执行快照读的那一刻，会生成一个数据系统当前的快照，记录并维护系统当前活跃事务的id，事务的id值是递增的。

   其实Read View的最大作用是用来做可见性判断的，也就是说当某个事务在执行快照读的时候，对该记录创建一个Read View的视图，把它当作条件去判断当前事务能够看到哪个版本的数据，有可能读取到的是最新的数据，也有可能读取的是当前行记录的undolog中某个版本的数据

   Read View遵循的可见性算法主要是将要被修改的数据的最新记录中的DB_TRX_ID（当前事务id）取出来，与系统当前其他活跃事务的id去对比，如果DB_TRX_ID跟Read View的属性做了比较，不符合可见性，那么就通过DB_ROLL_PTR回滚指针去取出undolog中的DB_TRX_ID做比较，即遍历链表中的DB_TRX_ID，直到找到满足条件的DB_TRX_ID,这个DB_TRX_ID所在的旧记录就是当前事务能看到的最新老版本数据。

   Read View的可见性规则如下所示：

   首先要知道Read View中的三个全局属性：

   • trx_list: 一个数值列表，用来维护Read View生成时刻系统正活跃的事务ID（1,2,3）
   • up_limit_id: 记录trx_list列表中事务ID最小的ID（1）
   • low_limit_id: Read View生成时刻系统尚未分配的下一个事务ID，（4）

   具体的比较规则如下：

   1. 首先比较DB_TRX_ID < up_limit_id,如果小于，则当前事务能看到DB_TRX_ID所在的记录，如果大于等于进入下一个判断
   2. 接下来判断DB_TRX_ID >= low_limit_id,如果大于等于则代表DB_TRX_ID所在的记录在Read View生成后才出现的，那么对于当前事务肯定不可见，如果小于，则进入下一步判断
   3. 判断DB_TRX_ID是否在活跃事务中，如果在，则代表在Read View生成时刻，这个事务还是活跃状态，还没有commit，修改的数据，当前事务也是看不到，如果不在，则说明这个事务在Read View生成之前就已经开始commit，那么修改的结果是能够看见的。

整体处理流程

假设有四个事务同时在执行，如下图所示：

事务1	事务2	事务3	事务4
事务开始	事务开始	事务开始	事务开始
…	…	…	修改且已提交
进行中	快照读	进行中
…	…	…

从上述表格中，我们可以看到，当事务2对某行数据执行了快照读，数据库为该行数据生成一个Read View视图，可以看到事务1和事务3还在活跃状态，事务4在事务2快照读的前一刻提交了更新，所以，在Read View中记录了系统当前活跃事务1，3，维护在一个列表中。同时可以看到up_limit_id的值为1，而low_limit_id为5，如下图所示：

在上述的例子中，只有事务4修改过该行记录，并在事务2进行快照读前，就提交了事务，所以该行当前数据的undolog如下所示：

当事务2在快照读该行记录的是，会拿着该行记录的DB_TRX_ID去跟up_limit_id,lower_limit_id和活跃事务列表进行比较，判读事务2能看到该行记录的版本是哪个。

​ 具体流程如下：先拿该行记录的事务ID（4）去跟Read View中的up_limit_id相比较，判断是否小于，通过对比发现不小于，所以不符合条件，继续判断4是否大于等于low_limit_id,通过比较发现也不大于，所以不符合条件，判断事务4是否处理trx_list列表中，发现不再次列表中，那么符合可见性条件，所以事务4修改后提交的最新结果对事务2 的快照是可见的，因此，事务2读取到的最新数据记录是事务4所提交的版本，而事务4提交的版本也是全局角度的最新版本。如下图所示：

当上述的内容都看明白了的话，那么大家就应该能够搞清楚这几个核心概念之间的关系了，下面我们讲一个不同的隔离级别下的快照读的不同。

所以接下来再分析两个案例

RC、RR级别下的InnoDB快照读有什么不同

因为Read View生成时机的不同，从而造成RC、RR级别下快照读的结果的不同

1. 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照即Read View,将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View,所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View,所以对之后的修改不可见

2. 在RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动和事务的快照，这些事务的修改对于当前事务都是不可见的，而早于Read View创建的事务所做的修改均是可见

3. 在RC级别下，事务中，每次快照读都会新生成一个快照和Read View,这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因。

总结：在RC隔离级别下，是每个快照读都会生成并获取最新的Read View,而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View，之后的快照读获取的都是同一个Read View.

事务并发会带来什么问题？

当很多事务并发的去操作数据库的表或者行的时候，会出现脏读，不可重复读，幻读等问题。

1. 脏读
   当一个事务正在访问数据并且对数据进行了修改，但这种修改还没有提交到数据库中时，另一个事务也访问了这个数据并使用了它。这种情况下，第二个事务读取的数据是第一个事务尚未提交的修改，因此可能是不准确或不一致的。脏读通常发生在事务隔离级别较低的情况下，例如读未提交（Read Uncommitted）级别。

2. 不可重复读
   在一个事务内，多次读取同一数据。在这个事务还没有结束时，另一个事务也访问了该同一数据并对其进行了修改。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的。这种情况通常发生在读已提交（Read Committed）或可重复读（Repeatable Read）的事务隔离级别下。

3. 幻读
   当事务不是独立执行时发生的一种现象，通常发生在可重复读（Repeatable Read）的事务隔离级别下。例如，第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。在第二个事务中，由于第一个事务的修改，导致第二个事务在读取数据时出现了额外的行或缺失的行，就像产生了幻觉一样。

   产生幻读的根本原因：
   如果在同一个事务中如果同时出现了快照读和当前读，那么就会产生幻读问题，如果只有快照读，那么是不会出现幻读问题的。

   CREATE TABLE `user` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(255) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB ;
   
   INSERT into user VALUES (1,'1',20),(5,'5',20),(15,'15',30),(20,'20',30);
   

   假设有如下业务场景：

   时间	事务1	事务2
   	begin；
   T1	select * from user where age = 20;2个结果
   T2		insert into user values(25,‘25’,20);commit;
   T3	select * from user where age =20;2个结果
   T4	update user set name=‘00’ where age =20;此时看到影响的行数为3
   T5	select * from user where age =20;三个结果

   执行流程如下：

   1. T1时刻读取年龄为20 的数据，事务1拿到了2条记录
   2. T2时刻另一个事务插入一条新的记录，年龄也是20
   3. T3时刻，事务1再次读取年龄为20的数据，发现还是2条记录，事务2插入的数据并没有影响到事务1的事务读取
   4. T4时刻，事务1修改年龄为20的数据，发现结果变成了三条，修改了三条数据
   5. T5时刻，事务1再次读取年龄为20的数据，发现结果有三条，第三条数据就是事务2插入的数据，此时就产生了幻读情况

   此时大家需要思考一个问题，在当下场景里，为什么没有解决幻读问题？

   其实通过前面的分析，大家应该知道了快照读和当前读，一般情况下select * from …where …是快照读，不会加锁，而 for update,lock in share mode,update,delete都属于当前读，如果事务中都是用快照读，那么不会产生幻读的问题，但是快照读和当前读一起使用的时候就会产生幻读。

   如果都是当前读的话，如何解决幻读问题呢？

   truncate table user;
   INSERT into user VALUES (1,'1',20),(5,'5',20),(15,'15',30),(20,'20',30);
   

   时间	事务1	事务2
   	begin;
   T1	select * from user where age =20 for update;
   T2		insert into user values(25,‘25’,20);此时会阻塞等待锁
   T3	select * from user where age =20 for update;

   此时，可以看到事务2被阻塞了，需要等待事务1提交事务之后才能完成，其实本质上来说采用的是间隙锁的机制解决幻读问题。

什么是隔离级别？有哪些隔离级别？

隔离级别是对事务并发控制的等级，描述了一个事务必须与由其他事务进行的资源或数据更改相隔离的程度。数据库的事务隔离级别有四种，分别是读未提交、读已提交、可重复读、序列化，不同的隔离级别下会产生脏读、幻读、不可重复读等相关问题，因此在选择隔离级别的时候要根据应用场景来决定，使用合适的隔离级别。

​各种隔离级别和数据库异常情况对应情况如下：

隔离级别	脏读	不可重复 读	幻读
READ- UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE- READ	×	×	√
SERIALIZABLE	×	×	×

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 事务的修改，即使没有提交，对其他事务也都是可见的。事务能够读取未提交的数据，这种情况称为脏读。
• READ-COMMITTED(读取已提交)： 事务读取已提交的数据，大多数数据库的默认隔离级别。当一个事务在执行过程中，数据被另外一个事务修改，造成本次事务前后读取的信息不一样，这种情况称为不可重复读。
• REPEATABLE-READ(可重复读)： 这个级别是MySQL的默认隔离级别，它解决了脏读的问题，同时也保证了同一个事务多次读取同样的记录是一致的，但这个级别还是会出现幻读的情况。幻读是指当一个事务A读取某一个范围的数据时，另一个事务B在这个范围插入行，A事务再次读取这个范围的数据时，会产生幻读
• SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读取提交内容):，但是你要知道的是InnoDB 存储引擎默认使用 **REPEATABLE-READ（可重读）**并不会有任何性能损失。

如何解决数据的读一致性问题

1. LBCC
   第一种，既然要保证前后两次读取数据一致，那么读取数据的时候，锁定我要操作的数据，不允许其他的事务修改就行了。这种方案叫做基于锁的并发控制 Lock Based Concurrency Control（LBCC）。

   如果仅仅是基于锁来实现事务隔离，一个事务读取的时候不允许其他时候修改，那就意味着不支持并发的读写操作，而我们的大多数应用都是读多写少的，这样会极大地影响操作数据的效率。

2. MVCC

Innodb中包含哪些锁？

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

锁的基本模式——共享锁

第一个行级别的锁就是我们在官网看到的 Shared Locks （共享锁），我们获取了一行数据的读锁以后，可以用来读取数据，所以它也叫做读锁。而且多个事务可以共享一把读锁。那怎么给一行数据加上读锁呢？

我们可以用 select lock in share mode;的方式手工加上一把读锁。

释放锁有两种方式，只要事务结束，锁就会自动事务，包括提交事务和结束事务。

锁的基本模式——排它锁

第二个行级别的锁叫做 Exclusive Locks（排它锁），它是用来操作数据的，所以又叫做写锁。只要一个事务获取了一行数据的排它锁，其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种，第一种是自动加排他锁，可能是同学们没有注意到的：

我们在操作数据的时候，包括增删改，都会默认加上一个排它锁。

还有一种是手工加锁，我们用一个 FOR UPDATE 给一行数据加上一个排它锁，这个无论是在我们的代码里面还是操作数据的工具里面，都比较常用。

释放锁的方式跟前面是一样的。

锁的基本模式——意向锁

意向锁是由数据库自己维护的。

也就是说，当我们给一行数据加上共享锁之前，会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前，会自动在这张表上面加一个意向排他锁。

反过来说：

如果一张表上面至少有一个意向共享锁，说明有其他的事务给其中的某些数据行加上了共享锁。

记录锁

记录锁表示的是给记录行所在的索引上添加的锁。例如select c1 from t where c1 = 10 for update;防止任何其他事务插入、更新或者删除c1=10的行

记录锁总是锁定索引记录，即使表没有定义索引，innodb也会创建一个隐藏的聚簇索引，并使用该索引进行记录锁定

间隙锁

间隙锁的锁定范围是索引记录之间的间隙，或者在第一个索引记录之前或最后一个索引记录之后的间隙，间隙锁是针对事务隔离级别为可重复读或以上级别。

临键锁

临键锁是记录锁和间隙锁的组合，也就是索引记录本身加上之前的间隙，间隙锁保证在RR级别不会出现幻读问题，防止在同一个事务内得到的结果不一致。假设索引包含10,11,13,20这几个值，那么临键锁的范围就是（-∞，10],(10,11],(11,13],(13,20],(20,+∞)。对于最后一个间隔，临键锁锁定索引中最大值以上的间隙，以及值高于索引中任何实际值的supremum(在查看加锁信息的时候可以看到这个标识)。

上面简单介绍了下各种锁的基础概念，实际情况下锁的情况会更加复杂，需要根据不同表的索引情况来判断，可以参考文档《mysql的加锁情况》。

什么是死锁？如何解决死锁的问题？

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

解决死锁的思路一般就是切断环路，尽量避免并发形成环路：

1. 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁的概率
2. 在同一个事务中，尽可能做到一次锁定所有的资源，减少死锁产生的概率
3. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁粒度，通过表锁来减少死锁的概率
4. 死锁和索引密不可分，合理优化索引

分享两个死锁分析很好的文章，大家可以做参考：

https://mp.weixin.qq.com/s?__biz=MzkyMzU5Mzk1NQ==&mid=2247505885&idx=1&sn=7601fcf37cdc4e801d1a098ff2effe6a&source=41#wechat_redirect

https://mp.weixin.qq.com/s?__biz=MzkyMzU5Mzk1NQ==&mid=2247505849&idx=1&sn=1ef7f984aa3729ae56ee107b2922c9e1&source=41#wechat_redirect

mysql的加锁情况

# 查看锁信息需要开启两个变量
show variables like 'innodb_status_output%';
set global innodb_status_output = on;
set global innodb_status_output_locks = on;

1、REPEATABLE-READ隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

create table t(id int default null,name char(20) default null);
insert into t values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;
select * from t for update;
show engine innodb status\G

从返回的信息中，可以看到对表添加了IX锁和4个记录锁，表中的三行记录上分别添加了Next-key Lock锁，防止有数据变化发生幻读，例如进行了更新、删除操作。同时会出现“ 0: len 8; hex 73757072656d756d; asc supremum;;”这样的描述信息，此操作也是为了防止幻读，会将最大索引值之后的间隙锁住并用supremum表示高于表中任何一个索引的值。

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;
select * from t where id = 10 for update;
show engine innodb status\G

从上述反馈信息中，可以发现跟不加where条件的加锁情况是一样的，会同时出现多个行的临键锁和supremum，这到底是为什么呢？

出现supremum的原因是：虽然where的条件是10，但是每次插入记录时所需要生成的聚簇索引Row_id还是自增的，每次都会在表的最后插入，所以就有可能插入id=10这条记录，因此要添加一个supremum防止数据插入。

出现其他行的临键锁的原因是：为了防止幻读，如果不添加Next-Key Lock锁，这时若有其他会话执行DELETE或者UPDATE语句，则都会造成幻读。

2、REPEATABLE-READ隔离级别+表有显式主键无索引

创建如下表并添加数据：

create table t2(id int primary key not null,name char(20) default null);
insert into t2 values(10,'10'),(20,'20'),(30,'30');

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;
select * from t2 for update;
show engine innodb status\G

通过上述信息可以看到，与之前的加锁方式是相同的。

2、where条件是主键字段

begin;
select * from t2 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，只会对表中添加IX锁和对主键添加了记录锁（X locks rec but not gap）,并且只锁住了where条件id=10这条记录，因为主键已经保证了唯一性，所以在插入时就不会是id=10这条记录。

3、where条件包含主键字段和非关键字段

begin;
select * from t2 where id = 10 and name = '10' for update;
show engine innodb status\G

通过看到，加锁方式与where条件是主键字段的加锁方式相同，因为根据主键字段可以直接定位一条记录。

3、REPEATABLE-READ隔离级别+表无显式主键有索引

1、不带where条件，跟之前的情况类似

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

创建如下表：

create table t3(id int default null,name char(20) default null);
create index idx_id on t3(id);
insert into t3 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t3 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了Next-Key Lock锁，区间是负无穷到10，对索引对应的聚集索引添加了X记录锁，为了防止幻读，对索引记录区间（10，20）添加间隙锁。

此时大家可以开启一个新的事务，插入负无穷到id=19的全部记录都会被阻塞，而大于等于20 的值不会被阻塞

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t4(id int default null,name char(20) default null);
create unique index idx_id on t4(id);
insert into t4 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t4 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，添加了id索引的记录锁，以及对应的聚簇索引的记录锁

4、REPEATABLE-READ隔离级别+表有显式主键和索引

此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

create table t5(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t5 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t5 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;
select * from t5 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加临键锁，对主键索引列添加X记录锁，为了防止幻读，对name的（10，20）添加间隙锁

begin;
select * from t5 where name <= '10' for update;
show engine innodb status\G

跟上面对比，发现改成范围查询后，变成了(10,20]范围变成了临键锁，=号是间隙锁
(3)where条件是主键字段

begin;
select * from t5 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;
select * from t5 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。

2、表有显式主键和唯一索引

创建如下表：

create table t6(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t6 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t6 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对supremum添加临键锁，对name索引列添加临键锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;
select * from t6 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;
select * from t6 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;
select * from t6 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。

5、READ-COMMITTED隔离级别+表无显式主键和索引

创建表t,没有索引和主键，并插入测试数据

create table t7(id int default null,name char(20) default null);
insert into t7 values(10,'10'),(20,'20'),(30,'30');

手动开启事务，执行语句并采用for update方式（当前读）

begin;
select * from t7 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

同表下，如果加上where条件之后，是否会产生Next-key Lock呢？执行如下语句：

begin;
select * from t7 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后会对聚集索引添加记录锁，因为RC隔离级别无法解决幻读问题，所以不会添加临键锁。

6、READ-COMMITTED隔离级别+表有显式主键无索引

创建如下表并添加数据：

create table t8(id int primary key not null,name char(20) default null);
insert into t8 values(10,'10'),(20,'20'),(30,'30');

在此情况下要分为三种情况来进行分析，不同情况的加锁方式也不同：

1、不带where条件

begin;
select * from t8 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（主键）

2、where条件是主键字段

begin;
select * from t8 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

3、where条件包含主键字段和非关键字段

begin;
select * from t8 where id = 10 and name = '10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表id=10的积累添加记录锁

7、READ-COMMITTED隔离级别+表无显式主键有索引

创建如下表：

create table t9(id int default null,name char(20) default null);
create index idx_id on t9(id);
insert into t9 values(10,'10'),(20,'20'),(30,'30');

1、不带where条件，跟之前的情况类似

begin;
select * from t9 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对表的三行记录添加记录锁（聚簇索引）

2、where条件是普通索引字段或者（普通索引字段+非索引字段）

执行如下语句：

begin;
select * from t9 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁，

3、where条件是唯一索引字段或者（唯一索引字段+非索引字段）

创建如下表：

create table t10(id int default null,name char(20) default null);
create unique index idx_id on t10(id);
insert into t10 values(10,'10'),(20,'20'),(30,'30');

执行如下语句：

begin;
select * from t10 where id = 10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了IX锁，对id=10的索引添加了行锁，对索引对应的聚集索引添加了行锁。

8、READ-COMMITTED隔离级别+表有显式主键和索引

此情况可以分为以下几种：

1、表有显式主键和普通索引

创建如下表：

create table t11(id int not null,name char(20) default null,primary key(id),key idx_name(name));
insert into t11 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t11 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加记录锁，对主键索引添加X记录锁

(2)where条件是普通索引字段

begin;
select * from t11 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name添加X记录锁，对主键索引列添加X记录锁

(3)where条件是主键字段

begin;
select * from t11 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，对表添加了意向锁，对主键添加了记录锁。

(4)where条件同时包含普通索引字段和主键索引字段

begin;
select * from t11 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是普通索引，那么跟普通字段是类似的，其实本质点就在于加锁的字段不同而已。

2、表有显式主键和唯一索引

创建如下表：

create table t12(id int not null,name char(20) default null,primary key(id),unique key idx_name(name));
insert into t12 values(10,'10'),(20,'20'),(30,'30');

(1)不带where条件

begin;
select * from t12 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name索引列添加X记录锁，对主键索引添加X记录锁

(2)where条件是唯一索引字段

begin;
select * from t12 where name='10' for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后对name和主键添加行锁

(3)where条件是主键字段

begin;
select * from t12 where id=10 for update;
show engine innodb status\G

通过上述信息可以看到，首先对表添加IX锁，然后主键添加行锁

(4)where条件是唯一索引字段和主键字段

begin;
select * from t12 where id=10 and name='10' for update;
show engine innodb status\G

此处大家需要注意，如果在执行过程中使用的是主键索引，那么跟使用主键字段是一致的，如果使用的是唯一索引，那么跟唯一索引字段是一样的，其实本质点就在于加锁的字段不同而已。

架构

谈一下你对于MYSQL整体架构的理解

一般情况下，我们在进行MYSQL整体架构描述的时候分为三层，分别是客户端，服务端，存储引擎，如下图所示：

客户端

客户端主要用于向MYSQL的服务端发送SQL语句，我们使用的cli，jdbc，可视化工具都可以称之为客户端

服务端

MYSQL的服务端主要是对外提供MYSQL的服务，主要包含四个组件：连接器，分析器，优化器，执行器

1. 连接器
   在MYSQL中，支持多种通信协议，主要有以下分类：
   （1）TCP/IP协议，任何编程语言在进行数据库连接的时候基本都是通过TCP协议连接到MYSQL服务器。
   （2）Unix Socket协议，在Linux服务器上，进行数据库连接的时候需要理解一个物理文件，mysql.sock

   在MYSQL中，主要使用半双工的通信方式，半双工意味着要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，这两个动作不能同时发生，在进行数据传输的过程中，数据不能分成小块发送，只能一次性发送，如果发送给服务器的数据包过大，我们需要调整MYSQL Server的max_allowed_packet参数，默认值为4M

   在MYSQL中，既支持短连接，也支持长连接，短连接就是每次操作完成之后都会进行关闭，长连接可以保持打开，方便后续的程序进行使用，对于长时间不用的连接，MYSQL服务器会自动断开：wait_timeout和interactive_timeout都用来控制连接在空闲状态下被服务器关闭之前等待的时间，wait_timeout表示非交互式连接，比如JDBC程序，interactive_timeout表示交互式连接，比如mysql的命令行。

   在mysql中可以通过show processlist命令来查看mysql的连接，mysql的连接状态如下所示：

状态	含义
Sleep	线程正在等待客户端，以向它发送一个新语句
Query	线程正查询或往客户端发送数据
Lcoked	该查询被其他查询锁定
Coping to tmp table	临时结果集大于tmp_talbe_size。线程把临时表从存储器内部格式改变为磁盘模式，以节约存储器
Sending data	线程正在为SElECT 语句处理行，同时正在向客户端发送数据
Sorting for group	线程正在进行分类，以满足GROUP BY 要求
Sorting for order	线程正在分类，以满足ORDER BY 要求

2. 分析器
   分析器主要用来进行词法分析和语法分析操作，将一条SQL语句转化为MYSQL可以执行的抽象语法树

   词法分析是指将输入的SQL字符串分割成一系列的词法单元，称之为token

   语法分析是指将词法分析产生的词法单元进行一些语法检查，并转换成一棵抽象语法树。

   SELECT id, name FROM t_user WHERE status = 'ACTIVE' AND age >= 18
   

   

3. 优化器
   优化器主要用于根据抽象语法树生成不同的执行计划，然后选择一种最优的执行计划进行执行，现在的MYSQL选择的是基于成本的优化，哪种执行计划开销最小，就用哪种。

4. 执行器
   执行器主要用于根据优化器提供的执行计划来操作数据，并返回结果给客户端。在此过程中，执行器会调用执行引擎的API来执行数据操作并且验证用户是否具有权限执行该SQL语句，在执行完成之后将查询到的结果返回给客户端。

存储引擎

在MYSQL中，我们看到的数据是以表的方式进行展示，但是实际在进行存储的时候以文件的方式进行存储，不同类型的表在磁盘中会有不同的组织和存储形式。

不同的数据文件在磁盘的不同组织形式。

​通过执行show engines可以查看MYSQL中支持的存储引擎：

1. MyISAM（3个文件）
   These tables have a small footprint. Table-level locking limits the performance in read/write workloads, so it is often used in read-only or read-mostly workloads in Web and data warehousing configurations.

   应用范围比较小。表级锁定限制了读/写的性能，因此在 Web 和数据仓库配置中，它通常用于只读或以读为主的工作。

   特点：

   支持表级别的锁（插入和更新会锁表）。不支持事务。
   拥有较高的插入（insert）和查询（select）速度。
   存储了表的行数（count 速度更快）。
   适合：只读之类的数据分析的项目。

2. InnoDB（2个文件）
   The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

   mysql 5.7 中的默认存储引擎。InnoDB 是一个事务安全（与 ACID 兼容）的 MySQL存储引擎，它 具有提交、回滚和崩溃恢复功能来保护用户数据。InnoDB 行级锁（不升级为更粗粒度的锁）和Oracle 风格的一致非锁读提高了多用户并发性和性能。InnoDB 将用户数据存储在聚集索引中，以减少基于主键的常见查询的 I/O。为了保持数据完整性，InnoDB 还支持外键引用完整性约束。

   特点：

   支持事务，支持外键，因此数据的完整性、一致性更高。
   支持行级别的锁和表级别的锁。
   支持读写并发，写不阻塞读。
   特殊的索引存放方式，可以减少 IO，提升查询效率。
   适合：经常更新的表，存在并发读写或者有事务处理的业务系统。

3. Memory（1个文件）
   Stores all data in RAM, for fast access in environments that require quick lookups of non-critical data. This engine was formerly known as the HEAP engine. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.

   将所有数据存储在 RAM 中，以便在需要快速查找非关键数据的环境中快速访问。这个引擎以前被称为堆引擎。其使用案例正在减少；InnoDB 及其缓冲池内存区域提供了一种通用、持久的方法来 将大部分或所有数据保存在内存中，而 ndbcluster 为大型分布式数据集提供了快速的键值查找。

   特点：

   把数据放在内存里面，读写的速度很快，但是数据库重启或者崩溃，数据会全部消失。只适合做临时表。默认使用哈希索引，将表中的数据存储到内存中。

4. CSV（3个文件）
   Its tables are really text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

   它的表实际上是带有逗号分隔值的文本文件。csv 表允许以 csv 格式导入或转储数据，以便与读写相同格式的脚本和应用程序交换数据。因为 csv 表没有索引，所以通常在正常操作期间将数据保存在 innodb表中，并且只在导入或导出阶段使用 csv 表。

   特点：

   不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之间导入导出。

5. Archive（2个文件）
   These compact, unindexed tables are intended for storing and retrieving large amounts of seldom-referenced historical, archived, or security audit information.

   这些紧凑的未索引表用于存储和检索大量很少引用的历史、存档或安全审计信息。

   特点：

   不支持索引，不支持 update delete。

聊一下innodb存储引擎的整体架构图

在mysql的早期版本中，默认的存储引擎是Myisam，后来由Innobase Oy公司开发出innodb，作为插件引擎集成在mysql中，因其出色的性能在mysql5.5版本之后开始作为默认的存储引擎。Innodb是第一个完整支持ACID事务的mysql存储引擎，特点是行锁设计，支持MVCC，支持外键，提供一致性非锁定读，非常适合OLTP场景。

innodb存储引擎架构包含内存结构和磁盘结构两大部分，整体架构图如下：

In-Memory Structure

Buffer Pool

buffer pool是Innodb在访问表和索引数据时缓存的主要内存区域。缓冲池允许直接从内存访问频繁使用的数据，这种方式加快了数据的处理速度，在某些专用的服务器上，通常会将80%的物理内存分配给buffer pool。

为了提高大容量读取的效率，缓冲池被划分为可能包含多行的页面，为了提高缓存管理的效率，缓冲池被实现问我页面链表，很少使用的数据使用最近最少算法（LRU）从缓存中淘汰。

数据库中的缓冲池是通过 LRU（Latest Recent Used，最近最少使用）算法来进行管理的。即最频繁使用的页在 LRU 列表的前端，而最少使用的页在 LRU 列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放 LRU 列表中尾端的页。

在 InnoDB 存储引擎中，缓冲池中页的大小默认为 16KB，同样使用 LRU 算法对缓冲池进行管理。稍有不同的是 InnoDB 存储引擎对传统的 LRU 算法做了一些优化。在 InnoDB 的存储引擎中，LRU 列表中还加入了 midpoint 位置。新读取到的页，虽然是最新访问的页，但并不是直接放入到 LRU 列表的首部，而是放入到 LRU 列表的 midpoint 位置。这个算法在 InnoDB 存储引擎下称为 midpoint insertion strategy。在默认配置下，该位置在 LRU 列表长度的 5/8 处。

在innodb存储引擎中，把midpoint之后的列表称之为旧列表，之前的列表称之为新列表，每一个子列表都有头部和尾部，可以把新列表里的数据理解为活跃的热点数据。

默认情况下

1. Old 链表占整个LRU 链表的比例是3/8。该比例由innodb_old_blocks_pct控制，默认值是37（3/8*100）。该值取值范围为5~95，为全局动态变量。
2. 当新的页被读取到Buffer Pool里面的时候，和传统的LRU算法插入到LRU链表头部不同，Innodb LRU算法是将新的页面插入到Yong 链表的尾部和Old 链表的头部中间的位置，这个位置叫做Mid Point，如上图所示。
3. 频繁访问一个Buffer Pool的页面，会促使页面往Young链表的头部移动。如果一个Page在被读到Buffer Pool后很快就被访问，那么该Page会往Young List的头部移动，但是如果一个页面是通过预读的方式读到Buffer Pool，且之后短时间内没有被访问，那么很可能在下次访问之前就被移动到Old List的尾部，而被驱逐了。
4. 随着数据库的持续运行，新的页面被不断的插入到LRU链表的Mid Point，Old 链表里的页面会逐渐的被移动Old链表的尾部。同时，当经常被访问的页面移动到LRU链表头部的时候，那些没有被访问的页面会逐渐的被移动到链表的尾部。最终，位于Old 链表尾部的页面将被驱逐。

如果一个数据页已经处于Young 链表，当它再次被访问的时候，只有当其处于Young 链表长度的1/4(大约值)之后，才会被移动到Young 链表的头部。这样做的目的是减少对LRU 链表的修改，因为LRU 链表的目标是保证经常被访问的数据页不会被驱逐出去。

innodb_old_blocks_time 控制的Old 链表头部页面的转移策略。该Page需要在Old 链表停留超过innodb_old_blocks_time 时间，之后再次被访问，才会移动到Young 链表。这么操作是避免Young 链表被那些只在innodb_old_blocks_time时间间隔内频繁访问，之后就不被访问的页面塞满，从而有效的保护Young 链表。

在全表扫描或者全索引扫描的时候，Innodb会将大量的页面写入LRU 链表的Mid Point位置，并且只在短时间内访问几次之后就不再访问了。设置innodb_old_blocks_time的时间窗口可以有效的保护Young List，保证了真正的频繁访问的页面不被驱逐。

innodb_old_blocks_time 单位是毫秒，默认值是1000。调大该值提高了从Old链表移动到Young链表的难度，会促使更多页面被移动到Old 链表，老化，从而被驱逐。

当扫描的表很大，Buffer Pool都放不下时，可以将innodb_old_blocks_pct设置为较小的值，这样只读取一次的数据页就不会占据大部分的Buffer Pool。例如，设置innodb_old_blocks_pct = 5，会将仅读取一次的数据页在Buffer Pool的占用限制为5％。

当经常扫描一些小表时，这些页面在Buffer Pool移动的开销较小，我们可以适当的调大innodb_old_blocks_pct，例如设置innodb_old_blocks_pct = 50。

在SHOW ENGINE INNODB STATUS 里面提供了Buffer Pool一些监控指标，有几个我们需要关注一下：

1. youngs/s：该指标表示的是每秒访问Old 链表中页面，使其移动到Young链表的次数。如果MySQL实例都是一些小事务，没有大表全扫描，且该指标很小，就需要调大innodb_old_blocks_pct 或者减小innodb_old_blocks_time，这样会使得Old List 的长度更长，Old页面被移动到Old List 的尾部消耗的时间会更久，那么就提升了下一次访问到Old List里面的页面的可能性。如果该指标很大，可以调小innodb_old_blocks_pct，同时调大innodb_old_blocks_time，保护热数据。
2. non-youngs/s：该指标表示的是每秒访问Old 链表中页面，没有移动到Young链表的次数，因为其不符合innodb_old_blocks_time。如果该指标很大，一般情况下是MySQL存在大量的全表扫描。如果MySQL存在大量全表扫描，且这个指标又不大的时候，需要调大innodb_old_blocks_time，因为这个指标不大意味着全表扫描的页面被移动到Young 链表了，调大innodb_old_blocks_time时间会使得这些短时间频繁访问的页面保留在Old 链表里面。

每隔1秒钟，Page Cleaner线程执行LRU List Flush的操作，来释放足够的Free Page。innodb_lru_scan_depth 变量控制每个Buffer Pool实例每次扫描LRU List的长度，来寻找对应的脏页，执行Flush操作。

Change Buffer

在 MySQL5.5 之前，叫插入缓冲（Insert Buffer），只针对 INSERT 做了优化；现在对 DELETE 和 UPDATE 也有效，叫做写缓冲（Change Buffer）。它是一种应用在非唯一普通索引页（non-unique secondary index page）不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更（Buffer Changes），等未来数据被读取时，再将数据合并（Merge）恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘 IO，提升数据库性能。

数据的修改分为两个情况：

1. 当修改的数据页在缓冲池时

通过 LRU、Flush List 的管理，数据库不是直接写入磁盘中，是先将 redo log 写入到磁盘，再通过 checkpoint 机制，将这些 “脏数据页” 同步地写入磁盘，等于是将这期间发生的 n 次的落盘合并成了一次落盘。因为有 redo log 是落盘的，所以即使数据库崩溃，缓存中的数据页全部丢失，也可以通过 redo log 将这些数据页找回来。

redo log 是数据库用来在崩溃的时候进行数据恢复的日志，redo log 的写入策略可以通过参数控制，并不一定是每一次写操作之后立即落盘 redo log，在部分参数下，redo log 可能是每秒集中写入一次，也有可能采取其他落盘策略，但是无论采用什么方式，redo log 的量都是不会减少的，与数据写入的覆盖性不同，后一条 redo log 是不会覆盖前一条的，而是增量形式的，因此写 redo log 的操作，等同于是对磁盘某一小块区域的顺序 I/O，而不像数据落盘一样的随机 IO 在磁盘里写入，需要磁盘在多个地方移动磁头。所以 redo log 的落盘是 IO 操作当中消耗较少的一种，比数据直接刷回磁盘要优很多。

2. 当修改的数据页不在缓冲池时，不用写缓冲至少需要下面的三步：

• 先把需要的索引页，从磁盘加载到缓冲池，一次磁盘随机读操作；
• 修改缓冲池中的页，一次内存操作；
• 写入 redo log ，一次磁盘顺序写操作；

在没有命中缓冲池的时候，至少多产生一次磁盘 IO，对于写多读少的业务场景，性能损耗是很高的

加入写缓冲优化后，流程优化为：

• 在写缓冲中记录这个操作，一次内存操作；
• 写入 redo log，一次磁盘顺序写操作；

其性能与这个索引页在缓冲池中，相近。

3. 如何保证数据的一致性？

• 数据库异常奔溃，能够从 redo log 中恢复数据；
• 写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；
• 数据读取时，有另外的流程，将数据合并到缓冲池；

下一次读到该索引页：

• 载入索引页，缓冲池未命中，这次磁盘 IO 不可避免；
• 从写缓冲读取相关信息；
• 恢复索引页，放到缓冲池 LRU 和 Flush 里；（在真正被读取时，才会被加载到缓冲池中）

4. 为什么写缓冲优化，仅适用于非唯一普通索引页呢？

InnoDB 里有聚集索引（Clustered Index）) 和普通索引 (Secondary Index) 两种。如果索引设置了唯一（Unique）属性，在 进行修改操作 时， InnoDB 必须进行唯一性检查 。也就是说， 索引页即使不在缓冲池，磁盘上的页读取无法避免（否则怎么校验是否唯一！？）

此时就应该直接把相应的页放入缓冲池再进行修改。

5. 除了数据页被访问，还有哪些场景会触发刷写缓冲中的数据呢？

• 有一个后台线程，会认为数据库空闲时；
• 数据库缓冲池不够用时；
• 数据库正常关闭时；
• redo log 写满时；（几乎不会出现 redo log 写满，此时整个数据库处于无法写入的不可用状态）

6. 什么业务场景，适合开启 InnoDB 的写缓冲机制？

• 数据库大部分是非唯一索引；
• 业务是写多读少，或者不是写后立刻读取；

Adaptive Hash Index

Innodb存储引擎会监控对表上各索引页的查询，如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引。自适应哈希是通过缓冲池的B+树页构造而来，因此建立的速度很快，不需要对整张表创建哈希索引，Innodb存储引擎会自动根据访问的频率和模式来自动的为某些热点页建立哈希索引。可以通过 innodb_adaptive_hash_index 参数来开启和关闭自适应哈希。

Log Buffer

Log Buffer是存储要写入磁盘上的日志文件的数据的内存区域，日志缓冲区的大小由innodb_log_buffer_size变量决定的，默认是16M，日志缓冲区的内容定期刷新到磁盘。较大的日志缓冲区可以运行大型事务，而无需再事务提交之前将重做日志数据写入磁盘。因此，如果有更新、插入或者删除许多行的事务，则增加日志缓冲区的大小可以节省磁盘IO。

可以通过 innodb_flush_log_at_trx_commit参数来控制如何将日志缓冲区的内容写入并刷新到磁盘，默认是1

1、参数为 0 时，表示事务 commit 不立即把 redo log buffer 里的数据刷入磁盘文件的，而是依靠 InnoDB 的主线程每秒（此时间由参数 innodb_flush_log_at_timeout 控制，默认 1s）执行一次刷新到磁盘。此时可能你提交事务了，结果 mysql 宕机了，然后此时内存里的数据全部丢失。

2、 参数为 1 时，表示事务 commit 后立即把 redo log buffer 里的数据写入到 os buffer 中，并立即执行 fsync () 操作

3、 参数为 2 时，表示事务 commit 后立即把 redo log buffer 里的数据写入到 os buffer 中，但不立即 fsync () SQL 执行过程

On-Disk Structures

Innodb存储引擎的逻辑存储结构是将所有的数据都逻辑的放在了一个空间中，这个空间中的文件就是实际存在的物理文件（ibd文件）。默认情况下，一个表占用一个表空间，表空间可以看做是Innodb存储引擎逻辑结构的最高层，所有的数据都放在表空间中。

表空间分为系统表空间，临时表空间，通用表空间，undo表空间和独立表空间。

系统表空间

系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下， InnoDB 会在数据目录下创建一个名为ibdata1，大小为 12M 的文件，这个文件就是对应的系统表空间在文件系统上的表示。这个文件是可以自扩展的，当不够用的时候它会自己增加文件大小。需要注意的一点是，在一个 MySQL 服务器中，系统表空间只有一份。

可以通过innodb_data_file_path变量设置

独立表空间

在 MySQL5.6.6 以及之后的版本中， InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同，只不过添加了一个.ibd 的扩展名而已。

可以通过变量innodb_file_per_table变量设置

通用表空间

通用表空间是使用create tablespace语法创建的共享表空间，主要有以下功能：

1、跟系统表空间类似，通用表空间是能够存储多个表的数据的共享表空间

2、与独立表空间相比，通用表空间具有潜在的内存优势：通用表空间多个表共享一个表空间，所以消耗更少的内存用于表空间元数据，独立表空间需要更多的内存用于表空间元数据

3、数据文件可以放置在与mysql目录相关或独立的目录中

4、通用表空间支持所有的行格式以及相关的特性

5、create table语句可以使用tablespace选项在通用表空间，独立表空间，或者系统表空间创建表

6、create table语句可以使用tablespace选项在通用表空间，独立表空间，或者系统表空间移动表

Undo 表空间

在初始化mysql实例的时候，会创建两个默认的undo表空间，用于事务异常时进行数据回滚，默认的undo表空间文件名称是undo_001和undo_002。如果默认的表空间不足以支撑需求，那么可以增加、删除和移动undo表空间

临时表空间

临时表空间分为会话临时表空间和全局临时表空间

1. 会话临时表空间

   会话临时表空间用于存储用户创建的临时表或由优化器创建的内部临时表。

   当一个会话首次请求创建磁盘上的临时表时，服务器会从临时表空间池中分配临时表空间给会话，分配给会话的临时表空间将用于会话创建的所有磁盘上的临时表。

   当会话断开连接时，其临时表空间被截断并释放会池中。

   一个会话最多分配两个临时表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表

   服务器启动时将创建一个包含10个临时表空间的池，池的大小永远不会缩小，表空间会根据需要自动添加到池中，当服务器正常关闭或者初始化终止时，临时表空间将被移除。

2. 全局临时表空间

   全局临时表空间用于存储对用户创建的临时表所做更改的回滚段。

   全局临时表空间在正常关闭或初始化终止时被移除，并在服务器启动时重新创建

   全局临时表空间在创建时接受一个动态生成的空间ID

   如果无法创建全局临时表空间，服务器将拒绝启动，如果服务器意外停止，全局临时表空间不会被移除

   重新启动mysql服务器会自动移除并重新创建全局临时表空间，回收全局临时表空间数据文件占用的磁盘空间

Doublewrite Buffer Files

我们常见的服务器一般是linux系统，linux文件系统页的默认大小是4KB，而mysql的页大小默认是16KB，mysql程序一般是运行在linux操作系统上的，所以当跟操作系统进行交互的时候，mysql中一页数据刷到磁盘，就要写4个文件系统里的页，如下图所示：

注意，这个刷页的操作并不是原子操作，比如我们操作第二个页的时候，服务器断电了，那么就会有问题，出现页数据损坏的情况，此时是无法通过redolog来进行修复的，因为redolog中记录的是对页的物理操作，而不是页面的全量记录，当发生部分页写入的时候，出现的是未修改的数据，此时redolog是无能为力的。

Doublewrite Buffer的出现就是为了解决上面的这种，给Innodb提供了数据页的可靠性，虽然名字带了Buffer，但是实际上是由内存+磁盘的结构：

内存结构：Doublewrite Buffer内存结构由128个页组成，大小是2MB

磁盘结构：Doublewrite Buffer磁盘结构由128个页（2个区组成，每个区1MB），大小是2MB

Doublewrite Buffer的原理是，再把数据页写到数据文件之前，InnoDB先把它们写到一个叫「doublewrite buffer（双写缓冲区）」的共享表空间内，在写doublewrite buffer完成后，InnoDB才会把页写到数据文件适当的位置。

如果在写页的过程中发生意外崩溃，InnoDB会在doublewrite buffer中找到完好的page副本用于恢复。

DoubleWrite Buffer原理图如下所示：

1、页数据先通过memcpy函数拷贝到内存中的DoubleWrite Buffer中

2、DoubleWrite Buffer的内存里的数据页会fsync刷到DoubleWrite Buffer的磁盘上，分两次写入，每次写1MB

3、 如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的Double write中找到该页的一个副本，将其复制到表空间文件，再应用redo日志

那么如何利用Doublewrite Buffer来进行数据恢复呢？主要由三种情况：1、脏数据写磁盘成功，这种情况是最常见的，这种情况不需要Doublewrite Buffer，2、表空间写失败，如果写表空间失败，那么这些数据不会写到数据文件中，数据库认为这次刷盘没有发生过，mysql此时会载入原始数据，不做任何修改，3、脏数据刷数据文件失败，此时写表空间成功了，但是写数据文件失败，在恢复的时候，mysql会比较整个页面，如果不对的话，直接从Doublewrite Buffer中找到该页的副本，并将其复制到数据文件中，然后再进行redolog的操作。

Redo Log

redolog记录数据库的变更，数据库崩溃后，会从redolog中获取事务信息，进行系统恢复，在5.x版本中，redolog在磁盘上表现为ib_logfile0和ib_logfile1.在mysql8版本之后以#ib_redo_N来记录

Tablespace结构

从Innodb存储引擎的逻辑结构看，所有的数据都被逻辑地放在一个空间内，称之为表空间，表空间又由 段（segment），区（extent），页（page）组成。页在一些文档中有时候也称为块（block） ，如下图所示：

• 段（segment）

  • 表空间由段组成，常见的段有数据段、索引段、回滚段等。
  • InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。数据段即为B+树的叶子结点，索引段即为B+树的非索引结点。
  • 在InnoDB存储引擎中对段的管理都是由引擎自身所完成，DBA不能也没必要对其进行控制。

  区（extent）

  • 区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。
  • 为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。
  • 默认情况下，InnoDB存储引擎页的大小为16KB，一个区中一共64个连续的区。

  页（page）

  • 页是InnoDB磁盘管理的最小单位。
  • 在InnoDB存储引擎中，默认每个页的大小为16KB。
  • 从InnoDB1.2.x版本开始，可以通过参数innodbpagesize将页的大小设置为4K，8K，16K。
  • InnoDB存储引擎中，常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页等。

页结构

innodb数据页由以下7部分组成，如图所示：

其中File Header、Page Header、File Trailer的大小是固定的，分别为38，56，8字节，这些空间用来标记该页的一些信息，如Checksum，数据页所在B+树索引的层数等。User Records、Free Space、Page Directory这些部分为实际的行记录存储空间，因此大小是动态的。

下边我们用表格的方式来大致描述一下这7个部分：

记录在页中的存储流程图

每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

不同的Innodb页构成的数据结构图

一张表中可以有成千上万条记录，一个页只有16KB，所以可能需要好多页来存放数据。不同页其实构成了一条双向链表，File Header是InnoDB页的第一部分，它的FILPAGEPREV和FILPAGENEXT就分别代表本页的上一个和下一个页的页号，即链表的上一个以及下一个节点指针。

分库分表相关的问题

为什么要进行分库操作？

分库指的是将存储在一个数据库中的数据拆分到多个数据库中进行存储。主要原因如下:

1、性能提升：随着业务量的增长，单一数据库可能会面临性能瓶颈。分库可以将数据和请求分散到多个数据库上，从而提高系统的吞吐量和响应时间

2、容量扩展：单一数据库可能收到硬件资源（磁盘，CPU，内存）的限制，分库可以将数据分散到多个数据库上，从而突破这些限制，实现容量的线性扩展

3、可靠性提升：分库可以提高系统的可靠性。当某个数据库出现故障时，其他数据库仍然可以正常工作，从而保证业务的连续性

4、安全性提升：分库可以降低数据泄露的风险。通过将数据分散到多个数据库上，即使某个数据库被攻破，攻击者也无法获取全部数据

5、运维便利性：分库可以提高运维的便利性。每个数据库可以独立进行备份、恢复、监控等操作，降低了运维的复杂性和难度

为什么要进行分表操作？

分表操作是指将一个大表按照一定的规则分解成多张具有独立空间的表的过程。主要原因如下：

1. 性能提升：随着业务的发展，数据库中的数据量会不断增长，导致单表的数据量变得非常大。当表的数据量过大时，查询和插入操作会变得非常耗时，性能低下。分表可以将数据分散到多个表中，减少单个表的数据量，从而提高查询和插入的效率。

2. 减轻IO和CPU压力：当单表数据量过大时，查询操作可能需要扫描大量的数据，导致磁盘IO和网络IO的压力增大，同时也可能增加CPU的负担。分表可以将数据分散到多个表中，减少单次查询需要扫描的数据量，从而减轻IO和CPU的压力。

3. 方便数据管理：随着数据量的增长，单表的数据管理可能会变得非常复杂。分表可以将数据按照一定的规则进行划分，使得数据管理变得更加简单和清晰。

4. 避免锁竞争：在并发访问较高的情况下，单表可能会成为锁竞争的热点，导致性能下降。分表可以将数据分散到多个表中，减少锁竞争的可能性。

如何选择分片键？

分片键的选择并没有啥具体的原则，重点还是结合业务和需求来进行判断，比如可以进行如下选择和判断：

1. 选择合适的业务逻辑主体：比如面向用户的应用，可以使用用户id作为分片键，在电商系统重，可以选择订单、卖家等作为分片键

2. 如果没有特别明确的业务主体，那么可以考虑数据分布和访问均衡度，尽可能的使每个分表重的数据相对均衡的分布在不同的物理分表中，比如在电商系统中，可以按照商品id和用户id将数据均衡的分布，在金融行业，可以按照用户id或者交易时间进行分表，在物流行业，可以按照订单id或者运输轨迹进行分布，在社交媒体行业，可以按照用户id或者发布时间进行分布

注意：时间类型是一个很好的分片键选择，可以将数据均衡分布

非分片键如何查询？

当按照某一个或者某几个分片键进行分库分表之后，在一些业务场景，难免需要按照非分片键进行查询，此时可以按照如下方式进行查询：

1. 对所有库所有表进行遍历查询，找到符合条件的记录，此时的效率一定很低，不建议使用

2. 将需要查询的数据信息同步到ES中，在ES中进行高效查询，此方式效率较高，但是需要进行mysql和ES的数据同步

3. 可以考虑在不同的分表中存储一些冗余数据，能够在此分表上直接进行查询，而不需要跨表查询，比如，在一个有大量用户但是活跃用户较少的系统中，可以将活跃用户的数据冗余在单独的表中，查询的时候直接查询这张表，而不需要扫描全部的表

4. 可以考虑使用基因法，比如在电商系统中，让同一个用户的所有订单全部存储到一个表中，且查询的时候既可以通过用户id，也可以通过订单id。

如何实现跨节点的join关联

在没有分库分表的场景中，join关联多张表的时候非常简单，但是分库分表之后，相关联的表可能不再同一个数据库中，那么如何解决跨库的join操作呢？

1. 字段冗余：把需要关联的字段放入主表中，避免关联操作；比如订单表保存了卖家ID（sellerId），你把卖家名字sellerName也保存到订单表，这就不用去关联卖家表了。这是一种空间换时间的思想。

2. 全局表：比如系统中所有模块都可能会依赖到的一些基础表（即全局表），在每个数据库中均保存一份。

3. 数据抽象同步：比如A库中的a表和B库中的b表有关联，可以定时将指定的表做同步，将数据汇合聚集，生成新的表。一般可以借助ETL工具。

4. 应用层代码组装：分开多次查询，调用不同模块服务，获取到数据后，代码层进行字段计算拼装。

分库后，事务问题如何解决？

分库分表后，假设两个表在不同的数据库中，那么本地事务已经无效了，此时需要考虑分布式事务，常见的分布式事务解决方案如下：

1. 两阶段提交

2. 三阶段提交

3. TCC

4. saga

5. 本地消息表

6. 最大努力通知

分库分表的分页问题

1. 方案1（全局视野法）：
   在各个数据库节点查到对应结果后，在代码端汇聚再分页。这样优点是业务无损，精准返回所需数据；缺点则是会返回过多数据，增大网络传输，也会造成空查，

   比如分库分表前，你是根据创建时间排序，然后获取第2页数据。如果你是分了两个库，那你就可以每个库都根据时间排序，然后都返回2页数据，然后把两个数据库查询回来的数据汇总，再根据创建时间进行内存排序，最后再取第2页的数据。

2. 方案2（业务折衷法-禁止跳页查询）：
   这种方案需要业务妥协一下，只有上一页和下一页，不允许跳页查询了。

   这种方案，查询第一页时，是跟全局视野法一样的。但是下一页时，需要把当前最大的创建时间传过来，然后每个节点，都查询大于创建时间的一页数据，接着汇总，内存排序返回。

垂直分库、水平分库、垂直分表、水平分表的区别

1. 垂直分库：将原本一个数据库中的表按照业务功能分布到不同的数据库中，每个数据库只包含部分表

2. 水平分库：将一个数据库中的表按照数据行进行拆分，分不到多个数据库中国，每个数据库都包含完成的表结构，但只包含部分数据

3. 垂直分表：将一个数据库中的列拆分到不同的表中，这些表具有相同的主键，但包含不同的列

4. 水平分表：将一个数据库表中的数据拆分到多个相同结构的表中，每个表都包含部分数据行，通常按照一定的规则拆分

分表要停服嘛？不停服怎么做？

不用停服。不停服的时候，应该怎么做呢，主要分五个步骤：

1. 编写代理层，加个开关（控制访问新的DAO还是老的DAO，或者是都访问），灰度期间，还是访问老的DAO。

2. 发版全量后，开启双写，既在旧表新增和修改，也在新表新增和修改。日志或者临时表记下新表ID起始值，旧表中小于这个值的数据就是存量数据，这批数据就是要迁移的。

3. 通过脚本把旧表的存量数据写入新表。

4. 停读旧表改读新表，此时新表已经承载了所有读写业务，但是这时候不要立刻停写旧表，需要保持双写一段时间。

5. 当读写新表一段时间之后，如果没有业务问题，就可以停写旧表啦