数据库概述

为什么使用数据库

⭐⭐ 使用数据库的主要原因包括但不限于以下几点：

  • 数据管理：数据库提供了一种系统化的方法来存储、管理和处理大量信息。它允许用户通过简单的查询语言（如SQL）来访问和操作数据。
  • 数据完整性：数据库设计有机制确保数据的完整性和一致性。例如，通过设置主键、外键等约束条件，可以避免数据冗余和不一致的问题。
  • 数据安全性：数据库管理系统（DBMS）提供了多种安全措施来保护数据免受未授权访问、恶意攻击或意外损坏。这些措施包括用户身份验证、权限控制、加密技术等。
  • 并发控制：在多用户环境中，数据库能够有效地管理多个用户同时对同一数据进行读写操作，确保事务的一致性与隔离性。
  • 数据备份与恢复：现代数据库系统支持定期自动备份功能，并能够在发生故障后快速恢复数据，保证业务连续性。
  • 性能优化：数据库系统通过索引、缓存等技术提高数据检索速度；并且可以通过调整参数、优化查询等方式进一步提升性能。
  • 易于扩展：随着企业的发展，数据量可能会迅速增长。一个好的数据库架构应该能够轻松地扩展以适应不断增长的需求。
  • 标准化：采用数据库可以遵循一定的标准和规范，比如SQL标准，这有助于不同系统之间的互操作性和数据共享。

数据库的相关概念

术语 说明
数据库
(Database) 		是一种按照特定方式组织、存储和管理数据的集合。它可以是关系型数据库、非关系型数据库（NoSQL）、对象数据库等。
数据库管理系统
(DBMS)
(Database Management System) 		是用于创建、维护和管理数据库的软件系统。常见的DBMS包括MySQL、Oracle、SQL Server、MongoDB等。
表
(Table) 		在关系型数据库中，数据是以二维表格的形式存储的，每个表由行（记录）和列（字段）组成。
记录
(Record/Row) 		表中的每一行代表一条完整的数据记录，包含关于某个实体的具体信息。
字段
(Field/Column) 		表中的每一列代表一个属性或特性，用来描述记录的某个方面。
主键
(Primary Key) 		用于唯一标识表中每条记录的一个或一组字段。主键必须具有唯一性和非空性。
外键
(Foreign Key) 		是一个表中引用另一个表主键的字段，用于建立两个表之间的关联。
索引
(Index) 		为了加快数据检索速度而创建的数据结构。索引可以基于一个或多个字段，类似于书籍的目录。
视图
(View) 		是一个虚拟表，其内容由查询定义。视图可以简化复杂的查询逻辑，也可以用于实现数据抽象和安全控制。
事务
(Transaction) 		是一系列作为单个逻辑单元执行的操作，具有ACID特性（原子性、一致性、隔离性、持久性），确保数据的一致性和可靠性。
结构化查询语言
(SQL)
(Structured Query Language) 		是一种用于管理和处理关系型数据库的标准编程语言。SQL语句可以用来查询、插入、更新和删除数据。
模式
(Schema) 		定义了数据库的结构，包括表、字段、数据类型、约束等。模式描述了如何组织数据以及它们之间的关系。
数据字典
(Data Dictionary) 		是关于数据库中所有对象（如表、字段、索引等）的信息的集合，通常由DBMS自动维护。
数据仓库
(Data Warehouse) 		是一种特殊类型的数据库，专门用于存储历史数据和支持决策分析。数据仓库通常包含大量的汇总数据。
ETL
(Extract, Transform, Load) 		是将数据从源系统提取出来，经过转换处理后再加载到目标系统的过程，常用于构建数据仓库。

⭐⭐ 数据库与数据库管理系统的关系

  1. 定义
    • 数据库 (Database)：是一个按特定方式组织起来的数据集合。这些数据可以是结构化的（如关系型数据库中的表格数据），也可以是非结构化的（如文档数据库中的JSON文档）。数据库的主是存储和检索数据，以便应用程序能够高效地使用这些数据。
    • 数据库管理系统 (Database Management System, DBMS)：是一种软件应用，用于创建、维护和管理数据库。DBMS 提供了一组工具和服务，使得用户能够方便地定义数据结构、存储数据、检索数据、更新数据以及保护数据。它还负责确保数据的一致性、完整性和安全性。
  2. 功能
    • 数据库：主要关注数据的存储形式和内容。它本身并不直接提供数据的增删改查功能，而是依赖于DBMS来实现这些操作。
    • DBMS：提供了一系列的功能和服务，包括但不限于：
    • 数据定义：允许用户定义数据结构，如创建表、定义字段等。
    • 数据操纵：提供数据查询、插入、更新和删除的能力。
    • 数据控制：管理用户权限，确保数据的安全性和完整性。
    • 数据维护：支持备份、恢复、性能优化等功能。
    • 并发控制：允许多个用户同时访问数据库，同时保持数据的一致性和准确性。
    • 故障恢复：在系统出现故障时，能够恢复数据到最近的一致状态。
  3. 使用场景
    • 数据库：对于最终用户来说，他们可能不会直接与数据库交互，而是通过应用程序或前端界面来间接使用数据库中的数据。
    • DBMS：开发人员、数据库管理员和IT专业人员会直接使用DBMS提供的工具和接口来进行数据库的设计、部署、管理和维护工作。
  4. 示例
    • 数据库示例：一个电子商务网站的用户信息表、订单表、商品表等都是具体的数据库实例。
    • DBMS示例：MySQL、PostgreSQL、Oracle、SQL Server、MongoDB等都是流行的DBMS产品，它们提供了创建和管理各种类型数据库的能力。

MySQL 的发展历程

  1. 早期发展 (1979-1995)
    • 1979年：Michael “Monty” Widenius 为 TcX 公司设计了一个名为 Unireg 的报表工具，这是 MySQL 最初的雏形。
    • 1995年：Monty 重新设计了整个系统，1995年5月23日，MySQL 的第一个内部版本发布。
  2. 正式发布与初期发展 (1996-1999)
    • 1996年：MySQL 对外发布了官方正式发行版 3.11.1。
    • 1996-1998年：MySQL 加入了许多新特性，并被移植到不同的平台，到1998年，MySQL 已经能够运行在 Solaris、FreeBSD、Linux、Windows 95 和 Windows NT 等10多种操作系统之上。
    • 1999年冬季：MySQL 3.23 发布，首次包含了事务型存储引擎 BDB。
  3. 商业化与国际化 (2000-2004)
    • 2000年：MySQL 将许可模式改为 GPL 许可，商业用户也无需再购买许可证，这为 MySQL 的迅速流行打下了基础。同年，Heikki 开始接触 MySQL AB，尝试将 InnoDB 存储引擎整合到 MySQL 中。
    • 2001年：MySQL 4.0 Alpha 版本发布，正式结合 InnoDB 存储引擎。
    • 2004年10月：MySQL 4.1 版本发布，首次支持子查询、Unicode 和预编译 SQL 等功能。
  4. 功能完善与企业级应用 (2005-2009)
    • 2005年10月：MySQL 5.0 版本发布，加入了游标、存储过程、触发器、视图、查询优化以及分布式事务等特性，实现了“正常数据库管理系统”应当拥有的功能。
    • 2008年初：Sun Microsystems 以10亿美元收购了 MySQL。
    • 2009年4月：Oracle 以74亿美元收购 Sun 公司，MySQL 转入 Oracle 门下。
  5. Oracle 时代的 MySQL (2010年至今)
    • 2010年12月：MySQL 5.5 发布，主要新特性包括半同步复制及对 SIGNAL/RESIGNAL 异常处理功能的支持，InnoDB 存储引擎成为默认存储引擎。
    • 2011年以后：MySQL 持续发布新版本，包括 5.6、5.7 和 8.0 等，不断增加新的功能和改进性能。
    • 2019年12月：在 db-engines 数据库流行度排行中，MySQL 仅次于 Oracle 居于次席，在所有开源数据库产品中排名第一。

MySQL 逻辑架构

  • 连接层
    • 连接处理：处理客户端的连接请求，包括身份验证等。
    • 线程管理：为每个连接创建一个单独的线程来处理来自客户端的请求。
    • 缓存：包括查询缓存（在较新的版本中已移除）、结果集缓存等。
  • 服务层
    • SQL 接口：解析 SQL 语句并生成相应的执行计划。
    • 存储过程：支持存储过程和函数的定义与调用。
    • 触发器：允许在特定事件发生时自动执行预定义的 SQL 语句。
    • 视图：提供虚拟表，基于一个或多个实际表中的数据。
    • 事务管理：确保事务的 ACID 属性（原子性、一致性、隔离性、持久性）。
    • 权限系统：控制用户对数据库资源的访问权限。
  • 引擎层：MySQL 支持多种存储引擎，每种引擎都有自己的特点，适合不同的应用场景。
    • InnoDB：默认存储引擎，支持事务处理、行级锁定和外键约束。
    • MyISAM：不支持事务，但读操作性能较好，适合于只读或以读为主的场景。
    • Memory：将所有数据存储在内存中，速度非常快，但在服务器重启后数据会丢失。
    • Archive：用于存储大量的归档数据，压缩比高，但不适合频繁更新的数据。
  • 存储层
    • 物理文件：数据最终以文件的形式存储在磁盘上。不同存储引擎有不同的文件格式和组织方式。
    • 索引：为了加快数据检索速度，MySQL 使用各种类型的索引来优化查询性能。
    • 日志：包括错误日志、二进制日志（用于复制和恢复）、慢查询日志等，帮助监控和故障排除。
  • 复制和集群
    • 主从复制：允许将一个 MySQL 服务器设置为主服务器，其他服务器作为从服务器，从主服务器复制数据。
    • Galera Cluster：一种多主复制解决方案，允许多个节点同时接受写入操作。
    • InnoDB Cluster：提供高可用性和可扩展性的解决方案，使用 Group Replication 技术实现多主复制。

SQL 的主要特点

  • 数据定义 (Data Definition Language, DDL)：
    • 用于定义和管理数据库结构。
    • 常用的 DDL 语句包括：
      • CREATE：创建数据库或表。
      • ALTER：修改现有的数据库对象。
      • DROP：删除数据库或表。
      • TRUNCATE：清空表中的所有数据。
  • 数据操作 (Data Manipulation Language, DML)：
    • 用于操作数据库中的数据。
    • 常用的 DML 语句包括：
      • INSERT：插入数据。
      • UPDATE：更新数据。
      • DELETE：删除数据。
  • 数据查询 (Data Query Language, DQL)：
    • 主要用于查询数据。
    • 最常用的 DQL 语句是 SELECT。
  • 数据控制 (Data Control Language, DCL)：
    • 用于管理数据库的权限和事务。
    • 常用的 DCL 语句包括：
      • GRANT：授予用户权限。
      • REVOKE：撤销用户权限。
      • COMMIT：提交事务。
      • ROLLBACK：回滚事务。
  • 事务控制 (Transaction Control Language, TCL)：
    • 用于管理事务的开始、提交和回滚。
    • 常用的 TCL 语句包括：
      • BEGIN TRANSACTION 或 START TRANSACTION：开始一个事务。
      • COMMIT：提交事务。
      • ROLLBACK：回滚事务。

SQL 执行流程

  • 客户端发送请求
    • 连接建立：客户端通过网络连接到 MySQL 服务器。如果连接成功，MySQL 会为该连接分配一个线程。
    • 请求发送：客户端发送 SQL 语句到 MySQL 服务器。
  • 服务器接收请求
    • 连接池管理：MySQL 服务器使用连接池来管理客户端连接，为每个连接分配一个线程。
    • 身份验证：服务器验证客户端的身份信息（用户名、密码等），确保其具有执行 SQL 语句的权限。
  • 查询缓存（已弃用）
    • 查询缓存检查：MySQL 会检查查询缓存（Query Cache），看是否有相同的查询结果已经存在。如果有，直接返回缓存的结果，跳过后续步骤。
    • 注意：从 MySQL 8.0 开始，查询缓存已被移除，不再支持。
  • 解析器
    • 语法分析：解析器将 SQL 语句分解成解析树（Parse Tree），检查语法是否正确。
    • 语义分析：解析器进一步检查解析树的语义，确保所有的表和列都存在，并且用户具有相应的权限。
  • 优化器
    • 生成执行计划：优化器根据解析树生成多个可能的执行计划，并选择最优的一个。
    • 考虑因素：优化器会考虑索引的存在、表的大小、统计信息等因素，选择最高效的执行计划。
  • 执行器
    • 执行计划：执行器按照优化器生成的执行计划执行 SQL 语句。
    • 存储引擎接口：执行器通过存储引擎接口与具体的存储引擎（如 InnoDB、MyISAM 等）进行交互，读取或写入数据。
    • 事务管理：如果 SQL 语句涉及事务，执行器会管理事务的开始、提交或回滚。
  • 结果返回
    • 结果生成：执行器将查询结果或操作结果生成为结果集。
    • 结果返回：结果集通过网络返回给客户端。
  • 日志记录
    • 二进制日志：对于修改数据的 SQL 语句（如 INSERT、UPDATE、DELETE），MySQL 会将这些操作记录到二进制日志（Binary Log）中，用于复制和恢复。
    • 错误日志：MySQL 会记录执行过程中遇到的任何错误信息到错误日志中。
    • 慢查询日志：如果 SQL 语句执行时间超过设定的阈值，MySQL 会将其记录到慢查询日志中，帮助优化查询性能。
  • 释放资源
    • 清理：执行完 SQL 语句后，MySQL 会释放相关的资源，如内存、锁等。
    • 连接关闭：如果客户端显式关闭连接，MySQL 会释放该连接占用的资源。

安装、配置

数据库安装

Windows 安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 1. 下载 MySQL Installer：
#       - 访问 MySQL 官方网站：https://dev.mysql.com/downloads/installer 下载 MySQL Installer。
#       - 选择适合你系统的版本（32位或64位）。
# 2. 安装 MySQL：
#       - 运行下载的安装程序。
#       - 选择“Custom”安装类型，这样你可以自定义安装组件。
#       - 选择要安装的产品，至少需要选择 MySQL Server 和 MySQL Workbench。
#       - 按照提示完成安装过程。
# 3. 配置 MySQL：
#       - 安装完成后，MySQL Installer 会提示你配置 MySQL Server。
#       - 选择配置类型（如 Developer Default 或 Server Computer）。
#       - 设置 root 用户的密码。
#       - 完成配置后，启动 MySQL 服务。
# 4. 验证安装：
#       - 打开命令提示符，输入 mysql -u root -p，然后输入你设置的密码，如果成功连接到 MySQL 服务器，说明安装成功

# 启动服务
net start mysql

# 停止服务
net stop mysql

# 登录用户
# -u：用户名
# -P：端口号
# -h：服务地址
# -p：密码
mysql -u root -p
mysql -u root -P 3306 -p
mysql -u root -P 3306 -h localhost -p

Linux 安装

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
# 通过下载并安装 MySQL 的 Yum 仓库文件来完成：
wget https://dev.mysql.com/get/mysql80-community-release-el7.noarch.rpm

# 添加仓库后，可以使用以下命令来安装 MySQL 服务器
yum -y install mysql80-community-release-el7.rpm

# 启动服务、查看服务状态
systemctl start mysqld
systemctl status mysqld

# 获取密码
grep "password" /var/log/mysqld.log

# 登录并修改密码
set global validate_password.policy=0;
set global validate_password.length=4;
ALTER USER 'root'@'localhost' IDENTIFIED BY '1234';

# 授权远程用户连接
use mysql;
update user set host = '%' where user = 'root';
flush privileges;

# 设置mysql开机自启动
cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysql # 复制并重命名
chmod +x /etc/init.d/mysql  # 赋予权限
chkconfig --add mysql       # 添加为服务
chkconfig --list            # 查看服务列表

数据库配置

开放端口号

1
firewall-cmd --permanent --add-port=3306/tcp

关闭防火墙

1
2
3
4
5
6
7
8
# 临时关闭
sudo systemctl stop firewalld

# 永久关闭
sudo systemctl disable firewalld

# 检查状态
sudo systemctl status firewalld

修改登录密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 进入数据库
USE mysql;

-- 只修改密码
ALTER USER 'root' IDENTIFIED BY 'new_password';

-- 默认密码加密方式和本地连接
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

-- 任何IP都可以连接
-- mysql_native_password：8.0 之前的加密方式
-- caching_sha2_password：8.0 开始的加密方式
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'abc123';

-- 本地连接
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123';

-- 设置IP连接
UPDATE USER SET host = '%' WHERE user = 'root';

-- 刷新
FLUSH PRIVILEGES;

字符集设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 查看字符集
SHOW VARIABLES LIKE 'character_%';
SHOW VARIABLES LIKE 'collation_%';
SHOW CREATE DATABASE test;

-- 修改 mysql 默认的字符集
vim /etc/my.cnf
-- 添加配置
character_set_server=utf8
-- 重启服务
sudo systemctl restart mysqld

-- 修改已有数据库的字符集
ALTER DATABASE test1 DEFAULT CHARACTER SET 'utf8';

-- 修改已有表的字符集
ALTER TABLE emp CONVERT TO CHARACTER SET 'utf8';

-- 查看GBK字符集的比较规则
SHOW COLLATION LIKE 'gbk%';

-- 查看UTF-8字符集的比较规则
SHOW COLLATION LIKE 'utf8%';

-- 设置客户端与服务器之间的通信字符集
SET NAMES utf8;
-- 验证
SHOW VARIABLES LIKE 'character_set_%';

-- 注：utf8 是 utf8mb3的缩写，是1-3字节。utf8mb4是1-4字节，可以存emoji表情。

大小写规则设置

1
2
3
4
5
6
7
8
9
10
11
# 数据库名、表名、表的别名、变量名是严格区分大小写的;
# 关键字、函数名称在 SQL 中不区分大小写;
# 列名(或字段名)与列的别名(或字段别名)在所有的情况下均是忽略大小写;
SHOW VARIABLES LIKE '%lower_case_table_names%';

# 大小写规则设置
vim /etc/my.cnf
# 添加配置:大小写不敏感
lower_case_table_names=1
# 重启服务
sudo systemctl restart mysqld

用户和权限

MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限；普通用户只拥有被授予的各种权限。

用户管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查询用户列表
USE mysql;
SELECT host, user FROM user;

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';

-- 修改用户名称
UPDATE user SET user = 'wangwu' WHERE user = 'lisi' AND host = '%';

-- 修改用户密码
ALTER USER 'lisi'@'localhost' IDENTIFIED BY 'new_password123';

-- 密码设置为已过期。用户在下次登录时将被强制要求更改密码。
ALTER USER 'newuser'@'localhost' PASSWORD EXPIRE;

-- 将用户的密码设置为永不过期
ALTER USER 'newuser'@'localhost' PASSWORD EXPIRE NEVER;

-- 将用户的密码设置为在 90 天后过期
ALTER USER 'newuser'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

-- 删除用户
DROP USER 'newuser'@'localhost';

权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 查看用户权限
SHOW GRANTS FOR 'newuser'@'localhost';

-- 查看当前用户可以执行的操作列表
SHOW GRANTS;
-- 或
SHOW PRIVILEGES;

-- 给予 newuser 对所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';

-- 给特定数据库或表的权限。
-- 注：多次设置，是叠加，不是覆盖。
GRANT SELECT, INSERT ON mydatabase.* TO 'newuser'@'localhost';

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 'newuser'@'localhost';

-- 撤销特定权限
REVOKE INSERT ON mydatabase.* FROM 'newuser'@'localhost';

角色管理

角色管理是一种简化权限管理的方式。角色是一组预定义的权限集合，可以分配给用户，从而简化了权限的管理和维护。通过角色管理，管理员可以更容易地控制多个用户的权限，而无需逐个用户进行设置。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 创建角色
CREATE ROLE 'developer';

-- 查看角色列表
SELECT host, user FROM user;

-- 激活角色
SET ROLE 'developer';

-- 查看已激活的角色列表
SELECT CURRENT_ROLE();

-- 删除角色
DROP ROLE 'developer';

-- 授予角色权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'developer';

-- 给用户分配角色
GRANT 'developer' TO 'newuser'@'localhost';

-- 查看用户的角色
SHOW GRANTS FOR 'newuser'@'localhost';

-- 撤销分配给用户的角色
REVOKE 'developer' FROM 'newuser'@'localhost';

-- 角色管理是MySQL 8.0 之后的功能，MySQL 5.7 或更早版本，你需要使用其他方法来模拟角色管理。
-- 创建“角色”用户
CREATE USER 'role_manage'@'localhost';
-- 授予“角色”用户权限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'role_manage'@'localhost';
-- 将“角色”用户权限复制给其他用户
GRANT PROXY ON 'role_manage'@'localhost' TO 'newuser'@'localhost';
-- 撤销权限
REVOKE PROXY ON 'role_manage'@'localhost' FROM 'newuser'@'localhost';

数据导入/导出

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 导出数据
# 导出整个数据库
mysqldump -u username -p database_name > backup_file.sql

# 导出特定表
mysqldump -u username -p database_name table_name > backup_file.sql

# 导出数据而不导出表结构
mysqldump -u username -p --no-create-info database_name > backup_file.sql

# 导出表结构而不导出数据
mysqldump -u username -p --no-data database_name > backup_file.sql

# 导入数据
# 导入整个数据库
mysql -u username -p database_name < backup_file.sql

# 创建数据库并导入数据
mysql -u username -p -e "CREATE DATABASE database_name;"
mysql -u username -p database_name < backup_file.sql

增删改查

数据类型

数值类型

  • TINYINT：小整数，存储大小为1个字节。有符号范围是从 -128 到 127，无符号范围是从 0 到 255。
  • SMALLINT：中等大小的整数，存储大小为2个字节。有符号范围是从 -32768 到 32767，无符号范围是从 0 到 65535。
  • MEDIUMINT：中等大小的整数，存储大小为3个字节。有符号范围是从 -8388608 到 8388607，无符号范围是从 0 到 16777215。
  • INT：整数类型，存储大小为4个字节。有符号范围是从 -2147483648 到 2147483647，无符号范围是从 0 到 4294967295。
  • BIGINT：大整数，存储大小为8个字节。有符号范围是从 -9223372036854775808 到 9223372036854775807，无符号范围是从 0 到 18446744073709551615。
  • FLOAT(M,D)：单精度浮点数，存储大小通常为4个字节。M是总的位数，D是小数点后的位数。
  • DOUBLE(M,D)：双精度浮点数，存储大小通常为8个字节。M和D的意义同上。
  • DECIMAL(M,D) / NUMERIC(M,D)：精确数值类型，适合用于货币等需要精确计算的场合。M是总的位数，D是小数点后的位数。

时间类型

  • YEAR：年份，可以存储2位或4位的年份值。默认情况下，YEAR(4) 表示4位年份。
  • DATE：日期格式为 ‘YYYY-MM-DD’。
  • TIME：时间或持续时间，格式为 ‘HH:MM:SS’ 或者 ‘HHMMSS’。
  • DATETIME：日期和时间的组合，格式为 ‘YYYY-MM-DD HH:MM:SS’。
  • TIMESTAMP：时间戳，表示从1970年1月1日00:00:00 UTC到现在的秒数。范围从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC。

字符串类型

  • CHAR(N)：固定长度的字符串，N 是字符的最大数量（最多255个）。
  • VARCHAR(N)：可变长度的字符串，N 是字符的最大数量（最多65535个）。
  • TINYTEXT：最大长度为255个字节的文本字符串。
  • TEXT：最大长度为65,535个字节的文本字符串。
  • MEDIUMTEXT：最大长度为16,777,215个字节的文本字符串。
  • LONGTEXT：最大长度为4,294,967,295个字节的文本字符串。
  • ENUM：枚举类型，允许在列定义中指定一个值列表，列中的值必须来自这个列表。
  • SET：集合类型，允许在列定义中指定一个值列表，列中的值可以是列表中的任意个值的组合。
  • JOSN：json类型，可以储存复合 JSON 格式的字符串。

二进制类型

  • BIT：位值数据类型，可以存储1到64位的位值。
  • BINARY(N)：固定长度的二进制字符串。
  • VARBINARY(N)：可变长度的二进制字符串。
  • TINYBLOB：最大长度为255个字节的二进制。
  • BLOB：最大长度为65,535个字节的二进制。
  • MEDIUMBLOB：最大长度为16,777,215个字节的二进制。
  • LONGBLOB：最大长度为4,294,967,295个字节的二进制。

空间类型

  • GEOMETRY：通用的空间数据类型。
  • POINT：表示地理坐标系统中的点。
  • LINESTRING：表示地理坐标系统中的一条线。
  • POLYGON：表示地理坐标系统中的一个多边形。
  • MULTIPOINT：表示多个点的集合。
  • MULTILINESTRING：表示多条线的集合。
  • MULTIPOLYGON：表示多个多边形的集合。
  • GEOMETRYCOLLECTION：表示不同几何对象的集合。

运算符

算术运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 加法
SELECT 5 + 3; -- 返回 8

-- 减法
SELECT 10 - 2; -- 返回 8

-- 乘法
SELECT 4 * 3; -- 返回 12

-- 除法
SELECT 10 / 2; -- 返回 5.0

-- 模（取余）
SELECT 10 % 3; -- 返回 1

-- 整数除法
SELECT 10 DIV 3; -- 返回 3

-- 模函数
SELECT MOD(10, 3); -- 返回 1

比较运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- 相等
SELECT * FROM users WHERE age = 25;

-- 不等于
SELECT * FROM users WHERE age <> 25;

-- 小于
SELECT * FROM users WHERE age < 25;

-- 大于
SELECT * FROM users WHERE age > 25;

-- 小于或等于
SELECT * FROM users WHERE age <= 25;

-- 大于或等于
SELECT * FROM users WHERE age >= 25;

-- 在某个范围内
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- 在列表中
SELECT * FROM users WHERE age IN (20, 25, 30);

-- 字符匹配
SELECT * FROM users WHERE name LIKE 'A%';

-- 判断是否为NULL值
SELECT * FROM users WHERE email IS NULL;

-- 判断是否不为NULL值
SELECT * FROM users WHERE email IS NOT NULL;

-- 正则表达式匹配
SELECT * FROM users WHERE name REGEXP '^[A-Z]';

-- 同REGEXP
SELECT * FROM users WHERE name RLIKE '^[A-Z]';

逻辑运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 逻辑与
SELECT * FROM users WHERE age > 20 AND gender = 'M';

-- 逻辑或
SELECT * FROM users WHERE age > 20 OR gender = 'F';

-- 逻辑非
SELECT * FROM users WHERE NOT age > 20;

-- 逻辑与（同AND）
SELECT * FROM users WHERE age > 20 && gender = 'M';

-- 逻辑或（同OR）
SELECT * FROM users WHERE age > 20 || gender = 'F';

-- 逻辑非（同NOT）
SELECT * FROM users WHERE ! age > 20;

-- 逻辑异或
SELECT * FROM users WHERE age > 20 XOR gender = 'F';

位运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 位与
SELECT 5 & 3; -- 返回 1 (二进制: 101 & 011)

-- 位或
SELECT 5 | 3; -- 返回 7 (二进制: 101 | 011)

-- 位异或
SELECT 5 ^ 3; -- 返回 6 (二进制: 101 ^ 011)

-- 位非
SELECT ~5; -- 返回 -6 (二进制: ~101)

-- 左移
SELECT 5 << 1; -- 返回 10 (二进制: 101 << 1)

-- 右移
SELECT 5 >> 1; -- 返回 2 (二进制: 101 >> 1)

赋值运算符

1
2
3
4
5
-- 赋值
SET @var = 10;

-- 更新表中的值
UPDATE users SET age = 25 WHERE id = 1;

其他运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
- CASE 用于条件判断
SELECT name,
       CASE
           WHEN age < 20 THEN 'Teen'
           WHEN age BETWEEN 20 AND 30 THEN 'Young Adult'
           ELSE 'Adult'
       END AS age_group
FROM users;

-- IF() 用于条件判断
SELECT IF(age > 20, 'Old', 'Young') AS age_category FROM users;

-- IFNULL() 用于处理NULL值
SELECT IFNULL(email, 'No Email') AS email FROM users;

-- COALESCE() 用于返回第一个非NULL值
SELECT COALESCE(email, phone, 'No Contact') AS contact FROM users;

数据库

查看数据库列表

1
2
-- 查看数据库列表
SHOW DATABASES;

创建数据库

1
2
3
4
5
6
7
8
-- 创建数据库
CREATE DATABASE test;

-- 创建数据库：设置数据库字符集 
CREATE DATABASE test CHARACTER SET 'utf8';

-- 如果要创建的数据库已经存在，则创建不成功，但不会报错。
CREATE DATABASE IF NOT EXISTS test CHARACTER SET '字符集';

删除数据库

1
2
-- 删除数据库
DROP DATABASE IF EXISTS test;

修改数据库

1
2
-- 更改数据库字符集
ALTER DATABASE test CHARACTER SET 'utf8';

进入数据库

1
2
-- 进入数据库
USE test;

表相关

查看表的列表

1
SHOW TABLES;

创建表

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建一个名为 table_name 的表，包含 id 和 name 列，并将 id 设为主键
CREATE TABLE table_name (
    id int(11),
    name varchar(50),
    PRIMARY KEY (id)
);

-- 创建表指定字符集
CREATE TABLE table_name (
    id int(11),
    name varchar(50),
    PRIMARY KEY (id)
) CHARACTER SET 'utf8';

表结构

1
2
-- 查看表结构
DESC table_name;

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 重命名表
RENAME TABLE table_name TO new_table_name;

-- 向表 table_name 中添加一个新的列 column3
ALTER TABLE table_name ADD column3 varchar(50);

-- 修改 表 table_name 中 column3 列的 数据类型
ALTER TABLE table_name MODIFY column3 varchar(30);
-- 修改 表 table_name 中 column3 列的 默认值
ALTER TABLE table_name MODIFY column3 varchar(30) DEFAULT 'aaa';

-- 重命名字段
ALTER TABLE table_name CHANGE column3 sex varchar(30);

-- 删除字段
ALTER TABLE table_name DROP COLUMN sex;

删除表

1
2
-- 删除表 table_name
DROP TABLE IF EXISTS table_name;

删减表

1
2
-- 删除表中的所有数据，但保留表结构。
TRUNCATE TABLE table_name;

插入记录

1
2
-- 向表 table_name 中插入一行数据，其中 column1 为 'value1'，column2 为 'value2'
INSERT INTO table_name (column1, column2) VALUES ('value1', 'value2');

修改记录

1
2
-- 更新表 table_name 中 column2 等于 'some_value' 的行，将 column1 设置为 'new_value'
UPDATE table_name SET column1 = 'new_value' WHERE column2 = 'some_value';

删除记录

1
2
-- 从表 table_name 中删除 column1 等于 'value' 的所有行
DELETE FROM table_name WHERE column1 = 'value';

查询数据

简单查询

1
2
3
4
5
-- 从表 table_name 中选择所有列的数据
SELECT * FROM table_name;

-- 从表 table_name 中选择 column1 和 column2 列的数据
SELECT column1, column2 FROM table_name;

指定别名

1
2
-- 从表 table_name 中选择 column1 列，并将其命名为 alias1
SELECT column1 AS alias1 FROM table_name;

逻辑查询

1
2
3
4
5
6
7
-- 从表 table_name 中选择 column1 和 column2 的条件结果
SELECT column1, 
    CASE 
        WHEN column2 > 10 THEN 'High'
        ELSE 'Low'
    END AS result
FROM table_name;

条件查询

1
2
3
4
5
6
7
8
9
10
11
-- 从表 table_name 中选择 column1 等于 'value' 的所有行
SELECT * FROM table_name WHERE column1 = 'value';

-- 从表 table_name 中选择 column1 以 'a' 开头的所有行
SELECT * FROM table_name WHERE column1 LIKE 'a%';

-- 从表 table_name 中选择 column1 等于 'value1'、'value2' 或 'value3' 的所有行
SELECT * FROM table_name WHERE column1 IN ('value1', 'value2', 'value3');

-- 从表 table_name 中选择 column1 在 10 到 20 之间的所有行
SELECT * FROM table_name WHERE column1 BETWEEN 10 AND 20;

数据排序

1
2
-- 从表 table_name 中选择所有列的数据，并按 column1 升序和 column2 降序排序
SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

分组查询

1
2
-- 从表 table_name 中选择 column1 列，并计算每个值的出现次数
SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

分组过滤

1
2
-- 从表 table_name 中选择 column1 列，并计算每个值的出现次数，只显示出现次数大于1的组
SELECT column1, COUNT(*) AS count FROM table_name GROUP BY column1 HAVING count > 1;

数据分页

1
2
3
-- 从表 table_name 中查询前十条数据
SELECT * FROM table_name LIMIT 10;
SELECT * FROM table_name LIMIT 0, 10;

去重查询

1
2
-- 从表 table_name 中选择 column1 列的所有唯一值
SELECT DISTINCT column1 FROM table_name;

连接查询

1
2
3
4
5
6
7
8
9
-- 从 table1 和 table2 中选择所有匹配的行
SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2;
SELECT * FROM table1 INNER JOIN table2 ON table1.column1 = table2.column2;

-- 返回 table1 中的所有行，即使 table2 中没有匹配的行
SELECT * FROM table1 LEFT JOIN table2 ON table1.column1 = table2.column2;

-- 返回 table2 中的所有行，即使 table1 中没有匹配的行
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column1 = table2.column2;

合并查询

1
2
3
4
5
-- 合并 table1 和 table2 中 column1 的所有唯一值
SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

-- 合并 table1 和 table2 中 column1 的所有值，包括重复值
SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

数据控制

授予用户权限

1
2
-- 授予用户 user_name 对表 table_name 的 SELECT 和 INSERT 权限
GRANT SELECT, INSERT ON table_name TO user_name;

撤销用户权限

1
2
-- 撤销用户 user_name 对表 table_name 的 SELECT 和 INSERT 权限
REVOKE SELECT, INSERT ON table_name FROM user_name;

事务控制

开始事务

1
2
-- 开始一个事务
START TRANSACTION;

提交事务

1
2
-- 提交当前事务
COMMIT;

回滚事务

1
2
-- 回滚当前事务
ROLLBACK;

视图

视图（View）是一种虚拟表，其内容由查询定义。与真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

  • 视图的优点
    • 简化复杂查询：视图可以将复杂的SQL查询封装起来，使得用户无需记住复杂的SQL语句。
    • 安全性：通过视图，可以限制用户访问的数据，从而实现一定程度的安全控制。
    • 数据独立性：即使基础表结构发生变化，只要视图的输出不变，基于视图的应用程序就不需要修改。
  • 视图的缺点
    • 性能开销：每次查询视图时，都会执行视图定义中的查询，这可能会影响性能，尤其是在视图涉及大量数据或复杂计算时。
    • 维护成本：如果视图依赖的基础表结构发生改变，可能需要调整视图的定义。

创建视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 基于单表
CREATE VIEW sales_employees AS
    SELECT employee_id, first_name, last_name, department
    FROM employees
    WHERE department = 'Sales';

-- 包含聚合函数
CREATE VIEW sales_summary AS
SELECT department, SUM(sales) AS total_sales
FROM sales
GROUP BY department;

-- 包含 GROUP BY 子句
CREATE VIEW employee_summary AS
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;

-- 包含 DISTINCT 关键字
CREATE VIEW unique_customers AS
SELECT DISTINCT customer_name
FROM orders;

-- 包含子查询
CREATE VIEW top_selling_products AS
SELECT product_id, (SELECT MAX(price) FROM products WHERE category = 'Electronics') AS max_price
FROM sales;

-- 联合查询
CREATE VIEW combined_data AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

-- 基于其他视图
CREATE VIEW view1 AS
SELECT * FROM table1;
CREATE VIEW view2 AS
SELECT * FROM view1;

查看视图

1
2
3
4
5
6
7
8
9
10
11
-- 查看数据库的表对象、视图对象
SHOW TABLES;

-- 查看视图的结构
DESC view_name;

-- 查看视图的属性信息
SHOW TABLE STATUS LIKE 'view_name';

-- 查看视图的详细定义信息
SHOW CREATE VIEW view_name;

使用视图

1
2
-- 可以像使用普通表一样使用它
SELECT * FROM sales_employees;

更新视图

如果底层表允许，可以通过视图更新、插入或删除数据。但是，并非所有视图都支持这些操作，这取决于创建视图时的定义。

  • 包含聚合函数：
    • 如果视图中包含了聚合函数（如 SUM(), AVG(), COUNT(), MAX(), MIN() 等），则该视图不能更新。
  • 包含 GROUP BY 或 HAVING 子句：
    • 如果视图中使用了 GROUP BY 或 HAVING 子句，通常是为了汇总数据，这种视图也是不可更新的。
  • 包含 DISTINCT 关键字：
    • 如果视图中使用了 DISTINCT 关键字来消除重复行，那么该视图不能更新。
  • 包含子查询：
    • 如果视图中包含了子查询，尤其是那些返回多行或多列的子查询，这样的视图通常不能更新。
  • 联合查询：
    • 如果视图是由多个表通过 UNION 或 UNION ALL 联合查询创建的，那么该视图不能更新。
  • 包含不可更新的表达式：
    • 如果视图中包含了不可更新的表达式（如 CURRENT_DATE, NOW(), USER() 等），那么该视图不能更新。
  • 基于其他视图：
    • 如果视图是基于另一个不可更新的视图创建的，那么这个视图也不能更新。
  • 包含 JOIN 操作：
    • 如果视图中包含了 JOIN 操作，特别是当 JOIN 涉及多个表时，视图通常是不可更新的。不过，如果 JOIN 是简单的左连接或右连接，并且更新操作只影响单个表，那么在这种特定情况下，视图可能是可更新的。
  • 包含 WITH CHECK OPTION：
    • 如果视图定义中使用了 WITH CHECK OPTION，并且更新操作违反了这个选项的约束条件，那么更新操作将失败。
  • 包含 ORDER BY 子句：
    • 虽然 ORDER BY 子句本身不会使视图不可更新，但如果视图中同时包含了其他使视图不可更新的因素，那么 ORDER BY 可能会进一步限制视图的可更新性。
1
2
3
4
-- 可更新视图
UPDATE sales_employees
SET department = 'Marketing'
WHERE employee_id = 101;

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。如果基于视图a、b创建了新的视图c，如果将视图a或者视图b删除，会导致视图c的查询失败。这样的视图c需要手动删除或修改，否则影响使用。

1
DROP VIEW IF EXISTS sales_employees;

其他

EXISTS

1
2
3
-- 检查子查询存在结果
-- 从 table1 中选择存在匹配行的记录
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column2);

NOT EXISTS

1
2
3
-- 检查子查询不存在结果
-- 从 table1 中选择不存在匹配行的记录
SELECT * FROM table1 WHERE NOT EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column2);

WITH

1
2
3
4
-- 定义公共表表达式 (CTE)
-- 定义一个 CTE 并从中选择数据
WITH cte_name AS (SELECT column1, column2 FROM table_name)
SELECT * FROM cte_name;

数据处理

预设函数

字符串函数

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

-- 使用分隔符连接字符串
SELECT CONCAT_WS(', ', 'Apple', 'Banana', 'Cherry'); -- 返回 'Apple, Banana, Cherry'

-- 转换为小写
SELECT LOWER('HELLO'); -- 返回 'hello'

-- 转换为大写
SELECT UPPER('hello'); -- 返回 'HELLO'

-- 去除字符串两端的空格
SELECT TRIM('   Hello   '); -- 返回 'Hello'

-- 去除字符串左边的空格
SELECT LTRIM('   Hello   '); -- 返回 'Hello   '

-- 去除字符串右边的空格
SELECT RTRIM('   Hello   '); -- 返回 '   Hello'

-- 重复字符串
SELECT REPEAT('Hello', 3); -- 返回 'HelloHelloHello'

-- 返回字符串的长度（字节）
SELECT LENGTH('Hello'); -- 返回 5

-- 返回字符串的长度（字符）
SELECT CHAR_LENGTH('Hello'); -- 返回 5

-- 提取子字符串
SELECT SUBSTRING('Hello World', 7, 5); -- 返回 'World'

-- 从左边提取子字符串
SELECT LEFT('Hello World', 5); -- 返回 'Hello'

-- 从右边提取子字符串
SELECT RIGHT('Hello World', 5); -- 返回 'World'

-- 在字符串中插入子字符串
SELECT INSERT('Hello World', 7, 5, 'MySQL'); -- 返回 'Hello MySQL'

-- 替换字符串中的子字符串
SELECT REPLACE('Hello World', 'World', 'MySQL'); -- 返回 'Hello MySQL'

-- 反转字符串
SELECT REVERSE('Hello'); -- 返回 'olleH'

-- 生成指定数量的空格
SELECT SPACE(5); -- 返回 '     '

-- 用指定的字符串左填充
SELECT LPAD('Hello', 10, '*'); -- 返回 '*****Hello'

-- 用指定的字符串右填充
SELECT RPAD('Hello', 10, '*'); -- 返回 'Hello*****'

-- 返回字符串的长度（字符）
SELECT CHAR_LENGTH('Hello'); -- 返回 5

-- 返回第 N 个字符串
SELECT ELT(2, 'apple', 'banana', 'cherry'); -- 返回 'banana'

-- 返回 str 在列表中的位置
SELECT FIELD('banana', 'apple', 'banana', 'cherry'); -- 返回 2

-- 返回 str 在逗号分隔的字符串列表中的位置
SELECT FIND_IN_SET('banana', 'apple,banana,cherry'); -- 返回 2

-- 创建一个集合
SELECT MAKE_SET(1, 'apple', 'banana', 'cherry'); -- 返回 'apple'

-- 返回字符串的字符集
SELECT CHARSET('Hello'); -- 返回 'utf8mb4'

-- 返回字符串的排序规则
SELECT COLLATION('Hello'); -- 返回 'utf8mb4_0900_ai_ci'

-- 将字符串转换为指定的字符集
SELECT CONVERT('Hello' USING latin1); -- 返回 'Hello'

-- 将字符串转换为指定的字符集
SELECT CAST('Hello' AS CHAR CHARACTER SET latin1); -- 返回 'Hello'

-- 返回字符串的 Soundex 编码
SELECT SOUNDEX('Hello'); -- 返回 'H400'

-- 返回字符串的权重字符串
SELECT WEIGHT_STRING('Hello World'); -- 返回 'Hello World' 的权重字符串

-- 全文搜索
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL');

数值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- 圆周率
SELECT PI(); -- 返回 3.141592653589793

-- 自然指数
SELECT EXP(1); -- 返回 2.718281828459045

-- 自然对数
SELECT LOG(2.718281828459045); -- 返回 1.0

-- 以 10 为底的对数
SELECT LOG10(100); -- 返回 2.0

-- 以 2 为底的对数
SELECT LOG2(8); -- 返回 3.0

-- 幂运算
SELECT POW(2, 3); -- 返回 8

-- 平方根
SELECT SQRT(16); -- 返回 4

-- 绝对值
SELECT ABS(-5); -- 返回 5

-- 向上取整
SELECT CEIL(3.14); -- 返回 4

-- 向下取整
SELECT FLOOR(3.14); -- 返回 3

-- 四舍五入
SELECT ROUND(3.14159, 2); -- 返回 3.14

-- 随机数
SELECT RAND(); -- 返回一个介于 0 和 1 之间的随机数
SELECT RAND(12345); -- 返回一个固定的随机数

-- 模运算
SELECT MOD(10, 3); -- 返回 1

-- 截断
SELECT TRUNCATE(123.456, 1); -- 返回 123.4 不进位直接舍去

-- 格式化数字
SELECT FORMAT(1234567.89, 2); -- 返回 '1,234,567.89'

-- 返回 x 的符号
SELECT SIGN(-5); -- 返回 -1
SELECT SIGN(0); -- 返回 0
SELECT SIGN(5); -- 返回 1

三角函数

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
-- 正弦值
SELECT SIN(PI() / 2); -- 返回 1.0

-- 余弦值
SELECT COS(PI()); -- 返回 -1.0

-- 正切值
SELECT TAN(PI() / 4); -- 返回 1.0

-- 反正弦值（弧度）
SELECT ASIN(1); -- 返回 1.5707963267948966 (即 PI()/2)

-- 反余弦值（弧度）
SELECT ACOS(-1); -- 返回 3.141592653589793 (即 PI())

-- 反正切值（弧度）
SELECT ATAN(1); -- 返回 0.7853981633974483 (即 PI()/4)

-- 反正切值（弧度），使用两个参数
SELECT ATAN(1, 1); -- 返回 0.7853981633974483 (即 PI()/4)

-- 将弧度转换为角度
SELECT DEGREES(PI() / 2); -- 返回 90.0

-- 将角度转换为弧度
SELECT RADIANS(90); -- 返回 1.5707963267948966 (即 PI()/2)

位操作函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 将数字转换为二进制字符串
SELECT BIN(10); -- 返回 '1010'

-- 将数字转换为八进制字符串
SELECT OCT(10); -- 返回 '12'

-- 将数字转换为十六进制字符串
SELECT HEX(10); -- 返回 'A'

-- 将十六进制字符串转换为二进制字符串
SELECT UNHEX('A'); -- 返回 0x41

-- 返回 x 的二进制表示中 1 的个数
SELECT BIT_COUNT(10); -- 返回 2

-- 返回分组中所有值的按位与
SELECT BIT_AND(id) FROM users;

-- 返回分组中所有值的按位或
SELECT BIT_OR(id) FROM users;

-- 返回分组中所有值的按位异或
SELECT BIT_XOR(id) FROM users;

时间函数

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

-- 当前时间
SELECT CURTIME(); -- 返回当前时间

-- 当前日期和时间
SELECT NOW(); -- 返回当前日期和时间

-- 返回日期的年份
SELECT YEAR('2023-10-01'); -- 返回 2023

-- 返回日期的月份
SELECT MONTH('2023-10-01'); -- 返回 10

-- 返回日期的天数
SELECT DAY('2023-10-01'); -- 返回 1

-- 返回日期时间的季度
SELECT QUARTER('2023-10-01'); -- 返回 4

-- 返回日期时间的周数
SELECT WEEK('2023-10-01', 0); -- 返回 40

-- 返回日期时间的星期几（1=Sunday, 7=Saturday）
SELECT DAYOFWEEK('2023-10-01'); -- 返回 1 (Sunday)

-- 返回日期时间的星期几（0=Monday, 6=Sunday）
SELECT WEEKDAY('2023-10-01'); -- 返回 6 (Sunday)

-- 返回日期时间的月份中的天数
SELECT DAYOFMONTH('2023-10-01'); -- 返回 1

-- 返回日期时间的年份中的天数
SELECT DAYOFYEAR('2023-10-01'); -- 返回 274

-- 返回日期时间的年份中的周数
SELECT WEEKOFYEAR('2023-10-01'); -- 返回 40

-- 返回日期时间的微秒部分
SELECT MICROSECOND('2023-10-01 12:00:00.123456'); -- 返回 123456

-- 返回 UTC 日期
SELECT UTC_DATE();

-- 返回 UTC 时间
SELECT UTC_TIME();

-- 返回 UTC 日期和时间
SELECT UTC_TIMESTAMP();

--------------------------时间计算---------------------------
-- 返回两个时间的差值
SELECT TIMEDIFF('12:00:00', '10:00:00'); -- 返回 '02:00:00'

-- 计算两个日期之间的天数差
SELECT DATEDIFF('2023-10-01', '2023-09-01'); -- 返回 30

-- 向日期时间添加一个时间间隔
SELECT ADDTIME('2023-10-01 12:00:00', '01:00:00'); -- 返回 '2023-10-01 13:00:00'

-- 从日期时间减去一个时间间隔
SELECT SUBTIME('2023-10-01 12:00:00', '01:00:00'); -- 返回 '2023-10-01 11:00:00'

-- 向日期添加时间间隔
SELECT DATE_ADD('2023-10-01', INTERVAL 1 DAY); -- 返回 '2023-10-02'

-- 从日期减去时间间隔
SELECT DATE_SUB('2023-10-01', INTERVAL 1 DAY); -- 返回 '2023-09-30'

--------------------------时间转换---------------------------
-- 从日期中提取年份
SELECT EXTRACT(YEAR FROM '2023-10-01'); -- 返回 2023

-- 将时间转换为秒数
SELECT TIME_TO_SEC('01:00:00'); -- 返回 3600

-- 将秒数转换为时间
SELECT SEC_TO_TIME(3600); -- 返回 '01:00:00'

-- 将日期时间从一个时区转换到另一个时区
SELECT CONVERT_TZ('2023-10-01 12:00:00', '+00:00', '+08:00');

-- 将时间转换为秒数
SELECT TIME_TO_SEC('01:00:00'); -- 返回 3600

-- 将秒数转换为时间
SELECT SEC_TO_TIME(3600); -- 返回 '01:00:00'

-- 将字符串转换为日期
SELECT STR_TO_DATE('2023-10-01', '%Y-%m-%d'); -- 返回 '2023-10-01'

-- 按照指定格式格式化日期
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 返回 '2023-10-01 12:34:56'

-- 按照指定格式格式化时间
SELECT TIME_FORMAT('12:34:56', '%H:%i:%s'); -- 返回 '12:34:56'

流控制函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 条件判断
SELECT IF(1 > 0, 'True', 'False'); -- 返回 'True'

-- 多条件分支选择
SELECT 
    CASE 
        WHEN age < 20 THEN 'Teen'
        WHEN age BETWEEN 20 AND 30 THEN 'Young Adult'
        ELSE 'Adult'
    END AS age_group
FROM users;

-- 处理 NULL 值
SELECT IFNULL(NULL, 'Default Value'); -- 返回 'Default Value'

-- 返回第一个非 NULL 值
SELECT COALESCE(NULL, NULL, 'First Non-NULL', 'Second Non-NULL'); -- 返回 'First Non-NULL'

-- 如果 expr1 等于 expr2，则返回 NULL，否则返回 expr1
SELECT NULLIF(10, 10); -- 返回 NULL
SELECT NULLIF(10, 20); -- 返回 10

类型转换函数

1
2
3
4
5
6
7
8
9
10
11
-- 将字符串转换为整数
SELECT CAST('123' AS UNSIGNED); -- 返回 123

-- 将字符串转换为日期
SELECT CAST('2023-10-01' AS DATE); -- 返回 '2023-10-01'

-- 将字符串转换为整数
SELECT CONVERT('123', UNSIGNED); -- 返回 123

-- 将字符串转换为二进制
SELECT BINARY 'Hello'; -- 返回二进制字符串

聚合函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 返回行数
SELECT COUNT(*) FROM users;

-- 返回总和
SELECT SUM(salary) FROM employees;

-- 返回平均值
SELECT AVG(salary) FROM employees;

-- 返回最小值
SELECT MIN(salary) FROM employees;

-- 返回最大值
SELECT MAX(salary) FROM employees;

-- 将分组中的值连接成一个字符串
SELECT GROUP_CONCAT(name) FROM users; -- 返回 'John,Doe,Alice,Bob'

-- 返回分组中所有值的按位与
SELECT BIT_AND(id) FROM users;

-- 返回分组中所有值的按位或
SELECT BIT_OR(id) FROM users;

-- 返回分组中所有值的按位异或
SELECT BIT_XOR(id) FROM users;

-- 返回分组中表达式的标准差
SELECT STDDEV(salary) FROM employees;

-- 返回分组中表达式的方差
SELECT VARIANCE(salary) FROM employees;

加密函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- MD5 哈希
SELECT MD5('Hello'); -- 返回 '8b1a9953c4611296a827abf8c47804d7'

-- SHA-1 哈希
SELECT SHA1('Hello'); -- 返回 '2aae6c35c94fcfb415dbe95f408b9ce91ee846ed'

-- SHA-2 哈希
SELECT SHA2('Hello', 256); -- 返回 '2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824'

-- 加密密码
SELECT PASSWORD('my_password'); -- 返回加密后的密码

-- DES 加密
SELECT ENCRYPT('Hello', 'salt'); -- 返回加密后的字符串

-- AES 加密
SELECT HEX(AES_ENCRYPT('Hello', 'my_key')); -- 返回加密后的十六进制字符串

-- AES 解密
SELECT AES_DECRYPT(UNHEX('加密后的十六进制字符串'), 'my_key'); -- 返回 'Hello'

-- 返回字符串的加密密码
SELECT PASSWORD('my_password');

-- 返回字符串的旧版加密密码
SELECT OLD_PASSWORD('my_password');

-- 使用指定的密码对字符串进行编码
SELECT ENCODE('Hello', 'my_password');

-- 使用指定的密码对字符串进行解码
SELECT DECODE('encoded_string', 'my_password');

JSON 函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 创建 JSON 对象
SELECT JSON_OBJECT('name', 'John', 'age', 30); -- 返回 '{"name": "John", "age": 30}'

-- 创建 JSON 数组
SELECT JSON_ARRAY('apple', 'banana', 'cherry'); -- 返回 '["apple", "banana", "cherry"]'

-- 从 JSON 文档中提取值
SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- 返回 '"John"'

-- 向 JSON 文档中插入或更新值
SELECT JSON_SET('{"name": "John", "age": 30}', '$.city', 'New York'); -- 返回 '{"name": "John", "age": 30, "city": "New York"}'

-- 从 JSON 文档中删除值
SELECT JSON_REMOVE('{"name": "John", "age": 30}', '$.age'); -- 返回 '{"name": "John"}'

-- 检查 JSON 文档中是否包含指定值
SELECT JSON_CONTAINS('{"name": "John", "age": 30}', '"John"', '$.name'); -- 返回 1 (表示 true)

空间函数

1
2
3
4
5
6
7
8
9
10
11
-- 从 WKT 创建几何对象
SELECT ST_GeomFromText('POINT(1 1)');

-- 将几何对象转换为 WKT
SELECT ST_AsText(ST_GeomFromText('POINT(1 1)')); -- 返回 'POINT(1 1)'

-- 计算两个几何对象之间的距离
SELECT ST_Distance(ST_GeomFromText('POINT(1 1)'), ST_GeomFromText('POINT(2 2)'));

-- 检查一个几何对象是否包含另一个几何对象
SELECT ST_Contains(ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'), ST_GeomFromText('POINT(0.5 0.5)'));

窗口函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 返回行号
SELECT user_id, transaction_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS row_num FROM transactions;

-- 返回排名
SELECT user_id, transaction_date, RANK() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rank FROM transactions;

-- 返回密集排名
SELECT user_id, transaction_date, DENSE_RANK() OVER (PARTITION BY user_id ORDER BY transaction_date) AS dense_rank FROM transactions;

-- 返回当前行之后的行的值
SELECT user_id, transaction_date, LEAD(transaction_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY transaction_date) AS next_date FROM transactions;

-- 返回当前行之前的行的值
SELECT user_id, transaction_date, LAG(transaction_date, 1, NULL) OVER (PARTITION BY user_id ORDER BY transaction_date) AS prev_date FROM transactions;

-- 返回分区中的第一个值
SELECT user_id, transaction_date, FIRST_VALUE(transaction_date) OVER (PARTITION BY user_id ORDER BY transaction_date) AS first_date FROM transactions;

-- 返回分区中的最后一个值
SELECT user_id, transaction_date, LAST_VALUE(transaction_date) OVER (PARTITION BY user_id ORDER BY transaction_date) AS last_date FROM transactions;

目录函数

1
2
3
4
5
6
7
8
9
10
11
-- 从文件系统中读取文件内容
SELECT LOAD_FILE('/path/to/file.txt');

-- 返回文件的权限
SELECT FILE_PERMS('/path/to/file.txt');

-- 返回文件的大小
SELECT FILE_SIZE('/path/to/file.txt');

-- 读取文件内容（仅在某些存储引擎中可用）
SELECT FILE_READ('/path/to/file.txt');

信息函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 返回 MySQL 服务器的版本号
SELECT VERSION();

-- 返回当前用户的名称
SELECT USER();

-- 返回当前连接的用户名
SELECT CURRENT_USER();

-- 返回系统用户名
SELECT SYSTEM_USER();

-- 返回会话用户名
SELECT SESSION_USER();

-- 返回当前选择的数据库名称
SELECT DATABASE();

-- 返回最后一个插入的自增 ID
INSERT INTO users (name, age) VALUES ('John Doe', 30);
SELECT LAST_INSERT_ID();

-- 显示系统变量
SHOW VARIABLES;

-- 显示服务器状态信息
SHOW STATUS;

-- 显示当前正在运行的线程
SHOW PROCESSLIST;

-- 终止指定的线程
KILL 12345;

权限安全函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 开始一个新的事务
START TRANSACTION;

-- 提交当前事务
COMMIT;

-- 回滚当前事务
ROLLBACK;

-- 设置一个保存点
SAVEPOINT my_savepoint;

-- 释放一个保存点
RELEASE SAVEPOINT my_savepoint;

-- 获取行级锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 锁定表
LOCK TABLES users WRITE;

-- 解锁表
UNLOCK TABLES;

-- 检查锁是否可用
SELECT IS_FREE_LOCK('my_lock');

-- 检查锁是否已被占用
SELECT IS_USED_LOCK('my_lock');

-- 尝试获取锁
SELECT GET_LOCK('my_lock', 10);

-- 释放锁
SELECT RELEASE_LOCK('my_lock');

管理函数

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
-- 显示用户的权限
SHOW GRANTS FOR 'user'@'localhost';

-- 显示表的创建语句
SHOW CREATE TABLE users;

-- 显示数据库的创建语句
SHOW CREATE DATABASE mydatabase;

-- 显示表的状态信息
SHOW TABLE STATUS FROM mydatabase LIKE 'users';

-- 显示表的索引信息
SHOW INDEX FROM users FROM mydatabase;

-- 显示系统变量
SHOW VARIABLES LIKE 'max_connections';

-- 显示服务器状态信息
SHOW STATUS LIKE 'Threads_connected';

-- 显示当前正在运行的线程
SHOW PROCESSLIST;

-- 终止指定的线程
KILL 12345;

其他函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 生成 UUID
SELECT UUID(); -- 返回类似 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11' 的字符串

-- 使查询暂停 5 秒
SELECT SLEEP(5); -- 暂停 5 秒

-- 性能测试
SELECT BENCHMARK(1000000, 1+1); -- 重复执行 1+1 一百万次

-- 将 IP 地址转换为数字
SELECT INET_ATON('192.168.0.1'); -- 返回 3232235521

-- 将数字转换为 IP 地址
SELECT INET_NTOA(3232235521); -- 返回 '192.168.0.1'

-- 将 IPv6 地址转换为数字
SELECT INET6_ATON('2001:0db8:85a3:0000:0000:8a2e:0370:7334'); -- 返回数字

-- 将数字转换为 IPv6 地址
SELECT INET6_NTOA(340282366920938463463374607431768211456); -- 返回 '2001:0db8:85a3:0000:0000:8a2e:0370:7334'

-- 返回列的默认值
INSERT INTO users (name) VALUES ('Alice');
SELECT DEFAULT(age) FROM users;

-- 在 INSERT ... ON DUPLICATE KEY UPDATE 语句中使用 VALUES
INSERT INTO users (id, name) VALUES (1, 'Bob') ON DUPLICATE KEY UPDATE name = VALUES(name);

-- 返回上一次 SELECT 语句返回的行数
SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 10;
SELECT FOUND_ROWS();

-- 返回上一次操作影响的行数
DELETE FROM users WHERE id = 1;
SELECT ROW_COUNT();

-- 重复执行表达式 count 次，用于性能测试
SELECT BENCHMARK(1000000, 1+1);

-- 显示最近的警告信息
SHOW WARNINGS;

-- 显示最近的错误信息
SHOW ERRORS;

自定义函数

自定义函数（User-Defined Function, UDF）是数据库中的一种编程对象，用于封装特定的计算逻辑并返回一个值。自定义函数可以直接在SQL查询中使用，就像使用内置函数一样。

  • 灵活性：可以实现比内置函数更复杂的逻辑。
  • 集成性：可以直接嵌入到SQL查询中使用，提高查询的表达能力。
  • 性能：在数据库服务器端执行，减少了客户端与服务器之间的数据传输量，提高了执行效率。

创建自定义函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- DELIMITER // 和 DELIMITER ;
--      改变结束符，因为函数体中可能包含多条SQL语句，每条语句以分号结尾，如果不改变结束符，MySQL会将整个函数视为一条语句。
-- RETURNS return_data_type：指定函数返回的数据类型。
-- DETERMINISTIC：表示函数对于相同的输入总是返回相同的结果。
-- READS SQL DATA：表示函数读取数据但不修改数据。
-- MODIFIES SQL DATA：表示函数可以修改数据。
DELIMITER //
CREATE FUNCTION function_name (parameter_name data_type, ...)
RETURNS return_data_type
DETERMINISTIC | READS SQL DATA | MODIFIES SQL DATA
BEGIN
    -- SQL statements and control structures
    RETURN result;
END //
DELIMITER ;

创建自定义函数示例

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
-- 假设我们有一个员工表 employees，表结构如下：
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 创建一个自定义函数，用于计算员工的奖金：
DELIMITER //
CREATE FUNCTION CalculateBonus (salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE bonus DECIMAL(10, 2);
    
    -- 计算奖金
    IF salary > 50000 THEN
        SET bonus = salary * 0.1;
    ELSE
        SET bonus = salary * 0.05;
    END IF;
    
    RETURN bonus;
END //
DELIMITER ;

使用自定义函数

1
SELECT id, name, salary, CalculateBonus(salary) AS bonus FROM employees;

查看修改删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看创建信息：\G 格式化，可以不加
SHOW CREATE FUNCTION function_name\G;

-- 查看状态信息
SHOW FUNCTION STATUS LIKE 'function_name';

-- 查看详细信息
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'function_name' AND ROUTINE_TYPE = 'FUNCTION';

-- 修改：不能修改函数体
ALTER FUNCTION function_name 
SQL SECURITY INVOKER 
COMMENT '功能描述';

-- 删除
DROP FUNCTION IF EXISTS function_name;

存储过程

存储过程（Stored Procedure）是一组预编译的SQL语句，它被作为一个单元保存在数据库中。通过调用存储过程的名字并提供所需的参数来执行这组SQL语句。存储过程可以接收输入参数、输出参数或者同时接收输入输出参数。

  • 性能提升：由于存储过程是在服务器端运行的，减少了网络传输的数据量。
  • 安全增强：可以通过存储过程限制对数据库表的直接访问。
  • 易于维护：如果需要修改业务逻辑，只需更改存储过程即可，无需修改应用代码。
  • 复用性高：存储过程可以被多个程序或用户重复调用。

创建存储过程

1
2
3
4
5
6
7
8
9
10
11
-- DELIMITER // 和 DELIMITER ; 
--      改变结束符，因为存储过程中可能包含多条SQL语句，每条语句以分号结尾，如果不改变结束符，MySQL会将整个存储过程视为一条语句。
-- IN 参数：表示输入参数，调用者必须提供值。
-- OUT 参数：表示输出参数，存储过程执行完毕后会将值返回给调用者。
-- INOUT 参数：既可以作为输入也可以作为输出参数。
DELIMITER //
CREATE PROCEDURE procedure_name (IN|OUT|INOUT parameter_name data_type, ...)
BEGIN
    -- SQL statements and control structures
END //
DELIMITER ;

创建存储过程示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 示例
-- 假设我们有一个员工表 employees，表结构如下：
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 创建一个存储过程，用于更新员工的薪水：
DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary (
    IN emp_id INT,
    IN new_salary DECIMAL(10, 2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE current_salary DECIMAL(10, 2);
    
    -- 获取当前薪水
    SELECT salary INTO current_salary FROM employees WHERE id = emp_id;
    
    -- 检查新薪水是否大于当前薪水
    IF new_salary > current_salary THEN
        UPDATE employees SET salary = new_salary WHERE id = emp_id;
        SET result = 'Salary updated successfully';
    ELSE
        SET result = 'New salary must be greater than current salary';
    END IF;
END //
DELIMITER ;

调用存储过程

1
2
CALL UpdateEmployeeSalary(1, 60000, @result);
SELECT @result;

查看修改删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 查看创建信息：\G 格式化，可以不加
SHOW CREATE PROCEDURE procedure_name\G;

-- 查看状态信息
SHOW PROCEDURE STATUS LIKE 'procedure_name';

-- 查看详细信息
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'procedure_name' AND ROUTINE_TYPE = 'PROCEDURE';

-- 修改：不能修改函数体
ALTER PROCEDURE procedure_name 
SQL SECURITY INVOKER 
COMMENT '功能描述';

-- 删除
DROP PROCEDURE IF EXISTS procedure_name;

系统变量

系统变量是MySQL服务器内部使用的变量，用于控制服务器的行为和操作。它们由MySQL预定义，用户不能创建新的系统变量，但可以在一定范围内修改它们的值。

全局变量

全局变量（Global Variables）影响服务器整体的操作，对所有连接到该服务器的客户端都有效。全局变量通常在服务器启动时根据配置文件中的设置初始化，并且可以在运行时通过命令进行更改。但是，并非所有的全局变量都可以在运行时更改。

1
2
3
4
5
6
7
8
9
10
11
-- 查看全局变量列表
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 查看指定的系统变量
SELECT @@global.max_connections;
SELECT @@max_connections;

-- 设置全局变量
SET GLOBAL max_connections = 500;
SET @@global.max_connections = 500;

会话变量

会话变量（Session Variables）只影响当前客户端连接的操作。每个客户端连接都有自己的会话变量副本，因此更改会话变量不会影响其他客户端连接。会话变量可以通过命令来设置。如果省略了 GLOBAL 或 SESSION 关键字，默认情况下 SET 语句会作用于会话变量。

1
2
3
4
5
6
7
8
9
10
11
-- 查看会话变量列表
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'sql_mode';

-- 查看指定的会话变量
SELECT @@session.sql_mode;
SELECT @@sql_mode;

-- 设置会话变量
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SET @@session.sql_mode = 'STRICT_TRANS_TABLES';

自定义变量

用户定义的变量是由用户创建并用于存储数据的变量。这类变量可以用来在SQL语句之间传递值。用户定义的变量以 @ 符号开始，不需要提前声明即可使用。

局部变量

局部变量（Local Variables）是在存储过程或函数等程序块中声明的变量，它们的作用域仅限于声明它们的程序块。

1
2
3
4
5
6
7
8
DELIMITER //
CREATE PROCEDURE example_procedure()
BEGIN
    DECLARE local_var INT DEFAULT 0; -- 使用 DECLARE 关键字，声明局部变量。使用 DEFAULT 关键字，添加默认值。
    SET local_var = 10; -- 使用 SET 关键字，赋值给局部变量
    SELECT local_var; -- 使用局部变量
END //
DELIMITER ;

用户变量

用户变量（User-defined Variables）可以在一个语句中设置，在另一个语句中读取，直到客户端会话结束。用户变量的名称以 @ 开头。

1
2
3
4
5
6
7
8
9
-- 设置用户变量
SET @user_var = 1;

-- 使用用户变量
SELECT @user_var;

-- 示例
SELECT AVG(salary) INTO @avg_sal FROM employees;
SELECT @avg_sal;

控制结构

IF 判断语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 用途：用于简单的条件判断。
-- 示例：计算员工的奖金，根据不同的薪水范围返回不同的奖金比例。
DELIMITER //
CREATE FUNCTION CalculateBonus (salary DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
    DECLARE bonus DECIMAL(10, 2);

    -- 根据薪水范围计算奖金
    IF salary > 50000 THEN
        SET bonus = salary * 0.1;  -- 薪水超过50000，奖金为10%
    ELSE
        SET bonus = salary * 0.05; -- 薪水不超过50000，奖金为5%
    END IF;

    RETURN bonus;
END //
DELIMITER ;

CASE 判断语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 用途：用于多条件判断。
-- 示例：根据员工的薪水范围确定其薪资等级。
DELIMITER //
CREATE FUNCTION DetermineSalaryLevel (salary DECIMAL(10, 2))
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
    DECLARE level VARCHAR(50);

    -- 根据薪水范围确定薪资等级
    CASE
        WHEN salary < 30000 THEN SET level = 'Low';  -- 薪水低于30000，等级为低
        WHEN salary >= 30000 AND salary < 70000 THEN SET level = 'Medium';  -- 薪水在30000到70000之间，等级为中
        ELSE SET level = 'High';  -- 薪水超过70000，等级为高
    END CASE;

    RETURN level;
END //
DELIMITER ;

LOOP 循环语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 用途：用于无限循环，通常需要配合 LEAVE 语句使用。
-- LEAVE 语句：用于立即退出循环或条件语句。
-- 示例：计算两个数的最小公倍数。
DELIMITER //
CREATE FUNCTION LeastCommonMultiple (a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE lcm INT;
    DECLARE max_val INT;
    DECLARE min_val INT;

    -- 确定较大的数和较小的数
    SET max_val = GREATEST(a, b);
    SET min_val = LEAST(a, b);

    -- 初始化lcm为较大的数
    SET lcm = max_val;

    -- 循环直到找到最小公倍数
    my_loop: LOOP
        IF lcm % min_val = 0 THEN
            LEAVE my_loop;  -- 找到最小公倍数，退出循环
        END IF;
        SET lcm = lcm + max_val;  -- 增加较大的数
    END LOOP;

    RETURN lcm;
END //
DELIMITER ;

WHILE 循环语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 用途：用于条件循环，当条件满足时继续执行循环体。
-- LEAVE 语句：用于立即退出循环或条件语句。
-- ITERATE 语句：用于跳过当前循环体，继续下一次循环。
-- 示例：过滤数组中的负数并求和。
DELIMITER //
CREATE FUNCTION SumPositive (arr VARCHAR(100))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE sum_val INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    DECLARE len INT;
    DECLARE num INT;

    -- 将字符串转换为数组长度
    SET len = LENGTH(arr) - LENGTH(REPLACE(arr, ',', '')) + 1;

    -- 遍历数组
    my_loop: LOOP
        IF i > len THEN
            LEAVE my_loop;  -- 结束循环
        END IF;

        SET num = CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(arr, ',', i), ',', -1) AS SIGNED);
        IF num < 0 THEN
            ITERATE my_loop;  -- 跳过负数，继续下一次循环
        END IF;

        SET sum_val = sum_val + num;
        SET i = i + 1;
    END LOOP;

    RETURN sum_val;
END //
DELIMITER ;

REPEAT 循环语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 用途：用于至少执行一次的循环，直到条件不再满足。
-- 示例：计算斐波那契数列的第n项。
DELIMITER //
CREATE FUNCTION Fibonacci (n INT)
RETURNS BIGINT
DETERMINISTIC
BEGIN
    DECLARE a BIGINT DEFAULT 0;
    DECLARE b BIGINT DEFAULT 1;
    DECLARE result BIGINT DEFAULT 0;
    DECLARE i INT DEFAULT 1;

    -- 至少执行一次，然后检查条件
    REPEAT
        SET result = a + b;
        SET a = b;
        SET b = result;
        SET i = i + 1;
    UNTIL i > n
    END REPEAT;

    RETURN a;
END //
DELIMITER ;

游标

游标（Cursor）是数据库中用于从结果集中逐行检索数据的机制。游标允许你对查询结果集进行迭代，每次处理一行数据。这对于需要逐行处理大量数据的场景非常有用。

游标的语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 声明游标
-- cursor_name：游标的名称。
-- select_statement：关联的查询语句。
DECLARE cursor_name CURSOR FOR select_statement;

-- 打开游标
OPEN cursor_name;

-- 获取数据
-- variable_list：用于存储从游标中读取的数据的变量列表。
FETCH cursor_name INTO variable_list;

-- 关闭游标
CLOSE cursor_name;

使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
-- 假设我们有一个员工表 employees，表结构如下：
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- 编写一个存储过程，该过程使用游标遍历所有员工，并计算每个部门的总薪水。
DELIMITER //

CREATE PROCEDURE CalculateDepartmentSalaries()
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT FALSE;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(100);
    DECLARE emp_department VARCHAR(100);
    DECLARE emp_salary DECIMAL(10, 2);
    DECLARE total_salary DECIMAL(10, 2) DEFAULT 0;
    DECLARE current_department VARCHAR(100);

    -- 声明游标
    DECLARE cur CURSOR FOR SELECT id, name, department, salary FROM employees;

    -- CONTINUE HANDLER FOR NOT FOUND：当游标读取到最后一行时，设置 done 为 TRUE。
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 创建临时表存储结果
    CREATE TEMPORARY TABLE IF NOT EXISTS temp_salaries (
        department VARCHAR(100),
        total_salary DECIMAL(10, 2)
    );

    -- 打开游标
    OPEN cur;

    -- 读取数据
    read_loop: LOOP
        FETCH cur INTO emp_id, emp_name, emp_department, emp_salary;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 如果部门变化，插入当前部门的总薪水
        IF current_department IS NULL OR current_department != emp_department THEN
            IF current_department IS NOT NULL THEN
                INSERT INTO temp_salaries (department, total_salary) VALUES (current_department, total_salary);
                SET total_salary = 0;
            END IF;
            SET current_department = emp_department;
        END IF;

        -- 累加薪水
        SET total_salary = total_salary + emp_salary;
    END LOOP;

    -- 确保最后一个部门的总薪水也被插入到临时表中
    IF current_department IS NOT NULL THEN
        INSERT INTO temp_salaries (department, total_salary) VALUES (current_department, total_salary);
    END IF;

    -- 关闭游标
    CLOSE cur;

    -- 返回结果
    SELECT * FROM temp_salaries;
END //

DELIMITER ;

触发器

触发器是一种存储在数据库中的程序，它会在特定的表上执行INSERT、UPDATE或DELETE操作之前或之后自动执行。触发器可以用于强制业务规则、维护数据完整性和一致性等场景。

基本语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER //
-- trigger_name 是触发器的名称。
CREATE TRIGGER trigger_name
-- {BEFORE | AFTER} 指定触发器是在数据修改之前还是之后执行。
-- {INSERT | UPDATE | DELETE} 指定触发器响应哪种类型的数据修改操作。
-- table_name 是触发器关联的表名。
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
-- FOR EACH ROW 表明此触发器将为每一行受影响的数据执行一次。
FOR EACH ROW
-- BEGIN ... END; 之间是触发器的主体，即当触发条件满足时要执行的操作。
BEGIN
    -- 触发器主体
END;//
DELIMITER ;

使用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 假设我们有一个orders表用来存储订单信息，
-- 同时还有一个order_logs表用来记录对orders表的所有更新操作。

-- 订单表结构
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_amount DECIMAL(10, 2) NOT NULL
);

-- 订单日志表结构
CREATE TABLE order_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    old_value DECIMAL(10, 2),
    new_value DECIMAL(10, 2),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建一个触发器，在orders表上的任何更新操作后记录日志
DELIMITER //

CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    -- 插入日志条目
    INSERT INTO order_logs (order_id, action, old_value, new_value)
    VALUES (OLD.id, 'UPDATE', OLD.order_amount, NEW.order_amount);
END; //

DELIMITER ;

-- DELIMITER // 和 DELIMITER ; 用于更改语句结束符，这样可以在触发器中使用多行SQL语句。
-- OLD 关键字引用更新前的行数据。
-- NEW 关键字引用更新后的行数据。

查看删除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看触发器列表
SHOW TRIGGERS;

-- 查看创建信息：\G 格式化，可以不加
SHOW CREATE TRIGGER trigger_name\G;

-- 查看状态信息
SHOW TRIGGER STATUS LIKE 'trigger_name';

-- 查看详细信息
SELECT * FROM information_schema.TRIGGERS;

-- 删除
DROP TRIGGER IF EXISTS trigger_name;

错误处理

在 MySQL 存储过程中，可以定义条件处理程序（handler）来响应特定类型的错误或警告。当发生指定的条件时，处理程序将被执行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
-- DECLARE CONTINUE HANDLER ：用于定义当遇到指定的错误或条件时，存储过程应该继续执行后续的语句。
-- 创建一个存储过程，用于向 orders 表中插入订单。
-- 如果由于某些原因（例如，外键约束失败）导致插入失败，我们希望存储过程能够记录这个错误，并继续执行其他操作。
DELIMITER $$

CREATE PROCEDURE InsertOrder(IN order_id INT, IN customer_id INT, IN order_date DATE)
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' -- SQLSTATE '23000' 表示完整性约束违反
    BEGIN
        -- 记录错误信息
        INSERT INTO error_log (message) VALUES ('订单插入失败，可能是由于客户ID不存在');
    END;

    -- 尝试插入订单
    INSERT INTO orders (order_id, customer_id, order_date) VALUES (order_id, customer_id, order_date);

    -- 插入成功后，进行其他操作
    SELECT '订单已成功插入' AS Status;
END$$

DELIMITER ;


-- DECLARE EXIT HANDLER：用于定义当遇到指定的错误或条件时，存储过程应该立即停止执行，并退出存储过程。
-- 创建一个存储过程，用于更新 products 表中的产品价格。
-- 如果在更新过程中发生错误，我们希望立即停止存储过程的执行，并记录错误信息。
DELIMITER $$

CREATE PROCEDURE UpdateProductPrice(IN product_id INT, IN new_price DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION -- 对任何SQL异常都进行处理
    BEGIN
        -- 记录错误信息
        INSERT INTO error_log (message) VALUES ('更新产品价格时发生错误');
        -- 可以选择回滚事务
        ROLLBACK;
    END;

    START TRANSACTION;

    -- 尝试更新产品价格
    UPDATE products SET price = new_price WHERE id = product_id;

    -- 更新成功后，提交事务
    COMMIT;

    -- 返回成功消息
    SELECT '产品价格已成功更新' AS Status;
END$$

DELIMITER ;

存储引擎

MySQL 的存储引擎是其核心组件之一，负责数据的存储、检索和管理。不同的存储引擎具有不同的特性和适用场景。可以使用 SHOW ENGINES 指令查看存储引擎列表。

InnoDB

  • 事务支持：InnoDB 是 MySQL 的默认存储引擎，支持 ACID 事务，确保数据的一致性和可靠性。
  • 行级锁定：InnoDB 使用行级锁定，允许多个用户同时访问同一张表的不同行，提高了并发性能。
  • 外键支持：InnoDB 支持外键约束，可以维护表之间的引用完整性。
  • MVCC（多版本并发控制）：InnoDB 使用 MVCC 来实现高并发下的数据一致性和隔离性。
  • 崩溃恢复：InnoDB 具有崩溃恢复机制，可以通过重做日志（redo log）和回滚日志（undo log）恢复未完成的事务。
  • 适用场景：适用于需要高并发写入和事务处理的场景，如金融系统、电子商务平台等。

MyISAM

  • 不支持事务：MyISAM 不支持事务，因此在数据一致性方面不如 InnoDB。
  • 表级锁定：MyISAM 使用表级锁定，当一个用户在写入数据时，整个表会被锁定，影响并发性能。
  • 全文索引：MyISAM 支持全文索引，适用于需要全文搜索的应用。
  • 空间效率：MyISAM 对磁盘空间的使用效率较高，适合存储大量静态数据。
  • 适用场景：适用于读多写少的场景，如博客、论坛等。

MEMORY

  • 内存存储：Memory 存储引擎将所有数据存储在内存中，读取速度非常快。
  • 临时数据：由于数据存储在内存中，服务器重启后数据会丢失，适合存储临时数据。
  • 固定长度记录：Memory 存储引擎只支持固定长度的记录，不支持 BLOB 和 TEXT 类型。
  • 适用场景：适用于需要快速查询临时数据的场景，如缓存表、临时表等。

ARCHIVE

  • 压缩存储：Archive 存储引擎主要用于存储大量的归档数据，数据会被高度压缩，节省存储空间。
  • 只读操作：Archive 存储引擎不支持索引，只支持插入和选择操作，不支持更新和删除操作。
  • 适用场景：适用于需要长期保存大量历史数据的场景，如日志记录、审计记录等。

CSV

  • CSV 文件：CSV 存储引擎将数据存储在逗号分隔的文本文件中，方便与其他应用程序交换数据。
  • 简单易用：CSV 存储引擎不支持索引，性能较低，但简单易用。
  • 适用场景：适用于数据导入导出、数据交换等场景。

BLACKHOLE

  • 黑洞存储：Blackhole 存储引擎会丢弃所有插入的数据，但会记录二进制日志，常用于测试和日志记录。
  • 适用场景：适用于需要模拟数据插入但不需要实际存储数据的场景，如性能测试、日志记录等。

FEDERATED

  • 远程访问：Federated 存储引擎允许通过 MySQL 服务器访问远程 MySQL 服务器上的表，类似于分布式数据库。
  • 适用场景：适用于需要跨多个数据库服务器进行数据访问的场景。

PERFORMANCE_SCHEMA

  • Performance Schema 存储引擎用于收集数据库服务器性能信息。
  • 适用场景：主要为数据库管理员提供了一种监控数据库性能的方法。

NDB (Cluster)

  • 分布式存储：NDB 存储引擎支持分布式集群，允许多个节点同时进行读写操作，提供高可用性和水平扩展。
  • 内存存储：NDB 存储引擎将数据存储在内存中，支持持久化到磁盘。
  • 适用场景：适用于需要高可用性和高性能的分布式应用，如电信、金融等。

查看存储引擎信息

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
-- 查看存储引擎列表
SHOW ENGINES;

-- 查看默认存储引擎
SELECT @@default_storage_engine;

-- 查看当前数据库的存储引擎
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name';

-- 查看当前表使用的存储引擎
SHOW TABLE STATUS LIKE 'table_name';

-- 查看存储引擎的详细信息
SHOW ENGINE engine_name STATUS;

-- 检查存储引擎的变量
SHOW VARIABLES LIKE '%engine_name%';

-- 查看存储引擎的配置
SHOW VARIABLES LIKE 'have_%';

-- 查看存储引擎的性能指标
SHOW GLOBAL STATUS LIKE '%engine_name%';

-- 查看存储引擎的错误日志
SHOW ENGINE InnoDB LOGS;

存储引擎的使用

1
2
3
4
5
6
7
8
9
10
11
12
-- 修改默认的存储引擎
SET DEFAULT_STORAGE_ENGINE=engine_name;

-- 创建表时指定存储引擎
CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
) ENGINE=engine_name;

-- 修改表的存储引擎
ALTER TABLE table_name ENGINE=engine_name;

约束

约束是用于确保数据库中数据的准确性和可靠性的一系列规则。通过定义这些规则，可以控制数据如何插入、更新和删除，从而保证数据的一致性和完整性。

1
2
-- 查看表中的约束
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'table_name';

主键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 主键约束：PRIMARY KEY 关键字，用于声明主键约束。
-- 主键用于唯一标识表中的每一行。
-- 每个表只能有一个主键。
-- 主键列不允许有NULL值。
-- 主键可以由一个或多个字段组成，但组合起来必须能够唯一地标识每条记录。

-- 自动增长：AUTO_INCREMENT 关键字，表示自动递增。
-- 自动增长不是一种真正的约束，而是一种特殊的列属性。
-- 它使得每次向表中添加新记录时，该列的值都会自动增加。
-- 一个表最多只能有一个自增长列，通常与主键一起使用，但也可以单独使用。
CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，主键，自动增长
    name VARCHAR(100), -- 学生姓名，不能为空
    age INT, -- 学生年龄
    email VARCHAR(100) -- 学生邮箱
);

-- 复合主键约束
CREATE TABLE order_items (
    order_id INT NOT NULL, -- 订单ID，不能为空
    item_id INT NOT NULL, -- 商品ID，不能为空
    PRIMARY KEY (order_id, item_id) -- 复合主键，由order_id和item_id组成
);

外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 外键：FOREIGN KEY 关键字，用于声明外键约束。
-- 外键用于建立两个表之间的关系。
-- 外键字段通常对应于另一个表的主键。
-- 外键可以为空（NULL），但当它包含值时，这些值必须在关联的主表中存在。
-- 有助于维护参照完整性。
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY, -- 课程ID，主键，自动增长
    course_name VARCHAR(100) NOT NULL -- 课程名称，不能为空
);

-- enrollments 表中的 student_id 和 course_id 分别引用 students 表和 courses 表的主键。
CREATE TABLE enrollments (
    enrollment_id INT AUTO_INCREMENT PRIMARY KEY, -- 报名ID，主键，自动增长
    student_id INT, -- 学生ID
    course_id INT, -- 课程ID
    FOREIGN KEY (student_id) REFERENCES students(student_id), -- 外键，引用students表的student_id
    FOREIGN KEY (course_id) REFERENCES courses(course_id) -- 外键，引用courses表的course_id
);

非空约束

1
2
3
4
5
6
7
8
9
-- 非空约束：NOT NULL 关键字，用于声明非空约束。
-- 非空约束确保列中的值不能为NULL。
CREATE TABLE addresses (
    address_id INT AUTO_INCREMENT PRIMARY KEY, -- 地址ID，主键，自动增长
    street VARCHAR(100) NOT NULL, -- 街道，不能为空
    city VARCHAR(50) NOT NULL, -- 城市，不能为空
    state VARCHAR(50) NOT NULL, -- 州/省，不能为空
    postal_code VARCHAR(10) NOT NULL -- 邮政编码，不能为空
);

唯一性约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 唯一性：UNIQUE 关键字，用于声明唯一性约束。
-- 唯一性约束确保列中的所有值都是唯一的。
-- 可以应用于单个列或一组列。
-- 唯一性约束允许NULL值的存在。
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY, -- 用户ID，主键，自动增长
    username VARCHAR(50) UNIQUE NOT NULL, -- 用户名，唯一且不能为空
    email VARCHAR(100) UNIQUE -- 邮箱，唯一但可以为空
);

-- 复合唯一性约束
CREATE TABLE student_course (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 主键，自动增长
    student_id INT NOT NULL, -- 学生ID
    course_id INT NOT NULL, -- 课程ID
    UNIQUE KEY(student_id, course_id) -- 复合唯一性约束
);

默认值约束

1
2
3
4
5
6
7
8
9
10
-- 默认值约束：DEFAULT '该字段默认值' 关键字，用于声明默认值约束。
-- 默认值约束用于设置字段的默认值。
-- 当没有为该字段提供具体值时，将自动使用默认值。
-- CURRENT_DATE：获取当前时间的关键字。
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY, -- 订单ID，主键，自动增长
    customer_id INT NOT NULL, -- 客户ID，不能为空
    order_date DATE DEFAULT CURRENT_DATE, -- 订单日期，默认为当前日期
    status VARCHAR(20) DEFAULT 'Pending' -- 订单状态，默认为“待处理”
);

检查约束

1
2
3
4
5
6
7
8
9
-- 检查约束：CHECK (表达式) 关键字，用于声明默认值。
-- 检查约束用于限制列中可接受的值范围。
-- 在MySQL 8.0.16及更高版本中支持。
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY, -- 产品ID，主键，自动增长
    name VARCHAR(100) NOT NULL, -- 产品名称，不能为空
    price DECIMAL(10, 2) CHECK (price > 0), -- 价格，必须大于0
    quantity INT CHECK (quantity >= 0) -- 库存量，必须大于等于0
);

无符号

1
2
3
4
5
6
7
8
9
-- 无符号：UNSIGNED 关键字，用于指定一个数值类型的列不允许存储负数。
-- 使用 UNSIGNED 后，数值类型的取值范围会从负数变为更大的正数范围。
-- 例如，INT 类型的取值范围通常是 -2147483648 到 2147483647，而 UNSIGNED INT 的取值范围则是 0 到 4294967295。
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY, -- 产品ID，主键，自动增长，不允许负数
    name VARCHAR(100) NOT NULL, -- 产品名称，不能为空
    price DECIMAL(10, 2) UNSIGNED NOT NULL, -- 价格，不允许负数，不能为空
    quantity INT UNSIGNED NOT NULL -- 库存量，不允许负数，不能为空
);

零填充

1
2
3
4
5
6
7
8
9
-- 零填充: ZEROFILL 关键字，用于在数值类型列的值前面自动填充零，直到达到指定的宽度。
-- ZEROFILL 通常与 UNSIGNED 一起使用，但也可以单独使用。
-- 例如，如果定义了一个 INT(4) ZEROFILL 列，那么值 5 会被存储为 0005。
CREATE TABLE invoices (
    invoice_id INT(6) ZEROFILL AUTO_INCREMENT PRIMARY KEY, -- 发票ID，主键，自动增长，6位数字，不足6位时前面补0
    customer_id INT NOT NULL, -- 客户ID，不能为空
    amount DECIMAL(10, 2) NOT NULL, -- 金额，不能为空
    issue_date DATE NOT NULL -- 发票日期，不能为空
);

添加约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 添加主键约束
ALTER TABLE order_items ADD PRIMARY KEY (order_id, item_id);

-- 添加外键约束
ALTER TABLE order_items ADD CONSTRAINT fk_order_items_orders FOREIGN KEY (order_id) REFERENCES orders(order_id);

-- 添加唯一性约束
ALTER TABLE users ADD CONSTRAINT uk_users_email UNIQUE (email);

-- 添加非空约束
ALTER TABLE users MODIFY name VARCHAR(100) NOT NULL;

-- 添加默认值约束
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'Pending';

-- 添加检查约束
ALTER TABLE products ADD CONSTRAINT chk_products_price CHECK (price > 0);

删除约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 在删除约束时，需要知道约束的名称。
-- 如果不确定约束的名称，可以查看表的结构和约束信息。
SHOW CREATE TABLE table_name;

-- 删除主键约束
ALTER TABLE order_items DROP PRIMARY KEY;

-- 删除外键约束
ALTER TABLE order_items DROP FOREIGN KEY fk_order_items_orders;

-- 删除唯一性约束
ALTER TABLE users DROP INDEX uk_users_email;

-- 删除非空约束
ALTER TABLE users MODIFY name VARCHAR(100) NULL;

-- 删除默认值约束
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

-- 删除检查约束
ALTER TABLE products DROP CHECK chk_products_price;

索引

索引是存储引擎用于快速找到数据记录的一种数据结构。索引可以被看作是指向表中行的指针的集合。使用索引可以帮助数据库引擎快速定位到需要的数据，而不需要扫描整个表。

索引通过B树（B-tree）、哈希（Hash）等数据结构来实现。在B树中，每个节点包含一个键值和指向两个子节点的指针，这些子节点分别对应于小于或大于键值的数据。这种结构使得插入、删除和查找操作的时间复杂度保持在对数级别。

在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引。

名词概念

目录项（Directory Entry）

  • 定义：目录项是索引结构中的一部分，通常包含键值和指向数据的指针。在 B-Tree 索引中，目录项存在于非叶子节点中，用于指导搜索过程。
  • 键值：目录项中的键值用于确定数据的位置。
  • 指针：目录项中的指针指向下一个节点，可以是另一个目录项或叶子节点。

记录页（Record Page）

  • 定义：记录页是存储实际数据记录的物理单位。在数据库中，数据通常按页存储，每个页可以包含多个记录。
  • 固定大小：记录页通常具有固定的大小，以便于管理和优化 I/O 操作。
  • 存储记录：记录页中存储的是实际的数据记录，包括所有列的值。

聚簇索引（Clustered Index）

  • 定义：聚簇索引决定了数据在物理存储上的顺序。在聚簇索引中，数据行按索引键的顺序存储，每个表只能有一个聚簇索引。
  • 数据存储顺序：数据行按索引键的顺序存储。
  • 唯一性：聚簇索引通常是唯一的，除非指定了允许重复的键值。
  • 主键默认：InnoDB 存储引擎中，如果没有显式指定聚簇索引，主键会被用作聚簇索引。
1
2
3
4
5
6
CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (user_id) -- 聚簇索引
);

二级索引（Secondary Index）、辅助索引（Auxiliary Index）、非聚簇索引（Non-Clustered Index）

  • 定义：二级索引也称为、辅助索引、非聚簇索引，是在表的非聚簇索引列上创建的索引。二级索引的叶子节点存储的是聚簇索引的键值（通常是主键）。
  • 非数据存储顺序：二级索引不决定数据的物理存储顺序。
  • 叶子节点：二级索引的叶子节点存储的是聚簇索引的键值，而不是实际的数据行。
1
2
3
4
5
6
7
CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (user_id), -- 聚簇索引
    INDEX idx_username (username) -- 二级索引
);

联合索引（Composite Index）

  • 定义：联合索引是在多个列上创建的索引。联合索引的键值由多个列的值组合而成。
  • 多列组合：联合索引的键值由多个列的值组合而成。
  • 最左前缀原则：在使用联合索引时，查询条件必须包含索引的最左边的列，才能有效地利用索引。
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (order_id), -- 聚簇索引
    INDEX idx_customer_date (customer_id, order_date) -- 联合索引
);

-- 有效利用联合索引
SELECT * FROM orders WHERE customer_id = 1 AND order_date = '2023-10-01';

-- 无效利用联合索引
SELECT * FROM orders WHERE order_date = '2023-10-01';

覆盖索引（Covering Index）

  • 定义：覆盖索引是指查询的所有列都可以在索引中找到，而不需要回表查询实际的数据行。
  • 提高查询性能：避免了回表操作，减少了 I/O 操作次数。
1
2
3
4
5
6
7
8
9
10
CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (user_id),
    INDEX idx_username_email (username, email) -- 联合索引
);

-- 覆盖索引查询
SELECT username, email FROM users WHERE username = 'john';

回表查询（Back-Table Lookup）

  • 定义：回表查询是指在使用二级索引时，先通过二级索引找到聚簇索引的键值，然后再通过聚簇索引键值查找实际的数据行。
  • 额外 I/O 开销：增加了额外的 I/O 操作，可能会影响查询性能。
1
2
-- 回表查询
SELECT * FROM users WHERE username = 'john';

主键索引

主键是一种特殊类型的唯一索引，不允许有重复值和NULL值。每个表最多只能有一个主键，通常用来唯一标识表中的每一行记录。

1
2
3
4
5
6
CREATE TABLE users (
    user_id INT NOT NULL AUTO_INCREMENT, -- 定义主键列
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (user_id) -- 指定主键
);

唯一索引

唯一索引确保某列或多列中的所有值都是唯一的，但可以有NULL值。如果在一个列上创建了唯一索引，则该列不能有重复值。

1
2
3
4
5
6
CREATE TABLE emails (
    email_id INT NOT NULL AUTO_INCREMENT,
    email_address VARCHAR(100) NOT NULL,
    PRIMARY KEY (email_id),
    UNIQUE (email_address) -- 在email_address列上创建唯一索引
);

普通索引

普通索引是最基本的索引类型，没有唯一性的限制，可以包含重复值。它主要用于加速查询过程。

1
2
3
4
5
6
7
CREATE TABLE products (
    product_id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (product_id),
    INDEX idx_name (name) -- 在name列上创建普通索引
);

联合索引

联合索引是在多个列上创建的索引。MySQL 使用最左前缀原则来决定使用哪些列的索引。

1
2
3
4
5
6
7
CREATE TABLE orders (
    order_id INT NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (order_id),
    INDEX idx_customer_date (customer_id, order_date) -- 创建联合索引
);

全文索引

全文索引用于全文搜索，支持复杂的搜索功能，如短语匹配、近似匹配等。目前仅支持MyISAM和InnoDB存储引擎。

1
2
3
4
5
6
7
8
9
10
CREATE TABLE articles (
    article_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    PRIMARY KEY (article_id),
    FULLTEXT idx_content (title, content) -- 在content列上创建全文索引
);

-- 全文索引的查询方式
SELECT * FROM articles WHERE MATCH(title, content) AGAINST ('查询字符串');

空间索引

空间索引是专门为地理空间数据设计的索引，如 GIS 应用中的坐标数据。空间索引支持特定的空间查询，如范围查询和最近邻查询。

1
2
3
4
5
6
CREATE TABLE locations (
    location_id INT NOT NULL AUTO_INCREMENT,
    location_point POINT NOT NULL SRID 0,
    PRIMARY KEY (location_id),
    SPATIAL INDEX sp_idx_location (location_point) -- 在location_point列上创建空间索引
);

查看索引

1
2
3
4
5
-- 方式一
SHOW CREATE TABLE table_name;

-- 方式二
SHOW INDEX FROM table_name;

添加索引

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
-- 添加普通索引
-- 方式一
ALTER TABLE users ADD INDEX idx_age (age);
-- 方式二
CREATE INDEX idx_age ON users (age);

-- 添加组合索引
-- 方式一
ALTER TABLE users ADD INDEX idx_username_age (username, age);
-- 方式二
CREATE INDEX idx_username_age ON users (username, age);

-- 添加唯一索引
-- 方式一
ALTER TABLE users ADD UNIQUE INDEX idx_unique_email (email);
-- 方式二
CREATE UNIQUE INDEX idx_unique_email ON users (email);

-- 添加全文索引
-- 方式一
ALTER TABLE articles ADD FULLTEXT INDEX idx_fulltext_content (content);
-- 方式二
CREATE FULLTEXT INDEX idx_fulltext_content ON articles (content);

-- 添加空间索引
-- 方式一
ALTER TABLE locations ADD SPATIAL INDEX sp_idx_location (location_point);
-- 方式二
CREATE SPATIAL INDEX sp_idx_location ON locations (location_point);

删除索引

1
2
3
4
5
-- 方式一
ALTER TABLE users DROP INDEX idx_username;

-- 方式二
DROP INDEX idx_username ON users;

设计原则

索引的设计是数据库优化的关键环节之一。合理的索引设计可以显著提高查询性能，而不合理的索引则可能导致性能下降和额外的存储开销。

选择合适的列

  • 高选择性：选择性是指索引列中不同值的数量与总行数的比例。选择性越高，索引的效果越好。例如，user_id 列的选择性通常比 gender 列的选择性高。
  • 频繁查询的列：经常出现在 WHERE 子句中的列适合创建索引，因为这些列通常用于过滤数据。
  • 连接列：在 JOIN 操作中频繁使用的列应该考虑建索引，以加快连接操作的性能。

合理使用组合索引

  • 最左前缀原则：组合索引的使用遵循最左前缀原则，即查询条件必须包含索引的最左边的列。例如，对于索引 (a, b, c)，查询条件可以是 a、(a, b) 或 (a, b, c)，但不能是 b 或 (b, c)。
  • 覆盖索引：尽量使索引包含查询所需的所有列，这样可以避免回表操作，提高查询性能。

避免过度索引

  • 索引开销：每个索引都会增加存储空间的开销，并且在插入、更新和删除操作时需要维护索引，这会增加额外的开销。
  • 评估索引效果：定期评估索引的效果，删除那些使用频率低或效果不佳的索引。

使用适当的索引类型

  • 普通索引：适用于大多数场景。
  • 唯一索引：确保列的值唯一，适用于需要唯一性的列。
  • 全文索引：适用于全文搜索，支持复杂的搜索功能。
  • 空间索引：适用于地理空间数据的索引。
  • 哈希索引：适用于等值查询，不支持范围查询和排序。

考虑查询模式

  • 分析查询：使用 EXPLAIN 语句分析查询计划，了解查询的执行情况，找出性能瓶颈。
  • 优化查询：根据查询模式优化索引，确保索引能够有效支持查询。

定期维护索引

  • 定期分析和优化：定期使用 ANALYZE TABLE 和 OPTIMIZE TABLE 语句分析和优化表，确保索引的统计信息准确。
  • 监控索引使用情况：使用性能监控工具（如 MySQL 的 Performance Schema）监控索引的使用情况，及时发现和解决问题。

考虑事务和并发

  • 事务开销：在高并发环境下，频繁的索引维护操作可能会导致锁竞争，影响性能。
  • 批量操作：在进行大量数据插入或更新时，可以考虑暂时禁用索引，批量操作后再重建索引。

⭐⭐ 哪些情况适合创建索引

  • 字段的数值有唯一性的限制
  • 频繁作为 WHERE 查询条件的字段
  • 经常 GROUP BY 和 ORDER BY 的列
  • UPDATE、DELETE 的 WHERE 条件列
  • DISTINCT 字段需要创建索引
  • 使用列的类型小的创建索引
  • 使用字符串前缀创建索引
  • 区分度高(散列性高)的列适合作为索引
  • 使用最频繁的列放到联合索引的左侧
  • 在多个字段都要创建索引的情况下，联合索引优于单值索引

⭐⭐ 哪些情况不适合创建索引

  • 在 WHERE 中使用不到的字段，不要设置索引
  • 数据量小的表最好不要使用索引
  • 有大量重复数据的列上不要建立索引
  • 避免对经常更新的表创建过多的索引
  • 不建议用无序的值作为索引
  • 删除不再使用或者很少使用的索引
  • 不要定义冗余或重复的索引

事务

事务是一组数据库操作的集合，这些操作被视为一个单一的工作单元。事务中的所有操作必须全部成功完成，否则就全部不执行。这种“全有或全无”的特性确保了数据库的一致性。

事务的特性

  • 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么都做，要么都不做。如果事务在执行过程中发生错误，会被回滚到事务开始前的状态，好像这个事务从来没有执行过一样。
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态变到另一个一致性状态，保证数据库的完整性不受破坏。这意味着事务执行的结果必须是使数据库从一个合法状态转变为另一个合法状态，而不会导致数据损坏或不一致。
  • 隔离性（Isolation）：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，各个事务之间彼此隔离。事务的隔离性是通过锁和事务的并发控制来实现的。
  • 持久性（Durability）：一旦事务提交，则其所做的修改就会永久保存到数据库中，即使系统发生故障也不会丢失。

事务的状态

  • 活动（Active）：事务正在执行中。
  • 部分提交（Partially Committed）：事务中的所有操作已经完成，但还没有正式提交。
  • 失败（Failed）：事务在执行过程中遇到了错误，无法继续进行。
  • 中止（Aborted）：事务由于失败而被回滚，恢复到事务开始前的状态。
  • 提交（Committed）：事务成功完成并提交，所有更改永久保存到数据库中。

事务的操作

  • 开始事务：BEGIN TRANSACTION; 或 START TRANSACTION; 开始一个新的事务。
  • 提交事务：COMMIT; 提交事务，使所有更改永久保存到数据库中。
  • 回滚事务：ROLLBACK; 撤销事务中的所有更改，恢复到事务开始前的状态。
  • 设置保存点：SAVEPOINT savepoint_name; 在事务中设置一个保存点，以便在需要时可以部分回滚。
  • 回滚到保存点：ROLLBACK TO SAVEPOINT savepoint_name; 回滚到指定的保存点，撤销该保存点之后的操作。
  • 释放保存点：RELEASE SAVEPOINT savepoint_name; 删除指定的保存点。

事务的分类

  • 扁平事务：扁平事务是最简单的事务类型，由一系列顺序执行的数据库操作组成。这些操作作为一个整体提交或回滚。
  • 带有保存点的扁平事务：带有保存点的扁平事务是在扁平事务的基础上增加了保存点的功能。保存点允许事务在执行过程中设置一个临时的恢复点，可以在必要时部分回滚到该点。
  • 链式事务：链式事务是指一个事务的提交会自动启动另一个事务。这种方式常用于需要连续执行多个事务的场景，每个事务的成功提交会触发下一个事务的开始。
  • 嵌套事务：嵌套事务是指在一个事务内部可以启动另一个事务。每个嵌套事务都有自己的提交和回滚机制，但最终的提交或回滚会影响最外层的事务。
  • 分布式事务：分布式事务涉及多个数据库或多个系统之间的协调。分布式事务确保在多个资源管理器（如不同的数据库服务器）之间的一致性。常见的分布式事务协议包括两阶段提交（2PC）和三阶段提交（3PC）。

事务隔离级别

数据并发问题

脏写：脏写是指一个事务覆盖了另一个事务尚未提交的数据。这种情况会导致数据的不一致性和丢失。
产生原因：

  • 两个事务同时对同一数据项进行写操作。
  • 其中一个事务尚未提交，另一个事务就覆盖了它的数据。

脏读：脏读是指一个事务读取了另一个事务尚未提交的数据。这种情况会导致读取到不一致或错误的数据。
产生原因：

  • 一个事务读取了另一个事务尚未提交的数据。
  • 如果未提交的事务回滚，读取到的数据将不正确。

不可重复读：不可重复读是指在一个事务中多次读取同一数据，但由于其他事务的修改，读取到的数据不一致。
产生原因：

  • 一个事务在两次读取同一数据之间，另一个事务修改了该数据并提交。
  • 导致第一次读取和第二次读取的结果不同。

幻读：幻读是指在一个事务中多次执行同一查询，但由于其他事务插入了新数据，导致查询结果集发生变化。
产生原因：

  • 一个事务在两次执行同一查询之间，另一个事务插入了新的数据。
  • 导致第一次查询和第二次查询的结果集不同。

四种隔离级别

  • 读未提交（Read Uncommitted）：最低的隔离级别，允许脏读取，即一个事务可以读取另一个事务未提交的数据。
  • 读已提交（Read Committed）：不允许脏读取，但是允许不可重复读取。这意味着同一个事务内多次执行相同的查询，可能会得到不同的结果。
  • 可重复读（Repeatable Read）：这是 MySQL InnoDB 存储引擎的默认事务隔离级别。它确保了在同一个事务中多次执行相同的查询，将返回相同的结果集，即使另一个事务已经提交了新的数据。
  • 串行化（Serializable）：最高的隔离级别，完全阻止了脏读取、不可重复读取和幻读。但是，这可能导致大量的超时现象和锁竞争。

隔离级别的设置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查看全局隔离级别
-- 全局隔离级别影响所有新连接的默认隔离级别。
SELECT @@global.transaction_isolation;

-- 查看会话隔离级别
-- 会话隔离级别影响当前连接的隔离级别。
SELECT @@session.transaction_isolation;

-- 其中 ISOLATION_LEVEL 可以是以下值之一
-- READ UNCOMMITTED     读未提交
-- READ COMMITTED       读已提交
-- REPEATABLE READ      可重复读
-- SERIALIZABLE         序列化

-- 设置全局隔离级别
-- 设置全局隔离级别会影响所有新连接的默认隔离级别。
SET GLOBAL transaction_isolation = 'ISOLATION_LEVEL';

-- 设置会话隔离级别
-- 设置会话隔离级别只影响当前连接的隔离级别。
SET SESSION transaction_isolation = 'ISOLATION_LEVEL';

事务的使用

显式事务

显式事务是指在代码中明确地开始、提交或回滚事务。显式事务提供了对数据库操作的细粒度控制，确保了数据的一致性和可靠性。通过合理使用 BEGIN TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT 和 ROLLBACK TO SAVEPOINT 等语句，可以有效地管理复杂的业务逻辑。

⭐⭐ 基本的显式事务

1
2
3
4
5
6
7
8
9
10
11
-- 开始事务
START TRANSACTION;

-- 从账户 1 减少 100 元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 向账户 2 增加 100 元
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 提交事务
COMMIT;

⭐⭐ 带有错误处理的显式事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 开始事务
START TRANSACTION;

-- 从账户 1 减少 100 元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 模拟一个错误
IF (SOME_CONDITION) THEN
    ROLLBACK;
    -- 处理错误
    SELECT 'Error occurred, transaction rolled back.';
ELSE
    -- 向账户 2 增加 100 元
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

    -- 提交事务
    COMMIT;
END IF;

⭐⭐ 带有保存点的显式事务

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
-- 开始事务
START TRANSACTION;

-- 从账户 1 减少 100 元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 设置保存点
SAVEPOINT sp1;

-- 向账户 2 增加 100 元
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 模拟一个错误
IF (SOME_CONDITION) THEN
    -- 回滚到保存点
    ROLLBACK TO SAVEPOINT sp1;

    -- 恢复账户 1 的余额
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 1;

    -- 处理错误
    SELECT 'Error occurred, rolled back to savepoint.';
ELSE
    -- 提交事务
    COMMIT;
END IF;

-- 释放保存点
RELEASE SAVEPOINT sp1;

隐式事务

隐式事务是指数据库系统在某些情况下自动开始和管理事务，而不需要显式地使用 BEGIN TRANSACTION、COMMIT 或 ROLLBACK 语句。

通常在以下情况下发生：

  • 自动提交模式：当数据库连接处于自动提交模式时，每个单独的 SQL 语句都被视为一个独立的事务。执行完一个 SQL 语句后，数据库会自动提交该事务。
  • 特定的 SQL 语句：某些 SQL 语句会隐式地开始一个新事务，例如 CREATE TABLE、DROP TABLE、ALTER TABLE 等DDL语句。

默认情况下，MySQL 连接处于自动提交模式。

⭐⭐ 设置自动提交模式

1
2
3
4
5
6
7
8
-- 查看当前的自动提交模式
SELECT @@autocommit;

-- 开启自动提交模式
SET autocommit = 1;

-- 关闭自动提交模式
SET autocommit = 0;

⭐⭐ 开启自动提交模式

1
2
3
4
5
6
7
8
9
10
-- 开启自动提交模式
SET autocommit = 1;

-- 插入一条记录
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);

-- 查询记录
SELECT * FROM accounts WHERE account_id = 1;

-- 在这个例子中，INSERT 语句和 SELECT 语句分别被当作两个独立的事务处理。

⭐⭐ 关闭自动提交模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 关闭自动提交模式
SET autocommit = 0;

-- 插入一条记录
INSERT INTO accounts (account_id, balance) VALUES (1, 1000);

-- 更新一条记录
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;

-- 提交事务
COMMIT;

-- 查询记录
SELECT * FROM accounts WHERE account_id = 1;

-- 在这个例子中，INSERT 和 UPDATE 语句被组合成一个事务，只有在 COMMIT 语句执行后，更改才会永久保存到数据库中。

链式事务

在 MySQL 中，completion_type 是一个系统变量，用于控制事务提交后的行为。这个变量决定了在事务提交后，是否自动启动一个新的事务。这对于实现链式事务特别有用。

completion_type 可选值：

  • 0：默认值。事务提交或回滚后，不会自动开始新的事务。这与标准的 SQL 行为一致。
  • 1：事务提交或回滚后，自动开始一个新的事务。这类似于 Oracle 数据库的行为。
  • 2：事务提交或回滚后，释放当前的事务并关闭连接。这个值主要用于调试和测试。

⭐⭐ 设置变量

1
2
3
4
5
6
7
8
9
10
11
-- 查看全局级别变量
SELECT @@global.completion_type;

-- 查看会话级别变量
SELECT @@session.completion_type;

-- 全局设置
SET GLOBAL completion_type = 1;

-- 会话设置
SET SESSION completion_type = 1;

⭐⭐ 默认行为（completion_type = 0）

1
2
3
4
5
6
7
8
9
-- 默认情况下，事务提交后不会自动开始新的事务
SET SESSION completion_type = 0;

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- 此时没有新的事务自动开始

⭐⭐ 自动开始新的事务（completion_type = 1）

1
2
3
4
5
6
7
8
9
-- 设置会话的 completion_type 为 1
SET SESSION completion_type = 1;

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- 此时会自动开始一个新的事务

⭐⭐ 释放当前事务并关闭连接（completion_type = 2）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 设置会话的 completion_type 为 2
SET SESSION completion_type = 2;

-- 开始事务
START TRANSACTION;

-- 执行一些操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 提交事务
COMMIT;

-- 此时连接会被关闭

⭐⭐ 使用 completion_type 实现链式事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 设置会话的 completion_type 为 1
SET SESSION completion_type = 1;

-- 第一个事务
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- 第二个事务
-- 由于 completion_type 为 1，这里会自动开始一个新的事务
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 3;
COMMIT;

REDO 日志

REDO日志是一种物理日志，它记录了对数据库数据页的所有物理修改操作。REDO日志的主要目的是确保在系统崩溃后，能够恢复已经提交的事务，保证数据的一致性和持久性。

工作原理

日志记录:

  • 当事务对数据库进行修改时，InnoDB存储引擎不仅会修改内存中的数据页，还会生成相应的REDO日志记录。
  • 这些REDO日志记录包含足够的信息，以便在必要时重新执行这些修改操作。

日志写入:

  • REDO日志记录首先被写入到内存中的REDO日志缓冲区（Redo Log Buffer）。
  • 定期或在某些特定条件下（如事务提交），REDO日志缓冲区中的内容会被刷写到REDO日志文件中。

检查点（Checkpoint）:

  • 为了减少恢复时需要重放的日志量，InnoDB会定期创建检查点（Checkpoint）。
  • 检查点记录了哪些数据页已经被写入磁盘，因此在恢复时可以从检查点开始重放REDO日志，而不是从头开始。

日志重放:

  • 如果系统崩溃，InnoDB会在重启时通过重放REDO日志来恢复已经提交的事务。
  • 从最后一个检查点开始，重放所有未完成的REDO日志记录，确保所有已提交的事务都被正确应用到数据文件中。

日志结构

REDO日志文件:

  • REDO日志文件通常位于MySQL的数据目录下，文件名通常是ib_logfile0和ib_logfile1。
  • 可以配置多个REDO日志文件，形成一个循环使用的日志组（Log Group）。
  • 每个REDO日志文件的大小可以通过配置参数innodb_log_file_size来设置。

REDO日志缓冲区:

  • REDO日志缓冲区是一个内存区域，用于暂存即将写入REDO日志文件的记录。
  • 缓冲区的大小可以通过配置参数innodb_log_buffer_size来设置。
  • 较大的缓冲区可以减少磁盘I/O操作，提高性能，但也会增加系统崩溃时丢失数据的风险。

配置参数

innodb_log_file_size

  • 功能: 设置单个REDO日志文件的大小。
  • 默认值: 48M（具体版本可能有所不同）
  • 建议值: 根据系统的负载和磁盘I/O能力调整。通常设置为512M到1G之间。
  • 示例: innodb_log_file_size = 512M
  • 注释: 较大的日志文件可以减少日志切换的频率，但会增加恢复时间。

innodb_log_files_in_group

  • 功能: 设置REDO日志文件的数量。
  • 默认值: 2
  • 建议值: 通常保持默认值2即可。
  • 示例: innodb_log_files_in_group = 2
  • 注释: 多个日志文件可以提高系统的可用性，但在某些情况下可能会增加管理复杂度。

innodb_log_buffer_size

  • 功能: 设置REDO日志缓冲区的大小。
  • 默认值: 8M
  • 建议值: 根据系统的内存情况调整。通常设置为16M到64M之间。
  • 示例: innodb_log_buffer_size = 16M
  • 注释: 较大的缓冲区可以减少磁盘I/O操作，提高性能，但也会增加系统崩溃时丢失数据的风险。

innodb_flush_log_at_trx_commit

  • 功能: 控制事务提交时REDO日志的刷新策略。
  • 默认值: 1
  • 可选值:
    • 0: 每秒刷新一次日志到磁盘，不保证事务的持久性。
    • 1: 每次事务提交时都刷新日志到磁盘，保证事务的持久性。
    • 2: 每次事务提交时将日志写入操作系统缓存，每秒刷新到磁盘。
  • 建议值: 生产环境中通常设置为1，以确保事务的持久性。
  • 示例: innodb_flush_log_at_trx_commit = 1
  • 注释: 设置为0或2可以提高性能，但会牺牲数据的安全性。

innodb_checkpoint_age_target

  • 功能: 设置检查点的频率。
  • 默认值: 无
  • 建议值: 根据系统的负载和恢复时间需求调整。通常设置为1G左右。
  • 示例: innodb_checkpoint_age_target = 1G
  • 注释: 较高的值可以减少检查点的频率，但会增加恢复时间。

innodb_log_group_home_dir

  • 功能: 设置REDO日志文件的路径。
  • 默认值: MySQL数据目录
  • 建议值: 根据磁盘布局和性能需求调整。
  • 示例: innodb_log_group_home_dir = /var/lib/mysql/
  • 注释: 将日志文件放在高速磁盘上可以提高性能。

innodb_log_files_in_group_preallocate

  • 功能: 设置是否预分配REDO日志文件。
  • 默认值: OFF
  • 建议值: ON
  • 示例: innodb_log_files_in_group_preallocate = ON
  • 注释: 预分配日志文件可以减少文件碎片，提高性能。

可以使用 SET GLOBAL 命令设置配置参数；使用 SHOW VARIABLES LIKE 命令查看当前配置。

  • 示例：SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
  • 示例：SET GLOBAL innodb_flush_log_at_trx_commit=1;;

注意事项

优点:

  • 高性能: 通过将日志写入操作与数据写入操作分离，减少了磁盘I/O次数，提高了系统的整体性能。
  • 数据持久性: 确保在系统崩溃后，已经提交的事务不会丢失，增强了数据的安全性。

注意事项:

  • 日志文件大小: 合理设置REDO日志文件的大小非常重要。太小可能导致频繁的磁盘I/O操作，太大则会增加恢复时间。
  • 日志文件数量: 多个REDO日志文件可以提高系统的可用性，但在某些情况下可能会增加管理复杂度。

UNDO 日志

UNDO日志是一种逻辑日志，它记录了对数据库数据页的修改操作的逆向操作。UNDO日志的主要目的是确保在事务需要回滚时能够撤销已经进行的操作，同时支持多版本并发控制，以提高数据库的并发性能。

工作原理

日志记录:

  • 当事务对数据库进行修改时，InnoDB存储引擎不仅会修改内存中的数据页，还会生成相应的UNDO日志记录。
  • 这些UNDO日志记录包含了修改前的数据版本，以便在需要时恢复到修改前的状态。

回滚:

  • 如果事务在执行过程中出现错误需要回滚，可以通过UNDO日志恢复到事务开始前的状态。
  • 回滚操作会根据UNDO日志中的记录，撤销已经进行的修改操作，确保事务的一致性。

多版本并发控制（MVCC）:

  • UNDO日志还支持多版本并发控制，允许多个事务同时读取数据库的不同版本。
  • 当查询某个数据行时，如果该行被其他事务锁定，查询可以通过UNDO日志访问该行的一个较旧版本，而不必等待锁释放。
  • 这样可以实现非锁定读取，提高数据库的并发性能。

垃圾回收:

  • 事务提交后，UNDO日志记录不会立即删除，而是保留一段时间，直到所有可能需要这些记录的事务都完成了。
  • InnoDB有一个后台线程负责清理不再需要的UNDO日志记录，这个过程称为垃圾回收（Purge）。

日志结构

UNDO日志表空间:

  • UNDO日志记录存储在UNDO表空间中，这些表空间可以是共享的或独立的。
  • 共享UNDO表空间（Shared Undo Tablespaces）: 默认情况下，所有事务的UNDO日志记录都存储在同一个表空间中。
  • 独立UNDO表空间（Per-Tablespace Undo Tablespaces）: 从MySQL 8.0开始，可以为每个表创建独立的UNDO表空间。

配置参数

innodb_undo_tablespaces

  • 功能: 设置独立UNDO表空间的数量。
  • 默认值: 0（表示使用共享UNDO表空间）
  • 建议值: 根据系统的负载和并发需求调整。
  • 示例: innodb_undo_tablespaces = 2
  • 注释: 独立UNDO表空间可以提高并发性能，但会增加管理复杂度。

innodb_undo_directory

  • 功能: 设置UNDO表空间的路径。
  • 默认值: MySQL数据目录
  • 建议值: 根据磁盘布局和性能需求调整。
  • 示例: innodb_undo_directory = /var/lib/mysql/undo
  • 注释: 将UNDO表空间放在高速磁盘上可以提高性能。

innodb_undo_logs

  • 功能: 设置UNDO日志的数量（也称为回滚段的数量）。
  • 默认值: 128
  • 建议值: 根据系统的负载和并发需求调整。
  • 示例: innodb_undo_logs = 128
  • 注释: 较多的回滚段可以提高并发性能，但会增加内存和磁盘的使用。

innodb_purge_threads

  • 功能: 设置垃圾回收线程的数量。
  • 默认值: 4
  • 建议值: 根据系统的负载和并发需求调整。
  • 示例: innodb_purge_threads = 4
  • 注释: 较多的垃圾回收线程可以加快清理速度，但会增加CPU和I/O的负担。

注意事项

优点:

  • 回滚支持: 确保事务在出错时可以回滚到初始状态，保证数据的一致性。
  • 多版本并发控制: 支持非锁定读取，提高数据库的并发性能。
  • 垃圾回收: 自动清理不再需要的UNDO日志记录，减少存储空间的浪费。

注意事项:

  • 表空间管理: 独立UNDO表空间可以提高性能，但会增加管理和维护的复杂度。
  • 垃圾回收: 合理配置垃圾回收线程的数量，避免过多的CPU和I/O负担。
  • 存储空间: UNDO日志记录会占用一定的存储空间，特别是在长时间运行的事务或高并发环境下，需要注意监控和管理。

MVCC

MVCC（Multi-Version Concurrency Control）多版本并发控制，是数据库管理系统中用于处理并发事务的一种技术。MVCC允许数据库同时处理多个读写操作，而不需要对数据进行锁定，从而提高了系统的并发性能。MVCC的核心思想是在数据库中保存数据的多个版本，每个事务看到的是它开始时的数据版本，即使其他事务在该事务运行期间修改了数据。

基本原理

  • 多版本数据：
    • 每个数据项可以有多个版本，每个版本对应于某个事务开始时的数据状态。
    • 这意味着同一数据项可以在不同的时间点有不同的值。
  • 事务标识：
    • 每个事务都有一个唯一的事务ID（Transaction ID）。
    • 数据项的每个版本都与一个或多个事务ID相关联，以标识哪个事务创建了该版本。
  • 读取一致性：
    • 事务在读取数据时，会根据其开始时的快照（称为Read View）来确定应该读取哪个版本的数据。
    • 这确保了事务在其整个生命周期内看到的数据是一致的，即使其他事务对数据进行了修改。
  • 写入操作：
    • 当事务修改数据时，不会直接覆盖现有的数据版本，而是创建一个新的版本。
    • 新版本的数据会包含修改后的值以及创建该版本的事务ID。

具体实现

  1. 隐藏列：在InnoDB存储引擎中，每行数据包含两个隐藏的列。
    • DB_TRX_ID：记录了最后一次对该行进行插入或更新的事务ID。
    • DB_ROLL_PTR：指向一个回滚段（undo log），这个回滚段包含了该行之前的状态信息。
  2. 回滚段（Undo Log）：当事务修改数据时，InnoDB会将数据的旧版本写入到undo log中。
    用途：
    • 支持事务的回滚操作。
    • 提供给需要查看数据旧版本的事务使用。
  3. 读视图（Read View）：每当一个事务开始时，InnoDB会创建一个读视图，这个视图决定了该事务能够看到哪些数据版本。
    包含以下内容：
    • 创建read view时活跃的事务列表。
    • 最小和最大事务ID。
    • 事务ID的上下界。

工作流程

  1. 事务开始：
    • 事务开始时，InnoDB会创建一个读视图（Read View）。
    • 读视图包含事务开始时的系统状态，包括活跃事务的列表和事务ID的范围。
  2. 读取数据：
    • 事务在读取数据时，会根据读视图来确定应该读取哪个版本的数据。
    • 如果数据的版本在读视图的范围内且没有被未提交的事务修改，则该版本对当前事务可见。
  3. 写入数据：
    • 当事务修改数据时，不会直接覆盖现有数据，而是创建一个新的版本。
    • 新版本的数据会包含修改后的值以及创建该版本的事务ID。
    • 旧版本的数据会被保留，并通过回滚段（undo log）链接起来。
  4. 事务提交：
    • 事务提交时，其对数据的修改成为永久性的。
    • 其他事务在创建新的读视图时，可以看到这些已提交的更改。
  5. 事务回滚：
    • 事务回滚时，通过回滚段（undo log）恢复数据的旧版本。
    • 回滚操作会撤销事务对数据的所有修改。

使用示例

假设有一个简单的用户账户表users，我们可以通过以下步骤来演示MVCC的使用：

1
2
3
4
5
6
7
8
-- 表结构
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    balance DECIMAL(10,2) NOT NULL
);

INSERT INTO users (name, balance) VALUES ('Alice', 1000.00), ('Bob', 2000.00);

⭐⭐ 可重复读

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 修改隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务A
START TRANSACTION;
-- 第一次读取
SELECT balance FROM users WHERE name = 'Alice'; -- 返回1000.00
-- 执行事务B
-- 再次读取
SELECT balance FROM users WHERE name = 'Alice'; -- 仍然返回1000.00
COMMIT;

-- 事务B
START TRANSACTION;
UPDATE users SET balance = balance + 500 WHERE name = 'Alice';
COMMIT;

⭐⭐ 读已提交

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 修改隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务A
START TRANSACTION;
-- 第一次读取
SELECT balance FROM users WHERE name = 'Alice'; -- 返回1000.00
-- 执行事务B
-- 再次读取
SELECT balance FROM users WHERE name = 'Alice'; -- 返回1500.00
COMMIT;

-- 事务B
START TRANSACTION;
UPDATE users SET balance = balance + 500 WHERE name = 'Alice';
COMMIT;

锁

MySQL中的锁机制是数据库管理并发控制的重要组成部分，用于保证数据的一致性和完整性。在多用户环境中，当多个事务同时访问同一资源时，如果没有适当的锁机制，可能会导致数据的不一致或损坏。

锁的类型

表级锁（Table-Level Locking）

  • 定义：表级锁是最粗粒度的锁，锁定整个表。当一个事务获得对某个表的写入锁后，其他事务不能对该表进行读或写操作，直到该事务释放锁。如果一个事务获得了对某个表的读取锁，则其他事务只能对该表进行读取操作，而不能进行写入操作。
  • 优点：
    • 实现简单，资源消耗较少。
    • 锁管理开销小，加锁和解锁速度快。
  • 缺点：
    • 并发度低，可能导致严重的性能瓶颈，尤其是在高并发环境下。
    • 容易引发死锁。
  • 适用场景：
    • 数据量不大，对并发要求不高的应用。
    • 适合批量操作或维护任务，如备份、重建索引等。

行级锁（Row-Level Locking）

  • 定义：行级锁是最细粒度的锁，锁定表中的某一行记录。允许多个事务同时操作不同行的数据，但如果多个事务尝试操作同一行数据，则会互相阻塞。
  • 优点：
    • 并发度高，可以大大提高多用户环境下数据库的处理能力。
    • 能够有效减少锁冲突，提高系统的吞吐量。
  • 缺点：
    • 实现复杂，消耗更多的内存资源。
    • 锁管理开销大，加锁和解锁速度相对较慢。
  • 适用场景：
    • 高并发的环境，尤其是数据量大且并发需求高的场景。
    • 适合需要频繁更新数据的业务场景。

页级锁（Page-Level Locking）

  • 定义：页级锁介于表级锁和行级锁之间，锁定表中的某一页（通常包含多行记录）。页级锁将表划分为若干个页面，每个页面可以被独立锁定。这意味着在同一个表上，不同页面之间的操作不会相互阻塞。
  • 优点：
    • 比表级锁提供了更高的并发度，同时又不像行级锁那样消耗大量资源。
    • 锁管理开销适中，性能较好。
  • 缺点：
    • 并发度不如行级锁高。
    • 如果一个页面内的数据量较大，可能会导致较高的锁争用。
  • 适用场景：
    • 中等规模的应用，可以平衡并发性和资源消耗。
    • 适合数据量较大但并发需求不是特别高的场景。

锁模式

共享锁（Shared Locks, S）

  • 定义：共享锁也称为读锁，允许多个事务同时读取同一数据，但不允许任何事务写入数据。
  • 特点：
    • 多个事务可以同时持有对同一数据项的共享锁。
    • 在有共享锁存在的情况下，任何事务都不能获得排他锁。
  • 用途：用于确保数据在读取过程中不会被其他事务修改，适用于读多写少的场景。

排他锁（Exclusive Locks, X）

  • 定义：排他锁也称为写锁，如果一个事务获得了对数据的排他锁，那么其他任何事务都不能再对该数据加任何类型的锁，无论是共享锁还是排他锁。
  • 特点：
    • 只有一个事务可以持有对某一数据项的排他锁。
    • 排他锁确保了数据在修改过程中不会被其他事务读取或修改。
  • 用途：用于确保数据在修改过程中的一致性，适用于需要频繁更新数据的场景。

意向锁（Intention Locks）

  • 定义：意向锁是表级别的锁，用来表示事务希望在表中的某些行上加锁。意向锁分为两种：
    • 意向共享锁（Intention Shared Locks, IS）：表示事务希望在表中的某些行上加共享锁。
    • 意向排他锁（Intention Exclusive Locks, IX）：表示事务希望在表中的某些行上加排他锁。
  • 特点：
    • 意向锁主要用于解决死锁检测和避免的问题，确保在多层锁结构中的一致性。
    • 意向锁是隐式加锁的，通常不需要显式指定。
  • 用途：确保在多层锁结构中的一致性，防止死锁。

间隙锁（Gap Locks）

  • 定义：间隙锁锁定的是索引记录之间的“间隙”或第一个索引记录之前、最后一个索引记录之后的范围。间隙锁的主要目的是防止幻读（Phantom Reads），即防止其他事务在这些间隙中插入新的记录。
  • 特点：
    • 间隙锁是InnoDB存储引擎在可重复读（Repeatable Read）隔离级别下自动使用的。
    • 间隙锁可以防止其他事务在这些间隙中插入新记录。
  • 用途：防止幻读，确保数据的一致性。

临键锁（Next-Key Locks）

  • 定义：临键锁是行锁和间隙锁的结合体，它不仅锁定索引记录本身，还锁定索引记录之前的间隙。临键锁可以防止其他事务在当前记录之前插入新记录，也可以防止其他事务修改当前记录。
  • 特点：
    • 临键锁是InnoDB存储引擎在可重复读（Repeatable Read）隔离级别下自动使用的。
    • 临键锁比单纯的间隙锁更强大，因为它同时锁定了索引记录和记录之前的间隙。
  • 用途：防止幻读，确保数据的一致性。

自增锁（Auto-Increment Locks）

  • 定义：自增锁用于控制自增列（AUTO_INCREMENT）的分配。InnoDB存储引擎在插入新记录时会使用自增锁来确保自增值的唯一性和连续性。
  • 特点：
    • 自增锁是自动管理的，通常不需要显式指定。
    • 自增锁可以防止多个事务同时插入新记录时出现自增值冲突。
  • 用途：确保自增列的唯一性和连续性。

乐观锁：

  • 定义：乐观锁假设数据在大多数情况下不会被修改，因此在读取数据时不加锁，而在提交更新时检查数据是否被其他事务修改过。
  • 实现方式：通常通过数据版本（Version）控制来实现，即为数据行增加一个版本标识。
  • 特点：适用于读多写少的场景，可以提高并发性能。

悲观锁：

  • 定义：悲观锁假设数据在大多数情况下会被其他事务修改，因此在读取数据时就加锁，以确保数据的一致性。
  • 实现方式：通过共享锁和排他锁来实现。
  • 特点：适用于写多读少的场景，可以确保数据的一致性。

锁的兼容性

共享锁（S）与排他锁（X）

  • 一个事务持有共享锁时，其他事务可以再加共享锁，但不能加排他锁。
  • 一个事务持有排他锁时，其他事务既不能加共享锁，也不能加排他锁。

意向锁（IS/IX）

  • 一个事务持有意向共享锁时，其他事务可以再加意向共享锁，但不能加意向排他锁。
  • 一个事务持有意向排他锁时，其他事务既不能加意向共享锁，也不能加意向排他锁。

间隙锁（Gap Locks）和临键锁（Next-Key Locks）

  • 一个事务持有间隙锁时，其他事务可以再加间隙锁或临键锁。
  • 一个事务持有临键锁时，其他事务可以再加间隙锁或临键锁。

意向锁（IS/IX） 与 共享锁（S）/排他锁（X） 的兼容性：

  • 一个事务持有意向共享锁时，其他事务可以再加共享锁，但不能加排他锁。
  • 一个事务持有意向排他锁时，其他事务既不能加共享锁，也不能加排他锁。

意向锁（IS/IX） 之间的兼容性：

  • 一个事务持有意向共享锁时，其他事务可以再加意向共享锁，但不能加意向排他锁。
  • 一个事务持有意向排他锁时，其他事务既不能加意向共享锁，也不能加意向排他锁。

行级锁与页级锁

  • 行级锁（S/X） 与 页级锁 之间的兼容性取决于具体的存储引擎实现。通常情况下，页级锁是行级锁的扩展，其兼容性规则与行级锁类似。

乐观锁与悲观锁

  • 乐观锁和悲观锁是不同的并发控制策略，通常不会在同一事务中混合使用。乐观锁适用于读多写少的场景，悲观锁适用于写多读少的场景。

锁策略选择

并发需求

  • 高并发读多写少：在这种场景下，读取操作远多于写入操作，且写入操作相对较少。使用乐观锁可以显著提高系统的并发性能，因为乐观锁在读取数据时不加锁，只有在提交更新时才检查数据是否被修改。
    • 例子：电子商务网站的商品详情页，用户频繁浏览商品信息，但下单操作较少。
  • 高并发写多读少：在这种场景下，写入操作频繁，且多个事务可能同时修改同一数据。使用悲观锁可以确保数据的一致性，因为在读取数据时就加锁，防止其他事务修改数据。
    • 例子：银行转账系统，多个用户可能同时进行转账操作。

数据一致性要求

  • 数据一致性要求极高：在金融、医疗等行业中，对数据的一致性要求非常高，不允许出现任何的数据不一致或脏读现象。在这种情况下，使用悲观锁可以确保数据在任一时刻只被一个事务访问和修改。
    • 例子：证券交易系统，必须确保交易数据的绝对准确性。
  • 数据一致性要求较高但可以容忍一定程度的失败：在一些业务场景中，虽然数据一致性很重要，但可以容忍一定程度的失败（例如，通过重试或其他补偿机制来恢复）。在这种情况下，使用乐观锁可以提高系统的并发性能。
    • 例子：在线购物车系统，用户频繁添加和删除商品，但系统可以通过重试来处理冲突。

事务执行时间

  • 事务执行时间较短：如果事务的执行时间很短，使用悲观锁可以有效地保护数据，因为锁的持有时间较短，对其他事务的影响较小。
    • 例子：用户登录系统，验证用户名和密码的操作通常很快完成。
  • 事务执行时间较长：如果事务的执行时间较长，使用悲观锁可能会导致资源浪费和性能下降。在这种情况下，使用乐观锁可以减少锁的持有时间，提高系统的并发性能。
    • 例子：数据迁移或批处理任务，事务可能需要较长时间才能完成。

冲突频率

  • 冲突频率低：如果数据更新操作之间的冲突较少，即多个事务或线程同时更新同一份数据的概率较低，那么使用乐观锁可以提高系统的并发性能。
    • 例子：新闻发布系统，新闻文章的发布和修改操作较少。
  • 冲突频率高：如果数据更新操作之间的冲突较多，即多个事务或线程同时更新同一份数据的概率较高，那么使用悲观锁可以确保数据的一致性。
    • 例子：库存管理系统，多个用户可能同时修改库存数量。

系统复杂性

  • 系统复杂性低：如果系统的复杂性较低，使用悲观锁可以简化锁的管理和实现。
    • 例子：小型企业内部管理系统，业务逻辑相对简单。
  • 系统复杂性高：如果系统的复杂性较高，使用乐观锁可以减少锁的竞争和死锁的风险，提高系统的稳定性和可用性。
    • 例子：大型电商平台，业务逻辑复杂，涉及多个模块和功能。

死锁处理

MySQL的InnoDB存储引擎提供了自动检测和解除死锁的功能。

死锁检测：

  • InnoDB会定期检查事务之间的等待关系，如果发现死锁，会选择一个事务进行回滚。
  • 可以通过配置参数innodb_deadlock_detect来控制是否启用死锁检测。默认值为ON，表示启用死锁检测。

死锁解除：

  • 一旦检测到死锁，InnoDB会选择一个事务进行回滚，解除死锁。
  • 回滚的事务会收到错误代码1213 (ER_LOCK_DEADLOCK)，表示发生了死锁。

超时设置：

  • 可以通过配置参数innodb_lock_wait_timeout来设置事务等待锁的最大超时时间。如果事务在规定时间内未能获得锁，将会被回滚。
  • 默认值为50秒，可以根据具体需求调整。

使用示例

共享锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 假设有一个表 orders，我们希望在一个事务中读取订单信息，但不希望其他事务在这段时间内修改这些订单信息。

START TRANSACTION;

-- 加共享锁
SELECT * FROM orders WHERE order_id = 1234 LOCK IN SHARE MODE;

-- 进行业务逻辑处理
-- ...

COMMIT;

-- LOCK IN SHARE MODE：在读取数据时加上共享锁，允许多个事务同时读取同一数据，但不允许其他事务修改这些数据。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据不会被其他事务修改。

排他锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 假设有一个表 products，我们希望在一个事务中更新产品的库存信息。

START TRANSACTION;

-- 加排他锁
SELECT * FROM products WHERE product_id = 101 FOR UPDATE;

-- 更新库存
UPDATE products SET stock = stock - 1 WHERE product_id = 101;

COMMIT;

-- FOR UPDATE：在读取数据时加上排他锁，确保其他事务不能读取或修改这些数据，直到当前事务提交或回滚。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据不会被其他事务读取或修改。

意向锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 假设有一个表 customers，我们希望在一个事务中对某些客户的信息进行操作。

START TRANSACTION;

-- 获取表的意向共享锁
LOCK TABLES customers READ;

-- 读取客户信息
SELECT * FROM customers WHERE customer_id = 1001;

-- 释放锁
UNLOCK TABLES;

COMMIT;

-- LOCK TABLES customers READ：获取表的意向共享锁，允许多个事务同时读取表中的数据，但不允许其他事务修改这些数据。
-- UNLOCK TABLES：释放表的意向共享锁。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据不会被其他事务修改。

间隙锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 假设有一个表 reservations，我们希望在一个事务中预订某个时间段内的房间。

START TRANSACTION;

-- 加排他锁，同时锁定间隙
SELECT * FROM reservations WHERE room_id = 101 AND reservation_time BETWEEN '2023-10-01' AND '2023-10-07' FOR UPDATE;

-- 插入新的预订
INSERT INTO reservations (room_id, reservation_time) VALUES (101, '2023-10-02');

COMMIT;

-- FOR UPDATE：在读取数据时加上排他锁，同时锁定索引记录之间的间隙，防止其他事务在这些间隙中插入新记录。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据不会被其他事务读取或修改。

临键锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 假设有一个表 transactions，我们希望在一个事务中处理某个账户的交易记录。

START TRANSACTION;

-- 加临键锁
SELECT * FROM transactions WHERE account_id = 1234 AND transaction_date >= '2023-10-01' FOR UPDATE;

-- 插入新的交易记录
INSERT INTO transactions (account_id, transaction_date, amount) VALUES (1234, '2023-10-02', 100);

COMMIT;

-- FOR UPDATE：在读取数据时加上排他锁，同时锁定索引记录及其之前的间隙，防止其他事务插入新记录或修改当前记录。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据不会被其他事务读取或修改。

乐观锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 假设有一个表 users，我们希望在一个事务中更新用户的余额。

START TRANSACTION;

-- 读取用户信息
SELECT * FROM users WHERE user_id = 1001 FOR UPDATE;

-- 更新用户余额
UPDATE users SET balance = balance + 100, version = version + 1 WHERE user_id = 1001 AND version = 1;

-- 检查更新是否成功
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

-- FOR UPDATE：在读取数据时加上排他锁，确保其他事务不能读取或修改这些数据。
-- version = version + 1：使用版本号机制实现乐观锁，确保在更新数据时检查版本号是否一致。
-- ROW_COUNT()：检查更新操作影响的行数，如果为0，说明版本号不一致，回滚事务。
-- START TRANSACTION 和 COMMIT 或 ROLLBACK：定义了一个事务的开始和结束，确保在这段事务中，数据的一致性。

悲观锁

⭐⭐ 单行数据的悲观锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 假设有一个表 accounts，我们希望在一个事务中更新某个账户的余额。

START TRANSACTION;

-- 加排他锁
SELECT balance FROM accounts WHERE account_id = 1001 FOR UPDATE;

-- 更新余额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001;

COMMIT;

-- FOR UPDATE：在读取数据时加上排他锁，确保其他事务不能读取或修改这些数据，直到当前事务提交或回滚。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据的一致性。

⭐⭐ 多行数据的悲观锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 假设有一个表 orders，我们希望在一个事务中更新多个订单的状态。

START TRANSACTION;

-- 加排他锁
SELECT * FROM orders WHERE status = 'processing' FOR UPDATE;

-- 更新订单状态
UPDATE orders SET status = 'completed' WHERE status = 'processing';

COMMIT;

-- FOR UPDATE：在读取数据时加上排他锁，确保其他事务不能读取或修改这些数据，直到当前事务提交或回滚。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据的一致性。

表级锁

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 假设有一个表 customers，我们希望在一个事务中对某些客户的信息进行操作。

START TRANSACTION;

-- 获取表的意向排他锁
LOCK TABLES customers WRITE;

-- 读取客户信息
SELECT * FROM customers WHERE customer_id = 1001;

-- 更新客户信息
UPDATE customers SET email = 'new_email@example.com' WHERE customer_id = 1001;

-- 释放锁
UNLOCK TABLES;

COMMIT;

-- LOCK TABLES customers WRITE：获取表的意向排他锁，确保其他事务不能读取或修改表中的数据。
-- UNLOCK TABLES：释放表的意向排他锁。
-- START TRANSACTION 和 COMMIT：定义了一个事务的开始和结束，确保在这段事务中，数据的一致性。

数据库设计

名词定义

  • 超键：能唯一标识元组的属性集叫做超键。
  • 候选键：如果超键不包括多余的属性，那么这个超键就是候选键。
  • 主键：用户可以从候选键中选择一个作为主键。
  • 外键：如果数据表 R1 中的某属性集不是 R1 的主键，而是另一个数据表 R2 的主键，那么这个属性集就是数据表 R1 的外键。
  • 主属性：包含在任一候选键中的属性称为主属性。
  • 非主属性：与主属性相对，指的是不包含在任何一个候选键中的属性。

第一范式

第一范式要求每个表的每一列都是原子性的，即不可再分。换句话说，每个字段只能包含单一值，不能包含多个值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 假设有一个学生选课表 student_courses，初始设计如下：
CREATE TABLE student_courses (
    student_id INT,
    course_ids VARCHAR(255) -- 例如 "1,2,3"
);

-- 这种设计违反了1NF，因为 course_ids 字段包含多个值。

-- 正确的设计应该是：
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

-- 确保每个字段都是原子性的，不可再分。
-- 消除重复组，将多值字段拆分为单独的表。

第二范式

第二范式要求表必须满足1NF，并且所有非主键字段完全依赖于主键。也就是说，非主键字段不能部分依赖于主键的一部分。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- 假设有一个订单表 orders，初始设计如下：
CREATE TABLE orders (
    order_id INT,
    product_id INT,
    customer_id INT,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)
);

-- 在这个设计中，product_name 和 product_price 依赖于 product_id 而不是整个主键 (order_id, product_id)，因此违反了2NF。

-- 正确的设计应该是将产品信息分离到另一个表中：
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN_KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100),
    product_price DECIMAL(10, 2)
);

-- 确保表满足1NF。
-- 消除非主键字段对主键的部分依赖，将相关字段分离到新的表中。

第三范式

第三范式要求表必须满足2NF，并且所有非主键字段不传递依赖于主键。也就是说，非主键字段不能依赖于其他非主键字段。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 假设有一个员工表 employees，初始设计如下：
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    department_name VARCHAR(100),
    manager_id INT,
    manager_name VARCHAR(100)
);

-- 在这个设计中，department_name 依赖于 department_id，manager_name 依赖于 manager_id，因此违反了3NF。

-- 正确的设计应该是将部门和经理信息分离到单独的表中：
CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    department_id INT,
    manager_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id),
    FOREIGN KEY (manager_id) REFERENCES managers(manager_id)
);

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE managers (
    manager_id INT AUTO_INCREMENT PRIMARY KEY,
    manager_name VARCHAR(100)
);

-- 确保表满足2NF。
-- 消除非主键字段对主键的传递依赖，将相关字段分离到新的表中。

巴斯-科德范式

巴斯-科德范式（BCNF）是在第三范式（3NF）的基础上进一步消除某些类型的依赖关系。BCNF要求每个决定因素（非平凡函数依赖的左部）都必须是候选键。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 假设有一个关系模式 Courses，包含以下字段和函数依赖：
CREATE TABLE Courses (
    CourseID INT,
    InstructorID INT,
    DepartmentID INT,
    PRIMARY KEY (CourseID, InstructorID)
);

-- 函数依赖：
-- 1. CourseID → DepartmentID
-- 2. InstructorID → DepartmentID

-- 在这个关系模式中，CourseID 和 InstructorID 都是候选键的一部分，但它们各自都能决定 DepartmentID。
-- 这违反了BCNF，因为 CourseID 和 InstructorID 都不是超键。

-- 正确的设计应该是将 DepartmentID 分离到一个新的表中：
CREATE TABLE Courses (
    CourseID INT,
    InstructorID INT,
    PRIMARY KEY (CourseID, InstructorID),
    FOREIGN KEY (CourseID) REFERENCES CourseDepartments(CourseID),
    FOREIGN KEY (InstructorID) REFERENCES InstructorDepartments(InstructorID)
);

CREATE TABLE CourseDepartments (
    CourseID INT PRIMARY KEY,
    DepartmentID INT
);

CREATE TABLE InstructorDepartments (
    InstructorID INT PRIMARY KEY,
    DepartmentID INT
);

-- BCNF：消除非平凡函数依赖中的非超键决定因素。

第四范式

第四范式（4NF）进一步消除了多值依赖。4NF要求关系模式中不存在非平凡的多值依赖。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
-- 假设有一个关系模式 Employees，包含以下字段和多值依赖：
CREATE TABLE Employees (
    EmployeeID INT,
    ProjectID INT,
    Skill VARCHAR(50),
    PRIMARY KEY (EmployeeID, ProjectID, Skill)
);

-- 多值依赖：
-- 1. EmployeeID →→ ProjectID
-- 2. EmployeeID →→ Skill

-- 在这个关系模式中，EmployeeID 决定了多个 ProjectID 和多个 Skill，这违反了4NF。

-- 正确的设计应该是将 ProjectID 和 Skill 分离到新的表中：
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY
);

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    PRIMARY KEY (EmployeeID, ProjectID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

CREATE TABLE EmployeeSkills (
    EmployeeID INT,
    Skill VARCHAR(50),
    PRIMARY KEY (EmployeeID, Skill),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

-- 4NF：消除非平凡的多值依赖。

第五范式

第五范式（5NF）或完美范式（Project-Join Normal Form, PJNF）进一步消除了连接依赖。5NF要求关系模式中不存在非平凡的连接依赖。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 假设有一个关系模式 SuppliersPartsProjects，包含以下字段和连接依赖：
CREATE TABLE SuppliersPartsProjects (
    SupplierID INT,
    PartID INT,
    ProjectID INT,
    PRIMARY KEY (SupplierID, PartID, ProjectID)
);

-- 连接依赖：
-- 1. (SupplierID, PartID) → (SupplierID, ProjectID)
-- 2. (SupplierID, ProjectID) → (PartID, ProjectID)
-- 3. (PartID, ProjectID) → (SupplierID, PartID)

-- 在这个关系模式中，存在连接依赖，违反了5NF。

-- 正确的设计应该是将 SuppliersPartsProjects 分解为三个独立的表：
CREATE TABLE SuppliersParts (
    SupplierID INT,
    PartID INT,
    PRIMARY KEY (SupplierID, PartID)
);

CREATE TABLE SuppliersProjects (
    SupplierID INT,
    ProjectID INT,
    PRIMARY KEY (SupplierID, ProjectID)
);

CREATE TABLE PartsProjects (
    PartID INT,
    ProjectID INT,
    PRIMARY KEY (PartID, ProjectID)
);

-- 5NF：消除非平凡的连接依赖。

反范式

反范式（Denormalization）是指有意地违反数据库规范化原则，以提高查询性能和简化某些操作。反范式通过在某些表中重复数据或预先计算结果，来优化读取操作和提高查询速度。

应用场景

  • 提高查询性能：通过减少表连接操作，加快查询速度。
  • 简化查询：减少复杂的联接和子查询，使查询更简单、更易读。
  • 减少锁争用：减少对多个表的并发访问，降低锁争用。
  • 提高数据仓库性能：在数据仓库中，反范式常用于预聚合和预计算，以加速报表生成。

常见技术

  • 冗余数据：在多个表中存储相同的数据。
  • 派生数据：存储预先计算的结果，如总计、平均值等。
  • 物化视图：创建物理存储的视图，以提高查询性能。
  • 嵌套表：在一个表中嵌套另一个表的数据。

应用示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- 正规化的表结构
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

CREATE TABLE OrderItems (
    OrderItemID INT AUTO_INCREMENT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);

-- 反范式的表结构
-- 冗余数据：在 OrderItems 表中存储产品的名称和价格，而不是通过 ProductID 进行联接。
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

CREATE TABLE OrderItems (
    OrderItemID INT AUTO_INCREMENT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    ProductName VARCHAR(100), -- 冗余数据
    Price DECIMAL(10, 2),     -- 冗余数据
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

-- 派生数据：在 Orders 表中存储订单的总金额。
CREATE TABLE Orders (
    OrderID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2) -- 派生数据
);

CREATE TABLE OrderItems (
    OrderItemID INT AUTO_INCREMENT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2),
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);

-- 可创建视图
-- 创建一个视图来存储订单和订单详情的汇总数据。
CREATE MATERIALIZED VIEW OrderSummary AS
SELECT 
    o.OrderID,
    o.CustomerID,
    o.OrderDate,
    SUM(oi.Quantity * oi.Price) AS TotalAmount
FROM 
    Orders o
JOIN 
    OrderItems oi ON o.OrderID = oi.OrderID
GROUP BY 
    o.OrderID, o.CustomerID, o.OrderDate;

注意事项

  • 数据一致性：反范式可能导致数据冗余和不一致，需要额外的机制（如触发器、事务）来确保数据的一致性。
  • 维护成本：反范式增加了数据更新的复杂性，需要更多的维护工作。
  • 存储成本：冗余数据会增加存储空间的使用。
  • 适用场景：反范式适用于读多写少的场景，如数据仓库、报表系统等。

ER模型

实体-关系模型（Entity-Relationship Model，简称ER模型）是一种用于描述数据库中数据和数据之间关系的概念模型。ER模型由Peter Chen在1976年提出，广泛应用于数据库设计的初期阶段，帮助设计师和开发者清晰地表达和理解数据结构及其关系。

模型的基本元素

实体（Entity）：实体是现实世界中可以区分的对象或概念。在数据库中，实体通常对应一个表。例如：学生、课程、订单等。

  • 强实体：强实体是具有自己独立主键的实体，其存在不依赖于其他实体。强实体的主键是唯一的，可以唯一标识该实体的每个实例。
  • 弱实体：弱实体是没有自己独立主键的实体，其存在依赖于另一个实体（称为标识关系）。弱实体的主键是由其标识关系实体的主键和部分其他属性组合而成的。

属性（Attribute）：属性是描述实体特征的属性。每个实体可以有多个属性。例如：学生的姓名、年龄、学号等。

  • 简单属性：不能再分解的属性，如姓名。
  • 复合属性：可以进一步分解的属性，如地址（包括街道、城市、邮政编码）。
  • 单值属性：一个实体只有一个值的属性，如学号。
  • 多值属性：一个实体可以有多个值的属性，如电子邮件地址。
  • 派生属性：可以通过其他属性计算得到的属性，如年龄。

关系（Relationship）：关系表示实体之间的联系。关系可以是一对一、一对多或多对多。例如：学生与课程之间的选课关系。

  • 一对一关系（1:1）：一个实体实例与另一个实体实例之间有一对一的关系。
  • 一对多关系（1:N）：一个实体实例与多个实体实例之间有关系。
  • 多对多关系（M:N）：多个实体实例与多个实体实例之间有关系。

模型的图形表示

  • 实体：
    • 强实体：用矩形表示，矩形内写上实体的名称。
    • 弱实体：弱实体用双矩形表示。
  • 属性：用椭圆表示，椭圆内写上属性的名称。
    • 主键：用下划线表示。
    • 多值属性：用双椭圆表示。
    • 派生属性：用虚线椭圆表示。
  • 关系：用菱形表示，菱形内写上关系的名称。
    • 一对一关系：1:1 表示
    • 一对多关系：1:N 表示
    • 多对多关系：M:N 表示
  • 联系：联系用菱形表示，并通过直线连接到相关的实体。
  • 继承：继承用箭头表示，并通过直线箭头指向父实体。
  • 聚合：聚合用双菱形表示，并通过直线连接到相关的实体。

性能调优

系统性能参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
/*
    查看全局变量
    全局变量控制了 MySQL 服务器的行为。通过查看这些变量，可以了解服务器的配置情况。
*/
-- 查看所有全局变量
SHOW GLOBAL VARIABLES;
-- 查看特定的全局变量
SHOW GLOBAL VARIABLES LIKE 'max_connections';

/*
    查看状态变量
    状态变量提供了关于服务器运行状况的信息，如连接数、查询次数等。
*/
-- 查看所有状态变量
SHOW GLOBAL STATUS;
-- 查看特定的状态变量
SHOW GLOBAL STATUS LIKE 'Threads_connected';

/*
    查看当前连接
    了解当前有多少客户端连接到服务器，以及它们正在执行的操作。
*/
-- 查看当前的所有连接
SHOW PROCESSLIST;

/*
    查询缓存
    查询缓存是 MySQL 中用于存储 SELECT 语句及其结果的缓存机制，以提高重复查询的速度。
*/
-- 查看查询缓存的设置
SHOW VARIABLES LIKE 'query_cache%';
-- 查看查询缓存的统计信息
SHOW STATUS LIKE 'Qcache%';

/*
    InnoDB 存储引擎状态
    InnoDB 是 MySQL 的默认存储引擎，对于事务处理非常重要。查看 InnoDB 的状态可以帮助诊断问题。
*/
-- 显示 InnoDB 存储引擎的状态
SHOW ENGINE INNODB STATUS;

/*
    锁等待
    当多个会话试图同时访问相同的资源时，可能会发生锁等待。了解锁等待的情况有助于解决并发问题。
*/
-- 查看当前的锁等待情况
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

/*
    表空间和数据文件大小
    了解表空间的大小和增长情况对于规划磁盘空间和预防性能下降至关重要。
*/
-- 获取每个表的数据和索引大小
SELECT table_name, data_length, index_length 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name';

/*
    二进制日志
    二进制日志记录了对数据库的所有更改操作，对于数据恢复和主从复制非常重要。
*/
-- 查看二进制日志是否启用
SHOW VARIABLES LIKE 'log_bin';
-- 查看当前的二进制日志文件
SHOW BINARY LOGS;

/*
    Connections (连接次数)
    这个状态变量显示了自MySQL服务器启动以来，尝试连接到服务器的总次数。这包括成功的连接和失败的连接。
*/
-- 查询自MySQL服务器启动以来的连接次数
SHOW GLOBAL STATUS LIKE 'Connections';

/*
    Uptime (上线时间)
    记录了MySQL服务器已经运行的时间（秒）。这个值可以帮助你了解服务器的稳定性和可能需要重启的时间点。
*/
-- 查询MySQL服务器的上线时间
SHOW GLOBAL STATUS LIKE 'Uptime';

/*
    Innodb_rows_read (Select查询返回的行数)
    记录了自MySQL服务器启动以来，通过InnoDB存储引擎读取的行数。
*/
-- 查询InnoDB存储引擎自MySQL服务器启动以来读取的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';

/*
    Innodb_rows_inserted (执行INSERT操作插入的行数)
    记录了自MySQL服务器启动以来，通过InnoDB存储引擎插入的新行数。
*/
-- 查询InnoDB存储引擎自MySQL服务器启动以来插入的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';

/*
    Innodb_rows_updated (执行UPDATE操作更新的行数)
    记录了自MySQL服务器启动以来，通过InnoDB存储引擎更新的行数。
*/
-- 查询InnoDB存储引擎自MySQL服务器启动以来更新的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_updated';

/*
    Innodb_rows_deleted (执行DELETE操作删除的行数)
    记录了自MySQL服务器启动以来，通过InnoDB存储引擎删除的行数。
*/
-- 查询InnoDB存储引擎自MySQL服务器启动以来删除的行数
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted';

/*
    Com_select (查询操作的次数)
    记录了自MySQL服务器启动以来，执行的 SELECT 查询的数量。
*/
-- 查询自MySQL服务器启动以来执行的SELECT查询次数
SHOW GLOBAL STATUS LIKE 'Com_select';

/*
    Com_insert (插入操作的次数)
    记录了自MySQL服务器启动以来，执行的 INSERT 插入操作的次数。如果插入操作包含多行，则只计数一次。
*/
-- 查询自MySQL服务器启动以来执行的INSERT操作次数
SHOW GLOBAL STATUS LIKE 'Com_insert';

/*
    Com_update (更新操作的次数)
    记录了自MySQL服务器启动以来，执行的 UPDATE 更新操作的次数。
*/
-- 查询自MySQL服务器启动以来执行的UPDATE操作次数
SHOW GLOBAL STATUS LIKE 'Com_update';

/*
    Com_delete (删除操作的次数)
    记录了自MySQL服务器启动以来，执行的 DELETE 删除操作的次数。
*/
-- 查询自MySQL服务器启动以来执行的DELETE操作次数
SHOW GLOBAL STATUS LIKE 'Com_delete';

/*
    Last_query_cost (统计查询成本)
    记录了最近一次优化器估计的查询成本。这个值可以帮助你了解 MySQL 优化器是如何评估查询效率的，从而对查询进行优化。
    低值：表示查询的优化器估计成本较低，查询可能执行得较快。
    高值：表示查询的优化器估计成本较高，查询可能执行得较慢。
*/
-- 执行一个查询
SELECT * FROM your_table WHERE some_column = 'some_value';
-- 查询 Last_query_cost
SHOW STATUS LIKE 'Last_query_cost';

慢查询日志

慢查询日志（Slow Query Log）是 MySQL 中一个非常有用的工具，用于记录执行时间超过指定阈值的 SQL 语句。通过分析慢查询日志，可以发现性能瓶颈并优化查询。

配置慢查询日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 方法一：通过动态变量开启（无需重启 MySQL 服务）
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 关闭慢查询日志
SET GLOBAL slow_query_log = 'OFF';

-- 设置慢查询日志文件路径（可选）
SET GLOBAL slow_query_log_file = '/path/to/your/slow-query.log';

-- 设置慢查询的时间阈值（默认为10秒）
SET GLOBAL long_query_time = 2;  -- 单位为秒

-- 方法二：通过配置文件开启（需要重启 MySQL 服务）
-- 编辑 MySQL 的配置文件（通常是 my.cnf 或 my.ini），在 [mysqld] 部分添加或修改以下参数：
slow_query_log = 1
slow_query_log_file = /path/to/your/slow-query.log
long_query_time = 2

-- 保存文件后，重启 MySQL 服务
sudo systemctl restart mysql

查看慢查询日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 查看慢查询日志的文件路径
SHOW VARIABLES LIKE 'slow_query_log_file';

-- 查看慢查询的时间阈值
SHOW VARIABLES LIKE 'long_query_time';

/*
    Slow_queries (慢查询次数)
    记录了执行时间超过 long_query_time 配置值的SQL查询数量。这对于性能调优非常关键。
*/
-- 查询自MySQL服务器启动以来的慢查询次数
SHOW GLOBAL STATUS LIKE 'Slow_queries';

/*
    查看慢查询日志内容
    慢查询日志文件通常是一个文本文件，可以使用文本编辑器或命令行工具查看其内容。
*/
cat /path/to/your/slow-query.log

分析慢查询日志

mysqldumpslow 是一个 MySQL 自带的工具，用于解析和汇总慢查询日志文件。通过 mysqldumpslow，你可以快速地了解哪些查询是导致性能问题的主要原因。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# 基本语法
# mysqldumpslow 的输出通常包含以下信息：
#   Count：查询出现的次数。
#   Time：查询的总时间（秒）。
#   Lock：查询的总锁定时间（秒）。
#   Rows_sent：查询返回的总行数。
#   Rows_examined：查询扫描的总行数。
#   Query：查询语句。
mysqldumpslow [options] log_file ...

# 常用的 mysqldumpslow 选项及其说明：
#   -a：显示所有字段。
#   -g pattern：使用正则表达式过滤查询。
#   -s order：按指定的顺序排序输出结果。常见的排序选项包括：
#       - t：按时间排序（默认）
#       - l：按锁定时间排序
#       - r：按返回的行数排序
#       - c：按查询次数排序
#   -t N：显示前 N 条记录。
#   -v：详细模式，显示更多信息。
#   -d：调试模式，显示调试信息。
mysqldumpslow /path/to/your/slow-query.log

# 显示所有字段
mysqldumpslow -a /path/to/your/slow-query.log

# 显示前 10 条最慢的查询
mysqldumpslow -t 10 /path/to/your/slow-query.log

# 按返回的行数排序并显示前 5 条记录
mysqldumpslow -s r -t 5 /path/to/your/slow-query.log

# 按锁定时间排序并显示前 10 条记录
mysqldumpslow -s l -t 10 /path/to/your/slow-query.log

# 使用正则表达式过滤查询
mysqldumpslow -g "SELECT" /path/to/your/slow-query.log

查询性能分析

SHOW PROFILES 和 SHOW PROFILE 是 MySQL 中用于分析查询性能的强大工具。它们可以帮助你了解查询的各个阶段所花费的时间，从而找出性能瓶颈。

启用查询性能分析

1
2
3
4
5
6
7
8
-- 查看性能分析是否开启
SHOW VARIABLES LIKE 'profiling';

-- 方法一：使用 performance_schema
SET GLOBAL performance_schema = ON;

-- 方法二：使用 SHOW PROFILES 和 SHOW PROFILE
SET profiling = 1;

查询性能分析列表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 启用查询性能分析
SET profiling = 1;

-- 执行一些查询
SELECT * FROM your_table WHERE some_column = 'some_value';
SELECT * FROM another_table WHERE another_column = 'another_value';

-- 查看所有查询的性能概要
SHOW PROFILES;

+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                              
+----------+------------+----------------------------------------------------------------------+
|        1 | 0.00045300 | SELECT * FROM your_table WHERE some_column = 'some_value' 
|        2 | 0.00032100 | SELECT * FROM another_table WHERE another_column = 'another_value' 
+----------+------------+----------------------------------------------------------------------+

查询性能分析详情

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 查看特定查询的详细性能信息
SHOW PROFILE FOR QUERY 1;
SHOW PROFILE cpu, block io FOR QUERY 30;

+----------------------+----------+-----------------------+
| Status               | Duration |
+----------------------+----------+-----------------------+
| starting             | 0.000045 | 查询开始的时间。
| checking permissions | 0.000005 | 检查用户权限的时间。
| Opening tables       | 0.000010 | 打开表的时间。
| System lock          | 0.000005 | 获取系统锁的时间。
| init                 | 0.000015 | 初始化查询的时间。
| optimizing           | 0.000005 | 优化查询的时间。
| statistics           | 0.000020 | 收集统计信息的时间。
| preparing            | 0.000010 | 准备执行查询的时间。
| executing            | 0.000005 | 执行查询的时间。
| Sending data         | 0.000350 | 发送数据给客户端的时间。
| end                  | 0.000005 | 查询结束的时间。
| query end            | 0.000005 | 查询结束的时间。
| closing tables       | 0.000010 | 关闭表的时间。
| freeing items        | 0.000015 | 释放资源的时间。
| cleaning up          | 0.000015 | 清理操作的时间。
+----------------------+----------+-----------------------+

执行计划分析

EXPLAIN 是 MySQL 中一个非常强大的工具，用于显示 MySQL 如何执行 SQL 语句。通过 EXPLAIN，你可以了解查询的执行计划，包括表的扫描方式、使用的索引、连接顺序等信息。这有助于优化查询性能。

输出列解释

EXPLAIN 的输出结果包含多个列，每个列提供了关于查询执行的不同信息。以下是一些主要的列及其含义：

  • id：查询的标识符。每个查询都有一个唯一的 ID。如果查询中有子查询，每个子查询会有不同的 ID。
    • id如果相同，可以认为是一组，从上往下顺序执行
    • 在所有组中，id值越大，优先级越高，越先执行
    • id号每个号码，表示一次独立的查询,一个sql的查询趟数越少越好
  • select_type：查询的类型。常见的值包括：
    • SIMPLE：简单的查询，不包含子查询或联合。 
      1
      EXPLAIN SELECT * FROM employees WHERE department_id = 1;
    • PRIMARY：最外层的查询。通常出现在包含子查询的复杂查询中，表示最外层的查询部分。 
      1
      EXPLAIN SELECT * FROM employees WHERE id IN (SELECT department_id FROM departments);
    • UNION：联合中的第二个或后面的 SELECT 语句。 
      1
      2
      3
      EXPLAIN SELECT * FROM employees WHERE department_id = 1
      UNION
      SELECT * FROM employees WHERE department_id = 2;
    • UNION RESULT：联合的结果集。表示将多个 UNION 查询的结果合并在一起的步骤。 
      1
      2
      3
      EXPLAIN SELECT * FROM employees WHERE department_id = 1
      UNION
      SELECT * FROM employees WHERE department_id = 2;
    • SUBQUERY：子查询中的第一个 SELECT 语句。通常出现在 IN、EXISTS 等子查询中。 
      1
      EXPLAIN SELECT * FROM employees WHERE id IN (SELECT department_id FROM departments);
    • DEPENDENT SUBQUERY：依赖于外部查询的子查询。子查询的结果取决于外部查询的行。 
      1
      EXPLAIN SELECT * FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE d.id = e.department_id);
    • DEPENDENT UNION：依赖于外部查询的联合中的 SELECT 语句。联合中的每个查询都依赖于外部查询的行。 
      1
      2
      3
      4
      5
      EXPLAIN SELECT * FROM employees e WHERE e.id IN (
          SELECT d.id FROM departments d WHERE d.id = e.department_id
          UNION
          SELECT d.id FROM departments d WHERE d.name = 'Sales'
      );
    • DERIVED：派生表的 SELECT 语句。派生表是在 FROM 子句中的子查询。 
      1
      EXPLAIN SELECT * FROM (SELECT * FROM employees WHERE department_id = 1) AS derived_table;
    • MATERIALIZED：物化子查询。MySQL 会先执行子查询并将结果存储在一个临时表中，然后再使用这个临时表进行后续的查询。 
      1
      EXPLAIN SELECT * FROM employees e JOIN (SELECT * FROM departments WHERE id > 1) AS d ON e.department_id = d.id;
    • UNCACHEABLE SUBQUERY：不可缓存的子查询。子查询的结果不能被缓存，每次都需要重新计算。 
      1
      EXPLAIN SELECT * FROM employees e WHERE e.salary > (SELECT AVG(salary) FROM employees);
    • UNCACHEABLE UNION：不可缓存的联合。联合的结果不能被缓存，每次都需要重新计算。 
      1
      2
      3
      EXPLAIN SELECT * FROM employees WHERE department_id = 1
      UNION
      SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
  • table：当前行显示的是哪个表的执行计划。
  • partitions：匹配的分区（如果使用了分区表）。
  • type：访问类型，表示 MySQL 如何查找表中的行。常见的值包括：
    • system：表中只有一行数据。这是 const 类型的特例。 
      1
      EXPLAIN SELECT * FROM (SELECT 1 AS id) t;
    • const：表最多有一个匹配行，读取时会被立即读取。通常用于主键或唯一索引的等值查询。 
      1
      EXPLAIN SELECT * FROM employees WHERE id = 1;
    • eq_ref：用于连接操作，通常出现在 JOIN 语句中。表示对于前一个表中的每一行，MySQL 都会在当前表中查找唯一匹配的行。通常用于主键或唯一索引的等值连接。 
      1
      EXPLAIN SELECT * FROM employees e JOIN departments d ON e.department_id = d.id;
    • ref：用于非唯一索引的等值查询。表示对于前一个表中的每一行，MySQL 会在当前表中查找所有匹配的行。通常用于非唯一索引的等值连接。 
      1
      EXPLAIN SELECT * FROM employees WHERE department_id = 1;
    • fulltext：使用全文索引进行搜索。适用于全文索引的查询。 
      1
      EXPLAIN SELECT * FROM articles WHERE MATCH(title, content) AGAINST('MySQL');
    • ref_or_null：类似于 ref，但额外包含对 NULL 值的搜索。通常用于 OR 语句中。 
      1
      EXPLAIN SELECT * FROM employees WHERE department_id = 1 OR department_id IS NULL;
    • index_merge：使用索引合并优化。MySQL 可以使用多个索引进行查询。 
      1
      EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith' OR first_name = 'John';
    • unique_subquery：用于 IN 子查询，子查询返回的结果是唯一的。通常用于主键或唯一索引的子查询。 
      1
      EXPLAIN SELECT * FROM employees WHERE id IN (SELECT department_id FROM departments);
    • index_subquery：用于 IN 子查询，子查询返回的结果不是唯一的。通常用于非唯一索引的子查询。 
      1
      EXPLAIN SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);
    • range：用于范围查询。表示 MySQL 可以在索引中进行范围扫描。 
      1
      EXPLAIN SELECT * FROM employees WHERE salary BETWEEN 50000 AND 60000;
    • index：全索引扫描。表示 MySQL 会扫描整个索引，但不会扫描表中的数据行。 
      1
      EXPLAIN SELECT department_id FROM employees;
    • ALL：全表扫描。表示 MySQL 会扫描整个表，性能较差。 
      1
      EXPLAIN SELECT * FROM employees;
  • possible_keys：可能使用的索引。
  • key：实际使用的索引。
  • key_len：使用的索引长度。
  • ref：与索引比较的列或常量。
  • rows：MySQL 认为必须检查的行数。
  • filtered：按表条件过滤的行数百分比。
  • Extra：额外的信息，例如：
    • Using where：使用了 WHERE 子句。
    • Using index：使用了覆盖索引。
    • Using temporary：使用了临时表。
    • Using filesort：使用了文件排序。
    • Using join buffer：使用了连接缓冲区。

输出格式

1
2
3
4
5
6
7
8
9
-- 表格格式：默认格式
EXPLAIN SELECT * FROM employees WHERE department_id = 1;
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM employees WHERE department_id = 1;

-- JSON 格式：从 MySQL 5.6.3 开始，EXPLAIN 支持 JSON 格式的输出
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department_id = 1;

-- TREE 格式：从 MySQL 8.0.18 开始，EXPLAIN 支持 TREE 格式的输出。
EXPLAIN FORMAT=TREE SELECT * FROM employees WHERE department_id = 1;

执行计划分析示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 查看执行计划
EXPLAIN SELECT e.name, d.name 
FROM employees e 
JOIN departments d ON e.department_id = d.id 
WHERE e.salary > 50000;

-- 输出列
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | e     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                  | 1000 |    10.00 | Using where |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.e.department_id  | 1    |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------+

-- 用于显示最近执行的 SQL 语句产生的警告信息。
-- 在查看执行计划之后执行，可以查看到 SQL 优化器具体执行的 SQL 语句。
SHOW WARNINGS;

⭐⭐ 优化建议

  • 全表扫描（ALL）：尽量避免全表扫描，为经常用于查询条件的列创建索引。
  • 使用覆盖索引（Using index）：创建覆盖索引，使查询所需的所有列都包含在索引中。
  • 文件排序（Using filesort）：为排序列创建索引，或优化查询逻辑。
  • 临时表（Using temporary）：优化查询逻辑，减少子查询的使用，或为相关列创建索引。

分析优化器执行计划

EXPLAIN 命令虽然提供了查询的执行计划，但有时我们需要更详细的优化器决策信息。MySQL 从 5.7 版本开始引入了 EXPLAIN 的 TRACE 格式，它可以提供优化器在生成执行计划过程中的详细决策信息。

1
2
3
4
5
6
7
-- 启用优化器跟踪，并设置格式为 JSON。
SET optimizer_trace="enabled=on", end_markers_in_json=on;
-- 设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace_max_mem_size=1000000;

-- 执行查询并查看优化器跟踪信息
EXPLAIN FORMAT=TRACE SELECT * FROM employees WHERE department_id = 1;

监控分析视图

sys schema 是 MySQL 5.7 及以上版本中提供的一个系统模式，它包含了一系列的视图和存储过程，用于简化数据库的监控和诊断工作。sys schema 提供了高级别的抽象，使得管理员和开发人员可以更容易地理解和分析数据库的性能和状态。

安装工具

1
2
3
-- 在 MySQL 5.7 及以上版本中，sys schema 默认是安装的
-- 如果你的 MySQL 没有安装 sys schema，可以手动安装
mysql -u root -p < /usr/share/mysql/sys_schema.sql

常用的视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
-- 显示每个表的读写统计信息。
SELECT * FROM sys.schema_table_statistics LIMIT 10;

-- 显示每个索引的读写统计信息。
SELECT * FROM sys.schema_index_statistics LIMIT 10;

-- 显示最耗时的 SQL 语句及其性能指标。
SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- 与 sys.statement_analysis 类似，但提供更多的细节信息。
SELECT * FROM sys.x$statement_analysis ORDER BY total_latency DESC LIMIT 10;

-- 按主机汇总的 SQL 语句延迟。
SELECT * FROM sys.host_summary_by_statement_latency ORDER BY total_latency DESC LIMIT 10;

-- 按用户汇总的 SQL 语句延迟。
SELECT * FROM sys.user_summary_by_statement_latency ORDER BY total_latency DESC LIMIT 10;

-- 显示 InnoDB 缓冲池中每个表的统计信息。
SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;

-- 按文件汇总的 I/O 统计信息。
SELECT * FROM sys.io_global_by_file_by_bytes ORDER BY total DESC LIMIT 10;

-- 按等待时间汇总的 I/O 统计信息。
SELECT * FROM sys.io_global_by_wait_by_bytes ORDER BY total DESC LIMIT 10;

-- 按主机汇总的内存使用情况。
SELECT * FROM sys.memory_by_host_by_current_bytes ORDER BY current_allocated DESC LIMIT 10;

-- 按用户汇总的内存使用情况。
SELECT * FROM sys.memory_by_user_by_current_bytes ORDER BY current_allocated DESC LIMIT 10;

-- 查看使用了临时表的 SQL 语句
SELECT * FROM sys.statements_with_temp_tables ORDER BY tmp_tables_created DESC LIMIT 10;

-- 与 statements_with_temp_tables 类似，但提供了更详细的临时表使用信息
SELECT * FROM sys.x$statements_with_temp_tables ORDER BY tmp_tables_created DESC LIMIT 10;

-- 查看全表扫描的 SQL 语句
SELECT * FROM sys.statements_with_full_table_scans ORDER BY rows_sent DESC LIMIT 10;

-- 与 statements_with_full_table_scans 类似，但提供了更详细的全表扫描信息
SELECT * FROM sys.x$statements_with_full_table_scans ORDER BY rows_sent DESC LIMIT 10;

-- 自定义查询
-- 可以结合 performance_schema.events_statements_summary_by_digest 表和 sys schema 的视图来构建自定义查询。
SELECT 
    DIGEST_TEXT AS query,
    SCHEMA_NAME AS db,
    COUNT_STAR AS exec_count,
    SUM_CREATED_TMP_TABLES AS tmp_tables,
    SUM_CREATED_TMP_DISK_TABLES AS tmp_disk_tables,
    FIRST_SEEN AS first_seen,
    LAST_SEEN AS last_seen,
    DIGEST AS digest
FROM 
    performance_schema.events_statements_summary_by_digest
WHERE 
    SUM_CREATED_TMP_TABLES > 0
ORDER BY 
    SUM_CREATED_TMP_TABLES DESC
LIMIT 10;

常用的存储过程

sys schema 还提供了一些存储过程，用于执行特定的监控和诊断任务。

1
2
3
4
5
6
7
8
9
10
11
-- 启用 Performance Schema 仪器。
CALL sys.ps_setup_enable_instrument('statement/sql/select');

-- 禁用 Performance Schema 仪器。
CALL sys.ps_setup_disable_instrument('statement/sql/select');

-- 启用 Performance Schema 消费者。
CALL sys.ps_setup_enable_consumer('events_statements_history_long');

-- 禁用 Performance Schema 消费者。
CALL sys.ps_setup_disable_consumer('events_statements_history_long');

注意事项

  • 性能影响：虽然 sys schema 提供了丰富的监控和诊断信息，但启用 Performance Schema 会增加一定的性能开销。在生产环境中使用时，应谨慎评估其影响。
  • 权限：访问 sys schema 的视图和存储过程需要相应的权限。确保只有授权的用户可以访问这些资源。
  • 数据保留：Performance Schema 的数据是实时的，不会持久化。如果需要长期保留监控数据，可以考虑将数据导出到其他存储系统。

查询优化

索引失效问题

⭐⭐ 使用函数或表达式时

1
2
3
4
5
6
7
8
-- 索引
CREATE INDEX idx_email ON users(email);

-- 当在索引列上使用函数或表达式时，索引失效
SELECT * FROM users WHERE LEFT(email, 5) = 'abcde';

-- 解决方法
-- 尽量避免在索引列上使用函数或表达式。如果确实需要使用，可以考虑使用函数索引或者重新设计查询逻辑。

⭐⭐ 查询条件存在类型转换时

1
2
3
4
5
6
7
8
9
-- 索引
CREATE INDEX idx_email ON users(email);

-- 如果在查询中以不匹配的方式使用该字段（例如，将字符串字段与数字进行比较），会导致索引失效。
-- 索引失效
SELECT * FROM users WHERE email = 123;

-- 解决方法：确保查询条件中使用的数据类型与索引列的数据类型相匹配。
SELECT * FROM users WHERE email = '123';

⭐⭐ OR 前后存在非索引的列时

1
2
3
4
5
6
7
8
9
-- 索引
CREATE INDEX idx_email ON users(email);

-- email 有索引，phone 没有索引，email 索引失效
SELECT * FROM users WHERE email = 'test@example.com' OR phone = '1234567890';

-- 解决方法：给 email 和 phone 分别创建索引
CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_phone ON users(phone);

⭐⭐ 范围条件右边的列索引失效

1
2
3
4
5
6
7
8
9
10
-- 索引
CREATE INDEX idx_age_classId_name ON student(age, classId, name);

-- 当使用范围条件（如 >、<、>=、<=、BETWEEN、IN 等）时
-- 组合索引中，范围条件列，不在最后一列时，索引失效
SELECT * FROM student WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc'; 
SELECT * FROM student WHERE student.age = 30 AND student.name = 'abc' AND student.classId > 20; 

-- 解决方法：调整索引顺序
CREATE INDEX idx_age_name_cid ON student(age, name, classId);

⭐⭐ 条件使用不等于时

1
2
3
4
5
6
7
-- 索引
CREATE INDEX idx_age ON student(age);

-- 条件使用不等于(!= 或者<>)时索引失效
-- 因为不等于条件通常会导致MySQL无法有效地利用索引进行快速查找，而是选择全表扫描。
SELECT * FROM student WHERE student.age <> 18;
SELECT * FROM student WHERE student.age != 20;

⭐⭐ 使用 IS NOT NULL 条件时

1
2
3
4
5
6
--索引
CREATE INDEX idx_name ON student(name);

-- 使用 IS NOT NULL 条件时，索引会失效
-- 因为 IS NOT NULL 条件通常涉及大量数据的扫描，MySQL的查询优化器可能会选择全表扫描而不是使用索引。
SELECT * FROM student WHERE name IS NOT NULL;

⭐⭐ LIKE 以通配符 % 开头时

1
2
3
4
5
6
7
--索引
CREATE INDEX idx_name ON student(name);

-- LIKE 以通配符 % 开头时，索引会失效
-- 因为 % 开头的 LIKE 条件通常涉及大量数据的扫描，MySQL的查询优化器可能会选择全表扫描而不是使用索引。
-- LIKE 以通配符 % 结尾的索引不会失效
SELECT * FROM student WHERE name LIKE '%ab';

⭐⭐ 全文检索语法不对时

1
2
3
4
5
6
7
8
-- 索引
CREATE FULLTEXT INDEX idx_content ON articles (content);

-- 全文索引（FULLTEXT）用于处理文本搜索，但如果查询不是使用全文检索相关的关键词（如MATCH AGAINST），则全文索引不会被使用。
SELECT * FROM articles WHERE content LIKE 'keyword%';

-- 解决方法：使用全文检索语法来利用全文索引
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword');

⭐⭐ 数据量过小、选择性差时

1
2
3
4
5
6
--索引
CREATE INDEX idx_gender ON users(gender);

-- 当表中的数据量非常小时，全表扫描比使用索引更快，索引失效。
-- 当索引的选择性很低（即，索引列中的重复值很多），全表扫描比使用索引更高效。
SELECT * FROM users WHERE gender = 'M';

⭐⭐ 索引使用建议

  • 对于单列索引，尽量选择针对当前 query 过滤性更好的索引。
  • 在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引。
  • 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

查询优化

关联查询（JOIN操作）是数据库操作中最常见的部分之一。当涉及到多个表的数据查询时，正确地使用JOIN可以极大地提高查询效率。然而，不当的JOIN使用可能会导致性能问题。

⭐⭐ 选择合适的 JOIN 类型

1
2
3
-- INNER JOIN：仅返回两个表中匹配的行。
-- LEFT JOIN / RIGHT JOIN：返回左表/右表的所有记录，即使在另一个表中没有匹配项。
-- FULL OUTER JOIN：返回所有表中的所有记录，如果某一行在另一个表中没有匹配，则结果集中这些列将包含NULL。

⭐⭐ 尽可能减少返回的数据量

1
2
3
4
5
6
7
8
9
10
11
-- 只选择需要的列，避免使用SELECT *。这可以减少网络传输的数据量，同时也可以减少服务器的处理负担。

-- 不推荐的做法
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- 推荐的做法
SELECT o.order_id, o.order_date, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id;

⭐⭐ 使用适当的索引

1
2
3
4
5
6
7
8
9
10
11
-- 确保所有用于JOIN条件的列都已建立索引。索引可以显著加快查询速度，因为它减少了数据库引擎需要扫描的数据量。

-- 创建索引
CREATE INDEX idx_customer_id ON orders (customer_id);
CREATE INDEX idx_customer_name ON customers (name);

-- 查询示例
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.name = 'John Doe';

⭐⭐ 使用 JOIN 替代子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 在某些情况下，使用JOIN可以显著提高性能，尤其是在处理大量数据时。
-- JOIN通常比子查询更高效，因为数据库优化器可以更好地优化JOIN操作。

-- 不推荐的做法
SELECT o.order_id, 
       (SELECT COUNT(*) 
        FROM order_items oi 
        WHERE oi.order_id = o.order_id) AS item_count
FROM orders o;

-- 推荐的做法
SELECT o.order_id, COUNT(oi.item_id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id;

⭐⭐ 避免在 JOIN 条件中使用函数

1
2
3
4
5
6
7
8
9
10
11
-- 在JOIN条件中使用函数可能会阻止MySQL使用索引，导致全表扫描。

-- 不推荐的做法
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON DATE(o.order_date) = '2023-01-01';

-- 推荐的做法
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.order_date BETWEEN '2023-01-01' AND '2023-01-01 23:59:59';

⭐⭐ 使用 EXISTS 代替 IN

1
2
3
4
5
6
7
8
9
10
11
12
-- 在某些情况下，使用EXISTS代替IN可以提高性能，特别是当子查询返回大量数据时。
-- EXISTS通常在找到第一个匹配项后立即停止搜索，而 IN 则需要遍历整个子查询结果集。

-- 不推荐的做法
SELECT c.customer_id, c.name
FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

-- 推荐的做法
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

⭐⭐ 优化子查询中的索引

1
2
3
4
5
6
7
8
-- 确保子查询中使用的列上有适当的索引。

SELECT o.order_id, o.order_amount
FROM orders o
WHERE o.order_amount > 1000;

-- 创建 order_amount 列的索引
CREATE INDEX idx_order_amount ON orders (order_amount);

⭐⭐ 使用临时表或CTE

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 对于复杂的查询，可以考虑使用临时表或CTE来存储中间结果，
-- 然后再进行最终查询。这可以减少重复计算，提高性能。

WITH customer_orders AS (
    SELECT c.customer_id, c.name, o.order_amount
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
)
SELECT customer_id, name, 
       SUM(order_amount) AS total_amount, 
       COUNT(order_amount) AS order_count
FROM customer_orders
GROUP BY customer_id, name;

⭐⭐ 使用LIMIT减少排序范围

1
2
3
4
5
6
7
8
9
10
-- 如果只需要返回前几条记录，使用LIMIT可以显著减少排序的范围，从而提高性能。

-- 创建索引
CREATE INDEX idx_order_date ON orders (order_date);

-- 查询示例
SELECT order_id, order_date, customer_id
FROM orders
ORDER BY order_date DESC
LIMIT 10;

⭐⭐ 使用覆盖索引

1
2
3
4
5
6
7
8
9
10
11
-- 覆盖索引是指查询所需的列都在同一个索引中。
-- 这样，数据库引擎可以直接从索引中获取所需的数据，而不需要回表查询，从而提高性能。

-- 创建覆盖索引
CREATE INDEX idx_order_date_covering ON orders (order_date, order_id, customer_id);

-- 查询示例
SELECT order_id, order_date, customer_id
FROM orders
ORDER BY order_date DESC
LIMIT 10;

⭐⭐ 增加排序缓冲区大小

1
2
3
4
5
-- 查看当前的排序缓冲区大小
SHOW VARIABLES LIKE 'sort_buffer_size';

-- 设置排序缓冲区大小（需要有足够的权限）
SET sort_buffer_size = 1048576; -- 1MB

⭐⭐ 索引下推（ICP）优化

1
2
3
4
5
6
7
8
9
10
11
12
-- 当查询中包含可以在索引上直接评估的部分条件时，ICP特别有用。
-- 真正满足所有条件的行才会被加载到服务器层，减少了不必要的I/O操作和数据传输量。

-- ICP默认是开启的，可以通过设置系统变量来确保其处于开启状态。
SET optimizer_switch='index_condition_pushdown=on';

-- 创建索引
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);

-- 查询
SELECT * FROM orders
WHERE customer_id = 100 AND order_date >= '2023-01-01' AND total_amount > 1000;

大表优化

限定查询范围

  • 使用索引：确保查询语句中经常使用的字段上有合适的索引。这可以显著减少查询需要扫描的数据量。
  • 避免全表扫描：尽量避免使用SELECT *这样的语句，而是明确指定所需的列。这样可以减少从磁盘读取的数据量，加快查询速度。
  • 使用LIMIT：如果只需要获取前几条或最后几条记录，可以使用LIMIT来限制返回的结果数量，从而减少服务器的负担。

读写分离

  • 主从复制：通过设置一个或多个只读副本（从库），将读操作分散到这些副本上，而写操作仍然由主库处理。这样可以有效减轻单个数据库的压力，提高系统的整体吞吐量。
  • 智能路由：在应用层实现读写请求的智能路由，确保写操作发送到主库，读操作根据负载情况分发到不同的从库。

垂直拆分

  • 按功能划分：将不同业务模块的数据存储在不同的数据库或表中。例如，用户信息、订单信息、商品信息等可以分别存放在不同的表或数据库中。
  • 减少表间关联：垂直拆分后，减少了跨表的JOIN操作，提高了查询效率。

水平拆分

  • 数据分片：将同一张表的数据根据某些规则（如ID范围、哈希值等）分布到多个物理表或数据库中。这种方式可以有效解决单一表过大导致的性能问题。
  • 中间件支持：利用ShardingSphere、MyCat等中间件可以简化水平拆分的实现，它们提供了透明的SQL解析、路由及结果集合并等功能。
  • 合理选择分片键：选择合适的分片键对于保证数据均匀分布至关重要，同时也要考虑到查询的便利性。

表结构优化

冷热数据分离

  • 定义冷热数据：热数据是指频繁访问的数据，而冷数据则是较少访问的数据。
  • 物理分离：可以通过创建两个表来实现，一个是热数据表，另一个是冷数据表。当数据不再活跃时，将其从热数据表移动到冷数据表。
  • 逻辑分离：也可以在同一张表中通过索引来实现，为热数据创建覆盖索引，减少全表扫描的次数。

增加中间表

  • 解决多对多关系：在多对多的关系中，通常需要一个中间表来存储两个实体之间的关联信息。
  • 减少重复数据：通过中间表，可以避免在一个表中存储大量重复的数据，从而减少存储空间并提高查询效率。

增加冗余字段

  • 减少JOIN操作：在某些情况下，为了减少复杂的JOIN操作，可以在表中添加冗余字段，以存储常用的信息。
  • 注意维护成本：增加冗余字段会增加数据维护的成本，因为需要在更新数据时同步更新冗余字段。

优化数据类型

  • 选择合适的数据类型：使用最合适的数据类型可以节省存储空间并提高查询效率。例如，使用INT而不是BIGINT，使用VARCHAR而不是TEXT。
  • 压缩数据：对于大文本字段，可以考虑使用压缩技术来减少存储空间。

优化插入速度

  • 批量插入：使用INSERT INTO … VALUES (…), (…), (…)语法批量插入数据，而不是一次插入一条记录。
  • 禁用索引：在大量插入数据之前，可以临时禁用非唯一索引，待数据插入完毕后再重新启用。
  • 使用延迟更新：对于某些场景，可以考虑使用DELAY_KEY_WRITE选项，允许MySQL在事务提交后再更新索引。

表优化

  • 分析表：使用 ANALYZE TABLE 命令定期分析表的统计信息，帮助优化器选择最佳执行计划。
  • 优化表：使用 OPTIMIZE TABLE 命令定期优化表，回收未使用的空间并整理索引碎片。
  • 检查表：使用 CHECK TABLE 命令检查表是否有错误，包括数据文件和索引文件的完整性。

服务器优化

优化服务器硬件

CPU

  • 多核处理器：选择多核CPU，以充分利用MySQL的多线程能力。
  • 高主频：选择高主频的CPU，以提高单线程性能，特别是在高并发查询时。

内存

  • 足够大的内存：确保服务器有足够的内存来缓存数据和索引，减少磁盘I/O操作。
  • 使用高速内存：选择高速DDR4或DDR5内存，以提高内存访问速度。

存储

  • SSD固态硬盘：使用SSD固态硬盘替代传统的HDD机械硬盘，大幅提高I/O性能。
  • RAID配置：使用RAID 10配置，既提供冗余又提高读写性能。
  • IOPS优化：确保存储系统的IOPS（每秒输入输出操作数）足够高，以应对高并发请求。

网络

  • 高速网络接口：使用10Gbps或更高的网络接口卡，以提高网络传输速度。
  • 低延迟网络：选择低延迟的网络设备和配置，减少网络延迟。

优化MySQL参数

缓冲池和缓存

  • innodb_buffer_pool_size：设置为物理内存的50%-75%，以缓存InnoDB表的数据和索引。
  • key_buffer_size：对于MyISAM表，设置适当的key_buffer_size以缓存索引。
  • query_cache_size：虽然MySQL 8.0已移除查询缓存，但在MySQL 5.7及更早版本中，可以适当设置query_cache_size以缓存查询结果。

连接和线程

  • max_connections：根据服务器的负载和资源情况，设置合理的最大连接数。
  • thread_cache_size：设置适当的线程缓存大小，以减少创建和销毁线程的开销。
  • thread_handling：选择合适的线程处理模式，如one-thread-per-connection或pool-of-threads。

日志和恢复

  • innodb_log_file_size：设置合理的日志文件大小，以平衡恢复时间和日志文件的大小。
  • innodb_flush_log_at_trx_commit：设置为1（默认值）以确保事务的持久性，或设置为2以提高性能。
  • sync_binlog：设置为1以确保二进制日志的持久性，或设置为0以提高性能。

查询优化

  • optimizer_switch：调整查询优化器的行为，例如禁用某些优化策略。
  • join_buffer_size：设置适当的连接缓冲区大小，以优化连接操作。
  • sort_buffer_size：设置适当的排序缓冲区大小，以优化排序操作。

其他优化策略

操作系统优化

  • 文件系统：使用高性能的文件系统，如XFS或EXT4。
  • 内核参数：调整内核参数以优化网络和I/O性能，例如：
    • vm.swappiness：设置为10或更低，减少不必要的交换。
    • net.core.somaxconn：增加最大监听队列长度，防止连接丢失。
    • net.ipv4.tcp_tw_reuse：重用TIME_WAIT状态的套接字，减少连接建立时间。

监控和调优

  • 性能监控工具：使用工具如Percona Toolkit、MySQLTuner、Prometheus等，监控数据库的性能指标。
  • 慢查询日志：启用慢查询日志，分析和优化慢查询。
  • 定期维护：定期执行表分析、优化和检查，确保数据库处于最佳状态。

安全性和备份

  • 安全配置：禁用不必要的服务和端口，使用防火墙保护数据库。
  • 备份策略：制定定期备份策略，使用工具如Percona XtraBackup进行高效备份和恢复。
  • 高可用性：考虑使用主从复制、Galera Cluster等技术实现高可用性。

⭐⭐

学习资源