02-MySQL基础入门-部署与管理体系

## 1. 5.7,8.0版本企业规范部署,启动

### 1.1.准备
- 硬件:服务器标准化
```sql
与公司基础运维团队提供采购建议,根据业务场景和压力选择适合
```
- 软件:系统版本与系统参数优化
- 数据库版本标准化

```sql
1.确认Supported Platforms
https://www.mysql.com/support/

2.确认安装版本
推荐:20之后的小版本

3.获取MySQL软件包
https://downloads.mysql.com/archives/community/

# 选包下
https://dev.mysql.com/downloads/mysql/

4.MD5验证
```

![](/media/202403/2024-03-15_152430_9831290.45565129598521525.png)

- 数据库软件工具标准化
- CMDB平台标准化-软件打包

### 1.2.Mysql部署安装
https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html

在Ubuntu20.04 系统 2GB 4C
```shell
root@db01:~# apt install wget -y
root@db01:~# cd /usr/local/src/
root@db01:/usr/local/src# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz

1.解压
root@db01:/usr/local/src# tar xf mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz
--可直接用最新版本

2.软连接
root@db01:/usr/local/src# ln -s /usr/local/src/mysql-8.0.36-linux-glibc2.28-x86_64 /opt/mysql-8.0.36

3. 环境清理（centos7 清理，Ubuntu 忽略如下两步）
root@db01:/usr/local/src# yum remove -y mariadb-libs # 忽略
root@db01:/usr/local/src# yum install -y libaio-devel # 忽略

4. 创建用户
root@db01:/usr/local/src# useradd mysql

5. 创建目录
root@db01:/usr/local/src# mkdir -p /data/mysql3306/{log,etc,tmp,data}

6. 授权
root@db01:/usr/local/src# chown -R mysql.mysql /data/mysql3306/

7. 初始化数据
root@db01:/usr/local/src# echo 'export PATH=/opt/mysql-8.0.36/bin:$PATH' >> /etc/profile
root@db01:/usr/local/src# source /etc/profile
root@db01:/usr/local/src# mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql-8.0.36/ --datadir=/data/mysql3306/data/

8. 配置文件
root@db01:/usr/local/src# cat > /data/mysql3306/etc/my.cnf <<EOF 
[mysql]
# CLIENT #
no_auto_rehash
port = 3306
socket = /data/mysql3306/tmp/mysql.sock
default_character_set = utf8mb4
prompt = "\u:\p:\d>"

[mysqld]
user = mysql
port = 3306
default_storage_engine = InnoDB
character_set_server = utf8mb4
init_connect = 'SET NAMES utf8mb4'
transaction_isolation = REPEATABLE-READ
basedir = /opt/mysql-8.0.36
datadir = /data/mysql3306/data
socket = /data/mysql3306/tmp/mysql.sock
pid_file = /data/mysql3306/data/mysql.pid
tmpdir = /data/mysql3306/tmp/
slave_load_tmpdir = /data/mysql3306/tmp/

### LOGGING ###
#log_error_verbosity = 2
log_error = /data/mysql3306/log/mysql.err
general_log = 0
general_log_file = /data/mysql3306/log/mysql.log
EOF

root@db01:/usr/local/src# touch /data/mysql3306/log/mysql.err
root@db01:/usr/local/src# chown -R mysql.mysql /data/mysql3306

9. 启动数据库
#启动方式一：
root@db01:/usr/local/src# mysqld_safe --defaults-file=/data/mysql3306/etc/my.cnf --user=mysql &

#启动方式二：
root@db01:/opt/mysql-8.0.36/support-files# tree
.
├── mysql.server
└── mysqld_multi.server

root@db01:/opt/mysql-8.0.36/support-files# vim mysql.server 
# 编辑下面两个路径
basedir=/opt/mysql-8.0.36
datadir=/data/mysql3306/data

root@db01:/opt/mysql-8.0.36/support-files# cp mysql.server /etc/init.d/mysqld
root@db01:~# systemctl daemon-reload  # 看情况执行
root@db01:/opt/mysql-8.0.36/support-files# systemctl start mysqld

10.连接数据库
root@db01:~# mysql -uroot -p -S /tmp/mysql.sock			#端口连接
root@db01:~# mysql -uroot -p -hlocalhost -P 3306		#TCP链接

11.关闭数据库
mysqladmin shutdown -S /tmp/mysql.sock
root@db01:~# systemctl stop mysqld

# 命令补全
root@db01:~# apt-get install bash-completion
```

## 2.MySQL架构体系讲解
### 2.1.客户端与服务端模式
```shell
客户端遵从MySQL Protocol,一套用来连接MySQL client和MySQL Server端的协议，用来支持

- Connectors (Connector/C, Connector/J, and so forth)
- MySQL Proxy
- Communication between master and slave replication servers

连接方式:
Socket (Unix Scoket)
TCP/IP
API:C、PHP、JDBC、ODBC、.NET、Python、Go...
```

- MySQL基架的几大模块组件
	- Connectors:MySQL向外提供的交互接口
		-  对外交互组件,API,如java,python,go等语言可以通过该组件连接数据库
	- 管理服务组件和工具组件(Management Service & Utilities）
		- 集成对MySQL的管理,比如备份，恢复等
	-  Connection Pool:连接池组件
		- 负责监听对客户端向MySQL Server端的各种请求，接收请求，转发请求到目标模块。
		- 每个成功连接MySQL Server的客户请求都会被创建或分配一个线程，该线程负责客户端与MySQL Server端的通信，接收客户端发送的命令，传递服务端的结果信息等。
	- SQL Interface:SQL接口组件
		- 接收用户SQL命令，如DML,DDL和存储过程等，并将最终结果返回给用户
	- Parser:查询分析器组件
		- 分析SQL语法合法
		- SQL命令分解成数据结构
		-  失败则提示语法错误
	- Optimizer:优化器组件
		- 对SQL命令按照标准流程进行优化分析
	- Caches & Buffers:缓存主件
		- 缓存和缓冲组件
	- Pluggable Storage Engines:插件式存储引擎
		- MySQL对表的创建,数据的存储与检索靠引擎来实现
	- File System:物理文件
		- data
		- 日志
		- 配置文件等

![img](https://img.sunrisenan.com/img/2024/03/15/155523822)

![img](https://img.sunrisenan.com/img/2024/03/15/155817076)

![img](https://img.sunrisenan.com/img/2024/03/15/160016932.png)

### 2.2.一条SQL语句的执行

```shell
查询语句的执行分为以下几步：
1. 查询缓存
2. 解析器生成解析树
3. 预处理再次生成解析树
4. 查询优化器
5. 查询执行计划
6. 查询执行引擎
7. 查询数据返回结果
```
![image-20240315160346384](https://img.sunrisenan.com/img/2024/03/15/160348119.png)

![img](https://img.sunrisenan.com/img/2024/03/15/160247441)

#### 2.2.1.查询缓存

MySQL 8.0 已经删除了

```shell
mysql> show variables like 'query_cache%';
1.MySQL 拿到一个查询请求后先会在查询缓存中看看是否执行过此语句，之前执行的语句会以
key-value 的形式缓存在内存中，key 是缓存的语句，value 是查询的结果

2.如果命中缓存则直接将结果返回,如果没有命中则继续执行后面

**额外补充**生产环境中我一般会关闭cache，why?
1.命中率低:请求sql与环境(连接的数据库、协议版本、字符集等)要与缓存完全一致
2.资源消耗大:QC需要缓存最新的数据,所以在数据发生变化时(比如增删改操作)QC就会刷新,这
导致缓存会频繁的写入、擦除,消耗过多资源

3.QC适用场景少:以下场景无效
	读取mysql的系统表
	子查询或者外层查询
	查询语句中用到随机函数,或者now这种函数,给查询结果带来不确定性(每次查询结果会有差异)
	查询中用到了视图,临时表
	存储过程,触发器中调用的sql也不适合
	查询语句加了SQL_NO_CACHE
	查询语句未引用任何表
	类似select into这种sql,显示加锁的sql都不会去缓存
	验证用户的访问权限(用户无权限则拒绝)
	**全局锁控制**每次更新QC的内存块都需要进行锁定，查询结果必须大于
	MySQL 8.0 直接删除查询缓存

最为重要的是，在MySQL里QC是由一个全局锁在控制，每次更新QC的内存块都需要进行锁定。
例如，一次查询结果是20KB，参数query_cache_min_res_unit 值设置为 4KB），查询结果共需要分为5次写入QC，每次都要锁定，而且容易造成block碎片

所以QC适合表数据量少,更新频率低,查询比较多,比如配置表、权限表。

关闭方法很简单,有两种:
1、同时设置选项 query_cache_type = 0 和 query_cache_size = 0；
2、如果用源码编译MySQL的话，编译时增加参数 --without-query-cache 即可；

**生产环境怎么办**
缓存数据给第三方处理,比如redis
```
![image-20240315161015611](https://img.sunrisenan.com/img/2024/03/15/161017074.png)

**课后作业**

```shell
show status;
show variables;

是两个DBA运维MySQL最常用的两个查看命令，尝试去概览一遍大概的变量，没有基础的同学可
以挑几个感兴趣的值去官方文档或者百度一下，有基础的同学建议导图进行功能分类,比如

show variables like '%innodb%';
show status like '%Com%';

Com_select Com_insert Com_delete
```

#### 2.2.2.解析器与解析树

1.语法解析

```shell
语法解析是解析你的语句是不是满足 MySQL 语法标准，如果不对则会 ：
ERROR 1064 (42000): You have an error in your SQL syntax … 关于错误码在官网有说明

官方地址:
https://dev.mysql.com/doc/refman/8.0/en/error-message-elements.html
https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_open_as_readonly

#中文对照表
https://www.cnblogs.com/Nedved/p/10472467.html
```
2.词法解析

```sql
select name from user_info where sex=1 and age>20
```

![image-20240315161419207](https://img.sunrisenan.com/img/2024/03/15/161420719.png)

#### 2.2.3.生成解析树
同上

#### 2.2.4.查询优化器
1.生成执行计划

```sql
生成一个执行计划,要做一些事情：
选择最合适的索引；
选择表扫还是走索引；
选择表关联顺序；
优化 where 子句；
排除管理中无用表；
决定 order by 和 group by 是否走索引；
尝试使用 inner join 替换 outer join；
简化子查询，决定结果缓存；
合并视图；
```

2.查询执行计划

查询最后一次查询的消耗,选择开销最小的计划进行

```sql
show status like 'Last_query_cost';
```

#### 2.2.5.查询执行引擎与返回结果
这里执行器会先对权限做一个判断，如果有权限，才会执行以下步骤，否则跑出权限异常：

```sql
调用 Innodb 引擎接口获取这个表或索引的第一行，筛选sex=1和age>20的结果集
如果不是跳过，如果是则存在结果集中；
引擎执行下一行，重复判断相同的逻辑，直到最后一行；
最后将满足结果的结果集返回；
```

### 2.3.MySQL内部结构
#### 2.3.1.文件结构
![image-20240315161712680](https://img.sunrisenan.com/img/2024/03/15/161714186.png)

#### 2.3.2.内存结构
- 共享内存:整个数据库实例共享

```sql
show variables where variable_name in ('innodb_buffer_pool_size','innodb_l
og_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query
_cache_size');

#参数说明：
1.innodb_buffer_pool
- Innodb 引擎最重要的缓存区域,性能决定性参数之一。包含数据页、索引页、undo 页、i
nsert buffer、自适应哈希索引、锁信息以及数据字典等信息。
- 优点:提升数据库的性能、加快 SQL 运行速度
- 缺点:故障发生后值越大恢复速度较慢

2.innodb_log_buffer
- 存放redo log

3.innodb_additional_mem_pool
- 该部分主要存放 InnoDB 内的一些数据结构.

4.key_buffer
- MyISAM 表的重要缓存区域,索引缓存再此

5.query_cache
- 查询缓存
```

- Session私有内存:每个线程连接是独有

```sql
show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');
```

- 如果连接数为1000则,MySQL实例使用内存为
```sql
共享内存+1000Xsession内存
```

#### 2.3.3.线程结构
![image-20240315163436241](https://img.sunrisenan.com/img/2024/03/15/163438870.png)

![image-20240315163511565](https://img.sunrisenan.com/img/2024/03/15/163513908.png)

#### 2.1.3 MySQL 8.0的"新姿势"
-  Metadata 结构变化
	- 5.7 版本问题
		- 两套数据字典信息（Server层 frm，InnoDB 数据字典）
		- DDL无原子化
		- frm和innodb层会出现不一致
		- 并发处理需要小心处理（MDL,dict_sys::mutex,dict_sys::rw_lock）
		- 崩溃无法恢复
	- 8.0 变化
		- 支持事务性DDL，崩溃可以回滚，保证一致。
		- 保留一份数据字典信息，取消frm数据字典。
		- 数据字典存放至InnoDB表中
		- 采用套锁机制，管理数据字典的并发访问（MDL）
		- 全新的Plugin支持
			- 8.0.17+ 加入Clone Plugin,更好的支持MGR，InnoDB Cluster的节点管理
		- 安全加密方式改变
			- 改变加密方式为caching_sha2_password
			- SSL 将支持到 TLSv1.3 版本。
		- 用户管理及认证方式改变
			- 改变授权管理方式
			- 加入role角色管理
			- 添加更多权限
		-  原子性DDL
			-  支持原子性DDL
		- Cache && Buffer的变化
			- 取消Query Cache