MySQL工作流程

mysql的工作流程:

　　

1.mysql 架构

　　mysql 分为 server 层和存储引擎

1.1.server层

　　

  • 连接器：管理连接权限验证

  • 查询缓存：命中缓存直接换回查询结果

  • 分析器：分析语法

  • 优化器：生成执行计划，选择索引

  • 执行器：操作索引返回结果

1.2. 存储引擎

　　存储引擎负责数据的存储和提取；其架构是插件式的。innodb 在 mysql5.5.5 版本开始成为 mysql 默认存储引擎。

　　

各存储引擎比对：

  • InnoDB：支持事务，支持外键，InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据，不支持全文索引。

  • MyISAM：不支持事物，不支持外键，MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的，查询效率上 MyISAM 要高于 InnnDB ，因此做读写分离的时候一般选择用 InnoDB 做主机，MyISAM 做从机

  • Memory：有比较大的缺陷使用场景很少；文件数据都存储在内存中，如果 mysqld 进程发生异常，重启或关闭机器这些数据都会消失。

1.3.sql 的执行过程

　　第一步客户端连接上 mysql 数据库的连接器，连接器获取权限，维持管理连接；连接完成后如果你没有后续的指令这个连接就会处于空闲状态，如果太长时间不使用这个连接这个连接就会断开，这个空闲时长默认是 8 小时，由 wait_timeout 参数控制。

　　第二步你往 mysql 数据库发送了一条 sql ，这个时候查询缓存开始工作，看看之前有没有执行过这个 sql ，如果有则直接返回缓存数据到客户端，只要对表执行过更新操作缓存都会失效，因此一些很少更新的数据表可考虑使用数据库缓存，对频繁更新的表使用缓存反而弊大于利。使用缓存的方法如以下 sql ，通过 SQL_CACHE 来指定：

　　select SQL_CACHE * from table where xxx=xxx

　　第三步当未命中缓存的时候，分析器开始工作；分析器判断你是 select 还是 update 还是 insert ，分析你的语法是否正确。

　　第四步优化器根据你的表的索引和 sql 语句决定用哪个索引，决定 join 的顺序。

　　第五步执行器执行 sql ，调用存储引擎的接口，扫描遍历表或者插入更新数据。

2 mysql 日志

　　2.1 mysql 日志介绍

　　mysql 有两个重要日志—— redolog 和 binlog ，redolog 是独属于 innodb 的日志，binlog 则是属于 server 层的日志。下面介绍这两个日志有什么用：当我们更新数据库数据的时候，这两个日志文件也会被更新，记录数据库更新操作。

　　redolog 又称作重做日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。它在数据库重启恢复的时候被使用，innodb 利用这个日志恢复到数据库宕机前的状态，以此来保证数据的完整性。redolog 是物理日志，记录的是某个表的数据做了哪些修改，redolog 是固定大小的，也就是说后面的日志会覆盖前面的日志。

　　binlog 又称作归档日志，它记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作。binlog 是逻辑日志，记录的是某个表执行了哪些操作。binlog 是追加形式的写入日志，后面的日志不会被前面的覆盖。

　　2.2 数据更新过程

　　我们执行一个更新操作是这样的：读取对应的数据到内存—>更新数据—>写 redolog 日志—> redolog 状态为 prepare —>写 binlog 日志—>提交事务—> redolog 状态为 commit ，数据正式写入日志文件。我们发现 redolog 的提交方式为“两段式提交”，这样做的目的是为了数据恢复的时候确保数据恢复的准确性，因为数据恢复是通过备份的 binlog 来完成的，所以要确保 redolog 要和 binlog 一致。

 

 

 

6执行计划和慢查询日志

　6.1 执行计划

　　在查询 sql 之前加上 explain 可查看该条 sql 的执行计划,如：

　　　　EXPLAIN SELECT * FROM table

　　这条 sql 会返回这样一个表：

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredextra 
1 simple                      

　　这个表便是 sql 的执行计划，我们可以通过分析这个执行计划来知道我们 sql 的运行情况。现对各列进行解释：

　　　　1）id：查询中执行 select 子句或操作表的顺序。

　　　　2）select_type：查询中每个 select 子句的类型（简单 到复杂）包括：

      • SIMPLE：查询中不包含子查询或者UNION；

      • PRIMARY：查询中包含复杂的子部分；

      • SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY；

      • DERIVED：衍生，在FROM列表中包含的子查询被标记为DERIVED；

      • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；

      • UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT；

　　　　3） type：表示 MySQL 在表中找到所需行的方式，又称“访问类型”，包括：

      • ALL：Full Table Scan， MySQL 将遍历全表以找到匹配的行；

      • index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树；

      • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于 between < > 等查询；

      • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找；

      • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；

      • onst 和 system：当 MySQL 对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量，system 是 const 类型的特例，当查询的表只有一行的情况下， 使用 system；

      • NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引。

　　　　4）possible_keys：指出 MySQL 能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

　　　　5）key：显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 NULL。

　　　　6）key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

　　　　7）ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

　　　　8）rows：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

　　　　9）Extra：其他重要信息 包括：

      • Using index：该值表示相应的 select 操作中使用了覆盖索引；

      • Using where：MySQL 将用 where 子句来过滤结果集；

      • Using temporary：表示 MySQL 需要使用临时表来存储结果集，常见于排序和分组查询；

      • Using filesort：MySQL 中无法利用索引完成的排序操作称为“文件排序”。

　6.2 慢查询日志

　　mysql 支持慢查询日志功能—— mysql 会将查询时间过长的 sql 相关信息写入日志。这个查询时间阀值由参数 long_query_time 指定， long_query_time 的默认值为 10，运行 10S 以上的查询 sql 会被记录到慢查询日志中。默认情况下，Mysql 数据库并不启动慢查询日志，需要我们手动来设置这个参数。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

可通过以下 sql 查看慢查询日志是否开启：

　　　　show variables like '%slow_query_log%';

　　通过以下 sql 开启慢查询：

　　　　set global slow_query_log=1;

　　使用 sql 修改慢查询日志设置只对当前数据库生效，如果 MySQL 重启后则会失效。如果要永久生效，就必须修改配置文件 my.cnf。

　　通过以下 sql 查看修改慢查询的阈值：

　　　　show variables like 'long_query_time%';

　　　　et global long_query_time=4;

7 主从备份

　7.1 主从备份原理

　　主从复制是指一台服务器充当主数据库服务器，另一台或多台服务器充当从数据库服务器，主服务器中的数据自动复制到从服务器之中。通过这种手段我们可以做到读写分离，主库写数据，从库读数据，从而提高数据库的可用。MySQL 主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点。

主节点 binary log dump 线程：

　　当从节点连接主节点时，主节点会创建一个 logdump 线程，用于发送 binlog 的内容。在读取 binlog 中的操作时，此线程会对主节点上的 binlog 加锁，当读取完成，甚至在发动给从节点之前，锁会被释放。

　　从节点I/O线程: 用于从库将主库的 binlog复制到本地的 relay log中，首先，从库库会先启动一个工作线程，称为IO工作线程，负责和主库建立一个普通的客户端连接。如果该进程追赶上了主库，它将进入睡眠状态，直到主库有新的事件产生通知它，他才会被唤醒，将接收到的事件记录到 relay log(中继日志)中。

从节点 SQL 线程:

　　SQL 线程负责读取 relay log 中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

7.2 主从备份延迟

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

    • 大事务，主库上必须等事务执行完成才会写入 binlog，再传给备库，当一个事物用时很久的时候，在从库上会因为这个事物的执行产生延迟。

    • 从库压力大。

　　主备延迟当然是不好的，那么有哪些办法尽量减小主备延迟呢？有下面几个办法：

    • 一主多从——多接几个从库，让这些从库来分担读的压力。这样方法适用于从库读压力大的时候。

    • 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力

8 分布式事务

　　这里不再对分布式事物的概念做普及，直接介绍两种分布式事务: XA 分布式事务和 TCC 分布式事务。

　　8.1 XA 分布式事务

　　　　XA 是两阶段提交的强一致性事物。在 MySQL 5.7.7 版本中，Oracle 官方将 MySQL XA 一直存在的一个 “bug” 进行了修复，使得MySQL XA 的实现符合了分布式事务的标准。

　　　　XA 事务中的角色：

      • 资源管理器（resource manager）：用来管理系统资源，是通向事务资源的途径。数据库就是一种资源管理器。资源管理还应该具有管理事务提交或回滚的能力。

      • 事务管理器（transaction manager）：事务管理器是分布式事务的核心管理者。事务管理器与每个资源管理器（resource manager）进行通信，协调并完成事务的处理。事务的各个分支由唯一命名进行标识。

　　　　XA 规范的基础是两阶段提交协议：

　　在第一阶段，交易中间件请求所有相关数据库准备提交（预提交）各自的事务分支，以确认是否所有相关数据库都可以提交各自的事务分支。当某一数据库收到预提交后，如果可以提交属于自己的事务分支，则将自己在该事务分支中所做的操作固定记录下来，并给交易中间件一个同意提交的应答，此时数据库将不能再在该事务分支中加入任何操作，但此时数据库并没有真正提交该事务，数据库对共享资源的操作还未释放（处于锁定状态）。如果由于某种原因数据库无法提交属于自己的事务分支，它将回滚自己的所有操作，释放对共享资源上的锁，并返回给交易中间件失败应答。

在第二阶段，交易中间件审查所有数据库返回的预提交结果，如所有数据库都可以提交，交易中间件将要求所有数据库做正式提交，这样该全局事务被提交。而如果有任一数据库预提交返回失败，交易中间件将要求所有其它数据库回滚其操作，这样该全局事务被回滚。

mysql 允许多个数据库实例参与一个全局的事务。MySQL XA 的命令集合如下：

　　   -- 开启一个事务，并将事务置于 ACTIVE 状态，此后执行的 SQL 语句都将置于该是事务中。

 

　　　　　　XA START xid

 

    •  

    • -- 将事务置于 IDLE 状态，表示事务内的 SQL 操作完成。

    • 　　　　

      • XA END xid

    • -- 事务提交的准备动作，事务状态置于 PREPARED 状态。事务如果无法完成提交前的准备操作，该语句会执行失败。

    • 　　　　

      • XA PREPARE xid

    • -- 事务最终提交，完成持久化。

    • 　　　　

      • XA COMMIT xid

    • -- 事务回滚终止

    • 　　　　

      • XA ROLLBACK xid

    • -- 查看 MySQL 中存在的 PREPARED 状态的 xa 事务。

    • 　　　　

    • 　　
　　          XA RECOVER

　　　　MySQL 在 XA 事务中扮演的是参与者的角色，被事务协调器所支配。XA 事务比普通本地事务多了一个 PREPARE 状态，普通事务是 begin-> commit 而分布式事务是 begin->PREPARE 等其他数据库事务都到 PREPARE 状态的时候再 PREPARE->commit。分布式事务 sql 示例：

　　　　xa start 'aaa';

　　　　insert into table(xxx) values(xxx);

　　　　xa end 'aaa';　

　　　　xa prepare 'aaa';

　　　　xa commit 'aaa';

XA 事务存在的问题：

  • 单点问题:事务管理器在整个流程中扮演的角色很关键，如果其宕机，比如在第一阶段已经完成，在第二阶段正准备提交的时候事务管理器宕机，资源管理器就会一直阻塞，导致数据库无法使用。

  • 同步阻塞:在准备就绪之后，资源管理器中的资源一直处于阻塞状态，直到提交完成才能释放资源。

  • 数据不一致:两阶段提交协议虽然为分布式数据强一致性所设计，但仍然存在数据不一致性的可能，比如在第二阶段中，假设协调者发出了事务 commit 的通知，但是因为网络问题该通知仅被一部分参与者所收到并执行了 commit 操作，其余的参与者则因为没有收到通知一直处于阻塞状态，这时候就产生了数据的不一致性。

　8.2 TCC 分布式事务

　　TCC 又被称作柔性事务，通过事务补偿机制来达到事务的最终一致性，它不是强一致性的事务。TCC 将事务分为两个阶段，或者说是由两个事务组成的。相对于 XA 事务来说 TCC 的并发性更好，XA 是全局性的事务，而 TCC 是由两个本地事务组成。

　　假设我们购买一件商品，后台需要操作两张表——积分表加积分而库存表扣库存，这两张表存在于两个数据库中，使用 TCC 事务执行这一事务：

　　1）TCC 实现阶段一：Try

　　在 try 阶段并不是直接减库存加积分，而是将相关数据改变为预备的状态。库存表先锁定一个库存，锁定的方式可以预留一个锁定字段，当这个字段为一的时候表示这个商品被锁定。积分表加一个数据，这个数据也是被锁定状态，锁定方式和库存表一样。其 sql 形如：

　　　　update stock set lock=1 where id=1;

　　　　nsert into credits (lock,...) values (1,...)

　　这两条 sql 如果都执行成功则进入 Confirm 阶段，如果执行不成功则进入 Cancel 阶段

　　2）TCC 实现阶段二：Confirm

　　这一阶段正式减库存加积分订单状态改为已支付。执行 sql 将锁定的库存扣除，为累加积分累加，以及一些其他的逻辑。

　　3）TCC 实现阶段三：Cancel

　　当 try 阶段执行不成功，就会执行这一阶段，这个阶段将锁定的库存还原，锁定的积分删除掉。退回到事务执行前的状态。

　　TCC 事务原理很简单，使用起来却不简单。首先 TCC 事务对系统侵入性很大，其次是让业务逻辑变得复杂。在实际使用中我们必须依赖 TCC 事务中间件才能让 TCC 事务得以实现。通常一个 TCC 事务实现大概是这样子的：某个服务向外暴露了一个服务，这个服务对外正常调用，其他服务并不能感知到 TCC 事务的存在，而其服务内部，分别实现了 Try,Confirm,Cancel 三个接口，注册到 TCC 中间件上去。当调用这个服务的时候，其事务操作由该服务和 TCC 中间件共同完成。

　　而 TCC 事务中间件还要做好其他事情，比如确保 Confirm 或者 Cancel 执行成功，如果发现某个服务的 Cancel 或者 Confirm 一直没成功，会不停的重试调用他的 Cancel 或者 Confirm 逻辑，务必要他成功！即使在尝试多次后无法成功也能通知到系统需要人工排查异常。TCC 事务还要考虑一些异常情况的处理，比如说订单服务突然挂了，然后再次重启，TCC 分布式事务框架要能够保证之前没执行完的分布式事务继续执行。TCC 分布式事务框架还需要做好日志的记录，保存下来分布式事务运行的各个阶段和状态，以便系统上线后能够排查异常，恢复数据。目前开源的 TCC 事务框架有：Seata ByteTCC tcc-transaction等。

posted @ 2019-08-27 00:42  笑得好美  阅读(2856)  评论(0)  编辑  收藏  举报