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总结

作者其他创作

大纲/内容

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

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

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

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

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

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

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

水平切分

垂直切分

类别

哈希取模: hash(key) % NUM_DB

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

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

Sharding策略

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

事务问题

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

链接

使用全局唯一 ID: GUID

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

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

ID唯一性

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

分库分表

分页\\深分页

强制索引

函数使用

分组

子查询

表连接

组合查询

视图

存储过程

游标

触发器

SQL进阶实战

实战内容

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

第一

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

第二

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

第三

数据库三范式

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

一对一

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

两张表

一对多

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

多对多

表关系

数据表的个数越少越好

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

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

表设计原则

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

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

整型

FLOAT、DOUBLE

浮点

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

DECIMAL

数值型

这个性能会比较高

char（定长）

varchar（变长）

二进制、文本

字符型

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

DATETIME（8字节）

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

TIMESTAMP（4字节）

YEAR

TIME

DATE

时间日期型

JSON对象

JSON数组

Json(mysql8)

数据类型

Not NULL

UNIQUE

PRIMARY KEY

FOREIGN KEY

DEFAULT

CHECK

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

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

PS：

约束

AUTO_INCREMENT

自增

DROP TABLE

☆删除表操作

DDL

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

新增、修改、删除记录

DML

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

逻辑运算符

常用运算符

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

DESC（descend）:降序

单列排序

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

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

☆多列排序

排序查询（order by）

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

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

没用过

自连接

取交集

内连接

整个左边

左外连接

整个右边

右外连接

全都要

满外连接（MySQL不支持）

图例

外连接

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

示例

自然连接()

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

USING连接

SQL99新特性

作用：合并查询结果

会去重

union

不会去重

union all（尽量用这个）

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

查询分类

多表查询

ABS(x)

RAND()

ROUND(x)

SQRT(x)

基本函数

三角函数

指数对数函数

弧度函数

进制转换

数值函数

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

UPPER(s) 或 UCASE(s)

LOWER(s) 或LCASE(s)

TRIM(s)

字符串函数

获取日期、时间

日期与时间戳的转换函数

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

日期的操作函数

时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

日期和时间函数

流程控制函数

加密与解密函数

VERSION()

CONNECTION_ID()

DATABASE()，SCHEMA()

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

CHARSET(value)

COLLATION(value)

MySQL信息函数

单行函数

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

什么是聚合函数

count

avg

sum

min

max

PS：NULL值不参与聚合运算

聚合函数类型

聚合函数

函数

Group by ... having...

执行顺序 where>group by >having

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

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

①SELECT中出现的非组函数的字段必须声明在GROUP BY中。反之，GROUP BY中声明的字段可以不出现在SELECT中

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

tips：

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

WHERE

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

HAVING

用法

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

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

缺点

开发中的选择

Having 和 Where的区别

分组查询（Group by）

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

等等

单行子查询

ANY

ALL

SOME

多行子查询

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

NOT EXISTS

条件返回 FALSE

继续在子查询中查找

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

不在子查询中继续查找

条件返回 TRUE

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

EXISTS

EXISTS 与 NOT EXISTS 关键字

查询记录

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

执行顺序：

查询结构总结：

DQL

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

用户权限控制

DCL

分类

SQL

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

CREATE VIEW 视图名称AS 查询语句

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

call

调用

变量、流程控制、游标

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

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

更完善的JSON支持

新增了 caching_sha2_password 授权插件

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

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

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

为什么？

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

介绍

静态窗口函数

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

动态窗口函数

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

ROW_NUMBER()函数

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

RANK()函数

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

DENSE_RANK()函数

序号函数

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

PERCENT_RANK()函数

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

CUME_DIST()函数

分布函数

前后函数

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

FIRST_VALUE(expr)函数

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

LAST_VALUE(expr)函数

首尾函数

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

NTILE(n)函数

其他函数

细分

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

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

语法

开始支持窗口函数

MySQL8新特性

基础

建立tcp连接、认证授权

连接层

sql解析、优化

服务层

拿数据

存储引擎层

架构分层

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

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

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

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

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

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

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

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

减少磁盘IO

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

作用

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

内容

预读

特性

进阶：Innodb的Buffer Pool

MySQL逻辑架构

外键（重点记忆）

事务支持（重点记忆）

行锁（重点记忆）

热备份，崩溃恢复

MVCC

聚簇索引

特征

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

.ibd文件

存储方式

InnoDB（默认）

表锁

不支持事务

不支持外键

没有热备份

MyISAM（没用过）

内存中，不能持久化

Memory（没用过）

存储引擎

查询快，减少磁盘IO

好处

需要维护，占磁盘空间

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

B树

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

①数据存储在叶子结点

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

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

B+树

不支持范围查询

不支持模糊查询

有hash冲突问题

Hash

Innodb有，但是我没用过

全文索引

MyISAM的，用的比较少

R树

索引的数据结构

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

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

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

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

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

聚集索引的选择

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

回表查询

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

主键索引

唯一索引

INDEX

普通索引

FULLTEXT

索引类型

具有唯一性的字段

频繁作为where条件使用的列

经常用于group by和order by的列

Distinct去重的列

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

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

限制索引的数目

适合创建索引的情况

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

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

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

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

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

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

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

不要在索引列上进行运算

不适用索引的情况

索引的设计、使用原则

索引

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

38字节

文件头，描述页的信息

文件头

56字节

页头

26字节

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

最大最小记录

不确定

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

用户记录

行记录

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

空闲空间

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

页目录

8字节

文件尾

内部结构

页

Innodb数据存储结构

SQL执行频率

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

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

profile查看sql执行成本

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

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

simple

复杂查询中最外层的 select

PRIMARY

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

SELECT/WHERE之后包含了子查询

SUBQUERY

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

derived

......

select_type

涉及到的表名

table

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

partitions

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

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

NULL

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

system

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

const

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

eq_ref

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

ref

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

range

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

index

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

☆type

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

☆possible_key

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

☆key

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

☆key_len

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

☆rows

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

filtered

一些额外的信息

☆Extra

☆分析sql语句explain（DESC）

性能分析

批量插入数据用load

插入数据

非顺序插入会导致页分裂

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

不通过索引，全表扫

using filesort

通过索引

using index

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

Order by优化

Group by 优化

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

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

limit偏移量转化为条件

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

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

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

问题

数据分片（分库分表）

库级优化

整体优化思路

其他优化

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

事务的定义

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

COMMIT;

ROLLBACK;

显示事务

隐式事务

事务的使用

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

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

insert undo log

update undo log

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

undo log

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

原子性（A）

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

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

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

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

组成部分

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

redo log

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

持久性（D）

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

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

读-读情况

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

写-写情况

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

读-写或写-读情况

MySQL并发事务的情况分类

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

两个事务的写写情况

脏写

使用读已提交解决

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

脏读

使用可重复读解决

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

不可重复读

使用串行化解决

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

幻读

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

事务并发带来的问题

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

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

只加写锁

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

READ-UNCOMMITTED(读取未提交)

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

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

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

脏读的解决原理

READ-COMMITTED(读取已提交)

加读锁、写锁

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

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

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

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

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

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

REPEATABLE-READ(可重复读)

加读锁、写锁、范围锁

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

SERIALIZABLE(可串行化)

隔离级别

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

共享锁/读锁

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

排他锁/写锁

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

根据读写类型

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

全局锁

表共享锁

表独占锁

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

意向共享

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

意向排他

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

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

意向锁

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

简单插入

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

批量插入

混合模式插入

数据的插入方式

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

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

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

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

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

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

自增锁的三种模式

自增锁

系统自动控制，显示使用

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

元数据锁

表级锁

锁记录

也有读写锁之分

记录锁（Record Locks）

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

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

间隙锁（Gap Locks）

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

临建锁Next-key lock

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

什么是MVCC

针对当前读（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日志，每条undo日志也都有一个 roll_pointer 属性可以将这些 undo日志都连起来，串成一个链表

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

undo log版本链

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

使用 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。

Read View的主要内容

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

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

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

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

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

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

ReadView的规则

Read View

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整体操作流程

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

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

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

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

隔离性（I）

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

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

一致性（C）

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

ACID

事务（Innodb）

undo log（前面说过了不说）

redo log（前面说过了不说）

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

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

定义

数据恢复

数据复制

默认是1GB

文件是伪SQL的形式

特点

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

ROW

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

Statement这是默认的binlog格式

Statement与Row的结合

Mixed

binlog的形式

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

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

bin log 和 redo log的比较

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

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

☆二进制日志(bin log)