客户端查询mysql版本：select version();
1 第 1 章 装作自己是个小白 - 重新认识 MySQL 1.1 MySQL 的客户端／服务器架构 每个进程都有一个唯一的编号，称为 进程ID ，英文名叫 PID ，这个编号是在我们启动程序的时候由操作系统随 机分配的，操作系统会保证在某一时刻同一台机器上的进程号不重复。比如我们启动的 MySQL 服务器进程的默认名称为 mysqld ， 而我们常用的 MySQL 客户端进程的默认名称为 mysql 1.2 MySQL 的安装 `MySQL`的大部分安装包都包含了服务器程序和客户端程序，不过在Linux下使用RPM包时会有单独的服 务器RPM包和客户端RPM包，需要分别安装 类UNIX操作系统非常多，比如FreeBSD、Linux、macOS、Solaris等都属于UNIX操作系统的范畴，我们这里使用macOS操作系统代表类UNIX操作系统来运行MySQL1）进入该路径下：C:\ProgramData\MySQL\MySQL Server 5.7。
这里面有一个重要的目录和一个重要的配置文件，需要我们注意：
对于配置文件my.ini的详细说明，这里不介绍，可以参考如下网址：https://blog.csdn.net/hanwuqia0370/article/details/85680775对于这个data目录，先看看里面有什么：
从上图中可以看到：
系统默认的数据库：
2）安装mysql后，注意区分如下几个目录的作用。
5、怎么查看MYSQL服务是否在Windows启动？
1）在左下角输入框(或win7中使用Win + R可以打开搜索框)中，输入"services.msc"。
2)当启动了MySQL后，会在这里出现一个MYSQL服务进程，可以检查MYSQL是否启动。
1.4 启动 MySQL 客户端程序 启动 MySQL 客户端，并且连接到服务器：mysql -hlocalhost -uroot -p123456 如果我们想断开客户端与服务器的连接并且关闭客户端的话，可以在 mysql> 提示符后输入下边任意一个命令： 1. quit 2. exit 3. \q 1.4.1 连接注意事项 最好不要在一行命令中输入密码。 我们直接在黑框框里输入密码很可能被别人看到，这和你当着别人的面输入银行卡密码没啥区别，所以我们 在执行 mysql 连接服务器的时候可以不显式的写出密码，就像这样： mysql -hlocahhost -uroot -p 点击回车之后才会提示你输入密码： Enter password: 如果你使用的是类 UNIX 系统，并且省略 -u 参数后，会把你登陆操作系统的用户名当作 MySQL 的用户名去 处理。 比方说我用登录操作系统的用户名是 xiaohaizi ，那么在我的机器上下边这两条命令是等价的： mysql -u xiaohaizi -p mysql -p 对于 Windows 系统来说，默认的用户名是 ODBC ，你可以通过设置环境变量 USER 来添加一个默认用户名。 1.5 客户端与服务器连接的过程 我们现在已经知道如何启动 MySQL 的服务器程序，以及如何启动客户端程序来连接到这个服务器程序。运行着的 服务器程序和客户端程序本质上都是计算机上的一个进程，所以客户端进程向服务器进程发送请求并得到回复的 过程 本质上是一个进程间通信的过程 ！ MySQL 支持下边三种 客户端进程和服务器进程的通信方式。 1.5.1 TCP/IP 如果 3306 端口号已经被别的进程占用了或者我们单纯的想自定义该数据库实例监听的端口号，那我们可以在启 动服务器程序的命令行里添加 -P 参数来明确指定一下端口号，比如这样： mysqld -P3307 监听的端口号为 3307 ，那我们启动客户端程序时可以这样写：启动客户端程序时可以这样写： mysql -h127.0.0.1 -uroot -P3307 -p 1.5.2 命名管道和共享内存(Windows操作系统中的两种进程间通信方式) 需要在启动服务器程序的命令中加上 --enable-named-pipe 参数，然后在启动客户端程序的命令中加入 --pipe 或者 --protocol=pipe 参数 使用 共享内存 来进行进程间通信 需要在启动服务器程序的命令中加上 --shared-memory 参数，在成功启动服务器后， 共享内存 便成为本地客户端程序的默认连接方式，不过我们也可以在启动客户端程序的命令中加入 --protocol=memory 参数来显式的指定使用共享内存进行通信。不过需要注意的是，使用 共享内存 的方式进行通信的服务器进程和客户端进程必须在同一台 Windows 主机中。 1.5.3 Unix 域套接字文件 如果我们的服务器进程和客户端进程都运行在同一台操作系统为类 Unix 的机器上的话，我们可以使用 Unix域套 接字文件 来进行进程间通信。如果我们在启动客户端程序的时候指定的主机名为 localhost ，或者指定了 -- protocol=socket 的启动参数，那服务器程序和客户端程序之间就可以通过 Unix 域套接字文件来进行通信了。 MySQL 服务器程序默认监听的 Unix 域套接字文件路径为 /tmp/mysql.sock ，客户端程序也默认连接到这个 Unix 域套接字文件。如果我们想改变这个默认路径，可以在启动服务器程序时指定 socket 参数，就像这样： mysqld --socket=/tmp/a.txt 这样服务器启动后便会监听 /tmp/a.txt 。在服务器改变了默认的 UNIX 域套接字文件后，如果客户端程序想通 过 UNIX 域套接字文件进行通信的话，也需要显式的指定连接到的 UNIX 域套接字文件路径，就像这样： mysql -hlocalhost -uroot --socket=/tmp/a.txt -p 这样该客户端进程和服务器进程就可以通过路径为 /tmp/a.txt 的 Unix 域套接字文件进行通信了。 1.6 服务器处理客户端请求 
1.6.1 连接管理 客户端进程可以采用我们上边介绍的 TCP/IP 、 命名管道或共享内存 、 Unix域套接字 这几种方式之一来与服务器进程建立连接，每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互，当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。这样就起到了不频繁创建和销毁线程的效果，从而节省开销。从这一点大家也能看出， MySQL 服务器会为每一个连接进来的客户端分配一个线程，但是线程分配的太多了会严重影响系统性能，所以我们也需要限制一下可以同时连接到服务器的客户端数量，至于怎么限制我们后边再说哈～ 在客户端程序发起连接的时候，需要携带主机信息、用户名、密码，服务器程序会对客户端程序提供的这些信息进行认证，如果认证失败，服务器程序会拒绝连接。另外，如果客户端程序和服务器程序不运行在一台计算机上，我们还可以采用使用了 SSL （安全套接字）的网络连接进行通信，来保证数据传输的安全性 1.6.2 解析与优化 1.6.2.1 查询缓存 MySQL 服务器程序会把刚刚处理过的查询请求和结果 缓存起来，如果下一次有一模一样的请求过来，直接从缓存中查找结果就好了，就不用再傻呵呵的去底层的表中查找了。这个查询缓存可以在不同客户端之间共享，也就是说如果客户端A 刚刚查询了一个语句，而客户端 B 之后发送了同样的查询请求，那么客户端B 的这次查询就可以直接使用查询缓存中的数据。 当然， MySQL 服务器并没有人聪明， 如果两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中 。另外， 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如mysql 、 information_schema 、 performance_schema 数据库中的表，那这个请求就不会被缓存 。以某些系统函数举例，可能同样的函数的两次调用会产生不一样的结果，比如函数 NOW ，每次调用都会产生最新的当前时间,如果在一个查询请求中调用了这个函数，那即使查询请求的文本信息都一样，那不同时间的两次查询也应该得到不同的结果，如果在第一次查询时就缓存了，那第二次查询的时候直接使用第一次查询的结果就是错误的！ 不过既然是缓存，那就有它缓存失效的时候。 MySQL 的缓存系统会监测涉及到的每张表，只要该表的结构或者数据被修改，如对该表使用了 INSERT 、 UPDATE 、 DELETE 、 TRUNCATE TABLE 、 ALTER TABLE 、 DROP TABLE 或 DROP DATABASE 语句，那使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除 ！ 小贴士： 虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。 从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL 8.0中删除 1.6.2.2 语法解析
1.6.2.3 查询优化 MySQL 的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接吧啦吧啦的一堆东西。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。我们可以使用 EXPLAIN 语句来查看某个语句的执行计划，关于查询优化这部分的详细内容我们后边会仔细唠叨，现在你只需要知道在 MySQL 服务器程序处理请求的过程中有这么一个步骤就好了 1.6.3 存储引擎 截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表， MySQL 服务器把数据的存储和提取操作都封装到了一个叫 存储引擎 的模块里。我们知道 表 是由一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是 存储引擎 负责的事情。为了实现不同的功能， MySQL 提供了各式各样的 存储引擎 ，不同 存储引擎 管理的表具体的存储结构可能不同，采用的存取算法也可能不同。存储引擎的功能就是接收上层传下来的指令，然后对表中的数据进行提取或写入操作。 为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。各种不同的存储引擎向上边的 MySQLserver 层提供统一的调用接口（也就是存储引擎 API ），包含了几十个底层函数，像 " 读取索引第一条内容 " 、 " 读取索引下一条内容" 、 " 插入记录 " 等等。所以在 MySQL server 完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的 API ，获取到数据后返回给客户端就好了。 1.7 常用存储引擎 MySQL 支持非常多种存储引擎，我这先列举一些： 存储引擎 描述 ARCHIVE 用于数据存档（行被插入后不能再修改） BLACKHOLE 丢弃写操作，读操作会返回空内容 CSV 在存储数据时，以逗号分隔各个数据项 FEDERATED 用来访问远程表 InnoDB 具备外键支持功能的事务存储引擎 MEMORY 置于内存的表 MERGE 用来管理多个 MyISAM 表构成的表集合 MyISAM 主要的非事务处理存储引擎 NDB MySQL 集群专用存储引擎 这么多我们怎么挑啊，哈哈，你多虑了，其实我们最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。其中 InnoDB 是 MySQL 默认的存储引擎 1.8 关于存储引擎的一些操作 1.8.1 查看当前服务器程序支持的存储引擎 我们可以用下边这个命令来查看当前服务器程序支持的存储引擎(不区分大小写)： SHOW ENGINES; 1.8.2 设置表的存储引擎 可以为不同的表设置不同的存储引擎 1.8.2.1 创建表时指定存储引擎 mysql> CREATE TABLE engine_demo_table( #这里输入回车能换行 -> i int -> ) ENGINE = MyISAM; # ;代表命令结束，点击回车执行命令 1.8.2.2 修改表的存储引擎 2 第 2 章 MySQL 的调控按钮 - 启动选项和系统变量 服务器允许同时连入的客户端的默认数量是 151 2.1 在命令行上使用选项 禁止各客户端使用 TCP/IP 网络进行通信: mysqld --skip-networking 改变表的默认存储引擎 mysqld --default-storage-engine=MyISAM show create table 表名 显示建表信息
2.1.1 选项的长形式和短形式
2.2 配置文件中使用选项 推荐使用配置文件的方式来设置启动选项 2.2.1 配置文件的路径 2.2.1.1 Windows 操作系统的配置文件
2.2.1.2 类 Unix 操作系统中的配置文件 
2.2.2 配置文件的内容
2.2.3 特定 MySQL 版本的专用选项组 2.2.4 配置文件的优先级 如果我们在多个配置文件中设置了相同的启动选项，那以最后一个配置 文件中的为准 2.2.5 同一个配置文件中多个组的优先级 将以最后一个出现的组中的启动选项为准 2.2.6 defaults-file 的使用 2.3 命令行和配置文件中启动选项的区别 如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启 动选项为准 2.4 系统变量 2.4.1 系统变量简介 2.4.2 查看系统变量 mysql> show variables like 'default_storage_engine';+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec) SHOW VARIABLES like 'max_connections'; 2.4.3 设置系统变量 2.4.3.1 通过启动选项设置 2.4.3.2 服务器程序运行过程中设置 对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修 改而无需停止并重启服务器 作用范围 分为这两种： GLOBAL ：全局变量，影响服务器的整体操作。 SESSION ：会话变量，影响某个客户端连接的操作。（注： SESSION 有个别名叫 LOCAL ） 通过启动选项设置的系统变量的作用范围都是 GLOBAL 的，也就是对所有客户端都有效的 如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是 SESSION 2.5 状态变量 它们的值只能由服务器程序自己来设置，我们程序员是 不能设置的 mysql> SHOW STATUS LIKE 'thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 0 | | Threads_connected | 1 | | Threads_created | 1 | | Threads_running | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) 3 第 3 章 乱码的前世今生 - 字符集和比较规则 3.1 字符集和比较规则简介 3.1.2 比较规则简介 同一种字符集可以有多种比较规则 3.1.3 一些重要的字符集 ASCII 字符集 ISO 8859-1 字符集：在 ASCII 字符集的基础上扩充(0x00-0xFF，F代表15，所以0xFF代表15*16+15=255，十进制范围0~255 ) GB2312 字符集 GBK 字符集：对 GB2312 字符集作了扩充 utf8 字符集：收录地球上能想到的所有字符，而且还在不断扩充，这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节 utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案 3.2.1 MySQL 中的 utf8 和 utf8mb4 常用的一些字符使用 1 ～ 3 个字节就可以表示了 utf8mb3 ：阉割过的 utf8 字符集，只使用 1 ～ 3 个字节表示字符。 utf8mb4 ：正宗的 utf8 字符集，使用 1 ～ 4 个字节表示字符。 在 MySQL 中 utf8 是 utf8mb3 的别名，所以之后在 MySQL 中提到 utf8 就意味着使 用 1~3 个字节来表示一个字符，如果大家有使用 4 字节编码一个字符的情况，比如存储一些 emoji 表情啥的，那请使用 utf8mb43.2.2 字符集的查看 mysql> SHOW CHARSET; 最后一列 Maxlen ，它代表该种字符集表示一个字符最多需要几个字节。为了让大家的印象更深刻，我把几个常用到的字符集的 Maxlen 列摘抄下来，大家务必记住：
3.2.3 比较规则的查看
mysql> SHOW COLLATION LIKE 'utf8\_%';
比如 utf8_general_ci 这个比较规则是以 ci 结尾的，说明不区分大小写 每种字符集对应若干种比较规则，每种字符集都有一种默认的比较规则 ， SHOW COLLATION 的返回结果中的Default 列的值为 YES 的就是该字符集的默认比较规则，比方说 utf8 字符集默认的比较规则就是utf8_general_ci 3.3 字符集和比较规则的应用 3.3.1 各级别的字符集和比较规则 MySQL 有 4 个级别的字符集和比较规则，分别是： 服务器级别 数据库级别 表级别 列级别 3.3.1.1 服务器级别 系统变量 描述 character_set_server 服务器级别的字符集 collation_server 服务器级别的比较规则 3.3.1.2 数据库级别 mysql> CREATE DATABASE charset_demo_db -> CHARACTER SET gb2312 -> COLLATE gb2312_chinese_ci; 想查看当前数据库使用的字符集和比较规则 系统变量 描述 character_set_database 当前数据库的字符集 collation_database 当前数据库的比较规则 mysql> CREATE DATABASE charset_demo_db -> CHARACTER SET gb2312 -> COLLATE gb2312_chinese_ci; Query OK, 1 row affected (0.01 sec) character_set_database 和 collation_database 这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则 数据库的创建语句中也可以不指定字符集和比较规则， 这样的话将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则 3.3.1.3 表级别 mysql> CREATE TABLE t( -> col VARCHAR(10) -> ) CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 0 rows affected (0.03 sec) 如果创建和修改表的语句中没有指明字符集和比较规则， 将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则 3.3.1.4 列级别 同一个表中的不同的列也可以有不同的字符集和比较规则 。 mysql> ALTER TABLE t MODIFY col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci; Query OK, 0 rows affected (0.04 sec) 列没有指明字符集和比较规则， 将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则 3.3.1.5 仅修改字符集或仅修改比较规则 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集 3.3.1.6 各级别字符集和比较规则小结 我们介绍的这 4 个级别字符集和比较规则的联系如下： 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则 3.3.2 客户端和服务器通信中的字符集 3.3.2.1 编码和解码使用的字符集不一致的后果 如果对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果 3.3.2.2 字符集转换的概念 3.3.2.3 MySQL 中字符集的转换 系统变量 描述 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_results 这三个系统变量设置成和客户端使用的字符集一致的情况，这样减少了很多无谓的字符集转换 SET NAMES 字符集名; 这一条语句产生的效果和我们执行这 3 条的效果是一样的： SET character_set_client = 字符集名; SET character_set_connection = 字符集名; SET character_set_results = 字符集名; [client] default-character-set=utf8 它起到的效果和执行一遍 SET NAMES utf8 是一样一样的 3.3.3 比较规则的应用 比较规则 的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中 4 第 4 章 从一条记录说起 -InnoDB 记录结构 4.2 InnoDB 页简介 将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位， InnoDB 中页的大小 一般为 16 KB 。也就是在一般情况下，一次最少从磁盘中读取 16KB 的内容到内存中，一次最少把内存中的 16KB内容刷新到磁盘中 4.3 InnoDB 行格式 Compact 、 Redundant 、 Dynamic 和 Compressed 行格式，随着时间的推移，他们可能会设计出更多的行格式 4.3.1 指定行格式的语法 4.3.2 COMPACT 行格式 变长字段长度列表 
把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长 字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放 如果该可变字段允许存储的最大字节数（ M×W ）超过 255 字节并且真实存储的字节数（ L ） 超过 127 字节，则使用 2 个字节，否则使用 1 个字节记录可变字段长度 变长字段长度列表中只存储值为 非 NULL 的列内容占用的长度，值为 NULL 的列的长度是不储存的 NULL 值列表如果表中没有允许存储 NULL 的列，则 NULL 值列表 也不存在了 记录头信息 4.3.2.2 记录的真实数据
实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，我们为了美观才写成了row_id、transaction_id和roll_pointer。 InnoDB 表对主键的生成策略：优先使用用户自定义主键作为主键，如果用户没有定义主键，则 选取一个 Unique 键作为主键，如果表中连 Unique 键都没有定义的话，则 InnoDB 会为表默认添加一个名为row_id 的隐藏列作为主键。所以我们从上表中可以看出： InnoDB 存储引擎会为每条记录都添加 transaction_id和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键以及 Unique 键的情况下才会添加该列） 4.3.2.3 CHAR(M) 列的存储格式 对于 CHAR(M) 类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字 段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表 4.3.3 Redundant 行格式 Redundant 行格式是MySQL5.0 之前用的一种行格式 
字段长度偏移列表实质上是存储每个列中的值占用的空间在记录的真实数据处结束的位置
4.3.3.1 CHAR(M) 列的存储格式 Redundant 不管该列使用的字符集是啥，只要是使用 CHAR(M) 类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和 M 的乘积 使用 Redundant 行格式的 CHAR(M) 类型的列是不会产生碎片的 4.3.4 行溢出数据 4.3.4.1 VARCHAR(M) 最多能存储的数据 除了 BLOB 或者 TEXT 类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节 这个 65535 个字节除了列本身的数据之外，还包括一些 其他的数据（ storage overhead ），比如说我们为了存储一个 VARCHAR(M) 类型的列，其实需要占用 3 部分存储空间： 真实数据 真实数据占用字节的长度 NULL 值标识，如果该列有 NOT NULL 属性则可以没有这部分存储空间 这都是在表中只有一个字段的情况下说的，一定要记住一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节4.3.4.2 记录中的数据太多产生的溢出
在 Compact 和 Reduntant 行格式中，对于占用存储空间非常大的列，在 记录的真实数据 处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后 记录的真实数据 处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页
4.3.4.3 行溢出的临界点
你不用关注这个临界点是什么，只要知道如果我们想一个行中存储了很大的数据时，可能发生 行溢出 的现象 4.3.5 Dynamic 和 Compressed 行格式 MySQL 版本是 5.7 ，它的默认行格式就是 Dynamic ，这俩行格式和 Compact 行格式挺像，只不过在处理 行溢出 数据时有点儿分歧，它们不会在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中 5 第 5 章 盛放记录的大盒子 -InnoDB 数据页结构 5.1 不同类型的页简介 5.2 数据页结构的快速浏览
5.3 记录在页中的存储 5.3 记录在页中的存储 5.3.1 记录头信息的秘密 
delete_mask 这个属性标记着当前记录是否被删除，占用 1 个二进制位，值为 0 的时候代表记录并没有被删除，为 1 的时候代表记录被删除掉了 min_rec_mask B+树的每层非叶子节点中的最小记录都会添加该标记 heap_no 这个属性表示当前记录在本 页 中的位置 record_type 这个属性表示当前记录的类型，一共有 4 种类型的记录， 0 表示普通记录， 1 表示 B+ 树非叶节点记录， 2 表示最小记录， 3 表示最大记录。从图中我们也可以看出来，我们自己插入的记录就是普通记录，它们的record_type 值都是 0 ，而最小记录和最大记录的 record_type 值分别为 2 和 3 next_record 这玩意儿非常重要，它表示 从当前记录的真实数据到下一条记录的真实数据的地址偏移量 下一条记录 指得并不是按照我们插入顺序的下一条记录，而 是按照主键值由小到大的顺序的下一条记录 。而且规定 Infimum 记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum 记录（也就是最大记录） 不论我们怎么对页中的记录做增删改操作， InnoDB 始终会维护一条记录的单链表，链表中的各个 节点是按照主键值由小到大的顺序连接起来的 5.4 Page Directory （页目录） 在一个数据页中查找指定主键值的记录的过程分为两步： 1. 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录 。 2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录 5.5 Page Header （页面头部） 一个数据页中存储的记录的状态信息 5.6 File Header （文件头部） 不同类型的页都会以 File Header 作为第一个组成部分，它描述了一些针对各种页都通用的一些信息 并不是所有类型的页都有上一个和下一个页的属性 ，不过我们本集中唠叨的 数据页 （也就是类型为 FIL_PAGE_INDEX 的页）是有这两个属性的，所以 所有的数据页其实是一个双链表 5.7 File Trailer 前 4 个字节代表页的校验和这个部分是和 File Header 中的校验和相对应的 后 4 个字节代表页面被最后修改时对应的日志序列位置（ LSN ） 6 第 6 章 快速查询的秘籍 -B+ 树索引 6.1 没有索引的查找6.1.1 在一个页中的查找 以主键为搜索条件 这个查找过程我们已经很熟悉了，可以在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。 以其他列作为搜索条件 对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的 页目录 ，所以我们无法通过二分法快速定位相应的 槽 。这种情况下只能从 最小记录 开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的 6.1.2 在很多页中查找 在没有索引的情况下，不论是根据主键列或者其他列的值进行查找， 由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录 。因为要遍历所有的数据页，所以这种方式显然是超级耗时的 6.2 索引 6.2.1 一个简单的索引方案 因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以 不得不 依次遍历所有的数据页 新分配的数据页编号可能并不是连续的，也就是说我们使用的这些页在存储空间里可能并不挨着 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值 。这个过程我们也可以称为 页分裂 6.2.2 InnoDB 中的索引方案 复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为 目录项记录 为这些存储 目录项记录 的页再生成一个更高级的目录 
随着表中记录的增加，这个目录的层级会继续增加
这个 B+ 树与上边介绍的聚簇索引有几处不同： 使用记录 c2 列的大小进行记录和页的排序，这包括三个方面的含义： 页内的记录是按照 c2 列的大小顺序排成一个单向链表。 各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一个双向链表。 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的 c2 列大小顺序排成一个双向链表。 B+ 树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键 这两个列的值。 目录项记录中不再是 主键+页号 的搭配，而变成了 c2列+页号 的搭配。 B+ 树的叶子节点中的记录只存储了 c2 和 c1 （也就是 主键 ）两个列，所以 我们必须再根据主键 值去聚簇索引中再查找一遍完整的用户记录 联合索引 
8.3 数据目录的结构 8.3.1 数据库在文件系统中的表示 每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹
每当我们新建一个数据库时， MySQL 会帮我们做这两件事儿：
1. 在 数据目录 下创建一个和数据库名同名的子目录（或者说是文件夹）。
2. 在该与数据库名同名的子目录下创建一个名为 db.opt 的文件，这个文件中包含了该数据库的各种属性，比方说该数据库的字符集和比较规则是个啥
查看有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.17 sec)
8.3.2 表在文件系统中的表示
1. 表结构的定义
2. 表中的数据 表结构：表名.frm， 这个后缀名为.frm 是以二进制格式存储的，我们直接打开会是乱码的 8.3.2.1 InnoDB 是如何存储表数据的 表空间 或者 文件空间 （英文名： table space 或者 file space ）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个 表空间 可以被划分为很多很多很多个 页 ，我们的表数据就存放在某个 表空间 下的某些页里 系统表空间（system tablespace） 可以把 系统表空间 对应的文件路径不配置到 数据目录 下，甚至可以配置到单独的磁盘分区上 MySQL5.5.7 到 MySQL5.6.6 之间的各个版本中，我们表中的数据都会被默认存储到这个系统表空间 独立表空间(file-per-table tablespace) 在 MySQL5.6.6 以及之后的版本中， InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间 test.frm test.ibd ( 存储test 表中的数据和索引) 其他类型的表空间 随着 MySQL 的发展，除了上述两种老牌表空间之外，现在还新提出了一些不同类型的表空间，比如通用表空间 （general tablespace ）、 undo 表空间（ undo tablespace ）、临时表空间（ temporary tablespace ） 8.3.2.2 MyISAM 是如何存储表数据的 MyISAM 并没有什么所谓的 表空间 一说， 表数据都存放到对应的数据库子目录下 test.frm test.MYD test.MYI test.MYD 代表表的数据文件，也就是我们插入的用户记录； test.MYI 代表表的索引文件 8.3.3 视图在文件系统中的表示 我们知道 MySQL 中的视图其实是虚拟的表，也就是某个查询语句的一个别名而已，所以在存储 视图 的时候是不需要存储真实的数据的， 只需要把它的结构存储起来就行了 。和 表 一样，描述视图结构的文件也会被存储到所属数据库对应的子目录下边，只会存储一个 视图名.frm 的文件 8.3.4 其他的文件 服务器进程文件 。 我们知道每运行一个 MySQL 服务器程序，都意味着启动一个进程。 MySQL 服务器会把自己的进程 ID 写入到一个文件中。 服务器日志文件 。 在服务器运行过程中，会产生各种各样的日志，比如常规的查询日志、错误日志、二进制日志、 redo 日志吧啦吧啦各种日志 默认/自动生成的SSL和RSA证书和密钥文件 8.4 文件系统对数据库的影响 数据库名称和表名称不得超过文件系统所允许的最大长度。 MySQL 会 把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值 的形式作为文件名 。 文件长度受文件系统最大长度限制 8.5 MySQL 系统数据库简介 mysql 这个数据库贼核心，它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。 information_schema 这个数据库保存着 MySQL 服务器维护的所有其他数据库的信息，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引吧啦吧啦。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为元数据。 performance_schema 这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，算是对 MySQL 服务器的一个性能监控。包括统计最近执行了哪些语句，在执行过程的每个阶段都花费了多长时间，内存的使用情况等等信息。 sys 这个数据库主要是通过视图的形式把 information_schema 和 performance_schema 结合起来，让程序员可以更方便的了解MySQL 服务器的一些性能信息 9 第 9 章 存放页面的大池子 -InnoDB 的表空间 9.1 回忆一些旧知识 9.1.1 页面类型 9.1.2 页面通用部分 9.2 独立表空间结构 9.2.1 区（ extent ）的概念 对于 16KB 的页来说，连续的 64 个页就是一个 区 ，也就是说一个区默认占用 1MB 空间大小。不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每256 个区被划分成一组 一个区就是在物理位置上连续的64个页 在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区 为单位分配 9.2.2 段（ segment ）的概念 段是一些零散的页面以及一些完整的区的集合 如果链表中相邻的两个页物理位置离得非常远，就是所谓的 随机I/O 。再一次强调， 磁盘的速度和内存的速度差了好几个数量级 ， 随机I/O 是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的 顺序I/O存放叶子节点的区的集合就算是一个 段 （ segment ），存放非叶子节点的区的集合也算是一个段 。也就是说一个索引会生成 2 个段，一个叶子节点段，一个非叶子节点段 碎片（ fragment ）区的概念:在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页 可以用于不同的目的，比如有些页用于段A ，有些页用于段 B ，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段 9.2.3 区的分类 状态名 含义 FREE 空闲的区 FREE_FRAG 有剩余空间的碎片区 FULL_FRAG 没有剩余空间的碎片区 FSEG 附属于某个段的区 处于 FREE 、 FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，算是直属于表空 间；而处于 FSEG 状态的区是附属于某个段的9.2.3.1 XDES Entry 链表当段中数据已经占满了 32 个零散的页后，就直接申请完整的区来插入数据了 CREATE TABLE t ( c1 INT NOT NULL AUTO_INCREMENT, c2 VARCHAR(100), c3 VARCHAR(100), PRIMARY KEY (c1), KEY idx_c2 (c2) )ENGINE=InnoDB; 这个表 t 共有两个索引，一个聚簇索引，一个二级索引 idx_c2 ，所以这个表共有 4 个段，每个段都会维护上述3 个链表，总共是 12 个链表，加上我们上边说过的直属于表空间的 3 个链表，整个独立表空间共需要维护15 个链表。所以段在数据量比较大时插入数据的话，会先获取 NOT_FULL 链表的头节点，直接把数据插入这个头节点对应的区中即可，如果该区的空间已经被用完，就把该节点移到 FULL 链表中 9.2.3.2 链表基节点 List Base Node 的结构，翻译成中文就是链表的基节点 9.2.3.3 链表小结 综上所述，表空间是由若干个区组成的，每个区都对应一个 XDES Entry 的结构，直属于表空间的区对应的 XDESEntry 结构可以分成 FREE 、 FREE_FRAG 和 FULL_FRAG 这 3 个链表；每个段可以附属若干个区，每个段中的区应的 XDES Entry 结构可以分成 FREE 、 NOT_FULL 和 FULL 这 3 个链表。每个链表都对应一个 List Base Node 的结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数 9.2.4 段的结构 INODE Entry 结构来记录一下段中的属性
9.2.5 各类型页面详细情况 9.2.5.1 FSP_HDR 类型 File Space Header 部分 XDES Entry 部分 9.2.5.2 XDES 类型 9.2.5.3 IBUF_BITMAP 类型 9.2.5.4 INODE 类型 9.2.6 Segment Header 结构的运用 9.2.7 真实表空间对应的文件大小 新建的表对应的 .ibd 文件只占用了 96K ，才 6个页面大小。一开始表空间占用的空间自然是很小，因为表里边都没有数据，不过 .ibd 文件是自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大 9.3 系统表空间 9.3.1 系统表空间的整体结构 9.3.1.1 InnoDB 数据字典 InnoDB 存储引擎特意定义了一些列的内部系统表（ internalsystem table）来记录这些这些 元数据 ： 表名 描述 SYS_TABLES 整个InnoDB 存储引擎中所有的表的信息 SYS_COLUMNS 整个InnoDB 存储引擎中所有的列的信息 SYS_INDEXES 整个InnoDB 存储引擎中所有的索引的信息 SYS_FIELDS 整个InnoDB 存储引擎中所有的索引对应的列的信息 SYS_FOREIGN 整个InnoDB 存储引擎中所有的外键的信息 SYS_FOREIGN_COLS 整个InnoDB 存储引擎中所有的外键对应列的信息 SYS_TABLESPACES 整个InnoDB 存储引擎中所有的表空间信息 SYS_DATAFILES 整个InnoDB 存储引擎中所有的表空间对应文件系统的文件路径信息 SYS_VIRTUAL 整个InnoDB 存储引擎中所有的虚拟生成列的信息 这些系统表也被称为 数据字典 ，它们都是以 B+ 树的形式保存在系统表空间的某些页面中 SYS_TABLES 表 SYS_COLUMNS 表 SYS_INDEXES 表 SYS_FIELDS 表 Data Dictionary Header 页面 information_schema 系统数据库 mysql> USE information_schema; Database changed mysql> SHOW TABLES LIKE 'innodb_sys%'; +--------------------------------------------+ | Tables_in_information_schema (innodb_sys%) | +--------------------------------------------+ | INNODB_SYS_DATAFILES | | INNODB_SYS_VIRTUAL | | INNODB_SYS_INDEXES | | INNODB_SYS_TABLES | | INNODB_SYS_FIELDS | | INNODB_SYS_TABLESPACES | | INNODB_SYS_FOREIGN_COLS | | INNODB_SYS_COLUMNS | | INNODB_SYS_FOREIGN | | INNODB_SYS_TABLESTATS | +--------------------------------------------+ 10 rows in set (0.00 sec) information_schema 数据库中的这些以 INNODB_SYS 开头的表并不是真正的内部系统表（内部系统表就是我们上边唠叨的以 SYS 开头的那些表），而是在存储引擎启动时读取这些以 SYS 开头的系统表，然后填充到这些以INNODB_SYS 开头的表中。以 INNODB_SYS 开头的表和以 SYS 开头的表中的字段并不完全一样 9.3.2 总结图 图太大省略 10 第 10 章 条条大路通罗马 - 单表访问方法 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; 插入 10000 行记录 10.1 访问方法（ access method ）的概念 MySQL 执行查询语句的方式称之为 访问方法 或者 访问类型 10.2 const(常数级) 有的时候我们可以通过主键列来定位一条记录，比方说这个查询： SELECT * FROM single_table WHERE id = 1438; (主键定位，只需要遍历聚簇索引) SELECT * FROM single_table WHERE key2 = 3841; (唯一二级索引) 这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才 有效，如果主键或者唯一二级索引是由多个列构成的话，索 引中的每一个列都需要与常数进行等值比较，这个const 访问方法才有效 （这是因为只有该索引中全部列都采用等值比较才可以定位 唯一的一条记录 ） 10.3 ref SELECT * FROM single_table WHERE key1 = 'abc';(普通二级索引，可能查出多条记录) SELECT * FROM single_table WHERE key1 IS NULL;（最多到达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 了，比方说这样： SELECT * FROM single_table WHERE key_part1 = 'god like' AND key_part2 > 'legendary';(非ref) 10.4 ref_or_null SELECT * FROM single_demo WHERE key1 = 'abc' OR key1 IS NULL;(普通二级索引+NULL) 10.5 range SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79); 种利用索引进行范围匹配的访问方法称之为： range 使用索引进行范围匹配中的 `索引` 可以是聚簇索引，也可以是二级索引。 10.6 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 10.7 all 全表扫描，对于 InnoDB 表来说也就是直接扫描聚簇索引 10.8 注意事项 10.8.1 重温 二级索引 + 回表 一般情况下 只能利用单个二级索引执行查询，比方说下边的这个查询： SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000; 
10.8.2 明确 range 访问方法使用的范围区间 LIKE操作符比较特殊，只有在匹配完整字符串或者匹配字符串前缀时才可以利用索引 IN操作符的效果和若干个等值匹配操作符`=`之间用`OR`连接起来是一样的，也就是说会产生多个单点区间 10.8.2.1 所有搜索条件都可以使用某个索引的情况 SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200; 10.8.2.2 有的搜索条件无法使用索引的情况 在为某个索引确定范围区间的时候只需要把用不到相关索引的搜索条件替换为 TRUE SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';(能使用索引) SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';(不能使用索引) <==> SELECT * FROM single_table WHERE key2 > 100 OR TRUE; 一个使用到索引的搜索条件和没有使用该索引的搜索条件使用 OR 连接起来后是无法使用该索引的 10.8.2.3 复杂搜索条件下找出范围匹配的区间 分析sql使用true替换 10.8.3 索引合并 一般情况下执行一个查询时最多只会用到单个二级索引 使用到多个索引来完成一次查询的执行方法称之为： index merge 10.8.3.1 Intersection 合并 适用于使用不同索引的搜索条件之间使用 AND 连接起来的情况 SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b'; 从 idx_key1 二级索引对应的 B+ 树中取出 key1 = 'a' 的相关记录。 从 idx_key3 二级索引对应的 B+ 树中取出 key3 = 'b' 的相关记录。 二级索引的记录都是由 索引列 + 主键 构成的，所以我们可以计算出这两个结果集中 id 值的交集。 按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户 虽然读取多个二级索引比读取一个二级索引消耗性能，但是读取二级索引的操作是 顺序I/O ，而回表操作是 随机I/O ，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为 回表 而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。 比方说下边这个查询可能用到 idx_key1 和 idx_key_part 这两个二级索引进行 Intersection 索引合并的操作： SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; 而下边这两个查询就不能进行 Intersection 索引合并： SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'; SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a'; 第一个查询是因为对 key1 进行了范围匹配，第二个查询是因为联合索引 idx_key_part 中的 key_part2 列并没有出现在搜索条件中，所以这两个查询不能进行 Intersection 索引合并。 情况二：主键列可以是范围匹配 比方说下边这个查询可能用到主键和 idx_key1 进行 Intersection 索引合并的操作： SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';(直接在二级索引判断id是否符合) 只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的(两个有序结果集取交集的时间复杂度更低) 按照有序的主键值去回表取记录有个专有名词儿，叫：Rowid Ordered Retrieval，简称ROR 10.8.3.2 Union 合并 适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况 情况一：二级索引列是等值匹配的情况 SELECT * FROM single_table WHERE key1 = 'a' OR ( key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'); 情况二：主键列可以是范围匹配 情况三：使用 Intersection 索引合并的搜索条件 SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b'); 10.8.3.3 Sort-Union 合并 SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z' 根据 key1 < 'a' 从 idx_key1 索引中获取的二级索引记录的主键值不是排好序的 把上述这种先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 Sort-Union 索引合并 10.8.3.4 索引合并注意事项 10.8.3.5 联合索引替代 Intersection 索引合并 11 第 11 章 两个表的亲密接触 - 连接的原理 11.1 连接简介 11.1.1 连接的本质 建立了 t1 、 t2 两个表，这两个表都有两个列，一个是 INT 类型的，一个是 CHAR(1) 类型的，填充好数据的两个表长这样： mysql> SELECT * FROM t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t2; +------+------+ | m2 | n2 | +------+------+ | 2 | b | | 3 | c | | 4 | d | +------+------+ 3 rows in set (0.00 sec) 连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积 。因为表 t1 中有 3 条记录，表 t2 中也有 3 条记录，所以这两个表连接之后的笛卡尔积就有 3×3=9 行记录 11.1.2 连接过程简介 SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd'; 假设t1是驱动表，t2是被驱动表 
11.1.3 内连接和外连接 内连接：驱动表中的记录在被驱动表中没有匹配的记录，不需要加入到结果集 外连接：驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集 WHERE 子句中的过滤条件 不论是内连接还是外连接， 凡是不符合 WHERE 子句中的过滤条件的记录都不会被加入最后的结果集 。 ON 子句中的过滤条件 对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配 ON 子句中的过滤条件的记录，那么该记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用 NULL 值填充。 需要注意的是，这个 ON 子句是专门为外连接驱动表中的记录在被驱动表找不到匹配记录时应不应该把该记录加入结果集这个场景下提出的 ，所以如果把 ON 子句放到内连接中， MySQL 会把它和 WHERE 子句一样对待，也就是说： 内连接中的 WHERE 子句和 ON 子句是等价的 。 一般情况下，我们都把只涉及单表的过滤条件放到 WHERE 子句中，把涉及两表的过滤条件都放到 ON 子句中，我们也一般把放到 ON 子句中的过滤条件也称之为 连接条件 11.1.3.1 左（外）连接的语法 SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件]; 左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表 对于左（外）连接和右（外）连接来说，必须使用 ON 子句来指出连接条件 11.1.3.2 右（外）连接的语法 11.1.3.3 内连接的语法 内连接和外连接的根本区别就是 在驱动表中的记录不符合 ON 子句中的连接条件时不会把该记录加入到最后的结果集 下边这几种内连接的写法都是等价的： SELECT * FROM t1 JOIN t2; SELECT * FROM t1 INNER JOIN t2; SELECT * FROM t1 CROSS JOIN t2; SELECT * FROM t1, t2; 由于在内连接中 ON 子句和 WHERE 子句是等价的，所以内连接中不要求强制写明 ON 子句 对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果 左外连接和右外连接的驱动表和被驱动表不能轻易互换 11.1.3.4 小结 11.2 连接的原理 11.2.1 嵌套循环连接（ Nested-Loop Join ） 驱动表只访问一次，但被驱动表却可能被多次访问，访问次数取决于对驱动表执行单表查询后的结果集中的记录条数 的连接执行方式称之为 嵌套循环连接 （ Nested-Loop Join ），这是最简单，也是最笨拙的一种连接查询算法。 11.2.2 使用索引加快连接速度 把在连接查询中对被驱动表使用主键值或者唯一二级索引列的值进行等值查找的查询执行方式称之为： eq_ref 假设 m2 和 n2 列上都存在索引的话，那么就需要从这两个里边儿挑一个代价更低的去执行对 t2 表的查询。当然，建立了索引不一定使用索引，只有在 二级索引 + 回表 的代价比全表扫描的代价更低时才会使用索引 建议在真实工作中最好不要使用 * 作为查询列表，最好把真实用到的列作为查询列表 11.2.3 基于块的嵌套循环连接（ Block Nested-Loop Join ） 扫描一个表的过程其实是先把这个表从磁盘上加载到内存中，然后从内存中比较匹配条件是否满足 内存里可能并不能完全存放的下表中所有的记录，所以在扫描表前边记录的时候后边的记录可能还在磁盘 上，等扫描到后边记录的时候可能内存不足，所以需要把前边的记录从内存中释放掉 采用 嵌套循环连接 算法的两表连接过程中，被驱动表可是要被访问好多次的 尽量减少访问被驱动表的次数 
最好的情况是 join buffer 足够大，能容纳驱动表结果集中的所有记录，这样只需要访问一次被驱动表就可以完成连接操作了，设计者把这种加入了 join buffer 的嵌套循环连接算法称之为 基于块的嵌套连接（Block Nested-Loop Join）算法 这个 join buffer 的大小是可以通过启动参数或者系统变量 join_buffer_size 进行配置，默认大小为 262144字节 （也就是 256KB ），最小可以设置为 128字节 。当然，对于优化被驱动表的查询来说， 最好是为被驱动表加上效率高的索引，如果实在不能使用索引，并且自己的机器的内存也比较大可以尝试调大 join_buffer_size 的值来对连接查询进行优化 只有查询列表中的列和过滤条件中的列才会被放到 join buffer 中，所以再次提醒我们，最好不要把 * 作为查询列表 12 第 12 章 谁最便宜就选谁 -MySQL 基于成本的优化 12.1 什么是成本 I/O 成本 我们的表经常使用的 MyISAM 、 InnoDB 存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。 CPU 成本 读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，设计 MySQL 的大叔规定读取一个页面花费的成本默认是 1.0 ，读取以及检测一条记录是否符合搜索条件的成本默认是 0.2 。 1.0 、 0.2 这些数字称之为 成本常数 12.2 单表查询的成本 12.2.1 准备工作 12.2.2 基于成本的优化步骤 1. 根据搜索条件，找出所有可能使用的索引 2. 计算全表扫描的代价 3. 计算使用不同索引执行查询的代价 4. 对比各种执行方案的代价，找出成本最低的那一个 12.2.2.1 1. 根据搜索条件，找出所有可能使用的索引 把一个查询中可能使用到的索引称之为 possible keys 12.2.2.2 2. 计算全表扫描的代价 聚簇索引占用的页面数 该表中的记录数 查看表的统计信息：mysql> SHOW TABLE STATUS LIKE 'single_table' 12.2.2.3 3. 计算使用不同索引执行查询的代价 MySQL 查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本 12.2.2.4 4. 对比各种执行方案的代价，找出成本最低的那一个 12.2.3 基于索引统计数据的成本计算 把这种通过直接访问索引对应的 B+ 树来计算某个范围区间对应的索引记录条数的方式称之为 index dive mysql> SHOW INDEX FROM single_table; 对于 InnoDB 存储引擎来说，使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值，并不是精确的 12.3 连接查询的成本 12.3.1 准备工作 12.3.2 Condition filtering 介绍 把对驱动表进行查询后得到的记录条数称之为驱动表的 扇出 （英文名： fanout ） 猜 的过程称之为 condition filtering 12.3.3 两表连接的成本分析 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本 尽量在被驱动表的连接列上建立索引，这样就可以使用 ref 访问方法来降低访问被驱动表的成本了，如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列 12.3.4 多表连接的成本分析 12.4 调节成本常数 mysql> SHOW TABLES FROM mysql LIKE '%cost%'; +--------------------------+ | Tables_in_mysql (%cost%) | +--------------------------+ | engine_cost | | server_cost | +--------------------------+ 2 rows in set (0.00 sec) 2.4.1 mysql.server_cost 表 MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个 临时表 ， 使用这个临时表来辅助完成查询 （比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎 12.4.2 mysql.engine_cost 表 可以更改成本常数，然后 FLUSH OPTIMIZER_COSTS; 13 第 13 章 兵马未动，粮草先行 -InnoDB 统计数据是如何收 集的 SHOW TABLE STATUS 可以看到关于表的统计数据，通过 SHOW INDEX 可以看到关于索引的统计数据，那么这些统计数据是怎么来的呢？它们是以什么方式收集的呢？本章将聚焦于 InnoDB 存储引擎的统计数据收集策略 13.1 两种不同的统计数据存储方式 永久性的统计数据 ，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。 非永久性的统计数据，这种统计数据存储在内存中 InnoDB 默认是 以表为单位来收集和存储统计数据的 13.2 基于磁盘的永久性统计数据 mysql> SHOW TABLES FROM mysql LIKE 'innodb%'; +---------------------------+ | Tables_in_mysql (innodb%) | +---------------------------+ | innodb_index_stats | | innodb_table_stats | +---------------------------+ 13.2.1.1 n_rows 统计项的收集 CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量; 13.2.1.2 clustered_index_size 和 sum_of_other_index_sizes 统计项的收集 以区为单位申请空间中有一些页可能并没有使用 13.2.2 innodb_index_stats innodb_index_stats 表的每条记录代表着一个索引的一个统计项 。 13.2.3 定期更新统计数据 开启 innodb_stats_auto_recalc 自动重新计算统计数据的过程是异步发生的 ，也就是即使表中变动的记录数超过了 10% ，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算 手动调用 ANALYZE TABLE 语句来更新统计信息 ANALYZE TABLE 语句会立即重新计算统计数据，也就是这个过程是同步的13.2.4 手动更新 innodb_table_stats 和 innodb_index_stats 表13.3 基于内存的非永久性统计数据会 生成经常变化的执行计划13.4 innodb_stats_method 的使用 最好不在索引列中存放 NULL 值才是正解 14 第 14 章 不好看就要多整容 -MySQL 基于规则的优化（内 含关于子查询优化二三事儿） 查询重写 14.1 条件化简 14.1.1 移除不必要的括号 14.1.2 常量传递（ constant_propagation ） 14.1.3 等值传递（ equality_propagation ） 14.1.4 移除没用的条件（ trivial_condition_removal ） 14.1.5 表达式计算 14.1.6 HAVING 子句和 WHERE 子句的合并 如果查询语句中没有出现诸如 SUM 、 MAX 等等的聚集函数以及 GROUP BY 子句，优化器就把 HAVING 子句和WHERE 子句合并起来 14.1.7 常量表检测 查询的表中一条记录没有，或者只有一条记录。 这一条不能用于使用InnoDB作为存储引擎的表，只能适用于使用Memory或者MyISAM存储引擎的表。 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表 14.2 外连接消除 在被驱动表的 WHERE 子句符合空值拒绝的条件后，外连接和内连接可以相互转换 。这种转换带来的好处就是 查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询 14.3 子查询优化 14.3.1 子查询语法 子查询结果集组成的表称之为 派生表 14.3.1.1 按返回的结果集区分子查询 标量子查询 那些只返回一个单一值的子查询称之为 标量子查询 ，比如这样： SELECT (SELECT m1 FROM t1 LIMIT 1); 或者这样： SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2); 这两个查询语句中的子查询都返回一个单一的值，也就是一个 标量 。这些标量子查询可以作为一个单一值或者表达式的一部分出现在查询语句的各个地方。 行子查询 顾名思义，就是返回一条记录的子查询，不过这条记录需要包含多个列（只包含一个列就成了标量子查询了）。比如这样： SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1); 其中的 (SELECT m2, n2 FROM t2 LIMIT 1) 就是一个行子查询，整条语句的含义就是要从 t1 表中找一些记录，这些记录的 m1 和 n2 列分别等于子查询结果中的 m2 和 n2 列。 列子查询 列子查询自然就是查询出一个列的数据喽，不过这个列的数据需要包含多条记录（只包含一条记录就成了标量子查询了）。比如这样： SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2); 其中的 (SELECT m2 FROM t2) 就是一个列子查询，表明查询出 t2 表的 m2 列的值作为外层查询 IN 语句的参数。 表子查询 顾名思义，就是子查询的结果既包含很多条记录，又包含很多个列，比如这样： SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2); 14.3.1.2 按与外层查询关系来区分子查询 不相关子查询 如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为 不相关子查询 。相关子查询 如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为 相关子查询 。比如： SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);14.3.1.3 子查询在布尔表达式中的使用 这里的子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录 IN 或者 NOT IN SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2); ANY/SOME （ ANY 和 SOME 是同义词） SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2); ALL SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2); EXISTS 子查询 SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2); 14.3.1.4 子查询语法注意事项 子查询必须用小括号扩起来。 在 SELECT 子句中的子查询必须是标量子查询 mysql> SELECT (SELECT m1, n1 FROM t1); ERROR 1241 (21000): Operand should contain 1 column(s) 在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量。 对于 [NOT] IN/ANY/SOME/ALL 子查询来说，子查询中不允许有 LIMIT 语句 mysql> SELECT * FROM t1 WHERE m1 IN (SELECT * FROM t2 LIMIT 2); ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SO ME subquery' 不允许在一条语句中增删改某个表的记录时同时还对该表进行子查询 。 比方说这样： mysql> DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1); ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause 14.3.2 子查询在 MySQL 中是怎么执行的 14.3.2.1 小白们眼中子查询的执行方式 14.3.2.2 标量子查询、行子查询的执行方式 SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1); 对于包含不相关的标量子查询或者行子查询的查询语句来说， MySQL 会分别独立的执行外层查询和子查询，就当作两个单表查询就好了 对于 相关 的标量子查询或者行子查询来说 
14.3.2.3 IN 子查询优化 物化表的提出 SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a'); 不直接将不相关子查询的结果集当作外层查询的参数，而是将该结果集写入一个临时表里 该临时表的列就是子查询结果集中的列。 写入临时表的记录会被去重。 建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立 哈希索引 如果子查询的结果集非常大，超过了系统变量 tmp_table_size 或者 max_heap_table_size ，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录， 索引类型也对应转变为 B+ 树索引 将子查询结果集中的记录保存到临时表的过程称之为 物化 物化表转连接 将子查询转换为semi-join 半连接的意思就是： 对于 s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 表的记录 由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询 不适用于semi-join的情况 如果 IN 子查询不满足转换为 semi-join 的条件，又不能转换为物化表或者转换为物化表的 成本太大，那么它就会被转换为 EXISTS 查询 14.3.2.4 ANY/ALL 子查询优化 14.3.2.5 [NOT] EXISTS 子查询的执行 14.3.2.6 对于派生表的优化 15 第 15 章 查询优化的百科全书 -Explain 详解（上） 列名 描述 id 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id select_type SELECT 关键字对应的那个查询的类型 table 表名 partitions 匹配的分区信息 type 针对单表的访问方法 possible_keys 可能用到的索引 key 实际上使用的索引 key_len 实际使用到的索引长度 ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 rows 预估的需要读取的记录条数 filtered 某个表经过搜索条件过滤后剩余记录条数的百分比 Extra 一些额外的信息 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; 15.1 执行计划输出中各列详解 15.1.1 table 不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以设计MySQL 的大叔规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名 
15.1.2 id 查询语句中每出现一个 SELECT 关键字，设计 MySQL 的大叔就会为它分配一个唯一的 id 值 对于连接查询来说，一个 SELECT 关键字后边的 FROM 子句中可以跟随多个表，所以在连接查询的执行计划中， 每个表都会对应一条记录，但是这些记录的id 值都是相同的 在连接查询的执行计划中，每个表都会对应一条记录，这些记录的 id 列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表 查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询 如果查询语句是一个子查询，但是执行计划中 s1 和 s2 表对应的记录的 id 值全部是 1 ，这就 表明了 查询优化器将子查询转换为了连接查询 UNION 子句是为了把 id 为 1 的查询和 id 为 2 的查询的结果集合并起来并去重，所以在内部创建了一个名为 
UNION ALL 就不需要为最终的结果集进行去重，没有那个 id 为 NULL 的记录 
15.1.3 select_type
UNCACHEABLE SUBQUERY 不常用，就不多唠叨了。 UNCACHEABLE UNION 不常用，就不多唠叨了 15.1.4 partitions 由于我们压根儿就没唠叨过分区是个啥，所以这个输出列我们也就不说了哈，一般情况下我们的查询语句的执行计划的 partitions 列的值都是 NULL 。 15.1.5 type 代表着 MySQL 对某个表的执行查询时的访问方法，所有存储引擎访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL system 当表中只有一条记录并且 该表使用的存储引擎的统计数据是精确的，比如 MyISAM 、 Memory const 当我们根据主键或者唯一二级索引列与常数进行等值匹配 eq_ref 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该 被驱动表 的访问方法就是eq_ref ref 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么该表的访问方法就 可能 是 ref fulltext 全文索引 ref_or_null 当对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时，那么对该表的访问方法就 可能 是ref_or_null index_merge 一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用 Intersection 、 Union 、 Sort-Union 这三种索引合并的方式来执行查询 mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a'; unique_subquery 类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery 
index_subquery index_subquery 与 unique_subquery 类似，只不过访问子查询中的表时使用的是普通的索引 range 如果使用索引获取某些 范围区间 的记录，那么就 可能 使用到 range 访问方法 index 当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是 index ALL 最熟悉的全表扫描 15.1.6 possible_keys 和 key possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些 possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引 15.1.7 key_len key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的： 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。 如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多 1 个字节。 对于变长字段来说，都会有 2 个字节的空间来存储该变长列的实际长度 执行计划的生成是在MySQL server 层中的功能，并不是针对具体某个存储引擎的功能，设计 MySQL 的大叔在执行计划中输出key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列 
18.2.3 free 链表的管理 我们最好在某个地方记录一下 Buffer Pool 中哪些缓存页是可用的 ，这个时候缓存页对应的 控制块 就派上大用场了，我们可以 把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中 ，这个链表也可以被称作 free链表 （或者说空闲链表） 
18.2.4 缓存页的哈希处理 18.2.5 flush 链表的管理 如果我们修改了 Buffer Pool 中某个缓存页的数据，那它就和磁盘上的页 不一致 了，这样的缓存页也被称为 脏页 （英文名： dirty page ） 凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫 flush链表 18.2.6 LRU 链表的管理 18.2.6.1 缓存不够的窘境 free链表 中已经没有多余的空闲缓存页的时候 只要我们使用到某个缓存页，就把该缓存页调整到 LRU链表 的头部，这样 LRU链表 尾部就是最近最少使用的缓存页喽, 当 Buffer Pool 中的空闲缓存页使用完时，到 LRU链表 的尾部找些缓存页淘汰就 OK 啦 18.2.6.3 划分区域的 LRU 链表 将 LRU 链表划分为 young 和 old 区域这两个部分，又添加了 innodb_old_blocks_time 这个系统变量，使得预读机制和全表扫描造成的缓存命中率降低的问题得到了遏制，因为用不到的预读页面以及全表扫描的页面都只会被放到 old 区域，而不影响 young 区域中的缓存页 18.2.6.4 更进一步优化 LRU 链表 如只有被访问的缓存页位于 young 区域的 1/4 的后边，才会被移动到 LRU链表 头部，这样就可以降低调整 LRU链表 的频率，从而提升性能 尽量高效的提高 Buffer Pool 的缓存命中率 18.2.7 其他的一些链表 为了更好的管理这个 Buffer Pool 引入了各种链表或其他数据结构 18.2.8 刷新脏页到磁盘 从 LRU链表 的冷数据中刷新一部分页面到磁盘。 从 flush链表 中刷新一部分页面到磁盘 18.2.9 多个 Buffer Pool实例 多个Buffer Pool，提高并发处理能力 18.2.10 innodb_buffer_pool_chunk_size 一个 Buffer Pool 实例其实是由若干个 chunk 组成的，一个 chunk 就代表一片连续的内存空间，里边儿包含了若干缓存页与其对应的控制块 innodb_buffer_pool_chunk_size 的值只能在服务器启动时指定，在服务器运行过程中是不可以修改 18.2.11 配置 Buffer Pool 时的注意事项 18.2.12 Buffer Pool中存储的其它信息 Buffer Pool 的缓存页除了用来缓存磁盘上的页面以外，还可以存储锁信息、自适应哈希索引等信息 18.2.13 查看Buffer Pool的状态信息 mysql> SHOW ENGINE INNODB STATUS 18.3 总结 
来源地址：https://blog.csdn.net/qq_35572013/article/details/128237147
