一、SQL 查询语句的执行

MySQL 逻辑架构

总体上，MySQL 分为 Server 层和存储引擎层

1、Server 层：涵盖 MySQL 大多数核心服务功能；所有内置函数，如日期、时间、数学和加密函数等；所有跨存储引擎的功能都在这一层实现，如存储过程、触发器、视图

2、存储引擎层：负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、 Memory 等多个存储引擎，现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎，但是在建表时可以指定使用其他存储引擎

一条 SQL 查询语句的执行

select * from T where ID = 10;

连接器

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

连接又分为长连接和短连接

长连接：指连接成功后，客户端持续有请求，一直使用同一个连接

短连接：指每次只进行很少的查询，然后就断开连接，下次查询再重新建立

长连接可能会导致 MySQL 内存占用过大，因为MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放，最后会被系统强行杀掉（MySQL 异常重启）

如何解决？

1、定期断开长连接

2、在 MySQL 5.7 或更新版本中，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源，该过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

1、第一步是建立连接。输入命令 mysql -u root -p 并输入密码后，会利用命令中 mysql 这个客户端工具和服务端建立连接，完成 TCP 握手后会利用输入的用户名和密码来进行身份验证。如果输入的密码错误，那么会返回一个 Access denied for user 错误，客户端程序结束执行；否则代表身份认证通过，连接建立成功

2、第二步是获取权限。连接建立成功后，连接器就会去权限表中查出你拥有的权限，即使后面你使用管理员账号对该权限进行了修改，这个权限也不会立即受到影响，这个修改后的权限设置只有在下一次新建连接时才会生效

3、第三步是维持和管理连接。连接建立完成后，若无后续动作，则该连接就处于空闲状态，若长时间没有动作(默认是 8 小时，由参数 wait_timeout 控制)，该连接就会被连接器断开，断开后如果继续发送请求，则会收到错误 Lost connection to MySQL server during query ，此时就需要重连后再进行后续操作

查询缓存

负责查看之前是否执行过当前要执行的查询语句

连接建立成功后，MySQL 会拿着当前查询请求先去查询缓存中找一个 key，这个 key 就是之前可能已经执行过的查询语句，它和它对应的结果 value 会以 key-value 对的形式存储在查询缓存中

如果能够在查询缓存中找到这个 key，那么其对应的结果 value 会被直接返回给客户端，无需再进行后续的执行过程，提高效率；否则继续执行后续过程，执行完毕后，执行得到的结果会被存入查询缓存

但是一个表的更新会将这个表的查询缓存全部清空，因此查询缓存很容易失效，只有在更新操作不频繁的表上才适合使用查询缓存

MySQL 提供了查询缓存 ⌈按需使用⌋ ，将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定 select SQL_CACHE * from T where ID = 10;

MySQL 8.0 中查询缓存功能已经被弃用

分析器

负责对 SQL 语句做解析，解析出你这个 SQL 语句究竟要做什么操作

1、词法分析。

MySQL 需要识别出 SQL 语句中每个字符串都代表什么，例如 ⌈select⌋ 代表是一个查询语句

2、语法分析。

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

优化器

SQL 被执行之前，会先经过优化器的处理

例如：表中存在多个索引，或者存在多表关联时，优化器会决定使用哪一个索引，决定各个表的连接顺序

执行器

执行 SQL 语句

MySQL 通过分析器知道了你要做什么，通过优化器知道了应该怎么做，最后就要通过执行器执行语句

开始执行时，首先会验证你对该表是否有查询的权限，没有则会返回没有权限的错误

若有权限，则会根据表的引擎定义，去使用这个引擎提供的接口

二、SQL 更新语句的执行

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

首先是连接器进行数据库连接，更新操作会导致该表的查询缓存全部失效，经过分析器知道了这是一条更新语句，经过优化器决定使用 ID 这个索引，最后执行器负责执行该语句

更新操作设计两个日志模块，redo log(重做日志)、binlog(归档日志)

redo log

InnoDB 存储引擎特有的日志

提高更新效率

有了 redo log，InnoDB 可以保证在数据库异常重启的情况下不丢失之前的记录（crash-safe）

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

当有某条记录需要更新时，InnoDB 存储引擎会先将记录写到 redo log，并更新内存，此时更新就算完成了。只有在系统比较空闲的时候，InnoDB 存储引擎才会将该操作记录更新到磁盘中。如果 redo log 满了，那么 InnoDB 会停下其他工作，将 redo log 的部分操作记录更新到磁盘，腾出一点空间，来记录新的操作

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

binlog

作用：归档

属于 Server 层的日志

将 sync_binlog 这个参数设置成 1，表示每次事务的 binlog 都持久化到磁盘。这样可以保证 MySQL 异常重启之后 binlog 不丢失

和 redo log 的不同点：

1、redo log 属于 InnoDB 存储引擎特有的日志；binlog 是 MySQL 的 Server 层实现的，所有存储引擎都可以使用

2、redo log 属于物理日志，记录的是 ⌈在某个数据页上做了什么修改⌋；binlog 属于逻辑日志，记录的是 ⌈该语句的原始逻辑⌋，例如 ⌈给 ID=2 这一行的 c 字段加 1⌋

3、redo log 是 ⌈循环写⌋ 的，固定空间会用完；binlog 是 ⌈追加写⌋ 的，指的是 binlog 文件到一定大小时，会切换下一个，而不会覆盖之前的日志

内部执行流程

浅色框表示是在 InnoDB 内部执行的， 深色框表示是在执行器中执行的

1、执行器首先找存储引擎取 ID=2 这一行，若该行所在数据页就在内存中，则直接返回给执行器，否则存储引擎会先从磁盘中将其读入内存，然后再返回给执行器

2、执行器拿到该行数据后，进行更新操作，得到新的行数据，再调用存储引擎接口写入新的行数据

3、存储引擎将这行新数据更新到内存中，并将该更新操作记录到 redo log，此时 redo log 处于 prepare 状态，之后存储引擎会告诉执行器，执行已经完成，随时可以提交事务

4、执行器生成这个操作的 binlog，并将 binlog 写入磁盘

5、最后，执行器调用存储引擎的提交事务接口，存储引擎将刚刚写入的 redo log 状态改为提交(commit)，至此，更新完成

两阶段提交

redo log 的写入被拆分为 prepare 和 commit（两阶段提交）

目的：让 redo log 和 binlog 两份日志之间的逻辑一致

简单说，redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两 个状态保持逻辑上的一致

数据恢复流程

如何让数据库恢复到半个月内任意一秒的状态？

binlog 会记录所有的逻辑操作，并且是采用 ⌈追加写⌋ 的方式；而且系统会定期做 ⌈整库备份⌋，这取决于系统的重要性，⌈一天一次备份⌋ 或者是 ⌈一周一次备份⌋

1、找到最近的一次全量备份，从这个备份恢复到临时库

2、从备份的时间点开始，将备份的 binlog 依次取出，重放到误删表之前的那个时刻

3、将表数据从临时库中取出，按需恢复到线上库

为什么需要两阶段提交?

redo log 和 binlog 是两个独立的逻辑，如果不使用两阶段提交，那么就是先写完 redo log，再写 binlog，或者使用相反的顺序

以 update T set c=c+1 where ID=2; 为例，假设当前 ID=2 的行，字段 c 的值为 0

1、先写 redo log，再写 binlog。如果在 redo log 写完之后，binlog 还没有写完，此时数据库异常重启了；redo log 已经写完，即使数据库崩溃，数据依然可以恢复，因此数据恢复后 c 的值为 1；但由于 binlog 还没有写完，于是 binlog 中就没有记录这一行更新语句，之后如果要使用 binlog 来恢复临时库，由于这个语句的 binlog 丢失，临时库就会缺少这一次更新，恢复出来的 c 就是 0，和原库中的 c 值不同

2、先写 binlog，再写 redo log。如果 binlog 写完之后，redo log 还没写完，数据库就崩溃了；由于 redo log 没有写完，因此恢复后这个事务无效，c 的值还是 0；但是 binlog 已经写完了，其中已经记录了 ⌈把 c 从 0 改为 1⌋ 这个日志，后面使用 binlog 恢复临时库时就会多出一个事务，恢复出来的 c 的值为 1，和原库中的数据不符

三、存储引擎

特性：插件式，可根据需要选择使用不同的存储引擎

MySQL 5.5 之前默认存储引擎：MyISAM

MySQL 5.5 之后默认存储引擎：InnoDB

MyISAM

1、不支持事务、外键，表锁设计(并发性很差)，支持全文索引，访问速度快

2、MyISAM 存储引擎的缓冲池只缓存(cache)索引文件，数据文件的缓存交给操作系统本身完成

3、MyISAM 存储引擎表由 ⌈MYD⌋ 和 ⌈MYI⌋ 组成，前者存放数据文件，后者存放索引文件

4、可以通过 myisampack 工具进一步压缩文件，该工具使用 ⌈赫夫曼 Huffman 编码静态算法⌋ 来压缩数据，因此使用该工具压缩后的表是只读的，也可通过该工具来解压数据文件

5、MySQL 5.0 之前，MyISAM 默认支持的表大小为 4GB，如果需要支持大于 4GB 的 MyISAM 表，可以指定 ⌈MAX_ROWS⌋、⌈MAX_ROWS⌋ 这两个属性；MySQL 5.0 版本开始，MyISAM 默认支持 256TB 的单表数据，足以满足一般应用需求

InnoDB

1、支持事务、外键，行锁设计，支持 MVCC，提供一致性非锁定读(默认读取操作不会产生锁)

2、InnoDB 存储引擎将数据放在一个逻辑的表空间中，该表空间由 InnoDB 存储引擎自身管理，MySQL 4.1(包括 4.1) 开始，它可以将每个 InnoDB 存储引擎的表单独存放到一个独立的 ibd 文件中，InnoDB 存储引擎支持用裸设备(row disk) 建立其表空间

3、通过 ⌈多版本并发控制(MVCC)⌋ 获得高并发性

4、实现了 SQL 标准的 4 种隔离级别，默认为可重复读级别 ⌈REPEATABLE⌋

5、使用 ⌈next-key locking⌋ 策略来避免幻读

6、还提供了 ⌈插入缓冲 insert buffer⌋、⌈二次写 double write⌋、⌈自适应哈希索引 adaptive hash index⌋、⌈预读 read ahead⌋ 等高性能和高可用功能

7、InnoDB 存储引擎采用 ⌈聚集 clustered⌋ 方式来存储表中数据，InnoDB 存储引擎表是索引组织表，每张表中的数据都是按主键的顺序进行存放，如果没有显式地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID，并以此为主键

四、索引

索引的目的就是提高查询速度

索引的实现有多种方式，例如：哈希表、有序数组、搜索树

哈希表

以键值对(Key-Value) 形式存储数据

将值放在数组中，用一个哈希函数把 Key 换算成一个确定的位置，然后将 Value 放在数组的这个位置

多个 Key 值经过哈希函数换算，会出现同一个值的情况，此时利用链表处理

只适用于只有等值查询的场景

假设现在维护一个身份证信息和姓名的表，需要根据身份证号查找对应的名字

图中四个 ID_Card_n 并不是递增的
优点：增加新的 User 速度很快，只需要往后追加即可
缺点：因为不是有序的，所以区间查询速度很慢

有序数组

有序数组索引只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人 口信息，这类不会再修改的数据

优点：有序数组在等值查询和范围查询场景中性能都非常好，无论是等值查询还是范围查询，使用二分法即可
缺点：但是更新数据就十分麻烦，若向中间插入一个记录，就得挪动后面所有记录，成本高

二叉查找树

左子树的键值总是小于根的键值，右子树的键值总是大于根的键值

二叉查找树可以任意构造，但查找效率可能就会降低，例如：

若想最大性能地构造一个二叉查找树，需要这棵二叉查找树是平衡的，也就是平衡二叉树(AVL)

平衡二叉树

首先符合二叉查找树的定义，其次必须满足任何节点的两个子树高度最大差为 1

平衡二叉树的查询速度很快，但是维护的代价很高，通常需要1 次或者多次左旋和右旋来得到插入或更新后树的平衡性，例如：

B+树

为磁盘或其他直接存取辅助设备设计的一种平衡查找树

B+树中，所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接

插入( 3 种情况)

插入 28，此时 Leaf Page 和 Index Page 都没有满

接着插入 70，此时 Leaf Page 满了，Index Page 没有满，插入 Leaf Page 后变成 [50、55、60、65、70]，根据中间值 60 来拆分 Leaf Page

最后插入 95，此时 Leaf Page 和 Index Page 都满了，需要两次拆分

旋转

1、从以上的插入操作可以发现，不管怎么变化，B+树总是会保持平衡，但为了保持平衡，对于新插入的键值可能需要做大量拆分页操作，而 B+树结构主要用于磁盘，页的拆分意味着磁盘的操作，所以应该在可能的情况下尽量减少页的拆分操作，因此，B+树也提供了类似平衡二叉树的旋转功能

2、旋转发生在 Leaf Page 已经满了，但是其左右兄弟节点没有满的情况下；此时 B+树不会急着去做拆分页的操作，而是将记录移到所在页的兄弟节点上；通常情况下，左兄弟会被首先检查用来做旋转操作

例如：插入键值 70，此时 B+树并不会急着去做拆分操作，而是去做旋转操作，减少了一次页的拆分，高度依然是 2

删除(3 种情况)

B+树使用 ⌈填充因子 fill factor⌋ 来控制树的删除变化，填充因子可设置的最小值为 50%

5 阶树：每页最多有 4 个 key，大于等于 5 时就要分裂或旋转合并

4 个 key，如果已经存在了 2 个，表示填充因子为 50%

删除操作也必须保证删除后叶子节点中的记录依然排序

以上面最后一次插入后的结果为例子，首先删除键值为 70 的记录，删除后叶子页面有 2 个 key，填充因子等于 50%，索引页面有 2 个 key，填充因子等于 50%，属于第 1 种情况

删除键值 25 的记录，属于第一种情况，但该值还是 Index Page 中的值，因此删除 Leaf Page 中的 25 后，还应该将 25 的右兄弟节点 28 更新到 Index Page 中

最后删除键值 60 的记录，删除后 Leaf Page 只剩一个 key，填充因子小于 50%，Index Page 填充因子等于 50%，属于第 2 种情况

B+树索引

B+树索引的本质：B+树在数据库中的实现

特点：高扇出性

因此特点，数据库中 B+树的高度一般在 2~4 层，查找某一键值的行记录时最多只需要 2~4 次 IO

可分为聚集索引、辅助索引，这两个索引内部都是 B+树，是高度平衡的，叶子节点存放所有数据，它们的不同之处在于，叶子节点存放的是否是一整行的信息

主键索引属于聚集索引，叶子节点存放整行数据 非主键索引属于辅助索引，也叫二级索引，叶子节点存放主键的值

例如：有一个表 T，ID 为主键列，还有一个字段 k，并且在 k 上有索引

create table T(
id int primary key, 
k int not null, 
name varchar(16),
index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)

基于主键索引和普通索引的查询有什么区别?

1、如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树

2、如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表

3、 基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询

聚集索引

1、InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放

2、聚集索引就是按照每张表的主键构造一棵 B+树，叶子节点存放整张表的行记录数据，聚集索引的叶子节点也称为 ⌈数据页⌋，该特性决定了索引组织表中数据也是索引的一部分，每个数据页都通过一个双向链表进行链接

3、实际的数据页只能按照一棵 B+树进行排序，因此每张表只能拥有一个聚集索引

4、多数情况下，查询优化器倾向于使用聚集索引，因为聚集索引能够在 B+树的叶子节点上直接找到数据

5、由于定义了数据的逻辑顺序，聚集索引可以很快地访问针对范围值的查询，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可

6、数据页上存放的是完整的每行的记录，非数据页(索引页) 存放的仅仅是键值及指向数据页的偏移量

7、聚集索引的存储是逻辑上连续的，并不是物理上连续的。其一，每个页通过双向链表链接；其二，每个页中的记录也是通过双向链表进行维护的

8、对于主键的排序查找和范围查询速度很快，叶子节点的数据就是用户所要查询的数据

辅助索引(有时也叫做非聚集索引)

1、叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，每个叶子节点的索引行中还包含了一个书签(bookmark)，该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键

2、举例来说，如果在一棵高度为 3 的辅助索引树中查找数据，那需要对这棵辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页

3、辅助索引和聚集索引的关系

索引维护

B+ 树为了维护索引有序性，在插入新的值时需要做必要的维护

以该图为例子：

假设插入的新 ID 为 400，并且如果 R5 所在数据页满了，那么此时就会进行页分裂，影响性能，还会影响空间利用率，此时 B+树就会进行优化，它不会急着去做页分裂，而是去看当前页的左右兄弟有没有满，如果没满，则会将数据页做合并