MySQL

版本

一、文档概述

1.1 文档目的

记录MySQL高级工程师所需技能、实施内容、设计维度与范围，以及通用设计。最终形成案例总结、快速查阅知识、以及设计优化等。

1.2 文档内容(关键词)

MySQL、Sql、Dev、Advanced、Operations

MySqlContent2.png

1.3 术语

1.4 参考文献

二、MySQL

Key Words: 数据库、数据库管理系统（软件）、SQL

MySQL是关系型数据库，而SQL是一种语言，是一种标准，是一种数据库管理系统的命令集合。

2.1 数据库相关概念

截屏2025-10-03 13.30.29.png

2.2 主流关系数据库管理系统

DBMS.png

2.3 关系型数据库（RDBMS）

概念：建立在关系明星基础上，由多张相互连接的二维表组成的数据库。

特点：
1.使用表存储数据，格式统一，便于维护
2.使用SQL语言操作，标准统一，使用方便

截屏2025-10-03 13.52.31.png

2.4 数据模型

截屏2025-09-29 23.06.46.png

三、Basic

3.0 MySQL数据库

• 启动和停止

• 客户端连接

mysql [-h 127.0.0.1] [-P 3306] -u root -p

3.1 SQL

3.1.1 通用语法

• 结尾符
• 可读性
• 不区分大小写，关键字标准写法为使用大写
• 注释

3.1.2 DDL

3.1.2.1 库操作

<!-- 查询 所有 -->
SHOW DATABASES;
<!-- 查询当前 -->
SHOW DATABASE();

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

<!-- 删除  -->
DROP DATABASE [IF EXISTS] 数据库名;

<!-- 使用 -->
USE 数据库名;

3.1.2.2 表操作

数据类型: 数值类型、字符串类型、日期时间类型

[图片上传中...(DataTypeTime.png-45efdd-1759474534262-0)]

DataTypeTime.png

DataTypeString.png

• 表操作- 查询

// 查询
<!-- 查询当前数据库所有表 -->
SHOW TABLES;
<!-- 查询表结构 -->
DESC 表名;
<!-- 查询指定表的建表语句 -->
SHOW CREATE TABLE 表名;

• 表操作-创建

CREATE TABLE 表名 (
    字段1 字段1类型[COMMENT 字段1注释]
    字段2 字段2类型[COMMENT 字段2注释]

    字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

• 表操作-修改

<!-- 添加字段 -->
ALTER TABLE 表名 ADD 字段名 字段类型(长度) [COMMENT 字段注释] [约束];
<!-- 示例 -->
alter table emp add nickname varchar(20) comment '昵称';

<!-- 修改字段 -->
ALTER TABLE 表名 MODIFY 字段名 字段类型(长度);
<!-- 修改字段和字段类型 -->
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型(长度) [COMMENT 字段注释] [约束];
<!-- 示例 -->
alter table emp change age nickname username varchar(20) comment '用户名';

<!-- 删除字段 -->
ALTER TABLE 表名 DROP 字段名;
<!-- 示例 -->
alter table emp drop username;

<!-- 修改表名 -->
ALTER TABLE 表名 RENAME TO 新表名;

<!-- 删除表 -->
DROP TABLE [IF EXISTS] 表名;
<!-- 删除指定表，并重新创建该表 -->
TRUNCATE TABLE 表名;

注意：删除表时，表中的全部数据也会被删除。

3.1.2 DML

DML（Data Manipulation Language）,用来对数据库中表的数据记录进行增删改操作。

• 添加数据（INSERT）
• 修改数据（UPDATE）
• 删除数据（DELETE）

添加数据

<!-- 给指定字段添加数据 -->
INSERT INTO 表名 (字段1, 字段2,...) VALUES (值1, 值2,...);

<!-- 给全部字段添加数据 -->
INSERT INTO 表名 VALUES (值1, 值2,...);

<!-- 批量添加数据 -->
INSERT INTO 表名 (字段1, 字段2,...) VALUES (值1, 值2,...), (值1, 值2,...),...;
INSERT INTO 表名 VALUES (值1, 值2,...), (值1, 值2,...),...;

<font color=red>注意:</font>

• 插入数据时，指定的字段顺序需要与值的顺序是一致的。
• 字符串和日期型数据应该包含在引号中
• 插入的数据大小，应该在字段的规定范围内

修改数据

UPDATE 表名 SET 字段1 = 值1, 字段2 = 值2,... [WHERE 条件];
<!-- 示例 -->
update employee set name = 'Tom', gender = '男' wehre id = 1;

删除数据

DELETE FROM 表名 [WHERE 条件];

<font color=red>注意:</font>

• 删除语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的数据。
• DELETE语句不能删除某一个字段的值(可以使用UPDATE)

3.1.3 DQL

DQL（Data Query Language）数据查询语言，用于从关系数据库中检索、检索数据。

查询关键字：<font color=red>SELECT</font>。

内容：

• 基本查询
• 条件查询(WHERE)
• 聚合函数(count, max, min, sum, avg)
• 分组查询(GROUP BY)
• 排序查询(ORDER BY)
• 分页查询(LIMIT)

语法结构：

DQLStructure.png

DQLStructure2.png

DQLExecuteSequence.png

DQLSummary.png

基本查询

• 查询多个字段

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

• 设置别名

SELECT 字段1 [AS 别名1], 字段2 [AS 别名2], 字段3 [AS 别名3],... FROM 表名;

• 去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

<!-- 示例 -->
select distinct workaddress from employee;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件列表;

[图片上传失败...(image-2eae7d-1759475642716)]

• 等于

select * from employee where id = 1;

• 小于

select * from employee where id < 1;
<!-- 小于等于 -->
select * from employee where id <= 1;

• 查询没有值的字段

<!-- 查询没有身份证号的员工 -->
select * from employee where idcard is null;

• 查询有值所有信息

<!-- 查询有身份证号的员工 -->
select * from employee where idcard is not null;

• 不等于

select * from employee where id != 1;
<!-- 等同上 -->
select * from employee where id <> 1;

• >= 和 <=(同一字段)

select * from employee where id >= 1 && id 
<= 10;
<!-- 等同上 -->
select * from employee where id >= 1 and id <= 10;
<!-- 等同上 -->
select * from employee where id between 1 and 10;

• 不同字段

select * from employee where age < 25 and name = 'Tom';

• or

select * from employee where age = 18 or age = 20 or age = 40;
<!-- 等同上 -->
select * from employee where age in (18, 20, 40);

• 占位符

<!-- 查询姓名为2个字的员工信息 -->
select * from employee where name like '__';

<!-- 查询身份证结尾为'X'的员工信息 -->
select * from employee where idcard like '%X';

分组查询

作用： 对数据进行分组，然后进行聚合操作
语法：SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
where与having的区别：

• where：在分组前过滤数据，在分组后不再过滤数据。
• having：在分组后过滤数据，在分组前不再过滤数据。

<!-- 1.查询性别分组，统计男性员工 和女性员工的数量 -->
select gender, count(*) from employee group by gender;

<!-- 2.根据性别分组，统计男性员工 和 女性员工的平均年龄 -->
select gender, avg(age) from employee group by gender;

<!-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工大于等于3的工作地址 -->
select workaddress, count(*) from employee where age < 45 group by workaddress having count(*) >= 3;

排序查询

作用： 对查询结果进行排序
语法：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式：

• ASC：升序(默认)
• DESC：降序

<font color=red>注意：如果多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。</font>

<!-- 1.根据年龄排序 -->
select * from employee order by age asc;
select * from employee order by age desc;

<!-- 2.根据入职时间进行讲叙排序 -->
select * from employee order by entrydate desc;

<!-- 3.根据年龄对公司的员工进行生序排序，年龄相同，再按照入职时间进行降序排序 -->
select * from employee order by age asc, entrydate desc;

分页查询

语法:

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

<font color=red>
<注意：>
起始索引从0开始，起始索引=（查询页码-1）* 每页显示记录数
分页查询是数据库的方言，不同的数据库有不同的实现，MySql中是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10.
</font>

<!-- 1.查询第一页数据 -->
slect * from employee limit 0, 10;
<!-- 同上 -->
slect * from employee limit 10;

<!-- 2.查询第二页数据 -->
select * from employee limit 10, 10;

聚合函数

作用： 将一列数据作为一个整体，进行纵向计算。常见聚合函数：

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

<font color=red>注意: null值不参与所有聚合函数运算。</font>

<!-- 1.查询员工数量 -->
select count(*) from employee;
select count(id) from employee;

<!-- 2.查询员工的平均年龄 -->
select avg(age) from employee;

<!-- 3.查询员工的最大年龄 -->
select max(age) from employee;

<!-- 4.查询员工的最小年龄 -->
select min(age) from employee;

<!-- 5.查询员工的年龄之和 -->
select sum(age) from employee;

3.1.4 DCL

DCL（Data Control Language）数据控制语言，用于控制对数据库的访问权限。主要包括授权和撤销权限两个操作。

DCL.png

管理用户

• 查询用户

USE mysql;
SELECT * FROM user;

<font color=red>在MySQL中，用户的信息、用户的权限数据，都存放在系统数据库mysql中的user表中。</font>

• 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

• 修改用户密码

ALTER USER 'username'@'host' IDENTIFIED BY 'new_password';

• 删除用户

DROP USER 'username'@'host';

<font color=red>
注意：
主机名可以使用%通配
这类SQL开发人员操作的比较少，主要是DBA(Database Administrator数据管理员)使用。
</font>

权限控制

DCLAuths.png

• 查询权限

SHOW GRANTS FOR 'username'@'host';

• 授予权限

GRANT privileges ON database_name.table_name TO 'username'@'host';

• 撤销权限

REVOKE privileges ON database_name.table_name FROM 'username'@'host';

<font color=red>
注意：
privileges可以是SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、INDEX、ALTER、LOCK TABLES、REFERENCES、EXECUTE、CREATE TEMPORARY TABLES、CREATE VIEW、SHOW VIEW、CREATE ROUTINE、ALTER ROUTINE、EVENT、TRIGGER等。

1. 多个权限之间，使用逗号分隔
2. 授权时，数据库名和表名可以使用*进行通配，代表所有。
   </font>

3.2 函数

函数是指一段可以直接被另一段程序调用的程序或代码。

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

字符串函数

MySQL中内置了很多字符串函数，常见的几个如下：

StrFunctions.png

数值函数

ValueFunctions.png

日期函数

DateFunctions.png

流程函数

FlowFunctions.png

3.3 约束

概述

约束是作用于表中字段中的规则，用于限制存储在表中的数据。最终保证数据库中的数据的正确、有效性和完整性。

<font color=red>注意：约束是作用于表中字段上的，可以在创建/修改表的时候添加约束。</font>

案例

FlowFunctions.png

CREATE TABLE `user` (
  id int PRIMARY KEY AUTO_INCREMENT commnet '主键',
  name varchar(10) NOT NULL unique comment '姓名',
  age int check(age>0 && age<120) comment '年龄',
  status char(1) default '1' comment '状态',
  gender char(1) comment '性别'
) comment '用户表';

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

ConstraintForeignKey.png

<font color=red size =3>注意：目前上述的两张表，在数据库层面，并为建立关联，所以是无法保证数据的一致性和完整性。</font>

• 创建表时-添加外键

CREATE TABLE 表明 (
    字段名 数据类型
    [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
)

• 修改表时-添加外键

ALTER TABLE 表明 ADD [CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

• 示例

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

删除外键

alter table emp drop foreign key fk_emp_dept_id;

删除/更新行为

alter table 表名 ADD constraint 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;


alter table 表名 ADD constraint 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表(主表列名) ON UPDATE set null ON DELETE set null;

3.4 多表查询

3.5 事务

3.6 索引

四、Advanced

4.1 存储引擎

4.1.1 MySQL体系结构

截屏2025-10-03 15.45.33.png

• 连接层
  最上层是一些客户端和链接服务，主要完成一些类似于连接处理、授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
• 服务层
  第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。
• 引擎层
  存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。
• 存储层
  主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

4.1.2 存储引擎简介

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。默认存储引擎为InnoDB。

• 在创建表时，指定存储引擎

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释]
    ...
    字段2 字段2类型 [COMMENT 字段2注释]
) ENGINE = INNODB [COMMENT 表注释];

• 查看当前数据库支持的存储引擎

SHOW ENGINES;

4.1.3 存储引擎特点

InnoDB

• 介绍。InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5之后，InnoDB是默认的 MySQL 存储引擎。

• 特点：

  • DML操作遵循ACID模型，支持事务；
  • 行级锁，提高并发访问性能；
  • 支特外键 FOREIGN KEY约束，保证数据的完整性和正确性；

• 文件

  • xxx.ibd:xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm、sdi）、数据和索引。
    参数: innodb_file_per_table

• 逻辑存储结构

InnoDBStorageStructure.png

MyISAM

MyISAM是MySQL早期的默认存储引擎。

• 特点：
  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快
• 文件
  • xxx.sdi: 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

Memory

Memory引擎的表数据时存储在内存中的，由于收到硬件问题、或断点问题的影响，只能将这些表作为临时表或缓存使用。

• 特点：
  • 内存存放
  • hash索引(默认)
• 文件
  • xxx.sdi: 存储表结构信息

4.1.4 存储引擎选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

InnoDB ：是Mysal的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InoDB存储引擎是比较合适的选择。

MyISAM：如果应用是以读操作和插入操作主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

MEMORY：将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

4.2 索引

索引

4.3 SQL优化

插入数据、主键优化、order by 优化、group by优化、limit优化、cout优化、update优化。

4.4 视图

4.5 存储过程

存储过程

4.6 触发器

4.7 锁

4.8 InnoDB引擎

4.9 MySQL管理

4.9.1 系统数据库

以8.0为例，MySQL数据库安装完成后，自带了以下四个数据库，具体作用如下：

• information_schema：提供关于数据库的元数据信息，包括表、视图、列、索引等。
• mysql：存储MySQL服务器的相关信息，包括用户、权限、配置等。
• performance_schema：提供关于服务器性能的相关信息，包括服务器状态、查询性能、连接信息等。
• sys：存储系统相关信息，包括进程列表、配置参数、全局变量等。

4.9.2 常用工具

1.mysql

该mysql不是指mysql服务，而是指mysql的客户端工具。

语法：
  mysql [options] [database]
选项：
  -u, --user=name        登录用户名
  -p, --password[=pass]  登录密码
  -h, --host=name        服务器主机名IP或域名
  -P, --port=num         服务器端口号
  -e, --execute=name     执行SQL语句并退出

• e选项可以在mysql客户端执行SQL语句，而不用连接到MySQL服务库再执行，对于一些批处理脚本，这种方式尤其方便。

示例:
    mysql -uroot -p123456 db01 -e "select * from table;"

2.mysqladmin

mysqladmin是一个执行管理操作的客户端程序。可以用它来检查服务器的配置和当前状态、创建并删除数据库等。

通过帮助文档查看选项:
    mysqladmin --help

示例：
    mysqladmin -uroot -p123456 drop 'test01';
    mysqladmin -uroot -p123456 version;

3.mysqlbinlog

由于服务器生成的二进制日志文件以二简直格式保存，所以如果想要检查这些文本的文本格式，就会使用到mysqlbinglog日志管理工具。

语法： 
    mysqlbinlog [options] log-files1 log-files2...

选项：
    -d, --database=name    指定数据库名称，只列出指定的数据库相关操作
    -0, --offset=#         忽略掉日志中的前n行命令
    -r, --result-file=name 将输出的文本格式日志输出到指定文件
    -s, --short-form       显示简单格式，省略掉一些信息。
    -start-datetime=date1 --stop-datetime=date2  指定日期间隔内的所有日志
    -start-position=pos1 --stop-position=pos2.   指定位置间隔内的所有日志

4.mysqlshow

5.mysqldump

mysqldump客户端工具用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表，及插入表的SQL语句。

语法：
    mysqldump [options] db_name [tables]
    mysqldump [options] --database/ -B db1 [db2 db3...]
    mysqldump [options] --all-databases/-A

连接选项：
    -u, -user=name        登录用户名
    -p, -password[=name]  指定密码
    -h, -host=name        指定服务器ip或域名
    -P, --port=#          指定连接端口
输出选项：
    --add-drop-database   在每个数据库创建语句前加上drop database语句
    --add-drop-table.     在每个表创建语句前加上drop table语句，默认开启；不开启（--skip-add-drop-table）

6.mysqlimport/source

mysqlimport是客户端数据导入工具，用来导入mysqldump加-T参数后导出的文本文件。

语法：
    mysqlimport [options] db_name textfile1 [testfile2...]
示例：
    mysqlimport -uroot -p123456 /tmp/city.txt

如果需要导入sql文件，可以使用mysql中的source指令：

语法：
    source /root/xxxxxx.sql

4.9.3 工具总结

<!-- 1.mysql -->
Mysql 客户端工具，-e 执行SQL并退出

<!--- 2.mysqladmin -->
MySQL管理工具

<!-- 3.mysqlbinlog -->
二进制日志查看工具

<!-- 4.mysqlshow -->
查看数据库、表、字段的统计信息

<!-- 5.mysqldump -->
数据库备份工具

<!-- 6.mysqlimport/source -->
数据导入工具

五、Op And Maintenance

5.1 日志

5.1.1 错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。
该日志是默认开启的，默认存放目录/var/log/，默认的日志文件名为mysqld.log。查看日志位置：show variables like '%log_error%';，实时查看尾部出现的日志tail -f /var/log/mysqld.log。

5.1.2 二进制日志

5.1.3 查询日志

5.1.4 慢查询日志

5.2 主从复制

5.3 分库分表

5.4 读写分离

六、Cases

七、FAQ

7.1以下SQL语句，哪个执行效率高？为什么？

select * from user where id = 10;与select * from user where name = 'Arm';

select * from user where id = 10;语句执行效率高，因为只需查询聚集索引即可。

select * from user where name = 'Arm';语句执行效率低，因为需要查询聚集索引和二级索引。
先查询二级索引找到对应主键值，再到聚集索引中查询对应的行数据。

7.2@Resouce和@AutoWired应用场景、关系、区别

7.3实现层分为两层与一层的区别

class xxx extends ServiceImpl<XXXMapper, XXXList>

- LogServiceImpl 业务层职责：
业务逻辑处理：实现 ILogService 接口定义的业务方法，如 addLogList、logList、valid 等
事务管理：通过 @Transactional 注解管理业务事务
服务编排：协调调用多个服务组件，如 LogListService、OtherService、xxxDao 等
业务规则验证：执行业务规则检查，如白名单重复性验证、状态检查等
复杂业务流程：处理密钥更新、历史记录管理等复合操作

- LogListService 数据访问层职责：
实体操作：专门负责 logList 实体的CRUD操作
数据查询：提供针对白名单数据的查询方法，如 lambdaQuery() 查询
数据持久化：执行数据的保存、更新、删除等基础操作
MyBatis-Plus集成：继承 IService<OpLogList>，提供通用数据访问能力
数据访问优化：专注于数据层面的操作优化和封装

# 分工优势：
关注点分离：业务逻辑与数据访问逻辑分离，职责清晰
可维护性：各层职责单一，便于维护和扩展
复用性：数据访问逻辑可在不同业务场景中复用

八、Appendix