关于mysql:Mysql笔记1基础和日志

5次阅读

共计 3920 个字符，预计需要花费 10 分钟才能阅读完成。

基础架构

构造

Server 层

  • 包含连接器，查问缓存，分析器，优化器，执行器等
  • 涵盖 Mysql 大多数外围服务性能，和所有内置函数（日期，工夫，数学，加密等）
  • 实现所有跨存储引擎的性能（存储过程，触发器，视图等）

存储引擎层

  • 负责数据的存储和提取
  • 架构模式是插件式的，从 Mysql5.5.5 开始 InnoDB 成为默认存储引擎

1. 连接器

连贯 Mysql
mysql -h$ip -P$port -u$user -p

一个用户胜利建设连贯后，即便你用管理员账号对这个用户的权限做了批改，也不会影响曾经存在连贯的权限。批改实现后，只有再新建的连贯才会应用新的权限设置。

闲暇连贯

连贯实现后，如果你没有后续的动作，这个连贯就处于闲暇状态

show processlist
  • Command 列显示为“Sleep”的这一行，就示意当初零碎外面有一个闲暇连贯。
  • 客户端如果太长时间没动静，连接器就会主动将它断开。这个工夫是由参数 wait_timeout 管制的，默认值是 8 小时。
短连贯

短连贯则是指每次执行完很少的几次查问就断开连接，下次查问再从新建设一个。

长连贯(举荐)

长连贯是指连贯胜利后，如果客户端继续有申请，则始终应用同一个连贯。

MySQL 在执行过程中长期应用的内存是治理在连贯对象外面的。这些资源会在连贯断开的时候才开释。如果长连贯累积下来，可能导致内存占用太大，被零碎强行杀掉（OOM），从景象看就是 MySQL 异样重启了。

解决办法：

  1. 定期断开长连贯。应用一段时间，或者程序外面判断执行过一个占用内存的大查问后，断开连接，之后要查问再重连。
  2. MySQL 5.7 或更新版本，能够在每次执行一个比拟大的操作后，通过执行 mysql_reset_connection 来从新初始化连贯资源。这个过程不须要重连和从新做权限验证，然而会将连贯复原到刚刚创立完时的状态。

2. 查问缓存（Mysql8.0 已删除）

  • MySQL 拿到一个查问申请后，会先到查问缓存看看，之前是不是执行过这条语句。
  • MySQL 之前执行过的语句及其后果可能会以 key-value 对的模式，被间接缓存在内存中。
  • 查问缓存的生效十分频繁，只有有对一个表的更新，这个表上所有的查问缓存都会被清空。
  • 能够将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不应用查问缓存。

强制查问缓存语句

select SQL_CACHE * from T where ID=10；

3. 分析器

分析器先会做“词法剖析”。你输出的是由多个字符串和空格组成的一条 SQL 语句，MySQL 须要辨认出外面的字符串别离是什么，代表什么。

如果没有命中查问缓存，就要开始真正执行语句了。

语法错误揭示：ERROR 1064 (42000): You have an error in your SQL syntax;

4. 优化器

优化器是在表外面有多个索引的时候，决定应用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连贯程序。

5. 执行器

  • 开始执行的时候，要先判断一下有没有执行权限，如果没有，就会返回没有权限的谬误
  • 如果有权限，就继续执行，执行器会依据表的引擎定义，去应用这个引擎提供的接口。

示例：

mysql> select * from T where ID=10;

执行流程：

  1. 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在后果集中；
  2. 调用引擎接口取“下一行”，反复雷同的判断逻辑，直到取到这个表的最初一行。
  3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为后果集返回给客户端。

数据库的慢查问日志中有一个 rows_examined 字段，示意这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。

小结

问题：如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1, 那必定是会报“不存在这个列”的谬误：“Unknown column‘k’in‘where clause’”。你感觉这个谬误是在咱们下面提到的哪个阶段报进去的呢？
答案：分析器。Oracle 会在分析阶段判断语句是否正确，表是否存在，列是否存在等。
其余答案：预处理器。
解析器解决语法和解析查问, 生成一课对应的解析树。
预处理器进一步查看解析树的非法。比方: 数据表和数据列是否存在, 别名是否有歧义等。如果通过则生成新的解析树，再提交给优化器。

日志零碎

重做日志 redo log

redo log 是 InnoDB 引擎所特有的

1）作为 WAL 的一种实现，在断电的状况下实现原子性和持久性；

2）将对数据库操作的随机 IO 转换为程序 IO，曾经通过组提交的形式进步 mysql 的 IO 效率。

WAL 技术

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写 redo log 到 log buffer（程序，只寻址一次），再择机写入 redo 日志文件（寻址屡次）。

  1. 当有一条记录须要更新的时候，InnoDB 引擎就会先把记录写到 redo log 外面，并更新内存，这个时候更新就算实现了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里。

  2. redo log 大小固定，是循环写的，空间固定会用完；binlog 是能够追加写入的。“追加写”是指 binlog 文件写到肯定大小后会切换到下一个，并不会笼罩以前的日志。

    • write pos 以后记录的地位
    • checkpoint 以后要擦除的地位
  3. 有了 redo log，InnoDB 就能够保障即便数据库产生异样重启，之前提交的记录都不会失落，这个能力称为 crash-safe。

归档日志 binlog

binlog（归档日志）是 Server 层的日志

  1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都能够应用。

  2. redo log 是物理日志，记录的是“在某个数据页上做了什么批改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比方“给 ID=2 这一行的 c 字段加 1”。

    redolog 事务内每执行一条就会写入一条；binlog 是在事务提交后一次性写入。

  3. redo log 是循环写的，空间固定会用完；binlog 是能够追加写入的。“追加写”是指 binlog 文件写到肯定大小后会切换到下一个，并不会笼罩以前的日志。

update 执行流程

语句执行时，server 层调用引擎的更新接口，引擎在更新后，会写 redolog，此时处于 redolog 的 prepare 阶段。而后 server 层写入 binlog 日志后，能够调用引擎的事务提交接口，由引擎把 redo log 改为提交状态。

update T set c=c+1 where ID=2;
  1. 执行器先找引擎取 ID=2 这一行。ID 是主键，引擎间接用树搜寻找到这一行。如果 ID=2 这一行所在的数据页原本就在内存中，就间接返回给执行器；否则，须要先从磁盘读入内存，而后再返回。
  2. 执行器拿到引擎给的行数据，把这个值加上 1，比方原来是 N，当初就是 N+1，失去新的一行数据，再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 外面，此时 redo log 处于 prepare 状态。而后告知执行器执行实现了，随时能够提交事务。
  4. 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新实现。

两阶段提交

redo log 的写入拆成了两个步骤：prepare 和 commit，这就是 ” 两阶段提交 ”。

数据库复原

通过定期的整库备份加上 binlog 的操作回放能够保证数据的安全性。

  1. 找到最近的一次全量备份，从这个备份复原到长期库；
  2. 从备份的工夫点开始，将备份的 binlog 顺次取出来，重放到中午误删表之前的那个时刻。

备份实现：https://www.cnblogs.com/shamo…

异常情况(crash)

  1. 先写 redo log 后写 binlog。假如在 redo log 写完，binlog 还没有写完的时候，MySQL 过程异样重启。因为咱们后面说过的，redo log 写完之后，零碎即便解体，依然可能把数据恢复回来，所以复原后这一行 c 的值是 1。然而因为 binlog 没写完就 crash 了，这时候 binlog 外面就没有记录这个语句。因而，之后备份日志的时候，存起来的 binlog 外面就没有这条语句。而后你会发现，如果须要用这个 binlog 来复原长期库的话，因为这个语句的 binlog 失落，这个长期库就会少了这一次更新，复原进去的这一行 c 的值就是 0，与原库的值不同。
  2. 先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，因为 redo log 还没写，解体复原当前这个事务有效，所以这一行 c 的值是 0。然而 binlog 外面曾经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来复原的时候就多了一个事务进去，复原进去的这一行 c 的值就是 1，与原库的值不同。

小结

  1. redo 是物理日志，binlog 是逻辑日志；
  2. redo log 用于保障 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，示意每次事务的 redo log 都间接长久化到磁盘。这个参数我倡议你设置成 1，这样能够保障 MySQL 异样重启之后数据不失落。
  3. sync_binlog 这个参数设置成 1 的时候，示意每次事务的 binlog 都长久化到磁盘。这个参数我也倡议你设置成 1，这样能够保障 MySQL 异样重启之后 binlog 不失落。
  4. Binlog 有两种模式，statement 格局的话是记 sql 语句，row 格局会记录行的内容，记两条，更新前和更新后都有，然而日志会变大。举荐应用 row 格局。
正文完
mysql
发表至： mysql
2022-01-11
 0
关于mysql:3-安装部署MGR集群-深入浅出MGR
关于mysql:mysql中的各种锁把我搞糊涂啦
关于mysql:日志Redo-Log-和-Undo-Log
关于mysql:从零开始学Mysql-字符集和编码下

站内搜索

-「」