20220711 MySQL 是怎样运行的

参考资料

前言

版本信息

2022年7月11日 最新版本：8.0.29

书上版本：5.7

学习使用版本：5.7.31

系统版本：CentOS Linux release 7.9 (Final)

安装/卸载参考

第1章 装作自己是个小白-重新认识MySQL

MySQL的客户端／服务器架构

我们的MySQL服务器程序和客户端程序本质上都算是计算机上的一个进程，这个代表着MySQL服务器程序的进程也被称为MySQL数据库实例，简称数据库实例。

每个进程都有一个唯一的编号，称为进程ID，英文名叫PID

每个进程都有一个名称，这个名称是编写程序的人自己定义的，比如我们启动的MySQL服务器进程的默认名称为mysqld， 而我们常用的MySQL客户端进程的默认名称为mysql。

启动MySQL服务器程序

mysqld这个可执行文件就代表着MySQL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个命令不常用

mysqld_safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外，使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错日志，这样可以方便我们找出发生错误的原因。

mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了

mysql.server start
mysql.server stop

这个 mysql.server 文件其实是一个链接文件，它的实际文件是 ../support-files/mysql.server

其实我们一台计算机上也可以运行多个服务器实例，也就是运行多个MySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或停止进行监控。

启动MySQL客户端程序

mysql -h主机名  -u用户名 -p密码

客户端与服务器连接的过程

客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程！

MySQL支持下面三种客户端进程和服务器进程的通信方式：

  • TCP/IP

    • 不同的主机，通过网络来进行通讯

  • 命名管道和共享内存

    • Windows

  • Unix域套接字文件

    • 类Unix

服务器处理客户端请求

服务器程序处理来自客户端的查询请求大致需要经过三个部分，分别是

  1. 连接管理

  2. 解析与优化

    1. 查询缓存

    2. 语法解析

    3. 查询优化

  3. 存储引擎

查询缓存

如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。

如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql 、information_schema、 performance_schema 数据库中的表，那这个请求就不会被缓存。

MySQL的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了INSERT、 UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE或 DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除！

查询优化

优化的结果就是生成一个执行计划，我们可以使用EXPLAIN语句来查看某个语句的执行计划

存储引擎

截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表，MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。

我们知道表是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负责的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上面的MySQL server层提供统一的调用接口（也就是存储引擎API），包含了几十个底层函数，像"读取索引第一条内容"、"读取索引下一条内容"、"插入记录"等等。

所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

常用存储引擎
存储引擎 描述
ARCHIVE 用于数据存档（行被插入后不能再修改）
BLACKHOLE 丢弃写操作，读操作会返回空内容
CSV 在存储数据时，以逗号分隔各个数据项
FEDERATED 用来访问远程表
InnoDB 具备外键支持功能的事务存储引擎
MEMORY 置于内存的表
MERGE 用来管理多个MyISAM表构成的表集合
MyISAM 主要的非事务处理存储引擎
NDB MySQL集群专用存储引擎 
# 查看当前服务器程序支持的存储引擎
SHOW ENGINES;


# 查看表结构，可以查看到表的执行引擎
SHOW CREATE TABLE engine_demo_table\G

# 创建表时指定存储引擎
CREATE TABLE 表名(
    建表语句;
) ENGINE = 存储引擎名称;


# 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 存储引擎名称;

第2章 MySQL的调控按钮-启动选项和系统变量

MySQL的服务器程序和客户端程序也有很多设置项

这些设置项一般都有各自的默认值

我们可以在程序启动的时候去修改这些默认值，对于这种在程序启动时指定的设置项也称之为 启动选项（startup options），这些选项控制着程序启动后的行为。

在命令行上使用选项

# 在命令行上使用选项

## 格式
--启动选项1[=值1] --启动选项2[=值2] ... --启动选项n[=值n]

## 示例
mysqld --skip-networking

如果选项名是由多个单词构成的，它们之间可以由短划线-连接起来，也可以使用下划线_连接起来

# 查看程序支持的启动选项
mysql --help
mysqld_safe --help
mysqld --verbose --help

对于一些常用的选项提供了短形式

长形式 短形式 含义
--host -h 主机名
--user -u 用户名
--password -p 密码
--port -P 端口
--version -V 版本信息

选项名是区分大小写的，比如-p和-P选项拥有完全不同的含义

# 使用短形式指定启动选项时，选项名和选项值之间可以没有间隙，或者用空白字符隔开
# -p选项有些特殊，-p和密码值之间不能有空白字符

# 指定端口，下面两个命令等价
mysqld -P3307
mysqld -P 3307

配置文件中使用选项

推荐使用配置文件的方式来设置启动选项

配置文件的路径

路径名 备注
/etc/my.cnf
/etc/mysql/my.cnf
SYSCONFDIR/my.cnf
$MYSQL_HOME/my.cnf 特定于服务器的选项（仅限服务器）
defaults-extra-file 命令行指定的额外配置文件路径
~/.my.cnf 用户特定选项
~/.mylogin.cnf 用户特定的登录路径选项（仅限客户端）

配置文件的优先级：

如果我们在多个配置文件中设置了相同的启动选项，那以最后一个配置文件中的为准。

例如 ~/.my.cnf 覆盖 /etc/my.cnf 中的配置

defaults-file 和 defaults-extra-file 选项可以指定配置文件路径

mysqld --defaults-file=/tmp/myconfig.txt

注意defaults-extra-file和defaults-file的区别，使用defaults-extra-file可以指定额外的配置文件搜索路径（也就是说那些固定的配置文件路径也会被搜索）。

配置文件的内容

配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号 [] 扩起来

[server]
option1                #这是option1，该选项不需要选项值
option2 = value2       #这是option2，该选项需要选项值

[mysqld]
(具体的启动选项...)

[mysqld_safe]
(具体的启动选项...)

[client]
(具体的启动选项...)

[mysql]
(具体的启动选项...)

[mysqladmin]
(具体的启动选项...)

配置文件中不同的选项组是给不同的启动命令使用的，如果选项组名称与程序名称相同，则组中的选项将专门应用于该程序。例如，[mysqld]和[mysql]组分别应用于mysqld服务器程序和mysql客户端程序。

不过有两个选项组比较特别：

  • [server] 组下面的启动选项将作用于所有的服务器程序

  • [client] 组下面的启动选项将作用于所有的客户端程序

需要注意的一点是，mysqld_safe和mysql.server这两个程序在启动时都会读取[mysqld]选项组中的内容。

启动命令 类别 能读取的组
mysqld 启动服务器 [mysqld]、[server]
mysqld_safe 启动服务器 [mysqld]、[server]、[mysqld_safe]
mysql.server 启动服务器 [mysqld]、[server]、[mysql.server]
mysql 启动客户端 [mysql]、[client]
mysqladmin 启动客户端 [mysqladmin]、[client]
mysqldump 启动客户端 [mysqldump]、[client]

相同配置出现在同一个配置文件中多个组时的优先级：

将以最后一个出现的组中的启动选项为准

例如，

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

[mysqld]组在[server]组后边，就以[mysqld]组中的配置项为准。

如果我们想指定 mysql.server 程序的启动参数，则必须将它们放在配置文件中，而不是放在命令行中。mysql.server 仅支持 start 和 stop 作为命令行参数。

特定MySQL版本的专用选项组

[mysqld-5.7]的选项组只有版本号为5.7的mysqld程序才能使用这个选项组中的选项

命令行和配置文件中启动选项的区别

  • 在命令行上指定的绝大部分启动选项都可以放到配置文件中，但是有一些选项是专门为命令行设计的

  • 如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准

系统变量

MySQL服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为MySQL系统变量

SHOW VARIABLES [LIKE 匹配的模式];


# 示例
SHOW VARIABLES LIKE 'default_storage_engine';
SHOW VARIABLES LIKE 'default%';

大部分的系统变量都可以通过启动服务器时传送启动选项的方式来进行设置

  • 命令行

  • 配置文件

对于启动选项来说，如果启动选项名由多个单词组成，各个单词之间用短划线-或者下划线_连接起来都可以，但是对应的系统变量之间必须使用下划线_连接起来。

对于大部分系统变量来说，它们的值可以在服务器程序运行过程中，进行动态修改而无需停止并重启服务器

作用范围

作用范围分为这两种：

  • GLOBAL：全局变量，影响服务器的整体操作。

  • SESSION：会话变量，影响某个客户端连接的操作。（注：SESSION有个别名叫LOCAL）

通过启动选项设置的系统变量的作用范围都是GLOBAL的，也就是对所有客户端都有效的

# 语法
SET [GLOBAL|SESSION] 系统变量名 = 值;
SET [@@(GLOBAL|SESSION).]var_name = XXX;


# 示例
SET GLOBAL default_storage_engine = MyISAM;
SET @@GLOBAL.default_storage_engine = MyISAM;


SET SESSION default_storage_engine = MyISAM;
SET @@SESSION.default_storage_engine = MyISAM;
SET default_storage_engine = MyISAM;

如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION。也就是说SET 系统变量名 = 值和SET SESSION 系统变量名 = 值是等价的。

SHOW VARIABLES语句查看的是SESSION作用范围的系统变量

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

  • 并不是所有系统变量都具有GLOBAL和SESSION的作用范围。

  • 有些系统变量是只读的，并不能设置值

启动选项和系统变量的区别

  • 启动选项是在程序启动时我们程序员传递的一些参数，而系统变量是影响服务器程序运行行为的变量，它们之间的关系如下：

  • 大部分的系统变量都可以被当作启动选项传入。

  • 有些系统变量是在程序运行过程中自动生成的，是不可以当作启动选项来设置，比如auto_increment_offset、character_set_client等。

  • 有些启动选项也不是系统变量，比如defaults-file。

状态变量

为了让我们更好的了解服务器程序的运行情况，MySQL服务器程序中维护了很多关于程序运行状态的变量，它们被称为状态变量。

由于状态变量是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，我们程序员是不能设置的。

与系统变量类似，状态变量也有GLOBAL和SESSION两个作用范围

# 语法
SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];


# 示例
SHOW STATUS LIKE 'thread%';

第3章 乱码的前世今生-字符集和比较规则

字符集

计算机中只能存储二进制数据，那该怎么存储字符串呢？

需要建立字符与二进制数据的映射关系

将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码

# 字符集的查看
## 语法
SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];

## 示例
SHOW CHARSET;

一些重要的字符集

ASCII字符集

共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码

ISO 8859-1字符集

共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名latin1

GB2312字符集

收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个。同时这种字符集又兼容ASCII字符集

所以在编码方式上显得有些奇怪：

  • 如果该字符在ASCII字符集中，则采用1字节编码。
  • 否则采用2字节编码。

这种表示一个字符需要的字节数可能不同的编码方式称为 变长编码方式

我们怎么区分某个字节代表一个单独的字符还是代表某个字符的一部分呢？别忘了ASCII字符集只收录128个字符，使用0～127就可以表示全部字符，所以如果某个字节是在0～127之内的，就意味着一个字节代表一个单独的字符，否则就是两个字节代表一个单独的字符。

GBK字符集

GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312

utf8字符集

收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1～4个字节

其实准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。

MySQL中并不区分字符集和编码方案的概念，所以后边介绍的时候把utf8、utf16、utf32都当作一种字符集对待。

utf8字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1～3个字节就可以表示了。

  • utf8mb3：阉割过的utf8字符集，只使用1～3个字节表示字符。

  • utf8mb4：正宗的utf8字符集，使用1～4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符，如果大家有使用4字节编码一个字符的情况，比如存储一些emoji表情什么的，那请使用utf8mb4。

比较规则

同一种字符集可以有多种比较规则

比较规则的查看

SHOW COLLATION [LIKE 匹配的模式];


SHOW COLLATION LIKE 'utf8\_%';

比较规则的命名还挺有规律的，具体规律如下：

  • 比较规则名称以与其关联的字符集的名称开头。如上图的查询结果的比较规则名称都是以utf8开头的。

  • 后边紧跟着该比较规则主要作用于哪种语言，比如utf8_polish_ci表示以波兰语的规则比较，utf8_spanish_ci是以西班牙语的规则比较，utf8_general_ci是一种通用的比较规则。

  • 名称后缀意味着该比较规则是否区分语言中的重音、大小写什么的，具体可以用的值如下：

    后缀 英文释义 描述
    _ai accent insensitive 不区分重音
    _as accent sensitive 区分重音
    _ci case insensitive 不区分大小写
    _cs case sensitive 区分大小写
    _bin binary 以二进制方式比较

比如utf8_general_ci这个比较规则是以ci结尾的，说明不区分大小写。

每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则，SHOW COLLATION的返回结果中的Default列的值为YES的就是该字符集的默认比较规则，比方说utf8字符集默认的比较规则就是utf8_general_ci。

各级别的字符集和比较规则

MySQL有4个级别的字符集和比较规则，分别是：

  • 服务器级别
  • 数据库级别
  • 表级别
  • 列级别

服务器级别

MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则：

系统变量 描述
character_set_server 服务器级别的字符集
collation_server 服务器级别的比较规则 
SHOW VARIABLES LIKE 'character_set_server';


SHOW VARIABLES LIKE 'collation_server';

可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用SET语句修改这两个变量的值

[mysqld]
character_set_server=gbk
collation_server=gbk_chinese_ci

数据库级别

在创建和修改数据库的时候可以指定该数据库的字符集和比较规则

CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];


ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

DEFAULT可以省略

如果想查看当前数据库使用的字符集和比较规则，可以查看下面两个系统变量的值（前提是使用USE语句选择当前默认数据库，如果没有默认数据库，则变量与相应的服务器级系统变量具有相同的值）：

系统变量 描述
character_set_database 当前数据库的字符集
collation_database 当前数据库的比较规则 
USE charset_demo_db;

SHOW VARIABLES LIKE 'collation_database';

character_set_database 和 collation_database 这两个系统变量是 只读的 ，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

数据库的创建语句中也可以不指定字符集和比较规则，比如这样：

CREATE DATABASE 数据库名;

这样的话，将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

创建和修改表的时候指定表的字符集和比较规则

CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]


ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则。

列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。

创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下：

CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
)
;

ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示，就会发生错误

比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符。

仅修改字符集或仅修改比较规则

由于字符集和比较规则是互相有联系的，如果我们只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：

  • 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
  • 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

不论哪个级别的字符集和比较规则，这两条规则都适用

各级别字符集和比较规则小结

4个级别字符集和比较规则的联系如下：

  • 如果创建或修改列时，没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
  • 如果创建或修改表时，没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
  • 如果创建或修改数据库时，没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

知道了这些规则之后，对于给定的表，我们应该知道它的各个列的字符集和比较规则是什么，从而根据这个列的类型来确定存储数据时每个列的实际数据占用的存储空间大小了。

例如列col使用的字符集是gbk，一个字符'我'在gbk中的编码为0xCED2，占用两个字节，两个字符'我我'的实际数据就占用4个字节。如果把该列的字符集修改为utf8的话，这两个字符就实际占用6个字节。

    public static void main(String[] args) {
        String s1 = new String("我".getBytes(StandardCharsets.UTF_8), Charset.forName("gbk"));
        System.out.println(s1);     // utf8转码gbk，成了乱码

        byte[] bytes1 = "我".getBytes(StandardCharsets.UTF_8);
        byte[] bytes2 = "我".getBytes(Charset.forName("gbk"));
        System.out.println(bytes1.length);  // 3
        System.out.println(bytes2.length);  // 2
    }

客户端和服务器通信中的字符集

如果对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果，作为人类的我们看上去就像是产生了乱码一样。

字符集转换的概念

如果接收0xE68891这个字节串的程序按照utf8字符集进行解码，然后又把它按照gbk字符集进行编码，最后编码后的字节串就是0xCED2，我们把这个过程称为字符集的转换，也就是字符串'我'从utf8字符集转换为gbk字符集。

MySQL中字符集的转换

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集的转换，在这个过程中会用到3个系统变量

系统变量 描述
character_set_client 服务器解码请求时使用的字符集
character_set_connection 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results 服务器向客户端返回数据时使用的字符集

分析字符'我'在这个过程中字符集的转换

从这个分析中我们可以得出这么几点需要注意的地方：

  • 服务器认为客户端发送过来的请求是用character_set_client编码的

  • 服务器将把得到的结果集使用character_set_results编码后发送给客户端

  • character_set_connection只是服务器在将请求的字节串从character_set_client转换为character_set_connection时使用，它是什么其实没多重要，但是一定要注意，该字符集包含的字符范围一定涵盖请求中的字符，要不然会导致有的字符无法使用character_set_connection代表的字符集进行编码。

我们通常都把 character_set_client 、character_set_connection、character_set_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换。为了方便我们设置，MySQL提供了一条非常简便的语句：

SET NAMES 字符集名;

等效于：

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;
SET character_set_results = 字符集名;

可以在启动客户端的时候指定一个叫default-character-set的启动选项，比如在配置文件里可以这么写：

[client]
default-character-set=utf8

它起到的效果和执行一遍SET NAMES utf8是一样一样的，都会将那三个系统变量的值设置成utf8。

比较规则的应用

比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中，所以有时候也称为排序规则。

如果以后大家在对字符串做比较或者对某个字符串列做排序操作时，没有得到想象中的结果，需要思考一下是不是比较规则的问题。

第4章 从一条记录说起-InnoDB记录结构

MySQL服务器上负责对表中数据的读取和写入工作的部分是存储引擎，而服务器又支持不同类型的存储引擎

真实数据在不同存储引擎中存放的格式一般是不同的

InnoDB页

InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB。

也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

页是MySQL中磁盘和内存交互的基本单位，也是MySQL是管理存储空间的基本单位。

InnoDB行格式

我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为行格式或者记录格式。设计InnoDB存储引擎的大佬们到现在为止设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic （默认） 和Compressed行格式，随着时间的推移，他们可能会设计出更多的行格式，但是不管怎么变，在原理上大体都是相同的。

在创建或修改表的语句中指定行格式：

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称

ALTER TABLE 表名 ROW_FORMAT=行格式名称

COMPACT 行格式

略

Redundant 行格式

略

行溢出数据

VARCHAR(M)最多能存储的数据

VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用ascii字符集的话，一个字符就代表一个字节

MySQL对一条记录占用的最大存储空间是有限制的，除了BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节

在列的值允许为NULL的情况下，gbk字符集下M的最大取值就是32766，utf8字符集下M的最大取值就是21844，这都是在表中只有一个字段的情况下说的

一定要记住一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节！

记录中的数据太多产生的溢出

对于Compact和Reduntant行格式来说，如果某一列中的数据非常多的话，在本记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页。

不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出

行溢出的临界点

MySQL中规定一个页中至少存放两行记录

不用关注这个临界点是什么，只要知道如果我们向一个行中存储了很大的数据时，可能发生行溢出的现象

Dynamic和Compressed行格式

Dynamic和Compressed行格式，我现在使用的MySQL版本是5.7，它的默认行格式就是Dynamic，这俩行格式和Compact行格式挺像，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址

Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

第5章 盛放记录的大盒子-InnoDB数据页结构

页是InnoDB管理存储空间的基本单位，一个页的大小一般是16KB。InnoDB为了不同的目的而设计了许多种不同类型的页，比如存放表空间头部信息的页，存放Insert Buffer信息的页，存放INODE信息的页，存放undo日志信息的页等

我们聚焦的是那些存放我们表中记录的那种类型的页，官方称这种存放记录的页为索引（INDEX）页，鉴于我们还没有了解过索引是个什么东西，而这些表中的记录就是我们日常口中所称的数据，所以目前还是叫这种存放记录的页为数据页

InnoDB 数据页

名称 中文名 占用空间大小 简单描述
File Header 文件头部 38字节 页的一些通用信息
Page Header 页面头部 56字节 数据页专有的一些信息
Infimum + Supremum 最小记录和最大记录 26字节 两个虚拟的行记录
User Records 用户记录 不确定 实际存储的行记录内容
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页面目录 不确定 页中的某些记录的相对位置
File Trailer 文件尾部 8字节 校验页是否完整

在页的7个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到User Records部分。

总结

  1. InnoDB为了不同的目的而设计了不同类型的页，我们把用于存放记录的页叫做数据页。

  2. 一个数据页可以被大致划分为7个部分，分别是

    • File Header，表示页的一些通用信息，占固定的38字节。
    • Page Header，表示数据页专有的一些信息，占固定的56个字节。
    • Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节。
    • User Records：真实存储我们插入的记录的部分，大小不固定。
    • Free Space：页中尚未使用的部分，大小不确定。
    • Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
    • File Trailer：用于检验页是否完整的部分，占用固定的8个字节。

  3. 每个记录的头信息中都有一个next_record属性，从而使页中的所有记录串联成一个单链表。

  4. InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory中，所以在一个页中根据主键查找记录是非常快的，分为两步：

    • 通过二分法确定该记录所在的槽。

    • 通过记录的next_record属性遍历该槽所在的组中的各个记录。

  5. 每个数据页的File Header部分都有上一个和下一个页的编号，所以所有的数据页会组成一个双链表。

  6. 为保证从内存中同步到磁盘的页的完整性，在页的首部和尾部都会存储页中数据的校验和和页面最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

第6章 快速查询的秘籍-B+树索引

InnoDB各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

页和记录的关系示意图如下：

页分裂

复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为目录项记录

形成的 B+ 树过程

实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点或叶节点，其余用来存放目录项的节点称为非叶子节点或者内节点，其中B+树最上面的那个节点也称为根节点

聚簇索引

InnoDB存储引擎会自动的为我们创建聚簇索引

在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引

我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录

回表

也叫做 二级索引 或 辅助索引

联合索引

以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思与分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

  • 建立联合索引只会建立如上图一样的1棵B+树。

  • 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

InnoDB的B+树索引的注意事项

一个B+树索引的根节点自诞生之日起，便不会再移动

我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

  • 索引列的值
  • 主键值
  • 页号

MyISAM中的索引方案

略

MySQL中创建和删除索引的语句

InnoDB和MyISAM会自动为主键或者声明为UNIQUE的列去自动建立B+树索引

# 创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列
CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)

# 修改表结构的时候添加索引
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

# 修改表结构的时候删除索引
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;



# 示例
CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
);


# 示例
ALTER TABLE index_demo DROP INDEX idx_c2_c3;

第7章 好东西也得先学会怎么用-B+树索引的使用

总结

  1. B+树索引在空间和时间上都有代价，所以没事儿别瞎建索引。

  2. B+树索引适用于下面这些情况：

    • 全值匹配
    • 匹配左边的列
    • 匹配范围值
    • 精确匹配某一列并范围匹配另外一列
    • 用于排序
    • 用于分组

  3. 在使用索引时需要注意下面这些事项：

    • 只为用于搜索、排序或分组的列创建索引
    • 为列的基数大的列创建索引
    • 索引列的类型尽量小
    • 可以只对字符串值的前缀建立索引
    • 只有索引列在比较表达式中单独出现才可以适用索引
    • 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
    • 定位并删除表中的重复和冗余索引
    • 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

第8章 数据的家-MySQL的数据目录

数据库和文件系统的关系

我们知道像InnoDB、MyISAM这样的存储引擎都是把表存储在磁盘上的，而操作系统用来管理磁盘的那个东东又被称为文件系统，所以用专业一点的话来表述就是：像 InnoDB 、 MyISAM 这样的存储引擎都是把表存储在文件系统上的。

MySQL数据目录

要区分 MySQL 的安装目录和数据目录

安装目录下有非常重要的bin目录

数据目录对应着一个系统变量datadir

SHOW VARIABLES LIKE 'datadir';

数据目录的结构

数据库在文件系统中的表示

每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时，MySQL会帮我们做这两件事儿：

  1. 在数据目录下创建一个和数据库名同名的子目录（或者说是文件夹）。

  2. 在该与数据库名同名的子目录下创建一个名为db.opt的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是什么。

除了information_schema这个系统数据库外，其他的数据库在数据目录下都有对应的子目录。这个information_schema比较特殊，设计MySQL的大佬们对它的实现进行了特殊对待，没有使用相应的数据库目录

表在文件系统中的表示

每个表的信息其实可以分为两种：

  1. 表结构的定义

  2. 表中的数据

InnoDB和MyISAM这两种存储引擎都在数据目录下对应的数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样：

表名.frm

这个后缀名为 .frm 是以二进制格式存储的，我们直接打开会是乱码的

表中的数据存到什么文件中了呢？在这个问题上，不同的存储引擎就产生了分歧了

InnoDB是如何存储表数据的

为了更好的管理这些页，设计InnoDB的大佬们提出了一个表空间或者文件空间（英文名：table space或者file space）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个表空间可以被划分为很多很多很多个页，我们的表数据就存放在某个表空间下的某些页里。

系统表空间（system tablespace）

这个所谓的系统表空间可以对应文件系统上一个或多个实际的文件，默认情况下，InnoDB会在数据目录下创建一个名为ibdata1 、大小为12M的文件，这个文件就是对应的系统表空间在文件系统上的表示。这个文件是所谓的自扩展文件，也就是当不够用的时候它会自己增加文件大小

可以把系统表空间对应的文件路径不配置到数据目录下，甚至可以配置到单独的磁盘分区上，涉及到的启动参数就是innodb_data_file_path和innodb_data_home_dir

[server]
innodb_data_file_path=data1:512M;data2:512M:autoextend

这样在MySQL启动之后就会创建这两个512M大小的文件作为系统表空间，其中的autoextend表明这两个文件如果不够用会自动扩展data2文件的大小

需要注意的一点是，在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个 系统表空间。

独立表空间(file-per-table tablespace)

在MySQL5.6.6以及之后的版本中，InnoDB并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用独立表空间来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该独立表空间的文件，文件名和表名相同

表名.ibd

也可以自己指定使用系统表空间还是独立表空间来存储数据，这个功能由启动参数innodb_file_per_table控制，比如说我们想刻意将表数据都存储到系统表空间时，可以在启动MySQL服务器的时候这样配置：

[server]
innodb_file_per_table=0

当innodb_file_per_table的值为0时，代表使用系统表空间；当innodb_file_per_table的值为1时，代表使用独立表空间。不过innodb_file_per_table参数只对新建的表起作用，对于已经分配了表空间的表并不起作用。如果我们想把已经存在系统表空间中的表转移到独立表空间，可以使用下面的语法：

ALTER TABLE 表名 TABLESPACE [=] innodb_file_per_table;

或者把已经存在独立表空间的表转移到系统表空间，可以使用下面的语法：

ALTER TABLE 表名 TABLESPACE [=] innodb_system;

其中中括号扩起来的=可有可无，比方说我们想把test表从独立表空间移动到系统表空间，可以这么写：

ALTER TABLE test TABLESPACE innodb_system;
其他类型的表空间

随着MySQL的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间（general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）等等

MyISAM是如何存储表数据的

在MyISAM中的索引全部都是二级索引，该存储引擎的数据和索引是分开存放的。所以在文件系统中也是使用不同的文件来存储数据文件和索引文件。而且和InnoDB不同的是，MyISAM并没有什么所谓的表空间一说，表数据都存放到对应的数据库子目录下。假如test表使用MyISAM存储引擎的话，那么在它所在数据库对应的xiaohaizi目录下会为test表创建这三个文件：

test.frm
test.MYD
test.MYI

其中test.MYD代表表的数据文件，也就是我们插入的用户记录；test.MYI代表表的索引文件，我们为该表创建的索引都会放到这个文件中。

视图在文件系统中的表示

MySQL中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储视图的时候是不需要存储真实的数据的，只需要把它的结构存储起来就行了。和表一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下面，只会存储一个视图名.frm的文件

其他的文件

数据目录下还包括为了更好运行程序的一些额外文件，主要包括这几种类型的文件：

  • 服务器进程文件

    我们知道每运行一个MySQL服务器程序，都意味着启动一个进程。MySQL服务器会把自己的进程ID写入到一个文件中。

  • 服务器日志文件。

    在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、redo日志等等各种日志，这些日志各有各的用途

  • 默认/自动生成的SSL和RSA证书和密钥文件。

    主要是为了客户端和服务器安全通信而创建的一些文件

文件系统对数据库的影响

  • 数据库名称和表名称不得超过文件系统所允许的最大长度。

    每个数据库都对应数据目录的一个子目录，数据库名称就是这个子目录的名称；每个表都会在数据库子目录下产生一个和表名同名的.frm文件，如果是InnoDB的独立表空间或者使用MyISAM引擎还会有别的文件的名称与表名一致。这些目录或文件名的长度都受限于文件系统所允许的长度～

  • 特殊字符的问题

    为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况，MySQL会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值的形式作为文件名。比方说我们创建的表的名称为'test?'，由于?不属于数字或者拉丁字母，所以会被映射成编码值，所以这个表对应的.frm文件的名称就变成了test@003f.frm。

  • 文件长度受文件系统最大长度限制

    对于InnoDB的独立表空间来说，每个表的数据都会被存储到一个与表名同名的.ibd文件中；对于MyISAM存储引擎来说，数据和索引会分别存放到与表同名的.MYD和.MYI文件中。这些文件会随着表中记录的增加而增大，它们的大小受限于文件系统支持的最大文件大小。

MySQL系统数据库简介

  • mysql

    这个数据库贼核心，它存储了MySQL的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

  • information_schema

    这个数据库保存着MySQL服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引等等。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。

  • performance_schema

    这个数据库里主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。

  • sys

    这个数据库主要是通过视图的形式把information_schema 和performance_schema结合起来，让程序员可以更方便的了解MySQL服务器的一些性能信息。

第9章 存放页面的大池子-InnoDB的表空间

第10章 条条大路通罗马-单表访问方法

MySQL Server有一个称为查询优化器的模块，一条查询语句进行语法解析之后就会被交给查询优化器来进行优化，优化的结果就是生成一个所谓的执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是什么样的，最后会按照执行计划中的步骤调用存储引擎提供的方法来真正的执行查询，并将查询结果返回给用户

CREATE TABLE single_table (
    id INT NOT NULL AUTO_INCREMENT,
    key1 VARCHAR(100),
    key2 INT,
    key3 VARCHAR(100),
    key_part1 VARCHAR(100),
    key_part2 VARCHAR(100),
    key_part3 VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

访问方法（access method）的概念

设计MySQL的大佬把查询的执行方式大致分为下面两种：

  • 使用全表扫描进行查询

  • 使用索引进行查询

    • 针对主键或唯一二级索引的等值查询

    • 针对普通二级索引的等值查询

    • 针对索引列的范围查询

    • 直接扫描整个索引

设计MySQL的大佬把MySQL执行查询语句的方式称之为访问方法或者访问类型。同一个查询语句可能可以使用多种不同的访问方法来执行，虽然最后的查询结果都是一样的，但是执行的时间可能差远了

const

# 主键
SELECT * FROM single_table WHERE id = 1438;

# 唯一索引
SELECT * FROM single_table WHERE key2 = 3841;

通过主键或者唯一二级索引列来定位一条记录的访问方法定义为：const，意思是常数级别的，代价是可以忽略不计的

对于唯一二级索引来说，查询该列为NULL值的情况比较特殊，比如这样：

# 唯一索引，NULL 值
SELECT * FROM single_table WHERE key2 IS NULL;

因为唯一二级索引列并不限制 NULL 值的数量，所以上述语句可能访问到多条记录，也就是说 上面这个语句不可以使用const访问方法来执行

ref

# 普通索引
SELECT * FROM single_table WHERE key1 = 'abc';

搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为：ref

注意下面两种情况：

  • 二级索引列值为NULL的情况

    不论是普通的二级索引，还是唯一二级索引，它们的索引列对包含NULL值的数量并不限制，所以我们采用key IS NULL这种形式的搜索条件最多只能使用ref的访问方法，而不是const的访问方法。

  • 对于某个包含多个索引列的二级索引来说，只要是最左边的连续索引列是与常数的等值比较就可能采用ref的访问方法

    SELECT * FROM single_table WHERE key_part1 = 'god like';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary';

SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 = 'legendary' AND key_part3 = 'penta kill';

    但是如果最左边的连续索引列并不全部是等值比较的话，它的访问方法就不能称为ref

ref_or_null

SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;

当使用二级索引而不是全表扫描的方式执行该查询时，这种类型的查询使用的访问方法就称为ref_or_null

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

采用二级索引 + 回表的方式来执行的话，那么此时的搜索条件就不只是要求索引列与常数的等值匹配了，而是索引列需要匹配某个或某些范围的值，在本查询中key2列的值只要匹配下列3个范围中的任何一个就算是匹配成功了：

  • key2的值是1438

  • key2的值是6328

  • key2的值在38和79之间

设计MySQL的大佬把这种利用索引进行范围匹配的访问方法称之为：range

此处所说的使用索引进行范围匹配中的 索引 可以是聚簇索引，也可以是二级索引。

索引列等值匹配的情况称之为单点区间

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

由于key_part2并不是联合索引idx_key_part最左索引列，所以我们无法使用ref或者range访问方法来执行这个语句。但是这个查询符合下面这两个条件：

  • 它的查询列表只有3个列：key_part1, key_part2, key_part3，而索引idx_key_part又包含这三个列。

  • 搜索条件中只有key_part2列。这个列也包含在索引idx_key_part中

也就是说我们可以直接通过遍历idx_key_part索引的叶子节点的记录来比较key_part2 = 'abc'这个条件是否成立，把匹配成功的二级索引记录的key_part1, key_part2, key_part3列的值直接加到结果集中就行了。由于二级索引记录比聚簇索记录小的多（聚簇索引记录要存储所有用户定义的列以及所谓的隐藏列，而二级索引记录只需要存放索引列和主键），而且这个过程也不用进行回表操作，所以直接遍历二级索引比直接遍历聚簇索引的成本要小很多，设计MySQL的大佬就把这种采用遍历二级索引记录的执行方式称之为：index。

all

最直接的查询执行方式就是我们已经提了无数遍的全表扫描，对于InnoDB表来说也就是直接扫描聚簇索引，设计MySQL的大佬把这种使用全表扫描执行查询的方式称之为：all

第11章 两个表的亲密接触-连接的原理

连接简介

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

连接查询的大致执行过程如下：

  1. 首先确定第一个需要查询的表，这个表称之为驱动表 ，此处假设使用t1作为驱动表，那么就需要到t1表中找满足t1.m1 > 1的记录

  2. 针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到t2表中查找匹配的记录，所谓匹配的记录，指的是符合过滤条件的记录。因为是根据t1表中的记录去找t2表中的记录，所以t2表也可以被称之为被驱动表。上一步骤从驱动表中得到了2条记录，所以需要查询2次t2表。

也就是说在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

内连接和外连接

测试表：

CREATE TABLE student (
    number INT NOT NULL AUTO_INCREMENT COMMENT '学号',
    name VARCHAR(5) COMMENT '姓名',
    major VARCHAR(30) COMMENT '专业',
    PRIMARY KEY (number)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生信息表';

CREATE TABLE score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, score)
) Engine=InnoDB CHARSET=utf8 COMMENT '学生成绩表';

SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1, score AS s2 WHERE s1.number = s2.number;

  • 对于内连接的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上面提到的连接都是所谓的内连接。

  • 对于外连接的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。

    在MySQL中，根据选取驱动表的不同，外连接仍然可以细分为2种：

    • 左外连接

      选取左侧的表为驱动表。

    • 右外连接

      选取右侧的表为驱动表。

放在不同地方的过滤条件是有不同语义的：

  • WHERE子句中的过滤条件

    WHERE子句中的过滤条件就是我们平时见的那种，不论是内连接还是外连接，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。

  • ON子句中的过滤条件

    对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充。

内连接中的WHERE子句和ON子句是等价的

一般情况下，我们都把只涉及单表的过滤条件放到WHERE子句中，把涉及两表的过滤条件都放到ON子句中，我们也一般把放到ON子句中的过滤条件也称之为连接条件。

外连接语法

# 左外连接
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

# 右外连接
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

对于左（外）连接和右（外）连接来说，必须使用ON子句来指出连接条件

内连接语法

# 内连接
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

内连接和外连接的根本区别就是在驱动表中的记录不符合ON子句中的连接条件时不会把该记录加入到最后的结果集

在MySQL中，下面这几种内连接的写法都是等价的：

SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1, t2;

由于在内连接中ON子句和WHERE子句是等价的，所以内连接中不要求强制写明ON子句

对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果

但是对于外连接来说，由于驱动表中的记录即使在被驱动表中找不到符合ON子句连接条件的记录，所以此时驱动表和被驱动表的关系就很重要了，也就是说左外连接和右外连接的驱动表和被驱动表不能轻易互换。

连接的原理

嵌套循环连接（Nested-Loop Join）

对于两表连接来说，驱动表只会被访问一遍，但被驱动表却要被访问到好多遍，具体访问几遍取决于对驱动表执行单表查询后的结果集中的记录条数。对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定的，也就是说左（外）连接的驱动表就是左边的那个表，右（外）连接的驱动表就是右边的那个表。

t1表和t2表执行内连接查询的大致过程：

  • 步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
  • 步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

如果有3个表进行连接的话，那么步骤2中得到的结果集就像是新的驱动表，然后第三个表就成为了被驱动表，重复上面过程，也就是步骤2中得到的结果集中的每一条记录都需要到t3表中找一找有没有匹配的记录

这个过程就像是一个嵌套的循环，所以这种驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数的连接执行方式称之为嵌套循环连接（Nested-Loop Join），这是最简单，也是最笨拙的一种连接查询算法。

使用索引加快连接速度

在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为：eq_ref

有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index的访问方法来查询被驱动表。所以我们建议在真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。

基于块的嵌套循环连接（Block Nested-Loop Join）

采用嵌套循环连接算法的两表连接过程中，被驱动表可是要被访问好多次的，如果这个被驱动表中的数据特别多而且不能使用索引进行访问，那就相当于要从磁盘上读好几次这个表，这个I/O代价就非常大了，所以我们得想办法：尽量减少访问被驱动表的次数

join buffer就是执行连接查询前申请的一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，因为匹配的过程都是在内存中完成的，所以这样可以显著减少被驱动表的I/O代价

最好的情况是join buffer足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了。设计MySQL的大佬把这种加入了join buffer的嵌套循环连接算法称之为基于块的嵌套连接（Block Nested-Loop Join）算法。

join buffer的大小是可以通过启动参数或者系统变量join_buffer_size进行配置，默认大小为262144字节（也就是256KB），最小可以设置为128字节。当然，对于优化被驱动表的查询来说，最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大join_buffer_size的值来对连接查询进行优化。

需要注意的是，驱动表的记录并不是所有列都会被放到join buffer中，只有查询列表中的列和过滤条件中的列才会被放到join buffer中，所以再次提醒我们，最好不要把*作为查询列表，只需要把我们关心的列放到查询列表就好了，这样还可以在join buffer中放置更多的记录

第12章 谁最便宜就选谁-MySQL基于成本的优化

MySQL 中一条查询语句的执行成本是由下边这两个方面组成的：

  • I/O 成本

    • 我们的表经常使用的 MyISAM 、 InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。

  • CPU 成本

    • 读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。
查询成本= I/O 成本+ CPU 成本

对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，设计 MySQL 的大叔规定读取一个页面花费的成本默认是 1.0 ，读取以及检测一条记录是否符合搜索条件的成本默认是 0.2 。 1.0 、 0.2 这些数字称之为 成本常数

在一条单表查询语句真正执行之前， MySQL 的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的 执行计划 ，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：

  1. 根据搜索条件，找出所有可能使用的索引

  2. 计算全表扫描的代价

  3. 计算使用不同索引执行查询的代价

  4. 对比各种执行方案的代价，找出成本最低的那一个

# 查看 single_table 这个表的统计信息
SHOW TABLE STATUS LIKE 'single_table'\G

通过直接访问索引对应的 B+ 树来计算某个范围区间对应的索引记录条数的方式称之为 index dive

系统变量 eq_range_index_dive_limit ，小于这个变量的值将使用 index dive 的方式，否则使用索引统计数据来进行估算（不精确！）

像会为每个表维护一份统计数据一样， MySQL 也会为表中的每一个索引维护一份统计数据

# 查看索引统计数据
SHOW INDEX FROM single_table;

多表连接查询时，计算驱动表扇出值时需要靠 猜

这个 猜 的过程称之为 condition filtering

启发式规则

内连接时，驱动表和被驱动表可以互换，查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划

连接查询成本占大头的其实是 驱动表扇出数 x 单次访问被驱动表的成本 ，所以我们的优化重点其实是下边这两个部分：

  • 尽量减少驱动表的扇出

  • 对被驱动表的访问成本尽量低

这一点对于我们实际书写连接查询语句时十分有用，我们需要 尽量在被驱动表的连接列上建立索引，这样就可以使用 ref 访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

成本常数被存储到了 mysql 数据库

 SHOW TABLES FROM mysql LIKE '%cost%';

一条语句的执行其实是分为两层的：

  • server 层

  • 存储引擎层

在 server 层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在 server 层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的 成本常数 就存储在了 server_cost 表中，而依赖于存储引擎的一些操作对应的 成本常数 就存储在了 engine_cost 表中。

MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个 临时表，使用这个临时表来辅助完成查询（比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎。

第13章 兵马未动，粮草先行-InnoDB统计数据是如何收集的

总结

  • InnoDB 以表为单位来收集统计数据，这些统计数据可以是基于磁盘的永久性统计数据，也可以是基于内存的非永久性统计数据。

  • innodb_stats_persistent 控制着使用永久性统计数据还是非永久性统计数据；innodb_stats_persistent_sample_pages 控制着永久性统计数据的采样页面数量；innodb_stats_transient_sample_pages 控制着非永久性统计数据的采样页面数量；innodb_stats_auto_recalc 控制着是否自动重新计算统计数据。

  • 我们可以针对某个具体的表，在创建和修改表时通过指定 STATS_PERSISTENT 、 STATS_AUTO_RECALC 、STATS_SAMPLE_PAGES 的值来控制相关统计数据属性。

  • innodb_stats_method 决定着在统计某个索引列不重复值的数量时如何对待 NULL 值。

第14章 不好看就要多整容-MySQL基于规则的优化（内含关于子查询优化二三事儿）

MySQL 会依据一些规则，竭尽全力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以被称作查询重写（就是人家觉得你写的语句不好，自己再重写一遍）

  • 条件化简：

    • 移除不必要的括号

    • 常量传递（constant_propagation）

    • 等值传递（equality_propagation）

    • 移除没用的条件（trivial_condition_removal）

    • 表达式计算

    • HAVING子句和WHERE子句的合并

    • 常量表（constant tables）检测

  • 外连接消除

  • 子查询优化

子查询在MySQL中是怎么执行的

  • 标量子查询、行子查询的执行方式

  • IN子查询优化

    • 物化表转连接

    • 将子查询转换为semi-join

  • ANY/ALL子查询优化

  • [NOT] EXISTS子查询的执行

  • 对于派生表的优化

第15、16章 查询优化的百科全书-Explain详解

EXPLAIN语句来帮助我们查看某个查询语句的具体执行计划

其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE 语句前面都可以加上EXPLAIN这个词儿，用来查看这些语句的执行计划

EXPLAIN语句输出的各个列的作用：

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际上使用的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些额外的信息

执行计划输出中各列详解

table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名

id

查询语句中每出现一个 SELECT 关键字，设计 MySQL 的大叔就会为它分配一个唯一的 id 值。

对于连接查询来说，一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的

在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表

查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。

id 为 NULL 表明这个临时表是为了合并两个查询的结果集而创建的

select_type

为每一个 SELECT 关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们
只要知道了某个小查询的 select_type 属性，就知道了这个小查询在整个大查询中扮演了一个什么角色

名称 描述 描述
SIMPLE 查询语句中不包含 UNION 或者子查询 Simple SELECT (not using UNION or subqueries)
PRIMARY 对于包含 UNION 、 UNION ALL 或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的 select_type 值就是 PRIMARY Outermost SELECT
UNION 对于包含 UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的 select_type 值就是 UNION Second or later SELECT statement in a UNION
UNION RESULT MySQL 选择使用临时表来完成 UNION 查询的去重工作，针对该临时表的查询的 select_type 就是 UNION RESULT Result of a UNION
SUBQUERY 如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个 SELECT 关键字代表的那个查 询的 select_type 就是 SUBQUERY
由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍		 First SELECT in subquery
DEPENDENT SUBQUERY 如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是相关子查询，则该子查询的第一个 SELECT 关键字代表的那个查询的 select_type 就是 DEPENDENT SUBQUERY
select_type为DEPENDENT SUBQUERY的查询可能会被执行多次		 First SELECT in subquery, dependent on outer query
DEPENDENT UNION 在包含 UNION 或者 UNION ALL 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 DEPENDENT UNION Second or later SELECT statement in a UNION, dependent on outer query
DERIVED 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的 select_type 就是 DERIVED Derived table
MATERIALIZED 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的 select_type 属性就是 MATERIALIZED Materialized subquery
UNCACHEABLE SUBQUERY 不常用 A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION 不常用 The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

partitions

分区相关

一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL

type

执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法

type 列就表明了这个访问方法

访问方法 描述
system 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是 system
const 当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是 const
eq_ref 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是 eq_ref
ref 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref
fulltext 全文索引
ref_or_null 当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就可能是 ref_or_null
index_merge 单表访问方法时特意强调了在某些场景下可以使用 Intersection 、 Union 、 Sort-Union 这三种索引合并（index_merge）的方式来执行查询
unique_subquery 类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery
index_subquery index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引
range 如果使用索引获取某些 范围区间 的记录，那么就可能使用到 range 访问方法
index 可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index
ALL 全表扫描

一般来说，这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了 All 这个访问方法外，其余的访问方法都能用到索引，除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。

possible_keys和key

在 EXPLAIN 语句输出的执行计划中， possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些

另外需要注意的一点是，possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：

  • 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。

  • 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。

  • 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

输出key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法（type）是 const 、 eq_ref 、 ref 、 ref_or_null 、unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列

可能的值有：

  • const ：常量

  • my.s1.id ：库名、表名、列名

  • func ：函数

rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的 rows 列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。

filtered

之前在分析连接查询的成本时提出过一个 condition filtering 的概念，就是 MySQL 在计算驱动表扇出时采用的一个策略：

  • 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。

  • 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

对于单表查询来说，这个 filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
+----+-------------+-------+------------+------+---------------+----------+---------+------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+-------+----------+-------------+
|  1 | SIMPLE      | s1    | NULL       | ALL  | idx_key1      | NULL     | NULL    | NULL       | 10027 |    10.00 | Using where |
|  1 | SIMPLE      | s2    | NULL       | ref  | idx_key1      | idx_key1 | 303     | my.s1.key1 |     1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+-------+----------+-------------+

从执行计划中可以看出来，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。我们可以看到驱动表 s1 表的执行计划的 rows 列为 9688 ， filtered 列为 10.00 ，这意味着驱动表 s1 的扇出值就是 9688 × 10.00% = 968.8 ，这说明还要对被驱动表执行大约 968 次查询。

Extra

Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句

Extra 描述
No tables used 当查询语句的没有 FROM 子句时
Impossible WHERE 查询语句的 WHERE 子句永远为 FALSE 时
No matching min/max row 当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时
Using index 当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下
Using index condition 在查询语句的执行过程中将要使用 索引条件下推 这个特性
Using where 当我们使用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时
Using join buffer (Block Nested Loop) 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法
Not exists 当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且那个列又是不允许存储 NULL 值的，
Using intersect(...) 说明准备使用 Intersect 索引合并的方式执行查询，括号中的 ... 表示需要进行索引合并的索引名称
Using union(...) 如果出现了 Using union(...) 提示，说明准备使用 Union 索引合并的方式执行查询
Using sort_union(...) 出现了 Using sort_union(...) 提示，说明准备使用 Sort-Union 索引合并的方式执行查询
Zero limit 当我们的 LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录
Using filesort 很

多情况下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，设计 MySQL 的大叔把这种在内存中或者磁盘上进行排序的方式统称为文件排序（英文名：filesort ）
Using temporary 在许多查询的执行过程中， MySQL 可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含 DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询， MySQL 很有可能寻求通过建立内部的临时表来执行查询
Start temporary, End temporary 查询优化器会优先尝试将 IN 子查询转换成 semi-join ，而 semi-join 又有好多种执行策略，当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作时，驱动表查询执行计划的 Extra 列将显示 Start temporary 提示，被驱动表查询执行计划的 Extra 列将显示 End temporary 提示
LooseScan 在将 In 子查询转为 semi-join 时，如果采用的是 LooseScan 执行策略
FirstMatch(tbl_name) 在将 In 子查询转为 semi-join 时，如果采用的是 FirstMatch 执行策略

MySQL 会在包含 GROUP BY 子句的查询中默认添加上 ORDER BY 子句

如果我们并不想为包含 GROUP BY 子句的查询进行排序，需要我们显式的写上 ORDER BY NULL

Json格式的执行计划

EXPLAIN 语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本

查看某个执行计划花费的成本的方式：在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON

EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G

Extented EXPLAIN

在我们使用 EXPLAIN 语句查看了某个查询的执行计划后，紧接着还可以使用 SHOW WARNINGS 语句查看与这个查询的执行计划有关的一些扩展信息

 SHOW WARNINGS\G

最常见的就是 Code 为 1003 的信息，当 Code 值为 1003 时， Message 字段展示的信息类似于查询优化器将我们的查询语句重写后的语句

重写后的语句在很多情况下并不能直接执行

第17章 神兵利器-optimizer trace表的神器功效

在MySQL 5.6以及之后的版本中，设计MySQL的大佬贴心的为这部分小伙伴提出了一个optimizer trace的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量optimizer_trace决定

完整的使用optimizer trace功能的步骤总结如下：

# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";

# 2. 这里输入你自己的查询语句
SELECT ...; 

# 3. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
SELECT * FROM information_schema.OPTIMIZER_TRACE;

# 4. 可能你还要观察其他语句执行的优化过程，重复上面的第2、3步
...

# 5. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";

使用示例：

SET optimizer_trace="enabled=on";

SELECT * FROM s1 WHERE 
    key1 > 'z' AND 
    key2 < 1000000 AND 
    key3 IN ('a', 'b', 'c') AND 
    common_field = 'abc';

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

优化过程大致分为了三个阶段：

  • prepare阶段
  • optimize阶段
  • execute阶段

对于单表查询来说，我们主要关注optimize阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程

第18章 调节磁盘和CPU的矛盾-InnoDB的Buffer

总结

  1. 磁盘太慢，用内存作为缓存很有必要。

  2. Buffer Pool 本质上是 InnoDB 向操作系统申请的一段连续的内存空间，可以通过 innodb_buffer_pool_size 来调整它的大小。

  3. Buffer Pool 向操作系统申请的连续内存由控制块和缓存页组成，每个控制块和缓存页都是一一对应的，在填充足够多的控制块和缓存页的组合后， Buffer Pool 剩余的空间可能产生不够填充一组控制块和缓存页，这部分空间不能被使用，也被称为 碎片 。

  4. InnoDB 使用了许多 链表 来管理 Buffer Pool 。

  5. free链表 中每一个节点都代表一个空闲的缓存页，在将磁盘中的页加载到 Buffer Pool 时，会从 free 链表 中寻找空闲的缓存页。

  6. 为了快速定位某个页是否被加载到 Buffer Pool ，使用 表空间号 + 页号 作为 key ，缓存页作为 value ，建立哈希表。

  7. 在 Buffer Pool 中被修改的页称为 脏页 ，脏页并不是立即刷新，而是被加入到 flush链表 中，待之后的某个时刻同步到磁盘上。

  8. LRU链表 分为 young 和 old 两个区域，可以通过 innodb_old_blocks_pct 来调节 old 区域所占的比例。首次从磁盘上加载到 Buffer Pool 的页会被放到 old 区域的头部，在 innodb_old_blocks_time 间隔时间内访问该页不会把它移动到 young 区域头部。在 Buffer Pool 没有可用的空闲缓存页时，会首先淘汰掉 old 区域的一些页。

  9. 我们可以通过指定 innodb_buffer_pool_instances 来控制 Buffer Pool 实例的个数，每个 Buffer Pool 实例中都有各自独立的链表，互不干扰。

  10. 自 MySQL 5.7.5 版本之后，可以在服务器运行过程中调整 Buffer Pool 大小。每个 Buffer Pool 实例由若干个 chunk 组成，每个 chunk 的大小可以在服务器启动时通过启动参数调整。

  11. 可以用下边的命令查看 Buffer Pool 的状态信息：

    SHOW ENGINE INNODB STATUS\G

第19章 从猫爷被杀说起-事务简介

概述

原子性（Atomicity）、隔离性（Isolation）、一致性（Consistency）和持久性（Durability）

ACID是英文酸的意思

事务是一个抽象的概念，它其实对应着一个或多个数据库操作，设计数据库的大佬根据这些操作所执行的不同阶段把事务大致上划分成了这么几个状态：

只有当事务处于提交的或者中止的状态时，一个事务的生命周期才算是结束了

目前只有InnoDB和NDB存储引擎支持事务

MySQL中事务的语法

# 开启一个事务
BEGIN [WORK];


# 开启一个事务，默认读写模式
START TRANSACTION;
## 可以跟修饰符 READ ONLY、READ WRITE、WITH CONSISTENT SNAPSHOT
START TRANSACTION READ ONLY;
START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT


# 提交事务
COMMIT [WORK]


# 回滚事务
ROLLBACK [WORK]

自动提交

系统变量autocommit

SHOW VARIABLES LIKE 'autocommit';

默认值为ON，也就是说默认情况下，如果我们不显式的使用START TRANSACTION或者BEGIN语句开启一个事务，那么每一条语句都算是一个独立的事务，这种特性称之为事务的自动提交。

如果我们想关闭这种自动提交的功能，可以使用下面两种方法之一：

  • 显式的的使用START TRANSACTION或者BEGIN语句开启一个事务。

    这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。

  • 把系统变量autocommit的值设置为OFF，就像这样：

    SET autocommit = OFF;

隐式提交

当我们使用START TRANSACTION或者BEGIN语句开启了一个事务，或者把系统变量autocommit的值设置为OFF时，事务就不会进行自动提交，但是如果我们输入了某些语句之后就会悄悄的提交掉，就像我们输入了COMMIT语句了一样，这种因为某些特殊的语句而导致事务提交的情况称为隐式提交，这些会导致事务隐式提交的语句包括：

  • 定义或修改数据库对象的数据定义语言（Data definition language，缩写为：DDL）

    • 所谓的数据库对象，指的就是数据库、表、视图、存储过程等

  • 隐式使用或修改mysql数据库中的表

    • ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD

  • 事务控制或关于锁定的语句

    • 在一个事务还没提交或者回滚时就又使用START TRANSACTION或者BEGIN语句开启了另一个事务

    • 使用LOCK TABLES、UNLOCK TABLES等关于锁定的语句

  • 加载数据的语句

    • LOAD DATA

  • 关于MySQL复制的一些语句

    • START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO

  • 其它的一些语句

    • ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET

保存点

保存点（英文：savepoint）的概念，就是在事务对应的数据库语句中打几个点，我们在调用ROLLBACK语句时可以指定会滚到哪个点，而不是回到最初的原点。

# 定义保存点
SAVEPOINT 保存点名称;

# 回滚到某个保存点
ROLLBACK [WORK] TO [SAVEPOINT] 保存点名称;

# 删除某个保存点
RELEASE SAVEPOINT 保存点名称;

第20、21章 说过的话就一定要办到-redo日志

redo日志概念

为了满足持久性

想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。

把修改了哪些东西记录一下就好

因为在系统奔溃重启时需要按照上述内容所记录的步骤重新更新数据页，所以上述内容也被称之为 重做日志 ，英文名为 redo log ，我们也可以土洋结合，称之为 redo日志

redo日志格式

InnoDB 会自动的为表添加一个称之为 row_id 的隐藏列

服务器会在内存中维护一个全局变量 服务器会在内存中维护一个全局变量，每当向某个包含隐藏的 row_id 列的表中插入一条记录时，就会把该变量的值当作新记录的 row_id 列的值，并且把该变量自增1。

redo 日志本质上只是记录了一下事务对数据库做了哪些修改

把一条记录插入到一个页面时需要更改的地方非常多

redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统奔溃重启后可以把事务所做的任何修改都恢复出来

Mini-Transaction

以组的形式写入redo日志

语句在执行过程中可能修改若干个页面。由于对这些页面的更改都发生在 Buffer Pool 中，所以在修改完页面之后，需要记录一下相应的 redo 日志。在执行语句的过程中产生的 redo 日志被设计 InnoDB 的大叔人为的划分成了若干个不可分割的组，比如：

  • 更新 Max Row ID 属性时产生的 redo 日志是不可分割的。

  • 向聚簇索引对应 B+ 树的页面中插入一条记录时产生的 redo 日志是不可分割的。

  • 向某个二级索引对应 B+ 树的页面中插入一条记录时产生的 redo 日志是不可分割的。

  • 还有其他的一些对页面的访问操作时产生的 redo 日志是不可分割的

Mini-Transaction的概念

对底层页面中的一次原子访问的过程称之为一个 Mini-Transaction ，简称 mtr

一个所谓的 mtr 可以包含一组 redo 日志，在进行奔溃恢复时这一组 redo 日志作为一个不可分割的整体

一个事务可以包含若干条语句，每一条语句其实是由若干个 mtr 组成，每一个 mtr 又可以包含若干条 redo 日志

redo日志的写入过程

redo log block

设计 InnoDB 的大叔为了更好的进行系统奔溃恢复，他们把通过 mtr 生成的 redo 日志都放在了大小为 512字节的 页 中。为了和我们前边提到的表空间中的页做区别，我们这里把用来存储 redo 日志的页称为 block

redo日志缓冲区

设计 InnoDB 的大叔为了解决磁盘速度过慢的问题而引入了 Buffer Pool 。同理，写入 redo 日志时也不能直接直接写到磁盘上，实际上在服务器启动时就向操作系统申请了一大片称之为 redo log buffer 的连续内存空间，翻译成中文就是 redo日志缓冲区 ，我们也可以简称为 log buffer 。这片内存空间被划分成若干个连续的 redo log block

通过启动参数 innodb_log_buffer_size 来指定 log buffer 的大小，在 MySQL 5.7.21 这个版本中，该启动参数的默认值为 16MB

redo日志写入log buffer

向 log buffer 中写入 redo 日志的过程是顺序的，也就是先往前边的block中写，当该block的空闲空间用完之后再往下一个block中写

buf_free 的全局变量，该变量指明后续写入的 redo 日志应该写入到 log buffer 中的哪个位置

redo日志文件

redo日志刷盘时机

mtr 运行过程中产生的一组 redo 日志在 mtr 结束时会被复制到 log buffer 中，可是这些日志总在内存里呆着也不是个办法，在一些情况下它们会被刷新到磁盘里

  • log buffer 空间不足时

  • 事务提交时

  • 后台线程不停的刷刷刷

  • 正常关闭服务器时

  • 做所谓的 checkpoint 时（和事务的 checkpoint 不同）

  • 等等

redo日志文件组

MySQL 的数据目录（使用 SHOW VARIABLES LIKE 'datadir' 查看）下默认有两个名为 ib_logfile0 和 ib_logfile1 的文件， log buffer 中的日志默认情况下就是刷新到这两个磁盘文件中。如果我们对默认的 redo 日志文件不满意，可以通过下边几个启动参数来调节：

  • innodb_log_group_home_dir

    • 该参数指定了 redo 日志文件所在的目录，默认值就是当前的数据目录。

  • innodb_log_file_size

    • 该参数指定了每个 redo 日志文件的大小，在 MySQL 5.7.21 这个版本中的默认值为 48MB

  • innodb_log_files_in_group

    • 该参数指定 redo 日志文件的个数，默认值为2，最大值为100。

在将 redo 日志写入 日志文件组 时，是从 ib_logfile0 开始写，如果 ib_logfile0 写满了，就接着 ib_logfile1 写，同理， ib_logfile1 写满了就去写 ib_logfile2 ，依此类推。如果写到最后一个文件该咋办？那就重新转到 ib_logfile0 继续写

总共的 redo 日志文件大小其实就是： innodb_log_file_size × innodb_log_files_in_group

如果采用循环使用的方式向redo日志文件组里写数据的话，那岂不是要追尾，也就是后写入的 redo日志覆盖掉前边写的redo日志？当然可能了！所以设计InnoDB的大叔提出了checkpoint的概念

redo日志文件格式

log buffer 本质上是一片连续的内存空间，被划分成了若干个 512 字节大小的 block 。将log buffer中的redo日志刷新到磁盘的本质就是把block的镜像写入日志文件中，所以 redo 日志文件其实也是由若干个 512 字节大小的block组成

Log Sequeue Number

设计 InnoDB 的大叔为记录已经写入的 redo 日志量，设计了一个称之为 Log Sequeue Number 的全局变量，翻译过来就是： 日志序列号 ，简称 lsn

在向 log buffer 中写入 redo 日志时不是一条一条写入的，而是以一个 mtr 生成的一组 redo 日志为单位进行写入的

每一组由mtr生成的redo日志都有一个唯一的LSN值与其对应，LSN值越小，说明 redo日志产生的越早

flushed_to_disk_lsn

redo 日志是首先写到 log buffer 中，之后才会被刷新到磁盘上的 redo 日志文件。所以设计 InnoDB 的大叔提出了一个称之为 buf_next_to_write 的全局变量，标记当前 log buffer 中已经有哪些日志被刷新到磁盘中了

我们前边说 lsn 是表示当前系统中写入的 redo 日志量，这包括了写到 log buffer 而没有刷新到磁盘的日志，相应的，设计 InnoDB 的大叔提出了一个表示刷新到磁盘中的 redo 日志量的全局变量，称之为 flushed_to_disk_lsn 。系统第一次启动时，该变量的值和初始的 lsn 值是相同的，都是 8704 。

随着系统的运行， redo 日志被不断写入 log buffer ，但是并不会立即刷新到磁盘， lsn 的值就和 flushed_to_disk_lsn 的值拉开了差距。

如果两者的值相同时，说明log buffer中的所有redo日志都已经刷新到磁盘中了

flush链表中的LSN

我们知道一个 mtr 代表一次对底层页面的原子访问，在访问过程中可能会产生一组不可分割的 redo 日志，在mtr 结束时，会把这一组 redo 日志写入到 log buffer 中。除此之外，在 mtr 结束时还有一件非常重要的事情要做，就是把在mtr执行过程中可能修改过的页面加入到Buffer Pool的flush链表。

flush链表中的脏页按照修改发生的时间顺序进行排序，也就是按照oldest_modification代表的LSN值进行排序，被多次更新的页面不会重复插入到flush链表中，但是会更新newest_modification属性的值。

checkpoint

有一个很不幸的事实就是我们的 redo 日志文件组容量是有限的，我们不得不选择循环使用 redo 日志文件组中的文件，但是这会造成最后写的 redo 日志与最开始写的 redo 日志 追尾 ，这时应该想到：redo日志只是为了系统奔溃后恢复脏页用的，如果对应的脏页已经刷新到了磁盘，也就是说即使现在系统奔溃，那么在重启后也用不着使用redo日志恢复该页面了，所以该redo日志也就没有存在的必要了，那么它占用的磁盘空间就可以被后续的redo日志所重用。也就是说：判断某些redo日志占用的磁盘空间是否可以覆盖的依据就是它对应的脏页是否已经刷新到磁盘里。

设计 InnoDB 的大叔提出了一个全局变量 checkpoint_lsn 来代表当前系统中可以被覆盖的 redo 日志总量是多少，这个变量初始值也是 8704 。

查看系统中的各种LSN值

使用 SHOW ENGINE INNODB STATUS 命令查看当前 InnoDB 存储引擎中的各种 LSN 值的情况

SHOW ENGINE INNODB STATUS\G

  • Log sequence number ：代表系统中的 lsn 值，也就是当前系统已经写入的 redo 日志量，包括写入 log buffer 中的日志。

  • Log flushed up to ：代表 flushed_to_disk_lsn 的值，也就是当前系统已经写入磁盘的 redo 日志量。

  • Pages flushed up to ：代表 flush链表 中被最早修改的那个页面对应的 oldest_modification 属性值。

  • Last checkpoint at ：当前系统的 checkpoint_lsn 值。

innodb_flush_log_at_trx_commit的用法

如果有的同学对事务的 持久性 要求不是那么强烈的话，可以选择修改一个称为 innodb_flush_log_at_trx_commit 的系统变量的值，该变量有3个可选的值：

  • 0 ：当该系统变量值为0时，表示在事务提交时不立即向磁盘中同步 redo 日志，这个任务是交给后台线程做的。

  • 1 ：当该系统变量值为1时，表示在事务提交时需要将 redo 日志同步到磁盘，可以保证事务的 持久性 。 1 也是 innodb_flush_log_at_trx_commit 的默认值。

  • 2 ：当该系统变量值为2时，表示在事务提交时需要将 redo 日志写到操作系统的缓冲区中，但并不需要保证将日志真正的刷新到磁盘。

崩溃恢复

可以在重启时根据 redo 日志中的记录就可以将页面恢复到系统奔溃前的状态

第22、23章 后悔了怎么办-undo日志

事务回滚的需求

事务 需要保证 原子性

为了保证事务的原子性，我们需要把东西改回原先的样子，这个过程就称之为 回滚 （英文名： rollback ）

设计数据库的大叔把这些为了回滚而记录的这些东东称之为 撤销日志，英文名为 undo log ，我们也可以土洋结合，称之为 undo日志 。

事务id

一个事务可以是一个只读事务，或者是一个读写事务

  • START TRANSACTION READ ONLY 语句开启一个只读事务

  • START TRANSACTION READ WRITE 语句开启一个读写事务

如果某个事务执行过程中对某个表执行了增、删、改操作，那么 InnoDB 存储引擎就会给它分配一个独一无二的事务id ，

我们前边说过对某个查询语句执行EXPLAIN分析它的查询计划时，有时候在Extra列会看到Using temporary的提示，这个表明在执行该查询语句时会用到内部临时表。这个所谓的内部临时表和我们手动用CREATE TEMPORARY TABLE创建的用户临时表并不一样，在事务回滚时并不需要把执行SELECT语句过程中用到的内部临时表也回滚，在执行SELECT语句用到内部临时表时并不会为它分配事务id。

只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的 事务id 。

服务器会在内存中维护一个全局变量，每当需要为某个事务分配一个 事务id 时，就会把该变量的值当作 事务id 分配给该事务，并且把该变量自增1。

这样就可以保证整个系统中分配的 事务id 值是一个递增的数字。先被分配 id 的事务得到的是较小的 事务id ，后被分配 id 的事务得到的是较大的 事务id 。

聚簇索引的记录除了会保存完整的用户数据以外，而且还会自动添加名为trx_id、roll_pointer的隐藏列，如果用户没有在表中定义主键以及UNIQUE键，还会自动添加一个名为row_id的隐藏列。

其中的 trx_id 列其实还蛮好理解的，就是某个对这个聚簇索引记录做改动的语句所在的事务对应的 事务id 而已（此处的改动可以是 INSERT 、 DELETE 、 UPDATE 操作）

undo日志的格式

为了实现事务的 原子性 ， InnoDB 存储引擎在实际进行增、删、改一条记录时，都需要先把对应的 undo日志 记下来。

一个事务在执行过程中可能新增、删除、更新若干条记录，也就是说需要记录很多条对应的 undo日志 ，这些 undo日志 会被从 0 开始编号，这个编号也被称之为 undo no

INSERT操作对应的undo日志

当我们向某个表中插入一条记录时，实际上需要向聚簇索引和所有的二级索引都插入一条记录。不过记录undo日志时，我们只需要考虑向聚簇索引插入记录时的情况就好了，因为其实聚簇索引记录和二级索引记录是一一对应的，我们在回滚插入操作时，只需要知道这条记录的主键信息，然后根据主键信息做对应的删除操作，做删除操作时就会顺带着把所有二级索引中相应的记录也删除掉。

roll pointer隐藏列的含义

roll_pointer 本质就是一个指针，指向记录对应的undo日志

DELETE操作对应的undo日志

插入到页面中的记录会根据记录头信息中的 next_record 属性组成一个单向链表，我们把这个链表称之为 正常记录链表

被删除的记录其实也会根据记录头信息中的 next_record 属性组成一个链表，只不过这个链表中的记录占用的存储空间可以被重新利用，所以也称这个链表为 垃圾链表

假设现在我们准备使用 DELETE 语句把 正常记录链表 中的最后一条记录给删除掉，其实这个删除的过程需要经历两个阶段：

  • 阶段一：仅仅将记录的 delete_mask 标识位设置为 1 ，其他的不做修改（其实会修改记录的 trx_id 、roll_pointer 这些隐藏列的值）。设计 InnoDB 的大叔把这个阶段称之为 delete mark

    • 记录的 delete_mask 值被设置为 1 ，但是并没有被加入到 垃圾链表 。这种中间状态主要是为了实现MVCC功能

  • 阶段二：当该删除语句所在的事务提交之后，会有专门的线程后来真正的把记录删除掉。所谓真正的删除就是把该记录从 正常记录链表 中移除，并且加入到 垃圾链表 中。这个阶段称之为 purge

在删除语句所在的事务提交之前，只会经历 阶段一 ，也就是 delete mark 阶段

事务执行时，undo 日志会串成了一个链表，就是 版本链

UPDATE操作对应的undo日志

在执行 UPDATE 语句时， InnoDB 对更新主键和不更新主键这两种情况有截然不同的处理方案。

不更新主键的情况

  • 就地更新（in-place update）

    • 对于被更新的每个列来说，如果更新后的列和更新前的列占用的存储空间都一样大

  • 先删除掉旧记录，再插入新记录

    • 如果有任何一个被更新的列更新前和更新后占用的存储空间大小不一致
更新主键的情况

针对 UPDATE 语句中更新了记录主键值的这种情况， InnoDB 在聚簇索引中分了两步处理：

  • 将旧记录进行 delete mark 操作

    • 之所以只对旧记录做delete mark操作，是因为别的事务同时也可能访问这条记录，如果把它真正的删除加入到垃圾链表后，别的事务就访问不到了。这个功能就是所谓的MVCC

  • 根据更新后各列的值创建一条新记录，并将其插入到聚簇索引中（需重新定位插入的位置）

    • 由于更新后的记录主键值发生了改变，所以需要重新从聚簇索引中定位这条记录所在的位置，然后把它插进去。

单个事务中的Undo页面链表

在一个事务执行过程中，可能混着执行 INSERT 、 DELETE 、 UPDATE 语句，也就意味着会产生不同类型的 undo 日志 。但是我们前边又强调过，同一个 Undo页面 要么只存储 TRX_UNDO_INSERT 大类的 undo日志 ，要么只存储TRX_UNDO_UPDATE 大类的 undo日志 ，反正不能混着存，所以在一个事务执行过程中就可能需要2个 Undo页面 的链表，一个称之为 insert undo链表 ，另一个称之为 update undo链表

对普通表和临时表的记录改动时产生的 undo日志 要分别记录

所以在一个事务中最多有4个以 Undo页面 为节点组成的链表

并不是在事务一开始就会为这个事务分配这4个链表，而是按需分配，啥时候需要啥时候再分配，不需要就不分配

多个事务中的Undo页面链表

为了尽可能提高 undo日志 的写入效率，不同事务执行过程中产生的undo日志需要被写入到不同的Undo页面链表中。

回滚段相关配置

系统中一共有 128 个回滚段，其实这只是默认值，我们可以通过启动参数 innodb_rollback_segments 来配置回滚段的数量，可配置的范围是 1~128

配置undo表空间

默认情况下，针对普通表设立的回滚段（第 0 号以及第 33~127 号回滚段）都是被分配到系统表空间的。其中的第 0 号回滚段是一直在系统表空间的，但是第 33~127 号回滚段可以通过配置放到自定义的 undo表空间 中。但是这种配置只能在系统初始化（创建数据目录时）的时候使用，一旦初始化完成，之后就不能再次更改了。

相关启动参数：

  • 通过 innodb_undo_directory 指定 undo表空间 所在的目录，如果没有指定该参数，则默认 undo表空间 所在的目录就是数据目录

  • 通过 innodb_undo_tablespaces 定义 undo表空间 的数量。该参数的默认值为 0 ，表明不创建任何 undo表空间

设立 undo表空间 的一个好处就是在 undo表空间 中的文件大到一定程度时，可以自动的将该 undo表空间 截断（truncate）成一个小文件。而系统表空间的大小只能不断的增大，却不能截断。

第24章 一条记录的多幅面孔-事务的隔离级别与 MVCC

MySQL 是一个 客户端／服务器 架构的软件，对于同一个服务器来说，可以有若干个客户端与之连接，每个客户端与服务器连接上之后，就可以称之为一个会话（ Session ）。每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是某个事务的一部分，也就是对于服务器来说可能同时处理多个事务。

理论上在某个事务对某个数据进行访问时，其他事务应该进行排队，当该事务提交之后，其他事务才可以继续访问这个数据。但是这样子的话对性能影响太大，我们既想保持事务的 隔离性 ，又想让服务器在处理访问同一数据的多个事务时性能尽量高些，鱼和熊掌不可得兼，舍一部分 隔离性 而取性能者也

MVCC （Multi-Version Concurrency Control ，多版本并发控制）

事务隔离级别

事务并发执行遇到的问题

  • 脏写（ Dirty Write ）

    • 一个事务修改了另一个未提交事务修改过的数据

  • 脏读（ Dirty Read ）

    • 一个事务读到了另一个未提交事务修改过的数据

  • 不可重复读（Non-Repeatable Read）

    • 一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值

  • 幻读（Phantom）

    • 一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来

是删除了一些符合 number > 0 的记录而不是插入新记录，另一个 Session 读取到的数据变少了，这种现象算不算 幻读 呢？

不算。这相当于对每一条记录都发生了不可重复读的现象。幻读只是重点强调了读取到了之前读取没有获取到的记录。

SQL标准中的四种隔离级别

给这些问题按照严重性来排一下序：

脏写 > 脏读 > 不可重复读 > 幻读

设立一些隔离级别，隔离级别越低，越严重的问题就越可能发生。

SQL标准准中设立了4个 隔离级别 ：

  • READ UNCOMMITTED ：未提交读。

  • READ COMMITTED ：已提交读。

  • REPEATABLE READ ：可重复读。

  • SERIALIZABLE ：可串行化。

SQL标准 中规定，针对不同的隔离级别，并发事务可以发生不同严重程度的问题，具体情况如下：

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not Possible Possible Possible
REPEATABLE READ Not Possible Not Possible Possible
SERIALIZABLE Not Possible Not Possible Not Possible

脏写这个问题太严重了，不论是哪种隔离级别，都不允许脏写的情况发生

MySQL中支持的四种隔离级别

不同的数据库厂商对 SQL标准 中规定的四种隔离级别支持不一样，比方说 Oracle 就只支持 READ COMMITTED 和SERIALIZABLE 隔离级别。

MySQL 虽然支持4种隔离级别，但与 SQL标准 中所规定的各级隔离级别允许发生的问题却有些出入，MySQL在REPEATABLE READ隔离级别下，是可以禁止幻读问题的发生的

MySQL 的默认隔离级别为 REPEATABLE READ

如何设置事务的隔离级别
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;

# level 可选值有4个：
level: {
 REPEATABLE READ
 | READ COMMITTED
 | READ UNCOMMITTED
 | SERIALIZABLE
}


# 查看当前会话默认的隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';
SELECT @@transaction_isolation;

在 SET 关键字后可以放置 GLOBAL 关键字、 SESSION 关键字或者什么都不放，这样会对不同范围的事务产生不同的影响

  • 使用 GLOBAL 关键字（在全局范围影响）

    • 只对执行完该语句之后产生的会话起作用。

    • 当前已经存在的会话无效。

  • 使用 SESSION 关键字（在会话范围影响）

    • 对当前会话的所有后续的事务有效

    • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务。

    • 如果在事务之间执行，则对后续的事务有效。

  • 什么关键字都不使用

    • 只对当前会话中下一个即将开启的事务有效。

    • 下一个事务执行完后，后续事务将恢复到之前的隔离级别。

    • 该语句不能在已经开启的事务中间执行，会报错的。

可以使用设置系统变量transaction_isolation的方式来设置事务的隔离级别

transaction_isolation是在MySQL 5.7.20的版本中引入来替换tx_isolation的，如果你使用的是之前版本的MySQL，请将上述用到系统变量transaction_isolation的地方替换为tx_isolation

MVCC原理

版本链

对于使用 InnoDB 存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列

  • trx_id ：每次一个事务对某条聚簇索引记录进行改动时，都会把该事务的 事务id 赋值给 trx_id 隐藏列。

  • roll_pointer ：每次对某条聚簇索引记录进行改动时，都会把旧的版本写入到 undo日志 中，然后这个隐藏列就相当于一个指针，可以通过它来找到该记录修改前的信息。

聚簇索引记录每次更新后，都会将旧值放到一条 undo日志 中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被 roll_pointer 属性连接成一个链表，我们把这个链表称之为 版本链 ，版本链的头节点就是当前记录最新的值

ReadView

对于使用 READ UNCOMMITTED 隔离级别的事务来说，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了；

对于使用 SERIALIZABLE 隔离级别的事务来说，设计 InnoDB 的大叔规定使用加锁的方式来访问记录

对于使用 READ COMMITTED 和 REPEATABLE READ 隔离级别的事务来说，都必须保证读到已经提交了的事务修改过的记录，也就是说假如另一个事务已经修改了记录但是尚未提交，是不能直接读取最新版本的记录的，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。

为此，设计 InnoDB 的大叔提出了一个 ReadView 的概念，这个 ReadView 中主要包含4个比较重要的内容：

  • m_ids ：表示在生成 ReadView 时当前系统中活跃的读写事务的 事务id 列表。

  • min_trx_id ：表示在生成 ReadView 时当前系统中活跃的读写事务中最小的 事务id ，也就是 m_ids 中的最小值。

  • max_trx_id ：表示生成 ReadView 时系统中应该分配给下一个事务的 id 值。

  • creator_trx_id ：表示生成该 ReadView 的事务的 事务id 。

READ COMMITTED 和 REPEATABLE READ 隔离级别的的一个非常大的区别就是它们生成ReadView的时机不同。

  • READ COMMITTED —— 每次读取数据前都生成一个ReadView

  • REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

MVCC小结

MVCC （Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用 READ COMMITTD 、 REPEATABLE READ 这两种隔离级别的事务在执行普通的 SEELCT 操作时访问记录的版本链的过程，这样子可以使不同事务的 读-写 、 写-读 操作并发执行，从而提升系统性能。

READ COMMITTD 、REPEATABLE READ 这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

关于purge

insert undo 在事务提交之后就可以被释放掉了，而 update undo 由于还需要支持 MVCC ，不能立即删除掉。

为了支持 MVCC ，对于 delete mark 操作来说，仅仅是在记录上打一个删除标记，并没有真正将它删除掉。

随着系统的运行，在确定系统中包含最早产生的那个 ReadView 的事务不会再访问某些 update undo日志 以及被打了删除标记的记录后，有一个后台运行的 purge 线程 会把它们真正的删除掉。

第25章 工作面试老大难-锁

解决并发事务带来问题的两种基本方式

并发事务访问相同记录的情况大致可以划分为3种：

  • 读-读

  • 写-写

  • 读-写、写-读

    • 也就是一个事务进行读取操作，另一个进行改动操作

锁 其实是一个内存中的结构

怎么解决 脏读 、 不可重复读 、 幻读 这些问题呢？其实有两种可选的解决方案：

  • 读操作利用多版本并发控制（ MVCC ），写操作进行 加锁

    • 采用 MVCC 时， 读-写 操作并不冲突

  • 读、写操作都采用 加锁 的方式

    • 如果我们的一些业务场景不允许读取记录的旧版本，而是每次都必须去读取记录的最新版本，比方在银行存款的事务中

很明显，采用 MVCC 方式的话， 读-写 操作彼此并不冲突，性能更高，采用 加锁 方式的话， 读-写 操作彼此需要排队执行，影响性能。一般情况下我们当然愿意采用 MVCC 来解决 读-写 操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用 加锁 的方式执行，那也是没有办法的事。

一致性读（Consistent Reads）

事务利用 MVCC 进行的读取操作称之为 一致性读 ，或者 一致性无锁读 ，有的地方也称之为 快照读 。所有普通的 SELECT 语句（ plain SELECT ）在 READ COMMITTED 、 REPEATABLE READ 隔离级别下都算是 一致性读

一致性读 并不会对表中的任何记录做 加锁 操作，其他事务可以自由的对表中的记录做改动

锁定读（Locking Reads）

共享锁和独占锁

在使用 加锁 的方式解决问题时，由于既要允许 读-读 情况不受影响，又要使 写-写 、 读-写 或 写-读 情况中的操作相互阻塞

需要给锁分类：

  • 共享锁 ，英文名： Shared Locks ，简称 S锁 。

    • 在事务要读取一条记录时，需要先获取该记录的 S锁 。

  • 独占锁 ，也常称 排他锁 ，英文名： Exclusive Locks ，简称 X锁 。

    • 在事务要改动一条记录时，需要先获取该记录的 X锁 。
兼容性 X S
X 不兼容 不兼容
S 不兼容 兼容
锁定读的语句
# 对读取的记录加 S锁
SELECT ... LOCK IN SHARE MODE;


# 对读取的记录加 X锁
 SELECT ... FOR UPDATE;

写操作

  • DELETE

    • 获取 X锁 的 锁定读

  • UPDATE

    • 分为三种情况

      • 未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化

        • 获取 X锁 的 锁定读

      • 未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化

        • 定位待修改记录在 B+ 树中位置的过程看成是一个获取 X 锁 的锁定读 ，新插入的记录由 INSERT 操作提供的 隐式锁 进行保护

      • 修改了该记录的键值，则相当于在原记录上做 DELETE 操作之后再来一次 INSERT 操作

  • INSERT

    • 隐式锁

多粒度锁

行锁

表锁

意向锁

兼容性 X IX S IS
X 不兼容 不兼容 不兼容 不兼容
IX 不兼容 兼容 不兼容 兼容
S 不兼容 不兼容 兼容 兼容
IS 不兼容 兼容 兼容 兼容

MySQL中的行锁和表锁

其他存储引擎中的锁

对于 MyISAM 、 MEMORY 、 MERGE 这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。

InnoDB存储引擎中的锁

InnoDB中的表级锁

  • 表级别的 S锁 、 X锁

  • 表级别的 IS锁 、 IX锁

  • 表级别的 AUTO-INC锁

InnoDB中的行级锁

  • Record Locks

    • 正经记录锁

    • 官方的类型名称为：LOCK_REC_NOT_GAP

  • Gap Locks

    • 间隙锁

    • 仅仅是为了防止插入幻影记录而提出的

  • Next-Key Locks

    • 官方的类型名称为： LOCK_ORDINARY

  • Insert Intention Locks

    • 官方的类型名称为： LOCK_INSERT_INTENTION

    • 插入意向锁

  • 隐式锁

posted @ 2022-07-29 14:46  流星<。)#)))≦  阅读(195)  评论(0)  编辑  收藏  举报