一文整理完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镜像
1
2

2、docker run镜像创建容器

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

• –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
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

3. SQL语句

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

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

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

3、注释

单行注释

- 单行注释

# 单行注释
1
2
3

多行注释

/*
多行注释
多行注释
*/
1
2
3
4

4. SQL分类

5. DDL

5.1 库的DDL

展示所有的数据库

show databases;
1

创建数据库

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

示例：

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

删除数据库

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

示例：

drop database test;
drop database if exists test;
1
2

使用数据库

use 数据库名称;
1

查询正在使用的数据库

select database();
1

小结：

5.2 表、列的DDL

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

数值数据类型

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

字符串数据类型

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

2、查询速度：char > varchar

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

日期+时间数据类型

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;
1

描述表结构

desc 表名;
1

查看表的创建语句

show create table 表名;
1

创建表

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

删除表

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

修改表、列

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

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

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

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

# 删除列
alter table 表名 drop 列名;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

小结：

6. DML

6.1 添加数据

添加指定列数据

insert into 表名(列名1,列名2...) values(值1,值2...);
1

添加全部列数据

insert into 表名 values(值1,值2...);
1

批量添加指定列数据

insert into 表名(列名1,列名2...) values(值1,值2...),(值1,值2...)...;
1

批量添加全部列数据

insert into 表名 values(值1,值2...),(值1,值2...)...;
1

6.2 修改数据

修改表数据

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

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

6.3 删除数据

删除表数据

delete from 表名 [where条件]
1

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

7. DQL

完整语法

SELECT
	字段列表
FROM
	表名列表
WHERE
	条件列表
GROUP BY
	分组字段
HAVING
	分组后条件
ORDER BY
	排序字段
LIMIT
	分页限定
1
2
3
4
5
6
7
8
9
10
11
12
13
14

- 创建数据库
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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

7.1 基础查询

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

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

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

- 字段取别名
select id,sex as gender,math shuxue from stu;
1
2
3
4
5
6
7
8
9
10
11

7.2 条件查询

select 字段列表 from 表名 where条件
1

where后面支持多种运算符

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

_：代表单个任意字符

%：代表任意个字符

正则表达式

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

7.3 排序查询

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

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

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

7.4 聚合函数

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

select 聚合函数 from 表名;
1

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

7.5 分组查询

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

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

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

7.6 分页查询

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

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

8. 约束

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

8.1 约束分类

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
);
1
2
3
4
5
6
7
8

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,'小杨');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

9.1 内连接查询

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

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

示例

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

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

9.2 外连接查询

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

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

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

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

示例

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;
1
2
3

9.3 子查询

指查询中嵌套有查询

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

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

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

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

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

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

10. 事务

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

语法：

# 开启事务
start transaction;
或
begin;

-- 执行各种操作

# 回滚事务
rollback;

# 提交事务
commit;
1
2
3
4
5
6
7
8
9
10
11
12

事务特征：

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

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

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

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

10.1 隔离级别

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

读未提交(Read uncommitted)：

解释：

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

例如：

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

分析：

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

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

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

例如：

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

分析：

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

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

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

例如：

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

分析：

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

什么时候会出现幻读？

解释：

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

例如：

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

串行化(Serializable)：

解释：

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

小结：

隔离级别查询

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

-- MySQL8开始
SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
1
2
3
4
5

修改隔离级别

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

11. 函数

11.1 数学函数

11.2 字符串函数

11.3 日期函数

12. DCL

12.1 用户管理

创建用户

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

删除用户

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

修改密码

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

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'@'%';
1
2
3

给用户授予权限

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

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

撤销权限

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

13. 备份与还原

备份语法：

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

示例：

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

还原语法：

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

# 进入数据库后
source 备份文件.sql;
1
2
3
4

示例：

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

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

# 字符集
utf8mb4

# 排序规则，不区分大小写
utf8mb4_general_cli
1
2
3
4
5

字符集(Character Set)：utf8mb4

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

14. 表设计流程

14.1 三级模式和两级映射

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

14.2 数据库设计过程

14.3 需求说明和数据字典

需求说明：

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

数据字典：

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

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

成绩信息：学生成绩

14.4 E-R模型

14.5 关系模式

学生信息表(student)

课程信息表(course)

成绩信息表(score)

15. 规范化理论

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

1、数据冗余

2、更新异常

3、插入异常

4、删除异常

15.1 范式

第一范式(1NF)：

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

第二范式(2NF)：

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

第三范式(3NF):

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

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

不满足第一范式：

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

不满足第二范式：

不满足第三范式：

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

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
2
3
4
5
6
7
8

启用慢查询日志

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 # 设置记录为慢查询的执行时间阈值，单位是秒
1
2
3
4
5

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
1
2
3
4
5
6
7
8
9
10
11
12

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

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

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

chmod +x pt-query-digest
1
2
3
4

# 常用示例

# 直接分析慢查询文件
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
1
2
3
4
5
6
7
8
9
10

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
2
3
4

索引

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

索引分类

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

索引类型

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

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

索引使用

-- 在创建表时就创建索引
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

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

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
1
2
3
4
5
6
7

打开文件数的限制

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

*soft nofile 65535
*hard nofile 65535
1
2

除此之外最好在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'
1
2

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

常用参数

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;
1

如果数据库中只有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
1

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
1
2
3
4
5
6
7
8
9
10
11
12