MySQL复习 mysql - 锐客网

1. 一条查询SQL的执行过程

客户端 => 连接器权限验证（连接断开释放内存，对长连接需要reset_connection）
查询缓存（尽量不要使用，缓存命中率比较低，8.0后完全删除）
分析器语法分析词法分析 MySQL知道你要做什么
优化器优化查询计划：选择哪个索引？表连接的时候决定表的连接顺序
执行器：调用存储引擎接口获得每行数据，将符合的结果存储在结果集中

2. 一条更新SQL的执行过程

redo log：先写日志，再写磁盘 => 保证 crash safe
Update Set WHERE id = 2

1.执行器找存储引擎取ID = 2的这一行，ID是主键索引，引擎直接通过树搜索获得该行。如果本来就在内存中，直接返回；否则，从磁盘中加载这一行 1.执行器将该行数据更改后调用引擎接口写入这行新数据 1.引擎将数据更新到内存，同时写redo log，redo log 处于prepare状态，同时引擎告知执行器随时可以提交 1.执行器写binlog并落盘 1.执行器调用引擎的提交事务接口，引擎将redo log的状态改为提交，更新完成
redo log 和 bin log 都可以表示事务的提交状态，而两阶段提交保证了二者逻辑一致性
全量备份 + bin log ：恢复、备库

3. 事务隔离

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
可重复读的实现：回滚段 => 尽量避免长事务

4. 索引

为什么要用自增主键作为主键索引？（言外之意主键是连续的）：好处：每次插入都是追加操作，不需要挪动其他记录，也不涉及叶子节点的分裂。而采用有业务逻辑的字段作为主键，则不能保证是有序插入，涉及节点的分裂、合并。
自增主键字段空间更小（4或8），则普通索引的叶子节点小，单页存储更多。
一般情况下都使用自增主键，只有KV场景（只有一个唯一索引）例外。
覆盖索引：不必回表
最左前缀：建立联合索引时考虑索引的顺序

5. 行锁

两阶段锁：当需要时加锁，事务提交时才释放锁 => 尽可能将影响并发度的锁后推
高并发时，死锁检测很有可能占用大量CPU资源

6. 事务到底是隔离的还是不隔离的视图

view => 查询语句定义的虚拟表
实现MVCC用到的一致性读视图 =>

事务ID 每行数据不同版本 row_trx_id
为每个事务构造了事务启动时当前活跃的事务ID (启动了但是尚未提交）
【MySQL复习】低水位：数组里面最小trx_id
高水位：当前系统创建的最大trx_id + 1

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况
a. 若 row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见；
b. 若 row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

当前读：update 或 select for update / lock in share mode
RC 和 RR 的区别就是 RC 每个语句创建新的视图， RR事务开始创建新的视图，后面查询沿用
7. 普通索引 change buffer => 非唯一普通索引
8. 空洞删除只是打标记表示可复用，插入也可能造成页分裂导致出现空洞
1. 崩溃恢复 Prepare （时间A）=> binlog （时间B）=> commit

如果A处crash，则事务回滚
如果B处crash
- 如果redo log 有了commit 标记，则提交
- 如果redo log 只有prepare
  - 如果bin log 是完整的，则提交
  - 否则回滚事务

为什么需要两阶段提交？先写redo log，再写bin log，崩溃恢复的时候，两个日志都完整即可，行不行？

不行，这是一个2PC的问题，对于InnoDB来说，redo log提交后就无法回滚（如果回滚，则可能覆盖其他事务的更新）；而如果redo log 成功，bin log 失败，此时就会出现不一致。

只用bin log能不能实现崩溃恢复？
- 历史原因不提供 crash safe
- bin log 不记录数据页的更改，崩溃恢复时无法恢复尚未写入到磁盘的提交
只用redo log行不行？
- bin log 在主从复制方面还有不可替代的作用
- bin log 可以归档，而redo log 是循环写

2. explain应该关注哪些字段？ Column 含义

id 查询序号
select_type 查询类型（simple、primary、union）
table 表名
partitions 匹配的分区
type join类型
- index：索引全表扫描
- index_merge：两个或以上的索引，最后取交集或并集
- range：范围查询
- ref：匹配多行记录（非唯一索引） => 多表join时，前面表的一行能匹配到后面表的多行
- eq_ref：匹配一行记录（主键或唯一索引） => 多表join时，前面表的一行能匹配到后面表的一行
prossible_keys 可能会选择的索引
key 实际选择的索引
key_len 索引的长度
ref 与索引作比较的列（如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段）
rows 要检索的行数(估算值)
filtered 查询条件过滤的行数的百分比（存储引擎返回的数据在经过server过滤后，有多少满足比例）
Extra 额外信息
- distinct
- Using filesort：不能通过索引进行排序，需要额外的索引操作
- Using index：使用覆盖索引
- Using temporary：使用临时表

3. order by

全字段排序
rowid 排序：全字段长度太大，一个文件中放入的行数少，文件多，需要很多次归并排序，那么减少不必要的字段，只保留排序字段和 id，最后从主键索引按序返回
联合索引：避免排序
覆盖索引：避免二次查询

4. 幻读

语义问题：并没有保证所有的行都被选中
数据不一致：先插入后更新，bin log 记录的和实际发生的不一致
间隙锁：开区间
next-key lock：前开后闭区间
加锁原则：
1. 原则1：加锁的基本单位是next-key lock。
2. 原则2：查找过程中访问到的对象才会加锁。
3. 优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。
4. 优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。
5. 一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

5. 主备一致 bin log -> dump_thread -> io_thread -> sql_thread
bin log 格式

statement：SQL语句（当使用Limit时，如果索引选择不同，可能造成主备更改不一致）
row：逻辑更改
mixed

双M结构，互为主备
可用性：

备库机器性能差
备库跑一些运营分析内容
大事务执行很久

主备切换的策略：
? 可靠性优先：（一旦检测到主备差距到达阈值），则主库改为read only，等待备库追赶
? 可用性优先：备库直接可读写，可能产生数据不一致（同时接收客户端请求和relay log）
可用性取决于主备延迟
问题：

如何正确的主备切换？主要是如何继续同步relay log：GTID（global transaction ID）
读写分离哪些坑？过期读：semi-sync、判断是否完全同步、强制走主库

6. 如何选择驱动表？应该尽量选择行数少的表（小表）作为驱动表。
当能够使用被驱动表的索引时，就可以走树搜索。 Index Nested Loop
不能使用索引时，就将每行取出，逐一比较。 Block Nested Loop
MRR：multi-range-read
BKA：Index Nested Loop 为了用上MRR
目的：使用上被驱动表的索引
临时表上建立索引
哈希join，而不是无序数组
7. 临时表临时表只在对其他线程不可见，session结束时自动删除，可用于排序