MySQL基础架构

一、MySQL 的基础架构

       MySQL可以分为 Server 层和存储引擎层两部分：

       Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

       存储引擎层则负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

       客户端发出一条SQL查询语句的执行过程:

       连接器 --> 查询缓存 --> 分析器 --> 优化器 --> 执行器

       连接器 -- 验证账号密码，维持链接，超时自动断开，链接过程复杂，建议使用长链接，连接比较占用内存，需要定时断开，5.7之后可以使用mysql_reset_connection。

       查询缓存 -- 以key-value对的形式存储之前做过的查询，key是查询语句，value是查询的结果，如果正在执行的查询，存在缓存中，那么直接返回结果。表上的任意更新都会导致表上的所有查询缓存清空。

       分析器 -- 验证语法的合规性，把sql转换成mysql内部识别的语句，表名转换成对应的id。

       优化器 -- 执行计划生成、索引选择。

       执行器 -- 验证操作库表是否有权限，调存储引擎接口查询数据并返回结果。

二、MySQL日志系统

       redo log和binlog是MySQL的两个重要的日志模块。

       首先数据库更新操作都是基于内存页，更新的时候不会直接更新磁盘，如果内存有存在就直接更新内存，如果内存没有存在就从磁盘读取到内存，在更新内存，并且写redo log，目的是为了更新效率更快，等空闲时间在将其redo log所做的改变更新到磁盘中，innodb_flush_log_at_trx_commit设置为1时，也可以防止服务出现异常重启，数据不会丢失。

       redo log是存储引擎实现的，是 InnoDB 引擎特有的，记录的是在某个数据页做了什么修改，固定大小，默认为4GB，可以循环写，解决了每次更新操作写磁盘、查找记录、然后更新整个过程效率低下的问题，redo日志将磁盘的随机写变成了顺序写，这个机制是WAL，先写日志再刷磁盘。

       binlog日志Server层实现的，所有引擎都可以使用，记录数据库结构和表记录的原始逻辑变更，类似滚动日志。更新的流程先写redo日志，写完后更新内存，到这里操作就直接返回了。后续的流程是生成此操作的binlog，然后写到磁盘。

       redo log两阶段提交，是为了保证redo log和binlog的一致性，如果redo log写入成功处于prepare阶段，写binlog失败，事务回滚，redo log会回滚到操作之前的状态。

       以下是update语句的执行流程图，图中浅色框表示是在 InnoDB 内部执行的，深色框表示是在执行器中执行的。

三、MySQL中的锁

       数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

       根据加锁范围：MySQL里面的锁可以分为：全局锁、表级锁、行级锁

       （1）全局锁：

       对整个数据库实例加锁。

       MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)

       这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。

       使用场景：全库逻辑备份。

       风险：

       1.如果在主库备份，在备份期间不能更新，业务停摆

       2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟

       官方自带的逻辑备份工具mysqldump，当mysqldump使用参数--single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

全局锁一致性读是好，但是前提是引擎要支持这个隔离级别。如果要全库只读，为什么不使用set global readonly=true的方式？

       1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。

       2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

       （2）表级锁

       MySQL里面表级锁有两种，一种是表锁，一种是元数据锁(meta data lock,MDL)

       表锁的语法是:lock tables ... read/write

       可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

       对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

       MDL：不需要显式使用，在访问一个表的时候会被自动加上。

       MDL的作用：保证读写的正确性。

       在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

       读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。

       MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

       （3）行级锁

       MySQL 的行锁是在引擎层由各个引擎自己实现的，但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

       在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

       当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，即死锁。

       解决死锁的几种方式：

       1、主动死锁检测，主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但死锁检测要耗费大量的 CPU 资源。

       2、打破产生死锁的条件，确保这个业务一定不会出现死锁，可以关闭死锁检测。

       3、控制并发粒度，且并发控制要做在数据库服务端。

四、MySQL 是怎么保证数据不丢的

       MySQL通过WAL 机制保证只要 redo log 和 binlog 保证持久化到磁盘，就能确保 MySQL 异常重启后，数据可以恢复。

       WAL 机制主要得益于两个方面：

       1、redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；

       2、组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

       事务在执行过程中，生成的 redo log 是要先写到 redo log buffer ，然后再持久化到磁盘中。

       binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

       图中的 write，指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。

       图中的 fsync，才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPS。

五、MySQL主备数据一致

       下图是基本的主备切换流程

       在状态 1 中，客户端的读写都直接访问节点 A，而节点 B 是 A 的备库，只是将 A 的更新都同步过来，到本地执行。这样可以保持节点 B 和 A 的数据是相同的。

       当需要切换的时候，就切成状态 2。这时候客户端读写访问的都是节点 B，而节点 A 是 B 的备库。

       下面我们再看看节点 A 到 B 这条线的内部流程是什么样的。下图 中画出的就是一个 update 语句在节点 A 执行，然后同步到节点 B 的完整流程图。

       备库 B 跟主库 A 之间维持了一个长连接。主库 A 内部有一个线程，专门用于服务备库 B 的这个长连接。一个事务日志同步的完整过程是这样的：

       1、在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

       2、在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。

       3、主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

       4、备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

       5、sql_thread 读取中转日志，解析出日志里的命令，并执行。

六、MySQL高可用保证

       正常情况下，只要主库执行更新生成的所有 binlog，都可以传到备库并被正确地执行，备库就能达到跟主库一致的状态，这就是最终一致性。

       主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。以下是可能导致主备延迟的原因。

       1、有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

       2、备库的压力大，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟。

       3、主库做大量的DML操作，引起延迟

       4、主库有个大事务即大表DDL在处理，引起延迟

       5、对myisam存储引擎的表做dml操作，从库会有延迟。

       6、利用pt工具对主库的大表做字段新增、修改和添加索引等操作，从库会有延迟。

       由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略，根据业务场景一般采用可靠性优先策略或者可用性优先策略。

       在满足数据可靠性的前提下，MySQL 高可用系统的可用性，是依赖于主备延迟的。延迟的时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。

可靠性优先策略

       在上图的双 M 结构下，从状态 1 到状态 2 切换的详细过程是这样的：

       1、判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步；

       2、把主库 A 改成只读状态，即把 readonly 设置为 true；

       3、判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止；

       4、把备库 B 改成可读写状态，也就是把 readonly 设置为 false；

       5、把业务请求切到备库 B。

       这个切换流程，一般是由专门的 HA 系统来完成的，我们暂时称之为可靠性优先流程。

       备注：图中的 SBM，是 seconds_behind_master 参数的简写。

       可以看到，这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复。

可用性优先策略

       如果我强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了。

       我们把这个切换流程，暂时称作可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

       在双M结构下，且binlog_format设为mixed或者statement，会导致主备数据不一致，使用 row 格式的 binlog 时，数据不一致的问题更容易被发现，因为binlog row会记录字段的所有值。

       主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，建议使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。