MySQL 基本架构与日志两阶段提交

MySQL, 数据库 / 作者：

MySQL基本架构

MySQL大致可以分为Server层和存储引擎层

Server层包括连接器，查询缓存，解析器，预处理器，优化器，执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程，触发器和视图等

连接器

  • 负责跟客户端建立连接，获取权限，维持和管理连接
  • 用户名密码验证通过后，连接器会到权限表中查询用户的权限，之后这个链接里面的权限判断逻辑，都依赖于此时读取的权限；即使使用管理员账号对用户权限作修改，也不会影响已存在的连接的权限
  • 参数wait_timeout控制服务器保持与客户端的空闲连接的时长，默认值为8小时
  • 长连接积累导致MySQL占用内存飞涨
    • 考虑定期断开长连接
    • MySQL5.7以后，执行mysql_reset_connection来重新初始化连接资源 (此过程不需要重连和重现验证权限，但是会将连接恢复到刚创建完成的状态)

MySQL :: MySQL 5.7 Reference Manual :: 27.8.7.60 mysql_reset_connection() dev.mysql.com/doc/refman/…

查询缓存

  • 如果查询恰好命中查询缓存，那么查询不会被解析，执行计划不会被生成，在返回查询结果之前，MySQL会检查用户的权限
  • 对一张表的更新，会导致这张表的查询缓存被清空(查询缓存失效频繁，弊大于利)
  • 推荐按需使用查询缓存
    • 设置参数query_cache_type设置成DEMAND，默认情况下，SQL不使用查询缓存，通过SQL_CACHE显示指定使用查询缓存
select SQL_CACHE * from T where ID = 10;
  • MySQL 8.0已经将查询缓存整块功能删除掉

解析器（做什么）

  • 词法分析，识别SQL中的字符串是什么
  • 语法分析
    • 在词法分析的基础上，根据语法规则，判断SQL是否满足MySQL语法，最后得到一棵语法树
    • 对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是相同的；而同一SQL随着查询数据的变化，多次查询执行时间可能不同，但硬解析的时间是不变的

词法分析 -> 语法分析可以称为硬解析

预处理器

  • 根据MySQL规则，进一步检查语法树是否合法

比如检查表、数据列是否存在；别名是否有歧义等等

优化器（怎么做）

  • 优化器会将语法树转化成执行计划（可以生成多个执行计划），然后找出最合适的执行计划（成本最少）
  • 可以通过Last_query_cost得知查询的成本
” alt=”优化器认为，这条SQL大概需要做1.399个数据页的随机查找才能完成查询” data-src=”https://user-gold-cdn.xitu.io/2019/5/18/16ac9fad386a635e?imageView2/0/w/1280/h/960/format/webp/ignore-error/1″ data-width=”1187″ data-height=”415″ />

上图表示，优化器认为，这条SQL大概需要做1.399个数据页的随机查找才能完成查询

  • 可以请求优化器解析优化过程的各个因素（explain sql）
  • 在表里面有多个索引的时候，决定使用那个索引
  • 通过特殊的关键字提示优化器，可以影响优化器的决策过程（例如：for index）
  • 优化器阶段完成后，执行计划就确定下来了
  • 优化器有时候会选择错误的执行计划
    • 索引统计信息不准
    • 优化器理解的最优执行计划不一定是响应最快的执行计划

存储引擎层

  • 负责数据的存储和提取，插件式的架构模式，支持InnoDB，MyISAM等多个存储引擎
  • 不同的存储引擎共用同一个Server层

redo log

InnoDB引擎独有的日志，存储引擎层日志

  • 当有一条记录需要更新的时候，InnoDB引擎会更新内存，并把记录写到redo log里面，然后在适当的时候，将这个操作记录更新到磁盘
  • redo log的大小是固定的
    • write pos是当前记录的位置，一边写一边后移，写到末尾又从头开始
    • check point是当前要擦除的位置，同样也是往后移动并且循环，擦除记录，先把日志更新到磁盘中

有了redo log，innodb可以保证，数据库发生异常重启，之前提交的记录都不会丢失（崩溃恢复能力，crash-safe）

bin log

  • Server层的日志，只能用于归档，，没有carsh-safe能力

之后再介绍更加详细的信息

redo log跟bin log的区别

  1. redo log是InnoDB特有的；binlog是Server层实现的，所有存储引擎都可以使用
  2. redo log是物理日志，记录“某个数据页上的修改”；binlog是逻辑日志，记录语句的原始逻辑
  3. redo log是循环写的，空间固定；binlog是可以追加写入的，不会覆盖以前的日志

一条更新语句的执行流程

通过日志两阶段提交，可以保证redo log和bin log的逻辑一致

  • 如果事务在①处奔溃了，事务会回滚
  • 如果事务在②处奔溃了，由于redo log处于prepare阶段，并且binlog也已经写入，那么在恢复事务的时候，会自动commit

Write-Ahead Logging

  • 使用日志，存储引擎可以在内存中更新数据，然后将更新持久化到磁盘的日志文件中，不需要每次都将更新后的数据刷新到磁盘（随机IO）
  • 日志采用的是追加方式，写日志的操作实在磁盘上一小块区域的顺序IO（比随机IO快得多）
  • 日志持久化到磁盘后，内存中被修改的数据（脏页）在后台可以慢慢刷新到磁盘

推荐参数设置

  • innodb_flush_log_at_trx_commit参数设置成1，表示每次事务的redo log都会持久化到磁盘
  • sync_binlog参数设置成1，表示每次事务的binlog都持久化到磁盘

发表评论