重温Mysql及部分原理挖掘

MySQL 的基本架构

mysql> select * from T where ID=10；

一句sql的执行流程：

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等）。

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

---

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 这一行。ID 是主键，引擎直接用树搜索找到这一行。
• 如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
• 执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
• 引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
• 执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
• 执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

---

自增主键的好处（InnoDB）

• 自增主键的插入数据模式，是递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
• 主键索引树的叶子节点存储的是整行记录，而其他索引树的叶子节点存的主键的值，当使用自增主键的话，一般占用的空间会比业务主键小，那么普通索引占用的空间也就越小。（根据主键索引的查询，只需要查询一棵树，而根据非主键索引查询，会先得到主键值，然后再到主键索引获取表数据）

---

事务隔离的实现

多版本并发控制（MVCC）

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。

---

主键索引 & 非主键索引

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

注意：也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

---

mysql有哪些锁

全局锁、表锁、行锁

---

全局锁

全局锁就是对整个数据库实例加锁。命令是：FTWRL（Flush tables with read lock）。典型使用场景是，做全库逻辑备份。FTWRL 前有读写的话 ,FTWRL 都会等待 读写执行完毕后才执行。

---

表级锁：

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

• 表锁的语法是 lock tables … read/write，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。
• 元数据锁(MDL)不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
  在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，当无法获取到MDL锁时，对应线程会处于阻塞状态，用来保证变更表结构操作的安全性。

---

行锁

行锁就是针对数据表中行记录的锁。MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。这也是 MyISAM 被 InnoDB 替代的重要原因之一。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。

---

☆☆☆思考：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A、B所查询到的k值是多少呢？（默认 autocommit=1）

这个问题就涉及到了控制事务隔离级别的MVCC相关知识了，大家都知道事务的隔离级别（可重复读和读已提交）实现在于事务会去查询不同版本的视图，所以取到的结果也不一样。

begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 这个命令。

第一种启动方式，一致性视图是在执行第一个快照读语句时创建的；
第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。

事务 C 没有显式地使用 begin/commit，表示这个 update 语句本身就是一个事务，语句完成的时候会自动提交。

在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；（除非内部有更新操作也会更新一致性视图）
在读已提交隔离级别下，每一个语句执行前都会重新算出一个新的视图（获取到已经提交过得的数据）。

所以答案是：
读已提交的情况下， 事务A、B所查询到的k值是 2、3；
可重复读的情况下， 事务A、B所查询到的k值是 1、3；

---

前缀索引

-- 使用整个email字段作为索引
alter table SUser add index index1(email);

-- 前缀索引，使用email的前6位作为索引
alter table SUser add index index2(email(6));

优点：
前缀索引相对比较节省索引树的空间
缺点：
由于区分度相比与整个字符串的索引要低一写，所以可能会增加查询扫描的行数；
还有就是会影响覆盖索引的使用，无法直接从索引树取到想要查询的结果。

---

mysql突然抖动原因之一

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log。在更新内存写完 redo log 后，就返回给客户端，本次更新成功，所以更新效率很高。此时就可能出现磁盘的数据和redo log的数据不一致，Mysql会自行将redo log中的变化更新到磁盘中，这个操作在Mysql中叫做flush。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

现在你知道了，InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。

---

关于count()函数的用法和性能比较

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

• count(id) ，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。
• count(1) ，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。
• count(字段) ，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。
• count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

**结论：**性能比较结果：count(字段)<count(主键 id)<count(1)≈count(*)，所以建议，尽量使用 count(*)进行统计。

---

redo log 一般设置多大？

回答：redo log 太小的话，会导致很快就被写满，然后不得不强行刷脏页（flush），这样 WAL 机制的能力就发挥不出来了。所以，如果是现在常见的几个 TB 的磁盘的话，就不要太小气了，直接将 redo log 设置为 4 个文件、每个文件 1GB 吧。

---

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 ;

为了说明这个 SQL 查询语句的执行过程，我们先来看一下 city 这个索引的示意图。

从图中可以看到，满足 city='杭州’条件的行，是从 ID_X 到 ID_(X+N) 的这些记录。其执行流程为：

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

执行流程的示意图如下所示:

图中“按 name 排序”这个动作，可能在内存中完成，也可能需要使用外部排序(所需空间大于sort_buffer的时候)，这取决于排序所需的内存和参数 sort_buffer_size。

---

幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行（别的事务在期间新增的行）。隔离级别为可重复读的情况，可能出现幻读；

需要对“幻读”做一个说明：
在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读（可理解为更新类型的读，在更新时会先一次查询再更新）”下才会出现。

---

如何解决幻读？

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

间隙锁，锁的就是两个值之间的空隙。比如初始化插入了 6 个记录，这就产生了 7 个间隙。

当你开启一个事务执行查询的时候，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁。这样就确保了无法再插入新的记录。

间隙锁跟我们之前碰到过的锁都不太一样。间隙锁是防止“往这个间隙中插入一个记录”，间隙锁之间都不存在冲突关系（即可能存在一个间隙之间加了多把间隙锁）。

---

MySQL有哪些常见的影响性能问题？

• 短连接风暴:
  正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

• 慢查询性能问题:
  会引发性能问题的慢查询，大体有以下三种可能：
  1、索引没有设计好；
  2、SQL 语句没写好；
  3、MySQL 选错了索引。

• QPS 突增问题：
  由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

---

binlog 的写入机制

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了 binlog cache 的保存问题。

系统给 binlog cache 分配了一片内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过了这个参数规定大小的数据，就要暂存到磁盘。

事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中，并清空 binlog cache。每个线程有自己 binlog cache，但是共用同一份 binlog 文件。

---

MySQL是主备一致的实现流程？

备库跟主库 之间维持了一个长连接。主库内部有一个线程，专门用于服务备库的这个长连接。

一个事务执行，主库和备库中间发生了什么？

• 在备库 上通过 change master 命令，设置主库 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

• 在备库 上执行 start slave 命令，这时候备库会启动两个线程，io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。

• 主库 校验完用户名、密码后，开始按照备库 传过来的位置，从本地读取 binlog，发给 备库。

• 备库 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

• sql_thread 读取中转日志，解析出日志里的命令，并执行。

• 备份完成！

---

JOIN查询语句

-- 假设在表 t1和t2上的字段a都是有索引的
select * from t1 straight_join t2 on (t1.a=t2.a);

如果直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表，这样会影响我们分析 SQL 语句的执行过程。为了便于分析执行过程中的性能问题，我改用 straight_join 让 MySQL 使用固定的连接方式执行查询，这样优化器只会按照我们指定的方式去 join。在这个语句里，t1 是驱动表，t2 是被驱动表。

这个语句的执行流程是这样的：

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

结论（前提是“可以使用被驱动表的索引”）：

• join 语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。
• 使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；
• 如果使用 join 语句的话，需要让小表做驱动表。

提示：
但是，当被驱动表没有走索引的时候，那么驱动表和被驱动表的选择就不重要了，而且效率也会相当低，不建议使用。

---

自增主键为什么可能不是连续的？

• 唯一键冲突会导致自增主键 id 不连续；
• 事务回滚也会导致自增主键 id 不连续。

---

distinct 和 group by执行流程

select a from t group by a order by null;
select distinct a from t;

由于 group by没有结合类似count()等函数（如：select a, count(id) from t group by a order by null;）的操作一起执行，所以两条语句的执行流程是一致的。（需要借助临时表实现）

• 1、创建一个临时表，临时表有一个字段 a，并且在这个字段 a 上创建一个唯一索引；
• 2、遍历表 t，依次取数据插入临时表中：如果发现唯一键冲突，就跳过，否则插入成功；
• 3、遍历完成后，将临时表作为结果集返回给客户端。

---

自增id用完会怎么样？

最后insert 一条语句插入数据成功后，这个表的 AUTO_INCREMENT 对应的值没有该表，就导致了下一次的 insert 语句又拿到相同的自增 id 值，再试图执行插入语句，报主键冲突错误。

对于一个频繁插入删除数据的表来说，是可能会被用完的。因此在建表的时候你需要考察你的表是否有可能达到这个上限，如果有可能，就应该创建成 8 个字节的 bigint unsigned。

注：
bigint的取值范围为：-9223372036854775808 ~ 9223372036854775807
bigint unsigned（无符号bigint）取值范围为：0 ~ 18446744073709551615