一文整理完MySQL关系型数据库相关知识

1. 介绍

在开发中，数据库是专门用来存取数据的软件。数据库的职责就是管理数据的。

根据存取数据的类型分为关系型数据库和非关系型数据库

数据存储后呈现出来的效果类似上图的，是关系型数据库

数据存储后呈现出来的效果类似上图的，是非关系型数据库

1.1 MySQL

官网：http://www.mysql.com

2. 安装

docker方式安装MySQL数据库

前提：安装好了docker软件

1、拉取MySQL镜像

docker pull mysql:5.7   # 拉取 mysql 5.7
docker pull mysql       # 拉取最新版mysql镜像

2、docker run镜像创建容器

docker run --name mysql -e MYSQL_ROOT_PASSWORD=123456 -p 3306:3306 -d mysql:5.7

• –name：容器名，此处命名为mysql
• -e：配置信息，此处配置mysql的root用户的登陆密码
• -p：端口映射，此处映射容器的3306端口到主机3306端口
• -d：后台运行容器，保证在退出终端后容器继续运行

设置MySQL挂载目录，启动MySQL容器操作步骤

# MySQL挂在目录创建
mkdir -p /home/mysql/{conf,data,log,mysql-files}

# 新建配置文件
vim /home/mysql/conf/my.cnf

# 配置文件如下：
[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4

[mysqld]

character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names=1
init_connect='SET NAMES utf8'
max_connections=5000
wait_timeout=20000
max_user_connections=5000
max_allowed_packet=128M
thread_stack=262144

# 容器启动命令
docker run --restart=always --privileged=true --name mysql-container \
-v /home/mysql/conf:/etc/mysql/conf.d \
-v /home/mysql/data:/var/lib/mysql \
-v /home/mysql/log:/var/log \
-v /home/mysql/mysql-files:/var/lib/mysql-files \
-p 3306:3306 -e MYSQL_ROOT_PASSWORD='123456' -id mysql:latest

3. SQL语句

SQL的英文是Structured Query Language，简称SQL，是一种操作关系型数据库的结构化查询语言。操作数据库，最经常使用就是查询操作。

1、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写；

2、SQL语句可以使用单行或多行书写，以分号结尾

3、注释

单行注释

- 单行注释

# 单行注释

多行注释

/*
多行注释
多行注释
*/

4. SQL分类

分类	说明	作用	备注
DDL	数据定义语言	用来对数据库、表、列的定义	data definition language
DML	数据操作语言	用来对数据库中表的数据进行增、删、改操作	data manipulation language
DQL	数据查询语言	用来对数据库中表的数据进行查询	data query language
DCL	数据控制语言	用来对数据库的控制（用户创建、权限控制）	data control language

5. DDL

5.1 库的DDL

展示所有的数据库

show databases;

创建数据库

create database 数据库名称 charset=utf8;
create database if not exists 数据库名称 charset=utf8;

示例：

create database db1 charset=utf8;
create database if not exists db1 charset=utf8;

删除数据库

drop database 数据库名称;
drop database if exists 数据库名称;

示例：

drop database test;
drop database if exists test;

使用数据库

use 数据库名称;

查询正在使用的数据库

select database();

小结：

命令	作用	示例
show databases;	查看所有数据库	show databases;
create database if not exists 数据库名 charset=utf8;	创建数据库	create database if not exists db1 charset=utf8;
use 数据库名;	使用数据库	use db1;
select database();	查看当前使用的数据库	select database();
drop database if exists 数据库名;	删除数据库	drop database if exists db1;

5.2 表、列的DDL

列的内容可以是数字、字符串、时间等，由数据类型约束

数值数据类型

数据类型	字节数	有符号	无符号
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个字节	-263~263-1	0~2^64-1
float	单精度，4个字节	-231~231-1	0~2^32-1
double	双精度，8个字节
decimal[M, D]	双精度，8个字节

decimal(5,2)，表示共5位数字，其中2位是小数，比如：888.88

字符串数据类型

数据类型	长度	用途
char(size)	最大255个字符	数据是定长，如md5的密码，邮编，手机号，身份证号等
varchar(size)	最大65535个字节	存放一般内容长度
tinytext	最大255个字节
text	最大65535个字节
mediumtext	最大16777215个字节	大段文本时，如新闻、文章、论文等
longtext	最大4294967295个字节	大段文本时，如新闻、文章、论文等

1、VARCHAR(size)：0~65535字节 可变长度字符串，最大65532字节，1-3个字节用于记录大小【utf8编码size最大21844字符，gbk编码最大32766字符 】

2、查询速度：char > varchar

3、text与char、varchar不同的是，text不可以有默认值，能用varchar的地方不用text

日期+时间数据类型

数据类型	格式
date	年-月-日，如：2024-4-8
datetime	年-月-日 时:分:秒，如：2024-4-8 16:17:40
timestamp	年-月-日 时:分:秒，如：2024-4-8 16:17:40
time	时:分:秒，如：16:17:40
year	年，如：2024

datetime保存时间的范围： 1000-01-01 00:00:00 到 9999-12-31 23:59:59

timestamp保存时间的范围： 1970-01-01 00:00:01 到 2038-01-19 03:14:07

展示当前数据库所有表

show tables;

描述表结构

desc 表名;

查看表的创建语句

show create table 表名;

创建表

create table 表名(
		字段名1 数据类型,
		字段名2 数据类型,
		...
		字段名n 数据类型  # 最后一个不需要逗号(,)
);

删除表

drop table 表名称;
drop table if exists 表名称;

修改表、列

# 修改表名
alter table 表名 rename to 新的表名;

# 增加一列
alter table 表名 add 列名 数据类型;

# 修改列数据类型
alter table 表名 modify 列名 新数据类型;

# 修改列名和数据类型
alter table 表名 change 列名 新列名 新数据类型;

# 删除列
alter table 表名 drop 列名;

小结：

命令	作用
show tables;	查看当前数据库中所有表
desc 表名;	查看表结构
show create table 表名;	查看表的创建语句
alter table 表名 rename to 新的表名;	修改表名
alter table 表名 add 列名 类型;	添加字段
alter table 表名 modify 列名 类型及约束;	修改字段数据类型
alter table 表名 change 原名 新名 类型及约束;	修改字段名和数据类型
alter table 表名 drop 列名;	删除字段
drop table 表名;	删除表

6. DML

6.1 添加数据

添加指定列数据

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...)...;

6.2 修改数据

修改表数据

update 表名 set 列名1=值1 列名2=值2...[where条件];

注：如果不加where条件，就是对该表所有行内容进行修改

6.3 删除数据

删除表数据

delete from 表名 [where条件]

注：如果不加where条件，就是对该表所有行内容进行删除

7. DQL

完整语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定

- 创建数据库
create database if not exists db1

- 创建数据库表
use db1;

create table if not exists stu(
	id int(11),
    username varchar(32),
    sex tinyint(1),
    age tinyint(3),
    math tinyint(3),
    chinese tinyint(3)
);

- 添加数据
insert into stu values(1,'小张',1,16,75,86),(2,'小李',1,16,76,86),(3,'小王',0,17,76,87),(4,'小胡',0,17,77,87),(5,'小丘',0,18,77,88),(6,'小刘',0,18,80,90);

7.1 基础查询

- 查询指定字段
select id,username from stu;

- 查询表所有字段
select * from stu;

- 去除重复记录
select distinct sex from stu;

- 字段取别名
select id,sex as gender,math shuxue from stu;

7.2 条件查询

select 字段列表 from 表名 where条件

where后面支持多种运算符

运算符	功能描述
比较运算符	=、>、>=、<、<=、!=、<>
逻辑运算符	and、&&、or、||、not
模糊查询	like
范围查询	between…and…、in(…)
空判断	is null、is not null

模糊查询使用like关键字，可以使用通配符进行占位

_：代表单个任意字符

%：代表任意个字符

正则表达式

select * from stu where class_id REGEXP '1|7';

7.3 排序查询

select 字段列表 from 表名 order by 排序字段名1 [,排序字段2]...;

注：排序方式有上序ASC，降序DESC，默认情况下是升序ASC

select * from stu order by math desc,chinese desc;

7.4 聚合函数

在进行查询操作时，往往需要对一整列进行运算，例如成绩的平均分

函数名	功能
count(列名)	统计数量(一般选用不为null的列)
max(列名)	最大值
min(列名)	最小值
sum(列名)	求和
avg(列名)	平均值

select 聚合函数 from 表名;

注：NULL值不参与聚合函数运算

7.5 分组查询

select 字段列表 from 表名 [where分组前的条件限定] group by 分组字段名 [having 分组后的条件过滤];

注：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义

select sex,max(math),max(chinese) from stu group by sex;

7.6 分页查询

select 字段列表 from 表名 limit 查询起始索引,查询条目数

注：limit关键字中，查询起始索引是从0开始的

8. 约束

约束是作用于数据库表中列上的规则，用于限制添加数据的行为。从而保证数据库中数据的正确性、有效性和完整性。

8.1 约束分类

约束类型	关键字	功能
非空约束	NOT NULL	保证列中所有数据不能有NULL值
唯一约束	UNIQUE	保证列中所有数据各不相同
主键约束	PRIMARY KEY	主键是一行数据的唯一标识，要求非空且唯一
默认约束	DEFAULT	保存数据时，未指定值则采用默认值
外键约束	FOREIGN KEY	外键用来让两个表数据建立关联，保证数据的一致性和完整性
检查约束	CHECK	保证列中的值满足某一条件

create table if not exists stu(
	id int(11) unsigned auto_increment primary key not null,
    username varchar(32) not null unique,
    sex tinyint(1) default 1 not null,
    age tinyint(3) not null,
    math tinyint(3) default 0 not null,
    chinese tinyint(3) default 0 not null
);

9. 多表查询

一次性从多张表中查询需要的数据

create table class(
	class_id int(11) unsigned auto_increment primary key not null,
    class_name varchar(32) not null unique
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

create table stu(
	stu_id int(11) unsigned auto_increment primary key not null,
    class_id int(11) not null,
    stu_name varchar(32) not null unique
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;

insert into class(class_name) values('一班'),('二班');

insert into stu(class_id,stu_name) values(1,'小张'),(1,'小李'),(2,'小孙'),(2,'小杨');

在MySQL中多表查询分为连接查询和子查询，连接查询又分为内连接和外连接，内连接又分为隐式内连接和显示内连接，外连接又分为左外连接和右外连接。

9.1 内连接查询

- 隐式内连接查询
select 字段列表 from 表1,表2... where 条件;

- 显式内连接查询
select 字段列表 from 表1 [inner] join 表2 on 条件;

示例

- 隐式内连接查询
select * from class,stu where class.class_id=stu.class_id;

- 显式内连接查询
select * from class inner join stu on class.class_id=stu.class_id;

9.2 外连接查询

左外连接查询：相当于查询A表所有数据和交集部分数据

右外连接查询：相当于查询B表所有数据和交集部分数据

- 左外连接查询
select 字段列表 from 表1 left [outer] join 表2 on 条件;

- 右外连接查询
select 字段列表 from 表1 right [outer] join 表2 on 条件;

示例

select * from stu left outer join class on stu.class_id=class.class_id;

select * from stu right outer join class on stu.class_id=class.class_id;

9.3 子查询

指查询中嵌套有查询

子查询语句结果是单行单列，子查询语句作为条件值，使用>、>=、<、<=、=、!=等进行条件判断。

# class_id 大于 1班的class_id的学生
select * from stu where class_id > (select class_id from class where class_name='一班');

子查询语句结果是多行单列，子查询语句作为条件值，使用in等关键字进行条件判断

# 查询存在班级的学生
select * from stu where class_id in (select class_id from class);

子查询语句结果是多行多列，子查询语句作为虚拟表

select * from stu,(select * from class) c where stu.class_id=c.class_id;

10. 事务

使用场景：转账、下单扣库存

语法：

# 开启事务
start transaction;
或
begin;

-- 执行各种操作

# 回滚事务
rollback;

# 提交事务
commit;

事务特征：

1、原子性，事务是不可分割最小操作单位，要么同时成功，要么同时失败

2、一致性，事务完成时，必须使所有数据都保持一致状态

3、隔离性，多个事务之间，操作可见性

4、持久性，事务一旦提交或回滚，它对数据库中数据的改变是永久的

10.1 隔离级别

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。在事务的并发操作中可能会出现脏读，不可重复读，幻读。

读未提交(Read uncommitted)：

解释：

一个事务读到了另一个事务还没有提交的数据。

例如：

A给B转账，
A转给B，10万，点转账，但未点确认
B查账户看到10万
A及时发现，点撤回，修改为1万，再点转账，再点确认

分析：

A给B最终转账是1万，但是在过程中，B能看到过程数据。这就是脏读。

读已提交(Read committed)：
解释：

一个事务要等另一个事务提交后才能读取数据。

例如：

父亲的银行卡有10万
儿子拿着这个银行卡去买单，同时父亲准备转款(开启事务)。此时儿子看到银行卡里有10万
等儿子看完有10万后，钱被父亲转走，并提交
收费系统准备在这个银行卡扣款，再次检测，发现卡里没有钱了

分析：

这就是读已提交，若有事务对数据进行更新操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。
这个例子中，有读两次卡里的钱，一次是10万，一次是没有钱了，这就是不可重复读。

可重复读(Repeatable read)：
解释：

同一事务下，事务在执行期间，多次读取同一数据时，能够保证读取到的数据是一致的。

例如：

父亲的银行卡有10万
儿子拿着这个银行卡去买单，此时儿子看到银行卡里有10万，这个时候父亲想转账，发现转不了
等儿子买完单，父亲才能够实行转账行为

分析：

读数据的时候，不允许该数据有写数据的事务。因为写数据会改变数据。这样子就解决了不可重复读的问题。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

解释：

一个事务读取到了另一个事务新增的数据

例如：

儿子某一天去消费，花了8千元，然后他的父亲去查看他今天的消费记录（全表扫描，儿子事务开启），看到确实是花了8千元，就在这个时候，儿子花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当父亲打印儿子的消费记录清单时（儿子事务提交），发现花了1.8万元，似乎出现了幻觉，这就是幻读。

串行化(Serializable)：

解释：

它是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率最低，比较耗费数据库性能，一般不推荐使用。

小结：

隔离级别	脏读	不可重复读	幻读
读未提交	可能出现	可能出现	可能出现
读已提交	不会出现	可能出现	可能出现
可重复读	不会出现	不会出现	可能出现
串行化	不会出现	不会出现	不会出现

隔离级别查询

-- MySQL8以前
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

-- MySQL8开始
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

修改隔离级别

-- 建议开发者在修改时，仅修改当前session隔离级别即可
-- REPEATABLE-READ,MySQL默认级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE-READ

11. 函数

11.1 数学函数

函数	含义
abs(x)	返回x的绝对值
rand()	返回0到1的随机数
mod(x,y)	返回x除以y以后的余数
power(x,y)	返回x的y次方
round(x)	返回离x最近的整数
round(x,y）	保留x的y位小数四舍五入后的值
sqrt(x)	返回x的平方根
truncate(x,y)	返回数字 x 截断为 y 位小数的值
ceil(x)	返回大于或等于 x 的最小整数
floor(x)	返回小于或等于 x 的最大整数
greatest(x1,x2…)	返回返回集合中最大的值
least(x1,x2…)	返回返回集合中最小的值

11.2 字符串函数

函数	含义
trim()	返回去除指定格式的值
concat(x,y)	将提供的参数 x 和 y 拼接成一个字符串
substr(x,y)	获取从字符串 x 中的第 y 个位置开始的字符串，跟substring（）函数作用相同
substr(x,y,z)	获取从字符串 x 中的第 y 个位置开始长度为z 的字符串
length(x)	返回字符串 x 的长度
replace(x,y,z)	将字符串 z 替代字符串 x 中的字符串 y
upper(x)	将字符串 x 的所有字母变成大写字母
lower(x)	将字符串 x 的所有字母变成小写字母
left(x,y)	返回字符串 x 的前 y 个字符
right(x,y)	返回字符串 x 的后 y 个字符
repeat(x,y)	将字符串 x 重复 y 次
space(x)	返回 x 个空格
strcmp(x,y)	比较 x 和 y，返回的值可以为-1,0,1
reverse(x)	将字符串 x 反转

11.3 日期函数

函数	含义
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳

12. DCL

12.1 用户管理

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
-- 实例：
CREATE USER 'java'@'%' IDENTIFIED BY 'asd123crl';

删除用户

drop user 用户名@IP;
-- 实例：
drop user 'java'@'%';

修改密码

UPDATE `user` SET `Password` = PASSWORD('新密码') WHERE `User` = '用户名';
-- 实例：
UPDATE `user` SET `Password` = PASSWORD('asd123') WHERE `User` = 'java';

12.2 权限管理

常用权限：

• 表数据: select, update, delete, insert
• 表结构: create, alert, drop
• 外键: references
• 创建临时表: create temporary tables
• 操作索引: index
• 视图: create view, show view
• 存储过程: create routine, alert routine, execute
• 所有权限: all

查看用户权限命令

SHOW GRANTS FOR '用户名'@'主机名';
-- 实例：
SHOW GRANTS FOR 'root'@'%';

给用户授予权限

在MySQL中使用GRANT命令给用户授权，如果用户不存在，GRANT会自动创建用户，并进行授权。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';  -- 权限列表中如果有多个权限则用逗号,隔开
 
-- 授权所有库的所有表的所有权限
GRANT ALL ON *.* TO '用户名'@'主机名';  
-- 示例：给张三赋予db_test数据库students表的查询权限
GRANT SELECT ON db_test.students TO 'zhangsan'@'localhost';
-- 刷新权限
flush privileges

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
-- 示例：撤销张三在db_test.students表的查询权限
REVOKE SELECT ON db_test.students FROM 'zhangsan'@'localhost';
-- 刷新权限
flush privileges

13. 备份与还原

备份语法：

mysqldump -h 服务器 -u 用户名 -p -B 数据库名1 数据库2 数据库n > 备份文件.sql

示例：

mysqldump -u root -p -B test > /backup/123.sql

还原语法：

mysqldump -h 服务器 -u 用户名 -p密码 --databases 数据库名 < 备份文件.sql

# 进入数据库后
source 备份文件.sql;

示例：

mysqldump -u root -p --databases test < /backup/123.sql

一般系统推荐的字符集和排序规则

# 字符集
utf8mb4

# 排序规则，不区分大小写
utf8mb4_general_cli

字符集(Character Set)：utf8mb4

排序规则 | 字符序(Collate)：utf8mb4_general_cli 或 utf8mb4_bin

14. 表设计流程

14.1 三级模式和两级映射

视图：一个虚拟表(逻辑上的表)，其内容由查询定义(仅保存SQL查询语句)

14.2 数据库设计过程

14.3 需求说明和数据字典

需求说明：

能够记录学生信息(学号、姓名、性别、年龄)，也能够记录课程信息(课程名、任课老师)，一个学生可以选择多门课程，并需要记录学生学习课程的成绩。

数据字典：

学生信息：学号、姓名、性别、年龄

课程信息：课程名、任课老师

成绩信息：学生成绩

14.4 E-R模型

14.5 关系模式

学生信息表(student)

字段	说明
stu_id	主键
no	学号
name	姓名
sex	性别：男、女
age	年龄

课程信息表(course)

字段	说明
course_id	主键
course_name	课程名
teacher_name	任课老师

成绩信息表(score)

字段	说明
score_id	主键
stu_id	学生主键
course_id	课程主键
score	分数

15. 规范化理论

非规范的关系模式或者说设计出来的数据库表，可能存在的问题：

1、数据冗余

2、更新异常

3、插入异常

4、删除异常

15.1 范式

第一范式(1NF)：

数据库表的字段是不可再分的数据项。满足此规则，就是满足了第一范式。

年级名称	同学人数
	男同学	女同学
一年级	5	50
二年级	50	5

第二范式(2NF)：

在第一范式的基础上，如果主键是组合键时，其他字段不存在部分依赖。满足此规则，就是满足了第二范式。

学号	课程号	成绩	学分
S01	C01	60	2
S02	C01	70	2
S03	C02	80	4
S04	C02	90	4

第三范式(3NF):

在第二范式的基础上，如果其他字段不存在传递性依赖。满足此规则，就是满足了第三范式。

学号	姓名	系号	系名	系位置
S01	曹操	D1	计算机系	1号楼
S02	刘备	D1	计算机系	1号楼
S03	孙权	D2	信息系	2号楼
S04	袁绍	D2	信息系	2号楼

开发设计表格，一般要满足三大范式，不满足怎么办？拆…

不满足第一范式：

年级名称	同学人数
	男同学	女同学
一年级	5	50
二年级	50	5

| 年级名称 | 男同学 | 女同学 | | :------: | :----: | :----: | | 一年级 | 5 | 50 | | 二年级 | 50 | 5 |

不满足第二范式：

学号	课程号	成绩	学分
S01	C01	60	2
S02	C01	70	2
S03	C02	80	4
S04	C02	90	4

学号	课程号	成绩
S01	C01	60
S02	C01	70
S03	C02	80
S04	C02	90

课程号	学分
C01	2
C02	4

不满足第三范式：

学号	姓名	系号	系名	系位置
S01	曹操	D1	计算机系	1号楼
S02	刘备	D1	计算机系	1号楼
S03	孙权	D2	信息系	2号楼
S04	袁绍	D2	信息系	2号楼

学号	姓名	系号
S01	曹操	D1
S02	刘备	D1
S03	孙权	D2
S04	袁绍	D2

系号	系名	系位置
D1	计算机系	1号楼
D2	信息系	2号楼

拆了后存在问题，查询的时候，多表连表查询操作复杂或查询性能差。所以也存在反范式…

15.2 反范式

允许在数据库中引入冗余数据，以提高查询性能或简化查询操作…

特点：

1、冗余数据

2、数据冗余的更新

3、查询简化

运用场景：

1、高频读取、低频更新的情况

2、复杂查询和分析需求

3、数据仓库和报表生成

4、高并发和低延迟要求

5、特定优化需求

16. 并发控制

并发产生问题：

1、丢失更新

2、不可重复度读

3、读"脏"数据

解决方案：采用封锁协议。

S锁：读锁，也叫共享锁。若事务T对数据对象A加上S锁，则事务T可以读取A但不能修改A。其他事务只能对数据对象A加S锁，而不能加X锁，直到事务T释放对象A上的S锁。

X锁：写锁，也叫排它锁。若事务T对数据对象A加上X锁，则其他事务不能再对A加任何锁，直到事务T释放数据对象A上的锁。

一级封锁协议：

事务T1在修改数据对象A之前必须对数据对象A加上X锁，直到事务T1结束才释放X锁。可防止丢失更新。

二级封锁协议：

一级封锁协议加上事务T2在读取数据对象A之前先对其加上S锁，读完后即可释放S锁。可防止丢失更新，还可防止读"脏"数据。

三级封锁协议：

事务T1在读取数据对象A之前先对数据对象A加上S锁，直到事务结束才释放，事务T2在执行一级封锁协议。可防止丢失更新、防止读"脏"数据、防止数据重复读。

17. 优化

17.1 SQL及索引

当MySQL性能下降时，通过开启慢查询来获得哪条SQL语句造成的响应过慢，进行分析处理。

-- 是否开启慢查询
show variables like "%slow%";

-- 查询慢查询SQL状况
show status like "%slow%";

-- 慢查询时间
show variables like "long_query_time";

启用慢查询日志

1、添加或修改配置项

# 编辑MySQL配置文件(通常是my.cnf或my.ini)
[mysqld]
slow_query_log = 1 # 设置为1，启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log # 指定慢查询日志文件路径
long_query_time = 2 # 设置记录为慢查询的执行时间阈值，单位是秒

2、重启MySQL服务

慢查询分析工具：

mysqldumpslow，该工具是慢查询自带的分析慢查询工具，一般只要安装了mysql，就会有该工具

# 常用示例
# 取出使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
 
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/log/mysql/mysql-slow.log

# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/log/mysql/mysql-slow.log 
 
# 按照扫描行数最多的
mysqldumpslow -s r -t 10 -g 'left join' /var/log/mysql/mysql-slow.log

注意：使用mysqldumpslow的分析结果不会显示具体完整的sql语句，只会显示sql的组成结构

pt-query-digest，是一个perl脚本，只需下载并赋权即可执行

# 可以下载到PATH任意目录下
wget http://www.percona.com/get/pt-query-digest

chmod +x pt-query-digest

# 常用示例

# 直接分析慢查询文件
pt-query-digest slow.log > slow_report.log

# 分析最近12小时内的查询
pt-query-digest --since=12h slow.log > slow_report.log

# 分析指定时间范围内的查询
pt-query-digest slow.log --since '2024-01-01 09:30:00' --until '2024-05-01 10:00:00' > slow_report.log

explain可以用来分析SQL的执行计划

explain select * from salaries where from_date = '2024-01-01';

-- 以json 形式展示结果：
explain format=json select * from salaries where from_date = '2024-01-01';

索引

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等。

索引分类

1.普通索引index: 加速查找
2.唯一索引
    主键索引: primary key：加速查找+约束（不为空且唯一）
    唯一索引: unique：加速查找+约束 （唯一）
3.联合索引
    -primary key(id,name): 联合主键索引
    -unique(id,name): 联合唯一索引
    -index(id,name): 联合普通索引
4.全文索引fulltext: 用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial: 了解就好，几乎不用

索引类型

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长(innodb默认索引类型)

# 不同的存储引擎支持的索引类型也不一样
InnoDB，支持事务，支持行级别锁定，支持 B-tree、Full-text等索引，不支持 Hash 索引；
MyISAM，不支持事务，支持表级别锁定，支持 B-tree、Full-text等索引，不支持 Hash 索引；

索引使用

-- 在创建表时就创建索引
create table s1(
	id int ,#可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
);
    
-- 在创建表后再创建索引
create index name on s1(name); # 添加普通索引
create unique age on s1(age); # 添加唯一索引
alter table s1 add primary key(id); # 添加主键索引，也就是给id字段增加一个主键约束
create index name on s1(id,name); # 添加普通联合索引

-- 删除索引
drop index id on s1;
drop index name on s1; # 删除普通索引
drop index age on s1; # 删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
alter table s1 drop primary key; # 删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

如何选择合适的列建立索引

1、在where从句，group by从句，order by从句，on从句中出现的列

2、索引字段越小越好

3、离散力度大的列放到联合索引的前面

17.2 数据库结构优化

选择合适的数据类型

1、使用可以存下业务要求的最小的数据类型

2、使用简单的数据类型。int要比varchar类型在MySQL处理上简单

3、尽可能的使用not null定义字段

4、尽量少用text类型，非用不可时最好考虑分表

表的垂直拆分

1、把不常用的字段单独存放到一个表中

2、把大字段独立存放到一个表中

3、把经常一起使用的字段放到一起

表的水平拆分

1、解决单表数据量过大的问题，单表数据达到上万条

2、水平拆分方法：

进行hash运算，如果要拆分成5个表，则使用mod(id, number)取出0-4值

假如是订单表进行水平拆分，该id应该采用用户id来mod比较合适，这样相同用户的订单会存在同一张表里

针对不同的hash把数据存到不同的表中

前台考虑性能及个人，后台考虑统计及报表业务。前台可以采用拆分后的表，后台采用汇总后的表

17.3 系统配置优化

17.3.1 操作系统配置优化

网络方面的配置

要修改/etc/sysctl.conf文件

#增加tcp支持的队列数
net.ipv4.tcp_max_syn_backlog=65535
#减少断开连接时,资源回收
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10

打开文件数的限制

要修改/etc/security/limits.conf文件

*soft nofile 65535
*hard nofile 65535

除此之外最好在MySQL服务器上关闭iptables，selinux等防火墙软件

17.3.2 MySQL配置文件

查找配置文件

/etc/my.cnf
/etc/mysql/my.cnf
window(C:/windows/my.ini)

# 可以通过命令查找
mysqld --verbose --help | grep -A 1 'Default options'

注意：如果多个位置存在配置文件，则后面的同名配置项会覆盖前面的

常用参数

innodb_buffer_pool_size

配置innodb的缓冲池innodb_buffer_pool_size >= Total MB(引擎为Innodb的所有表中的数据和索引的总和)

select engine,round(sum(data_length+index_length)/1024/1024,1) as 'Total MB' from information_schema.tables where table_schema not in('information_schema','performance_schema') group by engine;

如果数据库中只有innodb表,则推荐配置量为总内存的75%

innodb_buffer_pool_instances

可以控制缓冲池的个数,默认情况下只有一个缓冲池

innodb_log_buffer_size

innode log 缓冲的大小，由于日志最长每秒钟就会刷新，所以一般不用太大

innodb_flush_log_at_trx_commit

对innodb的IO效率影响很大。值有0，1，2
默认值为1，一般建议为2，但如果数据安全性要求比较高则使用默认值1

innodb_read_io_threads和innodb_write_io_threads

决定innodb读写的IO进程数，默认为4

innodb_file_per_table

控制innodb每一个表使用独立的表空间，默认是OFF，也就是所有表都会简历在共享表空间中，建议设置成ON

innode_stats_on_metadata

决定了MySQL在什么情况下会刷新Innodb表的统计信息。建议设置成OFF

Linux配置文件一般在/etc/my.cnf或者/etc/mysql/my.cnf

windows配置文件一般在C:/windows/my.ini

如果存在多个位置存在配置文件，则后面的会覆盖前面的

第三方配置工具使用

https://tools.percona.com/wizard

17.4 服务器硬件优化

优化MySQL服务器硬件通常涉及以下几个方面：

• 更快的处理器：使用更快的CPU，如最新一代的Intel或AMD处理器。

• 更多的内存：增加服务器的RAM以加快磁盘访问速度。

• 更快的存储：使用SSD（固态硬盘）替代HDD（机械硬盘）。

• 更多的存储空间：扩展存储容量以适应数据增长。

• 网络适配器：对于高速网络，使用10GbE或更高速率的网卡。

• 电源：确保服务器供电足够，高效率的电源设备能提供稳定的电力。

用于检查服务器硬件规格

# 检查CPU信息
cat /proc/cpuinfo | grep "model name" | uniq
 
# 检查内存使用情况
free -h
 
# 检查存储设备类型和使用情况
lsblk
df -h
 
# 检查网络适配器
lspci | grep -i ethernet