数据库之舞：MySQL 数据结构的美妙之旅

前言

前段时间看了《MySQL是怎样运行的》这本书，书中介绍了MySQL数据结构，从数据结构角度去谈MySQL的特性。写这篇博客是做个总结，以下内容需要读者对MySQL有基础的认识和掌握，同时对于数据结构有一定的了解。所以这边先会对，数据结构和二分法，做些基本的介绍。然后再讲MySQL数据，是通过哪种数据结构存储的。如果掌握了其中的数据结构，那么当中的原理就掌握了。一些特性，索引，优化。就自然而然的理解了。变得非常简单。
套用《道德经》里的话就是道

技术分为两种，具体的做事方法是术，做事的原理和原则是道。

二分法

因为后面要介绍的MySQL数据结构，很多地方要用到二分法，这边单独拿出来讲一遍。

小时候，玩过的猜数字游戏
1-100范围内随机选择一个数，猜这个数字是多少。如果你猜的数比目标大，它会告诉你大了；比目标小，它会告诉你小了；等于目标告诉你答对了。如何在最少次数获得答案

那时候，我们就知道猜数字。要对半猜，这样猜的速度最快。以上如果方法，学名就叫做二分法。
我们看下官方介绍:

二分法的基本思想是，将数组或列表分成两部分，判断目标元素在哪一部分中，然后继续在该部分中进行查找，直到找到目标元素或确定目标元素不存在为止。

二分法适用于有序数组或有序列表中，时间复杂度O(log n)

    public static int search(int[] a, int target) {
        int lo = 0;
        int hi = a.length - 1;
        while (lo <= hi) {
            System.out.println(String.format("lo=%s,hi=%s", lo, hi));
            int mid = lo + (hi - lo) / 2;
            if (a[mid] > target) {
                hi = mid - 1;
            } else if (a[mid] < target) {
                lo = mid + 1;
            } else {
                return mid;
            }
        }
        return -1;
    }

以上是最基础的，还有两种变形：owerBound（下界）和upperBound（上界）

    //   Lower Bound（下界）： Lower Bound 是指在已排序数组中，比目标值大的第一个元素的位置索引。也可以说是第一个大于等于目标值的元素的位置索引。
//   如果数组中存在多个相同目标值，则返回最左边的位置
    public static int lowerBound(int[] a, int target) {
        int lo = 0;
        int hi = a.length;
        while (lo < hi) {
            int mid = lo + (hi - lo) / 2;
            if (a[mid] < target) {
                lo = mid + 1;
            } else {
                hi = mid;
            }
        }
        return lo;
    }

    //    Upper Bound（上界）： Upper Bound 是指在已排序数组中，比目标值大的第一个元素的位置索引。也可以说是第一个大于目标值的元素的位置索引。
//    如果数组中存在多个相同目标值，则返回最左边的大于目标值的位置。
    public static int upperBound(int[] a, int target) {
        int lo = 0;
        int hi = a.length;
        while (lo < hi) {
            int mid = lo + (hi - lo) / 2;
            if (a[mid] <= target) {
                lo = mid + 1;
            } else {
                hi = mid;
            }
        }
        return lo;
    }

以上变体思想，再接下来会使用到，大家可以先品味一下。如果不是很理解，那么可以将代码执行下。

数据结构	优点	缺点	特性	访问时间复杂度	插入时间复杂度
数组	- 快速随机访问	- 固定大小，不灵活	- 连续存储	O(1)	O(n)
链表	- 动态大小	- 随机访问慢	- 灵活插入、删除	O(n)	O(1)
栈	- 后进先出	- 仅允许在栈顶插入和删除	- 递归、表达式求值	O(1)	O(1)
队列	- 先进先出	- 随机访问慢	- 广度优先搜索、任务调度	O(n)	O(1)
树	- 分层结构	- 平衡维护复杂	- 二叉树、二叉搜索树	O(log n)	O(log n)
堆	- 高效的插入、删除	- 不支持随机访问	- 最大堆、最小堆	O(log n)	O(log n)
哈希表(散列表)	- 快速的插入、删除、查找	- 内存消耗相对较大	- 高效的散列函数、冲突处理	O(1)	O(1)
二叉搜索树（Binary Search Tree）	- 查找、插入和删除操作的时间复杂度为O(log n)	- 会退化成O(n)	- 左子树中的所有节点都小于根节点，右子树中的所有节点都大于根节点	O(m)	O(m)
红黑树（Red-Black Tree）	- 自平衡，保持搜索效率	- 相对复杂的实现	- 高效的搜索、插入、删除	O(log n)	O(log n)
B树（B-Tree)	- 高度平均性能，适用于大数据集	- 不适用于小数据集	- 多叉搜索树	O(log n)	O(log n)
B+树（B+ Tree）	- 顺序访问性能好	- 节点拆分和合并操作相对复杂	- 子节点形成了一个有序链表	O(log n)	O(log n)

B树和B+树，大家可能会比较陌生，接下来的内容又比较重要。这边做个简单介绍。

特性	B树	B+树
结构	所有节点都存储数据和关键字	非叶子节点只存储关键字，叶子节点存储数据
查找效率	略低，因为非叶子节点不包含全部关键字	高，由于非叶子节点不包含全部关键字
范围查询	低效，需要在非叶子节点和叶子节点之间遍历	高效，叶子节点形成有序链表，支持范围查询
插入和删除操作	相对复杂，可能导致树的失衡	相对稳定，树的平衡性较好
节点利用率	低，因为非叶子节点存储关键字和数据	高，由于非叶子节点只存储关键字
适用场景	适用于内存和外存储	主要用于外存储，如数据库索引、文件系统
节点拆分和合并操作	相对频繁	相对较少
删除记录时性能	略高	略低
数据检索和迭代效率	相对低效	高效，有序链表可支持顺序遍历

图片来源于网络

对于B树和B+树介绍，可以参考平衡二叉树、B树、B+树、B*树 理解其中一种你就都明白了这篇博文

MySQL简介

MySQL客户端请求服务器的过程：
连接管理：
	建立连接： 客户端通过TCP/IP协议与MySQL服务器建立连接。连接过程中，客户端和服务器协商通信协议版本、认证方式等信息。
	握手阶段： 服务器发送握手数据包，包含服务器版本、连接ID等信息。客户端接收握手数据包并回应，选择认证方式。
	
认证阶段：
	身份验证： 客户端发送身份验证信息，通常是用户名和密码等。服务器验证信息的合法性，如果认证通过，客户端获得访问权限。
	
查询缓存：
	查询缓存检查： 服务器在接收到客户端的查询请求后，会检查查询缓存，看是否已经缓存了相同的查询。如果在缓存中找到了相同的查询，服务器直接返回缓存结果，而不执行实际查询。
	
语法解析：
	解析SQL语句： 服务器对客户端发送的SQL语句进行语法解析，确保语法正确。如果语法错误，服务器返回错误信息。
查询优化：
	查询优化器： 服务器使用查询优化器分析查询语句，确定最佳执行计划。这涉及索引的选择、连接顺序等优化步骤。
	
存储引擎：
	执行计划： 优化后的查询计划交给存储引擎层执行。存储引擎负责管理表、索引，并执行具体的读取、写入操作。
	
结果返回：
	结果传输： 存储引擎返回执行结果给服务器，服务器将结果传递给客户端。结果可以是查询结果集、执行状态信息等。
	
断开连接：
	关闭连接： 当客户端不再需要与服务器通信时，可以选择主动断开连接。客户端发送断开连接请求，服务器关闭连接。如果是持久连接，可以选择保持连接以便后续的请求。

存储引擎是数据库管理系统（DBMS）中的一个关键组件，负责管理数据的存储、检索和索引等操作。存储引擎定义了数据库系统如何组织、存储和操作数据，决定了数据库的性能、事务支持、并发控制和其他关键特性。

常见的MySQL存储引擎：
InnoDB： InnoDB是MySQL的默认存储引擎，提供了ACID事务支持、行级锁、外键约束等特性。支持事务、行级锁、外键，适合并发，**数据存储方式是按照主键顺序存储**

MyISAM： MyISAM是MySQL的另一个常见的存储引擎，它不支持事务和行级锁，但具有较高的性能和较小的存储开销。MyISAM适用于读密集型应用，如数据仓库和日志分析。按照插入顺序存储;

MEMORY： MEMORY存储引擎将表数据存储在内存中，提供了非常快速的数据访问速度，但在数据库关闭时数据会丢失。适用于小型临时表或缓存数据。相当于Redis

NDB Cluster： NDB Cluster存储引擎是MySQL Cluster的一部分，提供高可用性和分布式数据库功能，适用于需要水平扩展的分布式应用。

CSV： CSV存储引擎以纯文本形式存储数据，适用于数据交换或导出。

Archive： Archive存储引擎专注于高度压缩的数据存储，适用于大量历史数据的存储。

在上面的存储引擎用的比较多是InnoDB和MyISAM，大家重点记得InnoDB数据存储方式是按照主键顺序存储,MyISAM按照插入顺序存储。以下内容介绍的都是InnoDB存储引擎

InnoDB记录结构

行格式

InnoDB 存储引擎支持不同的行格式，每种行格式有其自身的优势和适用场景。在 InnoDB 存储引擎中，常见的行格式包括 COMPACT、REDUNDANT、DYNAMIC（默认） 和 COMPRESSED。
行格式就是我们存储的每一条记录，一条记录就是一行。那么这条记录长什么样。

行格式	描述	适用场景
COMPACT	默认的行格式，压缩存储数据，提高性能和空间效率。	适用于不包含太多变长列的表。
REDUNDANT	旧版行格式，包含冗余信息，已不再推荐使用。	主要用于向后兼容。
DYNAMIC	对变长列进行动态存储，适用于包含大量变长列的表。	适用于具有许多变长列的表。
COMPRESSED	压缩行格式，通过 zlib 压缩算法减小存储空间。	适用于 I/O 受限的环境，提高性能。

COMPACT 格式

这边介绍的是COMPACT 格式，COMPACT 和DYNAMIC相差不多。

记录的额外信息，服务器为了描述这条记录而不得不额外添加的一些信息

变长字段

Compact 行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放，(单个字段提高效率)
例如（c1=aaaaa,c2=aa,c3=a ） 那么存储的变长字段长度列表是 01 02 04
变长类型 VARCHAR(M) 来说，这种类型表示能存储最多 M 个字符（注意是字符不是字节），所以这个类型能表示的字符串最多占用的字节数就是 M×W，VARCHAR(10)最多存10个字符

    1个字节还是2个字节表示真正字符串占用的字节数的规则
        M×W <= 255字节 ，那么使用1个字节来表示真正字符串占用的字节数；
        M×W>255字节,那么有两个情况如果 L <= 127字节 ，则用1个字节来表示真正字符串占用的字节数；如果 L > 127 ，则用2个字节来表示真正字符串占用的字节数（这点有点像redis也是先记录对应的长度，不够用才多一个字节）
    如果该可变字段允许存储的最大字节数（ M×W ）超过255字节并且真实存储的字节数（ L ）超过127字节，则使用2个字节，否则使用1个字节。（区分1个字节还是2个字节，1个字节第一位0，那么最大可以表示127。）
    CHAR(10)类型，如果字符存储的是"aaa"，那么会将剩下的不满足10个，填充7个空格让空间占用十个字符，保证每个数都是占用10个字符。（这是为了更新该字段，那么因为有空字符填充不用重新分配空间）
    对于CHAR(M)类型的列来说，当列采用的是定长字符集时，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表（比如utf-8睡觉哦变长字符集那么也就会产生变化，那么会产生碎片）

NULL值列表

NULL值列表：如果表中没有允许存储 NULL 的列，则 NULL值列表 也不存在了。只记录允许为null的
二进制位的值为 1 时，代表该列的值为 NULL；二进制位的值为 0 时，代表该列的值不为 NULL；
MySQL 规定 NULL值列表 必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则在字节 的高位补 0。例如3个允许为空的，那么就是需要1个字节。例如00000xxx

记录头信息

    记录头信息 ，它是由固定的 5 个字节组成。 5 个字节也就是 40 个二进制位
        预留位1 1bit 没有使用
        预留位2 1 没有使用
        delete_mask 1 标记该记录是否被删除
        min_rec_mask 1 B+树的每层非叶子节点中的最小记录都会添加该标记
        n_owned 4 表示当前记录拥有的记录数
        heap_no 13 表示当前记录在记录堆的位置信息
        record_type 3 表示当前记录的类型， 0 表示普通记录， 1 表示B+树非叶子节点记录， 2 表示最小记录， 3表示最大记录
        next_record 16bit 表示下一条记录的相对位置

MySQL 会为每个记录默认的添加一些列（也称为 隐藏列 ）:row_id(行ID，唯一标识一条记录),transaction_id(事务ID),roll_pointer(回滚指针)
InnoDB存储引擎会为每条记录都添加transaction_id 和roll_pointer这两个列，但是row_id是可选的（在没有自定义主键以及Unique键的情况下才会添加该列）

一个条记录行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节，因为变成字段长度只能是2字节，两个字节 2^16=65536，最多表示65535个字节。
VARCHAR(M)类型的列最多可以占用65535个字节(不包括隐藏列和记录头信息，两个字节 2^16=65536)，M=字符数量，实际能够满足是 M<65535/单位字符字节长度，还要扣除真实字符长度2个字节。（CHARSET=ascii最多65533（not null），而CHARSET=utf8mb4最多16383）

在MySQL中，一个行中的所有列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。这是因为MySQL使用了一种称为"行格式"的存储方式，即将一行数据存储为一个连续的字节序列。为了保证数据的完整性和一致性，MySQL需要限制一行数据的长度，以避免数据溢出或损坏。

    在MySQL中，存储一个VARCHAR(M)类型的列需要占用三部分存储空间：
    	真实数据：即该列实际存储的数据内容。
    	真实数据占用字节的长度：MySQL需要记录该列实际存储的数据内容占用的字节数，以便在读取时能够正确地解析该列。
    	NULL值标识：如果该列有NOT NULL属性，则可以没有这部分存储空间。否则，MySQL需要记录该列是否为NULL值，以便在读取时能够正确地解析该列。

一页最大是16kb，也就是16384字节，而有时候一页会放不下一条记录 列65532 个字节。不只是 VARCHAR(M) 类型的列，其他的 TEXT、BLOB 类型的列在存储数据非常多的时候也会发生行溢出。
Dynamic 和 Compressed 行格式，和Compact非常像只是行溢出，在记录的真实数据处存储字段真实数据的前 768 个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址

InnoDB数据页结构

InnoDB 存储引擎中，存储空间的基本单位是“页”（Page）。默认情况下，一个页的大小通常为 16KB。每个页包含一定数量的数据，例如表的数据行、索引、元数据等。这种以页为基本单位的存储方式有助于提高数据的存储效率和访问性能。

这种页的概念，在操作系统内存，磁盘等都有相关概念。也就是说很多的存储相关，都会有页的概念来提高效率。

在 InnoDB 存储引擎中，不同类型的页承担不同的角色，主要有以下几种类型的页：

	数据页（Data Page）： 数据页是存储实际表数据的主要页类型。每个数据页的大小通常是16KB。它包含了表的行数据、索引数据以及一些页头信息。数据页用于存储表中的记录。
	
	索引页（Index Page）： 索引页用于存储 B-Tree 索引结构。InnoDB 存储引擎使用 B+ 树来管理索引，每个节点对应一个索引页。这些索引页存储着索引的键值，以及指向下一级的索引页或数据页的指针。
	
	undo页： undo 页用于存储事务的回滚信息。当一个事务对数据进行修改时，InnoDB 将原始数据写入 undo 页，以便在事务回滚或执行 MVCC（多版本并发控制）时使用。
	
	回滚段页： 回滚段是 InnoDB 实现事务的一部分。回滚段页用于存储回滚段的状态信息，包括活跃事务列表、已提交事务列表等。
	
	系统页： 系统页存储 InnoDB 存储引擎的一些元数据信息，例如表空间的头信息、日志文件组的信息等。这些页不存储用户数据，而是用于管理存储引擎自身的状态。

    InnoDB 数据页的存储空间大致被划分成了 7 个部分,一共16kb字节：
        File Header 文件头部 38 字节 页的一些通用信息
        Page Header 页面头部 56 字节 数据页专有的一些信息
        Infimum + Supremum 最小记录和最大记录 26 字节 两个虚拟的行记录
        User Records 用户记录 不确定 实际存储的行记录内容
        Free Space 空闲空间 不确定 页中尚未使用的空间
        Page Directory 页面目录 不确定 页中的某些记录的相对位置
        File Trailer 文件尾部 8 字节 校验页是否完整，

    File Trailer：有8个字节（1）前面4个字节是文件的校验和，和File Header里的FIL_PAGE_SPACE_OR_CHKSUM是一样，页在内存数据被修改首先会将File Header里的FIL_PAGE_SPACE_OR_CHKSUM修改写入磁盘。页在内存里数据完全同步完更新页尾的File Trailer
                         （2）4个字节代表页面被最后修改时对应的日志序列位置（LSN）

在上一章【InnoDB行记录结构】中，介绍了行记录结构，有几个字段在数据页这里使用较多。这边再详细介绍下

    InnoDB行记录头信息:
        delete_mask(删除标识):MySQL数据库删除了记录，只是打个删除标识并没有从磁盘删除。这些删除记录组成垃圾链表，而链表中记录占用的空间就是可重用空间。之后如果有新的记录进来可能会覆盖被删除的记录存储空间。
                重新使用删除的空间，只会判断垃圾链表的头节点是否满足插入，不会遍历垃圾链表。插入的记录小于等于都会覆盖原来的记录，这会产生空间碎片，造成一些空间没有使用。
        heap_no（当前记录在本 页 中的位置）：用户插入记录是从2开始，而0和1是是MySQL系统插入的两条伪记录，分别记录最小记录（0位置）和最大记录（1位置）。由5字节大小的 记录头信息 和8字节大小的一个固定 的部分组成的
        record_type(记录的类型): 0 表示普通记录， 1 表示B+树非叶节点记录， 2 表示最小记录， 3 表示最大记录
        next_record：示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，下一条记录 指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。
                    Infimum记录（也就是最小记录） 的下一条记录就是本页中主键值最小的用户记录，而本页中主键值最大的用户记录的下一条记录就是 Supremum记录（也就是最大记录）
                    next_record指向的是记录头信息和真实数据之间的位置数据，也就是在中间。那么左边读取记录头信息，右边读取真实数据。前面的变长字段列表和null字段列表，逆序存取让位置靠前的字段和对应长度信息两个内存更近，效率更高些。
                    进行 delete 操作，产生空白空间，如果进行新的插入操作，MySQL将尝试利用这些留空的区域，但仍然无法将其彻底占用，久而久之就产生了碎片；（show table status from antiepidemic; Data_free 字段就是产生了数据碎片）
                    每个记录的头信息中都有一个 next_record 属性，从而使页中的所有记录串联成一个 单链表 。
        n_owned：页目录，分组里。组内一共有多少条记录

我们看下每一条记录，在数据页中的位置：

    Page Directory(页目录):是将页里面的数据（包括最小记录和最大记录）分成多个组，让后将每个组里的最后一条记录里的地址偏离量取出来按顺序存储到页的尾部地方。
                    页面目录中的这些地址偏移量被称为 槽 （英文名： Slot ）。这样子是为了加快查询而不需要查询的时候，遍历真个页里面的链表。

        每个分组中的记录条数是有规定的：对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。
        假设每条记录255b，那么一个16kb数据页大概可以存62条记录；如果是100b，那么大概可以存160条记录。
    在一个个数据页中查找指定主键值的记录的过程分为两步：
        1. 通过二分法查找页目录确定该记录所在的槽，并找到该槽中主键值最小的那条记录。（二分法找到的槽是槽里最大的主键值，找到最小因为槽是挨着，往前一个槽，下一个记录就是当前槽最小值）
        2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。

以上用到了，我们之前说的二分法。接下来我们看看最小页和页目录长什么样。

在这里，不知道读者是否有个疑问。就是一个数据页大小是16kb，那么如果每天记录比较大。那么实际一个数据页没有几条记录，是否直接遍历就行了。而不需要维护分组，页目录等。简化结构是否更好。

接下来我们看看，MySQL如何将不同的数据页串起来。这里就要用到File Header(文件头部)和 Page Header（页面头部）

    File Header(文件头部):
        FIL_PAGE_SPACE_OR_CHKSUM 4 字节 页的校验和（checksum值）
        FIL_PAGE_OFFSET 4 字节 页号，InnoDB 通过页号来可以唯一定位一个页（4个字节，32个比特位，一个表空间可以有2^32个页，1页有16kb，那么一个表空间最多支持64TB数据）
        FIL_PAGE_PREV 4 字节 上一个页的页号
        FIL_PAGE_NEXT 4 字节 下一个页的页号
        FIL_PAGE_LSN 8 字节 页面被最后修改时对应的日志序列位置（英文名是：Log Sequence
        Number）
        FIL_PAGE_TYPE 2 字节 该页的类型，索引页（ FIL_PAGE_INDEX 数据页，值是0x45BF ），undo日志页，系统页
        FIL_PAGE_FILE_FLUSH_LSN 8 字节 仅在系统表空间的一个页中定义，代表文件至少被刷新到了对应的LSN值
        FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID 4 字节 页属于哪个表空间


        FIL_PAGE_PREV，FIL_PAGE_NEXT有的数据很大，一个页放不下，就通过上下页将页关联起来，形成双向链表。有的页没有上下页属性，也就是说一个页完全放得下。

    Page Header（页面头部）：
        PAGE_N_DIR_SLOTS 2 字节 在页目录中的槽数量
        PAGE_HEAP_TOP 2 字节 还未使用的空间最小地址，也就是说从该地址之后就是 Free Space
        PAGE_N_HEAP 2 字节 本页中的记录的数量（包括最小和最大记录以及标记为删除的记录）
        PAGE_FREE 2 字节 第一个已经标记为删除的记录地址（各个已删除的记录通过 next_record 也会组成一个单链 表，这个单链表中的记录可以被重新利用）
        PAGE_GARBAGE 2 字节 已删除记录占用的字节数
        PAGE_LAST_INSERT 2 字节 最后插入记录的位置
        PAGE_DIRECTION 2 字节 记录插入的方向
        PAGE_N_DIRECTION 2 字节 一个方向连续插入的记录数量
        PAGE_N_RECS 2 字节 该页中记录的数量（不包括最小和最大记录以及被标记为删除的记录）
        PAGE_MAX_TRX_ID 8 字节 修改当前页的最大事务ID，该值仅在二级索引中定义
        PAGE_LEVEL 2 字节 当前页在B+树中所处的层级
        PAGE_INDEX_ID 8 字节 索引ID，表示当前页属于哪个索引
        PAGE_BTR_SEG_LEAF 10 字节 B+树叶子段的头部信息，仅在B+树的Root页定义
        PAGE_BTR_SEG_TOP 10 字节 B+树非叶子段的头部信息，仅在B+树的Root页定义

从上面图片，我们可以看出MySQL数据页之间通过 File Header(文件头部)里的FIL_PAGE_PREV和FIL_PAGE_NEXT，将不同页串起来了。而如果观察双端链表数据结构，那么她就是双向链表

    class Node {
        int val;
        Node pre;
        Node next;

        public Node(int val, Node pre, Node next) {
            this.val = val;
            this.pre = pre;
            this.next = next;
        }
    }

B+树索引

新分配的数据页编号可能并不是连续的，也就是说我们使用的这些页在存储空间里可能并不挨着。下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值

页分裂是指当插入一条新纪录时，数据页已满，需要在该页中创建新的页，并将记录分布到这两个页中的一种操作（页分裂的目的就是保证：后一个数据页中的所有行主键值比前一个数据页中主键值大。主键非递增才会导致或者插入的主键从原有数据中间插入）

从上一节我们知道，不同数据页之间是按主键大小排序，也就是前面数据页存储的数据，主键更小；而后面的数据存储的主键值更大。

现在的插入的数据，结构如上，那么查找该如何查找呢 select * from user where id=666
一种比较朴素的做法是从第一页开始找，也就是id=1开始，按顺序遍历。那么for循环找到id=666，那么就找到这条记录了。这个如果数据量不大，那么查询数据也是挺快的，那么如果从几十万，几百万的数据中查询。那么就比较慢了。

那么可以通过什么方式呢。
他们是按顺序排序，前面我们不是学过二分法了。那么直接通过二分法，不就行了吗。使用二分法，那么首先要将所有的数据从磁盘加载到内存，放在内存里通过CPU找到那一条。这里是个方法，但是如果数据量是几百万，那么存储容量就可能1TB。1TB的内存，这不是我们所能承担的。那么这个方法行不通，那么就是通过其他方式。

在算法中，有一个说法就是。算法就是内存和时间相互转换的游戏。你要时间更快，那么要牺牲内存；你要减少内存，那么要牺牲时间。
如果一本书，你要快速翻到对应的章节。那么你是先从目录查找，找到对应章节的页码。然后再翻到对应章节页码。
以上行为，就是通过存储额外的目录，来提升查找速度。

这边MySQL也是借用了以上思想，引用目录项概念来实现加快查询。

    下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，所以那么就是页是按主键值排序，页里面的数据也是按主键值排序变成链表。
    众多多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录(实际就是索引)，每个页对应一个目录项，每个目录项包括下边两个部分：
        （1）页的用户记录中最小的主键值，我们用 key 来表示。
        （2）页号，我们用 page_no 表示。
    几个目录项在物理存储器上连续存储，比如把他们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能。因为是有序的数组，那么可以快递定位要查找的key在哪个页中。
    实际目录项也是存储在页中，只是这个页中存储的是索引而不是数据。为了区分索引还是数据在记录头信息里的 record_type 属性:0 ：普通的用户记录（用户插入数据）,1 ：目录项记录(索引)2 ：最小记录 3 ：最大记录

我们看下利用数据页和页目录构造出B+树样子

    目录项记录和普通的 用户记录 的不同点：
        目录项记录 的 record_type 值是1，而普通用户记录的 record_type 值是0。
        目录项记录 只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有 InnoDB 自己添加的隐藏列。
        还记得我们之前在唠叨记录头信息的时候说过一个叫 min_rec_mask 的属性么，只有在存储 目录项记录 的页
        中的主键值最小的 目录项记录 的 min_rec_mask 值为 1 ，其他别的记录的 min_rec_mask 值都是 0 。
    数据非常多，记录非常多。页也就非常多，那么目录项的页页非常多。那么就需要建一个范围更广的目录项的页(存储目录项页里的最小记录)，一层一层上去，那么实际就是一棵树，叫B+ 树。

    实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为 叶子节点 或 叶节点 ，其余用来存放 目录项 的节点称为 非叶子节点 或者 内节点 ，其中 B+ 树最上边的那个节点也称为 根节点
    假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：
        如果 B+ 树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录。
        如果 B+ 树有2层，最多能存放 1000×100=100000 条记录。
        如果 B+ 树有3层，最多能存放 1000×1000×100=100000000 条记录。
        如果 B+ 树有4层，最多能存放 1000×1000×1000×100=100000000000 条记录。
    一般情况下，我们用到的 B+ 树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录
    查找某一键值的行记录时最多只需要1~3次磁盘I/O操作（根节点的那次不算磁盘I/O）。以上时间复杂度（ log1000+log1000+log1000+log100=log(1000*1000*1000*100)=log(100000000000)=log n ，就当作是O(log n)）

     B+ 树本身就是一个目录，或者说本身就是一个索引(聚簇索引)。它有两个特点：
        1. 使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：
        页内的记录是按照主键的大小顺序排成一个单向链表。
        各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
        存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成
        一个双向链表。
        2. B+ 树的叶子节点存储的是完整的用户记录。
   聚簇索引 只能在搜索条件是主键值时才能发挥作用，因为 B+ 树中的数据都是按照主键进行排序的

    二级索引(secondary index 辅助索引)和聚簇索引区别
        （1）使用记录 c2 列的大小进行记录和页的排序，这包括三个方面的含义：
            页内的记录是按照 c2 列的大小顺序排成一个单向链表。
            各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一个双向链表。
            存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的 c2 列大小顺序排成一个双向链表。
        （2）B+ 树的叶子节点存储的并不是完整的用户记录，而只是 c2列+主键 这两个列的值。
        （3）目录项记录中不再是 主键+页号 的搭配，而变成了 c2列+页号 的搭配。
    回表:通过二级索引找到主键后，我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

    一个B+树索引的根节点自诞生之日起，便不会再移动。B树构造也还是从下往上，知道了叶子节点的数据。才能逐层往上构造非叶子节点。
    我们需要保证在B+树的同一层内节点的目录项记录除 页号 这个字段以外是唯一的。不然如果在一个页中，假设二级索引都是1，那么不知道该插入左右两边哪个页。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成
        索引列的值
        主键值
        页号

MyISAM索引

接下来，我们看看MyISAM 数据结构长什么样

MyISAM 中建立的索引相当于全部都是 二级索引,数据页中记录都是行号+数据，主键索引也是找到行号，然后再查询一般数据页（相当于回表）
InnoDB中的索引即数据，数据即索引，而MyISAM中却是索引是索引、数据是数据。

二级索引

在前面我们已经介绍了，MySQL数据在一行记录时如何，以及数据是如何存储在B+树里。这边我们通过前面的知识，来解释索引的原理和规范。为什么要建立索引，以及为什么索引对于插入和删除有影响，为啥有的索引不生效，有的索引比较慢。所有的这些疑问，都可以通过数据结构解释。因为数据结构决定了以上所有的特性。（实际主键是一级索引，当前说得是二级索引。二级索引也是我们平常说得索引，这边为了方便直接说索引）

索引是对数据库表中一列或多列的值进行排序的数据结构，以便快速检索和定位数据行。使用索引可以大幅减少查询的时间复杂度，特别是在大型数据库中。

我们看下前面对索引的定义是一列或多列的值进行排序的数据结构,从这里我们可以知道索引就是对特定列排序的数据结构。
那我们从数据结构角度看看，索引实际长什么样。

每建立一个索引都要为它建立一棵 B+ 树，每一棵 B+ 树的每一个节点都是一个数据页， 一个页默认会占用 16KB 的存储空间(record_format_demo.ibd每个ibd文件大小都是16kb倍数) （这也就是解释了新增，修改，删除索引会变慢速度）
如果我们想使用联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列。所有记录都是按照索引列的值从小到大的顺序排好序的。 因为我们从最上面图中可以看出，页目录查找也是先找第一个索引字段，然后再找第二个索引字段。
我们看下 建立索引的原则，然后我会对以下原则从数据结构角度去解释，这样更容易理解为什么：

索引原则：
1.选择唯一性索引
唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。(唯一索引，那么当前字段辨识度很高。通过页目录二分查找，可以更快速的定位。加快查询速度)

2.为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。（如果ORDER BY字段在索引中，那么通过页目录就可以找到对应的值了。）

3.为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。（因为你查询字段走索引，那么页目录那可以过滤更多数据）

4.限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。（索引就是一颗B+树，你建立的索引越多。当前表的B+树也就越多）

5.尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。（索引就是数据结构，索引值长，那么索引形成的B+树特别大）

6.尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。（索引就是数据结构，索引值长，那么索引形成的B+树特别大）

7.删除不再使用或者很少使用的索引
表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8.最左前缀匹配原则，非常重要的原则。
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a1="1" and b="2" and c>=3 and d=4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。（这个实际就是在页目录查找过程中，也是按第一个列值比较，相同了再比较下一个列。这个有点像比较日期，先比较年，再比较月，然后日。而联合索引的二级索引，也是一样先匹配第一个字段，再第二个字段。）

9.=和in可以乱序。
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

10.尽量选择区分度高的列作为索引。
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。 （这个实际就是要让页目录，可以过滤更多的数据。这样才能查的更快）

11.索引列不能参与计算，保持列“干净”。
比如 from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’); 

12.尽量的扩展索引，不要新建索引。
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

注意：选择索引的最终目的是为了使查询的速度变快。上面给出的原则是最基本的准则，但不能拘泥于上面的准则。读者要在以后的学习和工作中进行不断的实践。根据应用的实际情况进行分析和判断，选择最合适的索引方式。

我们看下具体查找过程

    SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';索引范围查找过程
        （1）找到 name 值为 Asa 的记录。
        （2）找到 name 值为 Barlow 的记录。
        （3）哦啦，由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来喽～
        （4）找到这些记录的主键值，再到 聚簇索引 中 回表 查找完整的记录。
    如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到 B+ 树索引，从能走索引的范围过滤，已经是很少的数据量了。在这些范围继续过滤。

    MySQL文件排序（英文名： filesort ）:
        把查询到的记录都加载到内存中，再用一些排序算法，比如快速排序、归并排序、吧啦吧啦排序等等在内存中对这些记录进行排序，有的时候可能查询的结果集太大以至于不能在内存中进行排序的话，还可能暂时借助磁盘的空间来存放中间结果，排序操作完成后再把排好序的结果集返回到客户端
        如果 ORDER BY 是按照索引，那么查出来的记录就是按照索引排序好了，可以直接使用不需要使用到了文件排序。这个叫索引排序。
        ORDER BY name, birthday LIMIT 10；ORDER BY name DESC, birthday DESC LIMIT 10 ，都可以走索引排序；
        SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;这个不能走索引，

    SELECT * FROM person_info WHERE name > 'Asa' AND name < 'Barlow';
         name 列的值可以走索引，所以值在 Asa ～ Barlow 之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，这个就是顺序I/O
        name 列的值的记录的 id 字段的值可能并不相连，那么从不连续的id字段中获取对应值，那么可能需要查更多的数据页，那么这个查询就是随机I/O
    需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用 二级索引
    覆盖索引:最好在查询列表里只包含索引列(SELECT name, birthday, phone_number FROM person_info ORDER BY name, birthday, phone_numbe r; 没有contry其他字段)

以下是索引建立的一些注意事项：

    我们很不鼓励用 * 号作为查询列表，最好把我们需要查询的列依次标明。
    最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。（不用要状态建立索引，效果不大）；索引列的类型尽量小
    表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/O 。
    只索引字符串值的前缀的策略是我们非常鼓励的，尤其是在字符串类型能存储的字符比较多的时候(车次guid，太大了可以使用这个方案，不过排序有可能不走索引了不过没有按guid排序还是可以)
    让主键具有 AUTO_INCREMENT ，让存储引擎自己为表生成主键，而不是我们手动插入（不然会有页分裂，因为插入的顺序是按主键排序，如果主键不是递增的）。目前现在很多数据库都是分库分表，这边推荐使用雪花id。id尽量使用long类型，而不是string类型。

Explain

对比解决bug难的是发现bug，而不是解决bug。 Explain就是分析查询语句，这也是通过 Explain来优化慢SQL主要方式之一。

EXPLAIN 是 MySQL 中用于查询分析的关键字，用于获取关于查询执行计划的信息。通过 EXPLAIN，你可以了解数据库是如何执行查询的，以及它选择了哪些索引和算法。

     EXPLAIN 语句输出的各个列的作用先大致罗列一下：
        id 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id。查询标识符，用于标识查询中的不同部分。
        select_type SELECT 关键字对应的那个查询的类型（一般都是SIMPLE，对于使用UNION会有其他类型PRIMARY，UNION）。查询的类型，例如 SIMPLE（简单查询）、SUBQUERY（子查询）等。
        table 涉及的表名。
        partitions 匹配的分区信息
        type 针对单表的访问方法，表示连接类型，可能的取值包括 ALL（全表扫描）、INDEX（索引扫描）等。
        possible_keys 可能用于查询的索引列表。
        key 实际用于查询的索引。
        key_len 实际使用到的索引长度（联合索引可以查看用到了几个索引列，有时候不能全部都用到）
        ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息。显示索引如何被使用的详细信息。
        rows 预估的需要读取的记录条数
        filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
        Extra 其他的一些执行信息，如 Using filesort（使用文件排序）等。

下面介绍下 EXPLAIN type 字段

     type访问方法：
        system:该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory。
                CREATE TABLE t(i int) Engine=MyISAM;
                INSERT INTO t VALUES(1);
                EXPLAIN SELECT * FROM t;
        const：根据主键或者唯一二级索引列与常数进行等值匹配
                 EXPLAIN SELECT * FROM s1 WHERE id = 5;
        eq_ref：连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）
                EXPLAIN SELECT * FROM `t_pass_record` p LEFT JOIN device_info d  ON p.sn=d.sn WHERE p.sn="111";
        ref：普通的二级索引列与常量进行等值匹配时来查询某个表
            EXPLAIN SELECT * FROM `t_pass_record` WHERE transit_time="2022-04-12 10:39:58";
        ref_or_null：对普通二级索引进行等值匹配查询，该索引列的值也可以是 NULL 值时（group_code="1" OR group_code IS NULL; 这两个都要有）
            EXPLAIN SELECT * FROM `t_pass_record` WHERE group_code="1" OR group_code IS NULL;
        index_merge：索引合并,一个查询两个索引都用到
            EXPLAIN SELECT * FROM `t_pass_record` WHERE  group_code ="1" OR sn="2";
        unique_subquery：类似于两表连接中被驱动表的 eq_ref 访问方法， unique_subquery 是针对在一些包含 IN 子查询的查询语 句中，如果查询优化器决定将 IN 子查询转换为 EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的 type 列的值就是 unique_subquery
            EXPLAIN SELECT * FROM t_pass_record WHERE id IN (SELECT id FROM device_info where device_info.sn = t_pass_record.sn) OR transit_time = '2022-04-12 10:39:58';
        range：使用索引获取某些 范围区间 的记录：
            EXPLAIN SELECT * FROM `t_pass_record` WHERE transit_time BETWEEN "2022-04-12 10:39:58" AND "2023-04-12 10:39:58" ;
            EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
        index：可以使用索引覆盖，但需要扫描全部的索引记录时，使用联合索引时，也就是查询列和搜索列都在联合索引中，只是不在第一个
            EXPLAIN SELECT tenant_id FROM `t_pass_record` WHERE  tenant_id ="2022-04-12 10:39:58" ;（KEY `idx_transit_time` (`transit_time`,`tenant_id`,`transit_method`) USING BTREE,）
        ALL:全表扫描
            EXPLAIN SELECT * FROM `t_pass_record`

    possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些， key 列表示实际用到的索引有哪些
    possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

    key_len 列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：
        （1）对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是 VARCHAR(100) ，使用的字符集是 utf8 ，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。
        （2）如果该索引列可以存储 NULL 值，则 key_len 比不可以存储 NULL 值时多1个字节。（int4字节，不为null索引长度4，为null那么索引长度5）
        （3）对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。(`group_code` varchar(16),utf8mb4, DEFAULT NULL,16*4+2(变长字节)+1=67) （执行优化器按照最大的长度来算，实际可能会小一些）

    ref：当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是 const 、 eq_ref 、 ref 、 ref_or_null 、unique_subquery 、 index_subquery 其中之一时， ref 列展示的就是与索引列作等值匹配的（其他不会有）
    rows：执行计划的 rows 列就代表预计需要扫描的行 数，如果使用索引来执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。
    filtered:filtered 列的值范围从0到100，表示从0%到100%的过滤程度。如果 filtered 的值很低，说明查询条件没有充分利用索引，可能需要考虑优化查询或者添加适当的索引。如果 filtered 的值接近100%，则表示查询条件充分利用了索引，这通常是一个好的迹象。我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值，就是驱动表rows=44，再根据filtered=10%，驱动表扇出的值时44*10%=4.4，还要对被驱动表查询大约4.4次。
        EXPLAIN
        SELECT * FROM `t_pass_record` p
        LEFT JOIN device_info d  ON p.sn=d.sn
        WHERE p.group_code >"1" AND p.city="223";

    Explain的Extra信息，就是一些说明和补充
        No tables used：当查询语句的没有 FROM 子句时将会提示该额外信息
             EXPLAIN SELECT 1;
        Impossible WHERE：查询语句的 WHERE 子句永远为 FALSE 时将会提示该额外信息
            EXPLAIN SELECT * FROM t_pass_record WHERE 1 != 1;
        No matching min/max row：当查询列表处有 MIN 或者 MAX 聚集函数，但是并没有符合 WHERE 子句中的搜索条件的记录时(where 条件过滤后是null，没有记录时)
            EXPLAIN SELECT MIN(sn) FROM t_pass_record WHERE sn = 'abcdefg';
        Using index:当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，查询不需要回表
            EXPLAIN SELECT transit_method FROM `t_pass_record` WHERE transit_time BETWEEN "2022-04-12 10:39:58" AND "2023-04-12 10:39:58" ;(都在索引列中)
        Using index condition:有些搜索条件中虽然出现了索引列，但却不能使用到索引(先把两个条件都查询出来看有几条记录再回表查询,索引条件下推 （英文名： Index Condition Pushdown ）)
            EXPLAIN SELECT * FROM `t_pass_record` WHERE transit_time BETWEEN "2022-04-12 10:39:58" AND "2023-04-12 10:39:58" ;

        Using where：用全表扫描来执行对某个表的查询，并且该语句的 WHERE 子句中有针对该表的搜索条件时
            EXPLAIN SELECT city FROM `t_pass_record` WHERE  city ="1" AND sn ="1";（where 条件存在没有索引的列）

        Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度， MySQL 一般会为其分配一块名叫join buffer 的内存块来加快查询速度，也就是我们所讲的 基于块的嵌套循环算法
            EXPLAIN
            SELECT * FROM `t_pass_record` p
            LEFT JOIN device_info d  ON p.sn=d.sn
            WHERE p.group_code >"1" AND p.city="223";  （关联如果不是主键，那么效率会比较低。一般要通过主键关联）

        Not exists：当我们使用左（外）连接时，如果 WHERE 子句中包含要求被驱动表的某个列等于 NULL 值的搜索条件，而且 那个列又是不允许存储 NULL 值
            EXPLAIN
            SELECT * FROM `t_pass_record` p
            LEFT JOIN device_info d  ON p.sn=d.sn
            WHERE p.group_code >"1" AND p.city="223" AND d.id IS NULL;（d.id IS NULL 是不允许为空的）

        Using intersect(...) 、 Using union(...) 和 Using sort_union(...): Using intersect(...) 提示，说明准备使用 Intersect 索引合并的方式执行查询，括号中的 ... 表示需要进行索引合并的索引名称；如果出现了 Using union(...) 提示，说明准备
                使用 Union 索引合并的方式执行查询；出现了 Using sort_union(...) 提示，说明准备使用 Sort-Union 索 引合并的方式执行查询。
                EXPLAIN SELECT city FROM `t_pass_record` WHERE  group_code ="1" OR sn ="1";(也就是之前自己做优惠券，手机号和车牌用or查询也是走索引走的是索引合并)
        Zero limit：LIMIT 子句的参数为 0 时
            EXPLAIN SELECT city FROM `t_pass_record` WHERE  txnl_zt =1 LIMIT 0;

        Using filesort：下排序操作无法使用到索引，只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序（文件排序  filesort），而且文件排序很耗时最好采用索引排序也就是排序不要用id，还是乖乖用时间
            EXPLAIN SELECT * FROM `t_pass_record` WHERE transit_time BETWEEN "2022-04-12 10:39:58" AND "2023-04-12 10:39:58" ORDER BY  id DESC;（排序还是用时间排序，以前都用id排序增加了复杂度，排序还是用时间）


        Using temporary： DISTINCT 、 GROUP BY 、 UNION 等子句的查询过程中，如果不能有效利用索引来完成查询，需要创建临时表
            EXPLAIN SELECT  DISTINCT city FROM `t_pass_record`
            EXPLAIN SELECT  create_time,COUNT(*)  FROM `t_pass_record`  GROUP  BY create_time;（MySQL 会在包含 GROUP BY子句的查询中默认添加上 ORDER BY 子句，8.0版本去掉隐式排序了）
            EXPLAIN SELECT sn, COUNT(*) AS amount FROM t_pass_record GROUP BY sn;(走索引了GROUP BY sn，不会创建临时表，不过生产很少能用到)


    EXPLAIN FORMAT=JSON SELECT * FROM `t_pass_record` ;(FORMAT=JSON 会输出时间执行成本)
           "cost_info": {
                    "read_cost": "10629.70",  (IO 成本,检测 rows × (1 - filter) 条记录的 CPU 成本)
                    "eval_cost": "8.80",(检测 rows × filter 条记录的成本)
                    "prefix_cost": "10638.50",(read_cost + eval_cost)
                    "data_read_per_join": "315K"(此次查询中需要读取的数据量)
                  }

总结

以上是对MySQL数据结构做了简单介绍，实际还有很多内容。InnoDB的表空间，MVCC，锁，复制，等一些内容。推荐感兴趣的读者阅读原作《MySQL是怎样运行的》