首页  思维导图  详情



 



后端工程师必备mysql知识要点

2026-07-01 06:59:02   0  举报





AI智能生成

后端工程师必备mysql知识要点

程序员

MySQL主从复制原理

mysql核心

MySQL优化

MySQL思维导图

线上问题处理

模板推荐

作者其他创作

大纲/内容

数据结构

索引是一种排好序的数据结构

B+树

根节点

保存在内存

整棵树的入口，是查询的起点

内部节点

充当"目录页"，只存索引值和指针，用于路由查询路径，不存实际数据

叶子节点

即页，存放实际数据（InnoDB中存行数据或主键），最下层节点，并且所有叶子节点通过双向链表串联，支持范围扫描

页（Page）是存储单元，叶子节点（Leaf Node）是逻辑概念。一个叶子节点在物理上就是一个页（或者说由“一个页”来承载）页与页之间通过单向指针串联

MyISAM引擎

无事务

没有聚集索引

MyISAM索引文件和数据文件是分离的(非聚集)

节点保存真实物理地址

InnoDB引擎

聚集索引

表即索引

表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

保存数据物理地址

不用跨文件

非聚集索引

保存主键地址

找到叶子节点后，回表查找主键

一致性和节省存储空间

查找方式

将节点放到内存比对

节点大小为1页，1页16kb，也就是说，三层就能存两千万以上的叶子节点

常驻内存的包括根节点甚至所有非叶子节点

type列

Null

explain select min(id) from film;

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表

const, system

explain extended select * from (select * from film where id = 1) tmp;

show warnings;

mysql内部优化后的语句

mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system

eq_ref

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

explain select * from film_actor left join film on film_actor.film_id = film.id;

ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

1. 简单 select 查询，name是普通索引（非唯一索引）

explain select * from film where name = 'film1';

2.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

explain select * from actor where id > 1;

index

扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

explain select * from film;

ALL

即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

explain select * from actor;

其他

为什么索引推荐自增？

如果不是自增，插入数据时，会导致树的调整过大

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

一致性和节省存储空间

B-tree和B+-tree区别

B树是“哪里有位置哪里就放数据”，每个节点都是数据，适合随机读写；而 B+树是“上面只放目录，下面放全部数据且串成链表”，特别适合 MySQL 这种需要大量范围查询和稳定快速响应的场景。

对于 WHERE a > 1 ORDER BY b ，只能保证b局部有序

hash

无序，适合等值查找

索引优化方案

图中上层的绿色方块是非叶子节点。它们不存储完整的数据行，只存储索引列的前缀信息，用于导航。 你可以看到上层节点存储了 Bill, HanMeimei, Lilei。 这些值充当了“路标”。例如，如果要查找 Jeff，B+树会先比较根节点，发现 Jeff 大于 HanMeimei 且小于 Lilei（或者根据具体路由逻辑），从而快速定位到中间的子节点。

示例表： CREATE TABLE `employees` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',   `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',   `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',   `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',   PRIMARY KEY (`id`),   KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW()); INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

索引最佳实践

1.全值匹配

查询条件中的字段顺序与联合索引的字段顺序完全一致，且每个字段都是等值匹配（= 或 IN）。

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

EXPLAIN SELECT * FROM employees WHERE  name= 'LiLei' AND  age = 22 AND position ='manager';

2.最左前缀法则

MySQL使用联合索引时，会从索引的最左列开始匹配，直到遇到范围查询（>、<、BETWEEN、LIKE）或无法匹配的列为止。

反例

EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';

EXPLAIN SELECT * FROM employees WHERE position = 'manager';

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

给hire_time增加一个普通索引：

ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;

EXPLAIN  select * from employees where date(hire_time) ='2018-09-30';

data函数使索引失效

SELECT * FROM employees WHERE YEAR(create_time) = 2023;

1-- 针对时间函数，改为范围查询 SELECT * FROM employees  WHERE create_time >= '2023-01-01 00:00:00' AND create_time < '2024-01-01 00:00:00';

2隐式类型转换

3数学计算

-- 假设 age 上有索引 SELECT * FROM employees WHERE age + 1 = 30;

4.存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

name = 'Lilei'：生效（精确匹配）。 age > 22：生效（范围匹配）。 position = 'dev'：失效（由于age不确定，无法利用索引定位，只能回表后过滤）。

优化方案1：索引覆盖  SELECT name, age, position FROM ...，那么即使 position 没用于查找，整个查询依然可以只在索引树上完成（Using index）

5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

SQL: SELECT name, age, position FROM employees WHERE name = 'Lilei' AND age = 30; 过程： 在图中找到 Lilei, 30 的记录。 发现你要查的 name, age, position 全都在当前这个叶子节点里。 直接返回结果，不需要再去聚簇索引里找数据。

6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

用肯定查询替代：例如把 status != 1 改写为 status IN (0, 2, 3)，这样就能正常走索引了。

用范围查询替代：把 age != 30 拆分为 age < 30 OR age > 30。

NOT EXISTS 的优化：在大数据量场景下，NOT EXISTS 或 NOT IN 容易失效，建议使用 LEFT JOIN ... ON ... IS NULL 来替代，这通常能更好地利用索引。

拆分大范围：如果业务允许，可以将一个巨大的范围拆分成多个小范围查询。例如，将 age >= 1 AND age <= 2000 拆分为 age <= 1000 和 age >= 1001 分两次查。

尽量使用覆盖索引：如果你的查询是 SELECT age FROM ... WHERE age > 30，因为不需要回表，优化器更倾向于走索引（Extra 会显示 Using index）。

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

IS NULL 和 IS NOT NULL 是否走索引，并不取决于关键字本身，而是取决于 MySQL 优化器对“区分度（数据分布比例）”和“回表成本”的综合评估。

NULL 值在 B+ 树中会被统一放在该索引列的最左侧（或者最右侧，取决于版本和排序规则，通常是集中在一起的）。

这意味着，IS NULL 在底层其实等价于一个等值查询（定位到 NULL 聚集的区间）。

而 IS NOT NULL 在底层其实等价于一个范围查询（跳过 NULL 区间，扫描剩下的所有数据）。

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

这依然要回到 B+ 树的底层排序规则。B+ 树是严格按照从左到右的字典序来排列的。

右模糊 abc%：就像查字典找“张”字开头的词，可以直接在树上定位到“张”的分支，然后顺着叶子节点往后读，效率极高。

左模糊 %abc：相当于你要找“名字里包含abc”的人。因为 abc 可能出现在任意位置（比如 Xabc, Yabc, Zabc），它们在 B+ 树的各个角落都是散乱分布的，根本不存在连续的索引区间。优化器无法利用 B+ 树的有序性，只能被迫放弃索引，进行全表扫描。

：kk%可看作常量

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

10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

IN 本质上是多个等值查询的集合（例如 WHERE age IN (20, 25, 30)）。

如果表里有 100 万行数据，你 IN 了 3 个值，查出 3 条数据，优化器会毫不犹豫地走索引（Index Seek）。

但如果你 IN 了 1000 个值，或者这 3 个值恰好命中了表里 50% 的数据呢？优化器一算账：去 B+ 树里查 50 万次，每次查到后还要拿着主键去聚簇索引里回表 50 万次！这个随机 IO 成本太高了，还不如直接全表扫描（Full Table Scan）。这就是我们之前提到的“范围查询优化”逻辑。

OR 比 IN 更危险。如果 OR 连接的条件中，只要有一个字段没有索引，那么整个查询的索引都会失效，直接退化为全表扫描。

❌ 错误示范：假设 name 有索引，但 email 没索引。

SELECT * FROM employees WHERE name = 'Lilei' OR email = 'test@test.com';

11.范围查询优化

从库开慢查询

基数小的列加入sql例（sex in（xx,xx））

分页查询优化

1、根据自增且连续的主键排序的分页查询

主键自增且连续

结果是按照主键排序的

EXPLAIN select * from employees limit 90000,5;

先扫描并读取 90005 条记录，然后丢弃前 90000 条，只返回最后的 5 条。当偏移量（offset）极大时，这种“全量扫描+丢弃”的成本极高，执行效率非常低

EXPLAIN select * from employees where id > 90000 limit 5;

利用了 InnoDB 引擎 B+ 树主键索引的全局有序性。数据库不需要去扫描前 90000 行，而是直接在主键索引树上通过二分查找定位到 id=90000 的位置，然后向后顺序读取 5 条数据即可。这样扫描的行数大大减少，执行效率呈指数级提升

2、根据非主键字段排序的分页查询

延迟关联（Deferred Join）

EXPLAIN select * from employees ORDER BY name limit 90000,5;

描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录

Join关联查询优化

设使t1有1万条t2有100条

CREATE TABLE `t1` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `a` int(11) DEFAULT NULL,   `b` int(11) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `idx_a` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

create table t2 like t1;

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

一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

从执行计划中可以看到这些信息：

驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表，用where条件过滤完驱动表，然后再跟被驱动表做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。

当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。

使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。

上面sql的大致流程如下：

从表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；

从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；

取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；

重复上面 3 步。

整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行)。因此整个过程扫描了 200 行。

如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低(下面有详细解释)，mysql会选择Block Nested-Loop Join算法。

被驱动表的关联字段必须建索引：这是发挥 NLJ 威力的前提。如果没建索引，就会退化为 Simple NLJ 甚至触发 Block Nested-Loop（块嵌套循环），导致严重的性能问题。

严格遵循“小表驱动大表”：在写 LEFT JOIN 时，务必将过滤后数据量较小的表放在左侧作为驱动表，减少外层循环的次数。

禁止无意义的 SELECT *：在 NLJ 中，如果查询的字段过多，会导致频繁的回表操作，IO 开销激增。尽量只查业务需要的字段，或者利用联合索引实现“覆盖索引”，让查询直接在索引树上完成。

控制 JOIN 的表数量：阿里开发规范强制要求禁止三张表以上的 JOIN。因为表越多，NLJ 的嵌套层级越深，优化器越容易失效，极易产生临时表和文件排序（filesort）。

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

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

简单嵌套循环的痛点在于：驱动表有多少行，被驱动表就要被全表扫描多少次。 BNL 的优化思路非常巧妙：它引入了一个关键组件——Join Buffer（连接缓冲区），通过批量处理，大幅减少被驱动表的全表扫描次数。

EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。

上面sql的大致流程如下：

把 t2 的所有数据放入到 join_buffer 中

把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比

返回满足 join 条件的数据

整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。

这个例子里表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？·

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

比如 t2 表有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空  join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？

如果上面第二条sql使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。

很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

对于关联sql的优化

关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引

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

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

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）

尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

对于小表定义的明确

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

in和exsits优化

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

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

select * from A where id in (select id from B)   #等价于：   for(select id from B){       select * from A where A.id = B.id     }

exists：当A表的数据集小于B表的数据集时，exists优于in   将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留

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

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

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

select * from A where exists (select 1 from B where B.id = A.id) #等价于:     for(select * from A){       select * from B where B.id = A.id     }      #A表与B表的ID字段应建立索引

count(*)查询优化

四个sql的执行计划一样，说明这四个sql执行效率应该差不多

字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)    //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)

字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)    //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)

常见优化方法

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

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

对于innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制，后面会讲)，查询count需要实时计算

2、show table status

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

show table status like employees';

很多开发者为了绕过 COUNT(*) 的性能问题，会使用 SHOW TABLE STATUS 命令，读取其中的 Rows 或 TABLE_ROWS 字段。

这个值并不是精确的总数，而是 InnoDB 通过采样估算得来的。官方文档明确指出，这个估算值的误差可能高达 40% 到 50%。它只能用于运维监控趋势或后台报表展示，绝对不能用于分页总数计算、库存校验等需要强一致性的业务逻辑中。

3、将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

4、增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

基数小的列加入sql例（sex in（xx,xx））

维护的索引树过多，添加修改会增加维护成本

少用单值索引

事务

事务隔离级别

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

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

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

常看当前数据库的事务隔离级别: show variables like 'tx_isolation';

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

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

读未提交：

虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：

一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏数据：

读已提交

客户端B的事务还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题：

客户端B的事务提交

客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题

可重复读

在客户端A，接着执行update account set balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤2中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

快照读不会读自身已修改的数据，该数据对当前事务可见

串行化

打开一个客户端B，并设置当前事务模式为serializable，更新相同的id为1的记录会被阻塞等待，更新id为2的记录可以成功，说明在串行模式下innodb的查询也会被加上行锁。

锁

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

从性能上分为乐观锁(用版本对比来实现)和悲观锁

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

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。

手动增加表锁

lock table 表名称 read(write),表名称2 read(write);

查看表上加过的锁

show open tables;

删除表锁

unlock tables;

行锁

每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MYISAM的最大不同有两点：

InnoDB支持事务（TRANSACTION）

InnoDB支持行级锁

行锁演示

一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞

注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(RR级别会升级为表锁，RC级别不会升级为表锁) 比如我们在RR级别执行如下sql

总结： InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

PS：关于RR级别行锁升级为表锁的原因分析

因为在RR隔离级别下，需要解决不可重复读和幻读问题，所以在遍历扫描聚集索引记录时，为了防止扫描过的索引被其它事务修改(不可重复读问题) 或 间隙被其它事务插入记录(幻读问题)，从而导致数据不一致，所以MySQL的解决方案就是把所有扫描过的索引记录和间隙都锁上，这里要注意，并不是直接将整张表加表锁，因为不一定能加上表锁，可能会有其它事务锁住了表里的其它行记录。

页锁

只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

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

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响，比如：select * from T where id=1 lock in share mode

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁，数据修改操作都会加写锁，查询也可以通过for update加写锁，比如：select * from T where id=1 for update

意向锁（Intention Lock）：又称I锁，针对表锁，主要是为了提高加表锁的效率，是mysql数据库自己加的。当有事务给表的数据行加了共享锁或排他锁，同时会给表设置一个标识，代表已经有行锁了，其他事务要想对表加表锁时，就不必逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁。特别是表中的记录很多时，逐行判断加表锁的方式效率很低。而这个标识就是意向锁。

意向锁主要分为：

意向共享锁，IS锁，对整个表加共享锁之前，需要先获取到意向共享锁。

意向排他锁，IX锁，对整个表加排他锁之前，需要先获取到意向排他锁。

间隙锁(Gap Lock)

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

其他Session没法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据

它的核心使命是防止幻读。它阻止其他事务在这个空隙中插入新数据。注意，间隙锁之间是不互斥的，多个事务可以同时持有同一个间隙锁。

间隙锁是在可重复读隔离级别下才会生效。

临键锁(Next-key Locks)

Record Lock + Gap Lock 的组合。它是一个左开右闭区间，比如 (10, 20]

无索引行锁会升级为表锁(RR级别会升级为表锁，RC级别不会升级为表锁)

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

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。

锁优化建议

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

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

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

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

尽可能低级别事务隔离

锁等待分析

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

show status like 'innodb_row_lock%';

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

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

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

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

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

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

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

Innodb_row_lock_time_avg （等待平均时长）

Innodb_row_lock_waits （等待总次数）

Innodb_row_lock_time（等待总时长）

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

MVCC多版本并发控制机制

Mysql在可重复读隔离级别下如何保证事务较高的隔离性，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。

这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。 Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

undolog日志

undo日志版本链与read view机制详解

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链

read view

版本链比对规则： 1. 如果 row 的 trx_id 落在绿色部分( trx_id<min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的； 2. 如果 row 的 trx_id 落在红色部分( trx_id>max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若 row 的 trx_id 就是当前自己的事务是可见的）； 3. 如果 row 的 trx_id 落在黄色部分(min_id <=trx_id<= max_id)，那就包括两种情况     a. 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自己的事务是可见的)；     b. 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。

不同版本的数据通过回滚指针串起来

通过read view和隐藏字段trx_id实现mvcc

undolog如何保证事务的原子性？

undolog维护了insert undolog语句当事务回滚，会将其中的逆向语句回放，或者找到旧版本的数据

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束之前永远都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成read-view)，这个视图由执行查询时所有未提交事务id数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

 示例

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

此图中 所有事务开启时，均为创建trx_id，若事务100和事务200交换提交顺序，则事务300依然在所有查询建立trx_id之前提交， select 1 readview：{【100，200】300} select 2 readview：{【100】300} 事务修改，会加至最新的read_view，但有行锁仅自己可操作，对读未提交可见

关于readview和可见性算法的原理解释

readview和可见性算法其实就是记录了sql查询那个时刻数据库里提交和未提交所有事务的状态。

要实现RR隔离级别，事务里每次执行查询操作readview都是使用第一次查询时生成的readview，也就是都是以第一次查询时当时数据库里所有事务提交状态来比对数据是否可见，当然可以实现每次查询的可重复读的效果了。

要实现RC隔离级别，事务里每次执行查询操作readview都会按照数据库当前状态重新生成readview，也就是每次查询都是跟数据库里当前所有事务提交状态来比对数据是否可见，当然实现的就是每次都能查到已提交的最新数据效果了。

注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作或加排它锁操作(比如select...for update)的语句，事务才真正启动，才会向mysql申请真正的事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

总结：

MVCC机制的实现就是通过read-view机制与undo版本链比对机制，使得不同的事务会根据数据版本链对比规则读取同一条数据在版本链上的不同版本数据。

MVCC 的“可见性”与“全局计数”的矛盾

既然每个事务看到的“可见行”都是不同的，那么这张表在“当前时刻”到底有多少行，就是一个不确定的值。如果 InnoDB 像 MyISAM 引擎那样，在磁盘上维护一个固定的全局总行数，这个总数对任何一个事务来说都是不准确的。

因此，为了保证事务的隔离性和数据的强一致性，InnoDB 放弃了存储全局总行数，选择在每次执行 COUNT(*) 时，实时地把数据一行一行地读出来，逐行判断可见性，然后累积计数。这就导致了极大的性能开销。

事务及其ACID属性

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

原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。

一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性。

隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。

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

并发事务处理带来的问题

更新丢失(Lost Update)或脏写

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

脏读（Dirty Reads）

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

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

不可重读（Non-Repeatable Reads）

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

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

幻读（Phantom Reads）

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

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

事务优化实践原则

将查询等数据准备操作放到事务外

事务中避免远程调用，远程调用要设置超时，防止事务等待时间太久

事务中避免一次性处理太多数据，可以拆分成多个事务分次处理

更新等涉及加锁的操作尽可能放在事务靠后的位置

能异步处理的尽量异步处理

应用侧(业务代码)保证数据一致性，非事务执行

存储

日志

redolog

关键参数

innodb_log_buffer_size：设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。

innodb_log_group_home_dir：设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。

innodb_log_files_in_group：设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。

innodb_log_file_size：设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

写入磁盘过程分析： redo log 从头开始写，写完一个文件继续写另一个文件，写到最后一个文件末尾就又回到第一个文件开头循环写，如下面这个图所示。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里。

write pos 和 checkpoint 之间的部分就是空着的可写部分，可以用来记录新的操作。如果 write pos 追上checkpoint，表示redo log写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：

设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。

设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。

设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 操作系统函数 write 写到文件系统的 page cache，然后调用操作系统函数 fsync 持久化到磁盘文件。

redo log写入策略参看此图

page chche是系统文件系统的缓存，写入page chche就视为已经写入成功了

# 查看innodb_flush_log_at_trx_commit参数值：

show variables like 'innodb_flush_log_at_trx_commit';

# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：

set global innodb_flush_log_at_trx_commit=1;

MySQL引入了redo log，Buffer Pool内存写完了，然后会写一份redo log，这份redo log记载着这次在某个页上做了什么修改。

即便MySQL在中途挂了，我们还可以根据redo log来对数据进行恢复。

redo log 是顺序写的，写入速度很快。并且它记录的是物理修改（xxxx页做了xxx修改），文件的体积很小，恢复速度也很快。

binlog

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。

启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。

考虑性能，应在从库开启

# 查看binlog相关参数

show variables like '%log_bin%';

binlog 的日志格式

STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样。

ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。

MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，其它情况选择Statement，推荐使用这一种。

binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。

也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。

还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

发生以下任何事件时, binlog日志文件会重新生成：

服务器启动或重新启动

服务器刷新日志，执行命令flush logs

日志文件大小达到 max_binlog_size 值，默认值为 1GB

删除 binlog 日志文件

删除当前的binlog文件

reset master;

# 删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除

purge master logs to 'mysql-binlog.000006';

# 删除指定日期前的日志索引中binlog日志文件

purge master logs before '2023-01-21 14:00:00';

查看 binlog 日志文件

# 查看bin-log二进制文件（命令行方式，不用登录mysql）

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007

# 查看bin-log二进制文件（带查询条件）

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

能看到里面有具体执行的修改伪sql语句以及执行时的相关情况。

binlog日志文件恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql，举一个数据恢复的例子

# 先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000008，后面我们的修改操作日志都会记录在最新的这个文件里

flush logs;

# 执行两条插入语句

INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4', 'zhuge', '666');

INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5', 'zhuge1', '888');

# 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了

delete from account where id > 3;

# 现在需要恢复被删除的两条数据，我们先查看binlog日志文件

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000008

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 701

我们可以根据文件位置标识来恢复数据

为什么会有redo log和binlog两份日志呢？

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

undo log

InnoDB对undo log文件的管理采用段的方式，也就是回滚段（rollback segment） 。每个回滚段记录了 1024 个 undo log segment ，每个事务只会使用一个undo log segment。

undo log日志什么时候删除

新增类型的，在事务提交之后就可以清除掉了。

修改类型的，事务提交之后不能立即清除掉，这些日志会用于mvcc。只有当没有事务用到该版本信息时才可以清除。

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置

show variables like '%log_error%';

通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

general_log：是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。

general_log_file：通用查询日志记录的位置参数。

show variables like '%general_log%';

# 打开通用查询日志

SET GLOBAL general_log=on;

其他

为什么redo log不能做主从复制和备份恢复？

redolog空间固定会用完，用完了就从头写，只能记录事务提交后没有刷盘的数据；

binlog 是可以追加写入的。“追加写”是指binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志，记录的是全量日志

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻2.3.辑，比如“给 ID=2 这一行的 c 字段加 1 ”。redo log 是从数据页的角度来看的，它关心的是数据在磁盘上的物理布局和如何高效地修改这些数据。binlog 是从 SQL 语句的角度来看的，它关心的是执行了哪些操作以及这些操作的内容。

而redo log更适合事务级别的恢复，即数据库发生异常重启导致buffer pool中数据未刷盘的数据恢复，之前提交的记录都不会丢失，这个能力称为crash-safe。

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

因为来一个请求就直接对磁盘文件进行随机读写，然后更新磁盘文件里的数据性能可能相当差。

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

Mysql这套机制看起来复杂，但它可以保证每个更新请求都是更新内存BufferPool，然后顺序写日志文件，同时还能保证各种异常情况下的数据一致性。

更新内存的性能是极高的，然后顺序写磁盘上的日志文件的性能也是非常高的，要远高于随机读写磁盘文件。

正是通过这套机制，才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求。

关系型数据库出现拓展瓶颈不宜解决，可转移至java

bufferPool

mysql存数据以页为单位

1页16kb ，每次查询会查出这一页的数据放入buffer pool

为什么不直接放缓存而是直接拿这一页？

1.InnoDB的索引是B+树结构，相邻的索引行在物理磁盘上通常位于同一页或相邻页。

1.1.程序的空间性原理，一个内存位置被访问了很有可能附件的位置都会被访问，业务代码会大量使用数组这种连续的结构，对于取连续性数据命中率就很高了

2.MySQL崩溃重启后，必须用Redo Log去恢复Buffer Pool中丢失的修改。而Redo Log物理记录的是“对某个页内偏移量的修改”

3.硬件限制：操作系统文件系统的最小读写单位是块（Block），通常是4KB。InnoDB为了减少磁盘寻道时间，将自身管理单位设定为16KB的页，且页的大小必须是OS块大小的整数倍。以及由此得出的io成本问题，同理，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高

后续取数据从buffer pool中取，没有再找磁盘，写数据也是先写buffer pool，但不立即刷盘，而是将修改也标记为脏页，后台线程在某个时间将脏页刷盘

为什么buffer pool不立即刷盘？

磁盘io是随机io，需要找不同的磁盘页再修改，而redo log中记录了磁盘页的位置和修改记录，刷盘时只需要往redo log中追加即顺序io，这也是记录物理修改而不是数据的原因，redo log一刷盘，即使MySQL崩溃，也能恢复数据

为什么buffer pool不立即刷盘？

redo log和bin log有一个共同的数据字段，叫 XID。崩溃恢复的时候，会按顺序扫描 redo log：

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 XID 去 binlog 找对应的事务，判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。

Innodb底层原理与Mysql日志机制

1. Server 层处理流程

当客户端发起 SQL 请求时，首先经过的是 MySQL 的 Server 层（图中粉色区域）：

连接器：负责管理连接和权限校验。如果是长连接，会检查连接是否空闲超时；如果是短连接，则建立新连接。

分析器：进行词法分析和语法分析，识别出这是一条 UPDATE 语句，表名是 t，字段是 name 等。如果语法有误，直接报错。

优化器：决定使用哪个索引（例如根据 id=1 选择主键索引），并生成执行计划。

执行器：开始执行语句。首先检查当前用户是否有对表 t 的更新权限。如果有，调用 InnoDB 引擎接口。

2. InnoDB 引擎层核心操作

进入 InnoDB 引擎层（图中蓝色区域）后：

步骤 1 & 3：加载与更新 Buffer Pool

加载数据：InnoDB 不会直接修改磁盘上的 .ibd 文件。它会先将 id=1 的数据页从磁盘加载到内存中的 Buffer Pool。

更新内存：在 Buffer Pool 中，将 name 字段的值从 zhuge 修改为 zhuge666。此时，内存中的数据已经是新的了，但磁盘上的数据还是旧的。

步骤 2：写入 Undo Log (回滚日志)

作用：为了实现事务的原子性（Atomicity）。

操作：在修改内存数据之前，必须先把旧值（name=zhuge）记录到 Undo Log 中。

意义：如果后续事务执行失败需要回滚，或者为了支持 MVCC（多版本并发控制）实现快照读，数据库可以通过 Undo Log 将数据恢复到修改前的状态。

步骤 4 & 5：写入 Redo Log (重做日志)

作用：为了保证事务的持久性（Durability），即崩溃恢复能力。

WAL 技术：这是 Write-Ahead Logging 技术的体现。因为随机更新磁盘上的数据页（Page）非常慢，而顺序写日志非常快。

操作：

先在内存中的 Redo Log Buffer 记录这次修改（物理修改：在某页做了啥改动）。

随后（通常在事务提交时或每秒定时刷盘），将 Redo Log 顺序写入 磁盘上的 Redo Log 文件。

关键点：只要 Redo Log 落盘了，即使此时系统宕机，重启后也能通过 Redo Log 把 Buffer Pool 中没来得及写入磁盘的数据“重做”回来。

redo log buffer 就是一块内存，用来先存 redo 日志的。也就是说，在执行第一个insert 的时候，数据的内存被修改了，redo log buffer 也写入了日志。但是，真正把日志写到 redo log 文件（文件名是 ib_logfile+ 数字），是在执行 commit语句的时候做的。

步骤 6 & 7：写入 Binlog 与 两阶段提交

Binlog (归档日志)：这是 Server 层的日志，记录所有表的逻辑修改（如 update t set name='zhuge666' where id=1）。它主要用于主从复制和数据恢复。

两阶段提交：为了保证 Redo Log 和 Binlog 的逻辑一致性，InnoDB 采用了两阶段提交协议：

Prepare 阶段：将 Redo Log 写入磁盘（处于 prepare 状态）。

Write Binlog：将 Binlog 写入磁盘。

Commit 阶段：将 Redo Log 标记为 commit 状态。

意义：如果只写了 Redo Log 没写 Binlog 就宕机，恢复后事务会被回滚；如果写了 Binlog 没写 Redo Log，恢复后事务无法重做。只有两者都成功，事务才算真正完成。

步骤 8：后台刷盘

这是一个异步过程。InnoDB 有一个后台线程，会在系统空闲时，将 Buffer Pool 中被修改过的“脏页”随机写入到磁盘的 .ibd 文件中。这一步不需要等待事务提交才做，是为了释放内存压力。

内部结构

一条sql是如何执行的？

此图是分析器对sql的分析过程步骤：

SQL语句经过分析器分析之后，会生成一个这样的语法树

客户端

连接工具（Navacat、SQLyog、JDBC）都归纳为MySQL客户端(Client)，主要用于发送执行sql语句的请求。

服务端

Server 层

- 负责处理 SQL 语句、解析、优化、缓存等。

- 负责权限管理、用户认证等。

- 提供了各种 SQL 函数和存储过程。

- 提供了复制、备份、恢复等高级功能。

- Server 层有自己的日志系统，称为 binlog（归档日志）。binlog 记录了所有修改数据库数据的 SQL 语句（如 INSERT、UPDATE、DELETE 等）的信息，但不包括 SELECT 和 SHOW 这类查询语句。binlog 主要用于复制和恢复操作。

存储引擎层

- 负责数据的存储和检索。

- MySQL 支持多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种引擎都有其特点和适用场景。

- InnoDB 是 MySQL 的默认存储引擎，它支持事务、行级锁定和外键约束。InnoDB 有自己的日志系统，称为 redo log（重做日志） 和 undo log（撤销日志）。redo log 用于保证事务的持久性，在数据库崩溃后可以用来恢复数据；undo log 用于支持事务的原子性和多版本并发控制（MVCC）。

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

其他

连接为什么用Bio？

性能瓶颈不在网络连接，而在磁盘IO

为什么不用Nio，事件驱动？

在BIO下，每个连接绑定一个线程，线程的本地缓存命中率极高；若改用epoll异步回调，线程切换频繁，CPU的L1/L2缓存频繁失效，性能反而骤降

缓存的优化，由于表时常更新，缓存用处不大还会影响并发性能

权限校验的兜底方案，有些数据在执行器阶段才得到

执行计划都是在优化器做的