MYSQL四十五讲核心知识提炼

1 一条SQL查询语句是怎么执行的？

mysql的基本架构示意图

总体来说，mysql可分为Server层和存储引擎层两部分，Server层管功能层面，引擎层管存储层面

• Server层涵盖MySQL的大多数核心服务功能，内置函数、存储过程、触发器、视图
• 存储引擎负责数据的存储和提取，不同的存储引擎共用一个Server层
  

连接器

连接器负责和客户端建立连接
长连接：长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接
短连接：查询几次就断开连接，下次查询再重新建立一个
长连接导致数据库内存占用高的问题：MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断
开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉。

查询缓存

MySQL缓存以key-value对的形式被直接缓存到内存中，但是查询缓存往往弊大于利，因为只要有一个对表的更新，这个表上所有的查询缓存都会被清空，命中率太低
MySQL8.0版本直接将查询缓存的整块功能删掉了

分析器

分析器先会做词法分析，输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出相应的关键字，此阶段可能会报：You have an error in your SQL syntax

优化器

优化器阶段是在表里面有多个索引的时候决定使用哪个索引，或者一个语句做多表连接的时候决定表的连接顺序，具体规则有小表驱动大表等

执行器

执行器开始执行前会首先判断此用户是否有对表的操作权限，如果无权限会报权限错误
如果有权限则操作InnoDB引擎的接口去表中一行一行的取数据，遍历完成后将所有满足条件的行组成的记录集作为结果集返回给客户端

2 一条SQL更新语句是怎么执行的？

更新语句同样要执行SQL语句的执行链路，首先建立连接，然后清除此表的所有缓存，之后分析器确定这是一条更新语句，优化器选择索引，执行器找到这一行然后更新。与查询流程不同的是涉及两个重要的日志模块：redo log和binlog

redo log

问题背景：在MySQL里面，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。通常采用WAL（Write-Ahead Logging）技术解决，关键点就是先写日志，再写磁盘。

具体操作：当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

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

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

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

binlog

InnoDB引擎特有的日志是redo log，Server层特有的日志是binlog

问题背景：最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

redo log和binlog的异同点

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

执行update语句的内部流程

其中浅色是在InnoDB中执行的，深色是在执行器中执行的

具体流程如下：

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

两阶段提交

redo log的写入拆成了两个步骤，分别是prepare和commit阶段

分析两阶段提交，首先要分析数据恢复过程，比如怎么让数据库恢复到半个月内任意一秒的状态?
首先数据库会有全量备份，如果某天中午误删了表，此时我们找到最近一次的全量备份，然后取出binlog一直重放到误删表的哪个时刻，此时临时库就和误删之前的线上库一样了，然后把表数据从临时表中取出来按需恢复到线上库即可。

为什么要有两阶段提交？
由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，假设一条语句从0改为1

• 先写redo log后写binlog：若redo log写完后系统崩溃，此时数据恢复过来后还是1，但是binlog没写完就crash了，因此存起来的binlog没有这个日志，如果用binlog恢复备库的话，这行的值就是0，与原库中的值不同。
• 先写binlog后写redo log：若binlog写完后crash，由于redo log还没写，崩溃后这个事务无效，所以恢复后这一行的值仍然是0。但是binlog里面已经记录了改成1的这个日志。所以采用binlog恢复临时库的时候，这一行的值就是1，与原库的值不同。
• 不只是删表后需要用这个过程来恢复数据，当需要扩容时，也就是需要多搭建一些备库来增加系统的读能力的时候，通常也采用全量备份+binlog来实现，而这个不一致就有可能导致主从数据库不一致的情况。

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

持久化参数

mysql中有两个参数，表示将每次事务的redo log和binlog都直接持久化到磁盘中去。

3 事务隔离：为什么你改了我还看不见？

简单的说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的，MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是MyISAM 被 InnoDB 取代的重要原因之一。

隔离性与隔离级别

提到事务，你肯定会想到ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性），今天讨论的是其中的I，隔离性。

当数据库上有多个事务同时执行的时候，就可能出现脏读、不可重复读、幻读的问题，为了解决这些问题，就有了隔离级别的概念。

SQL标准的事务隔离级别包括：读未提交、读已提交、可重复读和串行化。

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

实现：在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

• 在可重复读隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。
• 在读已提交隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。
• 在读未提交隔离级别下直接返回记录上的最新值，没有视图概念。
• 在串行化隔离级别下直接用加锁的方式来避免并行访问

其中Oracle数据库的默认隔离级别就是读已提交，对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，一定要记得将 MySQL 的隔离级别设置为读已提交

事务隔离的实现

在 MySQL 中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。若对于read-viewA，要得到1，就必须将当前值依次执行图中所有回滚操作得到。

此时如果有另外一个事务正在将4改成5，这个事务跟read-viewA、B、C对应的事务是不会冲突的。

回滚日志什么时候删除？：系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除(系统里没有比这个回滚日志更早的read-view的时候)

长事务的问题

长事务意味着系统里面会存在很老的事务视图。

• 由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导
  致大量占用存储空间，即使长事务最终提交，回滚段被清理，文件也不会变小。我见过数据只有 20GB，而回滚段有200GB 的库。最终只好为了清理回滚段，重建整个库
• 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

4 深入浅出索引（上）

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样

索引的常见模型

索引的出现是为了提高查询效率，但是实现索引的方式却有很多种

• 哈希表
  哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的值即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
  
  图中的四个 ID_card_n的值并不是递增的
  好处：新增User会很快，往后追加即可
  坏处：无序做范围查询时会非常的慢

总结：哈希表这种结构适用于只有等值查询的场景

• 二叉搜索树

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树，其原因是，索引不止存在内存中，还要写到磁盘上
为了保持二叉树的查询效率，在插入结点的过程中需要保持这棵树是平衡二叉树，这就会涉及一个更新操作
当存储100万的节点时，树高20，一次查询可能会访问将近20个数据块，在机械硬盘时代很耗时，为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用N叉树。

InnoDB 的索引模型

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，所有数据都是存储在B+树中的
每一个索引在InnoDB里面对应一颗B+树。

索引类型分为主键索引和非主键索引

• 主键索引：叶子节点存储的是整行数据，又被称为聚簇索引
• 非主键索引：叶子节点存储的内容是主键的值，又被称为二级索引

回表：主键查询方式，只需要搜索主键这颗B+树即可，而普通索引查询方式需要先去普通索引B+树得到主键值，再到主键B+树上查询一次，这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树，我们平时应该尽量使用主键索引

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做维护，如果插入新的行 ID 值为 700，则直接插入到R5后面。如果新插入的 ID值为 400，需要逻辑上挪动后面的数据，空出位置。而较糟得情况是，如果 R5 所在的数据页已经满了，根据 B+ 树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。

页分裂不止会影响效率，也会影响空间利用率（放不满）

关于自增主键

设置主键自增后，插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的ID 值

优点：符合了我们前面提到的递增插入的场景，每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

若采用业务字段作为主键，则往往不容易保证有序插入，这样写数据成本相对较高

除了考虑性能外，我们也可以从存储空间的角度来看，假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

空间损耗：由于每个非主键索引的叶子节点上都是主键的值，用身份证号作为主键，那么每个二级索引的叶子节点占用约20字节，而如果使用整型作为主键的话，则只要4个字节。

所以，从性能和存储空间方面考量，自增主键往往是更合理的选择

什么场景下适合用业务字段直接做主键？

1. 只有一个索引
2. 该索引必须是唯一索引

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题，且直接将这个索引设置为主键也可以避免每次查询需要搜索两棵树。

5 深入浅出索引（下）

覆盖索引

如果执行select ID from T where k between 3 and 5语句，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询的结果，不需要回表。即索引k已经覆盖了我们的查询需求，因此称为覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？
首先我们需要知道身份证号是市民的唯一标识，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了，而建立（身份证号、姓名）的联合索引，是否浪费空间？

若有高频请求，根据身份证号去查姓名，这个联合索引就有意义。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录。当然，索引字段的维护是有代价的，因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。

最左前缀原则

背景：如果为每一种查询都设计一个索引，那么索引就太多了，但是对于某些不频繁的请求又不想让它走全表扫描

解决方案：B+树这种索引结构，可以利用索引的最左前缀，来定位记录

谈最左前缀原则，必须先聊联合索引

由此可见，索引项是有序的，首先是按照name排序，name相同的再根据age排序
查张三时，会定位到ID4然后向后遍历
查第一个名字是张的人，会查到ID3，然后向后遍历
只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符

提出问题，在建立联合索引的时候，如何安排索引内的字段顺序？
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的
至此，开头的问题中，我们要为高频请求创建 (身份证号，姓名）这个联合索引，并用这个索引支持根据身份证号查询地址的需求

同时，在既有联合查询，又有基于a、b字段各自查询的时候，我们就需要考虑空间了，当a字段比b字段大的时候，我们创建的索引就是（a，b）联合索引和b字段单索引。避免了单创建a字段的情况。

索引下推

背景：满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录，不符合最左前缀的部分会怎么样呢？

我们还是以市民表的联合索引（name, age）为例，如果现在有一个需求：检索出表中名字第一个字是张，而且年龄是 10 岁的所有男孩，那么，SQL 语句是这么写的： select * from tuser where name like ‘张 %’ and age=10 and ismale=1

• mysql5.6之前，只能从ID3一个个回表，到主键索引上找出数据行，再对比字段值（此时InnoDB不会去看age的值，因此标为空白）
  
• mysql5.6之后，引入索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
  

6 全局锁和表锁 ：给表加个字段怎么有这么多阻碍？

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

全局锁就是对整个数据库实例加锁，MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)
，全库只读后，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句
全局锁的典型使用场景是，做全库逻辑备份

备份

官方自带的逻辑备份工具是 mysqldump，当 mysqldump 使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。由于MVCC的支持，这个过程中数据是可以更新的
采用这种方法做备份，需要引擎支持这个隔离级别，比如MyISAM这种不支持事务的引擎，备份过程中总是能取到最新的数据，破坏了备份的一致性。

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁

表锁

表锁的语法是 lock tables … read/write

元数据锁

元数据所又称为MDL（metedate lock），不需要显示的使用，在访问一个表的时候会被自动加上。其目的是为了保证读写的正确性。

• 当对一个表做增删改查操作的时候，加** MDL读锁**
• 当要对表做结构变更操作的时候，加 MDL 写锁

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

字段操作

给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据
举例说明如下：

A和B之间均是读锁，读锁之间不互斥，可以正常执行，而C加写锁会被A阻塞（读写锁冲突），由于C被阻塞，之后所有的读请求都会被C阻塞住，此表处于不可读写状态
总结：事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放

若需要安全地给小表加字段？

• 做修改之前，先查看长事务，kill掉
• alter table语句设定等待时间，如果在这个等待时间中可以拿到MDL写锁就好，拿不到就放弃，不要阻塞后面的语句。