极客时间 mysql 学习记录

清疚 2022-01-07 18:17 298阅读 0赞

### mysql 整体结构 ###

*  示例图: ![image][]
 *  简单结构：
    
    1.  连接器
    2.  分析器
    3.  优化器
    4.  执行器

### mysql 日志种类（redo log和binlog） ###

*  本质区别：
    
    1.  redo log 记录物理日志
    2.  binlog 记录逻辑日志，比如操作了哪行记录
 *  用update的过程来理解（个人理解，可见极客时间的MySQL专栏 02 篇）
    
    1.  执行器找引擎查取数据（如找 ID=2 这一行）
    2.  引擎返回搜索到的数据给执行器，执行器更改数据（如 set num=num+1 ）
    3.  执行器通知引擎写数据（如 update）
    4.  引擎写入数据（内存）
    5.  引擎写 redo log（prepare），返回执行完成
    6.  执行器生成 binlog ，写入磁盘，执行器调用引擎的事务提交接口
    7.  引擎写 redo log（commit）
    8.  完成操作

### mysql 事务语法（[文档][Link 1]） ###

*  START TRANSACTION|BEGIN \[WORK\]：显式地开启一个事务，begin 等同于 begin work。
 *  COMMIT：等同于 COMMIT WORK
    
     *  commit work with chain ：表示马上自动开启一个相同隔离级别的事务
     *  commit work ：当事务提交后会自动断开与服务器的连接
 *  ROLLBACK：回滚
 *  SAVEPOINT identifier：SAVEPOINT允许你在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT
 *  RELEASE SAVEPOINT identifier：删除一个事务的保存点
 *  ROLLBACK TO \[SAVEPOINT\] identifier：将事务回滚至标记点
 *  示例
    
        begin; insert into t select 1; savepoint t1; insert into t select 2; savepoint t2; release savepoint t1; insert into t select 2; ERROR 1062（23000）：Duplicate entry'2'for key'PRIMARY' rollback to savepoint t2; select * from t; rollback;
 *  可以在 `information_schema.innodb_trx` 这个表中查询长事务
    
        # 查找持续时间超过 60s 的事务 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60;

### 索引 ###

#### 常见索引模型 ####

*  哈希
 *  有序数组
 *  搜索树
    
     *  B+树

#### 基于聚簇索引和非聚簇索引的查询 ####

*  主键索引（聚簇索引）和非主键索引（非聚簇索引）的数据储存结构和联系如图： ![image][image 1]
 *  通过`主键索引`查询会只查询主键索引数，通过`非主键索引`查询则会先查询对应索引树，再通过查找到的主键值查找主键索引数，该过程成为`回表`

#### 索引维护 ####

*  插入新值时：对于 B+ 树，若插入的新纪录比当前列最大值要大，则为`追加操作`，若小可能会触发`数据挪动`和`页分裂`
    
     *  追加：插入一条新记录，不涉及到挪动其他记录，也不会触发叶子节点的分裂（如自增主键的插入数据模式）。
     *  数据挪动：若插入新值比当前列最大值小，则需要逻辑上挪动后面的数据，空出位置。
     *  页分裂：所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新页，然后挪动部分数据过去。
 *  删除数据时：当相邻两个页由于删除了数据，利用率很低之后，会将数据页做`合并`。

### 关键实战问题记录 ###

1.  如何避免长事务对业务的影响？

*  应用开发端来看：
    
    1.  确认是否使用了 set autocommit=0。这个确认工作可以在测试环境中开展，把 MySQL 的 general\_log 开起来，然后随便跑一个业务逻辑，通过 general\_log 的日志来确认，在业务功能测试阶段要求输出所有的 general\_log ，分析日志行为提前发现问题。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。
    2.  确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。我见过有些是业务并没有这个需要，但是也把好几个 select 语句放到了事务中。这种只读事务可以去掉。
    3.  业务连接数据库的时候，根据业务本身的预估，通过SET MAX\_EXECUTION\_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。（为什么会意外？在后续的文章中会提到这类案例）
 *  数据库端来看：
    
    1.  监控 information\_schema.Innodb\_trx 表，设置长事务阈值，超过就报警/或者kill；
    2.  Percona 的 pt-kill 这个工具不错，推荐使用；
    3.  如果使用的是 MySQL 5.6 或者更新版本，把 innodb\_undo\_tablespaces 设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

转载于:https://www.cnblogs.com/varXinYuan/p/10865287.html

[image]: /images/20211220/553b69d2aba1451e804aef7a078ca298.png
[Link 1]: https://crate.io/docs/sql-99/en/latest/chapters/36.html#commit-statement
[image 1]: /images/20211220/1eea930a2fa049498d88990f1064e8b2.png