MySQL 学习笔记

mysql 整体结构

• 示例图:

• 简单结构：

  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 事务语法（文档）

• 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+树

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

• 主键索引（聚簇索引）和非主键索引（非聚簇索引）的数据储存结构和联系如图：

• 通过主键索引查询会只查询主键索引数，通过非主键索引查询则会先查询对应索引树，再通过查找到的主键值查找主键索引数，该过程成为回表

索引维护

• 插入新值时：对于 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（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。