Mysql
2024-04-24 02:00:35 29 举报AI智能生成
登录查看完整内容
Mysql 2024年4月24日02:00:08：补充幻读的解决原理。
作者其他创作
大纲/内容
连接器：建立连接，管理连接、校验用户身份
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型
预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列
优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划
执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端
执行 SQL：执行 SQL 共有三个阶段
执行一条 SQL 查询语句，期间发生了什么？
行锁（重点记忆）
事务支持（重点记忆）
热备份
外键（重点记忆）
MVCC
聚簇索引
InnoDB
表锁
不支持事务
不支持外键
没有热备份
MyISAM（没用过）
内存中，不能持久化
Memory（没用过）
存储引擎
共享锁
排他锁
范围锁
根据读写
整库备份
全局锁
表共享锁
表独占锁
意向锁
系统自动控制，显示使用
元数据锁
表级锁
记录锁
间隙锁
临建锁
行级锁（通过对索引项加锁实现）
根据粒度
锁的分类
Record lock：记录锁，锁定一个记录上的索引，而不是记录本身
Gap lock：间隙锁，锁定索引之间的间隙，但是不包含索引本身
针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读，因为当执行 select ... for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。
MVCC 不能完全解决幻读的问题，Next-Key Locks 就是为了解决这个问题而存在的。在可重复读(REPEATABLE READ)隔离级别下，使用 MVCC + Next-Key Locks 可以解决幻读问题。
Next-key lock：record+gap 临键锁，锁定一个范围，包含记录本身
锁算法
MVCC的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View（快照读） 来实现的
隐藏主键，有主动设置主键则不会生成
DB_ROW_ID
事务id
DB_TRX_ID
回滚指针，指向undoLog
DB_ROLL_PTR
隐藏字段
MVCC的作用就是在避免加锁的情况下，最大限度的解决并发读写的问题，它可以实现提交度和重复度
串行化则是真的通过锁实现的
MVCC解决隔离性问题
锁
没用过
视图\\存储过程\\触发器
水平分
垂直分
事务
主键唯一性
JOIN
问题
分库分表
属性不可再分，几乎所有数据库都满足
第一
非主键字段依赖于主键字段
第二
在非主键字段依赖于主键字段的前提下，强制不能传递依赖，必须直接依赖
第三
数据库三范式
整型
浮点
数值型
这个性能会比较高
char（定长）
varchar（变长）
二进制、文本
字符型
到9999年，但是没有时区概念
DATETIME（8字节）
到2038年，有时区概念，项目中建议使用这个
TIMESTAMP（4字节）
时间日期型
数据类型
这个个人不太回去记忆语法的使用。建表语句，实际项目中都是在工具上（PDManer）定义好表结构信息，然后一键生成。至于修改的操作，开发人员(就我前司而言)基本没机会接触，这得找运维执行。
表结构
DDL
这个个人没怎么记忆，项目中根本就没有自己手写过这类SQL，直接调ORM框架封装的方法得了，够用
新增、修改、删除记录
DML
这个写一些复杂SQL的时候会用到，简单的CRUD同样直接调ORM框架封装的方法
没啥好说的
基本查询
没啥好说的，where
条件查询
count
avg
sum
min
max
PS：NULL值不参与聚合运算
聚合函数
Group by ... having...
执行顺序 where>group by >having
where不能用聚合函数，having可以
分组查询（Group by）
默认升序
排序查询（order by）
这里再深入说一下，分页操作不是指查询出a-b之间的数据，而是把a+b条记录查出来，再减去前面的a条，所以遇到深度分页的情况下需要进行特殊的优化（有一说一我们在项目中除了特定的需求需要，一般都会对分页上限做限制的，防止出现深度分页，b站的个人中心里很多分页操作就禁止了深度分页）
分页查询
查询记录
DQL
这个我作为开发也没确实没机会接触
用户权限控制
DCL
分类
SQL
没用过，真没用过
函数
Not NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
PS：用的比较多的就这四个，对于外键，其实也没用过在数据库层面上，基本都是在业务层解决，直接在数据库上加外键太耗性能了
约束
没咋见过，大都是一张表字段太多了要拆开来提高性能
一对一
在多的一方建立外键，指向一的一方的主键（这个不是真建，只是想表达这个意思，在业务层解决）
两张表
一对多
三张表，一张关联表，关联两个表的主键
多对多
表关系
取交集
内连接
整个左边
左外连接
整个右边
右外连接
外连接
自连接
会去重
union
不会去重
union all
联合查询（列数、对应的数据类型要一致）（没用过）
能玩的挺花的，不过我没怎么用过，最多用一下单值的
单值
单列
单行
多行多列
子查询
查询分类
多表查询
使用 undo log(回滚日志) 来保证事务的原子性。
原子性保证了事务的多个操作要么都生效要么都不生效，不会存在中间状态
原子性（A）
MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，好像还有服务层的binlog？
持久性保证了一旦事务生效，就不会因为任何原因而导致其修改的内容被撤销或者丢失
持久性（D）
读锁（共享锁）
写锁（排他锁）
范围锁（范围排他锁）
锁分类
MySQL InnoDB 引擎通过 锁机制、MVCC 等手段来保证事务的隔离性
Mysql默认隔离级别为可重复读
不同隔离级别以及幻读、不可重复读、脏读等问题都只是表面现象，是各种锁在不同加锁时间上组合应用所产生的结果，以锁为手段来实现隔离性才是数据库表现出不同隔离级别的根本原因
只加写锁
最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-UNCOMMITTED(读取未提交)
加读锁、写锁，读锁在查询操作执行完立即释放
允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
READ-COMMITTED(读取已提交)
加读锁、写锁
对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生
只读、读写事务下可以靠mvcc实现不加锁避免幻读，写写事务下还是无法避免；
细说这个级别下的幻读
REPEATABLE-READ(可重复读)
加读锁、写锁、范围锁
最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读，但是性能最差。
SERIALIZABLE(可串行化)
隔离级别
使用读已提交解决
一个事务读到另外一个事务未提交的数据
脏读
使用可重复读解决
一个事务中对同一数据的多次读取结果不一致
不可重复读
使用串行化解决
一个事务中对表的多次数据查询结果不一致
幻读
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次查询同一条查询语句（DQL）时，记录发现记录增多或减少了
事务并发带来的问题（这个并入隔离性里）
隔离性保证了每个事务各自读、写的数据互相独立，不会彼此影响
隔离性（I）
保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。
系统中所有的数据都是符合期望的，且相互关联的数据之间不会产生矛盾
一致性（C）
补充：纠正网上对于ACID的说法：ACID这四种特征并不正交，A、I、D是手段，C是目的。前者是因后者是果，弄到一块完全是为了拼凑的单词缩写
ACID
查询快
好处
需要维护，占内存
缺点
索引是一种用于快速查询和检索数据的数据结构
PS：问的比较多的是和B树、红黑树相比，优点在哪里
①数据存储在叶子结点
②叶子结点之间有双向的指针，提高查询效率
③层级少，高度低，查的快
B+树
不支持范围查询
Hash表
ES
全文索引
MyISAM的，用的比较少
R树
索引的数据结构
主键索引
唯一索引
INDEX
普通索引
FULLTEXT
索引类型
.ibd文件
聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引（叶子结点存放的是数据）
非聚集索引即索引结构和数据分开存放的索引（叶子结点存放的是数据的主键，需要通过主键回表查询）
存在主键，主键就是聚集索引
不存在主键，将第一个使用唯一索引的作为聚集索引
没有主键或合适的唯一索引，生成隐藏id作为聚集索引
聚集索引的选择
聚集索引与非聚集索引(二级索引)（聚簇不聚簇不是索引的类型，是数据的存储方式）
从二级索引中获得数据的主键值，再去聚集索引中查找，尽量避免回表查询
回表查询
①遵守最左前缀原则
②不要在索引列上使用函数运算
③范围查询使用>=或者<=
④字符串类型加引号
⑤不要使用头部模糊查询
⑥or条件左右的列都要有索引
走覆盖索引
⑦不要用*号
⑧索引长字符串，建立前缀索引
⑨尽量用联合索引
⑩控制索引数量
索引优化总结
批量插入用load
非顺序插入会导致页分裂
主键顺序插入（自增）性能高于非顺序插入（UUID）
通过覆盖索引+子查询优化
Limit越往后查询效率越低
使用count(1)或者count(*)
COUNT()
SQL优化
索引
可预测读
自适应哈希
插入缓冲区
内部优化
Mysql
0 条评论
回复 删除
下一页
