字符串函数

• CONCAT(S1, S2, …) 字符串拼接；
• LOWER(str) 小写转换；
• UPPER(str) 大写转换；
• LPAD(str, n, pad) 左填充，用字符串 pad 对 str 左边进行填充，直到长度为 n；
• RPAD(str, n, pad) 右填充，用字符串 pad 对 str 右边进行填充，直到长度为 n；
• TRIM(str) 去掉字符串头部和尾部的空格；
• SUBSTRING(str, start, len) 返回字符串 str 从 start 位置起的 len 长度的字符串，需要注意的是字符索引是从1开始的。

数值函数

• CEIL(x) 向上取整；
• FLOOR(x) 向下取整；
• MOD(x, y) 返回 x/y 的模；
• RAND() 返回0～1的随机数；
• ROUND(x, y) 求参数 x 四舍五入的值并保留 y 位小数。

日期函数

• CURDATE() 当前日期；
• CURTIME() 当前时间；
• NOW() 当前日期和时间；
• YEAR(date) 获取指定 date 的年份；
• MONTH(date) 获取指定 date 的月份；
• DAY(date) 获取指定 date 的日期；
• DATE_ADD(date, INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔 expr 后的时间值；
• DATEDIFF(date1, date2) 返回起始时间 date1 和结束时间 date2 之间的天数。

流程函数

• IF(value, t, f) 如果 value 为真，返回 t，否则返回 f；
• IFNULL(value1, value2) 如果 value1 不为空，返回 value1，否则返回 value2；
• CASE WHEN [val1] THEN [res1] … ELSE [default] END 如果 val1 为 true，返回 res1，… 否则返回 default 默认值；
• CASE [expr] WHEN [val1] THEN [res1] … ELSE [default] END 如果 expr 的值等于 val1，返回 res1, … 否则返回 default 默认值。

简介

注释：

单行注释：– 或 #

多行注释：/**/

SQL分类：

DDL：数据定义语言，定义数据库对象（数据库，表，字段）。

DML：数据操作语言，对数据进行增删改。

DQL：数据查询语言，查询数据表的记录。

DCL：数据控制语言，创建数据库用户，控制数据库访问权限。

DDL

查询所有数据库：SHOW DATABASES;

查询当前数据库：SELECT DATABASE();

创建数据库：CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除数据库：DROP DATABASE [IF EXISTS] 数据库名;

切换数据库：USE 数据库名;

查询当前数据库中的所有表：SHOW TABLES;

查询表结构：DESC 表名;

查询指定表的建表语句：SHOW CREATE TABLE 表名;

创建表：

1
2
3
4
5
6

CREATE TABLE 表名(
  字段1 字段1类型 [COMMENT 字段注释],
  字段2 字段2类型 [COMMENT 字段注释],
  ...
  字段n 字段n类型 [COMMENT 字段注释]
) [表注释];

修改表：

• 添加字段：ALTER TABLE 表名 ADD 字段名 类型（长度）[COMMENT 注释] [约束];
• 修改数据类型： ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）;
• 修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型（长度） [COMMENT 注释] [约束];
• 删除字段：ALTER TABLE 表名 DROP 字段名;
• 修改表名：ALTER TABLE 表名 RENAME TO 新表名;
• 删除表：DROP TABLE [IF EXISTS] 表名;
• 删除指定表，并重新创建该表：TRUNCATE TABLE;

示例：

现有一张用户信息表 user_info，其中包含多年来在平台注册过的用户信息。

请在用户信息表，字段 level 的后面增加一列最多可保存 15 个汉字的字段 school；并将表中 job 列名改为 profession，同时 varchar 字段长度变为 10；achievement 的默认值设置为 0。

1
2
3

ALTER TABLE user_info ADD school varchar(15) AFTER `level`;
ALTER TABLE user_info CHANGE job profession varchar(10);
ALTER TABLE user_info CHANGE COLUMN achievement achievement int DEFAULT 0;

DML

给指定字段添加数据：INSERT INTO 表名 (字段1，字段2，...) VALUES (值1，值2);

给全部字段添加数据：INSERT INTO 表名 VALUES (值1，值2);

批量添加数据：

1
2

INSERT INTO 表名 (字段1，字段2，...) VALUES (值1，值2, ...), (值1，值2, ...), (值1，值2, ...);
INSERT INTO 表名 VALUES (值1，值2，...), (值1，值2，...), (值1，值2，...);

[!NOTE]

将多次单行插入合并为一次批量插入（multi-row INSERT）能够显著提升性能，主要原因包括：

• 减少网络往返次数，降低客户端与服务器的通信开销；
• 减少 SQL 解析与执行计划的开销，只需对一条语句进行解析与优化；
• 减少事务与日志写入开销，合并写入二进制日志（binlog）和 InnoDB 重做日志；
• 优化索引更新，批量更新索引比单次多次更新更高效；
• 降低锁竞争与事务开销，减少锁的申请与释放次数；
• 充分利用 InnoDB 的 Group Commit 机制，进一步减少磁盘刷新次数。

示例：

现有一张试卷作答记录表exam_record，结构如下表，其中包含多年来的用户作答试卷记录，由于数据越来越多，维护难度越来越大，需要对数据表内容做精简，历史数据做备份。

1
2
3
4

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

现在有一套ID为9003的高难度SQL试卷，时长为一个半小时，请你将 2021-01-01 00:00:00 作为发布时间插入到试题信息表examination_info（其表结构如下图），不管该ID试卷是否存在，都要插入成功，请尝试插入它。

1
2
3
4
5
6
7

REPLACE INTO examination_info
VALUES(NULL,9003,'SQL','hard',90,'2021-01-01 00:00:00');

DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00');

修改数据：UPDATE 表名 SET 字段1=值1， 字段2=值2，... [WHERE 条件];

删除数据：DELETE FROM 表名 [WHERE 条件];

[!NOTE]

DELETE 与 TRUNCATE 的区别

操作类型

• DELETE：属于 DML（数据操作语言），需要逐行删除数据，并记录每一行的删除操作。删除后需要手动提交事务。
• TRUNCATE：属于 DDL（数据定义语言），直接释放表的所有数据页，然后重新初始化表，速度更快。

日志记录

• DELETE：会记录每一行的删除操作到 binlog，用于事务回滚和主从同步。
• TRUNCATE：只记录表的重建操作，不记录逐行删除，日志量较小。

重置 AUTO_INCREMENT

• DELETE：不会重置自增值，下一次插入时继续从当前最大值递增。
• TRUNCATE：会重置自增值为初始值。

外键约束

• DELETE：可以针对有外键约束的表逐行删除，受外键规则影响。
• TRUNCATE：不能直接操作有外键约束的表，否则会报错。

当一个表中有大量的 DELETE 操作时，你会采取哪些措施来优化性能或管理存储空间？

1. 如果需要清空整张表，用 TRUNCATE 或 DROP。
2. 如果 DELETE 操作是高频行为，考虑使用 分区表 或 分表。
3. 如果需要保留数据历史，使用 软删除。
4. 定期使用 OPTIMIZE TABLE 或分批 DELETE 来回收空间。

示例：

请删除exam_record表中未完成作答或作答时间小于5分钟整的记录中，开始作答时间最早的3条记录。

1

delete from exam_record where date_add(start_time, interval 5 minute) > submit_time or submit_time is null limit 3;

请删除exam_record表中所有记录，并重置自增主键。

1

truncate table exam_record;

更新语句的执行过程

在执行 UPDATE 时，InnoDB 会先按照 WHERE 条件对匹配的记录及其前后间隙加上 next-key 锁，以防幻读；然后从缓冲池中将数据页加载到内存，先在 Undo 日志中记录修改前的值（用于回滚和 MVCC），再将更新操作以物理日志的形式写入 Redo 日志并将数据页标记为“脏页”。之后 MySQL 采用两阶段提交：第一阶段将 Redo 日志持久化并标记为 prepare 状态，第二阶段将事务的所有变更以逻辑或行事件写入 Binlog 并执行 fsync，最后将 Redo 日志标记为已提交并释放锁，从而保证 Redo 与 Binlog 的原子一致性。

MySQL 在执行更新语句时，在服务层执行语句的解析和执行，在引擎层执行数据的提取和存储；在服务层对 binlog 进行写入，在引擎层对 redo log 进行写入。

事务的两阶段提交

这是 MySQL 中保证数据一致性和持久性的关键机制。

1. prepare 阶段：记录事务的变更到 redo log，并标记为 prepare 状态。
2. binlog 写入：将对应的 SQL 语句写入 binlog。
3. commit 阶段：将 redo log 中对应的日志条目标记为 commit 状态，并完成整个提交过程。

事务不一致的处理

**情况一：**系统在 redo log 标记为 prepare 之后崩溃。这种情况下，事务已经记录到 redo log 中，但可能还未写入 binlog。恢复时，InnoDB 会检查 redo log 中的 prepare 状态。如果找到这样的记录，会继续检查 binlog。

1. 如果 binlog 中没有找到对应的记录，说明事务未提交，InnoDB 会回滚事务，确保数据一致性。
2. 如果 binlog 中找到了对应的记录，说明事务已提交，InnoDB 会完成提交过程，确保数据一致性。

**情况二：**系统在 binlog 写入后但在 redo log commit 前崩溃。在这种情况下，事务已经写入了 binlog，但未完成在 redo log 中的 commit 标记。恢复时，InnoDB 会首先检查 redo log，如果发现 prepare 状态的记录且 binlog 中有对应的记录，InnoDB 会将事务标记为 commit 状态并完成提交，这确保了事务的一致性和持久性。

DQL

查询多个字段：

1
2

SELECT 字段1，字段2，字段3... FROM 表名;
SELECT * FROM 表名;

设置别名：SELECT 字段1 [AS 别名1]，字段2[AS 别名2] ... FROM 表名;

去除重复记录：SELECT DISTINCT 字段列表 FROM 表名;

聚合函数：SELECT 聚合函数(字段) FROM 表名;

以上 SQL 语句将一列数据作为一个整体，进行纵向计算。NULL 不参与所有聚合函数运算。

分组查询：SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

[!NOTE]

WHERE 和 HAVING 区别

• 执行时机不同：WHERE 是分组之前进行过滤；HAVING 是分组之后进行过滤。
• 判断条件不同：WHERE 不能对聚合函数进行判断，HAVING 可以。
• 执行顺序：WHERE > 聚合函数 > HAVING

排序查询：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;

排序方式：ASC 升序（默认），DESC 降序。

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询：SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

起始索引从0开始，起始索引 =（查询页码 - 1）* 每页显示记录数;

SELECT 查询执行顺序

1. FROM：对 FROM 子句中的左表和右表执行笛卡儿积（Cartesianproduct），产生虚拟表 VT1。
2. ON：对虚拟表 VT1 应用 ON 筛选，只有那些符合的行才被插入虚拟表 VT2 中。
3. JOIN：如果指定了 OUTER JOIN（如 LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中，产生虚拟表 VT3。如果 FROM 子句包含两个以上表，则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤 1）～步骤 3），直到处理完所有的表为止。
4. WHERE：对虚拟表 VT3 应用 WHERE 过滤条件，只有符合的记录才被插入虚拟表 VT4 中。
5. GROUP BY：根据 GROUP BY 子句中的列，对 VT4 中的记录进行分组操作，产生 VT5
6. CUBE | ROLLUP：对表 VT5 进行 CUBE 或 ROLLUP 操作，产生表 VT6。
   1. CUBE：生成所有可能组合的汇总，包括每个维度的组合。适用于多维数据分析。
   2. ROLLUP：生成层级汇总，从详细级别到总体总和。适用于生成部分汇总数据。
7. HAVING：对虚拟表 VT6 应用 HAVING 过滤器，只有符合的记录才被插入虚拟表 VT7 中。
8. SELECT：第二次执行 SELECT 操作，选择指定的列，插入到虚拟表 VT8 中。
9. DISTINCT：去除重复数据，产生虚拟表 VT9。
10. ORDER BY：将虚拟表 VT9 中的记录按照进行排序操作，产生虚拟表 VT10。
11. LIMIT：取出指定行的记录，产生虚拟表 VT11，并返回给查询用户。

DQL 语句的执行过程

1. 客户端通过 TCP 连接发送 DQL 到 MySQL 服务器。
2. 连接器开始处理该请求，包括建立连接、权限验证等。
3. 解析器进行词法分词和语法分析，生成抽象语法树 AST 或解析树，同时检查 SQL 语法合法性和基本语法错误。在生成 AST 后，解析器将数据库名、表名和列名等标识符与内部数据字典中的对象进行映射，并对引用的对象执行权限检查，只有在用户拥有相应权限时，才允许继续执行。
4. 优化器基于成本模型，对解析树进行查询重写（如谓词下推、视图展开）和逻辑优化，然后评估多种访问路径：全表扫描 vs 索引扫描、Nested Loop Join vs Hash Join 等，计算各方案的成本并选择最优执行计划，该计划以具体的物理操作算子（索引扫描、排序、聚合）为粒度进行组合。
5. 执行器根据优化器生成的执行计划，调用相应的存储引擎接口，逐步执行算子操作（TableScan、IndexScan、Join、Sort），并在内存中构建最终的结果集。
6. 对于 InnoDB 引擎，普通 SELECT 语句采用多版本并发控制（MVCC），从缓冲池内查找 Undo 日志中保存的历史版本来重建查询时刻的数据快照，若未命中则从磁盘读取并加载到缓冲池，同时维护 LRU 链表。
7. 执行器完成结果集的生成后，通过 Protocol 层将数据逐行或一次性打包返回给客户端。

DCL

查询用户：

1
2

USE mysql;
SELECT * FROM user;

创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户：DROP USER '用户名'@'主机名';

查询权限：SHOW GRANTS FOR '用户名'@'主机名';

授予权限：GRANT 授权列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限：REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

[!NOTE]

主机名可使用 % 通配。

多个权限之间使用逗号分隔。

数据库名和表名可使用 * 进行通配，代表所有。

Each page consits of the header, the record space and a pointer array. Each slot in this array points to a record within the page.

A record is located by providing its page address and the slot number. The combination is called RID.

Life Cycle of a record:

Insert: Find an empty space to put it and set a slot at the very end of the page to point to it.

Delete: Remove the record and reclaim this space, set its slot number to null. When there are too many garbage slots, the system will drop the index structure, do reorganization on this disk page by removing all null slots, and reconstruct the index structure from scratch.

Non-clustered Index: the data of the disk page is independent of the bucket or leaf node of index.

Clustered Index: the data of the disk page resides within the bucket or leaf node of index.

Hash Index: the item in buckets is not ordered by the attribute value of index.

B+Tree Index: the item in leaf nodes is ordered by the attribute value of index.

• Primary Index: the data in the disk page is ordered.
• Secondary Index: the data in the disk page is not ordered.

Reference: https://www.vldb.org/conf/1990/P481.PDF

Overview

The HRPS declusters a relation into fragment based on the following criteria:

• Each fragment contains approximately FC tuples.
• Each fragment contains a unique range of values of the partitioning attribute.

The variable FC is determined based on the processing capability of the system and the resource requirements of the queries that access the relation (rather than the number of processors in the configuration).

A major underlying assumption of this partitioning strategy is that the selection operators which access the database retrieve and process the selected tuples using either a range predicate or an equality predicate.

For each query Qi, the workload defines the CPU processing time (CPUi), the Disk Processing Time (Diski), and the Network Processing time (Neti) of that query. Observe that these times are determined based on the resource requirements of each individual query and the processing capability of the system. Each query retrieves and processes (TuplesPerQi) tuples from the database. Furthermore, we assume that the workload defines the frequency of occurrence of each query (FreqQi).

Rather than describing the HRPS with respect to each query in the workload, we deline an average query (Qavg) that is representative of all the queries in the workload. The CPU, disk and network processing quanta for this query are:

Assume that a single processor cannot overlap the use of two resources for an individual query. Thus, the execution time of Qavg on a single processor in a single user environment is:

As more processors are used for query execution, the response time decreases. However, this also incurs additional overhead, represented by the variable CP, which refers to the cost of coordinating the query execution across multiple processors (e.g., messaging overhead). The response time of the query on M processors can be described by the following formula:

In a single-user environment, both HRPS and range partitioning perform similarly because they both efficiently execute the query on the required processor. However, in a multi-user environment, the range partitioning strategy is likely to perform better because it can distribute the workload across multiple processors, improving system throughput. In contrast, HRPS might not utilize all available processors as effectively, potentially leading to lower throughput.

Instead of M representing the number of processors over which a relation should be declustered, M is used instead to represent the number of processors that should participate in the execution of Qavg. Since Qavg processes TuplesPerQavg tuples, each fragment of the relation should contain FC = TuplesPerQavg / M tuples.

The process of fragmenting and distributing data in HRPS:

1. Sorting the relation: The relation is first sorted based on the partitioning attribute to ensure each fragment contains a distinct range of values.
2. Fragmentation: The relation is then split into fragments, each containing approximately FC tuples.
3. Round-robin distribution: These fragments are distributed to processors in a round-robin fashion, ensuring that adjacent fragments are assigned to different processors (unless the number of processors N is less than the required processors M).
4. Storing fragments: All the fragments for a relation on a given processor are stored in the same physical file.
5. Range table: The mapping of fragments to processors is maintained in a one-dimensional directory called the range table.

This method ensures that at least M processors and at most M + 1 processors participate in the execution of a query.

M = N：系统和查询需求匹配，HRPS 调度所有处理器，达到最大并行度和最优性能。

M < N：HRPS 只调度一部分处理器执行查询，减少通信开销，但部分处理器资源可能闲置。

M > N：HRPS 将多个片段分配给处理器，尽量利用所有处理器，但每个处理器负担加重，查询执行速度可能受到影响。

HRPS in this paper supports only homogeneous nodes.

Questions

How does HRPS decide the ideal degree of parallelism for a query?

HRPS (Hybrid-Range Partitioning Strategy) decides the ideal degree of parallelism by analyzing the resource requirements of the query, such as CPU, disk I/O, and communication costs. It calculates the optimal number of processors (denoted as M) based on these factors. The strategy strikes a balance between minimizing query response time and avoiding excessive overhead from using too many processors.

Why is it not appropriate to direct a query that fetches one record using an index structure to all the nodes of a system based on the shared-nothing architecture?

Fetching one record should only involve the node that contains the relevant data, as querying all nodes wastes resources and increases response time.

How to extend HRPS to support heterogeneous nodes?

1. More powerful nodes would receive more fragments, while weaker nodes would handle fewer fragments.
2. The system could monitor node performance and dynamically adjust the degree of parallelism and fragment allocation based on current load and node availability.
3. Heavier tasks may be directed to more powerful nodes, while smaller or simpler queries could be executed on less powerful nodes.

Reference: https://www.vldb.org/conf/1990/P481.PDF

背景

数据库是数据存储的仓库，是文件的集合，是依照某种数据模型组织起来并存放于二级存储器中的数据集合。

数据库实例是程序，是位于用户和操作系统之间的一层数据管理软件，用户对数据库数据的任何操作都是在数据库实例下进行的。应用程序只有通过数据库实例才能和数据库打交道。

在 MySQL 中，实例和数据库的关系通常是一一对应的。但在集群下可能存在一个数据库被多个数据库实例使用的情况。

关系型数据库（结构数据模型，表）：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

数据库管理系统（DBMS）：操纵和管理数据库的应用程序。

SQL：操作关系型数据库的编程语言，也是一套标准。

客户端=>数据库管理系统=>数据库=>数据表。

数据库三大范式

第一范式（1NF）：确保表的每一列都是不可分割的基本数据单元，比如说用户地址，应该拆分成省、市、区、详细信息等 4 个字段。

第二范式（2NF）：在 1NF 的基础上，要求数据库表中的每一列都和主键直接相关，而不能只与主键的某一部分相关（主要针对联合主键）。

第三范式（3NF）：在 2NF 的基础上，消除非主键列对主键的传递依赖，即非主键列只依赖于主键列，不依赖于其他非主键列。

简介

MySQL 是一款开源的关系型数据库管理系统（RDBMS），最初由 MySQL AB 于 1995 年 5 月 23 日推出，现由 Oracle 维护和发布。MySQL 使用 SQL 来定义、操作和管理数据表，将数据组织为由行与列构成的表格，以实现数据之间的关联与查询。它原生支持完整的 ACID 事务特性和多版本并发控制（MVCC），在高并发环境下能够保持数据的一致性与隔离度；InnoDB 引擎通过回滚段存储旧版本数据，并结合两阶段锁定（Two-Phase Locking）和插入意向锁等机制，实现并发控制与死锁检测。MySQL 可跨 Windows、Linux、macOS、FreeBSD 等多个操作系统部署，凭借易用性、高性能与可靠性，长期被广泛应用于 Web 应用、电子商务平台和企业级系统。

MySQL 采用可插拔的存储引擎架构，允许用户根据不同业务需求选择最合适的引擎。默认的 InnoDB 引擎提供事务处理、行级锁、外键约束、崩溃恢复（通过 redo log）和双写缓冲等功能，以确保数据安全与快速恢复。在 InnoDB 之前，MyISAM 曾为默认引擎，其采用表级锁设计、不支持事务与外键，适用于读密集型场景但无法满足高并发写入需求。此外，MySQL 还支持 Memory 引擎（将数据保存在内存中，适合临时表或高速缓存）和 NDB Cluster 引擎（面向分布式高可用集群，支持自动分片和多主复制），以满足不同场景下对性能与可用性的多样化需求。

在服务器层面，MySQL 包括 SQL 解析器、查询优化器和执行器三大组件。解析器负责将客户端提交的 SQL 文本进行词法与语法分析，生成内部抽象语法树（AST）；优化器基于统计信息与索引代价估算，选择最优执行计划；执行器则通过存储引擎接口调用底层引擎完成实际的数据访问和操作，例如数据页读取、加锁、写入等。MySQL 采用磁盘导向的存储架构，InnoDB 使用页为单位将数据加载到缓冲池并通过分代 LRU 策略进行页面替换，以优化磁盘 I/O 性能。在并发查询执行方面，MySQL 以元组级的迭代器模型处理查询，不支持内部并行化，但可借助索引和优化器策略减少 I/O 次数，从而提升查询效率。