Mysql

2024-09-03 18:14:20   55  举报





AI智能生成

Mysql 2024年4月24日02:00:08：补充幻读的解决原理。 2024年7月13日00:45:21：这是久违的一次大更新，基本上完善够用了。 ①进行内容分类，基础、高级、运维 ②基础部分补充了各种常用的函数、sql使用建议 ③高级部分锁的内容进行了完善 ④添加的运维的部分唯一美中不足的：存储过程、视图、游标那块我属实是有点抗拒，所以就草草的添了几笔。 2024年9月3日18:14:03：调整思维导图的样式风格。

MySQL

MySQL总结

作者其他创作

大纲/内容

实战内容

数据库规范设计的6个阶段

需求分析：分析用户的需求，包括数据、功能和性能需求；

概念结构设计：主要采用E-R模型进行设计，包括画E-R图；

逻辑结构设计：通过将E-R图转换成表，实现从E-R模型到关系模型的转换；

数据库物理设计：主要是为所设计的数据库选择合适的存储结构和存取路径；

数据库的实施：包括编程、测试和试运行；

数据库运行与维护：系统的运行与数据库的日常维护

分库分表

类别

水平切分

垂直切分

Sharding策略

哈希取模: hash(key) % NUM_DB

范围: 可以是 ID 范围也可以是时间范围

映射表: 使用单独的一个数据库来存储映射关系

Sharding策略带来的问题及解决方案

事务问题

使用分布式事务来解决，比如 XA 接口

链接

可以将原来的 JOIN 分解成多个单表查询，然后在用户程序中进行 JOIN

ID唯一性

使用全局唯一 ID: GUID

为每个分片指定一个 ID 范围

分布式 ID 生成器 (如 Twitter 的 Snowflake 算法)

SQL进阶实战

分页\深分页

强制索引

函数使用

分组

子查询

表连接

组合查询

视图

存储过程

游标

触发器

基础

SQL

分类

DDL

数据库三范式

第一

属性不可再分，几乎所有数据库都满足

第二

非主键字段依赖于主键字段

第三

在非主键字段依赖于主键字段的前提下，强制不能传递依赖，必须直接依赖

表关系

一对一

没咋见过，大都是一张表字段太多了要拆开来提高性能

一对多

两张表

在多的一方建立外键，指向一的一方的主键（这个不是真建，只是想表达这个意思，在业务层解决）

多对多

三张表，一张关联表，关联两个表的主键

表设计原则

数据表的个数越少越好

数据表中的字段个数越少越好

数据表中联合主键的字段个数越少越好

数据类型

数值型

整型

TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT

tips：定义表结构时指定的宽度对实际的存储并没有影响，只会影响查询时显示的宽度。

浮点

FLOAT、DOUBLE

DECIMAL

tips：小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE

字符型

char（定长）

这个性能会比较高

varchar（变长）

二进制、文本

时间日期型

DATETIME（8字节）

到9999年，但是没有时区概念

TIMESTAMP（4字节）

到2038年，有时区概念，项目中建议使用这个

YEAR

TIME

DATE

Json(mysql8)

JSON对象

JSON数组

约束

Not NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

DEFAULT

CHECK

PS：

用的比较多的就这几个，对于外键，其实也没用过在数据库层面上，基本都是在业务层解决，直接在数据库上加外键太耗性能了

建表时，为了避免null的出现，可以加 not null default '' 或 default 0。null是一种特殊值效率不高。影响提高索引效果。

自增

AUTO_INCREMENT

☆删除表操作

DROP TABLE

DML

新增、修改、删除记录

这个个人没怎么记忆，项目中根本就没有自己手写过这类SQL，直接调ORM框架封装的方法得了，够用

DQL

查询记录

基本查询

select ... from ...

别名

select c1 as c from tab1 as t

select c1 c from tab t

去重

select distinct ... from ...

着重号 ``

解决命名和系统关键字冲突的情况

条件查询

没啥好说的，where

常用运算符

算术运算

比较运算

IS NULL

IS NOT NULL

BETWEEN AND

NOT IN

“%”：匹配0个或多个字符。

“_”：只能匹配一个字符。

逻辑运算符

not

and

排序查询（order by）

ASC（ascend）: 升序，默认升序

DESC（descend）:降序

单列排序

☆多列排序

①可以使用不在SELECT列表中的列排序。

②在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。

分页查询（LIMIT [位置偏移量,] 行数）

limit a,b

网上记忆方法花里胡哨的，个人屡了一下，就是（a,a+b]，第a行条记录后面的b行记录

分页显式公式：（当前页数-1）* 每页条数，每页条数

SELECT * FROM table
LIMIT(PageNo - 1) * PageSize, PageSize;

这里再深入说一下，分页操作不是指查询出a-b之间的数据，而是把a+b条记录查出来，再减去前面的a条，所以遇到深度分页的情况下需要进行特殊的优化（有一说一我们在项目中除了特定的需求需要，一般都会对分页上限做限制的，防止出现深度分页，b站的个人中心里很多分页操作就禁止了深度分页）

多表查询

查询分类

自连接

没用过

内连接

取交集

外连接

左外连接

整个左边

右外连接

整个右边

满外连接（MySQL不支持）

全都要

图例

SQL99新特性

自然连接()

自动查询两张连接表中所有相同的字段，然后进行等值连接

示例

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

USING连接

支持使用 USING 指定数据表里的同名字段进行等值连接。但是只能配合JOIN一起使用。

示例

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id);

联合查询（列数、对应的数据类型要一致）（UNION）

作用：合并查询结果

union

会去重

union all（尽量用这个）

不会去重

函数

单行函数

数值函数

基本函数

ABS(x)

LEAST(e1,e2,e3…)

GREATEST(e1,e2,e3…)

MOD(x,y)

RAND()

ROUND(x)

SQRT(x)

三角函数

指数对数函数

弧度函数

进制转换

字符串函数

CONCAT(s1,s2,......,sn)

连接s1,s2,......,sn为一个字符串

REPLACE(str, a, b)

用字符串b替换字符串str中所有出现的字符串a

UPPER(s) 或 UCASE(s)

LOWER(s) 或LCASE(s)

TRIM(s)

日期和时间函数

获取日期、时间

日期与时间戳的转换
函数

获取月份、星期、星期数、天数等函数

日期的操作函数

时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

流程控制函数

加密与解密函数

MySQL信息函数

VERSION()

CONNECTION_ID()

DATABASE()，SCHEMA()

USER()，CURRENT_USER()、SYSTEM_USER()， SESSION_USER()

CHARSET(value)

COLLATION(value)

聚合函数

什么是聚合函数

聚合函数作用于一组数据，并对一组数据返回一个值。

聚合函数类型

count

avg

sum

min

max

PS：NULL值不参与聚合运算

分组查询（Group by）

Group by ... having...

执行顺序 where>group by >having

where不能用聚合函数，having可以

HAVING 不能单独使用，必须要跟 GROUP BY 一起使用

tips：

①SELECT中出现的非组函数的字段必须声明在GROUP BY中。

反之，GROUP BY中声明的字段可以不出现在SELECT中

②GROUP BY声明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面

Having 和 Where的区别

用法

WHERE

先筛选数据再关联，执行效率高

HAVING

可以使用分组中的计算函数

缺点

WHERE

不能使用分组中的计算函数进行筛选

HAVING

在最后的结果集中进行筛选，执行效率较低

开发中的选择

子查询

tips：子查询先于主查询执行，子查询的结果被主查询（外查询）使用

单行子查询

等等

多行子查询

ANY

ALL

SOME

EXISTS 与 NOT EXISTS 关键字

NOT EXISTS

关键字表示如果不存在某种条件，则返回TRUE，否则返回FALSE

EXISTS

在子查询中不存在满足条件的行

条件返回 FALSE

继续在子查询中查找

在子查询中存在满足条件的行

不在子查询中继续查找

条件返回 TRUE

查询结构总结：

#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

#方式2：
SELECT ...,....,...
FROM ... JOIN ...
ON 多表的连接条件
JOIN ...
ON ...
WHERE 不包含组函数的过滤条件
AND/OR 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...

执行顺序：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

DCL

用户权限控制

这个我作为开发也没确实没机会接触

视图

视图的作用：可以将视图理解为存储起来的 SELECT 语句，对查询语句的复用

CREATE VIEW 视图名称
AS 查询语句

存储过程

作用：提高了sql语句的重用性

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名参数类型,...)
[characteristics ...]
BEGIN
存储过程体
END

调用

call

变量、流程控制、游标

触发器

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块

MySQL8新特性

MySQL 8中新增了隐藏索引和降序索引

更完善的JSON支持

新增了 caching_sha2_password 授权插件

支持原子数据定义语言（DDL）

默认的字符集由 latin1 更改为 utf8mb4

开始支持窗口函数

为什么？

需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

分类

静态窗口函数

介绍

静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同

动态窗口函数

介绍

动态窗口函数的窗口大小会随着记录的不同而变化

细分

序号函数

ROW_NUMBER()函数

ROW_NUMBER()函数能够对数据中的序号进行顺序显示

RANK()函数

使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号，比如序号为1、1、3

DENSE_RANK()函数

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如序号为1、1、2。

分布函数

PERCENT_RANK()函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算。

(rank - 1) / (rows - 1)

CUME_DIST()函数

CUME_DIST()函数主要用于查询小于或等于某个值的比例。

前后函数

LAG(expr,n)函数

LAG(expr,n)函数返回当前行的前n行的expr的值。

LEAD(expr,n)函数

LEAD(expr,n)函数返回当前行的后n行的expr的值。

首尾函数

FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值。

LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值。

其他函数

NTH_VALUE(expr,n)函数

NTH_VALUE(expr,n)函数返回第n个expr的值。

NTILE(n)函数

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

语法

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

高级

MySQL逻辑架构

架构分层

连接层

建立tcp连接、认证授权

服务层

sql解析、优化

存储引擎层

拿数据

执行一条 SQL 查询语句，期间发生了什么？

连接器：建立连接，管理连接、校验用户身份

查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块

解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型

执行 SQL：执行 SQL 共有三个阶段

预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列

优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划

执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端

进阶：Innodb的Buffer Pool

作用

减少磁盘IO

InnoDB 存储引擎是以页为单位来管理存储空间的，我们进行的增删改查操作其实本质上都是在访问页
面（包括读页面、写页面、创建新页面等操作）。而磁盘 I/O 需要消耗的时间很多，而在内存中进行操
作，效率则会高很多，为了能让数据表或者索引中的数据随时被我们所用，DBMS 会申请占用内存来作为
数据缓冲池，在真正访问页面之前，需要把在磁盘上的页缓存到内存中的 Buffer Pool 之后才可以访
问。

内容

数据页、索引页、插入缓冲、锁信息、自适应 Hash 和数据字典
信息等

特性

预读

存储引擎

InnoDB（默认）

特征

外键（重点记忆）

事务支持（重点记忆）

行锁（重点记忆）

热备份，崩溃恢复

MVCC

聚簇索引

存储方式

最小存储单元是页，一个页的大小是16KB

.ibd文件

MyISAM（没用过）

表锁

不支持事务

不支持外键

没有热备份

Memory（没用过）

内存中，不能持久化

索引

索引是一种用于快速查询和检索数据的数据结构

好处

查询快，减少磁盘IO

缺点

需要维护，占磁盘空间

索引的数据结构

B树

B+树

PS：问的比较多的是和B树、红黑树相比，优点在哪里

①数据存储在叶子结点

②叶子结点之间有双向的指针，提高查询效率

③层级少，高度低，查的快

Hash

不支持范围查询

不支持模糊查询

有hash冲突问题

全文索引

Innodb有，但是我没用过

R树

MyISAM的，用的比较少

聚集索引与非聚集索引(二级索引)（聚簇不聚簇不是索引的类型，是数据的存储方式）

聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引（叶子结点存放的是数据）

.ibd文件

非聚集索引即索引结构和数据分开存放的索引（叶子结点存放的是数据的主键，需要通过主键回表查询）

聚集索引的选择

存在主键，主键就是聚集索引

不存在主键，将第一个使用唯一索引的作为聚集索引

没有主键或合适的唯一索引，生成隐藏id作为聚集索引

回表查询

从二级索引中获得数据的主键值，再去聚集索引中查找，尽量避免回表查询

索引类型

主键索引

PRIMARY KEY

唯一索引

UNIQUE

普通索引

INDEX

全文索引

FULLTEXT

索引的设计、使用原则

适合创建索引的情况

具有唯一性的字段

频繁作为where条件使用的列

经常用于group by和order by的列

Distinct去重的列

最频繁的列放到联合索引的左侧

在多个字段都要创建索引的情况下，联合索引优于单值索引

限制索引的数目

不适用索引的情况

在where中使用不到的字段，不要设置索引

数据量小的表最好不要使用索引

有大量重复数据的列上不要建立索引

.避免对经常更新的表创建过多的索引

不建议用无序的值作为索引

删除不再使用或者很少使用的索引

不要定义冗余或重复的索引

不要在索引列上进行运算

Innodb数据存储结构

页

InnoDB将数据划分为若干个页，一个页中可以存储多个行记录，InnoDB中页的大小默认为 16KB。页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的
记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个
页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历
该槽对应分组中的记录即可快速找到指定的记录。

内部结构

文件头

38字节

文件头，描述页的信息

页头

56字节

页头,页的状态信息

行记录

最大最小记录

26字节

最大和最小记录，这是两个虚拟的行记录

用户记录

不确定

用户记录，存储行记录内容

空闲空间

不确定

空闲记录，页中还没有被使用的空间

页目录

不确定

页目录，存储用户记录的相对位置

文件尾

8字节

文件尾,校验页是否完整

性能分析

SQL执行频率

慢查询日志（实际工作不怎么用）

profile查看sql执行成本

navicat执行完返回的结果分析用的这个

☆分析sql语句explain（DESC）

查询序列化，id相同执行顺序从上到下；不同值越大优先级越大，NULL为最后执行

select_type

simple

简单表，不使用表连接或者子查询

PRIMARY

复杂查询中最外层的 select

union

UNION 中的第二个或者后面的查询语句

SUBQUERY

SELECT/WHERE之后包含了子查询

derived

包含在 from 子句中的子查询。

......

table

涉及到的表名

partitions

如果查询的是基于分区的表，该字段显示查询将会访问的分区

☆type

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见上图中的蓝
色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴
开发手册要求）

NULL

MySQL 能在优化阶段分解查询语句，在执行阶段不需访问表或索引

system

该表只有一行，是 const 的特例

const

该表最多有一个匹配的行，MySQL 能对查询的某部分进行优化并将其转化成一个常量，因为只有一个匹配的行，所以速度非常快

eq_ref

primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录

ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会得到多个结果

range

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

index

扫描全索引就能拿到结果，一般是扫描某个二级索引，对二级索引的叶子节点进行遍历和扫描，所以速度较慢，而二级索引一般比较小，所以比 ALL 快

ALL

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

☆possible_key

显示可能应用在这张表上的索引，一个或多个。有时显示的是 NULL 值，是因为 MySQL 判断表中数据不多，不需要使用索引查询，选择全表查询

☆key

实际使用的索引，如果为NULL，则没有使用索引。如果强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。

☆key_len

表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长
度，在不损失精确性的前提下，长度越短越好。

ref

☆rows

MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，
可能并不总是准确的。

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

☆Extra

一些额外的信息

索引与查询优化

查询优化

插入数据

批量插入数据用load

主键顺序插入（自增）性能高于非顺序插入（UUID）

非顺序插入会导致页分裂

Order by优化

using filesort

不通过索引，全表扫

using index

通过索引

通过索引提高效率，遵循最左前缀

Group by 优化

通过索引提高效率，遵循最左前缀

Limit越往后查询效率越低（深分页）

通过覆盖索引+子查询优化

limit偏移量转化为条件

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。

COUNT()统计

使用count(1)或者count(*)

update 优化

条件用索引，防止行锁升级为表锁

关联查询优化

LEFT JOIN 时，选择小表作为驱动表，大表作为被驱动表。减少外层循环的次数。

INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略。

能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)。不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

子查询优化

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询
要快，如果查询中使用索引的话，性能就会更好。

给字符串添加索引

前缀索引

MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字
符串。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

索引下推

只能用于二级索引(secondary index)当SQL使用覆盖索引时，不支持ICP优化方法。

索引优化

索引失效的情况

where没有索引列

未正确使用联合索引（不符合最左前缀）导致索引失效

计算、函数、类型转换(自动或手动)导致索引失效

范围条件右边的列索引失效

不等于(!= 或者<>)索引失效

is null可以使用索引，is not null无法使用索引

like以通配符%开头索引失效

OR 前后存在非索引的列，索引失效

数据库和表的字符集统一使用utf8mb4。。不
同的字符集进行比较前需要进行转换会造成索引失效。

其他优化

整体优化思路

数据库选型

合理的表结果设计

字段

类型

索引

约束

存储引擎

查询优化

库级优化

读写分离

数据一致性

宕机问题

数据分片（分库分表）

水平分

垂直分

问题

事务

主键唯一性

JOIN

事务（Innodb）

事务的定义

一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务的使用

显示事务

START TRANSACTION 或者 BEGIN ，作用是显式开启一个事务。

COMMIT;

ROLLBACK;

隐式事务

ACID

原子性（A）

原子性保证了事务的多个操作要么都生效要么都不生效，不会存在中间状态

使用 undo log(回滚日志) 来保证事务的原子性。

undo log

UNDO LOG 称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性

分类

insert undo log

update undo log

undo log是逻辑日志，对事务回滚时，只是将数据库逻辑地恢复到原来的样子。

持久性（D）

持久性保证了一旦事务生效，就不会因为任何原因而导致其修改的内容被撤销或者丢失

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，好像还有服务层的binlog？

redo log

REDO LOG 称为重做日志，提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持
久性

组成部分

重做日志的缓冲 (redo log buffer) ，保存在内存中，是易失的。

重做日志文件 (redo log file) ，保存在硬盘中，是持久的。

redo log是物理日志，记录的是数据页的物理变化，undo log不是redo log的逆过程。

隔离性（I）

隔离性保证了并发情况下每个事务各自读、写的数据互相独立，不会彼此影响

MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性

MySQL并发事务的情况分类

读-读情况

读取操作本身不会对记录有任何影响，并不会引起什么
问题，所以允许这种情况的发生。

写-写情况

在这种情况下会发生脏写的问题，任何一种隔离级别都不允许这种问题的发生。所以在多个未提交事务
相继对一条记录做改动时，需要让它们排队执行，这个排队的过程其实是通过锁来实现的。

读-写或写-读情况

读-写或写-读，即一个事务进行读取操作，另一个进行改动操作。这种情况下可能发生脏读、不可重
复读、幻读的问题。
各个数据库厂商对 SQL标准的支持都可能不一样。比如MySQL在 REPEATABLE READ 隔离级别上就已经
解决了幻读问题。

事务并发带来的问题

脏写

两个事务的写写情况

所有的存储引擎都不允许这种情况的出现，直接用锁解决了

脏读

一个事务读到另外一个事务未提交的数据

使用读已提交解决

不可重复读

一个事务中对同一数据的多次读取结果不一致

使用可重复读解决

幻读

一个事务中对表的多次数据查询结果不一致

使用串行化解决

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次查询同一条查询语句（DQL）时，记录发现记录增多或减少了

隔离级别

Mysql默认隔离级别为可重复读

不同隔离级别以及幻读、不可重复读、脏读等问题都只是表面现象，是各种锁在不同加锁时间上组合应用所产生的结果，以锁为手段来实现隔离性才是数据库表现出不同隔离级别的根本原因

READ-UNCOMMITTED(读取未提交)

只加写锁

最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

READ-COMMITTED(读取已提交)

加读锁、写锁，读锁在查询操作执行完立即释放

允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

脏读的解决原理

一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就
是避免了脏读现象；

REPEATABLE-READ(可重复读)

加读锁、写锁

对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生

细说这个级别下的幻读解决方案

读写事务下可以靠mvcc实现不加锁避免幻读

采用加锁方式的话，读-写操作彼此需要排队执行，影响性能

不可重复读、幻读的解决原理

一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读和幻读的问题。

SERIALIZABLE(可串行化)

加读锁、写锁、范围锁

最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读，但是性能最差。

锁

锁的分类

根据读写类型

共享锁/读锁

针对同一份数据，多个事务的读操作可以同时进行而不会
互相影响，相互不阻塞的。

排他锁/写锁

当前写操作没有完成前，它会阻断其他写锁和读锁。这样
就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

tips：对于 InnoDB 引擎来说，读锁和写锁可以加在表上，也可以加在行上。

根据粒度

全局锁

全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后
其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结
构等）和更新类事务的提交语句。全局锁的典型使用场景是：做全库逻辑备份。

表级锁

表锁

表共享锁

表独占锁

意向锁

意向共享

事务有意向对表中的某些行加共享锁（S锁）

意向排他

事务有意向对表中的某些行加排他锁（X锁）

tips：InnoDB 支持多粒度锁（multiple granularity locking），它允许行级锁与表级锁共存，而意向
锁就是其中的一种表锁。

tips：意向锁是由存储引擎自己维护的，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，
InooDB 会先获取该数据行所在数据表的对应意向锁。

自增锁

数据的插入方式

简单插入

可以预先确定要插入的行数（当语句被初始处理时）的语句。

批量插入

事先不知道要插入的行数（和所需自动递增值的数量）的语句。

混合模式插入

自增锁的三种模式

innodb_autoinc_lock_mode = 0(“传统”锁定模式)

所有类型的insert语句都会获得一个特殊的表级AUTO-INC锁，并发能力不强

innodb_autoinc_lock_mode = 1(“连续”锁定模式)，8.0之前默认

批量插入用表锁，简单插入用轻量级锁

innodb_autoinc_lock_mode = 2(“交错”锁定模式)，8.0开始默认

在此锁定模式下，自动递增值保证在所有并发执行的所有类型的insert语句中是唯一且单调递增的。但
是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能
不是连续的。

元数据锁

系统自动控制，显示使用

当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写
锁。

行级锁（通过对索引项加锁实现）

记录锁（Record Locks）

锁记录

也有读写锁之分

间隙锁（Gap Locks）

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方
案解决，也可以采用加锁方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读
取操作时，那些幻影记录尚不存在，我们无法给这些幻影记录加上记录锁。InnoDB提出了一种称之为
Gap Locks 的锁，不允许别的事务在当前读取的记录的id值前的记录前边的间隙插入新记录，

gap锁的提出仅仅是为了防止插入幻影记录而提出的

临建锁Next-key lock

有时候我们既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新记录，所以InnoDB就提
出了一种称之为 Next-Key Locks 的锁，Next-Key Locks是在存储引擎 innodb 、事务级别在可重复读的情况下使用的数据库锁，
innodb默认的锁就是Next-Key locks

插入意向锁

InnoDB规
定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是
现在在等待。InnoDB就把这种类型的锁命名为插入意向锁

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。
事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

根据态度

悲观锁

顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身
的锁机制来实现，从而保证数据操作的排它性。

乐观锁

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新
的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过
程序来实现。在程序上，我们可以采用版本号机制或者 CAS机制实现。乐观锁适用于多读的应用类型，
这样可以提高吞吐量。

加锁方式

隐式加锁

A InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚簇索引的B+Tree中。

B 在操作一条记录前，首先根据记录中的trx_id检查该事务是否是活动的事务(未提交或回滚)。如果是活
动的事务，首先将隐式锁转换为显式锁 (就是为该事务添加一个锁)。

C 检查是否有锁冲突，如果有冲突，创建锁，并设置为waiting状态。如果没有冲突不加锁，跳到E。

D. 等待加锁成功，被唤醒，或者超时。

E. 写数据，并将自己的trx_id写入trx_id字段。

显示加锁

显示加共享锁

select .... lock in share mode

显示加排它锁

select .... for update

其他

死锁

解决策略

直接进入等待，直到超时。这个超时时间可以通过参数
innodb_lock_wait_timeout 来设置。

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级
排他锁的事务进行回滚），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为
on ，表示开启这个逻辑。

暴力解决，kill掉sql

InnoDB 存储引擎中的锁结构

MVCC

什么是MVCC

多版本并发控制。顾名思义，MVCC 是通过数据行的多个版
本管理来实现数据库的并发控制。这项技术使得在InnoDB的事务隔离级别下执行一致性读操作有了保
证。换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值，这样
在做查询的时候就不用等待另一个事务释放锁。

当前读与快照读

当前读

针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

快照读（一致性读）

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。

记录隐藏字段

trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的事务id 赋值给
trx_id 隐藏列。

roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志中，然
后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

ROW_ID

隐藏主键，有主动设置主键则不会生成

undo log版本链

每次对记录进行改动，都会记录一条undo日志，每条undo日志也都有一个 roll_pointer 属性可以将这些 undo日志
都连起来，串成一个链表

对记录每次更新后，都会将旧值放到一条 undo日志中，就算是该记录的一个旧版本，随着更新次数
的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为版本链，版
本链的头节点就是当前记录最新的值。
每个版本中还包含生成该版本时对应的事务id 。

Read View

在 MVCC 机制中，多个事务对同一个行记录进行更新会产生多个历史快照，这些历史快照保存在 Undo Log里。如果一个事务想要查询这个行记录，需要读取哪个版本的行记录呢?这时就需要用到 Readview 了，它帮我们解决了行的可见性问题。
ReadView 就是事务A在使用MVCC机制进行快照读操作时产生的读视图。当事务启动时，会生成数据库系统当前的一个快照，InnoDB为每个事务构造了一个数组，用来记录并维护系统当前活跃事务的ID(“活跃”指的就是，启动了但还没提交)。

Read View的设计思路

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录
的最新版本就好了。

使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。

使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务，都必须保证读到已经提交了的事务修改
过的记录。假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问
题就是需要判断一下版本链中的哪个版本是当前事务可见的，这是ReadView要解决的主要问题。

Read View的主要内容

creator_trx_id ，创建这个 Read View 的事务 ID。

trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

up_limit_id ，活跃的事务中最小的事务 ID。

low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系
统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

ReadView的规则

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

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

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

如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判
断一下trx_id属性值是不是在 trx_ids 列表中。

如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。

如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

MVCC整体操作流程

1. 首先获取事务自己的版本号，也就是事务 ID；

2. 获取 ReadView；

3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

5. 最后返回符合规则的数据。

tips：在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次
Read View。此时同样的查询语句都会重新获取一次 Read View，这时如果 Read View 不同，就可能产生
不可重复读或者幻读的情况。当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会
获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View。

总结：MVCC的实现原理就是记录的隐藏字段+Undo log版本链+Read View规则，核心点在于 ReadView 的原理， READ COMMITTD 、 REPEATABLE READ 这两个隔离级别的一个很大不同就是生成ReadView的时机不同

READ COMMITTD 在每一次进行普通SELECT操作前都会生成一个ReadView

REPEATABLE READ 只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复
使用这个ReadView就好了。

一致性（C）

系统中所有的数据都是符合期望的，且相互关联的数据之间不会产生矛盾

保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

补充：纠正网上对于ACID的说法：ACID这四种特征并不正交，A、I、D是手段，C是目的。前者是因后者是果，弄到一块完全是为了拼凑的单词缩写

日志

undo log（前面说过了不说）

redo log（前面说过了不说）

慢查询日志（前面说过了不说）

☆二进制日志(bin log)

定义

binlog即binary log，二进制日志文件，也叫作变更日志（update log）。它记录了数据库所有执行的
DDL 和 DML 等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、
show等）。MySQL 8 默认开启

作用

数据恢复

数据复制

特点

默认是1GB

文件是伪SQL的形式

binlog的形式

ROW

5.1.5版本的MySQL才开始支持row level 的复制，它不记录sql语句上下文相关信息，仅保存哪条记录被修
改。
优点：row level 的日志内容会非常清楚的记录下每一行数据修改的细节。而且不会出现某些特定情况下
的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

Statement这是默认的binlog格式

每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。

Mixed

Statement与Row的结合

bin log 和 redo log的比较

redo log 它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于 InnoDB 存储引擎层产生
的。

而 binlog 是逻辑日志，记录内容是语句的原始逻辑，类似于“给 ID=2 这一行的 c 字段加 1”，属于
MySQL Server 层。

bin log 和 redo log之间逻辑一致性的解决

InnoDB存储引擎使用两阶段提交方案

中继日志relay log

中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读
取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫
中继日志。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主
从服务器的数据同步。

内部优化