cocoa mysql_MySQL(一)——重新认识MySQL

该篇文章是MySQL 是怎样运行的：从根儿上理解 MySQL的第二章内容，原文讲的非常好，推荐看原文，以下都是个人整理，不是很全。

重新认识MySQL

MySQL的客户端/服务端架构

MySQL的服务器程序直接和我们存储的数据打交道，然后可以有好多客户端程序连接到这个服务器程序，发送增删改查的请求，然后服务器就响应这些请求，从而操作它维护的数据。

MySQL的每个客户端都需要提供用户名密码才能登录，登录之后才能给服务器发请求来操作某些数据。我们日常使用MySQL的情景一般是这样的：

启动MySQL服务器程序。

启动MySQL客户端程序并连接到服务器程序。

在客户端程序中输入一些命令语句作为请求发送到服务器程序，服务器程序收到这些请求后，会根据请求的内容来操作具体的数据并向客户端返回操作结果。

我们的MySQL服务器程序和客户端程序本质上都算是计算机上一个进程，这个代表着MySQL服务器程序的进程也被称为MySQL数据库实例，简称数据库实例。

每个进程都有一个唯一的编号，称为进程ID，英文名叫PID，这个编号是我们在启动程序的时候由操作系统随机分配的，操作系统会保证在某一时刻同一台机器上的进程号不重复。

每个进程都有一个名称，这个名称是编写程序的人自定义的，比如启动MySQL服务器进程的默认名称为mysqld，而我们常用的MySQL客户端进程的默认名称为mysql

MySQL的安装

macOS操作系统上的安装目录(默认)：

/usr/local/mysql

Windows操作系统上的安装目录(默认)：

C:\Program Files\MySQL\MySQL Server 5.7

启动MySQL服务器程序

Unix里启动服务器程序

在类Unix系统中用来启动MySQL服务器程序的可执行文件有很多，大多在MySQL安装目录的bin目录下。

mysqld

mysqld这个可执行文件就代表着MySQL服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。但这个命令不常用。

mysqld_safe

mysqld_safe是一个启动脚本，它会间接的调用mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在服务器进程挂了的时候，可以帮助重启它。另外，使用mysqld_safe启动服务器程序时，它会将服务器程序的出错信息和其他诊断信息重定向到某个文件中，产生出错的日志，方便我们找出发生错误的原因。

mysql.server

mysql.server也是一个启动脚本，它会间接的调用mysqld_safe，在调用mysql.server时在后边指定start参数就可以启动服务器程序了。

mysql.server start

需要注意的是，这个mysql.server文件其实是一个链接文件，它的实际文件是../support-files/mysql.server。我使用的macOS操作系统会帮助我们在bin目录下自动创建一个指向实际文件的链接文件，如果你的操作系统没有帮你自动创建这个链接文件，那就自己创建一个。

sudo ln -s ../support-files/mysql.server mysql.server

另外，还可以使用mysql.server命令来关闭正在运行的服务器程序，只要把start参数换成stop就好了。

mysql.server stop

mysqld_multi

其实我们一台计算机上也可以运行多个服务器实例，也就是运行多个MySQL服务器进程。mysql_multi可执行文件可以对每一个服务器进程的启动或停止进行监控。

启动MySQL客户端程序

在成功启动MySQL服务器程序后，就可以接着启动客户端程序来连接到这个服务器，bin目录下有许多客户端程序，比方说mysqladmin、mysqldump、mysqlcheck等等。这里重点关注的是可执行文件mysql，通过这个可执行文件可以让我们和服务器程序进程交互，也就是发送请求，接收服务器的处理结果。启动这个可执行文件时一般需要一些参数，格式如下：

mysql -h主机名 -u用户名 -p密码

各个参数的意义如下：

参数名

含义

-h

表示服务器进程所在计算机的域名或IP地址，如果服务器进程就运行在本机的话，可以省略这个参数，或者填写localhost或127.0.0.1。也可以写作—host=主机名的形式

-u

表示用户名。也可以写作—user=用户名的形式

-p

表示密码。也可以写作—password=密码的形式像h、u、p这样名称只有一个英文字母的参数称为短形式的参数，使用时前边需要加单短划线，像host、user、password这样大于一个英文字母的参数称为长形式的参数，使用时前边加双短划线。

mysql -hlocalhost -uroot -proot

最后一行的mysql>是一个客户端的提示符，之后客户端发送给服务器的命令都需要写在这个提示符后边。

如果想要断开客户端与服务器的连接并且关闭客户端的话，可以在mysql->提示符后输入下边任意一个命令：

quit

exit

\q

客户端与服务器连接的过程

客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程。

TCP/IP

真实环境中，数据库服务器进程和客户端进程可能运行在不同的主机中，它们之间必须通过网络来进行通讯。MySQL采用TCP作为服务器和客户端之间的网络通信协议。在网络环境下，每台计算机都有一个唯一的IP地址，如果某个进程有需要采用TCP协议进行网络通信方面的需求，可以向操作系统申请一个端口号，这是一个整数值，它的取值范围是0~65535。这样在网络中的其他进程就可以通过IP地址 + 端口号 的方式来与这个进程连接，这样进程之间就可以通过网络进行通信了。

MySQL服务器启动的时候会默认申请3306端口号，之后就在这个端口号上等待客户端进程进行连接，即MySQL服务器会默认监听3306端口。

如果3306端口号已经被别的进程占用了或者我们单纯的想自定义该数据库实例监听的端口号，那我们可以在启动服务器程序的命令行里添加 -P 参数来明确指定一下端口号，比如这样：(注意这里是大写的 P)

mysqld -P3307

这样MySQL服务器在启动时就会去监听指定的端口号3307。

如果客户端进程想要使用TCP/IP网络来连接到服务器进程，比如在使用mysql来启动客户端时，在 -h 参数后必须跟随IP地址来作为需要连接的服务器进程所在主机的主机名，如果客户端进程和服务器进程在一台计算机中的话，可以使用 127.0.0.1来代表本机的IP地址。另外，如果服务器进程监听的端口号不是默认的3306，可以在使用mysql启动客户端程序时使用 -P 参数(大写的 P，小写的 p 是用来指定密码的)来指定需要连接到的端口号。

mysql -h127.0.0.1 -uroot -P3307 -p

服务器处理客户端请求

不论客户端进程和服务器进程采用哪种方式进行通信，最后实现的效果都是：客户端进程向服务器进程发送一段文本(MySQL语句)，服务器进程处理后再向客户端进程发送一段文本(处理结果)。以查询请求为例画个图展示一下大致过程：

从图中可以看出，服务器程序处理来自客户端的查询请求大致需要经过三个部分，分别是连接管理、解析与优化、存储引擎。

连接管理

每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理这个客户端的交互，当该客户端退出时会与服务器断开连接，但是服务器并不会立即把与该客户端交互的线程销毁掉，而是把它换成起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端，这样就起到了不频繁创建和销毁线程的效果，从而节省开销。

当连接建立后，与该客户端关联的服务器线程会一直等待客户端发送来的请求，MySQL服务器收到请求只是一个文本消息，该文本消息还要经过各种处理。

解析与优化

到现在为止，MySQL服务器已经获得了文本形式的请求，接着还要经过几个比较重要的部分：查询缓存、语法解析和查询优化。

查询缓存

MySQL服务器程序处理查询请求会把刚刚处理过的查询请求和结果缓存起来，如果下次有一模一样的请求过来，直接从换从中查找结果就好了，就不用再去底层的表中查找了。这个查询缓存可以在不同客户端之间共享，也就是说如果客户端A刚刚查询了一个语句，而客户端B之后发送了同样的查询请求，那么客户端B的这次查询可以直接使用查询缓存中的数据。

当然，MySQL服务器并没有人聪明，如果两个查询请求在任何字符上的不同(例如：空格、注释、大小写)，都会导致缓存不会命中。另外，如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如 mysql、information_schema、performance_schema数据库中的表，那这个请求就不会被缓存。

不过既然是缓存，那就有它缓存失效的时候。MySQL的缓存系统会监测涉及到的每张表，只要该表结构或者数据被修改，如对该表使用了INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE、或DROP DATABASE语句，那使用该表的所有高速缓存查询都将变为无效并从高效缓存中删除！

虽然查询缓存有时可以提升系统性能，但也不得不因维护这块缓存而造成一些开销，比如每次都要去查询缓存中检索，查询请求处理完需要更新查询缓存，维护该查询缓存对应的内存区域。从MySQL 5.7.20开始，不推荐使用查询缓存，并在MySQL8.0中删除。

语法解析

如果查询缓存没有命中，接下来就需要进入正式的查询阶段了。因为客户端程序发送过来的请求只是一段文本而已，所以MySQL服务器程序首先要对这段文本做分析，判断请求的语法是否正确，然后从文本中将要查询的表、各种查询条件都提取出来放到MySQL服务器内部使用的一些数据结构上来。

查询优化

语法解析后，服务器程序获得了需要的信息，比如要查询的列是哪些，表是哪个，搜索条件是什么等等，但光有这些是不够的，因为我们写的MySQL语句执行起来效率可能并不是很高，MySQL的优化程序会对我们的语句做一些优化，如外连接转换为内连接、表达式简化等一堆东西。优化的结果就是生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。可以使用EXPLAIN语句来查看某个语句的执行计划。

存储引擎

截止到服务器程序完成了查询优化为止，还没有真正的去访问真实的数据表，MySQL服务器把数据的存储和提取操作都封装到了一个叫存储引擎的模块里。我们知道表是一行一行的记录组成的，但这只是一个逻辑上的概念，物理上如何表示记录，怎么从表中读取数据，怎么把数据写入具体的物理存储器上，这都是存储引擎负载的事情。为了实现不同的功能，MySQL提供了各式各样的存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。

为了管理方便，人们把连接管理、查询缓存、语法解析、查询优化这些并不涉及真实数据存储的功能划分为MySQL Server的功能，把真实存取数据的功能划分为存储引擎的功能。各种不同的存储引擎向上边的MySQL server层提供统一的调用接口(也就是存储引擎API)，包含了几十个底层函数，像“读取索引第一条内容”、“读取索引下一条内容”、“插入记录”等等。

所以在MySQL server完成了查询优化后，只需按照生成的执行计划调用底层存储引擎提供的API，获取到数据后返回给客户端就好了。

常用存储引擎

MySQL支持非常多种存储引擎，先列举一些：

存储引擎

描述

ARCHIVE

用于数据存档(行被插入后不能再修改)

BLACKHOLE

丢弃写操作，读操作会返回空内容

CSV

在存储数据时，以逗号分隔各个数据项

FEDERATED

用来访问远程表

InnoDB

具备外键支持功能的事务存储引擎

MEMORY

置于内存的表

MERGE

用来管理多个MyISAM表构成的表集合

MyISAN

主要的非事务处理存储引擎

NDB

MySQL集群专用存储引擎

其中最常用的就是InnDB和MyISAM，有时会提一下Memory。其中InnoDB是MySQL默认的存储引擎。

关于存储引擎的一些操作

查看当前服务器程序支持的存储引擎

我们可以用下边这个命令来查看当前服务器程序支持的存储引擎：

SHOW ENGINES;

来看一下调用效果：

其中Support列表示该存储引擎是否可用，DEFAULT值代表是当前服务器程序的默认存储引擎。Comment列是对存储引擎的一个描述，英文的。Transactions列代表该存储引擎是否支持事务处理。XA列代表着该存储引擎是否支持分布式事务。Savepoints代表着该存储引擎是否支持部分事务回滚。

设置表的存储引擎

存储引擎是负责对表中的数据进行提取和写入工作，我们可以为不同的表设置不同的存储引擎，也就是说不同的表可以有不同的物理存储结构，不同的提取和写入方式。

创建表时指定存储引擎

我们之前创建表的语句都没有指定表的存储引擎，那就会使用默认的存储引擎InnDB(当然这个默认的存储引擎也是可以修改的)。如果我们想显示的指定一下表的存储引擎，那可以这么写：

CREATE TABLE 表名(

建表语句;

) ENGINE = 存储引擎名称;

比如我们想创建一个存储引擎为MyISAN的表可以这么写：

mysql> CREATE TABLE engine_demo_table(

-> i int

->) ENGINE = MyISAM;

修改表的存储引擎

如果表已经建好了，也可以使用下边这个语句来修饰表的存储引擎：

ALTER TABLE 表名 ENGINE = 存储引擎名称

比如我们修改一下engine_demo_table表的存储引擎：

ALTER TABLE engine_demo_table ENGINE = InnDB;

这时我们查看一下engine_demo_table的表结构：

SHOW CREATE TABLE engine_demo_table\G

执行上面语句就能看到engine_demo_table表的存储引擎了。