MySQL 基础

MySQL（官方发音为/maɪ ˌɛskjuːˈɛl/ “My S-Q-L”，但也经常被读作 /maɪ ˈsiːkwəl/ “My Sequel”）是一种开源的关系型数据库管理系统(RDBMS)，由瑞典公司MySQL AB开发。2009年，甲骨文公司（Oracle）收购昇阳微系统公司，MySQL成为Oracle旗下产品。MySQL 在过去由于性能高、成本低、可靠性好，已经成为最流行的开源数据库，因此被广泛地应用在 Internet 上的中小型网站中

关系型数据库

关系型数据库是一种使用关系模型（也称为表格模型）来组织和存储数据的数据库管理系统。它是一种基于关系的数据结构，使用表格来表示和存储数据，其中每个表格被称为一个”关系”，每个关系由行和列组成。每一行表示一个数据记录，每一列表示一个属性（也称为字段或属性）

关系型数据库通过使用结构化查询语言(SQL)来处理对数据的操作。SQL是一种标准化的查询语言，用于定义、管理和操作关系型数据库中的数据。SQL提供了各种操作，包括插入（INSERT）、查询（SELECT）、更新（UPDATE）、删除（DELETE）等，以及用于创建和修改表格结构的命令（如CREATE TABLE、ALTER TABLE等）

关系型数据库具有以下特点：

1. 表格结构：数据以表格形式存储，每个表格由行和列组成。每一行代表一个数据记录，每一列代表一个属性。

2. 主键：每个表格通常都会定义一个主键，用于唯一标识表格中的每一行。主键确保数据的唯一性和完整性。

3. 外键：关系型数据库支持外键约束，用于建立表格之间的关系。外键是另一个表格的主键，它用来确保数据之间的关联性。

4. 数据完整性：关系型数据库通过各种约束（如唯一约束、非空约束等）来保持数据的完整性，防止数据不一致或无效的情况。

5. 数据查询：通过SQL查询语言，可以方便地执行各种复杂的数据查询和操作，包括数据的筛选、排序、聚合等。

6. 数据安全性：关系型数据库提供用户权限管理，管理员可以控制用户对数据库和表格的访问权限，确保数据的安全性。

7. 事务支持：关系型数据库支持事务处理，确保数据库操作的原子性、一致性、隔离性和持久性（ACID特性）。

一些著名的关系型数据库管理系统包括MySQL、Oracle、Microsoft SQL Server、PostgreSQL等。这些数据库系统被广泛用于各种应用场景，从个人项目到大型企业应用，关系型数据库一直是最常见和重要的数据存储解决方案之一

SQL

SQL 是一种结构化查询语言 ( Structured Query Language )，是一种用于管理和操作关系型数据库的标准化查询语言。它是一种声明性的语言，用于定义、管理和操作数据库中的数据。SQL允许用户通过简单的命令来执行各种操作，如插入、查询、更新、删除等，以及管理数据库的结构，例如创建和修改表格、索引等

SQL 的一些主要特点和用途包括：

1. 数据查询：通过SQL，用户可以从数据库中查询数据。最常见的查询是使用SELECT语句来检索特定的数据行或列。

2. 数据插入、更新和删除：SQL允许用户使用INSERT、UPDATE和DELETE语句来向数据库中插入、修改和删除数据。

3. 数据定义：用户可以使用SQL的CREATE、ALTER和DROP语句来定义数据库、表格、索引和其他数据库对象的结构。

4. 数据完整性：SQL支持约束（如PRIMARY KEY、UNIQUE、NOT NULL等）来保持数据的完整性和一致性。

5. 数据管理：通过SQL，用户可以管理数据库用户、角色和权限，控制数据的访问和安全性。

6. 数据聚合：SQL支持用于对数据进行聚合和计算的函数，如SUM、COUNT、AVG、MAX、MIN等。

SQL的基本语法通常包括以下几个部分：

• SELECT：用于查询数据。
• FROM：指定要查询的表格。
• WHERE：可选部分，用于筛选特定的数据行。
• INSERT INTO：用于向表格中插入数据。
• UPDATE：用于更新表格中的数据。
• DELETE FROM：用于从表格中删除数据。
• CREATE TABLE：用于创建新表格。
• ALTER TABLE：用于修改现有表格的结构。
• DROP TABLE：用于删除表格。

SQL是一种强大且灵活的查询语言，它在关系型数据库管理中扮演着核心角色，使用户可以轻松地进行数据管理和操作。无论是简单的数据库查询还是复杂的数据处理，SQL都是数据库开发和管理中不可或缺的工具

MySQL

字段类型

• 数值类型：
  • INT：用于存储整数，可以指定整数的范围，如 INT(10)
  • TINYINT：用于存储范围较小的整数，如 TINYINT(4)
  • BIGINT：用于存储大整数，如 BIGINT(20)
  • DECIMAL：用于存储高精度的小数，可以指定小数位数，如 DECIMAL(10, 2)
  • FLOAT：用于存储单精度浮点数
  • DOUBLE：用于存储双精度浮点数

2. 字符串类型：

   • CHAR：固定长度字符串，指定最大长度，如 CHAR(50)
   • VARCHAR：可变长度字符串，指定最大长度，如 VARCHAR(255)
   • TEXT：用于存储大文本数据，如文章内容
   • ENUM：用于定义枚举类型，可从预定义的选项中选择一个值

3. 日期和时间类型：

   • DATETIME：用于存储日期和时间，格式为 'YYYY-MM-DD HH:MM:SS'
   • TIMESTAMP：用于存储时间戳，通常用于记录数据的创建或修改时间

4. 布尔类型：

   • BOOL 或 BOOLEAN：用于存储布尔值，可以取值为 TRUE/FALSE 或 1/0

5. 二进制类型：

   • BLOB：用于存储二进制大对象，如图像、视频等
   • BINARY：固定长度的二进制数据
   • VARBINARY：可变长度的二进制数据

6. 其他类型：

   • JSON：用于存储 JSON 格式的数据

VARCHAR 和 CHAR

VARCHAR 是最常见的字符串类型。VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是可变的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，MyISAM会将行拆成不同的片段存储；InnoDB则需要分裂页来使行可以放进页内

下面这些情况使用VARCHAR是合适的：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等

CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符

VARCHAR(10) 和 VARCHAR(200)

使用VARCHAR(10)和VARCHAR(200)存储”hello”的空间开销是一样的。那么使用更短的列有什么优势吗？

VARCHAR(10) 和 VARCHAR(200) 都是变长类型，表示能存储最多 10 个字符和 200 个字符。因此，VARCHAR(200) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10) 存储超过 10 个字符时，就需要修改表结构才可以

虽说 VARCHAR(10) 和 VARCHAR(200) 能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点

不过，VARCHAR(200) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(200) 是按照 200 这个长度来进行的，也就会消耗更多内存

BLOB 和 TEXT

BLOB 和 TEXT 都是为存储很大的数据而设计的数据类型，分别采用二进制和字符方式存储

与其他类型不同，MySQL 把每个 BLOB 和 TEXT 值当做一个独立的对象去处理。当 BLOB 和 TEXT 值太大时，InnoDB会使用专门的 “外部” 存储区域来进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储区域存储实际的值

MySQL 对 BLOB 和 TEXT 列进行排序与其他类型是不同的：它只对每个列的最前max_sort_length个字节而不是整个字符串做排序。同样的，MySQL也不能将BLOB或TEXT列全部长度的字符串进行索引

为什么不推荐用 TEXT 和 BLOB？

• 不能有默认值
• 在遇到使用临时表的情况时，无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》这本书有提到）
• 检索效率比 CHAR 和 VARCHAR 低
• 不能直接创建索引，需要指定前缀长度
• 会消耗大量的网络和 IO 带宽
• 可能会导致表上的 DML 操作都变得较慢

DATETIME 和 TIMESTAMP

DateTime 类型没有时区信息，Timestamp 和时区有关

Timestamp 只需要使用 4 个字节的存储空间，但是 DateTime 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小

• DateTime：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

NULL 和 ‘’ 的区别

NULL 跟 '' (空字符串)是两个完全不一样的值，区别如下：

• NULL 代表一个不确定的值，就算是两个 NULL，它俩也不一定相等。例如，SELECT NULL = NULL 的结果为 false，但是在我们使用 DISTINCT ，GROUP BY， ORDER BY时，NULL又被认为是相等的
• '' 的长度是 0，是不占用空间的，而 NULL 是需要占用空间的
• NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数
• 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的

基础架构

可以看到， MySQL 的架构共分为两层：Server 层和存储引擎层

• Server 层负责建立连接、分析和执行 SQL。MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现
• 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引

MySQL 的基础架构是由多个组件组成的，每个组件都有不同的功能，共同构成了 MySQL 数据库管理系统的整体架构。下面是 MySQL 基础架构的主要组件：

1. 连接器（Connection Manager）：
   连接管理器负责处理客户端应用程序与MySQL服务器之间的连接请求。当客户端应用程序需要与MySQL进行通信时，它会向连接管理器请求连接，然后连接管理器负责建立和维护与MySQL服务器的连接。连接管理器还负责认证用户身份和处理连接的断开和关闭
2. 查询分析器和优化器（Query Parser and Optimizer）：
   当客户端应用程序发送SQL查询语句到MySQL服务器时，查询分析器负责解析这些查询，并将其转换为可执行的内部表示形式。优化器则负责分析查询，并尝试找到最优的执行计划，以提高查询性能。优化器会考虑索引、表格关联、数据分布等因素来生成最佳执行计划
3. 查询缓存（Query Cache）：
   查询缓存用于存储已经执行过的查询和其结果集。如果客户端发送了一个已经存在于查询缓存中的查询，MySQL可以直接返回缓存中的结果，而无需再次执行查询和优化。然而，查询缓存的使用也受到一些限制，因为在写操作后，相关的查询缓存将被自动失效，可能导致性能下降
4. 查询执行器（Query Executor）：
   查询执行器负责执行查询的执行计划，并从存储引擎获取数据。它会将查询结果返回给连接管理器，然后连接管理器将结果返回给客户端应用程序
5. 存储引擎（Storage Engines）：
   MySQL支持多种存储引擎，存储引擎负责实际的数据存储和检索。不同的存储引擎具有不同的特性和适用场景。常见的存储引擎包括InnoDB、MyISAM、Memory等
6. 日志管理（Log Manager）：
   MySQL维护多种日志来记录数据库的操作和变更。主要的日志包括二进制日志（Binary Log）、事务日志（Transaction Log）和错误日志（Error Log）。这些日志对于数据恢复、复制和故障排除都非常重要

存储引擎

• InnoDB（默认引擎）：
  InnoDB是MySQL的默认存储引擎，它支持事务处理（ACID特性），具有高并发性和数据完整性，适合用于大型应用和需要数据完整性的场景。InnoDB也支持行级锁定，可以提供更好的并发性能
• MyISAM：
  MyISAM是MySQL的另一个常用的存储引擎，它不支持事务处理，但在读取频繁、写入较少的场景下性能表现较好。MyISAM表格适合用于静态数据，例如日志表格等
• MEMORY：
  MEMORY存储引擎将表格数据存储在内存中，适用于临时表格和缓存数据。由于数据存储在内存中，读写速度非常快，但数据会在服务器重启时丢失
• Archive：
  Archive存储引擎适用于存储大量历史数据，它使用高压缩比来减少磁盘空间占用，但不支持索引和直接的更新操作，通常用于数据归档
• CSV：
  CSV存储引擎将数据存储在CSV（逗号分隔值）格式文件中，适合用于导入和导出数据
• NDB Cluster（MySQL Cluster）：
  NDB Cluster是一种高可用性和高容错性的存储引擎，适用于集群环境，支持分布式数据存储和并行查询
• Blackhole：
  Blackhole存储引擎接收写入操作后，会将数据丢弃，但可以用于测试和复制场景
• Federated：
  Federated存储引擎允许在不同MySQL服务器之间进行数据共享，类似于分布式数据库

可以通过 SELECT VERSION() 命令查看你的 MySQL 版本。

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

你也可以通过 SHOW VARIABLES LIKE '%storage_engine%' 命令直接查看 MySQL 当前默认的存储引擎

mysql> SHOW VARIABLES  LIKE '%storage_engine%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| default_storage_engine          | InnoDB    |
| default_tmp_storage_engine      | InnoDB    |
| disabled_storage_engines        |           |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.00 sec)

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它

提供了大量的特性，包括压缩表、空间数据索引等

不支持事务

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作

区别总结：

• InnoDB 支持行级别的锁粒度，MyISAM 不支持，只支持表级别的锁粒度
• MyISAM 不提供事务支持。InnoDB 提供事务支持，实现了 SQL 标准定义了四个隔离级别
• MyISAM 不支持外键，而 InnoDB 支持
• MyISAM 不支持 MVCC，而 InnoDB 支持
• 虽然 MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构，但是两者的实现方式不太一样
• MyISAM 不支持数据库异常崩溃后的安全恢复，而 InnoDB 支持
• InnoDB 的性能比 MyISAM 更强大

InnoDB

数据存放位置

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

我们每创建一个 database（数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里

比如，我这里有一个名为 my_test 的 database，该 database 里有一张名为 t_order 数据库表

进入 /var/lib/mysql/my_test 目录，看看里面文件

[root@xiaolin ~]#ls /var/lib/mysql/my_test
db.opt  
t_order.frm  
t_order.ibd

可以看到，共有三个文件，这三个文件分别代表着：

• db.opt，用来存储当前数据库的默认字符集和字符校验规则
• t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义
• t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件

一张数据库表的数据是保存在「 表名字.ibd 」的文件里的，这个文件也称为独占表空间文件

数据存储结构

从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间，而表空间由段（sengment）、区（extent）、页（page）组成

ps：页在一些文档中又称块（block）

InnoDB存储引擎的逻辑存储结构大致如下：

　　

表空间（table space）

表空间分为了两种，这里简单的概括一下：

1. 独立表空间：每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容，默认情况下它的存储位置也是在表的位置之中
2. 共享表空间： Innodb的所有数据保存在一个单独的表空间里面，而这个表空间可以由很多个文件组成，一个表可以跨多个文件存在，所以其大小限制不再是文件大小的限制，而是其自身的限制。从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。

InnoDB把数据保存在表空间内，表空间可以看作是InnoDB存储引擎逻辑结构的最高层。本质上是一个由一个或多个磁盘文件组成的虚拟文件系统。InnoDB用表空间并不只是存储表和索引，还保存了回滚段、双写缓冲区等

段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等

• 索引段：存放 B + 树的非叶子节点的区的集合；
• 数据段：存放 B + 树的叶子节点的区的集合；
• 回滚段：存放的是回滚数据的区的集合，MVCC 利用了回滚段实现了多版本查询数据

区（extent）

区是由连续的页（Page）组成的空间，在任何情况下每个区大小都为1MB，为了保证页的连续性，InnoDB存储引擎每次从磁盘一次申请4-5个区。默认情况下，InnoDB存储引擎的页大小为16KB，即一个区中有64个连续的页（1MB／16KB=64）

InnoDB1.0.x版本开始引入压缩页，每个页的大小可以通过参数KEY_BLOCK_SIZE设置为2K、4K、8K，因此每个区对应的页尾512、256、128.

InnpDB1.2.x版本新增了参数innodb_page_size，通过该参数可以将默认页的大小设置为4K、8K，但是页中的数据不是压缩的

但是有时候为了节约磁盘容量的开销，创建表默认大小是96KB，区中是64个连续的页（对于一些小表）

页（Page）

页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB；InnoDB存储引擎从1.2.x版本碍事，可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size，不可以再次对其进行修改，除非通过mysqldump导入和导出操作来产生新的库。

innoDB存储引擎中，常见的页类型有：

1. 数据页（B-tree Node)
2. undo页（undo Log Page）
3. 系统页 （System Page）
4. 事物数据页 （Transaction System Page）
5. 插入缓冲位图页（Insert Buffer Bitmap）
6. 插入缓冲空闲列表页（Insert Buffer Free List）
7. 未压缩的二进制大对象页（Uncompressed BLOB Page）
8. 压缩的二进制大对象页 （compressed BLOB Page）

行（row）

InnoDB存储引擎是面向列的（row-oriented)，也就是说数据是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录

行格式

InnoDB 存储引擎支持不同的行格式(Compact、Redundant、Dynamic和Compressed)

以Compact行格式为例，下边是其存储示意图：

一条记录数据的存储可以分为两部分：额外信息和真实数据。 额外信息用来描述记录，分为变长字段列表、NULL值列表和记录头信息：

• 变长字段列表：MySQL支持的一些变长的数据类型，比如 VARCHAR(M)、TEXT 等。这些变长字段中存储的字节数量是不固定的，存储这个数据的真实字节数很有必要，这在解析数据的时候，数据库就知道从真实数据区域取出哪部分数据了。所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，形成一个变长字段长度列表，各变长字段数据占用的字节数按照列的顺序逆序存放。

我们在项目使用到 char 类型，比如使用 char(32) 来存储用户的密码，那么使用 char 类型的字段会被添加到变长字段列表中吗？答案是有可能会！因为项目中数据表使用的字符集是不确定的，最常用的 utf8mb4 使用1～4个变长字节来编码数据，中文和英文所占用的字节数是不同的。

对于项目中绝大多数使用存储量小的字段，比如说 varchar(32)，tinyint(4) 等，假设使用utf8字符集，这种字段存储的真实数据长度一定不会超过255，使用一个字节表示就可以了。但是如果字段的真实数据可能会超过了255该怎么表示呢？分为两种情况：当真实数据字节数小于127的时候，用1个字节表示，大于127的时候使用2个字节表示，也就是说字节的最高位表示该字节表示的是一个变长数据的一部分还是全部

• NULL值列表：数据表中的某些列可能存储NULL值，把这些NULL值都放到记录的真实数据中存储很浪费存储空间，所以Compact行格式把这些值为NULL的列统一管理起来，存储到NULL值列表中。在表中，主键列和使用NOT NULL修改过的列不允许存储NULL值，其他的列如果也没有NULL值，那么NULL值列表也就不存在了，也就是说NULL值列表并不总是存在的，上边说到的变长字段列表也是一样。标示一条记录中的数据是否为NULL使用一个二进制位就可以搞定了，1为NULL，0为非NULL，NULL值列表标示也是按照记录列的顺序逆序存放的。

问:变长字段长度列表、NULL值列表中的信息之所以按照列的顺序逆序存放？答:这样可以使记录中位置靠前的字段和它们对应的字段长度信息加载到内存中时，位置距离更近，可能会提高高速缓存的命中率。

• 记录头信息：记录头信息由5个固定的字节组成,5个字节是40个二进制位，这40个二进制位描述了记录的不同属性信息，这些信息非常重要

变长字段列表

varchar(n) 和 char(n) 的区别是什么，相信大家都非常清楚，char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的

所以，在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的

为了展示「变长字段长度列表」具体是怎么保存「变长字段的真实数据占用的字节数」，我们先创建这样一张表，字符集是 ascii（所以每一个字符占用的 1 字节），行格式是 Compact，t_user 表中 name 和 phone 字段都是变长字段：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

现在 t_user 表里有这三条记录：

接下来，我们看看看看这三条记录的行格式中的 「变长字段长度列表」是怎样存储的

先来看第一条记录：

• name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
• phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
• age 列和 id 列不是变长字段，所以这里不用管

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「 03 01」，而不是 「01 03」

同样的道理，我们也可以得出第二条记录的行格式中，「变长字段长度列表」里的内容是「 04 02」，如下图：

第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度

为什么「变长字段长度列表」的信息要按照逆序存放？

这个设计是有想法的，主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率

同样的道理， NULL 值列表的信息也需要逆序存放

每个数据库表的行格式都有「变长字段字节数列表」吗？

其实变长字段字节数列表不是必须的

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间

所以「变长字段长度列表」只出现在数据表有变长字段的时候

NULL 值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列

• 二进制位的值为1时，代表该列的值为NULL
• 二进制位的值为0时，代表该列的值不为NULL

另外，NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0

还是以 t_user 表的这三条记录作为例子：

接下来，我们看看看看这三条记录的行格式中的 NULL 值列表是怎样存储的

先来看第一条记录，第一条记录所有列都有值，不存在 NULL 值，所以用二进制来表示是酱紫的：

但是 InnoDB 是用整数字节的二进制位来表示 NULL 值列表的，现在不足 8 位，所以要在高位补 0，最终用二进制来表示是酱紫的：

所以，对于第一条数据，NULL 值列表用十六进制表示是 0x00

接下来看第二条记录，第二条记录 age 列是 NULL 值，所以，对于第二条数据，NULL值列表用十六进制表示是 0x04

最后第三条记录，第三条记录 phone 列 和 age 列是 NULL 值，所以，对于第三条数据，NULL 值列表用十六进制表示是 0x06

我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：

每个数据库表的行格式都有「NULL 值列表」吗？

NULL 值列表也不是必须的

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

「NULL 值列表」的空间不是固定 1 字节的

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推

记录头信息

记录头信息中包含的内容很多，我就不一一列举了，这里说几个比较重要的：

• delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
• next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
• record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：row_id、trx_id、roll_pointer，我们来看下这三个字段是什么。

• row_id

如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id不是必需的，占用 6 个字节

• trx_id

事务id，表示这个数据是由哪个事务生成的。 trx_id是必需的，占用 6 个字节

• roll_pointer

这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节

行溢出

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。在一般情况下，InnoDB 的数据都是存放在 「数据页」中。但是当发生行溢出时，溢出的数据会存放到「溢出页」中

当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。大致如下图所示

上面这个是 Compact 行格式在发生行溢出后的处理

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别

这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中，看起来就像下面这样：

索引

索引是一种用于快速查询和检索数据的数据结构，其本质可以看成是一种排序好的数据结构

索引的作用就相当于书的目录，方便我们快速查找书中的内容，所以索引是以空间换时间的设计思想。那换到数据库中，索引的定义就是帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录

索引底层数据结构存在很多种类型，常见的索引结构有: B 树， B+树 和 Hash、红黑树。在 MySQL 中，无论是 Innodb 还是 MyIsam，都使用了 B+树作为索引结构

分类

按照四个角度来分类索引：

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引
• 按「字段个数」分类：单列索引、联合索引

B+tree

B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，每个节点里的数据是按主键顺序存放的。在叶子节点中，包括了所有的索引值信息，并且每一个叶子节点都指向下一个叶子节点，形成一个链表。B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，千万级的表查询目标数据最多需要 3-4 次磁盘 I/O

B+树和B树相比：

• B+树所有关键码都存放在叶节点中，上层的非叶节点的关键码是其子树中最小关键码的复写
• B+树叶节点包含了全部关键码及指向相应数据记录存放地址的指针，且叶节点本身按关键码从小到大顺序连接
• B+树在搜索过程中，如果查询和内部节点的关键字一致，那么搜索过程不停止，而是继续向下搜索这个分支

优势：

• 单点查询：B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到。从平均时间代价来看，会比 B+ 树稍快一些。但是 B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，数据量相同的情况下，B+树的非叶子节点可以存放更多的索引，查询底层节点的磁盘 I/O次数会更少
• 插入和删除效率：B+ 树有大量的冗余节点，删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，删除非常快。B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及树的一条路径。B 树没有冗余节点，删除节点的时候非常复杂，可能涉及复杂的树的变形
• 范围查询：B+ 树所有叶子节点间有一个链表进行连接，而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，范围查询效率不如 B+ 树。B+ 树的插入和删除效率更高。存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如nosql的MongoDB

对比

• B+Tree 对比 B Tree：B+Tree 只在叶子节点存储数据，而 B 树 的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点
• B+Tree 对比 二叉树：对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数。在实际的应用当中， d 值是大于100的，即使数据达到千万级别时，B+Tree 的高度依然维持在 3 - 4 层左右，一次数据查询操作只需要做 3 - 4 次的磁盘 I/O 操作就能查询到。二叉树的每个父节点的儿子节点个数是 2 个，意味着其搜索复杂度为 O(logN)，二叉树检索到目标数据所经历的磁盘 I/O 次数要更多
• B+Tree 对比 Hash：Hash在做等值查询的时候效率高，搜索复杂度为 O(1)。但是 Hash 表不适合做范围查询

聚簇索引和非聚簇索引（二级索引）

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引（理由：数据一旦存储，顺序只能有一种）

聚簇索引和非聚簇索引的区别是:

• 聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点
• 二级索引的叶子节点存放的是主键值，而不是实际数据
• 通常情况下， 主键索引（聚簇索引）查询只会查一次，而非主键索引（非聚簇索引）需要回表查询多次。当然，如果是索引覆盖的话，查一次即可

注意：MyISAM无论主键索引还是二级索引都是非聚簇索引，而InnoDB的主键索引是聚簇索引，二级索引是非聚簇索引。我们自己建的索引基本都是非聚簇索引

因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个

InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键
• 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键

一张表只能有一个聚簇索引，那为了实现非主键字段的快速搜索，就引出了二级索引（非聚簇索引/辅助索引），它也是利用了 B+ 树的数据结构，但是二级索引的叶子节点存放的是主键值，不是实际数据

索引覆盖

索引覆盖（covering index ，或称为覆盖索引）即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表的产生减少了树的搜索次数，显著提升性能

联合索引

MySQL 可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照 name 排序，如果 name 相同，则按照 age 排序，如果 age 的值也相等，则按照 school 进行排序

当进行查询时，此时索引仅仅按照 name 严格有序，因此必须首先使用 name 字段进行等值查询，之后对于匹配到的列而言，其按照 age 字段严格有序，此时可以使用 age 字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整

最左前缀匹配原则

最左前缀匹配原则指的是，在使用联合索引时，MySQL 会根据联合索引中的字段顺序，从左到右依次到查询条件中去匹配，如果查询条件中存在与联合索引中最左侧字段相匹配的字段，则就会使用该字段过滤一批数据，直至联合索引中全部字段匹配完成，或者在执行过程中遇到范围查询（如 **>、<**）才会停止匹配。对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。所以，我们在使用联合索引时，可以将区分度高的字段放在最左边，这也可以过滤更多数据

索引下推

MySQL 5.6 引入了索引下推优化。默认开启，使用 SET optimizer_switch = ‘index_condition_pushdown=off’; 可以将其关闭

• 有了索引下推优化，可以在减少回表次数
• 在 InnoDB 中只针对二级索引有效

官方文档中给的例子和解释如下：

在 people_table中有一个二级索引(zipcode，lastname，address)，查询是SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

• 如果没有使用索引下推技术，则MySQL会通过 zipcode=’95054’ 从存储引擎中查询对应的数据，返回到 MySQL 服务端，然后MySQL服务端基于 lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’ 来判断数据是否符合条件
• 如果使用了索引下推技术，则MYSQL首先会返回符合 zipcode=’95054’ 的索引，然后根据 lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’ 来判断索引是否符合条件。如果符合条件，则根据该索引来定位对应的数据，如果不符合，则直接 reject 掉

索引创建注意

• 非空字段：应该指定列为NOT NULL，除非你想存储NULL。在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值；
• 取值离散大的字段：（变量各个取值之间的差异程度）的列放到联合索引的前面，可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多字段的离散程度高；
• 索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高
• 选择合适的字段创建索引
  • 不为 NULL 的字段：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0,1,true,false 这样语义较为清晰的短值或短字符作为替代
  • 被频繁查询的字段：我们创建索引的字段应该是查询操作非常频繁的字段
  • 被作为条件查询的字段：被作为 WHERE 条件查询的字段，应该被考虑建立索引
  • 频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
  • 被经常频繁用于连接的字段：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率

分析语句是否走索引

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 的输出格式如下：

mysql> EXPLAIN SELECT `score`,`name` FROM `cus_order` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | cus_order | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

各个字段的含义如下：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

索引失效

• 当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效
• 当我们在查询条件中对索引列使用函数，就会导致索引失效
• 当我们在查询条件中对索引列进行表达式计算，也是无法走索引的
• MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效
• 联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效
• 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效

执行计划

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式

执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以被命中、哪些索引实际会命中、每个数据表有多少行记录被查询等信息

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息

需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息

EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，使用起来非常简单，语法如下：

EXPLAIN + SELECT 查询语句；

我们简单来看下一条查询语句的执行计划：

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

可以看到，执行计划结果中共有 12 列，各列代表的含义总结如下表：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

EXPLAIN 字段

id

SELECT 标识符，是查询中 SELECT 的序号，用来标识整个查询中 SELELCT 语句的顺序。

id 如果相同，从上往下依次执行。id 不同，id 值越大，执行优先级越高，如果行引用其他行的并集结果，则该值可以为 NULL。

select_type

查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询，常见的值有：

• SIMPLE：简单查询，不包含 UNION 或者子查询。
• PRIMARY：查询中如果包含子查询或其他部分，外层的 SELECT 将被标记为 PRIMARY。
• SUBQUERY：子查询中的第一个 SELECT。
• UNION：在 UNION 语句中，UNION 之后出现的 SELECT。
• DERIVED：在 FROM 中出现的子查询将被标记为 DERIVED。
• UNION RESULT：UNION 查询的结果。

table

查询用到的表名，每行都有对应的表名，表名除了正常的表之外，也可能是以下列出的值：

• <unionM,N> : 本行引用了 id 为 M 和 N 的行的 UNION 结果；
• <derivedN> : 本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。 -<subqueryN> : 本行引用了 id 为 N 的表所产生的的物化子查询结果。

type（重要）

查询执行的类型，描述了查询是如何执行的。所有值的顺序从最优到最差排序为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含义如下：

• system：如果表使用的引擎对于表行数统计是精确的（如：MyISAM），且表中只有一行记录的情况下，访问方法是 system ，是 const 的一种特例。
• const：表中最多只有一行匹配的记录，一次查询就可以找到，常用于使用主键或唯一索引的所有字段作为查询条件。
• eq_ref：当连表查询时，前一张表的行在当前这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 方式，常用于使用主键或唯一索引的所有字段作为连表条件。
• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
• index_merge：当查询条件使用了多个索引时，表示开启了 Index Merge 优化，此时执行计划中的 key 列列出了使用到的索引。
• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。
• index：查询遍历了整棵索引树，与 ALL 类似，只不过扫描的是索引，而索引一般在内存中，速度更快。
• ALL：全表扫描。

possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。

key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

rows

rows 列表示根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• Using join buffer (Block Nested Loop)：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免

日志

MySQL 日志 主要包括错误日志、查询日志、慢查询日志、事务日志、二进制日志几大类。其中，比较重要的还要属 MySQL 二进制日志 binlog（归档日志）和 innoDB 事务日志 redo log（重做日志）和 undo log（回滚日志）

• undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
• redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
• binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制；

binlog

MySQL Server 层生成的日志，主要用于数据备份和主从复制

在完成一条更新操作后，Server 层会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件。binlog 文件是记录了所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作

记录格式

binlog 日志有三种格式，可以通过binlog_format参数指定。

• STATEMENT：每一条修改数据的 SQL 都会被记录到 binlog 中，主从复制中 slave 端再根据 SQL 语句重现

  STATEMENT 有动态函数的问题，比如用了 uuid 或者 now 这些函数，在主库上执行的结果并不是你在从库执行的结果，这种随时在变的函数会导致复制的数据不一致

• ROW：记录行数据最终被修改成什么样了，不会出现 STATEMENT 下动态函数的问题

  但 ROW 的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 STATEMENT 格式下只会记录一个 update 语句

• MIXED：包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式

写入机制

binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中

因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache

我们可以通过binlog_cache_size参数控制单个线程 binlog cache 大小，如果存储内容超过了这个参数，就要暂存到磁盘（Swap）

binlog日志刷盘流程如下

• 上图的 write，是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快
• 上图的 fsync，才是将数据持久化到磁盘的操作

write和fsync的时机，可以由参数sync_binlog控制，默认是0

为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync

虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失

为了安全起见，可以设置为1，表示每次提交事务都会执行fsync，就如同 redo log 日志刷盘流程 一样

最后还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write，但累积N个事务后才fsync

在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能

同样的，如果机器宕机，会丢失最近 N 个事务的 binlog 日志

undo log

undo log是Innodb存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和MVCC。

在事务没提交之前，Innodb会先记录更新前的数据到undo log中，回滚时利用 undo log 来进行回滚。每当进行一条记录进行操作(修改、删除、新增)时，要把回滚时需要的信息都记录到 undo log 里：原理是执行一条相反的操作。undo log 有两个参数：roll_pointer 指针和一个 trx_id 事务id，通过 trx_id 可以知道该记录是被哪个事务修改的；通过 roll_pointer 指针可以将这些 undo log 串成一个链表，形成版本链

innodb 存储引擎也通过 ReadView + undo log 实现 MVCC (多版本并发控制)

undo log 的作用

实现事务回滚，保障事务的原子性：如果出现了错误或者用户执行了 ROLLBACK 语句，可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态。

实现 MVCC关键因素之一：MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，在执行快照读的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

这里需要注意的一点是，由于查询操作（SELECT）并不会修改任何用户记录，所以在查询操作执行时，并不需要记录相应的undo log。undo log主要分为3种：

• Insert undo log ：插入一条记录时，至少要把这条记录的主键值记下来，之后回滚的时候只需要把这个主键值对应的记录删掉就好了。
• Update undo log：修改一条记录时，至少要把修改这条记录前的旧值都记录下来，这样之后回滚时再把这条记录更新为旧值就好了。
• Delete undo log：删除一条记录时，至少要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中就好了
  • 删除操作都只是设置一下老记录的DELETED_BIT，并不真正将过时的记录删除
  • 为了节省磁盘空间，InnoDB有专门的purge线程来清理DELETED_BIT为true的记录。为了不影响MVCC的正常工作，purge线程自己也维护了一个read view（这个read view相当于系统中最老活跃事务的read view）;如果某个记录的DELETED_BIT为true，并且DB_TRX_ID相对于purge线程的read view可见，那么这条记录一定是可以被安全清除的

在发生回滚时，就读取 undo log 里的数据，然后做原先相反操作。比如当 delete 一条记录时，undo log 中会把记录中的内容都记下来，然后执行回滚操作的时候，就读取 undo log 里的数据，然后进行 insert 操作

不同的操作，需要记录的内容也是不同的，所以不同类型的操作（修改、删除、新增）产生的 undo log 的格式也是不同的，具体的每一个操作的 undo log 的格式我就不详细介绍了，感兴趣的可以自己去查查

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

• 通过 trx_id 可以知道该记录是被哪个事务修改的
• 通过 roll_pointer 指针可以将这些 undo log 串成一个链表，这个链表就被称为版本链

另外，undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）

对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

• 「读提交」隔离级别是在每个 select 都会生成一个新的 Read View，也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务
• 「可重复读」隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录

这两个隔离级别实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见行，就会顺着 undo log 版本链里找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。具体的实现可以看这篇文章：MySQL 事务

因此，undo log 两大作用：

• 实现事务回滚，保障事务的原子性。事务处理过程中，如果出现了错误或者用户执 行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始之前的状态
• 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录

很多人疑问 undo log 是如何刷盘（持久化到磁盘）的？

undo log 和数据页的刷盘策略是一样的，都需要通过 redo log 保证持久化。

buffer pool 中有 undo 页，对 undo 页的修改也都会记录到 redo log。redo log 会每秒刷盘，提交事务时也会刷盘，数据页和 undo 页都是靠这个机制保证持久化的

redo log

redo log 是物理日志，记录了某个数据页做了什么修改，每当执行一个事务就会产生一条或者多条物理日志。在事务提交时，先将redo log持久化到磁盘即可，不需要等到将缓存在Buffer Pool里的脏页数据持久化到磁盘。当系统崩溃时，虽然脏页数据没有持久化但是redo log已经持久化，可以根据 redo log 的内容，将所有数据恢复到最新的状态

redo log 实现了事务中的持久性，主要用于掉电等故障恢复。发生更新的时候，InnoDB会先更新内存，同时标记为脏页，然后将本次对这个页的修改以redo log的形式记录下来。InnoDB引擎会在适当的时候，由后台线程将缓存在Buffer Pool的脏页刷新到磁盘里，实现WAL技术

WAL技术

WAL技术指的是，MySQL的写操作并不是立刻写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上

crash-safe

redo log + WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已提交的记录都不会丢失

MySQL 中数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool 中。

后续的查询都是先从 Buffer Pool 中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。

更新表数据的时候，也是如此，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。

然后会把“在某个数据页上做了什么修改”记录到重做日志缓存（redo log buffer）里，接着刷盘到 redo log 文件里。

图片笔误提示：第 4 步 “清空 redo log buffe 刷盘到 redo 日志中”这句话中的 buffe 应该是 buffer。

理想情况，事务一提交就会进行刷盘操作，但实际上，刷盘的时机是根据策略来进行的。

小贴士：每条 redo 记录由“表空间号+数据页号+偏移量+修改数据长度+具体修改的数据”组成

刷盘时机

InnoDB 存储引擎为 redo log 的刷盘策略提供了 innodb_flush_log_at_trx_commit 参数，它支持三种策略：

• 0：设置为 0 的时候，表示每次事务提交时不进行刷盘操作
• 1：设置为 1 的时候，表示每次事务提交时都将进行刷盘操作（默认值）
• 2：设置为 2 的时候，表示每次事务提交时都只把 redo log buffer 内容写入 page cache

innodb_flush_log_at_trx_commit 参数默认为 1 ，也就是说当事务提交时会调用 fsync 对 redo log 进行刷盘

另外，InnoDB 存储引擎有一个后台线程，每隔1 秒，就会把 redo log buffer 中的内容写到文件系统缓存（page cache），然后调用 fsync 刷盘。

也就是说，一个没有提交事务的 redo log 记录，也可能会刷盘。

为什么？

因为在事务执行过程 redo log 记录是会写入redo log buffer 中，这些 redo log 记录会被后台线程刷盘。

除了后台线程每秒1次的轮询操作，还有一种情况，当 redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动刷盘。

下面是不同刷盘策略的流程图

innodb_flush_log_at_trx_commit=0

为0时，如果MySQL挂了或宕机可能会有1秒数据的丢失。

innodb_flush_log_at_trx_commit=1

为1时， 只要事务提交成功，redo log记录就一定在硬盘里，不会有任何数据丢失

如果事务执行期间MySQL挂了或宕机，这部分日志丢了，但是事务并没有提交，所以日志丢了也不会有损失

innodb_flush_log_at_trx_commit=2

为2时， 只要事务提交成功，redo log buffer中的内容只写入文件系统缓存（page cache）。

如果仅仅只是MySQL挂了不会有任何数据丢失，但是宕机可能会有1秒数据的丢失。

日志文件组

硬盘上存储的 redo log 日志文件不只一个，而是以一个日志文件组的形式出现的，每个的redo日志文件大小都是一样的。

比如可以配置为一组4个文件，每个文件的大小是 1GB，整个 redo log 日志文件组可以记录4G的内容。

它采用的是环形数组形式，从头开始写，写到末尾又回到头循环写，如下图所示。

在个日志文件组中还有两个重要的属性，分别是 write pos、checkpoint

• write pos 是当前记录的位置，一边写一边后移
• checkpoint 是当前要擦除的位置，也是往后推移

每次刷盘 redo log 记录到日志文件组中，write pos 位置就会后移更新。

每次 MySQL 加载日志文件组恢复数据时，会清空加载过的 redo log 记录，并把 checkpoint 后移更新。

write pos 和 checkpoint 之间的还空着的部分可以用来写入新的 redo log 记录。

如果 write pos 追上 checkpoint ，表示日志文件组满了，这时候不能再写入新的 redo log 记录，MySQL 得停下来，清空一些记录，把 checkpoint 推进一下。

redo log 小结

相信大家都知道 redo log 的作用和它的刷盘时机、存储形式

现在我们来思考一个问题：只要每次把修改后的数据页直接刷盘不就好了，还有 redo log 什么事？

它们不都是刷盘么？差别在哪里？

1 Byte = 8bit
1 KB = 1024 Byte
1 MB = 1024 KB
1 GB = 1024 MB
1 TB = 1024 GB

实际上，数据页大小是16KB，刷盘比较耗时，可能就修改了数据页里的几 Byte 数据，有必要把完整的数据页刷盘吗？

而且数据页刷盘是随机写，因为一个数据页对应的位置可能在硬盘文件的随机位置，所以性能是很差。

如果是写 redo log，一行记录可能就占几十 Byte，只包含表空间号、数据页号、磁盘文件偏移 量、更新值，再加上是顺序写，所以刷盘速度很快。

所以用 redo log 形式记录修改内容，性能会远远超过刷数据页的方式，这也让数据库的并发能力更强。

其实内存的数据页在一定时机也会刷盘，我们把这称为页合并，讲 Buffer Pool的时候会对这块细说

两阶段提交

redo log（重做日志）让 InnoDB 存储引擎拥有了崩溃恢复能力

binlog（归档日志）保证了 MySQL 集群架构的数据一致性

虽然它们都属于持久化的保证，但是侧重点不同

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，redo log在事务执行过程中可以不断写入，而binlog只有在提交事务时才写入，所以redo log与binlog的写入时机不一样。

回到正题，redo log与binlog两份日志之间的逻辑不一致，会出现什么问题？

我们以update语句为例，假设id=2的记录，字段c值是0，把字段c值更新成1，SQL语句为update T set c=1 where id=2。

假设执行过程中写完redo log日志后，binlog日志写期间发生了异常，会出现什么情况呢？

由于binlog没写完就异常，这时候binlog里面没有对应的修改记录。因此，之后用binlog日志恢复数据时，就会少这一次更新，恢复出来的这一行c值是0，而原库因为redo log日志恢复，这一行c值是1，最终数据不一致。

为了解决两份日志之间的逻辑一致问题，InnoDB存储引擎使用两阶段提交方案。

原理很简单，将redo log的写入拆成了两个步骤prepare和commit，这就是两阶段提交。

使用两阶段提交后，写入binlog时发生异常也不会有影响，因为MySQL根据redo log日志恢复数据时，发现redo log还处于prepare阶段，并且没有对应binlog日志，就会回滚该事务。

再看一个场景，redo log设置commit阶段发生异常，那会不会回滚事务呢？

并不会回滚事务，它会执行上图框住的逻辑，虽然redo log是处于prepare阶段，但是能通过事务id找到对应的binlog日志，所以MySQL认为是完整的，就会提交事务恢复数据