MySQL学习分享

资源

首先分享我目前学习MySQL的资料

小林Coding：图解MySQL介绍 | 小林coding (xiaolincoding.com)

图书：《MySQL必知必会》（Ben Forta著）

2024/8/2 一条SQL的执行过程

谈谈目前对执行一条SQL的理解，不知道是否准确。

SQL（Structured Query Langage）是一种专门与数据库通信的语言。

为什么是Structured呢？数据库是数据库管理系统创建的管理各种结构化数据的容器，它是结构化的。那么对这些数据的管理就有结构、有特定的规律可循。

为什么是Query呢？“查询”功能是DBMS的核心功能，SQL的其他功能如增删改虽然也很重要，但是使用的频率和复杂性通常不如查询操作，调查询功能的名称更能反映 SQL 的核心用途和特点。

（图：小林Coding）

大部分DBMS都是C/S架构的，首先服务端要在一个计算机上运行，DBMS客户端通过网络与DBMS服务端进行通信。

服务器端暴露出连接器，客户端与其连接才可以得到服务，此连接与HTTP类似，分为短连接和长连接。连接通常需要：

• 主机名
• 端口号
• 用户名
• 用户口令（如果需要）

连接完成之后，客户端就可以向服务端发送SQL语句了，MySQL接收到SQL语句就会检查第一个关键字来判断语句的类型，如SELECT、UPDATE...

第一步：查询缓存（老版本）

如上图所示，在MySQL8.0之前，MySQL服务维护着一个数据结构，成为查询缓存（Query Cache）。它是一个个KV对，Key是查询语句，V是查询结构。但是，由于要维护数据的一致性，在一些数据进行修改时，如果它在缓存中呢，缓存就需要清理，而且查询的命中率很低，在MySQL8.0以后这个数据结构就被删除了。

第二步：解析SQL

MySQL服务做的事和编译器类似，是词法分析和语法分析两件事。

词法分析：识别出SQL的一个个Token，看看其中哪些是关键字。

语法分析：根据这些Token生成语法树（语法不正确在这边就会报错）。

（图：小林Coding）

第三步：执行SQL

执行分三步走，分别是：

• 预处理（Prepare）
• 优化（Optimize）
• 执行（Execute）

预处理：之前生成了一个个Token，知道了什么是关键字，什么是表名、字段名，Prepare做的就是检查这些表名、字段名是否存在（讨论的是MySQL8.0及之后的版本）。

优化：找到一个最合适的执行计划。优化器会生成多个可能的执行计划，一个执行计划描述了数据库如何访问表和索引，如何联接表，如何过滤和排序数据等。

执行：执行器根据执行计划，调用存储引擎的相关API接口，最后把执行结果传给连接器，客户端就可以收到SQL执行结果了。

执行器和存储引擎的交互过程可以分为三种：

1. 主键索引查询
2. 全表扫描
3. 索引下推

如，查询语句用到了主键索引，优化器会选择特定的访问类型。执行器第一次执行查询时，会调用 read_first_record 函数指针指向的函数，再根据优化器选择的访问类型const选择调用InnoDB的索引查询接口，根据条件找到B+树上的记录，再判断是否存在。执行器的代码是while循环，第二次执行时，会调用read_record函数指针指向的函数，因为访问类型是const，所以指向的函数返回-1，while循环退出。

具体细节参见执行一条 select 语句，期间发生了什么？ | 小林coding (xiaolincoding.com)。

2024/8/3 GROUP BY 分组数据细节

分组（GROUP BY）与聚集函数（aggregate function）的联系：

聚集函数可以数据进行汇总，但是只通过WHERE子句是不能满足很多需求的，需要引入分组来更好地对每个组进行聚集计算。

从一个报错中得到学习

在阅读《MySQL必知必会》第13章时，顺便上机编写SQL并执行

SELECT id,count(1) AS order_num,order_time FROM orders GROUP BY Date(order_time);

执行结果显示

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sky_take_out.orders.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

选中的列不在GROUP BY 子句，并且包含非聚合列'sky_take_out.orders.id'（哈哈，暴露了之前写的苍穹外卖），这个列在功能上不依赖GROUP BY 子句中的列。这与sql模式"only_full_group_by"不兼容。

需要弄清什么是

1. 该列在功能上不依赖GROUP BY 子句中的列
2. only_full_group_by模式

根据MySQL官网对SQL模式的解释

5.1.10 Server SQL Modes

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements.

Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This makes it easier to use MySQL in different environments and to use MySQL together with other database servers.

可以得知，MySQL维护着一个叫做sql_mode的变量，通过这个变量来决定对客户端发送过来的SQL的检验，这包含语法及其执行的数据验证检查。

根据官网对sqlmode_only_full_group_by的解释

• ONLY_FULL_GROUP_BY

  Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

  As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.)

  A MySQL extension to standard SQL permits references in the HAVING clause to aliased expressions in the select list. Before MySQL 5.7.5, enabling ONLY_FULL_GROUP_BY disables this extension, thus requiring the HAVING clause to be written using unaliased expressions. As of MySQL 5.7.5, this restriction is lifted so that the HAVING clause can refer to aliases regardless of whether ONLY_FULL_GROUP_BY is enabled.

  For additional discussion and examples, see Section 12.19.3, “MySQL Handling of GROUP BY”.

对于 GROUP BY 聚合操作，如果在 SELECT 中的列、HAVING 或者 ORDER BY 子句的列，没有在GROUP BY中出现，那么这个SQL是不合法的。

从 MySQL 5.7.5 开始，默认的 SQL 模式包括 ONLY_FULL_GROUP_BY。

SELECT id,count(1) AS order_num,order_time FROM orders GROUP BY Date(order_time);

再回到这个问题，如果没有理解错，

1指在一个关系型数据库表中，某些列的值依赖于其他列的值。如果一个列（或一组列）的值可以唯一确定另一个列（或一组列）的值，那么前者被称为后者的功能依赖。

2指的是当前sql_mode是默认的only_full_group_by模式。MySQL 要求 SELECT 语句中的每个非聚合列都必须出现在 GROUP BY 子句中，或者是功能依赖于 GROUP BY 子句中的列。否则，查询将会报错。

要解决这个问题可以

1. 将所有非聚合列包含在 GROUP BY 子句中
2. 使用聚合函数
3. 禁用 ONLY_FULL_GROUP_BY 模式

将所有非聚合列包含在 GROUP BY 子句中的例子：

SELECT id, customer_id, SUM(amount) FROM orders GROUP BY id, customer_id;

使用聚合函数的例子:

对非聚合列使用适当的聚合函数

SELECT customer_id, MIN(id), SUM(amount) FROM orders GROUP BY customer_id;

禁用 ONLY_FULL_GROUP_BY 模式的方法

临时禁用

在当前会话中禁用 ONLY_FULL_GROUP_BY 模式：

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

我尝试之后，发现刚刚的SQL可以得到查询结果。

永久禁用

编辑 MySQL 配置文件 my.cnf（或 my.ini），找到或添加 [mysqld] 部分，然后修改 sql_mode：

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

确保 sql_mode 中不包含 ONLY_FULL_GROUP_BY。

修改配置文件后，重启 MySQL 服务：

sudo service mysql restart

小结

学习到了聚集函数、分组的联系：聚合函数可以作用于分组，更好地解决复杂问题，

HAVING和WHERE的比较：HAVING过滤分组，WHERE过滤行。

通过一个报错，查询MySQL官网了解到了MySQL服务中的sql_mode，字段之间的功能依赖，如何修改当前的sql_mode，如何更好地写一个包含聚合和分组的SQL。同时加深了对于执行一条SQL的过程理解，特别是解析阶段和准备阶段。

ONLY_FULL_GROUP_BY 模式在 MySQL 中具有重要意义，因为它加强了 SQL 查询的严格性和确定性。启用 ONLY_FULL_GROUP_BY 模式后，MySQL 要求 SELECT 语句中的每个非聚合列都必须在 GROUP BY 子句中，或者是功能依赖于 GROUP BY 子句中的列。这种严格性有助于避免模棱两可和潜在错误的查询结果，确保查询的逻辑明确和结果的可预见性。

待更新