layout	title	date	categories	tags	comments	copyrights	draft
post	MySQL 从实践到原理	2025-01-19 00:00:00 +0800	工具	mysql sql db	true	原创	true

MySQL 是一个 SQL 数据库。

MySQL 使用基础

数据库管理

选择数据库

首先需要登录 MySQL 服务器：

$ mysql -u root -p
Enter password: ********

使用以下命令查看可选数据库：

SHOW DATABASES;

使用以下命令选择并进入数据库：

USE database_name;

进入后，可以使用下面的命令查看当前数据库：

SELECT DATABASE();

当然，也可以在登录时直接选择数据库：

$ mysql -u root -D database_name -p

创建数据库

使用以下命令创建数据库：

CREATE DATABASE database_name;

这里有一些可选参数：

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

IF NOT EXISTS 表示如果数据库已经存在，就不创建。CHARACTER SET 和 COLLATE 分别表示数据库的字符集和排序规则。

删除数据库

使用以下命令删除数据库：

DROP DATABASE [IF EXISTS] database_name;

在 MySQL 中，DATABASE 和 SCHEMA 是同义词，可以互换使用。

表管理

创建表

使用以下命令创建表：

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
) [table_constraints];

这里有几个可选项：

table_constraints 是对表的约束，比如：
- ENGINE：存储引擎
  
  可以使用下面的命令查看 MySQL 支持的存储引擎：
```
SHOW ENGINES;
```
  默认情况下，MySQL 使用 InnoDB 存储引擎。
- CHARSET：字符集
  
  可以使用下面的命令查看 MySQL 支持的字符集：
```
SHOW CHARACTER SET;
```
  常用的包括 utf8、utf8mb4 等。
- COLLATE：排序规则
  
  可以使用下面的命令查看 MySQL 支持的排序规则：
```
SHOW COLLATION;
```
  常用的包括 utf8_general_ci、utf8mb4_general_ci 等。
constraints 是对列的约束，比如：
- NOT NULL：列不允许为空
- UNIQUE：列的值必须唯一
- PRIMARY KEY：列是主键
  
  主键需要唯一且非空。如果没有指定主键，InnoDB 存储引擎会自动创建一个隐藏的主键。如果需要多个列作为主键，可以使用下面的语法：
```
PRIMARY KEY (column1, column2, ...);
```
- FOREIGN KEY：列是外键
  
  外键用于关联两个表。如果一张表中有一个非主键的字段指向另外一张表的主键，那么将该字段称之为外键。外键约束可以保证两个表之间的数据一致性。外键约束可以指定 ON DELETE 和 ON UPDATE 触发器，比如：
```
FOREIGN KEY (column)
    REFERENCES other_table(column)
    ON DELETE CASCADE
    ON UPDATE RESTRICT;
```
  ON DELETE 和 ON UPDATE 可以有以下选项：
  - RESTRICT：拒绝删除或者更新父表中的行
  - CASCADE：删除或者更新父表中的行，同时删除或者更新子表中的行
  - SET NULL：删除或者更新父表中的行，同时将子表中的外键列设为 NULL
- CHECK：检查约束
  
  CHECK 约束用于检查列的值是否满足一定的条件。比如：
```
CHECK (column > 0);
```
- DEFAULT：默认值
- AUTO_INCREMENT：自增
  
  AUTO_INCREMENT 用于自动递增列的值。如果没有指定初始值和步长，那么默认初始值是 1，步长是 1。

datatype 是列的数据类型，主要包括：

字符串类型：

数据类型	描述	最大长度（byte）
`CHAR`	定长字符串	255
`VARCHAR`	变长字符串	65535
`BINARY`	定长二进制字符串	255
`VARBINARY`	变长二进制字符串	65535
`TINYBLOB`	非常小的二进制大对象	255
`BLOB`	二进制大对象	65535
`MEDIUMBLOB`	中等大小的二进制大对象	16777215
`LONGBLOB`	大型二进制大对象	4294967295
`TINYTEXT`	非常小的文本字符串	255
`TEXT`	文本字符串	65535
`MEDIUMTEXT`	中等大小的文本字符串	16777215
`LONGTEXT`	大型文本字符串	4294967295
`ENUM('value1', 'value2', ...)`	枚举类型	65535
`SET('value1', 'value2', ...)`	集合类型	64

数值类型：

数据类型	描述	占据空间（byte）
`TINYINT`	微整型	1
`SMALLINT`	小整型	2
`MEDIUMINT`	中整型	3
`INT` / `INTEGER`	整型	4
`BIGINT`	大整型	8
`FLOAT`	浮点型	4
`DOUBLE`	双精度浮点型	8
`DECIMAL` / `DEC`	定点数	依赖于精度和小数位数
`BIT`	位类型	依赖于位数
`BOOL` / `BOOLEAN`	布尔类型	1

日期和时间类型：

数据类型	描述	格式
`DATE`	日期	`YYYY-MM-DD`
`TIME`	时间	`HH:MM:SS`
`DATETIME`	日期和时间	`YYYY-MM-DD HH:MM:SS`
`TIMESTAMP`	时间戳	`1970-01-01 00:00:00 UTC` 以来的秒数
`YEAR`	年份	`YYYY`

一个完整的例子如下：

CREATE TABLE IF NOT EXISTS table_name (
    id INT AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    age INT,
    is_student BOOLEAN DEFAULT TRUE,
    PRIMARY KEY (name, age),
    FOREIGN KEY (id)
        REFERENCES other_table(id)
        ON UPDATE RESTRICT
        ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;

查询

`SELECT FROM`

SELECT 语句用于查询数据。SELECT 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name;

SELECT 语句可以查询表中的所有列，也可以查询表中的指定列。如果要查询表中的所有列，可以使用 * 通配符：

SELECT
    *
FROM
    table_name;

`SELECT`

SELECT 并不一定要查询表中的数据，也可以查询表达式的值。比如，我们可以查询表达式、时间、字符串等。

SELECT
    1 + 1;

SELECT
    NOW();

SELECT
    CONCAT('Hello', ' ', 'World');

SELECT 还可以用于给列名或者表达式取别名：

SELECT
    column1
AS
    alias1;

SELECT
    CONCAT('Hello', ' ', 'World')
AS
    'greeting message';

排序

`ORDER BY`

ORDER BY 语句用于对查询结果进行排序。ORDER BY 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name
ORDER BY
    column1 [ASC|DESC],
    column2 [ASC|DESC], ...;

ORDER BY 语句默认是升序排序，可以省略 ASC。如果要降序排序，则需要使用 DESC。

在排序中，NULL 被认为是最小的值。如果要将 NULL 作为最大的，可以使用 IS NULL 和 IS NOT NULL：

SELECT
    column1
FROM
    table_name
ORDER BY
    column1 IS NULL,
    column1;

`FIELD`

FIELD 函数用于查找某个字段的值在一个列表中的位置。FIELD 函数的基本语法如下：

SELECT
    FIELD(value, value1, value2, ...);

例如：

SELECT
    FIELD('apple', 'banana', 'apple', 'orange');
+---------------------------------------------+
| FIELD('apple', 'banana', 'apple', 'orange') |
+---------------------------------------------+
|                                           2 |
+---------------------------------------------+

这表明 'apple' 在列表中的第 2 个位置。

FIELD 可被用于排序：

SELECT
    *
FROM
    table_name
ORDER BY
    FIELD(column1, value1, value2, ...);

这会让 column1 的值按照 value1、value2 等的顺序排序。

过滤

`WHERE`

WHERE 语句用于过滤数据。WHERE 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name
WHERE
    condition;

search_condition 可以是一个表达式，也可以是一个逻辑表达式。逻辑表达式可以使用 AND、OR、NOT 来连接。

WHERE 语句可以使用比较运算符、逻辑运算符、IN、BETWEEN、LIKE 等。

IN 用于判断某个字段的值是否在一个列表中。IN 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name
WHERE
    column1 IN (value1, value2, ...);

BETWEEN 用于判断某个字段的值是否在一个范围内。BETWEEN 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name
WHERE
    column1 BETWEEN value1 AND value2;

LIKE 用于判断某个字段的值是否匹配一个模式。LIKE 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name
WHERE
    column1 LIKE pattern;

pattern 可以使用 % 通配符来匹配任意字符，_ 通配符来匹配一个字符。

LIMIT 用于限制查询结果的数量。LIMIT 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table_name
LIMIT
    [offset,] row_count;

LIMIT 语句可以有一个或者两个参数。如果有一个参数，那么这个参数表示返回的行数；如果有两个参数，那么第一个参数表示偏移量，第二个参数表示返回的行数。

例如，如果 LIMIT 5，那么返回的行数是 5；如果 LIMIT 5, 10，那么返回的行数是 10，从第 6 行开始。

`SELECT DISTINCT`

SELECT DISTINCT 用于返回唯一不同的值。SELECT DISTINCT 语句的基本语法如下：

SELECT DISTINCT
    column1,
    column2,
    ...
FROM
    table_name;

它的作用相当于给查询结果去重。

连接

`JOIN`

JOIN 用于连接两个或者多个表。JOIN 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table1
JOIN table2 ON
    join_condition;

这里的 join_condition 是连接条件，通常会类似 table1.column = table2.column。

如果使用了相同的列名，那么可以简写为：

SELECT
    column1,
    column2,
    ...
FROM
    table1
JOIN table2 USING (column);

JOIN 有多种类型：

INNER JOIN：返回两个表中的交集，即两个表中都有的数据。

默认情况下，JOIN 就是 INNER JOIN。
LEFT JOIN：返回左表中的所有数据，以及右表中的交集，即左表中的数据都会返回，右表中的数据只有左表中有的才会返回，否则返回 NULL。

我们可以使用 IS NULL 来判断右表中的数据是否存在，这样求出在左表中有而在右表中没有的数据：
```
SELECT
    column1,
    column2,
    ...
FROM
    table1
LEFT JOIN table2 ON
    table1.column = table2.column
WHERE
    table2.column IS NULL;
```
RIGHT JOIN：返回右表中的所有数据，以及左表中的交集，即右表中的数据都会返回，左表中的数据只有右表中有的才会返回，否则返回 NULL。

我们同样可以使用 IS NULL 来判断左表中的数据是否存在，求出在右表中有而在左表中没有的数据。
CROSS JOIN：返回两个表的笛卡尔积。

这种情况下，两个表中的每一行都会和另一个表中的每一行组合，返回的结果是两个表的行数的乘积。它没有 ON 子句。
SELF JOIN：自连接。

自连接没有专门的关键字，需要使用上述的 LEFT JOIN、RIGHT JOIN、INNER JOIN 等。自连接时，需要给表取别名以区分。

聚合

`GROUP BY`

GROUP BY 用于对查询结果进行分组。GROUP BY 语句的基本语法如下：

SELECT
    column1,
    column2,
    aggregate_function(column3),
    ...
FROM
    table_name
GROUP BY
    column1,
    column2,
    ...;

GROUP BY 可以实现类似 SELECT DISTINCT 的功能：

SELECT
    column1
FROM
    table_name
GROUP BY
    column1;

GROUP BY 也可以和聚合函数 COUNT、SUM、AVG、MAX、MIN 等一起使用。

SELECT
    column1,
    COUNT(*)
FROM
    table_name
GROUP BY
    column1;

这会返回 column1 的值以及每个值出现的次数。

我们可以使用 HAVING 子句来过滤分组后的结果：

SELECT
    column1,
    COUNT(*)
FROM
    table_name
GROUP BY
    column1
HAVING
    COUNT(*) > 5;

需要注意，HAVING 会在 SELECT 之前执行，因此不能使用 SELECT 中的别名。

值得注意的是，从 MySQL 8.0 开始，GROUP BY 不再默认排序。

`ROLLUP`

ROLLUP 是 GROUP BY 的扩展，用于生成多维的汇总数据。给定需要汇总的列，ROLLUP 会生成这些列中所有可能的子列的汇总数据。ROLLUP 语句的基本语法如下：

SELECT
    column1,
    column2,
    COUNT(*)
FROM
    table_name
GROUP BY
    column1,
    column2
WITH ROLLUP;

集合

`UNION`

UNION 用于合并两个或者多个查询的结果集。UNION 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table1
UNION [DISTINCT | ALL]
SELECT
    column1,
    column2,
    ...
FROM
    table2;

UNION 默认会去重，如果不想去重，可以使用 ALL。

和 JOIN 相比，UNION 会将内容纵向合并，而 JOIN 会将内容横向合并。

需要注意的是，UNION 合并的两个查询的列数和列类型必须一致。

`EXCEPT`

EXCEPT 在 MySQL 8.0 中正式被支持。EXCEPT 用于返回在第一个查询中有而在第二个查询中没有的数据。EXCEPT 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table1
EXCEPT [ALL | DISTINCT]
SELECT
    column1,
    column2,
    ...
FROM
    table2;

EXCEPT 默认会去重，如果不想去重，可以使用 ALL。

`INTERSECT`

INTERSECT 在 MySQL 8.0 中正式被支持。INTERSECT 用于返回在两个查询中都有的数据。INTERSECT 语句的基本语法如下：

SELECT
    column1,
    column2,
    ...
FROM
    table1
INTERSECT [ALL | DISTINCT]
SELECT
    column1,
    column2,
    ...
FROM
    table2;

INTERSECT 默认会去重，如果不想去重，可以使用 ALL。

存储格式

MySQL 有多种存储引擎，每种存储引擎都有自己的存储格式。存储引擎是 MySQL 的一个组件，负责存储数据。MySQL 5.5 之前，MySQL 默认的存储引擎是 MyISAM，而 MySQL 5.5 之后，MySQL 默认的存储引擎是 InnoDB。下文将以 InnoDB 为例。

我们可以查看 MySQL 的数据库文件存储位置：

mysql> SELECT @@global.datadir;
+--------------------------+
| @@global.datadir         |
+--------------------------+
| ...path_to_storage/Data/ |
+--------------------------+

我们创建的每一个数据库都会在 datadir 目录下创建一个文件夹，文件夹的名字就是数据库的名字：

$ cd world
$ ls
Mode   Length Name
----   ------ ----
-a---  835584 city.ibd
-a---  196608 country.ibd
-a---  245760 countrylanguage.ibd

在 MySQL 8.0 之前，每一个表都会有一个 .frm 文件和一个 .ibd 文件。.frm 文件存储了表的元数据，比如表的结构、字段类型等。.ibd 文件存储了表的数据。此外，还有一个 db.opt 文件，存储了数据库的元数据，比如数据库的字符集、排序规则等。

然而，MySQL 8.0 之后，.frm 文件被移除了，表的元数据被作为 SDI（System Data Interface）存储在 .ibd 文件中。我们可以使用 ibd2sdi 工具来查看 SDI 文件：

ibd2sdi city.ibd > city.json

你会看到 city.json 文件中存储了表的元数据。

现在，我们抛开 SDI 不谈，只来看看表的数据是如何存储的。

行格式

InnoDB 存储引擎最基本的单元是行，也就是一条记录。InnoDB 存储引擎有四种行格式：

Redundant：这是最早的行格式，不支持行压缩。用于 MySQL 5.0 之前。
Compact：这是默认的行格式，支持行压缩。用于 MySQL 5.7 之前。
Dynamic：这是支持动态行格式，支持行压缩。MySQL 5.7 之后默认使用这个行格式。
Compressed：这是支持压缩行格式，支持行压缩。

我们可以查看表的行格式：

mysql> SHOW TABLE STATUS LIKE 'city';
+----+------+-------+----------+---+
|Name|Engine|Version|Row_format|...|
+----+------+-------+----------+---+
|city|InnoDB|     10|Dynamic   |...|
+----+------+-------+----------+---+

`Compact`

Compact 行格式的数据存储结构如下：

名称	大小
Variable Field Lengths	每个变长字段 1-2 bytes
Nullable Field Bitmap	每个 `NULL` 字段 1 bit
Info Flags	4 bits
Number of records owned	4 bits
Order	13 bits
Record Type	3 bits
Next Record Offset	2 bits
Row ID	6 bytes
Transaction ID	6 bytes
Roll Pointer	7 bytes
Data

Variable Field Lengths

Variable Field Lengths 存储了每个变长字段（VARCHAR、TEXT 等）的长度。每个变长字段的长度占用 1-2 个字节。

在该列表中，各个变长字段的长度是按照逆序存储，即最后一个变长字段的长度放在最前面，第一个变长字段的长度放在最后面。这样做的好处是使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，提高了命中率。

值得注意的是，如果某个字段是变长的，但记录中的该字段为 NULL，那么它并不会保存在 Variable Field Lengths 中。如果一个变长字段也没有，那么 Variable Field Lengths 也不会存在。

Nullable Field Bitmap

Nullable Field Bitmap 存储了哪些字段的值为 NULL。该列表使用二进制位来表示字段是否为 NULL，每个字段占用一个二进制位。如果某个字段的值为 NULL，则对应的二进制位为 1，否则为 0。

同样的，各个字段的 NULL 值信息是按照逆序存储的，即最后一个字段的 NULL 值信息放在最前面，第一个字段的 NULL 值信息放在最后面。

最后，Nullable Field Bitmap 还会在高位补 0 填充到一个字节的整数倍。如果表中所有字段都不为 NULL，那么 Nullable Field Bitmap 也不会存在。

Fixed Field Data

Fixed Field Data 存储了记录的一些元数据，比如记录的长度、记录的格式等。其占据了 5 个字节。

Info Flags：占据 4 个 bit，用于存储记录的格式信息。目前有两位被使用，分别是：
- min_rec (1)：表示此记录是 B+Tree 非叶级中的最小记录
- deleted (2)：表示此记录已标记为删除
Number of records owned：占据 4 个 bit，用于存储记录的个数
Order：占据 13 个 bit，用于存储记录插入堆中的顺序。堆中的最小值始终为 0，最大值为 1，用户插入的记录从 2 开始递增
Record Type：占据 3 个 bit，用于存储记录的类型。一共有 4 种类型：
- conventional (0)
- node pointer (1)
- infimum (2)
- supremum (3)
Next Record Offset：占据 2 个 bit，按键升序排列时，用于存储当前记录到页面内下一记录原点的相对偏移量

Row ID

Row ID 是一个 6 字节的字段，用于存储记录的 ID。Row ID 是一个递增的数字，每次插入一条记录，Row ID 会递增 1。

如果在建表时指定了主键或者唯一索引，那么 Row ID 就不存在。

Transaction ID

Transaction ID 是一个 6 字节的字段，用于存储事务的 ID，表明记录是由哪个事务插入的。这个字段在 InnoDB 中用于 MVCC（多版本并发控制）。

Roll Pointer

Roll Pointer 是一个 7 字节的字段，用于存储回滚指针。回滚指针是一个指向回滚段的指针，用于回滚记录。

`Dynamic` 和 `Compressed`

这两者和 Compact 的最主要的区别在于对于溢出字段的处理。

一个页的默认大小是 16KB，如果一条记录的长度超过了 16KB，是无法被成功插入的。但是，对于 TEXT、BLOB 等字段，其长度很大可能会超过 16KB。对于这种字段，Compact 格式会存储部分数据在行中，然后接上一个指针指向溢出页。相比之下，Dynamic 和 Compressed 格式会将整个字段存储在溢出页中，这一方法减小了行的长度，提高了查询效率，适合存储大字段，但是也会增加 I/O 次数。

Dynamic 和 Compressed 的区别在于压缩。Compressed 行格式会使用压缩算法对数据进行压缩，以减小存储空间。这一方法能够减小存储空间，但是会增加 CPU 的计算开销，影响性能。

表空间

上文我们已经介绍了表的行格式，现在我们来看看如何将这些行聚合在一起。

页

为了减少 I/O 次数，InnoDB 的读写均以页为单位。每个页的默认大小是 16KB，可以通过 innodb_page_size 参数来设置：

mysql> SELECT @@global.innodb_page_size;
+---------------------------+
| @@global.innodb_page_size |
+---------------------------+
|                     16384 |
+---------------------------+

一个数据页的结构如下：

名称	大小	描述
File Header	38 bytes	文件头
Page Header	56 bytes	页头
Infimum + Supremum	26 bytes	页中的最小记录和最大记录
User Records		行记录
Free Space		空闲空间
Page Directory		记录索引
File Trailer	8 bytes	校验和

File Header 中存储了两个指针，分别指向页的上一个页和下一个页，使得多个页构成了一个双向链表。

在行记录部分，数据按照主键顺序存储，按照我们上文的讨论，其访问类似于单向链表。为了加速遍历，InnoDB 使用了页目录。页目录将页内数据分为多组，并使用指针指向每组的最后一个记录。这样，可以通过二分法快速找到主键所在的组，然后再线性查找主键。

第一个分组中只有一个记录，称为 infimum；最后一个分组有 1-8 个记录，其中最后一个记录称为 supremum。infimum 记录的主键值是最小的，supremum 记录的主键值是最大的。其余分组中有 4-8 个记录。

区

上面讲到，不同的页通过双向链表连接在一起。然而，双向链表的各项在物理上并不是连续的，在遍历时会产生大量的随机 I/O。为了解决这个问题，InnoDB 引入了区的概念以快速定位页。

区是页的集合，每个区的大小为 1MB，包含 64 个页。

区的结构为一个 B+ 树。这棵树的每个节点都是页，叶子节点是存放了数据，非叶子节点存放了目录项作为索引。所有叶子节点构成了一个双向链表，这样可以很方便地完成范围查询。

区使用的 B+ 树可分为两种：

Clustered Index：叶子节点存放了数据
Secondary Index：叶子节点存放了主键值

表一定有一个 Clustered Index。如果该表有主键，则主键就是 Clustered Index 的索引键。如果没有主键，则 InnoDB 会选择一个唯一非空列作为 Clustered Index 的索引键。如果表没有唯一非空列，InnoDB 会生成一个隐藏的自增 ID 作为 Clustered Index 的索引键。

Secondary Index 也是 B+ 树，其叶子节点存放了主键值，而索引键则是二级索引。这样，当我们使用二级索引查询时，InnoDB 会先根据二级索引在 Secondary Index 中找到主键值，然后再根据主键值在 Clustered Index 中找到数据。这一过程被称为回表。当然，如果需要的查询结果正是主键的值，那么就不需要回表，这种查询被称为覆盖索引。

段

以上所述的多个区构成了一个段。如果一个段中的区被用于存储数据（也就是 B+ 树中的叶子节点），那么这个段被称为数据段；如果存储的是索引（也就是 B+ 树中的非叶子节点），那么这个段被称为索引段。此外，还有回滚段、临时段等。

执行流程

当我们向 MySQL 发送 SQL 语句时，MySQL 的执行流程如下图所示：

客户端发送语句

用户要想使用 MySQL，首先要连接到 MySQL 服务器。

$ mysql -u root -p
Enter password: ********

这个连接是通过 TCP/IP 协议来实现的。MySQL 服务器监听一个端口，通常是 3306。当用户连接到 MySQL 服务器时，MySQL 服务器会为用户创建一个线程。这个线程会处理用户发送的 SQL 语句。

MySQL 的不同用户可以有不同的权限。MySQL 会在执行 SQL 语句之前，根据用户的权限来决定用户是否有权执行。这一权限在连接时就会被确定，即便之后管理员修改了用户的权限，也不会对当前的连接产生影响。

用户的连接情况可以使用 SHOW PROCESSLIST 来查看。如果用户在连接后发送了 SQL 语句，那么这个连接会被标记为 Query 状态。相反，如果一个用户连接后从没发送过 SQL 语句，那么这个连接会被标记为 Sleep 状态。

mysql> SHOW PROCESSLIST;
+----+----------+---------+------+-------+------------------+
| Id | User     | Command | Time | State | Info             |
+----+----------+---------+------+-------+------------------+
| 15 | root     | Query   |    0 | init  | SHOW PROCESSLIST |
| 16 | ch3nyang | Sleep   |   60 |       | NULL             |
+----+----------+---------+------+-------+------------------+

可以看到，root 用户正在执行 SHOW PROCESSLIST 语句，而 ch3nyang 用户正在 Sleep 状态，已经有 60 秒了。

MySQL 定义了最大的 Sleep 时常，由 wait_timeout 参数控制。如果一个连接在 wait_timeout 时间内没有发送 SQL 语句，那么这个连接会被 MySQL 服务器断开。wait_timeout 默认是 28800 秒，即 8 小时。

mysql> SELECT @@global.wait_timeout;
+-----------------------+
| @@global.wait_timeout |
+-----------------------+
|                 28800 |
+-----------------------+

我们可以手动设置 wait_timeout 参数：

mysql> SET GLOBAL wait_timeout=28800;

对于长时间不使用的连接，我们也可以使用 KILL 命令来手动断开：

mysql> KILL 16;

MySQL 对最大连接数也有限制，由 max_connections 参数控制。如果 MySQL 服务器的连接数达到了 max_connections，那么新的连接会被拒绝。

mysql> SELECT @@global.max_connections;
+--------------------------+
| @@global.max_connections |
+--------------------------+
|                      151 |
+--------------------------+

客户端和服务器之间的 TCP/IP 连接分为长连接和短连接。MySQL 默认使用长连接，其可以减少连接的建立和断开的开销，但是会占用服务器的资源。为了单个长连接避免占用内存过多，可以使用 mysql_reset_connection() 来重置连接。如果你是在命令行中，只需断开再重新连接即可。

查询缓存

这一步在 MySQL 8.0 版本中已经被移除。

在这之前，MySQL 会检查查询缓存，看看是否有相同的 SELECT 语句已经被执行过。如果有，MySQL 会直接返回查询缓存中的结果，而不会再执行一次查询。

然而，只要一个表发生了变化，这个表上的所有查询缓存都会被清空。因此，当更新较为频繁时，查询缓存大概率不会命中。这使得查询缓存只对读多写少的场景有用，对于高并发场景，查询缓存会导致性能下降，这也是为什么 MySQL 8.0 移除了查询缓存。

解析器

这一步 MySQL 会对 SQL 语句进行解析，检查 SQL 语句是否符合 MySQL 的语法规范。如果 SQL 语句不符合规范，MySQL 会返回一个错误。

其基本就是编译原理里的一些东西，比如词法分析、语法分析等。此处不再赘述。

执行查询

预处理器会对 SQL 语句进行一些处理，比如检查表名和列名是否存在，检查用户是否有权限等，同时还会对 SQL 语句进行一些处理，比如将 SELECT * 扩展为 SELECT column1, column2, ...。

紧接着，MySQL 会调用优化器来生成一个执行计划。优化器会生成多个执行计划，然后选择成本最低的执行计划。例如，如果一个查询有多个索引可以使用，那么优化器会选择成本最低的索引。

为了查看 MySQL 生成的执行计划，我们可以在 SQL 语句前加上 EXPLAIN 关键字：

mysql> EXPLAIN SELECT * FROM city WHERE id = 1899;
+--+---+-----+-------------+-------+---+
|id|...|type |possible_keys|key    |...|
+--+---+-----+-------------+-------+---+
| 1|...|const|PRIMARY      |PRIMARY|...|
+--+---+-----+-------------+-------+---+

可以看到，MySQL 会返回一个表格，其中包含了查询的执行计划。这个表格的列的含义如下：

id：查询中的每个查询块都有一个唯一的标识符。
select_type：查询块的类型，比如 SIMPLE、PRIMARY、SUBQUERY 等。
table：查询块中的表名。
type：MySQL 在表中查找行的方式，比如 const、eq_ref、ref、range、index、ALL 等。
possible_keys：可能使用的索引。
key：实际使用的索引。
key_len：索引的长度。
ref：比较的列。
rows：MySQL 估计要检查的行数。
Extra：额外的信息。

如果查询需要全表扫描，那么 type 会是 ALL，key 会是 NULL：

mysql> EXPLAIN SELECT * FROM city WHERE name Like '%nanjing%';
+--+-----------+---+-----+-------------+---+---+
|id|select_type|...|type |possible_keys|key|...|
+--+-----------+---+-----+-------------+---+---+
| 1|SIMPLE     |...|ALL  |NULL         |NULL|...|
+--+-----------+---+-----+-------------+---+---+

我们可以再建立一个二级索引，然后再查看执行计划：

mysql> CREATE INDEX idx_population ON city(population);
mysql> EXPLAIN SELECT * FROM city WHERE id > 1 AND population > 10000000;
+--+---+-----+----------------------+--------------+---+
|id|...|type |possible_keys         |key           |...|
+--+---+-----+----------------------+--------------+---+
| 1|...|range|PRIMARY,idx_population|idx_population|...|
+--+---+-----+----------------------+--------------+---+

这里，我们看到有 PRIMARY 和 idx_population 两个索引可以使用，MySQL 选择了 idx_population 索引，且 type 是 range。

接下来，执行计划会被交由执行器执行。执行器会调用存储引擎的接口来执行查询。存储引擎会返回查询的结果给执行器，然后执行器会将结果返回给客户端。在这个过程中需要注意的是，每当存储引擎匹配到一个键值后就会立即发给执行器，执行器也会根据过滤条件过滤后立即发回客户端，然后再向存储引擎发送指令让其继续搜索，直到全部搜索完。

这个过程包含了三种情况：

如果 key 是 PRIMARY，那么存储引擎会直接通过 Clustered Index 来查找数据。存储引擎根据主键找到数据并返回给执行器，执行器根据条件过滤数据后发回给客户端。
如果 key 是 NULL，那么存储引擎会通过全表扫描来查找数据。存储引擎会遍历整个表，逐一判断每一行是否符合条件。每当找到一行符合条件，就会立即将这一行发回给执行器。当搜索到表的末尾，或者找到了 limit 条记录，就会结束查询。
如果 key 是二级索引，那么存储引擎会执行回表操作。存储引擎首先根据二级索引找到主键，然后再根据主键找到数据。

对于联合索引的情况，从 MySQL 5.6 开始，引入了索引下推优化。在没有索引下推优化时，对于查询联合索引中的键但联合索引失效的情况，存储引擎会把失效键的过滤工作交给执行器去完成。而当使用了索引下推优化后，则成了存储引擎的工作，这有效减少了回表的次数，优化了性能。

Files

2025-01-19-MySQL从实践到原理.md

Latest commit

History

2025-01-19-MySQL从实践到原理.md

File metadata and controls

MySQL 使用基础

数据库管理

选择数据库

创建数据库

删除数据库

表管理

创建表

查询

SELECT FROM

SELECT

排序

ORDER BY

FIELD

过滤

WHERE

SELECT DISTINCT

连接

JOIN

聚合

GROUP BY

ROLLUP

集合

UNION

EXCEPT

INTERSECT