mysql总结（一）

随风而逝君
138 阅读14分钟

1.mysql逻辑架构

mysql>select * from where id = 1;

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

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

1.1 连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接。

1.2 查询缓存

MySQL拿到查询请求后，先查询缓存（之前执行结果可能以key-value对形式缓存在内存中），若命中缓存，直接缓存结果，效率很高。

大多数情况下建议不要使用查询缓存，因为查询缓存的失效非常频繁，命中率非常低，将参数 query_cache_type 设置成 DEMAND，可以关闭查询缓存。

1.2 分析器

解析SQL语句，分析器先会做“词法分析”，输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么，根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

语法不读时提示：(42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from t where ID=1' at line 1一般语法错误会提示第一个出现错误的位置，所以要关注的是紧接“use near”的内容。

1.3 优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

比如你执行下面这样的语句，这个语句是执行两个表的 join：

mysql> select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

1.4 执行器

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

mysql> select * from T where id =10;

1.5 存储引擎

MyISAM和Innodb的区别：

  1. InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
  2. InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
  3. InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
  4. InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
  5. InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。

2.binlog、redo log、undo log

我们先来看一条更新语句。

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

最后三步是将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

注：

  1. 若数据页在内存中存在，直接更新内存即可
  2. 唯一索引：若数据页在内存中不存在，则需从磁盘读入内存
  3. 普通索引：若数据页在内存中不存在，直接写change buffer即可

2.1 redo log

2.1.1 redo 写入机制

这三种状态分别是：

  1. 存在 redo log buffer 中，物理上是在 MySQL 进程内存中，就是图中的红色部分；
  2. 写到磁盘 (write)，但是没有持久化（fsync)，物理上是在文件系统的 page cache 里面，也就是图中的黄色部分；
  3. 持久化到磁盘，对应的是 hard disk，也就是图中的绿色部分。

日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盘的速度就慢多了。

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参数，它有三种可能取值：

  1. 设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  2. 设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；
  3. 设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

2.1.2 redo log记录形式

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

write pos 和 checkpoint 之间的是redo log buffer上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示redo log buffer满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

2.2 undo log

undo log 主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里。

2.2.1 undo log 的用途

保证事务进行rollback时的原子性，当事务进行回滚的时候可以用undo log的数据进行恢复。

用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本。

2.2.2 undo log类型

insert undo log

代表事务在insert新记录时产生的undo log , 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log（主要）

事务在进行update或delete时产生的undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

2.3 binlog

binlog用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。binlog是mysql的逻辑日志，并且由Server层进行记录，使用任何存储引擎的mysql数据库都会记录binlog日志。

逻辑日志：可以简单理解为记录的就是sql语句。

物理日志：因为mysql数据最终是保存在数据页中的，物理日志记录的就是数据页变更。

binlog是通过追加的方式进行写入的，可以通过max_binlog_size参数设置每个binlog文件的大小，当文件大小达到给定值之后，会生成新的文件来保存日志。

2.3.1 binlog使用场景

在实际应用中， binlog的主要使用场景有两个，分别是 主从复制 和 数据恢复 。

  1. 主从复制 ：在 master端开启 binlog，然后将 binlog发送到各个 slave端，slave端重放 binlog从而达到主从数据一致。
  2. 数据恢复 ：通过使用 binlog工具来恢复数据。

2.3.2 binlog刷盘时机

对于 Innodb存储引擎而言，只有在事务提交时才会记录binlog，此时记录还在内存中，那么binlog

是什么时候刷到磁盘中的呢？ mysql是通过sync_binlog参数控制 binlog的刷盘时机，取值范围是0-N：

  • 0：不去强制要求，由系统自行判断何时写入磁盘；
  • 1：每次 commit的时候都要将binlog写入磁盘；
  • N：每N个事务，才会将 binlog写入磁盘。

从上面可以看出， sync_binlog最安全的是设置是1，这也是 MySQL 5.7.7之后版本的默认值。

2.3.3 binlog格式

binlog 的三种格式 一种是 statement，一种是 row。还有有第三种格式，叫作 mixed，其实它就是前两种格式的混合。

mysql> CREATE TABLE t ( id int(11) NOT NULL, a int(11) DEFAULT NULL, t_modified timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY a (a), KEY t_modified(t_modified)) ENGINE=InnoDB;

insert into t values(4,4,'2018-11-10');

insert into t values(5,5,'2018-11-09');

mysql> delete from t where a>=4 and t_modified<='2018-11-10' limit 1;

为了说明 statement 和 row 格式的区别，我们来看一下这条 delete 命令的执行效果图：

可以看到，运行这条 delete 命令产生了一个 warning，原因是当前 binlog 设置的是 statement 格式，并且语句中有 limit，所以这个命令可能是 unsafe 的。

delete 带 limit，很可能会出现主备数据不一致的情况。如果 delete 语句使用的是索引 a，那么会根据索引 a 找到第一个满足条件的行，也就是说删除的是 a=4 这一行；但如果使用的是索引 t_modified，那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。

由于 statement 格式下，记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的，因此可以将binlog改成row格式。

statement 格式 binlog 示例

row 格式 binlog 示例

2.4 两阶段协议

我们就一起分析一下在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象。

如果在图中时刻 A 的地方，也就是写入 redo log 处于 prepare 阶段之后、写 binlog 之前，发生了崩溃（crash），由于此时 binlog 还没写，redo log 也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog 还没写，所以也不会传到备库。到这里，大家都可以理解。

大家出现问题的地方，主要集中在时刻 B，也就是 binlog 写完，redo log 还没 commit 前发生 crash，那崩溃恢复的时候 MySQL 会怎么处理？

我们先来看一下崩溃恢复时的判断规则。

1.如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；

2.如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：

a. 如果是，则提交事务；

b. 否则，回滚事务。这里，时刻 B 发生 crash 对应的就是 2(a) 的情况，崩溃恢复过程中事务会被提交。

如何判断binlog是否完整？

开启Binary log的MySQL在crash recovery时：MySQL在prepare阶段会生成xid，然后会在commit阶段写入到binlog中。在进行恢复时事务要提交还是回滚，是由Binlog来决定的。

　　– 事务的Xid_log_event存在，就要提交。

　　– 事务的Xid_log_event不存在，就要回滚。

恢复的过程非常简单：

　　– 扫描最后一个Binlog文件（进行rotate binlog文件时，确保老的binlog文件对应的事务已经提交），提取其中的Xid_log_event

　　– 重做检查点以后的redo日志，将事务的xid与binlog中的xid对比，若存在，则提交，否则就回滚。

3.事务、隔离级别

3.1 隔离级别

提到事务，你肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），今天我们就来说说其中 I，也就是“隔离性”。当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要在二者之间寻找一个平衡点。

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

  1. 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
  2. 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
  3. 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
  4. 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

mysql> create table T(c int) engine=InnoDB;

　insert into T(c) values(1);

执行结果：

隔离级别V1V2V3
读未提交222
读已提交122
可重复读111
串行化事务B执行将1改成2时会被锁住，直到事务A提交

3.2 脏读、不可重复读、幻读

3.2.1 脏读

一个事务读取到了另一事务未提交的数据，造成select前后数据不一致。

3.2.2 不可重复读　

读指一个事务读取到了另一事务已提交的数据，造成select前后数据不一致。 比如事务A修改了一些数据并且提交了，此时事务B却读取了，这时事务B就形成了不可重复读。

3.2.3 幻读

CREATE TABLE t (

id int(11) NOT NULL,

c int(11) DEFAULT NULL,

d int(11) DEFAULT NULL,

PRIMARY KEY (id),

) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),

(10,10,10),(15,15,15),(20,20,20),(25,25,25);

4.2.3.1 幻读的问题

  1. 首先是语义上的。session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。
  2. 其次，是数据一致性的问题。一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

一致性问题

假设只在id=5这一行加行锁

现在，我们来分析一下图 3 执行完成后，数据库里会是什么结果。

  1. 经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;
  2. 经过 T2 时刻，id=0 这一行变成 (0,5,5);
  3. 经过 T4 时刻，表里面多了一行 (1,5,5);
  4. 其他行跟这个执行序列无关，保持不变。

我们再来看看这时候 binlog 里面的内容。

  1. T2 时刻，session B 事务提交，写入了两条语句；
  2. T4 时刻，session C 事务提交，写入了两条语句；
  3. T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

我统一放到一起的话，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。也就是说，id=0 和 id=1 这两行，发生了数据不一致。这个问题很严重，是不行的。

到这里，我们再回顾一下，这个数据不一致到底是怎么引入的？我们分析一下可以知道，这是我们假设“select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”导致的。

所以我们认为，上面的设定不合理，要改。

4.2.3.2 如何解决幻读

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

3.3 mvcc版本控制

mvcc是一种多版本并发控制的方法，为什么使用mvcc？

最原生的锁，锁住一个资源后会禁止其他任何线程访问，对于读多写少的场景，读取数据之间互斥没有必要，所以提供了读写锁的防范，读读之间不互斥，读写互斥，这样大大提高了系统的并发能力。之后又提出了读写不冲突的方法（mvcc），就是读取数据是通过一种类似快照的方式将数据保存下来，不同的事务session看到自己特定版本的数据。

它的实现原理主要是版本链、undo日志、Read view来实现的。

3.3.1 版本链

我们数据库中的每行数据，除了我们肉眼看见的数据，还有几个隐藏字段，分别是db_trx_id、db_roll_pointer、db_row_id。

  1. db_trx_id：6byte，最近修改(修改/插入)事务id：记录创建这条记录/最后一次修改该记录的事务ID。
  2. db_roll_pointer（版本链关键）：7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）
  3. db_row_id：6byte，隐含的自增id（隐藏主键），如果数据表没有主键，InnoDB会自动以db_row_id产生一个聚簇索引。
  4. 实际还有一个删除flag隐藏字段, 记录被更新或删除并不代表真的删除，而是删除flag变了

每次对数据库记录进行改动，都会记录一条undo 日志，每条undo日志也都有一个roll pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

3.3.2 undo日志

Undo log 主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里。当事务进行回滚时可以通过undo log 里的日志进行数据还原。

3.3.3 Read View

事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照。

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View几个属性

  • rw_trx_ids：表示在生成 Read View 时，当前活跃的读写事务数组。
  • min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是rw_trx_ids中的最小值。
  • max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
  • curr_trx_id：创建 Read View 的当前事务 id。

注：max_trx_id并不是rw_trx_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，min_trx_id就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。

3.3.4 Read View可见性判断条件

  • trx_id < min_trx_id，那么该记录则在当前事务可见，因为修改该版本记录的事务在当前事务生成 Read View 之前就已经提交。
  • trx_id in (rw_trx_ids)，那么该记录在当前事务不可见，因为需改该版本记录的事务在当前事务生成 Read View 之前还未提交。
  • trx_id > max_trx_id，那么该记录在当前事务不可见，因为修改该版本记录的事务在当前事务生成 Read View 之前还未开启。
  • trx_id = curr_trx_id，那么该记录在当前事务可见，因为修改该版本记录的事务就是当前事务。

3.3.5 示例

事务A（只读事务，trx_id=0）事务B（101）事务C（102）
begin;
select * from person where id = 1;(T1时刻)begin;
update person set age = 21 where id = 1;
select * from person where id = 1;(T2时刻)update person set age = 22 where id = 1;
commit;
begin;
update person set name = 'abc' where id = 1;
select * from person where id = 1;(T4时刻)update person set name = 'cba' where id = 1;
commit;
select * from person where id = 1;(T5时刻)

3.3.5.1 读提交

T1时刻

rw_trx_idsmin_trx_idmax_trx_idcur_trx_id
1011010

T2时刻

rw_trx_idsmin_trx_idmax_trx_idcur_trx_id
1011011020

3.3.5.1 可重复读

实际上，REPEATABLE READ 与 READ COMMITTED 的区别只有在生成 Read View 的时机上。

READ COMMITTED 是在每次执行 select 操作时，都会生成一个新的 Read View。而 REPEATABLE READ 只会在第一次执行 select 操作时生成一个 Read View，直到该事务提交之前，所有的 select 操作都是使用第一次生成的 Read View。

T1、T2、T3、T4时刻Read View

rw_trx_idsmin_trx_idmax_trx_idcur_trx_id
1011010
avatar
文章
阅读
粉丝