sql server更新统计 更新记录sql

1. Update语句执行基本流程

首先，我们先创建一个表，拥有两个字段，ID（主键，整形）和 c（整形）

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

接着，我们对于ID=2的行进行更新

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

然后，其实update的基本流程和select的基本流程相似，具体如下：

1. 用户通过连接器建立起连接
2. 分析器分析出来这个SQL语句是一条更新语句
3. 优化器决定要使用ID这个索引
4. 执行器找到这一行，进行更新。

而select语句和update语句不同的地方在于，update还会涉及两个重要的日志模块：redo log（重做日志）和 bin log（归档日志）。

2. redo log（重做日志）

小故事
   在孔乙己的故事里，酒店掌柜有一个粉板，经常会在白天用来记录顾客的赊账，然后到晚上空闲的时候
，才会将顾客的赊账记录转移到账本里面去。这是因为如果白天就直接记录到账本里面去，那么会耗费很多
时间，来不及招待顾客，所以就直接先写在粉版上。
   但是粉板有固定的大小，如果当天客户太多，那掌柜在白天就需要将粉板上的内容转移到账本上，这样
后面赊账的顾客才能记录到粉板上。
   此外，如果老板临时有事歇业两天，但是只要赊账记录记录在了粉板或者账本上，恢复生意后依然可以
通过账本和粉板上的数据明确赊账数目

在MySQL中，和掌柜面临着同样的问题，如果每一次更新操作都写进磁盘，然后磁盘找到那条对应的记录，然后更新，整个过程IO成本很高。

因此，MySQL中有一个Write-Ahead logging（WAL）技术，它的关键点就是先写日志，再写入磁盘。（相当于先写粉板，再写入账本。）
具体来说，当一条记录需要更新的时候，InnoDB就会先把记录写道redo log（粉板）里面，并更新内存，这个时候更新就完成了。同时，InnoDB会在适当的时候，将这个操作记录更新到磁盘里面（写入账本）。

redo log时InnoDB引擎特有的日志。此外，InnoDB的redo log是固定大小的，比如可以配置为4个文件，每个文件1GB。从头开始写，写到末尾就循环开始写。如下图所示，write pos代表写入的位置，从logfile0一直写入到logfile3，再循环，即顺时针方向。check point代表当前要擦除的位置，也是顺时针方向。图中绿色部分是可以写入的位置，黄色部分是已经写入内容的位置。当write pos指针追上check point时就代码log已满。

最后，因为有了redo log，InnoDB就可以保证数据库发生异常重启时，之前提交的记录不会丢失，这个能力称为crash-safe。（对比故事中的酒店停业后恢复）

3. bin log（归档日志）

bin log是Server层的日志，无论使用什么存储引擎，都是会有bin log的。它也是用来保存用户对于数据库的修改操作的日志。bin log和redo log不太一样，它不是采用循环写入的方式，二是进行不断追加的。此外，bin log没有crash-safe能力，只能进行归档。

数据库的恢复通常就会用到bin log。如果想让数据库恢复到半个月内任意一秒的状态，那么就需要对于半个月前的数据进行一次全量备份，然后再根据bin log依次取出来操作，重放到想要恢复的时刻。

4. 两种日志的区别

首先，两种日志都是用来记录对于数据库的修改，那么为什么需要两种日志呢？
理由：这主要是因为最开始MySQL并没有InnoDB引擎，默认是使用MyISAM引擎的，但是MyISAM没有crash-safe能力，bin log只能用于归档。所以引入InnoDB的redo log来是实现crash-safe能力。

然后，两种日志的主要区别如下：

1. redo log是InnoDB引擎特有的。而bin log是属于MySQL的Server，所有引擎都可以用。
2. redo log是物理日志，记录的是"在某个数据页上具体做了什么修改"。例如：T表第100行、第100列从0修改为1.
   bin log是逻辑日志，记录的是这个句子的原始逻辑。例如：给ID=2这一行的c字段加1
3. redo log是循环写的，空间固定会用完。bin log则是采用追加写的方式，写到一定大小后会切换到写一个，不会覆盖之前的。

5. 执行器和存储引擎进行Update的内部执行流程

下面是执行和InnoDB引擎执行简单的update语句的流程：

1. 执行引擎先找到ID=2这一行，如果这一行本身就在内存中就直接返回。如果不在的话，就从磁盘读取到内存中。
2. 执行器拿到引擎给的数据，更新这一行数据，再调用引擎接口写入这行数据。
3. 引擎将这行数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后就告诉执行器完成了，随时可以提交事务。
4. 执行器生成这个操作的bin log，并把bin log写入到磁盘中。
5. 执行器调用引擎的事务提交接口，引擎把刚刚写入的redo log改成commit状态，更新完成。

上面的流程也如下面这个图所示，深色的代表执行器，浅色的代表存储引擎：

6. 两阶段提交

从刚刚的执行步骤中可以看到，redo log的操作是分为两个阶段：prepare和commit阶段。那为什么要分为两个阶段呢？

我们假设不分为两个阶段，然后发生了这样的事情：假设存储引擎先写完了redo log，但是接着Server在写入bin log时发生了异常重启。因为redo log具有crash-safe功能，所以重启后可以恢复。但是由于bin log没有crash-safe功能，就会丢失一部分日志。

这样就会出现一个问题，存储引擎中记录了这次修改，之前会写入磁盘中。但是Server中的bin log没有这次修改。那么在利用bin log进行数据库恢复时，就会丢失一些操作。造成恢复的数据库和原来的数据库不一致的情况。

而两阶段提交中，当redo log进入prepare状态后，会等待bin log成功写入，再进行commit，这样就能保证数据的一致性。相当于是进行一个事务。

redo log的相关参数是innodb_flush_log_at_trx_commit，设置成1时，表示每次事务都持久化到磁盘。（建议这么做）
bin log的相关参数是sync-binlog，设置为1时，表示每次事务的binlog都会持久化到磁盘中。（建议这么做）