MySQL查询路径选择

大家好，我是大头，98年，职高毕业，上市公司架构师，大厂资深开发，管理过10人团队，我是如何做到的呢？

这离不开持续学习的能力，而其中最重要的当然是数据库技术了！

对于所有开发来说，都离不开数据库，因为所有的数据都是要存储的。

关注我一起学习！文末有惊喜哦！

什么是MySQL查询路径？

MySQL查询路径是指查询优化器在执行查询时所选择的具体执行步骤和顺序。查询优化器的目标是找到最高效的方式来执行查询，以最小化资源消耗（如CPU、I/O、内存等）和查询响应时间。查询路径包括以下几个关键阶段：

1. 解析（Parsing）：

• 将SQL语句解析成内部的逻辑结构，如语法树（Syntax Tree）。
• 检查SQL语句的语法是否正确。

2. 预处理（Preprocessing）：

• 检查用户是否有权限执行该查询。
• 检查表和列是否存在。
• 重写查询，如展开视图、处理子查询等。

3. 优化（Optimization）：

• 查询优化器评估不同的执行计划，选择最优的执行路径。
• 优化器会考虑索引、表的大小、数据分布等因素。
• 生成执行计划，决定如何访问表、如何连接表、如何过滤数据等。

4. 执行（Execution）：

• 按照优化器生成的执行计划，实际执行查询。
• 包括表扫描、索引扫描、连接操作、过滤操作等。

5. 结果返回（Result Returning）：

• 将查询结果返回给客户端。

查询路径的详细分析

1. 解析（Parsing）

解析阶段将SQL语句转换为内部的逻辑结构。例如，对于查询SELECT a, b FROM test_a WHERE a = 1 AND b = ‘A1’;，解析器会将其解析为一个查询树，包含以下信息：

• 查询类型：SELECT
• 查询的列：a, b
• 查询的表：test_a
• 查询的条件：a = 1 AND b = ‘A1’

2. 预处理（Preprocessing）

预处理阶段会检查用户是否有权限执行该查询，表和列是否存在，以及是否需要重写查询。例如：

• 如果test_a表不存在，查询会失败。
• 如果用户没有权限访问test_a表，查询会失败。
• 如果查询中包含子查询或视图，预处理器会将其展开。

3. 优化（Optimization）

优化阶段是查询路径中最关键的部分。查询优化器会评估不同的执行计划，选择最优的执行路径。优化器考虑的因素包括：

• 索引：是否有可用的索引，以及索引是否能有效过滤数据。
• 表的大小：表的大小会影响全表扫描的成本。
• 数据分布：数据的分布情况会影响过滤条件的效率。
• 连接顺序：如果有多个表连接，优化器会决定最佳的连接顺序。

优化器会生成一个执行计划，描述如何执行查询。例如，对于查询SELECT a, b FROM test_a WHERE a = 1 AND b = ‘A1’;，优化器可能会生成以下执行计划：

• 使用a列上的索引idx_a来定位满足条件a = 1的行。
• 在这些行中进一步过滤满足条件b = ‘A1’的行。

4. 执行（Execution）

执行阶段按照优化器生成的执行计划，实际执行查询。例如：

• 如果优化器选择使用a列上的索引idx_a，执行器会先扫描索引，找到满足条件a = 1的行。
• 然后在这些行中进一步过滤满足条件b = ‘A1’的行。
• 最终将结果返回给客户端。

5. 结果返回（Result Returning）

执行器将查询结果返回给客户端。客户端可以是MySQL客户端工具、应用程序等。

mysql架构

这里就要介绍一下MySQL的整体架构了。

• 连接器：MySQL连接器（MySQL Connector）是用于连接MySQL数据库的客户端库，它允许应用程序与MySQL数据库进行通信。这些连接器提供了API（应用程序编程接口），使得开发者可以在各种编程语言中轻松地执行SQL语句、管理数据库连接、处理查询结果等。
• 分析器：MySQL分析器才算真正进入了MySQL里面。它会分析词法和语法，如果你的SQL语句不对，就会看到一些报错，例如语法错误。
• 优化器：MySQL优化器是本文的重点内容，因为这里涉及了MySQL的一些优化，其中就包括了索引选择，也是导致索引失效的重要原因。在这一步也就确定了查询计划。
• 执行器：MySQL执行器会根据优化器输出的查询计划来执行这个查询计划，会调用底层的存储引擎进行执行。
• 存储引擎：MySQL支持多种存储引擎，每个存储引擎有不同的特点。其中InnoDB引擎因为支持事务等优点，现在是MySQL的默认存储引擎。

连接器

MySQL连接器（MySQL Connector）是用于连接MySQL数据库的客户端库，它允许应用程序与MySQL数据库进行通信。这些连接器提供了API（应用程序编程接口），使得开发者可以在各种编程语言中轻松地执行SQL语句、管理数据库连接、处理查询结果等。

当我们最开始连接数据库实例的时候，我们要输入用户名密码，这时候连接器会从数据库的用户信息中判断你是否有权限连接数据库进行操作，有哪些权限。

如果你输入的用户名密码错误或者没有权限，那么你会收到下面的报错信息。

1

Access denied for user 'root'@'localhost'(using password: YES)

分析器

连接成功以后。分析器会分析这个语句的词法，语法，语义这些信息。

通俗来讲就是看到select,update这些关键字，知道你要来干啥，看看你是不是来搞破坏的，来捣蛋的。

看看你是查询哪个表啊，有什么条件啊，这些玩意。

最后会输出一个词法树。

当然了这一步还会分析你的语法有没有错误，比如你把select打错试试。打成elect，会出现下面的报错信息

You have an error in your SQL syntax: check the maual that corresponds to your MySQL server version for the right syntax to use near ‘elect * from users’ at line 1

优化器

优化器负责几个事情

• 优化SQL：比如你写了一个很不友好的SQL，如select * from a where 1 =1,优化器会将1=1去掉。还有比如括号的删除，如select * from a where ((a AND b) AND c OR (((a AND b) AND (c AND d))))改写成select * from a where (a AND b AND c) OR (a AND b AND c AND d)。等等。
• 一些内部的优化器：下面列出的是部分，更多的可以参考MySQL官方文档优化器部分
  • 谓词下推：即where条件下推到扫描表的时候执行，而不是扫描表之后执行。
  • 范围优化：对于BTREE和HASH索引，当使用=、<=>、IN（）、IS NULL或IS NOT NULL运算符时，键部分与常量值的比较是范围条件。此外，对于BTREE索引，当使用>，<，>=，<=，BETWEEN，！= 、或<>运算符，或者LIKE比较（如果LIKE的参数是不以小写字符开头的常量字符串）。对于所有索引类型，多个范围条件与OR或AND组合形成范围条件。
  • index merge优化：index merge就是多个索引并发扫描，再将扫描结果合并。
  • hash join优化：使用hash join来代替Nested Loop Join算法，能大幅度提升join速度。
  • Is Null优化：可以对where a is null这种条件进行优化，比如该字段设置了not null，那么这个条件就会被删除。
  • order by + limit优化：使用Top N排序
  • group by 优化：优化group by语句。
  • 子查询物化：将子查询的内容物化保存起来。
• 通过成本模型、直方图等信息生成不同的执行路径。
• 对比执行路径的内容，如取样等，进行选择最终的执行路径。生成查询计划。

这里简单的介绍一些mysql内部的优化器，以了解mysql内部做了哪些优化手段。

最后会介绍mysql的成本模型、直方图信息等。结合实际的例子来给大家展示索引选择的问题。

谓词下推优化

谓词下推优化（Predicate Pushdown Optimization）是一种查询优化技术，它将查询中的过滤条件（谓词）尽可能地推送到数据访问的早期阶段，以减少数据扫描的范围，从而提高查询性能。
在数据库查询中，谓词通常是指WHERE子句中的条件。谓词下推优化的目的是让这些条件在数据被读取或处理的早期阶段就发挥作用，避免不必要的数据处理和传输。

在没有谓词下推优化的情况下，数据库会先读取所有数据，然后在内存中应用过滤条件。这可能导致大量的数据被加载到内存中，增加了I/O操作和内存使用。
通过谓词下推优化，数据库会在数据读取阶段就应用过滤条件，只加载满足条件的数据，从而减少数据的读取量和处理量。

假设存在table_a表，表里面有10条数据，a = 1的数据有一个，具体什么意思呢，我们来看一个SQL语句。

1

select a,b from table_a where a = 1;

如果没有谓词下推优化的话，执行树如下。

其执行顺序如下：

1. 扫描table_a表的10条数据，将10条数据传递给where过滤节点。
2. where过滤操作进行过滤，过滤出a = 1条件的1条数据，将这个数据传递给列选择节点。
3. 列选择节点选择这条数据的2列，将结果返回。

其内存中要存储10条数据。

而有了谓词下推优化以后，执行树如下。

执行顺序如下：

1. 扫描table_a表的10条数据，过滤出符合a = 1条件的这一个数据。将这个数据传给列选择节点。
2. 列选择节点选择这条数据的2列，将结果返回。

范围优化

对于BTREE和HASH索引，当使用=、<=>、IN（）、IS NULL或IS NOT NULL运算符时，键部分与常量值的比较是范围条件。此外，对于BTREE索引，当使用>，<，>=，<=，BETWEEN，！= 、或<>运算符，或者LIKE比较（如果LIKE的参数是不以小写字符开头的常量字符串）。对于所有索引类型，多个范围条件与OR或AND组合形成范围条件。

给定数据

1
2
3
4
5
6
7
8

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

执行where key_part1= 1，其扫描范围为 1,负无穷，负无穷到 1,正无穷，正无穷

1

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

即覆盖了这三行

1
2
3

1         1          'abc'
1         1          'xyz'
1         2          'abc'

index dives，优化器在范围的两端进行dives, 可以帮助优化器更准确的评估扫描的行数，index dives提供了更准确的行估计，但是随着比较值数量的增加，更加耗时，使用统计信息的准确性不如index dives，但允许对大值列表进行更快的行估计。

eq_range_index_dive_limit系统变量使您能够配置优化器从一个行估计策略切换到另一个行估计策略时的值数量。要允许使用索引潜水来比较最多N个相等范围，请将eq_range_index_dive_limit设置为N+ 1。要禁用统计信息并始终使用索引潜水而不管N，请将eq_range_index_dive_limit设置为0。

若要更新表索引统计信息以获得最佳估计值，请使用ANALYZE TABLE。

skip scan,比如有索引(f1,f2)，都知道最左前缀原则，所以一般where f2 > 40是不走索引的，skip scan可以让他走索引，通过构造f1 = 1 and f2 > 40，扫描完以后再扫描 f1 = 2 and f2 > 40，以此类推，可以通过explain来看extra列是否有skip scan

in优化，in查询可以用如下形式

1

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

range_optimizer_max_size_size系统变量可以设置优化器使用的内存

index merge 优化

index merge就是多个索引并发扫描，再将扫描结果合并

索引合并不适用于全文索引。

索引合并访问方法检索具有多个范围扫描的行，并将其结果合并为一个。此访问方法只合并单个表的索引扫描，而不合并多个表的扫描。合并可以产生其底层扫描的并集、交集或交集的并集。

可以使用索引合并的查询示例：

1
2
3
4
5
6
7
8
9
10
11
12

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

如果你的查询有一个带有深度AND/OR嵌套的复杂WHERE子句，并且MySQL没有选择最佳计划，请尝试使用以下恒等转换来分发术语：

1
2

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

在EXPLAIN输出中，Index Merge方法在type列中显示为index_merge。在本例中，key列包含使用的索引列表，key_len包含这些索引的最长键部分列表。

索引合并访问方法有几种算法，它们显示在EXPLAIN输出的Extra字段中：

• intersect：对多个and条件生效
• union：对多个or条件生效
• sort_union：sort-union算法和union算法之间的区别在于，sort-union算法必须首先获取所有行的行ID，并在返回任何行之前对其进行排序。

索引合并的使用取决于optimizer_switch系统变量的index_merge、index_merge_intersection、index_merge_union和index_merge_sort_union标志的值。默认情况下，所有这些标志都是打开的。

hash join

默认情况下，MySQL尽可能使用哈希连接。可以使用BNL和NO_BNL优化器提示之一来控制是否使用散列连接。

hash join比嵌套join快的多，首先创建hash表，在循环另一个表进行hash，判断是否相等

可以使用join_buffer_size系统变量控制哈希连接的内存使用量;哈希连接使用的内存量不能超过此值。当哈希连接所需的内存超过可用量时，MySQL会使用磁盘上的文件来处理。如果发生这种情况，您应该注意，如果哈希连接无法容纳内存并且它创建的文件比为open_files_limit设置的文件多，则连接可能不会成功。要避免此类问题，请进行以下更改之一：

• 增加join_buffer_size，使哈希连接不会溢出到磁盘。
• 增加open_files_limit。

成本模型

MySQL成本模型（Cost Model）是MySQL查询优化器（Query Optimizer）用来评估不同查询执行计划的成本（Cost）的一种机制。成本模型通过估算每种执行计划所需的资源（如CPU、I/O、内存等）来选择最优的执行计划。

MySQL的成本模型主要考虑以下几个方面：

1. CPU成本：
   • 评估执行计划中每个操作（如比较、计算、排序等）所需的CPU时间。
   • 例如，WHERE子句中的条件计算、JOIN操作中的匹配等。
2. I/O成本：
   • 评估执行计划中每个操作所需的磁盘I/O操作次数。
   • 例如，全表扫描、索引扫描、数据页的读取等。
   • I/O成本通常是最主要的成本因素之一，因为磁盘I/O操作相对较慢。
3. 内存成本：
   • 评估执行计划中每个操作所需的内存使用量。
   • 例如，排序操作、临时表的创建等。
4. 网络成本：
   • 评估执行计划中每个操作所需的网络传输量。
   • 例如，分布式查询中跨节点的数据传输。
5. 数据分布：
   • 评估数据的分布情况，如表的大小、索引的覆盖率等。
   • 数据分布会影响I/O操作的次数和效率。

其中大部分的成本都是固定的，比如CPU成本、IO成本、内存成本。这个是根据你服务器的配置决定的。

所以，主要关注的是数据分布。

MySQL的数据分布使用直方图来记录。

直方图

column_statistics数据字典表存储有关列值的直方图统计信息，供优化器在构造查询执行计划时使用。要执行直方图管理，请使用ANALYZE TABLE语句。

• 该表包含除几何类型（空间数据）和JSON之外的所有数据类型的列的统计信息。
• 该表是持久的，因此不必在每次服务器启动时都创建列统计信息。
• 服务器对表执行更新，用户不执行。

用户不能直接访问column_statistics表，因为它是数据字典的一部分。直方图信息可使用 INFORMATION_SCHEMA.COLUMN_STATISTICS 获得，它是作为数据字典表上的视图实现的。COLUMN_STATISTICS包含以下列：

• SCHEMA_NAME、TABLE_NAME、COLUMN_NAME：应用统计信息的模式、表和列的名称。
• HISTORIO：描述列统计信息的JSON值，存储为直方图。

直方图实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "collation-id": 8
}

• buckets：直方图桶。桶结构取决于直方图类型。
• null-values：一个介于0.0和1.0之间的数字，表示SQL NULL值的列值的分数。如果为0，则该列不包含NULL值。
• last-updated：直方图生成时，以 YYYY-MM-DD hh:mm:ss.uuuuuu 格式的UTC值表示。
• sampling-rate：0.0到1.0之间的数字，表示为创建直方图而采样的数据比例。值为1表示已读取所有数据（无采样）。
• histogram-type：直方图类型：
  • singleton：一个bucket表示列中的一个值。当列中的非重复值数量小于或等于生成直方图的ANALYZE TABLE语句中指定的存储桶数量时，将创建此直方图类型。
  • equi-height：一个桶表示一个值范围。当列中的非重复值数量大于生成直方图的ANALYZE TABLE语句中指定的存储桶数量时，将创建此直方图类型。
• number-of-buckets-specified：生成直方图的ANALYZE TABLE语句中指定的桶数。
• data-type：此直方图包含的数据类型。在将直方图从持久性存储器阅读和解析到内存中时，这是需要的。该值是int、uint（无符号整数）、double、decimal、datetime或string（包括字符和二进制字符串）之一。
• collation-id：直方图数据的归类ID。当数据类型值是字符串时，它最有意义。值对应于信息架构COLLATIONS表中的ID列值。

直方图统计信息主要用于非索引列。将索引添加到直方图统计信息适用的列还可以帮助优化器进行行估计。

优化器更喜欢范围优化器的行估计，而不是从直方图统计信息中获得的行估计。如果优化器确定范围优化器适用，则不使用直方图统计信息。

对于已建立索引的列，可以使用索引潜水(index dives)获得行估计值以进行相等比较。

在某些情况下，使用直方图统计信息可能不会改善查询执行（例如，如果统计信息过期）。要检查是否是这种情况，请使用ANALYZE TABLE重新生成直方图统计信息，然后再次运行查询。

这么看这些概念内容，可能很难理解直方图到底是干啥的，下面给出一个例子方便理解。

直方图示例

虽然直方图的字段很多，但其核心的字段只有几个

创建一个测试表。

1

create table test_a(id int auto_increment,a int not null default 0, b varchar(255) not null default '', primary key(id));

接下来我们插入几个数据。

1
2
3
4
5
6
7
8
9
10
11

INSERT INTO test_a (a, b) VALUES
(1, 'A1'),
(2, 'B2'),
(3, 'C3'),
(4, 'D4'),
(5, 'E5'),
(6, 'F6'),
(7, 'G7'),
(8, 'H8'),
(9, 'I9'),
(10, 'J10');

接下来生成直方图信息。

1

ANALYZE TABLE test_a update  HISTOGRAM ON a WITH 5 BUCKETS;

查询直方图信息。这里的SCHEMA_NAME是数据库的名称，TABLE_NAME是数据表的名称。

1

SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS where SCHEMA_NAME = 'test1' and TABLE_NAME = 'test_a';

查询结果：

1
2
3
4
5
6
7

+-------------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | HISTOGRAM
                                                                                                                                                   |
+-------------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test1       | test_a     | a           | {"buckets": [[1, 2, 0.2, 2], [3, 4, 0.4, 2], [5, 6, 0.6, 2], [7, 8, 0.8, 2], [9, 10, 1.0, 2]], "data-type": "int", "auto-update": false, "null-values": 0.0, "collation-id": 8, "last-updated": "2025-01-25 13:01:57.129967", "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 5} |
+-------------+------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

我们来看一下直方图的信息。

1
2
3
4
5
6
7
8
9
10
11

{
"buckets": [[1, 2, 0.2, 2], [3, 4, 0.4, 2], [5, 6, 0.6, 2], [7, 8, 0.8, 2], [9, 10, 1.0, 2]], 
"data-type": "int", 
"auto-update": false, 
"null-values": 0.0, 
"collation-id": 8, 
"last-updated": "2025-01-25 13:01:57.129967", 
"sampling-rate": 1.0, 
"histogram-type": "equi-height", 
"number-of-buckets-specified": 5
}

• buckets：直方图桶。桶结构取决于直方图类型。
• null-values：0.0,表示没有null值，这是因为我们使用了not null声明字段。
• last-updated：更新时间，没啥好说的。
• sampling-rate：1.0 获取了所有数据，因为我们只有10条数据，很少。
• histogram-type：直方图类型：equi-height，因为a列有10个值，都不重复，而桶数量我们用的是5，所以生成了这个类型。
• number-of-buckets-specified：生成直方图的ANALYZE TABLE语句中指定的桶数。
• data-type：列a是int类型
• collation-id：直方图数据的归类ID。当数据类型值是字符串时，它最有意义。值对应于信息架构COLLATIONS表中的ID列值。

桶里面有4个数据

• 最小值
• 最大值
• 密度
• 高度

比如，查询语句

1

select * from test_a where a = 5

a = 5的数据在第三个桶里面，最小值5，最大值6，密度0.6，高度2.

根据计算公式预估行数 = 密度 * 高度来计算0.6 * 2，预估行数就是1.2，也就是1-2行。

可以看到查询计划里面的rows是10行，这是因为类型是全表扫描，但是后面的filtered字段是10，表示的意思是会过滤出来 10 * 10% = 10 * 0.1 = 1行。

也就是最终会查出1行结果。

通常来说，对于没有索引的列，MySQL就是这样来预估行数的，并且通过这个结果来进行选择执行路线。

执行路线选择

什么叫路线选择呢，还是上面那个表，我们现在有如下SQL语句。

1

select a,b from test_a where a = 1 and b = 'A1';

根据表数据，其实我们知道，查出来的结果还是1条。但是对于mysql来说，却有不同的执行方式。

第一种执行方式，先查a=1在查询b='A1'的数据。执行树如下。

第二种执行方式，先查b='A1'在查询 a=1。执行树如下。

目前看着这两种方式都没啥问题。

但是，我们再插入一条数据呢？

1
2

INSERT INTO test_a (a, b) VALUES
(11, 'A1');

这样我们就知道了，方案1， 会直接过滤出1行数据，然后在过滤，这样显然比方案2更好。

因为方案2会先查出2条数据，再次过滤。

这就是不同的执行路线带来的性能区别。当然了，我们这里的例子只是打个比方，实际上谓词下推优化以后，这两个条件都是和扫描表一起执行的。

这个例子只是让你明白不同的路线选择而已。

对于join查询来说，会有更多的选择。

文末福利

以上就是今天的内容了，大家有任何疑问可以打在评论区，一起交流～

关注我发送“MySQL知识图谱”领取完整的MySQL学习路线。

发送“电子书”即可领取价值上千的电子书资源。

部分电子书如图所示。

MySQL零基础教程基础篇

大家好，我是大头，98年，职高毕业，上市公司架构师，大厂资深开发，管理过10人团队，我是如何做到的呢？

这离不开持续学习的能力，而其中最重要的当然是数据库技术了！

对于所有开发来说，都离不开数据库，因为所有的数据都是要存储的。

关注我一起学习！文末有惊喜哦！

基础篇的内容大致如下图所示。

SQL语句

介绍完概念以后，我们可以来看看SQL语句了。SQL语句通常由三类组成。

数据定义 DDL

• CREATE 创建数据库或数据库对象
• ALTER 对数据库或数据库对象进行修改
• DROP 删除数据库或数据库对象
  数据操纵 DML
• SELECT 从表或视图中检索数据
• INSERT 将数据插入到表或视图中
• UPDATE 修改表或视图中的数据
• DELETE 从表或视图中删除数据
  数据控制 DCL
• GRANT 用于授予权限
• REVOKE 用于收回权限

DML

MySQL 中的 DML（数据操纵语言，Data Manipulation Language） 语句用于对数据库中的数据进行操作，主要包括数据的插入、更新、删除和查询。DML 语句是数据库操作中最常用的语句类型。

插入数据

插入数据使用insert指令，可以往创建好的一张表里面插入数据，支持多种插入方式。

最常用的方式是insert values，这种方式也支持批量插入数据。

1

insert into table_name[(col_name)] values ();

示例：

1
2

INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John Doe', 'IT', 50000);

还有一种方式是 insert set。这种方式不支持批量插入。这种方式以键值对的形式插入数据，适用于插入单行数据。这种方式在插入单行数据时更加直观，尤其是当列名较多时。

1
2

insert into table_name
set col_name = '值', col_name = '值';

还是刚才的插入示例：

1
2

INSERT INTO employees (id, name, department, salary)
set id = 1，name = 'John Doe'，department='IT'，salary=50000；

还有一种方式是insert select方式，这种方式适合快速复制表数据，将查询出来的数据插入到另外一个表里面。

1
2

insert into table_name
select * from table_name;

删除数据

可以使用delete from指令来删除已经插入的数据。如果不加where条件的话，就是删除全表数据。

删除数据操作一定要慎重！！！

1

delete from table_name where id = 1

示例：删除刚才插入到表employees的ID=1的数据。

1

delete from employees where id = 1

除了使用delete指令以外，还可以使用TRUNCATE指令，这个指令可以删除全表的数据，并且新的数据id自增从1开始。删除全表数据的话，该指令通常更快速。

1

delete TABLE table_name

修改数据

当插入的数据内容需要修改或者说更新的时候，则可以使用update set指令进行修改。修改操作可以使用where条件来选择要修改的数据，不加where条件则会更新所有数据。

1

update table_name set col_name = '值' where id = 1

示例：将刚才插入的数据部门修改一下。

1

update employees set department = 'Market' where id = 1

数据查询

数据查询语句是最复杂的语句，这里只是介绍，想要完全用明白，需要大量的实践。

select 语句

select用于查询数据表里面插入的数据。

*代表查询所有字段。

1

select * from table_name

列的选择与指定

如果查询指定字段，则使用字段名称代替*。

实际开发中不推荐查询所有字段，推荐查询需要的字段，可以提升查询速度。

• 如果查询的字段正好是索引，那么可以触发覆盖索引
• 如果查询的字段过多，会增加网络传输消耗

1

select col_name1,col_name2... from table_name

定义别名

可以给字段和表定义别名，通过as指令实现。别名可以解决一些字段名冲突或者字段名过长的问题。

1

select col_name as alias from table_name

示例：department字段给一个别名是depart

1

select id，department as depart from employees

where条件

通过where关键字来进行条件筛选，可以选择出符合条件的数据。

比如当前用户表user有数据如下：以下数据均为随机生成，非真实数据。

这个时候我们需要查询出张三的用户信息，而不是将这10个数据都查询出来到程序里在筛选出张三的数据。

可以使用如下sql完成。

1

select * from user where name = '张三'; 

这个sql会把name字段中等于‘张三’的数据查询出来。

where条件支持的类型如下：

• 比较操作符

• 逻辑操作符

  操作符	描述
  AND	逻辑与（两个条件都满足）
  OR	逻辑或（至少一个条件满足）
  NOT	逻辑非（对条件取反）

• 范围操作符

  操作符	描述
  BETWEEN...AND...	在指定范围内（包括边界值）
  NOT BETWEEN	不在指定范围内

• 列表操作符

  操作符	描述
  IN	在指定的列表中
  NOT IN	不在指定的列表中

• 模糊匹配操作符

  操作符	描述
  LIKE	模糊匹配（使用%和_作为通配符）
  NOT LIKE	不匹配指定模式

• 空值操作符

  操作符	描述
  IS NULL	判断是否为NULL
  IS NOT NULL	判断是否不为NULL

• 其他操作符

  操作符	描述
  EXISTS	检查子查询是否存在结果
  NOT EXISTS	检查子查询是否不存在结果

替换查询结果集中的数据

可以使用if条件来进行结果的判定，比如性别，数据库里面存的可能是1代表男，2代表女。如果要查询出来男和女的话，就可以直接通过sql处理。

1
2
3
4
5

case 
when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式
end as alias

示例：性别转换。

1
2
3
4
5
6
7

SELECT 
    CASE 
        WHEN gender = 1 THEN '男'
        WHEN gender = 2 THEN '女'
        ELSE '未知'
    END AS 性别
FROM user;

计算列值

可以直接计算将字段的值进行加减乘除运算。

1

select col_name + 100 from table_name

from 子句与多表连接查询

交叉连接，笛卡尔积

交叉连接可以连接两个表，产生两个表的笛卡尔积作为结果。

语法如下：

1

select * from table_namme1 cross join table_name2

可以直接简写：

1

select * from table_name1,table_name2;

示例：获取两个表的交叉连接。

假设有表user如下：

还有表user_account存储用户的账户余额信息如下：

使用如下sql语句获取交叉连接：

1

select * from user,user_account;

结果如下：也就是用户表数据001和用户账户001产生一条数据，和用户账户002产生一条数据，用户数据002同样。

内连接

内连接返回两个表中匹配的记录。只有当两个表中的记录满足连接条件时，才会出现在结果集中。可以理解为两个表的交集。

连接的时候，on就类似于where条件，只不过仅仅在连接表数据的时候生效。内连接返回两个表都满足这个条件的交集。

语法：

1

select col_name from table_name inner join table_name2 on table_name.id = table_name2.t_id;

示例：获取用户数据和用户账户数据的内连接。

假设有表user如下：

还有表user_account存储用户的账户余额信息如下：

可以看到这两个表的交集就只有两条数据，也就是001和002，

使用如下sql语句获取交叉连接：

on user.id = user_account.user_id这个条件代表只有当user表的id字段和user_account表的user_id字段相等的时候，才会有结果;

1

select * from user inner join user_account on user.id = user_account.user_id;

结果如下：

外连接

外连接分为左连接和右连接，左连接返回内连接的结果+左表剩余的数据，右连接返回内连接的结果+右表剩余的数据。

左表就是 join左边的表，右表就是join右边的表。

左连接使用left join指令。

1

select col_name from table_name left join table_name2 on table_name.id = table_name2.t_id;

示例：获取用户表和用户账户表的左连接结果。

还有表user_account存储用户的账户余额信息如下：

使用如下sql语句，可以看到，仅仅是inner join换成了left join

1

select * from user left join user_account on user.id = user_account.user_id;

结果如下：在内连接的结果基础上，增加了左表user表剩下的8条数据，右表的字段内容则是null，代表没有对应字段的数据。

右连接使用right join指令。
语法如下：

1

select col_name from table_name right join table_name2 on table_name.id = table_name2.t_id;

示例：获取用户表和用户账户表的右连接结果。

还有表user_account存储用户的账户余额信息如下：

使用如下sql语句，可以看到，仅仅是left join换成了right join

1

select * from user right join user_account on user.id = user_account.user_id;

结果如下：在内连接的结果基础上，增加了右表user_account表剩下的1条数据，左表的字段内容则是null，代表没有对应字段的数据。

子查询

在MySQL中，子查询是一种强大的功能，允许在一个查询中嵌套另一个查询。根据子查询返回的结果类型，可以将其分为以下几种：

• 表子查询
• 行子查询
• 列子查询
• 标量子查询

注意：所有的子查询应该慎重使用，因为子查询会导致查询速度降低。

表子查询

• 定义：返回一个完整的表（多行多列）。
• 特点：可以作为虚拟表使用，通常用于FROM子句或JOIN操作中。
• 示例：SELECT id, name FROM users 这就是一个子查询，该子查询返回的结果是一张表的数据，将该子查询的结果作为一张表，供外部的查询使用。

1
2

SELECT * 
FROM (SELECT id, name FROM users) AS subquery;

行子查询

• 定义：返回一行数据（多列）。
• 特点：结果是一行数据，可以与多列比较，通常用于WHERE子句中。
• 示例：SELECT id, name FROM users WHERE mobile = “13012345678” 是一个子查询，该子查询返回了mobile字段等于13012345678的一行数据，并且只查询了id和name字段。将这两个字段作为外部查询的where条件。

  1 2 3

  SELECT * FROM users WHERE (id, name) = (SELECT id, name FROM users WHERE mobile = "13012345678");

列子查询

• 定义：返回一列数据（多行）。
• 特点：结果是一列数据，可以与IN、ANY、ALL等操作符配合使用，通常用于WHERE子句中。
• 示例：SELECT user_id FROM orders 是一个子查询，该子查询返回了orders表的所有用户id信息，并将这些用户id作为外部查询的where条件。

  1 2 3

  SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

标量子查询

• 定义：返回单个值（一行一列）。
• 特点：结果是一个单一值，可以与比较操作符配合使用，通常用于WHERE子句中。
• 示例：SELECT MAX(age) FROM users 是一个子查询，该子查询返回了users表的最大的年龄信息，并将最大的用户年龄作为外部查询的where条件。

  1 2 3

  SELECT * FROM users WHERE age = (SELECT MAX(age) FROM users);

group by

• group语句可以实现分组的效果，什么是分组？

假设该数据表中存储了10条订单信息，有3条是张三的，3条是李四的，剩下4条是王五的。

group分组以后就可以分成3组，一组是张三的3条数据，一组是李四的3条数据，一组是王五的4条数据。

• 分组能干什么？

分组以后可以统计每个分组中的订单数量、订单总额、订单平均金额等。

语法

1
2
3

SELECT * 
FROM table_name 
group by col_name

支持的聚合函数：

• count(col_name): 计算每个分组中该字段的数量，比如订单数量
• sum(col_name): 计算每个分组中该字段的总额，比如订单总金额
• avg(col_name): 计算每个分组中该字段的平均值，比如订单平均金额
• min(col_name): 获取每个分组中该字段的最小值
• max(col_name): 获取每个分组中该字段的最大值

有人要问了？那我不使用group by的情况下，可以使用上面的聚合函数吗？

当然可以了，没有分组，其实相当于所有数据是一个大分组，所以计算的是所有数据数量、总额等。

示例：下表是订单表，记录了3个用户的订单信息，现在需要查询这3个用户的订单数量、订单总金额、订单平均金额、最小金额以及最大金额。

使用如下sql：对userID进行分组，就可以分成三组数据了，在对每个分组使用聚合函数。

1
2
3

SELECT UserID, count(OrderID), sum(OrderAmount), avg(OrderAmount), min(OrderAmount), max(OrderAmount)
FROM orders 
group by UserID

结果如下：

having

having语句用来过滤group by分组以后的数据。

简单点说，就是相当于where条件，只不过where条件的执行顺序在group by之前，having条件的执行顺序在group by之后。

语法如下：

1
2
3

SELECT * 
FROM table_name 
group by col_name [having col_name = 任何数]

示例：还是上面group by的表，这次我们只需要总金额大于400的数据，从上面的结果来看，我们知道，只需要userId为1和3的数据。

但是注意，where条件是在group by之前执行，这个时候还没有总金额这个字段呢。所以，就需要使用having了。

使用的sql如下：可以看到，仅仅是在后面增加了having sum(OrderAmount) > 400这一条。

1
2
3

SELECT UserID, count(OrderID), sum(OrderAmount), avg(OrderAmount), min(OrderAmount), max(OrderAmount)
FROM orders 
group by UserID having sum(OrderAmount) > 400

结果如下：

order by

如果想对查询出来的结果集进行排序，可以使用order by语句。

语法如下：asc代表升序，即1，2，3这种排序，desc代表降序，即3，2，1这种。默认是asc。

1
2
3

SELECT * 
FROM table_name 
order by col_name [asc｜desc]

order by排序作用在group by分组之后，这意味着可以使用分组之后的聚合函数的结果进行排序，同时也意味着可以影响group by之后的数据。

示例：对上面group by之后的数据按照总金额进行降序排序。

1
2
3
4

SELECT UserID, count(OrderID), sum(OrderAmount), avg(OrderAmount), min(OrderAmount), max(OrderAmount)
FROM orders 
group by UserID having sum(OrderAmount) > 400
order by sum(OrderAmount) desc

结果如下：

group 和 order的差别

limit

如果不想每次都查询数据表的全部数据，只想获取几条数据呢？比如分页功能，一页10条数据这种，就可以使用limit命令来实现。

语法如下：start代表开始的位置，end代表结束的位置。

1
2
3

SELECT *
FROM orders 
limit [start, end]

比如表中有100条数据。获取1-10条数据就是limit 1,10，获取11-20条数据就是limit 11,20。

limit最好是配合order by使用。性能更佳，另外，如果只获取1条数据，也建议使用limit 1代表获取1条数据。

具体的原因在后面原理篇会讲到。

MySQL零基础教程基础篇

大家好，我是大头，98年，职高毕业，做过上市公司架构师，做过大厂资深开发，管理过10人团队，我是如何做到的呢？

这离不开持续学习的能力，而其中最重要的当然是数据库技术了！

对于所有开发来说，都离不开数据库，因为所有的数据都是要存储的。

关注我一起学习！可获得系统性的学习教程、转码经验、技术交流、大厂内推等～

文末有惊喜哦！

基础篇的内容大致如下图所示。

SQL语句

介绍完概念以后，我们可以来看看SQL语句了。SQL语句通常由三类组成。

数据定义 DDL

• CREATE 创建数据库或数据库对象
• ALTER 对数据库或数据库对象进行修改
• DROP 删除数据库或数据库对象
  数据操纵 DML
• SELECT 从表或视图中检索数据
• INSERT 将数据插入到表或视图中
• UPDATE 修改表或视图中的数据
• DELETE 从表或视图中删除数据
  数据控制 DCL
• GRANT 用于授予权限
• REVOKE 用于收回权限

DCL

DCL（Data Control Language）语句用于控制对数据库的访问权限，包括用户权限的授予和撤销。DCL语句主要涉及用户和角色的权限管理，确保数据库的安全性和数据的完整性。

• 授予权限（GRANT）
• 撤销权限（REVOKE）
• 设置用户密码（SET PASSWORD）
• 查看用户权限（SHOW GRANTS）

GRANT

• GRANT语句用于授予用户或角色特定的权限。
• 权限可以包括对表、视图、存储过程等的访问和操作权限。

语法

1

GRANT privilege_type ON object_name TO user_or_role;

示例：授予用户zhangsan对employees表的SELECT和INSERT权限

1

GRANT SELECT, INSERT ON employees TO 'zhangsan'@'localhost';

REVOKE

• REVOKE语句用于撤销用户或角色的特定权限。
• 撤销的权限可以是之前授予的任何权限。

语法如下：

1

REVOKE privilege_type ON object_name FROM user_or_role;

示例：撤销用户zhangsan对employees表的INSERT权限

1

REVOKE INSERT ON employees FROM 'zhangsan'@'localhost';

SET PASSWORD

• SET PASSWORD语句用于设置或更改用户的密码。

语法如下：

1

SET PASSWORD FOR user = 'new_password';

示例：设置用户zhangsan的新密码为new_password。

1

SET PASSWORD FOR 'zhangsan'@'localhost' = 'new_password';

SHOW GRANTS

• SHOW GRANTS语句用于查看用户的权限。

语法：

1

SHOW GRANTS FOR user;

示例如下：查看用户zhangsan的权限

1

SHOW GRANTS FOR 'zhangsan'@'localhost';

DCL主要是对于权限的控制，希望大家可以自己创建一个数据库a，在创建一个用户a,授予a用户a数据库的权限。进行练习。

DDL

DDL（Data Definition Language，数据定义语言）是SQL语言的一部分，用于定义和修改数据库的结构。DDL语句主要涉及数据库、表、索引、视图等的创建、修改和删除操作。这些语句直接影响数据库的结构，但不会直接操作数据本身。

数据库模式定义

创建数据库

使用 CREATE DATABASE 语句，IF NOT EXISTS代表没有这个数据库，才会进行创建。如果已经有了，则不会再创建了。

CHARACTER SET是设置字符集，推荐设置为utf8mb4字符集，COLLATE则使用默认的就可以了。

这里说一下utf8和utf8mb4这两个字符集的区别。

• utf8字符集：在MySQL中实际上是一个有限的字符集，它只支持最多3字节的UTF-8字符。这意味着它不能存储所有可能的Unicode字符，特别是那些需要4字节表示的字符（如某些表情符号）。utf8字符集支持的Unicode范围是U+0000到U+FFFF，即基本多语言平面（BMP）。
• utf8mb4字符集支持完整的UTF-8字符集，包括4字节的字符。这意味着它可以存储所有可能的Unicode字符，包括表情符号和一些罕见的字符。utf8mb4字符集支持的Unicode范围是U+0000到U+10FFFF，即整个Unicode范围。

1
2
3

CREATE {DATABASE} [IF NOT EXISTS] db_name
[DEFAULT] CHARACTER SET[=]charset_name
| [DEFAULT] COLLATE[=]collation_name

创建一个测试数据库

1

create database test;

查看数据库

使用show databases命令可以查看所有的数据库。也包括一些MySQL自带的数据库。这些数据库存储了MySQL的元数据，具体的等到原理篇会讲到。

1
2

SHOW {DATABASES}
[LIKE pattern | WHERE expr] 

选择数据库

使用use指令➕数据库名称可以选择数据库，或者说进入数据库。只有先进入一个数据库，才能操作这个数据库里面的数据表等等。

除此之外，也可以在操作数据表的前面加上数据库名称，但是那样比较麻烦。

1

use db_name

修改数据库

使用ALTER DATABASE指令可以修改数据库。

1
2
3

ALTER DATABASE db_name
DEFAULT CHARACTER SET gb2312
DEFAULT COLLATE gb2312_chinese_ci;

删除数据库

当这个数据库不再使用的时候，可以通过DROP DATABSE指令来删除掉这个数据库。

IF EXISTS代表存在则删除，不存在就不会删除。和创建的时候那个指令正好相反。都是可选指令。

1

DROP {DATABASE} [IF EXISTS] db_name;

表定义

数据表被定义为字段的集合
按 行 和列的格式存储
每一行代表一条记录
每一列代表记录中一个字段的取值

创建表

使用create table指令可以创建数据表，后面跟的是表名称和字段。

temporary表示临时表，临时表存放在内存中。

字段类型常用的如下：

• int类型，占11位，也可以设置为int(5)等，但是这个只影响展示，并不影响实际的存储。
• varchar/char类型，相当于字符串类型，varchar是可变长度的字符串，char是不可变长度的字符串。
• text类型，很不推荐使用该类型，会导致查询速度变慢，尽量使用varchar代替。
• timestamp时间戳类型，不推荐使用，因为该类型表示1970年到现在的秒数，最大只能到2038-01-19号，而现在已经2025年了。
• datetime类型，推荐使用这个代替时间戳，直接存储时间类型，并且表里的updated_time字段可以使用DEFAULT CURRENT_TIMESTAMP作为默认值，还可以使用ON UPDATE CURRENT_TIMESTAMP来实现自动更新。
• decimal类型,用来存储小数，使用定点小数来存储，可以防止精度丢失。请避免使用float和double来存储小数。
• json类型，可以存储json字符串。

1
2
3
4

create [temporary] table [if not exists] tbl_name
(
    字段名1 数据类型 [列完整性约束条件] [默认值]
)

修改表

对于创建的表结构不满意，可以通过ALTER TABLE指令来修改表结构。

1

ALTER TABLE table_name

下面介绍一些子句，配合alter table命令来执行。

• ADD [COLUMN] 子句：给表结构增加字段。
• change [COLUMN] 子句：修改表结构的字段类型、字段名称等。
  • CHANGE COLUMN name new_name VARCHAR(200);
• alter [column] 子句 修改或删除表中指定列的默认值。
  • alter colum city set default ‘bj’
• modify [column] 子句 只修改指定列的数据类型，不会干涉它的列名
  • modify column city char(50);
• drop [column] 子句 删除指定列
  • drop column city;
• rename [to] 子句 修改表名
  • rename table table_name to new_table_name
• add index index_name(column_name) 创建索引
• drop index index_name

删除表

当一个表不再使用的时候，也可以使用drop table将它删除。

1
2

drop [temporary] table [if exists]

查看表

可以通过SHOW CREATE TABLE来查看表结构。

1

SHOW CREATE TABLE tablename;

索引定义

索引是提高数据文件访问效率的有效方法，比如MySQL中的B+树索引、hash索引、全文索引等。

缺点

• 索引是以文件的形式存储的，如果有大量的索引，索引文件可能比数据文件更快达到最大的文件尺寸
• 索引在提高查询速度的同时，会降低更新表的速度

索引物理结构

• b+树索引
• hash索引
• 倒排索引

索引逻辑结构

• index 或 key： 普通索引
• unique ：唯一性索引 候选码
• primary key： 主键

索引逻辑概念

• 聚簇索引：比如主键索引，也就是b树的叶子节点存储数据的索引。
• 联合索引：由多个字段共同组成的索引。
• 覆盖索引：查询的字段和索引的字段一致，从而避免了再次去主键索引获取数据。

关于索引的具体讲解将放在原理篇讲解，这里以介绍DDL语句为主，有个概念就可以了。

创建索引

想要创建一个索引可以使用create index命令，unique表示创建唯一索引。

index_col_name表示要将索引创建在哪个字段上面，也可以是多个字段。

1
2

create [unique] index index_name
on table_name(index_col_name)

示例，在user表上的email字段上创建一个索引，索引名称是email_idx。

1
2

create index email_idx
on user(email);

索引并不是越多越好，太多的索引会导致维护成本升高，尽量少且有用即可。尤其是后续增加索引的时候，如果数据表中数据过多，建立索引的过程会较慢，会对业务产生影响，这个时候需要慎重。

索引删除

当索引不再使用的时候，可以删除索引，使用命令drop index可以删除索引。

1
2

drop [unique] index index_name
on table_name

示例：删除刚才建立的email_idx索引。

1
2

drop index email_idx
on user;

视图定义

什么是视图

• 视图是一个对象，他是数据库提供给用户的以多种角度观察数据库中数据的一种重要机制
• 视图不是数据库中真实的表，而是一张虚拟表，其自身并不存储数据

视图的优点

• 集中分散数据
• 简化查询语句
• 重用SQL语句
• 保护数据安全
• 共享所需数据
• 更改数据格式

创建视图

想要创建视图，可以使用create view指令。

or replace 防止报错，存在替换，不存在创建。
with check option 增删改查的时候检查视图条件。
select_statement 是一段select查询语句。视图的本质就是这一段select查询语句。

1
2
3

create [OR REPLACE] view view_name [(col_list)]
as select_statement
[with check option]

示例：创建一个zhangsan用户的登录记录的视图。

1
2

create view zhangsan
as select * from login_log where user = ‘zhangsan’

修改视图

想要修改视图，可以使用alter view指令。

修改视图其实就是修改这个查询语句。当然了，也可以修改其他的属性等。

1
2
3

alter view view_name [(col_list)]
as select_statement
[with check option]

删除视图

当视图不需要了，可以使用drop view指令删除视图。

1

DROP VIEW [IF EXISTS] view_name

示例：删除刚才创建的视图zhangsan

1

DROP view zhangsan

查看视图定义

和上面说的查看表的定义一样，也可以查看视图的定义。

1

show create view view_name

存储过程定义

存储过程 是一组为了完成某项特定功能的 SQL语句集

• 可增强SQL语言的功能和灵活性
• 良好的封装性
• 高性能
• 可减少网络流量
• 可作为一种安全机制来确保数据库的安全性和数据的完整性
  其实质就是一段存储在数据库中的 代码
  它可以由声明式的sql语句和过程式sql语句组成

创建存储过程

DELIMITER $$是用户定义的MYSQL 结束符

参数：in|out|inout 参数名 参数类型

1
2
3
4
5

DELIMITER $$
create procedure sp_name(参数)
BEGIN
body //存储过程代码
END $$

示例：查询员工表的名称、部门和薪资。

1
2
3
4
5
6
7
8
9
10

DELIMITER //

CREATE PROCEDURE get_employee_details(IN emp_id INT)
BEGIN
    SELECT name, department, salary
    FROM employees
    WHERE id = emp_id;
END //

DELIMITER ;

调用存储过程

调用需要使用call指令来调用。

1

call sp_name(参数)

示例：调用刚才的存储过程。

1

call get_employee_details(1);

删除存储过程

如果存储过程不再需要了，则可以通过drop procedure指令来删除它。

1

drop procedure sp_name

示例：删除刚才的存储过程。

1

drop procedure get_employee_details;

存储函数定义

存储函数由SQL语句和过程式语句组成。

创建存储函数

使用 create function指令可以创建一个存储函数。

1
2
3

create function sp_name(参数)
    returns type
    routine_body //主体

示例：给定id号返回性别。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

use test; //进入数据库test
delimiter $$ //声明结束符号
create function fn_search(cid int) //创建函数fn search，参数为cid，int类型
    returns char(20) //声明返回值类型char20
    deterministic
begin //开始
    declare sex char(20) //声明一个变量sex 类型char20
    select cust_sex into sex from customers where id = cid; //select语句，把查询出来的cust_sex字段内容放入变量sex中
    if sex is null then //if判断，如果sex变量是null，则返回'没有该客户'
        return(select '没有该客户');
    else //如果sex变量不是null
        if sex = 'F' then //则判断是F的话，返回'女'
            return(select '女');
        else // 不然的话就返回'男'
            return(select '男');
        end if;
    end if;
end $$

调用存储函数

使用 select 调用存储函数。

1

select sp_name(参数)；

示例：调用刚才的存储函数。

1

select fn_search(1)$$

删除存储函数

当存储函数不再使用的时候，可以使用drop function将它删除。

1

drop function fun_name

示例：删除刚才的存储函数。

1

drop function fn_search

文末福利

以上就是整体的MySQL学习路线了。

关注我发送“MySQL知识图谱”领取完整的MySQL学习路线。

发送“电子书”即可领取价值上千的电子书资源。

部分电子书如图所示。

MySQL零基础教程基础篇

大家好，我是大头，98年，职高毕业，上市公司架构师，大厂资深开发，管理过10人团队，我是如何做到的呢？

这离不开持续学习的能力，而其中最重要的当然是数据库技术了！

对于所有开发来说，都离不开数据库，因为所有的数据都是要存储的。

关注我一起学习！文末有惊喜哦！

基础篇的内容大致如下图所示。

概念学习

首先，我们应该知道什么是数据库？很多人都会搞混一个概念，那就是数据库和数据库管理系统。

数据库的英文是DataBase。它的概念是

1
2
3
4
5
6
7
8

数据库是一个长期存储在计算机内的、有组织的、可共享的数据集合，它具有以下特点
1. 数据的集合性
2. 数据的共享性
3. 数据的冗余度低
4. 数据的独立性
5. 数据的安全性
6. 数据的完整性
7. 数据的可维护性

而数据库管理系统的英文是DataBase Management System。它的概念是：

1

数据库管理系统（Database Management System，简称DBMS）是用于创建、管理、维护和操作数据库的软件系统。它在用户和数据库之间提供了一个接口，使得用户能够方便地存储、检索、更新和管理数据。

因此，我们要明白，MySQL是一个数据库管理系统，而不是一个数据库。

虽然我们老说MySQL数据库，但这个是因为大家已经习惯了，大家都明白MySQL是什么，因此省略了一些。

MySQL是用来管理数据库的一个系统。

那么问题来了，SQL又是什么呢？

SQL

SQL（Structured Query Language，结构化查询语言）是一种用于管理和操作关系数据库的标准编程语言。它允许用户定义、查询、更新和管理数据库中的数据。SQL 是关系数据库管理系统（RDBMS）的核心语言，广泛应用于各种数据库系统。

这里又出现了一个新的概念，就是关系数据库管理系统（RDBMS）。

上面已经介绍了数据库管理系统。那什么是关系数据库管理系统呢？

关系数据库

这里就需要先说明一下什么是关系数据库。

关系数据库（Relational Database）
关系数据库是一种基于关系模型的数据库管理系统（DBMS），它使用表格（表）来组织和存储数据。每个表由行（记录）和列（字段）组成，每一行代表一个数据记录，每一列代表一个数据属性。关系数据库的核心概念是关系模型，它由数学家埃德加·弗兰克·科德（E.F. Codd）在1970年提出。

关系模型

Ted Codd在1969年设计了关系模型。发表了A relational model of data for large shared data banks

关系模型将物理层和逻辑层分离，当数据的内部表示发生变化时，甚至当外部表示的某些方面发生变化时，用户在终端和大多数应用程序上的活动应该不受影响。

关系模型提供了一种仅用数据的自然结构来描述数据的方法，因此，它为高级数据语言提供了一个基础，这种语言将一方面在程序之间产生最大的独立性，另一方面在机器表示和数据组织之间产生最大的独立性。另一个优点是，它为处理关系的可导出性、冗余性和一致性提供了坚实的基础。

仍然需要消除的三种主要数据依赖是：顺序依赖、索引依赖和访问路径依赖。

• 顺序依赖：程序展示的顺序和文件内容的存储顺序并不一致，需要各自独立。
• 索引依赖：如果程序使用索引的时候，索引被删除那么程序将出错。
• 访问路径依赖：访问数据的时候依赖数据的物理结构。

关系指的是数学意义上的关系，对于给定集合S1,S2,S3…Sn，R是n个集合上的关系，如果它是n个元组的集合，每个元组的第一个元素来自S1,第二个来自S2，以此类推。我们称Sj是R上的第j个定义域。R的阶为n(degree n)，阶为1的时候称为一元关系，2的时候称为二元关系，阶为n称为n元关系。

关键原则：

• 将数据存储在简单的数据结构(关系)中
• 物理存储留给DBMS实现
• 通过高级语言访问数据，DBMS确定最佳策略。

结构采用关系。确保数据库内容满足完整性约束。程序通过接口来访问和修改数据库内容。

关系是无序的，n元关系就是n个列的表。一个元组是一行记录。

关系数据库就是基于关系模型的数据库。

其具体的体现形式就是我们通常说的表。

建表语句如下。

1
2
3
4
5
6

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

除此之外，还有一些关系完整性约束。以确保数据的准确性和一致性。常见的完整性约束包括：

• 主键约束（Primary Key）：确保表中每一行的唯一性。
• 外键约束（Foreign Key）：确保表之间的关系完整性。
• 唯一约束（Unique）：确保某一列或一组列的值是唯一的。
• 非空约束（NOT NULL）：确保某一列的值不能为空。
• 检查约束（CHECK）：确保某一列的值满足特定条件。

这里面主键约束是必须的。外键约束通常不使用，因为保证这些约束，会降低数据库的性能。因此外键约束通常由代码层实现。

唯一约束有的时候是可以使用的，来限制唯一性，同样也可以通过代码层实现。

非空约束也是推荐使用的，因为NULL在数据库里面处理起来也有一些麻烦，也会影响性能。

检查约束实际上也基本不使用。

支持SQL标准，实现了SQL标准，最低要求实现SQL92标准。

关系数据库支持事务（Transaction），事务是一组操作的集合，要么全部成功，要么全部失败。事务的四个基本特性（ACID）包括：

• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
• 一致性（Consistency）：事务执行前后，数据库的状态保持一致。
• 隔离性（Isolation）：并发事务之间相互隔离，不会相互干扰。
• 持久性（Durability）：事务一旦提交，其结果是永久性的，即使系统故障也不会丢失。

SQL标准

SQL标准是一系列由国际标准化组织（ISO）和美国国家标准协会（ANSI）制定的规范，旨在确保SQL语言的统一性和兼容性。这些标准定义了SQL语言的语法、语义和行为，使得不同的数据库管理系统（DBMS）能够以一致的方式实现和使用SQL。

主要的SQL标准：

1. SQL-86（1986年）：

• 第一个SQL标准，奠定了SQL语言的基础。
• 定义了基本的SQL语法和功能，包括数据定义语言（DDL）和数据操纵语言（DML）。

2. SQL-89（1989年）：

• 对SQL-86的修订和扩展，增加了对嵌套查询和子查询的支持。
• 也称为SQL1，是第一个广泛接受的SQL标准。

3. SQL-92（1992年）：

• 也称为SQL2，是SQL标准的重大更新。
• 引入了大量新功能，包括：
  • 外键约束（Foreign Key Constraints）
  • 多表连接（JOIN）
  • 嵌套查询（Subqueries）
  • 集合操作（Set Operations）
  • 视图（Views）
  • 完整性约束（Integrity Constraints）
• SQL-92 是目前大多数数据库系统支持的基础标准。

4. SQL:1999（1999年）：

• 引入了对大型对象（LOBs）、窗口函数（Window Functions）、递归查询（Recursive Queries）等的支持。
• 也称为SQL3，是SQL标准的进一步扩展。

5. SQL:2003（2003年）：

• 引入了对XML数据类型和操作的支持。
• 增加了对存储过程、触发器、用户定义类型（UDTs）等的支持。

6. SQL:2006（2006年）：

• 主要关注XML数据的处理和查询。
• 引入了对XQuery的支持。

7. SQL:2008（2008年）：

• 引入了对时间数据类型和操作的支持。
• 增加了对分区表、数据加密等的支持。

8. SQL:2011（2011年）：

• 引入了对多版本并发控制（MVCC）的支持。
• 增加了对分区表的进一步扩展。

9. SQL:2016（2016年）：

• 引入了对JSON数据类型和操作的支持。
• 增加了对窗口函数的进一步扩展。

10. SQL:2019（2019年）：

• 引入了对机器学习和数据分析的支持。
• 增加了对时间序列数据、地理空间数据等的支持。

尽管SQL标准提供了统一的规范，但不同的数据库系统在实现上存在差异。主要的数据库系统（如MySQL、PostgreSQL、Oracle、SQL Server等）都支持SQL标准的核心功能，但在某些高级功能和扩展上可能有所不同。例如：

• MySQL：支持SQL:2003标准，但在某些高级功能（如窗口函数）上支持较晚。
• PostgreSQL：支持SQL:2011标准，对SQL标准的兼容性较好。
• Oracle：支持SQL:2011标准，但在某些功能上有自己的扩展。
• SQL Server：支持SQL:2008标准，但在某些功能上有自己的扩展。

SQL标准是一系列由ISO和ANSI制定的规范，旨在确保SQL语言的统一性和兼容性。不同的数据库系统在实现上存在差异，但都支持SQL标准的核心功能。了解数据库系统对SQL标准的支持情况，可以帮助开发者更好地编写跨数据库的SQL代码，提高代码的可移植性和兼容性。

数据模型

• 关系模型：大多数数据库使用的
• 键值对模型：NOSql使用的，比如redis
• 图模型：NOSql使用的
• 文档模型：NOSql使用的，比如mongoDB
• 列存储模型
• 数组，向量模型：向量数据库，如Fassi
• 分组模型，网络模型，多值模型：已经很少使用的模型

函数依赖

设R为任一给定关系，如果对于R中属性X的每一个值，R中的属性Y只有唯一值与之对应，则称X函数决定Y或称Y函数依赖于X，记作X->Y.其中X称为决定因素

分类：

• 完全函数依赖
• 部分函数依赖
• 传递函数依赖

完全函数依赖：
设R为任一给定关系，X,Y为其属性集，若X->Y，且对X中的任何真子集X’，都有X’不依赖Y,则称Y完全函数依赖于X

部分函数依赖：
设R为任一给定关系，X,Y为其属性集，若X->Y,且X中存在一个真子集X’,都有X’->Y，则称Y部分依赖于X

传递函数依赖：
设R为任一给定关系，X,Y,Z为其不同属性子集，若X->Y,Y不决定X,Y->Z,则有X->Z，称为Z传递函数依赖于X。

设计范式

一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模式的集合，这种过程就叫规范化

第一范式1NF:
设R为任一给定关系，如果R中每个列与行的交点处的取值都是不可再分的基本元素，则R为第一范式

第二范式2NF:
设R为任一给定关系，若R为1NF
且其所有非主属性都完全函数依赖于候选关键字，则R为第二范式。

候选关键字：能唯一表示一行数据的就是候选关键字，比如主键，比如唯一索引等。

第三范式3NF:
设R为任一给定关系，若R为2NF
且其每一个非主属性都不传递函数依赖于候选关键字，则R为第三范式。

第三范式的改进形式BCNF:
设R为任意给定关系，X,Y为其属性集，F为其函数依赖集，若R为3NF
且其F中所有函数依赖X->Y(Y不属于X)中的X比包含候选关键字，则R为BCNF

有部分函数依赖就是1NF，没有就是2NF，没有传递函数依赖就是3NF

1NF->2NF
找到候选关键字，看其余的属性是否完全函数依赖候选关键字
是的，与候选关键字一同抄下来形成一个表格
不是的，抄下来，形成第二个表格，并且将候选关键字里能够唯一决定表格2的属性组抄在第一列

2NF->3NF
找到表格中的传递函数依赖关系的三个属性组，设为X,Y,Z
将这三个属性组拆成两个表格
第一个表格为X,Y
第二个表格为Y,Z

3NF->BCNF
列出表格中的所有函数依赖关系
每个关系拆出一个表格

ER图

ER图是一种图形化的表示方法，用于描述数据库中的实体、实体之间的关系以及实体的属性。它是一种强大的工具，广泛应用于数据库设计和概念建模阶段，帮助开发者和分析师理解数据结构和数据之间的关系。

ER图的主要组成部分

• 实体（Entity）：
  • 实体是数据库中具有相同属性集合的对象。例如，学生、课程、教师等。
  • 在ER图中，实体通常用矩形表示，矩形内写上实体的名称。
• 属性（Attribute）：
  • 属性是实体的特征或性质。例如，学生的属性可以包括学号、姓名、年龄等。
  • 在ER图中，属性通常用椭圆表示，椭圆内写上属性的名称，并用线连接到对应的实体。

下图就是一个ER图，实体是管理员用户。属性有头像、密码、登录名、ID、邮箱、手机号。

• 关系（Relationship）：
  • 关系描述了实体之间的联系。例如，学生和课程之间的关系可以是选修。
  • 在ER图中，关系通常用菱形表示，菱形内写上关系的名称，并用线连接到相关的实体。
• 关系的类型：
  • 一对一关系（1:1）：一个实体与另一个实体之间存在一对一的联系。例如，一个学生对应一个学号。
  • 一对多关系（1:N）：一个实体与多个实体之间存在联系。例如，一个教师可以教授多个课程。
  • 多对多关系（M:N）：多个实体与多个实体之间存在联系。例如，一个学生可以选修多个课程，一个课程也可以被多个学生选修。

下图就是一个ER图，实体是管理员用户和角色两个。关系是拥有，表示管理员用户拥有角色的关系。关系的类型用m和n表示多对多关系。意思是一个管理员用户可以拥有多个角色，一个角色也可以被多个管理员用户拥有。

画ER图可以使用processOn

OLTP

OLTP（Online Transaction Processing，联机事务处理）数据库是一种用于支持日常事务处理和业务操作的数据库系统。它旨在快速处理大量的在线事务，确保数据的完整性和一致性。OLTP数据库广泛应用于各种需要实时数据处理的场景，如银行系统、电子商务平台、企业资源规划（ERP）系统等。

OLTP数据库

• 通常是业务侧使用的传统数据库，比如oracle,postgresql,mysql
• 小的业务多次执行，比如多个简单的插入，更新，查询

OLTP数据库的主要特点

事务性：

• OLTP数据库的核心是事务处理。事务是一组操作的集合，要么全部成功，要么全部失败。OLTP系统支持ACID（原子性、一致性、隔离性、持久性）特性，确保事务的可靠性。
• 原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。
• 一致性（Consistency）：事务执行前后，数据库的状态保持一致。
• 隔离性（Isolation）：并发事务之间相互隔离，不会相互干扰。
• 持久性（Durability）：事务一旦提交，其结果是永久性的，即使系统故障也不会丢失。

高性能：

• OLTP数据库设计用于快速处理大量的在线事务。它们通常具有高效的索引、优化的查询计划和低延迟的响应时间。
• 例如，银行系统需要在几毫秒内完成交易，OLTP数据库能够满足这种高性能要求。

数据完整性：

• OLTP数据库通过各种完整性约束（如主键、外键、唯一约束、非空约束等）确保数据的准确性和一致性。
• 例如，一个订单表中的订单号必须是唯一的，客户表中的客户ID必须存在。

并发处理：

• OLTP数据库支持高并发访问，允许多个用户同时进行事务操作。它们通过锁机制和事务隔离级别来管理并发事务，确保数据的一致性和完整性。
• 例如，多个用户可以同时在电子商务平台上下单，OLTP数据库能够处理这些并发请求。

实时性：

• OLTP数据库提供实时数据处理，用户可以即时看到事务的结果。这使得OLTP系统适用于需要即时反馈的业务场景，如在线支付、库存管理等。
• 例如，用户在电子商务平台上下单后，系统立即更新库存信息并生成订单。

OLTP数据库的常见应用场景

银行系统：

• 用于处理日常的银行交易，如存款、取款、转账等。这些交易需要快速、准确地完成，确保数据的一致性和完整性。
  电子商务平台：
• 用于处理用户的订单、支付、库存管理等。这些系统需要支持高并发访问，确保用户能够实时看到订单状态和库存信息。
  企业资源规划（ERP）系统：
• 用于管理企业的各种资源，如人力资源、财务资源、生产资源等。这些系统需要处理大量的事务，确保数据的准确性和一致性。
  客户关系管理（CRM）系统：
• 用于管理客户信息、销售机会、客户服务等。这些系统需要支持实时数据处理，确保客户信息的准确性和及时性。

OLTP数据库是一种用于支持日常事务处理和业务操作的数据库系统。它具有事务性、高性能、数据完整性、并发处理和实时性等特点，广泛应用于银行系统、电子商务平台、企业资源规划（ERP）系统等。常见的OLTP数据库管理系统包括MySQL、PostgreSQL、Oracle Database、Microsoft SQL Server和SQLite。

OLAP

OLAP（Online Analytical Processing，联机分析处理）数据库是一种用于支持复杂的数据分析和报告的数据库系统。它旨在快速处理大量的数据，提供多维度的数据分析能力，帮助用户从不同角度理解数据。OLAP数据库广泛应用于数据仓库、商业智能（BI）和决策支持系统。

OLAP数据库

• 通常是大数据，数据分析来使用，比如Hbase等，支持复杂的数据查询
• OLAP位于OLTP的后方

OLAP数据库的主要特点

多维数据分析：

• OLAP数据库支持多维数据分析，允许用户从不同角度（如时间、地区、产品等）查看数据。这种多维视图有助于用户发现数据中的模式和趋势。
• 例如，用户可以按季度、地区和产品类别分析销售数据。

高性能查询：

• OLAP数据库优化了对大量数据的读取操作，能够快速响应复杂的查询请求。它们通常使用预计算的聚合数据和索引技术来提高查询性能。
• 例如，数据仓库中的销售数据可以按季度、地区和产品类别预计算，以便快速生成报告。

数据聚合：

• OLAP数据库支持数据的聚合操作，如求和、平均、最大值、最小值等。这些聚合操作通常在数据加载时预先计算，以便快速生成报告。
• 例如，销售数据可以按季度、地区和产品类别进行汇总，以便快速生成销售报告。

数据立方体（Data Cube）：

• OLAP数据库使用数据立方体（Data Cube）来组织数据。数据立方体是一个多维数据结构，允许用户从不同维度查看数据。
• 例如，一个销售数据立方体可以包含时间维度（年、季度、月）、地区维度（国家、城市）和产品维度（类别、品牌）。

数据更新：

• OLAP数据库通常不需要实时更新，数据通常在定期的时间间隔内批量加载。这使得OLAP数据库能够优化读取操作，提高查询性能。
• 例如，销售数据可以在每天晚上批量加载到数据仓库中。

OLAP数据库的常见应用场景

数据仓库：

• 用于存储和管理企业的历史数据，支持复杂的查询和报告。数据仓库通常从多个数据源（如事务处理系统）提取数据，进行清洗和转换，然后加载到数据仓库中。
• 例如，企业可以使用数据仓库来分析销售趋势、客户行为等。

商业智能（BI）：

• 用于支持企业的决策支持系统，提供数据驱动的决策支持。商业智能工具通常与OLAP数据库集成，提供交互式的报告和分析功能。
• 例如，企业可以使用商业智能工具生成销售报告、市场分析报告等。

决策支持系统（DSS）：

• 用于支持企业的决策过程，提供数据驱动的决策支持。决策支持系统通常使用OLAP数据库来存储和分析数据。
• 例如，企业可以使用决策支持系统来优化生产计划、资源分配等。

OLAP数据库是一种用于支持复杂的数据分析和报告的数据库系统。它具有多维数据分析、高性能查询、数据聚合、数据立方体和数据更新等特点，广泛应用于数据仓库、商业智能（BI）和决策支持系统。常见的OLAP数据库管理系统包括HBase,ClickHouse等。

高级概念

接下来在介绍一下MySQL中会用到的高级概念，这些能更好的帮助大家理解除了正常的表以外的其他东西。

视图

在MySQL中，视图（View）是一种虚拟表，其内容由SQL查询定义。视图并不存储实际的数据，而是根据定义的查询动态生成数据。视图可以简化复杂的SQL操作，提供数据的逻辑抽象，并且可以限制对某些数据的访问，从而增强数据的安全性。

MySQL中的视图是虚拟视图，说白了就是一条SQL语句，当查询视图的时候执行SQL语句而已。

除此之外，还有一个东西叫做物化视图，MySQL并没有实现这个东西，物化视图就是一张真的表，而不是一个SQL语句，因此查询效率更好。

视图的主要特点

虚拟表：

• 视图是一个虚拟表，其内容由SQL查询定义。视图本身并不存储数据，而是根据定义的查询动态生成数据。

简化复杂查询：

• 视图可以简化复杂的SQL操作，将复杂的查询逻辑封装起来，使用户可以像查询普通表一样查询视图。

数据抽象：

• 视图提供数据的逻辑抽象，隐藏了底层表的复杂性，使用户可以更直观地访问数据。

安全性：

• 视图可以限制对某些数据的访问，增强数据的安全性。通过视图，用户只能访问视图定义的特定数据，而不能访问底层表的全部数据。

更新限制：

• 视图可以是可更新的，也可以是不可更新的。可更新视图允许用户通过视图插入、更新或删除数据，但需要满足一定的条件。不可更新视图则不允许用户通过视图修改数据。

创建一个视图。下面的语句，创建一个视图，视图名称是sales_employees，内容就是后面的Select语句的结果。当原始表employees变化以后，视图的内容也会跟着变化。

1
2
3
4

CREATE VIEW sales_employees AS
SELECT name, salary
FROM employees
WHERE department = 'Sales';

触发器

在MySQL中，触发器（Trigger）是一种特殊的存储过程，它在特定的数据库操作（如INSERT、UPDATE、DELETE）发生时自动执行。触发器可以用于实现复杂的业务逻辑，确保数据的完整性和一致性，以及自动维护数据的同步。

触发器的主要特点

自动执行：

• 触发器在特定的数据库操作发生时自动执行，无需显式调用。这使得触发器可以用于实现自动化的数据处理和维护。

数据完整性：

• 触发器可以用于确保数据的完整性和一致性。例如，可以在插入或更新数据时自动检查数据的有效性，或者在删除数据时自动清理相关数据。

业务逻辑：

• 触发器可以用于实现复杂的业务逻辑。例如，可以在插入或更新数据时自动计算某些字段的值，或者在删除数据时自动更新相关表的数据。

数据同步：

• 触发器可以用于自动维护数据的同步。例如，可以在插入或更新数据时自动更新相关表的数据，或者在删除数据时自动清理相关表的数据。

触发器的类型

• BEFORE INSERT：
  • 在插入数据之前执行触发器逻辑。
• AFTER INSERT：
  • 在插入数据之后执行触发器逻辑。
• BEFORE UPDATE：
  • 在更新数据之前执行触发器逻辑。
• AFTER UPDATE：
  • 在更新数据之后执行触发器逻辑。
• BEFORE DELETE：
  • 在删除数据之前执行触发器逻辑。
• AFTER DELETE：
  • 在删除数据之后执行触发器逻辑。

触发器的限制

性能影响：

• 触发器的执行会增加数据库操作的开销，可能会影响性能。因此，应谨慎使用触发器，避免在高频操作的表上定义过多的触发器。
  复杂性：
• 触发器的逻辑可以非常复杂，但过多的复杂逻辑可能导致触发器难以维护和调试。因此，应尽量保持触发器的逻辑简单明了。
  调试困难：
• 触发器的调试相对困难，因为它们在特定的操作发生时自动执行，难以直接观察和调试。因此，建议在开发和测试阶段充分测试触发器的逻辑。

因此，实际开发中基本不使用触发器。

存储过程

在MySQL中，存储过程（Stored Procedure）是一种预编译的SQL语句集合，它存储在数据库中，可以通过调用其名称并传递参数来执行。存储过程可以包含复杂的逻辑和多个SQL语句，用于完成特定的任务。它们类似于其他编程语言中的函数或方法。

可以把存储过程想成一个函数。只不过是在MySQL中的函数，这个函数可以实现各种功能。可以实现一些复杂的SQL处理，这样可以简化调用。

存储过程的主要特点

预编译：

• 存储过程在创建时被预编译并存储在数据库中，这使得它们的执行速度比单独的SQL语句更快。
  代码重用：
• 存储过程可以被多次调用，减少了代码重复，提高了开发效率。
  减少网络流量：
• 存储过程在服务器端执行，减少了客户端和服务器之间的网络流量，因为只需要发送存储过程的名称和参数，而不是大量的SQL语句。
  安全性：
• 存储过程可以限制用户对底层数据的直接访问，只允许通过存储过程进行数据操作，从而增强数据的安全性。
  事务管理：
• 存储过程可以包含事务控制语句，如COMMIT和ROLLBACK，确保数据操作的完整性和一致性。

创建一个存储过程get_employee_details，用于根据员工ID获取员工的详细信息：

DELIMITER用来设置结束符，比如正常的句子结束符是句号。代码结束符是分号；

IN代表输入参数，也就是这个函数有一个输入参数emp_id，是int类型。
还有out代表输出参数，用于返回结果。
INOUT代表既可以输入参数也可以是输出参数。

1
2
3
4
5
6
7
8
9
10

DELIMITER //

CREATE PROCEDURE get_employee_details(IN emp_id INT)
BEGIN
    SELECT name, department, salary
    FROM employees
    WHERE id = emp_id;
END //

DELIMITER ;

上面这个存储过程的函数体，就是一段select查询语句。

存储过程的限制

性能影响：

• 存储过程的执行会增加数据库操作的开销，可能会影响性能。因此，应谨慎使用存储过程，避免在高频操作的表上定义过多的存储过程。
  复杂性：
• 存储过程的逻辑可以非常复杂，但过多的复杂逻辑可能导致存储过程难以维护和调试。因此，应尽量保持存储过程的逻辑简单明了。
  调试困难：
• 存储过程的调试相对困难，因为它们在服务器端执行，难以直接观察和调试。因此，建议在开发和测试阶段充分测试存储过程的逻辑。

存储函数

在MySQL中，存储函数（Stored Function）是一种预编译的SQL语句集合，类似于存储过程，但它返回一个值。存储函数可以被SQL语句直接调用，就像调用普通的函数一样。存储函数通常用于封装复杂的逻辑，并在查询中重用这些逻辑。

存储函数同样是一个函数，和上面的存储过程差不多。

存储过程和存储函数的区别

返回值的区别

• 存储过程
  • 存储过程可以返回多个值，这些值通过OUT或INOUT参数返回。
  • 存储过程可以执行多个SQL语句，但不直接返回一个单一的值。
• 存储函数
  • 存储函数必须返回一个单一的值。
  • 存储函数可以被SQL语句直接调用，就像调用普通的函数一样。

调用方式的区别

• 存储过程
  • 存储过程通过CALL语句调用。
  • 存储过程可以执行复杂的逻辑，包括多个SQL语句和事务控制。
• 存储函数
  • 存储函数可以直接在SQL语句中调用，就像调用普通的函数一样。
  • 存储函数通常用于封装复杂的逻辑，并在查询中重用这些逻辑。

存储函数的主要特点

返回值：

• 存储函数必须返回一个值，这使得它们可以被SQL语句直接调用。
  代码重用：
• 存储函数可以被多次调用，减少了代码重复，提高了开发效率。
  减少网络流量：
• 存储函数在服务器端执行，减少了客户端和服务器之间的网络流量，因为只需要发送函数的名称和参数，而不是大量的SQL语句。
  安全性：
• 存储函数可以限制用户对底层数据的直接访问，只允许通过函数进行数据操作，从而增强数据的安全性。
  事务管理：
• 存储函数可以包含事务控制语句，如COMMIT和ROLLBACK，确保数据操作的完整性和一致性。

创建一个存储函数get_employee_salary，用于根据员工ID获取员工的薪资：

可以看到和上面存储过程的区别，声明了一个返回值，类型是DECIMAL,最后通过return返回了，并且声明了一个变量。参数也没有IN、OUT这种了。

1
2
3
4
5
6
7
8
9
10
11
12
13

DELIMITER //

CREATE FUNCTION get_employee_salary(emp_id INT)
RETURNS DECIMAL(10, 2)
BEGIN
    DECLARE emp_salary DECIMAL(10, 2);
    SELECT salary INTO emp_salary
    FROM employees
    WHERE id = emp_id;
    RETURN emp_salary;
END //

DELIMITER ;

window窗口函数

窗口函数（Window Function）是SQL标准中的一种功能强大的工具，它允许在查询中对一组行进行计算，而不会像聚合函数那样消除行的个数。窗口函数在MySQL 8.0及更高版本中得到了支持，它们可以用于计算移动平均值、累积和、排名等复杂的分析任务。

窗口函数的主要特点

行级计算：

• 窗口函数在每一行上执行计算，同时可以访问同一组中的其他行。
• 这与聚合函数不同，聚合函数会将多行数据合并为一行。
  分区和排序：
• 窗口函数可以使用PARTITION BY子句将数据分成多个分区，每个分区独立计算。
• 可以使用ORDER BY子句在每个分区内对数据进行排序。
  灵活的范围定义：
• 窗口函数可以定义计算的范围，如当前行的前几行或后几行。
• 使用ROWS或RANGE子句可以指定计算的范围。
  多种功能：
• 窗口函数提供了多种功能，如ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、SUM()、AVG()、LEAD()、LAG()等。

ROW_NUMBER和RANK都需要和OVER一起使用。

• ROW_NUMBER(): 显示当前行号
• RANK() : 显示排序后的排名，如果没有排序，都是1
• OVER()
  • PARTITION BY 进行分组
  • GROUP BY 进行分组
  • ORDER BY 排序

获取每个课程中分数最高的学生信息

下面的SQL，在postgresql中执行成功，mysql8执行报错。

首先查询所有课程信息，并按照课程分组，按照分数排序。

1
2
3

SELECT *,
RANK() OVER (PARTITION BY cid ORDER BY grade ASC)
AS rank FROM enrolled

接着搜索上表中分数为1，也就是分数最高的学生。也就是每个课分数最高的学生信息。

1
2
3
4
5
6

SELECT * FROM (
    SELECT *,
    RANK() OVER (PARTITION BY cid
    ORDER BY grade ASC)
    AS rank FROM enrolled) AS ranking
WHERE ranking.rank = 1

CTE(common table expressions)

在MySQL中，CTE（Common Table Expressions，公共表表达式）是一种临时的结果集，可以在查询中被引用。CTE通常用于简化复杂的查询，使查询更易于理解和维护。CTE在MySQL 8.0及更高版本中得到了支持。

CTE的主要特点

临时结果集：

• CTE是一个临时的结果集，可以在查询中被引用。它类似于子查询，但更易于阅读和维护。
  简化复杂查询：
• CTE可以将复杂的查询分解为多个简单的部分，使查询更易于理解和维护。
  可重用性：
• CTE可以被多次引用，减少了代码重复，提高了开发效率。
  递归查询：
• CTE支持递归查询，可以用于处理层次结构或递归数据。

使用CTE实现获取每个课程中分数最高的学生信息。

通过WITH语句来声明一个临时表。表名cteSource，表的内容就是最的sid，通过SELECT MAX(sid) FROM enrolled查询出来的结果。字段名叫maxId。

然后在查询语句里面就可以连接cteSource表，然后通过sid = cteSource.maxId 来获取到sid最大的用户信息。

1
2
3
4
5

WITH cteSource (maxId) AS (
    SELECT MAX(sid) FROM enrolled
)
SELECT name FROM student, cteSource
WHERE student.sid = cteSource.maxId

还有一些其他的用法，比如:

1
2
3
4
5
6
7

WITH cte1 (col1) AS (
SELECT 1
),
cte2 (col2) AS (
SELECT 2
)
SELECT * FROM cte1, cte2;

索引

总结

介绍了上面的概念，我们现在明白了MySQL数据库是什么了吧。

MySQL数据库是

• OLTP数据库
• 基于关系模型的关系数据库管理系统
• 实现了SQL标准，可以使用SQL进行控制，并扩展了自己的东西。
• 可以使用ER图来设计关系模型
• 具有一些高级特性

文末福利

以上就是整体的MySQL学习路线了。

关注我发送“MySQL知识图谱”领取完整的MySQL学习路线。

发送“电子书”即可领取价值上千的电子书资源。

部分电子书如图所示。

JAVA中的函数式编程

optional并非银弹

首先，我们都知道，optional类型要更加安全，如果使用的好，不会出现空指针异常，因为它不会返回null。

但是注意，这里的前提是使用的好。

比如 下面这两段代码。这里的 optionalInt.get().toString() 并不会比 num.toString() 安全，如果optionInt.get()返回的是一个null，还是会触发空指针异常。

1
2

Optional<Integer> optionalInt = Optional.of(12);
optionalInt.get().toString();

1
2

Integer num = 12;
num.toString();

所以，optional并不是处理空指针的银弹，而是需要正确的使用它。

如果正确的使用optional

isPresent和ifPresent这两个方法。

• isPresent 是一个判断，类似于 num != null
• ifPresent 接受一个 lambda 表达式或者方法，如果存在的话就调用该方法。

1
2
3
4
5
6
7
8
9

Optional<Integer> optionalInt = Optional.of(12);
int num;
optionalInt.ifPresent(i -> {
    num = i;
});

if (optionalInt.isPresent()) {
    num = optionalInt.get();
}

这里更推荐的是使用 ifPresent 方法，更加安全方便。

为什么呢？因为你只是为了判断这么一下的话，完全可以使用 num != null 来代替 optionalInt.isPresent。毕竟这样还省去了包装optional的步骤，效果则是一样的。

1
2
3
4
5
6
7
8

if (optionalInt.isPresent()) {
    num = optionalInt.get();
}

int a;
if (a != null) {
    num = a * 2;
}

但是 ifPresent 方法只负责处理，并不返回任何值。

如果你想要返回值的话，可以使用map方法代替。他返回一个bool值，被封装到optional中的true或者false(根据optionalInt是否存在)，也可能是个空值。

1
2
3

Optional<Boolean> res = optionalInt.map(i -> {
    num = i;
});

那么在日常使用中，还会有默认值的情况，比如，如果int值存在我就赋值给num，不存在我就赋值0。这个时候就可以使用下面这三个方法

• orElse 如果有值，返回值，如果没有值，返回你给的默认值。
• orElseGet 和上面的效果一样，只是可以传一个lambda表达式
• orElseThrow 和上面的效果一样，没有值的时候返回一个异常。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Optional<Integer> optionalInt = Optional.of(12);
num = optionalInt.orElse(0); //这里有值，所以返回12

Optional<Integer> optionalInt = Optional.empty();
num = optionalInt.orElse(0); //这里没有值，所以返回默认值0

Optional<Integer> optionalInt = Optional.empty();
// 传一个默认值方法
num = optionalInt.orElseGet(() -> {
    return 0;
});

Optional<Integer> optionalInt = Optional.empty();
// 如果没有值，返回一个异常
return optionalInt.orElseThrow(() -> {
        return new RuntimeException("异常了");
});

通过faltMap方法实现optional链式操作。首先通过of方法创建一个Optional<Integer>类型的12。然后通过flatMap方法把这个Integer的12传递给doubleInt方法。doubleInt方法处理完以后返回一个Optional<Integer>类型的24。

因为返回的还是一个Optional。所以还可以继续调用flatMap方法。将24传给intToStr方法。将24转换成String类型。然后返回一个Optional<String>类型的24.

ofNullable方法的作用是如果你给的值存在就调用of方法创建一个Optional。如果不存在就调用empty方法创建一个空的Optional。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

public String optionalMap() {
    Optional<String> res = Optional.of(12).flatMap(this::doubleInt).flatMap(this::intToStr);
    return res.get();
}

// 把一个数转换成string
public Optional<String> intToStr(int x) {
    return Optional.ofNullable(String.valueOf(x));
}

// 把一个数 * 2
public Optional<Integer> doubleInt(int x) {
    return Optional.ofNullable(x << 1);
}

转语言者速学java

接口的默认方法

java的接口里面可以写方法体，这样的话，所有实现了该接口的，都自动具有了该方法而不用实现方法内容。当然了，也可以重写方法。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

public interface IGuideService {

    public default Integer getNum() {
        return 1;
    }
}

public class GuideService implements IGuideService {


}

public class Main {

    public static void main(String [] args) {
        IGuideService guideService = new GuideService;
        System.out.println(guideService.getNum());
    }
}

但是java可以实现多个接口，所以就可能会有二义性的问题，C++解决问题的方法是虚基类，java则是简单粗暴的。

• 如果是父类中和接口中的方法冲突了。那么选择父类中的方法。
• 如果是多个接口中的方法冲突了，那么你必须通过覆盖该方法来手动解决冲突。

看一下如果父类中也有getNum方法。创建一个父类返回2.再次执行以后会发现返回2了。

1
2
3
4
5
6
7
8
9
10
11
12

public class BaseGuideService {

    public Integer getNum() {
        return 2;
    }
}

// 继承父类
public class GuideService extends BaseGuideService implements IGuideService {


}

接下来不用父类，然后创建两个接口试试。

1
2
3
4
5
6
7
8
9
10
11
12

public interface IGuideNewService {

    public default Integer getNum() {
        return 3;
    }
}

// 实现两个接口
public class GuideService implements IGuideService,IGuideNewService {


}

这个时候编译就会报错了。

GuideService 从 IGuideService 和 IGuideNewService 中继承了getNum() 的不相关默认值

那如果这两个接口是父子接口呢？这个时候就不会报错了，因为相当于子接口重写了这个方法。所以只存在一个getNum方法了。

我们来看如果是单独的两个接口，那么需要我们手动来解决冲突。重写一下这个方法。

1
2
3
4
5
6
7
8
9

// 实现两个接口
public class GuideService implements IGuideService, IGuideNewService {

    @Override
    public Integer getNum() {
        return 5;
    }
}

在重写的时候也可以选择使用两个接口的某一个。

1
2
3
4
5
6
7
8
9
10
11
12

// 实现两个接口
public class GuideService implements IGuideService, IGuideNewService {

    @Override
    public Integer getNum() {
        return IGuideService.super.getNum();

        //或者使用另一个
        //return IGuideNewService.super.getNum();
    }
}

日期时间

在java中的时间，有几个概念

• 某一个具体的时间点：Instant
• 持续时间，两个时间点之间的时间：Duration
• 本地时间，没有时区信息的：LocalDate,LocalTime,LocalDateTime
• 带时区的时间：ZonedDateTime
• 处理时区信息时，时间段使用 Period 而不是 Duration
• 时间格式化处理： DateTimeFormatter
• 日历计算，比如查找月份的第一天，第二天，第一周，第二周的星期二：TemporalAdjuster

获取当前的一个时间点。

1

Instant.now();

获取一段时间，可以用来求运行速度。start是处理开始的时间点。end是处理完成的时间点。再通过Duration来求出一段时间.

1
2
3
4
5
6

Instant start = Instant.now();
//处理逻辑
Instant end = Instant.now();

Duration time = Duration.between(start,end);
long mills = time.toMills();

获取本地当前时间

1

LocalDateTime time = LocalDateTime.now();

时间格式化

1
2

LocalDateTime time = LocalDateTime.now();
time.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

获取带时区的当前时间

1

ZonedDateTime time = ZonedDateTime.now();

想看更多时间处理的，可以参考 LocalDateTime源码解析

stream

stream可以帮我们处理很多东西。比如

除了这些还可以实现比如把list转换成逗号分隔的字符串。把list经过处理以后转换成set或者map。

还可以变成指定值为key,指定值为value的map。比如id为key，name为值或者map为值的map。

具体的可以参考下面三个文章。

• stream数据结构和原理
• stream的map,filter,sorted等使用和源码
• stream的collect,foreach等使用和源码

optional

java中的optional可以帮助你避免空指针异常.

具体的可以参考optional并非银弹

函数式编程和lambda

注解

• @Autowired Spring提供的，基于类型注入的，可以放在setter方法上，变量上，构造函数上
• @Inject 同@Autowired
• @Qualifier Spring提供的，基于名称注入的，一般和@Autowired配合使用来通过value参数指定名称
• @Name 同@Qualifier，给@Inject配合使用
• @Primary
• @Resource Java提供的，可以基于类型或名称注入的,可以通过name参数来指定名称，可以放在setter方法上
• @RequiredArgsConstructor lombok提供的，基于类型注入，通过增加一个构造函数来注入。
• @Value Spring提供的，注入基本类型的注解，一般用来从配置文件取值。
• @Component 单纯的说我是一个bean
• @Service 和上面的一样，不过一般用在service类中，更加语义化
• @Controller 和上面的一样，一般用在controller类中
• @Repository 我也是一个bean，一般用在dao数据访问层
• @Bean 我也是一个bean，导入第三方包里面的注解
• @Import 导入组件
• @ImportSelector 返回需要导入的组件的全类名数组
• @ImportBeanDefinitionRegistrar 手动注册Bean到容器
• @JsonIgnore json的时候忽略属性
• @Bean(initMethod=”init”,destoryMethod=”destory”) 设置初始化和销毁方法
• @PostConstruct：初始化方法
• @PreDestory：销毁方法
• BeanPostProcessor：bean的后置处理器，在bean初始化前后进行一些处理工作
• @Configuration 声明为配置类
• @ComponentScan 扫描Component
• @Aspect 声明一个切面
• @After 在方法执行之后执行（方法上）
• @Before 在方法执行之前执行（方法上）
• @Around 在方法执行之前与之后执行（方法上）
• @PointCut 声明切点
• @EnableAspectJAutoProxy 启Spring对AspectJ代理的支持
• @Profile 指定组件在哪个环境的情况下才能被注册到容器中，不指定，任何环境下都能注册这个组件。
• @Conditional 通过实现Condition接口，并重写matches方法，从而决定该bean是否被实例化。
• @EnableAsync 配置类中通过此注解开启对异步任务的支持
• @Async 在实际执行的bean方法使用该注解来声明其是一个异步任务
• @EnableScheduling 在配置类上使用，开启计划任务的支持
• @Scheduled 来申明这是一个任务，包括cron,fixDelay,fixRate等类型
• @EnableConfigurationProperties：开启对@ConfigurationProperties注解配置Bean的支持；
• @EnableJpaRepositories：开启对SpringData JPA Repository的支持；
• @EnableTransactionManagement：开启注解式事务的支持；
• @EnableCaching：开启注解式的缓存支持；
• @EnableAspectAutoProxy：开启对AspectJ自动代理的支持；
• @EnableWebMvc 在配置类中开启Web MVC的配置支持。
• @RequestMapping 用于映射web请求，包括访问路径和参数。
• @ResponseBody 支持将返回值放到response内，而不是一个页面，通常用户返回json数据。
• @RequestBody 允许request的参数在request体中，而不是在直接连接的地址后面。
• @PathVariable 用于接收路径参数，比如@RequestMapping(“/hello/{name}”)声明的路径，将注解放在参数前，即可获取该值，通常作为Restful的接口实现方法。
• @RestController 该注解为一个组合注解，相当于@Controller和@ResponseBody的组合，注解在类上，意味着，该Controller的所有方法都默认加上了@ResponseBody。
• @ControllerAdvice 全局异常处理 全局数据绑定 全局数据预处理
• @ExceptionHandler 用于全局处理控制器里的异常。
• @InitBinder 用来设置WebDataBinder，WebDataBinder用来自动绑定前台请求参数到Model中。
• @ModelAttribute
• @Transactional
  • name 当在配置文件中有多个 TransactionManager , 可以用该属性指定选择哪个事务管理器。
  • propagation 事务的传播行为，默认值为 REQUIRED。
    • REQUIRED 如果当前存在事务，则加入该事务，如果当前不存在事务，则创建一个新的事务。( 也就是说如果A方法和B方法都添加了注解，在默认传播模式下，A方法内部调用B方法，会把两个方法的事务合并为一个事务 ）
    • SUPPORTS 如果当前存在事务，则加入该事务；如果当前不存在事务，则以非事务的方式继续运行。
    • MANDATORY 如果当前存在事务，则加入该事务；如果当前不存在事务，则抛出异常。
    • REQUIRES_NEW 重新创建一个新的事务，如果当前存在事务，暂停当前的事务。
    • NOT_SUPPORTED 以非事务的方式运行，如果当前存在事务，暂停当前的事务。
    • NEVER 以非事务的方式运行，如果当前存在事务，则抛出异常。
    • NESTED 和 REQUIRED 效果一样。
  • isolation 事务的隔离度，默认值采用 DEFAULT
  • timeout 事务的超时时间，默认值为-1。如果超过该时间限制但事务还没有完成，则自动回滚事务。
  • read-only 指定事务是否为只读事务，默认值为 false；为了忽略那些不需要事务的方法，比如读取数据，可以设置 read-only 为 true。
  • rollback-for 用于指定能够触发事务回滚的异常类型，如果有多个异常类型需要指定，各类型之间可以通过逗号分隔。
  • no-rollback- for 抛出 no-rollback-for 指定的异常类型，不回滚事务。
• @Schema 表示此类对应的数据库表对应的schema。
• @JsonFormat 可以方便的把Date类型直接转化为我们想要的模式
• @Transient 如果一个属性并非数据库表的字段映射，就务必将其标示为@Transient
• @JsonProperty 可以指定某个属性和json映射的名称
• @Scope设置类型包括：

设置Spring容器如何新建Bean实例（方法上，得有@Bean）

① Singleton

（单例,一个Spring容器中只有一个bean实例，默认模式）,

② Protetype

（每次调用新建一个bean）,

③ Request

（web项目中，给每个http request新建一个bean）,

④ Session

（web项目中，给每个http session新建一个bean）,

⑤ GlobalSession

（给每一个 global http session新建一个Bean实例）

stream

stream的中间态

中间态的主要作用是构建双向链表的中间节点。一个操作对应一个节点。比如map，就会创建一个节点。其中pre指针指向前一个节点也就是头节点。而头节点的next指针指向map节点。

filter操作的时候同样创建一个节点，pre指针指向上一个操作也就是map节点。map节点的next指针指向filter节点。

每个中间态节点中都存储了操作，也就是中间态的时候传入的函数。而数据则全部在头节点中。

比如下面这样：

每个中间态节点其实又分成两种

• 有状态节点
• 无状态节点

类图如下：

中间态节点的几个操作如下：

我们目前的代码中使用了两个中间态的方法。

• map
• filter

map

我们调用的是ReferencePipeline类的map方法。作为中间态方法，需要链式操作，所以返回值当然是一个stream了。接受一个函数作为入参，可以是一个写好的函数，也可以是一个lambda表达式的函数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

public final <R> Stream<R> map(Function<? super P_OUT, ? extends R> mapper) {
    //参数校验
    Objects.requireNonNull(mapper);
    //创建一个无状态的对象
    //第一个参数是this，也就是刚才初始化好的只有一个头节点的双向链表。
    //第二个参数是一个常量 REFERENCE
    //第三个参数是 8 & 2 = 1000 & 0010 = 1010 = 10
    return new StatelessOp<P_OUT, R>(this, StreamShape.REFERENCE,
                                    StreamOpFlag.NOT_SORTED | StreamOpFlag.NOT_DISTINCT) {

        //增加了opWrapSink方法，第一个参数是标志 = 95，第二个是sink节点
        //这个方法会在结果态的时候调用
        @Override
        Sink<P_OUT> opWrapSink(int flags, Sink<R> sink) {
            //创建一个 Sink.ChainedReference类的对象并返回。具体的放在结果态里面讲。
            return new Sink.ChainedReference<P_OUT, R>(sink) {
                @Override
                public void accept(P_OUT u) {
                    downstream.accept(mapper.apply(u));
                }
            };
        }
    };
}

//无状态节点的构造方法
//第一个参数是this，也就是刚才初始化好的只有一个头节点的双向链表。
//第二个参数是一个常量 REFERENCE
//第三个参数是 8 & 2 = 1000 & 0010 = 1010 = 10
StatelessOp(AbstractPipeline<?, E_IN, ?> upstream,
            StreamShape inputShape,
            int opFlags) {
    //调用父类的构造函数
    super(upstream, opFlags);
    // upstream.getOutputShape() 返回的就是 REFERENCE， inputShape = REFERENCE，
    // 所以返回true
    assert upstream.getOutputShape() == inputShape;
}

//巧了，他的父类也是ReferencePipeline，所以又来到了它的构造方法，不过和上次那个不是同一个了。
//这个作用是构造一个中间态的节点，加入到双向链表也就是加入到流中。
//第一个参数是 双向链表
//第二个是 10
ReferencePipeline(AbstractPipeline<?, P_IN, ?> upstream, int opFlags) {
    //再次调用父类的构造方法
    super(upstream, opFlags);
}

//这里同样来到了AbstractPipeline的构造方法。
//这个方法的作用是构造一个中间态的节点。
//第一个参数是上一个节点，这里上一个就是头节点。
//第二个参数是10
AbstractPipeline(AbstractPipeline<?, E_IN, ?> previousStage, int opFlags) {
    //判断上一个节点是否连接了消费者，如果连接了就报错。
    if (previousStage.linkedOrConsumed)
        //抛出非法状态异常
        throw new IllegalStateException(MSG_STREAM_LINKED);

    //把上一个节点的是否连接消费者 = 1
    previousStage.linkedOrConsumed = true;
    //上一个节点的 next 指针指向当前节点。
    previousStage.nextStage = this;

    //当前节点的头指针指向上一个节点
    this.previousStage = previousStage;
    //10 & 16777407 = 10
    this.sourceOrOpFlags = opFlags & StreamOpFlag.OP_MASK;
    //组合当前节点的标志和上一个节点的标志生成新的标志 生成的新标志 = 90
    this.combinedFlags = StreamOpFlag.combineOpFlags(opFlags, previousStage.combinedFlags);
    //当前节点的数据 = 上一个节点的数据 = list
    this.sourceStage = previousStage.sourceStage;
    //当前节点是否是有状态的节点，这里是false
    if (opIsStateful())
        sourceStage.sourceAnyStateful = true;
    //深度+1 = 1
    this.depth = previousStage.depth + 1;
}

//组合当前节点的标志和上一个节点的标志生成新的标志
//第一个参数10
//第二个参数95
static int combineOpFlags(int newStreamOrOpFlags, int prevCombOpFlags) {
    // 0x01 or 0x10 nibbles are transformed to 0x11
    // 0x00 nibbles remain unchanged
    // Then all the bits are flipped
    // Then the result is logically or'ed with the operation flags.
    // 95 & -16 ｜ 10 = 90
    return (prevCombOpFlags & StreamOpFlag.getMask(newStreamOrOpFlags)) | newStreamOrOpFlags;
}

@Override
final StreamShape getOutputShape() {
    return StreamShape.REFERENCE;
}

到这里map方法就执行结束了。可以看到这里依然没有真正的执行map方法。只是封装成了一个中间态的节点并加入了双向链表。并将数据list和操作mapper都存入了节点。如下。

filter

filter和map一样，作为中间态的方法。来看一下它的源码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

// 同样需要返回一个stream对象。
// 同样接受一个方法或lambda表达式
public final Stream<P_OUT> filter(Predicate<? super P_OUT> predicate) {
    //参数校验
    Objects.requireNonNull(predicate);
    //同样创建一个无状态的节点并返回
    //第一个参数是this，是包含了头节点和map节点的链表。
    //第二个参数是一个常量 REFERENCE
    //第三个参数是 128
    //具体的代码和map的执行是一样的创建。并没有区别。不再赘述。
    return new StatelessOp<P_OUT, P_OUT>(this, StreamShape.REFERENCE,
                                    StreamOpFlag.NOT_SIZED) {
        
        //增加了opWrapSink方法，第一个参数是标志 = 90，第二个是sink节点
        //这个方法会在结果态的时候调用
        Sink<P_OUT> opWrapSink(int flags, Sink<P_OUT> sink) {
            //创建一个 Sink.ChainedReference类的对象并返回。具体的放在结果态里面讲。
            return new Sink.ChainedReference<P_OUT, P_OUT>(sink) {
                @Override
                public void begin(long size) {
                    downstream.begin(-1);
                }

                @Override
                public void accept(P_OUT u) {
                    if (predicate.test(u))
                        downstream.accept(u);
                }
            };
        }
    };
}

当filter完成以后，可以得到下面的数据结构。

其他的中间态方法

总共有9个中间态方法，除了上面的两个还有

• 无状态
  • flatMap
  • unordered
  • peek
• 有状态
  • distinct
  • sorted
  • limit
  • skip

flatMap

先来看flatMap这个。他的作用是把给定的二维数组，转化成一维数组。比如

• 给定输入：[[1],[2],[3],[4,5]]
• 要求输出：[1,2,3,4,5]

来看应用层代码。首先构建一个二维数组，然后调用flatMap方法，传入Collection::stream方法进行处理元素，最后通过collect变成一个一维的list。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

//首先初始化输入列表
List<String> list1 = new ArrayList<>();
List<String> list2 = new ArrayList<>();
List<String> list3 = new ArrayList<>();
List<String > list4 = new ArrayList<>();
list1.add("1");
list2.add("2");
list3.add("3");
list4.add("4");
list4.add("5");
List<List<String>> list = new ArrayList<>();
list.add(list1);
list.add(list2);
list.add(list3);
list.add(list4);

//开始执行操作
List<String> listT = list.stream().flatMap(Collection::stream).collect(Collectors.toList());

return listT;

看一下具体的执行流程。橙色的是stream的通用执行流程，不管你中间态用哪个方法，这里是不变的，蓝色的是ArrayListSpliterator分割器。红色的执行流程是flatMap的执行流程。

可以看到ArrayListSpliterator先取出第一个元素[1]这个一维数组传递给flatMap，然后flatMap执行了我们传入的Collection::stream方法，该方法我们之前说过是初始化一个stream头节点。也就是再生成了一个stream

重点就是这里了。再次把[1]这个一维数组放入了新的stream里面。然后把结果态节点ReduceOps传递给了新的stream作为新的stream的结果态节点。

这个时候新的stream开始执行ArrayListSpliterator。从而把[1]一维数组进行for循环，取出了其中的1这个元素，然后把1传入了同一个ReduceOps进行处理从而组成了一个结果list->[1]。

把步骤总结如下：

1. 取出二维数组的第一个一维数组
2. 把一维数组和结果态节点重新创建一个stream
3. 执行stream把一维数组的元素循环放入结果态的list

循环二维数组，不断重复上述步骤，就可以把二维数组展开成一维数组了。

源码分析

来看方法的源码，可以看到接受一个方法，返回一个stream，标准的中间态处理。

可以看到参数校验以后就创建了一个无状态节点，节点的具体创建上面说过了。是一样的，不同的就是accpet这个方法。

这个处理逻辑，接受一个参数，这个参数就是上面传入的一维数组，第一次是[1]，第二次是[2]，第三次是[3]，第四次是[4,5]。因为我们传入的方法是 Collection.stream方法，所以会生成一个新的stream并返回给result。

这个时候的result就是一个只有一个头节点的stream。头节点中包含了一维数组[1]。然后判断stream不为空，则调用stream顺序流进行处理，并把collect结果态节点传入。

当forEach处理完以后，该accept方法处理完成。回到当前流也就是二维数组的流中。然后取出下一个一维数组[2]再次进入accept方法执行。直到当前二维数组的流处理完成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

@Override
public final <R> Stream<R> flatMap(Function<? super P_OUT, ? extends Stream<? extends R>> mapper) {
    //参数校验
    Objects.requireNonNull(mapper);
    // We can do better than this, by polling cancellationRequested when stream is infinite
    // 创建无状态节点并返回
    return new StatelessOp<P_OUT, R>(this, StreamShape.REFERENCE,
                                    StreamOpFlag.NOT_SORTED | StreamOpFlag.NOT_DISTINCT | StreamOpFlag.NOT_SIZED) {
        @Override
        Sink<P_OUT> opWrapSink(int flags, Sink<R> sink) {
            return new Sink.ChainedReference<P_OUT, R>(sink) {
                @Override
                // 初始化数据
                public void begin(long size) {
                    downstream.begin(-1);
                }

                @Override
                // 执行逻辑处理
                public void accept(P_OUT u) {
                    // 调用传入的方法进行处理元素 u ，u可能是 [1] [2] [3] [4,5]
                    // 并将执行结果新的stream赋值给 result
                    try (Stream<? extends R> result = mapper.apply(u)) {
                        // We can do better that this too; optimize for depth=0 case and just grab spliterator and forEach it
                        // 如果执行成功 返回了一个有效的 stream，则调用stream顺序流循环处理 downstream
                        // 这里的 downstream就是 结果态节点 ReduceOp 也就是 collect 收集方法
                        if (result != null)
                            result.sequential().forEach(downstream);
                    }
                }
            };
        }
    };
}

来看一下forEach方法，这个方法很简单。如果不是并行流，那么调用ArrayListSpliterator的forEachRemaining进行处理，传入结果态节点collect。

而forEachRemaining这个方法我们很熟悉了，无非就是循环流中的元素并传入sink链处理。要注意的是这个时候的流是上面新创建的流，这个流里面的元素是[1]这个一维数组。而这个流里面是没有中间态节点的，只有一个传入的collect结果态节点组成的sink链。所以这个流处理完以后就会把[1]里面的1这个元素收集到collect结果态节点中。

当这个forEach处理完这个一维数组以后，返回到上面的accept方法中。

1
2
3
4
5
6
7
8
9
10
11

@Override
public void forEach(Consumer<? super E_OUT> action) {
    //判断不是并行流
    if (!isParallel()) {
        //使用ArrayListSpliterator的forEachRemaining方法，传入结果态节点进行处理。
        sourceStageSpliterator().forEachRemaining(action);
    }
    else {
        super.forEach(action);
    }
}

unordered

这个方法很少使用。主要是不保证流有序，而不是主动打乱流的顺序。直接看当前类中的方法源码吧。

如果有序，直接返回了，如果无序的话会返回一个无状态节点。而这里面并没有accept操作，而是直接返回了传入的sink节点。

比如传入 reduceOp 这个结果态的sink，那就返回这个。如果传入 map 的 sink 就返回这个sink。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

@Override
public Stream<P_OUT> unordered() {
    //判断是否有序，如果无序，直接返回
    if (!isOrdered())
        return this;
    
    //如果有序，创建一个无状态节点，sink链直接返回。
    return new StatelessOp<P_OUT, P_OUT>(this, StreamShape.REFERENCE, StreamOpFlag.NOT_ORDERED) {
        @Override
        Sink<P_OUT> opWrapSink(int flags, Sink<P_OUT> sink) {
            return sink;
        }
    };
}

//判断是否有有序的标志
final boolean isOrdered() {
    //判断标志位里面是否有ordered这个标志位
    return StreamOpFlag.ORDERED.isKnown(combinedFlags);
}

peek

这个方法很少使用。主要是调试的时候使用来查看元素是否经过流，当然了也有其他的用法，如果你能保证它不出错的情况下。

来看一个debug的例子。他将打印每一个流经peek方法的元素，在当前场景下，所有元素都会执行打印操作。