mysql基础

Pgsql系列文章链接：

pg的编译安装 (Jan 1, 2022)
pg高可用 (Jan 1, 2022)
pg为何需要HOT (Jan 1, 2022)
pg常用查询 (Jan 1, 2022)
LSM-Tree (Jan 1, 2022)
innodb (Jul 1, 2022)
mysql基础 (Jul 1, 2022)
乐观锁和悲观锁 (Jul 1, 2022)
全文索引 (Jul 1, 2022)
问题 (Jul 1, 2022)

一条sql语句的执行过程

            {客户端}
             | | |
+------------+-+-+-----------+
|            v v v           |
|           {连接器}---------+--->管理连接,权限验证
|           /      \         |
|          v        v        |
|  {查询缓存}<-----{分析器}---+--->词法分析,语法分析
|                      |     |
|                      v     |
|                  {优化器}--+--->执行计划生成,索引选择
|                      |     |
| Server层             v     |
|                  {执行器}--+--->操作引擎,返回结果
|                            |
+--------------+-------------+
               v
+--------------+-------------+
| {存储}    {存储}    {存储}  |
| {引擎}    {引擎}    {引擎}  |--->存储数据,提供读写接口
+----------------------------+

1 连接器

连接命令： mysql -h$ip -P$port -u$user -p
参数：
- max_connections：用来控制一个MySQL实例同时存在的连接数的上限，超过这个值，
  系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。
  - 每个连接对象都会占用一定的内存来支持sql的执行，所以连接一旦多起来，可能导致OOM。
  - 1. MySQL 5.7以下的版本，需要定期断开长连接。
  - 1. MySQL 5.7或更新版本，需要定期执行mysql_reset_connection来重新初始化连接资源。

过程：

3次握手，建立TCP连接
身份认证:
- 如果用户名或密码不对，就会收到"Access denied for use"错误，并执行4次挥手关闭TCP连接
- 身份认证通过后，连接器就从权限表中拉取登录用户所拥有的权限到内存。
  - 如果命中查询缓存，在返回缓存的结果前，会做权限认证
  - 在优化器之前会调用 precheck 验证权限
  - 在执行器之前会调用 precheck 验证权限
  - 如果没有权限，就会报如下的错误：
    1 2
    
    mysql> select * from T where ID=10; ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，如果空闲时间超过wait_timeout(默认值为8小时)就会断开连接。

对一个已关闭的连接发送请求，就会收到"Lost conenction to MySQL server during query"的错误提示。

mysql> show processlist;
+----+------+-----------------+------+---------+------+---------+------------------+
| Id | User | Host            | db   | Command | Time | State   | Info             |
+----+------+-----------------+------+---------+------+---------+------------------+
|  5 | root | localhost:27710 | test | Sleep   |   16 |         | NULL             |
|  6 | root | localhost:27712 | test | Query   |    0 | staring | show processlist |
+----+------+------------------+------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

mysql-client
- -A参数：关闭代码补全功能
  - 为了支持代码补全功能，mysql-client在连接成功后，会额外执行如下操作：show databases; use dbname; show tables;，并使用本地哈希表存储两个show命令的结果。
- –quick(或者简写为-q)：不仅关闭代码补全功能，还会选择不缓存服务端返回的结果。

2 分析器

如果输入的sql语句语法有问题，就会收到"You have an error in your SQL syntax"的错误提示。

分析器阶段，该阶段会判断语句是否正确，表是否存在，列是否存在。

如果sql语句中使用了表中没有的字段k，就会在分析器阶段报“Unknown column ‘k’ in ‘where clause’”

3 优化器

在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
根据索引基数，决定是否使用该索引，有多个索引的时候决定使用哪个索引。

索引基数的价值：

一个索引上不同的值的个数，称之为“基数”。
索引基数越大，回表的次数就越少，索引的效率就越高。
我们可以使用show index from t方法，看到一个索引的基数。

MySQL是怎样得到索引的基数的呢？

采样统计法：统计 N 个数据页上的不同值，除以N得到平均值，再乘以该索引的总页面数，就得到这个索引的基数了。
当变更的数据行数超过1/M 的时候，会自动触发一次索引统计。
索引基数的存储：通过innodb_stats_persistent参数来控制：
- on : 持久化存储，此时N是20，M是10
- off : 只存储在内存中，此时N是8，M是16

优化器为什么会选错索引呢？

错误判断需要扫描的行数，导致优化器选错索引
- 使用analyze table t 命令重新统计索引信息
选错索引的处理办法：
- 使用force index强行选择一个索引
- 改造order by语句让优化器选择正确的索引
- 索引优化，让优化器选择正确的索引

索引失效

隐式类型转换
1

mysql> select * from tradelog where tradeid=110717;
其中tradeid的类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。在MySQL中，字符串和数字做比较的话，是将字符串转换为数字。所以相当于执行:
1

mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

隐式字符编码转换

`1`	`mysql> select * from trade_detail where tradeid=$L2.tradeid.value;`

其中，trade_detail.tradeid的字符编码是utf8，而$L2.tradeid.value的字符编码是utf8mb4。所以相当于执行：

`1`	`mysql> select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;`

当要查询的字符串超过表字段定义的长度时

mysql> CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;
# 假定表里面，有100万行数据，其中有10万行数据的b的值是'1234567890'
mysql> select * from table_a where b='1234567890abcd';

执行流程如下：

在传给引擎执行的时候，做了字符截断。因为引擎里面这个行只定义了长度是10，所以只截了前10个字节，就是'1234567890’进去做匹配；
这样满足条件的数据有10万行；
因为是select *，所以要做10万行回表；
但是每次回表以后查出整行，到server层以判断，b的值都不是'1234567890abcd’；
返回结果是空。

4 执行器

设置慢查询： set long_query_time=0;

4.1 查询语句

select id from T where k=5，k上没有索引，该查询语句的全表扫描流程如下：

    [取第i=1行数据]   i最终的值就是rows_examined的值
            |
            v<--------------------------------+
    <第i行是否存在>                            |
            |是                               |
            |        否                       |
    <数据页在内存中？>----->{从磁盘中读入内存}   |
            |是                |              |
            v                  |              |
        {返回行数据}<-----------+           [i++]
            v                                 |
      <判断这行的k值是否等于5> ----->-----------+
            v 是                              |        注意:
      [将该行放在net_buffer中]----->-----------+        1. []表示由执行器执行
                                                      2. {}表示由innodb引擎执行

如果k上有索引，该查询语句的执行流程如下：

    [取k=5这一行]
            |
            v          否
    <数据页在内存中?>----->{磁盘中读入内存}
            |是                |
            v                  |
        {返回行数据}<-----------+
            v
        <是否是唯一索引>---->[将该行放在net_buffer中]
            |
            v 否 <-------------------------------+
       [取下一行]                                 |
            v          否                        |
      <数据页在内存中？>----->{从磁盘中读入内存}    |
            |是                 |                |
            v                   |                |
        {返回行数据}<------------+                |
            |                                    |
      [将该行放在net_buffer中]                    |
            v                                    |
      <判断这行的k值是否等于5>-----是--------------+

server层取数据和发送数据的流程是这样的：

获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k。
重复获取行，直到net_buffer写满，调用网络接口发出去。
如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。
如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送。socket send buffer的长度定义在/proc/sys/net/core/wmem_default下。

                             +-----------------------------+
                             |+-----------------+   Server |
                             ||[data]     MySQL |          |
                             ||  v              |          |
                             ||[net_buffer]     |          |
                             |+----|------------+          |
+------------------------+   |     |                       |
|         client         |   |     +---------+             |
|                        |<--|               v             |
| [socket receive buffer]|   |        [socket send buffer] |
+------------------------+   +-----------------------------+

也就是说，MySQL是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长。这个时候，执行show processlist 会发现该线程一直处于"Sending to client"状态，表示服务器端的网络栈写满了。

与"Sending to client"长相很类似的一个状态是"Sending data"，并不一定是指“正在发送数据”，而可能是处于执行器过程中的任意阶段，它的真实意思其实是“正在执行”。

4.1.1 order by是怎么工作的?

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

select city,name,age from t where city='杭州' order by name limit 1000;

全字段排序 explain select city,name,age from t where city=‘杭州’ order by name limit 1000; 可以发现结果中的Extra字段显式为: Using index condition; Using filesort 所以，这是一个全字段排序。

执行流程如下：

初始化sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city=‘杭州’条件的主键 id，也就是图中的ID_X；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入sort_buffer中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的ID_Y；
对sort_buffer中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

第6步中的是否采用快速排序却决于排序所需的内存和sort_buffer_size参数。如果排序所需的内存大于sort_buffer_size，就需要使用外部排序。

可以使用如下的方法来确定一个排序语句是否使用了临时文件：

/* 打开optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=on';

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

rowid排序 max_length_for_sort_data是MySQL中专门控制用于排序的行数据的长度的一个参数。如果单行的长度超过了这个值，就会采用rowid排序。

执行流程如下：

初始化sort_buffer，确定放入两个字段，即 name 和 id；
从索引 city 找到第一个满足 city=‘杭州’条件的主键 id，也就是图中的ID_X；
到主键 id 索引取出整行，取 name、id 这两个字段，存入sort_buffer中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到不满足 city=‘杭州’条件为止，也就是图中的ID_Y；
对sort_buffer中的数据按照字段 name 进行排序；
遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
MEMORY 引擎不是索引组织表。因此，这个 rowid 其实就是数组的下标。

Limit MySQL 处理 limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前 Y 个，然后把下一个记录作为返回结果，因此这一步需要扫描 Y+1 行。再加上，第一步扫描的 C 行，总共需要扫描 C+Y+1 行。

4.1.2 连接查询

内连接

SELECT * FROM a INNER JOIN b ON a.aID = b.bID;
# 等价于
SELECT * FROM a JOIN b ON a.aID = b.bID;
# 等价于
SELECT * FROM a,b WHERE a.aID = b.bID;

左外连接

1
2
3

SELECT * FROM a LEFT JOIN b ON a.aID = b.bID;
# 等价于
SELECT * FROM a LEFT OUTER JOIN b ON a.aID = b.bID;

全外连接

1
2
3

SELECT * FROM a FULL JOIN b ON a.aID = b.bID:
# 等价于
SELECT * FROM a FULL OUTER JOIN b ON a.aID = b.bID;

交叉连接（取笛卡尔积）

# 会查询到`a表*b表`条记录
SELECT * FROM a CROSS JOIN b;
# 等价于
SELECT * FROM a, b;

自然连接（MySQL自己判断连接条件）

内自然连接:

1
2
3

SELECT * FROM one NATURAL JOIN two;
# 等价于
SELECT * FROM one INNER JOIN two USING(public_field);

外自然连接:

1
2
3

SELECT * FROM one NATURAL LEFT JOIN two;
# 等价于
SELECT * FROM one inner LEFT JOIN two USING(public_field);

4.1.3 到底可不可以使用join?

场景创建

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

Index Nested-Loop Join(NLJ)

# 使用straight_join让MySQL使用固定的连接方式执行查询，
# 这样优化器只会按照我们指定的方式去join。
mysql> explain select * from t1 straight_join t2 on (t1.a=t2.a);
+--+-----------+-----+----------+----+-------------+----+-------+---------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref      |rows|filtered|Extra      |
+--+-----------+-----+----------+----+-------------+----+-------+---------+----+--------+-----------+
| 1|SIMPLE     |t1   |NULL      |ALL |a            |NULL|NULL   |NULL     | 100|  100.00|Using where|
+--+-----------+-----+----------+----+-------------+----+-------+---------+----+--------+-----------+
| 1|SIMPLE     |t2   |NULL      |ref |a            |a   |5      |test.t1.a|   1|  100.00|NULL       |
+--+-----------+-----+----------+----+-------------+----+-------+---------+----+--------+-----------+

执行流程：

1. 从表t1中读入一行数据R；
2. 从数据行R中，取出a字段到表t2里去查找；
3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
4. 重复执行步骤1到3，直到表t1的末尾循环结束。

时间复杂度分析：假设被驱动表的行数是 M。每次在被驱动表查一行数据，要先搜索索引 a，再搜索主键索引。每次搜索一棵树近似复杂度是以 2 为底的 M 的对数，记为 log2M，所以在被驱动表上查一行的时间复杂度是2*log2M。

假设驱动表的行数是 N，执行过程就要扫描驱动表 N 行，然后对于每一行，到被驱动表上匹配一次。

因此，整个执行过程，近似复杂度是N + N*2*log2M。

显然，N对扫描行数的影响更大，因此应该让小表来做驱动表。

Block Nested-Loop Join(BLJ)

mysql> explain select * from t1 straight_join t2 on (t1.a=t2.b);
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra                                            |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------------------------+
| 1|SIMPLE     |t1   |NULL      |ALL |a            |NULL|NULL   |NULL| 100|  100.00|NULL                                             |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------------------------+
| 1|SIMPLE     |t2   |NULL      |ALL |NULL         |NULL|NULL   |NULL|1000|   10.00|Using where; Using join buffer(Block Nested Loop)|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-------------------------------------------------+

被驱动表上没有可用的索引时，算法的流程是这样的：

1. 把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存；

2. 扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回。

之所以将表t1所有满足条件的行都放在join_buffer中，是为了减少表t2的扫描次数。由于对于t2表中的每一行，都只需要对内存join_buffer中t1的数据遍历一遍就行了，因此总的扫描次数是100+1000=1100次。

由于join_buffer是以无须数组的方式组织的，因此对表t2中每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。

join_buffer的大小是由参数join_buffer_size设定的，默认值是 256k。如果放不下表 t1 的所有数据话，策略很简单，就是分段放。我把join_buffer_size改成 1200，再执行:

`1`	`select * from t1 straight_join t2 on (t1.a=t2.b);`

执行过程就变成了:

1. 扫描表 t1，顺序读取数据行放入 join_buffer 中，放完第 88 行 join_buffer 满了，继续第 2 步；
2. 扫描表 t2，把 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回；
3. 清空 join_buffer；
4. 继续扫描表 t1，顺序读取最后的 12 行数据放入 join_buffer 中，继续执行第 2 步。

总结能不能使用 join 语句？

如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种 join 尽量不要用。

添加索引，将BLJ转换为NLJ

使用临时表，在临时表上加索引，将BLJ转换为NLJ

1
2
3

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

在业务层使用hash join来取代BLJ

1
2
3

select * from t1;取得表 t1 的全部 1000 行数据，在业务端存入一个 hash 结构。
select * from t2 where b>=1 and b<=2000; 获取表 t2 中满足条件的 2000 行数据。
把这 2000 行数据，一行一行地取到业务端，到 hash 结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

4.2 更新语句

表: create table T(ID int primary key, c int); 更新: update T set c=c+1 where ID=2;

与查询语句不一样的是，更新语句还涉及两个重要的日志模块：redo log(重做日志)和 binlog(归档日志)。

redo log 不知道你还记不记得《孔乙己》这篇文章，酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本。

如果有人要赊账或者还账的话，掌柜一般有两种做法：

一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；
另一种做法是先在粉板上记下这次的账，等打烊以后再把账本翻出来核算。

在生意红火柜台很忙时，掌柜一定会选择后者，因为前者操作实在是太麻烦了。首先，你得找到这个人的赊账总额那条记录。你想想，密密麻麻几十页，掌柜要找到那个名字，可能还得带上老花镜慢慢找，找到之后再拿出算盘计算，最后再将结果写回到账本上。

这整个过程想想都麻烦。相比之下，还是先在粉板上记一下方便。你想想，如果掌柜没有粉板的帮助，每次记账都得翻账本，效率是不是低得让人难以忍受？

同样，在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。

而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

如果今天赊账的不多，掌柜可以等打烊后再整理。但如果某天赊账的特别多，粉板写满了，又怎么办呢？这个时候掌柜只好放下手中的活儿，把粉板中的一部分赊账记录更新到账本中，然后把这些记录从粉板上擦掉，为记新账腾出空间。

与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

                 write_pos
              _>   /                  1. wirte_pos和check_point都
ib-logfile-0./  \.<                      顺时针移动
          ./      \. ib-logfile-1
         /          \                 2. write_pos< i < check_point
        (            )<--check_point     之间的表示空闲空间
         \          /
          *\      /* ib-logfile-2
ib-logfile-3*\  /*
              -<

有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

binlog和redo log的区别

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

执行update语句时的内部流程:

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

      [取ID=2这一行]
            |
            v          否
    <数据页在内存中?>----->{磁盘中读入内存}
            |是                |
            v                  |
        {返回行数据}<-----------+
            v
      [将这行的C值加1]
            v                       注意:
         [写入新行]                 1. []表示由执行器执行
            v                       2. {}表示由innodb引擎执行
      {新行更新到内存}
            v
      {写入redolog    }
      {处于prepare阶段}
            | 时刻A
            v
        [写binlog]
            | 时刻B
            v   <-----------执行器调用引擎的提交事务接口
      {提交事务      } 
      {处于commit状态}<---引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

* redo log(ib_log_fileX)的写入分为两个步骤：prepare和commit，这就是“两阶段提交”
* 数据表空间(t.ibd)：存放表数据
* 系统表空间(ibdata1)：共享表空间，用于存放数据字典，比如表结构定义
      * 注意MySQL 8.0以前的版本，表结构定义是存放在.frm文件中的。
      * 在MySQL 5.5及以前的版本中，ibdata1中也存放undo日志。
* 表数据既可以存放在共享表空间里，也可以是单独的文件，
  这个行为是由参数`innodb_file_per_table`控制的。
      * 值为OFF， 表的数据放在系统共享表空间，也就是跟数据字典放在一起；
      * 值为ON， 每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。
      * 建议不论使用 MySQL 的哪个版本，都将这个值设置为 ON。
        因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，
        通过 drop table 命令，系统就会直接删除这个文件。
        而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

* 时刻A异常重启：此刻redo log处于prepare阶段，binlog还没有写，所以崩溃恢复时，该事务会回滚。
* 时刻B异常重启：此刻redo log处于prepare阶段，binlog已写完，崩溃恢复时MySQL会怎么处理？
1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交;
2. 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整;
   a. 如果是，则提交事务;
   b. 否则，回滚事务。

使用反证法来解释“为什么redo日志需要两阶段提交” 对于语句update T set c=c+1 where ID=2，假设当前ID=2的行，字段c的值为0。再假设执行update语句过程中在写完第一个日志后，第二个日志还没有写完就发生了crash

先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的， redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。
先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。而“两阶段提交”的作用就是让binlog和redo log这两种日志的状态保持逻辑上的一致。

配置优化

innodb_flush_log_at_trx_commit这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

binlog的两种模式 statement 格式的话是记sql语句， row格式会记录行的内容，记两条，更新前和更新后都有。
MySQL怎么知道binlog是完整的？

redo log和binlog有一个共同的数据字段，叫XID。崩溃恢复的时候，会按顺序扫描redo log:
* 如果碰到既有prepare、又有commit的redo log，就直接提交；
* 如果碰到只有prepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

一个事务的binlog是有完整格式的：
* statement格式的binlog，最后会有COMMIT;
* row格式的binlog，最后会有一个XID event;
另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的
正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验
checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。

redo log buffer是什么？是先修改内存，还是先写redo log文件？

在一个事务的更新过程中，日志是要写多次的。比如下面这个事务：
begin;
insert into t1 ...
insert into t2 ...
commit;

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存在redo log buffer，
真正把日志写到redo log文件(文件名是 ib_logfile+数字 )，是在执行commit语句的时候做的。

专题: pgsql

本文发表于 2022-07-01，最后修改于 2022-07-01。

本站永久域名「 jiavvc.top 」，也可搜索「后浪笔记一零二四」找到我。

上一篇 « 乐观锁和悲观锁下一篇 » innodb

mysql基础

1 连接器

2 分析器

3 优化器

4 执行器

4.1 查询语句

4.1.1 order by是怎么工作的?

4.1.2 连接查询

4.1.3 到底可不可以使用join?

4.2 更新语句

推荐阅读