MySQL高级 - 架构组成

「这是我参与11月更文挑战的第26天，活动详情查看：2021最后一次更文挑战」

高级MySQL介绍

什么是DBA?

数据库管理员，英文是Database Administrator，简称DBA。
百度百科介绍
数据库管理员（Database Administrator，简称DBA），是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。
DBA的核心目标是保证数据库管理系统的稳定性、安全性、完整性和高性能。
在国外，也有公司把DBA称作数据库工程师(Database Engineer)，两者的工作内容基本相同，都是保证数据库服务7*24小时的稳定高效运转，但是需要区分一下DBA和数据库开发工程师(Database Developer)：
1) 数据库开发工程师的主要职责是设计和开发数据库管理系统和数据库应用软件系统，侧重于软件研发；
2) DBA的主要职责是运维和管理数据库管理系统，侧重于运维管理

一个高级DBA的职责

负责MySQL的容量规划，架构设计及安装、部署
负责MySQL的日常管理，监控和维护，并对MySQL进行持续性能优化
负责MySQL开发支持，参与数据架构规划设计，以及相关业务的数据建模、设计评审、SQL代码审核优化

中级Java开发工程师对数据库知识的掌握程度

熟练操作主流数据库，能够通过代码(框架)完成日常的数据库操作
熟练使用SQL，熟悉SQL优化，熟悉存储过程视图等创建及使用
了解MySQL的整体体系结构，了解MySQL事务存储引擎的特点
了解MySQL索引优化，了解MySQL相关锁机制

我们作为Java开发工程师，关注的应该是跟开发相关的数据库知识。了解这些高级的知识，目的是让我们编写出更加高效的应用程序。

专业的数据库维护、服务器优化、性能参数调优等等数据库相关的运维工作还是要交给DBA去做的

MySQL逻辑架构

学习MySQL就好比盖房子，如果想把房子盖的特别高，地基一定要稳，基础一定要牢固。学习MySQL数据库前要先了解它的体系结构，这是学好MySQL数据库的前提。

MySQL架构体系介绍

MySQL由连接池、SQL接口、解析器、优化器、缓存、存储引擎等组成，可以分为四层，即连接层、服务层、引擎层和文件系统层。

连接层
最上面是一些客户端和连接服务, 不是MySQL特有的，所有基于网络的C/S的网络应用程序都应该包括连接处理、认证、安全管理等。

服务层
中间层是MySQL的核心，包括查询解析、分析、优化和缓存等。同时它还提供跨存储引擎的功能，包括存储过程、触发器和视图等。

引擎层
存储引擎层，它负责存取数据。服务器通过API可以和各种存储引擎进行交互。不同的存储引擎具有不同的功能，我们可以根据实际需求选择使用对应的存储引擎

存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互

SQL查询流程

我们用一条SQL SELECT语句的执行轨迹来说明客户端与MySQL的交互过程，如下图所示。

通过客户端/服务器通信协议与MySQL建立连接
查询缓存，这是MySQL的一个可优化查询的地方，如果开启了Query Cache且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启Query Cache或者没有查询到完全相同的SQL语句则会由解析器进行语法语义解析，并生成解析树。
预处理器生成新的解析树。
查询优化器生成执行计划。
查询执行引擎执行SQL语句，此时查询执行引擎会根据SQL语句中表的存储引擎类型，以及对应的API 接口与底层存储引擎缓存或者物理文件的交互情况，得到查询结果，由MySQL Server过滤后将查询结果缓存并返回给客户端。若开启了Query Cache，这时也会将SQL语句和结果完整地保存到QueryCache中，以后若有相同的SQL语句执行则直接返回结果。

MySQL物理文件

物理文件包括：日志文件，数据文件，配置文件

日志文件

日志文件包括
- error log错误日志排错 /var/log/mysqld.log【默认开启】
- bin log二进制日志备份增量备份 DDL DML DCL
- Relay log中继日志复制接收 replication master
- slow log慢查询日志调优查询时间超过指定值

-- 查看错误日志文件路径
show variables like 'log_error';

+---------------+---------------------+
| Variable_name | Value |
+---------------+---------------------+
| log_error | /var/log/mysqld.log |
+---------------+---------------------+

-- 慢查询日志文件路径
show variables like 'slow_query_log_file';

+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+

-- bin log 日志文件 需要在 my.cnf 中配置
log-bin=/var/log/mysql-bin/bin.log
server-id=2

-- 查看 relay log 相关参数
show variables like '%relay%';

配置文件&数据文件

配置文件my.cnf

在my.cnf文件中可以进行一些参数设置，对数据库进行调优。

[client] #客户端设置，即客户端默认的连接参数
port = 3307 #默认连接端口
socket = /data/mysqldata/3307/mysql.sock #用于本地连接的socket套接字
default-character-set = utf8mb4 #编码

[mysqld] #服务端基本设置
port = 3307 MySQL监听端口
socket = /data/mysqldata/3307/mysql.sock #为MySQL客户端程序和服务器之间的本地通讯指定一个套接字文件
pid-file = /data/mysqldata/3307/mysql.pid #pid文件所在目录
basedir = /usr/local/mysql-5.7.11 #使用该目录作为根目录（安装目录）
datadir = /data/mysqldata/3307/data #数据文件存放的目录
tmpdir = /data/mysqldata/3307/tmp #MySQL存放临时文件的目录
character_set_server = utf8mb4 #服务端默认编码（数据库级别）

数据文件

-- 查看数据文件的位置
show variables like '%dir%';

+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| datadir | /var/lib/mysql/ |
+-----------------------------------------+----------------------------+

.frm文件不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta）信息都存放在此文件中，包括表结构的定义信息等。
.MYD文件 myisam存储引擎专用，存放myisam表的数据（data）。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的目录下
.MYI文件也是myisam存储引擎专用，存放myisam表的索引相关信息。每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样
.ibd文件存放innoDB的数据文件（包括索引）。
db.opt文件此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。