二三事

“みんなみんな大好き！”

0%

Java Web数据库与缓存要点整理：MySQL、Redis、Spring Data与MyBatis

Posted on Edited on In
　　整理数据库MySQL与Redis的知识，以及Spring Data与MyBatis在Java Web开发中对数据库操作的相关知识。

前端：Java Web前端要点整理：HTML、CSS、JavaScript、Vue、HTTP协议与Tomcat

学习路线：

参考了Java 学习路线 | 25 年最新零基础到精通一条龙（万人收藏⭐️）。

MySQL计划内容：
  • 基本概念
  • MySQL搭建
  • SQL 语句编写
  • 约束
  • 索引
  • 事务
  • 锁机制
  • 设计数据库表
  • 性能优化
Redis计划内容：
  • Redis基础
  • 什么是缓存？
  • 本地缓存
  • Caffeine库
  • 多级缓存
  • Redis 分布式缓存
  • 数据类型
  • 常用操作
  • Java 操作 Redis
    • Spring Boot Redis Template
    • Redisson
  • 主从模型搭建
  • 哨兵集群搭建
  • 日志持久化
  • 缓存（Redis）应用场景
  • 数据共享
  • 单点登录
  • 计数器
  • 限流
  • 点赞
  • 实时排行榜
  • 分布式锁
  • 缓存常见问题
  • 缓存雪崩
  • 缓存击穿
  • 缓存穿透
  • 缓存更新一致性
  • 相关技术：Memcached、Ehcache
MyBatis计划内容：
  • 描述：数据访问框架，操作数据库进行增删改查等操作
  • 增删改查
  • 全局配置
  • 动态SQL
  • 缓存
  • 与其他框架的整合
  • 逆向工程
红色加粗部分是暂未学习但计划学习的。

MySQL

本文对MySQL的学习参考自黑马程序员 MySQL数据库入门到精通，从mysql安装到mysql高级、mysql优化全囊括。

在表示语法时，本文以大写字母表示MySQL保留字，以<...>表示必须填入的参数，比[...]表示可选参数，以(...)表示必须填入参数且应当保留外部的括号（可以将(...)视为(<...>)的简化）。

Overview of MySQL Storage Engine Architecture

MySQL默认使用InnoDB作为存储引擎。如果没有特别指明，本文不会刻意区分MySQL与InnoDB的特性。

InnoDB与MyISAM最大的区别体现在事务、外键与行级锁上。

Structured Query Language

SQL是一种用以操作关系型数据库的编程语言，定义了一套关于操作关系型数据库的标准。MySQL就是这样一种基于磁盘的关系型数据库管理系统，并支持SQL语句进行数据的操作。所谓关系型数据库，是指建立在关系模型基础上、由多张相互连接的二维表组成的数据库。

SQL在语法上不区分大小写，执行时忽视多余的空格、缩进与换行，以分号作为语句的结束标志。单行注释使用--起始，多行注释使用/*、*/分别作为起始与结束。MySQL额外支持使用#进行单行注释。

MySQL数据类型

MySQL的数值数据类型（Numeric Data Types）
类型 大小 有符号范围（SIGNED） 无符号范围（UNSIGNED）
TINYINT 1 Byte [-128, 127]，最大值为2^7-1 [0, 255]，最大值为2^8-1
SMALLINT 2 Bytes [-32768, 32767]，最大值为2^15-1 [0, 65535]，最大值为2^16-1
MEDIUMINT 3 Bytes [-83886608, 83886607]，最大值为2^23-1 [0, 16777215]，最大值为2^24-1
INT（INTEGER） 4 Bytes [-2^31, 2^31-1] [0, 2^32-1]
BIGINT 8 Bytes [-2^63, 2^63-1] [0, 2^64-1]
FLOAT 4 Bytes 负值范围 [-3.402823466E+38, -1.175494351E-38]
正值范围 [1.175494351E-38, 3.402823466E+38]
零值		 不被推荐，MySQL 8.0已弃用
DOUBLE 8 Bytes 负值范围 [-1.7976931348623157E+308, -2.2250738585072014E-308]
正值范围 [2.2250738585072014E-308, 1.7976931348623157E+308]
零值		 不被推荐，MySQL 8.0已弃用
DECIMAL 可变 取决于精度M与标度D 取决于精度M与标度D

注 1：一字节（Byte，简写为B）等于八位（bit，简写为b），即 1 Byte = 8 bits。字（Word）的大小取决于CPU架构与操作系统，32位操作系统上1字等于4B，64位操作系统上1字等于8B。如果采用utf8mb4字符集，则存储一个数字或一个拉丁字母需要一字节，存储一个常见汉字需要三字节。emoji与一些生僻汉字需要占用四字节。

注 2：对DECIMAL而言，精度（M）指忽视小数点时数字的位数，标度（D）指小数点后数字的位数。例如，160.0的精度为4、标度为1。

声明整数类型时，对于可以通过保留字UNSIGNED将数据声明为无符号数。例如，要声明名为age的无符号TINYINT类型，应写为age TINYINT UNSIGNED。

DECIMAL需要在声明时指定精度与标度，具体而言遵循DECIMAL(<M>, <D>)的语法。例如，要声明名为score的精度为4、标度为1的DECIMAL类型，应写为score DECIMAL(4, 1)。

旧版本的MySQL也支持FLOAT(<M>, <D>)与DOUBLE(<M>, <D>)的语法进而限制浮点数的输入范围，但这一设计已在新版本MySQL被淘汰。

MySQL的字符串数据类型（String Data Types）
类型 大小 描述
CHAR 0-255 Bytes 定长字符串，性能较好
VARCHAR 0-65535 Bytes 变长字符串，性能较差
TINYBLOB 0-255 Bytes 占用不超过255字节的二进制数据
BLOB 0-65535 Bytes 占用不超过65535字节的二进制数据
MEDIUMBLOB 0-16777215 Bytes 中等长度的二进制数据
LONGBLOB 0-4294967295 Bytes 长二进制数据
TINYTEXT 0-255 Byte 占用不超过255字节的文本数据
TEXT 0-65535 Bytes 占用不超过65535字节的文本数据
MEDIUMTEXT 0-16777215 Bytes 中等长度的文本数据
LONGTEXT 0-4294967295 Bytes 长文本数据

注 1：SQL引号使用规则：SQL标准推荐使用单引号囊括字符串字面量，而双引号通常被解析为标识符引用，尽管有些时候也可以用来包裹字符串字面量，但并不推荐这种不标准的做法。在MySQL中，通过执行SET sql_mode='ANSI_QUOTES';启用ANSI_QUOTES模式，双引号将被严格解释为标识符引用，从而与单引号功能分离。当标识符含保留字或特殊字符而需要强制引用标识符时，则应使用反引号。

注 2：虽然MySQL提供了用于存储二进制数据的数据类型BLOB及其派生类型，但很少会使用MySQL记录长文本的二进制数据。

在声明字符串数据类型时，需要同时指定其最大容量，相应的语法为<string_type>(<prefix_length>)，单位为字节。例如，CHAR(10)是固定占用10字节的定长字符串，而VARCHAR(4)是最大占用4字节的变长字符串。

MySQL的日期时间数据类型（Date and Time Data Types）
类型 大小 范围 标准格式 描述
DATE 3 Bytes 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
TIME 3 Bytes -838:59:59 至 838:59:59 HH:MM:SS 时间值（持续时间）
YEAR 1 Byte 1901 至 2155 YYYY 年份值
DATETIME 8 Bytes 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期时间值
TIMESTAMP 4 Bytes 1970-01-01 00:00:01 至 2038-01-19 03:14:07 时间戳

除了以上常用的数据类型外，SQL标准还定义了一些数据类型。如果需要MySQL数据类型的更详细文档，请参考13 Data Types。

在MySQL中，常使用CAST()函数转换类型。例如将CHAR类型的'2023-01-01'转换为DATE类型，语法为CAST('2023-01-01' AS DATE)。MySQL会进行一些隐式的类型转换，例如将数值与字符串比较、将日期与字符串比较与日期类型的算术运算等等。

CONVERT()函数也能做类型转换，在刚才的例子中如果使用CONVERT()函数，相应的语法为CONVERT('2023-01-01', DATE)。CONVERT()的特点是还可以转换字符串的字符集，例如CONVERT('Wonderhoy!' USING utf8mb3)。

SQL语句

MySQL客户端登录（连接服务端）指令为mysql -u <user> -p，然后按提示输入密码。-u告诉MySQL接下来会指定登录用户，-p告诉MySQL稍后将输入密码、使用密码登录。默认最高权限用户<user>为root。

SQL语句分类
分类 全称 说明
DDL Data Definition Language 用以定义数据库对象，包括数据库、表、字段等。
DML Data Manipulation Language 用以对数据库表中的数据进行操作，即“CUD”。
DQL Data Query Language 用以查询数据库中表的记录，即“R”。
DCL Data Control Language 用以创建数据库用户与控制数据库访问权限。

对于MySQL而言，保留字SCHEMA与DATABASE完全等价，可随意替换。字段（field）在一些场景下也被称为列（column）。

推荐使用JetBrains的软件DataGrip进行带有GUI的数据库操作。

DDL

  • 数据库操作

    • SHOW DATABASES;：查询所有数据库。
    • SELECT DATABASE();：查询当前数据库。
    • CREATE DATABASE [IF NOT EXISTS] <db> [DEFAULT CHARSET <char_set>] [COLLATE <sort_rule>];：创建数据库。以创建数据库mydb为例，如果数据库已存在，则使用命令CREATE DATABASE mydb会得到ERROR 1007，而使用命令CREATE DATABASE IF NOT EXISTS mydb会得到Query OK但不会执行实际的操作。在MySQL 8.0以后的版本中默认字符集为utf8mb4，支持完整的Unicode字符系统，对于更早期的版本需要在可选参数中指定字符集utf8mb4，否则会按默认字符集latin1设置。同时，在MySQL中utf8是utf8mb3的别名，该字符集最多只支持3字节的Unicode，仅仅是utf8mb4的子集，不推荐使用。常用的排序规则有utf8mb4_unicode_ci、utf8mb4_general_ci与utf8mb4_bin，分别对应标准Unicode排序规则、简化排序规则与二进制比较排序规则。其中，简化排序规则会忽略部分细节以换取执行效率，例如将德语字符ß视为ss；二进制比较排序规则会区分大小写和重音符号，更适用于严格匹配。
    • DROP DATABASE [IF EXISTS] <db>;：删除数据库。可选参数同上。
    • USE <db>;：使用指定数据库。

  • 表操作

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

    • CREATE TABLE <table>(
    <field_1> <data_type> [COMMENT '<field_1_comment>'],
    <field_2> <data_type> [COMMENT '<field_2_comment>'],
    ...
    <field_n> <data_type> [COMMENT '<field_n_comment>']
) [COMMENT '<table_comment>'];

      创建表。可以在创建表时对表与初始化的字段填入说明。

    • DESC <table>;：查询表的结构。

    • SHOW CREATE TABLE <table>;：查询表的创建语句，可以得到比DESC更详细的关于表的信息。

    • ALTER TABLE <table> ADD ...

      • `... <field_name> <data_type> [COMMENT '<comment>'];：为表添加新字段。这个写法完全等价于... ADD COLUMN <field_name> <data_type> [COMMENT '<comment>'];。
      • ... <constraint>;：添加约束。例如ALTER TABLE Sekai ADD UNIQUE(Saki);会为表Sekai的Saki字段添加唯一性约束。约束的使用请参考15.1.9 ALTER TABLE Statement。
      • ... CONSTRAINT <constraint_name> <constraint>;：添加约束的同时，显式命名约束。
      • ... [UNIQUE] INDEX [index_name] (column_list) [USING <algorithm>];：为表添加索引。如果<index_name>省略则会按默认命名规则自动生成，如果USING <algorithm>省略则默认使用BTREE算法。<column_list>是要索引的字段，多字段则用逗号分隔，支持联合索引。

    • ALTER TABLE <table> MODIFY <field_name> <data_type>;：修改指定字段的数据类型。

    • ALTER TABLE <table> CHANGE <old_field_name> <new_field_name> <data_type> [COMMENT '<comment>'] [constraint];：修改指定字段的字段名与数据类型。

    • ALTER TABLE <table> DROP <field_name>;：删除指定字段。

    • ALTER TABLE <old_table> RENAME TO <new_table>;：重命名表。

    • DROP TABLE [IF EXISTS] <table>;：删除指定表。如果没有IF EXISTS语句，则试图删除不存在的表时会返回错误。

    • TRUNCATE TABLE <table>;：删除指定表，然后重新创建同名的表，效果与清空表相同。

DML

  • 增 • INSERT

    • INSERT INTO <table>(field_1, field_2 ...) VALUES (value_1, value_2 ...);：为指定字段添加一条数据。
    • INSERT INTO <table> VALUES (value_1, value_2 ...);：为所有字段添加一条数据。
    • INSERT INTO <table>(field_1, field_2 ...) VALUES (value_11, value_12 ...), (value_21, value_22 ...) ...;：为指定字段批量添加数据。
    • INSERT INTO <table> VALUES (value_11, value_12 ...), (value_21, value_22 ...) ...;：为所有字段批量添加数据。

  • 删 • DELETE

    • DELETE FROM <table> [WHERE <condiction>];：删除表的数据。如果存在条件限制，则会删除符合条件的数据；如果不存在条件限制，则会删除整张表的所有数据。

      DELETE操作的对象是整行数据的全部字段。如果要单独将某些字段的数据置空或恢复默认值，则应当使用UPDATE。

  • 改 • UPDATE

    • UPDATE <table> SET <field_1>=<value_1>, <field_2>=<value_2> ... [WHERE <condition>];：修改表的数据。如果存在条件限制，则会修改符合条件的数据；如果不存在条件限制，则会修改整张表的所有数据。

DQL

关于SELECT的完整描述，请参考官方帮助文档15.2.13 SELECT Statement。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT  -- 可以使用通配符“*”，“SELECT *”表示选择全部字段
    <field_1> [AS <alias_1>],  -- 取别名可以省略保留字AS；可以对字段使用聚合函数
    <field_2> [AS <alias_2>],  -- 可以通过“表名.字段”来选择特定表中的特定字段
    ...
FROM
    <table_1> [AS <table_1>],  -- 同样地，取别名时可以省略保留字AS
    <table_2> [AS <table_2>],
    ...
WHERE  -- “WHERE”在分组前行级过滤数据且不能使用聚合函数的结果，不满足条件的数据不参与后续分组
    <where_condition_1>,  -- 支持“.”操作符
    <where_condition_2>,  -- 可以通过“表名.字段”指定表间字段关联关系
    ...
GROUP BY  -- “GROUP BY”对（过滤后）数据进行分组、可以使用聚合函数，多个分组依据用逗号隔开
    <col_name | expr | position>, ...  -- “SELECT”中的非聚合列必须出现在“GROUP BY”
HAVING  -- “HAVING”在分组后组级过滤数据且可以使用聚合函数的结果
    <where_condition_1>,
    <where_condition_2>,
    ...
ORDER BY  -- “ORDER BY”会按照给定的字段顺序作为排序优先级，默认“ASC”升序
    <col_name | expr | position>, ... [ASC | DESC]  -- 可以指定“DESC”降序
LIMIT  -- “LIMIT”用于分页查询
    [offset,] <row_count>  -- 或 <row_count> OFFSET offset，默认从0开始索引
;

说明：

  • 如果“FROM”中进行了多表查询但在“WHERE”中却没有限制条件（省略了“WHERE”），则会返回表间的笛卡尔积，也就是所有可能的行组合，会带来灾难级的数据量处理。

  • “SELECT”与“GROUP BY”中常用的聚合函数有：COUNT(·)、MAX(·)、MIN(·)、AVG(·)与SUM(·)。分别对应统计数量、最大值、最小值、平均值与求和。聚合函数作用于某一列，其中的·代表实际所指定的字段，例如MAX(ln.age)。

    需要特别说明：选定字段的值为NULL的行一般不参与聚合函数的运算。但存在例外，COUNT是唯一支持作用于通配符的聚合函数，其效果是直接统计行数，而不关心各行中是否存在值为NULL的字段。

  • “WHERE”中常用的比较运算符有：>，>=，<，<=，=，!=，BETWEEN <min> AND <max>，IN(...)，LIKE <pattern>，IS NULL。需要说明的是!=也可以写为<>，BETWEEN <min> AND <max>指定了选取范围但该范围包含最小值与最大值，IN(...)的用法类似于Python中的保留字in，例如WHERE gender IN('男', '女')，表示字段取值只要满足IN(...)内的任一值即可，LIKE <pattern>则用以模糊匹配，并提供了_与%两个通配符，其中_匹配任意单个字符、%匹配任意个字符的组合（包括0个字符）。

  • “WHERE”中常用的逻辑运算符有：AND（或&&）、OR（或||）与NOT（或!）。NOT的用法和自然语言相似，column IS NOT NULL结果上等价于NOT (column IS NULL)，但不能写为column NOT IS NULL，因为后者不符合语法规则。

  • “LIMIT”是MySQL特有的实现方式。

  • 当为表起别名后，就不能通过原表名使用表了，而只能通过别名使用。

DQL语句的实际执行顺序与代码编写的顺序是不同的。实际的执行优先级从高到低为：

  1. FROM
  2. WHERE
  3. GROUP BY
  4. SELECT
  5. ORDER BY
  6. LIMIT

在条件查询中，各条件的执行顺序亦与代码编写的顺序不同。实际的执行优先级从高到低为：

  1. 括号（优先计算括号内的表达式，强制改变默认运算顺序）
  2. 各个比较运算符
  3. 逻辑运算符NOT
  4. 逻辑运算符AND
  5. 逻辑运算符OR

要访问当前系统变量信息，需要在字段前添加@@。例如，要查看当前事务隔离级别，应执行SELECT @@transaction_isolation;。

DCL

MySQL的权限管理依赖于系统数据库mysql的user表，所有用户的权限情况都被存储在这张表中。因此，无论是创建用户、查询用户、修改用户权限还是删除用户，只需要操作user表即可。

  • 管理用户

    • USE mysql; SELECT * FROM user;：查询所有用户。
    • CREATE USER [IF NOT EXISTS] '<user_name>'@'<host_name>' IDENTIFIED BY '<password>';：创建用户。
    • CREATE USER [IF NOT EXISTS] '<user_name>'@'%' IDENTIFIED BY '<password>';：创建用户，且该用户能从任何主机上访问数据库。也就是说，<host_name>可以是通配符%，表示任何主机。
    • ALTER USER [IF EXISTS] '<user_name>'@'<host_name>' IDENTIFIED WITH mysql_native_password BY '<new_password>';：修改用户密码，其中mysql_native_password指传统密码认证（使用SHA1哈希算法）。
    • DROP USER [IF EXISTS] '<user_name>'@'<host_name>'：删除用户。

  • 权限控制

    MySQL中常用的权限如下：

    1. ALL、ALL PRIVILEGES：完全权限。
    2. SELECT：查询数据权限。
    3. INSERT：插入数据权限。
    4. UPDATE：修改数据权限。
    5. ALTER：修改表权限。
    6. DROP：删除数据库、表与视图的权限。
    7. CREATE：创建数据库与表的权限。

    关于权限的全部定义，请参考官方文档8.2.2 Privileges Provided by MySQL。

    可以用以下语句管理这些权限：

    • SHOW GRANTS FOR '<user_name>'@'<host_name>';：查询指定用户的权限。
    • GRANT <privilege_list> ON <privilege_level> TO '<user_name>'@'<host_name>';：授予指定用户指定权限。
    • REVOKE <privilege_list> ON <privilege_level> FROM '<user_name>'@'<host_name>';：取消指定用户的指定权限。

    <privilege_level>可以是全局权限*.*，可以是数据库级权限<db>.*，可以是表级权限<db>.<table>，也可以是列级权限、存储程序权限（例程级别）。以授予权限为例，授予列级权限的语法为GRANT <privilege_1> (field_11 <, field_12, ...>), [<privilege_2> (field_21 <, field_22, ...>), ...] ON <db.table> TO '<user_name>'@'<host_name>';。

默认情况下，MySQL以表格形式横向显示查询结果（字段为列，数据为行）。在语句末尾添加\G能够改变查询结果的显示格式，让每条记录的每个字段会单独成行，形成纵向展示。特别在当字段内容较长时，大大方便了各字段的查阅。不过这不属于SQL标准。

SQL函数

对于非SQL标准函数，会在注释中注明。

SQL的常用聚合函数
函数 功能（针对InnoDB存储引擎）
COUNT(arg) 1). COUNT(*)：InnoDB遍历表但不会取出字段，Server层对引擎返回行直接进行累加，返回数据行总数
2). COUNT(primary_key)：InnoDB遍历表并取出主键值返回给Server层，Server层对其直接进行累加
3). COUNT(not_null_field)：InnoDB遍历表并取出字段值返回给Server层，Server层对其直接进行累加
4). COUNT(field)：InnoDB遍历表并取出字段值返回给Server层，Server层进行判断并对非NULL值进行累加
5). COUNT(num)：InnoDB遍历表但不会取出字段，Server层对引擎返回的每一行插入常量值num并累加
6). COUNT(DISTINCT ...)：InnoDB遍历表并取出值返回给Server层，Server层在累加前额外判断值是否重复，重复者不予计入累加
MAX(field)
MIN(field)		 计算字段的最大值或最小值并返回，支持数值、日期、字符串类型。忽略NULL，但如果列值全为NULL则返回NULL。
AVG(numeric_field) 计算数值列的非NULL值的算术平均值并返回。如果列值全为NULL则返回NULL。
SUM(numeric_field) 计算数值列所有非NULL值的总和并返回。如果列值全为NULL则返回NULL。

聚合函数是一类对数据集进行汇总计算并返回单一值的函数，常用于统计、分析和简化大规模数据的处理。

如果数据量较大，则应考虑配合索引使用聚合函数。

SQL的常用字符串函数
函数 功能
CONCAT(str_1, str_2, ...) 拼接若干字符串为一个字符串
LOWER(str)
UPPER(str)		 将字符串全部转为小写
将字符串全部转为大写
LPAD(str, n, padding)
RPAD(str, n, padding)		 左填充
右填充
TRIM(str)
LTRIM(str)
RTRIM(str)		 移除首尾连续的空格
移除开头连续的空格
移除结尾连续的空格
SUBSTRING(str, start, len) 截取子字符串，start最少为1

注：LTRIM与RTRIM在MySQL中被支持，但并非标准SQL语法，可能不是所有的关系型数据库都支持。

SQL的常用数值函数
函数 功能
CEIL(x)
FLOOR(x)		 上取整
下取整
MOD(a, b) 取模
RAND() 返回0至1的DOUBLE类型随机数
ROUND(a, b) 对a四舍五入，保留b位小数

注：CEIL为MySQL的扩展函数，对应的SQL标准函数为CEILING。

SQL的常用日期函数
函数 功能 返回值类型与格式
CURDATE()
CURTIME()
NOW()		 返回当前日期
返回当前时间
返回当前日期与时间		 DATE，YYYY-MM-DD
TIME，HH:MM:SS
DATETIME，YYYY-MM-DD HH:MM:SS
YEAR(date)
MONTH(date)
DAY(date)		 返回date的年份
返回date的月份
返回date的日期		 INT
INT
INT
DATE_ADD(date, INTERVAL <expr> <unit>) 返回date加上interval后的时间
interval可以是任何时间类型		 与date相同
DATEDIFF(date_1, date_2) 返回date_1减去date_2的天数
例如date_2是date_1的第二天，则返回-1		 INT

注 1：CURDATE、CURTIME、NOW均为MySQL的扩展函数，对应的SQL标准函数分别为CURRENT_DATE、CURRENT_TIME、CURRENT_TIMESTAMP。

注 2：在函数DATE_ADD中，当<expr>为数值时<unit>常见为SECOND、DAY、MONTH、YEAR，当<expr>为字符串时<unit>则为与其格式相应的时间数据类型，例如INTERVAL '1:1' MINUTE_SECOND、INTERVAL '1 1:1:1' DAY_SECOND与INTERVAL '1.999999' SECOND_MICROSECOND等等。

SQL的常用流程函数
函数 功能
IF(value, a, b) 如果value为TRUE，返回a，否则返回b
IFNULL(value_1, value_2) 如果value_1为NULL，则返回value_2，否则返回value_1
CASE <case_value> WHEN [when_value_1] THEN [statement_list_1] [WHEN [when_value_2] THEN [statement_list_2] ...] [ELSE statement_list] END 简单CASE表达式
CASE WHEN [search_condition_1] THEN [statement_list_1] [WHEN [search_condition_2] THEN [statement_list_2] ...] [ELSE statement_list] END 搜索CASE表达式

注：IF与IFNULL均为MySQL的扩展函数，对应的SQL标准函数分别为CASE表达式与COALESCE函数。

对于简单CASE表达式与搜索CASE表达式的用法，分别举例子说明会更直观：

运用简单CASE表达式的例子：

1
2
3
4
5
6
7
SELECT 
    CASE grade
        WHEN 'A' THEN '优秀'
        WHEN 'B' THEN '良好'
        ELSE '其他'
    END AS grade_desc
FROM students;

运用搜索CASE表达式的例子：

1
2
3
4
5
6
7
SELECT 
    CASE
        WHEN score >= 90 THEN '优秀'
        WHEN score >= 80 THEN '良好'
        ELSE '其他'
    END AS score_level
FROM exams;

SQL约束

约束是作用域表中字段上的规则，用于限制存储在表的数据以保证数据库中数据的有效性。

约束常常在表设计阶段（CREATE TABLE）、表维护阶段（ALTER TABLE）中被添加。

SQL常用约束简介
约束 描述 说明
NOT NULL 非空约束 是列属性（Column Attribute）而非独立约束（Constraint），因此无法通过CONSTRAINT命名。
UNIQUE 唯一性约束：确保字段值唯一，但会忽视NULL。 独立约束，支持显式命名。在MySQL中唯一性约束通过创建唯一索引实现，故删除约束时应考虑DROP INDEX语句。
PRIMARY KEY 主键约束：设置字段为主键，要求非空且唯一。一张表至多拥有一个主键，但可以定义复合主键。 独立约束，支持显式命名。
DEFAULT 默认约束：保存数据时，如果未指定该字段的值，则采用给定值作为默认值。 列属性，在MySQL中不支持显式命名，但在一些其他的关系型数据库如SQL Server中支持显式命名，在语法上与MySQL存在区别。
CHECK 检查约束：保证字段值满足给定条件。 独立约束，支持显式命名。
FOREIGN KEY 外键约束：跨表约束。 独立约束，支持显式命名。外键位于子表，外键指向父表中的主键。
SQL常用约束的语法

由于在表设计时添加字段均需要使用完整的CREATE TABLE <table>(...);命令，因此在下文介绍表设计时添加约束的语法中略去了相同的这部分，而只保留括号内的内容。

约束 表设计时添加语法 表维护时添加语法 移除语法
NOT NULL <field> <data_type> NOT NULL ALTER TABLE <table> MODIFY <field> <data_type> NOT NULL; ALTER TABLE <table> MODIFY <field> <data_type>;
或
ALTER TABLE <table> MODIFY <field> <data_type> NULL;
UNIQUE 添加默认名称约束：<field> <data_type> UNIQUE
添加显式命名约束：<field> <data_type>, [...,] CONSTRAINT <cons_name> UNIQUE (field)		 添加默认名称约束：ALTER TABLE <table> ADD UNIQUE (field, [field ...]);
添加显式命名约束：ALTER TABLE <table> ADD CONSTRAINT <cons_name> UNIQUE (field, [field ...]);		 ALTER TABLE <table> DROP INDEX <cons_name>;
PRIMARY KEY <field> <data_type> PRIMARY KEY 添加单个主键：ALTER TABLE <table> ADD PRIMARY KEY (field);
添加复合主键：ADD PRIMARY KEY (field_1, field_2, ...);		 ALTER TABLE <table> DROP PRIMARY KEY;
DEFAULT <field> <data_type> DEFAULT <value> ALTER TABLE <table> MODIFY <field> <data_type> DEFAULT <value>; 删除原有的默认值：ALTER TABLE <table> MODIFY <field> <data_type>;
用新的默认值覆盖：ALTER TABLE <table> MODIFY <field> <data_type> DEFAULT <new_value>;
CHECK 添加默认名称约束：<field> <data_type> CHECK (expr)
添加显式命名约束：<field> <data_type>, [...,] CONSTRAINT <cons_name> CHECK (expr)		 添加默认名称约束：ALTER TABLE <table> ADD CHECK (expr)
添加显式命名约束：ALTER TABLE <table> ADD CONSTRAINT <cons_name> CHECK (expr)		 ALTER TABLE <table> DROP CONSTRAINT <cons_name>;
FOREIGN KEY 添加默认名称约束：<field> <data_type>, FOREIGN KEY (field) REFERENCES <parent_table>(parent_field)
添加显式命名约束：<field> <data_type>, CONSTRAINT <cons_name> FOREIGN KEY (field) REFERENCES <parent_table>(parent_field)		 添加默认名称约束：ALTER TABLE <table> ADD FOREIGN KEY (field) REFERENCES <parent_table>(parent_field);
添加显式命名约束：ALTER TABLE <table> ADD CONSTRAINT <cons_name> FOREIGN KEY (field) REFERENCES <parent_table>(parent_field);		 ALTER TABLE <table> DROP FOREIGN KEY <cons_name>;

注：检查约束CHECK在MySQL 8.0.16后才被支持，此前仅作解析但不会强制执行。在其他大多数关系型数据库如PostgreSQL、SQL Server中，从始至终都会强制执行检查约束。

MySQL特有语法AUTO_INCREMENT，对标SQL标准中定义的GENERATED ALWAYS AS IDENTITY。在MySQL中，当字段被定义为INT或BIGINT类型并被添加AUTO_INCREMENT列属性时（例如id INT PRIMARY KEY AUTO_INCREMENT;，事实上该属性常常与主键约束同时出现），若插入的数据未显式指定该字段的值，则数据库会自动为其生成一个递增的整数，默认从1开始，且每次插入递增1。可以通过ALTER TABLE <table> AUTO_INCREMENT = <n>;指定下次插入时主键从n开始自增。此外，如果因为约束而导致插入失败，拥有AUTO_INCREMENT属性的整数类型主键仍然会自增。但应注意“整型主键自增”并非SQL标准，在不同的数据库中存在不同的实现，例如PostgreSQL通过SERIAL实现自增字段。

显式命名约束是有必要的，在约束前添加CONSTRAINT <cons_name>即可命名约束。如果不对约束进行显式命名，则MySQL会按默认规则生成约束的名称，这对后续修改与删除约束而言很不方便，需要使用命令SHOW CREATE TABLE <table>;查看约束名称后才能继续操作。不过，MySQL会忽视对主键约束的显式命名：在MySQL中，主键约束拥有固定的名字PRIMARY。因此，主键约束虽然是独立约束，但和列属性NOT NULL、DEFAULT一样，不需要显式命名。

SQL调整外键约束行为的语法

外键约束有如下常用的行为：

行为 描述
NO ACTION 当父表删除/更新对应记录时，检查该记录是否存在对应外键，如果存在则不允许此次删除/更新。此为默认行为。
RESTRICT 非SQL标准，在MySQL中完全等同于NO ACTION，仅为别名。
CASCADE 当父表删除/更新对应记录时，检查该记录是否存在对应外键，如果存在则删除/更新外键在子表中的记录。
SET NULL 当父表删除对应记录时，检查该记录是否存在对应外键，如果存在则尝试将外键在子表中的记录设为NULL。
SET DEFAULT 当父表删除/更新对应记录时，检查该记录是否存在对应外键，如果存在则将外键在子表中的记录设为一个默认值。
目前MySQL默认的存储引擎InnoDB尚不支持该行为。

注：在PostgreSQL中NO ACTION允许延迟约束检查直到事务提交时，而 RESTRICT 则立即检查。然而在MySQL中两者完全等同，均立即检查。

要指定外键约束的行为，只需要在外键约束的添加语法后加上... [ON UPDATE <reference_option_1>] [ON DELETE <reference_option_2>]分别指定外键更新与删除时子表中记录的行为，无论是处于表设计时还是表维护时。

SQL多表设计与查询

两张关联的表之间，可以存在以下三种关系，分别对应不同的实现方式。

  1. 一对多、多对一：在多的一方添加外键。
  2. 一对一：常用以单表拆分。在任意一张表添加外键关联另一张表的主键，同时对外键施加唯一性约束，或通过共享主键实现（两表主键相同，无需额外外键）。
  3. 多对多：建立一张中间表，在中间表中添加两组外键，分别指向两张表中的主键。在DataGrip中右键表，选择Diagrams，可以查看多表与中间表的可视化关系图。

根据查询数据范围的不同，两表查询主要可以被分为两类，其中连接查询与子查询又可以再作细分：

  • 连接查询
    • 内连接查询：查询两表交集的部分。
    • 外连接查询：又分为左外连接、右外连接与全外连接，但全外连接目前在MySQL尚不被支持。左外连接保留左表全部数据，右表无匹配则补NULL；右外连接保留右表全部数据，左表无匹配则补NULL；全外连接则保留两表全部数据。直观上，可以认为左外连接和右外连接的查询范围分别为左表所有数据与两表交集的数据和右表所有数据与两表交集的数据。
    • 自连接查询：当前表与自身的连接查询，既可以是内连接的，也可以是外连接的。在自连接中应当将同一张表的两个别名视为不同的表；自连接在语法上强制使用表别名以区分查询对象。
  • 子查询
    • 标量子查询：子查询结果为单个值（一行一列）。
    • 列子查询：子查询结果为一列（通常多行，即单字段多行数据）。
    • 行子查询：子查询结果为一行（通常多列，即单行多字段数据）。
    • 表子查询：子查询结果为多行多列。
SQL连接查询的语法
连接查询分类 查询语法（如果没有明确指明<alias>，则<table>可选择设置别名，否则必须设置别名）
内连接查询 1). 隐式内连接：SELECT <field> [, <field>, ...] FROM <table_1>, <table_2> WHERE <condition>;
2). 显式内连接：SELECT <field> [, <field>, ...] FROM <table_1> [INNER] JOIN <table_2> ON <condition>;
外连接查询 1). 左外连接：SELECT <field> [, <field>, ...] FROM <table_1> LEFT [OUTER] JOIN <table_2> ON <condition>;
2). 右外连接：SELECT <field> [, <field>, ...] FROM <table_1> RIGHT [OUTER] JOIN <table_2> ON <condition>;
自连接查询 1). 自连接，隐式内连接：SELECT <field> [, <field>, ...] FROM <table> <alias_1>, <table> <alias_2> WHERE <condition>;
2). 自连接，显式内连接：SELECT <field> [, <field>, ...] FROM <table> <alias_1> [INNER] JOIN <table> <alias_2> ON <condition>;
3). 自连接，左连接：SELECT <field> [, <field>, ...] FROM <table> <alias_1> LEFT [OUTER] JOIN <table> <alias_2> ON <condition>;
4). 自连接，右连接：SELECT <field> [, <field>, ...] FROM <table> <alias_1> RIGHT [OUTER] JOIN <table> <alias_2> ON <condition>;

注：SQL-92标准还定义了全外连接，在外连接的基础上返回左表和右表的所有行、未匹配部分填充NULL，但目前MySQL仍不支持该语法，而Oracle、SQL Server、PostgreSQL等主流数据库均支持。

隐式内连接属于SQL-89标准，显式连接属于SQL-92标准，二者均在笛卡尔积基础上通过条件过滤实现查询。对于MySQL而言显式连接可以省略INNER、OUTER，对结果没有任何影响。在SQL标准中，外连接查询不存在隐式写法，但在一些数据库如Oracle等通过特定语法扩展了隐式写法的语法。

事实上，当没有WHERE限定时SELECT * FROM <table> [, <table> ...];返回笛卡尔积是SQL-89标准的语法。在SQL-92标准中则明确规定了交叉连接的语法，使用CROSS JOIN关键字，即SELECT * FROM <table_1> CROSS JOIN <table_2>;。

如果需要合并两个查询的结果集，且这两个结果集的列数与数据类型一致，则应进行联合查询，关键字为UNION ALL与UNION，用法是在两个连接查询语句间插入，例如SELECT * FROM ln WHERE height >= 159 UNION ALL SELECT * FROM ln WHERE age ;。其中，UNION ALL会直接返回联合查询结果，UNION则会将联合查询结果去重后再返回。要注意的是联合查询为集合操作，而非连接操作。

子查询：在SQL语句中使用操作符联通嵌套的SELECT语句。也称为嵌套查询。

子查询通常被嵌套在WHERE、FROM与SELECT语句中，并通过操作符进行逻辑判断与传递。这里分别通过一些例子进行说明。

  • 标量子查询示例：SELECT instrument FROM ln WHERE name = (SELECT name FROM miyamasuzaka_girls_academy WHERE name LIKE 'Tenma %' AND band = 'Leo/need');

    该语句作用为查询在宫益坂女子学园就读的姓“天马”的Leo/need乐队成员在乐队中常使用的乐器。

  • 列子查询示例：SELECT name FROM ln WHERE height > ALL (SELECT height FROM ws WHERE student_id IN (SELECT student_id FROM miyamasuzaka_girls_academy));

    该语句作用为查询Leo/need成员中身高大于所有在宫益坂女子学园就读的Wonderlands×Showtime成员的姓名。

  • 行子查询示例：SELECT * FROM ln WHERE (name, student_id) = (SELECT name, student_id FROM miyamasuzaka_girls_academy WHERE name LIKE 'Tenma %' AND band = 'Leo/need');

    该语句作用为查询在宫益坂女子学园就读的姓“天马”的Leo/need乐队成员的信息。

  • 表子查询示例：SELECT * FROM ln WHERE student_id IN (SELECT miyamasuzaka_id FROM miyamasuzaka_girls_academy WHERE grade = '2-B');

    该语句作用为查询所有在宫益坂女子学园就读且学年为2-B的Leo/need成员的信息。

SQL子查询常用操作符
常用操作符 说明
标量子查询：=, <> (!=) , >, >=, <, <=, BETWEEN <min> AND <max> 直接与主查询字段进行比较。子查询必须返回单行单列值。
列子查询：IN, NOT IN, ANY, SOME, ALL IN：用法为x IN (v1, v2, ...)，逻辑上是多个=条件的OR组合，用于匹配子查询结果集中的任意值。当子查询匹配至NULL时返回条件求值UNKNOWN，可能导致过滤条件失效、结果集为空。
NOT IN：用法同上，排除子查询结果集中的所有值，子查询含NULL时返回值恒为UNKNOWN，导致结果集为空。
ANY：与比较符（如>）结合使用，满足子查询结果中任意一个即可（如子查询不包含NULL时x > ANY(subquery)等价x > (SELECT MIN(v) FROM subquery)）。
SOME：完全等同ANY。
ALL：与比较符结合使用，需满足子查询结果所有值（如x > ALL(subquery)等价x > (SELECT MAX(v) FROM subquery)）。若子查询含有NULL且比较符不为=，可能返回UNKNOWN。
行子查询：=, <> (!=) , IN, NOT IN 同上，查询时可以使用使用行构造器语法进行多字段联合匹配，如WHERE (a, b) = (SELECT a, b FROM ...)。
表子查询：IN, EXISTS, NOT EXISTS, JOIN IN：用法同上，用于多列动态过滤（如(col1, col2) IN (subquery)）。
EXISTS：用法为EXISTS (subquery)，子查询返回至少一行则为true，可安全处理NULL，常用于关联查询，通常需在子查询中引用外部表字段以实现关联逻辑。
NOT EXISTS：用法同上，子查询无结果返回true，适用排除关联记录（如无订单客户）。
JOIN：将子查询结果作为临时表联查（需起别名），需要优化性能时优先使用JOIN替代IN与EXISTS。

注：在SQL的EXISTS子查询中，SELECT 1是存在性检查的标准实践（可以粗糙地认为是一种约定俗成的书写与优化习惯），它的核心目的是检查子查询是否至少返回一行，而不关心实际返回的数据内容。数据库引擎执行EXISTS时，只需知道子查询是否有匹配的行，不会实际读取或返回数据。无论子查询中是SELECT 1、SELECT 0还是SELECT NULL，甚至是SELECT *，性能与结果均完全一致。现代数据库在解析时识别到EXISTS子查询后，会自动忽略SELECT的字段列表，而仅关注是否有符合条件的行——该子查询不会实际生成结果集。如果使用EXISTS，则列子查询示例可以被改写为SELECT ln.name FROM ln WHERE NOT EXISTS ( SELECT 1 FROM ws INNER JOIN miyamasuzaka_girls_academy m ON ws.student_id = m.student_id WHERE ws.height >= ln.height);。

务必十分小心考虑三值逻辑（TRUE / FALSE / UNKNOWN）对子查询的影响。

SQL事务与日志

事务（transaction）是数据库操作的逻辑单元，是一个不可被分割的工作单位。事务会把所有的操作作为一个整体向系统提交或撤销回滚，这些操作要么全部成功，要么全部失败，这一性质便是所谓“原子性”。

MySQL默认事务自动提交（autocommit=1），即当执行一条DML语句时，MySQL会将其视为独立事务立即提交。如果需要手动提交事务，有以下几种方式可以考虑：

  • 可以先执行SET @@autocommit = 0;以改变当前会话事务提交设置，在输入全部的数据操作命令后通过COMMIT;手动提交事务。如果事务提交执行出错，应执行ROLLBACK;回滚。
  • 也可以不修改autocommit参数，而是执行START TRANSACTION;或BEGIN;显式地开启一个事务，随后执行COMMIT;提交事务或执行ROLLBACK;回滚事务。

事务具有四个最显著的特性，合称“ACID”。

事务的特性：ACID
特性 说明
原子性（Atomicity） 事务是数据库操作的（最小）逻辑单元，要么全部成功，要么全部失败。
一致性（Consistency） 确保事务执行前后，数据库始终处于合法（满足所有预定义的数据库约束）且符合业务规则（例如转账时无论是否成功，两个账户上的总金额在事务前后保持不变）的状态。
隔离性（Isolation） 依赖数据库系统提供隔离机制，数据库系统应保证事务在不受外部并发操作影响的独立环境下运行。
持久性（Durability） 事务一旦提交或回滚，对数据库中数据的改变是永久性的。

一致性是事务的最终目标，确保数据始终符合业务规则和数据库约束。一致性的实现也依赖于ACID的其他特性。

并发场景下，可能会出现一些事务问题：

  • 脏读：一个事务读取到了另一个事务尚未提交的数据。
  • 不可重复读：一个事务先后读取同一条记录，但两次读取到的数据不相同。
  • 幻读：一个事务在条件查询时没有查询到对应的数据行，但在随后插入数据时却发现数据行已存在（例如主键重复冲突）。

要避免这些问题，就需要合理设置事务隔离级别。事务与事务隔离级别均在SQL标准中被明确定义。

数据库中的事务隔离级别
事务隔离级别 脏读 不可重复读 幻读 补充说明
Read Uncommitted（读未提交） √ 允许 √ 允许 √ 允许 几乎不适用，仅用于极低一致性要求的监控。
Read Committed（读已提交） × 被禁止 √ 允许 √ 允许 Oracle与PostgreSQL的默认级别。
Repeatable Read（可重复读） × 被禁止 × 被禁止 √ 允许 MySQL的默认级别。
Serializable（串行化） × 被禁止 × 被禁止 × 被禁止 适用于金融交易等高一致性要求的场景。

查看当前事务隔离级别：SELECT @@transaction_isolation;

设置事务隔离级别：SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL <READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE>;，其中若省略[SESSION | GLOBAL]则设置的事务隔离级别仅对当前会话中下一个即将开启的事务生效。换句话说，事务隔离级别的设置存在三种不同的作用范围：当前会话、全局与下一个事务，分别对应SESSION关键字、GLOBAL关键字与无关键字，而不存在一般认知上的默认行为，故应当根据实际需求选择合适的范围。

事务隔离级别与性能呈相反趋势，事务隔离级别越严格，数据库执行数据操作时性能越差、效率越低。数据库通过不同的锁保证隔离性，事务隔离级别越高，加锁的场景越多，阻塞越频繁。现代数据库则通过引入MVCC（多版本并发控制）版本控制优化读操作的并发性能，一定程度上减少了锁的使用。

MySQL日志与事务的两阶段提交（2PC）

MySQL有三种日志binlog、redo log与undo log，区别如下：

MySQL中的三种日志
binlog（二进制日志） redo log（事务日志） undo log
日志归属 由MySQL的Server层实现，适用于所有存储引擎 InnoDB存储引擎特有的日志 InnoDB存储引擎特有的日志
日志类型 逻辑日志：提供三种日志格式，

1). STATEMENT：记录SQL语句，缺点是可能致更新时间与原库不一致
2). ROW：行数据（实际的）变更，缺点是记录数据量大
3). MIXED：混合模式，默认STATEMENT，涉及日期、函数相关的时候采用ROW		 物理日志：记录数据页的物理修改（如页偏移量、字节值变化） 逻辑日志：记录反向操作（如INSERT对应DELETE，UPDATE记录旧值）
写入方式 主从复制、数据恢复（PITR）、审计 循环写入固定大小文件组（通过LSN和Checkpoint机制管理覆盖） 段式存储（Segment），事务提交后由Purge线程异步清理历史版本
适用场景 主从同步与误删恢复 崩溃恢复（保证持久性）、Write-Ahead Logging (WAL) 机制 事务回滚（原子性）、MVCC（多版本并发控制）
事务关联性 与事务无直接关联（非事务引擎操作也会记录） 仅记录已提交事务的修改（通过两阶段提交与binlog保持一致性） 记录事务执行过程中的修改前状态（支持回滚和MVCC）
生命周期 长期保留（需人工或工具定期清理） 写入数据文件后由Checkpoint机制标记为可覆盖 事务提交后，若无其他事务依赖MVCC版本则异步清理

本小节内容的图片与部分内容引用自CSDN博客简单谈谈MySQL的两阶段提交。

要理解事务的两阶段提交，首先需要对Service层有基本的了解，请先移步至后文章节。不过，这里不必关心MySQL Service层各组件的具体实现，我们关心的是发生在执行器中的事务两阶段提交。

语句执行流程

语句执行流程

我们现在知道MySQL中存在三种日志：binlog、redo log和undo log。事务两阶段提交的设计，就是为了确保binlog与redo log的一致性。

执行器中事务两阶段提交流程

事务两阶段提交

事务的两阶段提交的流程如上图所示，简而言之：通过协调事务日志（redo log）和二进制日志（binlog）的写入顺序，确保事务操作的原子性和数据一致性。

为什么要写redo log，不写redo log的话，根本就不会出现“两阶段提交”的麻烦事啊？

先说结论：在于崩溃恢复。

MySQL为了提升性能，引入了BufferPool缓冲池。查询数据时，先从BufferPool中查询，查询不到则从磁盘加载在BufferPool。

每次对数据的更新，也不总是实时刷新到磁盘，而是先同步到BufferPool中，涉及到的数据页就会变成脏页。

同时会启动后台线程，异步地将脏页刷新到磁盘中，来完成BufferPool与磁盘的数据同步。

如果在某个时间，MySQL突然崩溃，则内存中的BufferPool就会丢失，剩余未同步的数据就会直接消失。

虽然在更新BufferPool后，也写入了binlog中，但binlog并不具备crash-safe的能力。

因为崩溃可能发生在写binlog后，刷脏前。在主从同步的情况下，从节点会拿到多出来的一条binlog。

所以server层的binlog是不支持崩溃恢复的，只是支持误删数据恢复。InnoDB考虑到这一点，自己实现了redo log。

为什么要写两次redo log，写一次不行吗？

先不谈到底写几次redo log合适，如果只写一次redo log会有什么样的问题呢？

redo log与binlog都写一次的话，也就是存在以下两种情况：

先写binlog，再写redo log

当前事务提交后，写入binlog成功，之后主节点崩溃。在主节点重启后，由于没有写入redo log，因此不会恢复该条数据。

而从节点依据binlog在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致。

先写redo log，再写binlog

当前事务提交后，写入redo log成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。

因此，只写一次redo log与binlog，无法保证这两种日志在事务提交后的一致性。

也就是无法保证主节点崩溃恢复与从节点本地回放数据的一致性。

在两阶段提交的情况下，是怎么实现崩溃恢复的呢？

首先比较重要的一点是，在写入redo log时，会顺便记录XID，即当前事务id。在写入binlog时，也会写入XID。

如果在写入redo log之前崩溃，那么此时redo log与binlog中都没有，是一致的情况，崩溃也无所谓。

如果在写入redo log prepare阶段后立马崩溃，之后会在崩恢复时，由于redo log没有被标记为commit。于是拿着redo log中的XID去binlog中查找，此时肯定是找不到的，那么执行回滚操作。

如果在写入binlog后立马崩溃，在恢复时，由redo log中的XID可以找到对应的binlog，这个时候直接提交即可。

总的来说，在崩溃恢复后，只要redo log不是处于commit阶段，那么就拿着redo log中的XID去binlog中寻找，找得到就提交，否则就回滚。

在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，来确保redo log与binlog的数据一致性。

MySQL Service

MySQL Service层架构

MySQL Service层由连接器、分析器、优化器、执行器与插件式存储引擎接口组成（查询缓存曾经也是Service层组件之一，但在MySQL 8.0+中已被废弃），其中：

  • 连接器（Connection Handler）

    管理客户端连接生命周期，执行身份认证与权限加载。

    • 身份认证（Authentication）：验证客户端提供的用户名密码与权限表（如mysql.user）是否匹配。
    • 权限校验（Privilege Check）：从权限表加载该连接的全局权限（如SELECT、INSERT），并在连接生命周期内缓存这些权限。
    • 连接管理（Connection Management）：
      • 短连接：每次请求后立即释放资源，适用于低频操作。
      • 长连接：复用连接以减少TCP握手开销，但需注意内存泄漏风险（如未释放临时表或排序缓冲区）。

  • 分析器（Parser）

    通过词法/语法分析生成抽象语法树（AST），预处理阶段完成语义校验与常量折叠，属于SQL语句的编译过程。

    • 词法分析（Lexical Analysis）：将SQL字符串拆解为Token（如识别SELECT为关键字、识别users为表名）。
    • 语法分析（Syntactic Analysis）：基于Bison生成的LALR语法规则构建AST，若违反语法规则（如缺少FROM子句），抛出ERROR 1064 (42000)。
    • 预处理（Preprocessor）：
      1. 语义校验：检查表名、列名是否存在，解析别名作用域。
      2. 权限二次校验：验证用户对目标表的具体操作权限（如DELETE）。
      3. 常量折叠（Constant Folding）：优化表达式如WHERE 1=1 AND id>5 → WHERE id>5。

    为提升性能，MySQL采用Buffer Pool（数据页缓存）缓存编译后的SQL语句。当执行新的SQL语句时，MySQL Service层首先会在Buffer Pool中查找是否存在相应缓存的已编译语句，如果存在则直接读取缓存并执行该语句，如果不存在再进行完整的词法分析、语法分析与预处理流程，并将结果缓存入Buffer Pool。

  • 优化器（Optimizer）

    基于成本模型生成执行计划，优化索引选择与 JOIN 顺序。

    • 基于成本的优化（Cost-Based Optimization, CBO）：
      1. 索引选择：计算全表扫描与索引扫描的IO成本（通过innodb_stats_persistent持久化统计信息）。
      2. 连接顺序优化：在多表JOIN时选择小表作为驱动表（Nested-Loop Join）。
      3. 等价变换：如将LEFT JOIN转换为INNER JOIN（当WHERE子句排除NULL值时）。
    • 执行计划生成：输出EXPLAIN可读的计划，包含type（访问类型）、key（使用索引）、rows（预估扫描行数）等字段。
    • 强制索引限制：FORCE INDEX可能绕过优化器选择更优路径，需谨慎使用（如索引统计信息过期时）。

  • 执行器（Executor）：

    调用存储引擎API执行数据操作，协调事务提交与日志持久化。

    • 执行阶段
      1. 执行终检：打开表时再次校验用户对表的操作权限（即使分析器已校验）。
      2. 调用存储引擎API
        • 读操作：通过handler::read_row接口逐行获取数据。
        • 写操作：调用handler::write_row写入数据，并触发索引维护。
    • 两阶段提交（2PC）：在事务提交时协调存储引擎（InnoDB）与Server层日志。
      1. Prepare阶段：InnoDB写入redo log 并标记为PREPARED。
      2. Commit阶段：
        • 写入binlog并刷盘（将内存中的数据写入磁盘）。
        • InnoDB将redo log状态改为COMMITTED。

MySQL缓存机制概述

MySQL的缓存机制由存储引擎层缓存与服务层缓存组成，核心目标是减少磁盘IO、加速数据访问。

  • InnoDB Buffer Pool：缓存数据页和索引页，是InnoDB存储引擎的核心缓存机制。

    工作原理：

    • 数据首次读取时从磁盘加载到Buffer Pool，后续访问直接从内存获取。
    • 采用LRU算法管理缓存页，优先淘汰最久未使用的页。
    • 支持预读机制：基于局部性原理，提前加载相邻数据页以减少未来IO。
    1
    2
    3
    4
    -- 查看当前Buffer Pool大小
    SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
    -- 动态调整（需重启生效）
    SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB

  • 预编译语句缓存：缓存参数化SQL模板的编译结果，避免重复解析与优化。

    工作原理：

    • 通过PreparedStatement发送带占位符（?）的SQL模板至服务端预编译。
    • 编译后的执行计划缓存在服务端，后续仅传递参数值执行。

  • 已弃用组件：查询缓存（Query Cache，已于MySQL 8.0移除），用以缓存SELECT语句的完整结果集。

    淘汰原因：

    • 数据更新导致频繁缓存失效，高并发场景性能下降。
    • 维护成本高，与事务机制存在兼容性问题。

Buffer Pool

核心机制：

  • 数据页与索引页缓存：所有磁盘数据页（默认16KB）通过内存副本加速访问。
  • 脏页管理：修改后的数据页标记为“脏页”，由后台线程异步刷盘（Checkpoint机制）。
  • 内存分配：
    • 内存划分为缓存页（存储数据）和控制块（存储元数据，如页号、LRU链表节点）。
    • 初始化时所有页加入Free List，加载数据后移至LRU List管理。

性能优化策略：

  • LRU算法改进： 将LRU链表分为Young区（热点数据）和Old区（新加载数据），防止全表扫描污染缓存。
  • 预读机制：
    • 线性预读：按顺序访问的页触发相邻页加载。
    • 随机预读：监测同一区（Extent）内多个页的访问，触发整个区预加载。

监控与调优：

  • 关键监控指标（Buffer Pool缓存命中率、脏页比例）：SHOW ENGINE INNODB STATUS;
  • 调优技巧：
    • Buffer Pool大小设为物理内存的70%-80%（避免Swap）。
    • 避免频繁全表扫描（可能导致Young区被挤出）。
    • SHOW STATUS显示Innodb_buffer_pool_read_requests远大于Innodb_buffer_pool_reads：意味着缓存命中率低，可以考虑增加innodb_buffer_pool_size，并优化查询索引以减少全表扫描。

预编译语句缓存

优势与场景：

  • 性能提升：减少SQL解析、优化、编译时间，尤其适合高频重复查询。
  • 防SQL注入：参数与SQL模板分离，避免恶意拼接（例如经典的' OR 1=1 --）。
  • 适用场景：
    • 高频参数化查询（如电商商品详情页）。
    • ORM框架（如MyBatis默认对所有SQL预编译）。

配置与限制：

  • 服务端缓存限制：

    • 每个连接独立缓存，不同连接无法共享预编译结果。
    • 缓存大小受prepared_stmt_count参数限制（默认16382）。
    • 错误日志提示Can't create more than max_prepared_stmt_count：意味着预编译缓存内存不足，可以考虑动态调整max_prepared_stmt_count，并检查连接泄漏以确保及时关闭PreparedStatement。

  • 原生语法：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 定义预编译语句模板，“?”作为占位符
    PREPARE stmt_name FROM 'INSERT INTO member (name, grade) VALUES (?, ?)';

    -- 绑定参数
    SET @saki_name = '天馬 咲希', @saki_grade = '2-B';

    -- 执行插入
    EXECUTE insert_stmt USING @saki_name, @saki_grade;

    -- 释放资源
    DEALLOCATE PREPARE insert_stmt;

    在各个客户端内有客户端所规定的语法，请查看相应客户端的文档手册。

缓存机制对比
缓存类型 数据粒度 生命周期 适用场景
InnoDB Buffer Pool 数据页/索引页 服务运行期间 所有读写操作的热点数据加速
预编译缓存 SQL模板+执行计划 连接会话期间 高频重复的参数化查询
查询缓存（历史） 完整结果集 数据变更时失效 已废弃，不推荐使用

OLTP系统：优先调优Buffer Pool，结合预编译减少CPU开销。 OLAP系统：增大Buffer Pool以缓存常用聚合结果，避免频繁磁盘扫描。

关于Buffer Pool更多原理的整理，可以参考MySQL原理简介—4.深入分析Buffer Pool。

  • InnoDB双写机制：防止部分页写入（Partial Page Write）导致数据损坏。
  • Redo Log缓存：事务提交前日志先写入内存缓冲区（innodb_log_buffer_size），定期刷盘。

SQL索引

索引是帮助数据库高效获取数据的有序数据结构。索引是SQL标准的一部分，SQL标准定义了索引的基本概念（如通过索引加速查询），要求数据库系统提供索引功能，但并未规定其具体实现方式，因此不同数据库拥有不同的实现。MySQL的索引在存储引擎层级实现，MySQL的默认存储引擎InnoDB默认使用B+树索引。使用基于自平衡多叉树的索引将极大提高有序查询的效率与成本，但会相对地降低增删改的效率，创建索引也需要等待一段时间——但比起收益，这点代价是完全可被接受的。

不过，MySQL索引所使用的B+树并非经典的B+树。

  • 经典的B+树在B树的基础上令非叶子节点仅存储键值（所有的数据都必须出现在叶子节点上）、非叶子节点上的内容（键值与子节点指针）只作搜索用而不进行逻辑上的存储，让每个非叶子节点能够存储更多键值，降低了树的高度；同时，所有叶子节点均拥有一个指向下一顺序叶子节点的指针，形成一个单向链表，支持顺序遍历（从而支持范围查询）。
  • MySQL所使用的B+树在经典B+树基础上让所有叶子节点额外拥有一个指向上一顺序叶子节点的指针（每个MySQL的B+树指针均占用6字节），使得叶子节点形成双向链表（末叶子节点与首叶子节点互相指向），从而支持双向遍历。MySQL的B+树节点以Page为单位存储，默认大小16KB，与磁盘块大小对齐、减少了随机IO，同时针对热数据实现了AHI（自适应哈希索引），使硬盘的IO操作更加高效。
  • 直观感受MySQL的查询效率：600多万行的数据，MySQL的B+树高度为3。
存储引擎对各数据结构索引的支持情况
InnoDB（MySQL默认） MyISAM Memory
B+树索引 支持（聚簇/非聚簇，默认） 支持（聚簇，默认） 支持
Hash索引 不支持（内核支持自适应哈希索引，用户无法手动创建） 不支持 支持（默认）
R树索引（空间索引） 支持 支持 不支持
Full-text 支持（5.6版本+） 支持 不支持
MySQL索引根据用途分类
分类 用途 特性 关键字
主键索引 索引主键 1). 每个表至多有一个主键索引，允许不存在主键索引
2). 如果在表创建或表维护时指定了主键，InnoDB将自动为主键字段创建主键索引
3). 如果在表创建时未显式指定主键，则不存在主键索引
4). 移除主键约束时，主键索引也将被删除		 PRIMARY
唯一索引 避免字段内容重复 MySQL通过唯一索引实现唯一性约束，添加唯一性约束时实际上创建了唯一索引。唯一索引允许但至多允许一个NULL。 UNIQUE
常规索引 快速定位特定资源的通用索引 缺省
全文索引 用于查找文本中特定的关键词 不可进行比较 FULLTEXT

主键、主键约束和主键索引在逻辑上是互相绑定的，在一些数据库如Oracle中允许通过命令手动分离、对三者解耦，而在MySQL中三者强制绑定。

MySQL索引根据聚簇/非聚簇分类
分类 含义 特点
聚簇索引（Clustered Index） 数据与索引绑定，索引结构的叶子节点保存行数据 1). 必须存在聚簇索引，且只能存在一个
2). 如果存在主键，InnoDB默认主键索引为聚簇索引
3). 如果不存在主键，InnoDB将使用第一个唯一索引作为聚簇索引
4). 如果不存在主键且也不存在唯一索引，InnoDB将隐式生成一个名为ROW_ID的自增隐藏聚簇索引，该索引由6字节自增整型构成，无法直接访问或使用，在后续显式添加主键时，隐式主键将被移除、显式主键索引取而代之，但注意唯一索引无法触发隐式聚簇索引的替换
非聚簇索引（Secondary Index，又称二级索引、辅助索引） 索引与数据分离，索引结构的叶子节点关联的是相应主键 对数量无限制，聚簇索引以外的索引均称为非聚簇索引

对于使用InnoDB存储引擎的MySQL而言，主键索引如果存在则一定是聚簇索引，反之不一定，因为主键索引可以不存在，但聚簇索引必须存在。

聚簇索引是InnoDB的核心结构，在B+树中全局唯一，直接对应物理存储位置，行数据依靠聚簇索引才能被数据库获取，非聚簇索引只能获取目标字段的值。

clustered index and secondary index

利用索引进行回表查寻：以上图为例，当执行SELECT * FROM USER WHERE name = 'Arm';时，按照SQL语句的执行顺序，首先执行WHERE name = 'name'，这时MySQL将通过字段name的常规索引、也是非聚簇索引进行查找，获得name字段为'Arm'数据的聚簇索引值为10，接着执行SELECT *，根据聚簇索引查找索引值为10的数据行，然后返回该行数据。这种先通过非聚簇索引找到主键值、再用主键值通过聚簇索引查询数据的方式就称为回表查询。

由此可知，直接使用主键进行查询是效率最高的查询方法。通过其他字段进行查询，可能需要进行回表查寻。

索引语法

  • 创建索引：CREATE [UNIQUE | FULLTEXT] INDEX <index_name> ON <table> (field_name [ASC | DESC], [<field_name> [ASC | DESC] ...]);

    关于可选参数[UNIQUE | FULLTEXT]有三种情形：

    • 填入UNIQUE：创建唯一索引
    • 填入FULLTEXT：创建全文索引
    • 缺省：创建常规索引

    关于可选参数[ASC | DESC]也有三种情形：

    • 填入ASC：自MySQL 8.0开始支持，指定字段索引升序排序。
    • 填入DESC：自MySQL 8.0开始支持，指定字段索引降序排序。
    • 缺省：默认索引升序排序，但在查询时支持反向扫描，

    可以指定多个字段(field_name_1, field_name_2, ...)以创建联合索引，但在设计、创建联合索引时应注意字段间的顺序与指定的字段排序方向将影响索引性能与查询效率。例如应遵守最左前缀原则，将更可能在查询中被作为条件的字段放在左边。

  • 查看索引：SHOW INDEX FROM <table>;

  • 删除索引：DROP INDEX <index_name> ON <table>;

  • SQL提示，建议优化器使用指定索引：添加于WHERE前，USE INDEX(<index_name> [, <index_name> ...])

  • SQL提示，令优化器忽略指定索引：添加于WHERE前，IGNORE INDEX(<index_name> [, <index_name> ...])

  • SQL提示，强制使用指定索引：添加于WHERE前，FORCE INDEX(<index_name> [, <index_name> ...])

  • 创建前缀索引：CREATE INDEX <index_name> ON <table> (field_name(n) [, ...]);

    前缀索引针对字符串类型字段，将字符串的定长前缀而非整个字面量作为索引，节省磁盘IO，提高查询效率。前缀索引属于常规索引、非聚簇索引，只是对字符串字段特化。

    前缀长度n可以根据索引的选择性来决定，其中选择性是值不重复的索引值除数据表记录总数的比值。选择性越高查询效率越高，但包括存储空间与更新开销在内的索引维护成本也随之水涨船高。当选择性为1此时索引等价于唯一索引。

    根据选择性的定义，可以在MySQL中计算前缀长度n的选择性：SELECT COUNT(DISTINCT SUBSTRING(<field>, 1, n))/COUNT(*) FROM <table>;

索引使用原则

遵守若干使用原则，能够大大提高查询时索引的使用效率。至于这些原则被确立背后的原因，需要先阅读后文中的性能分析 3：EXPLAIN / DESC再结合索引才能理解。

  • 最左前缀原则：针对联合索引查询，查询条件应包含联合索引的最左侧字段，并且尽量不无故跳过中间列。特别是查询条件应当包含联合索引的最左侧字段，这是最为重要的一点。

    究其原因是如果查询条件不包含某列，则按照创建联合索引时指定的顺序，该列以后的字段索引将失效。因此，如果跳过中间列，则联合索引部分失效；在最极端的情况下，如果跳过联合索引的第一列，则联合索引完全失效——如果还希望利用索引，则查询条件至少应包含联合索引的第一列，也就是最左列。查询条件是否包含联合索引的最左侧字段，结果存在相当之大的数量级程度的差异。

    应注意，查询语句中字段的顺序与查询性能完全无关，”最左“是指查询条件应包含”创建联合索引时最左侧“字段。例如，假设创建索引时所指定联合索引字段顺序为(f1, f2, f3)，则查询语句SELECT * FROM t WHERE f1 = 0 AND f2 = 1;与SELECT * FROM t WHERE f2 = 1 AND f1 = 0;没有任何性能上的差异，因为他们都包含了（创建联合索引时的）联合索引的最左列，能够利用部分索引，查询效率高。

  • 避免>、<范围查询：针对联合索引查询，如果出现>、<范围查询，则联合索引中范围查询字段右侧的字段将全部失效，原因是>、<会破坏B+树的序结构，优化器无法通过二分法快速定位后续字段。如果业务方面允许，改为使用>=与<=可以避免这一问题。

  • 尽量不要在索引列上进行运算，包括使用字符串函数，很可能导致该索引失效。

  • 对字符串的比较应规范地用单引号囊括字面量。如果不添加单引号，虽然查询语句能够完成查询并通过隐式转换正确处理字符串比较逻辑，但如果该字符串字段为索引字段，则索引会因为隐式转换而失效。

  • 模糊查询字符串时尽量将通配符放在末尾：模糊查询时，如果在字符串字面量头部使用通配符，将导致索引完全失效；在字符串字面量中间使用通配符将导致部分失效，优化器可能选择对通配符以前的内容继续索引范围扫描而对通配符以后的内容进行条件过滤；在字符串字面量尾部使用通配符能够将索引失效程度控制在最低范围内。

  • 避免用一个或多个OR连接索引字段与非索引字段：当查询条件中使用OR连接多个字段时，只要其中一个字段没有索引，整个查询的索引就会失效，导致全表扫描。

  • 尽量做到覆盖索引，尽量少使用SELECT *：尽可能让查询内容恰也是索引字段，避免回表查询。

  • 如果存在多个查询条件，推荐创建联合索引，让优化器选择合适的索引与索引组合，减少回表查询的情况。

若违背以上原则，很可能导致索引失效从而大大增加查询操作执行时间。数据分布是数据库优化器决定是否使用索引的核心因素之一：数据库优化器（如MySQL的查询优化器与Oracle的CBO）通过统计信息（如常用统计量、不同值数量NDV、空值比例、直方图等）评估索引的选择性，即使使用索引字段进行查询，优化器也可能选择不使用索引。例如，索引字段不存在NULL值却试图查询该字段NULL值的行数据，这时优化器会选择直接进行全盘扫描。

索引设计原则

  1. 对数据量较大、查询频繁的表建立索引。
  2. 对常作为查询条件（WHERE）、排序（ORDER BY）、分组（GROUP BY）操作的字段建立索引。
  3. 尽可能选择区分度高的字段作为索引，尽量建立唯一索引，因为区分度越高、索引使用效率越高。
  4. 如果字段是字符串类型且长度较长，可以考虑建立前缀索引。
  5. 尽量使用联合索引而不去使用单列索引，因为进行查询时联合索引常常能够做到覆盖索引，避免回表查询。
  6. 应控制索引的数量。索引并非多多益善，索引越多，维护索引结构的代价越大，影响增删改操作的效率。
  7. 如果索引列不会存储NULL值，则应使用NOT NULL约束，因为当优化器知道哪些列不包含NULL时，能帮助其更好地确认应当选择使用哪个索引以进行最高效查询。

SQL优化

性能分析 1：DML与DQL语句执行频次

SQL优化的主要工作是对查询操作SELECT进行优化，其中查询操作的优化又与索引密切相关。因此，要评判索引是否合理、数据库是否需要优化，需要首先了解DML与DQL语句的执行频率。

  • 使用模糊匹配查询INSERT、UPDATE、DELETE、SELECT访问频次：SHOW [SESSION | GLOBAL] STATUS LIKE 'Com_______';

性能分析 2：慢查询日志

MySQL提供了慢查询日志，但默认没有开启。慢查询日志能够记录所有执行时长超过指定数值的查询操作，启用后默认记录执行时长超过10秒的查询语句。

  • 查询慢查询日志是否启用：SHOW VARIABLES LIKE 'slow_query_log';
  • 启用慢查询：在MySQL配置文件/etc/my.cnf中将slow_query_log=0修改为slow_query_log=1
  • 更改慢查询判断条件：MySQL配置文件/etc/my.cnf中将long_query_time=10修改为指定数值，单位为秒

在Linux系统中，慢查询日志默认存放于/var/lib/mysql/localhost-slow.log。修改完配置文件后，不要忘记用命令systemctl restart mysqld重启MySQL服务。可以通过tail -f localhost-slow.log查询日志尾部的实时新增内容。

性能分析 3：EXPLAIN / DESC

在SQL语句句首添加EXPLAIN或DESC即可在执行语句后获取该语句的执行计划，即该指令的具体执行过程，包括访问路径、表如何连接、表连接顺序、索引使用等关键信息。

返回结果重要字段解释：

  • type：MySQL访问表数据的方式，按效率高到低依次为
    1. system与const：分别表示表中仅有一行数据与通过主键或唯一索引找到唯一匹配行。
    2. eq_ref：多表连接时，使用主键或唯一索引的等值匹配，例如关联条件为主键的JOIN操作。
    3. ref：通过非唯一索引的等值匹配，可能返回多行数据。
    4. range：索引范围扫描且仅扫描索引中特定区间，例如BETWEEN、IN、>等等。
    5. index：全索引扫描，遍历了整个索引树，应检查是否缺少索引或索引未生效，可能需要考虑优化。
    6. ALL：全表扫描，性能最差，应检查是否缺少索引或索引未生效，可能需要优先考虑优化。
  • possible_keys：MySQL优化器考虑使用的索引列表，如果为空意味着查询条件未命中任何索引，可能需要检查WHERE子句或表索引设计。
  • key：MySQL优化器最终选择的索引，若key为NULL表示未使用索引，原因可能是未遵守最左前缀原则，需要考虑优化；若列出的索引不在possible_keys中，则可能是覆盖索引或隐式优化。
  • key_len：实际使用的索引字节数，常用于判断是否充分利用联合索引，例如当key_len仅为联合索引前几列的字节数，意味着使用索引时可能没有遵守最左前缀原则，未能完全利用索引。
  • rows：MySQL优化器根据统计信息预估的需要扫描的行数。该值越小，表明查询效率越高。若rows远大于实际返回行数，说明索引未充分利用或条件过滤性差，可能需要通过覆盖索引减少回表次数或优化条件、使用更精确的过滤逻辑。
  • filtered：rows × filtered为与其他表连接时的预估行数，如果这一数值过大可能需要考虑优化关联逻辑。
  • Extra：执行的附加信息。除NULL外，常见的值有
    • Using index：查询仅通过索引即可获取数据（覆盖索引），此为最佳优化状态，无需回表。
    • Using where：使用WHERE条件过滤存储引擎返回的数据，需要检查type。
    • Using index condition：查询使用了索引，但需要进行回表查询。
    • Using filesort：通过表的索引或全表扫描读取满足条件的数据行，再在排序缓冲区中完成排序，没有通过索引直接返回排序结果。
    • Using temporary：需要创建临时表以处理查询，涉及GROUP BY、UNION等操作可能会得到该信息，表明该语句内存消耗高，可以尝试通过索引优化GROUP BY或改写SQL语句以避免临时表。
    • Using filesort：需要额外排序操作，典型如ORDER BY未命中索引排序，效率低下，可考虑为排序字段添加索引或减少排序数据量。
    • Using join buffer：表连接时使用了连接缓冲区，典型如未命中索引的全连接，可考虑检查关联条件是否命中索引。

性能分析 4：profile详情（MySQL 8.0+已弃用）

通过profile详情，可以查看每一条SQL语句的执行耗时。

  • 查看当前MySQL是否支持profile操作：SELECT @@have_profiling;
  • 启用profiling（默认关闭）：SET [SESSION | GLOBAL] profiling = 1;
  • 查看profile概览：SHOW PROFILES;
  • 查看指定SQL查询语句在各阶段的耗时：SHOW PROFILE FOR QUERY <query_id>;
  • 查看特定SQL语句的CPU使用情况：SHOW PROFILE CPU FOR QUERY <query_id>;

profile详情现已弃用，请考虑Performance Schema。

索引的设计目的是大大加快查询操作的执行，因此关于查询操作的优化已在索引的章节中一并列出。这里主要记录其他SQL操作的优化。

INSERT优化

批量插入：如果需要插入多条数据，应考虑批量插入语句，而不是一条一条地插入。但即使是批量插入，每次插入建议数据量为五百至一千条，一次性批量插入过多数据会带来批量插入过多数据的潜在后果，例如务锁竞争加剧、内存与I/O压力陡增、日志膨胀及网络传输瓶颈等。如果有大量数据需要插入，应考虑分割为若干批量插入语句。

手动提交事务：在执行多条批量插入语句时，应选择手动开启事务，在执行多条批量插入语句后再提交事务。

尽量主键顺序插入：MySQL主键顺序插入效率比主键乱序插入高得多，其原理涉及主键优化。

改用LOAD插入海量数据：如果有海量数据需要插入，使用INSERT不是一个好选择，特别是需要加载的海量数据就在本地磁盘上时。在客户端连接至服务端时，附带参数--local-infile（即mysql --local-infile -u <user> -p）表示将使用本地文件，可执行SELECT @@local_infile;查看本地加载文件导入数据功能是否启用，接下来执行SET [SESSION | GLOBAL] local_infile = 1;启用本地加载文件导入数据功能，接着执行LOAD DATA LOCAL INFILE '<path>' INTO TABLE <table> FIELDS TERMINATED BY '<delimiter>' LINES TERMINATED BY '<line_terminator>';，其中'<line_terminator>'默认为'\n'，若需要修改为Windows换行符则应指定为'\r\n'。对应本地文件，可以通过HEAD <file_name>;查看前几行数据以了解数据格式。通过LOAD插入巨量数据，执行耗时远低于INSERT。

主键优化

在InnoDB中，存储方式为索引组织表（index organized table，IOT），即表数据根据主键顺序进行组织与存储。乱序插入可能导致页分裂，删除数据可能导致页合并，浪费性能。

  • 如果可能，尽量降低主键长度
  • 尽量顺序插入，比如选择自增主键
  • 尽量不要选择UUID或其他自然主键，比如身份证号
  • 应避免对主键的修改

ORDER BY优化

ORDER BY优化的目标是尽量避免Extra中出现Using filesort，因为当有序索引本身能够直接提供排序结果时，排序查询语句效率最高——这时程序不需要先读取所需数据行、再在排序缓冲区中进行排序。

  • 单字段排序：
    • 若该字段有独立索引（非联合索引），无论升序（ASC）还是降序（DESC）均能直接利用索引有序性，避免Using filesort。
    • 若字段为联合索引的最左前缀且查询仅排序该字段，如果创建索引时没有显式指定索引排序方向，无论索引排序方向均可或直接或通过反向扫描索引实现高效排序；如果创建索引时有显式指定索引排序方向，则字段查询排序方向必须与指定索引排序方向一致才能实现高效排序。
    • 其他情形将导致Using filesort或触发全表扫描。
  • 多字段排序：
    • 若每个排序字段都具有独立索引，由于多个独立索引无法合并排序顺序，将出现Using filesort二次排序。
    • 使用联合索引进行多字段排序查询时，由于MySQL 8.0+支持显式定义混合方向索引，因此在严格遵循最左前缀原则的基础上，查询方向与索引创建时的排序方向完全一致就可以避免Using filesort；在MySQL 8.0以前因为默认全升序，要求查询方向如果全为升序或全为降序才能避免Using filesort。
    • 其他情形将导致Using filesort或触发全表扫描。
  • 尽量覆盖查询以避免回表。
  • 如果不可避免地会出现Using filesort并且排序涉及数据量较大，应适当增加排序缓冲区的大小。
    • 查询当前排序缓冲区大小：SHOW VARIABLES LIKE 'sort_buffer_size';，默认256KB
    • 动态临时更改排序缓冲区大小：SET GLOBAL sort_buffer_size = <size>;
    • 永久性配置排序缓冲区大小：在MySQL配置文件中写入sort_buffer_size = <size>

GROUP BY优化

GROUP BY优化的目标是尽量避免出现Using temporary。遵守最左前缀原则并尽可能覆盖索引，减少回表查询。

LIMIT优化

数据量过大时，MySQL特有的LIMIT分页查询操作执行耗时将大幅增加。例如，LIMIT 9000000, 10需要排序前9000010条数据记录，但最终却只返回最后10条数据记录，查询时排序的代价相当大。

既然耗时的过程主要是排序，那么limit优化思路与ORDER BY优化相同，尽量做到覆盖索引。

COUNT优化

MyISAM引擎会将一张表的总行数存储在磁盘上，因此SELECT COUNT(*) FROM <table>;时会直接返回总行数，执行速度极快。但是，InnoDB引擎则没有类似的设计。因此，对InnoDB引擎来说，或者对有条件查询时的MyISAM引擎来说，执行COUNT都需要进行全表查询并累积计数，相当耗时。

在前文中提到COUNT的几种用法，按效率进行顺序排序为：

  1. COUNT(*)与COUNT(num)
  2. COUNT(primary_key)与COUNT(not_null_field)
  3. COUNT(field)

因此如果要使用COUNT函数获得总行数，建议使用COUNT(*)，数据库对此进行了优化，操作过程中不会取出字段，节约了IO操作。

要优化这一过程，只能通过我们自行维护计数。可以通过其他高性能的内存级数据库，例如Redis，记录SQL语句、推算其对行数的影响并更新计数值。

UPDATE优化

UPDATE优化的目标是尽量避免出现行级锁升级为表锁。请移步至锁的章节。

SQL锁

全局锁

全局锁的作用对象是整个MySQL数据库实例。对MySQL数据库实例施加全局锁后，实例将处于只读状态，针对任何数据库与表的任何DML、DDL与事务提交都将被阻塞。

为全库逻辑备份（为.sql文件）是全局锁的典型使用场景，通过锁定所有表以获取一致性视图，进而保证数据的完整性。

对MySQL数据库实例添加全局锁：FLUSH TABLES WITH READ LOCK;

释放当前会话持有的所有表级锁（包括全局锁）：UNLOCK TABLES;

利用mysqldump数据备份：mysqldump -h [ip] -u root -p [db] > [path];

InnoDB无锁快照数据备份：mysqldump -h [ip] -u root -p --single-transaction [db] > [path];

将[db]替换为--all-databases可以对所有数据库进行备份。注意mysqldump不是MySQL语句，应当在操作系统的终端命令行中输入，而非MySQL命令行中。

表级锁

表级锁是若干种不同锁的统称，作用对象是数据表。表级锁锁定颗粒度大，发生锁冲突概率最高，并发度最低。

SQL标准表级锁分类
表级锁分类 细分 SQL标准 SQL语法
显式表级锁

由用户主动控制数据访问		 1). 共享读锁 (Shared Read Lock)

2). 独占写锁 (Exclusive Write Lock)		 1). 共享读锁（共享锁，S锁）允许所有会话并发读取，禁止任何会话写入

2). 独占写锁（排他锁，X锁）具有完全排他性，仅允许加锁会话读写，禁止其他会话读写		 1). 施加共享读锁：LOCK TABLE <table> READ;

2). 施加独占写锁 ：LOCK TABLE <table> WRITE;

3). 释放当前会话持有的所有显式表级锁：UNLOCK TABLES;
MDL（Meta Data Lock）

由MySQL系统自动管理元数据一致性		 1). 普通SELECT与SELECT ... LOCK IN SHARE MODE触发的MDL

2). DML与SELECT ... FOR UPDATE触发的MDL

3). DDL触发的MDL		 SQL标准仅定义了共享锁（S锁）、排他锁（X）锁，并未定义MDL。 SQL标准未定义MDL，无相关语法。此外，即使在MySQL中MDL也是由系统管理，用户无法直接操作，只能通过MySQL的机制触发。
意向锁（Intention Lock）

由MySQL系统自动管理，协调（执行DML时）行级锁与表级锁的冲突，使表级锁不必检查每一行是否加锁，提高效率		 1). SELECT ... LOCK IN SHARE MODE首先触发施加表的意向共享锁（IS），随后施加行的S锁

2). DML与与SELECT ... FOR UPDATE触发的意向排他锁（IX）		 SQL标准仅定义了共享锁（S锁）、排他锁（X）锁，并未定义意向锁。 SQL标准未定义MDL，无相关语法。此外，即使在MySQL中意向锁也是由系统管理，用户无法直接操作，只能通过MySQL的机制触发。

MDL与事务生命周期强绑定，对于由DDL触发的MDL将在操作完成后立即释放，其余MDL将在事务结束时释放。

意向锁则随行级锁释放或事务结束释放。

MySQL表级锁分类
表级锁分类 细分 MySQL锁类型 MySQL表现
显式表级锁

由用户主动控制数据访问		 1). 共享读锁 (Shared Read Lock)

2). 独占写锁 (Exclusive Write Lock)		 1). SHARED_READ_ONLY

2). SHARED_NO_READ_WRITE		 1). 对于共享读锁，加锁会话写入将触发错误，非加锁方的写入将被阻塞

2). 对于独占写锁，其他会话的读写将被阻塞
MDL（Meta Data Lock）

由MySQL系统自动管理元数据一致性		 1). 普通SELECT与SELECT ... LOCK IN SHARE MODE触发的MDL

2). DML与SELECT ... FOR UPDATE触发的MDL

3). DDL触发的MDL		 1). SHARED_READ（SR）

2). SHARED_WRITE（SW）

3). EXCLUSIVE（X）		 1). SR锁：允许加锁会话读取元数据和表数据但禁止表结构变更（DDL操作），允许其他会话并发读取，阻塞其他会话DML操作

2). SW锁：允许加锁会话读写元数据和表数据，允许其他会话普通SELECT（快照读），阻塞其他会话SELECT ... FOR UPDATE与DML操作

3). X锁：加锁会话完全独占元数据和表数据权限，允许加锁会话的任何操作，阻塞其他会话的DQL与DML操作，其他会话尝试执行DDL时将触发错误或等待超时

4). 与SHARED_READ、SHARED_WRITE与SHARED_READ、SHARED_WRITE兼容，与EXCLUSIVE互斥

5). EXCLUSIVE与其他,MDL均互斥
意向锁（Intention Lock）

由MySQL系统自动管理，协调（执行DML时）行级锁与表级锁的冲突，使表级锁不必检查每一行是否加锁，提高效率		 1). SELECT ... LOCK IN SHARE MODE首先触发施加表的意向共享锁（IS），随后施加行的S锁

2). DML与与SELECT ... FOR UPDATE触发的意向排他锁（IX）		 1). IS

2). IX		 1). IS与S锁兼容，与X锁互斥

2). IX与S锁、X锁均互斥

3). 意向锁之间互相兼容

SQL标准明确定义了元数据为”描述数据的数据“，包括表结构、列类型、索引等信息，并要求通过INFORMATION_SCHEMA等接口提供元数据查询能力，但并未规定应当如何实现。对MySQL而言，元数据包括数据库自身的元数据（名称、字符集、排序规则、创建时间等）、表元数据（名称、存储引擎、行数、表注释等）、列元数据（字段名、数据类型、约束、注释等）、索引元数据（名称、索引类型、索引列等）、权限与安全元数据（用户权限、角色定义、访问控制规则等）和服务器元数据（MySQL 版本号、当前连接状态、配置参数等）。

MDL的核心目的正是维护元数据在多线程环境中的一致性、保证元数据安全，包括但不限于防止DDL与DML并发导致的元数据冲突、支持事务隔离性中的表结构稳定性与保障主从复制的数据一致性。

MySQL当前所有存在的MDL都存储在系统表performance_schema.metadata_locks，可以使用SELECT object_type, object_schema, object_name, lock_type, lock_duration FROM performance_schema.metadata_locks语句查询当前所有的MDL。

MySQL当前所有存在的意向锁与行级锁都存储在系统表performance_schema.data_locks，可以使用SELECT object_type, object_schema, object_name, lock_type, lock_duration FROM performance_schema.data_locks语句查询当前所有的意向锁。

行级锁

行级锁的锁定颗粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB与MyISAM最大的区别体现在事务、外键与行级锁上。InnoDB的数据基于聚簇索引而组织，行级锁通过在索引上对索引项加锁实现，MyISAM则不支持行级锁。

MySQL（InnoDB）行级锁实现方式
行级锁实现方式 锁目的 细分 事务隔离级别支持情况
记录锁（Record Lock，也称为行锁） 锁定单个索引记录（非物理行），确保其他事务无法修改或删除该数据行记录。 1). S锁：允许并发读取数据，禁止其他事务加X锁（允许读共享，禁止写独占）。在MySQL中锁名为S。

2). X锁：允许当前事务修改数据，阻止其他事务对同一数据集加任何锁（独占锁）。在MySQL中锁名为X。		 Read Uncommitted，Repeatable Read
间隙锁（Gap Lock） 锁定索引记录之间的间隙（不包含记录本身），防止其他事务在范围内插入新数据，避免幻读。 Repeatable Read 及以上
临键锁（Next-Key Lock） 记录锁与间隙锁的组合，锁定索引记录及左开右闭区间，防止幻读和不可重复读。 Repeatable Read 及以上

默认情况下，InnoDB处于Repeatable Read事务隔离级别，此时InnoDB会直接使用临键锁进行搜索与索引扫描以防止幻读，但在以下两种情况下临键锁将被优化或升级：

  1. 性能优化：当通过唯一索引执行等值查询命中已存在的记录时，临键锁退化为记录锁。
  2. 并发性能下降，确保事务一致性：若未命中索引，随后进行全表扫描触发隐式聚簇索引锁，则所有行加记录锁、所有间隙加间隙锁，锁定范围覆盖全表。尽管整体效果类似表级锁，但底层仍为行级锁机制。
  3. 性能优化：当通过唯一索引执行等值查询且目标记录不存在时，InnoDB会将临键锁退化为间隙锁，锁定目标值所在的索引区间以防止幻读。
  4. 性能优化：当通过非唯一索引执行等值查询时，若索引遍历过程中遇到第一个不满足条件的记录，InnoDB会将临键锁退化为间隙锁，锁定目标值与第一个不匹配记录之间的索引区间。
  5. 性能优化：在唯一索引的范围查询中，InnoDB会继续向右扫描至第一个不满足条件的记录，并对该记录及之前的索引区间施加临键锁；若该记录不满足查询条件，临键锁退化为间隙锁（Gap Lock）。
MySQL（InnoDB）记录锁间的行为

请求锁：型记录锁请求锁：型记录锁当前锁：型记录锁兼容冲突当前锁：型记录锁冲突冲突

与记录锁不同，间隙锁的目的只是防止其他事务在间隙插入新数据，因此不同事务的间隙锁是可以共存的，同一处间隙上可以同时存在多个事务的间隙锁。

MySQL（InnoDB）记录锁触发时机
SQL 行级锁类型 说明
INSERT X锁 自动加锁
UPDATE X锁 自动加锁
DELETE X锁 自动加锁
普通SELECT 不会加锁
SELECT ... LOCK IN SHARE MODE S锁 需要指定... LOCK IN SHARE MODE
SELECT ... FOR UPDATE X锁 需要指定... FOR UPDATE

对于行级锁，InnoDB遵循两阶段锁协议：事务执行过程中按需加锁，所有锁在事务提交或回滚时统一释放，但存在个别例外情况：

  • Read Uncommitted与Read Committed隔离级别下，扫描到的记录不满足WHERE条件时，InnoDB存储引擎或Server层会立即释放该记录上的行锁。
  • 在二阶段提交的预备阶段，InnoDB将释放间隙锁，但保留记录锁。
  • 在自增主键插入场景中，轻量级锁在获取自增值后立即释放，无需等待事务提交。

MySQL主从复制与故障转移

图片引用自一文详解MySQL主从同步原理。

全同步复制、异步复制、半同步复制与增强半同步复制的区别可以参考Mysql系列-主从同步。

MySQL主从同步问题一览

Redis

本文对Redis的学习参考自黑马程序员Redis入门到实战教程，深度透析Redis底层原理+Redis分布式锁+企业解决方案+黑马点评实战项目。

Structured Query Language?

Redis（Remote Dictionary Server）是一种基于内存的键值型数据库，常被用于缓存，属于NoSQL，区别于MySQL等传统的关系型数据库。除了以Redis为代表的键值型数据库外，NoSQL还包含文档型数据库（如MongoDB）、列类型数据库（如HBase）与图类型数据库（如Neo4j）等。

数据库中的事务隔离级别
SQL NoSQL
数据结构 结构化 非结构化
数据关联 有关联 无关联
查询方式 一般支持标准SQL查询语句 不一定或不完整支持SQL查询语句
事务特性 ACID
原子性、隔离性、持久性、一致性		 BASE
不完全满足ACID，特别是至多满足基本一致性
存储介质 磁盘 内存
扩展性 垂直 水平
使用场景 1) 数据结构固定
2) 业务对数据安全性、一致性有较高要求		 1) 数据结构不固定
2) 业务对数据安全性、一致性要求较低
3) 业务对性能要求较高

Redis对主流程序设计语言有广泛支持，其本身具有一些特点：

  • 键值型数据库，值支持多种数据结构
  • 单线程，命令具有原子性，线程安全（部分命令会多线程处理，但主线程仍是单线程的）
  • 基于内存，延迟低、性能强劲
  • 支持数据持久化
  • 支持主从集群、分片集群

Redis默认支持16个数据库，如果需要用到更多的数据库，则需要预先在配置文件redis.conf中进行指定，这与MySQL是不一样的。

Redis数据结构

作为一种支持多种数据结构的键值型数据库，Redis的键通常为String类型，值则支持多种类型。以下为Redis常用的八种数据结构：

  1. String（Redis允许单引号或双引号囊括字面量以表示字符串，不同于SQL仅使用单引号）
  2. Hash（又名散列，实为哈希表，值是无序字典，类似HashMap）
  3. List（类似LinkedList，可以用来模拟栈、队列、阻塞队列等）
  4. Set（类似HashSet，可以视为值为null的HashMap，具有哈希表的若干特征，查找效率高）
  5. SortedSet（功能上类似TreeSet，但底层实现上完全不同）
  6. GEO
  7. BitMap
  8. HyperLog

其中前五种数据类型被称为基本类型，使用最频繁。其余数据类型被称为特殊类型，作为对基本类型们的封装，例如所列举的后三种数据类型。

关于Redis数据结构的帮助信息，可以在Commands上查看完整文档。也可以通过redis-cli命令help @<group>查看帮助摘要。

Redis的键通常以“:”作为层级分隔。例如：世界计划:Leo/need:天马咲希。

String类型又可以根据格式的不同被分为string、int与float。

如果将Java对象序列化为JSON字符串后存储为String类型，需要单独修改某个字段时会碰见麻烦；Hash类型则可以将对象中的每个字段独立分别储存，能够单独针对某个字段进行CRUD操作。对于Hash类型的键与字段，可以将其想象为类与成员变量名的关系。

SortedSet中的每一个元素都带有score属性，可以根据score属性对元素排序。其底层功能是通过一张跳表与一张哈希表实现的。

Redis命令

Redis数据库命令

  • AUTH <password>：密码登录。
  • SELECT <index>：切换到指定数据库。不同数据库间存在数据隔离，Redis默认支持16个数据库（编号0到15）。默认连接数据库0。
  • DBSIZE：返回当前数据库全部键的数量。
  • FLUSHDB [ASYNC]：FLUSHDB同步执行当前数据库清空操作，阻塞主线程直至完成，不应在生产环境设备上使用。FLUSHDB ASYNC则异步执行当前数据库清空操作，主线程可以继续响应其他命令。
  • FLUSHALL [ASYNC]：同上，但会清空Redis的所有数据库。
  • PING [message]：测试服务是否存活，如果存活则返回消息。默认返回"PONG"。
  • INFO [section]：查询服务器状态信息，包括CPU、内存与持久化等信息。根据参数可以指定特定的查询内容，例如INFO memory指定查询内存使用详情。
  • SAVE：同步执行 RDB 持久化，阻塞主线程直至完成。在生产环境设备上应避免使用。
  • BGSAVE：异步执行 RDB 持久化。
  • BGREWRITEAOF：异步重写AOF文件，优化日志体积。

数据操作通用命令

有关Redis命令的帮助文档同样可以在Commands上查看，也可以通过redis-cli命令help <command>查看帮助摘要。

  • KEYS <pattern>：根据模式进行匹配、查询键。作为通配符，KEYS *会查询所有键。在生产环境设备上不建议使用该命令，因为当数据量过大时，Redis单线程的命令执行方式会导致阻塞。
  • TYPE <key>：查看值的数据类型，如果键不存在则返回none。
  • RENAME <oldkey> <newkey>：重命名键，如果新键名已存在则覆盖键值。这是一个原子操作。
  • MOVE <key> <db_index>：将键移动到另一个数据库，前提是目标数据库不能已存在同名的键。
  • DEL <key> [key ...]：根据键名删除指定的键，并返回删除键的数量。
  • EXISTS <key> [key ...]：判断键是否存在，返回0或1表示键的存在与否。
  • EXPIRE <key> <seconds>：为键设定生命周期。只能给已存在的键设定。
  • TTL <key>：查询键的剩余有效时间。返回值-2表示不存在或已到期，返回值-1表示永久可用，非负返回值表示剩余有效时间。

依赖数据类型的数据操作非通用命令

  • String

    • SET <key> <value>：添加键值对，或修改已存在的String类型键值对。
    • GET <key>：根据键获取String类型的值。
    • MSET <key1> <value1> [key2 value2 ...]：批量添加键值对或修改已存在的String类型键值对。
    • MGET <key> [key ...]：批量根据键获取String类型的值。
    • INCR <key>、DECR <key>：根据键，令整型的值自增/减一。
    • INCRBY <key> <increment>、DECRBY <key> <increment>：根据键，令整型的值自增/减指定步长。
    • INCRBYFLOAT <key> <increment>、DECRBYFLOAT <key> <increment>：根据键，令浮点型的值自增/减指定步长。注意，浮点型值必须指定步长，没有默认的自增/减方法。
    • SETNX <key> <value>：如果键不存在，则添加键值对，否则不执行。
    • SETEX <key> <seconds> <value>：添加键值对，并设定生命周期。该命令是一个组合命令，等价于SET <key> <value> EX <seconds>。

  • Hash

    • HSET <key> <field> <value>
    • HGET <key> <field>
    • HMSET <key> <field> <value> [field value ...]
    • HMGET <key> <field> [field ...]
    • HGETALL <key>：获取键的所有字段与值。
    • HKEYS <key>：获取键的所有字段。
    • HVALS <key>：获取键的所有值。
    • HINCRBY <key> <field> <increment>：根据键与字段，令整型的值自增制定步长。
    • HSETNX <key> <field> <value>：如果字段不存在，则添加字段与值，否则不执行。

  • List

    • LPUSH <key> <element> [element ...]、RPUSH <key> <element> [element ...]：向列表左/右侧插入一个或多个元素。
    • LPOP <key> 、RPOP <key> ：移除列表左/右侧第一个元素并返回，若列表为空则返回nil。
    • LRANGE <key> <start> <end>：返回指定起止偏移量范围内的所有元素。偏移量0表示首个元素，并且可以为负，类似Python切片语法里的索引。
    • BLPOP <key> [key ...] <timeout>、BRPOP <key> [key ...] <timeout>：移除列表左/右侧第一个元素并返回，若列表为空则等待指定时间（而不是立刻返回nil），在此期间程序阻塞。等待期内当列表更新、不为空时移除列表左/右侧第一个元素并返回，同时返回等待时间。

  • Set

    • 针对元素命令

      • SADD <key> <member> [member ...]：向集合添加一个或多个元素。
      • SREM <key> <member> [member ...]：移除集合中指定的一个或多个元素。
      • SCARD <key>：返回集合中元素的个数。
      • SISMEMBER <key> <member>：判断指定元素是否存在于集合中，返回0或1表示元素的存在与否。
      • SMEMBERS <key>：获取集合中的所有元素。

    • 针对集合命令

      • SINTER <key1> [key2 ...]：求集合的交。

      • SUNION <key1> [key2 ...]：求集合的补。

      • SDIFF <key1> [key2 ...]：求集合的差。

        注意，，差集具有左结合性，并且不满足交换律。直观上，DIFF命令可以理解为“从左到右剔除元素”，也可以根据数学定义理解为对目标集合去除其余集合中所含有的元素，而只留下其“特有”的元素。

  • SortedSet

    • ZADD <key> <score> <member> [score member ...]：向SortedSet中添加一个或多个元素，对于已存在的元素则更新其score属性。
    • ZREM <key> <member> [member ...]：移除SortedSet中指定的一个或多个元素。
    • ZSCORE <key> <member>：获取SortedSet中指定元素的score属性。
    • ZRANK <key> <member>：获取SortedSet中指定元素的排名，排名0代表score最低。
    • ZCARD <key>：获取SortedSet中元素的数量。
    • ZCOUNT <key> <min> <max>：获取SortedSet中score属性在给定范围内（闭区间）的元素的数量。
    • ZINCRBY <key> <increment> <member>：让SortedSet中的指定元素以指定步长自增。
    • ZRANGE <key> <min> <max>：获取SortedSet中根据score属性排序后排名在给定范围内（闭区间）的元素。
    • ZRANGEBYSCORE <key> <min> <max>：获取SortedSet中score属性在给定范围内（闭区间）的元素。
    • ZINTER <key1> [key2 ...]：求SortedSet的交。
    • ZUNION <key1> [key2 ...]：求SortedSet的补。
    • ZDIFF <key1> [key2 ...]：求SortedSet的差。

Jedis与Spring Data Redis

Redis有许多客户端，即使只考虑支持Java语言的客户端，也有非常多的选择，更多信息可以参考官方文档Connect with Redis client API libraries。

在Redis的所有Java客户端中，Jedis与Lettuce最受推荐。Jedis以Redis命令作为方法名称，简单实用，但是Jedis的实例不能保证线程安全，在多线程环境下需要配合连接池使用。Lettuce基于高性能的网络应用框架Netty实现，支持同步、异步、响应式编程等诸多特性，并且能够保证线程安全，对Redis的支持也十分广泛。如果还需要分布式支持，则可以考虑Redission。

Spring Data Redis是一套同时兼容Jedis与Lettuce的API。因此，在简单了解Jedis后，可以直接学习Spring Data Redis。

一个简单的Jedis使用步骤如下：

  1. 首先引入依赖。例如，如果使用Maven，则在pom.xml中添加依赖项，

    1
    2
    3
    4
    5
    <dependency>
        <groupId>redis.clients</groupId>
        <artifactId>jedis</artifactId>
        <version>5.2.0</version>
    </dependency>

  2. 接下来创建Jedis对象、建立数据库链接，

    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
    import redis.clients.jedis.Jedis;

    public class JedisTest {
        private Jedis jedis;
        
        // 建立连接
        void setUp() {
            jedis = new Jedis("127.0.0.1", 6379);
            jedis.auth("myPassword");
            jedis.sellect(0);
        }
        
        void test() {
            String state = jedis.set("name:1", "天馬咲希");
            System.out.println("state is " + state);
            
            String name = jedis.get("name:1");
            System.out.println("name:1 = " + name);
        }
        
        // 释放资源
        void tearDown() {
            if (jedis != null) {
                jedis.close();
            }
        }
    }

由于Jedis实例线程不安全，频繁创建、销毁链接又存在性能损耗，因此推荐使用Jedis连接池代替Jedis的直接连接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import redis.clients.jedis.Jedis;
import redis.clients.jedis.JedisPool;

public class JedisConnectionFactory {
    private static final JedisPool jedisPool;
    
    static {
        JedisPoolConfig jedisPoolConfig = new JedisPoolConfig();
        jedisPoolConfig.setMaxTotal(8);
        jedisPoolConfig.setMaxIdle(8);
        jedisPoolConfig.setMinIdle(0);
        jedisPoolConfig.setMaxWaitMills(200);
        jedisPool = new JedisPool(jedisPoolConfig, "127.0.0.1", 6379, 1000, "myPassword");
    }
    
    public static Jedis getJedis() {
        return jedisPool.getResource();
    }
}

基于连接池，将JedisTest类建立连接的代码jedis = new Jedis("127.0.0.1", 6379);修改为jedis = JedisConnectionFactory.getJedis();即可。

Spring Data是Spring中负责数据操作的模块，集成了对多种数据库的操作。其中，Spring Data Redis是Spring Data对Redis的客户端的整合模块，并提供了统一的API及相关拓展（如JDK、JSON与Spring等对象的序列化与反序列化），帮助文档见Spring Data Redis。如果没有特别要求，建议使用Spring Data Redis。

Spring Data Redis将对Redis的各种操作均封装在了工具类RedisTemplate，其中不同数据类型的操作API封装进了不同的类型，具体而言：

API 返回值类型 说明
redisTemplate.opsForValue() ValueOperations 操作String类型数据
redisTemplate.opsForHash() HashOperations 操作Hash类型数据
redisTemplate.opsForList() ListOperations 操作List类型数据
redisTemplate.opsForSet() SetOperations 操作Set类型数据
redisTemplate.opsForZSet() ZSetOperations 操作SortedSet类型数据
redisTemplate 通用命令

类似Jedis的，一个简单的Spring Data Redis使用步骤如下：

  1. 首先引入依赖。例如，如果使用Maven，则在pom.xml中添加依赖项，

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    <dependencies>
        <!-- Redis依赖 -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-redis</artifactId>
        </dependency>
        <!-- 连接池依赖 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
        </dependency>
        ...
    </dependencies>

  2. 在Spring Boot的配置文件application.yaml中设定Spring Data Redis相关配置，

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    spring:
        redis:
            host: 127.0.0.1
            port: 6379
            password: myPassword
            lettuce:
                pool:
                    max-active: 8
                    max-idle: 8
                    min-idle: 0
                    max-wait: 100

  3. 注入RedisTemplate并使用，

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.data.redis.core.RedisTemplate;

    @SpringBootTest
    public class RedisTest {
        
        @Autowired
    	private RedisTemplate redisTemplate;
        
        @Test
        void test() {
            redisTemplate.opsForValue().set("name:1", "天馬咲希");
            Object name = redisTemplate.opsForValue().get("name:1");
            System.out.println(name);
        }
    }

需要注意的是，Spring Data Redis默认使用JDK的JdkSerializationRedisSerializer（封装自ObjectOutputStream()）序列化器，该序列化器接收任意Object对象写入Redis，这导致输入直接被当作Java对象进行序列化，而不是所期望的Redis数据类型字面量。

以示例代码为例，编译并执行后使用redis-cli查看，可以发现并不存在名为“name:1”的键、也不存在“”的值，只有键为“05t06name:1”、值为“05t0C8C”的数据。这是因为"name:1"、"天馬咲希"作为Java String对象首先被JVM序列化、序列化后的结果再被操作写入Redis，而不是被视为Redis String数据类型直接写入。其中，“”标识JDK序列化流，“”为JDK序列化协议版本，“t”标识String对象，“”与“0C”分别表示字符串长度6字节与12字节（UTF-8）。

如果只使用Spring Data Redis操作Redis，那么使用默认序列化器除了会因为前缀字节序列而带来更多的内存占用之外，不会造成更深远的影响。这是因为，在使用Spring Data Redis读取键“name:1”时会调用默认序列化器进行反序列化，因此Spring Data Redis最终可以通过序列化后的“05t06name:1”寻找到“05t0C8C”并返回反序列化后的Java String对象“天馬咲希”。也就是说，在这种情况下redisTemplate.opsForValue().get("name:1")能够得到正确的预期结果"天馬咲希"。

但是，此时若使用redis-cli等途径操作Redis，则会得到意料以外的结果。因为使用默认序列化器的Spring Data Redis在Redis中实际存储的是JVM序列化字节序列，redis-cli“并不知情”。在redis-cli看来，存储的键为“05t06name:1”而非“name:1”、存储的值为“05t0C8C”而非“天馬咲希”。于是，在得到(empty list or set)。

要避免这个问题、确保RedisTemplate对Redis操作在逻辑与行为上的一致性，就需要指定其他序列化器。

例如，可以新建Java类RedisConfig：

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
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.redis.connection.RedisConnectionFactory;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.serializer.GenericJackson2JsonRedisSerializer;
import org.springframework.data.redis.serializer.RedisSerializer;

@Configuration
public class RedisConfig {

    @Bean
    public RedisTemplate<String, Object> redisTemplate(RedisConnectionFactory connectionFactory) {
        RedisTemplate<String, Object> template = new RedisTemplate<>();
        template.setConnectionFactory(connectionFactory);

        // JSON serialization
        GenericJackson2JsonRedisSerializer jsonRedisSerializer = new GenericJackson2JsonRedisSerializer();

        // key serialization (UTF-8)
        template.setKeySerializer(RedisSerializer.string());
        template.setHashKeySerializer(RedisSerializer.string());

        // value serialization (Object to JSON)
        template.setValueSerializer(jsonRedisSerializer);
        template.setHashValueSerializer(jsonRedisSerializer);

        return template;
    }
}

在pom.xml导入相应Jackon依赖（否则尝试序列化实例为JSON时会报告依赖缺失异常），

1
2
3
4
5
6
7
8
9
<dependencies>
    ...
    <!-- Jackson依赖 -->
    <dependency>
    	<groupId>com.fasterxml.jackson.core</groupId>
    	<artifactId>jackson-databind</artifactId>
    </dependency>
    ...
</dependencies>

回到示例代码中，将private RedisTemplate redisTemplate;修改为private RedisTemplate<String, Object> redisTemplate;，编译并执行。这时在redis-cli中输入命令KEYS name:1便可以得到预期的结果1) "name:1"，对应值"天馬咲希"。这一情况下，如果将redisTemplate.opsForValue().set("name:1", "天馬咲希");中的字符串"天馬咲希"替换为合适的Java类的实例，则该实例会被序列化为JSON写入Redis。

例如，新建Java类TenmaSaki：

1
2
3
4
5
6
7
@lombok.Data
@lombok.AllArgsConstructor
@lombok.NoArgsConstructor
public class TenmaSaki {
    private String birthday;
    private int height;
}

同时，将测试代码修改为

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

@SpringBootTest
class RedisTest {

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Test
    void test() {
        TenmaSaki sakiChan = new TenmaSaki("May 9", 160);
        redisTemplate.opsForValue().set("name:1", sakiChan);
    }
}

编译并执行，在redis-cli中输入命令GET name:1得到值"{\"@class\":\"com.hmdp.TenmaSaki\",\"birthday\":\"May 9\",\"height\":160}"。将该值按JSON格式标准化：

1
2
3
4
5
{
  "@class": "com.hmdp.TenmaSaki",
  "birthday": "May 9",
  "height": 160
}

可见，指定的序列化器能够正确地将类的实例序列化为JSON储存。但必须注意，如果输入实例没有Getter方法（比如上述代码去掉注解@lombok.Data），则jsonRedisSerializer不能将其序列化为JSON。强行编译会得到错误：

org.springframework.data.redis.serializer.SerializationException: Could not write JSON: No serializer found for class com.hmdp.TenmaSaki and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS); nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class com.hmdp.TenmaSaki and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS)

此外，这种方式存储的Java对象具有"@class"字段。如果希望仅保留对象内含有的有效数据，则可以指定将键与值均按String序列化。Spring提供了StringRedisTemplate类实现该功能，不需要额外配置RedisTemplate。由于该序列化器不会在序列化时编码用于标识所属类的"@class"字段，所以当反序列化时需要指定类的字面量。

使用mapper序列化工具与StringRedisTemplate实现实例的String序列化如下：

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
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.redis.core.StringRedisTemplate;

@SpringBootTest
public class RedisTest {

    @Autowired
    private StringRedisTemplate stringRedisTemplate;

    private static final ObjectMapper mapper = new ObjectMapper();

    @Test
    void test() throws JsonProcessingException {
        TenmaSaki sakiChan = new TenmaSaki("May 9", 160);
        String jsonSakiChan = mapper.writeValueAsString(sakiChan);
        stringRedisTemplate.opsForValue().set("SAKI", jsonSakiChan);

        String val = stringRedisTemplate.opsForValue().get("SAKI");

        TenmaSaki s = mapper.readValue(val, TenmaSaki.class);
        System.out.println(s);
    }
}

编译并执行，得到结果TenmaSaki(birthday=May 9, height=160)。

redisTemplate.opsForHash()等类似使用。

MyBatis快速上手

MyBatis简易样例

Spring Boot工程开发的三层架构分别为Controller（控制层）、Service（业务层）与DAO（数据持久层），MyBatis就是一款用于操作数据库的优秀DAO框架，免除了几乎所有的JDBC（Java Database Connectivity，一套统一了数据库操作的接口，由数据库提供实现接口的驱动）代码以及设置参数和获取结果集的工作，大大提高了开发效率。

可以使用IntelliJ IDEA在一个Spring Boot工程中通过图形化交互界面引入MyBatis相关依赖并配置MyBatis，也可以在Spring Boot工程的全局配置文件自行配置由Spring Boot整合的MyBatis，以application.properties为例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 数据库连接配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver  # MySQL驱动器类名
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis     # 数据库URL
spring.datasource.username=root                               # 用户
spring.datasource.password=123456                             # 密码

# MyBatis配置
mybatis.mapper-locations=classpath:mapper/**/*.xml            # MySQL的Mapper扫描路径
mybatis.configuration.map-underscore-to-camel-case=true       # 驼峰命名规则自动封装
mybatis.configuration.default-fetch-size=100                  # 指定从数据库一次性获取的记录行数
mybatis.configuration.default-statement-timeout=30            # 超时控制

# 可选项
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOut  # 将日志输出至控制台

同时在Maven项目的核心配置文件pom.xml中为MySQL添加最新MySQL驱动包依赖：

1
2
3
4
5
6
<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <!-- <version>8.0.33</version> 指定版本号 -->
    <scope>runtime</scope>
</dependency>

或添加旧版本MySQL驱动包（8.0.17以前的版本）依赖：

1
2
3
4
5
6
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <!-- <version>5.1.49</version> 指定版本号 -->
    <scope>runtime</scope>
</dependency>

随后即可在MyBatis中可以通过注解或XML编写SQL语句。例如，首先定义一个数据类：

1
2
3
4
5
6
7
8
@Data
public class Member {
    private Integer groupNumber;  // 对应表中的group_number字段
    private String name;          // 对应表中的name字段
    private Short age;            // 对应表中的age字段
    private String gender;        // 对应表中的gender字段
    private String instrument;    // 对应表中的instrument字段
}

注：Lombok常用的注解有@Getter、@Setter、@ToString、@EqualsAndHashCode、@Data、@NoArgsConstructor、@AllArgsConstructor与@Slf4j。其中，@Data是一个复合注解，整合了@Getter、@Setter、@ToString、@EqualsAndHashCode，并默认生成一个 包含所有final或@NonNull字段的构造器。@Slf4j则简化了类中org.slf4j.Logger与org.slf4j.LoggerFactory的日志对象的声明private static Logger log = LoggerFactory.getLogger(thisClass.class);，使得我们能够直接在方法中调用log.info(str)、log.debug(str)等方法记录日志。

用Mapper注解定义持久层接口：

1
2
3
4
5
@Mapper
public interface MemberMapper {
    @Select("SELECT * FROM member")
    public List<Member> showMeMembers();
}

不需要定义实现类，框架将识别接口生成接口的代理对象，并将代理对象作为bean控制反转给IoC容器进行管理，因此直接调用showMeMembers方法即可：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@SpringBootTest
class test {

    @Autowired
    private MemberMapper memberMapper;

    @Test
    public void testShowMeMembers() {
        List<Member> memberList = memberMapper.showMeMembers();
        memberList.stream().forEach(
            member -> { System.out.println(member); }
        );
    }
}

为实现资源重用、提升系统响应速度并避免数据库连接遗漏，需要使用数据库连接池。数据库连接池是一个容器，负责分配、管理数据库连接。Java提供了名为DataSource的数据库连接池标准接口，目前已有若干成熟的第三方JDBC组件实现了该接口，例如C3P0、DBCP、Druid与Hikari等，其中Hikari是Spring Boot默认的JDBC数据库连接池组件。如果需要切换数据库连接池，以Druid为例，需要按Druid文档所述修改Maven项目的核心配置文件pom.xml以配置依赖，同时将Spring Boot全局配置文件（例如application.properties）内容中的datasource字符串全部替换为datasource.druid。

MyBatis基础用法手册

自动封装机制

默认情况下，当方法返回类型为实体类时，如果实体类属性名与数据库表查询返回的字段名一致，则MyBatis将通过反射机制进行自动封装，否则自动封装机制将不能正常工作、不一致的属性值将被赋为默认值null，例如 

1
2
3
4
5
@Mapper
public interface MemberMapper {
    @Select("SELECT * FROM member WHERE name = #{name}")
    public Member getMemberByName(String name);
}

调用该方法时，返回的对象的属性除groupNumber外值均为从数据库中查询的值，但groupNumber值为null，这是由于实体类属性名groupNumber与数据库表查询返回的字段名group_number不一致进而使MyBatis自动封装机制失效造成的。

要解决这个问题，可以考虑如下几种方法：

  1. 为数据库表字段名起别名，使别名与实体类属性名相同，自动封装机制将能够正常工作。

    1
    2
    3
    4
    5
    6
    @Mapper
    public interface MemberMapper {
        @Select("SELECT group_number groupNumber, name, age, gender, instrument " +
                "FROM member WHERE name = #{name}")
        public Member getMemberByName(String name);
    }

  2. 通过@Results注解与@Result注解进行映射。

    1
    2
    3
    4
    5
    6
    7
    8
    @Mapper
    public interface MemberMapper {
        @Select("SELECT * FROM member WHERE name = #{name}")
        @Results({
            @Result(column="group_number", proper="groupNumber")  // , @Result(...) ...
        })
        public Member getMemberByName(String name);
    }

  3. 在Spring Boot工程的全局配置文件中配置mybatis.configuration.map-underscore-to-camel-case=true以启用驼峰命名规则自动封装，MyBatis将把group_number自动映射到相应的小驼峰命名形式groupNumber。

对于增删改操作（INSERT/UPDATE/DELETE），@Mapper接口默认以int类型返回受影响记录行数，可以通过将方法声明为void以忽略返回值。

对于查询操作（SELECT），@Mapper接口需在映射文件中显式声明resultType或resultMap。返回值可为基本类型、自定义JavaBean（属性名与字段名应一致，或能够通过驼峰规则自动映射，如group_number → groupNumber）、集合或Map类型。

参数占位符

通过注解编写SQL语句时，只需要根据DML与DQL操作种类在方法前选择@Insert、@Delete、@Select、或@Update并以字符串参数的形式编写完整的SQL语句即可，但不需要标识语句结束的分号。

MyBatis采用两种参数占位符：#{arg}与${arg}。

  1. #{arg}的预编译特性

    该占位符通过JDBC的PreparedStatement实现参数绑定，所有使用#{arg}的SQL语句均采用预编译机制。此方式通过参数化查询（Parameterized Query）将输入值与SQL逻辑分离，既能提升执行效率（通过复用预编译语句），又能有效防御SQL注入攻击。

    由于预编译特性，#{arg}不能在字符串内使用。例如，涉及通配符的模糊查询不能写为LIKE '%#{咲}%'，这时只能写为LIKE '%${咲}%'或LIKE CONCAT('%', #{咲}, '%')。由于后者支持预编译而前者不支持（仅作字符串拼接），出于性能与安全考虑推荐优先考虑后者。

  2. ${arg}的字符串替换机制

    该占位符直接将参数值以文本形式拼接至SQL语句中，属于动态SQL解析阶段的字符串替换操作。由于未进行安全转义，存在潜在SQL注入风险，其典型应用场景为动态生成表名、列名或SQL函数调用等元数据操作。

参数占位符传递规则：

  1. 单参数场景

    当方法仅接收单个参数时（无论基本类型或对象类型），占位符名称可自由定义，例如#{name}或#{aaa}均有效。但若参数为POJO或Map类型，占位符名称需严格匹配对象属性名或Map键名。

  2. 多参数场景

    在Spring Boot的2.0以上的版本中使用内置的MyBatis时，只需要让参数占位符内的参数名称与方法形参中的名称一致，或使用JavaBean作为返回值并让参数占位符内的参数名称与实体类属性名称相同，程序就能自动将参数占位符与方法形参或JavaBean属性一一对应。

    但若所使用的Spring Boot是2.0以前的版本，或者需要独立于Spring Boot单独使用MyBatis，由于缺失了Spring Boo 2.0+提供的额外编译组件，@Mapper接口的方法在被编译生成代理对象后将丢失形参名，这时必须通过使用@Param注解显式指定参数名以确保参数占位符与形参一一对应。如果一定要使用自定义而不与参数占位符名称相匹配的形参名，也可以通过@Param注解使参数占位符名与形参名相对应。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    @Mapper
    public interface MemberMapper {
        @Select("SELECT * FROM member WHERE group_number = #{n}")
        public Member getNumberByNumber(Integer n);

        @Select("SELECT group_number, name, age, gender, instrument FROM member " +
                "WHERE group_number = #{groupNumber} AND name = #{name}")
        public Member getMember(@Param("groupNumber") Integer memberNumber, @Param("name") String memberName);

        @Insert("INSERT INTO member(group_number, name, age, gender, instrument) " +
                "VALUES(#{groupNumber}, #{name}, #{age}, #{gender}, #{instrument})")
        public void addMember(Member m);
    }

    对于集合等拥有泛型的对象类型，由于Java 8以前的编译器在编译期间将完全擦除泛型信息，因而MyBatis必须通过@Param注解显式指定参数名才能正确绑定集合参数，否则会因反射无法获取泛型类型而出现错误。Java 8引入了对方法参数名称的保留支持、能够将参数名称与泛型写入字节码中（编译参数-parameters），同时java.lang.reflect.Parameter类新增了用以通过反射获取参数泛型类型信息的getParameterizedType()方法。MyBatis在3.4.5版本开始适配Java 8的这一新特性，支持自动解析集合参数名。如果使用的是不支持自动解析集合参数名的旧版本，务必使用@Param注解显式指定集合参数名。

@Options注解

@Options注解能够帮助配置SQL执行细节，例如主键回填、缓存控制等。@Options注解的标准写法应位于方法之前、SQL语句注解以后。以下为@Options注解的若干核心属性。

  1. 插入操作（INSERT）的主键回填

    • useGeneratedKeys=true：启用数据库自增主键功能，对MySQL而言对应AUTO_INCREMENT

    • keyProperty="id"：将生成的主键值注入到实体类的id字段，绑定Java对象字段主键（方法形参应为Java对象）

    • keyColumn="field_name"：指定数据库主键列名，将Java对象字段主键映射到数据库列名主键（当数据库主键列名与实体类keyProperty字段名完全一致，或开启MyBatis驼峰命名自动转换且实体类字段名遵循小驼峰规则时，可以省略keyColumn，MyBatis将自动映射）

  2. 查询操作（SELECT）的缓存控制

    • useCache=false：禁用当前查询的二级缓存

    • flushCache=Options.FlushCachePolicy.TRUE：执行后强制刷新本地缓存

  3. 所有操作的执行控制

    • timeout=10：设置SQL执行超时时间，单位为秒
    • statementType=StatementType.PREPARED：此为默认参数，表示使用预编译语句提升批量插入性能；即使设置为关闭，使用#{arg}占位符时MyBatis也必定会触发预编译行为
    • fetchSize=500：设置批量操作时的批处理大小

XML映射

除了通过注解的方式外，MyBatis还可以通过XML映射文件操作数据库。通过XML映射，可以在@Mapper接口方法前略去@Insert、@Delete、@Select、与@Update注解，而将相应的SQL语句编写在XML映射文件中。

XML映射文件基本规范：

  1. XML映射文件的文件名应与@Mapper接口名称相同，而且二者应在相同的包下（并不要求相同的路径）。
  2. XML映射文件的<mapper>标签的属性namespace应当与@Mapper接口的全限定名相同。
  3. XML映射文件的SQL语句标签的id属性应与@Mapper接口内的方法名相同，resultType应与方法返回类型一致。

至于XML映射文件内的具体内容——只需要去官网复制一份样例然后照葫芦画瓢、按照需求编写自己需要的SQL语句即可。例如，项目文件的结构为

1
2
3
4
5
6
7
8
9
10
11
12
module
└─ src
   └─ main
      ├─ java
      │  └─ com.pjsk
      │     └─ mapper
      │        └─ MemberMapper (Java interface)
      │
      └─ resources
         └─ com.pjsk
            └─ mapper
               └─ MemberMapper.xml (XML file)

完整的XML映射为

1
2
3
4
5
6
7
8
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.pjsk.mapper.MemberMapper">
    <select id="getMemberByName" resultType="com.pjsk.pojo.Member">
        SELECT * FROM member WHERE name = #{name}
    </select>
</mapper>

如此一来，@Mapper接口就可以被简化为

1
2
3
4
@Mapper
public interface MemberMapper {
    Member getMemberByName(String name);
}

IntelliJ IDEA中有一款名为MyBatisX的插件，能够帮助我们快速定位@Mapper接口方法对应的XML映射。

PageHelper分页插件

PageHelper是一个由第三方开发的MyBatis分页插件，能够帮助我们快速实现分页查询、避免编写繁琐的分页查询逻辑与SQL语句。在使用该插件前，需要先引入pagehelper-spring-boot-starter依赖。

使用该插件进行分页查询，只需要在Service实现类的方法中链式调用：

1
2
PageInfo<Member> pageInfo = PageHelper.startPage(pageNum, pageSize)
                                      .doSelectPageInfo(() -> memberMapper.list());

然后将pageInfo返回给同前端交互的Controller即可。其中，PageInfo是PageHelper所提供的类，包含pageNum、pageSize、total（总记录数）、list（当前页数据集合）与pages（总页数）等若干前端分页查询所需的常用属性；此外，memberMapper.list()对应Mapper接口方法中的全表查询@Select("SELECT * FROM member")，由DAO层实现。

通过动态SQL，可以将list方法重载。这样的话，如果传入list的参数不同，则对应查询的WHERE条件不同，于是就可以方便地实现条件分页查询。

动态SQL

动态SQL能够实现更复杂的逻辑，所以通常考虑XML映射而非注解。学习MyBatis动态SQL，就是学习MyBatis XML映射中的一个个标签。

<if>、<where>与<set>

<if>用于动态地判断条件是否成立，使用test属性进行Java表达式条件判断。如果结果为真，则拼接SQL语句。

<where>能够进行动态的条件查询，常嵌套多个<if>使用。如果<where>标签内的<if>标签条件判断均为假，则不会进行任何SQL语句拼接；如果<where>标签内的<if>标签条件判断至少有一个为真，则去除首个有效条件句首的AND或OR（如果存在的话），然后插入相应WHERE子句。

1
2
3
4
5
6
7
8
9
<mapper namespace="com.pjsk.mapper.MemberMapper">
    <select id="getMember" resultType="com.pjsk.pojo.Member">
        SELECT * FROM member
        <where>
            <if test="memberNumber != null">AND member_number = #{memberNumber}</if>
            <if test="name != null">AND name = #{name}</if>
        </where>
    </select>
</mapper>
1
2
3
4
@Mapper
public interface MemberMapper {
    Member getMember(Integer memberNumber, String name);
}

<set>能够动态地在行首插入SET关键字；在UPDATE语句中，<set>还能动态地去除<if>标签内SQL语句末尾多余的逗号。

1
2
3
4
5
6
7
8
9
10
11
12
<mapper namespace="com.pjsk.mapper.MemberMapper">
    <update id="setMember">
        UPDATE member
        <set>
            <if test="name != null">name = #{name},</if>
            <if test="age != null">age = #{age},</if>
            <if test="gender != null">gender = #{gender},</if>
            <if test="instrument != null">instrument = #{instrument},</if>
        </set>
        WHERE group_number = #{groupNumber}
    </update>
</mapper>
1
2
3
4
@Mapper
public interface MemberMapper {
    void setMember(Integer groupNumber, String name, Integer age, String gender, String instrument);
}

<foreach>

<foreach>用于遍历批量操作，有以下几个常用的属性：

  1. collection：必选属性，指定需要遍历的集合参数名
  2. item：必选属性，指定遍历元素的别名
  3. separator：可选属性，指定元素间的分隔符
  4. open：可选属性，遍历开始前拼接的字符
  5. close：可选属性，遍历开始后拼接的字符
  6. index：可选属性，索引或键的别名
1
2
3
4
5
6
<insert id="batchInsertMember">
    INSERT INTO mamber VALUES
    <foreach collection="arr" item="m" open="(" separator="," close=")">
        #{m.groupNumber}, #{m.name}, #{m.age}, #{m.gender}, #{m.instrument}
    </foreach>
</insert>
1
2
3
4
@Mapper
public interface MemberMapper {
    void batchInsertMember(List<Member> arr);
}

<sql>与<include>

<sql>与<include>是为了提升代码复用性、减少重复代码块而生的，进而提升代码可维护性和安全性。

<sql>标签包裹公共SQL片段，通过id属性为SQL片段命名；自闭合标签<include>通过refid属性引用公共SQL片段。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<sql id="baseColumnList">
    group_number,
    name,
    age,
    gender,
    instrument
</sql>

<select id="getMember" resultType="com.pjsk.pojo.Member">SELECT <include refid="baseColumnList" />
    FROM member
    <where>
        <if test="groupNumber != null">AND group_number = #{groupNumber}</if>
        <if test="name != null">AND name = #{name}</if>
    </where>
</select>
1
2
3
4
@Mapper
public interface MemberMapper {
    Member getMember(Integer groupNumber, String name);
}