“这是我参与8月更文挑战的第2天，活动详情查看：8月更文挑战”

1. MySQL 客户端和服务端模型

MySQL 是一种典型的 C/S 结构，C/S 结构即客户端/服务端模型。

服务端程序：mysqld MySQL 自带的客户端：mysql、mysqladmin、mysqldump 等

2. MySQL 的两种连接方式

通过网络连接串：TCP/IP
# mysql -uroot -p -h 127.0.0.1 -P3306
通过套接字文件，socket
# mysql -uroot -p -S /tmp/mysql.sock

3. 实例

3.1 通俗解释

首先来说说，数据库是做什么的。

数据库是用来长久存储数据的，而我们大家都知道内存只能临时存储，磁盘等才能真正存储数据。那么数据库会放在哪里呢？肯定是存放在磁盘上，所以数据库其实就是磁盘上的一个文件。

简单的理解就是：数据库 = 磁盘上的文件。

既然数据库可以看成是磁盘上文件，那么我们怎么使用数据库呢？

如果说我们可以直接使用数据库，那就等价于直接使用磁盘上的文件。我们还知道，我们只有把磁盘上的文件读入内存中才可以使用。这便是正确的数据库的使用流程。

那么数据库如何把数据读入内存中呢？

这个时候就需要我们将要介绍的实例（instance）了，实例可以理解为内存结构和一组后台进程。

实例是用来将磁盘中的数据读入内存中，并使用数据。

3.2 专业解释

MySQL 在启动过程中，会：

启动后台守护进程（mysqld）；
生成工作线程；
预分配内存结构供 MySQL 处理数据使用；

所以实例是：MySQL 的后台进程 + 线程 + 预分配的内存结构

而 MySQL 是单进程多线程，也就是说 MySQL 实际在系统中表现的就是一个服务进程，即进程（通过多种方法可以创建多实例，再安装一个端口号不同的 MySQL，或者通过 workbench 来新建一个端口号不同的服务实例等）

4. mysql 和 mysqld 的区别

mysqld 是 SQL 的后台程序（即 MySQL 服务器），mysqld 意思是 mysql daemon，在后台运行，监听 3306 端口，要想使用客户端程序，该程序必须运行，因为客户端通过连接服务器来访问数据库。
mysql 是交互式输入 SQL 语句或从文件以批处理模式执行它们的命令行工具。

5. SQL 语句引入

我们平时在 Linux 经常会使用一些专业的命令来管理我们操作系统中的对象，比如 touch，mkidr。这些命令是 Linux 操作系统 bash shell 支持的一些功能。

对于 MySQL 也一样，MySQL 可能不会用 ls 这些命令，MySQL 也有一些专用的内置命令，用来管理数据库中的数据，我们把这种命令称为 SQL（Structured Query Language，结构化查询语言）。

为了更方便的学习记忆，我们将 SQL 语句分为如下常用的几类：

DML：（Data Manipulation Language，数据操作语言），由数据库管理系统（DBMS）提供，用于让用户或程序员使用，实现对数据库中数据的操作。主要包括 select、insert、update、delete、merge、explain plan、call、lock table 等语句。
DDL：（Data Definition Language，数据定义语言），用于定于 SQL 模式、基本表、视图和索引的创建、撤销操作，主要包含 create、alter、drop、truncate、comment、replace(rename) 等语句，一般不需要 commit 等事务操作。
DCL：（Data Control Language，数据控制语言），用于数据库授权、角色控制等管理工作。主要包含 grant、revoke 等语句。
TCL：（Transaction Control Language，事务控制语言），用于数据库的事务管理。主要包含 savepoint、rollback，commit，set transaction 等语句。

比如我们熟悉的这条语句，在 Linux 中无法执行。

# select user,host from mysql.user;
-bash: 未预期的符号 `from' 附近有语法错误

我们得进入到 mysql 中才能运行。

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

整个过程看起来非常简单，输入语句，点击回车，然后就显示结果了。

但是我们有没有想过，为什么我们在平时的业务开发中，比如要实现一个功能，有的人写的语句执行就快，有的人写的语句执行就慢，结果都是一样的，那么为什么一个快一个慢呢？

我们可能会想到有可能执行的慢的语句非常长、写的很复杂，是的，有可能是这个原因。

那么我们再深入想一想，有没有可能是语句在内部执行过程中，发生了什么不可预料的操作，或者是一些操作是代价较高的操作。我们可能不能从表面上看出问题，另外呢，也不一定 SQL 长就慢，反而有可能是越长的 SQL 执行越快，越简易的语句执行的越慢。

因此，我们不应该仅仅关注结果，而不去关注过程，就无法找到根本的原因，接下来，我们来学习一下，从连接数据库开始，到输入 SQL 语句，然后点击回车，然后显示结果，这个过程中间到底发生了什么。

接下来，我们就通过这一条查询语句，来学习一下 MySQL 详细的工作流程。

6. MySQL 内部执行过程

6.1 mysqld 程序结构

mysqld 结构，我们分成了三层：连接层、SQL 层、存储引擎层。

MySQL 体系结构

6.2 连接层

首先一条语句可能是一个用户发起的，比如 Navicat，Navicat 执行如下一条语句：

select user,host from mysql.user;

那么首先 Navicat 得连接上 MySQL，那么它怎么连接呢？我们之前有说过，通过 tcp/ip 或者 socket 方式进行连接。这说明连接层得支持这两种协议，并支持使用这两种协议进行连接。

然后，比如我们使用 tcp/ip 方式连接，我们还要输入用户名、密码、IP、端口号。假设我输入的端口号是 3307，那么能够登陆吗，肯定是不可以的，因为我们配置的端口号是 3306，那说明连接层的作用还有对用户名、密码、IP、端口号等进行校验，验证合法性。

连上了之后，我们也说了，在 MySQL 里面所有要实现的功能都需要工作的线程来提供，比如说，接收请求语句，返回结果。所以，在连接层会自动开启一个连接线程，接收语句、查看结果。我们可以通过 show processlist 来查看连接线程情况。

mysql> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info             |
+-----+------+-----------+------+---------+------+----------+------------------+
| 282 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+-----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.01 sec)

然后我在另一个终端上再启动一个 MySQL，再执行一下，发现连接线程变成了两个。

mysql> show processlist;
+-----+------+-----------+------+---------+------+----------+------------------+
| Id  | User | Host      | db   | Command | Time | State    | Info             |
+-----+------+-----------+------+---------+------+----------+------------------+
| 282 | root | localhost | NULL | Sleep   |   20 |          | NULL             |
| 286 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+-----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)

默认连接线程最多有 151 个，当然这个数字可以调整。没有用户请求进来都会开辟一个会话，如果这个会话 8 个小时没有动作的话，就会断开这个连接。

到这里，连接层的工作就做完了，接着把请求传递给下一层。

总结一下，连接层作用：

通过连接协议：tcp/ip 以及 socket 连接；
登录验证：验证用户名、密码、IP、端口号等合法性；
提供连接线程：接收用户 SQL 语句以及返回结果（使用 show processlist 命令可以查看用户连接的线程情况）
将请求传递给下一层，SQL 层。

6.3 SQL 层

接收上层传送的 SQL 语句。

SQL 层肯定首先将 SQL 接收到，然后才能执行后面的操作。
语法检查：验证语句语法，判断是否满足 SQL_MODE。

语法检查肯定是必要的，如果语法检查都没用过，那肯定也没法执行了。

SQL_MODE 常用来解决下面几类问题：
1. 通过设置 SQL_MODE，可以完成不同严格程度的数据校验，有效地保障数据准确性。
2. 通过设置 SQL_MODE 为宽松模式，来保证大多数 SQL 符合标准的 SQL 语法，这样应该用在不同数据库之间进行迁移时，则不需要对业务的 SQL 进行较大的修改。
3. 在不同数据库之前进行数据迁移之前，通过设置 SQL_MODE 可以使 MySQL 上的数据更方便的迁移到目标数据库中。
语义检查。判断 SQL 语句的类型。

在 Linux 上，我们以某个用户登录，比如 work 用户，有些文件是不能删除的，必须切换到 root 用户才可以操作。对于 MySQL 也是如此。但是在进行权限之前，我们可以先对 SQL 语句的类型做一下判断，然后再来判断相应的权限。
- DDL：数据定义语言，如 create/drop/alter/
- DCL：数据控制语言，如 grant/revoke/commit
- DML：数据操作语言，如 insert/delete/update/select
- DQL：数据查询语言，如 select
权限检查，检查用户对库、表是否有相应权限。
解析器：进行 SQL 语句的预处理，生成解析器（explain desc），生成多种执行方案。

MySQL 执行语句是否可以直接执行？执行完之后是否会产生不好的效果？我们是否需要评估一下语句的代价有多高？找到一个最快的、最合适的执行方式。
优化器：根据解析器得出的多种执行方案，进行判断，选出最优的执行计划。

代价模型：以前 MySQL 是按照时间去衡量 SQL 语句的优劣，现在按（CPU/IO/MEM）的损耗评估性能的好坏（基于代价）。

各个版本的优化器算法是不同的。
执行器，选择最优的执行计划去执行 SQL 语句，产生执行的结果。

真正运行 SQL。

执行结果：会提供给存储引擎层一个结果说明这个查询的结果在磁盘的哪个位置。
提供查询缓存（默认不开）。

如果某些 SQL 一直执行，比如执行 1000 万次，那么我们就没有必要一直执行了，我们可以提供一个查询缓存，将请求结果放到缓存中。
提供日志记录（binlog），记录二进制日志，默认不开启。

包括审计日志、通用日志、binlog。

6.4 存储引擎层

在 SQL 层中，执行器执行完，会得出一个结果，来展示给我们，但是我们的数据在哪呢，其实还在磁盘上。

Linux 上对于磁盘使用是文件系统，不能直接对磁盘进行读写。MySQL 也是如此。MySQL 将这个专门负责特殊数据读写的文件系统叫做存储引擎。（类似于 FS）。

存储引擎层的作用：

根据 SQL 的执行结果，去磁盘上找到相应数据。
找到磁盘上的 16 进制数据，再次返回 SQL 层，结构化成二维表的方式，再由连接层的专用线程返回用户，最终展现出来。

7. MySQL 逻辑存储结构

我们可以把 MySQL 理解成一个文件系统，因此很多概念、命令可以对比 Linux 学习。下标中总结了 MySQL 和 Linux 中类似的概念。

MySQL 中概念	Linux 中概念
库	目录
create database account charset utf8mb4;	mkdir /account
show databases;	ls /
use account;	cd /account
表	文件
列（字段）
数据行（记录）	数据行
表属性	文件属性
列属性

8. MySQL 物理存储结构

8.1 数据库

MySQL 中的库在文件系统上是使用目录来表示。我们 MySQL 的数据存储在 /data/mysql/data 下。

首先我们查看一下我们的数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

然后我在 /data/mysql/data 下创建个 account 目录。

# mkdir account

我们再来看一下数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| account            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

可以看到多了一个 account 数据库。

8.2 表

我们现在来看一下常见的 MyISAM 存储引擎和 InnoDB 存储引擎中数据是如何存储的。以 mysql.user 表为例。

8.2.1 MyISAM

# cd /data/mysql/data/mysql
# ls
......
-rw-r----- 1 mysql mysql   10816 1月  13 22:25 user.frm
-rw-r----- 1 mysql mysql     396 1月  14 17:21 user.MYD
-rw-r----- 1 mysql mysql    4096 1月  14 17:21 user.MYI

对于 MyISAM 存储引擎，MySQL 用 user.frm、user.MYD、user.MYI 三张表来存储数据。

user.frm：存储表结构（列、属性）
user.MYD：存储的数据记录
user.MYI：存储索引

8.2.2 InnoDB

-rw-r----- 1 mysql mysql    8636 1月  13 22:25 time_zone.frm
-rw-r----- 1 mysql mysql   98304 1月  13 22:25 time_zone.ibd

对于 InnoDB 存储引擎，MySQL 用 time_zone.frm、time_zone.ibd 两张表来存储数据。

time_zone.frm：存储表结构（列、属性）
time_zone.ibd：存储的数据记录和索引

其实对于 InnoDB 存储引擎，MySQL 还有一个文件用来存储元数据，也就是数据字典。该文件名叫 ibdata1。我们在 /data/mysql/data 目录下。

9. InnoDB 段、区、页

一般情况下（非分区表）：

一个表就是一个段；
一个段由多个区构成；
一个区在一个页是 16k 大小的情况下，由 64 个连续的页构成，共 1M 大小；

10. 用户和权限管理

10.1 用户作用

登录 MySQL
管理 MySQL

10.2 用户定义

用户名@'白名单'，例如：root@'localhost'

10.3 用户操作

10.3.1 创建用户：

mysql> create user user2@'localhost';
mysql> exit

# mysql -uuser2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 307
......

10.3.2 创建用户（带密码）：

mysql> create user user3@'localhost' identified by '123';
Query OK, 0 rows affected (0.00 sec)

10.3.3 查询用户：

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| user1         | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| user2         | localhost |
| user3         | localhost |
+---------------+-----------+
6 rows in set (0.00 sec)

10.3.3 修改用户密码

mysql> alter user user3@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

10.3.4 删除用户

mysql> drop user user3@'localhost';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| user1         | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| user2         | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

10.4 权限管理

10.4.1 权限列表

ALL：所有权限

select、insert、update、delete ......
with grant option：授权权限（可以给其他用户授权）

10.4.2 授权命令

grant 权限 on 作用目标 to 用户 identified 密码 with grant option;

作用目标：

*.*：MySQL 下所有数据库所有表。
account.*：account 数据库下面的所有表
account.t1：account 数据库下面的 t1 表

10.4.3 授权需求

创建一个管理员用户，可以通过 10 网段，管理数据库

mysql> grant all on *.* to root@'10.0.0.*' identified by '123' with grant option;

创建一个应用用户，可以通过 10 网段，可以对 account 库下的表进行 select、insert、delete、update

mysql> grant select,insert,delete,update on account.* to user4@'10.0.0.*' identified by '123';

10.4.4 回收权限

查看权限

mysql> show grants for user4@'10.0.0.*';
+---------------------------------------------------------------------------+
| Grants for user4@10.0.0.*                                                 |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user4'@'10.0.0.*'                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `account`.* TO 'user4'@'10.0.0.*' |
+---------------------------------------------------------------------------+
2 rows in set (0.00 sec)

回收权限

mysql> revoke delete on account.* from user4@'10.0.0.*';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for user4@'10.0.0.*';
+-------------------------------------------------------------------+
| Grants for user4@10.0.0.*                                         |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user4'@'10.0.0.*'                          |
| GRANT SELECT, INSERT, UPDATE ON `account`.* TO 'user4'@'10.0.0.*' |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

MySQL 学习（2）—— MySQL 体系结构与管理 | 8月更文挑战