xiaocainiaoya's blog

日常积累

0%

mysql45讲学习总结（一）---初试

发表于 更新于 分类于

mysql45讲学习总结（一）—初试

背景

​ 感觉自身对于MYSQL的知识点是零散的，没有形成网络，只用到了基本的增删改查功能，偶尔遇到一点性能的问题，还需要通过搜索引擎来查到解决方案，并且查找到的解决方案参差不齐，导致往往问题虽然解决，但是还是不知其所以然，从而重复出现问题时，也不能较快的解决问题。据说MYSQL45讲对MYSQL介绍比较细致，所以希望通过学习MYSQL45讲让自己的MYSQL形成知识网络。

组件

​ 下方为MYSQL的基本架构示意图，一个执行语句在不查询缓存的情况下，基本查询路径为：客户端→连接器 →分析器→优化器→执行器→存储引擎。对应过程：

  1. 连接：连接管理模块，接收请求；连接进行和用户模块，验证通过；连接线程和客户端连接。
  2. 查询：（查询缓存）
    1. 分析器：内建解析树，对其语法检查，form→on→join→where，检查权限，生成新的解析树，语义检查。
    2. 优化器：将解析树转换为执行计划，选择索引，并评估最优执行。
    3. 执行器：获取锁，打开表，通过meta数据，获取查询数据。
  3. 返回结果：返回给连接进程，然后情况，等待新的请求。

MySQL 的逻辑架构图.png

总体来说MYSQL基本架构分为两个部分：

1.Server层

Server层包含了很多执行组件，涵盖了MYSQL的大多数核心业务功能，以及所有的内置函数（聚合函数的处理），所有跨存储引擎的功能都是在这一层实现，比如存储过程、触发器、视图等。从图中可以看出所有存储引擎共用同一个Server层。

1.1连接器

当使用以下连接命令之后，连接器通过TCP与客户端建立连接、获取权限、维持和管理连接。

mysql -h$ip -P$port -u$user -p

通过以下命令可以查看每个连接的状态

show processlist;

Id User Host db Command Time State Info
5 root 172.17.0.1:40922 binlog_test Query 0 show processlist
6 root 172.17.0.1:40926 binlog_test Sleep 1237

Command列中存在一个Sleep，表示有一个连接处于空闲状态。

1.2分析器

分析器的主要作用是对接收到的SQL语句进行解析。

首先进行”词法分析“：识别收到的SQL语句中代表的是什么、识别出列ID，查询条件等。

其次进行”语法分析“：进行语法规则的校验，判断输入SQL是否满足MYSQL语法，若语法错误则会抛出You have an error in your SQL syntax。

1.3查询缓存

​ 接收到的SQL在进入到分析器之前，会先进入到查询缓存组件，判断之前是否执行过该语句，如果执行过直接返回结果，若没有执行过则进入分析器。但是大多数的情况下，由于缓存失效的非常频繁，只要对一个表存在更新操作，这个表所对应的所有缓存都失效，导致对缓存的命中率比较低。

query_cache_type设置为DEMAND表示开启，NO表示关闭

或者也可以通过SQL中关键字SQL_CACHE指定：

select SQL_CACHE * from T where ID=10；

同时在MYSQL 8.0版本中移除了缓存模块。

1.4优化器

​ 优化器接收到了经过了分析器的SQL，优化器的主要作用是对SQL进行优化、多表连接顺序、多索引时选择使用哪个索引等。同一个SQL语句，不同的连接顺序、索引选择虽然查询结果一致，但是执行效率上存在差异。

​ 优化器的作用便是在同样查询结果的情况下，对SQL语句进行优化，尽可能提升语句的查询效率，但是也有可能经过优化器处理之后，反而导致执行时长变长。

1.5执行器

​ 优化器执行之后，SQL语句的执行方案就确定下来，然后进入执行器，在开始执行之前，需判断登录用户对操作表有没有执行查询的权限，若没有权限则报错：

ERROR 1142 (42000): SELECT command denied to user ‘b’@’localhost’ for table ‘T’

比如查询语句为：

select * from T where ID=10;

表 T 中，ID 字段没有索引，在执行器中的执行流程为：

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

如果ID字段存在索引，只是在第1步骤中使用的调用存储引擎的接口不一致。

在慢查询日志中会有一个rows_examined的字段，表示这个语句在执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟rows_examined并不是完全相同的。

2.存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式，支持InnoDB、MyISAM、Memory。默认情况下使用InnoDB，也就是说通过create Table创建表时，若没有明确指定存储情况，默认就会使用InnoDB。

  • InnoDB：事务型数据库的首选存储引擎，支持书屋安全表ACID，支持行锁和外键，InnoDB是MYSQL的默认存储引擎。
  • MyISAM：基于ISAM存储引擎，并对其进行扩展，它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一，拥有较高的插入和查询速度，但不支持事务。
  • Memory：内存数据库，将表中的数据存储到内存中。

更新语句

​ 更新语句和查询语句在执行器之前经过的Server层组件是一样的(唯一不同的是更新语句会移除所操作表的查询缓存)。与查询流程不一样的是，更新流程还涉及到两个重要的日志模块redo log(重做日志)，binlog(归档日志)。

重做日志

​ 在MYSQL45讲中，使用《孔乙己》中酒店掌柜的例子，粉板和账本指代redo log和磁盘。当有一个更新操作时先将记录写入到redo log中，并不是执行写入磁盘(这么做的话IO成本、查找成本太高)。而是在适当的时机将redo log的内容写入到磁盘中。

​ 通过redo log和磁盘结合的方式进行数据记录，这个整合的过程，就是MYSQL的WAL(Write-Ahead Logging)技术，它的关键点就是先写日志，再写磁盘。

归档日志

​ redo log是InnoDB引擎特有的日志，bin log是Server层日志。

这两种日志的不同点：

  • redo log是InnoDB引擎特有的；bin log是Server层实现，所有存储引擎都可以使用。
  • redo log记录的是物理日志，记录的是”在某一个数据页上做了什么修改“，bin log是逻辑日志，记录的是这个语句的原始逻辑，比如”给 ID=2 这一行的 c 字段加 1 “。
  • redo log是循环写，空间是固定(粉板)，当写满之后会覆盖掉以前的日志；bin log是可以追加写，当文件写入到一定大小后会根据规则生成新的日志文件，不会覆盖旧日志。

create table T(ID int primary key, c int);

update T set c=c+1 where ID=2;

两阶段提交的简单流程

  1. 执行器先通过存储引擎接口获取到ID=2这一行记录，如果ID=2这行记录的数据页在内存中，则直接从内存中获取后返回给执行器。否则需要先将磁盘中这一页数据加载到内存中，再返回。
  2. 执行器得到这一行数据后，对c列执行+1操作，再调用存储引擎接口，将结果写入到这行新数据中。
  3. 存储引擎将这行数据更新到内存中，之后将这个更新操作写入到redo log中，并标记状态为prepare，然后告知执行器可以进行提交commit操作。
  4. 执行器生成这个操作的bin log，并将bin log刷盘。
  5. 执行器调用存储引擎的提交事务接口，存储引擎将刚刚的redo log由perpare修改为commit状态。

先说这样做的好处：实际上引入redo log的作用是希望MYSQL具备crash-safe(指服务器宕机重启后，能够保证已提交的事务仍然存在)。

若服务器宕机出现在以上的某个流程中：

  • 在第3步骤写入redo log之后，由于redo log的状态为prepare，所以重启恢复是去判断bin log是不是完整，显然在这一步骤是不完整的，直接回滚事务。
  • 在第4步骤写入bin log之后，由于redo log的状态为prepare，所以重启恢复是去判断bin log是不是完整，显然在这一步骤是完整的，直接提交事务。
  • 在第5步骤修改redo log状态为commit状态，显然直接提交这个事务。

若不使用两阶段提交：

假设两个前提：

  • 数据在一定周期内进行全量备份，可以是一天一备，也可以是一周一备。
  • 开启bin log。

在这种情况下数据恢复到某一时间点的操作为：

  1. 找到最近一次的全量备份，将这个备份恢复到临时库中。
  2. 从备份的时间点开始，将备份时间点之后的的bin log依次取出，在临时库进行重放到需要恢复的这一时刻。
  3. 这时在临时库就有被误删的数据，简单处理可以手动将数据从临时表取出，按需恢复到线上库。

由于存在两个日志文件，所以就需要保证两个日志文件在MYSQL发生异常时，数据能保持一致。

  1. **先写binlog后写redo log**：假设在binlog写完之后，MYSQL发生异常，由于redo log还没有写，导致MYSQL重启后binlog存在某一个操作，而redo log中没有这一操作，若从库使用binlog重放，则导致从库会有这一操作，从而导致主从数据不一致。
  2. **先写redo log再写binlog**：假设在redo log写完之后，MYSQL发生异常，由于binlog还没写，导致重启之后，通过redo log进行数据恢复，所以主库中是存在这一操作，但是由于binlog中没有写入，所以导致使用binlog重放时，从库丢失一个操作。

小结

​ redo log的主要作用是利用”粉板”和”账本”的配合，提高MYSQL的吞吐性能，降低IO成本、查找成本。bin log是Server层日志，它存在于redo log之前，redo log是在InnoDB存储引擎诞生时，由innoDB引擎自身携带，其他存储引擎是没有的。还有redo log是对一个日志文件进行循环写，到一定数据量之后会覆盖旧值，不持久保存，binlog才具备”归档“的能力。

故障恢复是使用redo log日志进行恢复。主从同步是使用binlog进行数据同步。

  • innodb_flush_log_at_trx_commit：设置为1时表示，表示每次事务的redo log都直接持久化到磁盘。建议设置为1，保证MySQL异常重启之后数据不丢失。
  • sync_binlog：设置成1的时候，表示每次事务的binlog都持久化到磁盘。建议设置成1，这样可以保证 MySQL异常重启之后binlog不丢失。

​

-------------本文结束感谢您的阅读-------------