YXuan-Wang's Blog

随便写写记录一下~

0%

《MySQL是怎样运行的：从根儿上理解MySQL》笔记

发表于 更新于 分类于
MySQL入门书籍的读书笔记

[TOC]

0. 前言

作者联系方式：

微信： xiaohaizi4919

公众号：我们都是小青蛙

  • 该书只是介绍 MySQL 内核的一些核心概念的小白进阶书籍
  • 学习方法：不要跳着看！！！

1. 重新认识MySQL

  • MySQL也是基于客户端／服务器架构的。

  • 大概流程是：用户使用MySQL客户端来连接MySQL服务器，成功连接之后向服务器进程发送请求并得到回复的

    过程。

  • MySQL服务器处理客户端请求的流程如下图1所示

  • 为什么叫引擎呢？因为这个名字更拉风～以前叫做表处理器。为了管理方便，人们把 连接管理 、 查询缓存 、 语法解析 、 查询优化 这些并不涉及真实数据存储的功能划分为 MySQL server 的功能，把真实存取数据的功能划分为 存储引擎 的功能。（有没有很熟悉的操作？网络的分层结构也是如此，下层对上层提供服务，上层依赖下层）

  • MySQL支持非常多种存储引擎，最常用的就是 InnoDB 和 MyISAM ，有时会提一下 Memory 。默认的存储引擎为 InnoDB 。

  • 存储引擎是负责对表中的数据进行提取和写入工作的，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

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

  • 在程序启动时指定的设置项也称之为启动选项（startup options），这些选项控制着程序启动后的行为。
  • 所以在启动服务器程序的命令行后边指定启动选项的通用格式就是这样的：--启动选项1[=值1] --启动选项2[=值2] ... --启动选项n[=值n]，需要注意，选项名、=、选项值之间不可以有空白字符。
  • 每个MySQL程序都有许多不同的选项。大多数程序提供了一个--help选项，你可以查看该程序支持的全部启动选项以及它们的默认值。例如，使用 mysql --help 可以看到 mysql 程序支持的启动选项， mysqld_safe --help 可以看到 mysqld_safe 程序支持的启动选项。查看 mysqld 支持的启动选项有些特别，需要使用 mysqld --verbose --help 。
  • 启动选项 是在程序启动时我们程序员传递的一些参数，而 系统变量 是影响服务器程序运行行为的变量，它们之间的关系如下：
    • 大部分的系统变量都可以被当作启动选项传入。
    • 有些系统变量是在程序运行过程中自动生成的，是不可以当作启动选项来设置，比如auto_increment_offset 、 character_set_client 啥的。
    • 有些启动选项也不是系统变量，比如 defaults-file 。
  • 为了让我们更好的了解服务器程序的运行情况， MySQL 服务器程序中维护了好多关于程序运行状态的变量，它们被称为 状态变量 。由于 状态变量 是用来显示服务器程序运行状况的，所以它们的值只能由服务器程序自己来设置，我们程序员是不能设置的。与 系统变量 类似， 状态变量 也有 GLOBAL 和 SESSION 两个作用范围的，所以查看 状态变量 的语句可以这么写：SHOW [GLOBAL|SESSION] STATUS [LIKE 匹配的模式];

3. 字符集和比较规则

  • 在计算机中只能存储二进制数据，那该怎么存储字符串呢？当然是建立字符与二进制数据的映射关系了，建立这个关系最起码要搞清楚两件事儿：
    1. 你要把哪些字符映射成二进制数据？
      也就是界定清楚字符范围。
    2. 怎么映射？
      将一个字符映射成一个二进制数据的过程也叫做 编码 ，将一个二进制数据映射到一个字符的过程叫做 解码 。
      人们抽象出一个 字符集 的概念来描述某个字符范围的编码规则。
  • 字符集表示字符的范围以及编码规则后，怎么比较两个字符的大小呢？最容易想到的就是直接比较这两个字符对应的二进制编码的大小，这种简单的比较规则也可以被称为二进制比较规则，英文名为 binary collation 。二进制比较规则是简单，但有时候并不符合现实需求，比如在很多场合对于英文字符我们都是不区分大小写的，也就是说 ‘a’ 和 ‘A’ 是相等的，在这种场合下就不能简单粗暴的使用二进制比较规则了。对于某一种字符集来说，比较两个字符大小的规则可以制定出很多种，也就是说同一种字符集可以有多种比较规则。
  • 常用的重要字符集
    • ASCII 字符集：共收录128个字符，占1字节。
    • ISO 8859-1 字符集：共收录256个字符，是在 ASCII 字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个字符集也有一个别名 latin1 。
    • GB2312 字符集：收录汉字6763个，其他文字符号682个。兼容 ASCII 字符集，所以编码方式是：如果该字符在 ASCII 字符集中，则采用1字节编码。否则采用2字节编码。可以通过第一个字节是否属于0-127来判断1字节还是2字节编码。
    • GBK 字符集：在收录字符范围上对 GB2312 字符集作了扩充，编码方式上兼容 GB2312 。占2字节。
    • utf8 字符集：收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容 ASCII 字符集，采用变长编码方式，编码一个字符需要使用1～4个字节。
      其实准确的说，utf8只是Unicode字符集的一种编码方案，Unicode字符集可以采用utf8、utf16、utf32这几种编码方案，utf8使用1～4个字节编码一个字符，utf16使用2个或4个字节编码一个字符，utf32使用4个字节编码一个字符。
  • MySQL 支持很多种字符集，查看 MySQL 中查看支持的字符集和比较规则的语句如下：
    SHOW (CHARACTER SET|CHARSET) [LIKE 匹配的模式];
    SHOW COLLATION [LIKE 匹配的模式];
  • 常用的一些字符使用1～3个字节就可以表示了。而在 MySQL 中字符集表示一个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以设计MySQL 的大叔偷偷的定义了两个概念：
    • utf8mb3 ：阉割过的 utf8 字符集，只使用1～3个字节表示字符。在 MySQL 中 utf8 是 utf8mb3 的别名。
    • utf8mb4 ：正宗的 utf8 字符集，使用1～4个字节表示字符。
  • MySQL 有4个级别的字符集和比较规则，分别是：服务器级别、数据库级别、表级别、列级别。
    • 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则。
    • 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则。
    • 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则。
  • 如果对于同一个字符串编码和解码使用的字符集不一样，会产生意想不到的结果。就像我们经常看到的乱码一样。
  • 从发送请求到接收结果过程中发生的字符集转换：
    • 客户端使用操作系统的字符集编码请求字符串，向服务器发送的是经过编码的一个字节串。
    • 服务器将客户端发送来的字节串采用 character_set_client 代表的字符集进行解码，将解码后的字符串再按照 character_set_connection 代表的字符集进行编码。
    • 如果 character_set_connection 代表的字符集和具体操作的列使用的字符集一致，则直接进行相应操作，否则的话需要将请求中的字符串从 character_set_connection 代表的字符集转换为具体操作的列使用的字符集之后再进行操作。
    • 将从某个列获取到的字节串从该列使用的字符集转换为 character_set_results 代表的字符集后发送到客户端。
    • 客户端使用操作系统的字符集解析收到的结果集字节串。
    • 一般情况下要使用保持这三个变量的值和客户端使用的字符集相同。

4. InnoDB记录结构

  • MySQL 服务器上负责对表中数据的读取和写入工作的部分是 存储引擎 ，而服务器又支持不同类型的存储引擎，比如 InnoDB 、 MyISAM 、 Memory 啥的，不同的存储引擎一般是由不同的人为实现不同的特性而开发的，真实数据在不同存储引擎中存放的格式一般是不同的。由于 InnoDB 是 MySQL 默认的存储引擎，本节以 InnoDB 引擎的内部实现为例，了解了一个存储引擎的数据存储结构之后，其他的存储引擎都是依葫芦画瓢。

  • InnoDB页：
    InnoDB 采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小 一般为 16 KB。也就是在一般情况下，一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。

  • InnoDB行格式：我们平时是以记录为单位来向表中插入数据的，这些记录在磁盘上的存放方式也被称为 行格式 或者 记录格式 。
    设计 InnoDB 存储引擎的大叔们到现在为止设计了4种不同类型的 行格式 ，分别是 Compact 、 Redundant 、Dynamic 和 Compressed 行格式。

  • COMPACT行格式：

    • 一条完整的记录其实可以被分为 记录的额外信息 和 记录的真实数据 两大部分。记录的额外信息是服务器为了描述这条记录而不得不额外添加的一些信息。

  • Redundant行格式：

    • Redundant 行格式是 MySQL5.0 之前用的一种行格式，也就是说它已经非常老了，但是本着知识完整性的角度还是要提一下，大家乐呵乐呵的看就好。

    • 行溢出：MySQL中一个页的大小一般是 16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中。这种现象就叫行溢出。
      那发生 行溢出 的临界点是什么呢？也就是说在列存储多少字节的数据时就会发生 行溢出 ？
      MySQL 中规定一个页中至少存放两行记录，至于为什么这么规定我们之后再说，这个规定就会对行溢出的判断造成的影响。至少两条记录。

  • Dynamic和Compressed行格式：
    这两种行格式类似于 COMPACT行格式 ，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。
    另外， Compressed 行格式会采用压缩算法对页面进行压缩。
    MySQL5.7的默认行格式就是 Dynamic 。

5. InnoDB数据页结构

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

  • 官方称存放记录的页为索引（ INDEX ）页，而这些表中的记录就是我们日常口中所称的 数据 ，所以目前还是叫这种存放记录的页为 数据页。

  • 数据页结构图如下，从图中可以看出，一个 InnoDB 数据页的存储空间大致被划分成了 7 个部分，有的部分占用的字节数是确定的，有的部分占用的字节数是不确定的。

  • User Records 部分：
    我们自己存储的记录会按照我们指定的 行格式 存储到 User Records 部分。
    在一开始生成页的时候，其实并没有 User Records 这个部分，每当我们插入一条记录，都会从 Free Space 部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到 User Records 部分，当 Free Space 部分的空间全部被 User Records 部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了。
    记录在页中按照主键值由小到大顺序串联成一个单链表。

  • Page Directory（页目录）：

    1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
    2. 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的 n_owned 属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
    3. 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近 页 的尾部的地方，这个地方就是所谓的 Page Directory ，也就是 页目录 （此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为 槽 （英文名： Slot ），所以这个页面目录就是由 槽 组成的。
      设计 InnoDB 的大叔们对每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。
      因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的 二分法 来进行快速查找。在一个数据页中查找指定主键值的记录的过程分为两步：
    4. 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
    5. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

  • Page Header（页面头部）：
    占用固定的 56 个字节，专门针对 数据页 记录的各种状态信息。

  • File Header（文件头部）：
    占用固定的 38 个字节，针对各种类型的页都通用。也就是说不同类型的页都会以 File Header 作为第一个组成部分，它描述了一些针对各种页都通用的一些信息，比方说这个页的编号是多少，它的上一个页、下一个页是谁等等。

    • FIL_PAGE_SPACE_OR_CHKSUM：代表当前页面的校验和（checksum）。
    • FIL_PAGE_OFFSET：每一个 页 都有一个单独的页号，就跟你的身份证号码一样， InnoDB 通过页号来可以唯一定位一个 页 。
    • FIL_PAGE_TYPE：代表当前 页 的类型。其中，存放记录的数据页的类型其实是 FIL_PAGE_INDEX ，也就是所谓的 索引页 。
    • FIL_PAGE_PREV 和 FIL_PAGE_NEXT：分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了。注意：并不是所有类型的页都有上一个和下一个页的属性。

  • File Trailer：
    由 8 个字节组成，与 File Header 类似，都是所有类型的页通用的。其中，前4个字节代表页的校验和，后4个字节代表页面被最后修改时对应的日志序列位置（LSN），也是为了校验页的完整性的。

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

  • 没有索引的时候是怎么查找记录的：

    • 在一个页中的查找：
      以主键为搜索条件：在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。
      以其他列作为搜索条件：只能从 最小记录 开始依次遍历单链表中的每条记录，效率很低。
    • 在很多页中查找：
      在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。显然是超级耗时的。

  • 一个简单的索引方案：

    查找一些记录时为什么要遍历所有的数据页呢？
    因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以 不得不 依次遍历所有的数据页。

    想办法为快速定位记录所在的数据页而建立一个别的目录，建这个目录必须完成下边这些事儿：

    • 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。我们必须通过一些诸如记录移动的操作来始终保证这个状态一直成立，这个过程我们也可以称为 页分裂 。
    • 给所有的页建立一个目录项。由于数据页的编号可能并不是连续的，那么为了从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录（这个 目录 有一个别名，称为 索引 ），每个页对应一个目录项，每个目录项包括两个部分：页的用户记录中最小的主键值，我们用 key 来表示。页号，我们用 page_no 表示。

  • InnoDB中的索引方案：
    设计 InnoDB 的大叔们需要一种可以灵活管理所有 目录项 的方式。
    他们灵光乍现，忽然发现这些 目录项 其实长得跟我们的用户记录差不多，只不过 目录项 中的两个列是 主键 和 页号 而已，所以他们复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为 目录项记录 。那 InnoDB 怎么区分一条记录是普通的 用户记录 还是 目录项记录 呢？通过记录头信息里的record_type 属性。

    如果我们表中的数据非常多则会产生很多存储 目录项记录 的页，那我们怎么根据主键值快速定位一个存储 目录项记录 的页呢？
    为这些存储 目录项记录 的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据。

  • 不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到 B+ 树这个数据结构中了，所以我们也称这些数据页为 节点 。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为 叶子节点 或 叶节点 ，其余用来存放 目录项 的节点称为 非叶子节点 或者 内节点 ，其中 B+ 树最上边的那个节点也称为 根节点 。

  • InnoDB规定最下边的那层，也就是存放我们用户记录的那层为第 0 层，之后依次往上加。

  • 聚簇索引：InnoDB 存储引擎会自动的为我们创建聚簇索引。在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。注意：聚簇索引只能在搜索条件是主键值时才能发挥作用。

  • 二级索引（secondary index，也叫辅助索引）：针对以别的列作为搜索条件。
    我们可以多建几棵 B+ 树，不同的 B+ 树中的数据采用不同的排序规则。比方说我们用 c2 列的大小作为数据页、页中记录的排序规则，再建一棵 B+ 树，和聚簇索引的区别是：

    • B+ 树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键 这两个列的值。
    • 目录项记录中不再是 主键+页号 的搭配，而变成了 c2列+页号 的搭配。
    • 搜索过程时，多了一步：必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。这个过程也被称为 回表 。

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

    • 每条 目录项记录 都由 c2 、 c3 、 页号 这三个部分组成，各条记录先按照 c2 列的值进行排序，如果记录的 c2 列相同，则按照 c3 列的值进行排序。
    • B+ 树叶子节点处的用户记录由 c2 、 c3 和主键 c1 列组成。
    • 建立 联合索引 只会建立1棵 B+ 树，而为c2和c3列分别建立索引会分别以 c2 和 c3 列的大小为排序规则建立2棵 B+ 树。

  • MyISAM中的索引方案简单介绍：索引是索引、数据是数据。
    虽然也使用树形结构，但是却将索引和数据分开存储。
    将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为 数据文件 。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。
    使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为 索引文件 的另一个文件中。 MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是 主键值 + 行号 的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！这说明在 MyISAM 中都需要进行一次 回表 操作，意味着 MyISAM 中建立的索引相当于全部都是 二级索引 ！
    对其它的列分别建立索引或者建立联合索引，原理和 InnoDB 中的索引差不多，不过在叶子节点处存储的是 相应的列 + 行号 。这些索引也全部都是 二级索引 。

  • 使用 MySQL 语句去建立、删除索引：
    InnoDB 和 MyISAM 会自动为主键或者声明为 UNIQUE 的列去自动建立 B+ 树索引。
    创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列：
    CREATE TALBE 表名 (
    各种列的信息 ··· ,
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
    )

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

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

    举例：在创建 index_demo 表的时候就为 c2 和 c3 列添加一个 联合索引 ，可以这么写建表语句：
    CREATE TABLE index_demo(
    c1 INT,
    c2 INT,
    c3 CHAR(1),
    PRIMARY KEY(c1),
    INDEX idx_c2_c3 (c2, c3)
    );

    建议索引名以 idx_ 为前缀，后边跟着需要建立索引的列名，多个列名之间用下划线 _ 分隔开。

7. B+树索引的使用

  • 索引的代价：空间上，内节点比较耗费空间；时间上，进行增、删、改操作时，都需要去修改各个 B+ 树索引。所以，没事就别瞎建立索引。

  • B+树索引适用的条件：

    • 全值匹配：如果我们的搜索条件中的列和索引列一致的话，这种情况就称为全值匹配。

    • 匹配左边的列：如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。因为排序规则是前一个列相同，则按照下一个列进行排序。如果进行跳列查询，就用不到联合索引了。

    • 匹配列前缀：字符串的前n个字符，也就是前缀都是排好序的，所以对于字符串类型的索引列来说，我们只匹配它的前缀也是可以快速定位记录的。如果只给出后缀或者中间的某个字符串，那就用不上索引了，也就无法快速定位记录位置了。注意：查找后缀的搜索条件，可以更改数据存储的顺序（逆序存储），然后使用前缀匹配，来优化查找效率。

    • 匹配范围值：所有记录都是按照索引列的值从小到大的顺序排好序的，所以这极大的方便我们查找索引列的值在某个范围内的记录。注意：在使用联合索引进行范围查找的时候，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引。

    • 精确匹配某一列并范围匹配另外一列：对于同一个联合索引来说，虽然对多个列都进行范围查找时只能用到最左边那个索引列，但是如果左边的列是精确查找，则右边的列可以进行范围查找。

    • 用于排序：如果 ORDER BY 子句里使用到了我们的索引列，就有可能省去在内存或文件中排序的步骤。注意：对于 联合索引 有个问题需要注意， ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出。

      特别注意，不可以使用索引进行排序的几种情况：

      • ASC、DESC混用：对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是 ASC 规则排序，要么都是 DESC 规则排序。混用排序，不能高效使用索引，不如直接使用文件排序。
      • WHERE子句中出现非排序使用到的索引列。
      • 排序列包含非同一个索引的列：有时候用来排序的多个列不是一个索引里的，这种情况也不能使用索引进行排序。
      • 排序列使用了复杂的表达式：要想使用索引进行排序操作，必须保证索引列是以单独列的形式出现，而不是修饰过的形式。例如：使用 UPPER 函数修饰过的列就不是单独的列，这样就无法使用索引进行排序。

    • 用于分组：有时候我们为了方便统计表中的一些信息，会把表中的记录按照某些列进行分组。如果分组顺序和我们的 B+ 树中的索引列的顺序是一致的，而我们的 B+ 树索引又是按照索引列排好序的，这正好就可以直接使用 B+ 树索引进行分组。

  • 回表的代价：
    使用联合索引的查询有这么两个特点：

    • 会使用到两个 B+ 树索引，一个二级索引，一个聚簇索引。
    • 访问二级索引使用 顺序I/O ，访问聚簇索引使用 随机I/O 。

    需要回表的记录越多，使用二级索引的性能就越低，所以让某些查询宁愿使用全表扫描也不使用 二级索引+回表。

    那什么时候采用全表扫描的方式，什么时候使用采用 二级索引 + 回表 的方式去执行查询呢？
    这个就是传说中的查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用 二级索引 + 回表 的方式。当然优化器做的分析工作不仅仅是这么简单，但是大致上是个这个过程。

    覆盖索引：为了彻底告别 回表 操作带来的性能损耗，建议最好在查询列表里只包含索引列。把这种只需要用到索引的查询方式称为覆盖索引。例如：建立了联合索引 idx_name_birthday_phone_number ，而查询也只涉及索引列，不需要回表操作。
    SELECT name, birthday, phone_number FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
    当然，如果业务需要查询出索引以外的列，那还是以保证业务需求为重。但是我们很不鼓励用 * 号作为查询列表，最好把我们需要查询的列依次标明。

  • 如何挑选索引：

    只为用于搜索、排序或分组的列创建索引。

    考虑列的基数： 列的基数 指的是某一列中不重复数据的个数。最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

    索引列的类型尽量小： 类型大小 指的就是该类型表示的数据范围的大小。对于表的主键来说更加适用。数据类型越小，在查询时进行的比较操作越快（这是CPU层次的东东）。数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

    索引字符串值的前缀：索引列的字符串前缀其实也是排好序的，所以索引的设计者提出了个方案，只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值，再对比就好了。只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候。
    如果使用了索引列前缀，比方说前边只把 name 列的前10个字符放到了二级索引中，那么 SELECT * FROM person_info ORDER BY name LIMIT 10; 查询就只能使用文件排序了。

    让索引列在比较表达式中单独出现：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式，或者函数调用形式出 现的话，是用不到索引的。

    主键插入顺序：如果主键不按顺序插入，产生的页面分裂和记录移位就意味着：性能损耗！所以，建议让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入。

    避免冗余和重复索引。

8. 数据的家-MySQL的数据目录

  • 数据库和文件系统的关系：像 InnoDB 、 MyISAM 这样的存储引擎都是把表存储在文件系统（操作系统用来管理磁盘）上的。

  • MySQL数据目录：MySQL服务器程序在启动时会到文件系统的某个目录下加载一些文件，之后在运行过程中产生的数据也都会存储到这个目录下的某些文件中，这个目录就称为 数据目录 。

    数据目录和安装目录的区别：一定要区分开两者区别。安装目录是安装MySQL的位置。其实，数据目录对应着一个系统变量 datadir ，使用 SHOW VARIABLES LIKE 'datadir'; 查询数据目录即可。

  • 数据目录的结构：

    • 数据库在文件系统中的表示：
      使用 CREATE DATABASE 数据库名 语句创建一个数据库的时候，在文件系统上实际发生了什么呢？其实，每个数据库都对应数据目录下的一个子目录，或者说对应一个文件夹，我们每当我们新建一个数据库时， MySQL 会帮我们做这两件事儿：

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

    • 表在文件系统中的表示：

      我们的数据其实都是以记录的形式插入到表中的，每个表的信息其实可以分为两种：

      1. 表结构的定义：包含该表的名称、表里边有多少列、每个列的数据类型是啥、有啥约束条件和索引、用的是啥字符集和比较规则吧啦吧啦的各种信息。 InnoDB 和 MyISAM 这两种存储引擎都在数据库子目录下创建了一个专门用于描述表结构的文件，文件名是这样 表名.frm ，以二进制格式存储。

      2. 表中的数据：不同的存储引擎就存在不同的存储表中数据方式，以InnoDB为例。
        设计 InnoDB 的大叔们提出了一个 表空间 或者 文件空间 （英文名： table space 或者 file space ）的概念，这个表空间是一个抽象的概念，它可以对应文件系统上一个或多个真实文件（不同表空间对应的文件数量可能不同）。每一个 表空间 可以被划分为很多很多很多个 页 ，我们的表数据就存放在某个 表空间 下的某些页里。设计 InnoDB 的大叔将表空间划分为几种不同的类型：

        • 系统表空间（system tablespace）：
          这个所谓的 系统表空间 可以对应文件系统上一个或多个实际的文件，默认情况下， InnoDB 会在 数据目录 下创建一个名为 ibdata1 、大小为 12M 的文件，这个文件就是对应的 系统表空间 在文件系统上的表示。并且这个文件是所谓的自扩展文件 ，也就是当不够用的时候它会自己增加文件大小。需要注意的是，在一个MySQL服务器中，系统表空间只有一份。从MySQL5.5.7到MySQL5.6.6之间的各个版本中，我们表中的数据都会被默认存储到这个 系统表空间。

        • 独立表空间(file-per-table tablespace)：
          在MySQL5.6.6以及之后的版本中， InnoDB 并不会默认的把各个表的数据存储到系统表空间中，而是为每一个表建立一个独立表空间，也就是说我们创建了多少个表，就有多少个独立表空间。使用 独立表空间 来存储表数据的话，会在该表所属数据库对应的子目录下创建一个表示该 独立表空间 的文件，文件名和表名相同，只不过添加了一个 .ibd 的扩展名而已，所以完整的文件名称长这样： 表名.ibd 。
          比方说假如我们使用了 独立表空间 去存储 xiaohaizi 数据库下的 test 表的话，那么在该表所在数据库对应的xiaohaizi 目录下会为 test 表创建这两个文件：test.frm、test.ibd。其中 test.ibd 文件就用来存储 test 表中的数据和索引。
          我们也可以自己指定使用 系统表空间 还是 独立表空间 来存储数据，这个功能由启动参数 innodb_file_per_table 控制，值为 0 时，代表使用系统表空间，值为 1 时，代表使用独立表空间。

        • 其他类型的表空间：通用表空间（general tablespace）、undo表空间（undo tablespace）、临时表空间（temporary tablespace）

        和 InnoDB 不同的是， MyISAM 并没有什么所谓的 表空间 一说，表数据都存放到对应的数据库子目录下。
        假如 test 表使用 MyISAM 存储引擎的话，那么在它所在数据库对应的 xiaohaizi 目录下会为 test 表创建这三个文件：test.frm、test.MYD、test.MYI。其中 test.MYD 代表表的数据文件，也就是我们插入的用户记录； test.MYI 代表表的索引文件，我们为该表创建的索引都会放到这个文件中。

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

    • 其他的文件： 数据目录下还包括为了更好运行程序的一些额外文件，主要包括服务器进程文件、服务器日志文件、默认/自动生成的SSL和RSA证书和密钥文件。

  • 文件系统对数据库的影响：
    因为 MySQL 的数据都是存在文件系统中的，就不得不受到文件系统的一些制约，这在数据库和表的命名、表的大小和性能方面体现的比较明显，比如下边这些方面：

    • 数据库名称和表名称不得超过文件系统所允许的最大长度。
    • 特殊字符的问题：为了避免因为数据库名和表名出现某些特殊字符而造成文件系统不支持的情况， MySQL 会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件名里都映射成 @+编码值 的形式作为文件名。
    • 文件长度受文件系统最大长度限制。

  • MySQL系统数据库简介：

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

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

    performance_schema：主要保存MySQL服务器运行过程中的一些状态信息，算是对MySQL服务器的一个性能监控。

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

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

  • 任何类型的页都会包含这两个部分：
    File Header ：记录页面的一些通用信息。
    File Trailer ：校验页是否完整，保证从内存到磁盘刷新时内容的一致性。

  • 独立表空间结构：

    表空间中的页实在是太多了，为了更好的管理这些页面，设计 InnoDB 的大叔们提出了 区 （英文名： extent ）的概念。对于16KB的页来说，连续的64个页就是一个 区 ，也就是说一个区默认占用1MB空间大小。不论是系统表空间还是独立表空间，都可以看成是由若干个区组成的，每256个区被划分成一组。如下图：

    第一个组最开始的3个页面的类型是固定的，也就是说 extent 0 这个区最开始的3个页面的类型是固定的，分别是：

    • FSP_HDR 类型：这个类型的页面是用来登记整个表空间的一些整体属性以及本组所有的256个区的属性。整个表空间只有一个 FSP_HDR 类型的页面。
    • IBUF_BITMAP 类型：这个类型的页面是存储本组所有的区的所有页面关于 INSERT BUFFER 的信息。
    • INODE 类型：这个类型的页面存储了许多称为 INODE 的数据结构。

    其余各组最开始的2个页面的类型是固定的，分别是：

    • XDES 类型：全称是 extent descriptor ，用来登记本组256个区的属性。上边介绍的 FSP_HDR 类型的页面其实和 XDES 类型的页面的作用类似，只不过 FSP_HDR 类型的页面还会额外存储一些表空间的属性。
    • IBUF_BITMAP 类型：同上。

    如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以，设计 InnoDB 的大叔们对 B+ 树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的 区 ，非叶子节点也有自己独有的 区 。存放叶子节点的区的集合就算是一个 段 （ segment ），存放非叶子节点的区的集合也算是一个 段 。也就是说一个索引会生成2个段，一个叶子节点段，一个非叶子节点段。

    碎片（fragment）区直属于表空间，并不属于任何一个段。所以此后为某个段分配存储空间的策略是这样的：

    • 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
    • 当某个段已经占用了32个碎片区页面之后，就会以完整的区为单位来分配存储空间。

    所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

    区的分类：（这4种类型的区也可以被称为区的4种状态（ State ））

    • 空闲的区（FREE）：现在还没有用到这个区中的任何页面。
    • 有剩余空间的碎片区（FREE_FRAG）：表示碎片区中还有可用的页面。
    • 没有剩余空间的碎片区（FULL_FRAG）：表示碎片区中的所有页面都被使用，没有空闲页面。
    • 附属于某个段的区（FSEG）。每一个索引都可以分为叶子节点段和非叶子节点段，除此之外InnoDB还会另外定义一些特殊作用的段，在这些段中的数据量很大时将使用区来作为基本的分配单位。

    为了方便管理这些区，设计 InnoDB 的大叔设计了一个称为 XDES Entry 的结构（全称就是Extent Descriptor Entry），每一个区都对应着一个 XDES Entry 结构，这个结构记录了对应的区的一些属性。

    把事情搞这么麻烦的初心仅仅是想提高向表插入数据的效率又不至于数据量少的表浪费空间。

    XDES Entry链表：
    表空间是由若干个区组成的，每个区都对应一个 XDES Entry 的结构，直属于表空间的区对应的 XDES Entry 结构可以分成 FREE 、 FREE_FRAG 和 FULL_FRAG 这3个链表；每个段可以附属若干个区，每个段中的区对应的 XDES Entry 结构可以分成 FREE 、 NOT_FULL 和 FULL 这3个链表。每个链表都对应一个 List Base Node 的结构，这个结构里记录了链表的头、尾节点的位置以及该链表中包含的节点数。正是因为这些链表的存在，管理这些区才变成了一件so easy的事情。

    段的结构：

    和区一样，设计 InnoDB 的大叔为每个段都定义了一个 INODE Entry 结构来记录一下段中的属性。

  • 系统表空间：

    系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，所以会比独立表空间多出一些记录这些信息的页面。因为这个系统表空间最牛逼，相当于是表空间之首，所以它的 表空间 ID （Space ID）是 0 。

    Data Dictionary Header 数据字典头部信息

    InnoDB数据字典：

    为了更好的管理我们这些用户数据而不得已引入的一些额外数据，这些数据也称为 元数据 。InnoDB存储引擎特意定义了一些列的内部系统表（internal system table）来记录这些这些 元数据 。

    这些系统表也被称为 数据字典 ，它们都是以 B+ 树的形式保存在系统表空间的某些页面中，其中 SYS_TABLES 、 SYS_COLUMNS 、 SYS_INDEXES 、 SYS_FIELDS 这四个表尤其重要，称之为基本系统表（basic system tables）

    Data Dictionary Header页面
    只要有了上述4个基本系统表，也就意味着可以获取其他系统表以及用户定义的表的所有元数据。

  • 系统表总结图：

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

  • 先来瞅瞅 MySQL 怎么执行单表查询（就是 FROM 子句后边只有一个表，最简单的那种查询～）

  • 访问方法（access method）：

    对于单个表的查询来说，设计MySQL的大叔把查询的执行方式大致分为下边两种：

    • 使用全表扫描进行查询
    • 使用索引进行查询

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

  • const：
    设计 MySQL 的大叔认为通过主键或者唯一二级索引列与常数的等值比较来定位一条记录是像坐火箭一样快的，所以他们把这种通过主键或者唯一二级索引列来定位一条记录的访问方法定义为： const ，意思是常数级别的，代价是可以忽略不计的。

    不过这种 const 访问方法只能在主键列或者唯一二级索引列和一个常数进行等值比较时才有效，如果主键或者唯一二级索引是由多个列构成的话，索引中的每一个列都需要与常数进行等值比较，这个 const 访问方法才有效（这是因为只有该索引中全部列都采用等值比较才可以定位唯一的一条记录）。

  • ref：

    设计 MySQL 的大叔就把这种搜索条件为二级索引列与常数等值比较，采用二级索引来执行查询的访问方法称为： ref 。

  • ref_or_null：

    想找出某个二级索引列的值等于某个常数的记录，还想把该列的值为 NULL 的记录也找出来。

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

  • range：

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

  • index：

    设计 MySQL 的大叔就把这种采用遍历二级索引记录的执行方式称之为： index 。

  • all：

    设计 MySQL 的大叔把这种使用全表扫描执行查询的方式称之为： all 。

  • 注意事项：

    重温 二级索引 + 回表：一般情况下只能利用单个二级索引执行查询。因为二级索引的节点中的记录只包含索引列和主键，所以在步骤1中使用 idx_key1 索引进行查询时只会用到与 key1 列有关的搜索条件，其余条件，比如 key2 > 1000 这个条件在步骤1中是用不到的，只有在步骤2完成回表操作后才能继续针对完整的用户记录中继续过滤。

    明确range访问方法使用的范围区间：

    索引合并：设计 MySQL 的大叔把这种使用到多个索引来完成一次查询的执行方法称之为： index merge ，具体的索引合并算法有下边三种。

    • Intersection合并：交集。
      MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并：
      情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
      情况二：主键列可以是范围匹配。

    • Union合并：并集。
      MySQL 在某些特定的情况下才可能会使用到 Union 索引合并：
      情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
      情况二：主键列可以是范围匹配。
      情况三：使用 Intersection 索引合并的搜索条件。

    • Sort-Union合并：
      先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 Sort-Union 索引合并，很显然，这种 Sort-Union 索引合并比单纯的 Union 索引合并多了一步对二级索引记录的主键值排序的过程。

11. 连接Join的原理

  • 连接的本质：
    连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
    连接查询的结果集中包含一个表中的每一条记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积 。

  • 连接过程简介：
    如果没有任何限制条件的话，这些表连接起来产生的 笛卡尔积 可能是非常巨大的。所以在连接的时候过滤掉特定记录组合是有必要的，在连接查询中的过滤条件可以分成两种：涉及单表的条件、涉及两表的条件。
    连接查询的大致执行过程如下：

    • 首先确定第一个需要查询的表，这个表称之为 驱动表 。怎样在单表中执行查询语句我们在前一章都唠叨过了，只需要选取代价最小的那种访问方法去执行单表查询语句就好了。
    • 针对上一步骤中从驱动表产生的结果集中的每一条记录，分别需要到 t2 表中查找匹配的记录，所谓 匹配的记录 ，指的是符合过滤条件的记录。因为是根据 t1 表中的记录去找 t2 表中的记录，所以 t2 表也可以被称之为 被驱动表 。
      从上边两个步骤可以看出来，在两表连接查询中，驱动表只需要访问一次，被驱动表可能被访问多次。

  • 内连接和外连接：
    对于 内连接 的两个表，驱动表中的记录在被驱动表中找不到匹配的记录，该记录不会加入到最后的结果集，我们上边提到的连接都是所谓的 内连接 。
    对于 外连接 的两个表，驱动表中的记录即使在被驱动表中没有匹配的记录，也仍然需要加入到结果集。
    对于内连接来说，驱动表和被驱动表是可以互换的，并不会影响最后的查询结果。左外连接和右外连接的驱动表和被驱动表不能轻易互换。

  • 连接的原理：
    真正的重点是MySQL采用了什么样的算法来进行表与表之间的连接，了解了这个之后，大家才能明白为啥有的连接查询运行的快如闪电，有的却慢如蜗牛。

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

  • 使用索引加快连接速度：
    在 嵌套循环连接 的 步骤2 中可能需要访问多次被驱动表，如果访问被驱动表的方式都是全表扫描的话，那得要扫描好多次！但是别忘了，查询 t2 表其实就相当于一次单表扫描，我们可以利用索引来加快查询速度哦。

  • 基于块的嵌套循环连接（Block Nested-Loop Join）：
    现实生活中的表可不像 t1 、 t2 这种只有3条记录，成千上万条记录都是少的，几百万、几千万甚至几亿条记录的表到处都是。内存里可能并不能完全存放的下表中所有的记录，所以需要尽量减少访问被驱动表的次数。
    设计 MySQL 的大叔提出了一个join buffer 的概念， 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. n

-

13. n

参考资料

[1]:https://juejin.cn/book/6844733769996304392 “掘金小册”