Mysql
2024-07-13 00:47:48 39 举报AI智能生成
登录查看完整内容
Mysql 2024年4月24日02:00:08：补充幻读的解决原理。 2024年7月13日00:45:21：这是久违的一次大更新，基本上完善够用了。 ①进行内容分类，基础、高级、运维 ②基础部分补充了各种常用的函数、sql使用建议 ③高级部分锁的内容进行了完善 ④添加的运维的部分 唯一美中不足的：存储过程、视图、游标那块我属实是有点抗拒，所以就草草的添了几笔。
作者其他创作
大纲/内容
属性不可再分，几乎所有数据库都满足
第一
非主键字段依赖于主键字段
第二
在非主键字段依赖于主键字段的前提下，强制不能传递依赖，必须直接依赖
第三
数据库三范式
没咋见过，大都是一张表字段太多了要拆开来提高性能
一对一
在多的一方建立外键，指向一的一方的主键（这个不是真建，只是想表达这个意思，在业务层解决）
两张表
一对多
三张表，一张关联表，关联两个表的主键
多对多
表关系
数据表的个数越少越好
数据表中的字段个数越少越好
数据表中联合主键的字段个数越少越好
表设计原则
TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
整型
FLOAT、DOUBLE
浮点
DECIMAL
数值型
这个性能会比较高
char（定长）
varchar（变长）
二进制、文本
字符型
到9999年，但是没有时区概念
DATETIME（8字节）
到2038年，有时区概念，项目中建议使用这个
TIMESTAMP（4字节）
时间日期型
JSON对象
JSON数组
Json(mysql8)
数据类型
Not NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
DEFAULT
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
IN
NOT IN
“%”：匹配0个或多个字符。
“_”：只能匹配一个字符。
LIKE
not
and
or
逻辑运算符
常用运算符
ASC（ascend）: 升序，默认升序
DESC（descend）:降序
单列排序
①可以使用不在SELECT列表中的列排序。
②在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第 一列数据中所有值都是唯一的，将不再对第二列进行排序。
☆多列排序
排序查询（order by）
分页显式公式：（当前页数-1）* 每页条数，每页条数
这里再深入说一下，分页操作不是指查询出a-b之间的数据，而是把a+b条记录查出来，再减去前面的a条，所以遇到深度分页的情况下需要进行特殊的优化（有一说一我们在项目中除了特定的需求需要，一般都会对分页上限做限制的，防止出现深度分页，b站的个人中心里很多分页操作就禁止了深度分页）
没用过
自连接
取交集
内连接
整个左边
左外连接
整个右边
右外连接
外连接
作用：合并查询结果
会去重
union
不会去重
union all
联合查询（列数、对应的数据类型要一致）（UNION）
查询分类
多表查询
基本函数
三角函数
指数对数函数
弧度函数
进制转换
数值函数
用字符串b替换字符串str中所有出现的字符串a
UPPER(s) 或 UCASE(s)
LOWER(s) 或LCASE(s)
TRIM(s)
字符串函数
获取日期、时间
日期与时间戳的转换函数
获取月份、星期、星期数、天数等函数
日期的操作函数
时间和秒钟转换的函数
计算日期和时间的函数
日期的格式化与解析
日期和时间函数
流程控制函数
加密与解密函数
单行函数
聚合函数作用于一组数据，并对一组数据返回一个值。
什么是聚合函数
count
avg
sum
min
max
PS：NULL值不参与聚合运算
聚合函数类型
聚合函数
函数
Group by ... having...
执行顺序 where>group by >having
where不能用聚合函数，having可以
①ELECT中出现的非组函数的字段必须声明在GROUP BY中。 反之，GROUP BY中声明的字段可以不出现在SELECT中
②GROUP BY声明在FROM后面、WHERE后面、ORDER BY前面、LIMIT前面
tips：
分组查询（Group by）
tips：子查询先于主查询执行
>
......
单行
ANY
ALL
SOME
多行
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
ES
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为最后执行
id
简单表，不使用表连接或者子查询
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)
中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入 本地的日志文件 中，这个从服务器本地的日志文件就叫中继日志 。然后，从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的 数据同步 。
中继日志relay log
日志
可预测读是一种隔离级别策略，主要应用于 InnoDB 存储引擎。它确保在读取数据时不需要等待行级锁，从而减少锁争用，提高并发性能。
可预测读
当InnoDB发现某些索引值被⾮ 常频繁地被访问时，它会在原有的B-tree索引之上，在内存中再构建⼀个哈希索引。这就让B-tree索引也 具备了⼀些哈希索引的优势， 这个过程是完全⾃动化的，⽤户⽆ 法进⾏控制或者配置。
自适应哈希
插入缓冲是一种优化技术，用于加速插入操作。InnoDB 在插入数据时不会立即将记录插入到索引页，而是将其缓存在插入缓冲区中。然后在后台批量处理这些插入操作，从而减少磁盘 I/O，提高插入效率。
插入缓冲区
是一种数据库查询优化技术。它的主要作用是将部分过滤操作从MySQL服务器层下推到存储引擎层，即在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，这样可以有效地减少回表次数。
内部优化
高级
数据备份
高可用
Slave 会从 Master 读取 binlog 来进行数据同步。
二进制日志转储线程 （Binlog dump thread）是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。
一个主库线程
从库 I/O 线程 会连接到主库，向主库发送请求更新 Binlog。这时从库的 I/O 线程就可以读取到主库的二进制日志转储线程发送的 Binlog 更新部分，并且拷贝到本地的中继日志 （Relay log）。
从库 SQL 线程 会读取从库中的中继日志，并且执行日志中的事件，将从库中的数据与主库保持同步。
两个从库线程
三个线程
步骤1： Master 将写操作记录到二进制日志（ binlog ）。
步骤2： Slave 将 Master 的binary log events拷贝到它的中继日志（ relay log ）；
步骤3： Slave 重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的，而且重启后从 接入点 开始复制。
复制步骤
原理
网络原因
从库压力大
大事务
主从延迟问题
无法最终保证数据的一致性问题
异步复制
半同步复制
（√）是基于 Paxos 协议的状态机复制
组复制
一致性问题
MyCat
ShardingSphere
数据库中间件
主从复制
物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用 xtrabackup 工具来进行物理备份
物理备份
逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为 mysqldump 。逻辑备份就是 备份sql语句 ，在恢复的时候执行备份的sql语句实现数据库数据的重现。
逻辑备份
备份
mysql –u root –p [dbname] < backup.sql
恢复
使用SELECT…INTO OUTFILE导出文本文件
使用mysqldump命令导出文本文件
导出
使用LOAD DATA INFILE方式导入文本文件
使用mysqlimport方式导入文本文件
导入
备份与恢复
大数据整体迁移
适用场景
快
优点
要停机
物理迁移（包括拷贝数据文件和使用 XtraBackup 备份工具两种）
各种
灵活
迁移时间长
逻辑迁移（mysqldump）
迁移
运维
Mysql
0 条评论
回复 删除
下一页
