汗颜！工作10年去面试，被“MySQL怎么保证事物一致性”难倒了

阿牛去一家中意的公司面试，本以为凭借以往丰富的经验，肯定手到擒来，结果第一个问题，我就“出门右拐”了。

问题就是：MySQL是怎么保证事务一致性的？

回到家阿牛翻阅资料，终于搞懂了，在这里分享给大家。

定义

在搞清楚问题答案之前，先搞清楚以下几个名词以及大致的用处

redo log：

通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)、Innodb特有的，他在存储引擎层。循环写的，空间固定会用完。作用是crash-safe能力

binlog：

是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ” 是 MySQL 的 Server 层实现的，所有引擎都可以使用。是可以追加写入的,“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。作用是数据归档

undo log：

有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

SQL执行的过程

了解了以上名词之后，让我们看一下“一条更新SQL语句执行的过程是什么？”

如图1有几个关键步骤：

1、先查找记录所在的Innodb页在不在内存里；如果不在内存里则将记录所在的页加载在内存里；根据SQL语句在内存中将记录更新

2、将更新前的记录写入undolog

3、根据记录的更新值将变更写入redolog（buffer）中，并将状态变更为prepare

4、将变更记录到逻辑日志

5、redolog日志中的状态修改为commit，返回结束

至此：一条更新语句的过程结束

上面的步骤中有些同学可能会有一些疑问：为什么更新一条记录要把一整页数据加载到内存里答：因为Innodb引擎中，最小的存储单位是页为什么一定要加载到内存里？答：因为所有的计算 *** 作都是在内存里， *** 作完成后最终才写回磁盘为什么要写入redolog，直接写入磁盘，然后写入binlog就好了啊？答：这将在下面会提到，请往后看

为了加深理解，准备了下面2张图辅助理解

以图3为例，让我们看看在每个步骤出现异常的时候，到底怎么保证事物一致性的吧！1、步骤123，所有的 *** 作最多还只是内存里，如果出现宕机、断电等异常，  记录不会有任何变动，事物是一致的2、步骤4刚执行完，断电了，因为redolog还处在prepare状态，   这时候事物也是一致的3、步骤5记录binlog的过程中断电了，这时候要保证主从一致性，  事物也是不生效的，最终也是一致的4、步骤6、7如果中间任何一个时刻断电了，这时候情况就不一样了，事物是生效的，因为redolog、binlog的数据都是完整的，服务器重启后可以按照xid来去查看binlog、redolog中是否都存在，  都存在该事物就是生效的。上面就是怎么保证事务一致性的根本原因

为什么要使用redolog？

回答这个问题之前，我们先看看redolog用图形表示的

图4是redolog的形象一点的表现，并不是说redolog 长这个样子，只是为了更形象；一般情况下redolog一组4个文件，每个文件1个G，其中write pos是指redolog当前写到什么位置了，check point是指上次刷脏结束的位置，当write log和check point重合时，所有的进程停止，开始新一轮的刷脏 *** 作。刷完后redolog清空开始下一轮的写入，往返重复。

可能这样表示有点抽象，让我们看下图5

从上图中可以看的更形象一点，在sql执行的时候，会有磁盘IO将数据页加载到内存，然后在内存中将数据修改，修改后的数据页在内存中叫做脏页（叫脏页因为和磁盘中的数据不一致啊），又因为在内存中容易丢失，所以将数据页的变更记录如redolog中，随着记录插入、更新等 *** 作的增多，redolog空间慢慢的满了，这时候就开始刷脏 *** 作了，page cleaner thread线程会将所有的脏页数据刷新到磁盘，使得变更最终被持久化到磁盘。

讲到这里一定还会有人不太理解，刷脏之前断电了咋办？

这就是redolog的另一个重要的作用，crash-safe能力，实现的逻辑是这样的，断电后内存的数据都没了，重启后读取redolog文件，因为redolog文件记录的是在Innodb页x的m处做了y的修改，所以根据redolog将涉及到的Innodb页重新加载到内存，根据redolog的记录将内存中的数据重新修改，这样就能恢复断电前的数据了。

                                                                     完

下期预告：还是MySQL，敬请期待

本文首发自： 程序员阿牛

在开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步 *** 作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。

那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大量的频繁更新 *** 作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

来看下具体演示

譬如，以下两条SQL，

A：

select from t1 where f1 = 20;

B：

select from t1 where f1 = 30;

如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 80 带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

mysql> desc t1;+------------+--------------+------+-----+---------+----------------+| Field      | Type         | Null | Key | Default | Extra          |+------------+--------------+------+-----+---------+----------------+| id         | int(11)      | NO   | PRI | NULL    | auto_increment || rank1      | int(11)      | YES  | MUL | NULL    |                || rank2      | int(11)      | YES  | MUL | NULL    |                || log_time   | datetime     | YES  | MUL | NULL    |                || prefix_uid | varchar(100) | YES  |     | NULL    |                || desc1      | text         | YES  |     | NULL    |                || rank3      | int(11)      | YES  | MUL | NULL    |                |+------------+--------------+------+-----+---------+----------------+7 rows in set (000 sec)

表记录数：

mysql> select count() from t1;+----------+| count() |+----------+|    32768 |+----------+1 row in set (001 sec)

这里我们两条经典的SQL：

SQL C：

select from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：

select from t1 where rank1 =100  and rank2 =100  and rank3 =100;

表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为324365。

mysql> explain  format=json select from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G 1 row EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "324365"    },    "table": {      "table_name": "t1",      "access_type": "ALL",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "rows_examined_per_scan": 32034,      "rows_produced_per_join": 115,      "filtered": "036",      "cost_info": {        "read_cost": "323207",        "eval_cost": "1158",        "prefix_cost": "324365",        "data_read_per_join": "49K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt``t1``rank1` = 1) or (`ytt``t1``rank2` = 2) or (`ytt``t1``rank3` = 2))"    }  }}1 row in set, 1 warning (000 sec)

我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为44109，明显比之前的快了好几倍。

mysql> explain  format=json select /+ index_merge(t1) / from t1  where rank1 =1 or rank2 = 2 or rank3 = 2\G 1 row EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "44109"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "union(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1103,      "rows_produced_per_join": 1103,      "filtered": "10000",      "cost_info": {        "read_cost": "33079",        "eval_cost": "11030",        "prefix_cost": "44109",        "data_read_per_join": "473K"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt``t1``rank1` = 1) or (`ytt``t1``rank2` = 2) or (`ytt``t1``rank3` = 2))"    }  }}1 row in set, 1 warning (000 sec)

我们再看下SQL D的计划：

不加HINT，

mysql> explain format=json select from t1 where rank1 =100 and rank2 =100 and rank3 =100\G 1 row EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "53434"    },    "table": {      "table_name": "t1",      "access_type": "ref",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "idx_rank1",      "used_key_parts": [        "rank1"      ],      "key_length": "5",      "ref": [        "const"      ],      "rows_examined_per_scan": 555,      "rows_produced_per_join": 0,      "filtered": "007",      "cost_info": {        "read_cost": "47884",        "eval_cost": "004",        "prefix_cost": "53434",        "data_read_per_join": "176"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt``t1``rank3` = 100) and (`ytt``t1``rank2` = 100))"    }  }}1 row in set, 1 warning (000 sec)

加了HINT，

mysql> explain format=json select /+ index_merge(t1)/ from t1 where rank1 =100 and rank2 =100 and rank3 =100\G 1 row EXPLAIN: {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "523"    },    "table": {      "table_name": "t1",      "access_type": "index_merge",      "possible_keys": [        "idx_rank1",        "idx_rank2",        "idx_rank3"      ],      "key": "intersect(idx_rank1,idx_rank2,idx_rank3)",      "key_length": "5,5,5",      "rows_examined_per_scan": 1,      "rows_produced_per_join": 1,      "filtered": "10000",      "cost_info": {        "read_cost": "513",        "eval_cost": "010",        "prefix_cost": "523",        "data_read_per_join": "440"      },      "used_columns": [        "id",        "rank1",        "rank2",        "log_time",        "prefix_uid",        "desc1",        "rank3"      ],      "attached_condition": "((`ytt``t1``rank3` = 100) and (`ytt``t1``rank2` = 100) and (`ytt``t1``rank1` = 100))"    }  }}1 row in set, 1 warning (000 sec)

对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

以上就是关于汗颜！工作10年去面试，被“MySQL怎么保证事物一致性”难倒了全部的内容，包括:汗颜！工作10年去面试，被“MySQL怎么保证事物一致性”难倒了、面试中常问：mysql数据库做哪些优化也提高mysql性能、等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！